Funkcia Excel COUNTIF umožňuje spočítať počet buniek, ktoré spĺňajú špecifické kritériá alebo podmienky v danom rozsahu.
Funkcia COUNTIF je jednou zo štatistických funkcií v Exceli, ktorá je kombináciou funkcií COUNT a IF alebo funkcie COUNTA. Pri použití vo vzorci funkcia počíta počet buniek, ktoré zodpovedajú špecifickým kritériám alebo podmienkam v rovnakom alebo viacerých rozsahoch. Funkcia COUNTIF pomáha počítať bunky obsahujúce text, čísla alebo dátumy, ktoré spĺňajú špecifické kritériá.
Bunky môžete počítať pomocou funkcií COUNTIF alebo COUNTIFS v Exceli. Rozdiel medzi funkciami COUNTIF a COUNTIFS je v tom, že COUNTIF sa používa na počítanie buniek, ktoré spĺňajú jedno kritérium v jednom rozsahu, zatiaľ čo COUNTIFS počíta bunky, ktoré spĺňajú viaceré podmienky v rovnakom alebo viacerých rozsahoch.
Tento článok vám ukáže, ako používať dve funkcie COUNTIF a COUNTIFS v Exceli.
Funkcia Excel COUNTIF
Funkcia COUNTIF vám umožňuje vykonávať počty údajov na základe špecifického kritéria alebo podmienky. Podmienka použitá vo funkcii funguje s logickými operátormi (, , =, >=, <=) a zástupnými znakmi (*, ?) na čiastočné priraďovanie.
Syntax funkcie COUNTIF
Štruktúra funkcie COUNTIF je:
=COUNTIF(rozsah,kritérium)Parametre:
rozsah– Rozsah buniek, ktoré sa majú spočítať.kritériá– Podmienka určuje, ktoré bunky by mali byť zahrnuté do počtu v zadanom rozsahu. Kritériá môžu byť číselná hodnota, text, odkaz na adresu bunky alebo rovnica.
Použitie funkcie COUNTIF na počítanie číselných hodnôt
Ako sme diskutovali vyššie, kritériá (druhý argument) vo funkcii COUNTIF definujú podmienku, ktorá hovorí funkcii, ktoré bunky má počítať.
Táto funkcia vám pomáha spočítať počet buniek s hodnotami, ktoré spĺňajú logické podmienky, ako napríklad rovné, väčšie, menšie alebo nerovnajúce sa zadanej hodnote atď.
V nižšie uvedenom príklade vzorec počíta bunky, ktoré obsahujú hodnotu rovnajúcu sa 5 (kritériá). Do vzorca môžete priamo vložiť „5“ alebo použiť odkaz na adresu bunky, ktorá má hodnotu (bunka D2 v príklade nižšie).
=COUNTIF(B2:B11;D2)
Vyššie uvedený vzorec počíta počet buniek v rozsahu buniek (B2:B11), ktoré obsahujú hodnotu rovnajúcu sa hodnote v bunke D2.
Nasledujúci vzorec počíta bunky, ktoré majú hodnotu menšiu ako 5.
=COUNTIF(B2:B11,"<5")Operátor menej ako (<) hovorí, že vzorec má počítať bunky s hodnotou menšou ako „5“ v rozsahu B2:B11. Vždy, keď v podmienke použijete operátor, nezabudnite ho uzavrieť do dvojitých úvodzoviek („“).
Niekedy, keď chcete spočítať bunky tak, že ich porovnáte s kritériom (hodnotou) v bunke. V takýchto prípadoch vytvorte kritérium spojením operátora a odkazu na bunku. Keď to urobíte, musíte operátor porovnania uzavrieť do dvojitých úvodzoviek („“) a potom medzi operátor porovnania a odkaz na bunku vložiť ampersand (&).
=COUNTIF(B2:B11,">="&D2)
Na obrázku nižšie je niekoľko vzorcov a ich výsledok.
Použitie funkcie COUNTIF na počítanie textových hodnôt
Ak chcete spočítať bunky, ktoré obsahujú určité textové reťazce, použite tento textový reťazec ako argument kritéria alebo bunku, ktorá obsahuje textový reťazec. Napríklad v tabuľke nižšie, ak chceme spočítať všetky bunky v rozsahu (B21:D27) s textovou hodnotou v bunke B21 (sam), môžeme použiť nasledujúci vzorec:
=COUNTIF(B21:D27;B21)
Ako sme už diskutovali, mohli by sme buď použiť text „sam“ priamo vo vzorci, alebo použiť odkaz na bunku, ktorý má kritériá (B21). Keď sa textový reťazec používa vo vzorci v programe Excel, mal by byť vždy uzavretý v dvojitých úvodzovkách („“).
=COUNTIF(B21:D27,"sam")
Ak chcete spočítať bunky, ktoré neobsahujú zadaný text, použite nasledujúci vzorec:
=COUNTIF(B21:D27,""&B21)Uistite sa, že ste pripojili „nerovná sa“ "" operátor v dvojitých úvodzovkách.
Ak používate text „sam“ priamo vo vzorci, musíte uzavrieť operátor „“ a textový reťazec spolu ("sam") v úvodzovkách.
=COUNTIF(B21:D27,"sam") 
Používanie zástupných znakov vo funkcii Excel COUNTIF (čiastočná zhoda)
Vzorec COUNTIF so zástupnými znakmi môžete použiť na sčítanie buniek, ktoré obsahujú konkrétne slovo, frázu alebo písmená. Vo funkcii Excel COUNTIF môžete použiť tri zástupné znaky:
*(hviezdička) – Používa sa na počítanie buniek s ľubovoľným počtom začiatočných a koncových znakov/písmen. (napr. St* môže znamenať Stark, Stork, Stacks atď.?(otáznik) – Používa sa na nájdenie buniek s ľubovoľným jedným znakom. (napr. St?rk môže znamenať Stark alebo Stork.~(tilda) – Používa sa na nájdenie a spočítanie počtu buniek obsahujúcich otáznik alebo hviezdičku (~, *, ?) v texte.
Počítanie buniek začínajúcich alebo končiacich určitými znakmi
Ak chcete spočítať bunky, ktoré začínajú alebo končia konkrétnym textom s ľubovoľným počtom ďalších znakov v bunke, použite zástupný znak hviezdičku (*) v druhom argumente funkcie COUNTIF.
Použite tento vzorový vzorec:
=COUNTIF(A1:A10,"A*") – na počítanie buniek, ktoré začínajú na „A“.
=COUNTIF(A19:A28,"*er") – spočítať počet buniek, ktoré končia znakmi „er“.
=COUNTIF(A2:A12,"*QLD*") – na počítanie buniek, ktoré obsahujú text „QLD“ kdekoľvek v textovom reťazci.
A ? predstavuje presne jeden znak, použite tento zástupný znak vo funkcii COUNTIF nižšie na spočítanie počtu buniek, ktoré obsahujú presne +1 znak, kde „?“ sa používa.
=COUNTIF(A1:A10,"Par?s")
Počítanie prázdnych a neprázdnych buniek pomocou funkcie COUNTIF
Vzorec COUNTIF je tiež užitočný, keď ide o počítanie počtu prázdnych alebo neprázdnych buniek v danom rozsahu.
Spočítajte neprázdne bunky
Ak chcete počítať iba bunky, ktoré obsahujú akékoľvek „textové“ hodnoty, použite nižšie uvedený vzorec. Tento vzorec považuje bunky s dátumami a číslami za prázdne bunky a nezahŕňa ich do počítania.
=COUNTIF(A1:B12,"*")Zástupný znak * zhoduje sa iba s textovými hodnotami a vráti počet všetkých textových hodnôt v danom rozsahu.
Ak chcete spočítať všetky neprázdne bunky v danom rozsahu, skúste tento vzorec:
=COUNTIF(A1:B12,"")
Počítajte prázdne bunky
Ak chcete spočítať prázdne bunky v určitom rozsahu, použite funkciu COUNTIF s * zástupný znak a operátor v argumente kritéria na počítanie prázdnych buniek.
Tento vzorec počíta bunky, ktoré neobsahujú žiadne textové hodnoty:
=COUNTIF(A1:B12,""&"*")Od r * sa zástupné znaky zhodujú s akoukoľvek textovou hodnotou, vyššie uvedený vzorec spočíta všetky bunky, ktoré sa nerovnajú *. Bunky s dátumami a číslami počíta aj ako prázdne miesta.
Ak chcete spočítať všetky prázdne miesta (všetky typy hodnôt):
=COUNTIF(A1:B12,"")Táto funkcia počíta iba prázdne bunky v rozsahu.
Použitie funkcie COUNTIF na počítanie dátumov
Bunky môžete spočítať s dátumami (rovnako ako s číselnými kritériami), ktoré spĺňajú logickú podmienku alebo zadaný dátum alebo dátum v referenčnej bunke.
Na sčítanie buniek, ktoré obsahujú zadaný dátum (05-05-2020), by sme použili tento vzorec:
=COUNTIF(B2:B10,"05-05-2020")
Môžete tiež zadať dátum v rôznych formátoch ako kritériá vo funkcii COUNTIF, ako je uvedené nižšie:
Ak chcete spočítať bunky, ktoré obsahujú dátumy pred alebo po určitom dátume, použite operátory menšie ako (pred) alebo väčšie ako (za) spolu s konkrétnym dátumom alebo odkazom na bunku.
=COUNTIF(B2:B10,">=05/05/2020")
Môžete tiež použiť odkaz na bunku, ktorý obsahuje dátum, a to tak, že ho skombinujete s operátorom (v úvodzovkách).
Ak chcete spočítať počet buniek v rozsahu A2:A14 s dátumom pred dátumom v E3, použite nižšie uvedený vzorec, kde operátor väčší ako (<) znamená pred dátumom v E3.
=COUNTIF(A2:A14,"<"&E3)
Niekoľko vzorcov a ich výsledok:
Dátum počítania podľa aktuálneho dátumu
Funkciu COUNTIF môžete skombinovať so špecifickými funkciami dátumu v Exceli, t. j. DNES (), aby ste spočítali bunky s aktuálnym dátumom.
=COUNTIF(A2:A14,">"&DNES())Táto funkcia spočíta všetky dátumy od dnešného dňa v rozsahu (A2:A14).
Počítajte dátumy medzi konkrétnym rozsahom dátumov
Ak chcete spočítať všetky dátumy medzi dvoma dátumami, musíte vo vzorci použiť dve kritériá.
Môžeme to urobiť pomocou dvoch metód: funkcie COUNTIF a COUNTIFS.
Použitie funkcie Excel COUNTIF
Na spočítanie všetkých dátumov medzi dvoma zadanými dátumami musíte použiť dve funkcie COUNTIF.
Ak chcete spočítať dátumy medzi '09-02-2020' a '20-08-2021', použite tento vzorec:
=COUNTIF(A2:A14,">09-02-2020")-COUNTIF(A2:A14,">20-08-2021")Tento vzorec najprv zistí počet buniek, ktoré majú dátum po 2. februári, a odpočíta počet buniek s dátumami po 20. auguste. Teraz dostaneme číslo. buniek, ktoré majú dátumy po 2. februári a 20. auguste alebo skôr (počet je 9).
Ak nechcete, aby vzorec započítal 2. február aj 20. august, použite namiesto toho tento vzorec:
=COUNTIF(A2:A14,">09-02-2020")-COUNTIF(A2:A14,">=20-08-2021")Stačí nahradiť operátor „>“ znakom „>=“ v druhom kritériu.
Použitie funkcie Excel COUNTIFS
Funkcia COUNTIFS tiež podporuje viacero kritérií a na rozdiel od funkcie COUNTIF počíta bunky až po splnení všetkých podmienok. Ak chcete spočítať bunky so všetkými dátumami medzi dvoma určenými dátumami, zadajte tento vzorec:
=COUNTIFS(A2:A14;">"&A11;A2:A14,"<"&A10)
Ak chcete do počtu zahrnúť aj uvedené dátumy, použite operátory „>=“ a „<=“. Tu postupujte podľa tohto vzorca:
=COUNTIFS(A2:A14,">=09-02-2020",A2:A14,"<=20-08-2021")V tomto príklade sme namiesto odkazu na bunku použili dátum priamo v kritériách.
Ako pracovať s COUNTIF a COUNTIFS s viacerými kritériami v Exceli
Funkcia COUNTIF sa väčšinou používa na počítanie buniek s jedným kritériom (podmienkou) v jednom rozsahu. Stále však môžete použiť COUNTIF na počítanie buniek, ktoré zodpovedajú viacerým podmienkam v rovnakom rozsahu. Funkciu COUNTIFS však možno použiť na počítanie buniek, ktoré spĺňajú viaceré podmienky v rovnakých alebo rôznych rozsahoch.
Ako počítať čísla v rámci rozsahu
Bunky obsahujúce čísla medzi dvoma určenými číslami môžete spočítať pomocou dvoch funkcií: COUNTIF a COUNTIFS.
COUNTIF na počítanie čísel medzi dvoma číslami
Jedným z bežných použití funkcie COUNTIF s viacerými kritériami je počítanie čísel medzi dvoma určenými číslami, napr. na počítanie čísel väčších ako 10, ale menších ako 50. Ak chcete spočítať čísla v rámci rozsahu, spojte dve alebo viac funkcií COUNTIF do jedného vzorca. Ukážeme vám ako.
Povedzme, že chcete spočítať bunky v rozsahu B2:B9, kde je hodnota väčšia ako 10 a menšia ako 21 (okrem 10 a 21), použite tento vzorec:
=COUNTIF(B2:B14;">10")-COUNTIF(B2:B14,">=21")Rozdiel medzi dvoma číslami sa zistí odčítaním jedného vzorca od druhého. Prvý vzorec počíta čísla väčšie ako 10 (čo je 7), druhý vzorec vráti počet čísel väčší alebo rovný 21 (čo je 4) a výsledok druhého vzorca sa odpočíta od prvého vzorca (7 -4), aby ste získali počet čísel medzi dvoma číslami (3).
Ak chcete spočítať bunky s číslom väčším ako 10 a menším ako 21 v rozsahu B2:B14 vrátane čísel 10 a 21, použite tento vzorec:
=COUNTIF(B2:B14,">=10")-COUNTIF(B2:B14,">21")
COUNTIFS na počítanie čísel medzi 2 číslami
Ak chcete spočítať čísla medzi 10 a 21 (okrem 10 a 21), ktoré sa nachádzajú v bunkách B2 až B9, použite tento vzorec:
=COUNTIFS(B2:B14;">10"B2:B14,"<21")
Ak chcete do počítania zahrnúť 10 a 21, vo vzorcoch použite operátory „väčšie alebo rovné“ (>=) namiesto „väčšie ako“ a „menšie alebo rovné“ (<=) namiesto operátorov „menej ako“. .
COUNTIFS na počítanie buniek s viacerými kritériami (A kritériá)
Funkcia COUNTIFS je množným náprotivkom funkcie COUNTIF, ktorá počíta bunky na základe dvoch alebo viacerých kritérií v rovnakých alebo viacerých rozsahoch. Je známa ako „A logika“, pretože funkcia je určená na počítanie buniek iba vtedy, keď sú všetky zadané podmienky PRAVDA.
Chceme napríklad zistiť, koľkokrát (počet buniek) sa ten chlieb (hodnota v stĺpci A) predal menej ako 5 (hodnota v stĺpci C).
Môžeme použiť tento vzorec:
=COUNTIFS(A2:A14,"Chlieb",C2:C14,"<5")
COUNTIF na počítanie buniek s viacerými kritériami (ALEBO kritéria)
Ak chcete spočítať počet buniek, ktoré spĺňajú viaceré kritériá v rovnakom rozsahu, spojte dve alebo viac funkcií COUNTIF. Ak chcete napríklad zistiť, koľkokrát sa slová „Chlieb“ alebo „Syr“ opakujú v určenom rozsahu (A2:A14), použite nasledujúci vzorec:
=COUNTIF(A2:A14,"Chlieb")+COUNTIF(A2:A14,"Syr")Tento vzorec počíta bunky, pre ktoré je aspoň jedna z podmienok PRAVDA. Preto sa to nazýva „logika ALEBO“.
Ak chcete vyhodnotiť viac ako jedno kritérium v každej z funkcií, je lepšie použiť COUNTIFS namiesto COUNTIF. V nižšie uvedenom príklade chceme získať počet stavov „Objednané“ a „Dodané“ pre „Chlieb“, takže by sme použili tento vzorec:
=COUNTIFS(A2:A14,"Chlieb",C2:C14,"Objednané")+COUNTIFS(A2:A14,"Chlieb",C2:C14,"Doručené")
Dúfame, že tento jednoduchý, ale skôr dlhý návod vám poskytne predstavu o tom, ako používať funkcie COUNTIF a COUNTIF v Exceli.