Excel má mnoho funkcí, kde uživatel potřebuje zadat jedno nebo více kritérií, aby získal výsledek. Pokud například chcete počítat buňky na základě více kritérií, můžete v Excelu použít funkce COUNTIF nebo COUNTIFS.
Tento tutoriál pokrývá různé způsoby použití jednoho nebo více kritérií ve funkci COUNTIF a COUNTIFS v aplikaci Excel.
I když se v tomto kurzu budu primárně soustředit na funkce COUNTIF a COUNTIFS, všechny tyto příklady lze použít také v jiných funkcích aplikace Excel, které jako vstupy používají více kritérií (například SUMIF, SUMIFS, AVERAGEIF a AVERAGEIFS).
Úvod do funkcí Excel COUNTIF a COUNTIFS
Pojďme se nejprve podívat na používání funkcí COUNTIF a COUNTIFS v Excelu.
Funkce Excel COUNTIF (vyžaduje jedno kritérium)
Funkce Excel COUNTIF je nejvhodnější pro situace, kdy chcete počítat buňky na základě jediného kritéria. Pokud chcete počítat na základě více kritérií, použijte funkci COUNTIFS.
Syntax
= COUNTIF (rozsah, kritéria)
Vstupní argumenty
- rozsah - rozsah buněk, které chcete počítat.
- kritéria - kritéria, která musí být vyhodnocena na základě rozsahu buněk, aby byla buňka započítána.
Funkce Excel COUNTIFS (vyžaduje více kritérií)
Funkce Excel COUNTIFS je nejvhodnější pro situace, kdy chcete počítat buňky na základě více kritérií.
Syntax
= COUNTIFS (rozsah_kritérií1, kritéria1, [rozsah_kritérií2, kritéria2]…)
Vstupní argumenty
- rozsah_kritérií1 - Rozsah buněk, pro které chcete vyhodnotit podle kritérií1.
- Kritéria 1 - kritéria, která chcete vyhodnotit pro kritérium_rozsahu1, abyste určili, které buňky se mají počítat.
- [rozsah_kritérií2] - Rozsah buněk, pro které chcete vyhodnotit podle kritérií2.
- [kritéria2] - kritéria, která chcete vyhodnotit pro kritérium_rozsah2, abyste určili, které buňky se mají počítat.
Nyní se podívejme na několik příkladů použití více kritérií ve funkcích COUNTIF v aplikaci Excel.
Použití NUMBER kritérií ve funkcích Excel COUNTIF
#1 Počítejte buňky, pokud jsou kritéria ROVNÁ k hodnotě
Chcete -li získat počet buněk, kde se argument kritéria rovná zadané hodnotě, můžete buď přímo zadat kritéria, nebo použít odkaz na buňku, který kritéria obsahuje.
Níže je uveden příklad, kde spočítáme buňky obsahující číslo 9 (což znamená, že argument kritéria je roven 9). Zde je vzorec:
= COUNTIF ($ B $ 2: $ B $ 11, D3)
Ve výše uvedeném příkladu (na obrázku) jsou kritéria v buňce D3. Kritéria můžete také zadat přímo do vzorce. Můžete například také použít:
= COUNTIF ($ B $ 2: $ B $ 11,9)
#2 Počítejte buňky, pokud jsou kritéria VĚTŠÍ NEŽ hodnota
Abychom získali počet buněk s hodnotou větší než zadaná hodnota, použijeme operátor větší než („>“). Mohli bychom to buď použít přímo ve vzorci, nebo použít odkaz na buňku, který má kritéria.
Kdykoli použijeme operátor v kritériích v Excelu, musíme jej dát do uvozovek. Pokud je například kritéria větší než 10, musíme jako kritéria zadat „> 10“ (viz obrázek níže):
Zde je vzorec:
= COUNTIF ($ B $ 2: $ B $ 11, ”> 10 ″)
Kritéria můžete mít také v buňce a jako kritéria použít odkaz na buňku. V takovém případě NEPOUŽÍVEJTE kritéria do uvozovek:
= COUNTIF ($ B $ 2: $ B $ 11, D3)
Může také nastat případ, kdy chcete, aby byla kritéria v buňce, ale nechtěli byste to s operátorem. Můžete například chtít, aby buňka D3 měla číslo 10 a ne> 10.
V takovém případě musíte vytvořit argument kritéria, který je kombinací odkazu na operátor a buňku (viz obrázek níže):
= COUNTIF ($ B $ 2: $ B $ 11, ”>” & D3)
POZNÁMKA: Když zkombinujete operátor a odkaz na buňku, operátor je vždy v uvozovkách. Odkaz na operátor a buňku je spojen znakem & ().
#3 Počítejte buňky, pokud je kritéria MÉNĚ NEŽ hodnota
Abychom získali počet buněk s hodnotou menší než zadaná hodnota, použijeme operátor less than („<“). Mohli bychom to buď použít přímo ve vzorci, nebo použít odkaz na buňku, který má kritéria.
Kdykoli použijeme operátor v kritériích v Excelu, musíme jej dát do uvozovek. Pokud je například kritériem, že by číslo mělo být menší než 5, pak musíme jako kritérium zadat „<5“ (viz obrázek níže):
= COUNTIF ($ B $ 2: $ B $ 11, ”<5 ″)
Kritéria můžete mít také v buňce a jako kritéria použít odkaz na buňku. V takovém případě NEPOUŽÍVEJTE kritéria do uvozovek (viz obrázek níže):
= COUNTIF ($ B $ 2: $ B $ 11, D3)
Může také nastat případ, kdy chcete, aby byla kritéria v buňce, ale nechtěli byste to s operátorem. Můžete například chtít, aby buňka D3 měla číslo 5 a ne <5.
V takovém případě musíte vytvořit argument kritéria, který je kombinací odkazu na operátor a buňku:
= COUNTIF ($ B $ 2: $ B $ 11, ”<“ & D3)
POZNÁMKA: Když zkombinujete operátor a odkaz na buňku, operátor je vždy v uvozovkách. Odkaz na operátor a buňku je spojen znakem & ().
#4 Počet buněk s více kritérii - mezi dvěma hodnotami
Abychom získali počet hodnot mezi dvěma hodnotami, musíme ve funkci COUNTIF použít více kritérií.
Zde jsou dva způsoby, jak toho dosáhnout:
METODA 1: Použití funkce COUNTIFS
Funkce COUNTIFS dokáže zpracovat více kritérií jako argumenty a počítá buňky, pouze pokud jsou všechna kritéria PRAVDA. K počítání buněk s hodnotami mezi dvěma zadanými hodnotami (řekněme 5 a 10) můžeme použít následující funkci COUNTIFS:
= COUNTIFS ($ B $ 2: $ B $ 11, ”> 5 ″, $ B $ 2: $ B $ 11,” <10 ″)
POZNÁMKA: Výše uvedený vzorec nepočítá buňky obsahující 5 nebo 10. Pokud chcete tyto buňky zahrnout, použijte operátory větší než rovná se (> =) a menší než rovno (<=). Zde je vzorec:
= COUNTIFS ($ B $ 2: $ B $ 11, ”> = 5 ″, $ B $ 2: $ B $ 11,” <= 10 ″)
Tato kritéria můžete mít také v buňkách a jako kritéria použít odkaz na buňku. V takovém případě NEPOUŽÍVEJTE kritéria do uvozovek (viz obrázek níže):
Můžete také použít kombinaci odkazů na buňky a operátorů (kde je operátor zadán přímo ve vzorci). Když kombinujete operátor a odkaz na buňku, operátor je vždy v uvozovkách. Odkaz na operátor a buňku je spojen znakem & ().
METODA 2: Použití dvou funkcí COUNTIF
Pokud máte více kritérií, můžete buď použít COUNTIFS, nebo vytvořit kombinaci funkcí COUNTIF. Níže uvedený vzorec by také udělal totéž:
= COUNTIF ($ B $ 2: $ B $ 11, ”> 5 ″)-COUNTIF ($ B $ 2: $ B $ 11,”> 10 ″)
Ve výše uvedeném vzorci nejprve zjistíme počet buněk, které mají hodnotu větší než 5, a odečteme počet buněk s hodnotou větší než 10. Tím bychom získali výsledek jako 5 (což je počet buněk, které mají hodnotu hodnoty vyšší než 5 a menší než rovno 10).
Pokud chcete, aby vzorec obsahoval 5 i 10, použijte místo toho následující vzorec:
= COUNTIF ($ B $ 2: $ B $ 11, ”> = 5 ″)-COUNTIF ($ B $ 2: $ B $ 11,”> 10 ″)
Pokud chcete, aby vzorec vyloučil z počítání „5“ i „10“, použijte následující vzorec:
= COUNTIF ($ B $ 2: $ B $ 11, ”> = 5 ″)-COUNTIF ($ B $ 2: $ B $ 11,”> 10 ″)-COUNTIF ($ B $ 2: $ B $ 11,10)
Tato kritéria můžete mít v buňkách a používat odkazy na buňky, nebo můžete použít kombinaci operátorů a odkazů na buňky.
Použití TEXTových kritérií ve funkcích Excelu
#1 Počítejte buňky, když jsou kritéria ROVNÁ pro zadaný text
Chcete -li počítat buňky, které obsahují přesnou shodu se zadaným textem, můžeme tento text jednoduše použít jako kritéria. Například v datové sadě (zobrazené níže na obrázku), pokud chci spočítat všechny buňky se jménem Joe, mohu použít níže uvedený vzorec:
= COUNTIF ($ B $ 2: $ B $ 11, „Joe“)
Protože se jedná o textový řetězec, musím dát textová kritéria do uvozovek.
Kritéria můžete mít také v buňce a poté použít odkaz na tuto buňku (jak je uvedeno níže):
= COUNTIF ($ B $ 2: $ B $ 11, E3)
POZNÁMKA: Pokud jsou v kritériích nebo v rozsahu kritérií mezery, můžete získat nesprávné výsledky. Před použitím těchto vzorců nezapomeňte vyčistit data.
#2 Počítejte buňky, pokud kritéria NEJSOU ROVNÁ pro zadaný text
Podobně jako jsme viděli ve výše uvedeném příkladu, můžete také počítat buňky, které neobsahují zadaný text. K tomu je třeba použít operátor nerovný operátor ().
Předpokládejme, že chcete spočítat všechny buňky, které neobsahují název JOE, zde je vzorec, který to udělá:
= COUNTIF ($ B $ 2: $ B $ 11, „Joe“)
Kritéria můžete mít také v buňce a jako kritéria použít odkaz na buňku. V takovém případě NEPOUŽÍVEJTE kritéria do uvozovek (viz obrázek níže):
= COUNTIF ($ B $ 2: $ B $ 11, E3)
Může také nastat případ, kdy chcete, aby kritéria byla v buňce, ale nechcete to s operátorem. Můžete například chtít, aby buňka D3 měla jméno Joe a ne Joe.
V takovém případě musíte vytvořit argument kritéria, který je kombinací odkazu na operátor a buňku (viz obrázek níže):
= COUNTIF ($ B $ 2: $ B $ 11, ”” a E3)
Když kombinujete operátor a odkaz na buňku, operátor je vždy v uvozovkách. Odkaz na operátor a buňku je spojen znakem & ().
Použití kritérií DATUM ve funkcích Excel COUNTIF a COUNTIFS
Excel ukládá datum a čas jako čísla. Můžeme ji tedy použít stejným způsobem jako čísla.
#1 Počítejte buňky, pokud jsou kritéria ROVNÁ k určenému datu
Abychom získali počet buněk, které obsahují zadané datum, použili bychom s operátorem rovná se operátor (=).
Chcete -li použít datum, doporučuji použít funkci DATE, protože se zbavuje jakékoli možnosti chyby v hodnotě data. Pokud například chci použít datum 1. září 2015, mohu použít funkci DATE, jak je uvedeno níže:
= DATUM (2015,9,1)
Navzdory regionálním rozdílům by tento vzorec vrátil stejné datum. Například 01-09-2015 by bylo 1. září 2015 podle americké syntaxe data a 9. ledna 2015 podle britské syntaxe data. Tento vzorec by však vždy vrátil 1. září 2105.
Zde je vzorec pro počítání počtu buněk, které obsahují datum 02-09-2015:
= COUNTIF ($ A $ 2: $ A $ 11, DATUM (2015,9,2))
#2 Počítejte buňky, pokud jsou kritéria PŘED nebo PO určitém datu
K počítání buněk, které obsahují datum před nebo po zadaném datu, můžeme použít operátory menší než/větší než.
Pokud například chci spočítat všechny buňky, které obsahují datum, které je po 02. září 2015, mohu použít vzorec:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” A DATUM (2015,9,2))
Podobně můžete také spočítat počet buněk před zadaným datem. Chcete -li do počítání zahrnout datum, použijte operátor a „rovný“ spolu s operátorem „větší než/menší než“.
Můžete také použít odkaz na buňku, který obsahuje datum. V takovém případě je třeba spojit operátor (v uvozovkách) s datem pomocí znaku ampersand (&).
Viz příklad níže:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” & F3)
#3 Počet buněk s více kritérii - mezi dvěma daty
Abychom získali počet hodnot mezi dvěma hodnotami, musíme ve funkci COUNTIF použít více kritérií.
Můžeme to udělat dvěma způsoby - jednou funkcí COUNTIFS nebo dvěma funkcemi COUNTIF.
METODA 1: Použití funkce COUNTIFS
Funkce COUNTIFS může mít jako argumenty více kritérií a počítá buňky, pouze pokud jsou všechna kritéria PRAVDA. K počítání buněk s hodnotami mezi dvěma zadanými daty (řekněme 2. září a 7. září) můžeme použít následující funkci COUNTIFS:
= COUNTIFS ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,2), $ A $ 2: $ A $ 11, ”<“ & DATE (2015,9,7))
Výše uvedený vzorec nepočítá buňky, které obsahují zadaná data. Pokud chcete zahrnout i tato data, použijte operátory větší než rovno (> =) a menší než rovno (<=). Zde je vzorec:
= COUNTIFS ($ A $ 2: $ A $ 11, ”> =” & DATE (2015,9,2), $ A $ 2: $ A $ 11, ”<=” & DATE (2015,9,7))
Můžete také mít data v buňce a jako kritéria použít odkaz na buňku. V takovém případě nemůžete mít v buňkách operátor s datem. Ve vzorci musíte ručně přidat operátory (v uvozovkách) a přidat odkaz na buňku pomocí ampersandu (&). Viz obrázek níže:
= COUNTIFS ($ A $ 2: $ A $ 11, ”>” & F3, $ A $ 2: $ A $ 11, ”<“ & G3)
METODA 2: Používání funkcí COUNTIF
Pokud máte více kritérií, můžete použít buď jednu funkci COUNTIFS, nebo vytvořit kombinaci dvou funkcí COUNTIF. Následující vzorec by také stačil:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,7))
Ve výše uvedeném vzorci nejprve zjistíme počet buněk, které mají datum po 2. září, a odečteme počet buněk s daty po 7. září. Tím bychom získali výsledek jako 7 (což je počet buněk, které mají data po 2. září a 7. září nebo dříve).
Pokud nechcete, aby vzorec počítal jak 2. září, tak 7. září, použijte místo toho následující vzorec:
= COUNTIF ($ A $ 2: $ A $ 11, ”> =” & DATE (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,7))
Pokud chcete z počítání vyloučit obě data, použijte následující vzorec:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ”>“ & DATE (2015,9,7) -COUNTIF ($ A $ 2: $ A $ 11, DATUM (2015,9,7)))
Také můžete mít data kritérií v buňkách a používat odkazy na buňky (společně s operátory v uvozovkách spojených pomocí ampersandu).
Použití WILDCARD CHARACTERS v kritériích ve funkcích COUNTIF & COUNTIFS
V aplikaci Excel jsou tři zástupné znaky:
- * (hvězdička) - Představuje libovolný počet znaků. Například ex* může znamenat excel, exceles, příklad, expert atd.
- ? (otazník) - Představuje jeden jediný znak. Například Tr? Mp může znamenat Trump nebo Tramp.
- ~ (vlnovka) - Používá se k identifikaci zástupných znaků (~, *,?) V textu.
Funkci COUNTIF se zástupnými znaky můžete použít k počítání buněk, pokud selže jiná vestavěná funkce počítání. Předpokládejme například, že máte sadu dat, jak je uvedeno níže:
Nyní si vezměme různé příklady:
#1 Počítejte buňky obsahující text
K počítání buněk s textem v něm můžeme použít zástupný znak * (hvězdička). Protože hvězdička představuje libovolný počet znaků, započítá všechny buňky, které obsahují libovolný text. Zde je vzorec:
= COUNTIFS ($ C $ 2: $ C $ 11, ”*”)
Poznámka: Výše uvedený vzorec ignoruje buňky, které obsahují čísla, prázdné buňky a logické hodnoty, ale počítá, že buňky obsahují apostrof (a proto se zdají prázdné) nebo buňky obsahující prázdný řetězec (= ””), který mohl být vrácen jako část vzorce.
Zde je podrobný návod k řešení případů, kdy je prázdný řetězec nebo apostrof.
Zde je podrobný návod k řešení případů, kde jsou prázdné řetězce nebo apostrofy.
Níže je video, které vysvětluje různé scénáře počítání buněk s textem.
#2 Počítejte neprázdné buňky
Pokud uvažujete o použití funkce COUNTA, přemýšlejte znovu.
Zkuste to a možná vás to nepovede. COUNTA také započítá buňku, která obsahuje prázdný řetězec (často vrácený vzorci jako = ”” nebo když lidé do buňky zadají pouze apostrof). Buňky, které obsahují prázdné řetězce, vypadají prázdné, ale nejsou, a proto jsou počítány funkcí COUNTA.
COUNTA také započítá buňku, která obsahuje prázdný řetězec (často vrácený vzorci jako = ”” nebo když lidé do buňky zadají pouze apostrof). Buňky, které obsahují prázdné řetězce, vypadají prázdné, ale nejsou, a proto jsou počítány funkcí COUNTA.
Pokud tedy použijete vzorec = COUNTA (A1: A11), vrátí 11, zatímco by měl vrátit 10.
Zde je oprava:
= COUNTIF ($ A $ 1: $ A $ 11, ”?*”)+COUNT ($ A $ 1: $ A $ 11)+SUMPRODUCT (-ISLOGICAL ($ A $ 1: $ A $ 11))
Pojďme tento vzorec pochopit rozdělením:
- COUNTIF ($ N $ 8: $ N $ 18, ”?*”) - Tato část vzorce vrací 5. To zahrnuje jakoukoli buňku, která obsahuje textový znak. A? představuje jeden znak a * představuje libovolný počet znaků. Kombinace?* V kritériích tedy nutí Excel počítat buňky, které obsahují alespoň jeden textový znak.
- COUNT ($ A $ 1: $ A $ 11) - To počítá všechny buňky, které obsahují čísla. Ve výše uvedeném příkladu vrátí 3.
- SUMPRODUCT (-ISLOGICAL ($ A $ 1: $ A $ 11) - To počítá všechny buňky, které obsahují logické hodnoty. Ve výše uvedeném příkladu vrací 2.
#3 Počet buněk, které obsahují konkrétní text
Řekněme, že chceme spočítat všechny buňky, kde název obchodního zástupce začíná na J. To lze snadno dosáhnout použitím zástupného znaku ve funkci COUNTIF. Zde je vzorec:
= COUNTIFS ($ C $ 2: $ C $ 11, „J*“)
Kritérium J* určuje, že text v buňce by měl začínat J a může obsahovat libovolný počet znaků.
Pokud chcete počítat buňky obsahující abecedu kdekoli v textu, ohraničte je hvězdičkou na obou stranách. Pokud například chcete počítat buňky, které v sobě obsahují abecedu „a“, použijte jako kritéria * a *.
Tento článek je ve srovnání s mými ostatními články neobvykle dlouhý. Doufám, že jste si to užili. Sdělte mi své myšlenky zanecháním komentáře.
Také se vám mohou hodit následující tutoriály k Excelu:
- Spočítejte počet slov v Excelu.
- Počítejte buňky podle barvy pozadí v Excelu.
- Jak sečíst sloupec v aplikaci Excel (5 opravdu snadných způsobů)
