Programmai Excel ir daudzas funkcijas, kurās lietotājam, lai iegūtu rezultātu, ir jānorāda viens vai vairāki kritēriji. Piemēram, ja vēlaties saskaitīt šūnas, pamatojoties uz vairākiem kritērijiem, programmā Excel varat izmantot funkcijas COUNTIF vai COUNTIFS.
Šī apmācība aptver dažādus veidus, kā izmantot vienu vai vairākus kritērijus programmā Excel COUNTIF un COUNTIFS.
Lai gan šajā apmācībā es galvenokārt koncentrēšos uz funkcijām COUNTIF un COUNTIFS, visus šos piemērus var izmantot arī citās Excel funkcijās, kuru ievadīšanai tiek izmantoti vairāki kritēriji (piemēram, SUMIF, SUMIFS, AVERAGEIF un AVERAGEIFS).
Ievads programmā Excel COUNTIF un COUNTIFS funkcijas
Vispirms izpētīsim funkciju COUNTIF un COUNTIFS izmantošanu programmā Excel.
Excel COUNTIF funkcija (izmanto vienus kritērijus)
Funkcija Excel COUNTIF ir vislabāk piemērota situācijām, kad vēlaties saskaitīt šūnas, pamatojoties uz vienu kritēriju. Ja vēlaties skaitīt pēc vairākiem kritērijiem, izmantojiet funkciju COUNTIFS.
Sintakse
= COUNTIF (diapazons, kritēriji)
Ievades argumenti
- diapazons - šūnu diapazons, kuru vēlaties saskaitīt.
- kritēriji - kritērijus, kas jānovērtē, salīdzinot ar šūnu diapazonu, lai šūna tiktu skaitīta.
Funkcija Excel COUNTIFS (izmanto vairākus kritērijus)
Funkcija Excel COUNTIFS ir vislabāk piemērota situācijām, kad vēlaties saskaitīt šūnas, pamatojoties uz vairākiem kritērijiem.
Sintakse
= COUNTIFS (kritēriju diapazons1, kritērijs1, [kritēriju diapazons2, kritērijs2]…)
Ievades argumenti
- kritēriju_diapazons1 - Šūnu diapazons, kuru vēlaties novērtēt atbilstoši kritērijiem
- kritēriji1 - kritēriji, kurus vēlaties novērtēt kritēriju_diapazons1, lai noteiktu, kuras šūnas skaitīt.
- [kritēriju_diapazons2] - Šūnu diapazons, kuru vēlaties novērtēt pēc kritērijiem2.
- [kritērijs2] - kritēriji, kurus vēlaties novērtēt kritēriju_diapazons2, lai noteiktu, kuras šūnas skaitīt.
Tagad apskatīsim dažus piemērus vairāku kritēriju izmantošanai programmā Excel COUNTIF funkcijās.
NUMBER kritēriju izmantošana programmā Excel COUNTIF funkcijas
#1 Saskaitiet šūnas, ja kritērijs ir vienāds ar vērtību
Lai iegūtu to šūnu skaitu, kurās kritēriju arguments ir vienāds ar norādīto vērtību, varat vai nu tieši ievadīt kritērijus, vai arī izmantot šūnu atsauci, kas satur kritērijus.
Zemāk ir piemērs, kurā tiek skaitītas šūnas, kurās ir skaitlis 9 (tas nozīmē, ka kritēriju arguments ir vienāds ar 9). Šeit ir formula:
= COUNTIF ($ B $ 2: $ B $ 11, D3)
Iepriekš minētajā piemērā (attēlā) kritēriji ir šūnā D3. Jūs varat arī ievadīt kritērijus tieši formulā. Piemēram, varat izmantot arī:
= COUNTIF ($ B $ 2: $ B $ 11,9)
#2 Saskaitiet šūnas, ja kritēriji ir lielāki par vērtību
Lai iegūtu šūnu skaitu, kuru vērtība ir lielāka par norādīto vērtību, mēs izmantojam lielāku par operatoru (“>”). Mēs varētu to izmantot tieši formulā vai izmantot šūnu atsauci, kurai ir kritēriji.
Ikreiz, kad izmantojam operatoru programmā Excel, mums tas jāievieto pēdiņās. Piemēram, ja kritēriji ir lielāki par 10, mums kā kritēriji jāievada “> 10” (skat. Attēlu zemāk):
Šeit ir formula:
= COUNTIF ($ B $ 2: $ B $ 11, "> 10")
Šūnā var būt arī kritēriji un kā kritērijs var izmantot šūnas atsauci. Šajā gadījumā kritēriji NAV jāievieto pēdiņās:
= COUNTIF ($ B $ 2: $ B $ 11, D3)
Var būt arī gadījums, kad vēlaties, lai kritēriji būtu šūnā, bet nevēlaties, lai tas būtu kopā ar operatoru. Piemēram, jūs varat vēlēties, lai šūnai D3 būtu skaitlis 10, nevis> 10.
Tādā gadījumā jums ir jāizveido kritēriju arguments, kas ir operatora un šūnas atsauces kombinācija (skat. Attēlu zemāk):
= COUNTIF ($ B $ 2: $ B $ 11, ”>” & D3)
PIEZĪME. Apvienojot operatoru un šūnas atsauci, operators vienmēr ir pēdiņās. Operatoram un šūnas atsaucei pievienota zīme (&).
#3 Saskaitiet šūnas, ja kritēriji ir mazāki par vērtību
Lai iegūtu šūnu skaitu, kuru vērtība ir mazāka par norādīto vērtību, mēs izmantojam mazāk par operatoru (“<“). Mēs varētu to izmantot tieši formulā vai izmantot šūnu atsauci, kurai ir kritēriji.
Ikreiz, kad izmantojam operatoru programmā Excel, mums tas jāievieto pēdiņās. Piemēram, ja kritērijs ir tāds, ka skaitlim jābūt mazākam par 5, tad mums kā kritērijs jāievada “<5” (skat. Attēlu zemāk):
= COUNTIF ($ B $ 2: $ B $ 11, "<5")
Šūnā var būt arī kritēriji un kā kritērijs var izmantot šūnas atsauci. Šajā gadījumā kritēriji NAV jāievieto pēdiņās (skat. Attēlu zemāk):
= COUNTIF ($ B $ 2: $ B $ 11, D3)
Var būt arī gadījums, kad vēlaties, lai kritēriji būtu šūnā, bet nevēlaties, lai tas būtu kopā ar operatoru. Piemēram, jūs varat vēlēties, lai šūnai D3 būtu skaitlis 5, nevis <5.
Tādā gadījumā jums ir jāizveido kritēriju arguments, kas ir operatora un šūnas atsauces kombinācija:
= COUNTIF ($ B $ 2: $ B $ 11, <<& D3)
PIEZĪME. Apvienojot operatoru un šūnas atsauci, operators vienmēr ir pēdiņās. Operatoram un šūnas atsaucei pievienota zīme (&).
#4 Skaitīt šūnas ar vairākiem kritērijiem - starp divām vērtībām
Lai iegūtu vērtību skaitu starp divām vērtībām, funkcijā COUNTIF jāizmanto vairāki kritēriji.
Šeit ir divas metodes, kā to izdarīt:
1. METODE: COUNTIFS funkcijas izmantošana
Funkcija COUNTIFS var apstrādāt vairākus kritērijus kā argumentus un skaita šūnas tikai tad, ja visi kritēriji ir PATIESI. Lai saskaitītu šūnas ar vērtībām starp divām norādītajām vērtībām (piemēram, no 5 līdz 10), mēs varam izmantot šādu funkciju COUNTIFS:
= COUNTIFS ($ B $ 2: $ B $ 11, "> 5", $ B $ 2: $ B $ 11, "<10")
PIEZĪME. Iepriekš minētā formula neskaita šūnas, kurās ir 5 vai 10. Ja vēlaties iekļaut šīs šūnas, izmantojiet lielākus par vienādiem (> =) un mazākus par vienādiem ar (<=) operatoriem. Šeit ir formula:
= COUNTIFS ($ B $ 2: $ B $ 11, ”> = 5”, $ B $ 2: $ B $ 11, ”<= 10”)
Šos kritērijus var iekļaut arī šūnās un kā kritēriju izmantot šūnu atsauci. Šajā gadījumā kritēriji NAV jāievieto pēdiņās (skat. Attēlu zemāk):
Varat arī izmantot šūnu atsauču un operatoru kombināciju (kur operators ir ievadīts tieši formulā). Apvienojot operatoru un šūnas atsauci, operators vienmēr ir pēdiņās. Operatoram un šūnas atsaucei pievienota zīme (&).
2. METODE: divu COUNTIF funkciju izmantošana
Ja jums ir vairāki kritēriji, varat izmantot COUNTIFS vai izveidot COUNTIF funkciju kombināciju. Tālāk sniegtā formula arī darītu to pašu:
= COUNTIF ($ B $ 2: $ B $ 11, "> 5")-COUNTIF ($ B $ 2: $ B $ 11, "> 10")
Iepriekš minētajā formulā mēs vispirms atrodam to šūnu skaitu, kuru vērtība ir lielāka par 5, un mēs atņemam to šūnu skaitu, kuru vērtība ir lielāka par 10. Tādējādi rezultāts būtu 5 (kas ir šūnu skaits, kurām ir vērtības ir lielākas par 5 un mazākas par 10).
Ja vēlaties, lai formula ietvertu gan 5, gan 10, izmantojiet šādu formulu:
= COUNTIF ($ B $ 2: $ B $ 11, "> = 5")-COUNTIF ($ B $ 2: $ B $ 11, "> 10")
Ja vēlaties, lai formula no skaitīšanas izslēgtu gan “5”, gan “10”, izmantojiet šādu formulu:
= COUNTIF ($ B $ 2: $ B $ 11, "> = 5")-COUNTIF ($ B $ 2: $ B $ 11, "> 10")-COUNTIF ($ B $ 2: $ B $ 11,10)
Šos kritērijus varat ievadīt šūnās un izmantot šūnu atsauces, vai arī varat izmantot operatoru un šūnu atsauču kombināciju.
Teksta kritēriju izmantošana Excel funkcijās
#1 Saskaitiet šūnas, ja kritēriji ir vienādi norādītajam tekstam
Lai saskaitītu šūnas, kurās ir precīza norādītā teksta atbilstība, mēs varam vienkārši izmantot šo tekstu kā kritēriju. Piemēram, datu kopā (parādīts attēlā zemāk), ja vēlos saskaitīt visas šūnas ar nosaukumu Džo, varu izmantot šādu formulu:
= COUNTIF ($ B $ 2: $ B $ 11, "Džo")
Tā kā šī ir teksta virkne, man teksta kritēriji jāievieto pēdiņās.
Kritērijus var ievietot arī šūnā un pēc tam izmantot šo šūnas atsauci (kā parādīts zemāk):
= COUNTIF ($ B $ 2: $ B $ 11, E3)
PIEZĪME. Jūs varat iegūt nepareizus rezultātus, ja kritēriju vai kritēriju diapazonā ir atstarpes. Pirms šo formulu lietošanas noteikti notīriet datus.
#2 Saskaitiet šūnas, ja kritēriji NAV LĪDZĪGI norādītajam tekstam
Līdzīgi tam, ko redzējām iepriekš minētajā piemērā, varat arī saskaitīt šūnas, kurās nav norādīta teksta. Lai to izdarītu, mums jāizmanto operatoram nav vienāds ().
Pieņemsim, ka vēlaties saskaitīt visas šūnas, kurās nav nosaukuma JOE, šeit ir formula, kas to darīs:
= COUNTIF ($ B $ 2: $ B $ 11, "Džo")
Šūnā var būt arī kritēriji un kā kritērijs var izmantot šūnas atsauci. Šajā gadījumā kritēriji NAV jāievieto pēdiņās (skat. Attēlu zemāk):
= COUNTIF ($ B $ 2: $ B $ 11, E3)
Var būt arī gadījums, kad vēlaties, lai kritēriji būtu šūnā, bet nevēlaties, lai tas būtu kopā ar operatoru. Piemēram, jūs varat vēlēties, lai šūnai D3 būtu vārds Džo, nevis Džo.
Tādā gadījumā jums ir jāizveido kritēriju arguments, kas ir operatora un šūnas atsauces kombinācija (skat. Attēlu zemāk):
= COUNTIF ($ B $ 2: $ B $ 11, ”” un E3)
Apvienojot operatoru un šūnas atsauci, operators vienmēr ir pēdiņās. Operatoram un šūnas atsaucei pievienota zīme (&).
DATUMA kritēriju izmantošana programmā Excel COUNTIF un COUNTIFS
Excel datumu un laiku saglabā kā skaitļus. Tātad mēs to varam izmantot tāpat kā skaitļus.
#1 Saskaitiet šūnas, ja kritēriji ir līdzvērtīgi norādītajam datumam
Lai iegūtu to šūnu skaitu, kurās ir norādītais datums, kopā ar datumu mēs izmantosim vienādu ar operatoru (=).
Lai izmantotu datumu, iesaku izmantot funkciju DATE, jo tā atbrīvojas no jebkādas kļūdas iespējas datuma vērtībā. Tā, piemēram, ja vēlos izmantot datumu 2015. gada 1. septembris, es varu izmantot funkciju DATE, kā parādīts zemāk:
= DATE (2015,9,1)
Šī formula atgrieztos tajā pašā datumā, neskatoties uz reģionālajām atšķirībām. Piemēram, 2015. gada 1. septembris būtu 2015. gada 1. septembris pēc ASV datuma sintakses un 2015. gada 9. janvāris saskaņā ar Apvienotās Karalistes datuma sintaksi. Tomēr šī formula vienmēr atgrieztos 2105.
Šeit ir formula, lai saskaitītu to šūnu skaitu, kurās ir datums 02-09-2015:
= COUNTIF ($ A $ 2: $ A $ 11, DATE (2015,9,2))
#2 Saskaitiet šūnas, ja kritēriji ir PIRMS vai PĒC noteiktā datuma
Lai saskaitītu šūnas, kurās ir datums pirms vai pēc noteikta datuma, mēs varam izmantot mazākus/lielākus par operatoriem.
Piemēram, ja vēlos saskaitīt visas šūnas, kurās ir datums, kas ir pēc 2015. gada 2. septembra, varu izmantot formulu:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,2))
Līdzīgi varat arī saskaitīt šūnu skaitu pirms noteiktā datuma. Ja skaitīšanā vēlaties iekļaut datumu, izmantojiet operatoru “vienāds ar” kopā ar “lielāks/mazāks” operatoru.
Varat arī izmantot šūnas atsauci, kurā ir datums. Šajā gadījumā jums ir jāapvieno operators (pēdiņās) ar datumu, izmantojot zīmi "&".
Skatiet piemēru zemāk:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” un F3)
#3 Skaitiet šūnas ar vairākiem kritērijiem - starp diviem datumiem
Lai iegūtu vērtību skaitu starp divām vērtībām, funkcijā COUNTIF jāizmanto vairāki kritēriji.
Mēs to varam izdarīt, izmantojot divas metodes - vienu COUNTIFS funkciju vai divas COUNTIF funkcijas.
1. METODE: COUNTIFS funkcijas izmantošana
Funkcija COUNTIFS var izmantot vairākus kritērijus kā argumentus un šūnas saskaita tikai tad, ja visi kritēriji ir PATIESI. Lai saskaitītu šūnas ar vērtībām starp diviem noteiktiem datumiem (piemēram, no 2. septembra līdz 7. septembrim), mēs varam izmantot šādu funkciju COUNTIFS:
= COUNTIFS ($ A $ 2: $ A $ 11, ">" & DATE (2015,9,2), $ A $ 2: $ A $ 11, "<" & DATE (2015,9,7))
Iepriekš minētā formula neskaita šūnas, kurās ir norādītie datumi. Ja vēlaties iekļaut arī šos datumus, izmantojiet lielākus par vienādiem (> =) un mazākus par vienādiem ar (<=) operatoriem. Šeit ir formula:
= COUNTIFS ($ A $ 2: $ A $ 11, ”> =” & DATE (2015,9,2), $ A $ 2: $ A $ 11, ”<=” & DATE (2015,9,7))
Šūnā varat norādīt arī datumus un kā kritēriju izmantot šūnas atsauci. Šādā gadījumā operatoram šūnās nevar būt datums. Jums ir manuāli jāpievieno operatori formulā (ar pēdiņām) un jāpievieno šūnu atsauce, izmantojot ampersandu (&). Skatiet attēlu zemāk:
= COUNTIFS ($ A $ 2: $ A $ 11, ">" & F3, $ A $ 2: $ A $ 11, "<" & G3)
2. METODE: COUNTIF funkciju izmantošana
Ja jums ir vairāki kritēriji, varat izmantot vienu funkciju COUNTIFS vai izveidot divu COUNTIF funkciju kombināciju. Tālāk sniegtā formula arī palīdzēs:
= COUNTIF ($ A $ 2: $ A $ 11, ">" & DATE (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ">" & DATE (2015,9,7))
Iepriekš minētajā formulā mēs vispirms atrodam to šūnu skaitu, kurām datums ir pēc 2. septembra, un mēs atņemam to šūnu skaitu, kurās datumi ir pēc 7. septembra. Tādējādi mēs iegūtu rezultātu 7 (kas ir to šūnu skaits, kurām ir datumi) pēc 2. septembra un pirms 7. septembra).
Ja nevēlaties, lai formula tiktu skaitīta gan 2. septembrī, gan 7. septembrī, izmantojiet šādu formulu:
= COUNTIF ($ A $ 2: $ A $ 11, ”> =” & DATE (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,7))
Ja vēlaties izslēgt abu datumu skaitīšanu, izmantojiet šādu formulu:
= 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)))
Turklāt šūnās var norādīt kritēriju datumus un izmantot šūnu atsauces (kopā ar operatoriem pēdiņās, kas pievienotas, izmantojot ampersand).
WILDCARD CHARACTERS izmantošana kritērijos COUNTIF & COUNTIFS funkcijās
Programmā Excel ir trīs aizstājējzīmes:
- * (zvaigznīte) - Tas apzīmē jebkuru rakstzīmju skaitu. Piemēram, ex* var nozīmēt izcilību, izcilību, piemēru, ekspertu utt.
- ? (jautājuma zīme) - Tas attēlo vienu rakstzīmi. Piemēram, Tr? Mp varētu nozīmēt Trampu vai Trampu.
- ~ (tilde) - To izmanto, lai tekstā identificētu aizstājējzīmi (~, *,?).
Jūs varat izmantot funkciju COUNTIF ar aizstājējzīmēm, lai skaitītu šūnas, ja cita iebūvēta skaitīšanas funkcija neizdodas. Piemēram, pieņemsim, ka jums ir datu kopa, kā parādīts zemāk:
Tagad ņemsim dažādus piemērus:
#1 Saskaitiet šūnas, kurās ir teksts
Lai saskaitītu šūnas ar tekstu, mēs varam izmantot aizstājējzīmi * (zvaigznīte). Tā kā zvaigznīte apzīmē jebkuru rakstzīmju skaitu, tā saskaitītu visas šūnas, kurās ir teksts. Šeit ir formula:
= COUNTIFS ($ C $ 2: $ C $ 11, ”*”)
Piezīme. Iepriekšminētā formula ignorē šūnas, kurās ir skaitļi, tukšas šūnas un loģiskas vērtības, taču tiktu skaitīts, ka šūnas satur apostrofu (un līdz ar to parādās tukšas) vai šūnas, kurās ir tukša virkne (= ””), kuras, iespējams, tika atdotas kā formulas daļa.
Šeit ir detalizēta apmācība par to, kā rīkoties gadījumos, kad ir tukša virkne vai apostrofs.
Šeit ir detalizēta apmācība par gadījumiem, kad ir tukšas virknes vai apostrofi.
Zemāk ir video, kas izskaidro dažādus šūnu skaitīšanas scenārijus ar tekstu.
#2 Saskaitiet tukšas šūnas
Ja domājat izmantot funkciju COUNTA, padomājiet vēlreiz.
Izmēģiniet to, un tas var jums neizdoties. COUNTA arī saskaitīs šūnu, kurā ir tukša virkne (bieži tiek atgriezta ar formulām kā = ”” vai kad cilvēki šūnā ievada tikai apostrofu). Šūnas, kurās ir tukšas virknes, izskatās tukšas, bet nav, un tādējādi tās tiek skaitītas, izmantojot funkciju COUNTA.
COUNTA arī saskaitīs šūnu, kurā ir tukša virkne (bieži tiek atgriezta ar formulām kā = ”” vai kad cilvēki šūnā ievada tikai apostrofu). Šūnas, kurās ir tukšas virknes, izskatās tukšas, bet nav, un tādējādi tās tiek skaitītas, izmantojot funkciju COUNTA.
Tātad, ja izmantojat formulu = COUNTA (A1: A11), tā atgriež 11, bet jāatgriež 10.
Šeit ir labojums:
= COUNTIF ($ A $ 1: $ A $ 11, ”?*”)+COUNT ($ A $ 1: $ A $ 11)+SUMPRODUCT (-ISLOGICAL ($ A $ 1: $ A $ 11))
Sapratīsim šo formulu, sadalot to:
- COUNTIF ($ N $ 8: $ N $ 18, ”?*”) - šī formulas daļa atgriež 5. Tas ietver jebkuru šūnu, kurā ir teksta rakstzīme. A? apzīmē vienu rakstzīmi un * apzīmē jebkuru rakstzīmju skaitu. Tādējādi kombinācija?* Kritērijos liek Excel saskaitīt šūnas, kurās ir vismaz viena teksta rakstzīme.
- COUNT ($ A $ 1: $ A $ 11) - tiek skaitītas visas šūnas, kurās ir skaitļi. Iepriekš minētajā piemērā tas atgriež 3.
- SUMPRODUCT (-ISLOGICAL ($ A $ 1: $ A $ 11) - tas saskaita visas šūnas, kurās ir loģiskās vērtības. Iepriekš minētajā piemērā tas atgriež 2.
#3 Skaitīt šūnas, kurās ir noteikts teksts
Pieņemsim, ka mēs vēlamies saskaitīt visas šūnas, kur pārdošanas pārstāvja nosaukums sākas ar J. To var viegli sasniegt, izmantojot funkciju COUNTIF, izmantojot aizstājējzīmi. Šeit ir formula:
= COUNTIFS ($ C $ 2: $ C $ 11, “J*”)
Kritērijs J* nosaka, ka tekstam šūnā jāsākas ar J un tajā var būt neierobežots rakstzīmju skaits.
Ja vēlaties jebkurā tekstā saskaitīt šūnas, kurās ir alfabēts, abās pusēs to papildina ar zvaigznīti. Piemēram, ja vēlaties saskaitīt šūnas, kurās ir alfabēts “a”, kā kritēriju izmantojiet * a *.
Šis raksts ir neparasti garš, salīdzinot ar citiem maniem rakstiem. Cerams, ka jums patika. Ļaujiet man zināt savas domas, atstājot komentāru.
Jums var noderēt arī šādas Excel apmācības:
- Saskaitiet vārdu skaitu programmā Excel.
- Šūnu skaitīšana, pamatojoties uz fona krāsu programmā Excel.
- Kā apkopot kolonnu programmā Excel (5 patiešām vienkārši veidi)
