Excel pre každého: Kontingenčná tabuľka z viacerých tabuliek

Človek sa musí neustále vzdelávať. To samozrejme platí aj v oblasti nových produktov. Aj keď pri pohľade na nový Excel 2013 si povieme, že disponuje kozmetickými úpravami, realita je úplne zcestná!

Pozrime sa na záložku DATA:

Táto zdanlivo nenápadná možnost Relationship nám dokáže dramaticky ušetriť čas a rozšíriť možnosti reportingu. Stiahnite si prosím nasledujúci prázdny súbor s datami: Klikni sem a môžeme začať Smile .

V tomto súbore máme k dispozícii tri tabuľky na zvláštnych listoch s názvami: Predajca, Predaj a Náklady, ktorých vzťahy znázorňuje nasledujúci obrázok:

V prípade, že by sme chceli vytvoriť štatistiky, museli by sme vytvoriť jednu shrnnú tabuľku, z ktorej by sme vytvorili kontingenčnú tabuľku. Avšak nová verzia Excel-u nám umožňuje vytvoriť relácie – vzťahy medzi tabuľkami.°

1 – Vytvoríme tabuľky:

Zmeníme názvy, poprípade formáty (podľa vlastného uváženia): takto by sme mali mať vytvorené tri tabuľky s názvami PREDAJCA, PREDAJ a NAKLADY.

Pre takto vytvorené tabuľky definujeme nasledujcúci vzťah:

DATA –> RELATIONSHIP

Zobrazí sa nasledujúce okno (Klikneme na New…), v ktorom vyplníme tabuľky, ktoré chceme prepojiť a názvy stĺpcov, pomocou ktorých chceme urobiť prepojenie. Začneme vzťahom PREDAJCA – PREDAJ.

A nasledujúce okno vyplníme takto:

Klikneme na OK a máme vytvorený vzťah (reláciu) medzi tabuľkami. Teraz prejdime na tabuľku PREDAJCA alebo PREDAJ a vytvorme z nej koningenčnú tabuľku. V editovacom prostredí tabuľky klikneme na možnosť MORE TABLES… .

Následne nám Excel oznámi, že sa musí vytvoriť nová kontingenčná tabuľka, klikneme OK a v PivotTable Fileds sa objaví nasledujúci prehľad dostupných zdrojov:

Tieto tabuľky si môžeme rozkliknúť, aby sme videli prehľad ich stĺpcov, ktoré používame pri tvorbe kontingenčnej tabuľky tak, ako sme zvyknutí. Pripravte si tabuľku podľa nasledujúceho obrázka:

Tabuľka obsahuje prehľad (štatistiky) výkonnosti jednotlivého predajcu po jednotlivých mesiacoch s prehľadom výkonnosti podľa pohlavia. Avšak kreativite sa meze nekladú, takže si môžete urobiť prehľad podľa seba Winking smile .

Pokúsme sa z prehľadu vyhodiť množsto a naopak dodať tam priame náklady z tabuľky náklady. V PivotFields Liste sa nám zobrazí nasledujúca hláška:

Excel nám nahlási, že medzi tabuľkami neexistuje žiaden vzťah (Relácia). Kliknime na možnosť CREATE… a vyplňme tabuľku podľa nasledujúceho obrázka:

Nezabudnite sa uistiť či dané relácie sú aktivované:

Ak by takáto situácia predsa len nastala, kliknite na možnost Activate v danom okne. Potom sa pokúste vytvoriť prehľad podľa nasledujúceho obrázka:

Položky v poli VALUES sú Predaj – suma stĺpca Čiastka z tabuľky Predaj, Priame a Nepriame položky sú totály priamych a nepriamych nákladov. Tento prehľad je podľa pohlavia a mesta zachytený v čase na mesačnej báze.

V konečnom dôsledku sa s prehľadom môžete hrať podľa potrieb. Môžete napríklad pre niektoré stĺpce použiť slicer apod. Azda jediný nedostatok, ktorým táto tabuľka disponuje je nemožnosť vytvorenia výpočtového poľa.

V nasledujúcom súbore, ktorý je pripravený na stiahnutie je nasledujúca kontingenčná tabuľka spolu so spomenutými slicermi.