Ako pridať a použiť vypočítané polia kontingenčnej tabuľky programu Excel

Po vytvorení kontingenčnej tabuľky je zvyčajne potrebné rozšíriť analýzu a zahrnúť do nej viac údajov/výpočtov.

Ak potrebujete nový údajový bod, ktorý možno získať pomocou existujúceho údajového bodu v kontingenčnej tabuľke, nie je potrebné sa vracať a pridávať ho do zdrojových údajov.Namiesto toho môžete použiťVypočítané polia kontingenčnej tabuľkyrobiť to.

Čo je to vypočítané pole kontingenčnej tabuľky?

Začnime základným príkladom kontingenčnej tabuľky.

Predpokladajme, že máte množinu údajov maloobchodníkov a vytvoríte kontingenčnú tabuľku, ako je táto:

Vyššie uvedená kontingenčná tabuľka sumarizuje hodnoty predaja a zisku maloobchodníka.

Čo keby ste teraz chceli vedieť, aké sú ziskové marže týchto maloobchodníkov (rozpätie zisku je „zisk“ vydelený „tržbami“)?

Existuje niekoľko spôsobov, ako to urobiť:

1. 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ú maržu.Keď to urobíte, musíte aktualizovať zdrojové údaje kontingenčnej tabuľky, aby ste získali tento nový stĺpec ako jej súčasť.
   • Zatiaľ čo tento prístup funguje, musíte sa manuálne vrátiť k súboru údajov a vykonať výpočty.Môžete napríklad chcieť pridať ďalší stĺpec na výpočet priemerného predaja na jednotku (predaj/množstvo).Opäť musíte pridať tento stĺpec do zdrojových údajov a potom aktualizovať kontingenčnú tabuľku.
   • Táto metóda tiež nafúkne vašu kontingenčnú tabuľku, keď do nej pridáte nové údaje.
2. Pridajte výpočty mimo kontingenčných tabuliek.Túto možnosť vyberte, ak sa štruktúra kontingenčnej tabuľky pravdepodobne nezmení.Ak však zmeníte kontingenčnú tabuľku, výpočty sa nemusia zodpovedajúcim spôsobom aktualizovať a môžu vám poskytnúť nesprávne výsledky alebo chyby.Ako je uvedené nižšie, vypočítal som ziskovú maržu, keď sú v rade maloobchodníci.Ale keď to zmením od zákazníka k oblasti, vzorec sa pokazí.
3. Vypočítajte polia pomocou kontingenčných tabuliek.Toto jePoužite existujúce údaje kontingenčnej tabuľky a vypočítajtenajefektívnejším spôsobom.Vypočítané polia si predstavte ako virtuálne stĺpce, ktoré pridáte pomocou existujúcich stĺpcov v kontingenčnej tabuľke.Používanie vypočítaných polí kontingenčnej tabuľky má mnoho výhod (ako uvidíme neskôr):
   • Nevyžaduje si prácu so vzorcami ani aktualizáciu zdrojových údajov.
   • Je rozšíriteľná, pretože automaticky zohľadňuje všetky nové údaje, ktoré môžete pridať do kontingenčnej tabuľky.Po pridaní vypočítaného poľa ho môžete použiť ako ktorékoľvek iné pole v kontingenčnej tabuľke.
   • Je ľahké ho aktualizovať a spravovať.Ak sa napríklad zmení metrika alebo potrebujete zmeniť výpočet, môžete to jednoducho urobiť zo samotnej kontingenčnej tabuľky.

Pridajte vypočítané polia do kontingenčných tabuliek

Pozrime sa, ako pridať vypočítané pole kontingenčnej tabuľky do existujúcej kontingenčnej tabuľky.

Predpokladajme, že máte kontingenčnú tabuľku, ktorá vyzerá takto, a chcete vypočítať ziskovú maržu pre každého maloobchodníka:

Tu sú kroky na pridanie výpočtového poľa kontingenčnej tabuľky:

Po pridaní vypočítavaného poľa sa zobrazí ako jedno z polí v zozname Polia kontingenčnej tabuľky.

Toto vypočítané pole môžete teraz 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 výpočtové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 presuniem oblasť do oblasti riadkov, získate výsledky, ako sú nasledujúce, kde sú uvedené hodnoty ziskovej marže pre maloobchodníka a región.

 

Vo vyššie uvedenom príklade som na vloženie vypočítaného poľa použil jednoduchý vzorec (=Zisk/Tržby).Môžete však použiť aj niektoré pokročilé vzorce.

Predtým, ako vám ukážem príklad použitia pokročilého vzorca na vytvorenie výpočtového poľa kontingenčnej tabuľky, musíte vedieť niekoľko vecí:

• Pri vytváraní výpočtových polí kontingenčnej tabuľky nemôžete použiť odkazy ani pomenované rozsahy.Tým sa vylúčia mnohé vzorce, ako napríklad VLOOKUP, INDEX, OFFSET atď.Môžete však použiť vzorce, ktoré fungujú bez odkazov (napr. SUM, IF, COUNT, atď...).
• Vo vzorcoch môžete použiť konštanty.Ak by ste napríklad chceli poznať predpokladaný rast predaja o 10 %, použili by ste vzorec = Predaj*1.1 (kde 1.1 je konštanta).
• Dodržujte poradie priority vo vzorcoch, ktoré generujú vypočítané polia.Ako osvedčený postup použite zátvorky, aby ste sa uistili, že si nemusíte pamätať prednosť.

Teraz sa pozrime na príklad vytvorenia vypočítavaného poľa pomocou pokročilého vzorca.

Predpokladajme, že máte množinu údajov zobrazenú nižšie a potrebujete zobraziť prognózovaný predaj v kontingenčnej tabuľke.

Pre predpovedané hodnoty by ste použili 300% zvýšenie predaja pre veľkých maloobchodníkov (viac ako 5 milióny predajov) a 300% zvýšenie predaja pre malých a stredných predajcov (menej ako 10 milióny predajov).

Poznámka: Údaje o predaji tu sú falošné a boli použité na ilustráciu príkladov v tomto návode.

Postup:

Tým sa do kontingenčnej tabuľky pridá nový stĺpec s hodnotami prognózy predaja.

Problém vypočítaného poľa kontingenčnej tabuľky

Vypočítané polia sú úžasnou funkciou, ktorá skutočne zvyšuje hodnotu kontingenčných tabuliek s výpočtami polí a zároveň zachováva všetko škálovateľné a spravovateľné.

Existuje však problém s vypočítavanými poľami kontingenčnej tabuľky, ktorému musíte pred použitím porozumieť.

Predpokladajme, že mám kontingenčnú tabuľku, ako je uvedené nižšie, a používam vypočítané pole na získanie prognózovaných predajných čísel.

Upozorňujeme, že medzisúčty a súčty sú nesprávne.

Aj keď by mali pridať samostatné hodnoty 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 22,287,000 XNUMX, South Total ju nesprávne uvádza ako XNUMX XNUMX XNUMX.Stáva sa to preto, že na získanie hodnoty používa vzorec 21,225,800 1.05 XNUMX * XNUMX.

Bohužiaľ tento problém nemôžete vyriešiť.

Najlepší spôsob, ako to vyriešiť, je odstrániť medzisúčty a celkové súčty z kontingenčnej tabuľky.

Môžete tiež prejsť Uvádza DebraNiektoré inovatívne riešenia na riešenie tohto problému.

Ako upraviť alebo odstrániť vypočítané pole kontingenčnej tabuľky?

Po vytvorení výpočtového poľa kontingenčnej tabuľky môžete vzorec upraviť alebo ho odstrániť pomocou nasledujúcich krokov:

Ako získať zoznam všetkých vzorcov vypočítaných polí?

Ak vytvoríte veľa vypočítavaných polí kontingenčnej tabuľky, nemusíte sa starať o sledovanie vzorcov použitých v jednotlivých poliach.

Excel vám umožňuje rýchlo vytvoriť zoznam všetkých vzorcov používaných na vytváranie vypočítavaných polí.

Tu sú kroky na rýchle získanie zoznamu všetkých vzorcov vypočítaných polí:

• Vyberte ľubovoľnú bunku v kontingenčnej tabuľke.
• Prejdite na Nástroje kontingenčnej tabuľky -> Analýza -> Polia, položky a množiny -> Vzorce zoznamu.

Po kliknutí na vzorec zoznamu Excel automaticky vloží nový pracovný hárok s podrobnosťami o všetkých vypočítaných poliach/položkách, ktoré ste použili v kontingenčnej tabuľke.

Toto môže byť veľmi užitočný nástroj, ak musíte poslať prácu klientovi alebo ju zdieľať so svojím tímom.