Jak zobrazit průměrné denní tržby v kontingenční tabulce

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. 

 

Jak zobrazit průměrné denní tržby v kontingenční tabulce 1
Obrázek č.1 Tvorba kontingenční tabulky

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.

Jak zobrazit průměrné denní tržby v kontingenční tabulce 2
Obrázek č.2 Změna formátu na měnu bez desetinných míst

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ý. 

Jak zobrazit průměrné denní tržby v kontingenční tabulce 0
Obrázek č.3 Nesprávně určené dny

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. 

Jak zobrazit průměrné denní tržby v kontingenční tabulce 3
Obrázek č.4 Funkce COUNTIF

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ů.

Jak zobrazit průměrné denní tržby v kontingenční tabulce 4
Obrázek č.5 Funkce KDYŽ v kombinaci s funkcí COUNTIF

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. 

Jak zobrazit průměrné denní tržby v kontingenční tabulce 5
Obrázek č.6 Změna zdrjových dat 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.

Obrázek č.7 Správný počet jedinečných dnů

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. 

Obrázek č.8 Počítané pole v kontingenční tabulce

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.

Jak zobrazit průměrné denní tržby v kontingenční tabulce 8
Obrázek č.9 Výsledná tabulka s průměrnými denními tržbami

Hotovo. Teď máme v kontingenční tabulce zobrazené průměrné denní tržby.

MOHLO BY VÁS ZAJÍMAT

Jedna odpověď

Napsat komentář

Vaše e-mailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *