Všeobecný vzorec
=SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
Zhrnutie
Ak chcete počítať viditeľné riadky iba podľa kritérií, môžete použiť pomerne zložitý vzorec založený na SUMPRODUCT, SUBTOTAL a OFFSET. V zobrazenom príklade je vzorec v C12:
=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))
Predslov
Funkcia SUBTOTAL môže ľahko generovať sumy a počty skrytých a neskrytých riadkov. Bez pomoci však nedokáže spracovať kritériá ako COUNTIF alebo SUMIF. Jedným z riešení je použiť SUMPRODUCT na použitie funkcie SUBTOTAL (cez OFFSET) aj kritérií. Podrobnosti tohto prístupu sú opísané nižšie.
Vysvetlenie
Tento vzorec funguje v jadre nastavením dvoch polí vo vnútri SUMPRODUCT. Prvé pole uplatňuje kritériá a druhé pole zaisťuje viditeľnosť:
=SUMPRODUCT(criteria*visibility)
Kritériá sa uplatňujú s časťou vzorca:
=(C5:C8=C10)
Ktoré generuje pole ako je toto:
(FALSE;TRUE;FALSE;TRUE)
Kde TRUE znamená „spĺňa kritériá“. Všimnite si, že pretože v tomto poli používame násobenie (*), budú hodnoty TRUE FALSE automaticky prevedené na 1 a 0 matematickou operáciou, takže nakoniec skončíme s:
(0;1;0;1)
Filter viditeľnosti sa aplikuje pomocou SUBTOTAL s funkciou číslo 103.
SUBTOTAL dokáže pri vykonávaní výpočtov vylúčiť skryté riadky, takže ho v tomto prípade môžeme použiť na vygenerovanie „filtra“ na vylúčenie skrytých riadkov vo vnútri SUMPRODUCT. Problém však je, že SUBTOTAL vráti jediné číslo, zatiaľ čo na úspešné použitie v SUMPRODUCT potrebujeme množstvo výsledkov. Trik spočíva v použití funkcie OFFSET na napájanie SUBTOTAL jednej referencie na riadok, takže funkcia OFFSET vráti jeden výsledok na riadok.
To si samozrejme vyžaduje ďalší trik, ktorým je dať OFFSETU pole, ktoré obsahuje jedno číslo na riadok, počnúc nulou. Robíme to s výrazom postaveným na funkcii ROW:
=ROW(C5:C8)-MIN(ROW(C5:C8)
ktorý vygeneruje pole ako je toto:
(0;1;2;3)
Stručne povedané, druhé pole (ktoré spracováva viditeľnosť pomocou SUBTOTAL) sa generuje takto:
=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)
A nakoniec máme:
=SUMPRODUCT((0,1,0,1)*(1;0;1;1))
Čo vráti 1.
Viaceré kritériá
Vzorec môžete rozšíriť tak, aby obsahoval viac kritérií, napríklad toto:
=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))
Sčítanie výsledkov
Ak chcete namiesto počtu vrátiť súčet hodnôt, môžete vzorec upraviť tak, aby zahŕňal rozsah súčtu:
=SUMPRODUCT(criteria*visibility*sumrange)
Polia kritérií a viditeľnosti fungujú rovnako, ako je vysvetlené vyššie, s výnimkou buniek, ktoré nie sú viditeľné. Ak potrebujete čiastočnú zhodu, môžete skonštruovať výraz pomocou ISNUMBER + SEARCH, ako je vysvetlené tu.
