Naučte se používat makra aplikace Excel k automatizaci únavných úkolů

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

Pokud vás zajímá, co jsou to makra nebo jak je vlastně vytvořit, žádný problém - provedeme vás celým procesem.

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

Co je to makro?

Makro Microsoft Office (protože tato funkce platí pro několik aplikací MS Office) je jednoduše kód jazyka Visual Basic for Applications (VBA) uložený uvnitř dokumentu. Pro srovnatelnou analogii si dokument představte jako HTML a makro jako Javascript. Stejným způsobem, jako může Javascript manipulovat s HTML na webové stránce, může makro manipulovat s dokumentem.

Makra jsou neuvěřitelně silná a dokáží skoro všechno, co vaše fantazie dokáže vykouzlit. Jako (velmi) krátký seznam funkcí, které můžete dělat s makrem:

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

Vytvoření makra: vysvětlení příkladem

Začínáme s vaším souborem CSV pro zahradní odrůdu. Nic zvláštního, jen sada 10 × 20 čísel mezi 0 a 100 se záhlavím řádku i sloupce. Naším cílem je vytvořit dobře naformátovaný, prezentovatelný datový list, který obsahuje souhrnné součty pro každý řádek.

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

Chcete-li vytvořit makro, přejděte na Zobrazení> Makra> Zaznamenat makro.

Přiřaďte makru název (bez mezer) a klikněte na OK.

Jakmile to uděláte, zaznamenají se všechny vaše akce - každá změna buňky, akce rolování, změna velikosti okna, pojmenujete to.

Existuje několik míst, která označují, že Excel je v režimu záznamu. Jedním z nich je prohlížení nabídky Makro a konstatování, že možnost Záznam záznamu nahradila možnost Záznam makra.

Druhý je v pravém dolním rohu. Ikona „stop“ označuje, že je v režimu makro, a stisknutím tohoto tlačítka se nahrávání zastaví (podobně, pokud není v režimu záznamu, bude tato ikona 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í.

Dále použijte příslušné vzorce (v uvedeném pořadí):

  • = SUM (B2: K2)
  • = PRŮMĚR (B2: K2)
  • = MIN (B2: K2)
  • = MAX (B2: K2)
  • = STŘEDNÍ (B2: K2)

Nyní zvýrazněte všechny buňky výpočtu a přetažením délky všech našich datových řádků použijte výpočty na každý řádek.

Po dokončení by měl každý řádek zobrazit příslušné souhrny.

Nyní chceme získat souhrnná data pro celý list, takže použijeme několik dalších výpočtů:

Respektive:

  • = SUM (L2: L21)
  • = PRŮMĚR (B2: K21) * Toto musí být vypočítáno pro všechna data, protože průměr průměrů řádků se nemusí nutně rovnat průměru všech hodnot.
  • = MIN (N2: N21)
  • = MAX (O2: O21)
  • = MEDIAN (B2: K21) * Vypočteno pro všechna data ze stejného důvodu jako výše.

Nyní, když jsou výpočty hotové, použijeme styl a formátování. Nejprve použijte obecné formátování čísel ve všech buňkách pomocí funkce Vybrat vše (buď Ctrl + A nebo klepněte na buňku mezi záhlavími řádků a sloupců) a vyberte ikonu „Čárkový styl“ v nabídce Domů.

Dále použijte některé vizuální formátování na záhlaví řádků i sloupců:

  • Tučně.
  • Na střed.
  • Barva výplně pozadí.

A nakonec použijte nějaký styl na součty.

Když je vše hotové, tak vypadá náš datový list:

Protože jsme s výsledky spokojeni, zastavte záznam makra.

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

Aby bylo možné použít naše nově zaznamenané makro, musíme uložit sešit aplikace Excel ve formátu souboru s povoleným makrem. Než to však uděláme, musíme nejprve vymazat všechna stávající data, aby nebyla vložena do naší šablony (myšlenka je při každém použití této šablony, naimportujeme nejaktuálnější data).

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

Když jsou data nyní vymazána (ale makra jsou stále obsažena v souboru Excel), chceme soubor uložit jako soubor šablony s podporou maker (XLTM). Je třeba poznamenat důležité, že pokud uložit jako soubor s standardní šablona (XLTX), pak makra se nebude moci spustit z něj. Alternativně můžete soubor uložit jako soubor starší šablony (XLT), který umožní spuštění maker.

Po uložení souboru jako šablony pokračujte a zavřete Excel.

Pomocí makra aplikace Excel

Než se podíváme na to, jak můžeme použít toto nově zaznamenané makro, je důležité pokrýt několik bodů o makrech obecně:

  • Makra mohou být škodlivá.
  • Viz výše uvedený bod.

Kód VBA je ve skutečnosti docela silný a dokáže manipulovat se soubory mimo rámec aktuálního dokumentu. Makro může například změnit nebo odstranit náhodné soubory ve složce Moje dokumenty. Proto je důležité zajistit, abyste makra spouštěli pouze z důvěryhodných zdrojů.

Chcete-li použít naše makro formátu dat, otevřete soubor šablony aplikace Excel, který byl vytvořen výše. Když to uděláte, za předpokladu, že máte povoleno standardní nastavení zabezpečení, uvidíte v horní části sešitu varování, které říká, že makra jsou zakázána. Protože důvěřujeme makru, které jsme sami vytvořili, klikněte na tlačítko „Povolit obsah“.

Dále budeme importovat nejnovější datovou sadu z CSV (toto je zdroj, který list použil k vytvoření našeho makra).

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

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

Ve výsledném dialogovém okně vidíme makro „FormatData“, které jsme zaznamenali výše. Vyberte jej a klikněte na Spustit.

Po spuštění můžete na několik okamžiků vidět skákat kurzorem, ale jak to děláte, uvidíte manipulaci s daty přesně tak , jak jsme je zaznamenali. Když je vše řečeno a hotovo, mělo by to vypadat stejně jako náš originál - kromě jiných údajů.

Pohled pod pokličku: Proč funguje makro?

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

Chcete-li zobrazit kód, který umožňuje spuštění našeho makra, klikněte v dialogovém okně Makra na tlačítko Upravit.

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

Vezmeme si náš příklad o krok dále ...

Hypoteticky předpokládejme, že náš zdrojový datový soubor data.csv je vytvořen automatizovaným procesem, který soubor vždy uloží na stejné místo (např. C: \ Data \ data.csv jsou vždy nejnovější data). Proces otevření a importu tohoto souboru lze snadno provést také do makra:

  1. Otevřete soubor šablony Excel obsahující naše makro „FormatData“.
  2. Zaznamenejte nové makro s názvem „LoadData“.
  3. Při záznamu makra importujte datový soubor jako obvykle.
  4. Po importu dat zastavte záznam makra.
  5. Smažte všechna data buňky (vyberte vše a poté smažte).
  6. Uložte aktualizovanou šablonu (nezapomeňte použít formát šablony s podporou maker).

Jakmile to uděláte, budou při každém otevření šablony existovat dvě makra - jedno, které načte naše data, a druhé, které je naformátuje.

Pokud jste si opravdu chtěli zašpinit ruce trochou úpravy kódu, můžete tyto akce snadno spojit do jediného makra zkopírováním kódu vytvořeného z „LoadData“ a jeho vložením na začátek kódu z „FormatData“.

Stáhněte si tuto šablonu

Pro vaše pohodlí jsme zahrnuli jak šablonu aplikace Excel vytvořenou v tomto článku, tak ukázkový datový soubor, se kterým si můžete zahrát.

Stáhněte si šablonu makra Excel z How-To Geek