Dnešní článek míří primárně na stranu
uživatelů Excelu. Protože PowerBI je hlavně o tom, přinést uživatelům podobné
možnosti, jaké máme v serverových produktech. K sepsání tohoto článku
mě inspirovalo uživatelské školení u klienta. Tímto posílám pozdravy do Mladé
Boleslavi :) Třeba i Vašim uživatelům tento článek pomůže PowerBI nástroje lépe
uchopit na něčem, co je jim blízké. Funkce zde zmíněné jsou ale použitelné
nejen pro ně. Bude se jednat o zajímavé DAX (Data Analysis Expressions) funkce
v rámci PowerPivot modelu, případně tabulárního modelu SSAS.
Prvně nastínění problému a k čemu se
funkce VLOOKUP používá. Jedná se o funkci, která dohledá do Excelové tabulky
data z tabulky jiné. Za stejným účelem by si databázista napsal select
s JOINem.
Na Excelové listy jsem
si vytvořil 2 tabulky, číselník produktů
Produkt kód
|
Produkt název
|
Jednotková cena
|
A
|
Produkt A
|
10
|
B
|
Produkt B
|
20
|
C
|
Produkt C
|
30
|
a prodeje produktů v
čase
Datum
|
Produkt
|
Kusy
|
Cena celkem
|
6/28/2015
|
A
|
5
|
|
6/29/2015
|
B
|
7
|
|
6/30/2015
|
C
|
5
|
|
7/1/2015
|
A
|
2
|
|
7/2/2015
|
B
|
7
|
|
7/3/2015
|
C
|
5
|
Potřeboval bych
spočítat celkovou cenu prodeje, k tomuto ale budu potřebovat dohledat
jednotkovou cenu. Přesně za tímto účelem by Excelový uživatel použil funkci
VLOOKUP. Tato má 4 argumenty. Co hledám, kde to chci najít (porovnání probíhá
na základě pouze prvního sloupečku), který sloupec chci vrátit a typ shody (jestli hledám přesnou shodu,
případně hledám intervalově). Nevýhody: Pokud nenajde, vrátí chybu, musím
porovnávat vždy na základě shody v prvním sloupci a hlavně nad větším objemem
dat je tato funkce velmi pomalá.
V našem scénáři by
vzorec pro VLOOKUP vypadal následovně.
=VLOOKUP([@Produkt],Table1[#All],3,0)
Řešení v PowerPivot
Výhody řešení v
PowerPivot jsou, že vyzbu uděláme jendou, použít můžeme opakovaně. Díky uložení
dat v paměti také výrazně rychlejší výkon nad větším objemem dat.
V PowerPivotu
nalinkujeme obě tabulky na záložce PowerPivot, add to data model. V diagramovém
zobrazení natáhnu produkt kód na produkt a vytvořím tímto relaci mezi
tabulkami. Pravím tlačítkem na tabulku prodeje dávám go to a dostávám se do
náhledu detailních dat. Vybírám poslední prázdný sloupec a mohu použít funkci
RELATED (co chci vrátit)
Syntaxe je velmi
jednoduchá. V DAXech se neodkazujeme na adresy buněk jako na Excelovém listu
(A1, B1,...) Ale názvy tabulek ‘tabulka’ a sloupců [sloupec]. DAX výpočet pro dohledání jednotkové
ceny by tedy vypadal následovně
=RELATED(PRODUKT[Jednotková
cena])
A
finální vzorec
=RELATED(PRODUKT[Jednotková
cena])*[Kusy]
Datum
|
Produkt
|
Kusy
|
Celkem
|
6/28/2015
|
A
|
5
|
50
|
6/29/2015
|
B
|
7
|
140
|
6/30/2015
|
C
|
5
|
150
|
7/1/2015
|
A
|
2
|
20
|
7/2/2015
|
B
|
7
|
140
|
7/3/2015
|
C
|
5
|
150
|
Problém 2 – dohledání přes dva sloupce
nad větším vzorkem dat
Dále
mám dvě tabulky. Jednu s prodeji. Prodeje se konají v různé dny a
různých měnách. Celá tabulka má 60 398 řádků
Měna
|
Datum prodeje
|
Částka
|
19
|
7/1/2005
|
3578.27
|
39
|
7/1/2005
|
3399.99
|
100
|
7/1/2005
|
3399.99
|
100
|
7/1/2005
|
699.0982
|
6
|
7/1/2005
|
3399.99
|
100
|
7/2/2005
|
3578.27
|
6
|
7/2/2005
|
3578.27
|
Druhá
tabulka obsahuje kurzy pro danou měnu z daného dne. Chtěl bych přepočítat částku
podle toho kdy k tomu došlo a v jaké měně. 14 264 řádků
v kurzovním lístku
Měna
|
Datum kurzu
|
Kurz kurzu
|
3
|
7/1/2005
|
0.99980004
|
3
|
7/2/2005
|
1.000900811
|
3
|
7/3/2005
|
0.99960016
|
3
|
7/4/2005
|
1
|
V
Excelu na listu bych použil pravděpodobně funkci SUMIFS, která umožní sečíst
hodnotu ve sloupci na základě několika podmínek (počítám, že pro daný den a
měnu bude pouze jeden stav).
Vzorec pro dohledání kurzu by
vypadal takto
=sumifs(KURZY!C:C,KURZY!A:A,[@Měna],KURZY!B:B,[@[Datum
prodeje]])
Výpočet
trval 26 vteřin a to můj notebook není žádné ořezávátko :)
Místo
toho bych mohl tabulky nalinkovat do PowerPivot modelu jako v prvním
případě a použít funkci LOOKUPVALUE. V tomto případě tabulky ani nemusí
být nalinkované
LOOKUPVALUE (co chci vrátit, kde hledám 1, co hledám
1, kde hledám 2, co hledám 2 atd)
Vzoreček by vypadal obdobně jako
v případě sumifs
=LOOKUPVALUE(KURZY[Kurz
kurzu],KURZY[Měna],[Měna],KURZY[Datum kurzu],[Datum prodeje])
Výsledek
byl ale vrácen za 1 vteřinu, což je rozdíl :)
Závěr
V dnešním
článku jsme si ukázali, jak nahradit funkci VLOOKUP z Excelu, která je nad
většími objemy dat velmi pomalá za použítí funkcí DAX v PowerPivot modelu.
Pokud byste chtěli demonstrace i vidět, můžete kouknout na následující
doprovodné video ke článku
http://youtu.be/thngBhQxNrM
Žádné komentáře:
Okomentovat