După cum am menționat în multe dintre blogurile mele, că un SUMPRODUCT este o funcție foarte versatilă și poate fi utilizat în mai multe scopuri. În acest articol, vom vedea cum putem folosi această funcție pentru a număra valori cu mai multe criterii SAU.
Formula generică SUMPRODUCT de numărat cu criterii multiple sau multiple
| = SUMPRODUCT (- (((criterii1)+(criterii2)+… )>0) |
Criterii 1: Acesta este orice criteriu care returnează o serie de TRUE și FALSE.
Criterii 2: Acesta este următorul criteriu pe care doriți să îl verificați. În mod similar, puteți avea oricâte criterii doriți.
Formula generică de mai sus este modificată adesea pentru a se potrivi cu cerințele de numărare cu mai multe criterii SAU. Dar formula de bază este aceasta. Mai întâi vom vedea cum funcționează acest lucru printr-un exemplu și după aceea vom discuta alte scenarii în care va trebui să modificați puțin această formulă.
Exemplu: Numărați utilizatorii dacă codul dealerului sau anulChibrituri Folosind SUMPRODUCT
Deci, aici avem un set de date de agenți de vânzări. Datele conțin multe coloane. Ce trebuie să facem este să numărăm numărul de utilizatori care au codul „INKA” sau anul este „2016”. Asigurați-vă că, dacă cineva are ambele (codul „inca” și anul 2016), acesta ar trebui să fie numărat ca 1.
Deci, aici avem două criterii. Folosim formula SUMPRODUCT menționată mai sus:
| = SUMPRODUCT (- (((Cod = I3) + (An = K3))> 0)) |
Aici, codul și anul sunt denumite intervale.
Aceasta returnează 7.
În date, avem 5 înregistrări ale codului INKA și 4 înregistrări ale anului 2016. Dar 2 înregistrări au atât codul „INKA”, cât și 2016 ca cod, respectiv anul. Aceste înregistrări sunt numărate ca 1. Și astfel obținem 7.
Cum functioneazã?
Deci, să aruncăm o privire la modul în care formula este rezolvată pas cu pas, apoi voi discuta cum funcționează.
| =SUMPRODUCT(- (((Cod = I3) + (An = K3))> 0)) |
| 1=>SUMPRODUCT(- (({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; …} + {FALSE; FALSE; FALSE; TRUE; TRUE; …})> 0)) |
| 2=>SUMPRODUCT(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0)) |
| 3=>SUMPRODUCT(- ({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; …}) |
| 4=>SUMPRODUCT({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0}) |
| 5=>7 |
În primul pas, valoarea lui I3 ("INKA") este comparată cu fiecare celulă din intervalul de coduri. Aceasta returnează o serie de ADEVĂRAT și FALS. ADEVĂRAT pentru fiecare meci. Pentru a economisi spațiu, nu am arătat tot ADEVĂRAT-FALS. În mod similar, valoarea K3 (2016) este potrivită cu fiecare celulă din intervalul de ani.
În pasul următor, adăugăm aceste două tablouri care duc la o nouă matrice de valori numerice. După cum știți, TRUE este tratat ca 1 și FALS ca 0 în Excel. Deci, când se adaugă TRUE și TRUE, obținem 2, iar restul le puteți înțelege.
În pasul următor, verificăm care valoare este mai mare decât 0 în matrice. Aceasta convertește din nou matricea într-o matrice falsă adevărată. Pentru fiecare valoare 0 pe care o obținem, False și rest sunt convertite ca adevărate. Acum, numărul de valori ADEVĂRATE din matrice este răspunsul nostru. Dar cum le numărăm? Iată cum.
Semnele negative duble (-) sunt utilizate pentru a converti valorile booleene în 1s și 0s. Deci, fiecare valoare ADEVĂRATĂ din matrice este convertită în 1 și FALSĂ în 0.
În ultima etapă, SUMPRODUCT rezumă această matrice și obținem răspunsul nostru ca fiind 7.
Adăugarea mai mult sau criterii de numărat folosind SUMPRODUCT
Deci, dacă trebuie să adăugați mai multe sau criterii de numărat, puteți adăuga criterii folosind + semnul la funcție.
De exemplu, dacă doriți să adăugați un alt criteriu la formula de mai sus, astfel încât să adauge numărul de angajați care au vândut mai mult de 5 produse. Formula SUMPRODUCT va arăta pur și simplu astfel:
| = SUMPRODUCT (- (((Cod = I3) + (An = K3) + (Vânzări> 5))> 0)) |
Simplu! nu-i așa?
Dar să presupunem că doriți să aveți două criterii din Cod gamă. Să presupunem că doriți să numărați „INKB”. Deci, cum faci asta? O metodă este utilizarea tehnicii de mai sus, dar aceasta ar fi repetitivă. Să presupunem că vreau să adaug încă 10 criterii din același interval. În astfel de cazuri, această tehnică nu este atât de inteligentă pentru numărarea cu SUMPRODUCT.
Să presupunem că avem date aranjate astfel.
Codurile criteriilor sunt într-un singur rând I2: J2. Aranjarea datelor este importantă aici. Formula SUMPRODUCT pentru 3 setări de numărare a criteriilor SAU va fi:
| = SUMPRODUCT (- (((Cod = I2: J2) + (An = I3: J3))> 0)) |
Aceasta este formula SUMPRODUCT pentru a conta cu mai multe criterii atunci când mai multe criterii dintr-un interval sunt scrise la rând.
Acesta returnează răspunsul corect, care este 10.
Dacă introduceți orice an în J3, formula va adăuga și acel număr.
Aceasta este utilizată atunci când criteriile sunt într-un singur rând. Va funcționa atunci când criteriile sunt într-o singură coloană pentru același interval? Nu, nu va fi.
În acest exemplu avem mai multe coduri de numărat, dar aceste coduri de tip sunt scrise într-o singură coloană. Când folosim formula SUMPRODUCT de mai sus, primim o eroare # N / A. Nu vom intra în modul în care a apărut această eroare, deoarece acest lucru va face acest articol prea lung. Să vedem cum putem face acest lucru să funcționeze.
Pentru ca această formulă să funcționeze, trebuie să înfășurați criteriile de cod în funcția TRANSPOSE. Aceasta va face ca formula să funcționeze.
| = SUMPRODUCT (- ((((Cod = TRANSPOSE (H3: H4)) + (An = TRANSPOSE (I3: I4)))> 0)) |
Aceasta este formula pentru numărarea cu multiple sau condiții din același interval atunci când criteriile sunt listate într-o coloană.
Deci da prietene, sper că am fost suficient de clar și a avut sens. Sper că vă servește scopului de a fi aici. Dacă această formulă nu a rezolvat problema dvs., spuneți-mi cerințele dvs. în secțiunea de comentarii de mai jos. Voi fi mai mult decât fericit să vă ajut în vreun fel. Puteți menționa orice dubiu legat de Excel / VBA. Până atunci continuă să înveți, continuă să excelezi.
Cum se utilizează funcția SUMPRODUCT în Excel: Returnează SUMA după multiplicarea valorilor în mai multe tablouri în excel. Această funcție poate fi utilizată pentru a efectua mai multe sarcini. Aceasta este una dintre cele mai versatile funcții.
COUNTIFS cu gamă de criterii dinamice : Pentru a număra cu criterii dinamice, pur și simplu folosim funcția INDIRECT. Această funcție poate
COUNTIFI CU SAU Pentru criterii multiple : Numărați celulele care au mai multe criterii care se potrivesc utilizând funcția SAU. Pentru a pune o logică SAU în funcția COUNTIFS nu va trebui să utilizați funcția SAU.
Utilizarea funcțiilor IF cu ȘI / SAU în Microsoft Excel : Aceste funcții logice sunt utilizate pentru a efectua calcule de criterii multiple. Cu IF funcțiile OR și AND sunt utilizate pentru a include sau exclude potriviri.
Cum se utilizează funcția SAU în Microsoft Excel : Funcția este utilizată pentru a include toate valorile TRUE în mai multe criterii.
Cum se numără celulele care conțin acest lucru sau altul în Excel în Excel : Pentru celulele care conțin acest lucru sau altul, putem folosi funcția SUMPRODUCT. Iată cum faceți aceste calcule.
Articole populare:
50 de comenzi rapide Excel pentru a vă crește productivitatea | Deveniți mai rapid la sarcina dvs. Aceste 50 de comenzi rapide vă vor face să lucrați și mai rapid pe Excel.
Cum se utilizează funcția Excel VLOOKUP| Aceasta este una dintre cele mai utilizate și populare funcții ale Excel, care este utilizată pentru a căuta valoarea din diferite intervale și foi.
Cum se folosește Excel Funcția COUNTIF| Numărați valorile cu condiții folosind această funcție uimitoare. Nu trebuie să vă filtrați datele pentru a număra anumite valori. Funcția Countif este esențială pentru a vă pregăti tabloul de bord.
Cum se utilizează funcția SUMIF în Excel | Aceasta este o altă funcție esențială a tabloului de bord. Acest lucru vă ajută să rezumați valori în condiții specifice.
