Prenesite primer delovnega zvezka
Ta vadnica bo pokazala, kako izračunati "vmesni seštevek če", pri čemer se štejejo samo vidne vrstice s kriteriji.
SUBTOTAL funkcija
Funkcija SUBTOTAL lahko izvede različne izračune za vrsto podatkov (število, vsota, povprečje itd.). Najpomembneje je, da se lahko uporablja za izračun samo na vidnih (filtriranih) vrsticah. V tem primeru bomo funkcijo uporabljali za štetje (COUNTA) vidnih vrstic z nastavitvijo argumenta SUBTOTAL function_num na 3 (Celoten seznam možnih funkcij najdete tukaj.)
= SUBTOTAL (3, $ D $ 2: $ D $ 14)
Opazujte, kako se rezultati spreminjajo, ko ročno filtriramo vrstice.
SUBTOTAL IF
Če želite ustvariti "Vmesni seštevek Če", bomo v formuli matrike uporabili kombinacijo SUMPRODUCT, SUBTOTAL, OFFSET, ROW in MIN. S to kombinacijo lahko v bistvu ustvarimo generično funkcijo "SUBTOTAL IF". Pojdimo skozi primer.
Za vsak dogodek imamo seznam članov in njihov status udeležencev:
Predvidevamo, da moramo šteti število članov, ki so se dinamično udeležili dogodka, ko ročno filtriramo seznam tako:
Za to lahko uporabimo naslednjo formulo:
= SUMPRODUCT ((=)*(SUBTOTAL (3, OFFSET (, ROW ()-MIN (ROW ()), 0))))
= SUMPRODUCT ((D2: D14 = "Prisoten")*(SUBTOTAL (3, OFFSET (D2, ROW (D2: D14) -MIN (ROW (D2: D14)), 0))))
Ko uporabljate Excel 2022 in starejše, morate formulo matrike vnesti s pritiskom na CTRL + SHIFT + ENTER sporočiti Excelu, da vnašate formulo matrike. Vedeli boste, da je formula pravilno vnesena kot matrična formula, ko se okoli formule pojavijo kodrasti oklepaji (glej sliko zgoraj).
Kako deluje formula?
Formula deluje tako, da pomnoži dva niza v SUMPRODUCT -u, kjer prvo polje obravnava naša merila, drugo pa filtrira samo v vidne vrstice:
= PODROČJE (*)
Niz meril
Niz meril ovrednoti vsako vrstico v našem obsegu vrednosti (stanje »Udeležen« v tem primeru) in ustvari matriko, kot je ta:
=(=)
= (D2: D14 = "Udeležen")
Izhod:
{PRAV; FALSE; FALSE; PRAV; FALSE; TURE; TURE; TURE; FALSE; FALSE; PRAV; FALSE; PRAV}
Upoštevajte, da izhod v prvem nizu v naši formuli ne upošteva, ali je vrstica vidna ali ne, pri čemer nam pomaga druga matrika.
Niz vidnosti
Z uporabo SUBTOTAL za izključitev nevidnih vrstic v našem obsegu lahko generiramo niz vidljivosti. Vendar bo samo SUBTOTAL vrnil eno samo vrednost, medtem ko SUMPRODUCT pričakuje niz vrednosti. Če se želite tega izogniti, uporabljamo OFFSET za podajanje ene vrstice naenkrat. Ta tehnika zahteva napajanje polja OFFSET z enim številom naenkrat. Drugi niz izgleda takole:
= SUBTOTAL (3, OFFSET (, ROW ()-MIN (ROW ()), 0))
= SUBTOTAL (3, OFFSET (D2, ROW (D2: D14) -MIN (ROW (D2: D14)), 0))
Izhod:
{1;1;0;0;1;1}
Sestavljanje obeh skupaj:
= SUMPRODUCT ({TRUE; TRUE; FALSE; FALSE; TRUE; TRUE} * {1; 1; 0; 0; 1; 1})
= 4
SUBTOTAL IF z več merili
Če želite dodati več meril, preprosto več kriterijev skupaj v PODROČJU skupaj, takole:
= SUMPRODUCT ((=)*(=)*(SUBTOTAL (3, OFFSET (, ROW ()-MIN (ROW ()), 0))))
= SUMPRODUCT ((E2: E14 = "Obiskan")*(B2: B14 = 2019)*(SUBTOTAL (3, OFFSET (E2, ROW (E2: E14) -MIN (ROW (E2: E14)), 0)) ))
SUBTOTAL IF v Google Preglednicah
Funkcija SUBTOTAL IF deluje v Google Preglednicah popolnoma enako kot v Excelu:
