Naučite se uporabljati Excelove makre za avtomatizacijo zahtevnih nalog

Ena od močnejših, a redko uporabljenih funkcij Excela je zmožnost zelo preproste izdelave avtomatskih nalog in logike po meri v makrih. Makri zagotavljajo idealen način za prihranek časa na predvidljivih, ponavljajočih se nalogah in standardizacijo formatov dokumentov - večkrat brez pisanja ene vrstice kode.

Če ste radovedni, kaj so makri ali kako jih dejansko ustvariti, ni problema - povedali vas bomo skozi celoten proces.

Opomba: isti postopek bi moral delovati v večini različic Microsoft Officea. Posnetki zaslona lahko izgledajo nekoliko drugače.

Kaj je makro?

Microsoft Office Macro (ker ta funkcionalnost velja za več aplikacij MS Office) je preprosto koda Visual Basic for Applications (VBA), shranjena v dokumentu. Za primerljivo analogijo, razmislite o dokumentu kot HTML in makro kot Javascript. Na podoben način, kot lahko Javascript manipulira HTML na spletni strani, lahko makro manipulira z dokumentom.

Makri so neverjetno zmogljivi in ​​lahko naredijo kar koli, kar si lahko domišljijo zaželite. Kot (zelo) kratek seznam funkcij, ki jih lahko naredite z makrom:

• Uporabi slog in oblikovanje.
• Manipulirajte podatke in besedilo.
• Komuniciranje z viri podatkov (podatkovna zbirka, besedilne datoteke itd.).
• Ustvarite popolnoma nove dokumente.
• Kakršna koli kombinacija katerega koli zgoraj navedenega.

Ustvarjanje makra: Razlaga po primeru

Začnemo z vašo CSV datoteko z vrtom. Nič posebnega tukaj, samo 10 × 20 niz številk med 0 in 100 z obema vrsticama in glavo stolpca. Naš cilj je izdelati dobro oblikovan in pregleden podatkovni list, ki vključuje povzetke za vsako vrstico.

Kot smo že omenili, je makro koda VBA, a ena izmed lepih stvari v Excelu je, da jih lahko ustvarite / zapišete z zahtevano kodo nič - kot bomo to storili tukaj.

Če želite ustvariti makro, pojdite na Pogled> Makri> Snemaj makro.

Dodelite makro ime (brez presledkov) in kliknite V redu.

Ko je to storjeno, vse vaših dejanj je zabeleženih - vsaka sprememba v celici, akcija drsenja, spreminjanje velikosti okna, ime.

Obstaja nekaj mest, ki kažejo, da je Excel način zapisa. Eden je tako, da si ogledate meni Macro (Makro) in ugotovite, da je zaustavitev snemanja nadomestila možnost za Macro zapis.

Drugi je v spodnjem desnem kotu. Ikona 'stop' označuje, da je v makro načinu, in če pritisnete tukaj, se snemanje ustavi (prav tako, ko ni v načinu snemanja, bo ta ikona gumb Makro zapisa, ki ga lahko uporabite namesto, da greste v meni Makri).

Zdaj, ko snemamo svoj makro, uporabimo naše povzetke izračunov. Najprej dodajte glave.

Nato uporabite ustrezne formule ():

• = SUM (B2: K2)
• = POVPREČINA (B2: K2)
• = MIN (B2: K2)
• = MAX (B2: K2)
• = MEDIAN (B2: K2)

Zdaj označite vse izračunske celice in povlecite dolžino vseh naših podatkovnih vrstic, da uporabite izračune za vsako vrstico.

Ko je to opravljeno, mora vsaka vrstica prikazati njihove povzetke.

Zdaj želimo dobiti povzetek podatkov za celoten list, zato uporabimo še nekaj izračunov:

Ustrezno:

• = SUM (L2: L21)
• = POVPREČINA (B2: K21) *To je treba izračunati za vse podatke, ker povprečje povprečij vrstic ni nujno enako povprečju vseh vrednosti.
• = MIN (N2: N21)
• = MAX (O2: O21)
• = MEDIAN (B2: K21) * Izračuna se za vse podatke iz istega razloga kot zgoraj.

Zdaj, ko so izračuni opravljeni, bomo uporabili slog in oblikovanje. Najprej uporabite splošno oblikovanje številk v vseh celicah, tako da izberete možnost Izberi vse (Ctrl + A ali kliknite celico med glavnimi vrsticami in stolpci) in v meniju Domov izberite ikono »Slog za vejico«..

Nato uporabite vizualno oblikovanje za glave vrstic in stolpcev:

• Krepko.
• Na sredini.
• Barva polnila v ozadju.

In končno, uporabite nekaj stila za vsote.

Ko je vse končano, je videti tako naš podatkovni list:

Ker smo z rezultati zadovoljni, zaustavimo snemanje makra.

Čestitamo - pravkar ste ustvarili makro programa Excel.

Da bi lahko uporabili naš nov posneti makro, moramo shraniti Excelov delovni zvezek v obliki zapisa, ki omogoča makro. Preden pa to storimo, moramo najprej počistiti vse obstoječe podatke, da ne bo vdelana v našo predlogo (zamisel bo vsakič, ko uporabimo to predlogo, uvozili najnovejše podatke).

To naredite tako, da izberete vse celice in jih izbrišete.

Z izbrisom podatkov (vendar makrov, ki so še vedno vključeni v Excelovo datoteko), želimo datoteko shraniti kot datoteko s predlogo za makro (XLTM). Pomembno je opozoriti, da če shranite to kot standardno predlogo (XLTX), potem bodo makri ne teči od njega. Datoteko lahko shranite tudi kot datoteko s podedovano predlogo (XLT), ki bo omogočala zagon makrov.

Ko datoteko shranite kot predlogo, nadaljujte in zaprite Excel.

Uporaba Excelovega makra

Preden pokrijemo, kako lahko uporabimo ta novo posneti makro, je pomembno pokriti nekaj točk o makrih na splošno:

• Makri so lahko zlonamerni.
• Glej zgoraj.

VBA koda je pravzaprav zelo močna in lahko manipulira datoteke zunaj obsega trenutnega dokumenta. Makro lahko na primer spremeni ali izbriše naključne datoteke v mapi »Moji dokumenti«. Zato je pomembno, da se prepričate samo zagnati makre iz zaupanja vrednih virov.

Če želite uporabiti makro oblike podatkov, odprite datoteko Excelove predloge, ki je bila ustvarjena zgoraj. Ko to storite, ob predpostavki, da imate omogočene standardne varnostne nastavitve, boste na vrhu delovnega zvezka videli opozorilo, ki pravi, da so makri onemogočeni. Ker zaupamo makru, ki smo ga ustvarili sami, kliknite gumb »Omogoči vsebino«.

Naslednje bomo uvozili najnovejše podatkovne nize iz CSV-ja (to je vir, ki ga je delovni list uporabil za ustvarjanje našega makra).

Če želite dokončati uvoz datoteke CSV, boste morda morali nastaviti nekaj možnosti, da bo Excel pravilno interpretiral (npr. Ločilo, prisotna glava itd.).

Ko so naši podatki uvoženi, preprosto pojdite v meni Makri (na zavihku Pogled) in izberite Ogled makrov.

V nastalem pogovornem oknu se prikaže makro »FormatData«, ki smo ga zabeležili zgoraj. Izberite ga in kliknite Zaženi.

Ko ste zagnali, boste morda videli, da se kazalec premakne za nekaj trenutkov, vendar boste videli, da so podatki manipulirani natančno kot smo ga posneli. Ko je vse povedano in storjeno, bi moralo izgledati tako kot naš izvirnik - razen z različnimi podatki.

Pogled pod napetostjo: kaj naredi makro delo

Kot smo že omenili nekajkrat, makro poganja koda Visual Basic for Applications (VBA). Ko zapišete makro, Excel dejansko prevede vse, kar počnete, v svoja navodila VBA. Preprosto povedano - vam ni treba pisati nobene kode, ker Excel piše kodo za vas.

Če si želite ogledati kodo, ki omogoča izvajanje našega makra, v pogovornem oknu Makri kliknite gumb Uredi.

Okno, ki se odpre, prikazuje izvorno kodo, ki je bila zabeležena pri naših dejanjih pri ustvarjanju makra. Seveda lahko to kodo uredite ali celo ustvarite nove makre v celoti znotraj okna kode. Medtem ko bo snemanje, uporabljeno v tem članku, verjetno ustrezalo večini potreb, bodo za bolj prilagojene akcije ali pogojne akcije morali urediti izvorno kodo.

Vzemimo naš primer Korak dlje ...

Hipotetično predpostavimo, da je naša izvorna podatkovna datoteka data.csv izdelana z avtomatiziranim procesom, ki datoteko vedno shrani na isto mesto (npr. C: Data.csv je vedno najnovejši podatek). Postopek odpiranja te datoteke in uvažanje je mogoče preprosto izdelati tudi v makru:

1. Odprite datoteko Excelove predloge, ki vsebuje makro »FormatData«.
2. Posnemite nov makro z imenom »LoadData«.
3. S snemanjem makra uvozite podatkovno datoteko kot običajno.
4. Ko so podatki uvoženi, ustavite snemanje makra.
5. Izbrišite vse podatke o celici (izberite vse, nato izbrišite).
6. Shranite posodobljeno predlogo (ne pozabite uporabiti oblike predloge, ki jo omogoči makro).

Ko je to storjeno, bodo ob odprtju predloge dva makra - enega, ki nalaga naše podatke, in drugega, ki ga formatira.

Če ste resnično želeli umazati roke z malo urejanja kode, lahko preprosto združite ta dejanja v en makro tako, da kopirate kodo, ustvarjeno iz »LoadData« in jo vstavite na začetek kode »FormatData«.

Prenesite to predlogo

Za vaše udobje smo vključili tako Excelovo predlogo, izdelano v tem članku, kot tudi vzorčno podatkovno datoteko, s katero se lahko igrate.

Prenesite Excelovo makro predlogo iz How-To Geek