Ako zlúčiť údaje z niekoľkých zošitov programu Excel. Povedzme, že máte priečinok plný súborov programu Excel. Každý z nich má jeden pracovný hárok s rôznym počtom riadkov. Môžete importovať všetky súbory v priečinku do jedného zošita programu Excel.
Pozeraj video
- Prehodnotenie čistých údajov pomocou podcastu Power Query # 2037
- Power Query teraz môže kombinovať všetky súbory programu Excel do priečinka.
- Vylepšené: Automaticky odstránia hlavičky zo všetkých súborov okrem prvého.
- Vyberiete, ktorý súbor sa má použiť ako ukážkový súbor.
- Vyberte, ktorý pracovný hárok, tabuľku a pomenovaný rozsah chcete importovať
- Na vykonanie akýchkoľvek transformácií použite editor dotazov
- Zatvorením a načítaním skombinujete všetky súbory
- Neskôr dopyt obnovte, aby sa aktualizoval
Prepis videa
Naučte sa Excel z Podcastu, epizóda 2077: Zlúčenie pracovných zošitov
Ahoj, vitaj späť na netcastu, som Bill Jelen. Len pred 40 epizódami hovoríme o čistení údajov pomocou Power Query, o tom, ako zobrať celú hromadu súborov CSV a spojiť ich do jedného súboru Excel. Táto funkcia sa práve dramaticky zlepšila.
Takže tu dnes mám priečinok a v tomto priečinku je celá séria mesačných súborov Excel, z toho 22 takmer dva roky. Potrebujem skombinovať všetky tieto súbory. Teraz majú všetky rovnaké rozloženie stĺpcov, dobre? Takže všetky majú 9 stĺpcov, všetky majú rovnaké hlavičky a tak ďalej, dobre. Všetko to teda skombinujeme bez makra.
Ak ste v programe Excel 2016, prejdete na kartu Údaje a získate a transformujete údaje. Ak ste v programe Excel 2010 alebo 2013, chystáte sa stiahnuť si vynikajúci produkt od spoločnosti Microsoft s názvom Power Query. Môžete si to celé stiahnuť a nainštalovať za menej ako minútu, v poriadku.
Ideme sem preto, aby sme získali údaje, zo súboru, ale nie z jedného zošita. Chceme to získať z priečinka, ako je tento. Dobre, tu sú. Žiadajú vás, aby ste prešli do priečinka, ale iba tam kliknem a vložím Ctrl + V, pretože som vložil adresár, kliknite na OK. Ukázali mi teda ukážku údajov, ktoré mám. Tu otvorím túto malú rozbaľovaciu ponuku Kombinovať a vyberiem Kombinovať a upraviť. Dobre, a týmto krokom je, že vyberieme jeden súbor, ktorý je dobrým reprezentatívnym súborom, v poriadku, a oni iba vyberú prvý, ktorý je v abecednom poradí. Nezáleží na tom, ktorý si vyberiete. A potom tento Ukážkový dopyt, ktorý zostavujeme z prvého súboru, ktorý si vyberieme, v tomto prípade chcem List1.Ak máte viac listov, môžete povedať List2 alebo list s názvom Výkaz ziskov a strát alebo Rozsah mien alebo tabuľku. A oni mi ukázali, ako to vyzerá, a klikám na OK.
Na tejto obrazovke sa toho teraz deje veľa. To, čo tu vlastne urobili, je, že vytvorili Ukážkový dotaz, ahh Ukážkový dotaz, a potom ho vytvorili ako funkciu a spustili túto funkciu proti všetkému v priečinku. Pridávajú nový stĺpec, ktorý mi hovorí, odkiaľ súbor pochádza, a v tomto konkrétnom prípade už viem, odkiaľ pochádza, pretože tu máme vyplnený dátum. Takže to nepotrebujem, kliknem pravým tlačidlom myši a odstránim to. A možno sú tam nejaké údaje, ktoré nepotrebujem. Možno tieto údaje pôjdu zákazníkovi, takže nechcem zobrazovať zisk, aby som mohol odstrániť tento stĺpec a dokonca odstrániť stĺpec Náklady, a možno sú tu nejaké nepríjemné veci, napríklad, použili zlé hlavičky a ja chcete tieto hlavičky premenovať. Kliknite teda pravým tlačidlom myši a premenujte, nazveme to MNOŽSTVO množstva.Možno sú veci v nesprávnom poradí, možno chcem vziať túto položku a presunúť ju na iné miesto, dobre? Iba všetky tieto malé nepríjemné veci, ktoré by ste mohli mať, a samozrejme tu, robia všetky použité kroky, a potom môžete pokračovať v akýchkoľvek veciach, ktoré musíte urobiť. Ale v tomto prípade je to len jednoduché. Chcem iba získať údaje. Takže idem na Zavrieť a načítať, pár sekúnd prejde a je to. Mám svoje finále. Poďme to sem zoradiť podľa dátumu; vidíte tam ideme od januára 2018 až do októbra 2019. Aké je to super.robíte všetky použité kroky a potom môžete pokračovať v robení akýchkoľvek vecí v Power Query, ktoré musíte urobiť. Ale v tomto prípade je to len jednoduché. Chcem iba získať údaje. Takže idem na Zavrieť a načítať, pár sekúnd prejde a je to. Mám svoje finále. Poďme to sem zoradiť podľa dátumu; vidíte tam ideme od januára 2018 až do októbra 2019. Aké je to super.robíte všetky použité kroky a potom môžete pokračovať v robení akýchkoľvek vecí v Power Query, ktoré musíte urobiť. Ale v tomto prípade je to len jednoduché. Chcem iba získať údaje. Takže idem na Zavrieť a načítať, pár sekúnd prejde a je to. Mám svoje finále. Poďme to sem zoradiť podľa dátumu; vidíte tam ideme od januára 2018 až do októbra 2019. Aké je to super.
Teraz je na tom úžasná a úžasná vec. Povedzme, že sa niečo v našom priečinku mení. Takže späť v súboroch Mesiac získam dva nové súbory, Prilepiť, a teraz sú tu ďalšie dva záznamy, ďalšie dva súbory programu Excel, ktoré musím skombinovať. Stačí sa vrátiť k môjmu súboru programu Excel, ktorý má momentálne 516 riadkov, nájsť súbory MonthFiles a kliknúť na tlačidlo Obnoviť. A že rýchlo mám teraz nové záznamy z novembra a decembra. Power Query je úžasný nový nástroj zadarmo pre program Excel 2010, bezplatne pre program Excel 2013. Bolo nádherné, keď mohli kombinovať súbory CSV, ale teraz, keď už môžu kombinovať aj súbory programu Excel, je to fenomenálne.
Aktualizoval som teda svoju knihu Power Excel o vydanie 2017, ktorá obsahuje Power Query, Power BI a všetky nové nástroje. Skontrolovať to. Kliknite na to „i“ v pravom hornom rohu.
Dobre, zhrnutie tejto epizódy: Prehodnocujeme Čisté dáta pomocou podcastu Power Query # 2037, kde kombinujeme viac súborov CSV. Ten istý nástroj, vylepšenú verziu tohto nástroja, teraz môžeme použiť na kombinovanie všetkých súborov programu Excel do priečinka. Ďalším vylepšením, zmenou od roku 2037, sú automatické odstránenie hlavičiek zo všetkých súborov okrem prvého a podpora týchto hlavičiek v prvom súbore. Vyberiete, ktorý súbor sa má použiť ako ukážkový súbor. V mojom prípade som použil január. Pracovná - Vyberte, ktorý pracovný hárok, tabuľku alebo pomenovaný rozsah chcete importovať, a potom urobte akékoľvek transformácie v editore dotazov. Klikneme na Zavrieť a načítať, skombinuje sa všetky súbory. A potom, ak do priečinka vložíte viac súborov alebo niektoré súbory odoberiete alebo zmeníte niektoré súbory, jednoducho obnovte dopyt a všetky tieto kroky sa vykonajú znova. Power Query,úplne úžasný nástroj.
Chcem sa vám poďakovať, že ste sa zastavili. Uvidíme sa nabudúce pri ďalšom netcaste z.
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Podcast2077.xlsm
