Exceller

Moyenne pondérée

Weighted Average

Formule Excel: moyenne pondéréeFormule générique
= SUMPRODUCT (weights,values)/ SUM (weights)
Résumé

Pour calculer une moyenne pondérée, vous pouvez utiliser le SOMMEPROD fonctionner avec le Fonction SUM . Dans l'exemple illustré, la formule dans G5, copiée vers le bas, est:

 
= SUMPRODUCT (weights,C5:E5)/ SUM (weights)

poids est le plage nommée I5: K5.



Explication

Une moyenne pondérée, également appelée moyenne pondérée, est une moyenne où certaines valeurs comptent plus que d'autres. En d'autres termes, certaines valeurs ont plus de «poids». Nous pouvons calculer une moyenne pondérée en multipliant les valeurs à moyenne par les poids correspondants, puis en divisant la somme des résultats par la somme des poids. Dans Excel, cela peut être représenté avec la formule générique ci-dessous, où les poids et les valeurs sont des plages de cellules:



coût de la formule de vente dans Excel
 
= SUMPRODUCT (weights,values)/ SUM (weights)

Dans la feuille de calcul illustrée, les scores de 3 tests apparaissent dans les colonnes C à E, et les poids sont dans le plage nommée poids (I5: K5). La formule de la cellule G5 est:

 
= SUMPRODUCT (weights,C5:E5)/ SUM (weights)

En travaillant de l'intérieur vers l'extérieur, nous utilisons d'abord la fonction SOMMEPROD pour multiplier les poids par les scores correspondants et additionner le résultat:



 
= SUMPRODUCT (weights,C5:E5) // returns 88.25

SUMPRODUCT multiplie d'abord les éléments correspondants des deux tableaux ensemble, puis renvoie la somme du produit:

 
= SUMPRODUCT ({0.25,0.25,0.5},{90,83,90}) = SUMPRODUCT ({22.5,20.75,45}) =88.25

Le résultat est ensuite divisé par la somme des poids:

 
=88.25/ SUM (weights) =88.25/ SUM ({0.25,0.25,0.5}) =88.25/1 =88.25

Lorsque la formule est copiée dans la colonne G, la plage nommée poids I5: K5 ne change pas, car il se comporte comme un référence absolue . Cependant, les scores en C5: E5, inscrits comme référence relative , mettez à jour à chaque nouvelle ligne. Le résultat est une moyenne pondérée pour chaque nom de la liste, comme indiqué. La moyenne de la colonne F est calculée à titre indicatif uniquement avec le Fonction MOYENNE :



 
= AVERAGE (C5:E5)

Poids qui ne totalisent pas 1

Dans cet exemple, les pondérations sont configurées pour s'additionner jusqu'à 1, de sorte que le diviseur est toujours 1 et le résultat est la valeur renvoyée par SUMPRODUCT. Cependant, une caractéristique intéressante de la formule est que les pondérations n'ont pas besoin de s'additionner jusqu'à 1.

Par exemple, nous pourrions utiliser un poids de 1 pour les deux premiers tests et un poids de 2 pour la finale (puisque la finale est deux fois plus importante) et la moyenne pondérée sera la même:

Moyenne pondérée Excel avec pondérations personnalisées

comment utiliser la fonction moyenne dans Excel 2010

Dans la cellule G5, la formule est résolue comme ceci:

 
= 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

Remarque: les valeurs entre accolades {} ci-dessus sont des plages exprimées en tableaux .

Transposer des poids

La fonction SOMMEPROD nécessite que les dimensions du tableau soient compatibles. Si les dimensions ne sont pas compatibles, SUMPRODUCT renverra une erreur #VALUE. Dans l'exemple ci-dessous, les poids sont les mêmes que dans l'exemple d'origine, mais ils sont répertoriés dans un verticale gamme:

Moyenne pondérée Excel avec TRANSPOSE

Pour calculer une moyenne pondérée avec la même formule, nous devons `` retourner '' les poids dans un tableau horizontal avec le Fonction TRANSPOSE comme ça:

 
= SUMPRODUCT ( TRANSPOSE (weights),C5:E5)/ SUM (weights)

Après l'exécution de TRANSPOSE, le tableau vertical:

 
= TRANSPOSE ({0.250.250.5}) // vertical array

devient:

 
={0.25,0.25,0.5} // horizontal array

Et à partir de ce point, la formule se comporte comme avant.

Lire la suite: tableaux verticaux et horizontaux .

Pièces jointes Déposer exceljet moyenne pondérée xlsx Auteur Dave Bruns


^