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

Obsah:

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

Video: Naučte se používat makra aplikace Excel pro automatizaci únavných úloh
Video: How to add shortcuts to the quick access toolbar in Microsoft Word 2013 2024, Březen
Naučte se používat makra aplikace Excel pro automatizaci únavných úloh
Naučte se používat makra aplikace Excel pro automatizaci únavných úloh
Anonim
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.
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 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ý 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 mohou dělat téměř vše, co vaše představivost může vyvolat. Jako (velmi) krátký seznam funkcí můžete udělat s makrem:

  • Použijte styl a formátování.
  • Manipulovat data a text.
  • Komunikujte se zdroji dat (databáze, textové soubory atd.).
  • Vytvořte zcela nové dokumenty.
  • Libovolná kombinace jakéhokoli z výše uvedených.

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 kód VBA, ale jednou 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.
Jak jsme uvedli výše, makro je kód VBA, ale jednou 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> Makra> Záznam makra.

Přiřadte makru jméno (bez mezer) a klepněte na tlačítko OK.
Přiřadte makru jméno (bez mezer) a klepněte na tlačítko OK.
Image
Image

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 makra a upozornění, že možnost Stop Recording nahradila možnost Záznam 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).
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í.
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.):
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ů a použijte výpočty na každý řádek.
Nyní zvýrazněte všechny výpočtové buňky a přetáhněte délku všech datových řádků a použijte výpočty na každý řádek.
Jakmile to bude provedeno, každý řádek by měl zobrazit své souhrny.
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ů:
Nyní chceme získat souhrnná data pro celý list, a proto použijeme ještě několik výpočtů:
Následně:
Následně:
  • = SUM (L2: L21)
  • = Průměrná (B2: K21) *To se musí vypočítat u všech dat, protože průměr průměrových řádků nemusí být nutně stejný jako průměr 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 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 "Styl čárky".
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 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 "Styl čárky".
Poté použijte některé grafické formátování na hlavičkách řádků a sloupců:
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.
A konečně aplikujte nějaký styl na součty.
Po dokončení všeho vypadá náš datový list:
Po dokončení všeho vypadá náš datový list:
Protože jsme spokojeni s výsledky, zastavte nahrávání makra.
Protože jsme spokojeni s výsledky, zastavte nahrávání makra.
Gratulujeme - právě jste vytvořili makro aplikace Excel.
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.

Image
Image

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), budou to makra ne být schopen být spuštěn 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.
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 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 nejnovější soubor dat z CSV (toto je zdrojový list, který byl použit k vytvoření našeho makra).
V dalším kroku importujeme nejnovější soubor dat z CSV (toto je zdrojový list, který byl použit 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é hlavičky atd.).
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.
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.
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.
Image
Image

Jakmile běžíte, můžete na několik okamžiků vidět, jak se kurzor nakloní, ale jak to uvidíte, budou ú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ů.

Image
Image

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 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.
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 náš 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 importu souboru lze snadno udělat i do makra:

  1. Otevřete soubor šablony aplikace Excel obsahující naše makro "FormatData".
  2. Zaznamenejte 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. Smazat všechna data o buňce (vyberte vše a pak odstranit).
  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 dvě makra - jedna, která načte naše data a druhá, která ji formátuje.

Pokud jste opravdu chtěli dostat špinavé ruce trochu editace 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".
Pokud jste opravdu chtěli dostat špinavé ruce trochu editace 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 Excel, která byla vytvořena v tomto článku, tak vzorový datový soubor, který vám umožní hrát si s sebou.

Stáhněte si Excel Macro Template z How-To Geek

Doporučuje: