Konsolidacija podataka na više radnih listova

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

Konsolidacija prema položaju: podaci u izvorišnim područjima imaju isti redoslijed i koriste iste oznake. Pomoću ove 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, ali 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 kategorije možete jednostavno reorganizirati. Razmislite o stvaranju zaokretne tablice ako vam je potrebna fleksibilnija konsolidacija prema kategoriji.

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

1. Ako to još niste učinili, postavite podatke na svakom konstitutivnim listom na sljedeći način:

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

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

   • Provjerite ima li svaki raspon isti raspored.

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

   : Da biste izbjegli prebrisivanje postojećih podataka na matrici radnog lista, ostavite dovoljno ćelija desno i ispod te ćelije za konsolidirane podatke.

3. Kliknite >Konsolidacija (u grupi Alati podataka).

   

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:

   

5. Odaberite podatke.

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

   

   Kliknite radni list koji sadrži podatke koje želite konsolidirati, odaberite podatke, a zatim kliknite gumb Proširi dijaloški okvir s desne strane 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 ga locirate i kliknete U redu, Excel će unijeti put datoteke u okvir Referenca i dodati uskličnik na taj put. Nakon toga možete nastaviti s odabirom drugih podataka.

   Evo primjera u kojem su odabrana tri raspona radnih listova:

   

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

7. Automatska ili ručna ažuriranja: Ako želite da Excel automatski ažurira konsolidacijske tablice kada se izvorni podaci mijenjaju, jednostavno potvrdite okvir Stvori veze na izvorišne podatke. Ako taj okvir i dalje nije potvrđen, konsolidaciju možete ažurirati ručno.

   : 

   • Ne možete stvoriti veze 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 morati izbrisati prethodnu prije ponovnog konsolidiranja. Jednostavno odaberite staru referencu i pritisnite tipku Delete.

8. Kliknite U redu, a Excel će generirati konsolidaciju umjesto vas. Ako želite, možete primijeniti oblikovanje. Potrebno je oblikovati samo jedanput, osim ako ponovno ne pokrećete 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 ne želite konsolidirati jedinstvene oznake koje se prikazuju samo u jednom izvorišnom rasponu.

Ako se podaci za konsolidaciju nalaze 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:

 

Ako se podaci za konsolidaciju nalaze 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 od prodaje do uključivog marketinga, u ćeliji E5 glavnog radnog lista unijeli biste sljedeće: