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. 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ů. 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 -
Příklad # 3 - Konsolidace dat z různých sešitů
Důležité informace o konsolidaci v Excelu
Doporučené články