Obecný vzorec
=SUMPRODUCT(--((ISNUMBER(FIND("abc",rng)) + ISNUMBER(FIND("def",rng)))>0))
souhrn
Chcete-li spočítat buňky, které obsahují buď x nebo y, můžete použít vzorec založený na funkci SUMPRODUCT. V zobrazeném příkladu je vzorec v buňce F5:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11))+ISNUMBER(FIND("def",B5:B11)))>0))
Toto je jednobuněčné formule řešení, vysvětleno níže. Je také možné použít jednodušší vzorec založený na pomocném sloupci, který je také vysvětlen níže.
Vysvětlení
Když počítáte buňky s logikou „NEBO logikou“, musíte dávat pozor, abyste počet nezdvojnásobili. Například pokud počítáte buňky, které obsahují „abc“ nebo „def“, nemůžete jen přidat dohromady dvě funkce COUNTIF, protože můžete zdvojnásobit počet buněk, které obsahují jak „abc“, tak „def“.
Jednobuněčné řešení
Pro jeden vzorec můžete použít SUMPRODUCT s ISNUMBER + FIND. Vzorec v F5 je:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11)) + ISNUMBER(FIND("def",B5:B11)))>0))
Tento vzorec je založen na zde vysvětleném vzorci, který umisťuje text uvnitř buňky:
ISNUMBER(FIND("abc",B5:B11)
Je-li daný rozsah buněk, tento fragment vrátí pole hodnot PRAVDA / NEPRAVDA, jedna hodnota pro každou buňku rozsahu. Protože toto používáme dvakrát (jednou pro „abc“ a jednou pro „def“), získáme dvě pole.
Dále přidáme tato pole dohromady (s +), což vytvoří nové jediné pole čísel. 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á výsledné pole takto:
(2;0;2;0;1;0;2)
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 PRAVDU nebo NEPRAVDA s „> 0“, poté vynucujeme na 1/0 s dvojitým záporem (-).
Nakonec SUMPRODUCT vrátí součet všech hodnot v poli.
Pomocné kolonové řešení
Díky pomocnému sloupci ke kontrole každé buňky zvlášť je problém méně složitý. Můžeme použít COUNTIF se dvěma hodnotami (poskytované jako „konstanta pole“). Vzorec v C5 je:
=--(SUM(COUNTIF(B5,("*abc*","*def*")))>0)
COUNTIF vrátí pole, které obsahuje dvě položky: počet pro „abc“ a počet pro „def“. Abychom zabránili dvojímu započítání, přidáme položky nahoru a poté vynutíme výsledek na hodnotu PRAVDA / NEPRAVDA pomocí „> 0“. Nakonec převedeme hodnoty TRUE / FALSE na 1 a 0 s dvojitým záporem (-).
Konečný výsledek je buď 1 nebo 0 pro každou buňku. Chcete-li získat součet za všechny buňky v rozsahu, jednoduše sečtěte pomocný sloupec.
