Általános képlet
=SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
Összegzés
A látható sorok csak feltételekkel történő számlálásához használhat meglehetősen összetett képletet, amely a SUMPRODUCT, SUBTOTAL és OFFSET alapú. A bemutatott példában a C12 képlete a következő:
=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))
Előszó
A SUBTOTAL függvény könnyen generál összegeket és számlálásokat a rejtett és nem rejtett sorokhoz. Azonban nem képes kezelni olyan kritériumokat, mint a COUNTIF vagy a SUMIF, minden segítség nélkül. Az egyik megoldás a SUMPRODUCT használata a SUBTOTAL függvény (az OFFSET-en keresztül) és a kritériumok alkalmazásához. Ennek a megközelítésnek a részleteit az alábbiakban ismertetjük.
Magyarázat
Lényegében ez a képlet úgy működik, hogy két tömböt állít fel a SUMPRODUCT belsejében. Az első tömb kritériumokat alkalmaz, a második tömb pedig a láthatóságot kezeli:
=SUMPRODUCT(criteria*visibility)
A kritériumokat a képlet egy részével alkalmazzák:
=(C5:C8=C10)
Ami egy ilyen tömböt generál:
(FALSE;TRUE;FALSE;TRUE)
Ahol az IGAZ azt jelenti, hogy "megfelel a kritériumoknak". Megjegyzés: mivel ezen a tömbön szorzást (*) használunk, az IGAZ HAMIS értékeket a matematikai művelet automatikusan 1-re és 0-ra konvertálja, így végül:
(0;1;0;1)
A láthatósági szűrőt a SUBTOTAL, a 103-as számú funkcióval kell alkalmazni.
A SUBTOTAL képes a rejtett sorok kizárására a számítások futtatásakor, ezért ebben az esetben "szűrő" létrehozására használhatjuk a SUMPRODUCT belsejében található rejtett sorok kizárását. A probléma azonban az, hogy a SUBTOTAL egyetlen számot ad vissza, míg a SUMPRODUCT-en belüli eredményes használatához tömb eredményre van szükségünk. A trükk az, hogy az OFFSET segítségével soronként SUBTOTAL egy referenciát táplál, így az OFFSET soronként egy eredményt ad vissza.
Természetesen ehhez még egy trükkre van szükség, vagyis egy olyan tömb megadásához, amely soronként egy számot tartalmaz, nullával kezdve. Ezt a ROW függvényre épített kifejezéssel tesszük:
=ROW(C5:C8)-MIN(ROW(C5:C8)
amely egy ilyen tömböt generál:
(0;1;2;3)
Összefoglalva, a második tömb (amely a láthatóságot a SUBTOTAL használatával kezeli) így jön létre:
=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)
És végül:
=SUMPRODUCT((0,1,0,1)*(1;0;1;1))
Ami 1-et ad vissza.
Több kritérium
Kiterjesztheti a képletet, hogy több ilyen szempontot kezeljen:
=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))
Eredmények összegzése
Ha az értékek összegét visszaadja a számlálás helyett, a képletet úgy módosíthatja, hogy tartalmazzon egy összegtartományt:
=SUMPRODUCT(criteria*visibility*sumrange)
A kritériumok és a láthatósági tömbök a fentiekkel megegyezően működnek, kivéve a nem látható cellákat. Ha részleges egyeztetésre van szüksége, akkor kifejezést szerkeszthet az ISNUMBER + SEARCH használatával, az itt leírtak szerint.
