Obecný vzorec
=COUNTIF(range,"*txt*")
souhrn
Chcete-li spočítat buňky, které obsahují určitý text, můžete použít funkci COUNTIF se zástupným znakem. V zobrazeném příkladu je vzorec v E5:
=COUNTIF(B5:B15,"*a*")
Vysvětlení
Funkce COUNTIF počítá buňky v rozsahu, který splňuje kritéria. Chcete-li například spočítat počet buněk v rozsahu, který obsahuje písmeno „a“, můžete použít:
=COUNTIF(range,"a") // exact match
Mějte však na paměti, že se jedná o přesnou shodu. Aby bylo možné buňku spočítat, musí obsahovat přesně „a“. Pokud buňka obsahuje jakékoli další znaky, nebude se počítat.
U zobrazeného problému chceme spočítat buňky, které obsahují konkrétní text, což znamená, že text může být kdekoli v buňce. K tomu potřebujeme jako zástupný znak použít hvězdičku (*). Chcete-li například spočítat buňky, které obsahují písmeno „a“ kdekoli, kde používáme:
=COUNTIF(range,"*a*")
Hvězdička ve vzorci znamená „shoda s libovolným počtem znaků, včetně nuly“, takže tento vzor bude počítat jakoukoli buňku, která obsahuje „a“ v jakékoli pozici, s dalšími znaky vlevo nebo vpravo nebo bez nich. Všechny vzorce použité v příkladu mají stejný vzorec:
=COUNTIF(B5:B15,"*a*") // contains "a" =COUNTIF(B5:B15,"*2*") // contains "2" =COUNTIF(B5:B15,"*-S*") // contains "-s" =COUNTIF(B5:B15,"*x*") // contains "x"
Poznámka: COUNTIF nerozlišuje velká a malá písmena.
S odkazem na buňku
Tento vzorec můžete snadno upravit a použít pro kritéria obsah jiné buňky. Například pokud A1 obsahuje text, který chcete porovnat, můžete použít:
=COUNTIF(range,"*"&A1&"*")
Spočítat konkrétní čísla
Výše uvedený vzorec COUNTIF nebude fungovat, pokud hledáte konkrétní číslo a buňky obsahují číselná data. Důvodem je, že zástupný znak automaticky způsobí, že COUNTIF bude hledat pouze text (tj. Místo „2“ bude hledat „2“). Protože textová hodnota nebude nikdy nalezena ve skutečném čísle, COUNTIF vrátí nulu.
Chcete-li spočítat konkrétní číslo v číselném obsahu, můžete použít jiný vzorec založený na funkci SEARCH a funkci ISNUMBER takto:
=SUMPRODUCT(--(ISNUMBER(SEARCH(text,range))))
V tomto vzorci Text je text, který hledáte, a rozsah je oblast buněk, které chcete spočítat. Když SEARCH najde shodu, vrátí číslo a funkce ISNUMBER převede číslo na hodnotu TRUE nebo FALSE. Protože kontrolujeme více než jednu buňku, výsledkem bude pole nebo hodnoty PRAVDA a NEPRAVDA. Dvojitý zápor se používá k převodu hodnot TRUE a FALSE na 1 s a 0 s a funkce SUMPRODUCT vrátí součet pole jako konečný výsledek.
SEARCH automaticky zachází se všemi čísly jako s textem, takže najde například 1 v čísle 201. Rovněž není třeba používat zástupné znaky k označení polohy, protože SEARCH automaticky prohledá celý text v buňce.
Verze rozlišující velká a malá písmena
U počtu malých a velkých písmen můžete použít vzorec založený na funkci FIND:
=SUMPRODUCT(--(ISNUMBER(FIND(text,range))))
Zde se místo SEARCH používá funkce FIND, protože FIND rozlišuje velká a malá písmena. V ostatních ohledech vzorec funguje jako výše popsaný vzorec ISNUMBER + FIND.
