Calcul de moyenne avec coefficient – La moyenne pondérée dans Excel

Vous pouvez calculer la moyenne pondérée dans Excel ou bien la moyenne avec coefficients en adoptant deux approches:

• L’approche manuelle simulant le calcul mathématique normal (avec ou sans la fonction SOMME d’Excel).
• L’approche plus automatique avec la fonction SOMMEPROD d’Excel.

Pour illustrer les 2 méthodes, on va prendre comme exemple le tableau Excel suivant où on veut calculer la moyenne des notes avec coefficients des étudiants dans 3 matières.

1- Calcul de moyenne avec coefficient dans Excel – Approche manuelle

Dans cette méthode basique, on va simuler le calcul mathématique normal pour calculer la moyenne pondérée.

Pour rappel, la formule mathématique de la moyenne pondérée (moyenne avec coefficients) est la suivante:

C’est à dire, c’est la somme des produits de chaque note par son coefficient, divisée par la somme des coefficients.

Dans notre tableau Excel (voir l’exemple d’en haut), la plage C3:E14 constitue la plage des notes des matières, et la plage C2:E2 est la plage des coefficients. La moyenne pondérée est à calculer dans la plage F3:F14.

On va donc saisir la formule suivante dans la cellule F3 (puis taper sur Entrée):

=(C3*$C$2+D3*$D$2+E3*$E$2)/($C$2+$D$2+$E$2)

ou encore en utilisant la fonction SOMME,

=(C3*$C$2+D3*$D$2+E3*$E$2)/SOMME($C$2:$E$2)

La formule peut vous paraître compliquée, mais c’est juste =(C3*C2+D3*D2+E3*E2)/(C2+D2+E2) avec l’ajout du signe $ pour les références absolues de la ligne des coefficients.

Note: Si vous avez des difficultés dans la saisie des formules et l’utilisation des fonctions, pensez à faire un tour au tutoriel Excel – Fonctions de calcul. L’utilisation des formules et fonctions dans Excel y est beaucoup plus détaillée.

Revenons à notre formule:

Pourquoi utilise t-on les références absolues? Et bien, simplement parce qu’on veut conserver une référence fixe à nos coefficients lorsqu’on étend la formule aux autres cellules.

Le plus simple pour vous serait donc de saisir la formule simple sans signe $, ou encore coupler la souris à la saisie des opérateurs mathématiques (*, +, et /), puis cliquer au niveau de l’élément qu’on veut rendre en référence absolue (l’élément dans lequel on veut insérer le signe $) et appuyer sur la touche F4 du clavier pour obtenir la référence voulue (c’est à dire 2 signes $):

Par exemple, je veux changer dans notre formule C2 en $C$2: je clique donc dans la formule sur l’élément C2, puis je tape sur la touche F4 du clavier; C2 se transforme donc dans la formule en $C$2.

Revenons maintenant à notre calcul de moyenne pondérée.

Notre formule =(C3*$C$2+D3*$D$2+E3*$E$2)/($C$2+$D$2+$E$2) est maintenant prête dans la cellule F3. On étend donc cette formule aux cellules sous-jacentes (F4 à F14) en glissant la poignée de recopie incrémentée ou en double-cliquant sur elle. Si vous ne savez pas comment faire pour étendre une formule Excel sur une ligne ou colonne, consultez l’article sur comment copier une formule Excel.

Voici l’aperçu final de notre tableau de calcul de moyenne avec coefficient en formule simple:

2- Calcul de moyenne avec coefficient – fonction SOMMEPROD d’Excel

---

Exemple Pratique à télécharger pour suivre cette section:

Je vous ai préparé cet exemple pratique de l’utilisation de la fonction SOMMEPROD() pour calculer une moyenne avec coefficients, que vous pouvez utiliser comme modèle prêt à l’emploi, ou bien comme base pour suivre ce qui suit:

---

Cette deuxième méthode est plus simple pour calculer la moyenne pondérée dans Excel et utilise la fonction SOMMEPROD d’Excel. C’est donc la méthode recommandée.

Comme son nom l’indique, la fonction SOMMEPROD effectue une somme des produits des éléments de deux matrices (ou deux plages de nombres pour faire plus simple).

La syntaxe de la fonction SOMMEPROD d’Excel est comme suit:

=SOMMEPROD(matrice1; matrice2; matrice3;...)

Si on prend l’exemple de cette syntaxe, la fonction SOMMEPROD va faire la multiplication de l’élément 1 de la matrice 1 par l’élément 1 de la matrice 2 par l’élément 1 de la matrice 3… puis la multiplication de l’élément 2 de la matrice 1 par l’élément 2 de la matrice 2 par l’élément 2 de la matrice 3, et ainsi de suite. Et lorsqu’elle aura parcouru tous les éléments des matrices, elle additionne le tout.

Plus simplement (pour le cas de 3 matrices):

SOMMEPROD(matrice1; matrice2; matrice3) = (Elément1Matrice1 * Elément1Matrice2 * Elément1Matrice3) + (Elément2Matrice1 * Elément2Matrice2 * Elément2Matrice3)...

Notre cas de calcul de moyenne avec coefficient est plus simple et on n’a que deux matrices: Plage des notes (pour chaque étudiant) et plage des coefficients (plage fixe).

Donc, pour calculer la moyenne pondérée dans Excel, on utilise la fomule suivante:

=SOMMEPROD(Plage des notes; Plage des coefficients)/SOMME(Plage des coefficients)

La plage des coefficients en référence absolue bien sûr (voir Section 1 ci-haut).

Dans notre exemple, on double-clique dans la cellule F3, puis:

• Soit on saisit manuellement la formule suivante:
  =SOMMEPROD(C3:E3;$C$2:$E$2)/SOMME($C$2:$E$2)
• Soit on utilise la souris pour sélectionner les plages requises:
  1- On saisit =SOMMEPROD(
  2- Puis, on sélectionne par la souris la plage des notes (de C3 à E3)
  3- On saisit le point-virgule (;)
  4- Puis, on sélectionne par la souris la plage des coefficients (de C2 à E2)
  5- On saisit la parenthèse fermante ) et le caractère / indiquant la division
  6- Puis, on saisit SOMME(
  7- On sélectionne de nouveau la plage des coefficients (de C2 à E2)
  8- Et enfin, on saisit la parenthèse fermante )
  9- Entrée.
  10-On change la référence des deux plages C2:E2 en référence absolue en cliquant dedans et en appuyant sur la touche F4 pour que ça se transforme en $C$2:$E$2.

La cellule F3 contient maintenant notre formule pour calculer la moyenne pondérée; on n’a qu’à l’étendre pour les cellules de dessous (F4 jusqu’à F14) en glissant la poignée de recopie incrémentée ou en la double-cliquant. Comme déjà dit dans la section 1, si vous avez des difficultés pour étendre une formule Excel sur une colonne ou une ligne, consultez l’article comment copier une formule Excel.

Voici l’aperçu final de notre tableau de calcul de moyenne avec coefficient en utilisant les fonctions SOMMEPROD et SOMME:

3- Calcul de moyenne avec coefficient dans Excel en cas d’absence d’étudiants

---

Exemple Pratique à télécharger pour suivre cette section:

Je vous ai préparé cet exemple pratique de l’utilisation des fonctions SOMMEPROD() et SOMME.SI pour calculer une moyenne avec coefficients en cas d’absence d’élèves, que vous pouvez utiliser comme modèle prêt à l’emploi, ou bien comme base pour suivre ce qui suit:

---

Le calcul de moyenne pondérée dans Excel en cas d’absence justifiée d’étudiants peut paraître complexe, surtout qu’Excel n’est pas très clément dans ce genre de situations:

Lorsque vous laissez donc une note vide ou que vous la remplissez par un texte indicatif (Absent, Malade,…), Excel compte un simple zéro pour la note tout en conservant tous les coefficients dans ses calculs (dans le cas de formule avec SOMMEPROD) ou même affiche une erreur de type de données (dans le cas d’une formule sans SOMMEPROD); ce qui fausse dans tous les cas vos calculs.

Heureusement, on peut facilement résoudre ce problème en s’aidant d’une nouvelle fonction Excel: SOMME.SI, qu’on va appliquer à nos coefficients pour n’inclure que ceux concernés.

La syntaxe de la fonction Excel SOMME.SI est comme suit:

=SOMME.SI(Plage_Source; Critère; [Plage_à_additionner])

C’est une fonction à 2 ou 3 arguments puisque le 3ème argument (plage à additionner) est optionnel (c’est pourquoi il est mis entre crochets). Si ce 3ème argument est omis, la plage à additionner est la plage source.

Mais c’est quoi le rôle exact de cette fonction Excel SOMME.SI?

La fonction SOMME.SI fait la somme d’une plage de cellules (plage à additionner), mais à une condition: le critère appliqué à la plage source.

Ou plus simplement, si la plage source remplit le critère, la fonction SOMME.SI fait la somme de la plage à additionner (qui peut être la plage source en cas d’omission du 3ème argument).

Ce qu’on va faire dans notre exemple de ce tutoriel, c’est d’appliquer la fonction SOMME.SI aux coefficients, au lieu de la fonction SOMME ou la simple addition, pour ne faire cette somme de coefficients que pour les coefficients dont les notes correspondantes sont supérieures ou égales à Zéro; c’est à dire, on lui dit d’ignorer les coefficients dont les notes correspondantes sont vides ou contiennent un texte.

Reprenons notre exemple de ce tutoriel et supposons que les deux étudiants 3 et 7 ont été absents lors de l’examen de la Matière 2.

Sans fonction SOMME.SI, le résultat sera comme suit:

Appliquons maintenant la fonction SOMME.SI à notre exemple.

Dans notre fonction SOMME.SI:

• la Plage_Source sera la plage des notes;
• le critère sera “>=0“;
• la Plage_à_additionner sera la plage des coefficients.

Donc, la formule de la somme des coefficients sera comme suit:

=SOMME.SI(Plage_des_Notes; ">=0"; Plage_des_Coefficients)

Et la formule globale de la moyenne pondérée sera donc comme suit pour la cellule F3 (de préférence utiliser la formule avec SOMMEPROD et SOMME.SI):

• En formule simple (à éviter en cas de saisie de texte dans la cellule des notes):
  =(C3*$C$2+D3*$D$2+E3*$E$2)/SOMME.SI(C3:E3; ">=0"; $C$2:$E$2)

• Avec SOMMEPROD (formule préférée):
  =SOMMEPROD(C3:E3;$C$2:$E$2)/SOMME.SI(C3:E3; ">=0"; $C$2:$E$2)

Le résultat de notre tableau de calcul de moyenne avec coefficient en cas de notes vides ou en texte est maintenant comme suit: