Le calcul de moyennes pondérées est d’usage courant à l’image de la moyenne de notes à un examen (du DEC par exemple) ou du coût unitaire moyen pondéré (CUMP) de références en stock.
Le calcul de la moyenne arithmétique (obtenue par la formule : totalisation / dénombrement) est assez simple à obtenir. Excel propose une fonction attitrée : MOYENNE(plage de cellules). Dans notre exemple : 5 + 3 + 2 = 10 € / 3, soit une moyenne de 3,33 €
La moyenne pondérée se calcule en additionnant la multiplication opérée entre les valeurs (ici la quantité) et leur coefficient de pondération (ici le prix unitaire), cette totalisation étant ensuite divisée par la somme des valeurs (ici les quantités). Dans notre exemple, la moyenne pondérée se détermine ainsi : (18 x 5 € + 15 x 3 €) /45… soit une moyenne pondérée (ou CUMP) de 3,53 €.
Excel n’offre aucune fonction dédiée mais l’usage de la fonction SOMMEPROD(plage de cellules;plage de cellules 2) associée à SOMME(plage de cellules) résout aisément ce calcul. Dans le cas qui nous occupe, la formule de calcul du CUMP est rédigée ainsi :
=SOMMEPROD(M7:M9;L7:L9)/SOMME(L7:L9)
La fonction SOMMEPROD renvoie la somme des produits des plages ou matrices spécifiées (plus d’informations sur cette fonction sur le site de Microsoft). Dans notre exemple, la formule calcule la somme des produits (SOMMEPROD) entre la plage M7:M9 (prix unitaires) et L7:L9 (quantités) divisée par la somme des quantités (SOMME(L7:L9)).
Ce même calcul de CUMP peut être obtenu à partir d’une requête SQL :
SELECT ACHATS.Réf, Sum([prix]*[quantité]/[totalqté]) AS CUMP FROM (SELECT ACHATS.Réf, Sum(ACHATS.Quantité) AS TotalQté FROM ACHATS GROUP BY ACHATS.Réf) AS TOTALQTE INNER JOIN ACHATS ON TOTALQTE.Réf = ACHATS.Réf GROUP BY ACHATS.Réf;
La sous-requête regroupement (SELECT ACHATS.Réf, Sum(ACHATS.Quantité) AS TotalQté calcule la totalisation des quantités (ici 45) puis la requête détermine le CUMP à l’aide de la formule suivante :
Sum([prix]*[quantité]/[totalqté]) AS CUMP
La requête SQL renvoie le même CUMP que la formule Excel, soit 3,53 € :
Approfondir le sujet : EXCEL, VBA : Valoriser un stock selon la méthode du PEPS (FIFO) à partir des mouvements d’une référence donnée
Pingback: Meilleurs voeux pour 2022 - Audit & Systèmes d'Information