Akonáhle vytvoríte kontingenčnú tabuľku, často budete musieť svoju analýzu rozšíriť a zahrnúť do nej viac údajov/výpočtov.
Ak potrebujete nový údajový bod, ktorý je možné získať pomocou existujúcich dátových bodov v kontingenčnej tabuľke, nemusíte sa vracať a pridávať ho do zdrojových údajov. Namiesto toho môžete použiť a Pole vypočítané pre kontingenčnú tabuľku urobiť toto.
Stiahnite si množinu údajov a postupujte podľa nich.
Čo je to pole vypočítané pre kontingenčnú tabuľku?
Začnime so základným príkladom kontingenčnej tabuľky.
Predpokladajme, že máte množinu údajov od maloobchodníkov a vytvoríte kontingenčnú tabuľku, ako je uvedené nižšie:
Vyššie uvedená kontingenčná tabuľka sumarizuje hodnoty tržieb a zisku pre maloobchodníkov.
Teraz, čo keby ste tiež chceli vedieť, aké bolo ziskové rozpätie týchto maloobchodníkov (kde je ziskové rozpätie „Zisk“ delené „tržbami“).
Existuje niekoľko spôsobov, ako to urobiť:
- Vráťte sa k pôvodnému súboru údajov a pridajte tento nový údajový bod. Do zdrojových údajov teda môžete vložiť nový stĺpec a vypočítať v ňom ziskové rozpätie. Akonáhle to urobíte, musíte aktualizovať zdrojové údaje kontingenčnej tabuľky, aby ste tento nový stĺpec získali ako jeho súčasť.
- Aj keď je táto metóda možná, museli by ste sa manuálne vrátiť k množine údajov a vykonať výpočty. Možno budete musieť napríklad pridať ďalší stĺpec na výpočet priemerného predaja na jednotku (predaj/množstvo). Opäť budete musieť pridať tento stĺpec do zdrojových údajov a potom aktualizovať kontingenčnú tabuľku.
- Táto metóda tiež nadúva vašu kontingenčnú tabuľku pri pridávaní nových údajov.
- Pridajte výpočty mimo kontingenčnej tabuľky. To môže byť možné, ak sa nepravdepodobne zmení štruktúra vašej kontingenčnej tabuľky. Ak však zmeníte kontingenčnú tabuľku, výpočet sa nemusí aktualizovať zodpovedajúcim spôsobom a môže spôsobiť nesprávne výsledky alebo chyby. Ako je uvedené nižšie, ziskovú maržu som vypočítal, keď v rade boli maloobchodníci. Keď som to však zmenil zo zákazníkov na regióny, vzorec zobrazil chybu.
- Použitie vypočítaného poľa kontingenčnej tabuľky. To je najefektívnejším spôsobom použiť existujúce údaje kontingenčnej tabuľky a vypočítať požadovanú metriku. Vypočítané pole považujte za virtuálny stĺpec, ktorý ste pridali pomocou existujúcich stĺpcov z kontingenčnej tabuľky. Používanie vypočítaného poľa pre kontingenčnú tabuľku má mnoho výhod (ako uvidíme o minútu):
- Nevyžaduje, aby ste spracovávali vzorce alebo aktualizovali zdrojové údaje.
- Je škálovateľný, pretože bude automaticky účtovať všetky nové údaje, ktoré môžete pridať do kontingenčnej tabuľky. Keď pridáte pole Vypočítať, môžete ho použiť ako akékoľvek iné pole v kontingenčnej tabuľke.
- Je ľahké ho aktualizovať a spravovať. Ak sa napríklad zmenia metriky alebo potrebujete zmeniť výpočet, môžete to ľahko vykonať z kontingenčnej tabuľky.
Pridanie vypočítaného poľa do kontingenčnej tabuľky
Pozrime sa, ako pridať pole vypočítané z kontingenčnej tabuľky do existujúcej kontingenčnej tabuľky.
Predpokladajme, že máte kontingenčnú tabuľku, ako je uvedené nižšie, a chcete vypočítať ziskové rozpätie pre každého maloobchodníka:
Nasledujú kroky na pridanie vypočítaného poľa kontingenčnej tabuľky:
- Vyberte ľubovoľnú bunku v kontingenčnej tabuľke.
- Prejdite na Nástroje kontingenčnej tabuľky -> Analyzovať -> Výpočty -> Polia, položky a sady.
- V rozbaľovacej ponuke vyberte položku Vypočítané pole.
- V dialógovom okne Vložiť vypočítaný súbor:
- Pomenujte ho zadaním do poľa Názov.
- V poli Vzorec vytvorte požadovaný vzorec pre vypočítané pole. Upozorňujeme, že si môžete vybrať z názvov polí uvedených pod ním. V tomto prípade je vzorec „= zisk/ tržby“. Môžete buď zadať názvy polí ručne, alebo dvakrát kliknúť na názov poľa uvedený v poli Polia.
- Kliknite na položku Pridať a zatvorte dialógové okno.
Hneď ako pridáte vypočítané pole, zobrazí sa ako jedno z polí v zozname Polia kontingenčnej tabuľky.
Teraz môžete toto vypočítané pole použiť ako akékoľvek iné pole kontingenčnej tabuľky (všimnite si, že vypočítané pole kontingenčnej tabuľky nemôžete použiť ako filter zostavy alebo rýchly filter).
Ako som už spomenul, výhodou použitia vypočítaného poľa kontingenčnej tabuľky je, že môžete zmeniť štruktúru kontingenčnej tabuľky a tá sa automaticky upraví.
Ak napríklad v oblasti riadkov potiahnem a pustíme oblasť, dostanete výsledok, ako je uvedené nižšie, kde je hodnota ziskovej marže uvedená pre maloobchodníkov aj pre región.
V uvedenom príklade som použil jednoduchý vzorec (= Zisk/Predaj) na vloženie vypočítaného poľa. Môžete však použiť aj niektoré pokročilé vzorce.
Predtým, ako vám ukážem príklad použitia rozšíreného vzorca na vytvorenie poľa na výpočet kontingenčnej tabuľky, musíte vedieť niekoľko vecí:
- Pri vytváraní vypočítavaného poľa kontingenčnej tabuľky NESMETE používať odkazy alebo pomenované rozsahy. To by vylúčilo veľa vzorcov, ako sú VLOOKUP, INDEX, OFFSET atď. Môžete však použiť vzorce, ktoré môžu fungovať bez odkazov (napríklad SUM, IF, COUNT a tak ďalej …).
- Vo vzorci môžete použiť konštantu. Ak napríklad chcete poznať prognózované tržby, kde sa predpokladá rast o 10%, môžete použiť vzorec = tržby*1,1 (kde 1,1 je konštantný).
- Poradie priority je dodržané vo vzorci, ktorý tvorí vypočítané pole. Ako osvedčený postup používajte zátvorky, aby ste sa presvedčili, že si nemusíte pamätať poradie priority.
Teraz sa pozrime na príklad použitia pokročilého vzorca na vytvorenie vypočítaného poľa.
Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a v kontingenčnej tabuľke musíte ukázať hodnotu prognózovaného predaja.
Na predpokladanú hodnotu musíte použiť 5% nárast tržieb u veľkých maloobchodníkov (tržby nad 3 milióny) a 10% nárast tržieb u malých a stredných maloobchodníkov (tržby pod 3 milióny).
Poznámka: Tu uvedené predajné čísla sú falošné a boli použité na ilustráciu príkladov v tomto návode.
Postupujte takto:
- Vyberte ľubovoľnú bunku v kontingenčnej tabuľke.
- Prejdite na Nástroje kontingenčnej tabuľky -> Analyzovať -> Výpočty -> Polia, položky a sady.
- V rozbaľovacej ponuke vyberte položku Vypočítané pole.
- V dialógovom okne Vložiť vypočítaný súbor:
- Pomenujte ho zadaním do poľa Názov.
- V poli Vzorec použite nasledujúci vzorec: = IF (región = „juh“, tržby *1,05, tržby *1,1)
- Kliknite na položku Pridať a zatvorte dialógové okno.
Tým sa do kontingenčnej tabuľky pridá nový stĺpec s hodnotou prognózy predaja.
Kliknutím sem stiahnete množinu údajov.
Problém s poliami vypočítanými v kontingenčnej tabuľke
Vypočítané pole je úžasná funkcia, ktorá skutočne zvyšuje hodnotu vašej kontingenčnej tabuľky pomocou výpočtov v poli, a pritom zachováva škálovateľnosť a spravovateľnosť všetkého.
Existuje však problém s poliami vypočítanými v kontingenčnej tabuľke, ktoré musíte vedieť pred použitím.
Predpokladajme, že mám kontingenčnú tabuľku, ako je uvedené nižšie, kde som použil vypočítané pole na získanie odhadovaných predajných čísel.
Upozorňujeme, že medzisúčet a celkové súčty nie sú správne.
Aj keď by tieto mali pripočítať individuálnu hodnotu prognózy predaja pre každého maloobchodníka, v skutočnosti sa riadi rovnakým vypočítaným vzorcom poľa, ktorý sme vytvorili.
Takže pre South Total, zatiaľ čo hodnota by mala byť 22 824 000, South Total ju mylne uvádza ako 22 287 000. Stáva sa to, pretože na získanie hodnoty používa vzorec 21,225,800*1,05.
Toto bohužiaľ nemôžete napraviť.
Najlepším spôsobom, ako to vyriešiť, by bolo odstrániť z vašej kontingenčnej tabuľky medzisúčty a celkové súčty.
Môžete si tiež prejsť niekoľko inovatívnych riešení, ktoré Debra ukázala, že si s týmto problémom poradí.
Ako upraviť alebo odstrániť vypočítané pole kontingenčnej tabuľky?
Po vytvorení vypočítaného poľa pre kontingenčnú tabuľku môžete vzorec upraviť alebo odstrániť pomocou nasledujúcich krokov:
- Vyberte ľubovoľnú bunku v kontingenčnej tabuľke.
- Prejdite na Nástroje kontingenčnej tabuľky -> Analyzovať -> Výpočty -> Polia, položky a sady.
- V rozbaľovacej ponuke vyberte položku Vypočítané pole.
- V poli Názov kliknite na rozbaľovaciu šípku (malá šípka nadol na konci poľa).
- V zozname vyberte vypočítané pole, ktoré chcete odstrániť alebo upraviť.
- Zmeňte vzorec, ak ho chcete zmeniť, alebo kliknite na Odstrániť, ak ho chcete odstrániť.
Ako získať zoznam všetkých vypočítaných vzorcov polí?
Ak vytvoríte veľa vypočítaného poľa pre kontingenčnú tabuľku, nerobte si starosti so sledovaním vzorca použitého v každom z nich.
Excel vám umožňuje rýchlo vytvoriť zoznam všetkých vzorcov použitých pri vytváraní vypočítaných polí.
Tu sú kroky na rýchle získanie zoznamu vzorcov všetkých vypočítaných polí:
- Vyberte ľubovoľnú bunku v kontingenčnej tabuľke.
- Prejdite na Nástroje kontingenčnej tabuľky -> Analyzovať -> Polia, položky a sady -> Vzorec zoznamu.
Hneď ako kliknete na Zoznam vzorcov, Excel automaticky vloží nový pracovný hárok, ktorý bude obsahovať podrobnosti o všetkých vypočítaných poliach/položkách, ktoré ste použili v kontingenčnej tabuľke.
Toto môže byť skutočne užitočný nástroj, ak musíte svoju prácu poslať klientovi alebo ju zdieľať so svojim tímom.
Tiež vám môžu byť užitočné nasledujúce návody na kontingenčné tabuľky:
- Príprava zdrojových údajov pre kontingenčnú tabuľku.
- Používanie krájačov v kontingenčnej tabuľke programu Excel: Príručka pre začiatočníkov.
- Ako zoskupiť dátumy v kontingenčných tabuľkách v programe Excel.
- Ako zoskupiť čísla v kontingenčnej tabuľke v programe Excel.
- Ako filtrovať údaje v kontingenčnej tabuľke v programe Excel.
- Ako nahradiť prázdne bunky nulami v kontingenčných tabuľkách programu Excel.
- Ako použiť podmienené formátovanie v kontingenčnej tabuľke v programe Excel.
- Kontingenčná vyrovnávacia pamäť v programe Excel - čo to je a ako ju najlepšie použiť?
