Práce s časem v Excelu může být někdy frustrující. Ostatně o tom máme celé video na Akademii Excelu, kde si ukazujeme, jak vyřešit nejčastější problémy u počítání s časem v Excelu. Pro výpočty a úpravy času existuje v Excelu několik různých funkcí, nicméně s časem můžete v Excelu pracovat i bez použití jediné excelové funkce. A to pomocí nástroje Power Query. A právě to, jak s časem pracovat pomocí Power Query, si ukážeme v dnešním videu.
Pro členy Akademie Excelu máme rovněž navazující video, kde si ukážeme, jak pomocí Power Query spočítat rozdíl v čase, který překračuje půlnoc.
Excelový soubor ke stažení:
Jak pracovat s časem v Power Query
V excelovém sešitu máme malou tabulku s datumy a časy. Z této tabulky chceme do sloupců postupně vyselektovat datum i čas. Pokud nechceme úpravy dělat pomocí excelových funkcí, tak máme možnost úpravy provést v Power Query. Na to, abychom mohli data nahrát do Power Query, ale musíme nejprve změnit zdrojovou tabulku na excelovou tabulku. Klikneme do tabulky a zmáčkneme klávesovou kombinaci CTRL+T, potvrdíme, že zdrojová tabulka má záhlaví a tabulka se změnila na excelovou tabulku.
Teď můžeme tabulku nahrát do Power Query. Klikneme do tabulky a na kartě Data najdeme Načíst a transformovat data a vybereme z Tabulky nebo oblasti. Tabulka se po chvilce nahraje do Power Query, kde s ní teď můžeme provádět úpravy.
Začneme tím, že nejprve zkontrolujeme, že máme dat nahraná v Power Query ve správném formátu. Ve sloupci máme datum a čas, takže i datový typ sloupce by měl být nastavený na formát datum a čas. Power Query někdy tuto úpravu udělá za vás, pokud ve sloupci ale vidíte jiný datový typ, tak ho přes ikonu v záhlaví můžete změnit na správný formát.
Teď když máme datový typ sloupce správně, tak můžeme přistoupit k prvním úpravám. Nejprve chceme ze sloupce vyselektovat datum. Jelikož chceme do tabulky sloupec přidat, tak na horní liště vybereme záložku Přidání sloupce a najdeme možnost Datum. Když rozklikneme šipku u Datumu, tak zde máme možnost Pouze datum. Vybereme tuto možnost a do vedlejšího sloupce se vyselektovaly pouze datumy.
To samé můžeme udělat s časem. Označíme původní sloupec a na kartě Přidání sloupce vybereme tentokrát možnost Čas a vybereme Pouze čas. Do dalšího sloupce se přidal pouze čas.
Stejně tak můžeme z času vyselektovat pouze hodiny, minuty a vteřiny. Můžeme označit původní zdrojový sloupec a na kartě Přidání sloupce vybrat možnost Čas a v nabídce vybrat Hodina. Tím se do tabulky přidání nový sloupec, který bude obsahovat pouze hodinu.
To samé můžeme udělat pro minuty. Stačí označit původní zdrojový sloupec a na kartě Přidání sloupce vybrat Čas a místo hodiny vybrat tentokrát Minuty. Tím se do tabulky vloží nový sloupec, ve kterém budou uvedené pouze minuty.
Ještě přidáme sloupec pro vteřiny. Označíme zdrojový sloupec a na kartě Přidání sloupce vybereme Čas a Sekunda. Teď máme čas rozložený na hodiny, minuty a sekundy.
A co kdybychom tyto sloupce potřebovali zpátky složit dohromady jako čas? V takovém případě nejprve označíme sloupec hodiny, držíme klávesu CTRL, označíme sloupec s minutami, stále držíme klávesu CTRL a označíme i poslední sloupec s vteřinami. Teď na záložce Přidání sloupce vybereme Sloučit sloupce.
Dostaneme na výběr pomocí jakého oddělovače chceme hodnoty spojit. Chceme, aby výsledkem byl čas, takže vybereme dvojtečku. A název sloupce bude třeba Čas dohromady. Potvrdíme a máme jednotlivé hodnoty sloučené dohromady a oddělené dvojtečkou.
Nicméně datový typ sloupce je text, proto máme čas zarovnaný doleva.
Musíme tedy upravit datový typ, takže v záhlaví klikneme na ikonu datového typu a vybereme Čas. Teď máme čas složený dohromady jako čas.
Úpravy jsou hotové, takže můžeme tabulku poslat zpátky do Excelu. Ještě ji můžeme pojmenovat. Nazveme ji třeba jako Čas. Když jsme hotovi, tak na záložce Domů vybereme Zavřít a Načíst. Tabulka se načte na samostatný list.
Výhodou propojení zdrojové tabulky na Power Query je to, že se tabulka lehce aktualizuje. Pokud do zdrojové tabulky přidáme nové údaje, tak budou údaje okamžitě zahrnuté do zdrojové tabulky, jelikož je ve formátu excelové tabulky. Překlikneme se zpátky na tabulku na listu Čas a na kartě Data vybereme Aktualizovat vše. Tím se propojení mezi tabulkami zaktualizuje a nová data včetně úprav se načtou do tabulky s časem.
S časem můžeme v Power Query i počítat. Ukážeme si to na dalším příkladu, kde máme počáteční a konečný čas. V prvním příkladu, který je lehčí, máme čas včetně datumu. Naším úkolem je zjistit rozdíl mezi těmito časy. Některé časy ovšem přesahují jeden den, což víme, že je v Excelu problém, který se musí obejít buď pomocí funkce KDYŽ nebo MOD.
Zkusíme rozdíl v čase spočítat pomocí Power Query. Opět nejprve zdrojovou tabulku změníme na excelovou tabulku. Klikneme do tabulky a zmáčkneme kombinaci CTRL+T, tabulka se změní na excelovou tabulku, kterou teď můžeme nahrát do Power Query. Klikneme do tabulky a na kartě Data najdeme Načíst a transformovat a vybereme Z tabulky nebo oblasti. Počkáme až se tabulka nahraje do Power Query a začneme s úpravami.
Nejprve zkontrolujeme, že mají sloupce správný datový typ datumu a času. Zkontrolujeme ikonu datového typu a v případě, že zde vidíme jiný typ než datum a čas, tak změníme datový typ.
Naším úkolem je zjistit rozdíl v čase mezi těmito dvěma sloupci. Abychom rozdíl zjistili, tak musíme od sebe časy odečíst. Nejprve označíme koncový čas, držíme klávesu CTRL a označíme i počáteční čas. Musíme časy označit ve správném pořadí, nejprve koncový čas a až potom počáteční čas. Když máme sloupce označené, tak na záložce Přidání sloupce najdeme možnost Čas a zde možnost odečíst.
Do tabulky se přidal nový sloupec, kde máme rozdíl v čase. Datový typ se změnil na datový typ Trvání. Tento typ je vyjádřený nejprve jako den, následuje tečka a pak následuje čas v hodinách, minutách a vteřinách.
Pokud bychom rozdíl v čase chtěli vyjádřit jen jako číslo, tak můžeme sloupec označit a na kartě Přidání sloupce vybrat Trvání a vybrat hodiny.
Tím se rozdíl v čase zaokrouhlí na celé hodiny, ale přišli jsme o vyjádření v minutách.
Kdybychom chtěli přesný rozdíl v čase, tak opět označíme sloupec a na kartě Přidání sloupce vybereme Trvání a vybereme celkový počet hodin.
To nám vrátí celou hodinu a za desetinnou čárkou jsou uvedené minuty.
Rozdíl máme spočítaný, takže tabulku můžeme nahrát zpátky do Power Query. Přejmenujeme tabulku na rozdíl v čase.
Na kartě Domů vybereme Zavřít a Načíst. Tabulka se načetla do Excelu a teď s ní můžeme dále pracovat.
Jedna odpověď
👍😊