Formule générique
=SUMPRODUCT(weights,values)/SUM(weights)
Sommaire
Pour calculer une moyenne pondérée, vous pouvez utiliser la fonction SUMPRODUCT avec la fonction SUM. Dans l'exemple illustré, la formule dans G5, copiée vers le bas, est:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
où poids est la 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:
=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 la plage de poids nommée (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, les poids de plage nommés I5: K5 ne changent pas, car ils se comportent comme une référence absolue. Cependant, les scores en C5: E5, saisis comme référence relative, sont mis à 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 la 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:
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 sous forme de 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 une plage verticale:
Pour calculer une moyenne pondérée avec la même formule, nous devons "retourner" les poids dans un tableau horizontal avec la fonction TRANSPOSE comme ceci:
=SUMPRODUCT(TRANSPOSE(weights),C5:E5)/SUM(weights)
Après l'exécution de TRANSPOSE, le tableau vertical:
=TRANSPOSE((0.25;0.25;0.5)) // vertical array
devient:
=(0.25,0.25,0.5) // horizontal array
Et à partir de ce point, la formule se comporte comme avant.
En savoir plus: tableaux verticaux et horizontaux.
