
Splošna formula
=SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
Povzetek
Če želite šteti vidne vrstice samo z merili, lahko uporabite precej zapleteno formulo, ki temelji na SUMPRODUCT, SUBTOTAL in OFFSET. V prikazanem primeru je formula v C12:
=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))
Predgovor
Funkcija SUBTOTAL lahko enostavno ustvari vsote in štetje za skrite in neskrite vrstice. Vendar ne more obvladati meril, kot sta COUNTIF ali SUMIF, brez pomoči. Ena od rešitev je uporaba SUMPRODUCT za uporabo funkcije SUBTOTAL (prek OFFSET) in meril. Podrobnosti tega pristopa so opisane spodaj.
Pojasnilo
V osnovi ta formula deluje tako, da znotraj SUMPRODUCT nastavi dva niza. Prva matrika uporablja merila, druga matrika pa obravnava vidnost:
=SUMPRODUCT(criteria*visibility)
Merila se uporabljajo z delom formule:
=(C5:C8=C10)
Kar ustvari matriko, kot je ta:
(FALSE;TRUE;FALSE;TRUE)
Kjer TRUE pomeni "izpolnjuje merila". Upoštevajte, ker na tej matriki uporabljamo množenje (*), vrednosti TRUE FALSE pa se z matematično operacijo samodejno pretvorijo v 1 in 0, zato dobimo:
(0;1;0;1)
Filter vidnosti se uporabi s SUBTOTAL s številko funkcije 103.
SUBTOTAL lahko med izvajanjem izračunov izloči skrite vrstice, zato ga lahko v tem primeru uporabimo za ustvarjanje "filtra" za izključitev skritih vrstic znotraj SUMPRODUCT. Težava pa je v tem, da SUBTOTAL vrne eno številko, medtem ko za uspešno uporabo znotraj SUMPRODUCT potrebujemo vrsto rezultatov. Trik je v tem, da uporabite OFFSET za podajanje SUBTOTAL enega sklica na vrstico, tako da bo OFFSET vrnil en rezultat na vrstico.
Seveda to zahteva še en trik, to je, da OFFSET dobimo matriko, ki vsebuje eno številko na vrstico, začenši z nič. To naredimo z izrazom, ki temelji na funkciji ROW:
=ROW(C5:C8)-MIN(ROW(C5:C8)
ki bo ustvaril matriko, kot je ta:
(0;1;2;3)
Če povzamemo, je druga matrika (ki obravnava vidnost s SUBTOTAL) ustvarjena takole:
=SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0)) =SUBTOTAL(103,OFFSET(C5,(0;1;2;3),0)) =SUBTOTAL(103,("East";"West";"Midwest";"West")) =(1;0;1;1)
In končno imamo:
=SUMPRODUCT((0,1,0,1)*(1;0;1;1))
Kar vrne 1.
Več meril
Formulo lahko razširite na več kriterijev, kot je ta:
=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))
Seštevanje rezultatov
Če želite vrniti vsoto vrednosti namesto števila, lahko prilagodite formulo tako, da vključuje obseg vsote:
=SUMPRODUCT(criteria*visibility*sumrange)
Merila in polja vidnosti delujejo enako kot zgoraj, razen celic, ki niso vidne. Če potrebujete delno ujemanje, lahko sestavite izraz s pomočjo ISNUMBER + SEARCH, kot je razloženo tukaj.