Dvoustý příspěvek

Tak tento týden jsem na tento blog umístil dvoustý příspěvek. Člověk se ani nenaděje, začne psát a najednou je toho tolik
Před pár lety jsem musel povinně vytvořit nějaké internetové stránky na jeden předmět na VŠ. Vytvořil jsem tohle http://beran_jiri.sweb.cz/ a nenapadlo mě nic lepšího, než tam umístit pár návodů na Excel, který jsem v té době při škole v různých firmách učil. Postupem času jsem došel k tomu, že psát stránky v HTML je zbytečná otročina a přesunul se na blogger a doménu www.excelentitriky.com.
Pak jsem na stránky kromě návodů přidal i nabídku doučování, která si časem vydobyla vlastní stránky www.vyuka-excelu.cz. A pak se to nějak zvrtlo, přibral jsem kolegu a najednou máme řadu spokojených zákazníků.
Nicméně už mi trochu dochází fantazie s tím, jaké návody na blog dávat. Tak kdybyste přišli na něco, co Vám tady chybí a co by se Vám hodilo pro práci, dejte vědět.
Jinak děkuji za pozornost a čtěte tento blog dál :)

Práce s hierarchickými daty - má někdo zkušenost a poradí?

Nedávno jsem potřeboval vytvořit úkol podobný tomuto. Týká se práce s daty, která jsou řazena hierarchicky - v tomto příkladě se jedná o organizační strukturu pracovníků firmy.
Nenašel jsem ale žádné jednoduché řešení. Uměl bych asi napsat makro, ale raději bych našel řešení bez makra.
Je někdo schopný s tímto poradit?
V příkladu mám tabulku zaměstnanců. V jednom sloupci je jejich jméno, ve druhém jméno jejich nadřízeného a ve třetím mzda. Mohlo by jít i o dvě tabulky, jednu s identifikací nadřízeného a druhou s mzdou pracovníka - to je jedno.
Jde o to, že potřebuji sečíst mzdy všech podřízených určitého pracovníka - tak, jak je to ve druhé tabulce.
I když jsem to tehdy nakonec nemusel řešit, je to docela zajímavý oříšek který se mi nedaří rozlousknout. Poradí někdo? Hledám samozřejmě řešení, které je použitelné i pro rozsáhlé tabulky a nevyžaduje žádné "manuální" zásahy.
Nevadilo by, pokud by řešení bylo limitované např. pěti úrovněmi, jednalo se o data s malým počtem uzlů.

SUBTOTAL - funkce důležitá pro filtrování

V tomto příspěvku se trochu podíváme na funkci SUBTOTAL (česky také SUBTOTAL).
Microsoft nám v tom dělá trochu lingvistický nepořádek - v anglické verzi používá název SUBTOTAL jak pro funkci, které se věnujeme v tomto příspěvku, tak pro to, co se česky nazývá Souhrn.
Funkce SUBTOTAL se používá v kombinaci s automatickým filtrem.

Příklad

Mám tabulku s daty, např. v bazaru.
Chtěl bych používat filtr a současně sledovat některé charakteristiky dat. Například chci filtrovat podle barvy a sledovat celkové ceny za jednotlivé barvy.
Ještě jinými slovy - až vyfiltruji modrá auta, chci vidět celkovou cenu modrých aut, a až to změním na červenou, tak cenu červených aut.

Návod

V tabulce použiji standardní filtr.
Pak do jedné z buněk zapisuji funkci SUBTOTAL. Je důležité zapisovat ji do buňky, která se nebude při filtrování "schovávat" - v mém případě tedy píši do prvního řádku.
Funkce má dva parametry.

• Prvním je číslo, které určuje funkci použitou pro sečtení/zprůměrování/jinou agregaci dat. Např.:
• 1 - Průměr
• 2 - Počet
• 9 - Součet

Čísla si samozřejmě nemusím pamatovat - mohu využít rozbalovací nápovědu Excelu.

• Druhým parametrem je sloupec, který se má počítat.

Zápis celé funkce v našem případě vypadá takto:
=SUBTOTAL(9;D:D)
Devítka proto, že jde o součet, D znamená sloupec který se sčítá.

Důležité je, že oproti standardnímu součtu se tento součet při používání filtru mění.

Vypnutí automatického přepočítávání vzorců

Příklad

V Excelu někdy pracujeme s opravdu velkými tabulkami a dlouhými řadami záznamů. Pokud pro takto velké tabulky provádíme rozsáhlejší výpočty, může se stát, že se práce se souborem neúnosně zpomaluje.

Excel je defaultně nastavený tak, že při každé změně nějakého vzorečku přepočte všechny vzorce. U velkých souborů ale trvá přepočtení i několik minut - a pokud po každé změně čehokoliv zablokuji Excel (a současně většinu dalších aplikací) na několik minut, je to pěkná otrava.

Že Excel zrovna stávkuje z důvodu probíhajícího výpočtu zjistím v pravé dolní liště podle stavu "Calculating" a uvedeného procenta:

Návod

Pomoc je jednoduchá.

Stačí vypnout automatické přepočítávání a nastavit jej na ruční. Excel pak nebude vzorce přepočítávat hned, ale počká na přímý příkaz uživatele.

Takže si mohu zadat všechny potřebné vzorce a až nakonec nechat vše přepočítat.

Přepnutí výpočtů z automatického na ruční se provádí v kartě Formulas/Vzorce v Calculation Options:

Změním tedy nastavení a píšu všechny vzorce, které potřebuji. Pak, pokaždé když chci provést výpočet, buď kliknu na vedlejší tlačítko nebo zmáčknu F9 - tím se vše přepočte.

• Vím, že když pracuji v tomto režimu manuálních výpočtů, mám občas tendenci na to zapomenout a pak se divit, že se vzorce nepřepočtou - takže si na to je dobré dávat pozor.
• Pro malé tabulky, kde přepočet trvá pár desetin vteřiny (a takových je většina), toto vypnutí většinou nemá smysl používat.
• Můžete si všimnout, že Excel nabízí tlačítko Calculate now (odpovídá F9) a také Calculate sheet. Rozdíl je ten, že první volba přepočte všechny listy, zatímco druhá jen ten aktivní.

V některých případech je velmi vhodné vypnout přepočítávání při ukládání - to abychom nemuseli při každém uložení čekat na přepočet.

To se dělá ve File / Options / Formulas:

Klávesová zkratka pro vložení dnešního data

Příklad

Potřebuji do buňky vepsat dnešní datum. Např. 21.7.2013.

Návod

Datum mohu standardně vypsat - postupně jednotlivé číslice a tečky.
Nebo mohu, a to je výrazně snazší, stisknout Ctrl + ; (Současně držet Ctrl a stisknout středník). Protože dnešní datum zadáváme relativně často, může nám tato zkratka být celkem užitečná.
Vložení dnešního data prostřednictvím Ctrl + ; je něco jiného než použití funkce DNES/TODAY.

• Při vložení přes Ctrl + ; mě zůstane datum jednou provždy stejný z toho dne, kdy jsem jej zapsal a nebude se měnit. 
• Při použití funkce DNES/TODAY bude zobrazen vždy aktuální datum - podle toho, který den sešit otevřu.

Nový kurs pro firmy - nejpraktičtější postupy v Excelu

Excel už školíme několik let. A během této doby si všímáme toho, že některá témata jsou pro naše studenty stále mimořádně atraktivní - postupy, které jsou velmi dobře použitelné v podnikové praxi.

Proto jsme se rozhodli vytvořit nový dvoudenní kurs pro firmy, zaměřený právě na takováto témata - vysvětlovaná na praktických příkladech.

Pro více informací o tomto kursu klikněte zde.

Cena kursu je 7500 Kč - což např. při 8 účastnících vychází cca na 940 Kč za dvoudenní kurs.

Nahrazování znaků v Excelu

Při práci s Excelem se docela často dostanete do situace, kdy potřebujete nahrazovat nějaký text jiným. V tomto příkladu nahrazují čárky tečkami, nicméně velmi podobně to funguje s jakýmikoliv textovými řetězci. Mám v zásadě dvě možnosti - buď použít Najít / nahradit, nebo funkci SUBSTITUTE / DOSADIT.

Najít / nahradit

Dřevní, ale často velmi efektivní metoda.
Prostě stisknete Ctrl + F a vyberete, co za co se má nahradit.
V tomto případě mám sloupec datumů, kde jsou dny a měsíce oddělené čárkou místo tečky.

Mně se ale lépe pracuje s tečkami. Abych čárky na tečky změnil, použiji Ctrl + F, nastavím že se mění čárky na tečky a takto vypadá výsledek:

Najít / nahradit je velmi užitečná funkce, má ale jednu zásadní nevýhodu - funguje jednorázově. Tedy kdybych např. do tabulky uvedené nahoře přidal další datum s čárkami, tak se mi na tečky už nezmění do doby, než znovu použiji Najít / nahradit.
Pokud mi toto vadí, pomůže mi funkce SUBSTITUTE / DOSADIT.

SUBSTITUTE / DOSADIT

Tato funkce nahrazuje ve vybraném textu určitý text jiným textem.

Pokud bych ji chtěl použít v předchozím případě, vypadal by zápis takto:

=DOSADIT(A1;",";".")

• První argument je text, se kterým pracuji
• Druhý argument je text, který se má najít
• Třetí argument je text, kterým se má text ze druhého argumentu nahradit
• Čtvrtý, nepovinný argument je číslo výskytu, na které se má výměna použít. Např. zápis DOSADIT("tadydadyda";"a";"X";2) vyhodí "tadydXdyda - protože se nahradilo druhé áčko velkým ikskem.