Tento článek je o tom, jak spojit velké množství tabulek, které jsou v jednom adresáři a mají stejné sloupce - například prodejní data za několik měsíců.

Využíváme v něm nástroj, který se ve verzi Excelu 2016 jmenuje "Načíst a transformovat" a je součástí každého Excelu, u verze 2010 a 2013 funguje jako zdarma stáhnutelný doplněk Power Query. U verze Excelu 2007 a starší tento nástroj nefunguje a tento návod tedy nelze použít.

Postup je jednoduchý a nezabere více než několik minut, ačkoliv zní trochu hrůzostrašně.

  1. Nejprve v Načíst a transformovat vytvoříme dotaz načítající obsah jedné z tabulek.
  2. Pak tento dotaz změníme na vlastní funkci.
  3. Pak dalším dotazem načteme názvy a cesty všech souborů. Na všechny tyto soubory, zjištěné na základě dotazu, pak pustíme vytvořenou vlastní funkci - tedy načteme data ze všech souborů.

V našem případě budeme slučovat pracovní výkazy zaměstnanců firmy za více měsíců:

adresar

Všechny tabulky mají stejné sloupce. (Data nemusí být ve vložené tabulce jako je to tady.)

zdrojova-tabulka

Jdeme do souboru (např. nového), kde se mají data spojit.

Vytvoříme dotaz a načteme data z jednoho (jakéhokoliv) souboru. Základní návod na načtení dat pomocí Načíst a transformovat (Power Query) je tady.

Dotaz vytvoříme, ale data nenačteme. V Domů klikneme na Rozšířený editor, zobrazí se text dotazu. My v něm potřebujeme udělat dvě malé změny.

funkce

Nejprve uděláme z příkazu funkci. Tedy před "let" na začátku napíšeme:

  • (cesta)=>

Tím definujeme, že se jedná o funkci nazvanou (např.) "cesta".

Pak ještě změníme cestu, která odkazuje k souboru, na proměnnou "cesta". Výsledek je takovýto:


(cesta)=>

let
  Zdroj = Excel.Workbook(File.Contents(cesta), null, true),
  Tabulka1_Table = Zdroj{[Item="Tabulka1",Kind="Table"]}[Data],
  #"Změněný typ" = Table.TransformColumnTypes(Tabulka1_Table,{{"Jméno", type text}, {"Datum", type date}, {"Hodin", Int64.Type}, {"Druh práce", type text}, {"Kluk / Holka", type text},    {"Den týdne", type text}})
in
  #"Změněný typ"


Potvrdíme a dotaz s uložením zavřeme. Funkci si přejmenujeme např. na Spojeni.

hotova-funkce

Jdeme na další dotaz, tentokrát ze složky.

ze-slozky

Vybereme adresář, ve kterém jsou soubory, které se mají slučovat.

vybrany-adresar

Obsahem dotazu je výpis všech souborů (jedná se ale jen o výpis, ne o jejich obsah...).

vlastni-soubor

V úpravě tohoto souboru přidáme vlastní sloupec.

vlastni-soubor

Do něj zapíšeme nebo naklikáme výpočet:

  • =Spojeni([Folder Path]&[Name])

vzorec-pro-vlastni-sloupec

Potvrdíme. V posledním sloupci klikneme na dvojitou šipku v záhlaví a potvrdíme Rozbalit.

hotovy-vlastni-sloupec

Jdeme na Domů / Zavřít a načíst. Data jsou načtená a při jakékoliv změně se sama aktualizují.

nactena-data

Pokud byste chtěli tento návod vytvořit s relativní cestou ke složce (tedy tak, aby fungovala i při překopírování jinam), jděte na tento návod.

Napsat komentář

Vaše emailová adresa nebude publikována.

smazat formulářOdeslat komentář