Kombinování dat z více listů

Pokud pracujete s informacemi rozloženými na několika listech – například s rozpočty z různých oblastí nebo sestavami vytvořenými více přispěvateli – možná budete chtít tato data spojit na jednom místě. Excel nabízí několik způsobů, jak to udělat v závislosti na tom, jestli chcete shrnout hodnoty nebo jednoduše zkombinovat seznamy.

Než začnete

Ujistěte se, že jsou zdrojová data dobře strukturovaná.

Použijte formát seznamu (žádné zcela prázdné řádky ani sloupce).
Zachování konzistentních popisků (záhlaví sloupců) napříč listy
Pokud vaše verze Excelu nemá funkci Data > Konsolidovat, možná používáte Excel pro web nebo platformu, která tuto funkci nepodporuje. V takovém případě si přečtěte část "Možnost 2: Kombinování nebo přidání dat namísto shrnutí".

Možnost 1: Shrnutí dat pomocí funkce Konsolidovat

Pokud chcete vypočítat součty, průměry, počty nebo jiné souhrnné výsledky na listech, použijte funkci Konsolidovat . Můžete konsolidovat podle pozice (stejné rozložení) nebo podle kategorie (odpovídající popisky).

Dialogové okno Sloučit zobrazující několik odkazů, které se mají sloučit pomocí funkce SUMA.

Sloučení podle umístění:

Tuto možnost použijte, když každý list používá stejné rozložení buněk.

Otevřete zdrojové listy a potvrďte, že se data na každém listu zobrazí na stejném místě .
Přejděte na list, na kterém chcete zkombinovat výsledky.
Vyberte levou horní buňku oblasti, ve které se mají zobrazit sloučená data.
- Ujistěte se, že je prostor pro rozšíření dat směrem dolů a doprava.
Vyberte Data > Konsolidovat.
Zvolte funkci (například Součet, Průměr nebo Počet).
V každém zdrojovém listu:
- Vyberte oblast dat.
- Vyberte Přidat , abyste ho zahrnuli do všech odkazů.
Výběrem OK vygenerujte konsolidovanou sestavu.

Sloučení podle kategorie:

Tuto možnost použijte, když listy sdílejí stejné popisky, i když data nejsou umístěná stejně. Všimněte si, že pokud jeden list používá "Průměr", zatímco jiný používá "Průměr", budete nejdřív muset štítky standardizovat, aby je Excel mohl správně shodovat.

Ověřte, že každý list používá odpovídající popisky v horním řádku nebo levém sloupci.
V cílovém listu vyberte levou horní buňku oblasti, kde se mají zobrazit sloučená data.
- Ujistěte se, že je prostor pro rozšíření dat směrem dolů a doprava.
Přejděte na Data > Konsolidovat.
Zvolte funkci (například Součet, Průměr nebo Počet).
Zaškrtněte políčka v části Použít popisky v (Horní řádek, Levý sloupec nebo obojí).
V každém zdrojovém listu:
1. Vyberte oblast dat.
2. Vyberte Přidat , abyste ho zahrnuli do všech odkazů.
Výběrem OK vygenerujte konsolidovanou sestavu.

Pokud se popisek zobrazí na jednom listu, ale ne v jiném, Excel ho i tak zahrne. Ve výsledku se vytvoří nový řádek nebo sloupec.

Možnost 2: Kombinování nebo přidání dat místo jejich souhrnu

Pokud potřebujete sloučit nebo skládat řádky z více listů, ne počítat součty, budete potřebovat jiný přístup.

Kopírování a vkládání

Jedná se o rychlou ruční možnost sloučení dat. Funguje nejlépe, když potřebujete sloučit jenom několik listů.

Vytvořte nový list.
Zkopírujte celý seznam z prvního listu a vložte ho.
Postup opakujte pro ostatní listy a vložte je přímo pod existující data.
V případě potřeby odeberte duplicitní hlavičky.

Použití vzorce VSTACK ke zásobníku dat

Pokud mají listy stejnou strukturu sloupců, můžete je dynamicky naskládat pomocí funkce VSTACK. Následující příklad by zkombinoval data ze tří listů.

=VSTACK(Sheet1!A1:D50, Sheet2!A1:D50, Sheet3!A1:D50)

Tím se vytvoří jeden kombinovaný seznam, který se aktualizuje při změně dat ve zdrojových listech.

Použití Power Query

Power Query umožňuje automaticky importovat a kombinovat data z více tabulek nebo listů, a to i napříč sešity. To je nejvhodnější pro velké datové sady a průběžné slučování.

Vyberte každou oblast dat a stisknutím Ctrl+T ji převeďte na tabulku.
Přejděte do části Data > Získat> dat z jiných zdrojů > prázdný dotaz.
K zobrazení tabulek použijte Excel.CurrentWorkbook() na řádku vzorců.
Rozbalte a zkombinujte je pomocí ikony s dvojitou šipkou.
Pokud chcete vytvořit kombinovaný list , zvolte Zavřít & Načíst .

Tento přístup vytvoří dynamickou kombinovanou datovou sadu, kterou je možné aktualizovat při každé změně dat.

Řešení potíží a tipy

Na základě zpětné vazby uživatelů jsou zde nejčastější potíže.

Nelze najít "konsolidovat"

Možná používáte Excel pro web nebo verzi, která ho nepodporuje. Místo toho použijte Power Query nebo vzorce.

Dialogové okno Konsolidovat neumožňuje výběr oblastí.

Ujistěte se, že dialogové okno zůstane aktivní. Pokud blokuje kliknutí do jiných oken, zkuste změnit velikost nebo přesunout.

Výsledky konsolidace vypadají špatně

Zkontrolujte, že:

Popisky se přesně shodují (např. "Průměr", ne "Průměr").
Neexistují žádné prázdné řádky nebo sloupce struktury seznamu způsobujících porušení.
Zvolili jste správnou funkci (Sum vs. Average).

Data se zobrazují v nekonzistentních řádcích nebo sloupcích.

Pokud listy nejsou zarovnané, použijte možnost Sloučit podle kategorie místo pozice.

Chcete přidat data, ne je shrnout.

Místo toho použijte VSTACK nebo Power Query. Jsou vhodnější pro sloučení.