Preuzmite primjer radne knjige
Ovaj će vodič pokazati kako koristiti funkcije SUMPRODUCT i SUMIFS za zbrajanje podataka koji zadovoljavaju određene kriterije na više listova u Excelu i Google tablicama.
Redovna svota na više listova
Ponekad vaši podaci mogu obuhvatiti nekoliko radnih listova u Excel datoteci. To je uobičajeno za podatke koji se povremeno prikupljaju. Svaki list u radnoj knjizi može sadržavati podatke za zadano razdoblje. Želimo formulu koja zbraja podatke sadržane u dva ili više listova.
Funkcija SUM omogućuje jednostavno zbrajanje podataka na više listova pomoću a 3D referenca:
| 1 | = SUM (List1: List2! A1) |
Međutim, to nije moguće s funkcijom SUMIFS. Umjesto toga moramo koristiti složeniju formulu.
Zbroj ako na više listova
Ovaj primjer će zbrojiti Broj planiranih isporuka za svakoga Kupac na više radnih listova, od kojih svaki sadrži podatke koji se odnose na različiti mjesec, pomoću funkcija SUMIFS, SUMPRODUCT i INDIRECT:
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECT ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
Prođimo kroz ovu formulu.
Korak 1: Izradite formulu SUMIFS samo za 1 ulazni list:
Koristimo funkciju SUMIFS za zbrajanje Broj planiranih isporuka po Kupac za jedan ulazni podatkovni list:
| 1 | = SUMIFS (D3: D7, C3: C7, H3) |
Korak 2: Formuli dodajte referencu na list
Rezultat formule zadržavamo isti, ali specificiramo da su ulazni podaci u pozvanom listu 'Korak 2'
| 1 | = SUMIFS ('Korak 2'! D3: D7, 'Korak 2'! C3: C7, H3) |
Korak 3: Ugnijezdite se u funkciju SUMPRODUCT
Za pripremu formule za izvođenje SUMIFS izračuna na više listova, a zatim za zbrajanje rezultata, dodajemo funkciju SUMPRODUCT oko formule
| 1 | = SUMPRODUCT (ZBIRA ('Korak 3'! D3: D7, 'Korak 3'! C3: C7, H3)) |
Korištenje funkcije SUMIFS na jednom listu daje jednu vrijednost. Na više listova funkcija SUMIFS ispisuje niz vrijednosti (po jednu za svaki radni list). Koristimo funkciju SUMPRODUCT za zbrajanje vrijednosti u ovom nizu.
Korak 4: Zamijenite referencu lista s popisom naziva listova
Želimo zamijeniti Naziv lista dio formule s popisom podataka koji sadrži vrijednosti: Siječanj, Veljače, Ožujka, i Travnja. Ovaj je popis pohranjen u ćelijama F3: F6.
Funkcija INDIRECT to osigurava da se prikazuje popis teksta Imena listova tretira se kao dio važeće reference ćelije u funkciji SUMIFS.
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECT ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
U ovoj formuli prethodno napisana referenca raspona:
| 1 | 'Korak 3'! D3: D7 |
Zamjenjuje se:
| 1 | INDIREKTNO ("'" & F3: F6 & "'!" & "D3: D7") |
Navodnici otežavaju čitanje formule, pa se ovdje prikazuje s dodatnim razmacima:
| 1 | INDIREKTNO ("'" & F3: F6 & "'!" & "D3: D7") |
Korištenje ovog načina upućivanja na popis ćelija omogućuje nam i sažimanje podataka s više listova koji ne slijede stil numeričkog popisa. Standardna 3D referenca zahtijevala bi da nazivi listova budu u stilu: Input1, Input2, Input3 itd., Ali gornji primjer omogućuje vam korištenje popisa bilo kojeg Imena listova i da ih navedete u zasebnoj ćeliji.
Zaključavanje referenci ćelija
Radi lakšeg čitanja formula prikazali smo formule bez zaključanih referenci ćelija:
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECT ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
Ali ove formule neće raditi ispravno ako ih kopirate i zalijepite na drugo mjesto u datoteci. Umjesto toga, trebali biste koristiti zaključane ćelijske reference poput ove:
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & $ F $ 3: $ F $ 6 & "'!" & "D3: D7"), INDIRECT ("'" & $ F $ 3: $ F $ 6 & "'!" & "C3: C7"), H3)) |
Pročitajte naš članak o zaključavanju referenci stanica da biste saznali više.
Zbroj ako na više listova u Google tablicama
Upotreba funkcije INDIRECT za pozivanje na popis listova u funkciji SUMPRODUCT i SUMIFS trenutno nije moguća u Google tablicama.
Umjesto toga, mogu se napraviti zasebni SUMIFS izračuni za svaki ulazni list i rezultati se zbrajaju:
| 1234 | = SUMIFS (Jan! D3: D7, Jan! C3: C7, H3)+ZBIRKE (veljača! D3: D7, veljača! C3: C7, H3)+ZBIRKE (ožujak! D3: D7, ožujak! C3: C7, H3)+ZBIRKE (travanj! D3: D7, travanj! C3: C7, H3) |
