Table des matières
Excel a fait du calcul de la moyenne de plusieurs cellules une tâche très facile - utilisez simplement la fonction MOYENNE (MOYEN). Mais que se passe-t-il si certaines valeurs ont plus de poids que d'autres ? Par exemple, dans de nombreux cours, les tests ont plus de poids que les devoirs. Dans de tels cas, il faut calculer moyenne pondérée.
Excel n'a pas de fonction pour calculer la moyenne pondérée, mais il existe une fonction qui fera le plus gros du travail à votre place : SUMPRODUCT (SOMME PRODUIT). Et même si vous n'avez jamais utilisé cette fonctionnalité auparavant, à la fin de cet article, vous l'utiliserez comme un pro. La méthode que nous utilisons fonctionne dans n'importe quelle version d'Excel ainsi que dans d'autres feuilles de calcul telles que Google Sheets.
Nous préparons la table
Si vous allez calculer une moyenne pondérée, vous aurez besoin d'au moins deux colonnes. La première colonne (colonne B dans notre exemple) contient les scores de chaque devoir ou test. La deuxième colonne (colonne C) contient les poids. Plus de poids signifie plus d'influence de la tâche ou du test sur la note finale.
Pour comprendre ce qu'est le poids, vous pouvez le considérer comme un pourcentage de votre note finale. En fait, ce n'est pas le cas, puisque dans ce cas, les poids devraient totaliser 100 %. La formule que nous analyserons dans cette leçon calculera tout correctement et ne dépendra pas de la somme des poids.
Nous entrons dans la formule
Maintenant que notre tableau est prêt, nous ajoutons la formule à la cellule B10 (n'importe quelle cellule vide fera l'affaire). Comme pour toute autre formule dans Excel, nous commençons par un signe égal (=).
La première partie de notre formule est la fonction SUMPRODUCT (SOMME PRODUIT). Les arguments doivent être mis entre parenthèses, nous les ouvrons donc :
=СУММПРОИЗВ(
=SUMPRODUCT(
Ensuite, ajoutez les arguments de la fonction. SUMPRODUCT (SUMPRODUCT) peut avoir plusieurs arguments, mais généralement deux sont utilisés. Dans notre exemple, le premier argument sera une plage de cellules. B2 : B9A qui contient les scores.
=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9
Le deuxième argument sera une plage de cellules C2 : C9, qui contient les poids. Ces arguments doivent être séparés par un point-virgule (virgule). Lorsque tout est prêt, fermez les parenthèses :
=СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)
Ajoutons maintenant la deuxième partie de notre formule, qui va diviser le résultat calculé par la fonction SUMPRODUCT (SOMMEPROD) par la somme des poids. Nous verrons plus tard pourquoi c'est important.
Pour effectuer l'opération de division, nous continuons la formule déjà entrée avec le symbole / (barre droite), puis écrivez la fonction SUM (SOMME):
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
Pour la fonction SUM (SOMME) nous ne spécifierons qu'un seul argument - une plage de cellules C2 : C9. N'oubliez pas de fermer les parenthèses après avoir entré l'argument :
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
Prêt! Après avoir appuyé sur la touche Entrer, Excel calculera la moyenne pondérée. Dans notre exemple, le résultat final sera 83,6.
Comment ça marche
Décomposons chaque partie de la formule, en commençant par la fonction SUMPRODUCT (SOMMEPROD) pour comprendre comment cela fonctionne. Fonction SUMPRODUCT (SUMPRODUCT) calcule le produit du score de chaque élément et de son poids, puis additionne tous les produits résultants. En d'autres termes, la fonction trouve la somme des produits, d'où le nom. Donc pour Missions 1 multiplier 85 par 5, et pour Le test multiplier 83 par 25.
Si vous vous demandez pourquoi nous devons multiplier les valeurs dans la première partie, imaginez que plus le poids de la tâche est important, plus nous devons en tenir compte. Par exemple, Tâche 2 compté 5 fois et Examen final – 45 fois. C'est pourquoi Examen final a un plus grand impact sur la note finale.
À titre de comparaison, lors du calcul de la moyenne arithmétique habituelle, chaque valeur n'est prise en compte qu'une seule fois, c'est-à-dire que toutes les valeurs ont le même poids.
Si vous pouviez regarder sous le capot d'une fonction SUMPRODUCT (SOMMEPROD), nous avons vu qu'en fait elle croit ceci :
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)
Heureusement, nous n'avons pas besoin d'écrire une formule aussi longue car SUMPRODUCT (SUMPRODUCT) fait tout cela automatiquement.
Une fonction en soi SUMPRODUCT (SUMPRODUCT) nous renvoie un nombre énorme − 10450. À ce stade, la deuxième partie de la formule entre en jeu : /SOMME(C2:C9) or /SOMME(C2:C9), qui renvoie le résultat dans la plage normale des scores, donnant la réponse 83,6.
La deuxième partie de la formule est très importante car permet de corriger automatiquement les calculs. N'oubliez pas que les pondérations ne doivent pas nécessairement totaliser 100 % ? Tout cela grâce à la deuxième partie de la formule. Par exemple, si nous augmentons une ou plusieurs valeurs de poids, la deuxième partie de la formule sera simplement divisée par la valeur la plus élevée, ce qui donnera à nouveau la bonne réponse. Ou nous pouvons rendre les poids beaucoup plus petits, par exemple en spécifiant des valeurs comme 0,5, 2,5, 3 or 4,5, et la formule fonctionnera toujours correctement. C'est génial, non ?