Cum se utilizează funcția SUM & IF în schimb pentru funcția SUMPRODUCT sau SUMIFS în Excel

Anonim

În acest articol, vom învăța cum să utilizați funcția IF în loc de funcția SUMPRODUCT și SUMIFS în Excel.

Scenariu:

În cuvinte simple, atunci când lucrăm cu un set de date lung împrăștiat, uneori trebuie să găsim suma numerelor cu câteva criterii peste el. De exemplu, găsirea sumei salariilor într-un anumit departament sau având mai multe criterii peste dată, nume, departament sau poate chiar să numeroteze date precum salarii sub valoare sau cantitate peste valoare. Pentru aceasta utilizați de obicei funcția SUMPRODUCT sau SUMIFS. Dar nu ați crede, îndepliniți aceeași funcție cu funcția de bază a funcției IF Excel.

Cum se rezolvă problema?

Trebuie să vă gândiți cum este posibil acest lucru, pentru a efectua operații logice peste tablouri folosind funcția IF. Dacă funcția în Excel este foarte utilă, vă va ajuta să faceți câteva sarcini dificile în Excel sau în orice alte limbi de codare. Funcția IF testează condițiile matricei corespunzătoare valorilor solicitate și returnează rezultatul ca matrice corespunzătoare condițiilor True ca 1 și False ca 0.

Pentru această problemă, vom folosi următoarele funcții:

  1. Funcția SUM
  2. Funcția IF

Vom solicita aceste funcții de mai sus și un anumit simț al funcționării datelor. condițiile logice pe tablouri pot fi aplicate folosind operatori logici. Acești operatori logici lucrează atât la text cât și la numere. Mai jos este formula generică. { } acoladele sunt instrumentul magic pentru a efectua formule matrice cu funcția IF.

Formula generică:

{ = SUM (IF ((logic_1) * (logic_2) * … * (logic_n), sum_array)) }

Notă: Pentru acolade ( { } ) Utilizare Ctrl + Shift + Enter când lucrați cu tablouri sau intervale în Excel. Aceasta va genera în mod implicit aparate dentare pe formulă. NU încercați să codificați caractere dentare cu coduri dure.

Logic 1: testează condiția 1 pe matricea 1

Logică 2: testează condiția 2 pe matricea 2 și așa mai departe

sum_array: matrice, operațiunea sumă este efectuată

Exemplu:

Toate acestea ar putea fi confuze pentru a înțelege. Deci, să testăm această formulă executând-o pe exemplul prezentat mai jos. Aici avem date despre produsele livrate în diferite orașe, împreună cu câmpurile și cantitățile corespunzătoare categoriei. Aici avem datele și trebuie să găsim cantitatea de cookie-uri trimise la Boston unde cantitatea este mai mare de 40.

Tabelul de date și tabelul criteriilor sunt prezentate în imaginea de mai sus. În scopul înțelegerii, am folosit intervale denumite pentru matricele utilizate. Intervalele denumite sunt enumerate mai jos.

Aici :

Oraș definit pentru matricea A2: A17.

Categorie definită pentru matricea B2: A17.

Cantitatea definită pentru matricea C2: C17.

Acum sunteți gata să obțineți rezultatul dorit folosind formula de mai jos.

Folosiți formula:

{ = SUM (IF ((City = "Boston") * (Category = "Cookies") * (Cantitate> 40), Cantitate)) }

Explicație:

  1. Oraș = "Boston": verifică valorile din zona orașului pentru a se potrivi cu "Boston".
  2. Category = „Cookies”: verifică valorile din gama de categorii pentru a se potrivi cu „Cookies”.
  3. Cantitate> 40: verifică valorile din intervalul Cantitate până la ma
  4. Cantitatea trebuie să fie matrice în care este necesară suma.
  5. Funcția IF verifică toate criteriile și asterisc char (*) multiplică toate rezultatele matricei.

= SUM (IF ({0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0}, {33; 87; 58; 38; 54; 51; 28; 36; 28; 44; 23; 27; 43; 42; 33; 30}))

  1. Acum funcția IF returnează numai cantitățile corespunzătoare 1s și restul sunt ignorate.
  2. Funcția SUM returnează SUMA.

Acum, cantitatea corespunzătoare lui 1 se adaugă doar pentru a obține rezultatul.


După cum puteți vedea, cantitatea 43 este returnată, dar există trei comenzi de cookie-uri livrate către „Boston” cu cantitatea 38, 36 și 43. Aveam nevoie de o sumă de cantitate în care cantitatea să fie peste 40. Deci formula returnează numai 43. Acum utilizați alte criterii pentru a obține SUM Cantitatea pentru oraș: „Los Angeles” și categoria: „bare” și cantitatea să fie mai mică de 50.

Folosiți formula

{ = SUM (IF ((Oraș = "Los Angeles") * (Categorie = "Baruri") * (Cantitate <50), Cantitate)) }

După cum puteți vedea, formula returnează valorile 86 ca rezultat. Care este suma a 2 comenzi care îndeplinesc condițiile având cantitatea 44 și 42. Acest articol ilustrează cum se înlocuiește o formulă IF imbricată cu un singur IF într-o formulă matrice. Aceasta poate fi utilizată pentru a reduce complexitatea în formulele complexe. Cu toate acestea, această problemă particulară ar putea fi ușor rezolvată cu funcția SUMIFS sau SUMPRODUCT.

Utilizarea funcției SUMPRODUCT:

Funcția SUMPRODUCT returnează suma valorilor corespunzătoare din matrice. Deci, vom obține matricile la returnează 1s a valorile instrucțiunii True și 0s la valorile instrucțiunii False. Deci ultima sumă va fi corespunzătoare acolo unde toate declarațiile sunt adevărate.

Folosiți formula:

= SUMPRODUCT (- (Oraș = "Boston"), - (Categorie = "Cookies"), - (Cantitate> 40), Cantitate)

-: operație utilizată pentru a converti toate TRUE-urile la 1s și False la 0.

Funcția SUMPRODUCT reverifică SUMA cantității returnate de funcția SUM și IF explicată mai sus.

În mod similar, pentru al doilea exemplu, rezultatul este același.

După cum puteți vedea, funcția SUMPRODUCT poate efectua aceeași sarcină.

Iată toate notele observaționale privind utilizarea formulei.

Note:

  1. Suma_array din formulă funcționează numai cu numere.
  2. Dacă formula returnează eroarea #VALUE, verificați dacă aparatele dentare trebuie să fie prezente în formulă așa cum se arată în exemplele din articol.
  3. Negation (-) char schimbă valorile, TRUEs sau 1s în FALSE sau 0s și FALSEs sau 0s în TRUEs sau 1s.
  4. Operații precum este egal cu ( = ), mai mic decât egal cu ( <= ), mai mare ca ( > ) sau nu egal cu () poate fi efectuat în cadrul unei formule aplicate, numai cu numere.

Sper că acest articol despre modul de utilizare a funcției IF în loc de funcția SUMPRODUCT și SUMIFS în Excel este explicativ. Găsiți mai multe articole despre formulele de rezumare aici. Dacă ți-au plăcut blogurile noastre, împărtășește-le cu prietenii 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

Cum se utilizează funcția SUMPRODUCT în Excel: Returnează SUMA după multiplicarea valorilor în mai multe tablouri în excel.

SUMA dacă data este între : Returnează SUMA valorilor între date sau perioade date în Excel.

Suma dacă data este mai mare decât data dată: Returnează SUMA valorilor după data sau perioada dată în Excel.

2 moduri de a suma prin lună în Excel: Returnează SUMA valorilor într-o anumită lună specifică în Excel.

Cum să însumați mai multe coloane cu condiție: Returnează SUMA valorilor pe mai multe coloane cu condiție în excel.

Articole populare:

50 Comandă rapidă 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 folosește tFuncț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 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.

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.