Sloučení dat z více listů

Pokud chcete shrnout a vytvořit sestavu výsledků ze samostatných listů, můžete data z každého listu sloučit do hlavního listu. Listy mohou být ve stejném sešitu jako hlavní list nebo v jiných sešitech. Když konsolidujete data, sestavíte je tak, abyste je mohli podle potřeby snadněji aktualizovat a agregovat.

Pokud máte například list s výdaji pro každou z vašich regionálních poboček, můžete tato čísla sloučit do hlavního listu s celopodnikovými výdaji. Tento hlavní list může obsahovat také celkový a průměrný objem prodeje, současné stavy zásob a nejprodávanější produkty pro celý podnik.

Tip: Pokud data často konsolidujete, může pomoct vytvořit nové listy ze šablony listu, která používá konzistentní rozložení. Další informace o šablonách najdete v tématu Vytvoření šablony. Je to také ideální příležitost využít v šabloně excelové tabulky.

Existují dva způsoby, jak konsolidovat data, a to buď podle pozice nebo kategorie.

Konsolidace podle pozice: Data ve zdrojových oblastech mají stejné pořadí a používají stejné popisky. Tuto metodu můžete použít ke sloučení dat z řady listů, třeba z rozpočtových listů oddělení vytvořených ze stejné šablony.

Sloučení podle kategorie: Když data ve zdrojových oblastech nejsou uspořádána ve stejném pořadí, ale mají stejné popisky. Tímto způsobem můžete sloučit data z řady listů, které mají různé rozložení, ale stejné popisky dat.

Sloučení dat podle kategorie je podobné, jako když vytváříte kontingenční tabulku. U kontingenční tabulky ale můžete snadno změnit uspořádání kategorií. Pokud potřebujete flexibilnější sloučení podle kategorií, zvažte vytvoření kontingenční tabulky .

Poznámka: Příklady v tomto článku byly vytvořeny v Excelu 2016. I když se zobrazení může lišit, pokud používáte jinou verzi Excelu, postup je stejný.

Pokud chcete sloučit několik listů do hlavního listu, postupujte takto:

Pokud jste to ještě neudělali, nastavte data v jednotlivých základních listech následujícím způsobem:
- Ujistěte se, že každá oblast dat je ve formátu seznamu. Každý sloupec musí mít v prvním řádku popisek (záhlaví) a musí obsahovat podobná data. V seznamu nesmí být žádné prázdné řádky ani sloupce.
- Každou oblast umístěte na samostatný list, ale do hlavního listu, kam plánujete data sloučit, nic nezadávejte. Excel to udělá za vás.
- Ujistěte se, že každá oblast má stejné rozložení.
Ve společném listu klikněte na levou horní buňku oblasti, ve které se mají zobrazit sloučená data.

Poznámka: Chcete-li se vyhnout přepsání existujících dat v hlavním listu, ujistěte se, že pro sloučená data ponecháte dostatek buněk vpravo a pod touto buňkou.
Klikněte na Data>Konsolidovat (ve skupině Datové nástroje ).
V rozevíracím seznamu Funkce klikněte na příslušnou možnost Souhrnná funkce, kterou má aplikace Excel použít ke sloučení dat. Výchozí funkce je SOUČET.

Tady je příklad, ve kterém byly vybrány tři oblasti listů:
Vyberte data.

Potom v poli Odkaz kliknutím na tlačítko Sbalit zmenšete panel a vyberte data v listu.

Klikněte na list obsahující data, která chcete sloučit, a data vyberte. Potom se kliknutím na tlačítko Rozbalit dialog na pravé straně vraťte do dialogu Sloučit .Pokud je list obsahující data, která potřebujete sloučit, v jiném sešitu, klikněte na Tlačítko Procházet a vyhledejte tento sešit. Po vyhledání a kliknutí na OK excel zadá cestu k souboru do pole Odkaz a připojí k této cestě vykřičník. Pak můžete pokračovat ve výběru dalších dat.

Tady je příklad, ve kterém byly vybrány tři oblasti listů:
V místním okně Konsolidovat klikněte na Přidat. Tento postup opakujte, pokud chcete přidat všechny oblasti, které konsolidujete.
Automatické nebo ruční aktualizace: Pokud chcete, aby Excel aktualizoval konsolidační tabulku automaticky, když se změní zdrojová data, jednoduše zaškrtněte políčko Vytvořit propojení na zdrojová data . Pokud toto políčko zůstane nezaškrtnuté, můžete sloučení aktualizovat ručně.
Poznámky:
- Nemůžete vytvořit propojení, pokud se zdrojová a cílová oblast nachází na stejném listu.
- Pokud potřebujete změnit rozsah oblasti nebo oblast nahradit, klikněte na oblast v místní nabídce Sloučit a aktualizujte ji pomocí výše uvedených kroků. Tím se vytvoří nový odkaz na oblast, takže před opětovnou konsolidací budete muset odstranit předchozí odkaz. Jednoduše zvolte starý odkaz a stiskněte klávesu Delete.
Klikněte na OK a Excel vygeneruje sloučení za vás. Volitelně můžete použít formátování. Pokud sloučení znovu nespustit, je nutné formátovat jenom jednou.
- Všechny popisky, které se neshodují s popisky v ostatních zdrojových oblastech, se při sloučení zobrazí v samostatných řádcích nebo sloupcích.
- Zajistěte, aby všechny kategorie, které nechcete sloučit, měly jedinečné popisky, které se zobrazují jenom v jedné zdrojové oblasti.

Pokud se data, která se mají sloučit, jsou v různých buňkách na různých listech:

Zadejte vzorec s odkazy na buňky v jiných listech, pro každý list samostatný vzorec. Jestliže například chcete sloučit do buňky A2 ve společném listu data z listů s názvy Prodej (v buňce B4), HR (v buňce F5) a Marketing (v buňce B9), zadali byste následující vzorec:

Vzorec s odkazem na více excelových listů

Tip: Pokud chcete zadat odkaz na buňku, například Prodej! B4 – ve vzorci bez zadávání zadejte vzorec až do bodu, kde potřebujete odkaz, klikněte na ouško listu a potom klikněte na buňku. Excel za vás dokončí název listu a adresu buňky. POZNÁMKA: Vzorce v takových případech můžou být náchylné k chybám, protože je velmi snadné nechtěně vybrat nesprávnou buňku. Může být také obtížné rozpoznat chybu po zadání složitého vzorce.

Pokud jsou data, která se mají sloučit, ve stejných buňkách na různých listech:

Zadejte vzorec s prostorovým odkazem, který používá odkaz na oblast názvů listů. Pokud například chcete sloučit data v buňkách A2 z prodejů do marketingu včetně, zadejte do buňky E5 hlavního listu následující:

Vzorec s prostorovým odkazem na excelové listy