- Bevezetés az Excel COUNTIF és COUNTIFS függvényeibe
- NUMBER feltétel használata az Excel COUNTIF függvényekben
- SZÖVEG Kritériumok használata Excel funkciókban
- DÁTUMKritériumok használata az Excel COUNTIF és COUNTIFS függvényekben
- WILDCARD CHARACTERS használata kritériumokban a COUNTIF & COUNTIFS függvényekben
Az Excel számos olyan funkcióval rendelkezik, ahol a felhasználónak egyetlen vagy több feltételt kell megadnia az eredmény eléréséhez. Például, ha több feltétel alapján szeretné számolni a cellákat, használhatja az Excel COUNTIF vagy COUNTIFS függvényeit.
Ez az oktatóanyag az Excel COUNTIF és COUNTIFS funkcióinak egyetlen vagy több feltételének különböző felhasználási módjait ismerteti.
Bár ebben az oktatóanyagban elsősorban a COUNTIF és a COUNTIFS függvényekre fogok összpontosítani, mindezek a példák más Excel -funkciókban is használhatók, amelyek több kritériumot használnak bemenetként (például SUMIF, SUMIFS, AVERAGEIF és AVERAGEIFS).
Bevezetés az Excel COUNTIF és COUNTIFS függvényeibe
Először ismerkedjünk meg a COUNTIF és COUNTIFS függvények használatával az Excelben.
Excel COUNTIF függvény (egyetlen kritériumot vesz igénybe)
Az Excel COUNTIF funkció a legalkalmasabb olyan helyzetekre, amikor egyetlen kritérium alapján szeretné számolni a cellákat. Ha több feltétel alapján szeretne számolni, használja a COUNTIFS függvényt.
Szintaxis
= COUNTIF (tartomány, feltételek)
Beviteli érvek
- hatótávolság - a számolni kívánt cellatartományt.
- kritériumok - azokat a kritériumokat, amelyeket a sejtek tartományához kell viszonyítani ahhoz, hogy a sejt megszámlálható legyen.
Excel COUNTIFS függvény (több kritériumot tartalmaz)
Az Excel COUNTIFS funkció a legalkalmasabb olyan helyzetekre, amikor több feltétel alapján szeretné számolni a cellákat.
Szintaxis
= COUNTIFS (feltételtartomány1, feltétel1, [kritériumtartomány2, feltétel2]…)
Beviteli érvek
- feltételek_tartomány1 - Az a cellatartomány, amelyet kritériumok alapján szeretne értékelni1.
- kritériumok1 - azokat a feltételeket, amelyeket ki szeretne értékelni a feltétel_tartomány1 számára annak meghatározásához, hogy mely cellákat számolja.
- [feltételek_tartomány2] - Az a cellatartomány, amelyet kritériumok alapján szeretne értékelni2.
- [kritérium2] - azokat a feltételeket, amelyeket ki szeretne értékelni a feltétel_tartomány2 számára annak meghatározásához, hogy mely cellákat számolja.
Most nézzünk néhány példát több feltétel használatára az Excel COUNTIF függvényében.
NUMBER feltétel használata az Excel COUNTIF függvényekben
#1 Számolja a cellákat, ha a kritériumok egyenlőek az értékkel
Ha meg szeretné tekinteni azon cellák számát, ahol a feltétel argumentum megegyezik egy meghatározott értékkel, akkor közvetlenül megadhatja a feltételeket, vagy használhatja a feltételeket tartalmazó cellahivatkozást.
Az alábbiakban egy példa látható, ahol számoljuk a 9 -es számot tartalmazó cellákat (ami azt jelenti, hogy a kritérium argumentum 9 -gyel egyenlő). Íme a képlet:
= COUNTIF ($ B $ 2: $ B $ 11, D3)
A fenti példában (a képen) a kritériumok a D3 cellában vannak. A feltételeket közvetlenül is beírhatja a képletbe. Használhatja például a következőket is:
= COUNTIF ($ B $ 2: $ B $ 11,9)
#2 Számolja a cellákat, ha a feltételek nagyobbak, mint egy érték
A megadott értéknél nagyobb értékű cellák számának kiszámításához a nagyobb, mint operátort („>”) használjuk. Használhatjuk közvetlenül a képletben, vagy használhatunk egy cellahivatkozást, amely rendelkezik a feltételekkel.
Amikor operátort használunk az Excel feltételeiben, akkor azt idézőjelek közé kell helyeznünk. Például, ha a kritériumok 10 -nél nagyobbak, akkor „> 10” -t kell megadnunk kritériumként (lásd az alábbi képet):
Íme a képlet:
= COUNTIF ($ B $ 2: $ B $ 11, "> 10")
A feltételeket egy cellában is elhelyezheti, és a cellahivatkozást használhatja kritériumként. Ebben az esetben a kritériumokat NEM kell idézőjelbe tenni:
= COUNTIF ($ B $ 2: $ B $ 11, D3)
Előfordulhat olyan eset is, amikor azt szeretné, hogy a feltételek egy cellában legyenek, de nem szeretné, ha az operátorral együtt. Például azt szeretné, ha a D3 cella 10 -es számmal rendelkezne, és nem> 10.
Ebben az esetben létre kell hoznia egy kritérium argumentumot, amely az operátor és a cellareferencia kombinációja (lásd az alábbi képet):
= COUNTIF ($ B $ 2: $ B $ 11, ”>” & D3)
MEGJEGYZÉS: Ha kombinál egy operátort és egy cellahivatkozást, az operátor mindig dupla idézőjelek között szerepel. Az operátort és a cellahivatkozást egy & jel jelzi (&).
#3 Számolja a cellákat, ha a feltételek kevesebb, mint egy érték
A megadott értéknél kisebb értékű cellák számának kiszámításához a kevesebb, mint operátort („<“) használjuk. Használhatjuk közvetlenül a képletben, vagy használhatunk egy cellahivatkozást, amely rendelkezik a feltételekkel.
Amikor operátort használunk az Excel feltételeiben, akkor azt idézőjelek közé kell helyeznünk. Például, ha a kritérium az, hogy a számnak 5 -nél kisebbnek kell lennie, akkor „<5” -t kell megadnunk kritériumként (lásd az alábbi képet):
= COUNTIF ($ B $ 2: $ B $ 11, "<5")
A feltételeket egy cellában is elhelyezheti, és a cellahivatkozást használhatja kritériumként. Ebben az esetben NEM kell a kritériumokat idézőjelbe tenni (lásd az alábbi képet):
= COUNTIF ($ B $ 2: $ B $ 11, D3)
Ezenkívül előfordulhat olyan eset, amikor azt szeretné, hogy a feltételek egy cellában legyenek, de nem szeretné, ha az operátorral együtt. Például azt szeretné, ha a D3 cella 5 -ös számmal rendelkezne, és nem <5 -el.
Ebben az esetben létre kell hoznia egy kritérium argumentumot, amely az operátor és a cellahivatkozás kombinációja:
= COUNTIF ($ B $ 2: $ B $ 11, "<" & D3)
MEGJEGYZÉS: Ha kombinál egy operátort és egy cellahivatkozást, az operátor mindig dupla idézőjelek között szerepel. Az operátort és a cellahivatkozást egy & jel jelzi (&).
#4 Számláló cellák több kritériummal - két érték között
Ahhoz, hogy két érték közötti értékeket kapjunk, több feltételt kell használnunk a COUNTIF függvényben.
Íme két módszer erre:
1. MÓDSZER: A COUNTIFS függvény használata
A COUNTIFS függvény több kritériumot is kezelhet argumentumként, és csak akkor számolja a cellákat, ha az összes feltétel IGAZ. A két meghatározott érték (például 5 és 10) közötti értékű cellák számolá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 -ös vagy a 10 -es cellákat. Ha fel szeretné venni ezeket a cellákat, használjon egyenlőnél nagyobb (> =) és kisebb, mint (<=) operátorokat. Íme a képlet:
= COUNTIFS ($ B $ 2: $ B $ 11, "> = 5", $ B $ 2: $ B $ 11, "<= 10")
Ezeket a feltételeket a cellákban is megadhatja, és a cellahivatkozást használhatja kritériumként. Ebben az esetben NEM kell a kritériumokat idézőjelbe tenni (lásd az alábbi képet):
Használhat cellahivatkozások és operátorok kombinációját is (ahol az operátor közvetlenül be van írva a képletbe). Ha kombinál egy operátort és egy cellahivatkozást, az operátor mindig dupla idézőjelek között szerepel. Az operátort és a cellahivatkozást egy & jel jelzi (&).
2. MÓDSZER: Két COUNTIF függvény használata
Ha több kritériuma van, használhatja a COUNTIFS funkciót, vagy létrehozhatja a COUNTIF függvények kombinációját. Az alábbi képlet is ugyanezt tenné:
= COUNTIF ($ B $ 2: $ B $ 11, "> 5")-COUNTIF ($ B $ 2: $ B $ 11, "> 10")
A fenti képletben először megtaláljuk azoknak a celláknak a számát, amelyek értéke 5 -nél nagyobb, és kivonjuk a 10 -nél nagyobb értékű cellák számát. Ez az eredményt 5 -nek (azaz a sejtek számának) adja meg értékek 5 -nél nagyobbak és 10 -nél kisebbek).
Ha azt szeretné, hogy a képlet 5 -öt és 10 -et is tartalmazzon, használja 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 a „10” számolást, 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 a cellákban tárolhatja, és használhatja a cellahivatkozásokat, vagy használhatja az operátorok és a cellahivatkozások kombinációját.
SZÖVEG Kritériumok használata Excel funkciókban
#1 Számolja a cellákat, ha a kritériumok EGYENLŐK egy megadott szöveghez
A megadott szöveg pontos egyezését tartalmazó cellák számolásához egyszerűen használhatjuk ezt a szöveget kritériumként. Például az adatkészletben (amely alább látható a képen), ha az összes cellát Joe névvel akarom számolni, az alábbi képletet használhatom:
= COUNTIF ($ B $ 2: $ B $ 11, "Joe")
Mivel ez egy szöveges karakterlánc, a szöveges kritériumokat idézőjelbe kell tennem.
A feltételeket egy cellában is elhelyezheti, majd ezt a cellahivatkozást használhatja (az alábbiak szerint):
= COUNTIF ($ B $ 2: $ B $ 11, E3)
MEGJEGYZÉS: Hibás eredményeket kaphat, ha a feltételekben vagy kritériumtartományban elő-/záró szóköz található. A képletek használata előtt feltétlenül tisztítsa meg az adatokat.
#2 Számolja a cellákat, ha a feltételek NEM EGYENLŐK egy megadott szöveghez
A fenti példában látottakhoz hasonlóan számolhat olyan cellákat is, amelyek nem tartalmaznak meghatározott szöveget. Ehhez a nem egyenlő operátort () kell használnunk.
Tegyük fel, hogy meg akarja számolni az összes cellát, amely nem tartalmazza a JOE nevet, itt van a képlet, amely ezt elvégzi:
= COUNTIF ($ B $ 2: $ B $ 11, "Joe")
A feltételeket egy cellában is elhelyezheti, és a cellahivatkozást használhatja kritériumként. Ebben az esetben NEM kell a kritériumokat idézőjelbe tenni (lásd az alábbi képet):
= COUNTIF ($ B $ 2: $ B $ 11, E3)
Előfordulhat olyan eset is, amikor azt szeretné, hogy a feltételek szerepeljenek egy cellában, de nem akarják, hogy az operátorral szerepeljenek. Például azt szeretné, ha a D3 cella Joe nevet viselne, és nem Joe nevet.
Ebben az esetben létre kell hoznia egy kritérium argumentumot, amely az operátor és a cellareferencia kombinációja (lásd az alábbi képet):
= COUNTIF ($ B $ 2: $ B $ 11, ”” és E3)
Ha kombinál egy operátort és egy cellahivatkozást, az operátor mindig dupla idézőjelek között szerepel. Az operátort és a cellahivatkozást egy & jel jelzi (&).
DÁTUMKritériumok használata az Excel COUNTIF és COUNTIFS függvényekben
Az Excel számként tárolja a dátumot és az időt. Tehát ugyanúgy használhatjuk, mint a számokat.
#1 Számolja a cellákat, ha a kritériumok EGYENLŐK a megadott dátumhoz
A megadott dátumot tartalmazó cellák számának kiszámításához a dátummal együtt az egyenlő operátort (=) használjuk.
A dátum használatához a DATE funkció használatát javaslom, mivel ez megszabadít a dátumérték hibáitól. Így például, ha 2015. szeptember 1 -jét akarom használni, használhatom a DATE funkciót az alábbiak szerint:
= DÁTUM (2015,9,1)
Ez a képlet ugyanazt a dátumot adja vissza a regionális különbségek ellenére. Például 2015-09-01 az Egyesült Államok dátumszintaxisa szerint 2015. szeptember 1-je, az Egyesült Királyság dátumszintaxisa szerint pedig 2015. január 09-e lenne. Ez a képlet azonban mindig 2105 szeptember 1 -én térne vissza.
Íme a képlet a 2015-09-02 dátumot tartalmazó cellák számának kiszámításához:
= COUNTIF ($ A $ 2: $ A $ 11, DATE (2015,9,2))
#2 Számolja a cellákat, ha a feltételek a megadott dátum előtt vagy után vannak
A megadott dátum előtti vagy utáni dátumot tartalmazó cellák számításához használhatjuk az operátoroknál kisebb/nagyobb értékeket.
Például, ha meg akarom számolni az összes cellát, amely 2015. szeptember 02 -i dátumot tartalmaz, akkor a következő képletet használhatom:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,2))
Hasonlóképpen megszámolhatja a megadott dátum előtti cellák számát is. Ha egy dátumot szeretne belefoglalni a számlálásba, használja az „egyenlő” operátort és a „nagyobb/kevesebb” operátort.
Használhat cellahivatkozást is, amely dátumot tartalmaz. Ebben az esetben az operátort (kettős idézőjelek között) kombinálnia kell a dátummal egy & -jel (&) segítségével.
Lásd az alábbi példát:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” és F3)
#3 Számláló cellák több kritériummal - két dátum között
Ahhoz, hogy két érték között értékeket kapjunk, több feltételt kell használnunk a COUNTIF függvényben.
Ezt két módszerrel tehetjük meg - egyetlen COUNTIFS függvény vagy két COUNTIF függvény.
1. MÓDSZER: A COUNTIFS függvény használata
A COUNTIFS függvény több kritériumot is felvehet argumentumként, és csak akkor számolja a cellákat, ha az összes feltétel IGAZ. A két meghatározott dátum (például szeptember 2. és szeptember 7.) közötti értékkel rendelkező cellák számítá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 veszi figyelembe a megadott dátumokat tartalmazó cellákat. Ha ezeket a dátumokat is fel szeretné venni, használjon nagyobb, mint egyenlő (> =) és kisebb, mint egyenlő (<=) operátorokat. Íme a képlet:
= COUNTIFS ($ A $ 2: $ A $ 11, ”> =” & DATE (2015,9,2), $ A $ 2: $ A $ 11, ”<=” & DATE (2015,9,7))
A dátumokat is megadhatja egy cellában, és a cellahivatkozást használhatja kritériumként. Ebben az esetben nem adhatja meg az operátort a dátummal a cellákban. Manuálisan hozzá kell adnia az operátorokat a képlethez (kettős idézőjelek között), és cellahivatkozást kell hozzáadnia egy ampersand (&) segítségével. Lásd az alábbi képet:
= COUNTIFS ($ A $ 2: $ A $ 11, ">" & F3, $ A $ 2: $ A $ 11, "<" & G3)
2. MÓDSZER: A COUNTIF függvények használata
Ha több feltétele van, akkor használhat egy COUNTIFS függvényt, vagy létrehozhat két COUNTIF függvény kombinációját. Az alábbi képlet is segítene:
= 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 megkeressük azoknak a celláknak a számát, amelyeknek szeptember 2 -a utáni dátuma van, és kivonjuk a sejtek számát a szeptember 7 utáni dátumokkal. Ezzel 7 -et kapunk (ez a dátummal rendelkező cellák száma) szeptember 2 -a után és szeptember 7 -én vagy azt megelőzően).
Ha nem szeretné, hogy a képlet szeptember 2 -át és szeptember 7 -ét is figyelembe vegye, 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 ki szeretné zárni mindkét dátumot a számítá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, DATE (2015,9,7)))
Ezenkívül megadhatja a feltételek dátumait a cellákban, és használhatja a cellahivatkozásokat (a kettős idézőjelekben szereplő operátorokkal együtt az ampersand használatával).
WILDCARD CHARACTERS használata kritériumokban a COUNTIF & COUNTIFS függvényekben
Az Excelben három helyettesítő karakter található:
- * (csillag) - Bármilyen számú karaktert képvisel. Például az ex* jelentése excel, excels, példa, szakértő stb.
- ? (kérdőjel) - Egyetlen karaktert képvisel. Például Tr? Mp jelentheti Trumpot vagy Trampot.
- ~ (tilde) - Egy helyettesítő karakter (~, *,?) Azonosítására szolgál a szövegben.
A COUNTIF függvény helyettesítő karakterekkel is számolhatja a cellákat, ha más beépített számolási funkció sikertelen. Tegyük fel például, hogy rendelkezik az alább látható adatkészlettel:
Vegyünk most különböző példákat:
#1 Számolja a szöveget tartalmazó cellákat
A szöveget tartalmazó cellák számolásához használhatjuk a helyettesítő karaktert * (csillag). Mivel a csillag tetszőleges számú karaktert jelent, számba veszi az összes cellát, amely bármilyen szöveget tartalmaz. Í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 számolja, hogy a cellák aposztrófot tartalmaznak (és ezért üresnek tűnnek), vagy azokat a cellákat, amelyek üres karakterláncot (= ””) tartalmaznak, és amelyeket esetleg képlet része.
Itt található egy részletes oktatóanyag azokról az esetekről, amikor üres karakterlánc vagy aposztróf van.
Itt található egy részletes oktatóanyag azokról az esetekről, amikor üres karakterláncok vagy aposztrófok vannak.
Az alábbiakban egy videó található, amely elmagyarázza a cellák számításának különböző forgatókönyveit szöveggel.
#2 Számolja a nem üres sejteket
Ha a COUNTA funkció használatán gondolkodik, gondolja át újra.
Próbáld ki, és lehet, hogy nem sikerül. A COUNTA számítani fog egy cellát is, amely üres karakterláncot tartalmaz (gyakran képletekkel adja vissza = ””, vagy ha az emberek csak egy aposztrófot írnak be a cellába). Az üres karakterláncokat tartalmazó cellák üresnek tűnnek, de nem, ezért a COUNTA függvény számolja őket.
A COUNTA számítani fog egy cellát is, amely üres karakterláncot tartalmaz (gyakran képletekkel adja vissza = ””, vagy ha az emberek csak egy aposztrófot írnak be a cellába). Az üres karakterláncokat tartalmazó cellák üresnek tűnnek, de nem, ezért a COUNTA függvény számolja őket.
Tehát ha a = COUNTA (A1: A11) képletet használja, akkor 11 -et ad vissza, míg 10 -et.
Itt 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 lebontva:
- COUNTIF ($ N $ 8: $ N $ 18, ”?*”) - A képlet ezen része 5. értéket ad vissza. Ez magában foglal minden olyan cellát, amelyben szöveg karakter található. A? egy karaktert, a * pedig tetszőleges számú karaktert jelent. Ezért a feltételben szereplő?* Kombináció arra kényszeríti az excelt, hogy számolja azokat a cellákat, amelyek legalább egy szöveges karaktert tartalmaznak.
- COUNT ($ A $ 1: $ A $ 11) - Ez számolja az összes cellát, amely számokat tartalmaz. A fenti példában 3 -at ad vissza.
- SUMPRODUCT (-ISLOGICAL ($ A $ 1: $ A $ 11) - Ez minden logikai értékeket tartalmazó cellát számol. A fenti példában 2 -et ad vissza.
#3 Számolja meg a meghatározott szöveget tartalmazó cellákat
Tegyük fel, hogy minden olyan cellát meg akarunk számolni, ahol az értékesítési képviselő neve J -vel kezdődik. Ez könnyen elérhető, ha helyettesítő karaktert használ 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 cella szövegének J -vel kell kezdődnie, és tetszőleges számú karaktert tartalmazhat.
Ha az ábécét tartalmazó cellákat a szövegben bárhol meg szeretné számolni, akkor mindkét oldalon csillaggal kell ellátni. Például, ha meg szeretné számolni azokat a cellákat, amelyek az „a” ábécét tartalmazzák, használja a * a * feltételt.
Ez a cikk szokatlanul hosszú a többi cikkemhez képest. Remélem, élvezte. Mondja el véleményét, ha megjegyzést hagy.
Az alábbi Excel oktatóanyagok is hasznosak lehetnek:
- Számolja meg a szavak számát Excelben.
- A sejtek számlálása a háttérszín alapján az Excelben.
- Hogyan lehet összegezni egy oszlopot az Excelben (5 nagyon egyszerű módszer)
