Dnes se podíváme na velmi praktický příklad ze světa kontingenčních tabulek. Budeme se snažit v kontingenční tabulce spočítat průměrné denní tržby. Na první dobrou, to zní jako úkol, který by měl být velmi jednoduchý. A do jisté míry i je, pokud znáte potřebné kroky, které musíte udělat. A přesně tyto kroky, jak zobrazit průměrné denní tržby v kontingenční tabulce, si dnes ve videu ukážeme.
Excelový soubor ke stažení
Jak zobrazit průměrné denní tržby v kontingenční tabulce
V dnešní ukázce máme jednoduchou tabulku dat, ve které máme pouze dva sloupce, a to datumy a tržby (sloupec A a B). Naším úkolem je pomocí kontingenčních tabulek zobrazit průměrné denní tržby v jednotlivých měsících. Prvním problémem, který u tohoto úkolu je, jak zobrazit v kontingenční tabulce počet jedinečných dnů pro každý měsíc. A druhým problémem je, jak následně dopočítat průměrnou tržbu na den.
Klikneme do zdrojové tabulky a na kartě Vložení vybereme Kontingenční tabulka a potvrdíme vytvoření kontingenční tabulky z tabulky nebo oblasti. Pro jednoduchost vložíme kontingenční tabulku na stejný list vedle zdrojové tabulky (D1). Na řádky kontingenční tabulky vložíme datumy. Datumy se automaticky seskupí do měsíců, což poznáme podle toho, že se u jednotlivých měsíců objeví znaménka plus. Když je rozklikneme, tak se měsíc rozbalí do jednotlivých dní. V tomto případě nám postačí seskupení na úrovni měsíců, jednotlivé dny nepotřebujeme, takže můžeme pole datum a dny z tabulky vyhodit a nechat tam pouze měsíce.
Dále vložíme do polí hodnoty tržby. Rovnou upravíme formát tržeb. Klikneme do sloupce tržeb a vybereme Formát čísla. Zde vybereme měnu bez desetinných míst. Potvrdíme a teď máme v kontingenční tabulce zobrazené součty tržeb pro jednotlivé dny a měsíce.
Teď potřebujeme do kontingenční tabulky dostat počet jedinečných dnů v kontingenční tabulce. Teoreticky by nás mohlo napadnout vzít pole datumy a vložit ho do pole hodnoty. Přetáhneme pole datum vedle tržeb do pole hodnoty. V kontingenční tabulce se zobrazil počet dní v měsíci, nicméně se nejedná o jedinečný počet dní. Některé datumy se nám totiž ve zdrojové tabulce opakují. V lednu máme ve zdrojové tabulce sice celkem 30 dnů, ale pouze 22 jedinečných dnů. Tento počet dnů tedy není správný.
Musíme se proto vrátit ke zdrojové tabulce a trochu ji upravit. Do zdrojové tabulky přidáme nový sloupec, který nazveme Jedinečný počet. V tomto sloupci označíme dny, které jsou jedinečné. K označení jedinečných dnů nám pomůže funkce COUNTIF. Napíšeme funkci COUNTIF, kde použijeme trik na odhalení jedinečných hodnot. Označíme první datum, napíšeme dvojtečku a znovu označíme první datum, v tomto rozpětí musíme ještě první buňku plně zafixovat klávesou F4. Napíšeme středník a jako kritérium opět označíme první buňku. Funkci potvrdíme a pošleme ji dolů. Funkce COUNTIF přiřadila ke každému datumu pořadové číslo. U prvního výskytu datumu napsala jedničku, pokud se datum v seznamu vyskytuje podruhé, vrátila číslo dva, pokud máme některé datum v seznamu třikrát, tak u třetího výskytu vrátila funkce COUNTIF trojku. Zajímají nás jedinečné dny, takže nás zajímají řádky, na kterých funkce COUNTIF vrátila jedničky.
Celou funkci COUNTIF tak můžeme zabalit do funkce KDYŽ, kde podmínkou bude, že se funkce COUNTIF rovná jedné. Pokud je podmínka splněná, tak chceme vrátit jedničku a pokud podmínka splněná není, tak chceme vrátit nulu. Funkci potvrdíme a pošleme ji dolů.
Teď se vrátíme ke kontingenční tabulce a přidáme tento pomocný sloupec do kontingenční tabulky. Klikneme do tabulky a na kartě Analýza kontingenční tabulky vybereme Změnit zdroj dat a rozšíříme zdroj kontingenční tabulky o nový sloupec. Nový sloupec s názvem jedinečný počet se objevil v polích kontingenční tabulky.
Teď můžeme do kontingenční tabulky vložit nový sloupec Jedinečný počet. Teď máme správný počet jedinečných dnů v kontingenční tabulce.
Teď stačí mezi sebou vydělit pole celkový součet tržeb a počet jedinečných dnů. S tím pomůže počítané pole v kontingenční tabulce. Klikneme na jakoukoliv hodnotu v kontingenční tabulce a na kartě Analýza kontingenční tabulky vybereme Pole, položky a sady a zde Počítané pole. Nejprve pole pojmenujeme, nazveme ho třeba Průměrné denní tržby. Do pole vzorec vložíme nejprve pole tržby, které vydělíme polem Jedinečný počet. Toto je celé počítané pole, které chceme vložit do kontingenční tabulky. Potvrdíme a pole se vložilo jak do kontingenční tabulky, tak se přidalo mezi ostatní pole v pravém panelu.
Pro pořádek ještě upravíme formát tohoto nového pole. Klikneme do pole, vybereme Formát čísla a zde měnu bez desetinných míst. Potvrdíme a teď máme v kontingenční tabulce spočítané průměrné denní tržby pro jednotlivé měsíce.
Hotovo. Teď máme v kontingenční tabulce zobrazené průměrné denní tržby.
Jedna odpověď
😊