Za rezimiranje i prijavljivanje rezultata iz zasebnih radnih listova možete da konsolidujete podatke sa svakog lista u glavni radni list. Listovi mogu biti u istoj radnoj svesci sa glavnim radnim listom ili u posebnim. Kada konsolidujete podatke, vi prikupljate podatke tako da možete lakše da ih ažurirate i prikupite po potrebi.
Na primer, ako imate radni list troškova za svaku od regionalnih kancelarija, možete da upotrebite konsolidovanje da biste objedinili ove cifre u glavni radni list za troškove preduzeća. Ovaj glavni radni list može da sadrži i ukupne vrednosti prodaje i proseke, tekući nivo zaliha i najprodavanije proizvode za celokupno preduzeće.
Savet
Ako često usaglašavate podatke, možda će vam pomoći da kreirate nove radne listove od predloška radnog lista koji koristi usaglašeni raspored. Da biste saznali više o predlošcima, pročitajte članak: Kreiranje predloška. Ovo je i idealno vreme za podešavanje predloška pomoću Excel tabela.
Načini za konsolidovanje podataka
Postoje dva načina za konsolidovanje podataka, prema položaju ili kategoriji.
Konsolidacija prema položaju: podaci u izvornim oblastima imaju isti redosled i koriste iste oznake. Koristite ovaj metod da biste konsolidovali podatke iz niza radnih listova, kao što su radni listovi budžeta odseka koji su kreirani iz istog predloška.
Konsolidacija prema kategoriji: Kada podaci u izvornim oblastima nisu raspoređeni u isti redosled, ali koriste iste oznake. Koristite ovaj metod da biste konsolidovali podatke iz niza radnih listova koji imaju različite rasporede, ali iste oznake podataka.
- Konsolidovanje podataka po kategoriji slično je pravljenju 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 kategorijama.
Napomena
Primeri u ovom članku kreirani su pomoću Excel 2016. Iako prikaz može da se razlikuje ako koristite neku drugu verziju programa Excel, koraci su isti.
Kako konsolidovati
Pratite ove korake da biste konsolidovali nekoliko radnih listova u glavni radni list:
Ako to već niste uradili, podesite podatke u svakom sastavnom listu tako što ćete uraditi sledeće:
- Uverite se da je svaki opseg podataka u obliku liste. Svaka kolona mora da ima oznaku (zaglavlje) u prvom redu i da sadrži slične podatke. Nigde na listi ne sme biti praznih redova ili kolona.
- Stavite svaki opseg na poseban radni list, ali nemojte ništa unositi u glavni radni list u kojem planirate da konsolidujete podatke. Excel će to uraditi umesto vas.
- Postarajte 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 zamenu postojećih podataka u glavnom radnom listu, uverite se da ste ostavili dovoljan broj ćelija sa desne strane i ispod ove ćelije za konsolidovane podatke.
Izaberite stavku "Konsolidovanje podataka>" (u grupi "Alatke za podatke").
U polju " Funkcija " izaberite funkciju 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.
Izaberite radni list koji sadrži podatke koje želite da konsolidujete, izaberite podatke, a zatim kliknite na dugme " Razvij dijalog " sa desne strane da biste se vratili u dijalog " Konsolidovanje ".Ako se radni list sa podacima koje treba da konsolidujete nalazi u nekoj drugoj radnoj svesci, kliknite na dugme Potraži da biste pronašli tu radnu svesku. Kada pronađete i kliknete na dugme "U redu", Excel će uneti putanju datoteke u polje " Referenca " i dodati znak uzvika na tu putanju. Zatim možete nastaviti sa izborom drugih podataka.
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 konsolidujete.
Automatska naspram ručnih ažuriranja: Ako želite da Excel automatski ažurira tabelu za konsolidaciju 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 ažurirati konsolidaciju.
Napomena
- Veze nije moguće kreirati kada su izvorne i odredišne oblasti na istom listu.
- Ako treba da promenite opseg ili da zamenite opseg, kliknite na opseg u iskačućem prozoru "Konsolidovanje" i ažurirajte ga pomoću gorenavedenih koraka. Kreiraće se nova referenca za opseg, tako da ćete morati da izbrišete prethodnu pre ponovnog konsolidovanja. 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 potrebno 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.
Korišćenje formule za konsolidovanje podataka
Podaci koje treba konsolidovati nalaze se 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:
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 kliknite na karticu radnog lista i kliknite na ćeliju. Excel će umesto vas dovršiti ime lista i adresu ćelije. 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 nakon unosa složene formule.
Podaci koje treba konsolidovati nalaze se 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 konsolidovali podatke u ćelijama A2, na radnim listovima "Prodaja" do zaključno sa "Marketing", u ćeliju E5 glavnog radnog lista, uneli biste sledeće:
Potrebna vam je dodatna pomoć?
Možete uvek da postavite pitanje stručnjaku u Excel Tech zajednici ili da potražite pomoć u zajednicama.
Takođe pogledajte
Pregled formula u programu Excel
Način izbegavanja oštećenih formula
Pronalaženje i ispravljanje grešaka u formulama
Tasterske prečice i funkcijski tasteri u programu Excel