Jak konsolidovat listy v Excelu 2016

Excel 2016 umožňuje konsolidovat data z různých listů do jednoho listu. Pomocí příkazového tlačítka programu Konsolidovat na kartě Data na pásu karet můžete snadno kombinovat data z více tabulek.

Můžete například použít příkaz Konsolidovat k sečtení všech rozpočtových tabulek připravených každým oddělením ve společnosti nebo k vytvoření souhrnných součtů pro výkazy zisků a ztrát za období několika let. Pokud jste k vytvoření každého listu, který konsolidujete, použili šablonu nebo identické rozvržení, Excel může rychle konsolidovat hodnoty na základě jejich společné pozice v příslušných listech. I když jsou datové položky v každé tabulce rozvrženy odlišně, Excel je stále dokáže sloučit za předpokladu, že jste použili stejné štítky k popisu datových položek v příslušných listech.

Většinou chcete sečíst data, která konsolidujete z různých listů. Ve výchozím nastavení Excel používá funkci SUM k sečtení všech buněk v listech, které sdílejí stejné odkazy na buňky (při sloučení podle pozice) nebo které používají stejné popisky (při sloučení podle kategorie). Můžete však nechat Excel použít kteroukoli z následujících statistických funkcí při provádění konsolidace: AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, VAR nebo VARP.

Chcete-li zahájit konsolidaci listů ve stejném sešitu, vyberte nový list, do kterého budou konsolidovaná data uložena. (V případě potřeby vložte nový list do sešitu kliknutím na tlačítko Vložit sešit.) Chcete-li začít spojovat listy v různých sešitech, otevřete nový sešit. Pokud jsou listy v různých sešitech generovány ze šablony, otevřete nový sešit pro konsolidovaná data z této šablony.

Než začnete s procesem konsolidace na novém listu, vyberte buňku nebo oblast buněk v tomto listu, kde se mají konsolidovaná data objevit. (Tento rozsah se nazývá cílová oblast. ) Pokud vyberete jednu buňku, Excel podle potřeby rozšíří cílovou oblast na sloupce vpravo a řádky níže, aby se do nich vešla konsolidovaná data. Pokud vyberete jeden řádek, program rozšíří cílovou oblast o další řádky listu, je-li to nutné pro umístění dat. Pokud vyberete jeden sloupec, Excel rozšíří cílovou oblast přes sloupce doprava, pokud je to nutné pro umístění dat. Pokud však jako cílovou oblast vyberete rozsah více buněk, program cílovou oblast nerozšíří a omezí konsolidovaná data pouze na výběr buňky.

Pokud chcete, aby aplikace Excel používala konkrétní oblast v listu pro všechna sloučení, která v listu provedete, přiřaďte této oblasti buněk název oblasti Consolidate_Area. Excel pak konsoliduje data do tohoto rozsahu, kdykoli použijete příkaz Konsolidovat.

Při konsolidaci dat můžete vybrat data v listech v sešitech, které jste otevřeli v Excelu, nebo v listech v neotevřených sešitech uložených na disku. Buňky, které určíte pro sloučení, se označují jako zdrojová oblast a listy obsahující zdrojové oblasti se nazývají zdrojové listy.

Pokud jsou zdrojové listy otevřené v Excelu, můžete určit odkazy na zdrojové oblasti tak, že ukážete na odkazy na buňky (i když je otevřené dialogové okno Sloučit, Excel vám umožní aktivovat různé listy a procházet je, když vyberete odkazy na buňky pro zdrojovou oblast). Pokud zdrojové listy nejsou otevřeny v Excelu, musíte zadat odkazy na buňky jako externí odkazy podle stejných pokynů, které používáte při psaní propojovacího vzorce s externím odkazem (kromě toho, že nezadáte =). Chcete-li například zadat data v rozsahu B4:R21 na List1 v sešitu s názvem CG Music – 2014 Sales.xlsx jako zdrojovou oblast, zadáte následující externí referenci:

'[CG Music – 2014 Sales.xlsx]Sheet1'!$b$4:$r$21

Všimněte si, že pokud chcete konsolidovat stejný rozsah dat ve všech listech, které používají podobný název souboru (například CG Music – 2012 Sales, CG Music – 2013 Sales, CG Music – 2014 Sales atd.), můžete použít hvězdička (*) nebo otazník (?) jako zástupné znaky, které zastupují chybějící znaky jako v

'[CG Music - 20?? Sales.xlsx]List1'!$B$4:$R$21

V tomto příkladu Excel sloučí rozsah A2:R21 v Listu 1 všech verzí sešitů, které používají „CG – Music – 20“ v hlavním souboru, když za tímto názvem následují další dva znaky (ať už 12, 13, 14, 15 a tak dále).

Při konsolidaci dat Excel použije pouze buňky ve zdrojových oblastech, které obsahují hodnoty. Pokud buňky obsahují vzorce, Excel použije jejich vypočítané hodnoty, ale pokud buňky obsahují text, Excel je ignoruje a zachází s nimi, jako by byly prázdné (kromě případu štítků kategorií, když slučujete data podle kategorií).