змест
У Excel ёсць магутная, але ў той жа час вельмі рэдка выкарыстоўваная магчымасць стварэння аўтаматычных паслядоўнасцяў дзеянняў з дапамогай макрасаў. Макрас - ідэальны выхад, калі вы маеце справу з аднатыпнай задачай, якая паўтараецца шмат разоў. Напрыклад, апрацоўка дадзеных або фарматаванне дакумента па стандартызаваны шаблону. У гэтым выпадку вам не спатрэбіцца веданне моў праграмавання.
Вам ужо цікава, што такое макрас і як ён працуе? Тады смела наперад - далей мы крок за крокам зробім з вамі ўвесь працэс стварэння макраса.
Што такое макрас?
Макрас у Microsoft Office (так, гэтая функцыя аднолькава працуе ў многіх праграмах пакета Microsoft Office) - гэта праграмны код на мове праграмавання Visual Basic для прыкладанняў (VBA), якія захоўваюцца ўнутры дакумента. Каб было больш зразумела, дакумент Microsoft Office можна параўнаць са старонкай HTML, тады макрас - гэта аналаг Javascript. Тое, што Javascript можа рабіць з дадзенымі HTML на вэб-старонцы, вельмі падобна на тое, што можа рабіць макрас з дадзенымі ў дакуменце Microsoft Office.
Макрасы могуць рабіць у дакуменце практычна ўсё, што заўгодна. Вось некаторыя з іх (вельмі малая частка):
- Прымяніць стылі і фарматаванне.
- Выкананне розных аперацый з лікавымі і тэкставымі дадзенымі.
- Выкарыстоўвайце знешнія крыніцы даных (файлы баз даных, тэкставыя дакументы і г.д.)
- Стварыце новы дакумент.
- Выканайце ўсё вышэйпералічанае ў любой камбінацыі.
Стварэнне макраса – практычны прыклад
Для прыкладу возьмем самы звычайны файл CSV. Гэта простая табліца 10×20, запоўненая лічбамі ад 0 да 100 з загалоўкамі для слупкоў і радкоў. Наша задача складаецца ў тым, каб ператварыць гэты набор даных у прадстаўлена адфарматаваную табліцу і згенераваць вынікі ў кожным радку.
Як ужо згадвалася, макрас - гэта код, напісаны на мове праграмавання VBA. Але ў Excel можна стварыць праграму без напісання радка кода, што мы і зробім прама зараз.
Каб стварыць макрас, адкрыйце выгляд (Тып) > Макрас (Макра) > Запіс макраса (Макразапіс...)
Дайце макрасу імя (без прабелаў) і націсніце OK.
З гэтага моманту рэгіструюцца ЎСЕ вашы дзеянні з дакументам: змены вочак, пракрутка табліцы, нават змяненне памеру акна.
Пра тое, што рэжым запісу макрасаў уключаны, Excel сігналізуе ў двух месцах. Па-першае, па меню Макрас (Макрасы) – замест радка Запіс макраса З'явіўся радок (Запіс макраса...). спыніць запіс (Спыніць запіс).
Па-другое, у левым ніжнім куце акна Excel. Значок Стоп (маленькі квадрат) паказвае, што ўключаны рэжым запісу макрасаў. Націск на яго спыніць запіс. І наадварот, калі рэжым запісу не ўключаны, у гэтым месцы ёсць значок для ўключэння запісу макрасаў. Націск на яе дасць той жа вынік, што і ўключэнне запісу праз меню.
Цяпер, калі рэжым запісу макрасаў уключаны, пяройдзем да нашай задачы. Перш за ўсё, давайце дадамо загалоўкі для зводных дадзеных.
Далей увядзіце ў вочкі формулы ў адпаведнасці з назвамі загалоўкаў (прыводзяцца варыянты формул для ангельскай і версій Excel, адрасы вочак - заўсёды лацінскімі літарамі і лічбамі):
- =СУМ(B2:K2) or =СУМ(B2:K2)
- =СЯРЭДНЯЕ(B2:K2) or =СРЗНАЧ(B2:K2)
- =MIN(B2:K2) or =MIN(B2:K2)
- =МАКС(В2:К2) or =МАКС(В2:К2)
- =МЕДЫЯНА(B2:K2) or =МЕДЫЯНА(B2:K2)
Цяпер вылучыце вочкі з формуламі і скапіруйце іх ва ўсе радкі нашай табліцы, перацягнуўшы ручку аўтазапаўнення.
Пасля завяршэння гэтага кроку кожны радок павінен мець адпаведныя вынікі.
Далей падвядзем вынікі па ўсёй табліцы, для гэтага праробім яшчэ некалькі матэматычных дзеянняў:
Адпаведна:
- =СУМ(L2:L21) or =СУМ(L2:L21)
- =СЯРЭДНЯЕ(B2:K21) or =СРЗНАЧ(B2:K21) – каб разлічыць гэтую велічыню, неабходна ўзяць менавіта зыходныя дадзеныя табліцы. Калі ўзяць сярэдняе з сярэдніх для асобных радкоў, вынік будзе іншым.
- =MIN(N2:N21) or =MIN(N2:N21)
- =МАКС(O2:O21) or =МАКС(O2:O21)
- =МЕДЫЯНА(B2:K21) or =МЕДЫЯНА(B2:K21) – мы лічым выкарыстанне зыходных дадзеных табліцы, па прычыне, пазначанай вышэй.
Цяпер, калі мы скончылі з разлікамі, давайце правядзем фарматаванне. Спачатку ўсталюем аднолькавы фармат адлюстравання даных для ўсіх вочак. Вылучыце ўсе вочкі на лісце, для гэтага скарыстайцеся спалучэннем клавіш Ctrl + A,або націсніце на значок выбраць усе, які знаходзіцца на скрыжаванні загалоўкаў радкоў і слупкоў. Затым націсніце Стыль коскі Укладка (Фармат з падзельнікамі). Галоўная (Дома).
Далей зменіце знешні выгляд загалоўкаў слупкоў і радкоў:
- Тлусты шрыфт.
- Выраўноўванне па цэнтры.
- Каляровая заліўка.
І, нарэшце, давайце ўсталюем фармат агульных вынікаў.
Вось як гэта павінна выглядаць у выніку:
Калі вас усё задавальняе, спыніце запіс макраса.
Віншую! Вы толькі што самі запісалі свой першы макрас у Excel.
Каб выкарыстоўваць згенераваны макрас, нам трэба захаваць дакумент Excel у фармаце, які падтрымлівае макрасы. Спачатку нам трэба выдаліць усе дадзеныя з табліцы, якую мы стварылі, г.зн. зрабіць яе пустым шаблонам. Справа ў тым, што ў далейшым, працуючы з гэтым шаблонам, мы будзем імпартаваць у яго самыя свежыя і актуальныя даныя.
Каб ачысціць усе вочкі ад дадзеных, пстрыкніце па значку правай кнопкай мышы выбраць усе, які знаходзіцца на скрыжаванні загалоўкаў радкоў і слупкоў, і з кантэкстнага меню абярыце Выдаляць (Выдаліць).
Цяпер наш ліст цалкам ачышчаны ад усіх дадзеных, а макрас застаецца запісаным. Нам трэба захаваць кнігу як шаблон Excel з падтрымкай макрасаў, які мае пашырэнне XLTM.
Важны момант! Калі вы захаваеце файл з пашырэннем XLTX, то макрас не будзе працаваць у ім. Дарэчы, вы можаце захаваць кнігу як шаблон Excel 97-2003, які мае фармат XLT, ён таксама падтрымлівае макрасы.
Калі шаблон будзе захаваны, вы можаце смела закрываць Excel.
Запуск макраса ў Excel
Перш чым раскрыць усе магчымасці створанага вамі макраса, лічу правільным звярнуць увагу на пару важных момантаў, якія тычацца макрасаў у цэлым:
- Макрасы могуць быць шкоднымі.
- Прачытайце папярэдні абзац яшчэ раз.
Код VBA вельмі магутны. У прыватнасці, ён можа выконваць аперацыі з файламі па-за межамі бягучага дакумента. Напрыклад, макрас можа выдаляць або змяняць любыя файлы ў тэчцы Мае дакументы. Па гэтай прычыне запускайце і дазваляйце толькі макрасы з крыніц, якім вы давяраеце.
Каб запусціць наш макрас фарматавання даных, адкрыйце файл шаблона, які мы стварылі ў першай частцы гэтага падручніка. Калі ў вас стандартныя налады бяспекі, то пры адкрыцці файла над табліцай з'явіцца папярэджанне аб тым, што макрасы адключаныя, і кнопка іх уключэння. Так як мы зрабілі шаблон самі і мы давяраем сабе, мы націскаем кнопку ўключыць змесціва (Уключыць змест).
Наступным крокам з'яўляецца імпарт апошняга абноўленага набору даных з файла CSV (на аснове такога файла мы стварылі наш макрас).
Калі вы імпартуеце даныя з файла CSV, Excel можа папрасіць вас наладзіць некаторыя параметры, каб правільна перанесці даныя ў табліцу.
Пасля завяршэння імпарту перайдзіце ў меню Макрас Укладка (Макрасы). выгляд (Прагляд) і абярыце каманду Прагляд макрасаў (Макра).
У якое адкрылася дыялогавым акне мы ўбачым радок з назвай нашага макраса FormatData. Выберыце яго і націсніце прагон (Выканаць).
Калі макрас пачне працаваць, вы ўбачыце таблічны курсор, які пераскоквае з ячэйкі ў ячэйку. Праз некалькі секунд з дадзенымі будуць зроблены тыя ж аперацыі, што і пры запісе макраса. Калі ўсё гатова, табліца павінна выглядаць гэтак жа, як арыгінал, які мы адфарматавалі ўручную, толькі з іншымі дадзенымі ў вочках.
Давайце зазірнем пад капот: як працуе макрас?
Як ужо не раз гаварылася, макрас - гэта праграмны код на мове праграмавання. Visual Basic для прыкладанняў (VBA). Калі вы ўключаеце рэжым запісу макрасаў, Excel фактычна запісвае кожнае ваша дзеянне ў выглядзе інструкцый VBA. Прасцей кажучы, Excel піша код для вас.
Каб убачыць гэты праграмны код, трэба ў меню Макрас Укладка (Макрасы). выгляд (праглядзець) націснуць Прагляд макрасаў (Макрасы) і ў дыялогавым акне, якое адкрыецца, націсніце Рэдагаваць (Змена).
Акно адчыняецца. Visual Basic для прыкладанняў, у якім мы ўбачым праграмны код макраса, які мы запісалі. Так, вы правільна зразумелі, тут вы можаце змяніць гэты код і нават стварыць новы макрас. Дзеянні, якія мы выконвалі з табліцай у гэтым уроку, можна запісаць з дапамогай аўтаматычнага запісу макрасаў у Excel. Але больш складаныя макрасы з дакладна настроенай паслядоўнасцю і логікай дзеянняў патрабуюць ручнога праграмавання.
Давайце дадамо яшчэ адзін крок да нашай задачы…
Уявіце, што наш зыходны файл дадзеных дадзеныя.csv ствараецца аўтаматычна нейкім працэсам і заўсёды захоўваецца на дыску ў адным і тым жа месцы. Напрыклад, C:Datadata.csv – шлях да файла з абноўленымі дадзенымі. Працэс адкрыцця гэтага файла і імпартавання даных з яго таксама можна запісаць у макрас:
- Адкрыйце файл шаблону, у якім мы захавалі макрас − FormatData.
- Стварыце новы макрас з імем Загрузіць даныя.
- Падчас запісу макраса Загрузіць даныя імпарт дадзеных з файла дадзеныя.csv – як і ў папярэдняй частцы ўрока.
- Калі імпарт будзе завершаны, спыніце запіс макраса.
- Выдаліць усе дадзеныя з вочак.
- Захавайце файл як шаблон Excel з падтрымкай макрасаў (пашырэнне XLTM).
Такім чынам, запусціўшы гэты шаблон, вы атрымліваеце доступ да двух макрасаў - адзін загружае дадзеныя, другі іх фарматуе.
Калі вы хочаце паглыбіцца ў праграмаванне, вы можаце аб'яднаць дзеянні гэтых двух макрасаў у адзін - проста скапіраваўшы код з Загрузіць даныя да пачатку кода FormatData.