Excel má mnoho funkcií, kde používateľ potrebuje na získanie výsledku zadať jedno alebo viac kritérií. Ak napríklad chcete počítať bunky na základe viacerých kritérií, môžete v programe Excel použiť funkcie COUNTIF alebo COUNTIFS.
Tento tutoriál sa zaoberá rôznymi spôsobmi použitia jedného alebo viacerých kritérií vo funkciách COUNTIF a COUNTIFS v programe Excel.
Aj keď sa v tomto návode budem zameriavať predovšetkým na funkcie COUNTIF a COUNTIFS, všetky tieto príklady je možné použiť aj v iných funkciách programu Excel, ktoré ako vstupy berú do úvahy viac kritérií (napríklad SUMIF, SUMIFS, AVERAGEIF a AVERAGEIFS).
Úvod do funkcií Excelu COUNTIF a COUNTIFS
Najprv sa zoznámime s používaním funkcií COUNTIF a COUNTIFS v programe Excel.
Funkcia Excel COUNTIF (vyžaduje jedno kritérium)
Funkcia Excel COUNTIF je najvhodnejšia pre situácie, keď chcete počítať bunky na základe jedného kritéria. Ak chcete počítať na základe viacerých kritérií, použite funkciu COUNTIFS.
Syntax
= COUNTIF (rozsah, kritériá)
Vstupné argumenty
- rozsah - rozsah buniek, ktoré chcete spočítať.
- kritériá - kritériá, ktoré sa musia vyhodnotiť vzhľadom na rozsah buniek, aby sa bunka započítala.
Excel funkcia COUNTIFS (vyžaduje viac kritérií)
Funkcia Excel COUNTIFS je najvhodnejšia pre situácie, keď chcete počítať bunky na základe viacerých kritérií.
Syntax
= COUNTIFS (rozsah_kritérií1, kritérium1, [rozsah_kritérií2, kritérium2]…)
Vstupné argumenty
- rozsah_kritérií1 - Rozsah buniek, pre ktoré chcete hodnotiť podľa kritérií1.
- kritériá1 - kritériá, ktoré chcete vyhodnotiť pre rozsah_kritérií1, aby ste určili, ktoré bunky sa majú počítať.
- [kritérium_rozsahu2] - Rozsah buniek, pre ktoré chcete hodnotiť podľa kritérií2.
- [kritériá2] - kritériá, ktoré chcete vyhodnotiť pre kritérium_rozsahu2, aby ste určili, ktoré bunky sa majú počítať.
Teraz sa pozrime na niekoľko príkladov použitia viacerých kritérií vo funkciách COUNTIF v programe Excel.
Použitie NUMBER kritérií v funkciách Excel COUNTIF
#1 Spočítajte bunky, ak sú kritériá ROVNÝ ako hodnota
Ak chcete získať počet buniek, v ktorých sa argument kritérií rovná zadanej hodnote, môžete buď priamo zadať kritériá, alebo použiť odkaz na bunku, ktorý kritériá obsahuje.
Nasleduje príklad, kde spočítame bunky obsahujúce číslo 9 (čo znamená, že argument kritéria sa rovná 9). Tu je vzorec:
= COUNTIF ($ B $ 2: $ B $ 11, D3)
Vo vyššie uvedenom príklade (na obrázku) sú kritériá v bunke D3. Kritériá môžete zadať aj priamo do vzorca. Môžete napríklad použiť aj:
= COUNTIF ($ B $ 2: $ B $ 11,9)
#2 Spočítajte bunky, ak sú kritériá Väčšie ako hodnota
Na získanie počtu buniek s hodnotou väčšou ako je zadaná hodnota používame operátor väčší ako („>“). Mohli by sme to použiť buď priamo vo vzorci, alebo použiť odkaz na bunku, ktorý má kritériá.
Kedykoľvek použijeme operátor v kritériách v programe Excel, musíme ho vložiť do úvodzoviek. Ak sú napríklad kritériá vyššie ako 10, musíme ako kritériá zadať „> 10“ (pozri obrázok nižšie):
Tu je vzorec:
= COUNTIF ($ B $ 2: $ B $ 11, ”> 10 ″)
Kritériá môžete mať aj v bunke a ako kritériá použiť odkaz na bunku. V takom prípade NEMUSÍTE zadávať kritériá do dvojitých úvodzoviek:
= COUNTIF ($ B $ 2: $ B $ 11, D3)
Mohlo by to nastať aj vtedy, keď chcete, aby boli kritériá v bunke, ale nechcete to s operátorom. Môžete napríklad chcieť, aby bunka D3 mala číslo 10 a nie> 10.
V takom prípade musíte vytvoriť argument kritérií, ktorý je kombináciou operátora a odkazu na bunku (pozri obrázok nižšie):
= COUNTIF ($ B $ 2: $ B $ 11, ”>” & D3)
POZNÁMKA: Keď skombinujete operátor a odkaz na bunku, operátor je vždy v dvojitých úvodzovkách. Odkaz na operátor a bunku je spojený znakom & ().
#3 Spočítajte bunky, ak sú kritériá Menej ako hodnota
Na získanie počtu buniek s hodnotou menšou ako zadaná hodnota použijeme operátor less than („<“). Mohli by sme to použiť buď priamo vo vzorci, alebo použiť odkaz na bunku, ktorý má kritériá.
Kedykoľvek použijeme operátor v kritériách v programe Excel, musíme ho vložiť do úvodzoviek. Ak je napríklad kritériom, že číslo by malo byť menšie ako 5, potom musíme ako kritérium zadať „<5“ (pozri obrázok nižšie):
= COUNTIF ($ B $ 2: $ B $ 11, ”<5 ″)
Kritériá môžete mať aj v bunke a ako kritériá použiť odkaz na bunku. V takom prípade NEMUSÍTE zadávať kritériá do dvojitých úvodzoviek (pozri obrázok nižšie):
= COUNTIF ($ B $ 2: $ B $ 11, D3)
Mohlo by tiež dôjsť k prípadu, keď chcete, aby boli kritériá v bunke, ale nechcete to s operátorom. Môžete napríklad chcieť, aby bunka D3 mala číslo 5 a nie <5.
V takom prípade musíte vytvoriť argument kritéria, ktorý je kombináciou odkazu na operátor a bunku:
= COUNTIF ($ B $ 2: $ B $ 11, ”<“ & D3)
POZNÁMKA: Keď skombinujete operátor a odkaz na bunku, operátor je vždy v dvojitých úvodzovkách. Odkaz na operátor a bunku je spojený znakom & ().
#4 Počet buniek s viacerými kritériami - medzi dvoma hodnotami
Aby sme získali počet hodnôt medzi dvoma hodnotami, musíme vo funkcii COUNTIF použiť viacero kritérií.
Tu sú dva spôsoby, ako to urobiť:
METODA 1: Použitie funkcie COUNTIFS
Funkcia COUNTIFS dokáže spracovať viac kritérií ako argumenty a bunky počíta, iba ak sú všetky kritériá PRAVDIVÉ. Na počítanie buniek s hodnotami medzi dvoma zadanými hodnotami (povedzme 5 a 10) môžeme použiť nasledujúcu funkciu COUNTIFS:
= COUNTIFS ($ B $ 2: $ B $ 11, ”> 5 ″, $ B $ 2: $ B $ 11,„ <10 “)
POZNÁMKA: Vyššie uvedený vzorec nepočíta bunky, ktoré obsahujú 5 alebo 10. Ak chcete tieto bunky zahrnúť, použite operátory väčšie než rovnajúce sa (> =) a menšie než rovné (<=). Tu je vzorec:
= COUNTIFS ($ B $ 2: $ B $ 11, ”> = 5 ″, $ B $ 2: $ B $ 11,” <= 10 ″)
Tieto kritériá môžete mať aj v bunkách a ako kritérium použiť odkaz na bunku. V takom prípade NEMUSÍTE zadávať kritériá do dvojitých úvodzoviek (pozri obrázok nižšie):
Môžete tiež použiť kombináciu odkazov na bunky a operátorov (kde je operátor zadaný priamo vo vzorci). Keď skombinujete operátor a odkaz na bunku, operátor je vždy v dvojitých úvodzovkách. Odkaz na operátor a bunku je spojený znakom & ().
METÓDA 2: Použitie dvoch funkcií COUNTIF
Ak máte viacero kritérií, môžete použiť COUNTIFS alebo vytvoriť kombináciu funkcií COUNTIF. Nasledujúci vzorec by tiež urobil to isté:
= COUNTIF ($ B $ 2: $ B $ 11, ”> 5 ″)-COUNTIF ($ B $ 2: $ B $ 11,”> 10 ″)
Vo vyššie uvedenom vzorci najskôr nájdeme počet buniek, ktoré majú hodnotu väčšiu ako 5, a odpočítame počet buniek s hodnotou väčšou ako 10. To by nám poskytlo výsledok ako 5 (čo je počet buniek, ktoré majú hodnotu hodnoty viac ako 5 a menej ako rovné 10).
Ak chcete, aby vzorec obsahoval 5 aj 10, použite namiesto toho nasledujúci vzorec:
= COUNTIF ($ B $ 2: $ B $ 11, ”> = 5 ″)-COUNTIF ($ B $ 2: $ B $ 11,”> 10 ″)
Ak chcete, aby vzorec vylúčil z počítania „5“ aj „10“, použite nasledujúci vzorec:
= COUNTIF ($ B $ 2: $ B $ 11, ”> = 5 ″)-COUNTIF ($ B $ 2: $ B $ 11,”> 10 ″)-COUNTIF ($ B $ 2: $ B $ 11,10)
Tieto kritériá môžete mať v bunkách a použiť odkazy na bunky, alebo môžete použiť kombináciu operátorov a odkazov na bunky.
Použitie kritérií TEXT vo funkciách programu Excel
#1 Spočítajte bunky, ak sú kritériá ROVNÝM k určenému textu
Na počítanie buniek, ktoré obsahujú presnú zhodu so zadaným textom, môžeme tento text jednoducho použiť ako kritérium. Napríklad v súbore údajov (zobrazenom nižšie na obrázku), ak chcem spočítať všetky bunky s menom Joe, môžem použiť nasledujúci vzorec:
= COUNTIF ($ B $ 2: $ B $ 11, „Joe“)
Keďže ide o textový reťazec, musím textové kritériá vložiť do úvodzoviek.
Môžete tiež mať kritériá v bunke a potom použiť odkaz na túto bunku (ako je uvedené nižšie):
= COUNTIF ($ B $ 2: $ B $ 11, E3)
POZNÁMKA: Ak v kritériách alebo rozsahu kritérií existujú úvodné/koncové medzery, môžete získať nesprávne výsledky. Pred použitím týchto vzorcov sa uistite, že ste údaje vyčistili.
#2 Spočítajte bunky, ak kritériá NIE sú rovnaké pre špecifikovaný text
Podobne ako to, čo sme videli vo vyššie uvedenom príklade, môžete počítať aj bunky, ktoré neobsahujú zadaný text. Na to musíme použiť operátor nerovný ().
Predpokladajme, že chcete spočítať všetky bunky, ktoré neobsahujú názov JOE, tu je vzorec, ktorý to urobí:
= COUNTIF ($ B $ 2: $ B $ 11, „Joe“)
Kritériá môžete mať aj v bunke a ako kritériá použiť odkaz na bunku. V takom prípade NEMUSÍTE zadávať kritériá do dvojitých úvodzoviek (pozri obrázok nižšie):
= COUNTIF ($ B $ 2: $ B $ 11, E3)
Mohlo by to nastať aj vtedy, keď chcete, aby boli kritériá v bunke, ale nechcete to s operátorom. Môžete napríklad chcieť, aby bunka D3 mala meno Joe a nie Joe.
V takom prípade musíte vytvoriť argument kritéria, ktorý je kombináciou odkazu na operátor a bunku (pozri obrázok nižšie):
= COUNTIF ($ B $ 2: $ B $ 11, ”” a E3)
Keď skombinujete operátor a odkaz na bunku, operátor je vždy v dvojitých úvodzovkách. Odkaz na operátor a bunku je spojený znakom & ().
Použitie kritérií DÁTUM v programe Excel, funkcie COUNTIF a COUNTIFS
Excel uloží dátum a čas ako čísla. Môžeme ho teda použiť rovnako ako čísla.
#1 Spočítajte bunky, ak sú kritériá ROVNÝ do určeného dátumu
Na získanie počtu buniek obsahujúcich zadaný dátum by sme spolu s dátumom použili operátor rovná sa (=).
Ak chcete použiť dátum, odporúčam použiť funkciu DATE, pretože sa zbavuje akejkoľvek možnosti chyby v hodnote dátumu. Ak napríklad chcem použiť dátum 1. september 2015, môžem použiť funkciu DÁTUM, ako je uvedené nižšie:
= DÁTUM (2015,9,1)
Tento vzorec by vrátil rovnaký dátum napriek regionálnym rozdielom. 01-09-2015 by napríklad bolo 1. septembra 2015 podľa syntaxe dátumu v USA a 09. januára 2015 podľa syntaxe dátumu v Spojenom kráľovstve. Tento vzorec by sa však vždy vrátil k 1. septembru 2105.
Tu je vzorec na spočítanie počtu buniek obsahujúcich dátum 02-09-2015:
= COUNTIF ($ A $ 2: $ A $ 11, DÁTUM (2015,9,2))
#2 Spočítajte bunky, ak sú kritériá PRED alebo PO určenom dátume
Na počítanie buniek obsahujúcich dátum pred alebo po zadanom dátume môžeme použiť operátory menšie ako/väčšie ako.
Ak chcem napríklad spočítať všetky bunky obsahujúce dátum, ktorý je po 02. septembri 2015, môžem použiť vzorec:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” A DÁTUM (2015,9,2))
Podobne môžete počítať aj počet buniek pred zadaným dátumom. Ak chcete do počítania zahrnúť dátum, použite operátor a „rovná sa“ spolu s operátorom „viac ako/menej ako“.
Môžete tiež použiť odkaz na bunku, ktorý obsahuje dátum. V tomto prípade musíte kombinovať operátor (v dvojitých úvodzovkách) s dátumom pomocou znaku & ().
Pozri príklad nižšie:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” a F3)
#3 Počet buniek s viacerými kritériami - medzi dvoma dátumami
Aby sme získali počet hodnôt medzi dvoma hodnotami, musíme vo funkcii COUNTIF použiť viacero kritérií.
Môžeme to urobiť dvoma spôsobmi - jednou jedinou funkciou COUNTIFS alebo dvoma funkciami COUNTIF.
METODA 1: Použitie funkcie COUNTIFS
Funkcia COUNTIFS môže považovať za argumenty viacero kritérií a bunky počíta, iba ak sú všetky kritériá PRAVDIVÉ. Na počítanie buniek s hodnotami medzi dvoma určenými dátumami (povedzme 2. september a 7. september) môžeme použiť nasledujúcu funkciu COUNTIFS:
= COUNTIFS ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,2), $ A $ 2: $ A $ 11, ”<“ & DATE (2015,9,7))
Vyššie uvedený vzorec nepočíta bunky, ktoré obsahujú zadané dátumy. Ak chcete zahrnúť aj tieto dátumy, použite operátory väčšie než rovnajúce sa (> =) a menšie než rovné (<=). Tu je vzorec:
= COUNTIFS ($ A $ 2: $ A $ 11, ”> =” & DATE (2015,9,2), $ A $ 2: $ A $ 11, ”<=” & DATE (2015,9,7))
Dátumy môžete tiež uložiť do bunky a ako kritérium použiť odkaz na bunku. V takom prípade nemôžete mať v bunkách operátora s dátumom. Operátory musíte do vzorca pridať ručne (v dvojitých úvodzovkách) a odkaz na bunku pridať pomocou znaku & (). Pozrite sa na obrázok nižšie:
= COUNTIFS ($ A $ 2: $ A $ 11, ”>” & F3, $ A $ 2: $ A $ 11, ”<“ & G3)
METODA 2: Používanie funkcií COUNTIF
Ak máte viacero kritérií, môžete použiť jednu funkciu COUNTIFS alebo vytvoriť kombináciu dvoch funkcií COUNTIF. Na to vám poslúži aj nasledujúci vzorec:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” A DÁTUM (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ”>” A DÁTUM (2015,9,7))
Vo vyššie uvedenom vzorci najskôr nájdeme počet buniek s dátumom po 2. septembri a odpočítame počet buniek s dátumami po 7. septembri. Výsledkom by bol výsledok 7 (čo je počet buniek s dátumami) po 2. septembri a 7. septembra alebo skôr).
Ak nechcete, aby sa vzorec počítal od 2. septembra do 7. septembra, použite namiesto toho nasledujúci vzorec:
= COUNTIF ($ A $ 2: $ A $ 11, ”> =” A DÁTUM (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ”>” A DÁTUM (2015,9,7))
Ak chcete vylúčiť zo započítania oba dátumy, použite nasledujúci vzorec:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” A DÁTUM (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ”>“ A DÁTUM (2015,9,7) -COUNTIF ($ A $ 2: $ A $ 11, DÁTUM (2015,9,7)))
Môžete tiež mať dátumy kritérií v bunkách a používať odkazy na bunky (spolu s operátormi v dvojitých úvodzovkách spojených pomocou znaku ampersand).
Použitie znakov WILDCARD v kritériách vo funkciách COUNTIF a COUNTIFS
V programe Excel sú tri zástupné znaky:
- * (hviezdička) - Predstavuje ľubovoľný počet znakov. Napríklad ex* môže znamenať excel, excel, príklad, expert atď.
- ? (otáznik) - Predstavuje jeden jediný znak. Tr? Mp môže napríklad znamenať Trump alebo Tramp.
- ~ (vlnovka) - Používa sa na identifikáciu zástupných znakov (~, *,?) V texte.
Funkciu COUNTIF so zástupnými znakmi môžete použiť na počítanie buniek, ak zlyhá iná vstavaná funkcia počítania. Predpokladajme napríklad, že máte množinu údajov, ako je uvedené nižšie:
Teraz si vezmime rôzne príklady:
#1 Spočítajte bunky, ktoré obsahujú text
Na počítanie buniek s textom v nich môžeme použiť zástupný znak * (hviezdička). Pretože hviezdička predstavuje ľubovoľný počet znakov, započítava všetky bunky, ktoré obsahujú ľubovoľný text. Tu je vzorec:
= COUNTIFS ($ C $ 2: $ C $ 11, ”*”)
Poznámka: Vyššie uvedený vzorec ignoruje bunky, ktoré obsahujú čísla, prázdne bunky a logické hodnoty, ale počítal by, že bunky obsahujú apostrof (a preto sa javia ako prázdne) alebo bunky obsahujúce prázdny reťazec (= ””), ktoré mohli byť vrátené ako časť vzorca.
Tu je podrobný návod na riešenie prípadov, kde je prázdny reťazec alebo apostrof.
Tu je podrobný návod na riešenie prípadov, kde sú prázdne reťazce alebo apostrofy.
Nasleduje video, ktoré vysvetľuje rôzne scenáre počítania buniek s textom v ňom.
#2 Spočítajte prázdne bunky
Ak uvažujete o použití funkcie COUNTA, zamyslite sa znova.
Skúste to a možno vás to zlyhá. COUNTA započíta aj bunku, ktorá obsahuje prázdny reťazec (často vrátený vzorcami ako = ”” alebo keď ľudia do bunky zadajú iba apostrof). Bunky, ktoré obsahujú prázdne reťazce, vyzerajú prázdne, ale nie sú, a preto ich počíta funkcia COUNTA.
COUNTA započíta aj bunku, ktorá obsahuje prázdny reťazec (často vrátený vzorcami ako = ”” alebo keď ľudia do bunky zadajú iba apostrof). Bunky, ktoré obsahujú prázdne reťazce, vyzerajú prázdne, ale nie sú, a preto ich počíta funkcia COUNTA.
Ak teda použijete vzorec = COUNTA (A1: A11), vráti 11, pričom by mal vrátiť 10.
Tu je oprava:
= COUNTIF ($ A $ 1: $ A $ 11, ”?*”)+COUNT ($ A $ 1: $ A $ 11)+SUMPRODUCT (-ISLOGICAL ($ A $ 1: $ A $ 11))
Pochopme tento vzorec rozdelením:
- COUNTIF ($ N $ 8: $ N $ 18, ”?*”) - Táto časť vzorca vráti 5. To zahŕňa každú bunku, v ktorej je textový znak. A? predstavuje jeden znak a * predstavuje ľubovoľný počet znakov. Kombinácia?* V kritériách teda núti program Excel počítať bunky, ktoré obsahujú aspoň jeden textový znak.
- COUNT ($ A $ 1: $ A $ 11) - Počíta všetky bunky, ktoré obsahujú čísla. Vo vyššie uvedenom príklade vráti 3.
- SUMPRODUCT (-ISLOGICKÉ ($ A $ 1: $ A $ 11) - Toto počíta všetky bunky, ktoré obsahujú logické hodnoty. Vo vyššie uvedenom príklade vráti 2.
#3 Spočítajte bunky, ktoré obsahujú konkrétny text
Povedzme, že chceme spočítať všetky bunky, kde názov obchodného zástupcu začína na J. To sa dá ľahko dosiahnuť použitím zástupného znaku vo funkcii COUNTIF. Tu je vzorec:
= COUNTIFS ($ C $ 2: $ C $ 11, „J*“)
Kritérium J* určuje, že text v bunke by mal začínať na J a môže obsahovať ľubovoľný počet znakov.
Ak chcete počítať bunky, ktoré obsahujú abecedu kdekoľvek v texte, ohraničte ju hviezdičkou na oboch stranách. Ak napríklad chcete počítať bunky, ktoré obsahujú abecedu „a“, ako kritériá použite * a *.
Tento článok je v porovnaní s ostatnými mojimi článkami neobvykle dlhý. Dúfam, že ste si to užili. Dajte mi vedieť svoje myšlienky zanechaním komentára.
Tiež vás môžu zaujímať nasledujúce návody na Excel:
- Spočítajte počet slov v programe Excel.
- Počet buniek na základe farby pozadia v programe Excel.
- Ako zhrnúť stĺpec v programe Excel (5 skutočne jednoduchých spôsobov)
