Tworzenie formularzy w Excelu

Aby ułatwić wprowadzanie danych do tabeli w Excelu, możesz użyć specjalnych formularzy, które pomogą przyspieszyć proces wypełniania zakresu tabel informacjami. W Excelu znajduje się wbudowane narzędzie, które pozwala wypełnić podobną metodę. Ponadto użytkownik może stworzyć własny wariant formularza, który będzie maksymalnie dostosowany do jego potrzeb, stosując do tego makro. Spójrzmy na różne zastosowania tych przydatnych narzędzi do wypełniania w programie Excel.

Spis treści

Zastosowanie narzędzi do napełniania
- Metoda 1: Wbudowany obiekt do wprowadzania danych Excel
- Metoda 2: Utwórz niestandardowy formularz
Pytania i odpowiedzi

Zastosowanie narzędzi do napełniania

Formą wypełnienia jest obiekt z polami, których nazwy odpowiadają nazwom kolumn kolumny tabeli, która ma być wypełniona. W tych polach należy wprowadzić dane i zostaną one natychmiast dodane do zakresu tabeli przez nową linię. Formularz może działać jako oddzielne wbudowane narzędzie Excel i może być umieszczony bezpośrednio na arkuszu jako jego zakres, jeśli jest tworzony przez użytkownika.

Teraz przyjrzyjmy się, jak korzystać z tych dwóch rodzajów narzędzi.

Metoda 1: Wbudowany obiekt do wprowadzania danych Excel

Przede wszystkim dowiedzmy się, jak używać wbudowanego formularza do wprowadzania danych Excel.

Należy zauważyć, że domyślnie ikona, która go uruchamia jest ukryta i musi być aktywowana. Aby to zrobić, przejdź do zakładki "Plik" , a następnie kliknij element "Opcje" .

W otwartym oknie opcji programu Excel przejdź do "Pasek narzędzi szybkiego dostępu" . Większość okna zajmuje szeroki zakres ustawień. W lewej części znajdują się narzędzia, które można dodać do paska narzędzi szybkiego dostępu, a po prawej stronie są już dostępne.
W polu "Wybierz polecenia z" ustaw wartość "Polecenia nie na taśmie" . Dalej z listy poleceń, rozmieszczonych w kolejności alfabetycznej, znajdujemy i wybieramy pozycję "Formularz ..." . Następnie kliknij przycisk "Dodaj" .

Następnie potrzebne narzędzie zostanie wyświetlone po prawej stronie okna. Kliknij przycisk "OK" .

Teraz to narzędzie znajduje się w oknie Excela na pasku narzędzi szybkiego dostępu i możemy z niego korzystać. Będzie on dostępny po otwarciu dowolnej książki z tym wystąpieniem programu Excel.

Teraz, aby narzędzie dokładnie zrozumiało, co należy wypełnić, należy ułożyć nagłówek tabeli i zapisać w nim dowolną wartość. Niech tablica u nas będzie składać się z czterech kolumn, które mają nazwy "Nazwa produktu" , "Ilość" , "Cena" i "Suma" . Wprowadź dane nazwy do dowolnego zakresu poziomego arkusza.

Ponadto, aby program mógł zrozumieć, z którymi zespołami będzie musiał pracować, konieczne jest wprowadzenie dowolnej wartości w pierwszym wierszu tablicy tabeli.

Następnie wybierz dowolną komórkę tabeli i kliknij pasek skrótów na ikonie "Formularz ..." , którą wcześniej aktywowaliśmy.

Otworzy się okno określonego narzędzia. Jak widać, ten obiekt ma pola odpowiadające nazwom kolumn naszej tablicy tabel. W tym przypadku pierwsze pole jest już wypełnione wartością, ponieważ wprowadziliśmy ją ręcznie na arkuszu.

Wprowadź wartości, które naszym zdaniem są potrzebne w pozostałych polach, a następnie kliknij przycisk "Dodaj" .

Następnie, jak widać, wprowadzone wartości zostały automatycznie przeniesione do pierwszego wiersza tabeli, a formularz został przekazany do następnego bloku pól odpowiadającego drugiemu wierszowi tablicy tablicowej.

Wypełniamy okno narzędzi wartościami, które chcemy zobaczyć w drugim wierszu obszaru tabel, i ponownie klikamy przycisk "Dodaj" .

Jak widać, wartości drugiej linii również zostały dodane i nie musieliśmy nawet przestawiać kursora w samej tabeli.

W ten sposób wypełniamy tablicę tablicową wszystkimi wartościami, które chcemy do niej wprowadzić.

Dodatkowo, jeśli chcesz, możesz poruszać się po wcześniej wprowadzonych wartościach za pomocą przycisków "Wstecz" i "Dalej" lub pionowego paska przewijania.

W razie potrzeby możesz dostosować dowolną wartość w tablicy tabeli, zmieniając ją w formularzu. Aby wprowadzić zmiany na arkuszu, po dodaniu ich do odpowiedniego pola narzędziowego kliknij przycisk "Dodaj" .

Jak widać, zmiana nastąpiła natychmiast w obszarze tabel.

Jeśli musimy usunąć linię, to za pomocą przycisków nawigacji lub paska przewijania przejdź do odpowiedniego bloku pól w formularzu. Następnie kliknij przycisk "Usuń" w oknie narzędzia.

Otworzy się okno dialogowe z ostrzeżeniem, informujące, że linia zostanie usunięta. Jeśli jesteś pewien swoich działań, kliknij przycisk "OK" .

Jak widać, linia została wyodrębniona z zakresu tabeli. Po zakończeniu wypełniania i edycji możesz wyjść z okna narzędzia, klikając przycisk "Zamknij" .

Następnie, aby tablica wyglądała bardziej wizualnie, możesz ją sformatować.

Metoda 2: Utwórz niestandardowy formularz

Ponadto przy użyciu makra i wielu innych narzędzi możliwe jest utworzenie własnego niestandardowego formularza w celu wypełnienia obszaru tabel. Zostanie on utworzony bezpośrednio na arkuszu i będzie reprezentował jego zakres. Za pomocą tego narzędzia użytkownik sam będzie mógł zrealizować te możliwości, które uzna za konieczne. Na poziomie funkcjonalnym praktycznie nie będzie gorszy od wbudowanego analogu Excela, aw niektórych przypadkach może go przekraczać. Jedyną wadą jest to, że dla każdej tablicy tablicowej należy utworzyć oddzielny formularz i nie używać tego samego szablonu, co jest możliwe w wersji standardowej.

Podobnie jak w poprzedniej metodzie, najpierw musisz utworzyć nagłówek przyszłej tabeli na arkuszu. Będzie składał się z pięciu komórek o nazwach: "№ п / п" , "Nazwa towaru" , "Ilość" , "Cena" , "Kwota" .

Następnie musimy stworzyć tak zwany inteligentny stół z naszej tablicy tablicowej, z możliwością automatycznego dodawania linii przy wypełnianiu sąsiednich pasm lub komórek danymi. Aby to zrobić, wybierz nagłówek, a na karcie "Strona główna" kliknij przycisk "Formatuj jako tabelę" w oknie narzędzi "Style" . Następnie pojawi się lista dostępnych stylów. Jeśli chodzi o funkcjonalność, wybór jednego z nich nie zostanie w żaden sposób zmieniony, dlatego wybieramy opcję, którą uważamy za bardziej odpowiednią.

Następnie otwiera małe okno do formatowania tabeli. Wskazuje zakres uprzednio przydzielony, czyli zakres limitu. Z reguły wszystko wypełnia się w tym polu. Ale powinniśmy sprawdzić opcję "Tabela z nagłówkami" . Następnie kliknij przycisk "OK" .

Tak więc nasz asortyment jest sformatowany jako "inteligentny" stół, o czym świadczy nawet zmiana wyświetlanego obrazu. Jak widać, między innymi o każdym tytule nagłówka kolumny pojawiły się ikony filtrowania. Powinny być wyłączone. Aby to zrobić, wybierz dowolną komórkę "inteligentnej" tabeli i przejdź do zakładki "Dane" . Tam, na wstążce w oknie narzędzi "Sortuj i Filtruj", kliknij ikonę "Filtr" .
Jest jeszcze jedna opcja, aby wyłączyć filtr. W takim przypadku nie musisz nawet iść do innej karty, pozostając na karcie "Strona główna" . Po wybraniu komórki obszaru tabeli na wstążce w bloku ustawień "Edytuj", kliknij ikonę "Sortuj i filtruj" . Na wyświetlonej liście wybierz element "Filtr" .

Jak widać, po tej czynności ikony filtrów zniknęły z nagłówka tabeli, zgodnie z wymaganiami.

Następnie musimy stworzyć sam formularz wejściowy. Będzie również reprezentował rodzaj tablicy tablicowej składającej się z dwóch kolumn. Nazwy wierszy tego obiektu będą odpowiadać nazwom kolumn tabeli głównej. Wyjątkiem są kolumny "Nr" i "Kwota" . Będą nieobecni. Numeracja pierwszego z nich nastąpi za pomocą makra, a obliczenia wartości w drugim zostaną wykonane poprzez zastosowanie wzoru na pomnożenie ilości przez cenę.
Druga kolumna obiektu wprowadzania danych pozostaje na razie pusta. Natychmiast w nim zostaną wprowadzone wartości, aby wypełnić linie w głównym zakresie tabel.

Potem utwórz kolejny mały stolik. Będzie składał się z jednej kolumny i będzie zawierał listę produktów, które skierujemy do drugiej kolumny tabeli głównej. Dla jasności komórka z tytułem tej listy ( "Lista towarów" ) może być wypełniona kolorem.

Następnie wybierz pierwszą pustą komórkę obiektu wprowadzania wartości. Przejdź do zakładki "Dane" . Klikamy ikonę "Sprawdź dane" , która znajduje się na wstążce w oknie narzędziowym "Praca z danymi" .

Rozpocznie się okno sprawdzania danych wejściowych. Klikamy na pole "Typ danych" , w którym domyślnie ustawiony jest parametr "Dowolna wartość" .

Z otwartych opcji wybierz element "Lista" .

Jak widać, okno do sprawdzania wartości wejściowych zmieniło nieco jego konfigurację. Pojawiło się dodatkowe pole "Źródło" . Kliknij ikonę po jego prawej stronie lewym przyciskiem myszy.

Następnie okno do sprawdzania wartości wejściowych jest zwinięte. Wybierz kursor lewym przyciskiem myszy, przycinając listę danych umieszczoną na arkuszu w obszarze dodatkowej tabeli "Lista towarów" . Następnie ponownie klikamy ikonę po prawej stronie pola, w którym pojawił się adres wybranego zakresu.

Pojawi się powrót do pola wyboru dla wprowadzonych wartości. Jak widać, współrzędne wybranego zakresu są już wyświetlane w polu "Źródło" . Kliknij przycisk "OK" w dolnej części okna.

Teraz ikona z trójkątem pojawiła się na prawo od wybranej pustej komórki obiektu wprowadzania danych. Po kliknięciu otwiera się lista rozwijana składająca się z tytułów pobranych z tablicy tabeli "Lista produktów" . Arbitralne dane w określonej komórce są obecnie niemożliwe do wykonania, ale tylko Ty możesz wybrać żądaną pozycję z listy. Wybierz pozycję z listy rozwijanej.

Jak widać, wybrana pozycja została natychmiast wyświetlona w polu "Nazwa produktu" .

Następnie będziemy musieli nazwać trzy komórki formularza wejściowego, w którym wprowadzimy dane. Wybieramy pierwszą komórkę, w której mamy już nazwę "Ziemniak" w naszym przypadku. Następnie przejdź do pola nazwy zakresu. Znajduje się po lewej stronie okna Excela na tym samym poziomie co pasek formuły. Wpisujemy tam dowolną nazwę. Może to być dowolna nazwa w alfabecie łacińskim, w której nie ma spacji, ale lepiej jest używać nazw bliskich zadaniom rozwiązywanym przez ten element. Dlatego pierwsza komórka zawierająca nazwę produktu nosi nazwę "Nazwa" . Wpisz tę nazwę w polu i naciśnij Enter na klawiaturze.

Dokładnie w ten sam sposób przypisujemy nazwę "Volum" do komórki, do której wprowadzimy ilość towarów.

Komórka z ceną to "Cena" .

Następnie, dokładnie w ten sam sposób, nadajemy nazwę całemu zakresowi powyższych trzech komórek. Przede wszystkim wybierz, a następnie podaj mu nazwisko w specjalnym polu. Niech to będzie nazwa "Diapason" .

Po ostatniej akcji musimy zapisać dokument, aby przypisane przez nas nazwy mogły postrzegać makro tworzone przez nas w przyszłości. Aby zapisać, przejdź do zakładki "Plik" i kliknij "Zapisz jako ..." .

W otwartym oknie zapisu, w polu "Typ pliku" wybierz opcję "Skoroszyt Excel z opcją obsługi makr (.xlsm)" . Następnie kliknij przycisk "Zapisz" .

Wtedy powinieneś aktywuj makra W swojej wersji programu Excel i włącz kartę "Programista" , jeśli jeszcze tego nie zrobiłeś. Chodzi o to, że obie te funkcje domyślnie w programie są rozłączone, a ich aktywacja musi być obowiązkowo przeprowadzona w oknie parametrów Excela.
Po wykonaniu tej czynności przejdź do zakładki "Programista" . Klikamy na dużą ikonę "Visual Basic" , która znajduje się na wstążce w oknie narzędziowym "Kod" .

Ostatnia akcja powoduje uruchomienie edytora makr VBA. W obszarze "Projekt" , który znajduje się w lewej górnej części okna, wybierz nazwę arkusza, w którym znajdują się nasze tabele. W tym przypadku jest to "Arkusz 1" .

Następnie przejdź do dolnej lewej części okna o nazwie "Właściwości" . Dostępne są ustawienia dla wybranego arkusza. W polu "(Nazwa)" należy zastąpić cyrylicę ( "Arkusz1" ) nazwą pisaną po łacinie. Nazwę można nadać każdemu, która będzie dla ciebie wygodniejsza, najważniejsze, że powinna zawierać tylko znaki alfabetu łacińskiego lub cyfry i nie ma innych znaków ani spacji. To właśnie z tą nazwą makro zadziała. Niech w naszym przypadku podana nazwa będzie "Producty" , chociaż możesz wybrać dowolną inną, która odpowiada warunkom opisanym powyżej.
W polu "Nazwa" możesz również zastąpić nazwę bardziej wygodną. Ale to nie jest konieczne. Dozwolone jest używanie spacji, cyrylicy i innych znaków. W przeciwieństwie do poprzedniego parametru, który określa nazwę arkusza programu, ten parametr przypisuje nazwę arkusza widocznego dla użytkownika na pasku skrótów.
Jak widać, nazwa Arkusza 1 zmieni się automatycznie w obszarze "Projekt" , który właśnie ustawiliśmy w ustawieniach.

Następnie przejdź do centralnej części okna. Tutaj musimy napisać sam kod makra. Jeśli pole edytora białego kodu w określonym obszarze nie jest wyświetlane, tak jak w naszym przypadku, naciśnij klawisz funkcyjny F7 i pojawi się.

Teraz konkretny przykład naszego przykładu, musimy napisać następujący kod w polu:
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
Ale ten kod nie jest uniwersalny, to znaczy, że jest w niezmienionej formie odpowiedniej tylko dla naszego przypadku. Jeśli chcesz dostosować go do swoich potrzeb, należy go odpowiednio zmodyfikować. Abyś mógł zrobić to sam, przeanalizujmy, na czym polega ten kod, co należy zastąpić i czego nie można zmienić.
Pierwsza linia:
Sub DataEntryForm()

"DataEntryForm" to nazwa samego makra. Możesz go pozostawić bez zmian lub zastąpić go innym, który odpowiada ogólnym regułom tworzenia nazw makr (bez spacji, tylko litery alfabetu łacińskiego itp.). Zmiana nazwy nie wpłynie na nic.
Wszędzie tam, gdzie w kodzie pojawia się słowo "Producty", należy zastąpić je nazwą przypisaną wcześniej do arkusza w polu "(Nazwa)" w obszarze "Właściwości" edytora makr. Oczywiście należy to zrobić tylko wtedy, gdy nazwałeś arkusz arkuszem w inny sposób.
Teraz rozważ tę linię:
nextRow = Producty.Cells(Producty.Rows.Count, 2).End(xlUp).Offset(1, 0).Row

Liczba "2" w tym wierszu oznacza drugą kolumnę arkusza. W tej kolumnie znajduje się kolumna o nazwie "Nazwa produktu" . Na nim rozważymy liczbę rzędów. Dlatego jeśli w twoim przypadku podobna kolumna ma inną kolejność na koncie, musisz wpisać odpowiedni numer. Wartość "End (xlUp) .Offset (1, 0) .Row" w każdym przypadku pozostawia niezmieniona.
Następnie rozważ wiersz
If .Range("A2").Value = "" And .Range("B2").Value = "" Then

"A2" to współrzędne pierwszej komórki, w której zostanie wyświetlona numeracja linii. "B2" to współrzędne pierwszej komórki, która będzie używana do wyprowadzania danych ( "Nazwa towarów" ). Jeśli są różne, wprowadź dane zamiast tych współrzędnych.
Przechodzimy do linii
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» внизу окна.

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

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

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

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

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

Czytaj także:
Как создать макрос в Excel
Как создать кнопку в Excel

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

Formy wprowadzania danych w Microsoft Excel

Zastosowanie narzędzi do napełniania

Metoda 1: Wbudowany obiekt do wprowadzania danych Excel

Metoda 2: Utwórz niestandardowy formularz