Calculer une moyenne est une tâche courante pour les personnes qui utilisent Google Sheets.
Lorsque les quantités d’une plage de données n’ont pas la même importance, calculer une simple moyenne arithmétique n'est pas vraiment utile. Dans de tels cas, la moyenne arithmétique pondérée peut constituer une meilleure représentation des données.
Dans ce tutoriel Google Sheets sur la moyenne pondérée, je vais vous montrer deux manières simples de calculer la moyenne pondérée dans Google Sheets :
- en utilisant la fonction SOMMEPROD ;
- en utilisant la fonction AVERAGE.WEIGHTED (je sais : cette fonction n’a pas été traduite en français 😒).
Qu’est-ce que la moyenne pondérée ?
La moyenne pondérée est une moyenne arithmétique qui tient compte de l'importance des éléments dans la plage de données.
La moyenne pondérée donne une image plus précise des données (comparé à la moyenne arithmétique, plus généralement utilisée).
La moyenne pondérée est souvent utilisée pour analyser les performances des classes, dans le contrôle de gestion et d'autres opérations d'analyse et statistiques.
Quand utiliser la moyenne pondérée ?
Pour mieux comprendre, il est plus facile de prendre un exemple.
Je prends l’exemple d'un étudiant qui passe trois épreuves différents (par exemple, une interrogation écrite en classe, un examen de mi-année et un examen final).
Chaque épreuve a une pondération (ou poids) différente dans la note finale qui va être attribuée à l'étudiant. L'examen en classe est moins important, celui de mi-année a un peu plus d'importance, tandis que l'examen final est celui qui a le plus d'importance.
Dans un tel cas, totaliser les 3 notes et les diviser par 3 ne prend pas en considération l’importance (ou le poids) de chaque examen.
Par conséquent, cela ne va pas donner une représentation correcte de la performance de l’élève.
En revanche, pour calculer la moyenne pondérée (si les notes sont pondérées comme on le voit dans l’exemple que j’utilise), il faut tenir compte de la pondération des examens individuels. Voilà comment vous pouvez calculer une moyenne pondérée :
- multipliez la note de chaque épreuve par sa pondération correspondante ;
- additionnez chacun de ces nombres ;
- divisez le total par la somme des pondérations.
Ainsi l'exemple présenté ci-dessus, la moyenne pondérée est calculée de la manière suivante :
Moyenne pondérée = [(9 x 15%) + (7 x 35%) + (12 x 50%)] / (15% + 35% +50%)
= 9,8
Cette valeur donne une image beaucoup plus précise des performances de l’étudiant puisqu’elle ne prend pas seulement en compte les notes des épreuves individuelles, mais prend aussi en compte l’importance de chaque note.
Comment calculer la moyenne pondérée dans Google Sheets
Les feuilles Google disposent de quelques fonctions intéressantes qui peuvent vous aider à calculer plus rapidement la moyenne pondérée de vos données :
- la fonction SOMMEPROD ;
- la fonction AVERAGE.WEIGHTED.
Je vais vous montrer comment utiliser ces deux fonctions. Pour l'explication des deux méthodes, je vais utiliser la plage de données suivante :
Utiliser SOMMEPROD pour pondérer les notes dans Google Sheets
La première méthode utilise la fonction SOMMEPROD de Google Sheets.
La fonction SOMMEPROD vous permet de calculer la somme des produits associés à des données variables.
Syntaxe de la fonction SOMMEPROD
La syntaxe de la fonction SOMMEPROD est la suivante :
SOMMEPROD(tableau1; [tableau2; …])
Ici, tableau1, tableau2, tableau3, etc. sont des variables distinctes. Il peut s'agir d'une plage de cellules, d'une liste de valeurs ou d'un nom de colonne individuel.
Par exemple, si nous avons deux plages A1:A8 et B1:B8, la fonction : SOMMEPROD(A1:A8; B1:B8) va prendre chaque nombre de la première plage (A1:A8) et va la multiplier par le nombre correspondant dans la deuxième plage (B1:B8).
SOMMEPROD fait aussi l’addition de tous ces résultats individuels pour calculer le résultat final.
Donc si vous avez la formule ci-dessous :
=SOMMEPROD(A1:A8; B1:B8)
Voilà le calcul détaillé réalisé par Google Sheets en arrière-plan :
= (A1 * B1) + (A2 * B2) + (A3 * B3) + (A4 * B4) + (A5 * B5) + (A6 * B6) + (A7 * B7) + (A8 * B8)
La fonction SOMMEPROD est idéale pour trouver la moyenne pondérée puisqu'une grande partie du calcul consiste à calculer la somme des multiplications en deux nombre situés sur une même ligne.
Pour utiliser cette formule pour le calcul de la moyenne pondérée, vous pouvez préciser la plage qui contient les données individuelles sous forme de tableau1 et la plage contenant les pondérations sous forme de tableau2.
Vous pouvez ensuite diviser le résultat par la somme des pondérations.
Comment faire une moyenne pondérée dans Google Sheets à l'aide de SOMMEPROD pour trouver la moyenne pondérée des notes
Dans l’exemple que vous ai montré, je souhaite :
- multiplier les notes de chaque épreuve individuelle avec sa pondération correspondante ;
- additionner tous les résultats ;
- puis diviser ce nombre total par la somme des pondérations.
Voici les étapes que vous devez suivre pour appliquer la fonction SOMMEPROD à l'exemple ci-dessus :
- sélectionnez la cellule où vous souhaitez afficher la moyenne pondérée (C8 dans mon exemple) ;
- saisissez la formule suivante : =SOMMEPROD(B2:B6; C2:C6) / SOMME(C2:C6) ;
- appuyez sur la touche Entrée de votre clavier.
Voilà : vous avez calculé la moyenne pondérée dans la cellule sélectionnée.
Calculer la moyenne pondérée à l’aide de la fonction AVERAGE.WEIGHTED de Google Sheets
AVERAGE.WEIGHTED est la formule idéale pour calculer directement la moyenne pondérée dans Google Sheets (à noter que sa traduction littérale, depuis l'anglais est justement “moyenne pondérée”, mais que cette fonction n’a pas été traduite en français).
A titre d'information, il n'existe pas de fonction similaire dans Excel qui permet de directement calculer la moyenne pondérée.
Cette fonction facilite grandement le calcul de la moyenne pondérée (par comparaison avec la méthode SOMMEPROD). Encore une fois, la seule chose que je trouve dommage, c'est qu'elle n'a pas été traduite en français !
La syntaxe de la fonction AVERAGE.WEIGHTED
La syntaxe de la fonction AVERAGE.WEIGHTED est la suivante :
AVERAGE.WEIGHTED(valeurs; pondérations; [valeurs_supplémentaires; …]; [pondérations_supplémentaires;…])
Voici les arguments de AVERAGE.WEIGHTED :
- les valeurs sont les valeurs de données pour lesquelles vous souhaitez trouver la moyenne pondérée ;
- pondérations est la plage de données qui contiennent les pondérations correspondantes.
Comme vous pouvez le voir dans la syntaxe, il est possible d'utiliser plusieurs plages de valeurs de données et leurs données de pondérations correspondantes.
La fonction AVERAGE.WEIGHTED rend le calcul de la moyenne pondérée beaucoup plus facile qu’avec SOMMEPROD, puisqu'il vous suffit de préciser les plages avec les valeurs et celles avec les pondérations, sans avoir à effectuer de calculs supplémentaires par la suite.
Par exemple, si vous avez une plage avec des valeurs dans la plage A1:A5 et les pondérations correspondantes dans B1:B5, la fonction : AVERAGE.WEIGHTED(A1:A5; B1:B5) va prendre chaque valeur de la première plage (A1 : A5) et la multiplier par la valeur correspondante dans la deuxième plage (B1:B5).
Cette fonction va ensuite additionner tous ces résultats individuels et va diviser le total par la somme des pondérations pour donner le résultat final (la moyenne pondérée).
Admettons que vous avez saisi cette formule :
=AVERAGE.WEIGHTED(A1:A5; B1:B5)
Eh bien, ce que Google Sheets fait (en arrière-plan), c'est ce calcul :
= [(A1 * B1) + (A2 * B2) + (A3 * B3) + (A4 * B4) + (A5 * B5)] / (B1+ B2 + B3 + B4 + B5)
Plus vos plages de données sont grandes et plus il est utile et efficace d'utiliser la fonction AVERAGE.WEIGHTED.
Utiliser la fonction AVERAGE.WEIGHTED pour calculer la moyenne pondérée (exemple)
Si on reprend les données de l'exemple, nous pouvons utiliser directement la fonction AVERAGE.WEIGHTED sans avoir besoin de la combiner avec une autre fonction.
Voici les étapes que vous devez suivre pour appliquer la fonction AVERAGE.WEIGHTED à l'exemple partagé précédemment :
- sélectionnez la cellule où vous souhaitez afficher la moyenne pondérée (C8 dans notre exemple) ;
- saisissez la formule : =AVERAGE.WEIGHTED(B2:B6; C2:C6) ;
- appuyez sur la touche "Entrée" de votre clavier.
Vous avez maintenant la moyenne pondérée qui s'affiche dans la cellule où vous avez saisi la formule.
Notez la simplicité de la formule. Tout ce que j'ai utilisé, c'est une plage de cellules avec des valeurs (ou nombres) et une plage de cellules avec des pondérations.
A noter : il est important que toutes les cellules contiennent une valeur numérique quand vous utilisez la fonction AVERAGE.WEIGHTED. Si une cellule est laissée vide, la formule va afficher un message d'erreur. Assurez-vous donc de remplir toutes les cellules vides avec au moins un « 0 ». A l'inverse, vous ne rencontrez pas ce type de problème quand vous utilisez la fonction SOMMEPROD.
Quand faut-il éviter d’utiliser AVERAGE.WEIGHTED dans Google Sheets ?
- Vous ne pouvez pas utiliser cette fonction lorsque des données sont manquantes dans une des plages de cellules.
- Les valeurs de chaque colonne doivent avoir le même format numérique (chiffres, dates, etc.).
Questions fréquentes sur la moyenne pondérée dans Google Sheets
Comment calculer la moyenne pondérée dans Google Sheets ?
Le meilleur moyen de calculer la moyenne pondérée dans Google Sheets est d'utiliser la fonction AVERAGE.WEIGHTED.
Voici la syntaxe de cette fonction :
=AVERAGE.WEIGHTED(valeurs; pondérations; [valeurs_supplémentaires; …]; [pondérations_supplémentaires;…])
Avec cette formule, vous utilisez des plages de cellules pour les valeurs et les pondérations. La colonne (ou taille de la plage) des pondérations doit être identique à celle des valeurs.
A noter que les pondérations sont généralement exprimées en pourcentage (mais ce n'est pas une obligation).
Est-ce que AVERAGE.WEIGHTED de Google Sheets est identique à SOMMEPROD ?
Les deux fonctions peuvent calculer une moyenne pondérée, mais elles fonctionnent avec une syntaxe différente. AVERAGE.WEIGHTED n'existe que dans Google Sheets et ne peut pas être utilisée dans Excel.
Quelle est la différence entre la moyenne et la moyenne pondérée ?
La moyenne pondérée prend en compte l’importance relative de chaque donnée (parmi une liste de données). A l'inverse, la moyenne calcule simplement la moyenne globale de la plage de données.
Quand doit-on utiliser une moyenne pondérée ?
Vous devez utiliser des moyennes pondérées lorsque vous attribuez plus d'importance à certaines données. Cela peut par exemple être le cas, quand un enseignant considère qu'un devoir a plus d'importance que les autres et qu'il veut calculer la moyenne pour un trimestre ou une année.
Conclusion sur des moyennes pondérées dans Google Sheets
Dans ce guide sur la moyenne pondérée dans Google Sheets, je vous ai montré deux façons de calculer la moyenne pondérée.
Vous pouvez le faire avec la fonction générique SOMMEPROD (qui est plus connue) ou en utilisant une fonction qui n'existe que dans Google Sheets et qui s'appelle WEIGHTED.AVERAGE.
La fonction WEIGHTED.AVERAGE a été créée par Google Sheets pour le seul calcul de la moyenne pondérée.
J'espère que ce tutoriel vous a été utile. Je vous recommande de parcourir les autres articles de TopSheets.fr si vous voulez progresser avec Google Sheets.
