Excela

Funcția Excel SUMPRODUCT

Excel Sumproduct Function

Funcția Excel SUMPRODUCTrezumat

Funcția SUMPRODUCT multiplică intervalele sau matricile împreună și returnează suma produselor. Sună plictisitor, dar SUMPRODUCT este o funcție incredibil de versatilă care poate fi utilizată pentru a număra și a suma cum ar fi COUNTIFS sau SUMIFS, dar cu mai multă flexibilitate. Alte funcții pot fi utilizate cu ușurință în interiorul SUMPRODUCT pentru a extinde funcționalitatea și mai mult.





Scop Multiplicare, apoi matrice sumă Valoare returnată Rezultatul matricilor multiplicate și însumate Sintaxă = SUMPRODUCT (matrice1, [matrice2], ...) Argumente
  • matrice1 - Prima matrice sau interval de multiplicat, apoi adăugați.
  • matrice2 - [opțional] A doua matrice sau interval de multiplicat, apoi adăugați.
Versiune Excel 2003 Note de utilizare

Funcția SUMPRODUCT funcționează cu tablouri, dar nu necesită sintaxa matricei normale (Ctrl + Shift + Enter) pentru a intra. Scopul funcției SUMPRODUCT este să înmulțească, apoi să adune, matrice. Dacă este furnizată o singură matrice, SUMPRODUCT va însuma pur și simplu articolele din matrice. Pot fi furnizate până la 30 de matrice.

Când întâlniți SUMPRODUCT pentru prima dată, poate părea plictisitor, complex și chiar inutil. Dar SUMPRODUCT este o funcție uimitor de versatilă cu multe utilizări. Deoarece va gestiona matricile cu grație și fără reclamații, le puteți utiliza pentru a procesa game de celule în moduri inteligente și elegante (consultați linkurile către exemple de formule de pe această pagină).





Pentru a ilustra modul în care funcționează SUMPRODUCT, iată câteva exemple obișnuite.

SUMPRODUCT pentru sume și numere condiționate

Să presupunem că aveți câteva date de comandă în A2: B6, cu starea în coloana A, vânzări în coloana B:



LA B
1 Stat Vânzări
2 OUT 75
3 CE 100
4 TX 125
5 CE 125
6 TX 150

Folosind SUMPRODUCT, puteți numara vânzările totale pentru Texas („TX”) cu această formulă:

 
= SUMPRODUCT (--(A2:A6='TX'))

Si tu poti sumă vânzările totale pentru Texas („TX”) cu această formulă:

 
= SUMPRODUCT (--(A2:A6='TX'),B2:B6)

Notă: Nu vă lăsați confundați cu dublul negativ. Acesta este un truc obișnuit utilizat în formule Excel mai avansate pentru a constrânge valorile ADEVĂRATE și FALSE în 1 și 0. Vedeți mai multe mai jos ...

ce este o bară de formulă în excel

Pentru exemplul de sumă de mai sus, iată o reprezentare virtuală a celor două matrice procesate pentru prima dată de SUMPRODUCT:

matrice1 matrice2
FALS 75
FALS 100
ADEVĂRAT 125
FALS 125
ADEVĂRAT 150

Fiecare matrice are 5 articole. Prima matrice conține valorile TRUE / FALSE care rezultă din expresia A2: A6 = 'TX', iar a doua matrice este conținutul B2: B6. Fiecare articol din prima matrice va fi înmulțit cu articolul corespunzător din a doua matrice. Cu toate acestea, în starea actuală, rezultatul SUMPRODUCT va fi zero, deoarece valorile TRUE și FALSE vor fi tratate ca zero. Avem nevoie ca elementele din matricea 1 să fie numerice - trebuie să fie „constrânse” în unii și zerouri. Aici intervine dublul negativ.

Prin utilizarea dublului negativ - (dublu unar, pentru tipurile dvs. tehnice), putem constrânge TRUE / FALSE în valorile numerice unu și zero, așa cum se arată în reprezentarea virtuală de mai jos. Ultima coloană „Produs” reprezintă rezultatul înmulțirii celor două matrice împreună. Rezultatul însumat, 275, este valoarea pe care o returnează SUMPRODUCT.

matrice1 matrice2 Produs
0 * 75 = 0
0 * 100 = 0
1 * 125 = 125
0 * 125 = 0
1 * 150 = 150
Sumă 275

Folosind sintaxa acoladei pentru matrice, exemplul arată astfel după constrângere:

cum să ștergeți text în Excel
 
= SUMPRODUCT ({0,0,1,0,1},{75,100,125,125,150}) 

și așa după multiplicare:

 
= SUMPRODUCT ({0,0,125,0,150})

Acest exemplu extinde ideile de mai sus cu mai multe detalii.

SUMPRODUCT cu alte funcții

SUMPRODUCT poate utiliza direct alte funcții. S-ar putea să vedeți SUMPRODUCT folosit cu funcția LEN pentru a număra totalul de caractere dintr-un interval sau cu funcții precum ISBLANK, ISTEXT etc. Acestea nu sunt în mod normal funcții de matrice, dar atunci când li se acordă un interval, creează o „matrice de rezultate”. Deoarece SUMPRODUCT este construit pentru a lucra cu matrici, este capabil să efectueze calcule asupra matricelor direct. Aceasta poate fi o modalitate bună de a economisi spațiu într-o foaie de lucru, eliminând necesitatea unei coloane „ajutor”.

De exemplu, să presupunem că aveți 10 valori de text diferite în A1: A10 și doriți să numărați totalul de caractere pentru toate cele 10 valori. Puteți adăuga o coloană de ajutor în coloana B care utilizează această formulă: LEN (A1) pentru a calcula caracterele din fiecare celulă. Apoi, puteți utiliza SUM pentru a aduna toate cele 10 numere. Cu toate acestea, folosind SUMPRODUCT, puteți scrie o formulă ca aceasta:

 
= SUMPRODUCT ( LEN (A1:A10))

Când este utilizat cu un interval precum A1: A10, LEN va returna o matrice de 10 valori. Apoi SUMPRODUCT va însuma pur și simplu toate valorile și va returna rezultatul, fără a fi necesară o coloană de ajutor.

Vedeți exemplele de mai jos, multe alte modalități de utilizare a SUMPRODUCT.

Note:

  1. SUMPRODUCT tratează elementele nenumerice din tablouri ca zerouri.
  2. Argumentele matrice trebuie să aibă aceeași dimensiune. În caz contrar, SUMPRODUCT va genera un #VALUE! valoarea erorii.
  3. Testele logice din matrici vor crea valori ADEVĂRATE și FALSE. În majoritatea cazurilor, veți dori să le constrângeți la 1 și 0.
  4. SUMPRODUCT poate folosi adesea rezultatul altor funcții direct (a se vedea exemplele de formulă de mai jos)


^