INFORMATIQUE

Moyenne pondérée Excel : comment la calculer avec des coefficients ?

Pour calculer une moyenne pondérée sur Excel, utilisez la formule =SOMMEPROD(B2:B4;C2:C4)/SOMME(C2:C4). Elle multiplie chaque valeur par son coefficient, additionne les résultats, puis divise le total par la somme des coefficients.

Cette méthode sert aussi bien à calculer un prix moyen d’achat qu’à pondérer des notes ou des indicateurs de gestion. Elle évite de donner la même importance à des valeurs qui ne représentent pas les mêmes quantités. Voici comment l’appliquer à vos tableaux, choisir les bonnes plages et éviter les erreurs qui faussent les résultats.

Calculer une moyenne pondérée sur Excel avec SOMMEPROD

La formule repose sur deux fonctions : SOMMEPROD, qui multiplie les valeurs par leurs poids et additionne les produits, et SOMME, qui totalise ces poids. Le résultat correspond à la moyenne réelle, compte tenu de l’importance de chaque valeur.

Rayonnages d'un entrepôt remplis de cartons de marchandises

Exemple : le prix moyen d’un produit acheté en plusieurs lots

Un gestionnaire commande le même article à trois reprises, mais les prix et les quantités changent. Avant de fixer son prix de vente, il souhaite connaître le prix moyen réellement payé par unité.

Son tableau Excel contient les données suivantes :

AchatPrix unitaire (B)Quantité (C)
Lot 112 €10
Lot 215 €20
Lot 318 €5
Schéma du calcul d'une moyenne pondérée dans Excel : 120 + 300 + 90 = 510 euros divisés par 35 unités, soit 14,57 euros
SOMMEPROD multiplie et additionne, SOMME totalise les quantités : 510 ÷ 35 = 14,57 €.

Dans une cellule libre, il saisit =SOMMEPROD(B2:B4;C2:C4)/SOMME(C2:C4).

Excel calcule d’abord le montant de chaque achat : 12 × 10 = 120 €, 15 × 20 = 300 € et 18 × 5 = 90 €. Les trois commandes représentent donc 510 € pour 35 unités. Le prix moyen pondéré est de 14,57 € par unité.

Une moyenne classique des trois prix aurait donné 15 €. Cette différence de 0,43 € vient des quantités achetées : le prix de 15 € concerne 20 unités, tandis que celui de 18 € n’en concerne que 5.

Sur un tableau de suivi des achats, cette distinction compte. Utiliser 15 € comme prix moyen reviendrait à considérer que chaque commande contient autant d’articles.

Comprendre la formule SOMMEPROD / SOMME

La fonction SOMMEPROD utilise la syntaxe =SOMMEPROD(matrice1;[matrice2];...). Elle effectue les multiplications ligne par ligne, puis additionne leurs résultats.

Dans notre exemple, SOMMEPROD(B2:B4;C2:C4) renvoie 510. Le calcul SOMME(C2:C4) renvoie 35, soit le total des quantités.

La division finale donne donc 510 / 35. Les quantités servent de coefficients : plus une commande contient d’unités, plus son prix influence la moyenne.

Microsoft utilise également cette méthode dans son aide consacrée au calcul des moyennes. Vous pouvez l’appliquer sans ajouter de colonne de multiplication à votre fichier.

Comment calculer une moyenne avec des coefficients sur Excel ?

Pour calculer une moyenne avec coefficient sur Excel, placez les valeurs dans une colonne et leurs coefficients dans la colonne voisine. La formule reste identique à celle d’un prix moyen pondéré : seules les données changent.

Cahiers ouverts, crayons de couleur et calculatrice sur un bureau blanc

Associer chaque valeur à son coefficient

Une formatrice prépare le bilan d’une session. Elle dispose de deux contrôles, d’un projet et d’un examen final. Chaque évaluation ne doit pas compter de la même manière dans la note globale.

ÉvaluationNote (B)Coefficient (C)
Contrôle 1121
Contrôle 292
Projet163
Examen144

La formule à saisir est =SOMMEPROD(B2:B5;C2:C5)/SOMME(C2:C5).

Excel additionne les produits : 12 × 1 + 9 × 2 + 16 × 3 + 14 × 4 = 134. Le total des coefficients est de 10. La moyenne pondérée atteint donc 13,4, contre 12,75 pour la moyenne simple.

Le projet et l’examen influencent davantage le résultat que les contrôles. C’est le rôle des coefficients : donner un poids différent à chaque évaluation, sans modifier sa note.

Utiliser des coefficients exprimés en pourcentage

Les poids peuvent aussi être exprimés en pourcentage. Par exemple, trois résultats de 8, 15 et 11, associés respectivement à 50 %, 25 % et 25 %, donnent une moyenne pondérée de 10,5.

Dans Excel, saisissez les pourcentages comme de véritables valeurs numériques au format pourcentage. Vous pourrez conserver la formule SOMMEPROD divisée par SOMME.

Lorsque les poids totalisent 100 %, la somme des produits donne directement la moyenne. Mais si les pourcentages ne totalisent pas 100 %, conservez la division par SOMME pour obtenir une moyenne pondérée normalisée.

Cette méthode convient aux évaluations, mais aussi aux tableaux de performance dans lesquels plusieurs indicateurs contribuent différemment à un résultat global.

Moyenne simple ou moyenne pondérée : quelle formule Excel choisir ?

Utilisez =MOYENNE(A1:A20) lorsque toutes les valeurs ont la même importance. Dès que certaines représentent davantage de quantités, de poids ou de coefficients, passez à SOMMEPROD divisée par SOMME.

Calculer une moyenne simple avec MOYENNE

Si vous cherchez comment calculer une moyenne sur Excel, la fonction MOYENNE est généralement le premier réflexe. Elle additionne les nombres et les divise par leur nombre.

Sa syntaxe est =MOYENNE(nombre1;[nombre2];...). Elle accepte jusqu’à 255 arguments, mais une plage de cellules suffit pour la plupart des calculs.

Attention aux cellules qui semblent vides. Dans une plage, MOYENNE ignore les cellules réellement vides et les textes, mais elle compte les zéros. Une cellule contenant 0 participe donc au calcul, contrairement à une cellule sans valeur. C’est son contenu, et non son apparence, qui détermine le résultat.

Pour approfondir la saisie et l’utilisation des calculs, consultez notre guide sur les formules et fonctions dans Excel.

Pourquoi les deux résultats diffèrent

Reprenons les trois prix d’achat : 12 €, 15 € et 18 €. La fonction MOYENNE donne 15 €, car elle attribue le même poids à chaque prix.

Avec SOMMEPROD, le prix de chaque commande est multiplié par sa quantité. Les 20 articles achetés à 15 € influencent donc davantage le résultat que les 5 articles achetés à 18 €.

La différence tient à la question posée. Pour connaître le prix moyen des trois tarifs proposés, MOYENNE convient. Pour connaître le prix moyen réellement payé par article, il faut pondérer par les quantités.

Les trois erreurs qui faussent le calcul

Trois problèmes méritent une vérification : une somme des poids égale à zéro, des plages de dimensions différentes et des références qui se déplacent lors d’une recopie. Chacun peut empêcher le calcul ou produire un résultat incorrect.

Erreur #DIV/0! : vérifier la somme des coefficients

Si tous les coefficients valent zéro ou si la plage des poids ne contient aucune valeur numérique, le dénominateur vaut zéro. Excel affiche alors #DIV/0!.

Pour afficher une cellule vide à la place de l’erreur, vous pouvez adapter la formule avec SIERREUR :

=SIERREUR(SOMMEPROD(B2:B4;C2:C4)/SOMME(C2:C4);"")

Cette protection évite l’affichage d’une erreur dans un tableau, mais elle masque aussi les autres erreurs éventuelles. Ne l’utilisez pas pour cacher un problème de données que vous devez corriger.

Une autre solution consiste à vérifier le dénominateur avant la division avec la fonction SI d’Excel. Elle permet de choisir explicitement le résultat à afficher lorsque la somme des coefficients est nulle.

Erreur #VALEUR! : contrôler les plages utilisées

SOMMEPROD exige des plages de mêmes dimensions. Si vous multipliez B2:B4 par C2:C5, les plages ne contiennent pas le même nombre de cellules : Excel renvoie #VALEUR!.

Vérifiez donc les premières et dernières lignes de chaque plage. Les valeurs et leurs coefficients doivent correspondre ligne par ligne.

SOMMEPROD traite les textes et les valeurs non numériques comme des zéros. Cela ne signifie pas qu’il faut laisser des données incorrectes dans votre tableau : un prix saisi comme du texte risque de fausser le total sans provoquer d’alerte visible. Pour convertir ce texte en nombre dans Excel, plusieurs méthodes existent.

Si vous préférez détailler chaque produit dans une colonne intermédiaire, notre tutoriel pour faire des multiplications dans Excel explique les opérations de base.

Recopie vers le bas : empêcher les coefficients de glisser

Une personne prépare plusieurs moyennes dans un tableau. La première formule fonctionne. Elle la recopie vers le bas et découvre des résultats absurdes : la plage des coefficients s’est déplacée avec les autres références.

Excel utilise par défaut des références relatives. Lors d’une recopie, les adresses de cellules évoluent selon le déplacement de la formule.

Si les coefficients se trouvent toujours dans la même plage, fixez cette référence avec des signes dollar. Par exemple, $C$2:$C$4 restera identique lors de la recopie.

Sous Windows, sélectionnez la référence dans la barre de formule et appuyez sur F4 pour faire défiler les différents types de références. Vérifiez ensuite que les plages des valeurs et des coefficients correspondent toujours aux bonnes données.

Pour déplacer uniquement les calculs sans modifier la présentation du tableau, le Collage spécial avec l’option « Formules » est également utile. Le raccourci Ctrl + Alt + V ouvre le Collage spécial.

Questions fréquentes

Existe-t-il une fonction MOYENNE.PONDEREE dans Excel ?

Non. Excel ne possède pas de fonction nommée MOYENNE.PONDEREE. La méthode utilisée par Microsoft consiste à diviser SOMMEPROD par SOMME.

Ne confondez pas Excel avec Google Sheets, qui propose une fonction spécifique : AVERAGE.WEIGHTED(valeurs; poids). Dans Google Sheets, les poids doivent être non négatifs et au moins l’un d’eux doit être positif.

Peut-on ajouter une condition au calcul ?

Vous pouvez associer SOMMEPROD à SOMME.SI pour ne conserver que les lignes correspondant à un critère.

Supposons que la colonne A indique le lot, la colonne B le prix et la colonne C la quantité. Pour calculer le prix moyen pondéré du « Lot A », utilisez :

=SOMMEPROD((A2:A4="Lot A")*B2:B4*C2:C4)/SOMME.SI(A2:A4;"Lot A";C2:C4)

Avec deux achats du Lot A, 10 unités à 12 € et 5 unités à 18 €, le résultat est de 14 € par unité. Cette construction combine un calcul pondéré et un filtrage par critère.

Pour aller plus loin dans les calculs conditionnels, consultez notre article sur SOMMEPROD SI. Les fonctions MOYENNE.SI et MOYENNE.SI.ENS, quant à elles, calculent des moyennes simples filtrées, et non des moyennes pondérées.

Quelle formule utiliser en anglais et sur Mac ?

Dans une version anglaise d’Excel, SOMMEPROD devient SUMPRODUCT et SOMME devient SUM. La formule s’écrit généralement :

=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)

Le séparateur des arguments dépend des réglages régionaux. Excel utilise souvent la virgule dans les configurations anglaises, mais pas systématiquement.

La fonction SOMMEPROD est également disponible sur Excel pour Microsoft 365 et Excel 2021 sur Mac. La logique de calcul reste la même.

Peut-on calculer une moyenne pondérée dans un tableau croisé dynamique ?

Oui, à condition de préparer les données. Un champ calculé classique d’un tableau croisé dynamique ne reproduit pas directement une moyenne pondérée en multipliant deux champs : les champs sont d’abord agrégés.

La méthode consiste à ajouter une colonne « Valeur × Poids » dans les données sources, puis à créer un champ calculé qui divise ce champ par le champ des poids. Comme Excel additionne chaque champ avant de faire la division, le tableau croisé dynamique calcule le rapport entre la somme de cette colonne et la somme des poids.

Vous retrouvez ainsi le principe de SOMMEPROD / SOMME, tout en conservant les regroupements de votre tableau croisé dynamique.

Les poids doivent-ils totaliser 100 % ?

Utilisez les pourcentages comme coefficients. Pour des valeurs de 8, 15 et 11 avec des poids de 50 %, 25 % et 25 %, le calcul donne 8 × 50 % + 15 × 25 % + 11 × 25 % = 10,5.

Dans Excel, la formule SOMMEPROD / SOMME fonctionne également avec ces poids. Elle reste pratique lorsque les pourcentages ne totalisent pas exactement 100 %, car elle rapporte le résultat à la somme des poids effectivement renseignés.

Les commentaires sont fermés.

Article Suivant :

0 %