V kontingenčních tabulkách můžete pomocí souhrnných funkcí v polích hodnot kombinovat hodnoty z podkladových zdrojových dat. Pokud vám souhrnné funkce a vlastní výpočty neumožňují získat požadované výsledky, můžete v počítaných polích a počítaných položkách vytvořit vlastní vzorce. Můžete třeba přidat počítanou položku se vzorcem pro provize z prodeje, které se můžou lišit pro jednotlivé oblasti. Kontingenční tabulka pak bude automaticky zahrnovat provizi v mezisoučtech a celkových součtech.
Dalším způsobem výpočtu je použití Míry v Power Pivotu, které vytvoříte pomocí vzorce DAX (Data Analysis Expressions). Další informace najdete v tématu Vytvoření míry v Power Pivotu.
Kontingenční tabulky umožňují počítat data. Přečtěte si informace o metodách výpočtů, které jsou k dispozici, a o tom, jak typ zdrojových dat ovlivňuje výpočty a jak se v kontingenčních tabulkách a kontingenčních grafech používají vzorce.
Dostupné metody výpočtu
Pokud chcete počítat hodnoty v kontingenční tabulce, můžete použít některé nebo všechny následující typy metod výpočtů:
Souhrnné funkce v polích hodnot Data v oblasti hodnot shrnují podkladová zdrojová data v kontingenční tabulce. Například následující zdrojová data:
Vytvoří následující kontingenční tabulky a grafy. Pokud vytvoříte kontingenční graf z dat v kontingenční tabulce, hodnoty v tomto kontingenčním grafu odrážejí výpočty v přidružené sestavě kontingenční tabulky.
V kontingenční tabulce jsou v poli sloupce Měsíc položky Březen a Duben. Pole na řádku Oblast obsahuje položky Sever, Jih, Východ a Západ. Hodnota v průsečíku sloupce Duben a řádku Sever představuje celkové výnosy z prodeje ze záznamů ve zdrojových datech, která mají pro Měsíc hodnoty Duben a pro Oblast hodnoty Sever.
V kontingenčním grafu může být pole Oblast pole kategorie zobrazující položky Sever, Jih, Východ a Západ jako kategorie. Pole Měsíc může být pole řady, která zobrazuje položky Březen, Duben a Květen jako řadu zastoupenou v legendě. Pole Hodnoty s názvem Celkový prodej může obsahovat datové značky, které představují celkový výnos v jednotlivých oblastech za každý měsíc. Například jedna datová značka může představovat svojí pozicí na svislé (hodnotové) ose celkový prodej za duben v oblasti Sever.
Pro výpočty polí hodnot jsou pro všechny typy zdrojových dat kromě zdrojových dat OLAP (Online Analytical Processing) dostupné následující souhrnné funkce.
Funkce Shrnuje Součet Součet hodnot. Toto je výchozí funkce pro numerická data. Počet Počet datových hodnot. Souhrnná funkce Počet funguje stejně jako funkce POČET2. Počet je výchozí funkce pro jiná data, než jsou čísla. Průměr Průměr hodnot Maximum Největší hodnota Minimum Nejmenší hodnota Součin Součin hodnot Počet čísel Počet datových hodnot, které jsou čísla. Souhrnná funkce Počet čísel funguje stejně jako funkce POČET. Odhad směrodatné odchylky Odhad směrodatné odchylky základního souboru, kde vzorek tvoří podmnožinu celého základního souboru. StDevp Směrodatná odchylka základního souboru, kde základní soubor tvoří všechna data, ze kterých se má souhrn spočítat. Var Odhad rozptylu základního souboru, kde vzorek tvoří podmnožinu celého základního souboru. Rozptyl Rozptyl základního souboru, kde základní soubor tvoří všechna data, ze kterých se má souhrn spočítat.
Vlastní výpočty Vlastní výpočet zobrazuje hodnoty na základě dalších položek nebo buněk v datové oblasti. V datovém poli Celkový prodej můžete třeba zobrazit hodnoty jako procentní část prodeje za březen nebo jako mezisoučet položek v poli Měsíc.
Pro vlastní výpočty hodnot polí jsou dostupné následující funkce.Funkce Výsledek Žádný výpočet Zobrazí hodnotu zadanou do pole. % z celkového součtu Zobrazí hodnoty jako procento celkového součtu všech hodnot nebo datových bodů v sestavě. % ze součtu sloupce Zobrazí všechny hodnoty v každém sloupci nebo řadě jako procento součtu sloupce nebo řady. % ze součtu řádku Zobrazí hodnotu na každém řádku nebo v každé kategorii jako procento součtu řádku nebo kategorie. % z Zobrazí hodnoty jako procento z hodnoty Základní položky v Základním poli. % součtu nadřazeného řádku Hodnoty se vypočítají následujícím způsobem:
(hodnota položky) / (hodnota pro nadřazenou položku na řádcích)% součtu nadřazeného sloupce Hodnoty se vypočítají následujícím způsobem:
(hodnota položky) / (hodnota pro nadřazenou položku ve sloupcích)% součtu nadřazené položky Hodnoty se vypočítají následujícím způsobem:
(hodnota položky) / (hodnota pro nadřazenou položku vybraného Základního pole)Rozdíl mezi Zobrazí hodnoty jako rozdíl v porovnání s hodnotou Základní položky v jejím Základním poli. % rozdílu mezi Zobrazí hodnoty jako rozdíl v procentech v porovnání s hodnotou Základní položky v jejím Základním poli. Mezisoučet v Zobrazí hodnotu po sobě jdoucích položek v Základním poli jako mezisoučet. % mezisoučtu v Vypočítá hodnotu pro po sobě jdoucí položky v Základním poli, která se zobrazí jako mezisoučet vyjádřený procentuální hodnotou. Pořadí od nejmenších po největší Zobrazí pořadí vybraných hodnot v konkrétním poli a uvádí nejmenší položku v poli jako 1 a každou vyšší hodnotu s vyšší hodnotou pořadí. Pořadí od největších po nejmenší Zobrazí pořadí vybraných hodnot v konkrétním poli a uvádí nejvyšší položku v poli jako 1 a každou nižší hodnotu s vyšší hodnotou pořadí. Index Hodnoty se vypočítají následujícím způsobem:
((Hodnota v buňce) × (Celkový součet celkových součtů)) / ((Celkový součet řádku) x (Celkový součet sloupce))
- Vzorce Pokud souhrnné funkce a vlastní výpočty neposkytují výsledky, které chcete, můžete v počítaných polích a počítaných položkách vytvářet vlastní vzorce. Můžete třeba přidat počítanou položku se vzorcem pro provize z prodeje, které se můžou lišit pro jednotlivé oblasti. Sestava pak automaticky bude zahrnovat provizi v mezisoučtech a celkových součtech.
Jak ovlivňuje typ zdrojových dat výpočty
Výpočty a možnosti, které jsou dostupné v sestavě, závisí na tom, jestli pocházela zdrojová data z databáze OLAP nebo ze zdroje dat jiného než OLAP.
-
Výpočty založené na zdrojových datech OLAP U kontingenčních tabulek, které jsou vytvořené z krychlí OLAP, se souhrnné hodnoty vypočítají předem na serveru OLAP a teprve potom se zobrazí v Excelu výsledky. To, jak se tyto hodnoty předem vypočítají v kontingenční tabulce, nemůžete změnit. Nemůžete například změnit souhrnné funkce, které slouží k výpočtu polí dat nebo mezisoučtů, ani není možné přidat počítaná pole nebo počítané položky.
Také pokud server OLAP poskytuje počítaná pole, která se označují jako počítané členy, zobrazí se tato pole v seznamu polí kontingenční tabulky. Uvidíte tady i všechna počítaná pole a počítané položky, které vytvářejí makra napsaná v jazyce VBA (jazyk Visual Basic for Application) a uložená ve vašem sešitě. Tato pole ani položky ale nebudete moci změnit. Pokud potřebujete další typy výpočtů, obraťte se na správce databáze OLAP.
Pro zdrojová data OLAP můžete při výpočtu mezisoučtů a celkových součtů zahrnout nebo vyloučit hodnoty pro skryté položky. - Výpočty založené na zdroji dat jiném než OLAP V kontingenčních tabulkách, které jsou založené na jiných typech externích dat nebo na datech listu, používá Excel k výpočtu polí hodnot, která obsahují číselná data, funkci Souhrnný součet, a k výpočtu datových polí, která obsahují text, funkci Souhrnný počet. Můžete vybrat i jiné souhrnné funkce, jako je například Průměr, Maximum nebo Minimum, a data dál analyzovat a přizpůsobit. Také můžete vytvořit počítané pole nebo počítanou položku v poli a vytvořit vlastní vzorce, které používají prvky sestavy nebo data z jiného listu.
Použití vzorců v kontingenčních tabulkách
Vzorce můžete vytvořit jenom v sestavách, které jsou založené na zdroji dat jiném než OLAP. Není možné použít vzorce v sestavách, které jsou založené na databázi OLAP. Při použití vzorců v kontingenčních tabulkách byste měli znát následující pravidla syntaxe vzorců a chování vzorců:
Prvky vzorce kontingenční tabulky Ve vzorcích, které vytváříte pro počítaná pole a počítané položky, můžete používat operátory a výrazy stejně jako v jiných vzorcích listu. Můžete používat konstanty a odkazovat na data v sestavě, ale nemůžete použít odkazy na buňky nebo definované názvy. Nemůžete použít funkce listu, které vyžadují odkazy na buňky nebo definované názvy jako argumenty, a nemůžete použít maticové funkce.
Názvy polí a položek Excel používá názvy polí a položek k identifikaci těchto prvků sestavy ve vzorcích. V následujícím příkladu používají data v rozsahu buněk C3:C9 název pole Mléčné výrobky. Vypočítaná položka v poli Typ, která odhaduje prodej pro nový produkt na základě prodeje mléčných výrobků, může použít třeba vzorec ='Mléčné výrobky' * 115 %.
Poznámka
V kontingenčním grafu se názvy polí zobrazují v seznamu polí kontingenční tabulky a názvy položek se zobrazují v rozevíracích seznamech v jednotlivých polích. Nespleťte si tyto názvy s těmi, které vidíte v tipech ke grafu a odrážejí názvy řad a datových bodů.
Vzorce pro operace se součty součtů, nikoli s jednotlivými záznamy Vzorce pro počítaná pole pracují se součtem podkladových dat pro všechna pole ve vzorci. Nepostupuje se tak, že se jednotlivé prodeje vynásobí hodnotou 1,2 a následně se vynásobené částky sečtou.
Vzorce pro počítané položky provádějí operace s jednotlivými záznamy. Například vzorec počítané položky ='Mléčné výrobky' * 115 % vynásobí jednotlivé prodeje mléčných výrobků 115 % a následně se tyto vynásobené částky sečtou v oblasti Hodnoty.Mezery, čísla a symboly v názvech Pole v názvu, který obsahuje více polí, můžou mít libovolné pořadí. V předchozím příkladu můžou mít buňky C6:D6 název Duben Sever nebo Sever Duben. Názvy, které mají více slov nebo obsahují čísla nebo symboly, uzavřete do jednoduchých uvozovek.
Součty: Vzorce nesmí odkazovat na souhrny (například Březen celkem, Duben celkem a Celkový součet v příkladu).
Názvy polí v odkazech na položku Název pole je možné zahrnout do reference na položku. Název položky musí být v hranatých závorkách, například Oblast[Sever]. Chcete se vyhnout #NAME použití tohoto formátu? když mají dvě položky ve dvou různých polích v sestavě stejný název. Pokud je například v sestavě položka s názvem Maso v poli Typ a další položka s názvem Maso v poli Kategorie, můžete zabránit #NAME? tak, že budete na tyto položky odkazovat jako na Typ[Maso] a Kategorie[Maso].
Odkazy na položky podle umístění Na položku můžete odkazovat podle jejího umístění v sestavě na základě jejího aktuálního řazení a zobrazení. Typ[1] jsou Mléčné produkty a Typ[2] jsou Mořské plody. Položka, na kterou se odkazuje tímto způsobem, se může změnit při každé změně umístění položek nebo při každém zobrazení nebo skrytí jiných položek. Skryté položky se v tomto seznamu nepočítají.
Pro odkazy na položky můžete použít relativní umístění. Umístění se určují ve vztahu k počítané položce, která obsahuje daný vzorec. Pokud je aktuální oblast Jih, Oblast[-1] je Sever. Pokud je aktuální oblast Sever, Oblast[+1] je Jih. Počítaná položka může například použít vzorec =Oblast[-1] * 3 %. Počítaná položka může například použít vzorec =Oblast[-1] * 3 %. Pokud je pozice, kterou uvedete, před první položkou nebo za poslední položkou v poli, bude výsledkem vzorce chyba #REF!.
Použití vzorců v kontingenčních grafech
Aby bylo možné použít vzorce v kontingenčním grafu, vytvoříte vzorce v přidružené kontingenční tabulce, kde můžete vidět jednotlivé hodnoty, které tvoří data, a pak můžete výsledky graficky zobrazit v kontingenčním grafu.
Například následující kontingenční graf zobrazuje prodeje pro jednotlivé prodejce v jednotlivých oblastech:
Pokud chcete zjistit, jak by prodeje vypadaly při zvýšení o 10 procent, můžete vytvořit počítané pole v přidružené kontingenční tabulce, které používá vzorec, například =Prodej * 110 %.
Výsledek se hned zobrazí v kontingenčním grafu, jak ukazuje následující graf:
Pokud chcete zobrazit samostatnou datovou značku pro prodeje v oblasti Sever minus přepravní náklady ve výši 8 %, můžete vytvořit počítanou položku v poli Oblast se vzorcem například =Sever - (Sever * 8 %).
Výsledný graf by vypadal takto:
Vypočtená položka, která se vytvoří v poli Prodejce, by se ale zobrazila jako řada zastoupená v legendě a v grafu by se zobrazila v jednotlivých kategoriích jako datový bod.
Potřebujete další pomoc?
Kdykoli se můžete zeptat odborníka z technické komunity Excelu nebo získat podporu v komunitách.