Excel formula: štejte vidne vrstice samo z merili -

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.

Dobre povezave

Pogovor MrExcel, z Mikeom Girvinom in Aladinom Akyurekom Mike Girvinov čarovniški trik 1010

Zanimive Članki...