Dostanete Excel, otevřete ho…a vidíte něco takového. Rozházená data, spojení textů v jedné buňce, ceny jako text, mezery, chaos. A teď otázka: Jak to co nejrychleji a přitom spolehlivě vyčistit? Jedna z nejčastějších věcí, kterou v Excelu řešíme, je čištění dat. V tomhle videu si ukážeme dva přístupy. Nejdřív zkusíme tuto tabulku vyčistit klasicky pomocí funkcí v Excelu…a pak si ukážeme, jak to samé zvládne Power Query – rychleji, přehledněji a hlavně opakovatelně.
Excelový soubor ke stažení:
Představte si, že jste obdrželi následující tabulku, se kterou máte dále pracovat. Ve stavu v jakém je tabulka teď to není dost dobře možné. V prvním sloupci máme spojené číslo objednávky a datum objednávky. Ve druhém sloupci máme datum, ale bez oddělovače tečky, takže Excel tento formát samozřejmě nepoznává jako datum. Ve sloupci datum dodání máme u datumu špatný oddělovač. Ve sloupci město máme nesoulad ve formátu písma a někde nám chybí diakritika. V dalších dvou sloupcích máme na řádcích chybějící hodnoty, které se musí doplnit a nakonec máme pro každý měsíc samostatné sloupce s cenou. S touto tabulkou by byla noční můra pracovat. Naším úkolem je tuto tabulku vyčistit. Nejprve se o to pokusíme standardními excelovými nástroji nebo funkcemi a nakonec si ukážeme daleko lepší a snazší způsob.
A začneme hned prvním sloupcem. Tento sloupec potřebujeme rozdělit podle oddělovače svislé čáry. Asi nejjednodušším způsobem, jak to můžeme udělat ve všech verzích Excelů je ten, že použijete nástroj Text do sloupců. Označíme sloupec a na kartě Data vybereme Text do sloupců.
Hodnoty chceme oddělit do sloupce pomocí oddělovače.
V dalším kroku si můžeme vybrat oddělovač. Máme vlastní oddělovač, takže vybereme jiné a do pole napíšeme svislou čáru.
A v posledním okně si můžeme vybrat, kam chceme hodnoty oddělit. Vzniknou nám dva sloupce a jelikož si nechceme přepsat hodnoty v tabulce, tak vybereme třeba sloupec vedle tabulky.
Potvrdíme a tím se hodnoty rozdělí podle svislé čáry.
Teď bychom samozřejmě mohli přidat nový sloupec do tabulky a hodnoty nakopírovat nazpět. Pozor si ale musíte dát na to, že i přesto, že se nám hodnoty rozdělili, tak datum je momentálně ve formátu textu a nikoliv datumu. Což poznáte mimo jiné podle zarovnání hodnot v buňce. Pokud byste teď hodnoty označili a změnili u nich formát na datum, tak se nic nestane. Nejjednodušší je určit v tomto případě formát hned u rozdělení. Zkusíme to znovu.
Zopakujeme celý postup s rozdělením Text do sloupců a
v posledním okně rovnou stanovíme formát výsledných sloupců. Takže
označíme druhý sloupec a vybereme datum. A tím si ušetříte čas se změnou
formátu.
Toto je nejjednodušší způsob, jak hodnoty rozdělit. Nevýhodou samozřejmě je, že to není dynamické řešení, takže pokud změníme cokoliv ve zdrojové tabulce, tak se změny nepropíší do rozdělených sloupců.
Někdo by mohl k rozdělení použít excelové funkce. A teď záleží na tom, jakou verzi Excelu máte. Pokud máte přístup k dynamickým funkcím, tak můžete použít funkce jako TEXTPŘED a TEXTZA. Pro rozdělení textu před svislou čárou použijeme funkci TEXTPŘED, kde se nejprve označuje text a následně se určí oddělovač v uvozovkách.
Pro oddělení hodnoty za svislou čárou použijeme funkci TEXTZA. Parametry jsou stejné. Nejprve se označí text a následuje oddělovač v uvozovkách. Výsledkem funkce bude text. Tedy datum ve formátu textu. Pokud ho chcete převést na datum, tak nejjednodušší způsob je vynásobit funkci jedničkou.
Pokud tyto funkce nemáte, musíte to udělat po staru. Text před oddělovačem oddělíme pomoc funkce HLEDAT, ve které nejprve určíme pořadové číslo svislé čáry, tedy na kterém místě v textu se svislá čára nachází. A funkci nakonec zabalíme do funkce ZLEVA, kde označíme text a jako počet znaků poslouží funkce HLEDAT, která nám počet znaků k oddělení určila. Pokud nechceme oddělit i svislou čáru a nadbytečnou mezeru, tak od funkce odečteme dvojku.
Pro oddělení hodnot za svislou čárou musíme použít kombinaci více funkcí. Nejprve opět určíme polohu svislé čáry pomocí funkce HLEDAT. Abychom zjistili, kolik znaků je ale za oddělovačem, tak musíme nejprve funkci HLEDAT odečíst od funkce DÉLKA, která určí celkový počet znaků. A to pak zabalíme do funkce ZPRAVA. Opět, pokud nechceme oddělit i nadbytečnou mezeru, tak od funkce odečteme jedničku.
A jak si poradíme s druhým sloupcem, kde máme datum, ale chybí nám zde oddělovače? Opět by bylo nejjednodušší použít Text do sloupců. Málokdo totiž ví, že tento nástroj lze použít k oddělení i bez oddělovače. Takže opět označíme sloupec a na kartě Data vybereme Text do sloupců. Tentokrát nemáme oddělovač ale pevnou šířku, takže vybereme pevnou šířku.
A v dalším okně máme možnost nastavit si pevnou šířku, podle které chceme hodnoty rozdělit. Takže klikneme do hodnot a tím se vloží čára, která označuje, kde se hodnoty rozdělí.
A opět vybereme buňku vedle tabulky. A tím se rozdělí datum do sloupců.
A teď bychom hodnoty mohli spojit a máme opět několik způsobů. Buď můžete hodnoty spojit pomocí ampersandů, nebo pomocí funkce CONCAT nebo pomocí dynamické funkce TEXTJOIN.
V dalším sloupci máme u datumů špatný oddělovač. A Excel teď samozřejmě s lomítky nepoznává datum jako datum. Nejjednodušší by bylo nahradit lomítka tečkou. Takže označíme sloupec a CTRL+H pro nahrazení. Nicméně si všimněte, že lomítka nebyla jediným problémem. Jednalo se o americké datum, takže máme nejprve uvedený měsíc, pak den a následně rok, takže tam kde máme číslo nad 12 se vrátí text, jelikož Excel nechápe, že je zde přehozený den a měsíc. Takže tento způsob by fungoval pouze někde. Opět by zde bylo nejlepší rozdělit nejprve sloupec do samostatných sloupců a složit datum postupně jako v předchozím příkladu.
Textové úpravy je nejsnazší nahradit před CTRL+H. Pokud máte hodně dat a nevíte kolik variant existuje, tak můžeme vložit filtr do záhlaví tabulky. Pozor ale na to, že filtr v záhlaví neukáže rozdíl mezi malými a velkými písmeny. Pokud byste tedy chtěli sjednotit i velikost písma, tak to přes filtr v záhlaví nezjistíte.
Pro nahrazení hodnot použijeme CTRL+H a budeme nahrazovat všechny hodnoty, až dokud nesjednotíme písmo a diakritiku u všech položek ve sloupci.
Ve sloupci produkt máme klasický problém, kdy nám na řádcích chybí hodnoty a logika je taková, že na chybějící řádky patří položky z nadřazeného řádku. Potřebujeme tedy tyto položky propsat vždy dolů na volné řádky. Na to v Excelu existuje docela jednoduchý trik. Označíme sloupec a použijeme klávesovou kombinaci CTRL+G, vybereme přejít na jinak.
Zde vybereme prázdné buňky.
Napíšeme rovná se a označíme první nadřazenou položku.
Potvrdíme stisknutím kombinace CTRL+ENTER. A máme doplněné hodnoty.
Bohužel tento trik ale nefunguje pro vyplnění směrem nahoru. Což je situace ve sloupci typ produktu. Zde potřebujeme položky propsat nahoru. Asi nejjednodušší je vytvořit v tomto případě pomocný sloupec a pomoci si podmínkovou funkcí KDYŽ.
A zbývá poslední a v podstatě nejtěžší úkol, kterým jsou poslední tři sloupce. Tato tabulka teď totiž není ve správném tabulkovém formátu. Aby s tabulkou šlo pohodlně počítat, tak by tyto tři sloupce měli být v podstatě dva, jeden pro měsíce a druhý pro hodnoty a tabulka by tím pádem byla delší než širší. To je něco, co je v podstatě nemožné provést jednoduchou cestou pomocí standardních nástrojů. To, co většina lidí dělá je to, že by hodnoty kopírovala pod sebe a ručně by tabulku předělala na správný formát.
Tabulka je jakž tak vyčištěná, nicméně zabralo nám to nemálo času a spoustu kopírování a vkládání a manuálního upravování. Nevýhodou většiny těchto postupů je samozřejmě to, že jsme je tvořili manuálně pomocí kopírování a vkládání, takže pokud se změní podkladová data nebo nám nová data přibydou, tak bychom celý tento proces museli opakovat.
A teď si pojďme ukázat, jak všechny tyto úpravy můžete udělat jednoduše během pár minut, a to v každé verzi Excelu od 2016.
Nejprve musíme ze zdrojové tabulky udělat excelovou tabulku, a to tak, že do zdrojové tabulky klikneme a použijeme klávesovou kombinaci CTRL+T. Tabulku pojmenujeme jako Data.
Následně klikneme do tabulky a na kartě Data vybereme Z tabulky nebo oblasti.
Tabulka se nahraje do editoru Power Query a my můžeme začít s úpravou prvního sloupce. Označíme sloupec a chceme ho rozdělit podle oddělovače, takže na kartě Transformace vybereme Rozdělit sloupec a Oddělovačem. Jako oddělovač stanovíme svislou čáru a potvrdíme rozdělení.
Druhý sloupec můžeme rovněž rozdělit z karty Transformace. Místo Oddělovače ale vybereme rozdělení podle pozic.
Power Query čísluje od nuly, takže jako první pozici určíme 0, rok končí na čtvrté pozici a den na šesté pozici.
Hodnoty se rozdělí do třech sloupců. Následně je musíme spojit do datumu, takže nejprve označíme den, držíme klávesu CTRL a označíme měsíc a rok. Na kartě Transformace vybereme Sloučit sloupce. Jako oddělovač určíme tečku a nový sloupec nazveme jako Datum zpracování.
Datum bude ve formátu textu, aby se z datumu stalo datum, tak stačí změnit datový typ u sloupce.
Další sloupec s datumem má nejen špatný oddělovač, ale jedná se ještě o americký formát datumu. Na tuto úpravu můžeme použít Národní prostředí v datových typech.
Jako datový typ vybereme Datum a jedná se o americké datum, takže vybereme jako Národní prostředí Angličtinu ze Spojených států.
Textové úpravy jsou v Power Query snadné. Sladit velikost písma můžeme přes kartu Transformace, kde vybereme Formát a zde Velké první písmeno každého slova.
Nahradit hodnoty zase můžeme pomocí nástroje Nahradit hodnoty, kde určíme původní hodnotu a následně hodnotu, kterou chceme původní hodnotu nahradit.
Sloupec produkt potřebujeme vyplnit nadřazenými položkami směrem dolů. Označíme sloupec a na kartě Transformace vybereme Vyplnit a dolů.
To samé lze udělat i pro vyplnění směrem nahoru.
V Power Query se snadno převedou i sloupce. Stačí označit sloupce, které nechceme převádět a vybrat Převést ostatní sloupce na řádky.
Tabulka se převede do správné tabulkové podoby. Ze sloupce Atribut můžeme odstranit slovo cena.
Poté již stačí jen přejmenovat sloupce a ujistit se, že máme u všech sloupců správný datový typ. Následně tabulku pošleme do Excelu přes kartu Domů a Zavřít a Načíst.
Vyčištěná tabulka se načte na samostatný list v Excelu a my s ní můžeme dále pracovat.
