Naučte se používat makra aplikace Excel pro automatizaci únavných úloh

Jednou z výkonnějších, ale zřídka používaných funkcí aplikace Excel je schopnost velmi snadno vytvářet automatizované úlohy a vlastní logiku v rámci maker. Makra poskytují ideální způsob, jak šetřit čas na předvídatelné, opakující se úkoly, stejně jako standardizovat formáty dokumentů - mnohokrát bez nutnosti psát jediný řádek kódu.

Pokud jste zvědaví, jaké makra jsou, nebo jak je skutečně vytvářet, žádný problém - projdeme vás celým procesem.

Poznámka: stejný proces by měl fungovat ve většině verzí sady Microsoft Office. Obrázky obrazovky mohou vypadat trochu jinak.

Co je makro?

Makro Microsoft Office (protože tato funkce se týká několika aplikací MS Office) je pouze kód jazyka Visual Basic for Applications (VBA) uložený v dokumentu. Pro srovnatelnou analogii si myslete na dokument jako HTML a makro jako Javascript. V podstatě stejným způsobem, jak může Javascript manipulovat s HTML na webové stránce, může makro manipulovat s dokumentem.

Makra jsou neuvěřitelně silné a dokáží dělat téměř vše, co může vaše představivost vyvolat. Jako (velmi) krátký seznam funkcí můžete udělat s makrem:

• Použijte styl a formátování.
• Manipulujte s daty a textem.
• Komunikace se zdroji dat (databáze, textové soubory atd.).
• Vytvořte zcela nové dokumenty.
• Jakákoli kombinace libovolného z výše uvedených pořadí.

Vytvoření makra: vysvětlení pomocí příkladu

Začínáme s vaší zahradní variantou souboru CSV. Nic zvláštního tady, jen 10 × 20 sada čísel mezi 0 a 100 s hlavičkou řádku a sloupce. Naším cílem je vytvořit dobře formátovaný, představitelný datový list, který obsahuje souhrnné součty pro každý řádek.

Jak jsme uvedli výše, makro je VBA kód, ale jedna z hezkých věcí o aplikaci Excel je, že je můžete vytvářet / zaznamenávat s nulovým kódováním požadovaným - jak to uděláme zde.

Chcete-li vytvořit makro, přejděte na možnost Zobrazit> Makra> Záznam makra.

Přiřadte makru název (bez mezer) a klepněte na tlačítko OK.

Jakmile to bude hotovo, Všechno vaše akce jsou zaznamenávány - každá změna buňky, posuvná akce, změna velikosti okna, jména.

Existuje několik míst, která označují, že program Excel je režim záznamu. Jedním z nich je zobrazení nabídky Macro a upozornění, že možnost Stop Recording nahradila možnost záznamu makra.

Druhý je v pravém dolním rohu. Ikona "zastavení" označuje, že je v režimu makro a stisknutím tlačítka se zastaví nahrávání (podobně, pokud není v režimu záznamu, tato ikona bude tlačítko Nahrát makro, které můžete použít namísto přechodu do nabídky Makra).

Nyní, když zaznamenáváme naše makro, použijeme naše souhrnné výpočty. Nejprve přidejte záhlaví.

Poté použijte odpovídající vzorce (resp.):

• = SUM (B2: K2)
• = Průměrná (B2: K2)
• = MIN (B2: K2)
• = MAX (B2: K2)
• = MEDIAN (B2: K2)

Nyní zvýrazněte všechny výpočtové buňky a přetáhněte délku všech datových řádků, abyste použili výpočty na každý řádek.

Jakmile to bude provedeno, každý řádek by měl zobrazit své souhrny.

Nyní chceme získat souhrnná data pro celý list, a proto použijeme ještě několik výpočtů:

Následně:

• = SUM (L2: L21)
• = Průměrná (B2: K21) *Tento údaj musí být vypočítán u všech dat, protože průměr průměrových řad nemusí nutně odpovídat průměru všech hodnot.
• = MIN (N2: N21)
• = MAX (O2: O21)
• = MEDIAN (B2: K21) * Vypočítáváno pro všechny údaje ze stejného důvodu jako výše.

Po dokončení výpočtů použijeme styl a formátování. Nejprve aplikujte obecné formátování čísel ve všech buňkách tak, že provedete volbu Vše (buď Ctrl + A, nebo kliknutím na buňku mezi záhlavími řádků a sloupců) a v místní nabídce vyberte ikonu "Styl čárky".

Poté použijte některé grafické formátování na hlavičkách řádků a sloupců:

• tučně.
• Centrovaný.
• Barva výplně pozadí.

A konečně aplikujte nějaký styl na součty.

Po dokončení všeho vypadá náš datový list:

Protože jsme spokojeni s výsledky, zastavte nahrávání makra.

Gratulujeme - právě jste vytvořili makro aplikace Excel.

Abychom mohli využít naše nově zaznamenané makro, musíme uložit náš sešit aplikace Excel ve formátu souborů s makrami. Nicméně před tím, než to uděláme, musíme nejprve vymazat všechna existující data, aby nebyla vložena do naší šablony (myšlenka je vždy, když používáme tuto šablonu, importujeme nejaktuálnější data).

Chcete-li to provést, vyberte všechny buňky a odstraňte je.

S daty, které jsou nyní vyčištěny (ale makra jsou stále součástí souboru aplikace Excel), chceme soubor uložit jako soubor šablony s povolenou makrou (XLTM). Je důležité si uvědomit, že pokud uložíte tento soubor jako standardní šablonu (XLTX), pak budou makra ne být schopen spustit z něj. Případně můžete soubor uložit jako soubor starší šablony (XLT), což umožní spuštění maker.

Jakmile soubor uložíte jako šablonu, pokračujte a zavřete aplikaci Excel.

Použití makra aplikace Excel

Než se pokusíme o použití tohoto nově zaznamenaného makra, je důležité pokrýt několik bodů o makrach obecně:

• Makra mohou být škodlivé.
• Viz výše.

Kód VBA je ve skutečnosti poměrně silný a může manipulovat s soubory mimo rámec aktuálního dokumentu. Například makro by mohlo změnit nebo odstranit náhodné soubory ve složce Moje dokumenty. Jako takový je důležité se ujistit, že jste pouze spustit makra z důvěryhodných zdrojů.

Chcete-li použít makro datového formátu, otevřete soubor šablony Excel, který byl vytvořen výše. Pokud tak učiníte, za předpokladu, že máte povoleno standardní nastavení zabezpečení, uvidíte varování v horní části sešitu, které říká, že makra jsou zakázána. Protože důvěřujeme makro, které jsme vytvořili, klikněte na tlačítko "Aktivovat obsah".

V dalším kroku importujeme nejnovější soubor dat z CSV (toto je zdrojový list, který jsme použili k vytvoření makra).

Chcete-li dokončit import souboru CSV, budete možná muset nastavit několik možností, aby aplikace Excel správně interpretovala (například oddělovač, přítomné hlavičky atd.)..

Jakmile jsou naše data importována, jednoduše přejděte do nabídky Makra (pod záložkou Zobrazení) a vyberte Zobrazit makra.

Ve výsledném dialogovém okně se zobrazí makro "FormatData", které jsme zaznamenali výše. Vyberte jej a klikněte na příkaz Spustit.

Jakmile běžíte, můžete na několik okamžiků vidět, jak se kurzor pohybuje, ale jak to uvidíte, budou zobrazené údaje manipulovány přesně tak jak jsme ji zaznamenali. Když je vše řečeno a hotovo, mělo by to vypadat stejně jako náš originál - s výjimkou různých údajů.

Při pohledu pod kapucí: Co dělá makro

Jak jsme již několikrát zmínili, makro je řízeno kódem Visual Basic for Applications (VBA). Když "zaznamenáváte" makro, aplikace Excel ve skutečnosti převádí vše, co děláte, do příslušných pokynů VBA. Jednoduše řečeno - nemusíte psát žádný kód, protože aplikace Excel psát kód.

Chcete-li zobrazit kód, který provádí naše makro, z dialogového okna Makra klikněte na tlačítko Upravit.

Okno, které se otevře, zobrazí při vytváření makra zdrojový kód, který byl zaznamenán z našich akcí. Samozřejmě, můžete tento kód upravit nebo dokonce vytvořit nové makra zcela uvnitř okna kódu. Zatímco záznamová akce použitá v tomto článku pravděpodobně odpovídá většině potřeb, více přizpůsobené akce nebo podmíněné akce by vyžadovaly úpravu zdrojového kódu.

Vzít náš příklad o krok dále ...

Hypoteticky předpokládejme, že zdrojový datový soubor data.csv je vytvořen automatizovaným procesem, který vždy ukládá soubor do stejného umístění (např. C: \ Data \ data.csv je vždy nejaktuálnější dat). Proces otevírání a importování souboru lze snadno udělat také do makra:

1. Otevřete soubor šablony aplikace Excel, který obsahuje naše makro "FormatData".
2. Nahrát nové makro s názvem "LoadData".
3. Při nahrávání maker importujte datový soubor, jako byste normálně používali.
4. Jakmile jsou data importována, zastavte nahrávání makra.
5. Vymažte všechna data buňky (vyberte vše a pak odstraňte).
6. Uložte aktualizovanou šablonu (nezapomeňte použít formát maker s povolenou šablonou).

Jakmile to bude hotovo, kdykoli bude šablona otevřena, budou existovat dvě makra - jedna, která načte naše data a druhá, která ji formátuje.

Pokud jste opravdu chtěli dostat špinavé ruce s trochou úpravy kódu, mohli byste tyto akce jednoduše spojit do jediného makra tím, že zkopírujete kód vytvořený z "LoadData" a vložíte ho na začátek kódu z formátu "FormatData".

Stáhněte si tuto šablonu

Pro vaše pohodlí jsme zahrnovali jak šablonu aplikace Excel, která byla vytvořena v tomto článku, tak vzorový datový soubor, který vám umožní hrát.

Stáhněte si Excel makro šablony z How-To Geek