форми введення даних у Microsoft Excel

Зміст

• Застосування інструментів заповнення
  • Спосіб 1: вбудований об'єкт для введення даних Excel
  • Спосіб 2: Створення Користувацької форми
• Питання та відповіді

Для полегшення введення даних в таблицю в 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. Відбувається повернення до віконця перевірки вводяться значень. Як бачимо, координати виділеного діапазону в ньому вже відображені в полі «джерело» . Кількома по кнопці «ОК» внизу вікна.

14. Тепер праворуч від виділеної порожньої комірки об'єкта Введення даних з'явилася піктограма у вигляді трикутника. При кліці на неї відкривається список, що випадає, що складається з назв, які підтягуються з табличного масиву " Список товарів» . Довільні дані в зазначену комірку тепер внести неможливо, а тільки можна вибрати з представленого списку потрібну позицію. Вибираємо пункт в випадаючому списку.

15. Як бачимо, обрана позиція тут же відобразилася в полі " Назва товару» .

16. Далі нам потрібно буде присвоїти імена тим трьом клітинкам форми введення, куди ми будемо вводити дані. Виділяємо першу клітинку, де вже встановлено в нашому випадку найменування " картопля» . Далі переходимо в поле найменування діапазонів. Воно поширене в лівій частині вікна Excel на тому ж рівні, що і stroka фormul. Вводимо туди довільну назву. Це може бути будь-яке найменування на латиниці, в якому немає пробілів, але краще все-таки використовувати назви близькі до розв'язуваних даними елементом завданням. Тому першу клітинку, в якій міститься Назва товару, назвемо «Ім'я» . Пишемо дане найменування в поле і тиснемо на клавішу введіть на клавіатурі.

17. Точно таким же чином присвоюємо осередку, в яку будемо вводити кількість товару, ім'я " обсяг» .

18. А осередку з ціною –»Ціна" .

19. Після цього точно таким же чином даємо назву всьому діапазону з вищевказаних трьох осередків. Перш за все, виділимо, а потім дамо йому найменування в спеціальному полі. Нехай це буде ім'я " діапазон» .

20. Після останньої дії обов'язково зберігаємо документ, щоб назви, які ми присвоїли, зміг сприймати макрос, створений нами надалі. Для збереження переходимо у вкладку " Файл» і натискаємо по пункту " Зберегти як ... » .

21. У вікні Збереження, що відкриється, у полі " тип файлів» вибираємо значення «книга Excel з добавкою макросів (.xlsm)» . Далі тиснемо на кнопку " Зберегти» .

22. Тоді Вам слід активувати роботу макросів у своїй версії Excel і включити вкладку »Розробник" , якщо ви цього ще не зробили. Справа в тому, що обидві ці функції за замовчуванням відключені в програмі, і їх потрібно виконувати активацію примусово у вікні параметрів програми Excel.
23. Після того, як ви зробили це, переходимо у вкладку»Розробник" . Кількома по великому значку «Visual Basic» , який розташований на стрічці в блоці інструментів «Код» .

24. Остання дія призводить до того, що запускається редак макросів VBA. В області «Проект» , яка розташована у верхній лівій частині вікна, виділяємо ім'я того листа, де розташовуються наші таблиці. В даному випадку це «Лист 1» .

25. Після цього переходимо до лівої нижньої області вікна під назвою «властивості» . Тут розташовані настройки виділеного листа. У полі " (Ім'я)» слід замінити кириличне найменування ( «Лист1» ) на назву, написане на латиниці. Назва можна дати будь-яке, яке вам буде зручніше, головне, щоб в ньому були виключно символи латиниці або цифри і були відсутні інші знаки або прогалини. Саме з цим ім'ям буде працювати макрос. Нехай в нашому випадку даними назвою буде " Продукт» , хоча ви можете вибрати і будь-яке інше, що відповідає умовам, які були описані вище.

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

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


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

27. Тепер для конкретно нашого прикладу потрібно записати в поле наступний код:

    
Допоміжна форма DataEntryForm()
Тьмяний наступний ряд до тих пір, поки
NextRow = Продукт.Осередки (Producty.Rows.Count, 2).Кінець (xlUp).Зміщення (1, 0).Рядок
З продуктом
If .Range("A2").Value = "" І .Діапазон ("B2").Значення = "" Тоді
NextRow = наступний рядок-1
Закінчиться, якщо
Продуктивний.Діапазон ("Ім'я").Копіювати
.Клітинки (NextRow, 2).Вставити спеціальну вставку:=xlPasteValues
.Клітинки (наступний рядок, 3).Значення = Продукт.Діапазон ("Обсяг").Цінність
.Клітинки (наступний рядок, 4).Значення = Продукт.Діапазон ("Ціна").Цінність
.Клітинки (наступний рядок, 5).Значення = Продукт.Діапазон ("Обсяг").Цінність * Продукт.Діапазон ("Ціна").Цінність
.Діапазон ("A2").Формула = " = IF (ISBLANK (B2),"""", COUNTA ($B$2: B2))"
Якщо NextRow > 2, то
Діапазон ("A2").Вибрати
Вибір.Призначення автозаповнення: = діапазон ("A2:A" &наступний рядок)
Діапазон ("A2:A" і наступний рядок).Вибрати
Закінчиться, якщо
.Діапазон ("Diapason").Чіткі змісту
Закінчувати словами
Кінцевий сабвуфер


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

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

    Допоміжна форма введення даних()

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

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

    

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

    NextRow = Продукт.Осередки (Producty.Rows.Count, 2).Кінець (xlUp).Зміщення (1, 0).Рядок

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

    

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

    If .Range("A2").Value = "" І .Діапазон ("B2").Значення = "" Тоді

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

    

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

    Продукт.Діапазон ("Ім'я").Копіювати

    В ній параметр»Ім'я" означать ім'я, яке ми присвоїли полю " Назва товару» У ФОРМІ введення.

    

    У рядках

    
.Клітинки (NextRow, 2).Вставити спеціальну вставку:=xlPasteValues
.Клітинки (наступний рядок, 3).Значення = Продукт.Діапазон ("Обсяг").Цінність
.Cells(nextRow, 4).Value = Producty.Range("Price").Value
.Cells(nextRow, 5).Value = Producty.Range("Volum").Value * Producty.Range("Price").Value


    Найменування &171;Volum&187; і &171;Price&187; означають назви, які ми призначили полям " кількість» і »Ціна" в тій же формі введення.

    

    У цих же рядках, які ми вказали вище, цифри «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» &8212; адреса цілого стовпця з нумерацією. Перевірте, де саме буде виводитися нумерація у вашій таблиці і змініть дані координати в коді, якщо це необхідно.

    

    У рядку проводиться очищення діапазону форми введення даних після того, як інформація з неї була перенесена в таблицю:

    .Range("Diapason").ClearContents

    Не важко здогадатися, що ( &171;Diapason&187; ) означає найменування того діапазону, який ми раніше присвоїли полям для введення даних. Якщо ви дали їм інше найменування, то в цьому рядку має бути вставлено саме воно.

    

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

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


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

29. Потім з затиснутою лівою клавішею миші обводимо курсором область, де хочемо розмістити кнопку запуску макросу, який буде виробляти перенесення даних з форми в таблицю.

30. Після того, як область обведена, відпускаємо клавішу миші. Потім автоматично запускається вікно призначення макросу об'єкту. Якщо у вашій книзі застосовується кілька макросів, то вибираємо зі списку назву того, який ми вище створювали. У нас він називається «DataEntryForm» . Але в даному випадку макрос один, тому просто вибираємо його і тиснемо на кнопку «OK» внизу вікна.

31. Після цього можна перейменувати кнопку, як ви захочете, просто виділивши її поточна назва.

    У нашому випадку, наприклад, логічно буде дати їй ім'я»додати" . Перейменовуємо і натискаємо мишкою по будь-якій вільній комірці листа.


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

33. Як бачимо, значення переміщені в таблицю, рядку автоматично присвоєно номер, сума порахована, поля форми очищені.

34. Повторно заповнюємо форму і тиснемо на кнопку»додати" .

35. Як ми бачимо, а другий рядок також додано до табличного масиву. Це означає, що інструмент працює.

Читайте також:
як створити макрос в Excel
як створити кнопку в Excel

В Ексель існує два способи застосування форми заповнення даними: вбудована і призначена для користувача. Застосування вбудованого варіанту вимагає мінімум зусиль від користувача. Його завжди можна запустити, додавши відповідний значок на панель швидкого доступу. Призначену для користувача форму потрібно створювати самому, але якщо ви добре розбираєтеся в коді VBA, то зможете зробити цей інструмент максимально гнучким і відповідним під ваші потреби.