Nilalaman
Ang Excel ay may isang malakas, ngunit sa parehong oras ay napakabihirang ginagamit, ang kakayahang lumikha ng mga awtomatikong pagkakasunud-sunod ng mga aksyon gamit ang mga macro. Ang isang macro ay isang mainam na paraan kung nakikitungo ka sa parehong uri ng gawain na inuulit nang maraming beses. Halimbawa, pagproseso ng data o pag-format ng dokumento ayon sa isang standardized na template. Sa kasong ito, hindi mo kailangan ng kaalaman sa mga programming language.
Curious ka na ba tungkol sa kung ano ang isang macro at kung paano ito gumagana? Pagkatapos ay buong tapang na magpatuloy - pagkatapos ay gagawin namin ang buong proseso ng paggawa ng macro sa iyo.
Ano ang Macro?
Ang macro sa Microsoft Office (oo, gumagana ang functionality na ito sa maraming application ng Microsoft Office package) ay isang program code sa isang programming language Visual Basic para sa Mga Aplikasyon (VBA) na nakaimbak sa loob ng dokumento. Upang gawing mas malinaw, ang isang dokumento ng Microsoft Office ay maaaring ihambing sa isang HTML na pahina, pagkatapos ang isang macro ay isang analogue ng Javascript. Ang magagawa ng Javascript sa HTML na data sa isang web page ay halos kapareho sa kung ano ang magagawa ng macro sa data sa isang dokumento ng Microsoft Office.
Magagawa ng mga macro ang halos anumang bagay na gusto mo sa isang dokumento. Narito ang ilan sa mga ito (isang napakaliit na bahagi):
- Ilapat ang mga istilo at pag-format.
- Magsagawa ng iba't ibang operasyon gamit ang numeric at text data.
- Gumamit ng external na data source (database file, text documents, atbp.)
- Lumikha ng isang bagong dokumento.
- Gawin ang lahat ng nasa itaas sa anumang kumbinasyon.
Paglikha ng isang macro - isang praktikal na halimbawa
Halimbawa, kunin natin ang pinakakaraniwang file CSV. Ito ay isang simpleng 10×20 table na puno ng mga numero mula 0 hanggang 100 na may mga heading para sa mga column at row. Ang aming gawain ay gawing isang kasalukuyang naka-format na talahanayan ang set ng data na ito at bumuo ng mga kabuuan sa bawat hilera.
Gaya ng nabanggit na, ang macro ay code na nakasulat sa VBA programming language. Ngunit sa Excel, maaari kang lumikha ng isang programa nang hindi sumusulat ng isang linya ng code, na gagawin namin ngayon.
Para gumawa ng macro, buksan tingnan (Uri) > Macros (Macro) > Itala ang Macro (Macro recording...)
Bigyan ng pangalan ang iyong macro (walang mga puwang) at i-click OK.
Simula sa sandaling ito, LAHAT ng iyong mga aksyon kasama ang dokumento ay naitala: mga pagbabago sa mga cell, pag-scroll sa talahanayan, kahit na baguhin ang laki ng window.
Ang Excel ay nagpapahiwatig na ang macro recording mode ay pinagana sa dalawang lugar. Una, sa menu Macros (Macros) – sa halip na isang string Itala ang Macro (Nagre-record ng macro…) na linya ang lumitaw Itigil ang Pagre-record (Ihinto ang pagre-record).
Pangalawa, sa ibabang kaliwang sulok ng Excel window. Icon Itigil (maliit na parisukat) ay nagpapahiwatig na ang macro recording mode ay pinagana. Ang pag-click dito ay hihinto sa pagre-record. Sa kabaligtaran, kapag ang recording mode ay hindi pinagana, mayroong isang icon upang paganahin ang macro recording sa lokasyong ito. Ang pag-click dito ay magbibigay ng parehong resulta tulad ng pag-on sa pag-record sa pamamagitan ng menu.
Ngayong naka-enable na ang macro recording mode, punta tayo sa ating gawain. Una sa lahat, magdagdag tayo ng mga header para sa buod ng data.
Susunod, ipasok ang mga formula sa mga cell alinsunod sa mga pangalan ng mga heading (ibinigay ang mga variant ng mga formula para sa Ingles at mga bersyon ng Excel, ang mga cell address ay palaging mga Latin na titik at numero):
- =SUM(B2:K2) or =SUM(B2:K2)
- =AVERAGE(B2:K2) or =СРЗНАЧ(B2:K2)
- =MIN(B2:K2) or =MIN(B2:K2)
- =MAX(B2:K2) or =MAX(B2:K2)
- =MEDIAN(B2:K2) or =MEDIAN(B2:K2)
Piliin ngayon ang mga cell na may mga formula at kopyahin ang mga ito sa lahat ng row ng aming talahanayan sa pamamagitan ng pag-drag sa hawakan ng autofill.
Pagkatapos mong kumpletuhin ang hakbang na ito, ang bawat row ay dapat magkaroon ng katumbas na kabuuan.
Susunod, ibubuod namin ang mga resulta para sa buong talahanayan, para dito gumawa kami ng ilang higit pang mga pagpapatakbo sa matematika:
Ayon sa pagkakabanggit:
- =SUM(L2:L21) or =SUM(L2:L21)
- =AVERAGE(B2:K21) or =СРЗНАЧ(B2:K21) – upang kalkulahin ang halagang ito, kinakailangang kunin nang eksakto ang paunang data ng talahanayan. Kung kukunin mo ang average ng mga average para sa mga indibidwal na row, mag-iiba ang resulta.
- =MIN(N2:N21) or =MIN(N2:N21)
- =MAX(O2:O21) or =MAX(O2:O21)
- =MEDIAN(B2:K21) or =MEDIAN(B2:K21) – isinasaalang-alang namin ang paggamit ng paunang data ng talahanayan, para sa kadahilanang ipinahiwatig sa itaas.
Ngayong tapos na tayo sa mga kalkulasyon, gawin natin ang ilang pag-format. Una, itakda natin ang parehong format ng pagpapakita ng data para sa lahat ng mga cell. Piliin ang lahat ng mga cell sa sheet, para gawin ito, gamitin ang keyboard shortcut Ctrl + Ao mag-click sa icon Piliin ang lahat ng, na matatagpuan sa intersection ng mga heading ng row at column. Pagkatapos ay i-click Comma Style (Delimited Format) na tab Tahanan (Tahan).
Susunod, baguhin ang hitsura ng mga header ng column at row:
- Naka-bold na istilo ng font.
- Pag-align sa gitna.
- Punan ng kulay.
At sa wakas, i-set up natin ang format ng mga kabuuan.
Ganito dapat ang hitsura nito sa huli:
Kung nababagay sa iyo ang lahat, itigil ang pagre-record ng macro.
Binabati kita! Kaka-record mo lang ng iyong unang macro sa Excel.
Upang magamit ang nabuong macro, kailangan naming i-save ang Excel na dokumento sa isang format na sumusuporta sa mga macro. Una, kailangan naming tanggalin ang lahat ng data mula sa talahanayan na aming nilikha, ibig sabihin, gawin itong isang walang laman na template. Ang katotohanan ay sa hinaharap, nagtatrabaho sa template na ito, i-import namin ang pinakabago at may-katuturang data dito.
Upang i-clear ang lahat ng mga cell mula sa data, i-right-click sa icon Piliin ang lahat ng, na matatagpuan sa intersection ng mga heading ng row at column, at mula sa menu ng konteksto, piliin alisin (Burahin).
Ngayon ang aming sheet ay ganap na na-clear ng lahat ng data, habang ang macro ay nananatiling naitala. Kailangan naming i-save ang workbook bilang isang macro-enabled na Excel template na may extension XLTM.
Isang mahalagang punto! Kung ise-save mo ang file na may extension XLTX, kung gayon ang macro ay hindi gagana dito. Sa pamamagitan ng paraan, maaari mong i-save ang workbook bilang isang template ng Excel 97-2003, na may format XLT, sinusuportahan din nito ang mga macro.
Kapag na-save ang template, maaari mong ligtas na isara ang Excel.
Pagpapatakbo ng Macro sa Excel
Bago ibunyag ang lahat ng mga posibilidad ng macro na iyong nilikha, sa palagay ko ay tama na bigyang pansin ang ilang mahahalagang punto tungkol sa mga macro sa pangkalahatan:
- Ang mga macro ay maaaring makapinsala.
- Basahin muli ang nakaraang talata.
Ang VBA code ay napakalakas. Sa partikular, maaari itong magsagawa ng mga operasyon sa mga file sa labas ng kasalukuyang dokumento. Halimbawa, maaaring tanggalin o baguhin ng macro ang anumang mga file sa isang folder Aking mga dokumento. Para sa kadahilanang ito, tumakbo lang at payagan ang mga macro mula sa mga pinagmumulan na pinagkakatiwalaan mo.
Upang patakbuhin ang aming macro sa pag-format ng data, buksan ang template file na ginawa namin sa unang bahagi ng tutorial na ito. Kung mayroon kang karaniwang mga setting ng seguridad, pagkatapos ay kapag nagbukas ka ng isang file, isang babala ang lalabas sa itaas ng talahanayan na ang mga macro ay hindi pinagana, at isang pindutan upang paganahin ang mga ito. Dahil kami mismo ang gumawa ng template at nagtitiwala kami sa aming sarili, pinindot namin ang button Paganahin ang nilalaman (Isama ang nilalaman).
Ang susunod na hakbang ay ang pag-import ng pinakabagong na-update na dataset mula sa file CSV (batay sa naturang file, nilikha namin ang aming macro).
Kapag nag-import ka ng data mula sa isang CSV file, maaaring hilingin sa iyo ng Excel na mag-set up ng ilang setting upang mailipat nang tama ang data sa talahanayan.
Kapag tapos na ang pag-import, pumunta sa menu Macros (Macros) na tab tingnan (View) at pumili ng command Tingnan ang Macros (Macro).
Sa dialog box na bubukas, makakakita tayo ng linya na may pangalan ng ating macro FormatData. Piliin ito at i-click Tumakbo (Ipatupad).
Kapag nagsimulang tumakbo ang macro, makikita mo ang table cursor na tumatalon mula sa cell patungo sa cell. Pagkatapos ng ilang segundo, ang parehong mga operasyon ay gagawin sa data tulad ng kapag nagre-record ng isang macro. Kapag handa na ang lahat, ang talahanayan ay dapat magmukhang kapareho ng orihinal na na-format namin sa pamamagitan ng kamay, na may iba't ibang data lamang sa mga cell.
Tingnan natin sa ilalim ng talukbong: Paano gumagana ang isang macro?
Tulad ng nabanggit nang higit sa isang beses, ang macro ay program code sa isang programming language. Visual Basic para sa Mga Aplikasyon (VBA). Kapag in-on mo ang macro recording mode, talagang nire-record ng Excel ang bawat aksyon na gagawin mo sa anyo ng mga tagubilin sa VBA. Sa madaling salita, isusulat ng Excel ang code para sa iyo.
Upang makita ang program code na ito, kailangan mo sa menu Macros (Macros) na tab tingnan (tingnan) i-click Tingnan ang Macros (Macros) at sa dialog box na bubukas, i-click Edit (Baguhin).
Bumukas ang bintana. Visual Basic para sa Mga Aplikasyon, kung saan makikita natin ang program code ng macro na naitala natin. Oo, naunawaan mo nang tama, dito maaari mong baguhin ang code na ito at kahit na lumikha ng isang bagong macro. Ang mga aksyon na ginawa namin kasama ang talahanayan sa araling ito ay maaaring maitala gamit ang awtomatikong macro recording sa Excel. Ngunit ang mas kumplikadong mga macro, na may pinong nakatutok na pagkakasunud-sunod at lohika ng pagkilos, ay nangangailangan ng manu-manong programming.
Magdagdag pa tayo ng isa pang hakbang sa ating gawain...
Isipin na ang aming orihinal na file ng data data.csv ay awtomatikong nilikha ng ilang proseso at palaging nakaimbak sa disk sa parehong lugar. Halimbawa, C:Datadata.csv – landas patungo sa file na may na-update na data. Ang proseso ng pagbubukas ng file na ito at pag-import ng data mula dito ay maaari ding i-record sa isang macro:
- Buksan ang template file kung saan namin na-save ang macro − FormatData.
- Gumawa ng bagong macro na pinangalanan LoadData.
- Habang nagre-record ng macro LoadData mag-import ng data mula sa file data.csv – tulad ng ginawa natin sa nakaraang bahagi ng aralin.
- Kapag kumpleto na ang pag-import, ihinto ang pagre-record ng macro.
- Tanggalin ang lahat ng data mula sa mga cell.
- I-save ang file bilang isang macro-enabled na Excel template (XLTM extension).
Kaya, sa pamamagitan ng pagpapatakbo ng template na ito, makakakuha ka ng access sa dalawang macro - ang isa ay naglo-load ng data, ang isa ay nag-format sa kanila.
Kung gusto mong makapasok sa programming, maaari mong pagsamahin ang mga aksyon ng dalawang macro na ito sa isa - sa pamamagitan lamang ng pagkopya ng code mula sa LoadData sa simula ng code FormatData.