Konsolidacija podataka na više radnih listova

Da biste saželi i prijavili rezultate iz zasebnih radnih listova, podatke sa svakog lista možete konsolidirati u glavni radni list. Listovi mogu biti u istoj radnoj knjizi kao glavni radni list ili u drugim radnim knjigama. Kada konsolidirate podatke, sastavljate podatke da biste ih mogli lakše ažurirati i agregirati po potrebi.

Ako, primjerice, imate radni list troškova za svaki od regionalnih ureda, pomoću konsolidacije te brojke možete uvaljati u glavni radni list korporativnih troškova. Ovaj glavni radni list može sadržavati i ukupne i prosječne iznose prodaje, trenutne razine inventara i najviše prodajne proizvode za cijelu korporaciju.

Savjet: Ako često konsolidirate podatke, to može pomoći pri stvaranju novih radnih listova iz predloška radnog lista koji koristi dosljedan raspored. Dodatne informacije o predlošcima potražite u članku Stvaranje predloška. Ovo je idealno vrijeme i za postavljanje predloška pomoću Excel tablica.

Dva su načina konsolidacije podataka prema položaju ili kategoriji.

Konsolidacija po položaju:podaci u izvorišnim područjima imaju isti redoslijed i koriste iste oznake. Pomoću te metode konsolidirajte podatke iz niza radnih listova, kao što su radni listovi proračuna odjela stvoreni iz istog predloška.

Konsolidacija prema kategoriji:kada podaci u izvorišnim područjima nisu raspoređeni istim redoslijedom, već koriste iste oznake. Pomoću te metode konsolidirajte podatke iz niza radnih listova koji imaju različite rasporede, ali imaju iste natpise nad podacima.

  • Konsolidacija podataka prema kategoriji slična je stvaranju zaokretne tablice. No uz zaokretnu tablicu možete jednostavno reorganizirati kategorije. Ako vam je potrebna fleksibilnija konsolidacija prema kategoriji, razmislite o stvaranju zaokretne tablice.

Napomena: Primjeri u ovom članku stvoreni su pomoću Excel 2016. Iako se prikaz može razlikovati ako koristite neku drugu verziju Excel, koraci su jednaki.

Slijedite ove korake da biste konsolidirali nekoliko radnih listova u glavni radni list:

  1. Ako još niste, postavite podatke na svakom sastavnog lista na sljedeći način:

    • Provjerite je li svaki raspon podataka u obliku popisa. Svaki stupac mora imati natpis (zaglavlje) u prvom retku i sadržavati slične podatke. Na popisu ne smije biti praznih redaka ni stupaca.

    • Stavite svaki raspon na zaseban radni list, ali nemojte unositi ništa na glavni radni list na kojem namjeravate konsolidirati podatke. Excel ćete to učiniti.

    • Provjerite ima li svaki raspon isti raspored.

  2. Kliknite gornju lijevu ćeliju područja na kojem želite prikazati konsolidirane podatke na glavnom radnom listu.

    Napomena: Da biste izbjegli prebrisavanje postojećih podataka na matrnom radnom listu, provjerite ostavljate li dovoljno ćelija desnoj i ispod te ćelije za konsolidirane podatke.

  3. Kliknite Podaci>Konsolidiraj (u grupi Alati za podatke).

    Grupa Alati za podatke na kartici Podaci

  4. U okviru Funkcija kliknite funkcija sažetka za koju želite da je Excel koristi za konsolidiranje podataka. Zadana je funkcija SUM.

    Evo primjera u kojem su odabrana tri raspona radnih listova:

    Dijaloški okvir Konsolidacija podataka

  5. Odaberite podatke.

    Zatim u okviru Referenca kliknite gumb Sažmi da biste smanjili ploču i odabrali podatke na radnom listu.

    Dijaloški okvir Sažimanje konsolidacije podataka

    Kliknite radni list koji sadrži podatke koje želite konsolidirati, odaberite podatke, a zatim kliknite gumb Proširi dijaloški okvir s desnoj tipki da biste se vratili u dijaloški okvir Konsolidacija.

    Ako se radni list koji sadrži podatke koje morate konsolidirati nalazi u drugoj radnoj knjizi, kliknite Pregledaj da biste pronašli tu radnu knjigu. Kada locirate i kliknete Uredu , Excel će put datoteke unijeti u okvir Referenca i dodati uskličnik tom putu. Nakon toga možete nastaviti s odabirom drugih podataka.

    Evo primjera u kojem su odabrana tri raspona radnih listova:

    Dijaloški okvir Konsolidacija podataka

  6. U skočnom prozoru Konsolidacija kliknite Dodaj. Ponovite postupak da biste dodali sve raspone koje konsolidirate.

  7. Automatska i ručna ažuriranja: Ako želite Excel automatski ažurirati konsolidaciju kada se izvorišni podaci mijenjaju, jednostavno potvrdite okvir Stvaranje veza na izvorišne podatke. Ako taj okvir i dalje nije potvrđen, konsolidaciju možete ažurirati ručno.

    Napomene: 

    • Veze ne možete stvarati kada su izvorišna i odredišna područja na istom listu.

    • Ako morate promijeniti opseg raspona ili zamijeniti raspon, kliknite raspon u skočnom prozoru Konsolidacija i ažurirajte ga pomoću prethodno navedenih koraka. Time ćete stvoriti novu referencu raspona, pa ćete prije ponovno konsolidiranja morati izbrisati prethodnu. Jednostavno odaberite staru referencu i pritisnite tipku Delete.

  8. Kliknite U redu, Excel će generirati konsolidaciju. Ako želite, možete primijeniti oblikovanje. Potrebno je oblikovati samo jedanput, osim ako ponovno ne ponovno pokrenite konsolidaciju.

    • Svi natpisi koji se ne podudaraju s natpisima u drugim izvornim područjima rezultiraju zasebnim recima ili stupcima u konsolidaciji.

    • Provjerite imaju li kategorije koje ne želite konsolidirati jedinstvene oznake koje se prikazuju u samo jednom izvorišnu rasponu.

Ako su podaci za konsolidaciju u različitim ćelijama na različitim 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:

Excel referencu formule s više listova
 

Savjet: Da biste unijeli referencu na ćeliju, primjerice Prodaja! B4 – u formulu bez upisivanja upišite formulu do točke na kojoj vam je potrebna referenca, a zatim kliknite karticu radnog lista, a zatim kliknite ćeliju. Excel će ispuniti naziv lista i adresu ćelije. NAPOMENA: formule u takvim slučajevima mogu biti jednostavne za pogreške jer je vrlo jednostavno slučajno odabrati pogrešnu ćeliju. Nakon unosa složene formule može biti teško uočiti pogrešku.

Ako se podaci koje želite konsolidirati nalazi u istim ćelijama na različitim radnim listovima:

Unesite formulu s 3D referencom koja koristi referencu na raspon naziva radnih listova. Da biste, primjerice, konsolidirali podatke u ćelijama A2 iz prodaje putem značajke Marketing inclusive, u ćeliji E5 glavnog radnog lista unijeli biste sljedeće:

Excel referentna formula za 3D list

Je li vam potrebna dodatna pomoć?

Postavite pitanje stručnjaku u tehničkoj zajednici za Excel, zatražite podršku u zajednici za odgovore ili predložite novu značajku ili poboljšanje na forumu za Excel User Voice.

Dodatni sadržaji

Pregled formula u programu Excel

Izbjegavanje neispravnih formula

Traženje i ispravljanje uobičajenih pogrešaka u formulama

Funkcijske tipke i tipkovni prečaci u programu Excel

Funkcije programa Excel (abecednim redoslijedom)

Funkcije programa Excel (po kategorijama)

Potrebna vam je dodatna pomoć?

Proširite svoje vještine korištenja sustava Office
Istražite osposobljavanje

Jesu li vam ove informacije bile korisne?

×