Miliony řádků v Excelu | Jak pracovat s obrovskými daty v Excelu

V dnešním videu si ukážeme několik triků a tipů, jak v Excelu můžete pracovat s obrovskými soubory dat, například excelovými soubory, které mají miliony řádků. Excel v práci s obrovskými daty skýtá jednu nevýhodu a tou je ta, že je jeho maximální kapacita něco málo přes milion řádků. Pomocí nástroje Power Query v kombinaci s kontingenčními tabulkami můžete v Excelu ovšem velmi hravě pracovat i s více než miliony řádků.

Excelový soubor ke stažení

Jak v Excelu pracovat s miliony řádků

V dnešním příkladu máme na ploše složku Tržby, kam pravidelně ukládáme excelové soubory s ročními tržbami. V současné době v ní máme tři excelové soubory s tržbami za rok 2020, 2021 a 2022. Tyto excelové soubory mají stejnou strukturu, přičemž záhlaví tabulek je pojmenované stejně ve všech souborech. Všechny tabulky obsahují tři sloupce, informace o datumu, produktu a tržbách. Naším úkolem je zanalyzovat data za tři roky. Jelikož má každý excelový soubor přes půl milionu řádků nemůžeme je jednoduše nakopírovat do jednoho Excelu, do jedné tabulky, jelikož by nám došly řádky v Excelu. Pomůžeme si s nástrojem Power Query, který na rozdíl od Excelu dokáže pracovat s miliony řádků. Nejprve se napojíme na excelové soubory, sloučíme je do jedné tabulky a pomocí kontingenčních tabulek zanalyzujeme data.

Miliony řádků v Excelu 1
Obrázek č.1 Zdrojová data ve složce a náhled na data

Propojení Power Query na složku

V prvním kroku se napojíme na složku, kde máme uložené excelové soubory. Otevřeme prázdný excelový soubor a na liště Data najdeme Načíst a transformovat a zde Načíst data, Ze souboru a ze složky. 

Miliony řádků v Excelu 2
Obrázek č.2 Napojení na složku přes Power Query

Otevře se navigační okno pomocí kterého můžeme najít složku, ve které máme uložené excelové soubory. Složku najdeme, otevřeme a klikneme na Otevřít. Otevře se navigační okno Power Query, kde se zobrazí náhled dat. Výběr potvrdíme a u tlačítka Kombinovat vybereme Sloučit a transformovat data. 

Miliony řádků v Excelu 3
Obrázek č.3 Napojení na složku přes Power Query

Vlevo se zobrazí první soubor, na základě kterého se soubory spojí. Zkontrolujeme v náhledu data a potvrdíme. Power Query bude chvíli pracovat a nakonec načte data do Power Query. Teď máme načtené a sloučené všechny tři listy do jedné tabulky.

Obrázek č.4 Napojení na složku přes Power Query

Jak vidíme, tak se do tabulky přidal ještě jeden extra sloupec navíc s názvem původního excelového souboru. Tento sloupec buď můžeme smazat a nebo využijeme toho, že z něho extrahujeme rok. To se nám ve výsledku může hodit v průřezu u kontingenční tabulky. 

Obrázek č.5 Načtená data do Power Query

Označíme nový sloupec, a na kartě Transformace vybereme Extrahovat a Text před oddělovačem. Zde nastavíme Oddělovač. Naším oddělovačem je v tomto případě tečka. Napíšeme tečku a potvrdíme. Sloupec přejmenujeme na Rok. Ještě musíme změnit datový typ sloupec z textu na celé číslo.

Miliony řádků v Excelu 6
Obrázek č.6 Extrahovat text před oddělovačem

Ještě ověříme, že máme v Power Query opravdu načtená všechna data. Na spodní liště v Power Query klikneme na Profilaci sloupce a změníme to na Profilace sloupců na základě celé sady.

Miliony řádků v Excelu 7
Obrázek č.7 Profilace sloupců na základě celé sady dat

Teď klikneme na jeden sloupec, třeba sloupec s rokem a na kartě Zobrazení vybereme Profil sloupce. Chvíli počkáme než Power Query zpracuje data. Po chvíli se načtou relevantní data o vybraném sloupci, a jak vidíme, tak sloupec obsahuje přesně 1,5 milionu řádků. Nakonec přejmenujeme sloučenou tabulku na Tržby celkem. Teď jsme připravení načíst data zpátky do Excelu.

Miliony řádků v Excelu 8
Obrázek č.8 Profilace sloupce

Načtení dat do Excelu

Zkusíme si nejdříve ukázat, co by se stalo kdybychom vybrali pouze Zavřít a Načíst a načetli celou tabulku do Excelu. Chvíli počkáme než Excel načte data a po nějaké chvíli na nás vyskočí upozornění, že Excel dosáhl svého limitu v počtu řádků a nemůže tudíž data načíst do Excelu. Načítání zrušíme a uděláme to lépe. 

Miliony řádků v Excelu 9
Obrázek č.9 Načíst do Excelu - chyba - více řádků

V pravém panelu se zobrazila sloučená tabulka, u které máme výstrahu, že data nebyla načtená do Excelu. Na tuto sloučenou tabulku můžeme kliknout pravým tlačítkem myši a vybrat načíst do a místo prostého načtení dat do tabulky vybereme Sestava kontingenční tabulky a vybereme si místo umístění kontingenční tabulky. Sloučená tabulka se opět začne načítat. A jak vidíme, tak Excel v tomto případě nemá problém načíst i více než milion řádků. Teď máme všechna data načtená do sestavy kontingenční tabulky a můžeme data analyzovat pomocí kontingenčních tabulek.

Miliony řádků v Excelu 10
Obrázek č. 10 Načíst do - sestava kontingenční tabulky

Analýza pomocí kontingenčních tabulek

Zkusíme vytvořit první tabulku a ověříme, že máme načtená všechna data. Na řádky kontingenční tabulky vložíme Produkty a do oblasti hodnoty vložíme rovněž pole produkty. Tím, že jsme do oblasti hodnoty vložili textovou hodnotu, tak se jako základní výpočet provede počet, nikoliv součet. A jak vidíme, tak v kontingenční tabulce máme načteno přesně 1,5 milionu řádků. 

Obrázek č.11 Kontingenční tabulka

Chceme ovšem analyzovat tržby, takže sloupec produkty z oblasti hodnoty vyhodíme a vložíme do něj sloupec s tržbami. Ještě můžeme upravit formát. Klikneme na hodnoty v kontingenční tabulce, vybereme Formát čísla a zde vybereme Měna a bez desetinných míst.

Obrázek č.12 Analýza pomocí kontingenční tabulky

Odložení aktualizace kontingenční tabulky

Pokud vaše sloučená tabulka obsahuje opravdu velké množství dat, tak se vám může stát, že když budete přetahovat pole v rámci kontingenční tabulky, že bude chvíli trvat než se tabulka vytvoří. A důvodem je velké množství dat, které kontingenční tabulka musí zpracovat. Můžete si pomoci zaškrtnutím pole Odložit aktualizaci rozložení. Ukážeme si, co toto pole dělá. Vyhodíme z kontingenční tabulky všechna pole a zaškrtneme toto pole. Teď, když přesuneme pole do oblastí kontingenční tabulky, tak nedostaneme živý náhled na data, tak jak jsme zvyklí. Tím se zrychlí čas u práce s kontingenční tabulkou. Až když máme tabulku vytvořenou, tak klikneme na Aktualizovat a data se načtou do kontingenční tabulky. 

Tabulku máme hotovu takže odškrtneme toto pole a ještě změníme formát čísla v tabulce. Formát čísla, měna a bez desetinných míst.

Obrázek č.13 Odložení aktualizace rozložení

V kontingenční tabulce můžeme využít i nový sloupec rok. Klikneme do kontingenční tabulky, na kartě Analýza kontingenční tabulky vybereme Průřez a jako pole průřezu vybereme Rok. Teď můžeme kontingenční tabulku s 1,5 milionem řádků dat analyzovat pomocí nového sloupce rok.

Miliony řádků v Excelu 14
Obrázek č.14 Průřez v kontingenční tabulce

Aktualizace dat

A co se stane, pokud do složky přidáme nový soubor? Do složky přidáme excelový soubor, ve kterém máme údaje za prvních 5 měsíců roku 2023. Excelový soubor přetáhneme do složky a vrátíme se k excelovému souboru, kde máme načtená sloučená data. Tím, že jsme se na začátku videa napojili na celou složku, tak se jakýkoliv excel načte do tabulky. Na kartě data vybereme Aktualizovat a chvíli počkáme než se všechna data načtou do sloučené tabulky. Když máme data načtená, tak klikneme do kontingenční tabulky a vybereme Aktualizovat tabulku. A jak vidíme, tak se nová data včetně nového roku okamžitě načetla do kontingenční tabulky. 

Obrázek č.15 Aktualizace dat o nový excelový soubor

MOHLO BY VÁS ZAJÍMAT

Dynamický prodejní report v Excelu | Microsoft 365

V dnešním videu se podíváme na to, jak pomocí dynamických funkcí v Excelu vytvořit plně dynamický prodejní report. Dynamické funkce, které budeme dnes pro vytvoření prodejního reportu

Začínáme s Power BI | Excel a Power BI

V dnešním videu se podíváme na základy práce v Power BI. Power BI je nástroj od společnosti Microsoft, který slouží k interaktivní vizualizaci a analýze dat. Specifikem Power

Jedna odpověď

Napsat komentář

Vaše e-mailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *