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.
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.
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.
Il ne nous reste plus qu’à diviser les deux totaux pour connaître le prix moyen.
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.
Il ne nous reste plus qu’à diviser cette somme par les quantités totales.
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.
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.