Acest tutorial arată cum să utilizați fișierulExcel COUNTIF și COUNTIFS Functiuni în Excel pentru a număra datele care îndeplinesc anumite criterii.
Prezentare generală a funcției COUNTIF
Puteți utiliza funcția COUNTIF în Excel pentru a număra celulele care conțin o anumită valoare, pentru a număra celulele care sunt mai mari sau egale cu o valoare etc.
(Observați cum apar datele introduse de formulă)
Sintaxa și argumentele funcției COUNTIF:
= COUNTIF (interval, criterii)
gamă - Gama de celule de numărat.
criterii - Criteriile care controlează ce celule ar trebui să fie numărate.
Ce este funcția COUNTIF?
Funcția COUNTIF este una dintre funcțiile mai vechi utilizate în foile de calcul. În termeni simpli, este minunat să scaneze o gamă și să vă spună câte dintre celule îndeplinesc această condiție. Vom vedea cum funcționează funcția cu text, numere și date; precum și unele dintre celelalte situații care ar putea apărea.
Exemplu de bază
Să începem examinând această listă de articole aleatorii. Avem câteva numere, celule goale și câteva șiruri de text.
Dacă doriți să știți câte elemente sunt potrivite exact cu criteriile, puteți specifica ceea ce doriți să căutați ca al doilea argument. Un exemplu al acestei formule ar putea arăta ca.
= COUNTIF (A2: A9, „Apple”)
Această formulă ar returna numărul 3, deoarece există 3 celule în gama noastră care îndeplinesc criteriile respective. Alternativ, putem folosi o referință de celulă în loc să codificăm o valoare. Dacă am scrie „Apple” în celula G2, am putea schimba formula în
= COUNTIF (A2: A9, G2)
Când aveți de-a face cu numărul, este important să faceți distincția între numere și numere care sunt stocate ca text. În general, nu puneți ghilimele în jurul numerelor atunci când scrieți formule. Deci, pentru a scrie o formulă care verifică numărul 5, ați scrie
= COUNTIF (A2: A9, 5)
În cele din urmă, am putea verifica și celulele goale utilizând un șir de lungime zero. Am scrie această formulă ca
= COUNTIF (A2: A9, "")
Notă: Această formulă va conta atât celulele care sunt cu adevărat goale, cât și cele care sunt goale ca rezultat al unei formule, cum ar fi o funcție IF.
Meciuri parțiale
Funcția COUNTIF acceptă utilizarea comodinelor, „*” sau „?”, În criterii. Să vedem această listă de produse de panificație gustoase:
Pentru a găsi toate elementele care încep cu Apple, am putea scrie „Apple *”. Deci, pentru a obține un răspuns de 3, formula noastră în D2 este
= COUNTIF (A2: A5, „Apple *”)
Notă: Funcția COUNTIF nu este diferențiată de majuscule și minuscule, deci ați putea scrie și „măr *” dacă doriți.
Revenind la produsele noastre de patiserie, am putea dori, de asemenea, să aflăm câte plăcinte avem în lista noastră. Putem găsi acest lucru prin plasarea comodinului la începutul termenului de căutare și scriem
= COUNTIF (A2: A5, „* plăcintă”)
Această formulă dă rezultatul 2.
De asemenea, putem folosi metacaractere pentru a verifica dacă există celule cu text. Să ne întoarcem la lista noastră originală de date.
Pentru a număra numărul de celule care au cel puțin un text, deci fără a număra numerele sau celula goală, putem scrie
= COUNTIF (A2: A9, "*")
Puteți vedea că formula noastră returnează corect un rezultat de 4.
Operatori de comparație în COUNTIF
Când am scris criteriile până acum, am sugerat că operatorul nostru de comparație este „=”. De fapt, am fi putut scrie acest lucru:
= COUNTIF (A2: A9, "= Apple")
Totuși, este un personaj suplimentar de scris, deci este de obicei omis. Cu toate acestea, acest lucru înseamnă că puteți utiliza ceilalți operatori, cum ar fi mai mare decât, mai mic decât sau nu egal cu. Să vedem această listă a vârstelor înregistrate:
Dacă am dori să știm câți copii au cel puțin 5 ani, putem scrie o comparație „mai mare sau egală cu” astfel:
= COUNTIF (A2: A8, "> = 5")
Notă: Operatorul de comparație este întotdeauna dat ca un șir de text și, prin urmare, trebuie să fie în interiorul ghilimelelor.
În mod similar, puteți verifica și elementele care sunt mai mici decât o anumită valoare. Dacă trebuie să aflăm câți sunt mai mici de 8, putem scrie
= COUNTIF (A2: A8, „<8”)
Acest lucru ne oferă rezultatul dorit de 5. Acum să ne imaginăm că toți copiii de 6 ani merg într-o excursie. Câți copii vor rămâne? Ne putem da seama folosind o comparație „nu egală cu” ca aceasta:
= COUNTIF (A2: A8, „6”)
Acum putem vedea rapid că avem 6 copii care nu au vârsta de 6 ani.
În aceste exemple de comparație, până acum, am codificat cu greu valorile dorite. De asemenea, puteți utiliza o referință de celulă. Trucul este că trebuie să concatenați operatorul de comparație cu referința celulei. Să presupunem că punem numărul 7 în celula C2 și dorim ca formula noastră din D2 să arate câți copii au mai puțin de 7 ani.
Formula noastră din D2 trebuie să arate astfel:
= COUNTIF (A2: A8, "<" & C2)
Notă: Acordați o atenție deosebită atunci când scrieți aceste formule dacă trebuie să puneți un articol în ghilimele sau în exterior. Operatorii sunt întotdeauna în interiorul ghilimelelor, referințele de celule sunt întotdeauna în afara ghilimelelor. Numerele sunt afară dacă faceți o potrivire exactă, dar în interior dacă faceți un operator de comparație.
Lucrul cu datele
Am văzut cum puteți da un text sau un număr ca criteriu, dar ce se întâmplă atunci când trebuie să lucrăm cu date? Iată o listă rapidă cu care putem lucra:
Pentru a număra câte date sunt după 4 mai, trebuie să fim atenți. Computerele stochează datele ca numere, așa că trebuie să ne asigurăm că computerul folosește numărul potrivit. Dacă am scrie această formulă, am obține rezultatul corect?
= COUNTIF (A2: A9, "
Răspunsul este „posibil”. Deoarece am omis anul din criteriile noastre, computerul va presupune că ne referim la anul curent. Dacă toate datele cu care lucrăm sunt pentru anul curent, atunci vom primi răspunsul corect. Cu toate acestea, dacă există câteva date în viitor, vom primi un răspuns greșit. De asemenea, odată ce începe anul următor, această formulă va întoarce un rezultat diferit. Ca atare, această sintaxă ar trebui probabil evitată.
Deoarece poate fi dificil să scrieți datele corect într-o formulă, este recomandată să scrieți data pe care doriți să o utilizați într-o celulă și apoi puteți utiliza acea referință de celulă în formula COUNTIF. Deci, să scriem data de 7 mai 2020 în celula C2 și apoi putem pune formula noastră în C4.
Formula din C4 este
= COUNTIF (A2: A9, "<" & C2)
Acum știm că rezultatul 7 este corect și răspunsul nu se va schimba neașteptat dacă deschidem această foaie de calcul cândva în viitor.
Înainte de a părăsi această secțiune, este obișnuit să folosiți funcția TODAY când lucrați cu date. Putem folosi acest lucru la fel cum am face o referință de celulă. De exemplu, am putea schimba formula anterioară astfel:
= COUNTIF (A2: A9, "<" & TODAY ())
Acum, formula noastră se va actualiza în timp ce progresează în timp real și vom avea un număr de articole care sunt mai mici decât astăzi.
Criterii multiple și COUNTIFS
Funcția originală COUNTIF a obținut o îmbunătățire în 2007 când COUNTIFS a ieșit. Sintaxa dintre cele două este foarte asemănătoare, aceasta din urmă vă permite să dați intervale și criterii suplimentare. Puteți utiliza cu ușurință COUNTIFS în orice situație care există COUNTIF. Este doar o idee bună să știți că ambele funcții există.
Să vedem acest tabel de date:
Pentru a afla câți oameni se află în nivelurile de plată 1 la 2, puteți scrie o sumă a funcțiilor COUNTIF ca aceasta:
= COUNTIF (B2: B7, "> = 1") - COUNTIF (B2: B7, "> 2")
Această formulă va funcționa, deoarece găsiți tot ce este peste 1, dar apoi scădeți numărul de înregistrări care depășesc punctul tău de limită. Alternativ, puteți utiliza COUNTIFS astfel:
= COUNTIFS (B2: B7, "> = 1", B2: B7, "<= 2")
Acesta din urmă este mai intuitiv de citit, deci este posibil să doriți să utilizați ruta respectivă. De asemenea, COUNTIFS este mai puternic atunci când trebuie să luați în considerare mai multe coloane. Să presupunem că vrem să știm câți oameni sunt în management și în nivelul de plată 1. Nu puteți face acest lucru doar cu un COUNTIF; ar trebui să scrieți
= COUNTIFS (A2: A7, „Management”, B2: B7, 1)
Această formulă vă va oferi rezultatul corect de 2. Înainte de a părăsi această secțiune, să luăm în considerare o logică de tip Sau. Ce ar fi dacă am vrea să aflăm câți oameni sunt în Management sau? Ar trebui să adăugați împreună COUNTIFS, dar există două modalități de a face acest lucru. Modul mai simplu este să-l scrieți astfel:
= COUNTIF (A2: A7, „HR”) + COUNTIF (A2: A7, „Management”)
De asemenea, puteți utiliza o matrice și puteți scrie această formulă matrice:
= SUM (COUNTIF (A2: A7, {"HR", "Management"}))
Notă: Formulele matrice trebuie confirmate folosind `Ctrl + Shift + Enter` nu doar` Enter`.
Cum va funcționa această formulă este că se va vedea că ați dat o matrice ca intrare. Astfel va calcula rezultatul la două funcții COUNTIF diferite și le va stoca într-o matrice. Funcția SUM va adăuga apoi toate rezultatele din matricea noastră împreună pentru a face o singură ieșire. Astfel, formula noastră va fi evaluată astfel:
= SUM (COUNTIF (A2: A7, {"HR", "Management"})) = SUM ({2, 3}) = 5
Numărați valori unice
Acum că am văzut cum să folosim o matrice cu funcția COUNTIF, putem face un pas mai departe pentru a ne ajuta să numărăm câte valori unice sunt într-un interval. Mai întâi, să ne uităm din nou la lista noastră de departamente.
= SUM (1 / COUNTIF (A2: A7, A2: A7))
Putem vedea că există date în valoare de 6 celule, dar există doar 3 elemente diferite. Pentru ca matematica să funcționeze, ar fi nevoie ca fiecare articol să fie în valoare de 1 / N, unde N este numărul de repetări ale unui articol. De exemplu, dacă fiecare HR valorează doar 1/2, atunci când le adăugați, veți obține un număr de 1, pentru 1 valoare unică.
Înapoi la COUNTIF, care este conceput pentru a afla de câte ori apare un articol într-un interval. În D2, vom scrie formula matricei
= SUM (1 / COUNTIF (A2: A7, A2: A7))
Cum va funcționa această formulă, este pentru fiecare celulă din intervalul A2: A7, va verifica pentru a vedea de câte ori apare. Cu eșantionul nostru, acest lucru va produce o serie de
{2, 2, 3, 3, 3, 1}
Apoi, transformăm toate acele numere în fracții făcând o anumită divizare. Acum matricea noastră arată
{1/2, 1/2, 1/3, 1/3, 1/3, 1/1}
Când adăugăm toate acestea, obținem rezultatul dorit de 3.
Countif cu două sau mai multe condiții - Funcția Countifs
Până acum am lucrat doar cu funcția COUNTIF. Funcția COUNTIF poate gestiona un singur criteriu la un moment dat. Pentru a COUNTIF cu mai multe criterii, trebuie să utilizați funcția COUNTIFS. COUNTIFS se comportă exact ca COUNTIF. Doar adăugați criterii suplimentare. Să aruncăm o privire la exemplul de mai jos.
= COUNTIFS (B2: B7, "= 130")
COUNTIF & COUNTIFS în Foi de calcul Google
Funcția COUNTIF și COUNTIFS funcționează exact la fel în Foi de calcul Google ca în Excel:
