DZÍSKAT (DGET)

V jednom z minulých článků jsme se seznámili s databázovými funkcemi. Většina z nich funguje podobně jako funkce typu SUMIFS, COUNTIFS apod. (tedy použijí určitou agregující funkci nad položkami odpovídajícími filtru), jen se v nich jinak definují kritéria.
Existuje ale jedna databázová funkce, která se vymyká, a to funkce DZÍSKAT (anglicky DGET).
Tato funkce neagreguje (nesčítá, neprůměruje, nepočítá) hodnoty, ale vybere konkrétní hodnotu. Tedy její fungování je spíše podobné funkci SVYHLEDAT.

Příklad

Chci najít jméno zaměstnance, který je muž a pracuje v oddělení HR, v následující tabulce:

Návod

Připravím si tabulku kritérií (více o nich v kapitole o rozšířených filtrech):

A zapíšu funkci:
=DZÍSKAT(A:E;"Jméno";G1:H2)

Prvním parametrem je umístění databáze, druhým parametrem označení sloupce který mě zajímá, a třetím parametrem tabulka kritérií.
Ve výsledku zjistím, že hledaným zaměstnancem je Karel:

Pozor. Pokud funkce DZÍSKAT najde více hodnot odpovídajích filtru, vrátí chybovou hodnotu - narozdíl od funkce SVYHLEDAT, která vrátí první nalezenou hodnotu.

Hromadné pojmenování oblastí

V Excelu lze pojmenovat oblast buněk tak, abychom se na ni pak mohli jednodušeji odkazovat. Pokud např. pojmenujete oblast mezi A1 a B10 názvem "moje oblast", můžete ji sčítat vzorcem "=SUMA(mojeoblast)" místo vzorce "=SUMA(A1:A10)". To je v některých případech jednodušší, navíc se to často hodí při navazujícím použití funkce NEPŘÍMÝ.ODKAZ(INDIRECT), třeba pro tvorbu kaskádových vybíracích menu.
V některých případech ale chceme pojmenovat více oblastí a nechceme to dělat postupně. Pak je možné použít hromadné pojmenování.

Příklad

V těchto datech chceme např. označit sloupce "jméno", "příjmení", "telefon", "oddělení".

Návod

Označíme všechny sloupce v celé délce, do které mají sahat pojmenované oblasti.

V kartě Vzorce klikneme na Vytvořit z výběru.

V našem případě zaškrtneme "Horní řádek".

A je hotovo. To, že se oblasti skutečně vytvořili, můžeme ověřit v roletce po kliknutí na šipečku s názvy:

Vytvoření seznamu unikátních náhodních celých čísel

Excel používá pro generování náhodných čísel v zásadě dvě funkce - NÁHČÍSLO/RAND (generuje náhodné mezi nulou a jedničkou s mnoha desetinnými místy) a RANDBETWEEN (generuje celá čísla v rozsahu, který zadáme).
Někdy ale potřebujeme v každé buňce jiné náhodné celé číslo - resp. určitý počet čísel v náhodném pořadí.

Návod

Je možné v jednom sloupci použít funkci NÁHČÍSLO (v anglické verzi RAND).

Ve dluhém sloupci pak pomocí funkce RANK zjistit pořadí tohoto čísla.

A ve výsledku máme čísla od jedné do deseti v náhodném pořadí.
Na základě tohoto sloupce je samozřejmě možné i řadit - ale předtím je ještě nutné v tomto sloupci převést sloupce na hodnoty, aby se čísla stále neměnila.

Kontingenční tabulka počítající unikátní, jedinečné hodnoty

V jednom reportu jsem narazil na zajímavý problém. Měl jsem seznam jazykových kurzů ve škole. Ve sloupečcích bylo jméno lektora, jazyk kurzu a nějaký identifikátor kurzu.
Vypadalo zhruba takto:

Úkolem bylo zjistit, kolik jazyků který lektor vyučuje. A zjistit to kontingenční tabulkou. Zjistit, kolik kurzů lektor vyučuje, nebo které jazyky vyučuje, by zabralo asi tak vteřinu a půl. Pokud ale chci, aby tabulka vypadala takto:

tak už to taková legrace není. Prošel jsem nějaké návody na webu a následující postup se mi zdál nejsnazší.
Je třeba vytvořit si a do tabulky s daty použít nový sloupec, a tam zapsat takovýto (nebo obdobný) vzorec:
=IF(COUNTIFS($B$1:B2;B2;$C$1:C2;C2)=1;1;0)
Vysvětlení:
Výstupem je 0 nebo 1, což závisí na dvou kritériích - na jméně lektora a na jazyce. Protože tato kritéria mají ve funkci COUNTIFS mezi sebou vztah AND (tedy musí platit obě), je výstupem funkce COUNTIFS v určitém řádku celkový počet řádků tohoto lektora na tomto jazyce. U buňky Novák / Angličtina je to počet buněk Novák / Angličtina ve sledované oblasti.
Všimněte si, že oblast funkce COUNTIFS je díky šikovným absolutnm odkazům "roztahovací" a sama funkce tedy zjišťuje, kolikrát se tato kombinace lektora a jazyka objevila pouze odshora až k příslušnému řádku (tedy ne v celé oblasti, ale jen od buňky nahoru).
S použitím samotné funkce COUNTIFS by byl výsledek takovýto:

My si ale pro kontingenční tabulku potřebujeme připravit data tak, aby se za každou kombinaci lektora  a jazyka "pričetla" jen jednička.
Proto z funkce COUNTIFS vytvoříme podmínku - její výsledek budeme porovnávat s jedničkou. Funkce tedy bude zobrazovat 1 v případě, že se jedná o první výskyt, a 0 v ostatních případech.
Tím zaručíme, že v novém sloupci bude 1 pouze u prvního výskytu, a jinde budou nuly.
Výsledek bude vypadat takto:

Když pak poslední sloupec dáme do pole hodnot kontingenční tabulky, číslo bude ukazovat, kolik jazyků lektor vyučuje.

Tři způsoby zápisu odkazu na buňku v Excelu

V každém vzorci nebo funkci v Excelu se potřebujeme odkazovat na buňku. Existuje více způsobů, jak na buňku odkazovat - v tomto článku si ukážeme tři z nich.

Příklad:

V následující tabulce mám několik druhů zboží. 

 U každého mám počet kusů a cenu za kus, v posledním sloupci potřebuju získat celkovou cenu. Výpočetně se jedy jedná o naprosto jednoduchou věc - ve sloupci D chci vynásobit to, co je ve sloupci B a to, co je ve sloupci C.

Takto nějak bude vypadat výsledek, ať použijeme jakýkoliv způsob zápisu:

1. Odkaz na jednu buňku s roztažením vzorce

První způsob je nejčastěji používaný. Do buňky D2 napíšeme:

=B2*C2

a vzorec roztáhneme.

Místo zapsání "B2" a "C2" samozřejmě můžeme také na buňky kliknout.

2. Odkaz na celý sloupec

Do buňky D2 můžeme také zapsat:

=B:B*C:C

a vzorec roztáhneme.

Místo zapsání "B:B" a "C:C" můžeme také kliknout na záhlaví sloupců.

Tím říkáme, že se ve sloupci D mají pronásobit hodnoty ze sloupce B a C. Excel to chápe tak, že do každé buňky ve sloupci D spočte součin buněk v B a C, které jsou na stejném řádku.

Výhodou je větší přehlednost - nemusím hlídat a sledovat, jestli odkazuji na stejný řádek - což téměř vždy odkazuji. Neměla by se pak vyskytovat chyba, že se ukliknu a omylem odkazuji o řádek níž nebo výš.

Nevýhodou je, že se nemohu odkázat na buňku v jiném řádku než v tom, do kterého zapisuji. 

3. Tabulkové odkazy

Je také možné vložit tabulku a odkazy pak vytvářet v tabulce.

Pak zapíšeme (v praxi téměř vždy spíše naklikáme) toto:

=[@Cena]*[@[Počet prodaných kusů]]

Více výhodách tabulek více tady a o jejich vkládání tady.

Všechny uvedené způsoby mohu kombinovat - tedy např. i v tabulce mohu používat běžné odkazy. Jen nestačí buňky naklikat, odkazy se musí "ručně" zapsat.

Při vložení tabulky se obvykle změní i barvy buněk tak, jako v ukázce. Není to ale nutné - barvy lze odstranit.

Občas se ještě používá odkaz typu R1C1 - ale osobně si nemyslím, že je nutné jej znát.

Databázové funkce v Excelu - DSUMA / DSUM

V tomto článku si představíme funkci DSUMA / v anglických verzích DSUM. Jedná se o jednu z několika tzv. databázových funkcí, které fungují podobně. Jedná se o tyto funkce:

• DPRŮMĚR - Vrátí průměr vybraných položek databáze. 
• DPOČET - Spočítá buňky databáze obsahující čísla. 
• DPOČET2 - Spočítá buňky databáze, které nejsou prázdné. 
• DZÍSKAT - Extrahuje z databáze jeden záznam splňující zadaná kritéria. 
• DMAX - Vrátí maximální hodnotu z vybraných položek databáze. 
• DMIN - Vrátí minimální hodnotu z vybraných položek databáze. 
• DSOUČIN - Vynásobí hodnoty určitého pole záznamů v databázi, které splňují daná kritéria. DSMDOCH.VÝBĚR - Odhadne směrodatnou odchylku výběru vybraných položek databáze. 
• DSMODCH - Vypočte směrodatnou odchylku základního souboru vybraných položek databáze. 
• DSUMA - Sečte čísla v poli (sloupci) záznamů databáze, které splňují zadaná kritéria. 
• DVAR.VÝBĚR - Odhadne rozptyl výběru vybraných položek databáze. 
• DVAR - Vypočte rozptyl základního souboru vybraných položek databáze.

Příklad

Typické použití uvedených funkcí si ukážeme na DSUMA / DSUM.
Mám tabulku s několika auty a chci určit, kolik dohromady stojí Fiaty a Citroeny. (Od pohledu je už teď zřejmé, že je to 156 000).

Návod

Musíme si připravit tzv. kriteriální tabulku. Ta vyjadřuje, kterých řádků se má početní operace (v našem případě obyčejné sčítání) týkat. Jinými slovy cenu kterých aut budeme chtít sečíst.
V našem případě bude tabulka vypadat takto:

Záhlaví "Značka" je uvedené proto, že budeme filtrovat podle značky auta. Slova "Fiat" a "Citroen" logicky zastupují hodnoty, které se mají vyfiltrovat (jejichž cena se má posčítat).
Teď vložíme nebo zapíšeme funkci a sice takto:
=DSUM(A1:E10;"Cena";J1:J3)
resp. v české verzi:
=DSUMA(A1:E10;"Cena";J1:J3)

• A1:E10 proto, že v této oblasti je původní tabulka
• "Cena" proto, že právě součet cen je to, o co nám jde. Šlo by nahradit odkazem na buňku, v našem případě C1.
• J1:13 proto, že v této oblasti se nachází tabulka kritérií

Výsledek pak vypadá takto:

U oblasti si filtrovacími kritérii si uvědomte, že jejich logiku již možná znáte z používání rozšířených filtrů - je to stejný princip.

Funkce TABULKA(TABELOVAT) / TABLE

Funkce TABULKA(v některých českých verzích také TABELOVAT, anglicky TABLE) je docela specifická, například v tom, že dává smysl použít ji jen jako maticovou funkci .Nebo tím, že obecně neprovádí žádný výpočet, ale přiřazuje výsledky výpočtu k většímu množství čísel, a tím umožňuje modelovat různé scénáře. Nebo jinými slovy sledovat dopad změny jedné buňky nebo dvou buněk na skupinu hodnot. To zní dost nesrozumitelně a lépe to popsat neumím, tak takže použijeme příklad.

Příklad

Naše firma má možnost oslovit zákazníky s nabídkou nového produktu. Toto oslovení bude něco stát, a zvažujeme, jestli se do něj pustit.
Dopředu víme (modrá pole):

• Kolik zákazníků oslovíme (1500)
• Kolik toto oslovení celkově bude stát (200000)

Zatím přesně nevíme (oranžová pole):

• Kolik zákazníků z oslovených si výrobek koupí (pro začátek vyplníme třeba 20%)
• Kolik přesně na jednom výrobku vyděláme (pro začátek vyplníme třeba 500)

Výsledkem má být tabulka, kde budou ve sloupcích různé varianty zisku, v řádcích různé varianty procenta úspěšných oslovení, a v buňkách relevantní hodnoty výsledného zisku pro danou kombinaci. Zatím je ale prázdná.

Návod

Nejprve do levého rohu (A11) zapíšeme vzorec, od kterého se má výpočet odvíjet.
Výnosy z akce se budou počítat jako počet oslovených zákazníků * úspěšnost * zisk z jednoho prodaného výrobku, náklady jsou paušálně dané.
V našem případě je tedy vzorec:
=A2*A4*A6-A8

Vidíme nějaký výsledek, ale o ten nám teď nejde.
Teď přichází chvíle pro funkci Tabulka.
Nejprve označíme celou tabulku, a pak jdeme na Data / Citlivostní analýza / Tabulka dat.

Ve Vstupní buňce řádku nastavujeme, která buňka na vstupu se mění podle záhlaví sloupce. V našem případě je to buňka A6, protože v ní je odhad zisku na jeden výrobek, jehož různé možnosti jsou zapsané v záhlaví sloupců.
Do Vstupní buňky sloupce pak nastavíme buňku A4. V té je odhadované procento úspěchů při oslovování, jehož různé hodnoty jsou v záhlavích řádků.

Odklepneme a je hotovo.
V tabulce teď máme různé hodnoty pro různé kombinace "žlutých" vstupů. Všimněte si, že v tabulce se objevila maticová funkce Tabulka / Table

Pro přehlednost ještě můžeme použít podmíněné formátování pomocí škál a takto vypadá výsledek:

Z výsledku poznáme třeba to, že aby akce byla zisková, musí být při úspěšnosti oslovení 20% zisk na zákazníka alespoň 700, při úspěšnosti 30% pak stačí 500. Při úspěšnosti 40% a více je pak akce zisková vždy.
Poznámky:

• Všimněte si, že výsledky v tabulce se nemění v případě, že přepisujete hodnoty ve vstupních "žlutých" buňkách. Ty hodnoty můžete klidně smazat - jsou tam víceméně jen k tomu, aby se lépe zapisoval vzorec.
• Funkci Table můžeme většinou docela šikovně nahradit šikovným použitím absolutních nebo smíšených odkazů. Je jen na vás, co si vyberete - jestli skousnete raději složitější smíšené vzorce nebo maticový vzorec. Do buňky B12 bychom v takovém případě zapsali =$A$2*$A12*B$11-$A$8 a poroztahovali.