Jak sloučit více listů aplikace Excel do jednoho: všechny možnosti

Pokud často pracujete s mnoha tabulkami, pravděpodobně jste se přistihli, že kopírujete a vkládáte informace roztroušené na více záložkách, jako by zítřek nepřišel. Sloučení více listů aplikace Excel do jednoho Nejenže šetří čas: také snižuje chyby, zjednodušuje reporting a umožňuje mnohem snadnější analýzu dat.

Dobrou zprávou je, že se neomezujete pouze na typické kopírování a vkládání. Excel a některé externí nástroje nabízejí několik metod Chcete-li kombinovat listy ze stejného souboru, z několika různých sešitů, s duplikáty nebo bez nich, a dokonce i na základě společných sloupců, jako je například identifikátor produktu. Podíváme se krok za krokem na všechny praktické alternativy a na to, co je v každém případě nejlepší použít.

Jaké máte možnosti pro sloučení více listů aplikace Excel?

Než se ponoříme do jednotlivých kroků, je užitečné mít jasný přehled. Existují velmi odlišné přístupy ke sjednocení tabulekOd interních funkcí Excelu (Consolidate, Power Query) až po makra VBA nebo externí webové služby a automatizační řešení, jako je Coupler.io nebo ASPOSE.

Nejlepší metoda bude záviset na tom, zda mají vaše tabulky stejnou strukturu, zda jsou ve stejném souboru nebo v různých složkách a jak dobře umíte pracovat s Excelem. Není totéž jako shromažďovat číselné součty pro manažerskou zprávu. propojit prodejní a finanční tabulky pomocí společného sloupce a provést podrobnou analýzu.

Sloučení dat z více listů pomocí funkce Consolidate v Excelu

Jedním z nejklasičtějších a nejméně známých způsobů shromažďování informací je použití příkazu Konsolidovat v Excelu. Konsolidace umožňuje shrnout číselná data rozptýlená na více listech nebo sešitech. a seskupte je na „hlavním“ listu se součty, průměry, počty atd.

Představte si, že si vedete záznamy o výdajích podle pobočky: jeden list na kancelář, všechny s podobnou tabulkou výdajů. Díky konsolidaci si můžete vytvořit firemní tabulku jen několika kliknutími. který shromažďuje data ze všech míst a vypočítává součty, průměry, zásoby nebo jakoukoli jinou metriku, kterou potřebujete.

Excel nabízí dva režimy konsolidace: podle pozice y podle kategorieVolba závisí na tom, jak jsou vaše rozsahy uspořádány na různých listech: zda jsou buňky zarovnány ke stejným souřadnicím, nebo zda se spoléháte na popisky řádků a sloupců.

Konsolidovat podle pozice: když jsou všechny listy „rozloženy“ stejně

Konsolidace pozic funguje velmi dobře, když Všechny listy mají stejný design tabulkystejné sloupce ve stejném pořadí, stejné řádky ve stejné sekvenci a žádné mezery v seznamu dat.

Abyste zajistili hladký chod tohoto systému, zkontrolujte každý zdrojový list a Ujistěte se, že datové oblasti zabírají přesně stejné buňky. Ve všech z nich, bez prázdných řádků nebo sloupců mezi nimi. Je to jako překrývání několika průhledností: Excel sečte nebo zpracuje data, která spadají do stejné „buňky“.

El obecný proces je toto:

• Otevřít všechny zdrojové listy nebo knihy s daty, která chcete přidat nebo shrnout, a zkontrolujte, zda je tabulka v každém z nich zarovnaná.
• V sešitu, kde chcete zobrazit součet, přejděte na cílový list a vyberte buňku vlevo nahoře kam chceš, abych šel/šla bota konsolidovaná tabulka.
• Na kartě data zadejte příslušný nástroj (ve španělských verzích je to obvykle Data > Konsolidovat).
• V okně Konsolidace vyberte v poli Función Pokud chcete sčítat, průměrovat, počítat atd.
• Pro každý zdrojový list vyberte rozsah obsahující data, která mají být konsolidována; Excel přidá každý odkaz do seznamu. rozsahů, které tvoří konsolidaci.
• Jakmile zahrnete všechny oblasti, stiskněte OK a Excel vytvoří sloučenou tabulku s výsledky.

Je to velmi pohodlná metoda, pokud potřebujete např. získat číselný souhrn několika periodických listů (měsíce, delegace, projekty), které sdílejí identickou strukturu.

Konsolidovat podle kategorie: když vládnou tagy, nikoli pozice

Druhý režim konsolidace funguje nejlépe, když Ne všechny listy mají řádky a sloupce ve stejném pořadí.Ale sdílejí stejné popisky. Zde Excel používá záhlaví řádků a sloupců k určení, co k čemu přidat.

Tento přístup je ideální, pokud jsou vaše tabulky seznamy dat bez prázdných míst, ale s malými rozdíly v pořadí, za předpokladu, že Názvy kategorií jsou psány konzistentněPokud do jednoho listu vložíte pole „Prom.“ a do druhého „Average“, Excel je bude považovat za samostatná pole a nesloučí je.

Chcete-li použít Konsolidace podle kategorie:

• Otevřít všechny zdrojové listy které obsahují data, která mají být seskupena.
• Na listu, kde chcete zobrazit souhrn, znovu vyberte levá horní buňka výstupního rozsahu.
• Přístup znovu Data > Konsolidovat a vyberte požadovanou funkci (součet, průměr atd.).
• Zaškrtněte políčka, která označují, kde se štítky nacházejí: horní řádek, Levý sloupec nebo obojí, v závislosti na vašich nadpisech.
• Pro každý zdrojový list vyberte datový rozsah včetně řádků nebo sloupců, kde se nacházejí popisky, aby Excel dokáže rozpoznat kategorie.
• Jakmile jsou všechny rozsahy přidány, potvrďte tlačítkem OK pro generování konsolidované tabulky.

Tímto způsobem můžete sloučit číselná data, která sdílejí kategorie, i když nejsou dokonale zarovnaná na všech listech, pokud jsou názvy sloupců a řádků konzistentní.

Ruční kombinace více listů aplikace Excel přímo v programu.

Pokud nechcete ani tak upevnit hodnoty, ale mít v jedné knize několik listů, které jsou aktuálně v samostatných souborechExcel to také relativně usnadňuje díky možnostem přesouvání nebo kopírování listů.

Nejzákladnější metoda spočívá v otevření příslušných souborů a z listu, který chcete duplikovat, Použijte možnost formátování k přesunutí nebo kopírováníJe to jednoduchý přístup, který nevyžaduje vzorce ani automatizaci, perfektní, když je k dispozici jen několik listů.

KrokyV širším smyslu jsou to:

• Otevřete všechny excelové listy, které chcete sloučit do jednoho sešitu.
• Umístěte kurzor na záložku listu, který chcete kopírovat. směrem k další knize.
• Přejděte na kartu Domů a ve skupině Buňky zadejte možnost Formát, kde se příkaz objeví pro přesunout nebo kopírovat list.
• V zobrazeném poli si budete moci vybrat Do které knihy by měl být list odeslán? (již otevřenou nebo novou knihu, která bude vytvořena) a na jakou pozici ji umístit.
• Nezapomeňte zaškrtnout políčko Vytvořte kopii pokud chcete, aby původní list zůstal ve zdrojovém souboru.

Pokud jsou stránky, které chcete spojit, již všechny ve stejné knize, můžete také vybrat více karet najednou Pomocí klávesy Ctrl (pro výběr jednotlivých listů) nebo Shift (pro po sobě jdoucí oblasti) a opakováním procesu přesunutí nebo kopírování můžete sloučit různé listy z více sešitů do jednoho souboru, aniž byste ztratili originály.

Sloučení více listů z různých souborů pomocí kódu VBA

Pokud jsou data rozptýlena v mnoha souborech a chcete proces automatizovat, VBA (Visual Basic for Applications) je velmi výkonný švýcarský nůž.Pomocí makra můžete otevřít složku plnou sešitů aplikace Excel a během několika sekund zkopírovat všechny jejich listy do hlavního souboru.

Ale nedělejme si iluze: Používání maker vyžaduje určitou úroveň znalosti kódu.Nemusíte být profesionální programátor, ale musíte vědět, jak otevřít editor Visual Basic, vložit makro a bez obav ho spustit.

Typický příklad makra pro sloučení listů z více souborů provádí následující: otevře pole pro výběr souborů, umožní vám vybrat více sešitů, Otevřete každý soubor, projděte si všechny jeho stránky a zkopírujte je. na konci hlavní knihy a po dokončení zavřete zdrojové knihy.

Pracovní postup Je to podobné jako následující:

• Vytvořte novou knihu, která bude sloužit jako „hlavní“ soubor.
• lis ALT + F11 otevřete Editor jazyka Visual Basic.
• Vložte a Modul Vytvořte a vložte kód makra, který bude zpracovávat procházení knih a kopírování listů.
• Uložte soubor jako sešit s podporou maker a Spusťte makro (F5) od editora.
• Po zobrazení průzkumníka souborů vyberte soubory aplikace Excel, které obsahují listy, které chcete sloučit, a potvrďte výběr.

Po chvíli to uvidíte ve své hlavní knize Zobrazily se všechny listy z vybraných souborů., umístěné jeden po druhém. Odtud můžete uspořádat, přejmenovat nebo uklidit, co potřebujete.

Další široce používanou variantou je skript VBA, která Automaticky prohledá všechny soubory .xlsx v dané složceOtevře je jeden po druhém a zkopíruje jejich listy do hlavního sešitu. To je skvělý přístup, pokud budete operaci často opakovat a sestavy vždy ukládat do stejné složky.

Zobrazení dat z více listů v hlavním listu pomocí nástroje Konsolidace

Kromě kopírování celých stránek často hledáte mít jeden hlavní list se shrnutými daty z mnoha listůaniž byste museli ručně manipulovat s každou záložkou. A právě zde se opět uplatňuje konsolidační funkce Excelu.

Myšlenka je taková, že máte podrobné listy odděleně (například jeden na měsíc nebo jeden na oblast podnikání) a vytvořit tabulku na hlavním listu, která odráží součet, průměr nebo počet všech z nichTento hlavní list může být základem pro grafy, dashboardy nebo pravidelné reporty.

V závislosti na tom, jak jsou vaše data uspořádána, si můžete zvolit konsolidaci. podle pozice proč kategoriejak jsme již viděli. Důležité je, aby zdrojové rozsahy byly definovány jako čisté seznamy (bez prázdných řádků nebo sloupců mezi nimi) a aby popisky a struktury jsou konzistentní mezi různými listy.

Tento přístup je obzvláště užitečný, když zpracováváte velké objemy informací a potřebujete pouze Hlavní list by měl obsahovat shrnutí, nikoli všechny podrobnosti řádek po řádku.Vždy si však můžete ponechat originální listy s podrobnostmi pro případ, že byste se do nich později potřebovali hlouběji ponořit.

Sloučení více listů bez duplikátů: sloučení a čištění

Další velmi častou situací je, když sloučíte několik listů s podobnými záznamy (například seznamy zákazníků, produkty nebo transakce) a Vidíte opakující se řádky Po sestavení všech prvků. Excel nemá kouzelné tlačítko, které by spojovalo listy a zároveň odstraňovalo duplikáty, ale můžete propojit dva nástroje dohromady, abyste toho dosáhli.

Myšlenka je jasná: Nejprve smíchejte listy (pomocí kopírování a vkládání, Power Query, Coupler.io nebo vámi preferované metody) mít všechna data pohromadě v jedné tabulce; jakmile to uděláte, přejdete k Vyčistěte tabulku pomocí možnosti Odebrat duplikáty ze samotného Excelu.

Abyste to udělali z Možnosti:

• Vyberte tabulku, ve které máte sloučená data.
• Přejděte na kartu Data a ve skupině Datové nástroje klikněte na odstranit duplikáty.
• V zobrazeném okně zaškrtněte nebo zrušte zaškrtnutí. Moje data mají záhlaví podle potřeby, aby Excel mohl záhlaví správně rozlišit.
• Vyberte, které sloupce chcete použít k detekci duplicitních záznamů; pokud vyberete všechny sloupceSmazány budou pouze řádky, které se shodují ve všech polích.
• Přijmout a Excel vám ukáže, kolik duplikátů bylo odstraněno a kolik jedinečných hodnot zbývá.

S touto kombinací fúze a čištění, Nakonec získáte jednu, propracovanou tabulku bez opakování., ideální pro reporty, kontingenční tabulky nebo export do jiných systémů.

Sloučení excelových listů na základě společného sloupce

V mnoha reálných situacích nechcete jen skládat rekordy, ale propojit doplňkové informace rozložené v různých tabulkáchNapříklad finanční tabulka s částkami přidruženými k ID produktu a další prodejní tabulka s podrobnostmi o stejném produktu; logické je chtít je sloučit pomocí tohoto společného klíče.

V uživatelském prostředí existují dva hlavní způsoby, jak toho dosáhnout: pomocí externího nástroje, jako je Coupler.io, který automatizuje spojování, nebo pomocí Power Querykterý je integrován do moderního Excelu a také umožňuje spojení mezi tabulkami podobné databázovým.

Metoda využívající Coupler.io pro spojení listů pomocí sloupce

Coupler.io je cloudové řešení určené pro automatizovat reporty a přesouvat data mezi různými zdrojiMezi jeho funkce patří možnost importovat několik excelových listů, kombinovat je a načíst výsledek do jiného listu nebo dokonce do analytických nástrojů, jako je BigQuery nebo Looker Studio.

Pokud chcete sloučit například list s názvem „Finanční tabulka“ a další list s názvem „Prodejní tabulka“ na základě sloupce ID produktuTypický tok by byl tento:

• Na Coupler.io si vyberete obojí původ i cíl Microsoft Excel v původní podobě a pokračovat.
• Připojíte svůj účet Microsoft, vyberete soubor aplikace Excel a jako první zdroj zvolíte list. Finanční tabulka.
• Využíváte možnost připojit další zdroj přidat druhý list, v tomto případě Prodejní tabulka, ze stejného souboru nebo z jiného.
• V kroku konfigurace kombinace zvolíte typ operace: Připojit (pro stohování tabulek se stejnou strukturou) nebo Připojte (pro porovnání dat pomocí sloupce s jedinečným identifikátorem).
• Vy vyberete Připojte a v obou listech definujete jako klíčový sloupec „ID produktu“.
• Prohlédnete si náhled spojených dat a upravíte sloupce, které chcete zachovat.

Dále v cílové sekci vyberete soubor aplikace Excel a list, kam bude sloučená tabulka uložena. Můžete naplánovat automatické aktualizace takže Coupler.io pravidelně aktualizuje data, aniž byste museli celý proces opakovat ručně.

Spojování listů pomocí Power Query na základě společného sloupce

Pokud dáváte přednost zůstat v ekosystému Excelu, Power Query je perfektní nástroj pro kombinování tabulek na základě odpovídajících sloupců. Jedná se o bezplatný doplněk v Excelu 2010 a 2013 a vestavěnou funkci počínaje Excelem 2016.

Předpokládejme znovu, že máte dvě tabulky: jednu v listu „Finanční tabulka“ a druhou v listu „Prodejní tabulka“, obě se sloupcem „ID produktu“. Cílem je vytvořit jednu tabulku, která kombinuje data z obou na základě daného identifikátoru.

Proces Lze to rozdělit do několika kroků:

Krok 1: Vytvoření připojení Power Query

• V sešitu aplikace Excel vyhledejte první tabulku (například v buňce na listu „Tabulka prodeje“).
• Na kartě Data v části Získejte a transformujte se, vyberte možnost Z tabulky nebo rozsahu načíst tuto tabulku do Power Query.
• V otevřeném poli zaškrtněte nebo zrušte zaškrtnutí Moje tabulka má záhlaví podle potřeby; ve většině případů toto políčko necháte zaškrtnuté.
• Otevře se editor Power Query. Pokud tabulku zatím nechcete načíst do sešitu, vyberte Zavřít a načíst… a zaškrtněte možnost Stačí vytvořit spojení.
• Opakujte postup s druhým listem (například „Finanční tabulka“) a vytvořte odpovídající propojení. V panelu dotazů knihy nyní uvidíte obě propojení..

Krok 2: Sloučení dotazů se společným sloupcem

• V Excelu vyberte libovolný z dotazů a přejděte do nabídky Data > Nový dotaz (nebo přímo z panelu dotazů) > Sloučit dotazy > Kombinovat.
• V okně sloučení vyberte jako první tabulku „Finanční tabulka“ a jako druhou „Prodejní tabulka“.
• V každé tabulce, Klikněte na sloupec „ID produktu“ označíte ho jako odpovídající pole; uvidíte, že se po úspěšném výběru zvýrazní.
• Typ spojení ponechte jako výchozí nebo jej upravte podle svých potřeb (např. vnitřní spojení, levé spojení atd.).
• Přijmout, aby Power Query vygeneroval nový dotaz s dalším sloupcem, který pro každý řádek obsahuje související „tabulku prodeje“.

Krok 3: Rozbalte sloupce kombinované tabulky

• V editoru Power Query se zobrazí sloupec s názvem druhé tabulky, která v každém řádku obsahuje slovo „Tabulka“.
• Klikněte na ikonu s dva šípy vedle názvu daného sloupce jej rozbalíte.
• V rozbalovacím okně vyberte, které sloupce z „Tabulky prodejů“ chcete přidat do kombinace (například „Položky“ a „Částka ($)“).
• Pokud nechcete, aby se původní název tabulky zobrazoval jako předpona v polích, Zrušte zaškrtnutí políčka, chcete-li jako předponu použít původní název sloupce. a přijmout.

Krok 4: Načtěte sloučenou tabulku do Excelu

• Po zkombinování a vyčištění tabulky v editoru Power Query přejděte na Zavřít a načíst….
• Vyberte, zda chcete data načíst jako tabulka v novém listu nebo na existujícím listu, v závislosti na tom, jak chcete knihu uspořádat.

Po dokončení budete mít v Excelu připravenou tabulku s data z obou listů propojených společným sloupcemse všemi poli, která jste vybrali v kroku rozšíření.

Použití dotazů a připojení ke spojení listů ze stejného souboru

Mnoho uživatelů to zjistí téměř náhodou Funkce Dotazy a připojení v Excelu umožňuje snadno kombinovat více listů. ze stejného souboru, pokud sdílejí stejnou strukturu. Tato funkce se spoléhá na Power Query, ale rozhraní je docela uživatelsky přívětivé.

Pokud například máte tři listy se stejnými sloupci (Datum – Položka – Hodnota) a chcete Zobrazit všechny záznamy v jedné tabulce seřazené podle dataTento přístup je velmi praktický a flexibilní.

Typické kroky bych:

• Na kartě Data vyberte Nový dotaz (nebo „Získat data“ v závislosti na vaší verzi) > Ze souboru > Z knihy.
• Vyberte soubor aplikace Excel, který obsahuje listy, které chcete sloučit; Může to být dokonce i kniha, na které pracujete..
• V prohlížeči zaškrtněte políčko Vyberte více položek a označte všechny listy, které chcete zahrnout.
• Klikněte na Transformace dat otevřete tabulky v editoru Power Query.
• V každém dotazu odeberte nepotřebné sloupce a Ujistěte se, že všechny tabulky mají přesně stejnou strukturu (stejné sloupce, stejný datový typ).
• Pomocí možností nabídky Domů v Power Query můžete přidat dotazytakže nakonec máte jeden dotaz, který obsahuje řádky ze všech listů.
• Jakmile je finální dotaz hotový, stiskněte Zavřít a načíst aby se do knihy přidala jako tabulka na novém listu.

Odtud můžete řadit podle data, používat barvy podle zdroje dat nebo dokonce Přidejte sloupec, který identifikuje, ze kterého listu jednotlivé záznamy pocházejí.což je velmi užitečné pro pokročilejší analýzu.