Naučte sa používať makrá programu Excel na automatizáciu únavných úloh

Jednou z výkonnejších, ale zriedka používaných funkcií programu Excel je schopnosť veľmi ľahko vytvárať automatizované úlohy a vlastnú logiku v rámci makier. Makrá poskytujú ideálny spôsob, ako šetriť čas na predvídateľných, opakujúcich sa úlohách, ako aj štandardizovať formáty dokumentov - mnohokrát bez nutnosti napísať jediný riadok kódu.

Ak ste zvedaví, aké makrá sú alebo ako ich vlastne vytvárať, nie je problém - prejdeme vás celým procesom.

Poznámka: ten istý proces by mal fungovať vo väčšine verzií balíka Microsoft Office. Obrázky obrazovky môžu vyzerať trochu inak.

Čo je makro?

Makro Microsoft Office (pretože táto funkcia platí pre niekoľko aplikácií MS Office) je jednoducho kód jazyka Visual Basic for Applications (VBA) uložený v dokumente. Pre porovnateľnú analógiu si myslite na dokument ako HTML a makro ako Javascript. Podobne ako Javascript môže manipulovať s HTML na webovej stránke, makro môže manipulovať s dokumentom.

Makrá sú neuveriteľne silné a môžu robiť skoro všetko, čo vaša predstavivosť môže vyvolať. Ako (veľmi) krátky zoznam funkcií, ktorý môžete urobiť s makro:

• Použiť štýl a formátovanie.
• Manipulujte s údajmi a textom.
• Komunikujte so zdrojmi údajov (databáza, textové súbory atď.).
• Vytvorte úplne nové dokumenty.
• Akákoľvek kombinácia akéhokoľvek z vyššie uvedeného.

Vytvorenie makra: Vysvetlenie pomocou príkladu

Začíname so súborom CSV v záhrade. Nič tu zvláštne, len 10 × 20 sada čísel medzi 0 a 100 s hlavičkou riadku a stĺpca. Naším cieľom je vytvoriť dobre formátovaný, prezentovateľný údajový list, ktorý obsahuje sumárne súhrny pre každý riadok.

Ako sme uviedli vyššie, makro je VBA kód, ale jednou z pekných vecí o programe Excel je, že ich môžete vytvárať / nahrávať s nulovým kódovaním - ako to urobíme tu.

Ak chcete vytvoriť makro, prejdite na položku Zobraziť> Makrá> Nahrať makro.

Priraďte makro názov (bez medzery) a kliknite na tlačidlo OK.

Akonáhle sa to stane, všetko z vašich akcií sa zaznamenávajú - každá zmena bunky, akcia posúvania, zmena veľkosti okna, jej pomenujete.

Existuje niekoľko miest, ktoré označujú, že program Excel je režim záznamu. Jedným z nich je zobrazenie ponuky makra a poznámka, že Stop Recording nahradil možnosť Record Makro.

Druhý je v pravom dolnom rohu. Ikona "zastávka" označuje, že je v režime makra a stlačením tu zastavíte nahrávanie (podobne, ak nie je v režime záznamu, táto ikona bude tlačidlo Nahrávať makro, ktoré môžete použiť namiesto prechodu do ponuky Makrá).

Teraz, keď zaznamenávame naše makro, použite naše súhrnné výpočty. Najprv pridajte hlavičky.

Potom použite príslušné vzorce (resp.):

• = SUM (B2: K2)
• = Stredná (B2: K2)
• = MIN (B2: K2)
• = Max (B2: K2)
• = Stredná (B2: K2)

Teraz zvýraznite všetky výpočtové bunky a presuňte dĺžku všetkých našich dátových riadkov, aby ste použili výpočty na každý riadok.

Akonáhle sa to stane, každý riadok by mal zobraziť príslušné súhrny.

Teraz chceme získať súhrnné údaje pre celý hárok, takže použijeme ešte niekoľko výpočtov:

v tomto poradí:

• = SUM (L2: L21)
• = Priemerná (B2: K21) *Toto sa musí vypočítať pre všetky údaje, pretože priemerná priemerná hodnota riadkov nemusí byť nevyhnutne rovnaká ako priemer všetkých hodnôt.
• = MIN (N2: N21)
• = Max (O2: O21)
• = Stredná (B2: K21) * Vypočítané pre všetky údaje z rovnakého dôvodu ako vyššie.

Teraz, keď sa vykonajú výpočty, použijeme štýl a formátovanie. Najskôr použite všeobecné formátovanie čísiel vo všetkých bunkách tak, že vykonáte Vybrať všetko (buď Ctrl + A, alebo kliknete na bunku medzi hlavičkami riadkov a stĺpcov) a v ponuke Home zvolíte ikonu "Štýl štýlu".

Potom použite niektoré vizuálne formátovanie pre hlavičky riadkov a stĺpcov:

• smelý.
• sústredený.
• Farba výplne pozadia.

A napokon, aplikujte nejaký štýl na súčty.

Keď skončí všetko, vyzerá to takto:

Keďže sme spokojní s výsledkami, zastavte nahrávanie makra.

Blahoželáme - práve ste vytvorili makro programu Excel.

Aby sme mohli použiť naše novo zaznamenané makro, musíme uložiť náš pracovný zošit programu Excel vo formáte súborov so zapnutým makrom. Predtým, než to urobíme, najskôr musíme vymazať všetky existujúce údaje, aby neboli vložené do našej šablóny (myšlienka je, že pri každom použití tejto šablóny budeme importovať najaktuálnejšie dáta).

Ak to chcete urobiť, vyberte všetky bunky a odstráňte ich.

S údajmi, ktoré sú teraz vymazané (ale makrá sú stále súčasťou súboru programu Excel), chceme súbor uložiť ako šablónu so šablónou (XLTM). Je dôležité poznamenať, že ak uložíte tento súbor ako štandardný šablón (XLTX), budú to makrá nie byť schopný byť spustený z neho. Alternatívne môžete súbor uložiť ako súbor staršej šablóny (XLT), čo umožní spustenie makier.

Po uložení súboru ako šablóny pokračujte a zatvorte aplikáciu Excel.

Používanie makra programu Excel

Predtým, ako budeme môcť použiť toto novo zaznamenané makro, je dôležité pokryť niekoľko bodov o makroch vo všeobecnosti:

• Makrá môžu byť škodlivé.
• Pozri vyššie uvedený bod.

Kód VBA je skutočne dosť silný a môže manipulovať so súbormi mimo rozsahu aktuálneho dokumentu. Napríklad makro by mohlo zmeniť alebo vymazať náhodné súbory v priečinku Moje dokumenty. Preto je dôležité, aby ste sa uistili iba spustiť makrá z dôveryhodných zdrojov.

Ak chcete použiť makro formátu údajov, otvorte súbor šablóny programu Excel, ktorý bol vytvorený vyššie. Ak to urobíte, za predpokladu, že máte povolené štandardné nastavenia zabezpečenia, uvidíte upozornenie v hornej časti zošita, v ktorom sa uvádza, že makrá sú zakázané. Keďže dôverujeme makro, ktoré sme vytvorili, kliknite na tlačidlo "Povoliť obsah".

Nasledujúci krok budeme importovať najnovšiu sadu údajov z CSV (toto je zdroj, ktorý pracovný hárok použil na vytvorenie nášho makra).

Ak chcete dokončiť import súboru CSV, budete musieť nastaviť niekoľko možností, aby program Excel mohol správne interpretovať (napríklad oddeľovač, prítomné hlavičky atď.)..

Akonáhle sú naše údaje importované, stačí prejsť do ponuky Makrá (pod záložkou Zobraziť) a vybrať možnosť Zobraziť makrá.

V výslednom dialógovom okne vidíme makro "FormatData", ktoré sme zaznamenali vyššie. Vyberte ho a kliknite na položku Spustiť.

Po spustení sa môže na niekoľko okamihov zobraziť kurzor, ale ako to uvidíte, údaje sa budú manipulovať presne ako sme ju zaznamenali. Keď je všetko povedané a urobené, malo by to vyzerať ako náš originál - s výnimkou rôznych údajov.

Pri pohľade pod kapucňou: čo robí makro

Ako sme už spomenuli niekoľko krát, makro je riadené kódom Visual Basic for Applications (VBA). Keď "makro" zaznamenáte, Excel v skutočnosti prekladá všetko, čo robíte, do príslušných pokynov VBA. Aby ste to jednoducho povedali - nemusíte písať žiadny kód, pretože Excel napíše kód pre vás.

Ak chcete zobraziť kód, ktorý spúšťa naše makro, kliknite v dialógovom okne Makrá na tlačidlo Upraviť.

Otvorené okno zobrazí zdrojový kód, ktorý bol zaznamenaný z našich akcií pri vytváraní makra. Samozrejme, môžete tento kód upraviť alebo dokonca vytvoriť nové makrá úplne vnútri okna kódu. Zatiaľ čo nahrávacia akcia použitá v tomto článku bude pravdepodobne zodpovedať väčšine potrieb, viac prispôsobené akcie alebo podmienené akcie vyžadujú úpravu zdrojového kódu.

Vzhľadom na náš príklad o krok ďalej ...

Hypoteticky predpokladáme, že náš zdrojový dátový súbor, data.csv, je vytvorený automatizovaným procesom, ktorý vždy ukladá súbor do rovnakého umiestnenia (napr. C: \ Data \ data.csv je vždy najnovší údaj). Proces otvárania a importovania súboru možno ľahko urobiť aj v makre:

1. Otvorte súbor šablóny programu Excel obsahujúci naše makro "FormatData".
2. Zaznamenajte nové makro s názvom "LoadData".
3. Pri nahrávaní makra importujte dátový súbor tak, ako by ste normálne urobili.
4. Po importovaní údajov zastavte nahrávanie makra.
5. Odstráňte všetky údaje o bunkách (vyberte všetko a potom odstráňte).
6. Uložte aktualizovanú šablónu (nezabudnite použiť formát šablóny so zapnutým makrom).

Akonáhle sa to robí, vždy, keď je šablóna otvorená, budú dve makrá - jedna, ktorá načíta naše dáta a druhá, ktorá ju formátuje.

Ak ste naozaj chceli zašpiniť ruky trochou editácie kódu, mohli by ste jednoducho kombinovať tieto akcie do jedného makra kopírovaním kódu vytvoreného z "LoadData" a jeho vložením na začiatok kódu z "FormatData".

Stiahnite si túto šablónu

Pre vaše pohodlie sme zaradili ako šablónu Excel vyrobenú v tomto článku, tak vzorový dátový súbor, s ktorým môžete hrať.

Stiahnite si Excel Macro Template from How-To Geek