Máte starší verzi Excelu a závidíte předplatitelům Microsoft 365 dynamické funkce jako UNIQUE nebo SROVNAT.SVISLE? Ale nechce se vám kvůli pár funkcím platit předplatné? A víte, že na spoustu těchto úprav vám stačí vaše verze Excelu? V Excelu už dávno totiž máte nástroj, který toto všechno zvládne, jen ho nejspíš nepoužíváte. Jmenuje se Power Query – a dneska vám ukážu 3 věci, které jsou peklo ve starších verzích Excelů, ale s Power Query je zvládnete během pár sekund.
Excelový soubor ke stažení:
Spojení tabulek
Úkon, který je ve starších Excelech skoro nemožný je jakékoliv dynamické napojování tabulek. Tedy situace, kdy máme několik tabulek, třeba na separátních listech a abychom s těmito tabulkami mohli dále pracovat, tak je musíme spojit do jedné. Ve starších verzích Excelu vám v podstatě nezbývá nic jiného než tabulky nakopírovat pod sebe. Tento způsob má samozřejmě tisíc problémů. Když se změní podkladová data, tak se data ve sloučené tabulce nezmění, když se přidají nová data, tak se tato data automaticky nepropíšou atd. Když se některé řádky smažou, tak zase nemáte aktuální data.
V nových Excelech s dynamickými funkcemi bychom použili funkci SROVNAT.SVISLE neboli funkci VSTACK.
Tuto funkci ale ve starších Excelech nemáte. To ale nevadí, spojte tabulky pomocí Power Query. První, co musíte udělat je nahrát tabulky do PQ. Abychom to mohli udělat, tak z nich musíme udělat excelové tabulky. Takže postupně klikneme do každé zdrojové tabulky a použijeme klávesovou kombinaci CTRL+T, potvrdíme, že tabulka má záhlaví a tím převedeme tabulky na excelové tabulky. Rovnou tabulky pojmenujeme jako Leden, Únor a Březen. Když máme tabulky převedeé na excelové tabulky, tak do nich postupně klikneme a nahrajeme je do editoru Power Query a vytvoříme na ně pouze připojení.
Když máme tabulky do editoru nahrané, tak je můžeme spojit dohromady. Na kartě Domů vybereme Připojit dotazy a Připojit dotazy jako nové.
Připojujeme více než jen jednu tabulku, takže vybereme Tři nebo více tabulek a postupně přidáme všechny tabulky, které chceme spojit.
Tabulky se spojí a máme jednu sloučenou tabulku. Dotaz můžeme přejmenovat na SloučenáTbl a změníme datový typ u sloupce Datum pouze na Datum. Tabulka je připravená na import zpátky do Excelu. Takže vybereme kartu Domů a Zavřít a Načíst.
Sloučená tabulka se nahraje na nový list v Excelu. Tabulka je napojená na zdrojové soubory, takže pokud cokoliv ve zdrojových tabulkách změníme, včetně přidání nebo smzání řádků, tak se po obnovení propojení tyto změny projeví i ve sloučené tabulce.
Přehození sloupců do řádků
V dalším příkladu máme klasický příklad s nestrukturovanou tabulkou. Tedy tabulkou, kde hodnoty v několika různých sloupcích. Místo toho abychom měli v jednom sloupci měsíce a ve druhém hodnoty, tak zde máme měsíce v samostatných sloupcích. S takto strukturovanou tabulkou nejde v Excelu pořádně počítat ani v ní pořádně vyhledávat. Musíme ji převést na klasický tabulkový formát. Ve straších verzích Excelu, ale i v moderních Excelech skoro nemožné. Převedeme tabulku pomocí Power Query.
Nejprve zdrojovou tabulku převedeme na excelovou tabulku (CTRL+T) a následně tabulku pošleme do Power Query, a to tak, že klikneme od tabulky a na kartě Data vybereme Z tabulky nebo oblasti. V tabulce označíme sloupce, které převádět nechceme, což jsou sloupce Produkt a Kategorie. A na kartě Transformace vybereme Převést sloupce na řádky a Převést ostatní sloupce na řádky.
Tím se sloupce s měsícem převedou a vzniknou dva nové sloupce – měsíce a hodnota.
Tuto převedenou tabulku nahrajeme zpátky do Excelu. A takto jednoduše můžete pomocí Power Query převádět nestrukturované tabulky na správné tabulkové formáty.
Rozdělování textu
Práce s textem byla v Excelu vždy komplikovaná, zejména rozdělování textu nebo jeho čištění. Trochu to vylepšili nové funkce jako ROZDĚLIT.TEXT a TEXTJOIN nebo funkce TEXTPŘED a TEXTZA. Nicméně tyto funkce nemáte. Vezmeme takový klasický příklad. V jedné tabulce máte jméno a příjmení a ve druhé příjmení a jméno. Naším úkolem je dohledat hodnoty, nicméně abychom to mohli udělat, tak musíme sladit jména. To znamená přehodit jméno a příjmení.
Ve starých verzích Excelu bychom museli použít kombinaci několika excelových funkcí.
Nové dynamické funkce postup trochu zjednodušily, ale stejně bychom museli použít kombinaci dvou funkcí.
Zkusíme, jak si s tímto úkolem poradí Power Query. Nejprve převedeme zdrojový sloupec na excelovou tabulku (CTRL+T) a tabulku nahrajeme do Power Query. Nejprve rozdělíme jméno a příjmení do samostatných sloupců, a to pomocí rozdělení sloupce oddělovačem.
Jako oddělovač použijeme mezeru.
Následně označíme sloupce v pořadí, v jakém je chceme spojit. Tedy nejprve příjmení a jméno a na kartě Transformace vybereme Sloučit sloupce a jako oddělovač použijeme mezeru.
A pomocí těchto dvou jednoduchých kroků se příjmení a jméno otočí.
Tabulku můžeme poslat zpátky do Excelu.
