Funcția COUNTIFS, cum să utilizați funcția de numărare a celulelor în funcție de mai multe condiții în Excel

Funcția COUNTIFS din Excel are multe aplicații practice. Iată ce trebuie să știți despre utilizarea funcției COUNTIFS în Excel .

Microsoft Excel este un software faimos pentru foi de calcul în lume, cu multe caracteristici utile. Apariția software-ului face ca introducerea datelor și calcularea unei serii de numere să fie mai simple ca niciodată. Și atunci când utilizați Excel, cu siguranță trebuie să cunoașteți funcția COUNTIFS .

Funcția COUNTIFS din Excel este folosită pentru a număra celulele care îndeplinesc multe condiții date. Funcția COUNTIFS este una dintre cele mai utilizate funcții statistice Excel din Excel. Este o funcție avansată a funcției COUNTIF care numără doar celulele cu o anumită condiție. Când lucrează cu funcția COUNTIFS, utilizatorii pot găsi cu ușurință celulele de rezultat care îndeplinesc condițiile date în cerere. Condițiile pot fi numere, date, text sau celule care conțin date. Articolul de mai jos vă va ghida cum să utilizați funcția COUNTIFS în Excel.

• Cum se compară datele pe 2 coloane Excel
• Cum să utilizați funcția DSUM pentru a calcula sume cu condiții complexe în Excel
• Cum să utilizați funcția SUMIF pentru a calcula sume condiționate în Excel
• Utilizarea funcției VLOOKUP și exemple specifice

Instrucțiuni pentru utilizarea funcției COUNTIFS în Excel

Sintaxa funcției COUNTIFS este =COUNTIFS(interval_criterii1, criterii1, [interval_criterii2, criterii2],...) .

Acolo:

• Criteria_range1 este primul interval de selecție care necesită statistici, o valoare necesară.
• Criteriul1 este o condiție aplicată selecției criteria_range1, valoarea necesară poate fi o celulă, o expresie sau un text.
• [criteria_range2, criteria2] sunt perechi suplimentare de selecție și condiție, permițând până la 127 de perechi de selecție și condiție.

Rețineți când utilizați funcția COUNTIFS:

• Selecțiile suplimentare trebuie să aibă același număr de rânduri și coloane ca și zona criteria_range1 și pot fi separate unele de altele.
• Dacă condiția de selecție se referă la o celulă goală, funcția COUNTIFS consideră automat valoarea 0.
• Caracterele ? pot fi folosite. Pentru a înlocui un anumit caracter, semnul * înlocuiește un întreg șir de caractere. Dacă trebuie să găsești un semn? sau un semn real *, tastați ~ în fața caracterului respectiv.
• Rețineți că funcția COUNTIFS nu face distincție între majuscule și minuscule.
• În general, valorile textului trebuie să fie incluse între ghilimele, dar numerele nu. Cu toate acestea, atunci când un operator logic este inclus cu un număr, numărul și operatorul trebuie să fie incluse între ghilimele, după cum se arată mai jos:

=COUNTIFS(A1:A10,100) // count equal to 100
=COUNTIFS(A1:A10,">50") // count greater than 50
=COUNTIFS(A1:A10,"jim") // count equal to "jim"

Notă : Condițiile suplimentare trebuie să respecte aceleași reguli.

• Când se utilizează o valoare dintr-o altă celulă într-o condiție, referința de celulă trebuie să fie concatenată cu un operator atunci când este utilizată. În exemplul de mai jos, COUNTIFS va număra valorile din A1:A10 care sunt mai mici decât valoarea din celula B1. Rețineți că operatorul mai puțin decât (care este text) este inclus între ghilimele, dar referința la celulă nu este necesară:

=COUNTIFS(A1:A10,"<"&b1) count="" cells="" less="" than="">

Notă : COUNTIFS este una dintre câteva funcții care împart condițiile în două părți: Interval + criterii. Acest lucru provoacă unele inconsecvențe cu alte formule și funcții.

• COUNTIFS poate număra celulele goale sau neblank. Formulele de mai jos numără celulele goale și neblank în intervalul A1:A10:

=COUNTIFS(A1:A10,"<>") // not blank
=COUNTIFS(A1:A10,"") // blank

• Cel mai simplu mod de a utiliza COUNTIFS cu date este de a face referire la o dată validă într-o altă celulă care are o referință de celulă. De exemplu, pentru a număra celulele din A1:A10 care conțin o dată mai mare decât o dată din B1, puteți utiliza o formulă ca aceasta:

=COUNTIFS(A1:A10, ">"&B1) // count dates greater than A1

Exemplu de utilizare a funcției COUNTIFS în Excel

1. Fişa tehnică nr. 1

Avem tabelul de date de mai jos pentru a face unele solicitări la tabel.

Exemplul 1: Calculați numărul de angajați bărbați cu 25 de zile lucrătoare.

În celula în care trebuie să introducem rezultatul, introduceți formula =COUNTIFS(C2:C7,"Male",D2:D7,25) și apăsați Enter.

Acolo:

• C2:C7 este o zonă obligatorie de 1 numărare cu sexul angajatului.
• Sud este starea zonei de numărare 1.
• C2:C7 este a doua zonă de numărare cu Ziua de lucru a angajatului.
• 25 este condiția regiunii de numărare 2.

Ca urmare, avem 2 angajati, Nam, cu 25 de zile lucratoare.

Exemplul 2: Calculați numărul de angajați bărbați a căror zi liberă este 0.

În caseta de introducere a formulei, introduceți =COUNTIFS(C2:C7,"Nam",E2:E7,0) și apăsați Enter.

Rezultatele arată 1 angajat de sex masculin cu 0 zile libere.

Exemplul 3: Calculați numărul de angajați bărbați care au 25 de zile de muncă și au zile libere mai mici sau egale cu 2 zile.

Introducem formula =COUNTIFS(C2:C7,"Male",D2:D7,25,E2:E7,"<> și apoi apăsăm Enter.

Ca urmare, sunt 2 angajați bărbați care îndeplinesc cerințele de a avea zile libere mai mici sau egale cu 2 zile.

2. Fișa tehnică numărul 2

Exemplul 1: Calculați totalul produselor taiwaneze cu un preț unitar mai mic de 200.000 VND.

În caseta de introducere a rezultatului, introducem formula de calcul =COUNTIFS(B2:B7,"*Taiwan",C2:C7,"<> și apoi apăsăm Enter.

Rezultatul au fost 2 produse taiwaneze care au îndeplinit cerințele.

Exemplul 2: Calculați totalul produselor taiwaneze cu prețuri unitare între 100.000 VND și 150.000 VND.

În caseta de introducere a rezultatului, introduceți formula =COUNTIFS(B2:B7,"*Taiwan",C2:C7,">100000",C2:C7,"<> apoi apăsați Enter.

Ca urmare, vor exista 2 produse taiwaneze cu prețuri cuprinse între 100.000 VND - 150.000 VND.

Cum se utilizează funcția COUNTIFS combinată cu SUM în Excel

Funcția COUNTIFS este în esență o versiune mai complexă a funcției COUNTIF. Principalul avantaj pe care îl are COUNTIFS față de COUNTIF este că acceptă mai multe condiții și intervale.

Cu toate acestea, puteți defini, de asemenea, un interval și o singură condiție pentru funcția COUNTIFS, la fel cum ați făcut cu funcția COUNTIF.

Un lucru important pe care ar trebui să îl înțelegeți despre funcția COUNTIFS înainte de a o utiliza este că face mai mult decât simpla însumare a rezultatelor celulelor care îndeplinesc criteriile pentru fiecare interval de celule.

De fapt, dacă aveți două condiții pentru două intervale, celulele din primul interval sunt filtrate de două ori: o dată prin prima condiție și apoi prin a doua condiție. Aceasta înseamnă că funcția COUTNIFS va returna numai valori care îndeplinesc ambele condiții, în intervalul lor dat.

Puteți înțelege mai bine funcționalitatea funcției COUNTIFS studiind exemplul de mai jos.

Exemplu de funcție COUNITFS în Excel

Pe lângă culoarea merelor, există și o coloană care descrie dimensiunea acestora. Scopul final din acest exemplu este de a număra numărul de mere roșii mari.

1. Selectați celula în care doriți să afișați rezultatele. (În acest exemplu, articolul va afișa numărul mare de mere roșii din celula E3).

2. Accesați bara de formule și introduceți formula de mai jos:

=COUNTIFS(A3:A11, "Red", B3:B11, "Big")

3. Cu această comandă, formula verifică celulele A3 până la A11 pentru condiția „Roșu”. Celulele care trec acest test sunt apoi testate din nou în intervalul B3 până la B11 pentru starea „Mare” .

4. Apăsați butonul Enter.

5. Acum, Excel va număra numărul de mere roșii mari.

Exemplu de rezultate ale funcției Excel COUNTIFS

Observați modul în care formula numără celulele care au atât atributele roșii , cât și cele mari. Formula preia celulele de la A3 la A11 și le testează pentru a găsi rezultatul roșu. Celulele care trec această condiție sunt apoi testate din nou față de următoarea condiție din al doilea interval, în acest caz, condiția mare.

În concluzie, intervalele și condițiile de după primul interval și condiție îngustează din ce în ce mai mult filtrul de numărare și nu sunt independente unele de altele. Deci, rezultatul final al rețetei este mere roșii și mari. Puteți număra numărul de mere roșii sau mari combinând funcția COUNTIF cu funcția SUM .

1. Selectați celula în care doriți să afișați rezultatele formulei. (În acest exemplu, articolul va folosi celula E6).

2. Introduceți formula de mai jos:

=SUM(COUNTIF(A3:A11, "Red"), COUNTIF(B3:B11, "Big"))

3. Această formulă va număra celulele care conțin mere roșii, apoi numărul de celule care conțin mere mari și, în final, va însuma aceste două numere.

4. Apăsați butonul Enter.

5. Excel va număra acum și va afișa numărul de mere mari sau roșii.

Combinați funcția COUNTIF cu funcția SUM

Mai sus sunt 2 tabele de date cu utilizare pentru funcția COUNTIFS în Excel. Utilizatorii trebuie să scrie condițiile care însoțesc zona de date despre condiție pentru ca funcția COUNTIFS să le determine corect.

Cum să remediați erorile comune atunci când utilizați funcția COUNTIFS în Excel

COUNTIFS nu funcționează când se numără valorile text

La numărarea șirurilor de text, acestea trebuie introduse între ghilimele. În caz contrar, funcția COUNTIFS nu va putea număra șirul de text și va returna valoarea 0. În următorul exemplu de imagine, șirul de text nu a fost plasat între ghilimele. Prin urmare, această formulă returnează 0.

Pentru a remedia această eroare, trebuie doar să rescrieți formula exact: =COUNTIFS(E5:E12, "Mașină")

COUNTIFS nu funcționează deoarece referința intervalului este greșită

Când utilizați mai mult de un criteriu în funcția COUNTIFS, intervalul de celule pentru celelalte criterii trebuie să aibă același număr de celule. În caz contrar, funcția COUNTIFS nu va funcționa.

Să presupunem că în acest exemplu dorim să numărăm vânzările de mașini în Austin. Formula introdusă este =COUNTIFS(E5:E12,"Mașină", ​​D5:D11,"Austin"). Privind îndeaproape formula, veți vedea că primul interval de criterii de aici este E5:E12, dar al doilea interval de criterii este D5:D11. Numărul de celule din intervalul pentru acest criteriu nu este același.

Acum apăsați Enter , formula va returna #VALOAREA! .

Rescrieți formula corect după cum urmează: =COUNTIFS(E5:E12,"Mașină", ​​D5:D12,"Austin")

COUNTIFS nu funcționează din cauza unei erori în formulă

Dacă formula nu este introdusă corect, funcția COUNTIFS nu va funcționa. Când utilizați orice operator matematic, cum ar fi mai mare decât (>), mai mic decât (<), egal cu="" (=")" și „nu="" equals=""> <> ), atât operatorul, cât și criteriul numeric trebuie introdus în aceeași ecuație. De exemplu, dacă doriți să găsiți vânzări mai mari de 100.000 USD, ați introduce următoarea formulă:

=COUNTIFS(F5:F12,">" 100000)

Aici, doar operatorul este introdus în ecuație, nu există un criteriu numeric.

Dacă apăsați Enter, se va afișa caseta de dialog cu mesajul Microsoft Excel: „Există o problemă cu această formulă”.

A rezolva problema. Introduceți formula exactă:

=COUNTIFS(F5:F12,">100000")

Acum am introdus atât operatorul, cât și criteriile în paranteze. Acum această formulă va returna cantitatea necesară.

Apasa Enter.

Ca urmare, veți primi o sumă de vânzări mai mare de 100.000 USD.

Vă doresc succes!