Výpočet hodnot v kontingenční tabulce

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, budou hodnoty v tomto kontingenčním grafu odrážet výpočty v přidružené kontingenční tabulce.

• 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 založené na jiných položkách nebo buňkách 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 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. Sestava pak automaticky bude zahrnovat provizi v mezisoučtech a celkových součtech.

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    Pro kontingenční tabulky vytvořené z datových krychlí OLAP se souhrnné hodnoty vypočítají 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 (Visual Basic for Applications) 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 zdrojových datech jiných 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 hodnot polí, která obsahují numerická data, souhrnnou funkci Součet. Pokud chcete počítat datová pole, která obsahují text, používá se souhrnná funkce 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.

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 vzorců kontingenční tabulky    Ve vzorcích, které vytváříte pro počítaná pole a počítané položky, můžete použít operátory a výrazy stejně jako u jiných vzorců na 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 vašich 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 provádějí operace s celkovými součty, nikoli jednotlivými záznamy   : Vzorce pro počítaná pole provádějí operace se součty podkladových dat všech polí ve vzorci. Například vzorec počítaného pole =Prodej * 1,2 vynásobí součet prodejů pro jednotlivé typy a oblasti hodnotou 1,2. 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]. Tento formát zabraňuje chybám #NÁZEV?, 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 je v poli Kategorie, můžete zabránit chybám #NÁZEV? 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!.

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.

Než začnete, rozhodněte se, jestli chcete počítané pole nebo počítanou položku v poli. Počítané pole použijte, když chcete ve vzorci použít data z jiného pole. Pokud chcete, aby vzorec použil data z jedné nebo více specifických položek v poli, použijte počítanou položku.

Pro počítané položky můžete postupně do buněk zadat různé vzorce. Pokud má například počítaná položka s názvem OrangeCounty vzorec =Pomeranče * 0,25 ve všech měsících, můžete tento vzorec pro červen, červenec a srpen změnit na =Pomeranče * 0,5.

Pokud máte více počítaných položek nebo vzorců, můžete upravit pořadí výpočtů.

Přidání počítaného pole

1. Klikněte na kontingenční tabulku.

   Zobrazí se Nástroje kontingenční tabulky s přidanými kartami Analýza a Návrh.

2. Na kartě Analýza ve skupině Výpočty klikněte na Pole, položky a sady a potom klikněte na Počítané pole.

   

3. Do pole Název zadejte název pole.

4. V poli Vzorec zadejte vzorec pole.

   Pokud chcete použít data z jiného pole ve vzorci, klikněte na pole v části Pole a pak klikněte na Vložit pole. Pokud třeba chcete vypočítat provizi 15 % ke každé hodnotě v poli Prodej, můžete zadat =Prodej * 15 %.

5. Klikněte na Přidat.

Přidání počítané položky do pole

1. Klikněte na kontingenční tabulku.

   Zobrazí se Nástroje kontingenční tabulky s přidanými kartami Analýza a Návrh.

2. Pokud jsou položky v poli seskupené, klikněte na kartě Analýza ve skupině Skupina na Oddělit.

   

3. Klikněte na pole, kam chcete přidat počítanou položku.

4. Na kartě Analýza ve skupině Výpočty klikněte na Pole, položky a sady a potom klikněte na Počítaná položka.

   

5. Do pole Název zadejte název počítané položky.

6. V poli Vzorec zadejte vzorec položky.

   Pokud chcete ve vzorci použít data z položky, klikněte na položku v seznamu Položky a potom klikněte na Vložit položku (položky musí být ze stejného pole jako počítaná položka).

7. Klikněte na Přidat.

Postupné zadávání různých vzorců do buněk pro počítané položky

1. Klikněte na buňku, pro kterou chcete změnit vzorec.

   Pokud chcete změnit vzorec pro několik buněk, podržte stisknutou klávesu CTRL a klikněte na další buňky.

2. Na řádku vzorců zadejte změny vzorce.

Úprava pořadí výpočtu pro více počítaných položek nebo vzorců

1. Klikněte na kontingenční tabulku.

   Zobrazí se Nástroje kontingenční tabulky s přidanými kartami Analýza a Návrh.

2. Na kartě Analýza ve skupině Výpočty klikněte na Pole, položky a sady a potom klikněte na Pořadí řešení.

   

3. Klikněte na vzorec a pak klikněte na Nahoru nebo Dolů.

4. Pokračujte, dokud nebudou vzorce v pořadí, ve kterém je chcete počítat.

Můžete zobrazit seznam všech vzorců, které se používají v aktuální kontingenční tabulce.

1. Klikněte na kontingenční tabulku.

   Zobrazí se Nástroje kontingenční tabulky s přidanými kartami Analýza a Návrh.

2. Na kartě Analýza ve skupině Výpočty klikněte na Pole, položky a sady a potom klikněte na Seznam vzorců.

   

Před úpravou vzorce určete, jestli je vzorec v počítaném poli nebo počítané položce. Pokud je vzorec v počítané položce, určete také, jestli je tento vzorec pro danou počítanou položku jediný.

U počítaných položek můžete upravit jednotlivé vzorce pro určité buňky počítané položky. Pokud má například počítaná položka s názvem OrangeCalc vzorec =Pomeranče * 0,25 ve všech měsících, můžete tento vzorec pro červen, červenec a srpen změnit na =Pomeranče * 0,5.

Určete, jestli je vzorec v počítaném poli nebo počítané položce.

1. Klikněte na kontingenční tabulku.

   Zobrazí se Nástroje kontingenční tabulky s přidanými kartami Analýza a Návrh.

2. Na kartě Analýza ve skupině Výpočty klikněte na Pole, položky a sady a potom klikněte na Seznam vzorců.

   

3. V seznamu vzorců najděte v části Výpočtové pole nebo Výpočtová položka vzorec, který chcete změnit.
   
   Když pro počítanou položku existuje více vzorců, má výchozí vzorec zadaný při vytvoření položky název počítané položky ve sloupci B. Pro další vzorce počítané položky obsahuje sloupec B název počítané položky i názvy protínajících se položek.

   Můžete mít třeba výchozí vzorec pro počítanou položku s názvem MojePoložka a jiný vzorec pro tuto položku označený jako Prodej MojePoložka za leden. V kontingenční tabulce můžete najít tento vzorec v buňce Prodej pro řádek MojePoložka a sloupec Leden.

4. Pokračujte jedním z následujících způsobů úprav:

Úpravy vzorce počítaného pole

1. Klikněte na kontingenční tabulku.

   Zobrazí se Nástroje kontingenční tabulky s přidanými kartami Analýza a Návrh.

2. Na kartě Analýza ve skupině Výpočty klikněte na Pole, položky a sady a potom klikněte na Počítané pole.

   

3. V poli Název vyberte počítané pole, u kterého chcete změnit vzorec.

4. V poli Vzorec vzorec upravte.

5. Klikněte na Změnit.

Úpravy jednoho vzorce pro počítanou položku

1. Klikněte na pole obsahující počítanou položku.

2. Na kartě Analýza ve skupině Výpočty klikněte na Pole, položky a sady a potom klikněte na Počítaná položka.

   

3. V poli Název vyberte počítanou položku.

4. V poli Vzorec vzorec upravte.

5. Klikněte na Změnit.

Úpravy jednotlivých vzorců pro určitou buňku počítané položky

1. Klikněte na buňku, pro kterou chcete změnit vzorec.

   Pokud chcete změnit vzorec pro několik buněk, podržte stisknutou klávesu CTRL a klikněte na další buňky.

2. Na řádku vzorců zadejte změny vzorce.

   Tip: Pokud máte více počítaných položek nebo vzorců, můžete upravit pořadí výpočtů. Další informace najdete v tématu Úprava pořadí výpočtu pro více počítaných položek nebo vzorců.

1. Určete, jestli je vzorec v počítaném poli nebo počítané položce.

   Počítaná pole se zobrazí v seznamu polí kontingenční tabulky. Počítané položky se zobrazí jako položky v dalších polích.

2. Udělejte jednu z těchto věcí:

   • Pokud chcete odstranit počítané pole, klikněte kamkoliv mimo kontingenční tabulku.

   • Když budete chtít odstranit počítanou položku, klikněte v kontingenční tabulce na pole obsahující položku, kterou chcete odstranit.

     Zobrazí se Nástroje kontingenční tabulky s přidanými kartami Analýza a Návrh.

3. Na kartě Analýza ve skupině Výpočty klikněte na Pole, položky a sady a potom klikněte na Počítané pole nebo Počítaná položka.

   

4. V poli Název vyberte pole nebo položku, které chcete odstranit.

5. Klikněte na Odstranit.