Форми вводу даних в Microsoft Excel

Для полегшення введення даних в таблицю в Excel можна скористатися спеціальними формами, які допоможуть прискорити процес заповнення табличного діапазону інформацією. У Ексель є вбудований інструмент дозволяє виробляти заповнення подібним методом. Також користувач може створити власний варіант форми, яка буде максимально адаптована під його потреби, застосувавши для цього макрос. Давайте розглянемо різні варіанти використання цих корисних інструментів заповнення в Excel.

Застосування інструментів заповнення

Форма заповнення є об'єктом з полями, назви яких співпадають з назвами колонок стовпців заповнюється таблиці. У ці поля потрібно вводити дані і вони тут же будуть додаватися новим рядком в табличний діапазон. Форма може виступати як у вигляді окремого вбудованого інструменту Excel, так і розташовуватися безпосередньо на аркуші у вигляді його діапазону, якщо вона створена самим користувачем.

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

Спосіб 1: вбудований об'єкт для введення даних Excel

Перш за все, давайте дізнаємося, як застосовувати вбудовану форму для введення даних Excel.

1. Потрібно відзначити, що за замовчуванням значок, який її запускає, прихований і його потрібно активувати. Для цього переходимо у вкладку «Файл», а потім клацаємо за пунктом «Параметри».
2. У вікні параметрів Ексель переміщаємося в розділ «Панель швидкого доступу». Велику частину вікна займає велика область налаштувань. У лівій її частині знаходяться інструменти, які можуть бути додані на панель швидкого доступу, а в правій - вже присутні.

   В поле «Вибрати команди з" встановлюємо значення «Команди не на стрічці». Далі зі списку команд, розташованого в алфавітному порядку, знаходимо і виділяємо позицію «Форма ...». Потім тиснемо на кнопку «Додати».

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

Спосіб 2: створення користувальницької форми

Крім того, за допомогою макросу і ряду інших інструментів існує можливість створити власну призначену для користувача форму для заповнення табличній області. Вона буде створюватися прямо на аркуші, і представляти собою її діапазон. За допомогою даного інструменту користувач сам зможе реалізувати ті можливості, які вважає потрібними. За функціоналом він практично ні в чому не буде поступатися вбудованому аналогу Excel, а де в чому, можливо, перевершувати його. Єдиний недолік полягає в тому, що для кожного табличного масиву доведеться складати окрему форму, а не застосовувати один і той же шаблон, як це можливо при використанні стандартного варіанту.

1. Як і в попередньому способі, перш за все, потрібно скласти шапку майбутньої таблиці на аркуші. Вона буде складатися з п'яти осередків з іменами: «№ п / п», «Найменування товару», «Кількість», «Ціна», «Сума».
2. Далі потрібно з нашого табличного масиву зробити так звану «розумну» таблицю, з можливістю автоматичного додавання рядків при заповненні сусідніх діапазонів або осередків даними. Для цього виділяємо шапку і, перебуваючи у вкладці «Головна», тиснемо на кнопку «Форматувати як таблицю» в блоці інструментів «Стилі». Після цього відкривається список доступних варіантів стилів. На функціонал вибір одного з них ніяк не вплине, тому вибираємо просто той варіант, який вважаємо більш підходящим.
3. Потім відкривається невелике віконце форматування таблиці. У ньому вказано діапазон, який ми раніше виділили, тобто, діапазон шапки. Як правило, в даному полі заповнене все вірно. Але нам слід встановити галочку біля параметра «Таблиця із заголовками». Після цього тиснемо на кнопку «OK».
4. Отже, наш діапазон відформатований, як «розумна» таблиця, свідченням чого є навіть зміна візуального відображення. Як бачимо, крім іншого, у будь-якого назви заголовка стовпців з'явилися значки фільтрації. Їх слід відключити. Для цього виділяємо будь-який осередок «розумної» таблиці і переходимо у вкладку «Дані». Там на стрічці в блоці інструментів «Сортування і фільтр» клацаємо по значку «Фільтр».

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

5. Як бачимо, після цього дії значки фільтрації зникли з шапки таблиці, як це і було потрібно.
6. Потім нам слід створити саму форму введення даних. Вона теж буде представляти собою свого роду табличний масив, що складається з двох стовпців. Найменування рядків даного об'єкта будуть відповідати іменам стовпців основної таблиці. Виняток становлять стовпці «№ п / п» і «Сума». Вони будуть відсутні. Нумерація першого з них буде відбуватися за допомогою макросу, а розрахунок значень у другому буде проводитися шляхом застосування формули множення кількості на ціну.

   Другий стовпець об'єкта введення даних залишимо поки що порожнім. Безпосередньо в нього пізніше будуть вводитися значення для заповнення рядків основного табличного діапазону.

7. Після цього створюємо ще одну невелику таблицю. Вона буде складатися з одного стовпчика і в ній розміститься список товарів, які ми будемо виводити в другу колонку основної таблиці. Для наочності осередок з заголовком даного переліку ( «Список товарів») можна залити кольором.
8. Потім виділяємо першу вільну позицію об'єкта введення значень. Переходимо у вкладку «Дані». Клацаємо по значку «Перевірка даних», який розміщений на стрічці в блоці інструментів «Робота з даними».
9. Запускається вікно перевірки введених даних. Кількома по полю «Тип даних», в якому за замовчуванням встановлений параметр «Будь-яке значення».
10. З розкрилися варіантів вибираємо позицію «Список».
11. Як бачимо, після цього вікно перевірки введених значень дещо змінило свою конфігурацію. З'явилося додаткове поле «Джерело». Клацаємо по піктограмі праворуч від нього лівою клавішею миші.
12. Потім вікно перевірки введених значень згортається. Виділяємо курсором з затиснутою лівою клавішею миші перелік даних, які розміщені на аркуші в додаткової табличній області «Список товарів». Після цього знову тиснемо на піктограму праворуч від поля, в якому з'явився адреса виділеного діапазону.
13. Відбувається повернення до віконця перевірки введених значень. Як бачимо, координати виділеного діапазону в ньому вже відображені в поле «Джерело». Кількома по кнопці «OK» внизу вікна.
14. Тепер справа від виділеної порожній осередку об'єкта введення даних з'явилася піктограма у вигляді трикутника. При кліці на неї відкривається список, що випадає, що складається з назв, які підтягуються з табличного масиву «Список товарів». Довільні дані в зазначену осередок тепер внести неможливо, а тільки можна вибрати з представленого списку потрібну позицію. Вибираємо пункт у випадаючому списку.
15. Як бачимо, обрана позиція тут же відобразилася в поле «Найменування товару».
16. Далі нам потрібно буде привласнити імена тих трьох осередків форми введення, куди ми будемо вводити дані. Виділяємо першу осередок, де вже встановлено в нашому випадку найменування «Картопля». Далі переходимо в поле найменування діапазонів. Воно розташоване в лівій частині вікна Excel на тому ж рівні, що і рядок формул. Вводимо туди довільну назву. Це може бути будь-яке найменування на латиниці, в якому немає прогалин, але краще все-таки використовувати назви близькі до вирішуваних даними елементом завданням. Тому перший осередок, в якій міститься назва товару, назвемо «Name». Пишемо дане найменування в поле і тиснемо на клавішу Enter на клавіатурі.
17. Точно таким же чином присвоюємо осередку, в яку будемо вводити кількість товару, ім'я «Volum».
18. А осередку з ціною - «Price».
19. Після цього точно таким же чином даємо назву всьому діапазону з вищевказаних трьох осередків. Перш за все, виділимо, а потім дамо йому найменування в спеціальному полі. Нехай це буде ім'я «Diapason».
20. Після останньої дії обов'язково зберігаємо документ, щоб назви, які ми присвоїли, зміг сприймати макрос, створений нами в подальшому. Для збереження переходимо у вкладку «Файл» і натискаємо по пункту «Зберегти як ...».
21. У вікні збереження в поле «Тип файлів» вибираємо значення «Книга Excel з підтримкою макросів (.xlsm)». Далі тиснемо на кнопку «Зберегти».
22. Потім вам слід активувати роботу макросів в своїй версії Excel і включити вкладку «Розробник», якщо ви це до сих пір не зробили. Справа в тому, що обидві ці функції за замовчуванням в програмі відключені, і їх активацію потрібно виконувати примусово у вікні параметрів Excel.
23. Після того, як ви зробили це, переходимо у вкладку «Розробник». Кількома по великому значку «Visual Basic», який розташований на стрічці в блоці інструментів «Код».
24. Остання дія призводить до того, що запускається редактор макросів VBA. В області «Project», яка розташована у верхній лівій частині вікна, виділяємо ім'я того листа, де розташовуються наші таблиці. В даному випадку це «Лист 1».
25. Після цього переходимо до лівої нижньої області вікна під назвою «Properties». Тут розташовані настройки виділеного аркуша. В поле «(Name)» слід замінити кириличне найменування ( «Лист1») на назву, написане на латиниці. Назву можна дати будь-яке, яке вам буде зручніше, головне, щоб у ньому були виключно символи латиниці або цифри і були відсутні інші знаки або прогалини. Саме з цим ім'ям буде працювати макрос. Нехай в нашому випадку даними назвою буде «Producty», хоча ви можете вибрати будь-який інший, що відповідає умовам, які були описані вище.

    В поле «Name» теж можна замінити назву на більш зручне. Але це не обов'язково. При цьому допускається використання прогалин, кирилиці і будь-яких інших знаків. На відміну від попереднього параметра, який задає найменування листа для програми, даний параметр присвоює назву листу, видиме користувачеві на панелі ярликів.

    Як бачимо, після цього автоматично зміниться і найменування Ліста 1 в області «Project», на те, що ми тільки що поставили в налаштуваннях.

26. Потім переходимо в центральну область вікна. Саме тут нам потрібно буде записати сам код макросу. Якщо поле редактора коду білого кольору в зазначеній галузі не відображається, як в нашому випадку, то тиснемо на функціональну клавішу F7 і воно з'явиться.
27. Тепер для конкретно нашого прикладу потрібно записати в поле наступний код:

    
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


    Але цей код не універсальний, тобто, він в незмінному вигляді підходить тільки для нашого випадку. Якщо ви хочете його пристосувати під свої потреби, то його слід відповідно модифікувати. Щоб ви змогли зробити це самостійно, давайте розберемо, з чого цей код складається, що в ньому слід замінити, а що міняти не потрібно.

    Отже, перший рядок:

    Sub DataEntryForm()

    «DataEntryForm» - це назва самого макросу. Ви можете залишити його як є, а можете замінити на будь-яке інше, яке відповідає загальним правилам створення найменувань макросів (відсутність пробілів, використання тільки букв латинського алфавіту і т.д.). Зміна найменування ні на що не вплине.

    Скрізь, де зустрічається в коді слово «Producty» ви повинні його замінити на те найменування, яке раніше привласнили для свого листа в поле «(Name)» області «Properties» редактора макросів. Природно, це потрібно робити тільки в тому випадку, якщо ви назвали лист по-іншому.

    Тепер розглянемо такий рядок:

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

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

    Далі розглянемо рядок

    If .Range("A2").Value = "" And .Range("B2").Value = "" Then

    «A2» - це координати першого осередку, в якій буде виводитися нумерація рядків. «B2» - це координати першого осередку, через яку здійснюватиметься висновок даних ( «Найменування товару»). Якщо вони у вас відрізняються, то введіть замість цих координат свої дані.

    Переходимо до рядка

    Producty.Range("Name").Copy

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

    В строках

    
.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» означают названия, которые мы присвоили полям «Количество» и «Цена» в той же форме ввода.

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

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

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

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

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

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


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

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

    .Range("Diapason").ClearContents

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

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

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

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

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

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

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

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

• ← Складання кошторису в Microsoft Excel
• Циклічні посилання в Microsoft Excel →