Obecný vzorec
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",rng1)) + ISNUMBER(SEARCH("rat",rng1)))>0),rng2)
souhrn
Chcete-li shrnout, zda buňky obsahují buď jeden textový řetězec, nebo jiný (tj. Obsahují „kočku“ nebo „krysu“), můžete použít funkci SUMPRODUCT společně s ISNUMBER + SEARCH nebo FIND. V zobrazeném příkladu je vzorec v buňce F5:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8)) + ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
který vrací součet hodnot v C4: C8, když buňky v B4: B8 obsahují buď „kočku“ nebo „krysu“.
Vysvětlení
Když sečtete buňky s kritérii „NEBO“, musíte dávat pozor, abyste nezdvojnásobili počet, pokud existuje možnost, že obě kritéria vrátí hodnotu true. V ukázaném příkladu chceme sečíst hodnoty ve sloupci C, když buňky ve sloupci B obsahují buď „kočku“ nebo „krysu“. SUMIF nemůžeme použít se dvěma kritérii, protože SUMIFS je založen na logice AND. A pokud se pokusíme použít dva SUMIFY (tj. SUMIFS + SUMIFS), zdvojnásobíme počet, protože existují buňky, které obsahují jak „kočka“, tak „krysa“.
Místo toho použijeme vzorec, jako je tento:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8))+ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
Toto jádro tohoto vzorce je založeno na zde vysvětleném vzorci, který lokalizuje text uvnitř buňky s ISNUMBER a SEARCH:
ISNUMBER(SEARCH("text",range)
Je-li daný rozsah buněk, tento fragment vrátí pole hodnot PRAVDA / NEPRAVDA, jedna hodnota pro každou buňku rozsahu. V tomto vzorci použijeme tento úryvek dvakrát, jednou pro „kočku“ a jednou pro „krysu“, takže dostaneme dvě pole. V tomto okamžiku máme:
=SUMPRODUCT(--(((TRUE;FALSE;TRUE;FALSE;FALSE)+ (TRUE;FALSE;TRUE;TRUE;FALSE))>0),C4:C8)
Dále přidáme tato pole dohromady, protože v logické algebře se pro logiku OR používá sčítání. Matematická operace automaticky vynucuje hodnoty PRAVDA a NEPRAVDA na 1 s a 0 s, takže skončíme s níže uvedeným polem:
=SUMPRODUCT(--(((2;0;2;1;0))>0),C4:C8)
Každé číslo v tomto poli je výsledkem přidání hodnot PRAVDA a NEPRAVDA v původních dvou polích dohromady. V zobrazeném příkladu vypadá pole takto:
(2;0;2;1;0)
Musíme tato čísla sečíst, ale nechceme zdvojnásobit počet. Musíme se tedy ujistit, že každá hodnota větší než nula se započítá jen jednou. Za tímto účelem vynucujeme všechny hodnoty na hodnotu PRAVDA nebo NEPRAVDA kontrolou pole pomocí „> 0“. Tím se vrátí PRAVDA / NEPRAVDA:
=SUMPRODUCT(--((TRUE;FALSE;TRUE;TRUE;FALSE)),C4:C8)
Který pak převedeme na 1/0 pomocí dvojitého záporného čísla (-):
=SUMPRODUCT((1;0;1;1;0),C4:C8)
a nakonec:
=SUMPRODUCT((1;0;1;1;0),(20;15;30;20;10))
SUMPRODUCT znásobí odpovídající prvky dvou polí dohromady a sečte výsledek a vrátí 70.
Možnost rozlišovat velká a malá písmena
Funkce SEARCH ignoruje velká a malá písmena. Pokud potřebujete citlivou volbu, nahraďte SEARCH funkcí FIND.
