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

The following two tabs change content below.

• Bio
• Derniers articles

Benoît RIVIERE

Après seize années passées en cabinet d’expertise-comptable et de commissariat aux comptes (où j’ai exercé comme expert-comptable et chef de mission audit), j’ai pris le poste de directeur comptable d’un groupe de distribution automobile en novembre 2014. Au cours de ma carrière, j’ai acquis une expérience significative en audit et en exploitation des systèmes d’information (analyse de données, automatisation des tâches, programmation informatique) au service de la production des comptes annuels et consolidés. C’est cette expérience personnelle et ma passion pour l’informatique que je partage sur ce blog. Mon CV / Réalisations personnelles et projets informatiques / Ma collection / Me contacter

Derniers articles parBenoît RIVIERE (voir tous)

• Excel : collecter des données juridiques sur les entreprises avec l’API SIRENE – 1ère partie : base SIREN (entreprises) - lundi 15 avril 2024
• Obtenir les données financières de sociétés au format Excel - mardi 2 avril 2024
• Robotic Process Automation (RPA) et Python : piloter des applications web - dimanche 24 mars 2024
• Initiation à la programmation en Basic : nouveau compilateur FreeBASIC - lundi 18 mars 2024
• Retrogaming : (re)jouer aux jeux vidéo d’antan - lundi 11 mars 2024