Konsolidácia Excelu (obsah)

  • Úvod do konsolidácie v Exceli
  • Ako konsolidovať údaje vo viacerých pracovných hárkoch?

Konsolidovať údaje v Exceli

Konsolidácia vo všeobecnosti znamená zhromažďovanie alebo zhromažďovanie niektorých bežných vecí na jednom mieste. Môže to byť akýkoľvek druh údajov alebo čokoľvek, ako napríklad informácia alebo číslo. Konsolidácia v Exceli označuje funkciu, ktorá kombinuje informácie alebo údaje z rôznych zošitov / pracovných listov do jedného pracovného hárka / zošita. Inými slovami, táto funkcia vyberie údaje z rôznych pracovných hárkov a spojí ich do jedného pracovného hárku, ktorý sa dá ľahko aktualizovať.

Napríklad, ak máte štyri pracovné hárky. Každý hárok má náklady na štvrtinu. Možno budete potrebovať jeden hárok s nákladmi zo všetkých štyroch štvrťrokov. To je, keď nastane funkcia konsolidácie. Ak sú údaje v pracovných hárkoch na rovnakom mieste, to znamená pod rovnakými štítkami av rovnakom poradí. Používame „Konsolidáciu podľa pozície“. A keď údaje v pracovných hárkoch nie sú pod rovnakými menovkami a nie v rovnakom poradí. Potom použijeme „Konsolidácia podľa kategórie“. Je to ako vytvorenie kontingenčnej tabuľky.

Ako konsolidovať údaje vo viacerých pracovných hárkoch?

Poďme pochopiť, ako konsolidovať údaje vo viacerých pracovných hárkoch s niekoľkými príkladmi.

Príklad č. 1 - Konsolidácia údajov v rovnakom zošite

Povedzme, že máme pracovný list, v ktorom máme údaje o predaji so štyrmi pracovnými listami označenými podľa ich regiónu ako sever, juh, východ a západ. Teraz by sme chceli konsolidovať údaje na jednom mieste, skôr v hárku v rámci toho istého zošita. Existuje piaty list s názvom konsolidovaný súbor.

Tento príklad ukazuje konsolidované tržby za všetky regióny. Tu sú ukážkové dátové súbory. Tu nájdete rôzne názvy pracovných hárkov a posledný konsolidovaný súbor, na ktorom musíme pracovať.

Toto je naša šablóna v hárku „konsolidovaných súborov“ a teraz začneme konsolidovať údaje z pracovných hárkov.

Teraz klikneme na bunku B3.

Ak chceme, aby funkcia „Konsolidovať“ vložila údaje z iných hárkov. Ako vidíme vyššie, bunka B3 je vybraná a teraz prejdeme na kartu Údaje v páse Excel a prejdeme na konsolidáciu.

Po kliknutí na Konsolidovať sa zobrazí nasledujúce okno:

Prvá vec, na ktorú sa pozeráme, je rozbaľovacia ponuka „Funkcie“, ktorá zobrazuje množstvo aritmetických funkcií, ako súčet, počet, max, priemer, atď. Pretože chceme sumu predaja, v rozbaľovacej ponuke vyberieme „Sum“. Teraz prejdeme na kartu referencie a pridáme odkaz na naše údaje z rôznych pracovných hárkov. Potom prejdeme na náš prvý hárok, ktorý obsahuje údaje o predaji pre sever. Musíme vybrať iba údaje o predaji, nie nadpis a riadky. Toto je zobrazené nižšie.

Odkaz je uvedený v poli „Referencia“, ako je tento.

Kliknite na „Pridať“ a odkaz sa pridá do poľa „Všetky referencie“.

Rovnakým spôsobom teraz pridáme referenciu zo všetkých ostatných hárkov, ako je sever, juh, východ a západ. Po dokončení pridávania odkazov kliknite na „OK“.

Teraz máme konsolidované údaje o predajoch pre vedúcich pracovníkov mesačne na vnútroštátnej úrovni.

Príklad č. 2 - Konsolidujte ročný predajný produkt

V tomto máme štvrťročné múdre tržby za výrobky A, B, C a D a chceme konsolidovaný ročný predaj produktov po múdrej.

Tu máme údaje v štyroch hárkoch, prvý hárok s údajmi o predaji za Q1, ďalej sú údaje za Q2 a následne údaje pre Q3 a Q4. Teraz prejdeme na kartu referencie a pridáme odkazy na naše údaje z rôznych pracovných hárkov. Prejdeme na náš prvý hárok, ktorý obsahuje údaje o predaji za Q1. Vyberieme údaje, ako je uvedené nižšie.

Teraz prejdeme na kartu Údaje, klikneme na položku Konsolidovať a zobrazí sa nasledujúce okno.

Klikneme na „Pridať“ a odkaz sa pridá do políčka „Všetky referencie“.

Klikneme na „Pridať“ a odkaz sa pridá do políčka „Všetky referencie“.

Klikneme na „Pridať“ a odkaz sa pridá do políčka „Všetky referencie“.

Nižšie je uvedená naša šablóna pre konsolidovaný dátový list. Teraz vyberieme bunku B2, aby sme získali celkové údaje o predaji z iných hárkov.

Z rozbaľovacej ponuky vyberieme funkciu „Súčet“. Podobne pridáme referencie z hárkov Q2, Q3 a Q4. Vyzerá to takto. Teraz sú pridané všetky referencie zo všetkých hárkov. Kliknite na „OK“

Teraz máme konsolidované ročné údaje o predaji spolu s celkovým súčtom pre každý produkt.

V prípade, že chceme vložiť konsolidovanú tabuľku do prázdneho pracovného listu namiesto vytvorenia tabuľky šablón. Môžeme to urobiť tiež. Ak vezmeme naše predchádzajúce vzorové údaje, vykonáme konsolidáciu uvedenú nižšie.

Nemáme šablónu pre konsolidačnú tabuľku a chceme získať konsolidované údaje v prázdnom pracovnom hárku s menovkami riadkov a stĺpcov. Pridáme nový pracovný hárok, v našom prípade je to „Konsolidovaný súbor“.

Teraz ako predtým pôjdeme na kartu Údaje

Teraz vyberieme referenciu z našich technických listov. Začneme to listom „Sever“ a potom pokračujeme listami „Juh“, „Východ“ a „Západ“. Vyberieme údaje, ako je uvedené nižšie, ktoré zahŕňajú štítky riadkov a stĺpcov.

Odkaz potom pridáme do poľa „Všetky referencie“:

Teraz pridajte všetky referencie rovnakým spôsobom zo všetkých technických listov.

Teraz skontrolujeme horný stĺpec aj ľavý stĺpec a potom stlačte tlačidlo OK.

Teraz vidíme konsolidované údaje o predaji s menovkami riadkov a stĺpcov.

Ak chcete aktualizovať konsolidované údaje po aktualizácii jednotlivých hárkov, kliknite na políčko „Vytvoriť odkazy na vytvorenie údajov“. Ak chcete údaje aktualizovať manuálne, nezačiarknite políčko a kliknite na tlačidlo OK.

Bunky v konsolidovanej tabuľke teraz obsahujú vzorec súčtu, ako je uvedené nižšie. Vďaka tomu je dynamický v prírode.

Toto je tabuľka dynamickej konsolidácie.

Príklad č. 3 - Konsolidácia údajov z rôznych zošitov

Ak máme údaje v rôznych zošitoch a chceme ich zlúčiť do jedného zošita. Môžeme to urobiť tiež. To nám poskytuje veľkú flexibilitu a jednoduchosť.

Máme údaje o štvrťročnom predaji rôznych produktov, ktoré uvádzajú A, B, C a D v rôznych zošitoch, ako je uvedené nižšie.

Po otvorení všetkých zošitov prejdeme do nového zošita a klikneme na bunku B3. Pridajte odkazy zo všetkých nižšie uvedených pracovných hárkov.

Zhromaždili sme údaje zo všetkých zošitov do nového zošita.

Okrem toho sa v novom zošite konsolidovaných údajov aktualizujú aj všetky zmeny údajov v akýchkoľvek zošitoch.

Naučili sme sa, ako v Exceli používať konsolidačnú funkciu pomocou príkladov. Je to veľmi užitočná funkcia pri zlúčení alebo zhromažďovaní údajov do jedného listu z rôznych pracovných hárkov alebo zošitov.

Dôležité informácie o konsolidácii v Exceli

  • Pri označovaní políčok „Horný riadok“ a „Ľavý stĺpec“ buďte opatrní pri výbere referenčných údajov. Potom budete musieť vybrať úplné údaje vrátane štítkov riadkov a stĺpcov.
  • Keď konsolidujete údaje dynamickej povahy z rôznych pracovných listov a zošitov. Začiarknite políčko „vytvoriť odkazy na zdrojové údaje“ a ak to bude hotové, automaticky sa aktualizujú zmeny v údajoch.

Odporúčané články

Toto je sprievodca konsolidáciou v Exceli. Tu diskutujeme o tom, ako konsolidovať údaje vo viacerých pracovných hárkoch spolu s praktickými príkladmi a šablónou programu Excel na stiahnutie. Môžete si tiež prečítať naše ďalšie súvisiace články -

  1. Vľavo vzorec v Exceli
  2. Okno sledovania v Exceli
  3. Ako pridať a používať Slicer v Exceli?
  4. Príklady VLOOKUP v Exceli

Kategórie:

Tipy programu Excel