Ez a bemutató bemutatja, hogyan kell használniExcel COUNTIF és COUNTIFS Functionok az Excel programban bizonyos feltételeknek megfelelő adatokat számlálni.
A COUNTIF funkció áttekintése
Az Excel COUNTIF funkciójával megszámolhatja az adott értéket tartalmazó cellákat, megszámolhatja az értéket meghaladó vagy azzal egyenlő cellákat stb.
(Figyelje meg, hogyan jelennek meg a képletbemenetek)
COUNTIF függvény szintaxisa és érvei:
= COUNTIF (tartomány, feltételek)
hatótávolság - A számolni kívánt sejtek tartománya.
kritériumok - A kritériumok, amelyek meghatározzák, hogy mely cellákat kell számolni.
Mi a COUNTIF funkció?
A COUNTIF függvény a táblázatokban használt egyik régebbi funkció. Egyszerűen fogalmazva, remekül letapogat egy tartományt, és megmondja, hány cella felel meg ennek a feltételnek. Megnézzük, hogyan működik a függvény szöveggel, számokkal és dátumokkal; valamint néhány egyéb esetlegesen felmerülő helyzet.
Alapvető példa
Kezdjük azzal, hogy megnézzük ezt a véletlenszerű elemek listáját. Van néhány szám, üres cella és néhány szöveg.
Ha szeretné tudni, hogy hány elem pontosan egyezik a feltételekkel, akkor megadhatja, hogy mit szeretne keresni második argumentumként. Ennek a képletnek egy példája így nézhet ki
= COUNTIF (A2: A9, "Apple")
Ez a képlet a 3 -as számot adja vissza, mivel a tartományunkban 3 cella felel meg ezeknek a feltételeknek. Alternatív megoldásként használhatunk cellahivatkozást az érték keménykódolása helyett. Ha a G2 cellába „Apple” -t írnánk, akkor a képletet erre változtathatnánk
= COUNTIF (A2: A9, G2)
A számok kezelésekor fontos különbséget tenni a számok és a szövegként tárolt számok között. A képletek írása során általában nem tesz idézőjelet a számok köré. Tehát, ha olyan képletet szeretne írni, amely ellenőrzi az 5 -ös számot, akkor írjon
= COUNTIF (A2: A9, 5)
Végül egy üres cellákat is ellenőrizhetünk egy nulla hosszúságú karakterlánc használatával. Ezt a képletet így írnánk
= COUNTIF (A2: A9, "")
jegyzet: Ez a képlet mind a valóban üres cellákat, mind a képlet eredményeként üres cellákat számolja, például egy IF függvényt.
Részleges egyezések
A COUNTIF függvény támogatja a „*” vagy „?” Helyettesítő karakterek használatát a feltételekben. Nézzük meg az ízletes pékáruk listáját:
Ahhoz, hogy megtaláljuk az Apple -vel kezdődő összes elemet, írhatjuk az „Apple*” -t. Tehát, hogy 3 -as választ kapjunk, a D2 képletünk az
= COUNTIF (A2: A5, "Apple*")
Jegyzet: A COUNTIF függvény nem különbözteti meg a kis- és nagybetűket, ezért tetszés szerint írhat „alma*” -t is.
Visszatérve péksüteményeinkhez, azt is szeretnénk megtudni, hogy hány pite van a listánkon. Ezt megtalálhatjuk, ha a helyettesítő karaktert a keresési kifejezésünk elejére helyezzük, és írhatunk
= COUNTIF (A2: A5, "*torta")
Ez a képlet 2 eredményt ad.
Helyettesítő karaktereket is használhatunk szöveges cellák keresésére. Térjünk vissza az eredeti adatok listájához.
Ha meg akarjuk számolni a legalább néhány szöveget tartalmazó cellák számát, tehát nem számolunk számokat vagy üres cellákat, írhatunk
= COUNTIF (A2: A9, "*")
Láthatja, hogy képletünk helyesen adja vissza a 4 -es eredményt.
Összehasonlító operátorok a COUNTIF -ban
Az eddigi kritériumok írásakor arra utaltunk, hogy összehasonlító operátorunk „=”. Valójában ezt írhattuk volna:
= COUNTIF (A2: A9, "= Apple")
Ez azonban egy extra karakter, amit ki kell írni, ezért általában kihagyják. Ez azonban azt jelenti, hogy használhatja a többi operátort, például nagyobbat, kisebbet vagy nem egyenlőt. Nézzük a feljegyzett korok listáját:
Ha tudni akarjuk, hány gyermek legalább 5 éves, írhatunk egy „nagyobb vagy egyenlő” összehasonlítást, így:
= COUNTIF (A2: A8, "> = 5")
Jegyzet: Az összehasonlító operátort mindig szöveges karakterláncként adjuk meg, ezért idézőjelek között kell lennie.
Hasonlóképpen ellenőrizheti az adott értéknél kisebb elemeket is. Ha ki kell derítenünk, hányan vannak kevesebbek 8 -nál, kiírhatjuk
= COUNTIF (A2: A8, "<8")
Ez adja meg a kívánt 5-ös eredményt. Most képzeljük el, hogy az összes 6 éves gyerek kirándulni megy. Hány gyerek marad? Ezt a „nem egyenlő” összehasonlítással találhatjuk ki, mint például:
= COUNTIF (A2: A8, "6")
Most gyorsan láthatjuk, hogy van 6 gyermekünk, akik nem 6 évesek.
Ezekben az összehasonlító példákban eddig keményen kódoltuk a kívánt értékeket. Használhat cellahivatkozást is. A trükk az, hogy össze kell kapcsolni az összehasonlító operátort a cellahivatkozással. Tegyük fel, hogy a 7 -es számot a C2 cellába tesszük, és azt szeretnénk, ha a D2 képletünk megmutatná, hány gyerek van 7 évesnél fiatalabb.
A D2 képletünknek így kell kinéznie:
= COUNTIF (A2: A8, "<" & C2)
jegyzet: Különös figyelmet fordítson ezekre a képletekre, amikor meg kell adnia egy elemet idézőjelek között vagy kívül. Az operátorok mindig az idézetekben vannak, a cellahivatkozások mindig az idézeteken kívül vannak. A számok kívül vannak, ha pontos egyezést végez, de belül, ha összehasonlító operátort.
Dátumokkal való munka
Láttuk, hogyan adhat meg szöveget vagy számot kritériumként, de mi van akkor, ha dátumokkal kell dolgoznunk? Íme egy gyors minta lista, amellyel dolgozhatunk:
Ahhoz, hogy megszámoljuk, hány dátum van május 4 -e után, óvatosnak kell lennünk. A számítógépek számként tárolják a dátumokat, ezért meg kell győződnünk arról, hogy a számítógép a megfelelő számot használja. Ha ezt a képletet írnánk, helyes eredményt kapnánk?
= COUNTIF (A2: A9, "
A válasz „lehetséges”. Mivel az évet kihagytuk a kritériumainkból, a számítógép feltételezi, hogy az aktuális évet értjük. Ha minden dátum, amellyel dolgozunk, az adott évre vonatkozik, akkor a helyes választ kapjuk. Ha azonban vannak olyan dátumok, amelyek a jövőben vannak, rossz választ kapunk. Továbbá, ha a következő év elkezdődik, ez a képlet más eredményt ad vissza. Mint ilyen, ezt a szintaxist valószínűleg el kell kerülni.
Mivel nehéz lehet a dátumokat helyesen írni egy képletbe, a legjobb gyakorlat, ha a használni kívánt dátumot egy cellába írja, majd ezt a cellahivatkozást használhatja a COUNTIF képletben. Írjuk tehát a 2020. május 7-i dátumot a C2 cellába, és akkor a C4-be tehetjük a képletünket.
A C4 képlet az
= COUNTIF (A2: A9, "<" & C2)
Most már tudjuk, hogy a 7 -es eredmény helyes, és a válasz nem változik váratlanul, ha ezt a táblázatot megnyitjuk valamikor a jövőben.
Mielőtt elhagynánk ezt a részt, gyakori, hogy a TODAY funkciót használjuk, ha dátumokkal dolgozunk. Ezt ugyanúgy használhatjuk, mint egy cellareferenciát. Például megváltoztathatjuk az előző képletet így:
= COUNTIF (A2: A9, "<" & TODAY ())
A képletünk továbbra is frissülni fog a valós idejű előrehaladtával, és a mainál kevesebb elem lesz.
Több feltétel és COUNTIFS
Az eredeti COUNTIF funkció 2007 -ben javult, amikor megjelent a COUNTIFS. A kettő közötti szintaxis nagyon hasonló, az utóbbi lehetővé teszi további tartományok és feltételek megadását. Könnyedén használhatja a COUNTIFS -t minden olyan helyzetben, ahol COUNTIF létezik. Csak jó ötlet tudni, hogy mindkét funkció létezik.
Nézzük ezt az adattáblát:
Ha meg szeretné tudni, hogy hányan vannak az 1–2 fizetési szinteken, írjon össze egy COUNTIF függvényt, mint ez:
= COUNTIF (B2: B7, "> = 1")-COUNTIF (B2: B7, "> 2")
Ez a képlet működni fog, mivel mindent megtalál, ami 1 felett van, de kivonja a rekordok számát meghaladó rekordok számát. Alternatív megoldásként használhatja a következő COUNTIFS -t:
= COUNTIFS (B2: B7, "> = 1", B2: B7, "<= 2")
Ez utóbbi intuitívabb olvasni, ezért érdemes ezt az útvonalat használni. Ezenkívül a COUNTIFS hatékonyabb, ha több oszlopot kell figyelembe vennie. Tegyük fel, hogy szeretnénk tudni, hányan vannak a menedzsmentben és az 1. bérszintben. Ezt nem teheti meg csak COUNTIF -el; ki kell írnia
= COUNTIFS (A2: A7, "Kezelés", B2: B7, 1)
Ez a képlet megadja a helyes 2. eredményt. Mielőtt elhagynánk ezt a részt, fontoljuk meg az Vagy típusú logikát. Mi lenne, ha szeretnénk megtudni, hányan vannak a menedzsmentben vagy? Össze kell adnia néhány COUNTIFS -t, de ezt kétféleképpen lehet elvégezni. Az egyszerűbb módszer az, hogy így írod:
= COUNTIF (A2: A7, "HR")+COUNTIF (A2: A7, "Management")
Használhat egy tömböt is, és írhatja ezt a tömbképletet:
= SUM (COUNTIF (A2: A7, {"HR", "Management"})))
Jegyzet: A tömbképleteket a "Ctrl+Shift+Enter" billentyűkombinációval kell megerősíteni, nem csak az "Enter" billentyűvel.
Ez a képlet hogyan fog működni, ha látni fogja, hogy tömböt adott meg bemenetként. Így kiszámítja az eredményt két különböző COUNTIF függvényre, és tárolja őket egy tömbben. A SUM függvény ezután összesíti a tömb összes eredményét, hogy egyetlen kimenetet hozzon létre. Így a képletünket a következőképpen értékeljük:
= SUM (COUNTIF (A2: A7, {"HR", "Management"}))) = SUM ({2, 3}) = 5
Számoljon egyedi értékeket
Most, hogy láttuk, hogyan kell használni a tömböt a COUNTIF függvénnyel, egy lépéssel tovább léphetünk, hogy segítsünk számolni, hány egyedi érték van egy tartományban. Először nézzük meg újra az osztályok listáját.
= SUM (1/COUNTIF (A2: A7, A2: A7))
Láthatjuk, hogy 6 cella értékű adat van, de csak 3 különböző elem van. Ahhoz, hogy a matematika működjön, minden elemnek 1/N értékűnek kell lennie, ahol N az elem ismétlésének száma. Például, ha minden HR csak 1/2 értékű, akkor ha összeadja őket, akkor 1 -et kap, 1 egyedi értékre.
Vissza a COUNTIF -hez, amelynek célja, hogy megtudja, hányszor jelenik meg egy elem egy tartományban. A D2 -be írjuk a tömbképletet
= SUM (1/COUNTIF (A2: A7, A2: A7))
Ez a képlet hogyan fog működni, az A2: A7 tartomány minden cellájára vonatkozóan ellenőrizni fogja, hogy hányszor jelenik meg. A mintánkkal ez egy tömböt fog előállítani
{2, 2, 3, 3, 3, 1}
Ezután ezeket a számokat törtekké alakítjuk, ha valamilyen osztást végzünk. Most úgy néz ki a tömbünk
{1/2, 1/2, 1/3, 1/3, 1/3, 1/1}
Ha mindezt összeadjuk, megkapjuk a kívánt 3 -as eredményt.
Számlálás két vagy több feltétellel - A Countifs funkció
Eddig csak a COUNTIF funkcióval dolgoztunk. A COUNTIF függvény egyszerre csak egy feltételt képes kezelni. A COUNTIF több feltétellel történő COUNTIFS függvény használatához. A COUNTIFS pontosan úgy viselkedik, mint a COUNTIFS. Csak adjon hozzá további kritériumokat. Nézzük az alábbi példát.
= COUNTIFS (B2: B7, "= 130")
COUNTIF és COUNTIFS a Google Táblázatokban
A COUNTIF & COUNTIFS funkció pontosan ugyanúgy működik a Google Táblázatokban, mint az Excelben:
