Jak se zbavit funkce VLOOKUP (SVYHLEDAT), DAX funkce pro detailní výpočty

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ů

a prodeje produktů v čase

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]

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ů

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

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