Comment calculer le prix unitaire moyen pondéré dans Excel

Ouvrez la feuille d’achats Excel (.xlsx) dans Cue Office. Utilisez SUMPRODUCT pour multiplier le prix unitaire de chaque lot par sa quantité et additionner les coûts. Divisez le total par le nombre d’unités achetées pour obtenir le coût moyen par unité.

Calculer la moyenne lorsque les lots ont des tailles différentes

Dans la feuille d’achats, B2:B4 contient les prix unitaires et C2:C4 les quantités associées. Un lot de 100 unités pèse deux fois plus qu’un lot de 50 dans le coût moyen.

Prix unitaires et quantités à gauche, cellules G2 et G3 vides à droite
Repérez les colonnes du prix unitaire, de la quantité et du résultat.

Sélectionnez G2 et collez les deux formules en une fois. La première calcule la moyenne pondérée en G2. La seconde calcule la moyenne simple des trois prix en G3.

=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)
=AVERAGE(B2:B4)

En G2, SUMPRODUCT calcule le coût total (10 × 100 + 16 × 50 + 28 × 0 = 1 800). SUM additionne les quantités pour obtenir 150. Le résultat est 12 par unité. En G3, AVERAGE additionne les trois prix et divise la somme par 3, ce qui donne 18. Enregistrez et rouvrez le classeur pour vérifier les deux valeurs.

Barre de formule avec SUMPRODUCT divisé par SUM, G2 vaut 12 et G3 vaut 18
Après réouverture, la moyenne pondérée est de 12 en G2 et la moyenne simple de 18 en G3.

Vérifier l’effet d’une modification du prix ou de la quantité

Dans une copie, passez le prix de P03 en B4 de 28 à 34. Comme la quantité du lot est de 0, il ajoute 0 au coût et au nombre d’unités. G2 reste à 12, mais G3 inclut 34 et passe de 18 à 20.

Rétablissez 28 en B4, puis passez C3 de 50 à 100. Les deux lots achetés comptent chacun 100 unités. Les prix 10 et 16 ont le même poids et G2 passe à 13. Les trois prix restent les mêmes, donc G3 reste à 18. Après vérification, remettez 50 en C3.

Quantité de P02 passée à 100, G2 passe à 13 et G3 reste à 18
Avec une quantité identique en P02 et P01, les deux lots achetés ont le même poids.

Si la quantité totale est de 0, le coût moyen par unité est indéfini. Vérifiez si des quantités reçues manquent. S’il n’y a eu aucun achat, omettez la moyenne dans le rapport.

Faire écrire la moyenne pondérée en G2 par Cue AI

Ouvrez une copie de la feuille avec G2 et G3 vides. Ouvrez Cue AI dans la barre Home et envoyez la demande ci-dessous.

Bouton Cue AI sur Home et volet Cue AI ouvert
Le volet Cue AI s’ouvre à côté de la feuille d’achats.
Répartis le coût total des achats sur le nombre d’unités réellement achetées pour obtenir le coût moyen par unité. Utilise les prix unitaires et quantités de B2:C4. Mets un résultat en G2 qui se met à jour si un prix ou une quantité change. Modifie uniquement G2 et conserve les données source et les libellés.

Cue AI a écrit en G2 une formule avec SUMPRODUCT pour le coût et SUM pour les quantités. Enregistrez et rouvrez la copie. Vérifiez que G2 vaut 12, que G3 est vide et que les en-têtes et la source sont intacts. Passez provisoirement C3 à 100 pour obtenir 13 en G2, puis rétablissez 50 en C3.

Classeur rouvert avec la formule pondérée en G2, le résultat 12 et G3 vide
Rouvrez la copie, sélectionnez G2 pour afficher la formule et vérifiez que G3 est vide.

S’exercer avec trois lots d’achats

Afficher l’exemple complet

Créez une feuille dans Cue Office et collez les données dans A1. Laissez G2 et G3 libres pour les deux formules ci-dessus.

Batch	Unit price	Quantity		Metric		Result
P01	10	100		Weighted unit price
P02	16	50		Simple mean
P03	28	0

Vérifier le coût unitaire lors du rapprochement de fin de mois

Si un article arrive en plusieurs livraisons, demandez à Cue AI d’écrire la moyenne pondérée dans une copie de la feuille mensuelle, avec les quantités réellement reçues. Pour une grande livraison, vérifiez que la formule associe son prix et sa quantité. Additionnez séparément les quantités pour contrôler le diviseur.

En cas d’écart avec l’entrepôt, ouvrez gratuitement les bons de livraison (.pdf) dans Cue Office et vérifiez les quantités avant de corriger la feuille Excel (.xlsx). Notez la livraison ajoutée ou corrigée dans Word (.docx). Dans le rapport PowerPoint (.pptx), présentez coût, quantité et prix unitaire moyen révisés. Cue Office permet de modifier gratuitement ces trois formats Office.

Télécharger Cue Office gratuitement