Jak přidat a používat vypočítané pole kontingenční tabulky aplikace Excel

Jakmile vytvoříte kontingenční tabulku, je často nutné vaši analýzu rozšířit a zahrnout do ní více dat/výpočtů.

Pokud potřebujete nový datový bod, který lze získat pomocí stávajících datových bodů v kontingenční tabulce, nemusíte se vracet a přidávat je do zdrojových dat. Místo toho můžete použít a Vypočtené pole kontingenční tabulky udělat toto.

Stáhněte si datovou sadu a postupujte podle ní.

Co je vypočtené pole kontingenční tabulky?

Začněme základním příkladem kontingenční tabulky.

Předpokládejme, že máte datovou sadu maloobchodníků a vytvoříte kontingenční tabulku, jak je uvedeno níže:

Výše uvedená kontingenční tabulka shrnuje hodnoty tržeb a zisku pro maloobchodníky.

Co kdybyste také chtěli vědět, jaké bylo ziskové rozpětí těchto maloobchodníků (kde je ziskové rozpětí „zisk“ děleno „prodejem“).

Existuje několik způsobů, jak to provést:

1. Vraťte se k původní sadě dat a přidejte tento nový datový bod. Můžete tedy do zdrojových dat vložit nový sloupec a vypočítat v něm ziskové rozpětí. Jakmile to uděláte, musíte aktualizovat zdrojová data kontingenční tabulky, abyste tento nový sloupek získali jako jeho součást.
   • I když je tato metoda možná, budete se muset ručně vrátit k datové sadě a provést výpočty. Může být například nutné přidat další sloupec pro výpočet průměrného prodeje na jednotku (prodej/množství). Znovu budete muset přidat tento sloupec do zdrojových dat a poté aktualizovat kontingenční tabulku.
   • Tato metoda také nadýmá vaši kontingenční tabulku, když do ní přidáváte nová data.
2. Přidejte výpočty mimo kontingenční tabulku. To může být volba, pokud se struktura vaší kontingenční tabulky pravděpodobně nezmění. Pokud však změníte kontingenční tabulku, výpočet se nemusí odpovídajícím způsobem aktualizovat a může způsobit nesprávné výsledky nebo chyby. Jak je uvedeno níže, vypočítal jsem ziskovou marži, když v řadě byli maloobchodníci. Když jsem to ale změnil ze zákazníků na regiony, vzorec zobrazil chybu.
3. Pomocí vypočítaného pole kontingenční tabulky. To je nejúčinnějším způsobem použít existující data kontingenční tabulky a vypočítat požadovanou metriku. Vypočítané pole považujte za virtuální sloupec, který jste přidali pomocí existujících sloupců z kontingenční tabulky. Použití počítaného pole kontingenční tabulky má mnoho výhod (jak uvidíme za minutu):
   • Nevyžaduje, abyste zpracovávali vzorce nebo aktualizovali zdrojová data.
   • Je škálovatelný, protože bude automaticky účtovat všechna nová data, která můžete přidat do své kontingenční tabulky. Jakmile přidáte pole Vypočítat, můžete ho použít jako jakékoli jiné pole v kontingenční tabulce.
   • Je snadné jej aktualizovat a spravovat. Pokud se například změní metriky nebo potřebujete změnit výpočet, můžete to snadno provést z kontingenční tabulky samotné.

Přidání vypočítaného pole do kontingenční tabulky

Podívejme se, jak přidat vypočtené pole kontingenční tabulky do stávající kontingenční tabulky.

Předpokládejme, že máte kontingenční tabulku, jak je uvedeno níže, a chcete vypočítat ziskovou marži pro každého maloobchodníka:

Zde jsou kroky k přidání vypočítaného pole kontingenční tabulky:

• Vyberte libovolnou buňku v kontingenční tabulce.
• Přejděte na Nástroje kontingenční tabulky -> Analyzovat -> Výpočty -> Pole, položky a sady.
• Z rozevíracího seznamu vyberte Vypočítané pole.
• V dialogovém okně Vložit vypočítaný soubor:
  • Pojmenujte jej zadáním do pole Název.
  • V poli Vzorec vytvořte požadovaný vzorec pro vypočítané pole. Všimněte si, že si můžete vybrat z názvů polí uvedených pod ním. V tomto případě je vzorec „= zisk/ prodej“. Názvy polí můžete zadat ručně nebo poklepáním na název pole uvedený v poli Pole.
• Klikněte na Přidat a zavřete dialogové okno.

Jakmile přidáte vypočítané pole, zobrazí se jako jedno z polí v seznamu Pole kontingenční tabulky.

Nyní můžete toto vypočítané pole použít jako jakékoli jiné pole kontingenční tabulky (všimněte si, že nemůžete vypočítané pole kontingenční tabulky použít jako filtr sestavy nebo kráječ).

Jak jsem již zmínil, výhodou použití vypočítaného pole kontingenční tabulky je, že můžete změnit strukturu kontingenční tabulky a ta se automaticky upraví.

Pokud například přetáhnu oblast v oblasti řádků, získáte výsledek, jak je uvedeno níže, kde je hodnota ziskové marže hlášena pro maloobchodníky i pro region.

Ve výše uvedeném příkladu jsem použil jednoduchý vzorec (= zisk/prodej) k vložení vypočítaného pole. Můžete však také použít některé pokročilé vzorce.

Než vám ukážu příklad použití pokročilého vzorce k vytvoření pole pro výpočet kontingenční tabulky, je zde několik věcí, které musíte vědět:

• Při vytváření pole pro výpočet kontingenční tabulky NEMŮŽETE používat odkazy nebo pojmenované rozsahy. To by vyloučilo mnoho vzorců jako VLOOKUP, INDEX, OFFSET atd. Můžete však použít vzorce, které mohou fungovat bez odkazů (například SUM, IF, COUNT atd.…).
• Ve vzorci můžete použít konstantu. Pokud například chcete znát předpovídané tržby, u nichž se předpovídá růst o 10%, můžete použít vzorec = tržby*1,1 (kde 1,1 je konstantní).
• Pořadí priority je dodrženo ve vzorci, který vytváří vypočítané pole. Jako osvědčený postup použijte závorky, abyste se ujistili, že si nemusíte pamatovat pořadí priorit.

Nyní se podívejme na příklad použití pokročilého vzorce k vytvoření vypočítaného pole.

Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a v kontingenční tabulce musíte ukázat hodnotu prognózovaného prodeje.

Pro předpokládanou hodnotu musíte použít 5% nárůst tržeb u velkých maloobchodníků (tržby nad 3 miliony) a 10% nárůst tržeb u malých a středních maloobchodníků (tržby pod 3 miliony).

Poznámka: Zde uvedená prodejní čísla jsou falešná a byla použita k ilustraci příkladů v tomto tutoriálu.

Postupujte takto:

• Vyberte libovolnou buňku v kontingenční tabulce.
• Přejděte na Nástroje kontingenční tabulky -> Analyzovat -> Výpočty -> Pole, položky a sady.
• Z rozevíracího seznamu vyberte Vypočítané pole.
• V dialogovém okně Vložit vypočítaný soubor:
  • Pojmenujte jej zadáním do pole Název.
  • V poli Vzorec použijte následující vzorec: = IF (Region = „Jih“, tržby *1,05, tržby *1,1)
• Klikněte na Přidat a zavřete dialogové okno.

Tím se do kontingenční tabulky přidá nový sloupec s hodnotou prognózy prodeje.

Kliknutím sem stáhnete datovou sadu.

Problém s vypočítanými poli kontingenční tabulky

Vypočítané pole je úžasná funkce, která skutečně zvyšuje hodnotu vaší kontingenční tabulky pomocí výpočtů polí, a přitom udržuje vše škálovatelné a spravovatelné.

U polí vypočtených v kontingenční tabulce je však problém, který musíte vědět před použitím.

Předpokládejme, že mám kontingenční tabulku, jak je uvedeno níže, kde jsem použil vypočítané pole k získání prognózovaných prodejních čísel.

Všimněte si, že mezisoučet a celkové součty nejsou správné.

I když by tyto hodnoty měly přidat individuální prognózu prodeje pro každého maloobchodníka, ve skutečnosti se řídí stejným vypočteným vzorcem pole, který jsme vytvořili.

Takže pro South Total, zatímco hodnota by měla být 22 824 000, South Total ji mylně uvádí jako 22 287 000. K tomu dochází, protože k získání hodnoty používá vzorec 21,225,800*1,05.

Bohužel neexistuje způsob, jak to opravit.

Nejlepším způsobem, jak to vyřešit, by bylo odebrání mezisoučtů a součtů z kontingenční tabulky.

Můžete si také projít některá inovativní řešení, která Debra ukázala, že si s tímto problémem poradí.

Jak upravit nebo odstranit vypočítané pole kontingenční tabulky?

Jakmile vytvoříte vypočítané pole kontingenční tabulky, můžete vzorec upravit nebo odstranit pomocí následujících kroků:

• Vyberte libovolnou buňku v kontingenční tabulce.
• Přejděte na Nástroje kontingenční tabulky -> Analyzovat -> Výpočty -> Pole, položky a sady.
• Z rozevíracího seznamu vyberte Vypočítané pole.
• V poli Název klikněte na rozevírací šipku (malá šipka dolů na konci pole).
• Ze seznamu vyberte vypočítané pole, které chcete odstranit nebo upravit.
• Změňte vzorec v případě, že jej chcete upravit, nebo klikněte na Odstranit v případě, že jej chcete odstranit.

Jak získat seznam všech vypočítaných vzorců polí?

Pokud vytvoříte hodně pole Vypočteno v kontingenční tabulce, nedělejte si starosti se sledováním vzorce použitého v každém z nich.

Excel vám umožňuje rychle vytvořit seznam všech vzorců použitých při vytváření vypočítaných polí.

Zde jsou kroky k rychlému získání seznamu vzorců všech vypočtených polí:

• Vyberte libovolnou buňku v kontingenční tabulce.
• Přejděte na Nástroje kontingenční tabulky -> Analyzovat -> Pole, položky a sady -> Vzorce seznamu.

Jakmile kliknete na Seznam vzorců, Excel automaticky vloží nový list, který bude obsahovat podrobnosti o všech vypočítaných polích/položkách, které jste použili v kontingenční tabulce.

To může být opravdu užitečný nástroj, pokud musíte svou práci poslat klientovi nebo ji sdílet se svým týmem.

Také vám mohou být užitečné následující návody pro kontingenční tabulky:

• Příprava zdrojových dat pro kontingenční tabulku.
• Používání kráječů v kontingenční tabulce aplikace Excel: Průvodce pro začátečníky.
• Jak seskupit data v kontingenčních tabulkách v aplikaci Excel.
• Jak seskupit čísla v kontingenční tabulce v aplikaci Excel.
• Jak filtrovat data v kontingenční tabulce v aplikaci Excel.
• Jak nahradit prázdné buňky nulami v kontingenčních tabulkách aplikace Excel.
• Jak použít podmíněné formátování v kontingenční tabulce v aplikaci Excel.
• Kontingenční mezipaměť v aplikaci Excel - co to je a jak ji nejlépe použít?