Excel má veľa funkcií, pri ktorých musí používateľ zadať jednu alebo viac podmienok, aby získal výsledok.Napríklad, ak chcete počítať bunky na základe viacerých kritérií, môžete použiť funkcie COUNTIF alebo COUNTIFS v Exceli.
Tento tutoriál obsahuje rôzne spôsoby použitia jedného alebo viacerých kritérií vo funkciách COUNTIF a COUNTIFS v Exceli.
Aj keď sa v tomto návode zameriam predovšetkým na funkcie COUNTIF a COUNTIFS, všetky tieto príklady možno použiť aj s inými funkciami Excelu, ktoré zadávajú viacero kritérií (napríklad SUMIF, SUMIFS, AVERAGEIF a AVERAGEIFS).
Obsah
Úvod do funkcií Excel COUNTIF a COUNTIFS
Poďme najprv pochopiť, ako používať funkcie COUNTIF a COUNTIFS v Exceli.
Funkcia Excel COUNTIF (s jediným kritériom)
Funkcia Excel COUNTIF funguje najlepšie, 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, podmienka)
Vstupné parametre
- Rozsah - Rozsah buniek, ktoré chcete vypočítať.
- stav -Kritériá, ktoré sa musia vyhodnotiť vzhľadom na rozsah buniek, ktoré sa majú spočítať.
Funkcia Excel COUNTIFS (s viacerými kritériami)
Funkcia Excel COUNTIFS funguje najlepšie, 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é parametre
- rozsah_kritérií1 – Rozsah buniek, ktoré chcete vyhodnotiť podľa kritéria 1.
- kritériá1 – Kritériom, ktoré chcete vyhodnotiť, je rozsah_kritérií1 na určenie, ktoré bunky sa majú vyhodnotiť.
- [rozsah_kritérií2] – Rozsah buniek, ktoré chcete vyhodnotiť podľa kritéria 2.
- [kritérium2] – Kritériom, ktoré chcete vyhodnotiť, je rozsah_kritérií2 na určenie, ktoré bunky sa majú počítať.
Teraz sa pozrime na niekoľko príkladov použitia viacerých kritérií vo funkcii COUNTIF v Exceli.
Použite podmienku ČÍSLO vo funkcii Excel COUNTIF
#1 Počítajte bunky, keď sa kritérium rovná hodnote
Ak chcete získať počet buniek, v ktorých sa parameter podmienky rovná zadanej hodnote, môžete podmienku zadať priamo alebo použiť odkaz na bunku, ktorá obsahuje podmienku.
Nižšie je uvedený príklad, kde počítame bunky, ktoré obsahujú číslo 9 (čo znamená, že parameter podmienky sa rovná 9).Tu je vzorec:
=COUNTIF($B$2:$B$11,D3)
Vo vyššie uvedenom príklade (na obrázku) je kritérium v bunke D3.Podmienky môžete zadať aj priamo do vzorcov.Môžete napríklad použiť aj:
=COUNTIF($B$2:$B$11,9)
#2 Počítajte bunky, keď je stav väčší ako hodnota
Na získanie počtu buniek s hodnotou väčšou ako zadaná hodnota používame operátor väčší ako (>“).Môžeme to použiť priamo vo vzorcoch alebo môžeme použiť odkazy na bunky s podmienkami.
Kedykoľvek použijeme operátor v podmienke v Exceli, musíme ho vložiť do dvojitých úvodzoviek.Napríklad, ak je podmienka väčšia ako 10, musíme ako podmienku zadať ">10" (pozri obrázok nižšie):
Tu je vzorec:
=COUNTIF($B$2:$B$11,”>10″)
Môžete tiež zahrnúť podmienky do buniek a použiť odkazy na bunky ako podmienky.V tomto prípade nie je potrebné uvádzať podmienku v úvodzovkách:
=COUNTIF($B$2:$B$11,D3)
Môže to byť aj prípad, keď chcete, aby bola podmienka v bunke, ale nechcete ju použiť s operátorom.Môžete napríklad chcieť, aby číslo v bunke D3 bolo 10 namiesto >10.
V tomto prípade musíte vytvoriť podmienený parameter, ktorý je kombináciou operátora a odkazu na bunku (pozri obrázok nižšie):
=COUNTIF($B$2:$B$11”>”&D3)
Poznámka: Keď kombinujete operátory a odkazy na bunky, operátory sú vždy uzavreté v dvojitých úvodzovkách.Operátory a odkazy na bunky sú spojené znakom ampersand (&).
#3 Počítajte bunky, keď je kritérium menšie ako hodnota
Na získanie počtu buniek s hodnotou menšou ako zadaná hodnota používame operátor menej ako ("<").Môžeme to použiť priamo vo vzorcoch alebo môžeme použiť odkazy na bunky s podmienkami.
Kedykoľvek použijeme operátor v podmienke v Exceli, musíme ho vložiť do dvojitých úvodzoviek.Napríklad, ak je kritériom, že množstvo by malo byť menšie ako 5, potom musíme zadať „<5“ ako kritérium (pozri obrázok nižšie):
=COUNTIF($B$2:$B$11,”<5”)
Môžete tiež zahrnúť podmienky do buniek a použiť odkazy na bunky ako podmienky.V tomto prípade nemusíte štandard uvádzať do dvojitých úvodzoviek (pozri obrázok nižšie):
=COUNTIF($B$2:$B$11,D3)
Môžete tiež chcieť, aby podmienka bola v bunke, ale nie s operátorom.Môžete napríklad chcieť, aby bunka D3 mala číslo 5 namiesto <5.
V tomto prípade musíte vytvoriť podmienený parameter, ktorý je kombináciou operátora a odkazu na bunku:
=COUNTIF($B$2:$B$11,"<"&D3)
Poznámka: Keď kombinujete operátory a odkazy na bunky, operátory sú vždy uzavreté v dvojitých úvodzovkách.Operátory a odkazy na bunky sú spojené znakom ampersand (&).
#4 Počítajte bunky 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 podmienok.
Tu sú dva spôsoby, ako to urobiť:
Metóda XNUMX: Použite funkciu COUNTIFS
Funkcia COUNTIFS dokáže spracovať viaceré podmienky ako argumenty a počítať bunky iba vtedy, ak sú všetky podmienky PRAVDA.Na počítanie buniek s hodnotami medzi dvoma určenými hodnotami, ako napríklad 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 zahrnúť tieto bunky, použite operátory väčšie alebo rovné (>=) a menšie alebo rovné (<=).Tu je vzorec:
=COUNTIFS($B$2:$B$11,”>=5″,$B$2:$B$11,”<=10″)
Tieto podmienky môžete zahrnúť aj do buniek a použiť odkazy na bunky ako podmienky.V tomto prípade nemusíte štandard uvádzať do dvojitých úvodzoviek (pozri obrázok nižšie):
Môžete tiež použiť kombináciu odkazov na bunky a operátorov (zadávanie operátorov priamo do vzorcov).Pri kombinovaní operátorov a odkazov na bunky sú operátory vždy uzavreté v úvodzovkách.Operátory a odkazy na bunky sú spojené znakom ampersand (&).
Metóda 2: Použite dve funkcie COUNTIF
Ak máte viacero podmienok, môžete použiť COUNTIFS alebo vytvoriť kombináciu funkcií COUNTIF.Nasledujúci vzorec urobí 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 s hodnotou väčšou ako 5 a potom odpočítame počet buniek s hodnotou väčšou ako 10.To nám dá výsledok 5 (tj s hodnotami väčšími ako 5 a menšími alebo rovnými 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 „5“ a „10“ z počítania, použite nasledujúci vzorec:
=COUNTIF($B$2:$B$11,”>=5″)-COUNTIF($B$2:$B$11,”>10″)-COUNTIF($B$2:$B$11,10)
Tieto podmienky môžete zahrnúť do buniek a použiť odkazy na bunky, alebo môžete použiť kombináciu operátorov a odkazov na bunky.
Použite podmienky TEXT vo funkciách Excelu
#1 Počíta bunky, keď sa podmienka rovná zadanému textu
Ak chcete spočítať bunky, ktoré obsahujú presnú zhodu so zadaným textom, môžeme tento text jednoducho použiť ako podmienku.Napríklad v množine údajov (zobrazenej nižšie), ak by som chcel spočítať všetky bunky s názvom Joe, mohol by som použiť nasledujúci vzorec:
=COUNTIF($B$2:$B$11,"Joe")
Keďže ide o textový reťazec, musím dať podmienku textu do dvojitých úvodzoviek.
Môžete tiež zahrnúť podmienku do bunky a potom použiť odkaz na túto bunku (ako je uvedené nižšie):
=COUNTIF($B$2:$B$11;E3)
Poznámka: Ak sú v rozsahu podmienok alebo podmienok na začiatku/na konci medzery, môžete získať nesprávne výsledky.Pred použitím týchto vzorcov nezabudnite vyčistiť údaje.
#2 Počítajte bunky, keď sa podmienka nerovná zadanému textu
Podobne ako v príklade vyššie môžete počítať aj bunky, ktoré neobsahujú zadaný text.Aby sme to dosiahli, musíme použiť operátor nerovná sa (<>).
Za predpokladu, že chcete spočítať všetky bunky, ktoré neobsahujú názov JOE, tu je vzorec, ktorý to dokáže:
=COUNTIF($B$2:$B$11,”<>Joe”)
Môžete tiež zahrnúť podmienky do buniek a použiť odkazy na bunky ako podmienky.V tomto prípade nemusíte štandard uvádzať do dvojitých úvodzoviek (pozri obrázok nižšie):
=COUNTIF($B$2:$B$11;E3)
Môže to byť aj prípad, keď chcete, aby bola podmienka v bunke, ale nechcete ju použiť s operátorom.Môžete napríklad chcieť, aby bunka D3 mala názov Joe namiesto <>Joe.
V tomto prípade musíte vytvoriť podmienený parameter, ktorý je kombináciou operátora a odkazu na bunku (pozri obrázok nižšie):
=COUNTIF($B$2:$B$11,”<>”&E3)
Pri kombinovaní operátorov a odkazov na bunky sú operátory vždy uzavreté v úvodzovkách.Operátory a odkazy na bunky sú spojené znakom ampersand (&).
Použitie podmienok DATE vo funkciách COUNTIF a COUNTIFS v Exceli
Excel ukladá dátumy a časy ako čísla.Môžeme ho teda použiť ako číslo.
#1 Počítajte bunky, keď sa podmienka rovná určenému dátumu
Na získanie počtu buniek, ktoré obsahujú zadaný dátum, použijeme operátor rovná sa (=) spolu s dátumom.
Ak chcete pracovať s dátumami, odporúčam použiť funkciu DATE, pretože eliminuje akúkoľvek možnosť nesprávnej hodnoty dátumu.Ak by som teda chcel napríklad použiť dátum 2015. september 9, mohol by som použiť funkciu DATE takto:
=DÁTUM(2015,9,1;XNUMX;XNUMX)
Napriek regionálnym rozdielom tento vzorec vráti rovnaký dátum.Napríklad 01-09-2015 by bol 2015. september 9 podľa syntaxe dátumu v USA a 1. január 2015 podľa syntaxe dátumu v Spojenom kráľovstve.Tento vzorec však vždy vráti 2105. septembra 9.
Tu je vzorec na výpočet počtu buniek obsahujúcich dátum 02-09-2015:
=COUNTIF($A$2:$A$11,DÁTUM(2015,9,2;XNUMX))
#2 Počítajte bunky, keď je podmienka pred alebo po zadanom dátume
Na počítanie buniek, ktoré obsahujú dátumy pred alebo po zadanom dátume, môžeme použiť operátory menšie alebo väčšie.
Napríklad, ak by som chcel spočítať všetky bunky obsahujúce dátumy po 2015. septembri 9, mohol by som použiť nasledujúci vzorec:
=COUNTIF($A$2:$A$11,”>”&DÁTUM(2015,9,2;XNUMX))
Podobne môžete spočítať aj počet buniek pred určeným dátumom.Ak chcete do počítania zahrnúť dátumy, použite operátor „rovná sa“ spolu s operátormi „väčší ako/menší ako“.
Môžete tiež použiť odkazy na bunky, ktoré obsahujú dátumy.V tomto prípade musíte použiť znak ampersand (&) na spojenie operátora (v úvodzovkách) s dátumom.
Pozrite si príklad nižšie:
=COUNTIF($A$2:$A$11”>”&F3)
#3 Počítajte bunky 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 podmienok.
Môžeme to urobiť dvoma spôsobmi – jednou funkciou COUNTIFS alebo dvomi funkciami COUNTIF.
Metóda XNUMX: Použite funkciu COUNTIFS
Funkcia COUNTIFS môže brať viaceré podmienky ako argumenty a počítať bunky iba vtedy, ak sú všetky podmienky PRAVDA.Na počítanie buniek s hodnotami medzi dvoma určenými dátumami, ako je 9. september a 2. 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átum.Ak chcete zahrnúť aj tieto dátumy, použite operátory väčšie alebo rovné (>=) a menšie alebo rovné (<=).Tu je vzorec:
=COUNTIFS($A$2:$A$11,”>=”&DATE(2015,9,2),$A$2:$A$11,”<=”&DATE(2015,9,7))
Do buniek môžete zahrnúť aj dátumy a ako kritériá použiť odkazy na bunky.V tomto prípade nemôžete nechať operátora zahrnúť dátum do bunky.Do vzorca musíte manuálne pridať operátory (uzavreté v úvodzovkách) a použiť ampersand (&) na pridanie odkazov na bunky.Pozri nižšie:
=COUNTIFS($A$2:$A$11,”>”&F3,$A$2:$A$11,”<“&G3)
Metóda 2: Použite funkciu COUNTIF
Ak máte viacero podmienok, môžete použiť funkciu COUNTIFS alebo vytvoriť kombináciu dvoch funkcií COUNTIF.Pomôže aj nasledujúci vzorec:
=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))
Vo vyššie uvedenom vzorci najskôr nájdeme počet buniek, ktorých dátum je po 9. septembri, a potom odpočítame počet buniek, ktorých dátum je po 2. septembri.Výsledkom bude 9 (to znamená počet buniek s dátumami po 7. septembri a po 7. septembri alebo skôr).
Ak nechcete, aby vzorec vypočítal 9. september aj 2. september, použite namiesto toho nasledujúci vzorec:
=COUNTIF($A$2:$A$11,”>=”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))
Ak chcete z počítania vylúčiť dva dátumy, použite nasledujúci vzorec:
=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7)-COUNTIF($A $2:$A$11,日期(2015,9,7)))
Okrem toho môžete v bunkách nastaviť štandardné dátumy a použiť odkazy na bunky (a operátory v úvodzovkách s ampersandom).
Použitie zástupných znakov v podmienkach funkcií COUNTIF a COUNTIFS
V Exceli 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 znak.Napríklad Trömp môže 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, keď iné vstavané funkcie počítania zlyhajú.Predpokladajme napríklad, že máte množinu údajov, ktorá vyzerá takto:
Teraz si uveďme niekoľko príkladov:
#1 Spočítajte bunky, ktoré obsahujú text
Na počítanie buniek s textom v nich môžeme použiť zástupný znak * (hviezdička).Keďže hviezdičky predstavujú ľubovoľný počet znakov, spočítajú sa všetky bunky, ktoré obsahujú akýkoľvek 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číta bunky, ktoré obsahujú apostrofy (a preto sa zdajú byť prázdne) alebo ktoré mohli byť zahrnuté ako súčasť vzorca.
Tu je podrobný návod na prácu s prípadmi s prázdnymi reťazcami alebo apostrofmi.
#2 Počítajte neprázdne bunky
Ak uvažujete o použití funkcie COUNTA, zamyslite sa znova.
Skúste to, možno vás sklame.COUNTA spočíta aj bunky, ktoré obsahujú prázdne reťazce (zvyčajne vrátené vzorcami ako ="" alebo keď ľudia do buniek zadajú apostrofy).Bunky, ktoré obsahujú prázdne reťazce, sa zdajú byť prázdne, ale nie sú, preto ich počíta funkcia COUNTA.
COUNTA spočíta aj bunky, ktoré obsahujú prázdne reťazce (zvyčajne vrátené vzorcami ako ="" alebo keď ľudia do buniek zadajú apostrofy).Bunky, ktoré obsahujú prázdne reťazce, sa zdajú byť prázdne, ale nie sú, preto ich počíta funkcia COUNTA.
Ak teda použijete vzorec = COUNTA (A1:A11), vráti 11, keď by mal vrátiť 10.
Tu je oprava:
=COUNTIF($A$1:$A$11,”?*”)+COUNT($A$1:$A$11)+SUMPRODUCT(–ISLOGICAL($A$1:$A$11))
Poďme pochopiť tento vzorec tak, že ho rozdelíme:
#3 Spočítajte bunky obsahujúce špecifický text
Predpokladajme, ž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ých znakov vo funkcii COUNTIF.Tu je vzorec:
=COUNTIFS($C$2:$C$11,"J*")
Podmienka J* určuje, že text v bunke má začínať písmenom J a môže obsahovať ľubovoľný počet znakov.
Ak chcete spočítať bunky, ktoré obsahujú písmená kdekoľvek v texte, obklopte ich hviezdičkou.Napríklad, ak chcete spočítať bunky, ktoré obsahujú písmeno "a", použite *a* ako podmienku.
V porovnaní s ostatnými mojimi článkami je tento nezvyčajne dlhý.Dúfam, že sa vám to páči.Dajte mi vedieť, čo si myslíte, zanechaním komentára.
