aplikace Excel má mnoho funkcí, kde uživatel musí zadat jedno nebo více kritérií, aby si výsledek. Například, pokud chcete počítat buňky na základě více kritérií, můžete použít funkce COUNTIF nebo COUNTIFS v aplikaci Excel.
tento tutoriál pokrývá různé způsoby použití jednoho nebo více kritérií v COUNTIF a COUNTIFS funkci v Excelu.,
Když jsem se primárně zaměřuje na COUNTIF a COUNTIFS funkce, v tomto návodu, všechny tyto příklady mohou být také použity v další funkce aplikace Excel, které se více kritérií, jako vstupy (jako SUMIF, SUMIFS, AVERAGEIF a AVERAGEIFS).
Tento tutoriál zahrnuje:
Úvod do funkcí Excel COUNTIF a COUNTIFS
nejprve se uchopíme pomocí funkcí COUNTIF a COUNTIFS v Excelu.,
Excel COUNTIF funkce (bere jednotlivá kritéria)
Excel COUNTIF funkce 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 spočítat.
- kritéria-kritéria, která musí být vyhodnocena podle rozsahu buněk pro buňku, která má být započítána.,
Excel COUNTIFS funkce (má více kritérií)
Excel COUNTIFS funkce je nejvhodnější pro situace, kdy chcete počítat buňky na základě více kritérií.
Syntax
=COUNTIFS(criteria_range1, criteria1, …)
Vstupní Argumenty
- criteria_range1 – oblast buněk, pro které chcete vyhodnocení proti criteria1.
- criteria1-kritéria, která chcete vyhodnotit pro criteria_range1 určit, které buňky počítat.
- – rozsah buněk, pro které chcete vyhodnotit criteria2.,
- – kritéria, která chcete vyhodnotit pro criteria_range2 určit, které buňky počítat.
nyní se podívejme na některé příklady použití více kritérií v COUNTIF funkcích v Excelu.
Použití POČET Kritérií v aplikaci Excel COUNTIF Funkce
#1 Počet Buněk, když Kritéria je ROVNA Hodnotě
Chcete-li získat počet buněk, kde argument kritéria je rovna zadané hodnotě, můžete buď přímo zadat kritéria, nebo použít odkaz na buňku, která obsahuje kritéria.,
níže je příklad, kdy počítáme buňky, které obsahují číslo 9 (což znamená, že argument kritéria se rovná 9). Zde je vzorec:
=COUNTIF($B$2:$B$11,D3)
Ve výše uvedeném příkladu (pic), kritéria, která je v buňce D3. Kritéria můžete také zadat přímo do vzorce., Například, můžete také použít:
=COUNTIF($B$2:$B$11,9)
#2 Počet Buněk, když Kritéria je VĚTŠÍ NEŽ Hodnota
Chcete-li získat počet buněk s hodnotou větší než specifikovaná hodnota, použijeme větší, než operátor („>“). Buď bychom ji mohli použít přímo ve vzorci, nebo použít odkaz na buňku, který má kritéria.
kdykoli použijeme operátora v aplikaci Excel, musíme jej uvést do dvojitých uvozovek., Například, pokud kritéria je větší než 10, pak musíme zadat „>10“ jako kritéria (viz obr níže):
Tady je vzorec:
=COUNTIF($B$2:$B$11,“>10″)
můžete mít také kritéria v cele a použijte odkaz na buňku jako kritéria. V tomto případě, nemusíte dát kritéria v uvozovkách:
=COUNTIF($B$2:$B$11,D3)
Tam by mohlo být také v případě, kdy chcete, kritéria musí být v buňce, ale nechci to s operátorem., Můžete například chtít, aby buňka D3 měla číslo 10 a ne >10.
V tomto případě, budete muset vytvořit argument kritéria, která je kombinací operátora a odkaz na buňku (viz obr. níže):
=COUNTIF($B$2:$B$11,“>“&D3)
POZNÁMKA: Pokud budete kombinovat provozovatel a odkaz na buňku, provozovatel je vždy v uvozovkách. Operátor a odkaz na buňku jsou spojeny ampersandem (&).,
#3 počet buněk pokud jsou kritéria menší než hodnota
pro získání počtu buněk s hodnotou menší než zadaná hodnota, používáme méně než operátor („<„). Buď bychom ji mohli použít přímo ve vzorci, nebo použít odkaz na buňku, který má kritéria.
kdykoli použijeme operátora v aplikaci Excel, musíme jej uvést do dvojitých uvozovek., Například, jestliže kritérium je to, že počet by měl být menší než 5, pak musíme zadat „<5“ jako kritéria (viz obr níže):
=COUNTIF($B$2:$B$11,“<5″)
můžete mít také kritéria v cele a použijte odkaz na buňku jako kritéria., V tomto případě, nemusíte dát kritéria v uvozovkách (viz obr níže):
=COUNTIF($B$2:$B$11,D3)
Také, tam by mohl být případ, kdy chcete, kritéria musí být v buňce, ale nechci to s operátorem. Můžete například chtít, aby buňka D3 měla číslo 5 a ne <5.,
V tomto případě, budete muset vytvořit argument kritéria, která je kombinací operátor a mobilní odkaz:
=COUNTIF($B$2:$B$11,“<„&D3)
POZNÁMKA: Pokud budete kombinovat provozovatel a odkaz na buňku, provozovatel je vždy v uvozovkách. Operátor a odkaz na buňku jsou spojeny ampersandem (&).,
#4 Počet Buněk s Více Kritérií – Mezi Dvěma Hodnotami
je spočítat hodnoty mezi dvěma hodnotami, musíme použít více kritérií v COUNTIF.
Zde jsou dvě metody, jak to udělat:
METODA 1: Použití funkce COUNTIFS
COUNTIFS funkce může zpracovat více kritérií jako argumenty a spočítá buňky pouze tehdy, když všechna kritéria jsou PRAVDIVÉ., Počítat buňky s hodnotami mezi dvě zadané hodnoty (řekněme 5 a 10), můžeme použít následující COUNTIFS funkce:
=COUNTIFS($B$2:$B$11,“>, 5″,$B$2:$B$11,“<10″)
POZNÁMKA: výše uvedený vzorec nepočítá buňky, které obsahují 5 nebo 10. Pokud chcete tyto buňky zahrnout, použijte větší než rovno (>=) a méně než rovno (<=) operátorům., Zde je vzorec:
=COUNTIFS($B$2:$B$11,“>=5″,$B$2:$B$11,“<=10″)
můžete mít také tyto kritéria v buňkách a použít odkaz na buňku jako kritéria. V tomto případě, nemusíte dát kritéria v uvozovkách (viz obr níže):
můžete také použít kombinaci buněk, odkazy a operátorů (tam, kde operátor zadává přímo ve vzorci). Když kombinujete operátora a odkaz na buňku, operátor je vždy ve dvojitých uvozovkách., Operátor a odkaz na buňku jsou spojeny ampersandem (&).
METODA 2: Pomocí dvou COUNTIF funkce,
Pokud máte více kritérií, můžete použít buď COUNTIFS nebo vytvořit kombinaci COUNTIF funkce., Níže uvedeného vzorce by také udělat stejnou věc:
=COUNTIF($B$2:$B$11,“>5″)-COUNTIF($B$2:$B$11,“>10″)
Ve výše uvedeném vzorci, musíme nejprve zjistit počet buněk, které mají hodnotu vyšší než 5 a odečteme počet buněk s hodnotou větší než 10. To by nám dalo výsledek jako 5 (což je počet buněk, které mají hodnoty větší než 5 a méně než 10).,3c219a21b4″>
Pokud chcete, aby vzorec zahrnují jak 5 a 10, použijte následující vzorec místo:
=COUNTIF($B$2:$B$11,“>=5″)-COUNTIF($B$2:$B$11,“>10″)
Pokud chcete, aby vzorec vyloučit oba ‚5‘ a ’10‘ z počítání, použijte následující vzorec:
=COUNTIF($B$2:$B$11,“>=5″)-COUNTIF($B$2:$B$11,“>10″)-COUNTIF($B$2:$B$11,10)
můžete mít tyto kritéria v buňkách a použití buněk, odkazy, nebo můžete použít kombinaci operátorů a články odkazy.,
Pomocí TEXTU Kritéria v aplikaci Excel Funkce
#1 Počet Buněk, když Kritéria je ROVNA Zadaný text
počet buněk, které obsahují přesnou shodu zadaného textu, můžeme jednoduše použít text jako kritéria. Například, v dataset (viz níže pic), když chci spočítat všechny buňky se jménem Joe, můžu použít níže uvedené vzorce:
=COUNTIF($B$2:$B$11,“Joe“)
Protože se jedná o textový řetězec, musím dát text kritéria v uvozovkách.,
můžete mít také kritéria v cele a pak použít odkaz na buňku (jak je uvedeno níže):
=COUNTIF($B$2:$B$11,E3)
POZNÁMKA: Můžete dostat špatné výsledky, pokud tam jsou předními/koncové mezery v kritériích nebo kritériích rozsahu. Před použitím těchto vzorců se ujistěte, že data vyčistíte.
#2 Počet Buněk, když Kritéria je ROVNA Zadaný text
Podobný tomu, co jsme viděli ve výše uvedeném příkladu, můžete také spočítat buňky, které neobsahují zadaný text., K tomu je třeba použít ne rovno operátorovi (<>).
Předpokládejme, že chcete spočítat všechny buňky, které neobsahují jméno JOE, tady je vzorec, který bude dělat:
=COUNTIF($B$2:$B$11,“<>Joe“)
můžete mít také kritéria v cele a použijte odkaz na buňku jako kritéria., V tomto případě, nemusíte dát kritéria v uvozovkách (viz obr níže):
=COUNTIF($B$2:$B$11,E3)
Tam by mohlo být také v případě, kdy chcete, kritéria musí být v buňce, ale nechci to s operátorem. Například můžete chtít, aby buňka D3 měla jméno Joe a ne <> Joe.,
V tomto případě, budete muset vytvořit argument kritéria, která je kombinací operátora a odkaz na buňku (viz obr. níže):
=COUNTIF($B$2:$B$11,“<>“&E3)
Když se spojí operátor a odkaz na buňku, provozovatel je vždy v uvozovkách. Operátor a odkaz na buňku jsou spojeny ampersandem (&).
použití kritérií data v Excel COUNTIF a COUNTIFS funkce
Excel store datum a čas jako čísla., Můžeme ho použít stejným způsobem jako čísla.
#1 počet buněk pokud se kritéria rovnají zadanému datu
pro získání počtu buněk, které obsahují zadané datum, použijeme rovno operátorovi ( = ) spolu s datem.
Chcete-li použít datum, doporučuji použít funkci Datum, protože se zbaví jakékoli možnosti chyby v hodnotě data. Tak, například, pokud chci použít datum 1. září, 2015, mohu použít funkce DATUM, jak je uvedeno níže:
=DATUM(2015,9,1)
Tento vzorec vrátí stejné datum i přes regionální rozdíly., Například 01-09-2015 by bylo 1.září 2015 podle syntaxe data v USA a 09. ledna 2015 podle syntaxe data ve Velké Británii. Tento vzorec by se však vždy vrátil 1. září 2105.
Tady je recept chcete-li spočítat počet buněk, které obsahují data 02-09-2015:
=COUNTIF($A$2:$A$11,DATUM(2015,9,2))
#2 Počet Buněk, kdy Kritérií je PŘED nebo PO stanoveném Datu
počet buněk, které obsahují data před nebo po zadaném datu, můžeme použít méně než/větší, než provozovatelé.,
například, pokud bych chtěl spočítat všechny buňky, které obsahují datum, které je po září 02, 2015, mohu použít vzorec:
=COUNTIF($A$2:$A$11,“>“&DATUM(2015,9,2))
Podobně můžete také spočítat počet buněk před zadaným datem. Pokud chcete do počítání zahrnout datum, použijte a „rovná se“ operátor spolu s „větší než / menší než“ operátor.
můžete také použít odkaz na buňku, který obsahuje datum., V tomto případě je třeba kombinovat operátora (v uvozovkách) s datem pomocí ampersand (&).
Viz příklad níže:
=COUNTIF($A$2:$A$11,“>“&F3)
#3 Počet Buněk s Více Kritérií – Mezi Dvěma Daty
je spočítat hodnoty mezi dvěma hodnotami, musíme použít více kritérií v COUNTIF.
můžeme to udělat pomocí dvou metod-jedné funkce COUNTIFS nebo dvou funkcí 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 tehdy, jsou-li všechna kritéria pravdivá. Počítat buňky s hodnotami mezi dvěma daty (řekněme 2. září a 7. září), můžeme použít následující COUNTIFS funkce:
=COUNTIFS($A$2:$A$11,“>“&DATUM(2015,9,2),$A$2:$A$11,“<„&DATUM(2015,9,7))
výše uvedený vzorec nepočítá buňky, které obsahují zadaná data., Pokud chcete zahrnout i tato data, použijte větší než rovno (>=) a méně než rovno (<=) operátorům. Zde je vzorec:
=COUNTIFS($A$2:$A$11,“>=“&DATUM(2015,9,2),$A$2:$A$11,“<=“&DATUM(2015,9,7))
můžete také mít data v buňce a použít odkaz na buňku jako kritéria. V takovém případě nemůžete mít operátora s datem v buňkách., Musíte ručně přidat operátory ve vzorci (ve dvojitých uvozovkách) a přidat odkaz na buňky pomocí ampersand (&). Viz obrázek níže:
=COUNTIFS($A$2:$A$11,“>“&F3,$A$2:$A$11,“<„&G3)
METODA 2: Použití funkce COUNTIF
Pokud máte více kritérií, můžete buď použít jeden COUNTIFS funkce nebo vytvořit kombinaci dvou COUNTIF funkce., Níže uvedeného vzorce by také udělat trik:
=COUNTIF($A$2:$A$11,“>“&DATUM(2015,9,2))-COUNTIF($A$2:$A$11,“>“&DATUM(2015,9,7))
Ve výše uvedeném vzorci, musíme nejprve zjistit počet buněk, které mají datum po 2. září a odečteme počet buněk s daty po 7.září. To by nám výsledek jako 7 (což je počet buněk, které mají data po 2. září a do 7. září).,>DATUM(2015,9,2))-COUNTIF($A$2:$A$11,“>“&DATUM(2015,9,7))
Pokud chcete vyjmout obě dat z počítání, použijte následující vzorec:
=COUNTIF($A$2:$A$11,“>“&DATUM(2015,9,2))-COUNTIF($A$2:$A$11,“>“&DATUM(2015,9,7)-COUNTIF($A$2:$A$11,DATUM(2015,9,7)))
Také můžete mít kritéria data do buněk a používat buněk odkazy (spolu s operátory v uvozovkách připojil pomocí ampersand).,
Použití ZÁSTUPNÉ ZNAKY v Kritériích v COUNTIF & COUNTIFS Funkce
k Dispozici jsou tři zástupné znaky v aplikaci Excel:
můžete použít COUNTIF s zástupné znaky počítat buňky, když ostatní vestavěné funkce count selže. Například, předpokládejme, že máte soubor údajů, jak je uvedeno níže:
Teď pojďme vzít různé příklady:
#1 Počet Buněk, které obsahují Text
počet buněk s textem v ní, můžeme použít zástupný znak * (hvězdička)., Vzhledem k tomu, asterisk představuje libovolný počet znaků, to by počítat všechny buňky, které mají libovolný text v něm. Zde je vzorec:
=COUNTIFS($C$2:$C$11,“*“)
Poznámka: výše uvedený vzorec ignoruje buněk, které obsahují čísla, prázdné buňky, logické hodnoty, ale bude se to počítat buňky obsahují apostrof (a proto se jeví prázdný), nebo buňky, které obsahují prázdný řetězec (tj.““) které mohou být vráceny jako součást vzorce.
zde je podrobný návod na řešení případů, kdy je prázdný řetězec nebo apostrof.,
zde je podrobný návod na řešení případů, kdy 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 v něm.
#2 počítat non-prázdné buňky
Pokud uvažujete o použití funkce COUNTA, zamyslete se znovu.
zkuste to a může vás to selhat. COUNTA bude také počítat buňku, která obsahuje prázdný řetězec (často vrácený vzorci jako =““ nebo když lidé vstoupí do buňky pouze apostrof). Buňky, které obsahují prázdné řetězce, vypadají prázdné, ale nejsou, a tak se počítají funkcí COUNTA.,
COUNTA bude také počítat buňku, která obsahuje prázdný řetězec (často vrácený vzorci jako =““ nebo když lidé vstoupí do buňky pouze apostrof). Buňky, které obsahují prázdné řetězce, vypadají prázdné, ale nejsou, a tak se počítají funkcí COUNTA.
takže pokud použijete vzorec = COUNTA(A1:A11), vrátí se 11, zatímco by se měl vrátit 10.
zde je oprava:
= COUNTIF($a$1:$a$11,“?*“)+COUNT($A$1:$A$11)+funkce sumproduct(–ISLOGICAL($A$1:$A$11))
Pojďme pochopit tento vzorec rozebrat to:
- COUNTIF($N$8:$N$18,“?,* „)- Tato část vzorce vrací 5. To zahrnuje jakoukoli buňku, která má v sobě textový znak. A ? představuje jeden znak a * představuje libovolný počet znaků. Tedy kombinace ?* v kritériích nutí excel počítat buňky, které mají v sobě 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 se vrací 3.
- SUMPRODUCT(–ISLAMOGICAL($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 obchodní zástupce jméno začíná na s. J. toho lze snadno dosáhnout pomocí zástupných znaků v COUNTIF. Zde je vzorec:
=COUNTIFS($C$2:$C$11,“J*“)
kritéria J* určuje, že text v buňce by měl začít s J a může obsahovat libovolný počet znaků.
Pokud chcete počítat buňky, které obsahují abecedu kdekoli v textu, lemujte ji hvězdičkou na obou stranách., Například, pokud chcete počítat buňky, které obsahují abecedu “ a “ v něm, použijte *a* jako kritéria.