Preuzmite primjer radne knjige
Ovaj će vodič pokazati kako izračunati "međuzbir ako", računajući samo vidljive retke s kriterijima.
SUBTOTAL funkcija
Funkcija SUBTOTAL može izvesti različite izračune na nizu podataka (broj, zbroj, prosjek itd.). Ono što je najvažnije, može se koristiti za izračunavanje samo na vidljivim (filtriranim) redovima. U ovom primjeru koristit ćemo funkciju za brojanje (COUNTA) vidljivih redaka postavljanjem argumenta SUBTOTAL function_num na 3 (Cijeli popis mogućih funkcija možete pronaći ovdje.)
= SUBTOTAL (3, $ D $ 2: $ D $ 14)
Primijetite kako se rezultati mijenjaju dok ručno filtriramo retke.
SUBTOTAL AKO
Da bismo stvorili "međuzbir ako", koristit ćemo kombinaciju SUMPRODUCT, SUBTOTAL, OFFSET, ROW i MIN u formuli niza. Pomoću ove kombinacije možemo u biti stvoriti generičku funkciju „SUBTOTAL IF“. Prođimo kroz primjer.
Za svaki događaj imamo popis članova i njihov status posjećenosti:
Pretpostavimo da se od nas traži da dinamički brojimo broj članova koji su prisustvovali događaju dok ručno filtriramo popis na sljedeći način:
Da bismo to postigli, možemo upotrijebiti ovu formulu:
= SUMPRODUCT ((=)*(SUBTOTAL (3, OFFSET (, ROW ()-MIN (ROW ()), 0))))
= SUMPRODUCT ((D2: D14 = "Prisustvovao")*(SUBTOTAL (3, OFFSET (D2, ROW (D2: D14) -MIN (ROW (D2: D14)), 0))))
Kada koristite Excel 2022 i starije verzije, morate unijeti formulu polja pritiskom na CTRL + SHIFT + ENTER da biste Excelu rekli da unosite formulu polja. Znat ćete da je formula ispravno unesena kao formula niza kada se oko formule pojave kovrčave zagrade (pogledajte gornju sliku).
Kako funkcionira formula?
Formula funkcionira množenjem dva niza unutar SUMPRODUCT -a, gdje se prvi niz bavi našim kriterijima, a drugi filtrira samo u vidljive retke:
= SUMPRODUCT (*)
Niz kriterija
Niz kriterija procjenjuje svaki redak u našem rasponu vrijednosti (status "Prisutan" u ovom primjeru) i generira niz ovako:
=(=)
= (D2: D14 = "Prisustvovao")
Izlaz:
{PRAVI; FALSE; FALSE; PRAVI; FALSE; TURE; TURE; TURE; FALSE; FALSE; PRAVI; FALSE; PRAVI}
Imajte na umu da izlaz u prvom nizu u našoj formuli zanemaruje je li redak vidljiv ili ne, a tu nam pomaže drugi niz.
Niz vidljivosti
Koristeći SUBTOTAL za isključivanje nevidljivih redaka u našem rasponu, možemo generirati naš niz vidljivosti. Međutim, samo SUBTOTAL će vratiti jednu vrijednost, dok SUMPRODUCT očekuje niz vrijednosti. Da bismo to zaobišli, koristimo OFFSET za prolaz jedan po jedan red. Ova tehnika zahtijeva unošenje OFFSET -a niza koji sadrži jedan broj odjednom. Drugi niz izgleda ovako:
= SUBTOTAL (3, OFFSET (, ROW ()-MIN (ROW ()), 0))
= SUBTOTAL (3, OFFSET (D2, ROW (D2: D14) -MIN (ROW (D2: D14)), 0))
Izlaz:
{1;1;0;0;1;1}
Spajanje njih dvoje zajedno:
= SUMPRODUCT ({TRUE; TRUE; FALSE; FALSE; TRUE; TRUE} * {1; 1; 0; 0; 1; 1})
= 4
SUBTOTAL AKO s više kriterija
Da biste dodali više kriterija, jednostavno više više kriterija zajedno u SUMPRODUCT -u, na sljedeći način:
= SUMPRODUCT ((=)*(=)*(SUBTOTAL (3, OFFSET (, ROW ()-MIN (ROW ()), 0)))))
= SUMPRODUCT ((E2: E14 = "Prisustvovao")*(B2: B14 = 2019)*(SUBTOTAL (3, OFFSET (E2, ROW (E2: E14) -MIN (ROW (E2: E14)), 0)) ))
SUBTOTAL AKO u Google tablicama
SUBTOTAL IF funkcija radi potpuno isto u Google tablicama kao i u Excelu:
