Jak sloučit data z několika sešitů aplikace Excel. Řekněme, že máte složku plnou souborů aplikace Excel. Každý z nich má jeden list s různým počtem řádků. Můžete importovat všechny soubory ve složce do jednoho sešitu aplikace Excel.
Sledovat video
- Přehodnocení čistých dat pomocí podcastu Power Query # 2037
- Power Query nyní může kombinovat všechny soubory aplikace Excel ve složce.
- Vylepšeno: Automaticky odstraní záhlaví ze všech souborů kromě prvního.
- Vyberete, který soubor chcete použít jako ukázkový soubor.
- Vyberte, který list, tabulku a pojmenovaný rozsah chcete importovat
- Pomocí editoru dotazů proveďte jakékoli transformace
- Zavřít a načíst kombinovat všechny soubory
- Později aktualizujte dotaz, aby se aktualizoval
Přepis videa
Naučte se Excel z Podcastu, epizoda 2077: Sloučení sešitů
Hej, vítej zpět na netcastu, jsem Bill Jelen. Před pouhých 40 epizodami hovoříme o čištění dat pomocí Power Query, o tom, jak vzít celou hromadu souborů CSV a přenést je do jednoho souboru Excel. Tato funkce se právě dramaticky zlepšila.
Takže dnes tu mám složku a v této složce je celá řada měsíčních souborů aplikace Excel, 22 z nich téměř dva roky. Musím spojit všechny tyto soubory. Nyní mají všechny stejné rozložení sloupců, dobře? Takže všechny mají 9 sloupců, všechny mají stejné záhlaví a tak dále, dobře. Takže to vše spojíme bez makra.
Pokud jste v aplikaci Excel 2016, přejdete na kartu Data, abyste získali a transformovali data. Pokud používáte Excel 2010 nebo 2013, půjdete si stáhnout skvělý produkt od společnosti Microsoft s názvem Power Query. Celé si můžete stáhnout a nainstalovat za méně než minutu, dobře.
Pojdeme sem proto, abychom získali data ze souboru, ale ne z jednoho sešitu. Chceme to získat ze složky, jako je tato. Dobře, tady jsou. Žádají vás, abyste prošli do složky, ale já tam jen kliknu a vložím Ctrl + V, protože jsem vložil adresář, klikněte na OK. Ukázali mi tedy náhled dat, která mám. Otevírám zde malou rozevírací nabídku Combine a zvolím Combine & Edit. Dobře, a pak je zde krok, že vybereme jeden soubor, který je dobrým reprezentativním souborem, dobře, a oni si prostě vyberou první, který je shodný v abecedním pořadí. Nezáleží na tom, který si vyberete. A pak tento Ukázkový dotaz, který vytváříme z prvního souboru, který si vybereme, v tomto případě chci List1.Pokud máte více listů, můžete říci List2 nebo list nazvaný Výkaz příjmů nebo Rozsah jmen nebo tabulka. A oni mi ukázali, jak to vypadá, a já kliknu na OK.
Na této obrazovce se toho nyní děje hodně. To, co zde vlastně udělali, je, že vytvořili Ukázkový dotaz, ahh Ukázkový dotaz, a poté jej vytvořili jako funkci a spustili tuto funkci proti všemu ve složce. Přidají nový sloupec, který mi říká, odkud soubor pochází, a v tomto konkrétním případě už vím, odkud pochází, protože zde máme vyplněno datum. Takže to nepotřebuji, kliknu pravým tlačítkem a odstraním to. A možná tam jsou nějaká data, která nepotřebuji. Možná tato data půjdou k zákazníkovi, takže nechci ukázat zisk, abych mohl tento sloupec odstranit a dokonce odstranit sloupec Náklady, a možná jsou nějaké nepříjemné věci, jako například, oni používali špatné hlavičky a já chcete tyto hlavičky přejmenovat. Klikněte tedy pravým tlačítkem a přejmenujte, budeme to nazývat POČET pro množství.Možná jsou věci ve špatném pořadí, možná chci vzít tuto položku a přesunout ji na jiné místo, dobře? Jen všechny tyto malé nepříjemné věci, které byste možná měli, a samozřejmě tady, dělají všechny aplikované kroky a pak můžete pokračovat v provádění všech věcí Power Query, které musíte udělat. Ale v tomto případě je to prostě jednoduché. Chci jen dostat data dovnitř. Takže se chystám zavřít a načíst, pár sekund uběhne a je to. Mám své finále. Pojďme to zde seřadit podle data; vidíte, že jdeme od ledna 2018 až do října 2019. To je skvělé.znovu provedete všechny použité kroky a pak můžete pokračovat v provádění všech věcí Power Query, které musíte udělat. Ale v tomto případě je to prostě jednoduché. Chci jen dostat data dovnitř. Takže se chystám zavřít a načíst, pár sekund uběhne a je to. Mám své finále. Pojďme to zde seřadit podle data; vidíte, že jdeme od ledna 2018 až do října 2019. To je skvělé.znovu provedete všechny použité kroky a pak můžete pokračovat v provádění všech věcí v Power Query, které musíte udělat. Ale v tomto případě je to prostě jednoduché. Chci jen dostat data dovnitř. Takže se chystám zavřít a načíst, pár sekund uběhne a je to. Mám své finále. Pojďme to zde seřadit podle data; vidíte, že jdeme od ledna 2018 až do října 2019. To je skvělé.
Tady je úžasná a úžasná věc. Řekněme, že se v naší složce něco změní. Takže zpět v souborech Měsíc, mám dva nové soubory, Vložit, a teď jsou tu další dva záznamy, dva další soubory Excel, které musím kombinovat. Stačí se vrátit k mému souboru Excel, který má v současné době 516 řádků, najít MonthFiles a kliknout na tlačítko Obnovit. A tak rychle mám nové záznamy z listopadu a prosince. Power Query je úžasný nový nástroj zdarma pro Excel 2010, zdarma pro Excel 2013. Bylo krásné, když mohli kombinovat soubory CSV, ale nyní, když mohou kombinovat také soubory Excel, prostě fenomenální.
Aktualizoval jsem tedy svoji knihu Power Excel pomocí edice 2017, která obsahuje Power Query, Power BI a všechny nové nástroje. Koukni na to. Klikněte na to „i“ v pravém horním rohu.
Dobře, shrnutí této epizody: Přehodnocujeme Čistá data pomocí podcastu Power Query # 2037, kde kombinujeme více souborů CSV. Nyní můžeme použít stejný nástroj, vylepšenou verzi tohoto nástroje, ke kombinování všech souborů aplikace Excel ve složce. Další vylepšení, změna od roku 2037, automaticky odstraní záhlaví ze všech kromě prvního souboru a propaguje tyto záhlaví v prvním souboru. Vyberete, který soubor chcete použít jako ukázkový soubor. V mém případě jsem použil leden. Práce - Vyberte, který list, tabulku nebo pojmenovaný rozsah chcete importovat, a poté proveďte jakékoli transformace v editoru dotazů. Klikneme na Zavřít a načíst, bude to kombinovat všechny soubory. A pak, pokud do složky vložíte více souborů nebo některé soubory odeberete nebo některé soubory změníte, jednoduše obnovte dotaz a všechny tyto kroky budou provedeny znovu. Power Query,naprosto úžasný nástroj.
Chci vám poděkovat, že jste se zastavili. Uvidíme se příště na dalším netcastu z.
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Podcast2077.xlsm
