Zlúčenie údajov vo viacerých hárkoch

Ak chcete vytvoriť súhrn a zostavu výsledkov zo samostatných hárkov, môžete zlúčiť údaje z každého hárka do nadradeného hárka. Hárky sa môžu nachádzať v tom istom zošite ako nadradený hárok alebo v iných zošitoch. Keď zlučujete údaje, zostavíte údaje, aby ste ich mohli podľa potreby jednoduchšie aktualizovať a agregovať.

Ak máte napríklad hárok s výdavkami pre každú vašu miestnu pobočku, môžete pomocou zlučovania zahrnúť tieto čísla do nadradeného hárka podnikových výdavkov. Tento nadradený hárok môže obsahovať aj súhrnné objemy predaja a priemery, aktuálne úrovne zásob a najpredávanejšie produkty za celý podnik.

Tip: Ak často konsolidujete údaje, môže to pomôcť vytvoriť nové hárky zo šablóny hárka, ktorá používa konzistentné rozloženie. Ďalšie informácie o šablónach nájdete v téme: Vytvorenie šablóny. Teraz je tiež ideálny čas na nastavenie šablóny s excelovými tabuľkami.

Existujú dva spôsoby zlúčenia údajov, a to buď podľa pozície alebo kategórie.

Zlúčenie podľa pozície: Údaje v zdrojových oblastiach majú rovnaké poradie a používajú rovnaké označenia. Túto metódu použite na zlúčenie údajov z radu hárkov, ako sú napríklad hárky s rozpočtom oddelení, ktoré boli vytvorené pomocou rovnakej šablóny.

Zlúčenie podľa kategórie: Keď údaje v zdrojových oblastiach nie sú usporiadané v rovnakom poradí, ale používajú rovnaké menovky. Túto metódu použite na zlúčenie údajov z radu hárkov, ktoré majú odlišné rozloženia, ale majú rovnaké menovky údajov.

Zlučovanie údajov podľa kategórie je podobné ako vytvorenie kontingenčnej tabuľky. V kontingenčnej tabuľke však môžete jednoducho zmeniť usporiadanie kategórií. Ak potrebujete flexibilnejšie zlúčenie podľa kategórie, zvážte vytvorenie kontingenčnej tabuľky .

Poznámka: Príklady v tomto článku boli vytvorené pomocou Excel 2016. Aj keď sa vaše zobrazenie môže líšiť, ak používate inú verziu Excelu, kroky sú rovnaké.

Ak chcete zlúčiť niekoľko hárkov do nadradeného hárka, postupujte podľa týchto krokov:

Ak ste tak ešte neurobili, nastavte údaje v každom zložkovom hárku takto:
- Skontrolujte, či je každý rozsah údajov vo formáte zoznamu. Každý stĺpec musí mať v prvom riadku označenie (hlavičku) a musí obsahovať podobné údaje. V zozname nesmú byť žiadne prázdne riadky ani stĺpce.
- Každý rozsah umiestnite do samostatného hárka, ale do nadradeného hárka, do ktorého plánujete zlúčiť údaje, nezadávajte nič. Excel to urobí za vás.
- Uistite sa, že každý rozsah má rovnaké rozloženie.
V nadradenom hárku kliknite na ľavú hornú bunku oblasti, v ktorej sa majú zobraziť zlúčené údaje.

Poznámka: Ak sa chcete vyhnúť prepísaniu existujúcich údajov v nadradenom hárku, uistite sa, že pre zlúčené údaje ponecháte dostatok buniek napravo a pod touto bunkou.
Kliknite na položku Údaje>Zlúčiť (v skupine Nástroje pre údaje ).
V poli Funkcia kliknite na súhrnná funkcia, pomocou ktorej má program Excel zlúčiť údaje. Predvolená funkcia je SUM.

Tu je príklad, v ktorom boli vybraté tri rozsahy hárkov:
Vyberte údaje.

Potom v poli Odkaz kliknutím na tlačidlo Zbaliť zmenšite panel a vyberte údaje v hárku.

Kliknite na hárok obsahujúci údaje, ktoré chcete zlúčiť, vyberte údaje a potom sa kliknutím na tlačidlo Rozbaliť dialógové okno na pravej strane vráťte do dialógového okna Zlúčiť.

Ak sa hárok obsahujúci údaje, ktoré potrebujete zlúčiť, nachádza v inom zošite, kliknutím na tlačidlo Prehľadávať daný zošit vyhľadajte . Po vyhľadaní a kliknutí na tlačidlo OK Excel zadá cestu k súboru do poľa Odkaz a pripojí k tejto ceste výkričník. Potom môžete pokračovať vo výbere iných údajov.

Tu je príklad, v ktorom boli vybraté tri rozsahy hárkov:
V kontextovom okne Zlúčiť kliknite na položku Pridať. Tento postup zopakujte, ak chcete pridať všetky rozsahy, ktoré zlučujete.
Automatická verzus manuálna aktualizácia: Ak chcete, aby Excel automaticky aktualizoval zlučovanie tabuľky pri zmene zdrojových údajov, stačí začiarknuť políčko Vytvoriť prepojenia na zdrojové údaje . Ak toto políčko zostane nezačiarknuté, zlučovanie môžete aktualizovať manuálne.
Poznámky:
- Ak sú zdrojové a cieľové oblasti v tom istom hárku, nie je možné vytvoriť prepojenia.
- Ak potrebujete zmeniť rozsah alebo nahradiť rozsah, kliknite na rozsah v kontextovej ponuke Zlúčiť a aktualizujte ho pomocou vyššie uvedených krokov. Tým sa vytvorí nový odkaz na rozsah, takže pred ďalším zlúčením budete musieť pôvodný odkaz odstrániť. Stačí vybrať starý odkaz a stlačiť kláves Delete.
Kliknite na tlačidlo OK a Excel vygeneruje zlúčenie za vás. Voliteľne môžete použiť formátovanie. Formátovanie je potrebné vykonať len raz, pokiaľ zlúčenie znova neskonštátujete.
- Menovky, ktoré sa nezhodujú s menovkami v iných oblastiach zdroja, spôsobia rozdelenie riadkov a stĺpcov v zlúčení.
- Skontrolujte, či všetky kategórie, ktoré nechcete zlúčiť, majú jedinečné označenia, ktoré sa zobrazujú len v jednom zdrojovom rozsahu.

Ak sa údaje na zlúčenie nachádzajú v rôznych bunkách v rôznych hárkoch:

Zadajte vzorec s odkazmi na iné hárky pre každý samostatný hárok. Napríklad ak chcete zlúčiť údaje z hárkov Predaj (v bunke B4), HR (v bunke F5) a Marketing (v bunke B9) v bunke A2 nadradeného hárka, zadali by ste nasledujúce údaje:

Odkaz na vzorce na viacerých hárkoch Excelu

Tip: Ak chcete zadať odkaz na bunku, napríklad Predaj! B4 – vo vzorci bez písania zadajte vzorec až do bodu, kde potrebujete odkaz, potom kliknite na kartu hárka a potom kliknite na bunku. Excel doplní názov hárka a adresu bunky za vás. POZNÁMKA: Vzorce v takýchto prípadoch môžu byť náchylné na chyby, pretože omylom je veľmi jednoduché vybrať nesprávnu bunku. Môže byť tiež ťažké rozpoznať chybu po zadaní zložitého vzorca.

Ak sa údaje, ktoré sa majú zlúčiť, nachádzajú v rovnakých bunkách v rôznych hárkoch:

Zadajte vzorec s priestorovým odkazom, ktorý používa odkaz na rozsah názvov hárkov. Ak chcete napríklad zlúčiť údaje v bunkách A2 od sales through Marketing inclusive, do bunky E5 nadradeného hárka zadáte nasledovné:

Vzorec s odkazom na 3D hárok v Exceli