Obsah
Excel má výkonnou, ale zároveň velmi zřídka používanou schopnost vytvářet automatické sekvence akcí pomocí maker. Makro je ideálním východiskem, pokud řešíte stejný typ úlohy, která se mnohokrát opakuje. Například zpracování dat nebo formátování dokumentu podle standardizované šablony. V tomto případě nepotřebujete znalost programovacích jazyků.
Už jste zvědaví, co je makro a jak funguje? Pak směle pokračujte – pak s vámi krok za krokem provedeme celý proces tvorby makra.
Co je to makro?
Makro v Microsoft Office (ano, tato funkce funguje stejně v mnoha aplikacích balíku Microsoft Office) je programový kód v programovacím jazyce Visual Basic pro aplikace (VBA) uložený v dokumentu. Aby to bylo jasnější, dokument Microsoft Office lze přirovnat ke stránce HTML, makro je pak obdobou Javascriptu. Co Javascript dokáže s daty HTML na webové stránce, je velmi podobné tomu, co může dělat makro s daty v dokumentu Microsoft Office.
Makra mohou v dokumentu dělat téměř vše, co chcete. Zde jsou některé z nich (velmi malá část):
- Použít styly a formátování.
- Provádějte různé operace s číselnými a textovými daty.
- Používejte externí zdroje dat (databázové soubory, textové dokumenty atd.)
- Vytvořte nový dokument.
- Proveďte vše výše uvedené v libovolné kombinaci.
Vytvoření makra – praktický příklad
Vezměme si například nejběžnější soubor CSV. Jedná se o jednoduchou tabulku 10×20 plnou čísel od 0 do 100 s nadpisy pro sloupce a řádky. Naším úkolem je převést tento soubor dat do prezentovatelně formátované tabulky a generovat součty v každém řádku.
Jak již bylo zmíněno, makro je kód napsaný v programovacím jazyce VBA. Ale v Excelu můžete vytvořit program bez psaní řádku kódu, což uděláme právě teď.
Chcete-li vytvořit makro, otevřete Pohled (Typ) > makra (Makro) > Nahrát makro (Makro záznam…)
Pojmenujte své makro (bez mezer) a klikněte OK.
Od tohoto okamžiku jsou zaznamenávány VŠECHNY vaše akce s dokumentem: změny buněk, procházení tabulkou, dokonce i změna velikosti okna.
Excel signalizuje, že režim záznamu maker je povolen na dvou místech. Nejprve v nabídce makra (Makra) – místo řetězce Nahrát makro Objevil se řádek (Nahrávání makra…). Zastavte nahrávání (Zastavit nahrávání).
Za druhé, v levém dolním rohu okna aplikace Excel. Ikona Stop (malý čtvereček) znamená, že je povolen režim záznamu makra. Kliknutím na něj zastavíte nahrávání. Naopak, když režim záznamu není povolen, je na tomto místě ikona umožňující záznam makra. Kliknutím na něj získáte stejný výsledek jako zapnutím nahrávání přes nabídku.
Nyní, když je povolen režim záznamu maker, pojďme k našemu úkolu. Nejprve přidáme hlavičky pro souhrnné údaje.
Dále zadejte vzorce do buněk podle názvů nadpisů (jsou uvedeny varianty vzorců pro angličtinu a verze Excelu, adresy buněk jsou vždy latinská písmena a čísla):
- =SUM(B2:K2) or =SUM(B2:K2)
- =AVERAGE(B2:K2) or =СРЗНАЧ(B2:K2)
- =MIN(B2:K2) or =MIN(B2:K2)
- =MAX(B2:K2) or =MAX(B2:K2)
- =MEDIAN(B2:K2) or =MEDIAN(B2:K2)
Nyní vyberte buňky se vzorci a zkopírujte je do všech řádků naší tabulky přetažením úchytu automatického vyplňování.
Po dokončení tohoto kroku by měl mít každý řádek odpovídající součty.
Dále shrneme výsledky pro celou tabulku, k tomu provedeme několik dalších matematických operací:
Respektive:
- =SUM(L2:L21) or =SUM(L2:L21)
- =AVERAGE(B2:K21) or =СРЗНАЧ(B2:K21) – pro výpočet této hodnoty je nutné vzít přesně počáteční data tabulky. Pokud vezmete průměr průměrů za jednotlivé řádky, bude výsledek jiný.
- =MIN(N2:N21) or =MIN(N2:N21)
- =MAX(O2:O21) or =MAX(O2:O21)
- =MEDIAN(B2:K21) or =MEDIAN(B2:K21) – z výše uvedeného důvodu zvažujeme použití výchozích údajů z tabulky.
Nyní, když jsme s výpočty hotovi, pojďme udělat nějaké formátování. Nejprve nastavíme stejný formát zobrazení dat pro všechny buňky. Vyberte všechny buňky na listu, k tomu použijte klávesovou zkratku Ctrl +nebo klikněte na ikonu Vybrat vše, který se nachází na průsečíku záhlaví řádků a sloupců. Pak klikněte Styl čárky (Formát s oddělovači) kartu Domů (Domov).
Dále změňte vzhled záhlaví sloupců a řádků:
- Tučný styl písma.
- Zarovnání na střed.
- Barevná výplň.
A nakonec nastavíme formát součtů.
Takto by to mělo nakonec vypadat:
Pokud vám vše vyhovuje, přestaňte makro nahrávat.
Gratulujeme! Právě jste sami nahráli své první makro v Excelu.
Abychom mohli vygenerované makro použít, musíme dokument Excel uložit ve formátu, který makra podporuje. Nejprve musíme z tabulky, kterou jsme vytvořili, smazat všechna data, tj. udělat z ní prázdnou šablonu. Faktem je, že v budoucnu při práci s touto šablonou do ní budeme importovat nejaktuálnější a relevantní data.
Chcete-li vymazat všechny buňky z dat, klikněte pravým tlačítkem na ikonu Vybrat vše, který se nachází na průsečíku záhlaví řádků a sloupců, a z místní nabídky vyberte Vymazat (Vymazat).
Nyní je náš list zcela vyčištěn od všech dat, zatímco makro zůstává zaznamenáno. Musíme sešit uložit jako šablonu aplikace Excel s podporou maker, která má příponu XLTM.
Důležitý bod! Pokud uložíte soubor s příponou XLTX, pak v něm makro nebude fungovat. Mimochodem, sešit můžete uložit jako šablonu Excel 97-2003, která má formát XLT, podporuje také makra.
Po uložení šablony můžete Excel bezpečně zavřít.
Spuštění makra v Excelu
Před odhalením všech možností vámi vytvořeného makra si myslím, že je správné věnovat pozornost několika důležitým bodům týkajícím se maker obecně:
- Makra mohou být škodlivá.
- Přečtěte si znovu předchozí odstavec.
Kód VBA je velmi výkonný. Zejména může provádět operace se soubory mimo aktuální dokument. Makro může například odstranit nebo upravit jakékoli soubory ve složce Moje dokumenty. Z tohoto důvodu spouštějte a povolujte makra pouze ze zdrojů, kterým důvěřujete.
Chcete-li spustit naše makro pro formátování dat, otevřete soubor šablony, který jsme vytvořili v první části tohoto kurzu. Pokud máte standardní nastavení zabezpečení, pak se při otevření souboru nad tabulkou zobrazí upozornění, že makra jsou zakázána, a tlačítko pro jejich povolení. Jelikož jsme si šablonu vyrobili sami a věříme si, stiskneme tlačítko Povolit obsah (Zahrnout obsah).
Dalším krokem je import poslední aktualizované datové sady ze souboru CSV (na základě takového souboru jsme vytvořili naše makro).
Když importujete data ze souboru CSV, Excel vás může požádat o nastavení některých nastavení, aby se data správně přenesla do tabulky.
Po dokončení importu přejděte do nabídky makra kartu (Makra). Pohled (Zobrazit) a vyberte příkaz Zobrazit makra (Makro).
V dialogovém okně, které se otevře, uvidíme řádek s názvem našeho makra FormatData. Vyberte jej a klikněte Běh (Vykonat).
Když se makro spustí, uvidíte, že kurzor tabulky přeskakuje z buňky na buňku. Po několika sekundách budou s daty provedeny stejné operace jako při záznamu makra. Když je vše připraveno, tabulka by měla vypadat stejně jako originál, který jsme zformátovali ručně, pouze s jinými údaji v buňkách.
Podívejme se pod pokličku: Jak funguje makro?
Jak již bylo zmíněno více než jednou, makro je programový kód v programovacím jazyce. Visual Basic pro aplikace (VBA). Když zapnete režim záznamu maker, Excel vlastně zaznamená každou vaši akci ve formě instrukcí VBA. Jednoduše řečeno, Excel napíše kód za vás.
Chcete-li zobrazit tento kód programu, musíte v nabídce makra kartu (Makra). Pohled (zobrazit) kliknout Zobrazit makra (Makra) a v dialogovém okně, které se otevře, klepněte na Změnit (Změna).
Otevře se okno. Visual Basic pro aplikace, ve kterém uvidíme programový kód makra, které jsme zaznamenali. Ano, pochopili jste správně, zde můžete tento kód změnit a dokonce vytvořit nové makro. Akce, které jsme provedli s tabulkou v této lekci, lze zaznamenat pomocí automatického záznamu maker v Excelu. Složitější makra s jemně vyladěnou sekvencí a akční logikou však vyžadují ruční programování.
Přidejme k našemu úkolu ještě jeden krok…
Představte si, že náš původní datový soubor data.csv je vytvářen automaticky nějakým procesem a je vždy uložen na disku na stejném místě. Například, C:Datadata.csv – cesta k souboru s aktualizovanými daty. Proces otevření tohoto souboru a import dat z něj lze také zaznamenat v makru:
- Otevřete soubor šablony, do kterého jsme uložili makro − FormatData.
- Vytvořte nové makro s názvem Načíst data.
- Při nahrávání makra Načíst data importovat data ze souboru data.csv – stejně jako v předchozí části lekce.
- Po dokončení importu zastavte záznam makra.
- Odstraňte všechna data z buněk.
- Uložte soubor jako šablonu aplikace Excel s podporou maker (přípona XLTM).
Spuštěním této šablony tedy získáte přístup ke dvěma makrům – jedno načítá data, druhé je formátuje.
Pokud se chcete pustit do programování, můžete akce těchto dvou maker spojit do jednoho – jednoduše zkopírováním kódu z Načíst data na začátek kódu FormatData.