9Sep

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 uš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 vlastně vytvářet, žádný problém - projdeme vás celým procesem.

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

Co je to 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ý uvnitř dokumentu. Pro srovnatelnou analogii zvažte, že 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áží udělat vše, co vaše představivost dokáže vykouzlit. Jako( velmi) krátký seznam funkcí můžete provést s makrem:

  • Použití stylu a formátování.
  • Manipulovat data a text.
  • Komunikujte se zdroji dat( databáze, textové soubory atd.).
  • Vytvoření zcela nových dokumentů.
  • Jakákoli kombinace libovolného z výše uvedených pořadí.

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

Začneme s vaším šaržovým souborem 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 - jak to uděláme zde.

Chcete-li vytvořit makro, přejděte na možnost Zobrazit & gt;Makra & gt;Nahrát makro.

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

Jakmile to bude hotovo, bude zaznamenávána všechna vaše akce - každou změnu buňky, aktivaci posouvání, změnu velikosti okna, její název.

Existuje několik míst, která označují, že program Excel je režim záznamu. Jedním z nich je zobrazení nabídky makra 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í.

Dále použijte příslušné vzorce( respektive):

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

Nyní zvýrazněte všechny výpočtové buňky a přetáhněte délku všech datových řádků tak, abychom aplikovali 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, proto použijeme ještě několik výpočtů:

Respektivně:

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

Poté, co se provedou výpočty, použijeme styl a formátování.Nejprve aplikujte obecné formátování čísel ve všech buňkách pomocí volby Vybrat 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 "Styly čárky".

Poté použijte několik vizuálních formátů jak pro záhlaví řádků, tak pro sloupce:

  • Bold.
  • Centrovaný.
  • Barva pozadí naplnění.

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 stávající data tak, aby nebyla vložena do naší šablony( myšlenka je při každém použití této šablony, importujeme nejaktuálnější data).

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

S daty, které jsou nyní vymazány( ale makra jsou stále součástí souboru aplikace Excel), chceme soubor uložit jako šablonu šablony( XLTM).Je důležité si uvědomit, že pokud uložíte tento soubor jako soubor standardní šablony( XLTX), budou moci makra ne spustit z něj. Můžete také uložit soubor jako soubor starší šablony( XLT), který 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 skutečně 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 pouze spustit makra z důvěryhodných zdrojů.

Chcete-li použít makro formát dat, otevřete soubor šablony aplikace 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 Povolit obsah.

V dalším kroku importujeme poslední soubor dat z CSV( toto je zdrojový list, který jsme použili k vytvoření našeho 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é záhlaví apod.).

Jakmile jsou 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, uvidíte data, která jsou manipulována s přesně , když jsme je 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 práci

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 skutečně 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 napíše kód pro vás.

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.

Vzhledem k tomu, že náš příklad je 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 na stejné místo( např. C: \ Data \ data.csv je vždy nejvíceposlední údaje).Proces otevírání a importu souboru lze také snadno udělat do makra:

  1. Otevřete soubor šablony aplikace Excel obsahující makro "FormatData".
  2. Zaznamenejte nové makro s názvem "LoadData".
  3. Při záznamu makra importujte datový soubor tak, jak byste normálně udělali.
  4. Jakmile jsou data importována, zastavte nahrávání makra.
  5. Odstranění všech dat 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 ruce špinavé s trochou úpravy kódu, můžete snadno kombinovat tyto akce do jednoho makra tím, že zkopíruje kód vyrobený z "LoadData" a vloží jej na začátek kódu z "FormatData".

Stáhněte si tuto šablonu

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

Stáhnout Excel makro šablony z How-To Geek