Ako konsolidovať pracovné hárky v Exceli 2016

Excel 2016 umožňuje konsolidovať údaje z rôznych hárkov do jedného hárka. Pomocou príkazového tlačidla programu Konsolidovať na karte Údaje na páse s nástrojmi môžete jednoducho kombinovať údaje z viacerých tabuliek.

Môžete napríklad použiť príkaz Konsolidovať na sčítanie všetkých rozpočtových tabuliek pripravených každým oddelením v spoločnosti alebo na vytvorenie súhrnných súčtov pre výkazy ziskov a strát za obdobie niekoľkých rokov. Ak ste na vytvorenie každého pracovného hárka, ktorý konsolidujete, použili šablónu alebo identické rozloženie, Excel dokáže rýchlo konsolidovať hodnoty na základe ich spoločnej pozície v príslušných hárkoch. Avšak aj keď sú položky údajov v každej tabuľke rozložené inak, Excel ich môže stále konsolidovať za predpokladu, že ste použili rovnaké označenia na popis údajových položiek v príslušných pracovných hárkoch.

Väčšinu času chcete zhrnúť údaje, ktoré konsolidujete z rôznych pracovných hárkov. V predvolenom nastavení Excel používa funkciu SUM na sčítanie všetkých buniek v hárkoch, ktoré zdieľajú rovnaké odkazy na bunky (keď konsolidujete podľa pozície) alebo ktoré používajú rovnaké označenia (keď konsolidujete podľa kategórie). Môžete však nechať Excel použiť ktorúkoľvek z nasledujúcich štatistických funkcií pri vykonávaní konsolidácie: AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, VAR alebo VARP.

Ak chcete začať s konsolidáciou hárkov v rovnakom zošite, vyberte nový hárok, do ktorého budú uložené konsolidované údaje. (V prípade potreby vložte nový hárok do zošita kliknutím na tlačidlo Vložiť hárok.) Ak chcete začať spájať hárky v rôznych zošitoch, otvorte nový zošit. Ak sú hárky v rôznych zošitoch generované zo šablóny, otvorte nový zošit pre konsolidované údaje z tejto šablóny.

Pred začatím procesu konsolidácie na novom hárku si vyberiete bunku alebo rozsah buniek v tomto hárku, kde sa majú zobraziť konsolidované údaje. (Tento rozsah sa nazýva cieľová oblasť. ) Ak vyberiete jednu bunku, Excel podľa potreby rozšíri cieľovú oblasť na stĺpce vpravo a riadky nižšie, aby sa do nej zmestili konsolidované údaje. Ak vyberiete jeden riadok, program rozšíri cieľovú oblasť o ďalšie riadky pracovného hárka, ak je to potrebné na prispôsobenie údajov. Ak vyberiete jeden stĺpec, Excel rozšíri cieľovú oblasť cez stĺpce doprava, ak je to potrebné na prispôsobenie údajov. Ak však ako cieľovú oblasť vyberiete rozsah viacerých buniek, program cieľovú oblasť nerozšíri a konsolidované údaje obmedzí len na výber buniek.

Ak chcete, aby Excel použil konkrétny rozsah v hárku pre všetky konsolidácie, ktoré vykonáte v hárku, priraďte tomuto rozsahu buniek názov rozsahu Consolidate_Area. Excel potom konsoliduje údaje do tohto rozsahu vždy, keď použijete príkaz Konsolidovať.

Pri konsolidácii údajov môžete vybrať údaje v hárkoch v zošitoch, ktoré ste otvorili v Exceli, alebo v hárkoch v neotvorených zošitoch uložených na disku. Bunky, ktoré zadáte na konsolidáciu, sa označujú ako zdrojová oblasť a hárky, ktoré obsahujú zdrojové oblasti, sa nazývajú zdrojové hárky.

Ak sú zdrojové hárky otvorené v Exceli, môžete určiť odkazy na zdrojové oblasti tak, že ukážete na odkazy na bunky (aj keď je otvorené dialógové okno Konsolidovať, Excel vám umožní aktivovať rôzne hárky a posúvať sa v nich, keď vyberiete odkazy na bunky pre zdrojovú oblasť). Ak zdrojové hárky nie sú otvorené v Exceli, musíte zadať odkazy na bunky ako externé odkazy podľa rovnakých pokynov, aké používate pri zadávaní vzorca prepojenia s externým odkazom (okrem toho, že nezadáte =). Ak chcete napríklad zadať údaje v rozsahu B4:R21 na Háre 1 v zošite s názvom CG Music – 2014 Sales.xlsx ako zdrojovú oblasť, zadajte nasledujúci externý odkaz:

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

Upozorňujeme, že ak chcete konsolidovať rovnaký rozsah údajov vo všetkých hárkoch, ktoré používajú podobný názov súboru (napríklad CG Music – 2012 Sales, CG Music – 2013 Sales, CG Music – 2014 Sales atď.), môžete použiť hviezdička (*) alebo otáznik (?) ako zástupné znaky, ktoré zastupujú chýbajúce znaky ako v

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

V tomto príklade Excel zlúči rozsah A2:R21 v Háre 1 všetkých verzií zošitov, ktoré používajú „CG – Music – 20“ v hlavnom súbore, keď za týmto názvom nasledujú ďalšie dva znaky (či už 12, 13, 14, 15 a tak ďalej).

Pri konsolidácii údajov Excel použije iba bunky v zdrojových oblastiach, ktoré obsahujú hodnoty. Ak bunky obsahujú vzorce, Excel použije ich vypočítané hodnoty, ale ak bunky obsahujú text, Excel ich ignoruje a zaobchádza s nimi, akoby boli prázdne (okrem prípadu menoviek kategórií, keď zlučujete údaje podľa kategórie).