Spojení všech tabulek v adresáři pomocí Power Query (Načíst a transformovat)

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

 

Napsat komentář

Vaše emailová adresa nebude zveřejněna.

Vyhledávání

Exact matches only
Search in title
Search in content
Search in posts
Search in pages
Filter by Categories
Nezařazené

Nyní otevřený kurz

Jednodenní Excel v praxi

  • 14.3.2017
  • V Praze
Další info / přihláška

Firemní kurzy

  • Přemýšlíte o firemním školení Excelu?
  • Školíme Excel od začátečníků po experty, ale také PowerPoint, PowerPivot nebo Access.
Mám zájem o kurz

Individuální výuka

Mám zájem o výuku

Vývoj aplikací v Excelu (makra - VBA)

  • Vyvíjíme aplikace pro potřeby firemních zákazníků
  • Reporty, vizualizace dat, harmonogramy, plánování
  • Slučování dat ze souborů, agregace, exporty
Zajímám se o vytvoření aplikace

Kontakt

  • Tel: +420 602 274 999
  • Mail: info@vyuka-excelu.cz
Kontaktní formulář

FB CZ

TOPlist