Věděli jste, že si pomocí Power Query můžete vytvořit vlastní datové typy v Excelu? Ne? Tak to pro vás bude dnešní video velkým překvapením. Jelikož vám ukážu, jak pomocí datových typů v Excelu vyhledávat a tvořit interaktivní reporty a to bez použití jedné jediné funkce. Jediné, co k tomu potřebujete je licence Microsoft 365.
Excelový soubor ke stažení:
Představte si, že pracujete se zdrojovou tabulkou, která obsahuje informace o produktech, které prodáváme na našem e-shopu. Tabulka obsahuje informace o produktech, zařazení produktu, od jaké firmy produkt odebíráme, za jakou cenu atd. Toto je naše zdrojová tabulka, ze které neustále někam dohledáváme data. Takže kombinujeme funkce, XLOOKUP, FILTER a další. Co kdyby šlo ale tvořit reporty i bez těchto funkcí? Co kdybyste všechny tyto informace z tabulky mohly uskladnit v jedné buňce u produktu? Pojďme si ukázat jak na to.
Zdrojovou tabulku máme ve formátu excelové tabulky, pojmenovanou jako Data, takže ji můžeme načíst do PQ. Klikneme do tabulky, karta Data a Načíst data z tabulky nebo oblasti. Tabulka se po chvilce načte do PQ.
Než se pustíme do tvorby vlastních datových typů, tak nejprve upravíme a vyčistíme tabulku. Nejprve rozdělíme obsah ve sloupci Produkt, kde máme název produktu i kód. Karta Transformace > Rozdělit sloupec a rozdělit sloupec oddělovačem. Jako oddělovač zvolíme středník.
Ještě vyplníme kategorie produktů směrem dolů, tak aby se kategorie doplnila na všechny prázdné řádky.
V tabulce máme uvedenou cenu produktu bez DPH, cenu potřebujeme s DPH, takže karta Transformace > Standardní výpočet a Součin. A vynásobíme obsah ve sloupci 1,21. A přejmenujeme sloupec na Cena vč. DPH.
Vytvoříme datové typy. Musíme označit celou tabulku. Takže klikneme do tabulky a použijeme pro označení všech sloupců klávesovou kombinaci CTRL+A. A na kartě Transformace vybereme Vytvořit datový typ.
Datový typ nazveme jako Produkty a datový typ tvoříme ke sloupci Produkt.
Potvrdíme a celá tabulka se shrne do sloupce Produkt. Tabulka je připravená a můžeme ji poslat do Excelu.
Tabulka se načte a datové typy poznáme podle ikon vedle produktů. Když na ikonu najedeme myší, tak ikona problikne, což znamená, že na ní můžeme kliknout > rozbalí se karta jako u datových typů měna nebo akcie a máme zde uvedené všechny informace patřící k vybranému produktu. Pro každý produkt tak máme kartu informací dostupnou přímo z buňky. Další výborná věc je, že můžeme datové typy řadit ve sloupci i podle hodnot, které nejsou viditelné. Můžeme kliknout do záhlaví a v liště se objeví nová možnost řazení. Vybereme třeba podle marže a produkty se seřadí. Správnost můžeme zkontrolovat podle datové karty.
Pomocí karet můžeme přidávat informace do tabulku, klikneme na kartu a řekněme, že chceme přidat dodavatele > vybereme ikonu přidat.
Nebo najedeme na ikonu v záhlaví a přidáme pole.
Další možností je použít funkci, tak jak ji známe od datových typů. Vybereme produkt, tečka a nabídnou se pole.
Poslední možností jak přidat sloupec je do tabulky napsat sloupec do záhlaví > třeba Typ produktu a on se přidá.
Teď když víme, jak vlastní datové typy vytvořit, tak si ukážeme, jak je jednoduché s nimi pracovat. Naším úkolem je vytvořit následující report. V rozbalovacím seznamu chceme mít seznam produktů a pro vybraný produkt chceme, aby se zobrazily informace o dodavateli, kategorii produktu, datum uvedení do prodeje a cenu produktu.
Klasicky bychom k tomu používali vyhledávací funkce. Ne když pracujeme s datovými typy.
Napíšeme rovná se, ozančíme produkt v rozbalovacím seznamu, napíšeme tečku a rozbalí se pole, která můžeme vložit.
Tento postup následně můžeme zopakovat pro všechny položky.
Pomocí operátoru tečky tedy můžeme přidávat informace o datových typech do buněk.
3 komentáře
chcel by som sa prihlásiť na kurz Power Query , kde sa to dá? Ďakujem
Ráda bych se přihlásila na kurz Power Query. Děkuji
Jarmila Kalistová
Dobrý den chtěla bych se přihlásit do kurzu Power Query.Děkují.