Ako zlúčiť viacero hárkov programu Excel do jedného: všetky možnosti

Ak často pracujete s viacerými tabuľkami, pravdepodobne ste sa pristihli pri šialenom kopírovaní a vkladaní, aby ste zhromaždili informácie roztrúsené po rôznych kartách. Zlúčenie viacerých excelovských hárkov do jedného nielen šetrí čas, ale aj znižuje počet chýb, zjednodušuje tvorbu prehľadov a umožňuje oveľa jednoduchšiu analýzu údajov.

Dobrou správou je, že nie ste obmedzení len na typické kopírovanie a vkladanie. Excel a niektoré externé nástroje ponúkajú niekoľko metód na kombinovanie hárkov z toho istého súboru, z rôznych zošitov, s duplikátmi alebo bez nich, a dokonca aj na základe bežných stĺpcov, ako je napríklad identifikátor produktu. Pozrime sa krok za krokom na všetky praktické alternatívy a na to, čo je najlepšie použiť v každom prípade.

Aké máte možnosti na zlúčenie viacerých excelovských hárkov?

Predtým, ako sa pustíme do jednotlivých krokov, je užitočné mať jasný prehľad. Existuje mnoho rôznych prístupov ku konsolidácii tabuliek : od vstavaných funkcií programu Excel (Consolidate, Power Query) až po makrá VBA alebo externé webové služby a automatizačné riešenia ako Coupler.io alebo ASPOSE.

Najlepšia metóda bude závisieť od toho, či majú vaše tabuľky rovnakú štruktúru, či sú v tom istom súbore alebo v rôznych priečinkoch a ako dobre ovládate Excel. Zostavovanie číselných súčtov pre manažérsku správu nie je to isté ako prepojenie tabuliek predaja a financií pomocou spoločného stĺpca pre podrobnú analýzu.

Zlúčenie údajov z viacerých hárkov pomocou funkcie Consolidate v Exceli

Jedným z najklasickejších a najmenej známych spôsobov konsolidácie informácií je použitie príkazu Konsolidovať v Exceli. Konsolidácia umožňuje zhrnúť číselné údaje roztrúsené vo viacerých pracovných hárkoch alebo zošitoch a zoskupiť ich do „hlavného“ hárka so súčtami, priemermi, počtami atď.

Predstavte si, že sledujete výdavky podľa pobočky: jeden hárok na kanceláriu, všetky s podobnou tabuľkou výdavkov. Vďaka konsolidácii môžete vytvoriť firemnú tabuľku len niekoľkými kliknutiami , ktorá zhromažďuje údaje zo všetkých lokalít a vypočítava súčty, priemery, stav zásob alebo akúkoľvek inú metriku, ktorú potrebujete.

Excel ponúka dve metódy konsolidácie: podľa pozície a podľa kategórie . Výber závisí od toho, ako sú vaše rozsahy usporiadané v rôznych pracovných hárkoch: či sú bunky zarovnané na rovnakých súradniciach alebo či používate označenia riadkov a stĺpcov.

Konsolidovať podľa pozície: keď sú všetky hárky „rozložené“ rovnako

Konsolidácia pozícií funguje veľmi dobre, keď všetky hárky majú rovnaký návrh tabuľky : rovnaké stĺpce v rovnakom poradí, rovnaké riadky v rovnakom poradí a žiadne prázdne miesta v zozname údajov.

Aby tento systém fungoval hladko, skontrolujte každý zdrojový hárok a uistite sa, že rozsahy údajov v každom z nich zaberajú presne rovnaké bunky bez prázdnych riadkov alebo stĺpcov medzi nimi. Je to ako prekrývanie viacerých priehľadných fólií: Excel sčíta alebo spracuje údaje, ktoré spadajú do tej istej „bunky“.

Všeobecný postup je tento:

• Otvoriť všetky zdrojové listy alebo knihy s údajmi, ktoré chcete pridať alebo zhrnúť, a skontrolujte, či je tabuľka v každom z nich zarovnaná.
• V zošite, v ktorom chcete zobraziť súčet, prejdite na cieľový hárok a vyberte bunku vľavo hore kam ma chceš mať topánka konsolidovaná tabuľka.
• Na karte s údajmi zadajte príslušný nástroj (v španielskych verziách je to zvyčajne Dáta > Konsolidovať).
• V okne Konsolidácia vyberte v poli función Ak chcete sčítať, spriemerovať, počítať atď.
• Pre každý zdrojový hárok vyberte rozsah, ktorý obsahuje údaje, ktoré sa majú konsolidovať; Excel pridá každý odkaz do zoznamu. rozsahov, ktoré tvoria konsolidáciu.
• Keď zahrniete všetky oblasti, stlačte OK, aby Excel vytvoril kombinovanú tabuľku s výsledkami.

Je to veľmi pohodlná metóda, ak potrebujete napríklad získať číselný súhrn viacerých periodických hárkov (mesiace, delegácie, projekty), ktoré majú rovnakú štruktúru.

Konsolidovať podľa kategórie: keď sú dominantné značky, nie pozícia

Druhá metóda konsolidácie funguje najlepšie, keď nie všetky hárky majú riadky a stĺpce v rovnakom poradí , ale zdieľajú rovnaké označenia. V tomto prípade Excel používa hlavičky riadkov a stĺpcov na určenie, čo s čím sčítať.

Tento prístup je ideálny, ak sú vaše tabuľky zoznamy údajov bez medzier, ale s malými rozdielmi v poradí, za predpokladu, že názvy kategórií sú napísané konzistentne . Ak v jednom hárku použijete „Priemer“ a v inom „Priemer“, Excel ich bude považovať za samostatné polia a nezlúči ich.

Použitie konsolidácie podľa kategórie :

• Otvoriť všetky zdrojové hárky ktoré obsahujú údaje, ktoré sa majú zoskupiť.
• Na hárku, kde chcete zobraziť súhrn, znova vyberte ľavá horná bunka výstupného rozsahu.
• Znova prístup Dáta > Konsolidovať a vyberte požadovanú funkciu (súčet, priemer atď.).
• Začiarknite políčka, ktoré označujú, kde sa nachádzajú štítky: Horný riadok, Ľavý stĺpec alebo oboje, v závislosti od vašich nadpisov.
• Pre každý zdrojový hárok vyberte rozsah údajov vrátane riadkov alebo stĺpcov, v ktorých sa nachádzajú popisky, aby Excel dokáže rozpoznať kategórie.
• Po pridaní všetkých rozsahov potvrďte tlačidlom OK, čím sa vygeneruje konsolidovaná tabuľka.

Týmto spôsobom môžete zlúčiť číselné údaje, ktoré zdieľajú kategórie, aj keď nie sú dokonale zarovnané vo všetkých hárkoch, pokiaľ sú názvy stĺpcov a riadkov konzistentné.

Kombinujte viacero excelovských hárkov manuálne priamo v programe.

Ak nechcete ani tak konsolidovať hodnoty, ale mať viacero hárkov, ktoré sú momentálne v samostatných súboroch v jednom zošite , Excel to tiež relatívne uľahčuje vďaka možnostiam presúvania alebo kopírovania hárkov.

Najzákladnejšia metóda spočíva v otvorení príslušných súborov a v použití možnosti formátovania z hárka, ktorý chcete duplikovať, na jeho presunutie alebo kopírovanie . Je to jednoduchý prístup, ktorý nevyžaduje vzorce ani automatizáciu, ideálny pri práci len s niekoľkými hárkami.

Kroky sú, vo všeobecnosti, tieto:

• Otvorte všetky excelové hárky, ktoré chcete zlúčiť do jedného zošita.
• Umiestnite kurzor na záložku hárka, ktorý chcete kopírovať. smerom k ďalšej knihe.
• Prejdite na kartu Domov a v skupine Bunky zadajte možnosť Formát, kde sa príkaz zobrazuje pre presunúť alebo skopírovať hárok.
• V zobrazenom poli si budete môcť vybrať Do ktorej knihy má byť list odoslaný? (jednu už otvorenú alebo novú knihu, ktorá sa bude vytvárať) a na akú pozíciu ju umiestniť.
• Uistite sa, že ste zaškrtli políčko Vytvorte kópiu ak chcete, aby pôvodný hárok zostal v zdrojovom súbore.

Ak sa hárky, ktoré chcete zlúčiť, nachádzajú v tom istom zošite, môžete tiež vybrať viacero kariet naraz pomocou klávesu Ctrl (na výber jednotlivých hárkov) alebo Shift (pre po sebe idúce rozsahy) a zopakovať proces presunutia alebo kopírovania. Týmto spôsobom môžete zlúčiť rôzne hárky z viacerých zošitov do jedného súboru bez straty originálov.

Zlúčenie viacerých hárkov z rôznych súborov pomocou kódu VBA

Keď sú vaše dáta roztrúsené v mnohých súboroch a chcete proces automatizovať, VBA (Visual Basic for Applications) je silný švajčiarsky nôž . Pomocou makra môžete otvoriť priečinok plný excelových zošitov a v priebehu niekoľkých sekúnd skopírovať všetky ich hárky do hlavného súboru.

Nerobme si ilúzie: používanie makier si vyžaduje určitú úroveň ovládania kódu . Nemusíte byť profesionálny programátor, ale musíte vedieť, ako otvoriť editor Visual Basic, vložiť makro a spustiť ho bez obáv.

Typický príklad makra na zlúčenie hárkov z viacerých súborov vykoná nasledovné: otvorí pole pre výber súboru, umožní vám vybrať viacero zošitov, otvorí každý súbor, prejde všetkými jeho hárkami a skopíruje ich na koniec hlavného zošita a po dokončení zatvorí zdrojové zošity.

Pracovný postup je podobný nasledujúcemu:

• Vytvorte novú knihu, ktorá bude slúžiť ako „hlavný“ súbor.
• lis ALT + F11 otvorte editor jazyka Visual Basic.
• Vložte a modul Vytvorte a vložte kód makra, ktorý bude spravovať prechádzanie knihami a kopírovanie hárkov.
• Uložte súbor ako zošit s podporou makier a Spustite makro (F5) od redaktora.
• Keď sa zobrazí prieskumník súborov, vyberte súbory programu Excel, ktoré obsahujú hárky, ktoré sa majú zlúčiť, a potvrďte ich.

Po chvíli uvidíte, že všetky hárky z vybratých súborov sa zobrazili vo vašom hlavnom zošite , usporiadané jeden po druhom. Odtiaľ ich môžete podľa potreby reorganizovať, premenovať alebo vyčistiť.

Ďalšou často používanou možnosťou je skript VBA , ktorý automaticky prechádza všetkými súbormi .xlsx v danom priečinku , otvára ich jeden po druhom a skopíruje ich hárky do hlavného zošita. Toto je fantastický prístup, keď budete operáciu často opakovať a zostavy budete vždy ukladať do rovnakého priečinka.

Zobrazenie údajov z viacerých hárkov v hlavnom hárku pomocou nástroja Konsolidovať

Okrem kopírovania celých hárkov často chcete mať jeden hlavný hárok so súhrnnými údajmi z viacerých hárkov bez nutnosti manuálnej manipulácie s každou záložkou. Tu opäť prichádza na rad konsolidačná funkcia programu Excel.

Cieľom je mať podrobné hárky samostatne (napríklad jeden na mesiac alebo jeden na obchodnú oblasť) a na hlavnom hárku vytvoriť tabuľku, ktorá odráža súčet, priemer alebo počet všetkých z nich . Tento hlavný hárok môže byť základom pre grafy, dashboardy alebo pravidelné správy.

V závislosti od toho, ako sú vaše údaje usporiadané, si môžete zvoliť konsolidáciu podľa pozície alebo podľa kategórie , ako sme videli predtým. Dôležité je, aby zdrojové rozsahy boli definované ako čisté zoznamy (bez prázdnych riadkov alebo stĺpcov medzi nimi) a aby označenia a štruktúry boli konzistentné v rôznych hárkoch.

Tento prístup je obzvlášť užitočný pri spracovaní veľkého objemu informácií, keď potrebujete iba súhrn v hlavnom hárku, nie všetky podrobnosti riadok po riadku . Napriek tomu si môžete vždy ponechať pôvodné hárky s podrobnosťami, ak sa neskôr budete potrebovať ponoriť do hlbšieho skúmania.

Zlúčenie viacerých hárkov bez duplikátov: spojenie a vyčistenie

Ďalšou veľmi častou situáciou je, keď zlúčite niekoľko hárkov s podobnými záznamami (napríklad zoznamy zákazníkov, produkty alebo transakcie) a po zlúčení všetkých záznamov vám zostanú duplicitné riadky . Excel nemá magické tlačidlo, ktoré by zlúčilo hárky a zároveň odstránilo duplikáty, ale na dosiahnutie tohto cieľa môžete prepojiť dva nástroje.

Myšlienka je jasná: najprv skombinujete hárky (pomocou funkcie Kopírovať a Prilepiť, pomocou Power Query, pomocou Coupler.io alebo inou metódou, ktorú uprednostňujete), aby ste mali všetky údaje pohromade v jednej tabuľke; po dokončení pristúpite k vyčisteniu tabuľky pomocou možnosti Odstrániť duplikáty v samotnom Exceli.

Ak to chcete urobiť z pása s nástrojmi :

• Vyberte tabuľku, v ktorej máte zlúčené údaje.
• Prejdite na kartu Dáta a v skupine Nástroje pre údaje kliknite na odstrániť duplikáty.
• V zobrazenom okne začiarknite alebo zrušte začiarknutie. Moje údaje majú hlavičky podľa potreby, aby Excel mohol správne rozlíšiť hlavičky.
• Vyberte, ktoré stĺpce chcete použiť na detekciu duplicitných záznamov; ak vyberiete všetky stĺpceVymažú sa iba riadky, ktoré sa zhodujú vo všetkých poliach.
• Prijať a Excel vám ukáže, koľko duplikátov odstránil a koľko jedinečných hodnôt zostáva.

Vďaka tejto kombinácii zlúčenia a čistenia získate jednu, čistú tabuľku bez duplikátov , ktorá je ideálna pre zostavy, kontingenčné tabuľky alebo export do iných systémov.

Zlúčenie excelových hárkov na základe spoločného stĺpca

V mnohých reálnych scenároch nechcete len skladať záznamy, ale aj prepojiť doplnkové informácie rozložené v rôznych tabuľkách . Napríklad finančná tabuľka so sumami priradenými k ID produktu a tabuľka predaja s podrobnosťami o tom istom produkte; logicky by ste ich chceli skombinovať pomocou tohto spoločného kľúča.

V používateľskom prostredí existujú dva hlavné spôsoby, ako to urobiť: pomocou externého nástroja, ako je Coupler.io , ktorý automatizuje spojenie, alebo pomocou Power Query , ktorý je integrovaný do moderného Excelu a umožňuje aj spojenia medzi tabuľkami podobné databázovým.

Metóda používajúca Coupler.io na spojenie hárkov stĺpcom

Coupler.io je cloudové riešenie určené na automatizáciu prehľadov a presun údajov medzi rôznymi zdrojmi . Medzi jeho funkcie patrí možnosť importovať viacero excelovských tabuliek, kombinovať ich a načítať výsledok do inej tabuľky alebo dokonca do analytických nástrojov, ako sú BigQuery alebo Looker Studio.

Ak chcete zlúčiť napríklad hárok s názvom „Finančná tabuľka“ a ďalší s názvom „Tabuľka predaja“ na základe stĺpca ID produktu , typický postup by bol tento:

• Na Coupler.io si vyberiete oboje pôvod, ako aj cieľ Microsoft Excel v pôvodnej forme a pokračovať.
• Pripojíte si svoj účet Microsoft, vyberiete súbor programu Excel a ako prvý zdroj vyberiete hárok. Finančná tabuľka.
• Využívaš možnosť pripojiť iný zdroj pridať druhý hárok, v tomto prípade Predajný stôl, z toho istého súboru alebo z iného.
• V kroku konfigurácie kombinácie si vyberiete typ operácie: pripojiť (na stohovanie tabuliek s rovnakou štruktúrou) alebo pripojiť (na porovnanie údajov pomocou stĺpca s jedinečným identifikátorom).
• Vyberiete pripojiť a ako kľúčový stĺpec v oboch hárkoch definujete „ID produktu“.
• Skontrolujte ukážku spojených údajov a upravte stĺpce, ktoré chcete zachovať.

Ďalej v cieľovej sekcii vyberiete súbor a excelovský hárok, kam sa zlúčená tabuľka uloží. Môžete naplánovať automatické aktualizácie , aby Coupler.io pravidelne obnovoval údaje bez toho, aby ste museli celý proces opakovať manuálne.

Spojenie hárkov pomocou Power Query na základe spoločného stĺpca

Ak uprednostňujete zostať v ekosystéme Excelu, Power Query je perfektný nástroj na kombinovanie tabuliek na základe zhodných stĺpcov. Je to bezplatný doplnok v Exceli 2010 a 2013 a vstavaná funkcia od Excelu 2016.

Predpokladajme znova, že máte dve tabuľky: jednu v hárku „Finančná tabuľka“ a druhú v hárku „Predajná tabuľka“, obe so stĺpcom „ID produktu“. Cieľom je vytvoriť jednu tabuľku, ktorá kombinuje údaje z oboch na základe tohto identifikátora.

Proces možno rozdeliť do niekoľkých krokov:

Krok 1: Vytvorenie pripojení Power Query

• V zošite programu Excel vyhľadajte prvú tabuľku (napríklad v bunke na hárku „Tabuľka predaja“).
• Na karte Údaje v sekcii Získajte a transformujte sa, vyberte možnosť Z tabuľky alebo rozsahu načítať túto tabuľku do Power Query.
• V otvorenom okne začiarknite alebo zrušte začiarknutie Moja tabuľka má hlavičky podľa potreby; vo väčšine prípadov toto políčko necháte zaškrtnuté.
• Otvorí sa editor Power Query. Ak tabuľku ešte nechcete načítať do zošita, vyberte Zatvoriť a načítať… a začiarknite možnosť Stačí vytvoriť spojenie.
• Zopakujte postup s druhým hárkom (napríklad „Finančná tabuľka“) a vytvorte zodpovedajúce prepojenie. V paneli dopytov knihy teraz uvidíte obe prepojenia..

Krok 2: Zlúčenie dotazov so spoločným stĺpcom

• V Exceli vyberte ľubovoľný z dotazov a prejdite na položku Údaje > Nový dopyt (alebo priamo z panela dotazov) > Kombinovať dotazy > kombinovať.
• V okne zlúčenia vyberte ako prvú tabuľku „Finančná tabuľka“ a ako druhú „Predajná tabuľka“.
• V každej tabuľke, Kliknite na stĺpec „ID produktu“ označiť ho ako zodpovedajúce pole; uvidíte, že sa zvýrazní, keď je výber úspešný.
• Typ spojenia ponechajte ako predvolený alebo ho upravte podľa svojich potrieb (napr. vnútorné spojenie, ľavé spojenie atď.).
• Prijať, aby Power Query vygeneroval nový dotaz s ďalším stĺpcom, ktorý pre každý riadok obsahuje súvisiacu „Tabuľku predaja“.

Krok 3: Rozbaľte stĺpce kombinovanej tabuľky

• V editore Power Query sa zobrazí stĺpec s názvom druhej tabuľky, ktorá v každom riadku obsahuje slovo „Tabuľka“.
• Kliknite na ikonu s dva šípy vedľa názvu daného stĺpca ho rozbaľte.
• V kontextovom okne vyberte, ktoré stĺpce z „Tabuľky predajov“ chcete pridať do kombinácie (napríklad „Položky“ a „Suma ($)“).
• Ak nechcete, aby sa pôvodný názov tabuľky zobrazoval ako predpona v poliach, Zrušte začiarknutie políčka, ak chcete ako predponu použiť pôvodný názov stĺpca. a prijať.

Krok 4: Načítanie zlúčenej tabuľky do Excelu

• Po zlúčení a vyčistení tabuľky v editore Power Query prejdite na Zatvoriť a načítať….
• Vyberte, či chcete načítať údaje ako tabuľka v novom hárku alebo na existujúcom hárku, v závislosti od toho, ako chcete svoju knihu usporiadať.

Po dokončení budete mať v Exceli tabuľku s údajmi z oboch hárkov prepojených spoločným stĺpcom a so všetkými poľami, ktoré ste vybrali v kroku rozšírenia.

Použitie dotazov a pripojení na spojenie hárkov z toho istého súboru

Mnoho používateľov takmer náhodou zistí, že funkcia Dotazy a pripojenia v Exceli im umožňuje jednoducho kombinovať viacero hárkov v rámci toho istého súboru, za predpokladu, že zdieľajú spoločnú štruktúru. Táto funkcia sa spolieha na Power Query, ale rozhranie je celkom užívateľsky prívetivé.

Ak máte napríklad tri hárky s rovnakými stĺpcami (Dátum – Položka – Hodnota) a chcete zobraziť všetky záznamy v jednej tabuľke zoradené podľa dátumu , tento prístup je veľmi praktický a flexibilný.

Typické kroky by boli:

• Na karte Údaje vyberte možnosť Nový dopyt (alebo „Získať údaje“ v závislosti od vašej verzie) > Zo súboru > Z knihy.
• Vyberte súbor programu Excel, ktorý obsahuje hárky, ktoré chcete zlúčiť; Môže to byť dokonca aj kniha, na ktorej pracujete..
• V prehliadači začiarknite políčko Vyberte viac položiek a označte všetky hárky, ktoré chcete zahrnúť.
• Kliknite na Transformujte dáta otvorte tabuľky v editore Power Query.
• V každom dotaze odstráňte stĺpce, ktoré nepotrebujete, a Uistite sa, že všetky tabuľky majú presne rovnakú štruktúru (rovnaké stĺpce, rovnaký dátový typ).
• Použite možnosti ponuky Domov v Power Query na pridajte otázkytakže nakoniec máte jeden dotaz, ktorý obsahuje riadky zo všetkých hárkov.
• Keď je finálny dopyt hotový, stlačte Zatvorte a nabite aby sa do knihy pridala ako tabuľka na novom hárku.

Odtiaľ môžete triediť podľa dátumu, použiť farby podľa zdroja údajov alebo dokonca pridať stĺpec, ktorý identifikuje, z ktorého hárka každý záznam pochádza , čo je veľmi užitočné pre pokročilejšiu analýzu.