Naučite se uporabljati Excelove makre za avtomatizacijo zamudnih opravil

Ena najmočnejših, a redko uporabljenih funkcij Excela je zmožnost zelo enostavnega ustvarjanja avtomatiziranih nalog in logike po meri znotraj makrov. Makri ponujajo idealen način za prihranek časa pri predvidljivih, ponavljajočih se opravilih in standardizaciji formatov dokumentov - večkrat, ne da bi morali pisati eno vrstico kode.

Če vas zanima, kaj so makri ali kako jih dejansko ustvariti, ni problema - vodili vas bomo skozi celoten postopek.

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

Kaj je makro?

Makro Microsoft Office (ker ta funkcija velja za več aplikacij MS Office) je preprosto koda Visual Basic for Applications (VBA), shranjena v dokumentu. Za primerljivo analogijo si zamislite dokument kot HTML, makro pa kot Javascript. Na podoben način, kot lahko Javascript manipulira s HTML na spletni strani, lahko makro manipulira z dokumentom.

Makroi so neverjetno zmogljivi in ​​zmorejo skoraj vse, kar vam lahko pričara vaša domišljija. Kot (zelo) kratek seznam funkcij, ki jih lahko naredite z makrom:

  • Uporabi slog in oblikovanje.
  • Manipuliranje s podatki in besedilom.
  • Komunicirajte z viri podatkov (baza podatkov, besedilne datoteke itd.).
  • Ustvarite povsem nove dokumente.
  • Vsaka kombinacija katerega koli od zgoraj navedenih v katerem koli vrstnem redu.

Ustvarjanje makra: razlaga na primeru

Začnemo z datoteko CSV za vašo vrtno sorto. Tu ni nič posebnega, le nabor številk 10 × 20 med 0 in 100 z glavo vrstice in stolpca. Naš cilj je izdelati dobro oblikovan, predstavljiv podatkovni list, ki vključuje povzetke za vsako vrstico.

Kot smo že omenili, je makro koda VBA, toda ena od lepih stvari v Excelu je, da jih lahko ustvarite / posnamete z ničelnim kodiranjem - kot bomo storili tukaj.

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

Makroju dodelite ime (brez presledkov) in kliknite V redu.

Ko je to končano, se vsa vaša dejanja zabeležijo - vsako spremembo celice, dejanje pomikanja, spreminjanje velikosti okna sami določite.

Obstaja nekaj krajev, ki kažejo, da je Excel način snemanja. Eno je tako, da si ogledate meni Makro in opozorite, da je možnost Ustavi snemanje nadomestila možnost Snemanje makra.

Drugi je v spodnjem desnem kotu. Ikona 'stop' pomeni, da je v makro načinu in s pritiskom tukaj zaustavite snemanje (podobno, kadar ni v načinu snemanja, bo ta ikona gumb Snemaj makro, ki ga lahko uporabite namesto da odprete meni Makri).

Zdaj, ko beležimo svoj makro, uporabimo naše skupne izračune. Najprej dodajte glave.

Nato uporabite ustrezne formule (oziroma):

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

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

Ko je to končano, mora vsaka vrstica prikazati svoje povzetke.

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

Ustrezno:

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

Zdaj, ko so izračuni končani, bomo uporabili slog in oblikovanje. Najprej uporabite splošno oblikovanje števil v vseh celicah, tako da naredite Izberi vse (bodisi Ctrl + A bodisi kliknite celico med glavami vrstic in stolpcev) in v meniju Domov izberite ikono »Slog vejice«.

Nato uporabite nekaj vizualnega oblikovanja za glave vrstic in stolpcev:

  • Krepko.
  • Središče.
  • Barva zapolnitve ozadja.

In končno, uporabite nekaj sloga za vsote.

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

Ker smo z rezultati zadovoljni, ustavite snemanje makra.

Čestitamo - pravkar ste ustvarili Excelov makro.

Če želimo uporabiti nov posneti makro, moramo shraniti Excelov delovni zvezek v datotečni obliki, ki omogoča makro. Preden pa to storimo, moramo najprej počistiti vse obstoječe podatke, tako da niso vdelani v našo predlogo (ideja je, da bomo vsakič, ko uporabimo to predlogo, uvozili najsodobnejše podatke).

Če želite to narediti, izberite vse celice in jih izbrišite.

Ko so podatki zdaj izbrisani (vendar so makri še vedno vključeni v datoteko Excel), želimo datoteko shraniti kot datoteko predloge z omogočenim makro (XLTM). Pomembno je omeniti, da če datoteko shranite kot datoteko s standardno predlogo (XLTX), makrov iz nje ne bo mogoče zagnati. Datoteko lahko shranite tudi kot datoteko starejše predloge (XLT), ki bo omogočila zagon makrov.

Ko datoteko shranite kot predlogo, nadaljujte in zaprite Excel.

Uporaba Excelovega makra

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

  • Makri so lahko zlonamerni.
  • Glej točko zgoraj.

Koda VBA je pravzaprav precej zmogljiva in lahko manipulira z datotekami zunaj obsega trenutnega dokumenta. Makro lahko na primer spremeni ali izbriše naključne datoteke v mapi Moji dokumenti. Kot tak je pomembno, da poskrbite, da makre izvajate samo iz zaupanja vrednih virov.

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

Naslednjič bomo iz CSV uvozili najnovejši nabor podatkov (to je vir, na katerem je delovni list ustvaril naš makro).

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

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

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

Ko zaženete, boste morda nekaj trenutkov videli, kako kazalka poskakuje, toda s tem boste videli podatke, s katerimi se manipulira natanko tako , kot smo jih zabeležili. Ko je vse rečeno in narejeno, mora biti videti tako kot naš original - razen z različnimi podatki.

Pogled pod pokrovom: kaj naredi makro delo

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

Če si želite ogledati kodo, zaradi katere se izvaja naš makro, v pogovornem oknu Makri kliknite gumb Uredi.

V oknu, ki se odpre, je prikazana izvorna koda, ki je bila posneta iz naših dejanj pri ustvarjanju makra. Seveda lahko to kodo uredite ali celo ustvarite nove makre v celoti znotraj okna kode. Čeprav bo dejanje snemanja, uporabljeno v tem članku, verjetno ustrezalo večini potreb, bodo bolj prilagojena dejanja ali pogojna dejanja zahtevala urejanje izvorne kode.

Če vzamemo naš primer korak naprej ...

Hipotetično predpostavimo, da je naša izvorna podatkovna datoteka data.csv izdelana z avtomatiziranim postopkom, ki datoteko vedno shrani na isto mesto (npr. C: \ Data \ data.csv so vedno najnovejši podatki). Postopek odpiranja in uvoza te datoteke je mogoče enostavno narediti tudi v makro:

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

Ko je to končano, bosta vsakič, ko se predloga odpre, na voljo dva makra - eden, ki naloži naše podatke, in drugi, ki jih formatira.

Če ste si res želeli umazati roke z malo urejanja kode, lahko ta dejanja enostavno združite v en makro, tako da kopirate kodo, izdelano iz "LoadData", in jo vstavite na začetek kode iz "FormatData".

Prenesite to predlogo

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

Prenesite Excel makro predlogo iz How-To Geek