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

 

               

Formát čísla s m3 alebo kWh, alebo GJ …

 

V praxi sa stáva, že v niektorých tabuľkách používame špecifické čísla, ktoré by sme radi formátovali spolu s ich identifikátorom.

Pevne verím, že každý z Vás si dokáže naformátovať číslo do meny, ako je to napríklad  na nasledujúcom obrázku:

No čo ak by sme chceli naformátovať povedzme nasledujúcu tabuľku s vyúčtovaním takto:

A to bez toho, aby sme sa vzdali čísla ako hodnoty v bunke (inak povedané žiadne riešenie typu =”35,4” & “ m3”).

Prejdite do záložky Home – Number – More Number Formats … Zobrazí sa Vám nasledujúce okno:

V ňom prejdite na položku Custom a v nej si vyberte číselný formát, ktorý je vyhovujúci. Ja som si vybral typ # ##0,00 a teraz prichádza na rad tzv. “vylepšovák”

V poli Type: môžete editovať zvolený formát. V praxi ak za formát zadáte znak \ oddelíte tým formátovanie čísel od formátovania identifikátora. Tým pádom stačí napísať  “m3” a je hotovo

Výsledný upravený formát pre elektrickú energiu (KWh) vyzerá takto:

 

Výsledok by v praxi mohol vyzerať takto:

Kto z Vás by predsa mal problém si vytvoriť špecifické formáty, kliknutím si stiahne vzorový súbor a použije formáty formou Copy – Paste Special – Formats. Stačí kliknúť na obrázok. V prípade dotazov neváhajte – dajte lajk na facebooku a pošlite správu.

 

Ako vymazať všetky komentáre v Exceli

 

Občas sa stane, že sa v reporte premnožia komentáre. No čo by sme to bli za analytici, ak by sme ich mazali ručne .

Použijeme jednoduchú procedúru:

Sub clr_comments()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    Cells.ClearComments
Next

End Sub

A hurá na kávu / cigaretu alebo koketovať s kolegynkami .

 

Súbor s makrom na stiahnutie – klikni na Download: