Jak vytvořit makro v Excelu

V tomto příspěvku si ukážeme, jak vytvořit nejjednodušší makro v Excelu.  

Příklad

Makro, které si ukážeme, bude dělat jen jednoduchou věc - vymaže buňky A1 až B2 (tedy čtyři buňky). Je jasné, že takové makro ve skutečnosti práci příliš neulehčí - teď jde ale o to, abychom si ukázali obecný postup pro vytvoření maker.
Makro bude spojené s tlačítkem. Výsledek tedy takový, že uživatel klikne na tlačítko a tím smaže všechno, co je v buňkách A1 až B2 napsáno

Co je to makro

Než se dostaneme k našemu úkolu, pojďme si vyjasnit, co to vlastně makro je. Představte si, že v Excelu (nebo ve Wordu nebo v jiné aplikaci) děláte opakovaně nějakou rutinní věc - v našem případě mažete buňky. A protože je zbytečné, abyste to dělali znovu a znovu a ztráceli tím čas, potřebujete, aby to dělal Excel automaticky.
Aby to ale dělat mohl, musíte ho to nejdříve "naučit". Chcete tedy Excelu jakoby říci "teď ukážu, co chci abys dělal, a pak to uděláš sám pokaždé, když kliknu na tlačítko".
Technicky je makro aplikace napsaná v programovacím jazyce Visual Basic for Applications (VBA).

Návod

Abyste mohli pracovat s makry, musíte mít v Excelu aktivní kartu "Vývojář" - ta ale v základním nastavení aktivní není. Pro její zobrazení jděte na Soubor - Možnosti - Přizpůsobit pás karet a zaškrtněte "Vývojář". Tak, jak je to na obrázku.

V tuto chvíli byste už měli mít k dispozici kartu "Vývojář" tak, jako je to na obrázku.

Teď tedy nahrajeme makro - jinak řečeno provedeme úkony, které chceme pro příště automatizovat.

• Klikneme na tlačítko "Záznam makra" 
• Pokud chceme, tak makru dáme nějaké jméno (mezery nejsou povoleny)
• Klikneme na OK

V tuto chvíli se tlačítko "Záznam makra" změnilo na "Zastavit záznam". To znamená, že Excel se, obrazně řečeno, dívá, co děláme. Zapamatuje si to a příště už to bude dělat sám.

Teď provedeme to, co je obsahem makra - v našem případě označíme myší čtyři příslušné buňky a stiskneme "Delete". Přesně tak, jako bychom buňky promazávali bez makra.

Klikneme na "Zastavit záznam" - a makro je vytvořené.
Ověříme si to tak, že klikneme na tlačítko "Makra" a seznam všech maker (my máme zatím jen jedno) uvidíme. Můžeme je dokonce i spustit tlačítkem "Spustit" - pokud spustíme konkrétně naše makro, promažou se znovu příslušné buňky.
Vyzkoušejte si to. Napište něco do uvedených buněk a spusťte makro. Je-li makro nahrané správně, buňky se promažou.

Naším posledním úkolem je už jen přiřadit makro k tlačítku.

Nejprve tlačítko vložíme tak, jak je to na obrázku:

Tedy kliknutím a pak roztažením po listu. Excel v tuto chvíli sám nabídne přiřazení makra k tlačítku. Stačí vybrat příslušné makro a dát OK. 

A to je vše. Můžete si vyzkoušet, že i tlačítko funguje - pokud napíšete něco do příslušných buněk a pak zmáčknete tlačítko, buňky se promažou.

Pokud v seznamu maker kliknete na Upravit, zobrazí se Vám zápis makra zapsaný v jazyce VBA, nějak takto:

Sub Macro1()

'

' mojemakro

'

'

    Range("A1:B2").Select

    Selection.ClearContents

End Sub

Důležitý jsou třetí a čtvrtý řádek. Pokud umíte anglicky, není těžké je dešifrovat. V řádku tři označujeme oblast A1 až B2, v řádku čtyři vymažeme obsah z vybrané oblasti.

Další články

Chcete-li umět i další postupy s makry, klikněte sem pro další články.

Video

Video bylo vytvořené pro verzi Excel 2010, zatímco screeny pro návod jsou z verze Excel 2007. Ale odlišnosti verzí nejsou v tomto případě skoro žádné, takže by to nemělo vadit. A prakticky stejně to funguje i v Excelu 2013.

Porovnání seznamů pomocí podmíněného formátování

Tento návod je trochu složitější, ale užitečnější. Jedná se o porovnání dvou tabulek. Porovnání dvou tabulek už je na tomto blogu řešené zde, nicméně existuje ještě jedna cesta jak porovnávat, která je někdy vhodnější.
V tomto případě chci v jednom seznamu barevně vyznačit položky, které v jiném seznamu chybí - tedy označit to, co je jakoby navíc.
Představte si, že máte ve firmě několik aut - jejich výpis je v prvním sloupci. Některá jsou už pojištěná a jiná nejsou. Seznam pojištěných aut je ve druhém sloupci. Vy teď potřebujete v prvním sloupci označit ta data, která ve druhém sloupci chybí. Tedy označit nepojištěná auta, která je třeba pojistit.

• Označte auta, kterých se rozlišení týká - tedy hodnoty v levém sloupci.
• Jděte na Domů / Podmíněné formátování / Nové pravidlo / Určit buňky k formátování pomocí vzorce.
• Zadejte tento vzorec:
  =JE.CHYBHODN(SVYHLEDAT(A2;$D:$D;1;0))
• Nastavte formát buňky (u mě je to zelená barva)

Proč zrovna takovýto vzorec?

• Excel nejprve použije funkci SVYHLEDAT/VLOOKUP (podobně by bylo možné použít např. POZVYHLEDAT nebo jinou prohledávací funkci). Tato funkce se podívá po hodnotě z A2 (a s postupně i po dalších hodnotách) do druhého sloupce. Výstupem bude buď nějaká hodnota (číslo nalezeného auta) nebo chyba.
• To, jestli je to chyba nebo hodnota, určí funkce JE.CHYBHODN. Ta vezme výsledek funkce VYHLEDAT a podle něj vrátí PRAVDA (když se jedná o chybu) nebo NEPRAVDA (když se nejedná o chybu).
• A do podmíněného formátování pak spadne PRAVDA nebo NEPRAVDA. Při PRAVDĚ se aplikuje podmíněné formátování.
• Jinými slovy - podle toho, jestli SVYHLEDAT najde ve druhém seznamu odpovídající hodnotu, se probarví buňka.

Poznámka - šipky jsou pouze pro znázornění - jsou "dokreslené" ručně

Devátá hádanka - reklamní (promoakce)

Máte k dispozici prodejní data Vaší firmy. Víte, že marketingové oddělení v minulosti uspořádalo prodejní akci na jeden druh zboží, ale nevíte přesně kdy a na který druh. Vaším úkolem je tedy zjistit:

1) Ve kterém měsíci se prodejní akce uskutečnila (resp. ve kterém měsíci se tato akce projevila)

2) Na které zboží se tato akce vztahovala (vztahovala se pouze na jedno zboží)

3) O kolik korun se díky prodejní akci zvýšily tržby (ve sledovaném období)

Data jsou k dispozici tady:

https://www.dropbox.com/s/kthnma8z02rppba/velka_data_prodeje.xlsx

Nejčenější články za rok 2013

Co vás, čtenáře, na tomto blogu nejvíce zajímalo minulý rok?

Stránky jsou uvedeny od nejvíce navštěvovanou po nejméně navštěvovanou.

1. Kontingenční tabulky - bezkonkurenční evergereen
   Dovoluji si ale nabídnout aktualizovanou verzi zde.
2. SVYHLEDAT- čekali byste, že zrovna o tuhle funkci je takový zájem? 
3. Titulní stránka
4. Podrobnější pitvání ve funkci KDYŽ / IF
5. Základní úvod do maker
6. Zase kontingenční tabulky - tentokrát přehled všech článků k tématu
7. Pracovní pohovory - k tomu mohu dodat snad jen že neradím nechávat přípravu na poslední chvíli.
8. MS Access aneb nejen Excelem živa je kancelářská krysa
9. Agitka proti pračkám Whirlpool. Vzniklo jen jako úleva z pocitu flustrace, a vida, dnes v top ten. Současně článek s nejživější diskusí. Nejvtipnější ale je, že na stránce se občas objevuje PPC reklama na pračky Whirlpool. Whirlpoole, děkuji za sponzoring :)
10. Přehled důležitých témat, jakási malá sitemap.

Každopádně díky za pozornost všem dvěmastůmdesetitisícůmdvěmastůmdevadesátidvěma čtenářům, kteří na tento blog zatím přišli, a těm sedmi, co jsou tam zrovna v tomto okamžiku, také:)

Jak pořádat firemní kursy Excelu

Článek byl přesunut sem - na stránky týkající se školení a kursů.

Kontingenční tabulky v Excelu 2013

Tento základní návod na kontingenční tabulky platí pro Excel 2013 a (na 99%) také pro Excel 2010. Pokud hledáte návod pro Excel 2007 nebo starší, klikněte sem.

Příklad

Mám neuspořádaná data a chci z nich získat užitečné informace. V tomto případě se chci (s pomocí kontingenční tabulky) dozvědět, kolik je v seznamu (nabídka autobazaru) aut určité značky (např. Ford) a kolik dohromady stojí.

Návod

Tabulka ke stažení
Začnu tak, že kliknu kamkoliv do tabulky - nemusím nic označovat. Dále kliknu v kartě Vložení (Insert) na Kontingenční tabuka (Pivot table).

Následující dialog mohu nechat jak je a jen ho potvrdit "OK". Pouze pokud bych chtěl použít jiná data, než mi vybral Excel, vyberu je.

 

O možnosti použít externí data (Use an external data source) více zde
Tím vznikne nový list s kontingenční tabulkou. Nemusím se tedy bát, že původní tabulka zmizela - mohu se k ní vždy vrátit na původní list.

Všimněte si pravého sloupečku s nabídkou - nahoře jsou v řádcích vypsané názvy sloupců z původní tabulky. Tím, jak je budu přesouvat do levé tabulky nebo do spodních obdélníků, budu upravovat kontingenční tabulku.

Mým úkolem bylo zjistit, kolik je v seznamu Fordů a kolik dohromady stojí. Udělám to tak, že v tabulce nechám vypsat součty cen za všechny značky - tedy i za Ford.

"Ford" je jedna ze značek aut v seznamu. Proto přetáhnu "Značka" z horního obdélníku vpravo do obdélníku "Sem přetáhněte řádková pole" v tabulce nebo do pole "Řádky" vpravo dole

Tím se Vám v levé části tabulky vypíší všechny značky aut v seznamu. 

Teď ještě zjistit, kolik tyto značky dohromady stojí. Přetáhnu "Cena" do "Hodnoty".

Teď již u každé značky vidím, kolik dohromady stojí. 

Teď si přidám další úkol. Zajímá mě, kolik aut té které značky v seznamu je. Tedy ne kolik dohromady stojí, ale jejich počet.

Dvojkliknu na Součet z cena a v nabídce změním Součet (Sum) na Počet (Count). K tomuto dialogu se mohu dostat také kliknutím vpravo dole na Součet z Cena / Nastavení polí hodnot. 
Pokud bych chtěl obojí, součet i počet, přitáhnu do pole hodnot Cenu dvakrát - a jednou změním součet na počet.

A to je všechno.

Pár tipů navíc:

• Když "zmizí" okno pro tvorbu kontingenční tabulky vpravo, stačí kliknout do tabulky - a zase se objeví.
• Ve verzích Excelu od 2007 je možné místo do samotné tabulky přetahovat záhlaví sloupečků do čtyř polí dole v pravém pruhu. Pole odpovídají polím tabulky a je jedno, kam záhlaví přetáhnete - jestli přímo do tabulky nebo do "chlívečků" vpravo.
• Z tabulky je možno snadno kontingenční udělat graf - pouhým kliknutím na ikonku grafu a vybráním typu grafu.
• Další návody týkající se kontingenčních tabulek

Kam dál?

Pokud máte raději videonávody, tak tady jeden je. Pokud máte nějaký dotaz, napište jej prosím do diskuse.
http://www.youtube.com/watch?v=rZ3XbdkGqZE&list=PLFCPUmgA-NOPpTsYyBrf0DmY3sM4-7oZ4&index=6

Ploché (tabulkové) zobrazení kontingenčních tabulek

Kontingenční tabulky mají jeden zajímavý způsob zobrazení, který se občas velmi hodí. Nejsem schopný to popsat srozumitelně teoreticky, takže hned přejdu k příkladu.

Příklad

Mám takovouto tabulku, ve které sleduji tržby za prodejce a druhy zboží.

Nebyl by problém vytvořit kontingenční tabulku sledující, kolik který prodejce utržil na různých druzích zboží.

Když se ale na tabulku podíváte, vidíte problém. Jméno a příjmení je jsou nesmyslně ve dvou různých řádcích. Mnohem lépe by tabulka vypadala takto:

Návod

Jak na to?
Vytvořím obyčejnou kontingenční tabulku, jako je ta na druhém obrázku.
V Nástroje kontingenční tabulky / Návrh / Rozložení sestavy vyberu Zobrazit ve formě tabulky:

Ve stejné kartě v Souhrny kliknu na Nezobrazovat souhrny.

A je hotovo - tabulka je přehlednější a položky, které k sobě logicky patří, jsou opravdu vedle sebe.