Üldine valem
=SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
Kokkuvõte
Ainult kriteeriumidega nähtavate ridade lugemiseks võite kasutada üsna keerukat valemit, mis põhineb summadel SUMPRODUCT, SUBTOTAL ja OFFSET. Näidatud näites on valem C12-s järgmine:
=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))
Eessõna
Funktsioon SUBTOTAL suudab hõlpsasti luua summasid ja loendeid varjatud ja varjatud ridade jaoks. Kuid see ei saa ilma mõne abita hakkama selliste kriteeriumidega nagu COUNTIF või SUMIF. Üheks lahenduseks on kasutada funktsiooni SUBTOTAL (OFFSET kaudu) ja kriteeriumide rakendamiseks SUMPRODUCT. Selle lähenemise üksikasju on kirjeldatud allpool.
Selgitus
Põhimõtteliselt töötab see valem, seadistades SUMPRODUCTi sisse kaks massiivi. Esimene massiiv rakendab kriteeriume ja teine massiiv käsitleb nähtavust:
=SUMPRODUCT(criteria*visibility)
Kriteeriume rakendatakse koos valemi osaga:
=(C5:C8=C10)
Mis loob sellise massiivi:
(FALSE;TRUE;FALSE;TRUE)
Kui TRUE tähendab "vastab kriteeriumidele". Pange tähele, et kuna me kasutame sellel massiivil korrutamist (*), konverteeritakse matemaatikaoperatsiooni abil TRUE FALSE väärtused automaatselt 1-ks ja 0-ks, nii et lõpuks jõuame:
(0;1;0;1)
Nähtavuse filtrit rakendatakse SUBTOTAL abil funktsiooniga 103.
SUBTOTAL suudab arvutuste käitamisel peidetud read välja jätta, nii et saame seda sel juhul kasutada "filtri" loomiseks, et välistada peidetud read SUMPRODUCTis. Probleem on aga selles, et SUBTOTAL tagastab ühe numbri, samas kui selle edukaks kasutamiseks SUMPRODUCT-is vajame hulga tulemusi. Trikk on see, et kasutatakse OFFSET-i, et toita SUBTOTAL üks viide rea kohta, nii et OFFSET tagastab ühe tulemuse rea kohta.
Muidugi nõuab see veel ühte nippi, milleks on anda OFFSETile massiiv, mis sisaldab ühte arvu rea kohta, alustades nullist. Teeme seda funktsiooniga ROW ülesehitatud avaldisega:
=ROW(C5:C8)-MIN(ROW(C5:C8)
mis genereerib sellise massiivi:
(0;1;2;3)
Kokkuvõtteks võib öelda, et teine massiiv (mis haldab nähtavust SUBTOTAL abil) genereeritakse järgmiselt:
=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)
Ja lõpuks on meil:
=SUMPRODUCT((0,1,0,1)*(1;0;1;1))
Mis tagastab 1.
Mitu kriteeriumi
Valemit saab laiendada mitme sellise kriteeriumi käsitlemiseks:
=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))
Tulemuste liitmine
Väärtuste summa tagastamiseks loenduse asemel saate valemi kohandada nii, et see sisaldaks summat.
=SUMPRODUCT(criteria*visibility*sumrange)
Kriteeriumid ja nähtavuse massiivid töötavad samamoodi nagu eespool selgitatud, välistades lahtrid, mis pole nähtavad. Kui vajate osalist sobitamist, saate avaldise ISNUMBER + SEARCH abil koostada, nagu siin selgitatud.
