Contoare cu logică SA pentru mai multe criterii în Excel
În mod normal, puteți utiliza funcția COUNTIFS pentru a număra celulele pe baza uneia sau mai multor condiții cu logică ȘI în Excel. Ați suferit vreodată o situație în care trebuie să numărați mai multe valori dintr-o singură coloană sau dintr-o gamă de celule? Aceasta înseamnă să numărați cu condiții multiple și logică SAU. În acest caz, puteți aplica funcțiile SUM și COUNTIFS împreună sau puteți utiliza funcția SUMPRODUCT.
- Numărați celulele cu condiții SAU în Excel
- Numărați celule cu mai multe seturi de condiții SAU în Excel
Numărați celulele cu condiții SAU în Excel
De exemplu, am o gamă de date, așa cum se arată în imaginea de mai jos, acum, doresc să număr numărul produsului care este „Creion” sau „Riglă”, aici, voi discuta două formule pentru rezolvarea acestei sarcini în Excel.
Numărați celulele cu condiții SAU folosind funcțiile SUM și COUNTIFS
În Excel, pentru a conta cu mai multe condiții SAU, puteți utiliza funcțiile SUM și COUNTIFS cu o constantă de matrice, sintaxa generică este:
- range: Intervalul de date conține criteriile de la care numărați celulele;
- criterion1, criterion2, criterion3…: Condițiile pe care doriți să le numărați pe celule.
Pentru a număra numărul de produse care sunt „Creion” sau „Riglă”, vă rugăm să copiați sau să introduceți formula de mai jos într-o celulă goală, apoi apăsați Intrați cheie pentru a obține rezultatul:
Explicația formulei:
= SUM (COUNTIFS (B2: B13, {"Creion", "Ruler"}))
- {"Creion", "Conducător"}: Mai întâi, ar trebui să împachetați toate condițiile într-o constantă de matrice astfel: {"Creion", "Ruler"}, separați elementele prin virgule.
- COUNTIFS (B2: B13, {"Creion", "Ruler"}): Această funcție COUNTIFS va obține numărări individuale pentru „Creion” și „Reglă” și veți obține rezultatul astfel: {2,3}.
- SUM (COUNTIFS (B2: B13, {"Pencil", "Ruler"})) = SUM ({2,3}): În cele din urmă, această funcție SUM rezumă toate elementele din matrice și returnează rezultatul: 5.
sfaturi: Puteți utiliza, de asemenea, referințe de celule pentru criterii, vă rugăm să aplicați formula matricei de mai jos, apoi apăsați Ctrl + Shift + Enter tastele împreună pentru a obține rezultatul corect:
Numărați celulele cu condiții SAU folosind funcția SUMPRODUCT
Iată o altă formulă creată de funcția SUMPRODUCT care poate ajuta la numărarea celulelor cu logică SAU. Sintaxa generică este:
- range: Intervalul de date conține criteriile de la care numărați celulele;
- criterion1, criterion2, criterion3…: Condițiile pe care doriți să le numărați pe celule.
Vă rugăm să copiați sau să introduceți următoarea formulă într-o celulă goală și apăsați Intrați tasta pentru a returna rezultatul:
Explicația formulei:
= SUMPRODUCT (1 * (B2: B13 = {"Creion", "Ruler"}))
- B2: B13 = {"Creion", "Conducător"}: Această expresie compară fiecare criteriu „Creion”, „Riglă” cu celula interval B2: B13. Dacă criteriul este îndeplinit, acesta returnează un ADEVĂRAT, în caz contrar, va apărea un FALS, veți obține rezultatul astfel: {ADEVĂR, FALS; FALS, FALS; FALS, FALS; FALS, ADEVĂR; FALS, FALS; ADEVĂR, FALS ; FALS, FALS; FALS, ADEVAR; FALS, FALS; FALS, FALS; FALS, ADEVAR; FALS, FALS}.
- 1 * (B2: B13 = {"Creion", "Conducător"}): Înmulțirea convertește valorile logice - ADEVĂRAT și FALS la 1 și 0, deci va obține rezultatul astfel: {1,0; 0,0; 0,0; 0,1; 0,0; 1,0; 0,0 , 0,1; 0,0; 0,0; 0,1; 0,0; XNUMX}.
- SUMPRODUCT(1*(B2:B13={"Pencil","Ruler"}))= SUMPRODUCT({1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0}): În cele din urmă, funcția SUMPRODUCT adaugă toate numerele din matrice pentru a obține rezultatul: 5.
Numărați celule cu mai multe seturi de condiții SAU în Excel
Uneori, poate fi necesar să numărați celulele cu două sau mai multe seturi de condiții SAU, în acest caz, puteți utiliza SUM și COUNTIFS cu o constantă de matrice sau SUMPRODUCT cu funcții ISNUMBER MATCH.
Numărați celulele cu două seturi de condiții SAU folosind funcțiile SUM și COUNTIFS
Pentru a trata doar două seturi de criterii SAU, trebuie doar să adăugați o altă constantă de matrice la formula COUNTIFS.
De exemplu, am intervalul de date prezentat în imaginea de mai jos, acum, vreau să număr persoanele care au comandat „Creion” sau „Riglă”, iar suma este fie <100, fie> 200.
Vă rugăm să introduceți sau să copiați următoarea formulă într-o celulă, apoi apăsați Intrați cheie pentru a obține rezultatul, vezi captura de ecran:
notițe: În formulă, ar trebui să utilizați un punct și virgulă pentru a doua constantă de matrice, care creează o matrice verticală.
Numărați celulele cu mai multe seturi de condiții SAU folosind funcția SUMPRODUCT
Formula de mai sus funcționează numai pentru două seturi de criterii SAU, dacă trebuie să numărați cu mai multe criterii, o formulă complexă SUMPRODUCT împreună cu funcțiile ISNUMBER MATCH vă pot ajuta.
Luați datele de mai jos, de exemplu, pentru a număra persoanele care au comandat „Creion” sau „Conducător”, iar starea este fie „Livrată”, fie „În tranzit” și semnată de „Bob” sau „Eko”, ar trebui să aplicați o formulă complexă .
Vă rugăm să copiați sau să introduceți formula de mai jos într-o celulă goală, apoi apăsați Intrați cheie pentru a obține calculul, vezi captura de ecran:
Explicația formulei:
=SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0)))
ISNUMBER (MATCH (B2: B13, {"Creion", "Ruler"}, 0)):
- MATCH (B2: B13, {"Creion", "Ruler"}, 0): Această funcție MATCH este utilizată pentru a compara fiecare celulă din intervalul B2: B13 cu constanta matricei corespunzătoare. Dacă se găsește potrivirea, returnează o poziție relativă a valorii din matrice, altfel se afișează o valoare de eroare. Deci, veți obține lista de matrice astfel: {1; # N / A; # N / A; 2; # N / A; 1; # N / A; 2; 1; # N / A; 2; # N / A}.
- ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))= ISNUMBER({1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;#N/A}): Funcția ISNUMBER convertește numerele în TRUE și valorile erorilor în FALSE astfel: {TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE}.
Logica de mai sus poate fi aplicată și expresiei ISNUMBER a doua și a treia.
SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0))):
- Apoi, aceste trei matrice sunt multiplicate împreună în interiorul SUMPRODUCT, care convertește automat valorile ADEVĂRATE și FALSE în 1s și 0s ca parte a operației matematice astfel: SUMPRODUCT ({1; 0; 0; 1; 0; 1; 0; 1; 1; 0; 1; 0} * {1; 1; 0; 0; 1; 1; 0; 1; 1; 0; 1; 1} * {1; 0; 0; 0; 0; 0; 0; 1; 0; 0; 1; 0}) = SUMPRODUCT ({1; 0; 0; 0; 0; 0; 0; 1; 0; 0; 1; 0}).
- În cele din urmă, funcția SUMPRODUCT însumează toate numerele din matrice pentru a obține rezultatul: 3.
Funcția relativă utilizată:
- SUMĂ:
- Funcția Excel SUM returnează suma valorilor furnizate.
- COUNTIF:
- Funcția COUNTIF este o funcție statistică în Excel care este utilizată pentru a număra numărul de celule care îndeplinesc un criteriu.
- SUMA PRODUS:
- Funcția SUMPRODUCT poate fi utilizată pentru a multiplica două sau mai multe coloane sau tablouri împreună, și apoi pentru a obține suma produselor.
- NUMĂR:
- Funcția Excel ISNUMBER returnează TRUE atunci când o celulă conține un număr și FALSE dacă nu.
- MECI:
- Funcția MATCH Microsoft Excel caută o anumită valoare într-un interval de celule și returnează poziția relativă a acestei valori.
Mai multe articole:
- Numărați valorile numerice unice pe baza criteriilor
- În foaia de lucru Excel, este posibil să suferiți de o problemă care numără numărul de valori numerice unice pe baza unei condiții specifice. De exemplu, cum pot calcula valorile unice ale cantității produsului „Tricou” din raport așa cum se arată în imaginea de mai jos? În acest articol, voi arăta câteva formule pentru a realiza această sarcină în Excel.
- Numărați celulele care se potrivesc cu două sau mai multe criterii
- În Excel, pentru a număra numărul de celule pe baza a două sau mai multe criterii, funcția COUNTIFS vă poate ajuta să rezolvați această sarcină rapid și ușor.
- Numărați numărul de rânduri cu criterii multiple SAU
- Pentru a număra numărul de rânduri cu mai multe criterii în diferite coloane, cu logică SAU, funcția SUMPRODUCT vă poate ajuta. De exemplu, am un raport de produs așa cum este prezentat mai jos, acum, doresc să număr rândurile în care produsul este „tricou” sau culoarea „negru”. Cum să faceți față acestei sarcini în Excel?
Cele mai bune instrumente de productivitate Office
Kutools pentru Excel - Vă ajută să vă distingeți de mulțime
Kutools pentru Excel se mândrește cu peste 300 de caracteristici, Asigurați-vă că ceea ce aveți nevoie este la doar un clic distanță...
Fila Office - Activați lectura și editarea cu file în Microsoft Office (includeți Excel)
- O secundă pentru a comuta între zeci de documente deschise!
- Reduceți sute de clicuri de mouse pentru dvs. în fiecare zi, spuneți adio de la mâna mouse-ului.
- Vă crește productivitatea cu 50% atunci când vizualizați și editați mai multe documente.
- Aduce file eficiente la Office (inclusiv Excel), la fel ca Chrome, Edge și Firefox.
