Funkce Excel COUNTIF umožňuje spočítat počet buněk, které splňují určitá kritéria nebo podmínky v daném rozsahu.
Funkce COUNTIF je jednou ze statistických funkcí v Excelu, která je kombinací funkcí COUNT a IF nebo funkce COUNTA. Při použití ve vzorci počítá funkce počet buněk, které odpovídají konkrétním kritériím nebo podmínkám ve stejném nebo více rozsazích. Funkce COUNTIF pomáhá spočítat buňky obsahující text, čísla nebo data, která splňují určitá kritéria.
Buňky můžete počítat pomocí funkcí COUNTIF nebo COUNTIFS v Excelu. Rozdíl mezi funkcemi COUNTIF a COUNTIFS je v tom, že COUNTIF se používá pro počítání buněk, které splňují jedno kritérium v jednom rozsahu, zatímco COUNTIFS počítá buňky, které splňují více podmínek ve stejném nebo více rozsazích.
Tento článek vám ukáže, jak používat dvě funkce COUNTIF a COUNTIFS v Excelu.
Funkce Excel COUNTIF
Funkce COUNTIF umožňuje provádět počítání dat na základě konkrétního kritéria nebo podmínky. Podmínka použitá ve funkci pracuje s logickými operátory (, , =, >=, <=) a zástupnými znaky (*, ?) pro částečnou shodu.
Syntaxe funkce COUNTIF
Struktura funkce COUNTIF je:
=COUNTIF(rozsah,kritéria)Parametry:
rozsah– Rozsah buněk, které se mají spočítat.kritéria– Podmínka určuje, které buňky by měly být zahrnuty do počtu v určeném rozsahu. Kritéria mohou být číselná hodnota, text, odkaz na adresu buňky nebo rovnice.
Použití funkce COUNTIF k počítání číselných hodnot
Jak jsme probrali výše, kritéria (druhý argument) ve funkci COUNTIF definují podmínku, která funkci říká, které buňky má počítat.
Tato funkce vám pomůže spočítat počet buněk s hodnotami, které splňují logické podmínky, jako je rovno, větší než, menší nebo nerovnající se zadané hodnotě atd.
V níže uvedeném příkladu vzorec počítá buňky, které obsahují hodnotu rovnou 5 (kritéria). Do vzorce můžete přímo vložit „5“ nebo použít odkaz na adresu buňky, která má hodnotu (buňka D2 v příkladu níže).
=COUNTIF(B2:B11;D2)
Výše uvedený vzorec počítá počet buněk v oblasti buněk (B2:B11), které obsahují hodnotu rovnou hodnotě v buňce D2.
Následující vzorec počítá buňky, které mají hodnotu menší než 5.
=COUNTIF(B2:B11,"<5")Operátor menší než (<) říká vzorci, aby počítal buňky s hodnotou menší než „5“ v rozsahu B2:B11. Kdykoli v podmínce použijete operátor, nezapomeňte jej uzavřít do dvojitých uvozovek („“).
Někdy, když chcete spočítat buňky tak, že je porovnáte s kritériem (hodnotou) v buňce. V takových případech vytvořte kritérium spojením operátoru a odkazu na buňku. Když to uděláte, musíte operátor porovnání uzavřít do dvojitých uvozovek (“”) a poté mezi operátor porovnání a odkaz na buňku umístit ampersand (&).
=COUNTIF(B2:B11,">="&D2)
Níže uvedený obrázek ukazuje několik příkladů vzorců a jejich výsledek.
Použití funkce COUNTIF k počítání textových hodnot
Chcete-li spočítat buňky obsahující určité textové řetězce, použijte tento textový řetězec jako argument kritéria nebo buňku obsahující textový řetězec. Například v níže uvedené tabulce, pokud chceme spočítat všechny buňky v rozsahu (B21:D27) s textovou hodnotou v buňce B21 (sam), můžeme použít následující vzorec:
=COUNTIF(B21:D27;B21)
Jak jsme diskutovali dříve, mohli bychom buď použít text „sam“ přímo ve vzorci, nebo použít odkaz na buňku, který má kritéria (B21). Textový řetězec by měl být vždy uzavřen do dvojitých uvozovek (“”), pokud je použit ve vzorci v Excelu.
=COUNTIF(B21:D27"sam")
Chcete-li spočítat buňky, které neobsahují zadaný text, použijte níže uvedený vzorec:
=COUNTIF(B21:D27,""&B21)Ujistěte se, že jste připojili „není rovno“ "" operátor ve dvojitých uvozovkách.
Pokud používáte text „sam“ přímo ve vzorci, musíte uzavřít operátor „“ a textový řetězec dohromady ("sam") ve dvojitých uvozovkách.
=COUNTIF(B21:D27"sam") 
Použití zástupných znaků ve funkci COUNTIF aplikace Excel (částečná shoda)
Vzorec COUNTIF se zástupnými znaky můžete použít k počítání buněk, které obsahují konkrétní slovo, frázi nebo písmena. Ve funkci Excel COUNTIF můžete použít tři zástupné znaky:
*(hvězdička) – Používá se k počítání buněk s libovolným počtem počátečních a koncových znaků/písmen. (např. St* může znamenat Stark, Stork, Stacks atd.?(otazník) – Používá se k vyhledání buněk s libovolným jedním znakem. (např. St?rk může znamenat Stark nebo Stork.~(tilda) – Slouží k vyhledání a sčítání počtu buněk obsahujících otazník nebo hvězdičku (~, *, ?) v textu.
Počítání buněk začínajících nebo končících určitými znaky
Chcete-li spočítat buňky, které začínají nebo končí určitým textem s libovolným počtem dalších znaků v buňce, použijte zástupný znak hvězdička (*) v druhém argumentu funkce COUNTIF.
Použijte tento vzorový vzorec:
=COUNTIF(A1:A10"A*") – k počítání buněk, které začínají na „A“.
=COUNTIF(A19:A28,"*er") – spočítat počet buněk, které končí znaky „er“.
=COUNTIF(A2:A12,"*QLD*") – pro počítání buněk, které obsahují text „QLD“ kdekoli v textovém řetězci.
A ? představuje přesně jeden znak, použijte tento zástupný znak ve funkci COUNTIF níže, abyste spočítali počet buněk, které obsahují přesně +1 znak, kde ‘?' se používá.
=COUNTIF(A1:A10,"Par?s")
Počítání prázdných a neprázdných buněk pomocí funkce COUNTIF
Vzorec COUNTIF je také užitečný, pokud jde o počítání počtu prázdných nebo neprázdných buněk v daném rozsahu.
Počítat neprázdné buňky
Pokud chcete počítat pouze buňky, které obsahují jakékoli „textové“ hodnoty, použijte níže uvedený vzorec. Tento vzorec považuje buňky s daty a čísly za prázdné buňky a nezahrnuje je do počítání.
=COUNTIF(A1:B12,"*")Zástupný znak * odpovídá pouze textovým hodnotám a vrací počet všech textových hodnot v daném rozsahu.
Pokud chcete spočítat všechny neprázdné buňky v daném rozsahu, zkuste tento vzorec:
=COUNTIF(A1:B12,"")
Počítat prázdné buňky
Pokud chcete spočítat prázdné buňky v určitém rozsahu, použijte funkci COUNTIF s * zástupný znak a operátor v argumentu kritéria pro počítání prázdných buněk.
Tento vzorec počítá buňky, které neobsahují žádné textové hodnoty:
=COUNTIF(A1:B12,""&"*")Od té doby * se zástupný znak shoduje s jakoukoli textovou hodnotou, výše uvedený vzorec spočítá všechny buňky, které se nerovnají *. Počítá také buňky s daty a čísly jako prázdná místa.
Počítání všech prázdných míst (všech typů hodnot):
=COUNTIF(A1:B12,"")Tato funkce počítá pouze prázdné buňky v rozsahu.
Použití funkce COUNTIF k počítání dat
Buňky můžete počítat s daty (stejně jako u číselných kritérií), které splňují logickou podmínku nebo zadané datum či datum v referenční buňce.
K počítání buněk, které obsahují zadané datum (05-05-2020), bychom použili tento vzorec:
=COUNTIF(B2:B10,"05-05-2020")
Můžete také zadat datum v různých formátech jako kritéria ve funkci COUNTIF, jak je uvedeno níže:
Pokud chcete spočítat buňky, které obsahují data před nebo po určitém datu, použijte operátory menší než (před) nebo větší než (za) spolu s konkrétním datem nebo odkazem na buňku.
=COUNTIF(B2:B10,">=05/05/2020")
Můžete také použít odkaz na buňku, který obsahuje datum, a to tak, že jej zkombinujete s operátorem (v uvozovkách).
Chcete-li spočítat počet buněk v rozsahu A2:A14 s datem před datem v E3, použijte níže uvedený vzorec, kde operátor větší než (<) znamená před datem v E3.
=COUNTIF(A2:A14,"<"&E3)
Několik příkladů vzorců a jejich výsledek:
Datum počítání na základě aktuálního data
Funkci COUNTIF můžete zkombinovat se specifickými funkcemi aplikace Excel Date, tj. TODAY(), abyste spočítali buňky, které mají aktuální datum.
=COUNTIF(A2:A14,">"&DNES())Tato funkce spočítá všechna data ode dneška v rozsahu (A2:A14).
Počítání dat mezi konkrétním časovým obdobím
Pokud chcete spočítat všechna data mezi dvěma daty, musíte ve vzorci použít dvě kritéria.
Můžeme to udělat pomocí dvou metod: funkcí COUNTIF a COUNTIFS.
Použití funkce Excel COUNTIF
Chcete-li spočítat všechna data mezi dvěma zadanými daty, musíte použít dvě funkce COUNTIF.
Chcete-li počítat data mezi '09-02-2020′ a '20-08-2021′, použijte tento vzorec:
=COUNTIF(A2:A14,">09-02-2020")-COUNTIF(A2:A14,">20-08-2021")Tento vzorec nejprve zjistí počet buněk, které mají datum po 2. únoru, a odečte počet buněk s daty po 20. srpnu. Nyní dostaneme číslo. buněk, které mají data po 2. únoru a 20. srpna nebo dříve (počet je 9).
Pokud nechcete, aby vzorec počítal jak 2. únor, tak 20. srpen, použijte tento vzorec:
=COUNTIF(A2:A14,">09-02-2020")-COUNTIF(A2:A14,">=20-08-2021")Stačí nahradit operátor „>“ znakem „>=“ ve druhém kritériu.
Použití funkce Excel COUNTIFS
Funkce COUNTIFS také podporuje více kritérií a na rozdíl od funkce COUNTIF počítá buňky až po splnění všech podmínek. Pokud chcete spočítat buňky se všemi daty mezi dvěma zadanými daty, zadejte tento vzorec:
=COUNTIFS(A2:A14;">"&A11;A2:A14,"<"&A10)
Chcete-li do počítání zahrnout i zadaná data, použijte operátory „>=“ a „<=“. Zde postupujte podle tohoto vzorce:
=COUNTIFS(A2:A14,">=09-02-2020",A2:A14,"<=20-08-2021")V tomto příkladu jsme místo odkazu na buňku použili datum přímo v kritériích.
Jak zacházet s COUNTIF a COUNTIFS s více kritérii v Excelu
Funkce COUNTIF se většinou používá pro počítání buněk s jedním kritériem (podmínkou) v jednom rozsahu. Stále však můžete použít COUNTIF k počítání buněk, které odpovídají více podmínkám ve stejném rozsahu. Funkci COUNTIFS však lze použít k počítání buněk, které splňují více podmínek ve stejném nebo různém rozsahu.
Jak počítat čísla v rozsahu
Buňky obsahující čísla mezi dvěma určenými čísly můžete počítat pomocí dvou funkcí: COUNTIF a COUNTIFS.
COUNTIF pro počítání čísel mezi dvěma čísly
Jedním z běžných použití funkce COUNTIF s více kritérii je počítání čísel mezi dvěma určenými čísly, např. k počítání čísel větších než 10, ale menších než 50. Chcete-li počítat čísla v rozsahu, spojte dvě nebo více funkcí COUNTIF do jednoho vzorce. Ukážeme vám, jak na to.
Řekněme, že chcete počítat buňky v rozsahu B2:B9, kde je hodnota větší než 10 a menší než 21 (kromě 10 a 21), použijte tento vzorec:
=COUNTIF(B2:B14;">10")-COUNTIF(B2:B14,">=21")Rozdíl mezi dvěma čísly se zjistí odečtením jednoho vzorce od druhého. První vzorec počítá čísla větší než 10 (což je 7), druhý vzorec vrátí počet čísel větších nebo rovných 21 (což je 4) a výsledek druhého vzorce se odečte od prvního vzorce (7 -4) pro získání počtu čísel mezi dvěma čísly (3).
Pokud chcete spočítat buňky s číslem větším než 10 a menším než 21 v rozsahu B2:B14, včetně čísel 10 a 21, použijte tento vzorec:
=COUNTIF(B2:B14,">=10")-COUNTIF(B2:B14,">21")
COUNTIFS pro počítání čísel mezi 2 čísly
Chcete-li počítat čísla mezi 10 a 21 (kromě 10 a 21) obsažená v buňkách B2 až B9, použijte tento vzorec:
=COUNTIFS(B2:B14;">10",B2:B14,"<21")
Chcete-li do počítání zahrnout 10 a 21, použijte ve vzorcích operátory „větší než nebo rovno“ (>=) místo „větší než“ a „menší nebo rovno“ (<=) místo operátorů „menší než“. .
COUNTIFS pro počítání buněk s více kritérii (AND kritéria)
Funkce COUNTIFS je množným protějškem funkce COUNTIF, která počítá buňky na základě dvou nebo více kritérií ve stejném nebo více rozsazích. Je známá jako „A logika“, protože funkce je vytvořena pro počítání buněk pouze tehdy, když jsou všechny zadané podmínky PRAVDA.
Chceme například zjistit, kolikrát (počet buněk) byl chléb (hodnota ve sloupci A) prodán méně než 5 (hodnota ve sloupci C).
Můžeme použít tento vzorec:
=COUNTIFS(A2:A14,"Chléb"C2:C14,"<5")
COUNTIF pro počítání buněk s více kritérii (OR kritéria)
Pokud chcete spočítat počet buněk, které splňují více kritérií ve stejném rozsahu, spojte dvě nebo více funkcí COUNTIF dohromady. Chcete-li například zjistit, kolikrát se slova „chléb“ nebo „sýr“ opakují v určeném rozsahu (A2:A14), použijte níže uvedený vzorec:
=COUNTIF(A2:A14,"Chléb")+COUNTIF(A2:A14,"Sýr")Tento vzorec počítá buňky, pro které je alespoň jedna z podmínek PRAVDA. Proto se tomu říká „logika NEBO“.
Pokud chcete vyhodnotit více než jedno kritérium v každé z funkcí, je lepší použít COUNTIFS místo COUNTIF. V níže uvedeném příkladu chceme získat počet stavů „Objednáno“ a „Dodáno“ pro „Chléb“, takže bychom použili tento vzorec:
=COUNTIFS(A2:A14,"Chléb",C2:C14,"Objednáno")+COUNTIFS(A2:A14,"Chléb",C2:C14,"Doručeno")
Doufáme, že tento snadný, ale spíše dlouhý tutoriál vám poskytne představu o tom, jak používat funkce COUNTIF a COUNTIF v Excelu.