Több feltétel használata az Excelben: COUNTIF és COUNTIFS

Az Excel számos olyan funkcióval rendelkezik, ahol a felhasználónak egy vagy több feltételt kell megadnia az eredmény eléréséhez.Ha például több feltétel alapján szeretne cellákat számolni, használhatja az Excel COUNTIF vagy COUNTIFS függvényeit.

Ez az oktatóanyag az Excel COUNTIF és COUNTIFS függvényeiben egyetlen vagy több feltétel használatának különféle módjait ismerteti.

Noha ebben az oktatóanyagban elsősorban a COUNTIF és COUNTIFS függvényekre összpontosítok, ezek a példák más Excel-függvényekkel is használhatók, amelyek több feltételt is használnak bevitelként (például SUMIF, SUMIFS, AVERAGEIF és AVERAGEIFS).

Bevezetés az Excel COUNTIF és COUNTIFS függvényeibe

Először ismerjük meg a COUNTIF és COUNTIFS függvények használatát az Excelben.

Excel COUNTIF függvény (egy feltétellel)

Az Excel COUNTIF funkciója akkor működik a legjobban, ha egyetlen feltétel alapján szeretné megszámolni a cellákat.Ha több feltétel alapján szeretne számolni, használja a COUNTIFS függvényt.

句法

=COUNTIF(tartomány, feltétel)

Bemeneti paraméterek

• Hatály -  A kiszámítani kívánt cellatartomány.
• 條件 -Kritériumok, amelyeket a megszámlálandó cellák tartományához képest kell értékelni.

Excel COUNTIFS függvény (több feltétellel)

Az Excel COUNTIFS funkciója akkor működik a legjobban, ha több feltétel alapján kíván cellákat számolni.

句法

=COUNTIFS(feltételtartomány1,feltétel1,[feltételtartomány2,feltétel2]…)

Bemeneti paraméterek

• feltételek_tartomány1 – Az 1. kritérium alapján értékelni kívánt cellatartomány.
• kritériumok1 – A kiértékelni kívánt feltétel az 1. kritérium, amely meghatározza, hogy mely cellákat kell kiértékelni.
• [criteria_range2] – Az 2. kritérium alapján értékelni kívánt cellatartomány.
• [kritérium2] – A kiértékelni kívánt feltétel a 2. kritérium, amely meghatározza, hogy mely cellákat kell megszámolni.

Most nézzünk meg néhány példát több feltétel használatára az Excel COUNTIF függvényében.

Használja a SZÁM feltételt az Excel COUNTIF függvényében

#1 Számolja meg a cellákat, ha a feltétel egyenlő egy értékkel

Azon cellák számának meghatározásához, amelyekben a feltétel paraméter megegyezik a megadott értékkel, megadhatja közvetlenül a feltételt, vagy használhatja a feltételt tartalmazó cellahivatkozást.

Az alábbiakban látható egy példa, ahol megszámoljuk a 9-es számot tartalmazó cellákat (ami azt jelenti, hogy a feltétel paraméter egyenlő 9-cel).Íme a képlet:

=COUNTIF($B$2:$B$11,D3)

A fenti példában (a képen) a kritérium a D3 cellában található.A feltételeket közvetlenül is megadhatja a képletekben.Használhatja például a következőket is:

=COUNTIF($B$2:$B$11,9)

#2 Számolja meg a cellákat, ha a feltétel nagyobb, mint az érték

A megadott értéknél nagyobb értékű cellák számának meghatározásához a nagyobb mint operátort (">") használjuk.Használhatjuk közvetlenül képletekben, vagy használhatunk cellahivatkozásokat feltételekkel.

Amikor egy operátort használunk feltételben az Excelben, azt dupla idézőjelbe kell helyeznünk.Például, ha a feltétel nagyobb, mint 10, akkor feltételként a ">10" értéket kell megadnunk (lásd az alábbi képet):

Íme a képlet:

=COUNTIF($B$2:$B$11,">10")

Feltételeket is beilleszthet a cellákba, és feltételekként cellahivatkozásokat is használhat.Ebben az esetben nem kell dupla idézőjelbe tenni a feltételt:

=COUNTIF($B$2:$B$11,D3)

Ez akkor is előfordulhat, ha egy feltételt egy cellában szeretne, de nem akarja operátorral használni.Előfordulhat például, hogy a D3 cellában lévő szám 10 helyett 10 legyen.

Ebben az esetben létre kell hoznia egy feltételes paramétert, amely az operátor és a cellahivatkozás kombinációja (lásd az alábbi képet):

=COUNTIF($B$2:$B$11,">"&D3)

Megjegyzés: Ha operátorokat és cellahivatkozásokat kombinál, az operátorok mindig dupla idézőjelek közé kerülnek.Az operátorokat és a cellahivatkozásokat egy „és” jel (&) köti össze.

#3 Számolja meg a cellákat, ha a feltétel kisebb, mint egy érték

A megadott értéknél kisebb értékű cellák számának meghatározásához a kisebb, mint operátort („<”) használjuk.Használhatjuk közvetlenül képletekben, vagy használhatunk cellahivatkozásokat feltételekkel.

Amikor egy operátort használunk feltételben az Excelben, azt dupla idézőjelbe kell helyeznünk.Például, ha a kritérium az, hogy a mennyiség 5-nél kisebb legyen, akkor kritériumként "<5"-et kell megadnunk (lásd az alábbi képet):

=COUNTIF($B$2:$B$11,"<5")

Feltételeket is beilleszthet a cellákba, és feltételekként cellahivatkozásokat is használhat.Ebben az esetben nem kell a szabványt dupla idézőjelbe tenni (lásd az alábbi képet):

=COUNTIF($B$2:$B$11,D3)

Ezenkívül érdemes lehet, hogy a feltétel a cellában legyen, de ne az operátornál.Előfordulhat például, hogy a D3 cellában 5-ös szám szerepeljen <5 helyett.

Ebben az esetben létre kell hoznia egy feltételes paramétert, amely operátor és cellahivatkozás kombinációja:

=COUNTIF($B$2:$B$11,"<"&D3)

Megjegyzés: Ha operátorokat és cellahivatkozásokat kombinál, az operátorok mindig dupla idézőjelek közé kerülnek.Az operátorokat és a cellahivatkozásokat egy „és” jel (&) köti össze.

#4 Számolja meg a cellákat több feltétellel – két érték között

A két érték közötti értékek számának kiszámításához több feltételt kell használnunk a COUNTIF függvényben.

Ennek két módja van:

XNUMX. módszer: Használja a COUNTIFS függvényt

A COUNTIFS függvény több feltételt is tud argumentumként kezelni, és csak akkor számolja a cellákat, ha minden feltétel IGAZ.A két megadott érték, például 5 és 10 közötti értékű cellák megszámlálásához a következő COUNTIFS függvényt használhatjuk:

=COUNTIFS($B$2:$B$11,”>5″,$B$2:$B$11,”<10″)

Megjegyzés: A fenti képlet nem számolja az 5-öt vagy 10-et tartalmazó cellákat.Ha fel szeretné venni ezeket a cellákat, használja a nagyobb vagy egyenlő (>=) és kisebb vagy egyenlő (<=) operátorokat.Íme a képlet:

=COUNTIFS($B$2:$B$11,”>=5″,$B$2:$B$11,”<=10″) 

Ezeket a feltételeket cellákba is beillesztheti, és feltételekként cellahivatkozásokat is használhat.Ebben az esetben nem kell a szabványt dupla idézőjelbe tenni (lásd az alábbi képet):

Használhatja a cellahivatkozások és az operátorok kombinációját is (az operátorokat közvetlenül a képletekben adja meg).Operátorok és cellahivatkozások kombinálásakor az operátorokat mindig dupla idézőjelek közé kell tenni.Az operátorokat és a cellahivatkozásokat egy „és” jel (&) köti össze.

2. módszer: Használjon két COUNTIF függvényt

Ha több feltétele van, használhatja a COUNTIFS-t, vagy létrehozhat COUNTIF-függvények kombinációját.A következő képlet ugyanezt teszi:

=COUNTIF($B$2:$B$11,”>5″)-COUNTIF($B$2:$B$11,”>10″)

A fenti képletben először megkeressük az 5-nél nagyobb értékű cellák számát, majd kivonjuk a 10-nél nagyobb értékű cellák számát.Ez 5-ös eredményt ad (azaz 5-nél nagyobb és 10-nél kisebb vagy azzal egyenlő értékekkel).

Ha azt szeretné, hogy a képlet 5-öt és 10-et is tartalmazzon, használja helyette a következő képletet:

=COUNTIF($B$2:$B$11,”>=5”)-COUNTIF($B$2:$B$11,”>10”)

Ha azt szeretné, hogy a képlet kizárja az „5” és „10” értéket a számlálásból, használja a következő képletet:

=COUNTIF($B$2:$B$11,”>=5″)-COUNTIF($B$2:$B$11,”>10″)-COUNTIF($B$2:$B$11,10)

Ezeket a feltételeket beillesztheti a cellákba, és használhat cellahivatkozásokat, vagy használhat operátorok és cellahivatkozások kombinációját is.

Használjon SZÖVEG feltételeket az Excel függvényekben

#1 Számolja a cellákat, ha a feltétel megegyezik a megadott szöveggel

A megadott szöveggel pontosan megegyező cellák megszámlálásához egyszerűen használhatjuk ezt a szöveget feltételként.Például egy adatkészletben (lásd alább), ha meg akarom számolni az összes Joe nevű cellát, a következő képletet használhatom:

=COUNTIF($B$2:$B$11,"Joe")

Mivel ez egy szöveges karakterlánc, a szövegfeltételt dupla idézőjelbe kell tennem.

Feltételt is beilleszthet egy cellába, majd használhatja azt a cellahivatkozást (az alábbiak szerint):

=COUNTIF($B$2:$B$11,E3)

Megjegyzés: Rossz eredményeket kaphat, ha a feltételben vagy a feltétel tartományban kezdő/vég szóközök vannak.A képletek használata előtt feltétlenül tisztítsa meg az adatokat.

#2 Számolja a cellákat, ha a feltétel nem egyenlő a megadott szöveggel

A fenti példában látottakhoz hasonlóan olyan cellákat is megszámolhat, amelyek nem tartalmaznak meghatározott szöveget.Ehhez a not equals operátort (<>) kell használnunk.

Feltéve, hogy meg akarja számolni az összes olyan cellát, amely nem tartalmazza a JOE nevet, a következő képlet megteheti ezt:

=COUNTIF($B$2:$B$11,"<>Joe)

Feltételeket is beilleszthet a cellákba, és feltételekként cellahivatkozásokat is használhat.Ebben az esetben nem kell a szabványt dupla idézőjelbe tenni (lásd az alábbi képet):

=COUNTIF($B$2:$B$11,E3)

Ez akkor is előfordulhat, ha egy feltételt egy cellában szeretne, de nem akarja operátorral használni.Előfordulhat például, hogy a D3 cellában a Joe név legyen a <>Joe helyett.

Ebben az esetben létre kell hoznia egy feltételes paramétert, amely az operátor és a cellahivatkozás kombinációja (lásd az alábbi képet):

=COUNTIF($B$2:$B$11,"<>"&E3)

Operátorok és cellahivatkozások kombinálásakor az operátorokat mindig dupla idézőjelek közé kell tenni.Az operátorokat és a cellahivatkozásokat egy „és” jel (&) köti össze.

A DATE feltételek használata az Excel COUNTIF és COUNTIFS függvényeiben

Az Excel a dátumokat és időpontokat számként tárolja.Tehát használhatjuk számként.

#1 Számolja a cellákat, ha a feltétel megegyezik a megadott dátummal

A megadott dátumot tartalmazó cellák számának kiszámításához az egyenlő operátort (=) használjuk a dátum mellett.

A dátumokkal való munkavégzéshez javaslom a DÁTUM függvény használatát, mivel ez kiküszöböli annak lehetőségét, hogy a dátum hibás legyen.Így például, ha a 2015. szeptember 9-i dátumot szeretném használni, a DÁTUM függvényt a következőképpen használhatom:

= DÁTUM (2015,9,1)

A regionális különbségek ellenére ez a képlet ugyanazt a dátumot adja vissza.Például a 01. 09. 2015. 2015. szeptember 9. az Egyesült Államok dátumszintaxisa szerint, és 1. január 2015. az Egyesült Királyság dátumszintaxisa szerint.Ez a képlet azonban mindig 2105. szeptember 9-jét adja vissza.

Íme a képlet a 02. 09. 2015. dátumot tartalmazó cellák számának megszámlálásához:

=COUNTIF($A$2:$A$11,DATE(2015,9,2;XNUMX;XNUMX))

#2 Számolja a cellákat, ha a feltétel a megadott dátum előtti vagy utáni

A megadott dátum előtti vagy utáni dátumot tartalmazó cellák megszámlálásához használhatunk kisebb, mint/nagyobb mint operátorokat.

Például, ha meg akarok számolni minden olyan cellát, amely 2015. szeptember 9. utáni dátumot tartalmaz, akkor a következő képletet használhatom:

=COUNTIF($A$2:$A$11,">"&DATE(2015,9,2;XNUMX;XNUMX))

Hasonlóképpen megszámolhatja a cellák számát egy adott dátum előtt.Ha dátumokat szeretne belefoglalni a számba, használja az "egyenlő" operátort a "nagyobb, mint/kisebb, mint" operátorral együtt.

Használhat dátumokat tartalmazó cellahivatkozásokat is.Ebben az esetben egy „és” jelet (&) kell használnia, hogy az operátort (kettős idézőjelben) kombinálja a dátummal.

Lásd az alábbi példát:

=COUNTIF($A$2:$A$11,">"&F3)

#3 Számolja meg a cellákat több feltétellel – két dátum között

A két érték közötti értékek számának kiszámításához több feltételt kell használnunk a COUNTIF függvényben.

Ezt kétféleképpen tehetjük meg - egyetlen COUNTIFS függvény vagy két COUNTIF függvény.

XNUMX. módszer: Használja a COUNTIFS függvényt

A COUNTIFS függvény több feltételt is felvehet argumentumként, és csak akkor számolhat cellákat, ha minden feltétel IGAZ.A két megadott dátum, például szeptember 9. és szeptember 2. közötti értékű cellák megszámlálásához a következő COUNTIFS függvényt használhatjuk:

=COUNTIFS($A$2:$A$11,”>”&DATE(2015,9,2),$A$2:$A$11,”<“&DATE(2015,9,7))

A fenti képlet nem számolja a megadott dátumot tartalmazó cellákat.Ha ezeket a dátumokat is fel szeretné venni, használja a nagyobb vagy egyenlő (>=) és kisebb vagy egyenlő (<=) operátorokat.Íme a képlet:

=COUNTIFS($A$2:$A$11,”>=”&DATE(2015,9,2),$A$2:$A$11,”<=”&DATE(2015,9,7))

Dátumokat is megadhat a cellákban, és feltételekként cellahivatkozásokat is használhat.Ebben az esetben az operátor nem írhatja be a dátumot a cellába.Manuálisan kell operátorokat hozzáadnia a képlethez (idézőjelek között), és és jelet (&) kell használnia cellahivatkozások hozzáadásához.Lásd lejjebb:

=COUNTIFS($A$2:$A$11,”>”&F3,$A$2:$A$11,”<“&G3)

2. módszer: Használja a COUNTIF függvényt

Ha több feltétele is van, használhat egy COUNTIFS függvényt, vagy hozhat létre két COUNTIF függvény kombinációját.A következő képlet is megteszi a trükköt:

=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))

A fenti képletben először keressük meg azoknak a celláknak a számát, amelyeknek a dátuma szeptember 9-a után van, majd vonjuk ki azon cellák számát, amelyeknek a dátuma szeptember 2-e utáni.Ez 9-es eredményt ad (azaz a szeptember 7. utáni és szeptember 7-e előtti dátummal rendelkező cellák számát).

Ha nem szeretné, hogy a képlet szeptember 9-át és szeptember 2-ét is kiszámolja, használja a következő képletet:

=COUNTIF($A$2:$A$11,”>=”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))

Ha két dátumot szeretne kizárni a számlálásból, használja a következő képletet:

=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)))

Ezenkívül beállíthat egy szabványos dátumot egy cellában, és használhat cellahivatkozásokat (és operátorokat kettős idézőjelben és és jellel).

Helyettesítő karakterek használata a COUNTIF és COUNTIFS függvények feltételei között

Az Excelben három helyettesítő karakter található:

1. *(Csillag) - tetszőleges számú karaktert jelent.Például az ex* jelentése lehet excel, excel, példa, szakértő stb.
2. ? (kérdőjel) - Egyetlen karaktert jelöl.Például Trömp jelentheti Trumpot vagy Csavargót.
3. ~ (tilde) - A helyettesítő karakterek (~, *,?) azonosítására szolgál a szövegben.

Használhatja a COUNTIF függvényt helyettesítő karakterekkel a cellák megszámlálásához, ha más beépített számláló funkciók meghiúsulnak.Tegyük fel például, hogy van egy adatkészlete, amely így néz ki:

Most vegyünk néhány példát:

#1 Számolja meg a szöveget tartalmazó cellákat

A szöveget tartalmazó cellák megszámlálásához használhatjuk a * helyettesítő karaktert (csillag).Mivel a csillagok tetszőleges számú karaktert jelölnek, minden szöveget tartalmazó cellát számol.Íme a képlet:

=COUNTIFS($C$2:$C$11,"*")

Megjegyzés: A fenti képlet figyelmen kívül hagyja a számokat, üres cellákat és logikai értékeket tartalmazó cellákat, de olyan cellákat számol, amelyek aposztrófokat tartalmaznak (és ezért üresen jelennek meg), vagy amelyek a képlet részeként szerepeltek.

Itt található egy részletes oktatóanyag az üres karakterláncokat vagy aposztrófokat tartalmazó esetek kezeléséhez.

#2 Számolja meg a nem üres cellákat

Ha a COUNTA függvény használatát fontolgatja, gondolja át újra.

Próbáld ki, csalódást okozhat.A COUNTA olyan cellákat is számol, amelyek üres karakterláncokat tartalmaznak (általában a képletek =""-ként adják vissza, vagy amikor az emberek csak aposztrófokat írnak be a cellákba).Az üres karakterláncokat tartalmazó cellák üresnek tűnnek, de nem azok, ezért a COUNTA függvény számolja őket.

A COUNTA olyan cellákat is számol, amelyek üres karakterláncokat tartalmaznak (általában a képletek =""-ként adják vissza, vagy amikor az emberek csak aposztrófokat írnak be a cellákba).Az üres karakterláncokat tartalmazó cellák üresnek tűnnek, de nem azok, ezért a COUNTA függvény számolja őket.

Tehát ha az = képletet használja COUNTA (A1:A11), 11-et ad vissza, amikor 10-et kell visszaadnia.

Íme a javítás:

=COUNTIF($A$1:$A$11,”?*”)+COUNT($A$1:$A$11)+SUMPRODUCT(–ISLOGICAL($A$1:$A$11))

Értsük meg ezt a képletet úgy, hogy lebontjuk:

#3 Számolja meg a meghatározott szöveget tartalmazó cellákat

Tegyük fel, hogy meg akarunk számolni minden olyan cellát, ahol az értékesítési képviselő neve J betűvel kezdődik.Ez könnyen elérhető helyettesítő karakterek használatával a COUNTIF függvényben.Íme a képlet:

=COUNTIFS($C$2:$C$11,"J*") 

A J* feltétel azt határozza meg, hogy a cellában lévő szövegnek J betűvel kell kezdődnie, és tetszőleges számú karaktert tartalmazhat.

Ha a szövegben bárhol betűket tartalmazó cellákat szeretne megszámolni, vegye körül őket csillaggal.Például, ha meg szeretné számolni az "a" betűt tartalmazó cellákat, használja az *a* feltételt.

A többi cikkemhez képest ez szokatlanul hosszú.Remélem tetszik.Mondja el, mit gondol egy megjegyzés írásával.