Формы ввода данных в Microsoft Excel

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

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

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

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

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

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

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

   В поле «Выбрать команды из» установите значение «Команды не на ленте». Кроме того, из списка команд, расположенных в алфавитном порядке, находим и выбираем позицию «Модуль…». Затем нажмите кнопку «Добавить».

3. Далее нужный нам инструмент появится в правой части окна. Щелкните кнопку «ОК».
4. Теперь этот инструмент находится в окне Excel на панели быстрого доступа, и мы можем его использовать. Он будет присутствовать при открытии книги из этого экземпляра Excel.
5. Теперь, чтобы инструмент понимал, что именно ему нужно заполнить, нужно выложить заголовок таблицы и записать в него любые значения. Пусть наша матрица таблицы состоит из четырех столбцов, которые имеют названия «Название продукта», «Количество», «Цена» и «Количество». Введите эти имена в произвольный горизонтальный диапазон листа.
6. Также, чтобы программа понимала, с какими диапазонами она будет работать, необходимо ввести любое значение в первую строку массива таблицы.
7. Затем выберите любую ячейку шаблона таблицы и щелкните значок «Форма…» на панели быстрого доступа, которую мы активировали ранее.
8. Затем откроется окно указанного инструмента. Как видите, у этого объекта есть поля, соответствующие названиям столбцов нашего массива таблиц. В этом случае первое поле уже заполнено значением, так как мы вручную ввели его в лист.
9. Введите значения, которые мы сочтем необходимыми, в оставшиеся поля, затем нажмите кнопку «Добавить».
10. В дальнейшем, как видим, введенные значения автоматически переносились в первую строку таблицы и в форме происходил переход к следующему блоку полей, который соответствует второй строке массива таблицы.
11. Заполните панель инструментов значениями, которые мы хотим видеть во второй строке области таблицы, и снова нажмите кнопку «Добавить».
12. Как видите, значения второй строки также были добавлены, и нам даже не пришлось переставлять курсор в самой таблице.
13. Поэтому мы заполняем массив таблицы всеми значениями, которые хотим в него поместить.
14. Также, при желании, вы можете перемещаться между ранее введенными значениями, используя кнопки «Назад» и «Далее» или вертикальную полосу прокрутки.
15. При необходимости вы можете исправить любое значение в массиве таблиц, изменив его в форме. Чтобы изменения отображались на листе, после внесения их в соответствующий блок инструмента нажмите кнопку «Добавить».
16. Как видите, изменение произошло сразу в области таблицы.
17. Если нам нужно удалить строку, с помощью кнопок навигации или полосы прокрутки перейдите к соответствующему блоку полей в форме. Затем нажмите кнопку «Удалить» на панели инструментов.
18. Появится диалоговое окно с предупреждением о том, что строка будет удалена. Если вы уверены в своих действиях, нажмите кнопку «ОК».
19. Как видите, строка была извлечена из диапазона таблицы. После завершения заполнения и редактирования вы можете выйти из панели инструментов, нажав кнопку «Закрыть».
20. Позже вы можете выполнить форматирование, чтобы сделать массив таблиц более наглядным.

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

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

1. Как и в предыдущем способе, в первую очередь нужно создать на листе будущий заголовок таблицы. Он будет состоять из пяти ячеек с названиями: «Номер позиции», «Название продукта», «Количество», «Цена», «Количество».
2. Затем нам нужно создать так называемую «умную» таблицу из нашего массива таблиц с возможностью автоматического добавления строк при заполнении соседних диапазонов или ячеек данными. Для этого выберите заголовок и, находясь на вкладке «Главная», нажмите кнопку «Форматировать как таблицу» на панели инструментов «Стили». Далее открывается список доступных стилей. Выбор любого из них никак не повлияет на функциональность, поэтому давайте просто выберем вариант, который мы считаем наиболее подходящим.
3. Затем откроется небольшое окно для форматирования таблицы. Указывает диапазон, который мы выделили ранее, то есть диапазон заголовка. Как правило, в этом поле все заполняется правильно. Но мы должны поставить галочку рядом с опцией «Таблица с заголовками». Затем нажмите кнопку «ОК».
4. Следовательно, наш диапазон отформатирован как интеллектуальная таблица, о чем также свидетельствует изменение отображения. Как вы можете видеть, помимо прочего, рядом с названием каждого заголовка столбца появились значки фильтров. Они должны быть отключены. Для этого выберите любую ячейку в умной таблице и перейдите на вкладку «Данные», где на ленте в панели инструментов «Сортировка и фильтр» щелкните значок «Фильтр».

   

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

5. Как видите, после этого действия значки фильтров исчезли из заголовка таблицы, как и требовалось.
6. Итак, нам нужно создать саму форму ввода данных. Это тоже будет своего рода табличный массив, состоящий из двух столбцов. Имена строк этого объекта будут соответствовать именам столбцов основной таблицы. Исключение составляют столбцы «N p / p» и «Amount». Их не будет. Первый будет пронумерован с помощью макроса, а значения во втором будут рассчитаны путем применения формулы для умножения количества на цену.

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

7. Далее создадим еще один журнальный столик. Он будет состоять из одного столбца и содержать список продуктов, который мы будем отображать во втором столбце основной таблицы. Для наглядности ячейку с названием этого списка («Список товаров») можно заполнить цветом.
8. Затем мы выбираем первую пустую ячейку объекта ввода значения. Переходим во вкладку «Данные». Щелкните значок «Проверка данных», расположенный на ленте панели инструментов «Работа с данными».
9. Открывается окно проверки входных данных. Щелкните по полю «Тип данных», где по умолчанию установлен параметр «Любое значение».
10. Из открывшихся опций выберите пункт «Список».
11. Как вы увидите позже, окно проверки введенных значений немного изменило свою конфигурацию. Появилось дополнительное поле «Источник». Щелкните значок справа от него левой кнопкой мыши.
12. Затем окно проверки введенных значений сворачивается. С помощью курсора, удерживая левую кнопку мыши, выберите список данных, расположенный на листе в дополнительной табличной области «Список товаров». После этого снова нажмите на иконку справа от поля, где появился адрес выбранного диапазона.
13. Возврат к окну проверки введенных значений. Как видите, координаты выбранного диапазона в нем уже отображаются в поле «Источник». Нажмите кнопку «ОК» внизу окна.
14. Теперь значок треугольника появляется справа от выбранной пустой ячейки объекта ввода данных. При нажатии на нее открывается раскрывающийся список, состоящий из имен, извлеченных из массива таблицы «Список товаров». Теперь невозможно вставить произвольные данные в указанную ячейку, а только вы можете выбрать нужную позицию из представленного списка. Выбираем пункт в выпадающем списке.
15. Как видите, выбранная статья сразу отобразилась в поле «Название продукта».
16. Далее нам нужно будет назвать эти три ячейки формы ввода, в которые мы будем вводить данные. Выбираем первую ячейку, в которой в нашем случае уже задано название «Картошка». Затем перейдите в поле имени диапазона. Он расположен в левой части окна Excel на том же уровне, что и строка формул. Введите здесь произвольное имя. Это может быть любое название латинского алфавита, в котором нет пробелов, но все же лучше использовать имена, близкие к задачам, решаемым этим элементом. Поэтому первую ячейку, содержащую название продукта, мы будем называть «Имя». Пишем это имя в поле и нажимаем клавишу Enter на клавиатуре.
17. Точно так же присваиваем имя «Volum» ячейке, в которую будем вводить количество товара».
18. А ячейка с ценой — «Цена».
19. Далее точно так же назовем весь диапазон предыдущих трех ячеек. Сначала мы выбираем, а затем даем имя в определенном поле. Пусть будет название «Диапазон».
20. После последнего шага обязательно сохраните документ, чтобы присвоенные нами имена могли быть восприняты макросом, который мы создали в будущем. Для сохранения перейдите на вкладку «Файл» и нажмите «Сохранить как…».
21. В открывшемся окне сохранения в поле «Тип файлов» выберите значение «Книга Excel с поддержкой макросов (.xlsm)». Далее нажмите кнопку «Сохранить».
22. Поэтому вам следует включить макросы в своей версии Excel и включить вкладку «Разработчик», если вы еще этого не сделали. Дело в том, что обе эти функции по умолчанию в программе отключены, и их активацию нужно производить принудительно в окне настроек Excel.
23. Как только вы это сделаете, перейдите на вкладку «Разработчик». Щелкните большой значок «Visual Basic», расположенный на ленте в панели инструментов «Код».
24. Последний шаг запускает редактор макросов VBA. В области «Проект», расположенной в верхней левой части окна, выберите название листа, на котором расположены наши таблицы. В данном случае это «Лист 1».
25. Затем перейдите в нижнюю левую область окна под названием «Свойства». Здесь можно найти настройки для выбранного листа. В поле «(Имя)» замените кириллическое имя («Sheet1») на имя, написанное латиницей. Вы можете дать любое имя, которое вам удобнее, главное, чтобы оно состояло только из латинских букв или цифр и никаких других знаков и пробелов не было. Именно под этим именем макрос будет работать. Пусть в нашем случае это имя будет «Продукт», хотя вы можете выбрать другое, отвечающее условиям, описанным выше.

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

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

26. Итак, перейдем к центральной части окна. Здесь нам нужно написать сам код макроса. Если поле редактора белого кода в указанной области не появляется, как в нашем случае, нажмите функциональную клавишу F7, и оно появится.
27. Теперь для нашего конкретного примера вам нужно написать следующий код в поле:

    Sub DataEntryForm()
    
    Затемнить следующий ряд самый длинный
    
    nextRow = Producty.Cells (Producty.Rows.Count, 2) .End (xlUp) .Offset (1, 0) .Row
    
    С Producty
    
    Если .Range («A2»). Value = «» And .Range («B2»). Value = «» Тогда
    
    следующая строка = следующая строка — 1
    
    Конец, если
    
    Range.Product («Название»). Копировать
    
    .Cells (nextRow, 2) .PasteSpecial Вставить: = xlPasteValues
    
    .Cells (nextRow, 3) .Value = Producty.Range («Объем»). Ценить
    
    .Cells (nextRow, 4) .Value = Producty.Range («Цена»). Ценить
    
    .Cells (nextRow, 5) .Value = Producty.Range («Volum») Значение * Producty.Range («Цена»). Ценить
    
    .Interval («A2»). Формула = «= ЕСЛИ (ISBLANK (B2),» «» «, COUNT ($ B $ 2: B2))»
    
    Если nextRow> 2, то
    
    Диапазон («A2»). Выбирать
    
    Selection.AutoFill Destination: = Range («A2: A» и следующая строка)
    
    Диапазон («A2: A» и следующая строка). Выберите
    
    Конец, если
    
    .Range («Диапазон»). ClearContents
    
    Конец с
    
    Конец подзаголовка

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

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

    Sub DataEntryForm()

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

    Где бы в коде ни появлялось слово «Продукт», вы должны заменить его именем, которое вы ранее присвоили листу в поле «(Имя)» в области «Свойства» редактора макросов. Конечно, это нужно делать только в том случае, если вы назвали лист по-другому.

    

    Давайте теперь рассмотрим такую ​​строку:

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

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

    

    Итак, рассмотрим строку

    Если .Range («A2»). Value = «» And .Range («B2»). Value = «» Тогда

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

    

    Перейти к линии

    Range.Product («Название»). Копировать

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

    

    Онлайн

    .Cells (nextRow, 2) .PasteSpecial Вставить: = xlPasteValues
    
    .Cells (nextRow, 3) .Value = Producty.Range («Объем»). Ценить
    
    .Cells (nextRow, 4) .Value = Producty.Range («Цена»). Ценить
    
    .Cells (nextRow, 5) .Value = Producty.Range («Volum») Значение * Producty.Range («Цена»). Ценить

    имена «Объем» и «Цена» указывают на имена, которые мы присвоили полям «Количество» и «Цена» в той же регистрационной форме.

    

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

    

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

    .Cells (nextRow, 5) .Value = Producty.Range («Volum») Значение * Producty.Range («Цена»). Ценить

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

    

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

    Если nextRow> 2, то
    
    Диапазон («A2»). Выбирать
    
    Selection.AutoFill Destination: = Range («A2: A» и следующая строка)
    
    Диапазон («A2: A» и следующая строка). Выберите
    
    Конец, если

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

    

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

    .Range («Диапазон»). ClearContents

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

    

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

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

28. Затем вернемся к листу Excel. Теперь нам нужно разместить кнопку, которая активирует созданный макрос. Для этого перейдите во вкладку «Разработчик». В блоке настроек «Элементы управления» на ленте нажмите кнопку «Вставить». Откроется список инструментов. В группе инструментов «Элементы управления формой» выберите самую первую — «Кнопку».
29. Затем, удерживая левую кнопку мыши, мы обводим курсором область, в которой мы хотим разместить кнопку для запуска макроса, который будет передавать данные из формы в таблицу.
30. После того, как область будет обведена, отпустите кнопку мыши. Затем автоматически запускается окно для назначения макроса объекту. Если в вашей книге используется несколько макросов, выберите из списка имя созданного нами выше. Мы называем это DataEntryForm. Но в данном случае макрос только один, поэтому выберите его и нажмите кнопку «ОК» внизу окна.
31. Позже вы можете переименовать кнопку по своему усмотрению, просто выделив ее текущее имя.

    

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

32. Итак, наша форма полностью готова. Давайте проверим, как это работает. Введите необходимые значения в его поля и нажмите кнопку «Добавить».
33. Как видите, значения перенесены в таблицу, строке автоматически присваивается номер, рассчитывается сумма, очищаются поля формы.
34. Заполните форму еще раз и нажмите кнопку «Добавить».
35. Как видите, вторая строка также была добавлена ​​в массив таблицы. Это означает, что инструмент работает.

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