Konsolidace Excelu (obsah)

  • Úvod do konsolidace v Excelu
  • Jak konsolidovat data do více listů?

Konsolidace dat v Excelu

Konsolidace obecně znamená shromažďování nebo shromažďování některých společných věcí na jednom místě. Mohlo by se jednat o jakýkoli druh dat nebo o něco jako informace nebo číslo. Konsolidace v Excelu označuje funkci, která kombinuje informace nebo data z různých sešitů / listů do jednoho listu / sešitu. Jinými slovy, tato funkce vezme data z různých listů a spojí je do jednoho listu, který lze snadno aktualizovat.

Například pokud máte čtyři pracovní listy. Každý list má náklady na čtvrtinu. Možná budete potřebovat jediný list s výdaji ze všech čtyř čtvrtletí. To je okamžik, kdy se objeví funkce konsolidace. Pokud jsou data v listech na stejné pozici, to znamená pod stejnými štítky a ve stejném pořadí. Používáme „Konsolidaci podle pozice“. A když data v listech nejsou pod stejnými štítky a nejsou ve stejném pořadí. Poté použijeme „Konsolidace podle kategorie“. Je to jako vytvořit kontingenční tabulku.

Jak konsolidovat data do více listů?

Podívejme se, jak konsolidovat data do několika listů s několika příklady.

Příklad č. 1 - Konsolidace dat ve stejném sešitu

Řekněme, že máme pracovní list, ve kterém máme údaje o prodeji se čtyřmi tabulkami označenými podle jejich regionu jako sever, jih, východ a západ. Nyní bychom chtěli konsolidovat data na jedno místo, spíše v listu ve stejném sešitu. Existuje pátý list s názvem konsolidovaný soubor.

Tento příklad ukazuje konsolidovaný prodej ve všech regionech. Zde jsou ukázkové datové soubory. Zde můžete vidět různé názvy listů a poslední konsolidovaný soubor, na kterém musíme pracovat.

Toto je naše šablona v listu „konsolidovaný soubor“ a nyní začneme konsolidovat data z pracovních listů.

Nyní klikneme na buňku B3.

Kam chceme, aby funkce „Konsolidovat“ vložila data z jiných listů. Jak vidíme výše, je vybrána buňka B3 a nyní přejdeme na kartu Data v pásu karet aplikace Excel a přejdeme ke sloučení.

Jakmile klikneme na Sloučit, objeví se následující okno:

První věc, na kterou se podíváme, je rozbalovací nabídka „Funkce“, která ukazuje mnoho aritmetických funkcí, jako je součet, počet, maximum, průměr atd. Protože chceme součet tržeb, v rozevíracím seznamu vybereme „Součet“. Nyní přejdeme na kartu reference a přidá se odkaz na naše data z různých listů. Poté přejdeme na náš první list, který obsahuje údaje o prodeji pro Severní. Musíme pouze vybrat údaje o prodeji, nikoli nadpis a řádky. To je znázorněno níže.

Odkaz se zobrazí v poli „Reference“, jako je tento.

Klikněte na „Přidat“ a odkaz bude přidán do pole „Všechny reference“.

Stejným způsobem nyní přidáme odkaz ze všech ostatních listů, jako je sever, jih, východ a západ. Po dokončení přidávání referencí klikněte na „OK“.

Nyní máme konsolidované údaje o prodejích pro vedoucí pracovníky meziměsíčně na národní úrovni.

Příklad č. 2 - Konsolidujte roční prodejní produkt

V tomto máme čtvrtletní prodej produktů A, B, C a D a chceme konsolidovaný roční prodej produktů.

Zde máme data ve čtyřech listech, první list s údaji o prodeji za Q1, dále pak data za Q2 následovaná daty pro Q3 a Q4. Nyní přejdeme na kartu referencí a přidáme odkazy na naše data z různých listů. Přejdeme na náš první list, který obsahuje údaje o prodeji za Q1. Vybereme data, jak je ukázáno níže.

Nyní přejdeme na kartu Data a klikneme na Konsolidovat a objeví se následující okno.

Klikneme na „Přidat“ a odkaz se přidá do pole „Všechny reference“.

Klikneme na „Přidat“ a odkaz se přidá do pole „Všechny reference“.

Klikneme na „Přidat“ a odkaz se přidá do pole „Všechny reference“.

Níže je naše šablona pro konsolidovaný datový list. Nyní vybereme buňku B2, abychom získali celkové údaje o prodeji z jiných listů.

Z rozbalovací nabídky vybereme funkci „Sum“. Podobně přidáme odkazy z listů Q2, Q3 a Q4. Vypadá to takto. Nyní jsou přidány všechny reference ze všech listů. Klikněte na „OK“

Nyní máme konsolidovaná roční data o prodeji s celkovým součtem pro každý produkt.

V případě, že chceme vložit konsolidovanou tabulku do prázdného listu místo vytvoření šablony tabulky. Můžeme to udělat také. Vezmeme-li naše předchozí ukázková data, provedeme konsolidaci níže.

Nemáme šablonu pro konsolidační tabulku a chceme získat konsolidovaná data v prázdném listu s popisky řádků a sloupců. Přidáme nový list, v našem případě je to „Konsolidovaný soubor“.

Nyní stejně jako předtím půjdeme na kartu Data

Nyní vybereme odkaz z našich datových listů. Začneme tím listem „North“ a poté pokračujeme s listy „South“, „East“ a „West“. Vybereme data, jak je uvedeno níže, která obsahuje popisky řádků a sloupců.

Poté přidáme odkaz do pole „Všechny reference“:

Nyní přidejte všechny reference stejným způsobem ze všech datových listů.

Nyní zkontrolujeme „Horní řádek“ i „Levý sloupec“ a poté stiskneme OK.

Nyní vidíme konsolidovaná data o prodeji s popisky řádků a sloupců.

Pokud chcete aktualizovat konsolidovaná data po aktualizaci jednotlivých listů, klikněte na políčko „Vytvořit odkazy pro vytvoření dat“. Pokud chcete data aktualizovat ručně, nezaškrtněte políčko a klikněte na OK.

Buňky v konsolidované tabulce nyní obsahují vzorec součtu, jak je uvedeno níže. Díky tomu je příroda dynamická.

Toto je dynamická konsolidační tabulka.

Příklad # 3 - Konsolidace dat z různých sešitů

Pokud máme data v různých sešitech a chceme je sloučit do jednoho sešitu. Můžeme to udělat také. To nám dává velkou flexibilitu a snadnost.

Máme čtvrtletní údaje o prodeji různých produktů, které říkají A, B, C a D, v různých sešitech, jak je ukázáno níže.

Jakmile budou všechny sešity otevřeny, přejdeme do nového sešitu a klikneme na buňku B3. Přidejte odkazy ze všech níže uvedených pracovních listů.

Sloučili jsme data ze všech sešitů do nového sešitu.

Kromě toho budou všechny změny v údajích v jakýchkoli sešitech aktualizovány také v novém sešitu konsolidovaných dat.

Na příkladech jsme se naučili, jak používat konsolidační funkci v Excelu. Je to velmi užitečná funkce při slučování nebo shromažďování dat do jednoho listu z různých listů nebo sešitů.

Důležité informace o konsolidaci v Excelu

  • Při zaškrtávání políček „Horní řádek“ a „Levý sloupec“ buďte opatrní při výběru referenčních dat. Poté budete muset vybrat úplná data včetně štítků řádků a sloupců.
  • Když konsolidujete data dynamické povahy z různých listů a sešitů. Zkontrolujte zaškrtávací políčko „Vytvořit odkazy na zdrojová data“, a pokud se tak stane, automaticky se změní data.

Doporučené články

Toto je průvodce konsolidací v Excelu. Zde diskutujeme o tom, jak konsolidovat data ve více listech spolu s praktickými příklady a šablonou Excel ke stažení. Můžete si také prohlédnout naše další související články -

  1. LEFT Formula v Excelu
  2. Okno sledování v Excelu
  3. Jak přidat a používat kráječ v Excelu?
  4. Příklady VLOOKUP v Excelu

Kategorie:

Tipy pro Excel