Formula generică
=SUMPRODUCT(weights,values)/SUM(weights)
rezumat
Pentru a calcula o medie ponderată, puteți utiliza funcția SUMPRODUCT împreună cu funcția SUM. În exemplul prezentat, formula din G5, copiată, este:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
unde greutăți este gama numită I5: K5.
Explicaţie
O medie ponderată, numită și medie ponderată, este o medie în care unele valori contează mai mult decât altele. Cu alte cuvinte, unele valori au mai multă „greutate”. Putem calcula o medie ponderată înmulțind valorile cu media cu greutățile corespunzătoare, apoi împărțind suma rezultatelor la suma greutăților. În Excel, acest lucru poate fi reprezentat cu formula generică de mai jos, unde greutățile și valorile sunt intervale de celule:
=SUMPRODUCT(weights,values)/SUM(weights)
În foaia de lucru afișată, scorurile pentru 3 teste apar în coloanele C până la E, iar ponderile sunt în ponderile intervalului denumit (I5: K5). Formula din celula G5 este:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
Lucrând din interior spre exterior, folosim mai întâi funcția SUMPRODUCT pentru a înmulți greutățile cu scorurile corespunzătoare și a însuma rezultatul:
=SUMPRODUCT(weights,C5:E5) // returns 88.25
SUMPRODUCT multiplică mai întâi elementele corespunzătoare din cele două matrice împreună, apoi returnează suma produsului:
=SUMPRODUCT((0.25,0.25,0.5),(90,83,90)) =SUMPRODUCT((22.5,20.75,45)) =88.25
Rezultatul este apoi împărțit la suma greutăților:
=88.25/SUM(weights) =88.25/SUM((0.25,0.25,0.5)) =88.25/1 =88.25
Deoarece formula este copiată în coloana G, intervalul numit ponderează I5: K5 nu se modifică, deoarece se comportă ca o referință absolută. Cu toate acestea, scorurile din C5: E5, introduse ca referință relativă, se actualizează în fiecare rând nou. Rezultatul este o medie ponderată pentru fiecare nume din listă, așa cum se arată. Media din coloana F este calculată doar cu referință cu funcția MEDIE:
=AVERAGE(C5:E5)
Greutăți care nu însumează 1
În acest exemplu, greutățile sunt configurate pentru a adăuga până la 1, astfel încât divizorul este întotdeauna 1, iar rezultatul este valoarea returnată de SUMPRODUCT. Cu toate acestea, o caracteristică frumoasă a formulei este că greutățile nu trebuie să adauge până la 1.
De exemplu, am putea folosi o greutate de 1 pentru primele două teste și o greutate de 2 pentru finală (deoarece finalul este de două ori mai important), iar media ponderată va fi aceeași:
În celula G5, formula este rezolvată astfel:
=SUMPRODUCT(weights,C5:E5)/SUM(weights) =SUMPRODUCT((1,1,2),(90,83,90))/SUM(1,1,2) =SUMPRODUCT((90,83,180))/SUM(1,1,2) =353/4 =88.25
Notă: valorile din acoladele () de mai sus sunt intervale exprimate ca tablouri.
Transpunerea greutăților
Funcția SUMPRODUCT necesită compatibilitatea dimensiunilor matricei. Dacă dimensiunile nu sunt compatibile, SUMPRODUCT va returna o eroare #VALUE. În exemplul de mai jos, greutățile sunt aceleași cu exemplul original, dar sunt listate într-un interval vertical:
Pentru a calcula o medie ponderată cu aceeași formulă, trebuie să „răsturnați” greutățile într-o matrice orizontală cu funcția TRANSPOSE astfel:
=SUMPRODUCT(TRANSPOSE(weights),C5:E5)/SUM(weights)
După executarea TRANSPOSE, matricea verticală:
=TRANSPOSE((0.25;0.25;0.5)) // vertical array
devine:
=(0.25,0.25,0.5) // horizontal array
Și din acest punct, formula se comportă ca înainte.
Citiți mai multe: tablouri verticale și orizontale.
