СТВАРЭННЕ ФОРМАЎ У EXCEL

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

Прымяненне інструментаў запаўнення

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

Зараз давайце разгледзім, як карыстацца гэтымі двума відамі інструментаў.

Спосаб 1: убудаваны аб'ект для ўводу дадзеных Excel

Перш за ўсё, давайце даведаемся, як прымяняць убудаваную форму для ўводу дадзеных Excel.

Трэба адзначыць, што па змаўчанні значок, які яе запускае, скрыты і яго трэба актываваць. Для гэтага пераходзім ва ўкладку "Файл", А затым пстрыкаем па пункце "Параметры".

У якое адкрылася акне параметраў Эксэля перамяшчаемся ў раздзел "Панэль хуткага доступу". Вялікую частку акна займае шырокая вобласць налад. У левай яе часткі знаходзяцца інструменты, якія могуць быць дададзены на панэль хуткага доступу, а ў правай - ужо прысутныя.
У полі "Выбраць каманды з" усталёўваем значэнне "Каманды ня на стужцы". Далей са спісу каманд, размешчанага ў алфавітным парадку, знаходзім і вылучаем пазіцыю "Форма ...". Затым ціснем на кнопку "Дадаць".

Пасля гэтага патрэбны нам інструмент адлюструецца ў правай частцы акна. Ціснем на кнопку "OK".

Цяпер дадзены інструмент размяшчаецца ў акне Excel на панэлі хуткага доступу, і мы ім можам скарыстацца. Ён будзе прысутнічаць пры адкрыцці любой кнігі дадзеных асобнікам Excel.

Зараз, каб інструмент зразумеў, што менавіта яму трэба запаўняць, варта аформіць шапку табліцы і запісаць любы значэнне ў ёй. Хай таблічны масіў у нас будзе складацца з чатырох слупкоў, якія маюць назвы "Найменне тавару", "Колькасць", "Кошт" і "Сума". Ўводзім дадзеныя назвы ў адвольны гарызантальны дыяпазон ліста.

Таксама, каб праграма зразумела, з якім менавіта дыяпазонах ёй трэба будзе працаваць, варта ўвесці любое значэнне ў першую радок таблічнага масіва.

Пасля гэтага вылучаем любую вочка нарыхтоўкі табліцы і пстрыкаем на панэлі хуткага доступу па значку "Форма ...", Які мы раней актывавалі.

Такім чынам, адкрываецца акно названага інструмента. Як бачым, дадзены аб'ект мае поля, якія адпавядаюць назвах слупкоў нашага таблічнага масіва. Пры гэтым першае поле ўжо запоўнена значэннем, так як мы яго ўвялі ўручную на лісце.

Ўводзім значэння, якія лічым вельмі важнымі і ў астатнія поля, пасля чаго ціснем на кнопку "Дадаць".

Пасля гэтага, як бачым, у першую радок табліцы былі аўтаматычна перанесены уведзеныя значэння, а ў форме адбыўся пераход да наступнага блоку палёў, які адпавядаюць другім радку таблічнага масіва.

Запаўняем акно інструмента тымі значэннямі, якія хочам бачыць у другім радку таблічнай вобласці, і зноў пстрыкаем па кнопцы "Дадаць".

Як бачым, значэння другога радка таксама былі дададзеныя, прычым нам нават не прыйшлося перастаўляць курсор ў самой табліцы.

Такім чынам, запаўняем таблічны масіў усімі значэннямі, якія хочам у яе ўвесці.

Акрамя таго, пры жаданні, можна вырабляць навігацыю па раней уведзеных значэнняў з дапамогай кнопак "Назад" і "Далей" або вертыкальнай паласы прагорткі.

Пры неабходнасці можна адкарэктаваць любое значэнне ў таблічным масіве, змяніўшы яго ў форме. Каб змены адлюстраваліся на лісце, пасля ўнясення іх у адпаведны блок інструмента, ціснем на кнопку "Дадаць".

Як бачым, змяненне адразу адбылося і ў таблічнай вобласці.

Калі нам трэба выдаліць, нейкую радок, то праз кнопкі навігацыі або паласу пракруткі пераходзім да адпаведнага ёй блоку палёў у форме. Пасля гэтага пстрыкаем па кнопцы "Выдаліць" ў акенцы інструмента.

Адкрываецца дыялогавае акно папярэджання, у якім паведамляецца, што радок будзе выдаленая. Калі вы ўпэўненыя ў сваіх дзеяннях, то цісніце на кнопку "OK".

Як бачым, радок была вынятая з таблічнага дыяпазону. Пасля таго, як запаўненне і рэдагаванне скончана, можна выходзіць з акна інструмента, націснуўшы на кнопку "Зачыніць".

Пасля гэтага для паданні таблічнага масіву больш навочнага візуальнага выгляду можна вырабіць фарматаванне.

Спосаб 2: стварэнне карыстацкай формы

Акрамя таго, з дапамогай макраса і шэрагу іншых інструментаў існуе магчымасць стварыць уласную карыстацкую форму для запаўнення таблічнай вобласці. Яна будзе стварацца прама на лісце, і ўяўляць сабой яе дыяпазон. З дапамогай дадзенага інструмента карыстальнік сам зможа рэалізаваць тыя магчымасці, якія лічыць патрэбнымі. Па функцыянале ён практычна ні ў чым не будзе саступаць ўбудаванаму аналагу Excel, а сёе ў чым, магчыма, пераўзыходзіць яго. Адзіны недахоп складаецца ў тым, што для кожнага таблічнага масіва прыйдзецца складаць асобную форму, а не ўжываць адзін і той жа шаблон, як гэта магчыма з дапамогай стандартнага варыянту.

Як і ў папярэднім спосабе, перш за ўсё, трэба скласці шапку будучай табліцы на лісце. Яна будзе складацца з пяці вочак з імёнамі: "№ п / п", "Найменне тавару", "Колькасць", "Кошт", "Сума".

Далей трэба з нашага таблічнага масіва зрабіць так званую "разумную" табліцу, з магчымасцю аўтаматычнага дадання радкоў пры запаўненні суседніх дыяпазонаў або вочак дадзенымі. Для гэтага вылучаем шапку і, знаходзячыся ва ўкладцы "Галоўная", Ціснем на кнопку "Фарматаваць як табліцу" ў блоку інструментаў "Стылі". Пасля гэтага адкрываецца спіс даступных варыянтаў стыляў. На функцыянал выбар аднаго з іх ніяк не паўплывае, таму выбіраем проста той варыянт, які лічым больш прыдатным.

Затым адкрываецца невялікае акенца фарматавання табліцы. У ім паказаны дыяпазон, які мы раней вылучылі, гэта значыць, дыяпазон шапкі. Як правіла, у дадзеным поле запоўнена ўсё дакладна. Але нам варта ўсталяваць галачку каля параметру "Табліца з загалоўкамі". Пасля гэтага ціснем на кнопку "OK".

Такім чынам, наш дыяпазон адфарматаваны, як "разумная" табліца, сведчаннем чаго з'яўляецца нават змена візуальнага адлюстравання. Як бачым, апроч іншага, каля кожнага назвы загалоўку слупкоў з'явіліся значкі фільтрацыі. Іх варта адключыць. Для гэтага вылучаем любую вочка "разумнай" табліцы і пераходзім ва ўкладку "Дадзеныя". Там на стужцы ў блоку інструментаў "Сартаванне і фільтр" пстрыкаем па значку "Фільтр".
Існуе яшчэ адзін варыянт адключэння фільтра. Пры гэтым не трэба нават будзе пераходзіць на іншую ўкладку, застаючыся ва ўкладцы "Галоўная". Пасля вылучэння ячэйкі таблічнай вобласці на стужцы ў блоку налад "Рэдагаванне" пстрыкаем па значку "Сартаванне і фільтр". У якое з'явілася спісе выбіраем пазіцыю "Фільтр".

Як бачым, пасля гэтага дзеяння значкі фільтрацыі зніклі з шапкі табліцы, як гэта і патрабавалася.

Затым нам варта стварыць саму форму ўводу дадзеных. Яна таксама будзе ўяўляць сабой свайго роду таблічны масіў, які складаецца з двух слупкоў. Найменні радкоў дадзенага аб'екта будуць адпавядаць імёнах слупкоў асноўнай табліцы. Выключэнне складаюць слупкі "№ п / п" і "Сума". Яны будуць адсутнічаць. Нумарацыя першага з іх будзе адбывацца пры дапамозе макраса, а разлік значэнняў у другім будзе вырабляцца шляхам прымянення формулы множання колькасці на цану.
Другі слупок аб'екта ўводу дадзеных пакінем пакуль што пустым. Непасрэдна ў яго пазней будуць уводзіцца значэння для запаўнення радкоў асноўнага таблічнага дыяпазону.

Пасля гэтага ствараем яшчэ адну невялікую табліцу. Яна будзе складацца з аднаго слупка і ў ёй размесціцца спіс тавараў, якія мы будзем выводзіць у другую калонку асноўнай табліцы. Для нагляднасці вочка з загалоўкам дадзенага пераліку ("Спіс тавараў") Можна заліць колерам.

Затым вылучаем першую пустое вочка аб'екта ўводу значэнняў. Пераходзім ва ўкладку "Дадзеныя". Пстрыкаем па значку "Праверка дадзеных", Які размешчаны на стужцы ў блоку інструментаў "Праца з дадзенымі".

Запускаецца акно праверкі ўводзяцца дадзеных. Клікаем па полі "Тып дадзеных", У якім па змаўчанні ўсталяваны параметр "Любое значэнне".

З якія расчыніліся варыянтаў выбіраем пазіцыю "Спіс".

Як бачым, пасля гэтага акно праверкі ўводзяцца значэнняў некалькі змяніла сваю канфігурацыю. З'явілася дадатковае поле "Крыніца". Пстрыкаем па піктаграме справа ад яго левай клавішай мышы.

Затым акно праверкі ўводзяцца значэнняў згортваецца. Вылучаем курсорам з заціснутай левай клавішай мышы пералік дадзеных, якія размешчаны на лісце ў дадатковай таблічнай вобласці "Спіс тавараў". Пасля гэтага зноў ціснем на піктаграму справа ад поля, у якім з'явіўся адрас вылучанага дыяпазону.

Адбываецца зварот да акенца праверкі ўводзяцца значэнняў. Як бачым, каардынаты вылучанага дыяпазону ў ім ужо адлюстраваны ў полі "Крыніца". Клікаем па кнопцы "OK" унізе акна.

Цяпер справа ад вылучанай пустой ячэйкі аб'екта ўводу дадзеных з'явілася піктаграма ў выглядзе трыкутніка. Пры кліку на яе адкрываецца выпадальны спіс, які складаецца з назваў, якія падцягваюцца з таблічнага масіва "Спіс тавараў". Адвольныя дадзеныя ў паказаную вочка цяпер ўнесці немагчыма, а толькі можна выбраць з прадстаўленага спісу патрэбную пазіцыю. Выбіраем пункт у выпадальным спісе.

Як бачым, абраная пазіцыя тут жа адлюстравалася ў полі "Найменне тавару".

Далей нам трэба будзе прысвоіць імёны тым тром вочках формы ўводу, куды мы будзем уводзіць дадзеныя. Вылучаем першую вочка, дзе ўжо ўстаноўлена ў нашым выпадку найменне "Бульба". Далей пераходзім у поле назвы дыяпазонаў. Яно размешчана ў левай частцы акна Excel на тым жа ўзроўні, што і радок формул. Ўводзім туды адвольнае назву. Гэта можа быць любая назва на лацінцы, у якім няма прабелаў, але лепш усё-ткі выкарыстоўваць назвы блізкія да вырашаемым дадзеных элементам задачам. Таму першую вочка, у якой утрымліваецца назву тавару, назавем "Name". Пішам дадзенае найменне ў поле і ціснем на клавішу Enter на клавіятуры.

Сапраўды такім жа чынам прысвойваем вочку, у якую будзем уводзіць колькасць тавару, імя "Volum".

А вочку з цаной - "Price".

Пасля гэтага сапраўды такім жа чынам даем назву ўсім дыяпазоне з вышэйзгаданых трох вочак. Перш за ўсё, вылучым, а потым дамо яму найменне ў спецыяльным полі. Хай гэта будзе імя "Diapason".

Пасля апошняга дзеянні абавязкова захоўваем дакумент, каб назвы, якія мы прысвоілі, змог ўспрымаць макрас, створаны намі ў далейшым. Для захавання пераходзім ва ўкладку "Файл" і клікаем па пункце "Захаваць як… ".

У якое адкрылася акне захавання ў поле "Тып файлаў" выбіраем значэнне "Кніга Excel з падтрымкай макрасаў (.xlsm)". Далей ціснем на кнопку "Захаваць".

Затым вам варта актываваць працу макрасаў ў сваёй версіі Excel і ўключыць ўкладку "Распрацоўнік", Калі вы гэта да гэтага часу не зрабілі. Справа ў тым, што абедзве гэтыя функцыі па змаўчанні ў праграме адключаныя, і іх актывацыю трэба выконваць прымусова ў акне параметраў Excel.
Пасля таго, як вы зрабілі гэта, пераходзім ва ўкладку "Распрацоўнік". Клікаем па вялікім значку "Visual Basic", Які размешчаны на стужцы ў блоку інструментаў "Код".

Апошняе дзеянне прыводзіць да таго, што запускаецца рэдактар макрасаў VBA. У вобласці "Project", Якая размешчана ў верхняй левай частцы акна, вылучаем імя таго ліста, дзе размяшчаюцца нашы табліцы. У дадзеным выпадку гэта "Ліст 1".

Пасля гэтага пераходзім да левай ніжняй вобласці акна пад назвай "Properties". Тут размешчаны налады вылучанага ліста. У полі "(Name)" варта замяніць кірылічнае найменне ("Лист1") На назву, напісанае на лацінцы. Назва можна даць любое, якое вам будзе зручней, галоўнае, каб у ім былі выключна сімвалы лацінкі або лічбы і адсутнічалі іншыя знакі або прабелы. Менавіта з гэтым імем будзе працаваць макрас. Хай у нашым выпадку дадзеных назвай будзе "Producty", Хоць вы можаце выбраць і любое іншае, якое адпавядае умовам, якія былі апісаны вышэй.
У полі "Name" таксама можна замяніць назву на больш зручны. Але гэта не абавязкова. Пры гэтым дапускаецца выкарыстанне прабелаў, кірыліцы і любых іншых знакаў. У адрозненне ад папярэдняга параметру, які задае найменне ліста для праграмы, дадзены параметр прысвойвае назву лісту, бачнае карыстачу на панэлі цэтлікаў.
Як бачым, пасля гэтага аўтаматычна зменіцца і найменне ліста 1 ў галіне "Project", На тое, якое мы толькі што задалі ў наладах.

Затым пераходзім ў цэнтральную вобласць акна. Менавіта тут нам трэба будзе запісаць сам код макраса. Калі поле рэдактара кода белага колеру ў названай галіне не адлюстроўваецца, як у нашым выпадку, то ціснем на функцыянальную клавішу 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" вы павінны яго замяніць на тое найменне, якое раней прысвоілі для свайго ліста ў поле "(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») Азначае найменне таго дыяпазону, які мы раней прысвоілі палях для ўводу дадзеных. Калі вы далі ім іншае найменне, то ў гэтым радку павінна быць зроблена менавіта яно.
Далейшая частка кода ўніверсальная і ва ўсіх выпадках будзе ўносіцца без зменаў.
Пасля таго, як вы запісалі код макраса ў акно рэдактара, варта націснуць на значок захавання ў выглядзе дыскеты ў левай частцы акна. Затым можна яго зачыняць, пстрыкнуўшы па стандартнай кнопцы закрыцця вокнаў у правым верхнім куце.

Пасля гэтага вяртаемся на ліст Excel. Цяпер нам варта размясціць кнопку, якая будзе актываваць створаны макрас. Для гэтага пераходзім ва ўкладку "Распрацоўнік". У блоку налад "Элементы кіравання" на стужцы клікаем па кнопцы "Уставіць". Адкрываецца пералік інструментаў. У групе інструментаў "Элементы кіравання формы" выбіраем самы першы - "Кнопка".

Затым з заціснутай левай клавішай мышы абводзім курсорам вобласць, дзе хочам размясціць кнопку запуску макраса, які будзе вырабляць перанос дадзеных з формы ў табліцу.

Пасля таго, як вобласць абведзеная, адпускаем клавішу мышы. Затым аўтаматычна запускаецца акно прызначэння макраса аб'екту. Калі ў вашай кнізе ўжываецца некалькі макрасаў, то выбіраем з спісу назву таго, які мы вышэй стваралі. У нас ён завецца "DataEntryForm". Але ў дадзеным выпадку макрас адзін, таму проста выбіраем яго і ціснем на кнопку "OK" унізе акна.

Пасля гэтага можна перайменаваць кнопку, як вы захочаце, проста вылучыўшы яе бягучае назву.
У нашым выпадку, напрыклад, лагічна будзе даць ёй імя "Дадаць". Пераназываем і клікаем мышкай па любой свабоднай вочку ліста.

Такім чынам, наша форма цалкам гатовая. Праверым, як яна працуе. Ўводзім ў яе поля неабходныя значэння і ціснем на кнопку "Дадаць".

Як бачым, значэння перамешчаныя ў табліцу, радку аўтаматычна прысвоены нумар, сума палічаная, палі формы ачышчаны.

Паўторна запаўняем форму і ціснем на кнопку "Дадаць".

Як бачым, і другая радок таксама дададзеная ў таблічны масіў. Гэта азначае, што інструмент працуе.

Чытайце таксама:
Як стварыць макрас ў Excel
Як стварыць кнопку ў Excel

У Эксэля існуе два спосабу ўжывання формы запаўнення дадзенымі: убудаваная і карыстацкая. Ужыванне ўбудаванага варыянту патрабуе мінімум намаганняў ад карыстальніка. Яго заўсёды можна запусціць, дадаўшы адпаведны значок на панэль хуткага доступу. Карыстацкую форму трэба ствараць самому, але калі вы добра разбіраецеся ў кодзе VBA, то зможаце зрабіць гэты інструмент максімальна гнуткім і прыдатным пад вашыя патрэбы.