Da biste saželi i prikazali rezultate sa različitih radnih listova, podatke sa svakog lista možete konsolidirati na glavni radni list. Listovi se mogu nalaziti u istoj radnoj knjizi kao i glavni radni list ili u drugim radnim knjigama. Kada konsolidirate podatke, prikupljate ih tako da ih po potrebi možete jednostavnije ažurirati i agregirati.
Ako, primjerice, imate radni list s troškovima za svaki od područnih ureda, pomoću konsolidacije te brojke možete prenijeti na glavni radni list korporativnih troškova. Taj glavni radni list može sadržavati i podatke o ukupnim i prosječnim iznosima prodaje, trenutnim razinama zaliha i najprodavanijim proizvodima za cijelu tvrtku.
Savjet
Ako često konsolidirate podatke, pomoći će vam stvaranje novih radnih listova iz predloška radnog lista koji koristi dosljedan raspored. Dodatne informacije o predlošcima potražite u sljedećim člancima: Stvaranje predloška. To je idealno vrijeme i za postavljanje predloška s tablicama programa Excel.
Načini konsolidacije podataka
Dva su načina konsolidacije podataka, prema položaju ili kategoriji.
Konsolidacija prema položaju: podaci u izvornim područjima imaju isti redoslijed i koriste iste oznake. Pomoću te metode konsolidirajte podatke sa niza radnih listova, kao što su radni listovi proračuna odjela stvoreni iz istog predloška.
Konsolidacija prema kategoriji: kada podaci u izvornim područjima nisu posloženi istim redoslijedom, već koriste iste oznake. Na taj način konsolidirajte podatke iz niza radnih listova koji imaju različite rasporede, ali iste natpise nad podacima.
- Konsolidacija podataka prema kategoriji slična je stvaranju zaokretne tablice. No pomoću zaokretne tablice možete jednostavno reorganizirati kategorije. Razmislite o stvaranju zaokretne tablice ako vam je potrebna fleksibilnija konsolidacija po kategoriji.
Napomena
Primjeri u ovom članku stvoreni su pomoću programa Excel 2016. Iako se vaš prikaz može razlikovati ako koristite neku drugu verziju programa Excel, koraci su isti.
Kako konsolidirati
Slijedite ove korake da biste konsolidirali nekoliko radnih listova u glavni radni list:
Ako to još niste učinili, podatke za svaki sastavni list postavite na sljedeći način:
- Provjerite je li svaki raspon podataka u obliku popisa. Svaki stupac u prvom retku mora imati natpis (zaglavlje) i sadržavati slične podatke. Na popisu ne smije biti praznih redaka ni stupaca.
- Smjestite svaki raspon na zasebni radni list, ali nemojte unositi ništa u glavni radni list na kojem planirate konsolidirati podatke. Excel će to učiniti umjesto vas.
- Provjerite imaju li svi rasponi jednak raspored.
Kliknite gornju lijevu ćeliju područja na kojem želite prikazati konsolidirane podatke na glavnom radnom listu.
Napomena
Da biste izbjegli prebrisivanje postojećih podataka na glavnom radnom listu, provjerite jeste li ostavili dovoljno ćelija za konsolidirane podatke s desne strane i ispod ćelije.
Kliknite Konsolidacija podataka> (u grupi Alati za podatke).
U okviru Funkcija kliknite funkciju zbrajanja koju želite da Excel koristi za konsolidiranje podataka. Zadana je funkcija SUM.
Evo primjera u kojem su odabrana tri raspona na radnom listu:
Odaberite podatke.
Zatim u okviru Reference kliknite gumb Sažmi da biste smanjili ploču i odabrali podatke na radnom listu.
Kliknite radni list s podacima koje želite konsolidirati, odaberite podatke, a zatim kliknite gumb Proširi dijalog s desne strane da biste se vratili u dijaloški okvir Konsolidacija .Ako se radni list s podacima koje morate konsolidirati nalazi u drugoj radnoj knjizi, kliknite Pregledaj da biste je pronašli. Nakon pronalaženja i klika na U redu, Excel će u okvir Reference unijeti put datoteke i dodati mu uskličnik. Zatim možete odabrati druge podatke.
Evo primjera u kojem su odabrana tri raspona radnog lista:
U skočnom prozoru Konsolidacija kliknite Dodaj. Ponovite taj postupak da biste dodali sve konsolidirane raspone.
Automatska i ručna ažuriranja: Ako želite da Excel automatski ažurira konsolidacijsku tablicu kada se izvorišni podaci promijene, jednostavno potvrdite okvir Stvori veze na izvorne podatke . Ako taj okvir ostane poništen, konsolidaciju možete ažurirati ručno.
Napomena
- Veze ne možete stvoriti ako se izvorno i odredišno područje nalaze na istom listu.
- Ako morate promijeniti opseg raspona ili zamijeniti raspon, kliknite raspon u skočnom prozoru Konsolidiraj i ažurirajte ga slijedeći prethodno navedene korake. Time ćete stvoriti novu referencu raspona pa ćete prije ponovne konsolidacije morati izbrisati prethodnu. Jednostavno odaberite staru referencu i pritisnite tipku Delete.
Kliknite U redu i Excel će generirati konsolidaciju umjesto vas. Po želji možete primijeniti oblikovanje. Oblikovanje je potrebno samo jedanput, osim ako ponovno ne pokrenete konsolidaciju.
- Svi natpisi koji se ne podudaraju s natpisima u drugim izvornim područjima rezultiraju zasebnim recima ili stupcima u konsolidaciji.
- Provjerite imaju li sve kategorije koje želite konsolidirati jedinstvene natpise koji se pojavljuju samo u jednom izvornom rasponu.
Konsolidacija podataka pomoću formule
Ako se podaci za konsolidaciju nalaze u drugim ćelijama na drugim radnim listovima:
Unesite formulu s referencama ćelije na druge radne listove, po jednu za svaki pojedinačni radni list. Na primjer, za konsolidaciju podataka iz radnih listova pod nazivom Prodaja (u ćeliji B4), HR (u ćeliji F5) i Marketing (u ćeliji B9), u ćeliji A2 glavnog radnog lista, unijet ćete sljedeće:
Savjet
Da biste unijeli referencu ćelije, primjerice Prodaja! B4 – u formulu bez tipkanja upišite formulu do točke na kojoj vam je potrebna referenca, zatim kliknite karticu radnog lista i potom ćeliju. Excel će unijeti naziv lista i adresu ćelije umjesto vas. NAPOMENA: formule u takvim slučajevima mogu biti podložne pogreškama jer je vrlo lako slučajno odabrati pogrešnu ćeliju. Može biti teško uočiti pogrešku nakon unosa složene formule.
Ako se podaci za konsolidaciju nalaze u istim ćelijama na drugim radnim listovima:
Unesite formulu s 3D referencom koja koristi referencu na raspon naziva radnih listova. Na primjer, za konsolidiranje podataka u ćelijama A2 od Prodaja do uključivo Marketing, u ćeliji E5 glavnog radnog lista unijet ćete sljedeće:
Je li vam potrebna dodatna pomoć?
Uvijek možete postaviti pitanje stručnjaku u tehničkoj zajednici za Excel ili zatražiti podršku u zajednicama.
Dodatne informacije
Pregled formula u programu Excel
Izbjegavanje neispravnih formula
Traženje i ispravljanje uobičajenih pogrešaka u formulama
Tipkovni prečaci i funkcijske tipke u programu Excel