Calculer une moyenne pondérée avec SOMMEPROD

Dans Excel, une moyenne pondérée peut être calculée en décomposant les étapes du calcul. Mais il existe une fonction qui va nous permettre de le faire en une seule opération : SOMMEPROD.
Cette fonction, pas toujours connue, sert pourtant dans de nombreux cas. Aujourd’hui, je vous propose donc le premier d’entre eux et le plus simple, avec une moyenne pondérée.


 

La solution par étapes

Dans l’exemple ci-dessous, nous cherchons à déterminer le prix moyen de vente sur l’année 2016 à partir des données ci-dessous.

Ventes par article calcul prix moyen

On pourrait être tenté de faire la moyenne des 5 prix de vente. Pour ça, on n’a même pas besoin de formule. On sélectionne la plage et Excel nous donne le résultat en bas de la fenêtre dans la barre d’état.

Moyenne arithmetique barre etat Excel

Sauf que cette moyenne est arithmétique, chaque article pèse pour 1/5ème du total. Et ce n’est pas ce qu’on cherche ici, tous les articles n’ont pas le même poids dans nos ventes. Les quantités vont nous servir à pondérer cette moyenne.

Si on procède par étape, on calcule un CA pour chaque produit. On ajoute ensuite la somme du CA et des quantités à notre tableau.

Calcul moyenne ponderee etape intermediaire

Il ne nous reste plus qu’à diviser les deux totaux pour connaître le prix moyen.

Resultat moyenne ponderee calcul etapes
 

Une solution plus rapide

La fonction SOMMEPROD va nous permettre d’obtenir le même résultat. Dans cette fonction, nous entrons deux plages, Excel les multiplie alors entre elles, ligne à ligne, et effectue ensuite la somme. D’où le nom composé de ‘somme’ et ‘produit’.

Pour revenir à notre exemple, si nous entrons la fonction SOMMEPROD avec les quantités et les prix moyens, Excel nous calcule tout de suite le CA total.

Fonction SOMMEPROD somme produits par ligne

Il ne nous reste plus qu’à diviser cette somme par les quantités totales.

Formule calcul moyenne ponderee SOMMEPROD

Une moyenne pondérée en une seule formule sans étape intermédiaire, c’est bien possible !
Pour un prix moyen, la formule à retenir est donc la suivante :

Prix moyen =SOMMEPROD( Quantités ; Prix ) / SOMME( Quantités )

Adaptez la formule à votre cas et à vos variables.
La seule contrainte, c’est de saisir deux plages de taille identique dans la fonction SOMMEPROD. Ça devrait être le cas, sinon il sera difficile de calculer une moyenne. Même pour Excel qui renverra d’ailleurs une erreur.

Erreur SOMMEPROD plages tailles differentes

Ainsi se termine notre première approche de la fonction SOMMEPROD dont les applications sont nombreuses. On peut notamment l’utiliser pour effectuer des sommes conditionnelles.
Nous aurons l’occasion d’en reparler. En attendant, dites-nous en commentaires si vous utilisez cette fonction et dans quels contextes.

 

Abonnez-vous à la newsletter

Illustration couverture ebook 100 trucs et astuces pour Excel-ler au travail

Et recevez gratuitement l'ebook 100 trucs et astuces pour Excel-ler au travail (5€ sur Amazon)

Votre adresse mail ne sera pas communiquée et vous pourrez vous désabonner à tout moment.

Taggé , , .Mettre en favori le Permaliens.

Laisser un commentaire

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.