Запросы для заполнения таблицы данными из других таблиц

Содержание

  • Работа автозаполнения в Эксель
    • Автозаполнение ячеек числами
    • Инструмент «Заполнить»
    • Автозаполнение формулами
    • Автозаполнение другими значениями
    • Добавление собственных списков
  • Вопросы и ответы

Автозаполнение в Microsoft Excel

Мало кому понравится долго и монотонно вводить одинаковые или однотипные данные в таблицу. Это довольно скучная работа, забирающая большое количество времени. В программе Excel имеется возможность автоматизировать ввод таких данных. Для этого предусмотрена функция автозаполнения ячеек. Посмотрим, как она работает.

Запросы для заполнения таблицы данными из других таблиц

При переносе сайта на другую CMS возникают проблемы c миграцией баз данных, структура как правило разная, универсальных решений нет. Поэтому приходится писать SQL запросы для переноса под конкретный случай. Далее несколько примеров таких запросов.

Если две таблицы одинаковы по структуре, все просто:

INSERT INTO `table_1` SELECT * FROM `table_2`

Для таблиц с разными названиями полей придется делать «подгонку». В запросе данные из `table_2` добавляются `table_1`.

INSERT INTO `table_1` (`id`, `name`, `keywords`, `text`)
SELECT
	NULL AS `id`,
	`title` AS `name`,
	`words` AS `keywords`,
	`short_text` AS `text`
FROM
	`table_2`

Содержание

  • Применение инструментов заполнения
    • Способ 1: встроенный объект для ввода данных Excel
    • Способ 2: создание пользовательской формы
  • Вопросы и ответы

Форма в Microsoft Excel

Для облегчения ввода данных в таблицу в Excel можно воспользоваться специальными формами, которые помогут ускорить процесс заполнения табличного диапазона информацией. В Экселе имеется встроенный инструмент позволяющий производить заполнение подобным методом. Также пользователь может создать собственный вариант формы, которая будет максимально адаптирована под его потребности, применив для этого макрос. Давайте рассмотрим различные варианты использования этих полезных инструментов заполнения в Excel.

Содержание

  • Создание связанных таблиц
    • Способ 1: прямое связывание таблиц формулой
    • Способ 2: использование связки операторов ИНДЕКС — ПОИСКПОЗ
    • Способ 3: выполнение математических операций со связанными данными
    • Способ 4: специальная вставка
    • Способ 5: связь между таблицами в нескольких книгах
  • Разрыв связи между таблицами
    • Способ 1: разрыв связи между книгами
    • Способ 2: вставка значений
  • Вопросы и ответы

Связанные таблицы в Microsoft Excel

При выполнении определенных задач в Excel иногда приходится иметь дело с несколькими таблицами, которые к тому же связаны между собой. То есть, данные из одной таблицы подтягиваются в другие и при их изменении пересчитываются значения во всех связанных табличных диапазонах.

Связанные таблицы очень удобно использовать для обработки большого объема информации. Располагать всю информацию в одной таблице, к тому же, если она не однородная, не очень удобно. С подобными объектами трудно работать и производить по ним поиск. Указанную проблему как раз призваны устранить связанные таблицы, информация между которыми распределена, но в то же время является взаимосвязанной. Связанные табличные диапазоны могут находиться не только в пределах одного листа или одной книги, но и располагаться в отдельных книгах (файлах). Последние два варианта на практике используют чаще всего, так как целью указанной технологии является как раз уйти от скопления данных, а нагромождение их на одной странице принципиально проблему не решает. Давайте узнаем, как создавать и как работать с таким видом управления данными.

Создадим таблицу samples с точно такой же структурой, что и таблица tests. Заполним ее 10 тестовыми записями.

CREATE TABLE samples (
  title varchar(256) NOT NULL
);

INSERT INTO
  samples (title)
VALUES
  ('8iRDgsEIq4GmOs32FnHM3b3cH60n3mm8070'), 
  ('UAmsXyrKgApfHMyV2kUrYqLphN99Q7TJSoe'), 
  ('OX1qiFeTigcOTO5JVvgFk7MRDgjgatkTqwL'), 
  ('CT9Kfbplp4QC87G32UIKlkGd31jdjt4qH4f'), 
  ('WsAhjBL5tAgihYZBtX97FNUmEpXavhb4CRw'), 
  ('LwaitJ5dieXyixEmjJXhhqDY8Zg9Tu5ecoV'), 
  ('KpHsnqrcMCpkRxkGNMjEJV0jFaeucPtbLWe'), 
  ('t2GSIDOvW14eMlroAWrRR6xU5DoeNUXY0lD'), 
  ('rSvrEPxR8rcw7QYjXfeNdyf3LpqYNHu3W7a'), 
  ('6liUHPkjnygSatoUB4juZ5TaJZjaxHpR4BL');

Для того, чтобы превратить 10 записей из таблицы samples в 1 000 000 записей произведем самообъединение таблицы samples шесть раз

SELECT
  fst.title
FROM
  samples AS fst,
  samples AS snd,
  samples AS thd,
  samples AS fth,
  samples AS fif,
  samples AS sth;

Оператор JOIN, который в SQL может быть заменен обычной запятой, осуществляет декартово соединение таблиц, когда каждой записи одной таблицы сопоставляется каждая запись другой таблицы. Таким образом, если в одной таблице 10 записей и в другой таблице 10 записей, результирующая таблица, полученная их соединением через JOIN будет содержать 100 записей. Как нетрудно увидеть

10 x 10 x 10 x 10 x 10 x 10 = 1 000 000 записей

Если в таблице samples будет 100 записей, для получения 1 000 000 записей будет достаточно соединений трех таких таблиц, если в samples будет 1000 записей, будет достаточно объединить таблицу саму с собой один раз.

Вставить результат в целевую таблицу tests можно при помощи оператора INSERT ... SELECT ...

INSERT INTO
  tests
SELECT
  fst.title
FROM
  samples AS fst,
  samples AS snd,
  samples AS thd,
  samples AS fth,
  samples AS fif,
  samples AS sth;

При создании структуры таблицы в режиме Конструктора таблицы (см. Приложение к главе 1) определяются имена полей (пример 2.5) и настраиваются их свойства — тип и формат.

Изменять имена полей можно как в режиме конструктора, так и в режиме таблицы. Выбирать имена полей следует так, чтобы имя было коротким и отражало смысл данных, хранящихся в поле.

Тип данных должен соответствовать значениям, которые предполагается вводить в поле, и операциям, которые будут выполняться с этими значениями. Для поля может быть определен один из следующих типов:

1. Текстовый. Короткий текст (до 255 символов) и длинный текст.

2. Числовой. Числовые данные (целые или действительные).

3. Дата и время. Дата и/или время.

4. Денежный. Денежные данные, хранящиеся с точностью до 4 десятичных знаков после запятой.

5. Счетчик. Последовательность целых чисел, которые задаются автоматически при вводе записей. Эти числа нельзя изменить.

6. Логический. Может иметь значения Истина или Ложь.

7. Поле объекта OLE. Изображения в формате Точечный рисунок.

8. Гиперссылка. Ссылка на информационный ресурс в Интернете.

9. Вложение. Вложениями могут быть изображения, документы, электронные таблицы, диаграммы и другие файлы.

(Рассмотрите пример 2.6.)

В таблицах вложение отображается знаком   с указанием в скобках количества вложений (пример 2.7). Чтобы увидеть содержимое вложения, необходимо создать форму или отчет.

Поле каждого типа имеет свой набор свойств. Наиболее важными являются:

1. Размер поля (пример 2.8), который определяет максимально возможную длину текста или числа.

2. Формат поля, который устанавливает формат данных (пример 2.9).

Обычно одно из полей таблицы при создании структуры определяется как ключевое поле — ключ (пример 2.10).

Ключ (ключевое поле) — поле, значения которого позволяют однозначно определить каждую запись таблицы.

В таблице не может быть двух записей, имеющих одинаковое значение ключа.

Новая таблица состоит из одной пустой записи (пример 2.11). Ввод данных в таблицу базы производится в Режиме таблицы и мало чем отличается от ввода данных в электронную таблицу. При вводе данных пустая запись смещается в конец таблицы (пример 2.12). Отменить ввод значения в поле до перехода к другому полю можно, нажав клавишу Esc или    на панели быстрого доступа.

При заполнении таблиц базы данных нужно соблюдать определенные правила:

1. Заполнение таблиц должно производиться по записям.

2. Вводимые данные должны соответствовать заданному при создании структуры таблицы типу. В случае несоответствия появляется предупреждающее сообщение.

Перед редактированием записей таблицы необходимо их выделить. Для этого достаточно выполнить щелчек в поле маркера соответствующей записи (пример 2.13). Отменяется выделение щелчком в любом месте таблицы вне выделения.

При работе с данными в поле маркера записи таблицы может отображаться один из следующих символов:

1. Звездочка. Обозначает пустую запись в конце таблицы.

2. Карандаш. Обозначает, что запись редактируется.

(Рассмотрите пример 2.14.)

Завершение ввода значений записи осуществляется при переходе к любой другой записи. Access автоматически сохраняет каждую запись по завершении ее обработки.

Для удаления записи необходимо выполнить команду Удалить запись контекстного меню выделенной записи (пример 2.15).

Для быстрого перемещения между записями можно использовать кнопки панели навигации окна таблицы (пример 2.16).

Пример 2.5. Определение имен полей таблицы.

Имена полей должны быть уникальны и содержать не более 64 символов. Они могут включать любые комбинации букв, цифр пробелов и специальных символов за исключением:

  • точки;
  • восклицательного знака;
  • надстрочного символа;
  • прямых скобок.

Имя не должно начинаться с пробела.

Пример 2.6. Определение типа данных в поле таблицы.

Запросы для заполнения таблицы данными из других таблиц

Для раскрытия списка типов полей необходимо нажать на кнопку со стрелкой.

Пример 2.7. Тип данных Вложение.

Запросы для заполнения таблицы данными из других таблиц

В этой таблице вложениями являются файлы с изображениями памятных монет Республики Беларусь (аверс и реверс).

Пример 2.8. Свойство Размер поля типа данных Числовой.

Запросы для заполнения таблицы данными из других таблиц

Пример 2.9. Свойство Формат поля типа данных Денежный.

Запросы для заполнения таблицы данными из других таблиц

Пример 2.10. Определение ключевого поля в структуре таблицы.

Для определения поля как ключевого нужно выбрать кнопку:

Запросы для заполнения таблицы данными из других таблиц

В качестве ключа чаще всего используют поле, содержащее тип данных Счетчик. Однако иногда удобнее в качестве ключа таблицы использовать другие поля: код товара, инвентарный номер и т. п.

Пример 2.11. Новая таблица.

Запросы для заполнения таблицы данными из других таблиц

Пример 2.12. Заполнение таблицы данными в Access.

Запросы для заполнения таблицы данными из других таблиц

При вводе данных к следующей ячейке можно перейти при помощи клавиши Enter либо Tab. В обратном направлении — с помощью комбинации клавиш Shift + Tab. Используя комбинацию клавиш Ctrl + Home, можно перейти в первую ячейку таблицы, Ctrl + End — в последнюю.

Пример 2.13. Выделение записи таблицы.

Запросы для заполнения таблицы данными из других таблиц

Пример 2.14. Символы в поле маркера.

Запросы для заполнения таблицы данными из других таблиц

Пример 2.15. Удаление записи из таблицы.

Запросы для заполнения таблицы данными из других таблиц

Пример 2.16. Панель навигации таблицы базы данных.

Запросы для заполнения таблицы данными из других таблиц

Соединение полей

INSERT INTO `table_1` (`id`, `name`, `text`)
SELECT
	NULL AS `id`,
	`title` AS `name`,
	CONCAT(`words`, `short_text`) AS `text`
FROM
	`table_2`

Соединение полей через разделитель.

INSERT INTO `table_1` (`id`, `name`, `text`)
SELECT
	NULL AS `id`,
	`title` AS `name`,
	CONCAT_WS(' ', `words`, `short_text`) AS `text`
FROM
	`table_2`

Объединение таблиц

LEFT JOIN

INSERT INTO `table_1` (`id`, `name`, `keywords`, `text`)
SELECT
	NULL AS `id`,
	`table_2`.`title` AS `name`,
	`table_2`.`words` AS `keywords`,
	`table_3`.`full_text` AS `text
FROM
	`table_2`
LEFT JOIN
	`table_3`
ON
	`table_2`.`id` = `table_3`.`item_id`

UNION

INSERT INTO `table_1` (`id`, `name`, `text`)
(
	SELECT
		NULL AS `id`,
		`title` AS `name`,
		`short_text` AS `text`
	FROM
		`table_2`
)
UNION
(
	SELECT
		NULL AS `id`,
		`title` AS `name`,
		`short_text` AS `text`
	FROM
		`table_3`
)

Создание связанных таблиц

Прежде всего, давайте остановимся на вопросе, какими способами существует возможность создать связь между различными табличными диапазонами.

Способ 1: прямое связывание таблиц формулой

Самый простой способ связывания данных – это использование формул, в которых имеются ссылки на другие табличные диапазоны. Он называется прямым связыванием. Этот способ интуитивно понятен, так как при нем связывание выполняется практически точно так же, как создание ссылок на данные в одном табличном массиве.

Посмотрим, как на примере можно образовать связь путем прямого связывания. Имеем две таблицы на двух листах. На одной таблице производится расчет заработной платы с помощью формулы путем умножения ставки работников на единый для всех коэффициент.

Таблица заработной платы в Microsoft Excel

На втором листе расположен табличный диапазон, в котором находится перечень сотрудников с их окладами. Список сотрудников в обоих случаях представлен в одном порядке.

Таблица со ставками сотрудников в Microsoft Excel

Нужно сделать так, чтобы данные о ставках из второго листа подтягивались в соответствующие ячейки первого.

  1. На первом листе выделяем первую ячейку столбца «Ставка». Ставим в ней знак «=». Далее кликаем по ярлычку «Лист 2», который размещается в левой части интерфейса Excel над строкой состояния.
  2. Переход на второй лист в Microsoft Excel

  3. Происходит перемещения во вторую область документа. Щелкаем по первой ячейке в столбце «Ставка». Затем кликаем по кнопке Enter на клавиатуре, чтобы произвести ввод данных в ячейку, в которой ранее установили знак «равно».
  4. Связывание с ячейкой второй таблицы в Microsoft Excel

  5. Затем происходит автоматический переход на первый лист. Как видим, в соответствующую ячейку подтягивается величина ставки первого сотрудника из второй таблицы. Установив курсор на ячейку, содержащую ставку, видим, что для вывода данных на экран применяется обычная формула. Но перед координатами ячейки, откуда выводятся данные, стоит выражение «Лист2!», которое указывает наименование области документа, где они расположены. Общая формула в нашем случае выглядит так:
  6. Две ячейки двух таблиц связаны в Microsoft Excel

  7. Теперь нужно перенести данные о ставках всех остальных работников предприятия. Конечно, это можно сделать тем же путем, которым мы выполнили поставленную задачу для первого работника, но учитывая, что оба списка сотрудников расположены в одинаковом порядке, задачу можно существенно упростить и ускорить её решение. Это можно сделать, просто скопировав формулу на диапазон ниже. Благодаря тому, что ссылки в Excel по умолчанию являются относительными, при их копировании происходит сдвиг значений, что нам и нужно. Саму процедуру копирования можно произвести с помощью маркера заполнения.

    Итак, ставим курсор в нижнюю правую область элемента с формулой. После этого курсор должен преобразоваться в маркер заполнения в виде черного крестика. Выполняем зажим левой кнопки мыши и тянем курсор до самого низа столбца.

  8. Маркер заполнения в Microsoft Excel

  9. Все данные из аналогичного столбца на Листе 2 были подтянуты в таблицу на Листе 1. При изменении данных на Листе 2 они автоматически будут изменяться и на первом.

Все данные столбца второй таблицы перенесены в первую в Microsoft Excel

Lumpics.ru

Способ 2: использование связки операторов ИНДЕКС — ПОИСКПОЗ

Но что делать, если перечень сотрудников в табличных массивах расположен не в одинаковом порядке? В этом случае, как говорилось ранее, одним из вариантов является установка связи между каждой из тех ячеек, которые следует связать, вручную. Но это подойдет разве что для небольших таблиц. Для массивных диапазонов подобный вариант в лучшем случае отнимет очень много времени на реализацию, а в худшем – на практике вообще будет неосуществим. Но решить данную проблему можно при помощи связки операторов ИНДЕКС – ПОИСКПОЗ. Посмотрим, как это можно осуществить, связав данные в табличных диапазонах, о которых шел разговор в предыдущем способе.

  1. Выделяем первый элемент столбца «Ставка». Переходим в Мастер функций, кликнув по пиктограмме «Вставить функцию».
  2. Вставить функцию в Microsoft Excel

  3. В Мастере функций в группе «Ссылки и массивы» находим и выделяем наименование «ИНДЕКС».
  4. Переход в окно аргуметов функции ИНДЕКС в Microsoft Excel

  5. Данный оператор имеет две формы: форму для работы с массивами и ссылочную. В нашем случае требуется первый вариант, поэтому в следующем окошке выбора формы, которое откроется, выбираем именно его и жмем на кнопку «OK».
  6. Выбор формы функции ИНДЕКС в Microsoft Excel

  7. Выполнен запуск окошка аргументов оператора ИНДЕКС. Задача указанной функции — вывод значения, находящегося в выбранном диапазоне в строке с указанным номером. Общая формула оператора ИНДЕКС такова:

    «Массив» — аргумент, содержащий адрес диапазона, из которого мы будем извлекать информацию по номеру указанной строки.

    «Номер строки» — аргумент, являющийся номером этой самой строчки. При этом важно знать, что номер строки следует указывать не относительно всего документа, а только относительно выделенного массива.

    «Номер столбца» — аргумент, носящий необязательный характер. Для решения конкретно нашей задачи мы его использовать не будем, а поэтому описывать его суть отдельно не нужно.

    Ставим курсор в поле «Массив». После этого переходим на Лист 2 и, зажав левую кнопку мыши, выделяем все содержимое столбца «Ставка».

  8. Аргумент Массив в окне аргументов функции ИНДЕКС в Microsoft Excel

  9. После того, как координаты отобразились в окошке оператора, ставим курсор в поле «Номер строки». Данный аргумент мы будем выводить с помощью оператора ПОИСКПОЗ. Поэтому кликаем по треугольнику, который расположен слева от строки функций. Открывается перечень недавно использованных операторов. Если вы среди них найдете наименование «ПОИСКПОЗ», то можете кликать по нему. В обратном случае кликайте по самому последнему пункту перечня – «Другие функции…».
  10. Окно аргументов функции ИНДЕКС в Microsoft Excel

  11. Запускается стандартное окно Мастера функций. Переходим в нем в ту же самую группу «Ссылки и массивы». На этот раз в перечне выбираем пункт «ПОИСКПОЗ». Выполняем щелчок по кнопке «OK».
  12. Переход в окно аргуметов функции ПОИСКПОЗ в Microsoft Excel

  13. Производится активация окошка аргументов оператора ПОИСКПОЗ. Указанная функция предназначена для того, чтобы выводить номер значения в определенном массиве по его наименованию. Именно благодаря данной возможности мы вычислим номер строки определенного значения для функции ИНДЕКС. Синтаксис ПОИСКПОЗ представлен так:

    «Искомое значение» — аргумент, содержащий наименование или адрес ячейки стороннего диапазона, в которой оно находится. Именно позицию данного наименования в целевом диапазоне и следует вычислить. В нашем случае в роли первого аргумента будут выступать ссылки на ячейки на Листе 1, в которых расположены имена сотрудников.

    «Просматриваемый массив» — аргумент, представляющий собой ссылку на массив, в котором выполняется поиск указанного значения для определения его позиции. У нас эту роль будет исполнять адрес столбца «Имя» на Листе 2.

    «Тип сопоставления» — аргумент, являющийся необязательным, но, в отличие от предыдущего оператора, этот необязательный аргумент нам будет нужен. Он указывает на то, как будет сопоставлять оператор искомое значение с массивом. Этот аргумент может иметь одно из трех значений: -1; 0; 1. Для неупорядоченных массивов следует выбрать вариант «0». Именно данный вариант подойдет для нашего случая.

    Итак, приступим к заполнению полей окна аргументов. Ставим курсор в поле «Искомое значение», кликаем по первой ячейке столбца «Имя» на Листе 1.

  14. Аргумент Искомое значение в окне аргументов функции ПОИСКПОЗ в Microsoft Excel

  15. После того, как координаты отобразились, устанавливаем курсор в поле «Просматриваемый массив» и переходим по ярлыку «Лист 2», который размещен внизу окна Excel над строкой состояния. Зажимаем левую кнопку мыши и выделяем курсором все ячейки столбца «Имя».
  16. Аргумент Просматриваемый массив в окне аргументов функции ПОИСКПОЗ в Microsoft Excel

  17. После того, как их координаты отобразились в поле «Просматриваемый массив», переходим к полю «Тип сопоставления» и с клавиатуры устанавливаем там число «0». После этого опять возвращаемся к полю «Просматриваемый массив». Дело в том, что мы будем выполнять копирование формулы, как мы это делали в предыдущем способе. Будет происходить смещение адресов, но вот координаты просматриваемого массива нам нужно закрепить. Он не должен смещаться. Выделяем координаты курсором и жмем на функциональную клавишу F4. Как видим, перед координатами появился знак доллара, что означает то, что ссылка из относительной превратилась в абсолютную. Затем жмем на кнопку «OK».
  18. Окно аргуметов функции ПОИСКПОЗ в Microsoft Excel

  19. Результат выведен на экран в первую ячейку столбца «Ставка». Но перед тем, как производить копирование, нам нужно закрепить ещё одну область, а именно первый аргумент функции ИНДЕКС. Для этого выделяем элемент колонки, который содержит формулу, и перемещаемся в строку формул. Выделяем первый аргумент оператора ИНДЕКС (B2:B7) и щелкаем по кнопке F4. Как видим, знак доллара появился около выбранных координат. Щелкаем по клавише Enter. В целом формула приняла следующий вид:
  20. Преобразование ссылки в абсолютную в Microsoft Excel

  21. Теперь можно произвести копирование с помощью маркера заполнения. Вызываем его тем же способом, о котором мы говорили ранее, и протягиваем до конца табличного диапазона.
  22. Маркер заполнения в программе Microsoft Excel

  23. Как видим, несмотря на то, что порядок строк у двух связанных таблиц не совпадает, тем не менее, все значения подтягиваются соответственно фамилиям работников. Этого удалось достичь благодаря применению сочетания операторов ИНДЕКСПОИСКПОЗ.

Значения связаны благодаря комбинации функций ИНДЕКС-ПОИСКПОЗ в Microsoft Excel

Читайте также:
Функция ИНДЕКС в Экселе
Функция ПОИСКПОЗ в Экселе

Способ 3: выполнение математических операций со связанными данными

Прямое связывание данных хорошо ещё тем, что позволяет не только выводить в одну из таблиц значения, которые отображаются в других табличных диапазонах, но и производить с ними различные математические операции (сложение, деление, вычитание, умножение и т.д.).

Посмотрим, как это осуществляется на практике. Сделаем так, что на Листе 3 будут выводиться общие данные заработной платы по предприятию без разбивки по сотрудникам. Для этого ставки сотрудников будут подтягиваться из Листа 2, суммироваться (при помощи функции СУММ) и умножаться на коэффициент с помощью формулы.

  1. Выделяем ячейку, где будет выводиться итог расчета заработной платы на Листе 3. Производим клик по кнопке «Вставить функцию».
  2. Переход в Мастер функций в Microsoft Excel

  3. Следует запуск окна Мастера функций. Переходим в группу «Математические» и выбираем там наименование «СУММ». Далее жмем по кнопке «OK».
  4. Переход в окно аргуметов функции СУММ в Microsoft Excel

  5. Производится перемещение в окно аргументов функции СУММ, которая предназначена для расчета суммы выбранных чисел. Она имеет нижеуказанный синтаксис:

    Поля в окне соответствуют аргументам указанной функции. Хотя их число может достигать 255 штук, но для нашей цели достаточно будет всего одного. Ставим курсор в поле «Число1». Кликаем по ярлыку «Лист 2» над строкой состояния.

  6. Окно аргметов функции СУММ в Microsoft Excel

  7. После того, как мы переместились в нужный раздел книги, выделяем столбец, который следует просуммировать. Делаем это курсором, зажав левую кнопку мыши. Как видим, координаты выделенной области тут же отображаются в поле окна аргументов. Затем щелкаем по кнопке «OK».
  8. Суммирование данных с помощью функции СУММ в Microsoft Excel

  9. После этого мы автоматически перемещаемся на Лист 1. Как видим, общая сумма размера ставок работников уже отображается в соответствующем элементе.
  10. Общая сумма ставок работников в Microsoft Excel

  11. Но это ещё не все. Как мы помним, зарплата вычисляется путем умножения величины ставки на коэффициент. Поэтому снова выделяем ячейку, в которой находится суммированная величина. После этого переходим к строке формул. Дописываем к имеющейся в ней формуле знак умножения (*), а затем щелкаем по элементу, в котором располагается показатель коэффициента. Для выполнения вычисления щелкаем по клавише Enter на клавиатуре. Как видим, программа рассчитала общую заработную плату по предприятию.
  12. Общая зарплата по предприятию в Microsoft Excel

  13. Возвращаемся на Лист 2 и изменяем размер ставки любого работника.
  14. Изменение ставки работника в Microsoft Excel

  15. После этого опять перемещаемся на страницу с общей суммой. Как видим, из-за изменений в связанной таблице результат общей заработной платы был автоматически пересчитан.

Сумма заработной платы по предприятию пересчитана в Microsoft Excel

Способ 4: специальная вставка

Связать табличные массивы в Excel можно также при помощи специальной вставки.

  1. Выделяем значения, которые нужно будет «затянуть» в другую таблицу. В нашем случае это диапазон столбца «Ставка» на Листе 2. Кликаем по выделенному фрагменту правой кнопкой мыши. В открывшемся списке выбираем пункт «Копировать». Альтернативной комбинацией является сочетание клавиш Ctrl+C. После этого перемещаемся на Лист 1.
  2. Копирование в Microsoft Excel

  3. Переместившись в нужную нам область книги, выделяем ячейки, в которые нужно будет подтягивать значения. В нашем случае это столбец «Ставка». Щелкаем по выделенному фрагменту правой кнопкой мыши. В контекстном меню в блоке инструментов «Параметры вставки» щелкаем по пиктограмме «Вставить связь».
    Вставка связи через контекстное меню в Microsoft Excel

    Существует также альтернативный вариант. Он, кстати, является единственным для более старых версий Excel. В контекстном меню наводим курсор на пункт «Специальная вставка». В открывшемся дополнительном меню выбираем позицию с одноименным названием.

  4. Переход в специальную вставку в Microsoft Excel

  5. После этого открывается окно специальной вставки. Жмем на кнопку «Вставить связь» в нижнем левом углу ячейки.
  6. Окно специальной вставки в Microsoft Excel

  7. Какой бы вариант вы не выбрали, значения из одного табличного массива будут вставлены в другой. При изменении данных в исходнике они также автоматически будут изменяться и во вставленном диапазоне.

Значения вставлены с помощью специальной вставки в Microsoft Excel

Урок: Специальная вставка в Экселе

Способ 5: связь между таблицами в нескольких книгах

Кроме того, можно организовать связь между табличными областями в разных книгах. При этом используется инструмент специальной вставки. Действия будут абсолютно аналогичными тем, которые мы рассматривали в предыдущем способе, за исключением того, что производить навигацию во время внесений формул придется не между областями одной книги, а между файлами. Естественно, что все связанные книги при этом должны быть открыты.

  1. Выделяем диапазон данных, который нужно перенести в другую книгу. Щелкаем по нему правой кнопкой мыши и выбираем в открывшемся меню позицию «Копировать».
  2. Копирование данных из книги в Microsoft Excel

  3. Затем перемещаемся к той книге, в которую эти данные нужно будет вставить. Выделяем нужный диапазон. Кликаем правой кнопкой мыши. В контекстном меню в группе «Параметры вставки» выбираем пункт «Вставить связь».
  4. Вставка связи из другой книги в Microsoft Excel

  5. После этого значения будут вставлены. При изменении данных в исходной книге табличный массив из рабочей книги будет их подтягивать автоматически. Причем совсем не обязательно, чтобы для этого были открыты обе книги. Достаточно открыть одну только рабочую книгу, и она автоматически подтянет данные из закрытого связанного документа, если в нем ранее были проведены изменения.

Связь из другой книги вставлена в Microsoft Excel

Но нужно отметить, что в этом случае вставка будет произведена в виде неизменяемого массива. При попытке изменить любую ячейку со вставленными данными будет всплывать сообщение, информирующее о невозможности сделать это.

Информационное сообщение в Microsoft Excel

Изменения в таком массиве, связанном с другой книгой, можно произвести только разорвав связь.

Разрыв связи между таблицами

Иногда требуется разорвать связь между табличными диапазонами. Причиной этого может быть, как вышеописанный случай, когда требуется изменить массив, вставленный из другой книги, так и просто нежелание пользователя, чтобы данные в одной таблице автоматически обновлялись из другой.

Способ 1: разрыв связи между книгами

Разорвать связь между книгами во всех ячейках можно, выполнив фактически одну операцию. При этом данные в ячейках останутся, но они уже будут представлять собой статические не обновляемые значения, которые никак не зависят от других документов.

  1. В книге, в которой подтягиваются значения из других файлов, переходим во вкладку «Данные». Щелкаем по значку «Изменить связи», который расположен на ленте в блоке инструментов «Подключения». Нужно отметить, что если текущая книга не содержит связей с другими файлами, то эта кнопка является неактивной.
  2. Переход к изменениям связей в Microsoft Excel

  3. Запускается окно изменения связей. Выбираем из списка связанных книг (если их несколько) тот файл, с которым хотим разорвать связь. Щелкаем по кнопке «Разорвать связь».
  4. Окно изменения связей в Microsoft Excel

  5. Открывается информационное окошко, в котором находится предупреждение о последствиях дальнейших действий. Если вы уверены в том, что собираетесь делать, то жмите на кнопку «Разорвать связи».
  6. Информационное предупреждение о разрыве связи в Microsoft Excel

  7. После этого все ссылки на указанный файл в текущем документе будут заменены на статические значения.

Ссылки заменены на статические значения в Microsoft Excel

Способ 2: вставка значений

Но вышеперечисленный способ подходит только в том случае, если нужно полностью разорвать все связи между двумя книгами. Что же делать, если требуется разъединить связанные таблицы, находящиеся в пределах одного файла? Сделать это можно, скопировав данные, а затем вставив на то же место, как значения. Кстати, этим же способом можно проводить разрыв связи между отдельными диапазонами данных различных книг без разрыва общей связи между файлами. Посмотрим, как этот метод работает на практике.

  1. Выделяем диапазон, в котором желаем удалить связь с другой таблицей. Щелкаем по нему правой кнопкой мыши. В раскрывшемся меню выбираем пункт «Копировать». Вместо указанных действий можно набрать альтернативную комбинацию горячих клавиш Ctrl+C.
  2. Копирование в программе Microsoft Excel

  3. Далее, не снимая выделения с того же фрагмента, опять кликаем по нему правой кнопкой мыши. На этот раз в списке действий щелкаем по иконке «Значения», которая размещена в группе инструментов «Параметры вставки».
  4. Вставка как значения в Microsoft Excel

  5. После этого все ссылки в выделенном диапазоне будут заменены на статические значения.

Значения вставлены в Microsoft Excel

Как видим, в Excel имеются способы и инструменты, чтобы связать несколько таблиц между собой. При этом, табличные данные могут находиться на других листах и даже в разных книгах. При необходимости эту связь можно легко разорвать.

Опишите, что у вас не получилось.
Наши специалисты постараются ответить максимально быстро.

Работа автозаполнения в Эксель

Автозаполнение в Microsoft Excel осуществляется с помощью специального маркера заполнения. Для того, чтобы вызвать этот инструмент нужно навести курсор на нижний правый край любой ячейки. Появится небольшой черный крестик. Это и есть маркер заполнения. Нужно просто зажать левую кнопку мыши и потянуть в ту сторону листа, где вы хотите заполнить ячейки.

Маркер заполнения в Microsoft Excel

То, как будут заполняться ячейки, уже зависит от того типа данных, который находится в исходной ячейке. Например, если там будет обычный текст в виде слов, то при перетягивании с помощью маркера заполнения производится его копирование в другие ячейки листа.

Ячейки заполнены в Microsoft Excel

Автозаполнение ячеек числами

Чаще всего автозаполнение используется для ввода большого массива чисел, которые следуют по порядку. Например, в определенной ячейке стоит цифра 1, а нам нужно пронумеровать ячейки от 1 до 100.

  1. Активируем маркер заполнения и проводим им вниз на необходимое количество ячеек.
  2. Автозаполнение чисел в Microsoft Excel

  3. Но, как видим, во все ячейки скопировалась только единица. Кликаем на значок, который находится снизу слева от заполненной области и называется «Параметры автозаполнения».
  4. Переход в параметры автозаполнения в Microsoft Excel

  5. В открывшемся списке устанавливаем переключатель в пункт «Заполнить».

Автозаполнение ячеек числами по порядку в Microsoft Excel

Как видим, после этого весь нужный диапазон заполнился числами по порядку.

Ячейки числами по порядку заполнены в Microsoft Excel

Но можно сделать ещё проще. Вам не нужно будет вызывать параметры автозаполнения. Для этого, когда тянете маркер заполнения вниз, то кроме зажатой левой кнопки мыши, нужно зажать ещё кнопку Ctrl на клавиатуре. После этого, заполнение ячеек числами по порядку происходит сразу же.

Существует также способ сделать автозаполнения ряда прогрессии.

  1. Вносим в соседние ячейки два первых числа прогрессии.
  2. Два числа прогрессии в Microsoft Excel

    Lumpics.ru

  3. Выделяем их. С помощью маркера заполнения вносим данные в другие ячейки.
  4. Автозаполнение прогрессией в Microsoft Excel

  5. Как видим, создается последовательный ряд чисел с заданным шагом.

Прогрессия в Microsoft Excel

Инструмент «Заполнить»

В программе Excel есть также отдельный инструмент, которые называется «Заполнить». Расположен он на ленте во вкладке «Главная» в блоке инструментов «Редактирование».

Инструмсмент заполнить в Microsoft Excel

  1. Заносим данные в любую ячейку, а затем выделяем её и диапазон ячеек, который собираемся заполнить.
  2. Выделение диапазона в Microsoft Excel

  3. Жмем на кнопку «Заполнить». В появившемся списке выбираем то направление, в которои следует заполнить ячейки.
  4. Заполнение ячеек в Microsoft Excel

  5. Как видим, после этих действий данные из одной ячейки были скопированы во все остальные.

Данные скопированы в Microsoft Excel

С помощью данного инструмента можно также заполнить ячейки прогрессией.

  1. Заносим число в ячейку и выделяем диапазон ячеек, который будет заполняться данными. Жмем на кнопку «Заполнить», а в появившемся списке выбираем пункт «Прогрессия».
  2. Запуск прогрессии в Microsoft Excel

  3. Открывается окно настройки прогрессии. Здесь нужно произвести ряд манипуляций:
    • выбрать расположение прогрессии (по столбцам или по строкам);
    • тип (геометрическая, арифметическая, даты, автозаполнение);
    • установить шаг (по умолчанию он равен 1);
    • установить предельное значение (необязательный параметр).

    Кроме того, в отдельных случаях, устанавливаются единицы измерения.

    Когда все настройки внесены, жмем на кнопку «OK».

  4. Настройка прогрессии в Microsoft Excel

  5. Как видим, после этого весь выделенный диапазон ячеек заполняется согласно установленными вами правилами прогрессии.

Ячейки заполнены прогрессией в Microsoft Excel

Автозаполнение формулами

Одним из основных инструментов Excel являются формулы. При наличии в таблице большого числа одинаковых формул можно также воспользоваться функцией автозаполнения. Суть не меняется. Нужно тем же способом маркера заполнения скопировать формулу в другие ячейки. При этом, если в формуле содержатся ссылки на другие ячейки, то по умолчанию при копировании таким способом координаты их меняются по принципу относительности. Поэтому, такие ссылки называют относительными.

Автозаполнение формулами в Microsoft Excel

Если вы хотите, чтобы при автозаполнении адреса стали фиксированными, то нужно в исходной ячейке перед координатами строк и столбцов поставить знак доллара. Такие ссылки называются абсолютными. Затем, производится обычная процедура автозаполнения с помощью маркера заполнения. Во всех заполненных таким способом ячейках формула будет абсолютно неизменная.

Автозаполнение формулами с абсолютными ссылками в Microsoft Excel

Урок: Абсолютные и относительные ссылки в Excel

Автозаполнение другими значениями

Кроме того, в программе Excel предусмотрено автозаполнение другими значениями по порядку. Например, если вы введете какую-нибудь дату, а затем, воспользовавшись маркером заполнения, выделите другие ячейки, то весь выбранный диапазон окажется заполненный датами в строгой последовательности.

Автозаполнение дат в Microsoft Excel

Точно так же, можно произвести автозаполнение по дням недели (понедельник, вторник, среда…) или по месяцам (январь, февраль, март…).

Автозаполнение дней недели в Microsoft Excel

Более того, если в тексте будет любая цифра, то Эксель распознает её. При использовании маркера заполнения произойдет копирование текста с изменением цифры по нарастающей. Например, если вы запишите в ячейку выражение «4 корпус», то в других ячейках, заполненных с помощью маркера заполнения, это название преобразится в «5 корпус», «6 корпус», «7 корпус» и т.д.

Автозаполнение чисел со словами в Microsoft Excel

Добавление собственных списков

Возможности функции автозаполнения в Excel не ограничивается определенными алгоритмами или предустановленными списками, такими как, например, дни недели. При желании пользователь может в программу добавить свой личный список. Тогда, при записи в ячейку любого слова из элементов, которые находятся в перечне, после применения маркера заполнения, данным списком будет заполнен весь выделенный диапазон ячеек. Для того, чтобы добавить свой список, нужно выполнить такую последовательность действий.

  1. Делаем переход во вкладку «Файл».
  2. Переход в раздел Файл в Microsoft Excel

  3. Переходим в раздел «Параметры».
  4. Переход в раздел Параметры в Microsoft Excel

  5. Далее, перемещаемся в подраздел «Дополнительно».
  6. Переход во вкладку Дополнительно в Microsoft Excel

  7. В блоке настроек «Общие» в центральной части окна жмем на кнопку «Изменить списки…».
  8. Переход к изменению списков в Microsoft Excel

  9. Открывается окно списков. В левой его части расположены уже имеющиеся списки. Для того, чтобы добавить новый список записываем нужные слова в поле «Элементы списка». Каждый элемент должен начинаться с новой строки. После того, как все слова записаны, жмем на кнопку «Добавить».
  10. Переход к добавлению списка в Microsoft Excel

  11. После этого, окно списков закроется, а при повторном его открытии пользователь сможет увидеть те элементы, которые он добавлял уже в окне активных списков.
  12. Список добавлен в Microsoft Excel

  13. Теперь, после того, как вы в любую ячейку листа внесете слово, которое являлось одним из элементов добавленного списка, и примените маркер заполнения, выделенные ячейки будут заполнены символами из соответствующего списка.

Автозаполнение ячеек новым списком в Microsoft Excel

Как видим, автозаполнение в Экселе является очень полезным и удобным инструментом, который позволяет значительно сэкономить время на добавлении одинаковых данных, повторяющихся списков, и т.д. Преимуществом этого инструмента является и то, что он настраиваемый. В него можно вносить новые списки или изменять старые. Кроме того, с помощью автозаполнения можно произвести быстрое заполнение ячеек различными видами математических прогрессий.

Опишите, что у вас не получилось.
Наши специалисты постараются ответить максимально быстро.

Применение инструментов заполнения

Форма заполнения представляет собой объект с полями, наименования которых соответствуют названиям колонок столбцов заполняемой таблицы. В эти поля нужно вводить данные и они тут же будут добавляться новой строкой в табличный диапазон. Форма может выступать как в виде отдельного встроенного инструмента Excel, так и располагаться непосредственно на листе в виде его диапазона, если она создана самим пользователем.

Теперь давайте рассмотрим, как пользоваться этими двумя видами инструментов.

Способ 1: встроенный объект для ввода данных Excel

Прежде всего, давайте узнаем, как применять встроенную форму для ввода данных Excel.

  1. Нужно отметить, что по умолчанию значок, который её запускает, скрыт и его нужно активировать. Для этого переходим во вкладку «Файл», а затем щелкаем по пункту «Параметры».
  2. Переход в параметры в Microsoft Excel

  3. В открывшемся окне параметров Эксель перемещаемся в раздел «Панель быстрого доступа». Большую часть окна занимает обширная область настроек. В левой её части находятся инструменты, которые могут быть добавлены на панель быстрого доступа, а в правой – уже присутствующие.
  4. Добавление инструмента форма на панель быстрого доступа в Microsoft Excel

  5. После этого нужный нам инструмент отобразится в правой части окна. Жмем на кнопку «OK».
  6. Инструмент форма добавлен на панель быстрого доступа в Microsoft Excel

  7. Теперь данный инструмент располагается в окне Excel на панели быстрого доступа, и мы им можем воспользоваться. Он будет присутствовать при открытии любой книги данным экземпляром Excel.
  8. Инструмент форма отображается на панеле быстрого доступа в Microsoft Excel

  9. Теперь, чтобы инструмент понял, что именно ему нужно заполнять, следует оформить шапку таблицы и записать любое значение в ней. Пусть табличный массив у нас будет состоять из четырех столбцов, которые имеют названия «Наименование товара», «Количество», «Цена» и «Сумма». Вводим данные названия в произвольный горизонтальный диапазон листа.
  10. Шапка таблицы в Microsoft Excel

  11. Также, чтобы программа поняла, с каким именно диапазонам ей нужно будет работать, следует ввести любое значение в первую строку табличного массива.
  12. первое значение в таблице в Microsoft Excel

  13. После этого выделяем любую ячейку заготовки таблицы и щелкаем на панели быстрого доступа по значку «Форма…», который мы ранее активировали.
  14. Запуск формы в Microsoft Excel

  15. Итак, открывается окно указанного инструмента. Как видим, данный объект имеет поля, которые соответствуют названиям столбцов нашего табличного массива. При этом первое поле уже заполнено значением, так как мы его ввели вручную на листе.
  16. Форма открыта в Microsoft Excel

  17. Вводим значения, которые считаем нужными и в остальные поля, после чего жмем на кнопку «Добавить».
  18. Ввод значений в форму в Microsoft Excel

  19. После этого, как видим, в первую строку таблицы были автоматически перенесены введенные значения, а в форме произошел переход к следующему блоку полей, который соответствуют второй строке табличного массива.
  20. Значения перенесы в таблицу в Microsoft Excel

    Lumpics.ru

  21. Заполняем окно инструмента теми значениями, которые хотим видеть во второй строке табличной области, и снова щелкаем по кнопке «Добавить».
  22. Добавление второй строки в таблицу через форму в Microsoft Excel

  23. Как видим, значения второй строчки тоже были добавлены, причем нам даже не пришлось переставлять курсор в самой таблице.
  24. Вторая строка заполнена в таблице в Microsoft Excel

  25. Таким образом, заполняем табличный массив всеми значениями, которые хотим в неё ввести.
  26. Все значения в таблицу введены в Microsoft Excel

  27. Кроме того, при желании, можно производить навигацию по ранее введенным значениям с помощью кнопок «Назад» и «Далее» или вертикальной полосы прокрутки.
  28. Навигация по форме в Microsoft Excel

  29. При необходимости можно откорректировать любое значение в табличном массиве, изменив его в форме. Чтобы изменения отобразились на листе, после внесения их в соответствующий блок инструмента, жмем на кнопку «Добавить».
  30. Корректировка данных в форме в Microsoft Excel

  31. Как видим, изменение сразу произошло и в табличной области.
  32. Изменение произведено в таблице в Microsoft Excel

  33. Если нам нужно удалить, какую-то строчку, то через кнопки навигации или полосу прокрутки переходим к соответствующему ей блоку полей в форме. После этого щелкаем по кнопке «Удалить» в окошке инструмента.
  34. Удаление строки через форму в Microsoft Excel

  35. Открывается диалоговое окно предупреждения, в котором сообщается, что строка будет удалена. Если вы уверены в своих действиях, то жмите на кнопку «OK».
  36. Подтверждение удаления строки в Microsoft Excel

  37. Как видим, строчка была извлечена из табличного диапазона. После того, как заполнение и редактирование закончено, можно выходить из окна инструмента, нажав на кнопку «Закрыть».
  38. Закрытие формы в Microsoft Excel

  39. После этого для предания табличному массиву более наглядного визуального вида можно произвести форматирование.

Таблица отформатированв в Microsoft Excel

Способ 2: создание пользовательской формы

Кроме того, с помощью макроса и ряда других инструментов существует возможность создать собственную пользовательскую форму для заполнения табличной области. Она будет создаваться прямо на листе, и представлять собой её диапазон. С помощью данного инструмента пользователь сам сможет реализовать те возможности, которые считает нужными. По функционалу он практически ни в чем не будет уступать встроенному аналогу Excel, а кое в чем, возможно, превосходить его. Единственный недостаток состоит в том, что для каждого табличного массива придется составлять отдельную форму, а не применять один и тот же шаблон, как это возможно при использовании стандартного варианта.

  1. Как и в предыдущем способе, прежде всего, нужно составить шапку будущей таблицы на листе. Она будет состоять из пяти ячеек с именами: «№ п/п», «Наименование товара», «Количество», «Цена», «Сумма».
  2. Шапка таблицы создана в Microsoft Excel

  3. Далее нужно из нашего табличного массива сделать так называемую «умную» таблицу, с возможностью автоматического добавления строчек при заполнении соседних диапазонов или ячеек данными. Для этого выделяем шапку и, находясь во вкладке «Главная», жмем на кнопку «Форматировать как таблицу» в блоке инструментов «Стили». После этого открывается список доступных вариантов стилей. На функционал выбор одного из них никак не повлияет, поэтому выбираем просто тот вариант, который считаем более подходящим.
  4. Создание умной таблицы в Microsoft Excel

  5. Затем открывается небольшое окошко форматирования таблицы. В нем указан диапазон, который мы ранее выделили, то есть, диапазон шапки. Как правило, в данном поле заполнено все верно. Но нам следует установить галочку около параметра «Таблица с заголовками». После этого жмем на кнопку «OK».
  6. Окошко форматирования таблицы в Microsoft Excel

  7. Итак, наш диапазон отформатирован, как «умная» таблица, свидетельством чему является даже изменение визуального отображения. Как видим, помимо прочего, около каждого названия заголовка столбцов появились значки фильтрации. Их следует отключить. Для этого выделяем любую ячейку «умной» таблицы и переходим во вкладку «Данные». Там на ленте в блоке инструментов «Сортировка и фильтр» щелкаем по значку «Фильтр».
    Отключение фильтра через вкладку Данные в Microsoft Excel

    Существует ещё один вариант отключения фильтра. При этом не нужно даже будет переходить на другую вкладку, оставаясь во вкладке «Главная». После выделения ячейки табличной области на ленте в блоке настроек «Редактирование» щелкаем по значку «Сортировка и фильтр». В появившемся списке выбираем позицию «Фильтр».

  8. Отключение фильтра через вкладку Главная в Microsoft Excel

  9. Как видим, после этого действия значки фильтрации исчезли из шапки таблицы, как это и требовалось.
  10. Фильтр снят в Microsoft Excel

  11. Затем нам следует создать саму форму ввода данных. Она тоже будет представлять собой своего рода табличный массив, состоящий из двух столбцов. Наименования строк данного объекта будут соответствовать именам столбцов основной таблицы. Исключение составляют столбцы «№ п/п» и «Сумма». Они будут отсутствовать. Нумерация первого из них будет происходить при помощи макроса, а расчет значений во втором будет производиться путем применения формулы умножения количества на цену.

    Второй столбец объекта ввода данных оставим пока что пустым. Непосредственно в него позже будут вводиться значения для заполнения строк основного табличного диапазона.

  12. Форма ввода данных на листе в Microsoft Excel

  13. После этого создаем ещё одну небольшую таблицу. Она будет состоять из одного столбца и в ней разместится список товаров, которые мы будем выводить во вторую колонку основной таблицы. Для наглядности ячейку с заголовком данного перечня («Список товаров») можно залить цветом.
  14. Таблица со списком товаров в Microsoft Excel

  15. Затем выделяем первую пустую ячейку объекта ввода значений. Переходим во вкладку «Данные». Щелкаем по значку «Проверка данных», который размещен на ленте в блоке инструментов «Работа с данными».
  16. Переход к проверке данных в Microsoft Excel

  17. Запускается окно проверки вводимых данных. Кликаем по полю «Тип данных», в котором по умолчанию установлен параметр «Любое значение».
  18. Окно проверки вводимых значений в Microsoft Excel

  19. Из раскрывшихся вариантов выбираем позицию «Список».
  20. Выбор списка в окне проверки вводимых значений в Microsoft Excel

  21. Как видим, после этого окно проверки вводимых значений несколько изменило свою конфигурацию. Появилось дополнительное поле «Источник». Щелкаем по пиктограмме справа от него левой клавишей мыши.
  22. Переход к выбору источника в окне проверки вводимых значений в Microsoft Excel

  23. Затем окно проверки вводимых значений сворачивается. Выделяем курсором с зажатой левой клавишей мыши перечень данных, которые размещены на листе в дополнительной табличной области «Список товаров». После этого опять жмем на пиктограмму справа от поля, в котором появился адрес выделенного диапазона.
  24. Адрес диапазона внесен в поле в Microsoft Excel

  25. Происходит возврат к окошку проверки вводимых значений. Как видим, координаты выделенного диапазона в нем уже отображены в поле «Источник». Кликаем по кнопке «OK» внизу окна.
  26. Закрытие окна проверки вводимых значений в Microsoft Excel

  27. Теперь справа от выделенной пустой ячейки объекта ввода данных появилась пиктограмма в виде треугольника. При клике на неё открывается выпадающий список, состоящий из названий, которые подтягиваются из табличного массива «Список товаров». Произвольные данные в указанную ячейку теперь внести невозможно, а только можно выбрать из представленного списка нужную позицию. Выбираем пункт в выпадающем списке.
  28. Список со значениями в Microsoft Excel

  29. Как видим, выбранная позиция тут же отобразилась в поле «Наименование товара».
  30. Выбранная позиция отобразилась в ячейке в Microsoft Excel

  31. Далее нам нужно будет присвоить имена тем трем ячейкам формы ввода, куда мы будем вводить данные. Выделяем первую ячейку, где уже установлено в нашем случае наименование «Картофель». Далее переходим в поле наименования диапазонов. Оно расположено в левой части окна Excel на том же уровне, что и строка формул. Вводим туда произвольное название. Это может быть любое наименование на латинице, в котором нет пробелов, но лучше все-таки использовать названия близкие к решаемым данным элементом задачам. Поэтому первую ячейку, в которой содержится название товара, назовем «Name». Пишем данное наименование в поле и жмем на клавишу Enter на клавиатуре.
  32. Наименование первой ячейки в Microsoft Excel

  33. Точно таким же образом присваиваем ячейке, в которую будем вводить количество товара, имя «Volum».
  34. Наименование второй ячейки в Microsoft Excel

  35. А ячейке с ценой – «Price».
  36. Наименование третьей ячейки в Microsoft Excel

  37. После этого точно таким же образом даем название всему диапазону из вышеуказанных трех ячеек. Прежде всего, выделим, а потом дадим ему наименование в специальном поле. Пусть это будет имя «Diapason».
  38. присвоение наименования диапазону в Microsoft Excel

  39. После последнего действия обязательно сохраняем документ, чтобы названия, которые мы присвоили, смог воспринимать макрос, созданный нами в дальнейшем. Для сохранения переходим во вкладку «Файл» и кликаем по пункту «Сохранить как…».
  40. Сохранение книги в Microsoft Excel

  41. В открывшемся окне сохранения в поле «Тип файлов» выбираем значение «Книга Excel с поддержкой макросов (.xlsm)». Далее жмем на кнопку «Сохранить».
  42. Окно сохранения файла в Microsoft Excel

  43. Затем вам следует активировать работу макросов в своей версии Excel и включить вкладку «Разработчик», если вы это до сих пор не сделали. Дело в том, что обе эти функции по умолчанию в программе отключены, и их активацию нужно выполнять принудительно в окне параметров Excel.
  44. После того, как вы сделали это, переходим во вкладку «Разработчик». Кликаем по большому значку «Visual Basic», который расположен на ленте в блоке инструментов «Код».
  45. Переход в редактор макросов в Microsoft Excel

  46. Последнее действие приводит к тому, что запускается редактор макросов VBA. В области «Project», которая расположена в верхней левой части окна, выделяем имя того листа, где располагаются наши таблицы. В данном случае это «Лист 1».
  47. Редактор макросов в Microsoft Excel

  48. После этого переходим к левой нижней области окна под названием «Properties». Тут расположены настройки выделенного листа. В поле «(Name)» следует заменить кириллическое наименование («Лист1») на название, написанное на латинице. Название можно дать любое, которое вам будет удобнее, главное, чтобы в нем были исключительно символы латиницы или цифры и отсутствовали другие знаки или пробелы. Именно с этим именем будет работать макрос. Пусть в нашем случае данным названием будет «Producty», хотя вы можете выбрать и любое другое, соответствующее условиям, которые были описаны выше.

    В поле «Name» тоже можно заменить название на более удобное. Но это не обязательно. При этом допускается использование пробелов, кириллицы и любых других знаков. В отличие от предыдущего параметра, который задает наименование листа для программы, данный параметр присваивает название листу, видимое пользователю на панели ярлыков.

    Как видим, после этого автоматически изменится и наименование Листа 1 в области «Project», на то, которое мы только что задали в настройках.

  49. Изменение наименования листа в редакторе макросов в Microsoft Excel

  50. Затем переходим в центральную область окна. Именно тут нам нужно будет записать сам код макроса. Если поле редактора кода белого цвета в указанной области не отображается, как в нашем случае, то жмем на функциональную клавишу F7 и оно появится.
  51. Поле в редкторе макросов отобразилось в Microsoft Excel

  52. Теперь для конкретно нашего примера нужно записать в поле следующий код:

    Sub DataEntryForm()
    Dim nextRow As Long
    nextRow = Producty.Cells(Producty.Rows.Count, 2).End(xlUp).Offset(1, 0).Row
    With Producty
    If .Range(«A2»).Value = «» And .Range(«B2»).Value = «» Then
    nextRow = nextRow — 1
    End If
    Producty.Range(«Name»).Copy
    .Cells(nextRow, 2).PasteSpecial Paste:=xlPasteValues
    .Cells(nextRow, 3).Value = Producty.Range(«Volum»).Value
    .Cells(nextRow, 4).Value = Producty.Range(«Price»).Value
    .Cells(nextRow, 5).Value = Producty.Range(«Volum»).Value * Producty.Range(«Price»).Value
    .Range(«A2»).Formula = «=IF(ISBLANK(B2), «»»», COUNTA($B$2:B2))»
    If nextRow > 2 Then
    Range(«A2»).Select
    Selection.AutoFill Destination:=Range(«A2:A» & nextRow)
    Range(«A2:A» & nextRow).Select
    End If
    .Range(«Diapason»).ClearContents
    End With
    End Sub

    Но этот код не универсальный, то есть, он в неизменном виде подходит только для нашего случая. Если вы хотите его приспособить под свои потребности, то его следует соответственно модифицировать. Чтобы вы смогли сделать это самостоятельно, давайте разберем, из чего данный код состоит, что в нем следует заменить, а что менять не нужно.

    Итак, первая строка:

    «DataEntryForm» — это название самого макроса. Вы можете оставить его как есть, а можете заменить на любое другое, которое соответствует общим правилам создания наименований макросов (отсутствие пробелов, использование только букв латинского алфавита и т.д.). Изменение наименования ни на что не повлияет.

    Везде, где встречается в коде слово «Producty» вы должны его заменить на то наименование, которое ранее присвоили для своего листа в поле «(Name)» области «Properties» редактора макросов. Естественно, это нужно делать только в том случае, если вы назвали лист по-другому.

    Наименование для листа в Microsoft Excel

    Теперь рассмотрим такую строку:

    nextRow = Producty.Cells(Producty.Rows.Count, 2).End(xlUp).Offset(1, 0).Row

    Цифра «2» в данной строчке означает второй столбец листа. Именно в этом столбце находится колонка «Наименование товара». По ней мы будем считать количество рядов. Поэтому, если в вашем случае аналогичный столбец имеет другой порядок по счету, то нужно ввести соответствующее число. Значение «End(xlUp).Offset(1, 0).Row» в любом случае оставляем без изменений.

    Второй столбец таблицы в Microsoft Excel

    Далее рассмотрим строку

    If .Range(«A2»).Value = «» And .Range(«B2»).Value = «» Then

    «A2» — это координаты первой ячейки, в которой будет выводиться нумерация строк. «B2» — это координаты первой ячейки, по которой будет производиться вывод данных («Наименование товара»). Если они у вас отличаются, то введите вместо этих координат свои данные.

    Две первые ячейки таблицы с данными в Microsoft Excel

    Переходим к строке

    В ней параметр «Name» означат имя, которое мы присвоили полю «Наименование товара» в форме ввода.

    Имя поля наименования товара в форме ввода в Microsoft Excel

    .Cells(nextRow, 2).PasteSpecial Paste:=xlPasteValues
    .Cells(nextRow, 3).Value = Producty.Range(«Volum»).Value
    .Cells(nextRow, 4).Value = Producty.Range(«Price»).Value
    .Cells(nextRow, 5).Value = Producty.Range(«Volum»).Value * Producty.Range(«Price»).Value

    наименования «Volum» и «Price» означают названия, которые мы присвоили полям «Количество» и «Цена» в той же форме ввода.

    Наименование полей количество и цена в Microsoft Excel

    В этих же строках, которые мы указали выше, цифры «2», «3», «4», «5» означают номера столбцов на листе Excel, соответствующих колонкам «Наименование товара», «Количество», «Цена» и «Сумма». Поэтому, если в вашем случае таблица сдвинута, то нужно указать соответствующие номера столбцов. Если столбцов больше, то по аналогии нужно добавить её строки в код, если меньше – то убрать лишние.

    Колонки в таблице в Microsoft Excel

    В строке производится умножение количества товара на его цену:

    .Cells(nextRow, 5).Value = Producty.Range(«Volum»).Value * Producty.Range(«Price»).Value

    Результат, как видим из синтаксиса записи, будет выводиться в пятый столбец листа Excel.

    Колонка для вывода суммы в Microsoft Excel

    В этом выражении выполняется автоматическая нумерация строк:

    If nextRow > 2 Then
    Range(«A2»).Select
    Selection.AutoFill Destination:=Range(«A2:A» & nextRow)
    Range(«A2:A» & nextRow).Select
    End If

    Все значения «A2» означают адрес первой ячейки, где будет производиться нумерация, а координаты «A» — адрес всего столбца с нумерацией. Проверьте, где именно будет выводиться нумерация в вашей таблице и измените данные координаты в коде, если это необходимо.

    Столбец с нумерацией в Microsoft Excel

    В строке производится очистка диапазона формы ввода данных после того, как информация из неё была перенесена в таблицу:

    Не трудно догадаться, что («Diapason») означает наименование того диапазона, который мы ранее присвоили полям для ввода данных. Если вы дали им другое наименование, то в этой строке должно быть вставлено именно оно.

    Наименование полей для ввода данных в Microsoft Excel

    Дальнейшая часть кода универсальна и во всех случаях будет вноситься без изменений.

    После того, как вы записали код макроса в окно редактора, следует нажать на значок сохранения в виде дискеты в левой части окна. Затем можно его закрывать, щелкнув по стандартной кнопке закрытия окон в правом верхнем углу.

  53. Код в редакторе макросов в Microsoft Excel

  54. После этого возвращаемся на лист Excel. Теперь нам следует разместить кнопку, которая будет активировать созданный макрос. Для этого переходим во вкладку «Разработчик». В блоке настроек «Элементы управления» на ленте кликаем по кнопке «Вставить». Открывается перечень инструментов. В группе инструментов «Элементы управления формы» выбираем самый первый – «Кнопка».
  55. Выбор элемента управления в Microsoft Excel

  56. Затем с зажатой левой клавишей мыши обводим курсором область, где хотим разместить кнопку запуска макроса, который будет производить перенос данных из формы в таблицу.
  57. Указание границ кнопки в Microsoft Excel

  58. После того, как область обведена, отпускаем клавишу мыши. Затем автоматически запускается окно назначения макроса объекту. Если в вашей книге применяется несколько макросов, то выбираем из списка название того, который мы выше создавали. У нас он называется «DataEntryForm». Но в данном случае макрос один, поэтому просто выбираем его и жмем на кнопку «OK» внизу окна.
  59. Окно назначения макроса объекту в Microsoft Excel

  60. После этого можно переименовать кнопку, как вы захотите, просто выделив её текущее название.
    Переименовывание кнопки в Microsoft Excel

    В нашем случае, например, логично будет дать ей имя «Добавить». Переименовываем и кликаем мышкой по любой свободной ячейке листа.

  61. Кнопка переименована в Microsoft Excel

  62. Итак, наша форма полностью готова. Проверим, как она работает. Вводим в её поля необходимые значения и жмем на кнопку «Добавить».
  63. Ввод данных в форму в Microsoft Excel

  64. Как видим, значения перемещены в таблицу, строке автоматически присвоен номер, сумма посчитана, поля формы очищены.
  65. Значения первой строки внесены в таблицу в Microsoft Excel

  66. Повторно заполняем форму и жмем на кнопку «Добавить».
  67. Повторный ввод данных в форму в Microsoft Excel

  68. Как видим, и вторая строка также добавлена в табличный массив. Это означает, что инструмент работает.

Вторая строка добавлена в таблицу в Microsoft Excel

Читайте также:
Как создать макрос в Excel
Как создать кнопку в Excel

В Экселе существует два способа применения формы заполнения данными: встроенная и пользовательская. Применение встроенного варианта требует минимум усилий от пользователя. Его всегда можно запустить, добавив соответствующий значок на панель быстрого доступа. Пользовательскую форму нужно создавать самому, но если вы хорошо разбираетесь в коде VBA, то сможете сделать этот инструмент максимально гибким и подходящим под ваши нужды.

Опишите, что у вас не получилось.
Наши специалисты постараются ответить максимально быстро.