Power View–Excel 2013 I. diel

Už v predchádzajúcom článku sme sa pozreli, ako pracovať s Data Model-om a pripraviť kontingenčnú tabuľku z viacerých zdrojov. Dnes naviažeme na predchádzajúci článok a pripravíme si tzv. “Power View”. Na otázku, čo to vlastne znamená si myslím, že si každý vytvorí odpoveď už počas článku.

Majme teda predchádzajúci dataset s tromi tabuľkami – viď predchádzajúci článok. Tieto tabuľky sú pomenované názvami PREDAJCA, PREDAJ a NAKLADY. Predtým, než začneme si ukážeme ako odstárniť staré prepojenia. Stiahnite si nasledujúci súbor kliknutím tu.

Všimnime si po kliknutí na Connections v záložke Data sa nám zobrazí zoznam neplatných prepojení:

Takto označené nefunkčné prepojenia zmažeme kliknutím na Remove možnosť a potom dáme Close. Tým pádom sme si pripravili prázdny Data Model, v ktorom budeme nastavovať vzťahy (relácie) medzi tabuľkami.

Ako v predchádzajúcom článku, pomenujeme si tabuľky názvami PREDAJCA, PREDAJ a NAKLADY. Medzi týmito tabuľkami si nadefinujeme vzťahy takto: DATA – RELATIONSHIP – NEW …

Medzi troma tabuľkami by sme mali mať vytvorené dve relácie:

Kliknite na INSERT – POWER VIEW

 

V prípade prvého vytvorenia tohto nástroja sa objaví nasledujúce okno:

Ak nemáte k dispozícii Silverlight na svojom PC, tak kliknite na možnosť Install Silverlight a potom možnosť Reload.

Objaví sa nám nový objekt a názvom Power View1.

Tento objekt, resp. samostatný list funguje na podobnom (skoro rovnakom) princípe ako tvorba kontingenčnej tabuľky. V čom je potom výhoda?

Prehľad viacerých kontingenčných tabuliek (prehľadov) na jednom liste
Veľmi pekné možnosti grafického zobrazenia
Možnosť použiť Google Earth na zobrazenie grafov pri jednotlivých mestách
Použitie legiend, výpočtových polí, nastavenie KPI’s a mnohé ďalšie možnosti

Čo je však veľmi príjemné je interaktivita medzi tabuľkami / grafmi v tomto objekte. Avšak vymenované detaily si podrobne rozoberieme v ďalších článkoch. Pre dnešok si ukážeme ako vložiť základnú tabuľku a graf, viď nasledujcúci obrázok a video:

 

 

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ť .

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 .

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.

Hotový súbor si kliknutím na Download stiahnete na PC a v prípade dotazov kliknite na FB logo a nebojte sa opýtať o dodatočné info

Automatizujeme report pomocou kontingenčnej tabuľky

 

Kontingenčné tabuľky v Exceli sú v praxi bežná užívateľská pomôcka, ktorej tvorbu a ovládanie som vysvetlil v jednom z prvých článkov tohto blogu.

Predstavte si situáciu, kedy pravidelne musíte z dataset-u urobiť kontingenčnú tabuľku(y) v štandardnom výstupe, s jediným rozdielom v názve alebo mesiaci, poprípade roka. Na prvý pohľad triviálna vec, ale po čase začne popri dôležitejšej práci liezť na nervy každému analytikovi.

Našťastie aj takýto report môžeme efektívne automatizovať. Celkovo potrebujeme nahrať makro na tvorbu kontingenčnej tabuľky, v ktorom upravíme dva základné atribúty: Dátový zdroj a  Názov.

Celý proces bude prebiehať nasledovne:

1) Skopíruje sa štandardný list DATA na špecifický Sales Summary „Rok“ „Mesiac“

2) V tomto liste sa nadefinuje oblasť pre kontingenčnú tabuľku RNG_“Mesiac“_“rok“

3) Vytvorí sa štandardná kontingenčná tabuľka pod názvom SALES_PIVOT_“Mesiac“ „Rok“

4) Procedúra nás upozorní, že daný prehľad sa vytvoril, poprípade že už existuje

Prejdime k prvému bodu, pre ktorý kód bude vyzerať takto:

Sub Procedura()

Sheets("DATA").Copy Before:=Sheets("DATA")

On Error GoTo koniec

ActiveSheet.Name = "Sales Summary " & Mesiac & " " & Rok

‘======== zvyšná časť kódu ===================================

koniec:

Application.DisplayAlerts = False

ActiveSheet.Delete

Application.DisplayAlerts = True

MsgBox "Prehľad za " & Mesiac & "_" & Rok & " už existuje", vbCritical

End Sub

Táto procedúra v sebe obsahuje ošetrenie, ktoré v prípade že takýto list existuje – vymaže už skopírovaný list DATA(2) a ukončí celé makro, viď časť kódu za časťou koniec:.

Než začneme s nahrávaním, pripravíme si dve premenné – Mesiac a Rok, ktoré budú načítavať hodnoty z buniek v aktuálnom liste.

Dim Mesiac As Integer

Dim Rok As Integer

Mesiac = ActiveSheet.Cells(10, 10)

Rok = ActiveSheet.Cells(8, 10)

Potom si vytvoríme procedúru, ktorá pripraví špecifickú pomenovanú oblasť pre kontingenčnú tabuľku s názvom RNG_“mesiac“_“rok“

ActiveSheet.Names.Add Name:="RNG_" & Mesiac & "_" & Rok, RefersTo:= _

Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 5))

Táto RNG oblasť nám bude slúžiť ako vstupný zdroj pre tvorbu kontingenčnej tabuľky. Napokon nám stačí nahrať tvorbu špecifickej tabuľky so štandardnými popismi stĺpcov a riadkov.

V samotnej tvorbe tabuľky kód po úprave vyzerá takto (pozn. červenou farbou sú označené zmeny atribútov nahraného kódu, pričom PivotStyle časť som úplne vymazal):

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

"RNG_" & Mesiac & "_" & Rok).CreatePivotTable TableDestination:= _

ActiveSheet.Cells(15, 9), TableName:="SALES_PIVOT_" & Mesiac & " " & Rok

Dohodíme kód pre úpravu vzhľadu – použijem jeden z štandardných dizajnov tabuľky:

ActiveSheet.PivotTables( "SALES_PIVOT_" & Mesiac & " " & Rok).TableStyle2 = "PivotStyleDark4"

V tomto štádiu stačí pridať stĺpce (Sales Executive), riadky (Group) a telo (Price) tabuľky + upraviť formát čísel:

With ActiveSheet.PivotTables("SALES_PIVOT_" & Mesiac & " " & Rok).PivotFields( _

"Sales Executive")

.Orientation = xlColumnField

.Position = 1

End With

With ActiveSheet.PivotTables("SALES_PIVOT_" & Mesiac & " " & Rok).PivotFields("Group")

.Orientation = xlRowField

.Position = 1

End With

ActiveSheet.PivotTables("SALES_PIVOT_" & Mesiac & " " & Rok).AddDataField ActiveSheet. _

PivotTables("SALES_PIVOT_" & Mesiac & " " & Rok).PivotFields("Price"), _

"Total Revenues", xlSum

With ActiveSheet.PivotTables("SALES_PIVOT_" & Mesiac & " " & Rok).PivotFields( _

"Total Revenues")

.NumberFormat = "# ##0"

End With

Range("A1").Select

Nakoniec dodáme message box, ktorý nám oznámi ukončenú procedúru, vymaže tlačidlo pre tvorbu prehľadu a ukončí makro pred oblasťou koniec: :

ActiveSheet.Buttons.Delete

MsgBox "Prehľad za " & Mesiac & "_" & Rok & " bol vytvorený", vbExclamation

Exit Sub

Posledný detail, ktorý ma napadol je, že by nebolo na škodu v podkladovom liste DATA nechať vstupy zmazať pre budúce použitie. Tým pádom stačí do posledného kódu hneď za metódu delete pridať:

Sheets("DATA").Select

Range(Cells(2, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 5)).Clear

sheets("Sales Summary " & Mesiac & " " & Rok).Select

Takto vytvorená procedúrka v nasleduúcom template bude vo finále vyzerať takto:

Teda: prehľad za obdobie Apríl roku 2014 je v špecifickom liste a zároveň nás message box upozornil, že prehľad je vytvorený. List DATA je samozrejme vyprádznený, viď nasledujúci obrázok:

A ak by sme do DATA listu nakopírovali hodnoty a chceli opäť uložiť pod rovnakým názvom, objaví sa nesledujúca hláška:

Ako je už zvykom, celý template spolu s kódom je k dispozícii – stačí kliknúť na obrázok Download, ak by ste mali akýkoľvek dotaz – navštívte facebookovskú skupinu – klikni na modré logo .