Създаване на формуляри в Excel

За да се улесни въвеждането на данни в таблица в Excel, можете да използвате специални формуляри, които ще ви помогнат да ускорите процеса на попълване на таблицата с информация. В Excel има вграден инструмент, който ви позволява да попълните подобен метод. Също така, потребителят може да създаде свой собствен вариант на формуляра, който да бъде максимално адаптиран към неговите нужди, като се използва макрос за това. Нека да разгледаме различните приложения на тези полезни инструменти за пълнене в Excel.

съдържание

Приложение на инструментите за пълнене
- Метод 1: Вграден обект за въвеждане на данни от Excel
- Метод 2: Създайте персонализиран формуляр
Въпроси и отговори

Приложение на инструментите за пълнене

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

Сега нека разгледаме как да използваме тези два вида инструменти.

Метод 1: Вграден обект за въвеждане на данни от Excel

На първо място, нека разберем как да използвате вградения формуляр, за да въведете данните в Excel.

Трябва да се отбележи, че по подразбиране иконата, която я стартира, е скрита и трябва да бъде активирана. За да направите това, отворете раздела "Файл" , след което кликнете върху опцията "Опции" .

В отворения прозорец на опциите за Excel преминете към "лентата с инструменти за бърз достъп" . По-голямата част от прозореца е зает от широк спектър от настройки. В лявата част има инструменти, които могат да бъдат добавени към лентата с инструменти за бърз достъп, а от дясната страна вече са налице.
В полето "Избор на команди от" задайте стойността на "Команди не на лента" . По-нататък от списъка с команди, намиращи се в азбучен ред, намираме и избираме позицията "Формуляр ..." . След това кликнете върху бутона "Добави" .

След това инструментът, от който се нуждаете, ще се покаже в дясната част на прозореца. Кликнете върху бутона "OK" .

Този инструмент се намира в прозореца на Excel в лентата с инструменти за бърз достъп и можем да го използваме. Той ще бъде налице, когато отворите всяка книга с този екземпляр на Excel.

Сега, за да може инструментът да разбере точно какво трябва да запълни, е необходимо да подредите заглавката на таблицата и да запишете всяка стойност в нея. Нека масата на таблицата в нас ще се състои от четири колони, които имат имената "Име на продукта" , "Количество" , "Цена" и "Сума" . Въведете имената в произволен хоризонтален обхват на листа.

Също така, за да може програмата да разбере с кои ленти тя ще трябва да работи, е необходимо да въведете стойност в първия ред на таблицата масив.

След това изберете която и да е клетка от таблицата да е празна и кликнете върху лентата за пряк достъп на иконата "Форма ..." , която преди това активирахме.

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

Въведете стойностите, които според нас са необходими в останалите полета, и кликнете върху бутона "Добави" .

След това, както можете да видите, въведените стойности автоматично се прехвърлят в първия ред на таблицата и формулярът се предава на следващия блок от полета, който съответства на втория ред на масива.

Попълваме прозореца на инструмента с тези стойности, които искаме да видим на втория ред в областта на таблицата, и отново кликни върху бутона "Добави" .

Както можете да видите, стойностите на втория ред също бяха добавени и дори не трябваше да пренаредим курсора в самата таблица.

По този начин попълваме табличния масив с всички стойности, които искаме да влезем в него.

Освен това, ако желаете, можете да прелиствате въведените по-рано стойности с помощта на бутоните "Назад" и "Напред" или вертикалната лента за превъртане.

Ако е необходимо, можете да коригирате всяка стойност в масива от таблицата, като я промените във формата. За да направите промени в листа, след като ги добавите в съответното поле за инструменти, кликнете върху бутона "Добави" .

Както можете да видите, промяната се появи веднага в областта на таблицата.

Ако трябва да изтриете някакъв ред, а след това чрез навигационните бутони или лентата за превъртане, отидете на съответния блок от полета във формуляра. След това кликнете върху бутона "Изтриване" в прозореца на инструмента.

Отваря се предупредителен диалогов прозорец, който ви информира, че линията ще бъде изтрита. Ако сте сигурни в действията си, кликнете върху бутона "OK" .

Както можете да видите, линията е извлечена от диапазона на таблицата. След като завършите попълването и редактирането, можете да излезете от прозореца на инструмента, като кликнете върху бутона "Затвори" .

След това, за да направите масива на таблицата по-визуален, можете да го форматирате.

Метод 2: Създайте персонализиран формуляр

Освен това, използвайки макрос и редица други инструменти, е възможно да създадете свой собствен формуляр, за да запълните пространството в таблицата. Той ще бъде създаден директно върху листа и ще представлява неговия диапазон. С помощта на този инструмент, самият потребител ще може да осъзнае възможностите, които счита за необходими. На функционално ниво той практически няма да бъде по-нисък от вградения аналог на Excel и в някои случаи може да го надхвърли. Единственият недостатък е, че за всеки масив таблица трябва да създадете отделен формуляр и да не използвате същия шаблон, колкото е възможно със стандартната версия.

Както и при предишния метод, на първо място, трябва да направите заглавие на бъдещата таблица на листа. Той ще се състои от пет клетки с имената: "№ п / п" , "Име на стоките" , "Количество" , "Цена" , "Сума" .

След това трябва да направим така наречената интелигентна маса от нашата масива, с възможност за автоматично добавяне на линии при попълване на съседни ленти или клетки с данни. За да направите това, изберете заглавката и в раздела "Начало" кликнете върху бутона "Форматирай като таблица" в полето "Стилове" . След това се отваря списък с опциите за налични стилове. От гледна точка на функционалността изборът на един от тях няма да бъде засегнат по никакъв начин, затова просто избираме варианта, който смятаме за по-подходящ.

След това се отваря малък прозорец за форматиране на таблицата. Показва диапазона, който преди това сме разпределили, т.е. диапазонът на капачката. Като правило всичко се попълва в това поле. Но трябва да проверим опцията "Таблица с заглавия" . След това кликнете върху бутона "OK" .

Така че нашият диапазон е форматиран като "интелигентна" таблица, както се вижда от дори промяната на визуалния дисплей. Както можете да видите, наред с други неща, за всяка заглавка на заглавката на колоната се появиха икони за филтриране. Те трябва да бъдат забранени. За да направите това, изберете която и да е клетка от "интелигентната" таблица и отидете в раздела "Данни" . Там, на лентата в полето "Сортиране и филтър" кликнете върху иконата "Филтър" .
Има още една възможност да изключите филтъра. В този случай дори не е нужно да отивате в друг раздел, оставайки в раздела "Начало" . След като изберете клетката в областта на таблицата на лентата в блока за настройки "Редактиране", кликнете върху иконата "Сортиране и филтриране" . В списъка, който се показва, изберете елемента "Филтриране" .

Както можете да видите, след това действие иконите на филтъра изчезнаха от заглавката на таблицата, както се изисква.

Тогава трябва да създадем самия вход. Той също така ще представлява един вид табличен масив, състоящ се от две колони. Имената на редовете на този обект ще съответстват на имената на колоните на главната таблица. Изключение са графите "№" и "Сума" . Те ще отсъстват. Номерирането на първата от тях ще се извърши с помощта на макрос, а изчисляването на стойностите във втория ще се извърши, като се приложи формулата за умножаване на количеството по цената.
Втората колона на обекта за въвеждане на данни остава празна за момента. Веднага в него ще бъдат въведени стойности, за да се запълнят линиите на масата на основната маса.

След това създайте друга малка маса. Тя ще се състои от една колона и ще съдържа списък с продукти, които ще изведем до втората колона на основната таблица. За по-голяма яснота клетката с заглавието на този списък ( "Списък на стоките" ) може да бъде попълнена с цвят.

След това изберете първата празна клетка на обекта за въвеждане на стойност. Отворете раздела "Данни" . Кликваме върху иконата "Проверка на данни" , която се намира на лентата в полето "Работа с данни" .

Прозорецът за проверка на входовете започва. Кликваме върху полето "Тип данни" , в което по подразбиране е зададен параметър "Всяка стойност" .

От отворените опции изберете елемента "Списък" .

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

След това прозорецът за проверка на входните стойности се свива. Изберете курсора с левия бутон на мишката подстригва списъка с данни, които са поставени в листа в допълнителната таблица "Списък на стоките" . След това отново кликваме върху иконата отдясно на полето, в което се появи адресът на избрания диапазон.

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

Сега вдясно от избраната празна клетка на обекта за въвеждане на данни се появи икона под формата на триъгълник. Когато кликнете върху него, се отваря падащ списък, състоящ се от заглавия, които са изтеглени от табличния масив "Списък на продуктите" . Невъзможно е да направите произволни данни в посочената клетка, но само вие можете да изберете желаната позиция от списъка. Изберете елемента в падащия списък.

Както можете да видите, избраната позиция бе незабавно показана в полето "Име на продукта" .

След това ще трябва да назовем трите клетки на входния формуляр, където ще въведем данните. Избираме първата клетка, където вече имаме името "Картофена" в нашия случай. След това отидете в полето за диапазона. Той се намира от лявата страна на прозореца на Excel на същото ниво като лентата с формули. Въвеждаме там произволно име. Това може да е всяко име в латинската азбука, в което няма интервали, но е по-добре да се използват имената, близки до задачите, които се решават от този елемент. Следователно, първата клетка, съдържаща името на продукта, се нарича "Име" . Напишете това име в полето и натиснете Enter на клавиатурата.

По същия начин ние даваме името "Volum" на клетката, в която ще впишем количеството на стоките.

Клетка с цена е "Цена" .

След това, точно по същия начин, даваме името на целия диапазон от горните три клетки. Първо, изберете и след това го дадете на специално поле. Нека това е името "Diapason" .

След последното действие трябва да запазим документа, така че имената, които сме назначили, да могат да възприемат макроса, създаден от нас в бъдеще. За да запазите, отворете раздела "Файл" и кликнете върху "Запазване като ..." .

В прозореца за запазване, който се отваря, в полето "Тип файл" изберете опцията "Работна книга на Excel с поддръжка на макроси (.xlsm)" . След това кликнете върху бутона "Запазване" .

Тогава трябва активирайте макроси Във вашата версия на Excel и включете раздела "Developer" , ако все още не сте го направили. Въпросът е, че и двете тези функции по подразбиране в програмата са прекъснати и тяхното активиране трябва да се извърши задължително в прозореца на параметрите на Excel.
След като направите това, отворете раздела "Програмист" . Кликваме върху голямата икона "Visual Basic" , която се намира на лентата в полето "Code" .

Последното действие предизвиква стартирането на редактора на макроси на VBA. В областта "Проект" , която се намира в горната лява част на прозореца, изберете името на листа, където се намират нашите таблици. В този случай това е "Лист 1" .

След това отидете в долната лява част на прозореца, наречен "Properties" . Има настройки за избрания лист. В полето "(Name)" трябва да замените кирилицата ( "Sheet1" ) с името, написано на латиница. Името може да се даде на някой, който ще ви бъде по-удобен, основното е, че трябва да съдържа само латински букви или цифри и няма други знаци или интервали. Именно с това име макросът ще работи. Нека в нашия случай даденото име да е "Producty" , въпреки че можете да изберете друг, който съответства на условията, описани по-горе.
В полето "Име" можете също да замените името с по-удобно. Но това не е необходимо. Разрешено е да се използват интервали, кирилица и други символи. За разлика от предишния параметър, който определя името на листа за програмата, този параметър задава името на листа, видим за потребителя от лентата за пряк достъп.
Както можете да видите, след това името на Лист 1 автоматично ще се промени в областта "Проект" , която току-що зададохме в настройките.

След това отидете в централната част на прозореца. Тук трябва да напишем самия макрос код. Ако полето за редактора на белия код в посочената област не се показва, както в нашия случай, след това натиснете функционалния клавиш F7 и ще се появи.

Сега за конкретния пример на нашия пример, трябва да напишем следния код в полето:
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" се появява в кода , трябва да я замените с името, което сте задали преди това за вашия лист в полето "(Име)" в областта "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» ) означает наименование того диапазона, который мы ранее присвоили полям для ввода данных. Если вы дали им другое наименование, то в этой строке должно быть вставлено именно оно.
Дальнейшая часть кода универсальна и во всех случаях будет вноситься без изменений.
После того, как вы записали код макроса в окно редактора, следует нажать на значок сохранения в виде дискеты в левой части окна. Затем можно его закрывать, щелкнув по стандартной кнопке закрытия окон в правом верхнем углу.

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

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

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

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

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

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

Повторно заполняем форму и жмем на кнопку «Добавить» .

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

Прочетете още:
Как создать макрос в Excel
Как создать кнопку в Excel

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

Формуляри за въвеждане на данни в Microsoft Excel

Приложение на инструментите за пълнене

Метод 1: Вграден обект за въвеждане на данни от Excel

Метод 2: Създайте персонализиран формуляр