Obecný vzorec
=SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
souhrn
Chcete-li počítat viditelné řádky pouze s kritérii, můžete použít poměrně složitý vzorec založený na SUMPRODUCT, SUBTOTAL a OFFSET. V zobrazeném příkladu je vzorec v C12:
=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))
Předmluva
Funkce SUBTOTAL může snadno generovat součty a počty skrytých a skrytých řádků. Bez pomoci však nedokáže zpracovat kritéria jako COUNTIF nebo SUMIF. Jedním z řešení je použít SUMPRODUCT k použití funkce SUBTOTAL (přes OFFSET) a kritérií. Podrobnosti tohoto přístupu jsou popsány níže.
Vysvětlení
V podstatě tento vzorec funguje tak, že uvnitř SUMPRODUCT nastavíte dvě pole. První pole aplikuje kritéria a druhé pole zpracovává viditelnost:
=SUMPRODUCT(criteria*visibility)
Kritéria se používají s částí vzorce:
=(C5:C8=C10)
Který generuje pole, jako je tento:
(FALSE;TRUE;FALSE;TRUE)
Kde TRUE znamená „splňuje kritéria“. Všimněte si, že na tomto poli používáme multiplikaci (*), hodnoty TRUE FALSE budou automaticky převedeny na 1 a 0 pomocí matematické operace, takže skončíme s:
(0;1;0;1)
Filtr viditelnosti se aplikuje pomocí SUBTOTAL s funkcí číslo 103.
SUBTOTAL dokáže při provádění výpočtů vyloučit skryté řádky, takže jej v tomto případě můžeme použít ke generování "filtru" pro vyloučení skrytých řádků uvnitř SUMPRODUCT. Problém však spočívá v tom, že SUBTOTAL vrací jedno číslo, zatímco k úspěšnému použití uvnitř SUMPRODUCT potřebujeme řadu výsledků. Trik spočívá v použití OFSETU ke krmení SUBTOTAL jedné reference na řádek, takže OFFSET vrátí jeden výsledek na řádek.
To samozřejmě vyžaduje další trik, kterým je dát OFFSET pole, které obsahuje jedno číslo na řádek, počínaje nulou. Děláme to s výrazem postaveným na funkci ROW:
=ROW(C5:C8)-MIN(ROW(C5:C8)
který vygeneruje pole takto:
(0;1;2;3)
Stručně řečeno, druhé pole (které zpracovává viditelnost pomocí SUBTOTAL) je generováno 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 konečně máme:
=SUMPRODUCT((0,1,0,1)*(1;0;1;1))
Který vrací 1.
Několik kritérií
Vzorec můžete rozšířit tak, aby zpracovával několik kritérií, jako je tato:
=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))
Souhrnné výsledky
Chcete-li namísto počtu vrátit součet hodnot, můžete vzorec upravit tak, aby zahrnoval rozsah součtu:
=SUMPRODUCT(criteria*visibility*sumrange)
Kritéria a pole viditelnosti fungují stejně, jak bylo vysvětleno výše, kromě buněk, které nejsou viditelné. Pokud potřebujete částečnou shodu, můžete vytvořit výraz pomocí ISNUMBER + SEARCH, jak je vysvětleno zde.
