250 000 návštěvníků blogu

Nedávno jsme na blogu www.excelentnitriky.com přivítali čtvrtmiliontého unikátního návštěvníka. Na nepornografickou stránku myslím slušný výkon :)

Tři základní pravidla zapisování vzorečků

Pokud jste pokročilými uživateli, nemá smysl, abyste četli dále.

V tomto článečku chci upozornit na tři pravidla pro vkládání vzorců (funkcí), která (často marně) propaguji na kurzech pro začátečníky. Jedná se o velmi jednoduché až banální zásady - přesto je méně zkušení uživatelé často porušují.

1. Vzorce nebo funkce vkládejte to buňky, kde má být výsledek
   Ano, zní to samozřejmě - nicméně uživatelé se často natolik soustředí na vzorec samotný, že jej píší jinam než chtějí.
2. Zápis vzorce začíná rovnítkem (znakem "=").
   Toto nemusíme dodržet, pokud vkládáme standardní funkci (např. "Průměr"), ale ani v tomto případě tím nic nezkazíme.
3. Zápis vzorce ukončujte klávesou Enter.
   Když ukončíme jinak, můžeme si vzorec rozbít. Entrovat můžeme i v případě, že nejsme kurzorem na konci vzorce.

Takže ode dneška už v tomto nechybujte - chybujte raději v něčem jiném :)

Nová učebna - aneb rosteme....

Od tohoto týdne Vás rádi uvítáme nejen ve stávající učebně na Andělu, ale nově i v učebně u Palmovky.

Jsme rádi, že k nám rádi chodíte - v případě zájmu otevřeme ještě další učebny :)

Jak vkládat funkce

Na tomto blogu je řada návodů na používání funkcí. Nikde tu ale zatím nejsou podrobně vysvětlené způsoby, jak funkce vkládat. Pojďme se na ně podívat. Předesílám, že se jedná tzv. standardní funkce, tedy funkce, které se nějak jmenují (Průměr, Suma, Svyhledat...).
Také předesílám Neuvádím tady zdaleka všechny možnosti, navíc ty, co tady uvádím, se dají různě kombinovat.

1. Zápis funkce do řádku vzorců

Asi nejméně uživatelsky přívětivé je zapisovat funkce přímo do buňky, resp. do řádku vzorců.
Pokud se do toho pustíme, tak:

• Klikneme do buňky, kde má být výsledek
• Napíšeme rovnítko "="
• Napíšeme název funkce. Excel v průběhu zápisu název funkce "našeptává".
  Když už ne "našeptané" to, co potřebujete, nemačkáme Enter, ale Tabulátor (nebo myší klikneme na našeptaný název). Tím se název funkce dokončí a napíše se začátek závorky.

• Za název funkce do závorky zapíšeme parametry funkce oddělené vzájemně středníky a uzavřeme závorku.

• Stiskneme Enter

2. Vkládání přes kartu Vzorce

Karta Vzorce v zásadě neumí nic jiného, než vkládat funkce. Můžeme si vybrat konkrétní skupinu a dále konkrétní funkci, nebo použít tlačítko Fx vlevo.

3. Vkládání přes tlačítko Fx vedle příkazového řádku

Toto je moje oblíbená možnost. Příkazový řádek je v Excelu dostupný v každé situaci, ať jsme na kterékoliv kartě.

Kliknutím na Fx se dostaneme do okna, kde se vybírají funkce.

Obvykle je to, co hledáme, v kategorii "Naposledy použité". Pokud není, můžeme si vybrat jinou kategorii, případně jít do kategorie "Vše", kde, jak se dá čekat, najdeme úplně všechny funkce.

Pokud víme, jak se funkce jmenuje, můžeme tento název napsat (alespoň přibližně) do okna "Vyhledat funkci" a odentrovat. Funkce se pak ukáže v seznamu.
Každopádně když funkci najdeme, klikneme na ni a otevře se dialog, kde zapisujeme její parametry.

Vždy co řádek, to parametr.
V dialogu si všimněte možnosti zobrazení nápovědy. Číst nápovědy nebaví nikoho, ale nápovědy k funkcím v Excelu jsou často napsané překvapivě lidsky a srozumitelně. Takže když nevíte, nebojte se na nápovědu kliknout.

Všechny cesty vedou do vzorce

Je dobré uvědomit si, že ať si vyberu kteroukoliv možnost, výsledkem je zápis vzorce v řádku vzorců (po rozkliknutí buňky viditelný i v buňce).
Pokud už mám vzorec zapsaný a chci jej upravit, mohu jej prostě přepsat, nebo kliknout do buňky se vzorcem a pak na Fx. Tím se dostanu di dialogu pro zápis.

Sledování spotřeby a nákladů na auto v Excelu

Dávám ke stažení tabulku, kterou už léta používám. Hodí se pro sledování spotřeby auta a také nákladů na opravy auta.
Není na ní vůbec nic světoborného z pohledu Excelu - ale myslím, že je docela praktická. Prostě zapisujete, kolik jste natankovali při jakém stavu tachometru, a kolik jste nechali v servisu, a ostatní údaje Vám z tabulky lezou samy.
Tak kdybyste se chtěli podívat na zoubek svému autu, vyzkoušejte...
Tabulka ke stažení

Časová osa v kontingenční tabulce

Časová osa je (od verze Excelu 2013) jedním z filtrů, které můžeme použít v kontingenční tabulce. Jedná se o:

• Pole Filtry (jedno ze čtyř základních polí kontingenční tabulky)
• Průřez (rychlý, klikací filtr)
• Časová osa - popsaná v tomto článku (je dostupná až od verze Excelu 2013)

Jak použít nástroj Časová osa

Časová osa má smysl v kontingenčních tabulkách, které chceme filtrovat dle času.

Např. z této tabulky prodejů mohu chtít vytvořit kontingenční tabulku omezenou pouze na prodeje realizované v druhé polovině ledna 2012í:

Začnu tím, že vytvořím základní kontingenční tabulku. Řekněme, že budu sledovat, jak velké tržby připadají na jednotlivé zaměstnance:

Teď budu chtít omezit tabulku např. pouze na období druhé poloviny ledna 2012.

Kliknu na Analýza a dále na Vložit časovou osu a v dialogu zaškrtnu Den - to je jediné pole z tabulky, které obsahuje data.

Už se mi vložila časová osa. Já ale potřebuji podrobnější členění než měsíce, proto se přepnu na dny.

Roztáhnu časovou osu na příslušné dny. Všimnu si, že čísla v tabulce se zmenšila - protože zobrazují tržby za kratší časové období než původní tabulka.

Dalším roztahováním si pak mohu toto období upravovat.
Pokud máte raději videonávody, tak tenhle popisuje to, co je obsahem tohoto článku, v necelé minutě. Doporučuji zvětšit na celou plochu.

Funkce XOR

V Excelu od nepaměti existuje funkce A(AND), která ověřuje, jestli jsou všechny vstupní hodnoty pravdivé. Pak také existuje funkce NEBO(OR), která ověřuje, jestli je alespoň jeden ze vstupů pravdivý.

Od verze 2013 existuje také funkce XOR, která ověřuje, jestli jsou hodnoty unikátní.

Použití funkce XOR pro dva logické vstupy

Funkce XOR vrátí hodnotu NEPRAVDA v případě, že oba vstupy jsou PRAVDA (resp. 1) nebo NEPRAVDA (resp. 0). V opačném případě, tedy když je jedno PRAVDA a druhé NEPRAVDA, vrátí PRAVDA.

Jinak řečeno - funkce poskytuje odpověď na otázku, jestli jsou vstupní hodnoty různé.

Použití funkce XOR pro tři a více logických vstupů

Při více argumentech než dvou je výstupem PRAVDA v případě, že počet vstupů PRAVDA je lichý. Neptejte se mě ale, k čemu se toto dá využití :)

Funkce FORMULATEXT

V tabulkách občas potřebujeme, aby se místo výsledků výpočtů zobrazovaly výpočtové vzorce.
Od verze 2013 proto Excel obsahuje funkci FORMULATEXT, která vezme vzorec a zobrazí jej jako text.
Funkce má nejjednodušší možný zápis - jediným jejím parametrem je buňka se vzorcem, který se má zobrazovat.

V našem případě je v buňce A1 vzorec =1+1. V buňce B1, která se přes funkci FORMULATEXT odkazuje na A1, se tedy zobrazuje =1+1.

Desátá hádanka - hierarchická data

Máte k dispozici takovouto tabulku:

Jsou v ní zaměstnanci firmy. Každý zaměstnanec má svého nadřízeného, vzájemné propojení zaměstnanců pak vytváří hierarchickou strukturu. U každého zaměstnance je uvedená mzda.
U "d" není žádný nadřízený, protože "d" je nejvyšší šéf.
Vymyslete vzorec, který do posledního sloupce sečte mzdu zaměstnance se mzdou všech jeho podřízených na všech úrovních. Jinými slovy - veškeré mzdy skupiny, které manažer velí, včetně jeho samého. U "d" tedy logicky bude součet mezd za celou firmu. U "a" bude jen jeho mzda - protože "a" žádné podřízené nemá.
Takto vypadá výsledek:

Zkuste vymyslet vzorec, kterým se k výsledku dostanete. Vzorec musí být platný pro neomezený počet úrovní a stejný (roztáhnutelný) pro všechny řádky. Pokud vymyslíte jednodušší vzorec než já, máte plusové body :)
Tabulka ke stažení

Dynamická definice oblasti buněk

I pokud s Excelem pracujete delší dobu, možná vás, stejně jako mě, zatím nenapadlo dynamicky definovat oblasti buněk. Přitom využití je zajímavé - např. pro ověřování dat nebo pro kontingenční tabulky.

Příklad

Chcete použít ověření dat založené na výběru ze seznamu.

Např. takto:

Zápis oblasti, ze které se čerpají hodnoty pro rozbalovací seznam, vypadá takto:

Všechno funguje. Jenomže co když přidáme do seznamu nového zaměstnance?

Tento zaměstnanec, připsaný do seznamu pod předchozí, ve vybíracím seznamu nebude - protože je mimo oblast, ze které vybírací seznam čerpá.

Návod

Řešením je vytvořit odkaz na oblast ne výčtem konkrétních buněk, ale proměnlivou oblastí - tak, aby oblast byla vždy přesně tak dlouhá, kolik je v ní buněk.

K tomu využijeme kombinaci funkcí POČET2 (anglicky COUNTA) a POSUN (anglicky OFFSET).

Zápis oblasti pro vybírací menu pak vypadá takto:

=POSUN(A2;0;0;POČET2($A:$A)-1;1)

Protože:

• POSUN - název funkce, která definuje oblast na základě parametrů. Pro více informací rozklikněte.
• A2 - oblast, kde začíná oblast buněk pro výběrové pole - bez ohledu na to, kam až sahá
• 0 - buňky se nikam neposouvají, teď nás nezajímá
• 0 - to samé jako předchozí bod
• POČET2($A:$A) - vyjadřuje rozměr oblasti směrem dolů. Je mazaně definovaná počtem neprázdných buněk ve sloupci A
• -1 - číslo získané v předchozím bodě je třeba zmenšit o jedničku, protože je v něm započítané i záhlaví - a to v rozevíracím seznamu nemá být
• 1 - vyjadřuje rozměr oblasti směrem doprava. Jinými slovy - vybírací menu se bere jen z jednoho sloupce

Vzniklé vybírací menu bude vždy zobrazovat všechny položky - bez ohledu na to, kolik jich je (pokud budou za sebou a nebude mezi nimi mezera).

Dynamická definice pojmenované oblasti

Pokud chci, mohu dynamicky i definovat pojmenovanou oblast. Pak bude oblast s určitým názvem (např. "zdrojovadata" různě velká podle toho, jaká data obsahuje.

A nad takovou oblastí je pak samozřejmě možné vytvořit kontingenční tabulku.