Preuzmite primjer radne knjige
Ovaj će vodič pokazati kako izračunati međuzbrojeve po grupama pomoću funkcije SUMIFS u Excelu i Google tablicama.
Tablica međuzbroja prema kategoriji ili skupini
Prvo ćemo pokazati kako stvoriti dinamičku sažetu tablicu međuzbroja iz raspona podataka u programu Excel 365 nadalje ili u Google tablicama.
Koristimo UNIQUE funkciju i SUMIFS funkciju za automatski međuzbroj Broj proizvoda po Grupa proizvoda:
| 1 | = ZBIRA (C3: C11, B3: B11, E3) |
Za izradu ove tablice međuzbroja koristimo standardnu primjenu funkcije SUMIFS za zbrajanje Broj proizvoda koji se međusobno podudaraju Grupa proizvoda. Međutim, prije nego što je to moguće, moramo stvoriti popis jedinstvenih Grupe proizvoda. Korisnici programa Microsoft Excel 365 i Google tablica imaju pristup funkciji UNIQUE za stvaranje dinamičkog popisa jedinstvenih vrijednosti iz raspona ćelija. U ovom primjeru u ćeliju E3 dodamo sljedeću formulu:
| 1 | = JEDINSTVENO (B3: B11) |
Kad se unese ova formula, ispod ćelije se automatski stvara popis koji prikazuje sve jedinstvene vrijednosti pronađene u Grupa proizvoda raspon podataka. U ovom primjeru popis se proširio na E3: E5 kako bi prikazao sva 3 jedinstvena Grupa proizvoda vrijednosti.
Ovo je funkcija dinamičkog niza u kojoj veličinu popisa rezultata ne treba definirati, a ona će se automatski smanjivati i rasti s promjenom vrijednosti ulaznih podataka.
Imajte na umu da u programu Excel 365 funkcija UNIQUE ne razlikuje velika i mala slova, ali u Google tablicama jest. Uzmite u obzir popis {“A”; "A"; "B"; “C”}. Izlaz UNIQUE funkcije ovisi o programu:
- {“A”; "B"; “C”} u programu Excel 365
- {“A”; "A"; "B"; “C”} u Google tablicama
Ako koristite Excel inačicu prije Excel 365, morat ćete imati drugačiji pristup. O tome se govori u sljedećem odjeljku.
Tablica međuzbroja prema kategoriji ili grupi - pre Excel 365
Ako koristite verziju programa Excel prije Excela 365, funkcija UNIQUE nije dostupna za upotrebu. Da biste ponovili isto ponašanje, možete kombinirati funkciju INDEX i funkciju MATCH s funkcijom COUNTIF kako biste stvorili formulu niza za izradu popisa jedinstvenih vrijednosti iz raspona ćelija:
| 1 | {= INDEKS ($ B $ 3: $ B $ 11, MATCH (0, COUNTIF ($ E $ 2: E2, $ B $ 3: $ B $ 11), 0))} |
Da bi ova formula funkcionirala, fiksne reference ćelija moraju biti pažljivo napisane, pri čemu funkcija COUNTIF upućuje na raspon $ E $ 2: E2, što je raspon koji počinje od E2 do ćelije iznad ćelije koja sadrži formulu.
Formulu je također potrebno unijeti kao formulu niza pritiskom na CTRL + SHIFT + ENTER nakon što je napisana. Ova formula je a Formula polja s 1 ćelijom, koje se zatim mogu kopirati zalijepiti u ćelije E4, E5 itd. Nemojte unositi ovo kao formulu niza za cijeli raspon E3: E5 u jednoj radnji.
Na isti način kao u prethodnom primjeru, funkcija SUMIFS tada se koristi za međuzbroj Broj proizvoda po Grupa proizvoda:
| 1 | = ZBIRA (C3: C11, B3: B11, E3) |
Zbroj prema kategoriji ili skupini - međuzbrojevi u podatkovnim tablicama
Kao alternativu gore prikazanoj metodi zbirne tablice, možemo dodati međuzbrojeve izravno u tablicu podataka. To ćemo pokazati korištenjem IF funkcija zajedno sa SUMIFS funkcijom za dodavanje a Međuzbroj po skupinama u izvornu tablicu podataka.
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Ovaj primjer koristi funkciju SUMIFS ugniježđenu unutar IF funkcije. Podijelimo primjer na korake:
Za dodavanje zbirne statistike izravno u podatkovnu tablicu možemo upotrijebiti funkciju SUMIFS. Počinjemo zbrajanjem Broj proizvoda koji odgovaraju relevantnim Grupa proizvoda:
| 1 | = ZBIRA (C3: C11, B3: B11, B3) |
Ova formula proizvodi međuzbrojnu vrijednost za svaki redak podataka. Za prikaz međuzbrojeva samo u prvom retku podataka svakog Grupa proizvoda, koristimo IF funkciju. Imajte na umu da se podaci moraju već sortirati prema Grupa proizvoda kako bi se osiguralo da su međuzbiri ispravno prikazani.
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
IF funkcija uspoređuje svaki redak podataka Grupa proizvoda vrijednost s retkom podataka iznad, a ako imaju istu vrijednost, prikazuje praznu ćeliju ("").
Ako je Grupa proizvoda vrijednosti su različite, prikazuje se zbroj. Na ovaj način, svaki Grupa proizvoda zbroj se prikazuje samo jednom (u retku prve instance).
Sortiranje skupova podataka po grupama
Ako podaci već nisu sortirani, još uvijek možemo koristiti istu formulu za međuzbir.
Gornji skup podataka nije razvrstan prema Grupa proizvoda, dakle Međuzbroj po skupinama stupac prikazuje svaki međuzbroj više puta. Kako bismo podatke dobili u željenom formatu, možemo odabrati podatkovnu tablicu i kliknuti "Poredaj od A do Z".
Zaključavanje referenci ćelija
Kako bismo naše formule učinili lakšim za čitanje, prikazali smo neke od formula bez zaključanih referenci ćelija:
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
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 | = IF (B3 = B2, "", SUMIFS ($ C $ 3: $ C $ 11, $ B $ 3: $ B $ 11, B3)) |
Pročitajte naš članak o zaključavanju referenci stanica da biste saznali više.
Korištenje zaokretnih tablica za prikaz međuzbrojeva
Kako bi se uklonio zahtjev za prethodno sortiranje podataka prema Grupa proizvoda, umjesto toga možemo upotrijebiti moć zaokretnih tablica za sažimanje podataka. Zaokretne tablice automatski izračunavaju međuzbrojeve i prikazuju ukupne i međuzbrojeve u nekoliko različitih formata.
Zbroj prema kategoriji ili grupi u Google tablicama
Ove formule rade isto u Google tablicama kao i u Excelu. Međutim, UNIQUE funkcija razlikuje velika i mala slova u Google tablicama.
