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 multiple sau criterii.
Formula generică SUMPRODUCT de numărat cu criterii multiple sau multiple
| = SUMPRODUCT (- (((criterii1) + (criterii2) + …)> 0) |
Criterii1: Acesta este orice criteriu care returnează o serie de ADEVĂRAT și FALS.
Criterii2: 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ă.
Problemă:
Prețul într-o celulă trebuie împărțit cu exactitate în 5 plăți similare. Când împărțim pur și simplu prețul la 88,04 USD la 5 și rotunjim rezultatul la două zecimale, primim plăți individuale de 17,61 USD. Cu toate acestea, acest lucru ar însemna o plată totală de 88,05 USD, care nu este suma exactă cu care începem. Prin urmare, vrem să calculăm 5 numere similare care se adună la suma totală.
Soluţie:
Mai întâi Calculați prima plată împărțind prețul la 5 și rotunjind rezultatul.
Utilizați funcția ROUND în următoarea formulă:
= ROUND (B1 / 5,2)
Pasul 2:
Calculați fiecare plată rămasă.
Utilizați funcțiile ROUND, SUM și ROW în următoarea formulă:
= ROUND (($ B $ 1-SUM ($ B $ 2: B2)) / (5-ROW () + ROW ($ B $ 2)), 2)
Exemplu:
Numărați utilizatorii dacă codul dealerului se potrivește sau anul utilizând 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) ar trebui să fie numărate ca 1.
Așadar, auziți că 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. Și aceste înregistrări sunt numărate ca 1. Și așa obținem 7.
Să vedem cum funcționează?
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))
- SUMPRODUCT(- (({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; …} + {FALSE; FALSE; FALSE; TRUE; TRUE; …})> 0))
- SUMPRODUCT(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0))
- SUMPRODUCT(- ({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; …})
- SUMPRODUCT({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})
7
Semnele duble negative (-) 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.
Adăugarea mai multor 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 + funcției.
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 gama Code. Să presupunem că doriți să numărați „INKB”. Atunci 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 SAU setarea numărului de criterii 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 în rând.
Formula de mai sus 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 dintr-o 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 apare această eroare, deoarece acest lucru face ca acest articol să fie 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ă.
Iată toate notele observaționale privind utilizarea formulei.
Note:
- Formula funcționează atât pentru text cât și pentru numere.
- Valorile de căutare pot fi date ca referință de celulă sau direct folosind simbolul ghilimelei (") în formulă ca argumente.
Sper că ați înțeles Cum să împărțiți o sumă în plăți egale evitând erorile de divizare / rotunjire în Excel. Explorați aici mai multe articole despre valoarea de căutare Excel și funcțiile Excel 2019. Dacă ți-au plăcut blogurile noastre, împărtășește-le prietenilor tăi de pe Facebook. Și, de asemenea, ne puteți urmări pe Twitter și Facebook. Ne-ar plăcea să aflăm de la dvs., să ne anunțați cum putem îmbunătăți, completa sau inova munca noastră și să o îmbunătățim. Scrieți-ne pe site-ul de e-mail.
Utilizați INDEX și MATCH pentru a căuta valoarea : Funcția INDEX & MATCH pentru a căuta valoarea după cum este necesar.
Gama SUMĂ cu INDEX în Excel : Utilizați funcția INDEX pentru a găsi SUMA valorilor, după cum este necesar.
Cum se utilizează funcția INDEX în Excel : Găsiți INDEX-ul unui tablou folosind funcția INDEX explicată cu un exemplu.
Cum se utilizează funcția MATCH în Excel : Găsiți MATCH-ul în matrice folosind valoarea INDEX din funcția MATCH explicată cu un exemplu.
Cum se utilizează funcția de căutare în Excel : Găsiți valoarea de căutare în matrice utilizând funcția LOOKUP explicată cu un exemplu.
Articole populare:
Cum se utilizează funcția IF în Excel : Instrucțiunea IF din Excel verifică starea și returnează o valoare specifică dacă condiția este ADEVĂRATĂ sau returnează o altă valoare specifică dacă este FALSĂ.
Cum se utilizează funcția VLOOKUP în Excel : 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 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.
Cum se utilizează funcția COUNTIF în Excel : 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.