Šī apmācība parāda, kā lietotIzcilael COUNTIF un COUNTIFS Funcjiem programmā Excel, lai saskaitītu datus, kas atbilst noteiktiem kritērijiem.
COUNTIF funkciju pārskats
Programmā Excel varat izmantot funkciju COUNTIF, lai saskaitītu šūnas, kurās ir noteikta vērtība, saskaitītu šūnas, kas ir lielākas vai vienādas ar vērtību utt.
(Ievērojiet, kā parādās formulas ievades)
Funkcijas COUNTIF sintakse un argumenti:
= COUNTIF (diapazons, kritēriji)
diapazons - Šūnu diapazons, kas jāskaita.
kritērijiem - Kritēriji, kas nosaka, kuras šūnas jāuzskaita.
Kas ir funkcija COUNTIF?
Funkcija COUNTIF ir viena no vecākajām funkcijām, ko izmanto izklājlapās. Vienkārši izsakoties, lieliski var skenēt diapazonu un pateikt, cik šūnu atbilst šim nosacījumam. Mēs apskatīsim, kā funkcija darbojas ar tekstu, cipariem un datumiem; kā arī dažas citas situācijas, kas varētu rasties.
Pamata piemērs
Sāksim, apskatot šo nejaušo vienumu sarakstu. Mums ir daži skaitļi, tukšas šūnas un dažas teksta virknes.
Ja vēlaties uzzināt, cik vienumu precīzi atbilst kritērijiem, kā otro argumentu varat norādīt, ko vēlaties meklēt. Šīs formulas piemērs varētu izskatīties šādi
= COUNTIF (A2: A9, "Apple")
Šī formula atgrieztu skaitli 3, jo mūsu diapazonā ir 3 šūnas, kas atbilst šiem kritērijiem. Alternatīvi, mēs varam izmantot šūnu atsauci, nevis vērtības kodēšanu. Ja šūnā G2 ierakstītu “Apple”, mēs varētu mainīt formulu uz
= COUNTIF (A2: A9, G2)
Strādājot ar numuru, ir svarīgi nošķirt ciparus no cipariem, kas tiek saglabāti kā teksts. Parasti, rakstot formulas, jūs neliekat pēdiņas ap cipariem. Tātad, lai uzrakstītu formulu, kas pārbauda skaitli 5, jūs rakstītu
= COUNTIF (A2: A9, 5)
Visbeidzot, mēs varētu arī pārbaudīt tukšas šūnas, izmantojot nulles garuma virkni. Mēs uzrakstītu šo formulu kā
= COUNTIF (A2: A9, "")
Piezīme: Šī formula saskaitīs gan šūnas, kas ir patiesi tukšas, gan tās, kuras ir tukšas formulas rezultātā, piemēram, IF funkcija.
Daļējas spēles
Funkcija COUNTIF atbalsta aizstājējzīmju “*” vai “?” Izmantošanu kritērijos. Apskatīsim šo garšīgo maizes izstrādājumu sarakstu:
Lai atrastu visus vienumus, kas sākas ar Apple, mēs varētu uzrakstīt “Apple*”. Tātad, lai iegūtu atbildi uz 3, mūsu formula D2 ir
= COUNTIF (A2: A5, "Apple*")
Piezīme: Funkcija COUNTIF nav reģistrjutīga, tāpēc, ja vēlaties, varat rakstīt arī “ābols*”.
Atgriežoties pie mūsu ceptiem izstrādājumiem, iespējams, vēlēsimies uzzināt, cik pīrāgu ir mūsu sarakstā. To var atrast, ievietojot aizstājējzīmi meklēšanas vienuma sākumā, un rakstīt
= COUNTIF (A2: A5, "*pīrāgs")
Šī formula dod rezultātu 2.
Mēs varam arī izmantot aizstājējzīmes, lai pārbaudītu, vai nav šūnu ar tekstu. Atgriezīsimies pie sākotnējā datu saraksta.
Lai saskaitītu to šūnu skaitu, kurās ir vismaz teksts, tādējādi neskaitot skaitļus vai tukšu šūnu, mēs varam rakstīt
= COUNTIF (A2: A9, "*")
Jūs varat redzēt, ka mūsu formula pareizi atgriež rezultātu 4.
Salīdzināšanas operatori COUNTIF
Rakstot kritērijus līdz šim, mēs esam norādījuši, ka mūsu salīdzināšanas operators ir “=”. Patiesībā mēs būtu varējuši rakstīt šādi:
= COUNTIF (A2: A9, "= Apple")
Tomēr tas ir papildu raksturs, kas jāizraksta, tāpēc tas parasti tiek izlaists. Tomēr tas nozīmē, ka varat izmantot citus operatorus, piemēram, lielākus, mazākus vai vienādus. Apskatīsim šo reģistrēto vecumu sarakstu:
Ja mēs gribētu zināt, cik bērnu ir vismaz 5 gadus veci, mēs varam uzrakstīt salīdzinājumu “lielāks vai vienāds”:
= COUNTIF (A2: A8, "> = 5")
Piezīme: Salīdzināšanas operators vienmēr tiek norādīts kā teksta virkne, un tāpēc tam jābūt pēdiņās.
Līdzīgi varat pārbaudīt arī vienumus, kuru vērtība ir mazāka par noteikto vērtību. Ja mums jānoskaidro, cik daudz ir mazāk par 8, mēs varam izrakstīties
= COUNTIF (A2: A8, "<8")
Tas dod mums vēlamo rezultātu 5. Tagad iedomāsimies, ka visi 6 gadus vecie bērni dodas izbraukumā. Cik bērnu paliks? Mēs to varam noskaidrot, izmantojot šādu “nav vienāds” salīdzinājumu:
= COUNTIF (A2: A8, "6")
Tagad mēs varam ātri redzēt, ka mums ir 6 bērni, kas nav 6 gadus veci.
Šajos salīdzināšanas piemēros līdz šim mēs esam grūti kodējuši vēlamās vērtības. Varat arī izmantot šūnu atsauci. Viltība ir tāda, ka jums jāsavieno salīdzinājuma operators ar šūnas atsauci. Pieņemsim, ka šūnā C2 ievietojam skaitli 7 un vēlamies, lai mūsu formula D2 parādītu, cik bērnu ir jaunāki par 7 gadiem.
Mūsu formulai D2 jāizskatās šādi:
= COUNTIF (A2: A8, "<" un C2)
Piezīme: Rakstot šīs formulas, pievērsiet īpašu uzmanību tam, vai vienums jāievieto pēdiņās vai ārpus tā. Operatori vienmēr atrodas pēdiņās, šūnu atsauces vienmēr ir ārpus pēdiņām. Skaitļi ir ārpusē, ja veicat precīzu atbilstību, bet iekšpusē, ja veicat salīdzināšanas operatoru.
Darbs ar datumiem
Mēs esam redzējuši, kā jūs varat norādīt tekstu vai numuru kā kritēriju, bet kā būtu, ja mums būtu jāstrādā ar datumiem? Šeit ir ātrs paraugu saraksts, ar kuru mēs varam strādāt:
Lai saskaitītu, cik datumu ir pēc 4. maija, mums jāievēro piesardzība. Datori datumus glabā kā skaitļus, tāpēc mums jāpārliecinās, vai dators izmanto pareizo numuru. Ja mēs uzrakstītu šo formulu, vai mēs iegūtu pareizo rezultātu?
= COUNTIF (A2: A9, "
Atbilde ir "iespējams". Tā kā gads nav iekļauts mūsu kritērijos, dators pieņems, ka mēs domājam pašreizējo gadu. Ja visi datumi, ar kuriem strādājam, attiecas uz kārtējo gadu, tad mēs saņemsim pareizo atbildi. Tomēr, ja daži datumi ir nākotnē, mēs saņemsim nepareizu atbildi. Turklāt, kad sāksies nākamais gads, šī formula atgriezīs citu rezultātu. No šādas sintakses, iespējams, vajadzētu izvairīties.
Tā kā formulā var būt grūti pareizi ierakstīt datumus, vislabāk ir ierakstīt datumu, kuru vēlaties izmantot šūnā, un pēc tam varat izmantot šo šūnas atsauci savā COUNTIF formulā. Tātad, ierakstīsim datumu 2020. gada 7. maijs šūnā C2, un tad mēs varam ievietot savu formulu C4.
C4 formula ir
= COUNTIF (A2: A9, "<" un C2)
Tagad mēs zinām, ka rezultāts 7 ir pareizs, un atbilde negaidīti nemainīsies, ja šo izklājlapu atvērsim kādreiz nākotnē.
Pirms atstājam šo sadaļu, strādājot ar datumiem, parasti tiek izmantota funkcija TODAY. Mēs to varam izmantot tāpat kā šūnu atsauci. Piemēram, mēs varētu mainīt iepriekšējo formulu šādi:
= COUNTIF (A2: A9, "<" un ŠODIEN ())
Tagad mūsu formula tiks atjaunināta reālā laika gaitā, un mums būs mazāk vienumu nekā šodien.
Vairāki kritēriji un COUNTIFS
Sākotnējā funkcija COUNTIF tika uzlabota 2007. gadā, kad iznāca COUNTIFS. Sintakse starp abiem ir ļoti līdzīga, un pēdējā ļauj norādīt papildu diapazonus un kritērijus. Jūs varat viegli izmantot COUNTIFS jebkurā COUNTIF situācijā. Ir tikai laba ideja zināt, ka abas funkcijas pastāv.
Apskatīsim šo datu tabulu:
Lai uzzinātu, cik cilvēku ir 1. līdz 2. algas līmenī, varat uzrakstīt šādu COUNTIF funkciju apkopojumu:
= COUNTIF (B2: B7, "> = 1")-COUNTIF (B2: B7, "> 2")
Šī formula darbosies, jo jūs atradīsit visu, kas ir virs 1, bet pēc tam atņemiet to ierakstu skaitu, kas pārsniedz jūsu robežvērtību. Varat arī izmantot COUNTIFS šādi:
= COUNTIFS (B2: B7, "> = 1", B2: B7, "<= 2")
Pēdējais ir intuitīvāk lasāms, tāpēc, iespējams, vēlēsities izmantot šo maršrutu. Turklāt COUNTIFS ir jaudīgāks, ja jāņem vērā vairākas kolonnas. Pieņemsim, ka mēs vēlamies uzzināt, cik cilvēku ir pārvaldībā un 1. līmeņa algā. To nevar izdarīt tikai ar COUNTIF; jums vajadzētu izrakstīties
= COUNTIFS (A2: A7, "Pārvaldība", B2: B7, 1)
Šī formula dotu jums pareizo rezultātu 2. Pirms mēs atstājam šo sadaļu, apsvērsim Or tipa loģiku. Ko darīt, ja mēs gribētu noskaidrot, cik cilvēku ir vadībā vai? Jums vajadzētu pievienot dažus COUNTIFS kopā, taču to var izdarīt divos veidos. Vienkāršākais veids ir rakstīt šādi:
= COUNTIF (A2: A7, "HR")+COUNTIF (A2: A7, "Pārvaldība")
Varat arī izmantot masīvu un uzrakstīt šo masīva formulu:
= SUMMA (COUNTIF (A2: A7, {"HR", "Management"})))
Piezīme: Masīva formulas ir jāapstiprina, izmantojot taustiņu kombināciju Ctrl+Shift+Enter, nevis tikai Enter.
Šī formula darbosies, ja redzēsit, ka esat ievadījis masīvu. Tādējādi tas aprēķinās rezultātu divām dažādām COUNTIF funkcijām un saglabās tās masīvā. Funkcija SUM saskaitīs visus mūsu masīva rezultātus kopā, lai izveidotu vienu izvadi. Tādējādi mūsu formula tiks novērtēta šādi:
= SUMMA (COUNTIF (A2: A7, {"HR", "Management"}))) = SUM ({2, 3}) = 5
Saskaitiet unikālas vērtības
Tagad, kad esam redzējuši, kā izmantot masīvu ar funkciju COUNTIF, varam spert šo soli tālāk, lai palīdzētu mums saskaitīt, cik unikālu vērtību ir diapazonā. Vispirms vēlreiz apskatīsim mūsu nodaļu sarakstu.
= SUM (1/COUNTIF (A2: A7, A2: A7))
Mēs redzam, ka datu apjoms ir 6 šūnas, bet ir tikai 3 dažādi vienumi. Lai matemātika darbotos, mums vajadzētu, lai katra vienuma vērtība būtu 1/N, kur N ir vienuma atkārtojumu skaits. Piemēram, ja katrs HR bija tikai 1/2 vērts, tad, saskaitot tos, jūs saņemsiet skaitli 1 par 1 unikālu vērtību.
Atpakaļ uz mūsu COUNTIF, kas ir izstrādāts, lai noskaidrotu, cik reižu vienums parādās diapazonā. D2 mēs uzrakstīsim masīva formulu
= SUM (1/COUNTIF (A2: A7, A2: A7))
Šī formula darbosies katrai šūnai diapazonā A2: A7, tā pārbaudīs, cik reizes tā parādās. Izmantojot mūsu paraugu, tiks izveidots masīvs
{2, 2, 3, 3, 3, 1}
Tad mēs pārvēršam visus šos skaitļus daļās, sadalot. Tagad mūsu masīvs izskatās
{1/2, 1/2, 1/3, 1/3, 1/3, 1/1}
Saskaitot tos visus, mēs iegūstam vēlamo rezultātu 3.
Countif ar diviem vai vairākiem nosacījumiem - funkcija Countifs
Līdz šim mēs esam strādājuši tikai ar funkciju COUNTIF. Funkcija COUNTIF vienlaikus var apstrādāt tikai vienu kritēriju. Lai veiktu COUNTIF ar vairākiem kritērijiem, jāizmanto funkcija COUNTIFS. COUNTIFS darbojas tieši tāpat kā COUNTIFS. Jūs vienkārši pievienojat papildu kritērijus. Apskatīsim zemāk redzamo piemēru.
= COUNTIFS (B2: B7, "= 130")
COUNTIF un COUNTIFS Google izklājlapās
Funkcija COUNTIF & COUNTIFS Google izklājlapās darbojas tieši tāpat kā programmā Excel:
