V tomto článku se podíváme na to, jak lze v Excelu získávat data z externích zdrojů. Může se to hodit například učitelům při exportování docházky z aplikace Microsoft Teams, o čemž se můžete dočíst níže, nebo studentům při tvorbě prací do školy. Jeden příklad za všechny. Nedávno jsem si potřeboval vytvořit tabulku v Excelu. Jednalo se o souhrn nejbohatších států v EU. Říkal jsem si, že když je tabulka volně dostupná z jiných zdrojů, tak je lepší použít už vytvořenou, namísto zdlouhavého vytváření vlastní. A právě k tomu nám v Excelu slouží hned několik funkcí. Všechny je najdeme na kartě Data ve skupině Načíst a transformovat data. Společně si je projdeme, ukážeme si jejich možnosti a praktické využití.
Ještě předtím, než zcela začneme, řekneme si pár základních informací k těmto funkcím. Využití je samozřejmě jasné, Excel vám nabízí použít už existující tabulky a přetvořit je dle svého. Díky tomu ušetříte váš drahocenný čas. Také vám umožňuje všechny údaje jednoduše aktualizovat, takže jste vždy napojeni na ta nejaktuálnější data. Když si necháte načítat externí tabulku, tak se ve vašem sešitě zobrazí jako běžná tabulka, což umožňuje jakkoli editovat údaje, které jste získali. Když přidáte vlastní sloupec k získané tabulce, tak vám ho Excel při aktualizaci tabulky nesmaže, což je velká výhoda. Co osobně preferuji na těchto funkcích, je jejich jednoduchost a přehlednost. Ovšem tohle vše zjistíte až dále v článku, je toho před námi dost, tak se do toho dáme.
Obsah
Načíst a transformovat data
Jak bylo uvedeno výše, všechny tyto funkce najdeme na kartě Data. V její levé části lze vidět skupinu Načíst a transformovat data. Tady, na jednom místě, najdeme vše, co dnes budeme potřebovat. Excel automaticky vybral pár nejčastěji používaných voleb, které umístil na panel. Jsou to Z textu/CSV, Z webu a Z tabulky nebo oblasti. Jejich použití je podobné a intuitivní.
Ze souboru
Pokud chcete načítat data z textového souboru, stačí zvolit první z uváděných možností, a to Z textu/CSV. Po jejím vybrání se zobrazí dodatečná nabídka, pomocí které si lze například určit, jak má brát Excel v úvahu sloupečky, tedy zda mají pevnou délku, tj. několik znaků, nebo zda jsou odděleny nějakým speciálním znakem, kterým bývá zpravidla středník. Až si to vše nastavíte, tabulka se načte do aktuálního sešitu. A s původním souborem je propojena, takže jakmile v něm uděláte nějaké změny, projeví se po kliknutí na tlačítko Aktualizovat vše ve skupině Dotazy a připojení i v tomto sešitě. Ale pozor, pokud se jedná o lokální soubor z úložiště vašeho zařízení, na ostatních zařízeních fungovat pravděpodobně nebude, protože tam ta cesta k souboru nebude platná. Tento postup se tedy hodí hlavně tehdy, když soubor s nikým neplánujete sdílet. Cestu k souboru, například pokud nebude fungovat po jeho přesunutí, můžete změnit v panelu Dotazy a připojení dostupného ze stejnojmenné skupiny.
Pokud chcete načítat data z jiného typu souboru, podívejte se do nabídky tlačítka Načíst data a do její první možnosti, tedy Ze souboru. Zde se nachází seznam všech typů souborů, ze kterých Excel umí načítat tabulky. Kromě Excelu a souborů CSV je tak možné načítat data i ze souborů formátu XML nebo JSON, z určité lokální složky nebo dokonce ze složky SharePointu, což se hodí. Postup je u všech typů souborů podobný, stačí jej pouze zvolit nebo, v případě SharePointu, zadat URL adresu webu a Excel už sám všechno zpracuje. Nalezené tabulky nám zobrazí a umožní nám je načíst do aktuálního sešitu. Propojení se uloží jako tzv. dotaz do karty Dotazy a připojení, odkud je možné upravit jeho vlastnosti, včetně doby, za kterou se data sama aktualizují.
Pokud jste si tento postup vyzkoušeli, o čemž nepochybuji, možná jste si všimli, že vedle tlačítka Načíst, které tabulku vkládá vždy na nový list, se nachází ještě tlačítko Transformovat data. Po jeho zvolení byste se dostali do relativně nového doplňku s názvem Editor Power Query. Ten nám umožňuje data upravit, kdyby se z nějakého důvodu nenačetla dle očekávání. O jeho funkcích se dozvíte v jednom z budoucích článků, teď jenom prozradím, že úplně stejný editor se nachází i v nástroji Power BI, takže se jej vyplatí umět, protože tím máte zvládnuté dvě mouchy jednou ranou. Pojďme se ale přesunout na další možnosti.
Z webu
Trochu zajímavější je způsob získávání tabulky z webu. Žádné zdlouhavé kopírování tabulky, ale pouze zadání adresy stránky a vybrání tabulky, která je na stránce. Excel totiž dokáže rozeznat tabulky a sám vám je nabídne k použití. Tato data si vezme z kódu HTML. Následně na vás vyskočí ještě jedno okno. V něm naleznete poslední úpravy vaší tabulky. Pak si můžete data ještě transformovat nebo rovnou načíst a je to. Velmi prostá, ale zároveň užitečná funkce, kterou využijete například ve chvíli, kdy vám tabulka nejde z internetu zkopírovat ručně. Připomínám, že na rozdíl od prostého kopírování se připojení zapamatuje a data je možné kdykoli aktualizovat přes tlačítko Aktualizovat vše. Toho byste při použití známého Ctrl + C a Ctrl + V asi nedocílili.
Z tabulky nebo oblasti
Tenhle způsob je velmi zajímavý, pokud chcete rychle a jednoduše vytvořit tabulku například z pár řádků a sloupečků. Naleznete ho samozřejmě na kartě Data a následně v levém horním rohu pod názvem Z tabulky nebo oblasti. Po vybrání oblasti se vám objeví již zmíněný Editor Power Query pro úpravu vaší budoucí tabulky, zde si před vložením doladíte některé detaily. Následně vyberete možnost Zavřít a načíst a to je všechno. Tuto tabulku můžete následně upravovat opět pomocí Power Query nebo k ní přidávat počítané sloupečky, což se hodí. I zde stojí za připomenutí, že jsou tabulky propojené, a nikoli pouze překopírované.
Z databáze a z Azure
Poslední způsob, který si dnes ukážeme, je už trochu pokročilejší. V podstatě vám ulehčuje získání tabulek z různých databází. Postup je následující. Vyberete si, z čeho chcete získat tabulku, například z databáze SQL. Po zvolení možnosti se vám otevře okno, ve kterém zadáte server, z něhož chcete vaši tabulku získat. Nadále musíte projít autentizačním a autorizačním procesem a posléze se dostanete do vaší databáze, ze které už využijete jakoukoliv tabulku. Tohle je zajímavý způsob, jak si převést vaše cenná data z databáze do Excelu, kde s nimi můžete nadále pracovat a analyzovat je pomocí vzorečků jednodušších než v SQL. Podobný princip je možné aplikovat i na Azure databáze a úložiště, na Dynamics 365, Exchange Online a SharePoint Online.
Využití při vedení docházky
Praktickým příkladem, jak mohou importování dat do Excelu využít také učitelé, je například vedení docházky. Aplikace Microsoft Teams, o které zde máme již nespočet článků pod tagem #MicrosoftTeams, nově umožňuje exportovat si docházku z online hodiny. Pokud tuto možnost využíváte, pravděpodobně víte, že ji exportuje do formátu CSV. To se nám ovšem přece skvěle hodí! Takto vygenerovaný soubor si můžete importovat do vašeho sešitu a tabulku dále zpracovávat. Jak na to?
Začněte vytvořením nového prázdného sešitu Excelu standardním způsobem. Rovnou si jej můžete uložit a pojmenovat podle třídy, ke které patří. Dále doporučujeme pojmenovat si i aktuální list, to proto, abyste později byli schopni zjistit, z jaké hodiny vlastně je. Pojmenovat list můžete přes kliknutí pravým tlačítkem na ouško s názvem listu dole a volbu možnosti Přejmenovat.
Dalším krokem je samotné importování tabulky. Běžte tedy do dnes tolik probírané karty Data a zvolte Načíst z textu/CSV. Zobrazí se dialogové okno s možností výběru souboru. Zde tedy vyberte soubor s docházkou exportovanou aplikací Teams a klikněte na Importovat. Zobrazí se další dialogové okno, kde se objeví náhled tabulky. Vše by se mělo nastavit samo, ale pokud náhodou ne, zkontrolujte, že oddělovač je nastavený na Tabulátor. Pokud vše vypadá v pořádku, stiskněte šipku vedle tlačítka Načíst a vyberte možnost Načíst do… To proto, aby se vám nevytvořil nový list, ale abyste byli schopni vložit tuto tabulku do již existujícího listu, který jste si před chvíli pojmenovali. V dalším dialogovém okně tedy vyberte možnost existující list. To je vše a hned by se v daném listu měla objevit tabulka s třemi sloupci, Celé jméno, Akce uživatele a Časová známka.
Abyste z toho vytvořili ještě přehlednější tabulku, budete potřebovat kontingenční tabulku. Klikněte do libovolné buňky v tabulce z předchozího kroku a na kartě Vložení vyberte Kontingenční tabulka. Otevře se nové dialogové okno, kde opět zvolte možnost Existující list a ještě vedle pole umístění klikněte na šipku pro výběr buňky. Objeví se nabídka, ve které označte libovolnou buňku napravo od tabulky, a poté ji potvrďte stisknutím šipky vedle zadávacího pole. Vše teď potvrďte tlačítkem OK. V listu se vytvoří nová kontingenční tabulka a vpravo se otevře panel Pole kontingenční tabulky. Na něj se nyní zaměříme.
V pravém panelu zaškrtněte všechny tři položky v pořadí, v jakém jsou. Pokud se po zaškrtnutí Časové známky zobrazí ještě Minuty a Hodiny, odškrtněte je. Vpravo dole ve stejném panelu je sekce Řádky, kde se objevily tři položky, z ní přesuňte tažením položku Časová známka do sekce Hodnoty. Poté na tuto položku klikněte a vyberte možnost Nastavení polí hodnot… Objeví se dialogové okno, tam vyberte Minimum v seznamu a dále pokračujte stisknutím tlačítka Formáty čísel. Zobrazí se ještě další dialogové okno, tam v seznamu vlevo vyberte možnost Vlastní a poté v seznamu vpravo možnost d. m. yyyy h:mm. Pokud tam taková možnost není, můžete ji zadat sami. Poté už jenom potvrďte toto okno a i to předchozí tlačítky OK a jste u konce.
Tím je tabulka hotova. Nyní tabulka vypadá tak, že se vám u každého studenta zobrazí tučně čas prvního připojení a postupně pod ním jednotlivé akce a jejich časy, tedy kdy se připojil a kdy odpojil. Věřím, že právě tento návod mnoha učitelům pomůže.
Závěrem
Pár slov na závěr k těmto funkcím. Je strašně zajímavé, jak se vše v dnešní době zrychluje a usnadňuje. Místo toho, abych tvořil složitě novou tabulku o nejbohatších státech v EU, tak jsem jednoduše zadal adresu z internetu, vybral si tabulku a bylo hotovo. Nadále jsem si upravil pár pojmů a tabulka byla na světě. A učitelé zase mohou relativně snadno zpracovat úplně automaticky generovaný CSV soubor. Určitě vám doporučuji tyto funkce, které vám pomohou v ušetření času. V případě dotazů nám napište.
