Kontingenčná tabuľka a funkcia získatkontdata resp. GETPIVOTDATA

 

Začnem otázkou: Prečo táto kombinácia?

1) Rieši komplikované delenie faktov podľa dimenzií (tržieb podľa krajov, predajcov apod.) – nemusíme používať databázové funkcie ani zložíté makra

2) Rozdelenie výpočtového výkonu na časť kontingenčnej tabuľky a funkcie           (V konečnom dôsledku Excel bude počítať len ako dostať hodnoty z kontingenčnej tabuľky a nie celkový prepočet, čo značne uľahčuje manipuláciu so súborom s veľmi veľkým množstvom dát a analýz)

Pokračujem otázkou: Prečo túto kombináciu nie?

1) Veľmi citlivé na zmenu kontingenčnej tabuľky – ak zmeníte usporiadanie stĺpcov, poprípade pridáte/odstánite ďalšie dimenzie tak vzorčeky Vám vyhodia chybu.

Princíp: Data – Kontingenčná tabuľka ako výpočtový most (Štandardne nastavené dimenzie a fakta, ktorých hodnoty sa menia v čase) – Summary, ktorý vzniká kombináciou funkcií IFERROR a GETPIVOTDATA (ZÍSKATKONTDATA).

Príklad: Dataset, pozostávajúci zo štatistík predajov (Predajca, Množstvo, Cena, Náklady na predaj, Kraj, Mena, Mesiac, Rok … atď.) – Kontingenčná tabuľka (Stĺpce – Mesiace a Roky, Riadky – Kraje, Výpočet – Súčet tržieb) – Summary (Prehľad tržieb podľa mesiacov v konkrétnom roku alebo posledných troch rokoch po kvartáloch).

Aktualizácia reportu: Doplníte údaje (ďalšie riadky) do listu Dataset a aktualizujete kontingenčnú tabuľku, ktorá má Zdroj Dat definovaný ako samotné stĺpce (napr. stĺpce “A:Z”). Po aktualizácii tabuľky sa automaticky prepočítajú vzorce IFERROR(GETPIVOTDATA()).

Majme tabuľku:

Z nej si pripravíme nasledujúcu kontingenčnú tabuľku (Všimnite si Table/Range: sú samotné stĺpce):

Štruktúra bude vyzerať takto:

A taktiež vymažeme Total podľa rokov a Grand Total. Výsledná tabuľka bude vyzerať takto:

Urobíme si nový list s názvom Summary a pripravíme napr. kvartálny prehľad tržieb podľa rokov a konkrétnych štátov:

Použijeme univerzálny GETPIVOTDATA (ZÍSKATKONTDATA) vzorec tak, že v tomto liste sa odkážeme na hodnotu vo vnútri kontingenčnej tabuľky. Všimnite si, že namiesto odkazu typu =’PIVOT’!C6 nám Excel vrátil nasledujúci vzorec:

V ňom potrebujeme v poradí určiť: Názov stĺpca, ktorý pivotka počíta v tabuľke; Akákoľvek bunka vrámci kontingenčnej tabuľky; Filtračný stĺpec; Konkrétna hodnota; ….. Filtračný stĺpec; Konkrétna hodnota)

Upravme a aplikujme vzorček podľa obrázka:

Teda v počiatku tabuľky bude vzorec vyzerať takto (nahradili sme konkrétne hodnoty odkazmi na ukotvené bunky):

=GETPIVOTDATA("Čiastka zostatok";PIVOT!$B$3;"Rok";$E9;"Měsíc";F$4;"Stát";$D$8)

Avšak pre univerzálnosť vzorček doplníme o funkciu IFERROR() tak,aby v prípade, že ak niektoré hodnoty zatiaľ neexistujú (napríklad 4. kvartál, pozostávajúci z mesiacov 10 11 a 12), tak funkcia vráti 0.

=IFERROR(GETPIVOTDATA("Čiastka zostatok";PIVOT!$B$3;"Rok";$E9;"Měsíc";F$4;"Stát";$D$8);0)

Skopírujeme vzorček a upravíme odkazy na bunky tak, aby ukazovali relevantné hodnoty. List s pivotkou zamkneme, aby s ním nemohol nikto manipulovať a farbu písma mesiacov v tabuľke nad summary zmeníme na bielu. Finálny report vyzerá takto:

V prípade, že chceme vytvoriť plnú automatizáciu, pripravíme si makra na vloženie dát a automatický refresh pivotky, ktorý som vysvetľoval v predchádzajúcom článku. Na záver podotknem, že vypracovaný súbor sa stiahne kliknutím na download a dotazy/like čakám kliknutím na facebook logo