Ce trebuie să știți
- Sintaxă: =SUMPRODUS(Matrice1, Matrice2, Matrice3, …Matrice255).
- Într-un calcul mediu ponderat, unele numere din interval valorează mai mult decât altele.
- Formula de ponderare SUMPRODUCT este o formulă non-standard care trebuie introdusă într-o celulă pentru a funcționa.
Acest articol explică cum să utilizați funcția SUMPRODUS pentru a calcula o medie ponderată în formulele Microsoft Excel.
Introduceți formula de ponderare
Ca majoritatea altor funcții din Excel, SUMPRODUCT poate fi introdus într-o foaie de lucru folosind Biblioteca de funcții din fila Formule. Deoarece formula de ponderare din acest exemplu folosește SUMPRODUCT într-un mod nestandard (rezultatul funcției este împărțit la factorul de pondere), formula de ponderare trebuie introdusă într-o celulă a foii de lucru.
Exemplul prezentat în acest articol calculează media ponderată pentru nota finală a unui student folosind funcția SUMPRODUS.
Funcția realizează acest lucru prin:
- Înmulțirea diferitelor mărci cu factorul lor individual de greutate.
- Adunarea produselor acestor operații de înmulțire împreună.
-
Împărțirea sumei de mai sus la totalul factorului de ponderare 7 (1+1+2+3) pentru cele patru evaluări.
Pentru a introduce formula SUMPRODUCT pentru a calcula o medie ponderată, deschideți o foaie de lucru goală, introduceți datele în rândurile 1 până la 6 din imaginea de mai sus și urmați acești pași:
- Selectați celula C7 pentru a deveni celula activă (aceasta este locația în care se va afișa nota finală a studentului).
- Introduceți formula =SUMPRODUS(B3:B6, C3:C6)/(1+1+2+3) în celulă. Formula apare în Bara de formule.
- Apăsați tasta Enter de pe tastatură.
- Răspunsul 78.6 apare în celula C7 (răspunsul dvs. poate avea mai multe zecimale).
Media neponderată pentru aceleași patru note ar fi 76,5. Deoarece studentul a avut rezultate mai bune la examenele intermediare și finale, cântărirea mediei a contribuit la îmbunătățirea notei generale.
Cum funcționează funcția SUMPRODUCT
De obicei, atunci când calculați o medie sau o medie aritmetică, fiecare număr are o valoare sau o pondere egală. Media se calculează adunând un interval de numere împreună și apoi împărțind acest total la numărul de valori din interval. O medie ponderată, pe de altă parte, consideră că unul sau mai multe numere din interval valorează mai mult sau au o pondere mai mare decât celel alte numere.
SUMPRODUCT înmulțește elementele a două sau mai multe matrice și apoi adaugă produsele pentru a calcula mediile ponderate. De exemplu, anumite note din școală, cum ar fi examenele intermediare și finale, valorează de obicei mai mult decât testele sau temele obișnuite. Dacă se folosește media pentru a calcula nota finală a unui student, examenele intermediare și finale vor primi ponderi mai mari.
În situația în care două tablouri cu patru elemente fiecare sunt introduse ca argumente pentru funcția SUMPRODUS:
- Primul element al matricei1 este înmulțit cu primul element din matricea2.
- Al doilea element al matricei1 este înmulțit cu al doilea element al matricei2.
- Al treilea element al matricei1 este înmulțit cu al treilea element al matricei2.
- Al patrulea element al matricei1 este înmulțit cu al patrulea element al matricei2.
În continuare, produsele celor patru operații de înmulțire sunt însumate și returnate de funcție ca rezultat.
SUMPRODUCT Sintaxă și argumente
Sintaxa unei funcții se referă la aspectul funcției și include numele, parantezele și argumentele funcției. Sintaxa pentru funcția SUMPRODUCT este:
=SUMĂPRODUS(Matrice1, Matrice2, Matrice3, … Array255)
Argumentele pentru funcția SUMPRODUCT sunt:
- Array1: Primul argument al matricei (obligatoriu).
- Array2, Array3, … Array255: matrice suplimentare (opționale), până la 255. Cu două sau mai multe matrice, funcția înmulțește elementele fiecărei matrice împreună și apoi adaugă rezultatele.
Elementele matricei pot fi referințe de celule la locația datelor din foaia de lucru sau numere separate prin operatori aritmetici, cum ar fi semnele plus (+) sau minus (-). Dacă introduceți numere care nu sunt separate de operatori, Excel le tratează ca date text.
Argumentele matricei trebuie să aibă același număr de elemente în fiecare matrice. Dacă nu, SUMPRODUCT returnează VALOARE! valoare de eroare. Dacă elementele matricei nu sunt numere, cum ar fi datele text, SUMPRODUCT le tratează ca zerouri.
SUMPRODUCT Variațiile formulei
Pentru a sublinia faptul că rezultatele funcției SUMPRODUCT sunt împărțite la suma ponderilor pentru fiecare grup de evaluare, divizorul (partea care face împărțirea) este introdus ca:
(1+1+2+3)
Formula de ponderare globală ar putea fi simplificată prin introducerea numărului 7 (suma ponderilor) ca divizor. Formula ar fi atunci:
=SUMAPRODUS(B3:B6, C3:C6)/7
Această alegere este bună dacă numărul de elemente din matricea de ponderare este mic și pot fi adăugate cu ușurință împreună, dar devine mai puțin eficientă pe măsură ce numărul de elemente din matricea de ponderare crește, făcând adăugarea lor mai dificilă.
O altă opțiune, și probabil cea mai bună alegere, având în vedere că folosește referințe de celule mai degrabă decât numere în totalizarea divizorului, ar fi folosirea funcției SUM pentru a totaliza divizorul. Formula este atunci:
=SUMAPRODUS(B3:B6, C3:C6)/SUMA(B3:B6)
De obicei, cel mai bine este să introduceți în formule referințe de celule, mai degrabă decât numere reale. Acest lucru simplifică actualizarea lor dacă datele formulei se modifică.
De exemplu, dacă factorii de ponderare pentru sarcini sunt modificați la 0,5 în exemplu și Teste sunt modificați la 1,5, primele două forme ale formulei trebuie editate manual pentru a corecta divizorul.
În a treia variantă, doar datele din celulele B3 și B4 trebuie actualizate, iar formula recalculează rezultatul.
