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
Derniers articles parBenoît RIVIERE (voir tous)
- Fusionner plusieurs fichiers PDF avec Python - mardi 23 juillet 2024
- Révisez les comptes d’une entreprise à partir de son FEC - lundi 15 juillet 2024
- Clôtures comptables : analysez vos FEC avant de les archiver… - dimanche 30 juin 2024
- Excel : collecter des données juridiques sur les entreprises avec l’API SIRENE – 2ème partie : base SIRET (établissements) - lundi 27 mai 2024
- Analyse de données & FEC provisoire : créer des numéros d’écriture avec un script Python - samedi 27 avril 2024
Pingback: Meilleurs voeux pour 2022 - Audit & Systèmes d'Information