Konsolidovanje podataka u više radnih listova

Da biste rezimirali rezultate i prijavili ih iz zasebnih radnih listova, možete da konsolidujete podatke iz svakog lista u glavni radni list. Listovi mogu biti u istoj radnoj svesci kao glavni radni list ili u drugim radnim svescima. Kada konsolidujete podatke, vi sastavljate podatke kako biste po potrebi mogli lakše da ih ažurirate i agregirate.

Na primer, ako imate radni list troškova za svaku od regionalnih kancelarija, možete da koristite konsolidaciju da biste te cifre pretvorili u glavni radni list poslovnih troškova. Ovaj glavni radni list može da sadrži i ukupne vrednosti prodaje i prosečne vrednosti, trenutne nivoe zaliha i najprodavanije proizvode za celo preduzeće.

Savet: Ako često konsolidujte podatke, može biti korisno da kreirate nove radne listove od predloška radnog lista koji koristi dosledan raspored. Da biste saznali više o predlošcima, pročitajte članak: Kreiranje predloška. Ovo je takođe idealno vreme za podešavanje predloška sa Excel tabelama.

Postoje dva načina za konsolidovanje podataka, po položaju ili kategoriji.

Konsolidacija po položaju: Podaci u izvornim oblastima imaju isti redosled i koriste iste oznake. Koristite ovaj metod za konsolidovanje podataka iz grupe radnih listova, kao što su radni listovi budžeta sektora koji su kreirani na osnovu istog predloška.

Konsolidacija po kategoriji: Kada podaci u izvornim oblastima nisu raspoređeni istim redosledom, ali koriste iste oznake. Koristite ovaj metod za konsolidovanje podataka iz grupe radnih listova koji imaju različite rasporede, ali imaju iste oznake podataka.

Konsolidacija podataka po kategoriji slična je kreiranju izvedene tabele. Međutim, pomoću izvedene tabele možete lako da reorganizujete kategorije. Razmotrite kreiranje izvedene tabele ako vam je potrebna fleksibilnija konsolidacija po kategoriji.

Napomena: Primeri u ovom članku kreirani su pomoću Excel 2016. Iako se prikaz može razlikovati ako koristite drugu verziju programa Excel, koraci su isti.

Pratite ove korake da biste konsolidovani nekoliko radnih listova u glavni radni list:

Ako to već niste uradili, podesite podatke na svakom konstitutivnog lista tako što ćete uraditi sledeće:
- Uverite se da je svaki opseg podataka u formatu liste. Svaka kolona mora da ima oznaku (zaglavlje) u prvom redu i da sadrži slične podatke. Na listi ne sme biti praznih redova ili kolona.
- Postavite svaki opseg na zaseban radni list, ali nemojte unositi ništa u glavni radni list u koji planirate da konsolidujte podatke. Excel će to uraditi za vas.
- Uverite se da svaki opseg ima isti raspored.
U glavnom radnom listu kliknite na gornju levu ćeliju oblasti u kojoj želite da se pojave konsolidovani podaci.

Napomena: Da biste izbegli zamenjivanje postojećih podataka u okviru glavnog radnog lista, uverite se da ste ostavili dovoljno ćelija sa desne strane i ispod ove ćelije za konsolidovane podatke.
Izaberite stavku Podaci> konsolidovanje (u grupi Alatke za podatke).
U polju Funkcija izaberite funkcija za rezimiranje koju želite da Excel koristi za konsolidovanje podataka. Podrazumevana funkcija je SUM.

Evo primera u kojem su izabrana tri opsega radnog lista:
Izaberite podatke.

Zatim u polju Referenca kliknite na dugme Skupi da biste smanjili tablu i izabrali podatke u radnom listu.

Kliknite na radni list koji sadrži podatke koje želite da konsolidirate, izaberite podatke, a zatim sa desne strane kliknite na dugme Razvij dijalog da biste se vratili u dijalog Konsolidovanje.

Ako se radni list koji sadrži podatke koje treba da konsolidirate nalazi u drugoj radnoj svesci, kliknite na dugme Potraži da biste pronašli tu radnu svesku. Nakon pronalaženja i klika na dugme U redu, Excel će uneti putanju datoteke u polje Referenca i dodati znak uzvika toj putanji. Zatim možete da nastavite da birate druge podatke.

Evo primera u kojem su izabrana tri opsega radnog lista:
U iskačućem prozoru Konsolidovanje kliknite na dugme Dodaj. Ponovite ovo da biste dodali sve opsege koje konsolidirate.
Automatsko naspram ručnih ispravki: Ako želite da Excel automatski ažurira tabelu konsolidacije kada se izvorni podaci promene, jednostavno potvrdite izbor u polju za potvrdu Kreiraj veze sa izvornim podacima . Ako ovo polje ostane opozvano, možete ručno da ažurirate konsolidaciju.
Napomene:
- Nije moguće kreirati veze kada se izvorne i odredišne oblasti nalaze na istom listu.
- Ako treba da promenite opseg – ili da zamenite opseg – kliknite na opseg u iskačućem meniju Konsoliduj i ažurirajte ga koristeći gorenavedene korake. Ovo će kreirati novu referencu opsega, tako da ćete morati da izbrišete prethodnu pre nego što ponovo izvršite konsolidovanje. Jednostavno odaberite staru referencu i pritisnite taster Delete.
Kliknite na dugme U redu i Excel će generisati konsolidaciju za vas. Opcionalno možete da primenite oblikovanje. Oblikovanje je neophodno samo jednom, osim ako ponovo ne pokrenete konsolidaciju.
- Sve oznake koje se ne podudaraju sa oznakama u drugim izvornim oblastima, tokom konsolidovanja će stvoriti posebne redove ili kolone.
- Uverite se da kategorije koje ne želite da konsolidujete imaju jedinstvene oznake koje se pojavljuju samo u jednom izvornom opsegu.

Ako su podaci za konsolidovanje u različitim ćelijama na različitim radnim listovima:

Unesite formulu sa referencama na ćelije ka drugim radnim listovima, jednu za svaki poseban radni list. Na primer, da biste konsolidovali podatke sa radnih listova pod nazivom „Prodaja“ (u ćeliji B4), „Ljudski resursi“ (u ćeliji F5) i „Marketing“ (u ćeliji B9), u ćeliju A2 glavnog radnog lista, uneli biste sledeće:

Referenca formule sa više listova u programu Excel

Savet: Da biste uneli referencu ćelije – kao što je Prodaja! B4 – u formuli bez kucanja otkucajte formulu do mesta gde vam je potrebna referenca, zatim izaberite karticu radnog lista, a zatim kliknite na ćeliju. Excel će dovršiti ime lista i adresu ćelije za vas. NAPOMENA: formule u takvim slučajevima mogu biti podložne greškama jer je vrlo lako slučajno izabrati pogrešnu ćeliju. Takođe može biti teško uočiti grešku kada unesete složenu formulu.

Ako se podaci koje treba konsolidovanje konsolidovanje nalaziti u istim ćelijama na različitim radnim listovima:

Unesite formulu sa 3-D referencom koja koristi referencu na opseg imena radnih listova. Na primer, da biste konsolidirali podatke u ćelijama A2, od prodaje do marketinga, u ćeliju E5 glavnog radnog lista uneli biste sledeće:

Referentna formula Excel 3D lista