Comment calculer une moyenne pondérée dans Excel ?

Supposons que vous disposiez d’une liste de courses comme celle illustrée dans la capture d’écran ci-dessous. Alors que la fonction MOYENNE d’Excel calcule un prix moyen sans tenir compte du poids ou de la quantité, une approche plus précise consiste ici à utiliser la moyenne pondérée. Celle-ci reflète bien mieux le coût réel par unité en donnant davantage de poids aux articles dont la quantité ou la fréquence est plus élevée.

Pour calculer le prix moyen pondéré, utilisez une combinaison des fonctions SOMMEPRODet SOMMEcomme suit :

Sélectionnez une cellule vide, par exemple F2, puis saisissez la formule suivante :

=SUMPRODUCT(C2:C18,D2:D18)/SUM(C2:C18)

et appuyez sur la touche Entrée pour obtenir le résultat.

Remarque : Dans cette formule, C2:C18 correspond à la colonne Poids, et D2:D18 correspond à la colonne Prix. Ajustez ces plages en fonction de la structure de vos propres données. La fonction SOMMEPROD multiplie chaque poids par le prix correspondant, puis additionne les résultats, tandis que SOMME totalise les poids — ce qui permet d’obtenir une moyenne pondérée exacte. Veillez à utiliser des plages de même longueur et assurez-vous qu’il n’y a ni incohérence ni cellules vides dans vos données, car cela pourrait entraîner des erreurs de calcul.

Si la moyenne pondérée calculée affiche trop ou pas assez de décimales à votre goût, sélectionnez la cellule, puis cliquez sur le bouton Augmenter les décimales ou sur le bouton Réduire les décimales, situés sous l’onglet Accueil, pour ajuster le nombre de décimales affichées selon vos besoins.

Si vous obtenez une erreur telle que #VALEUR!, vérifiez que chaque cellule référencée contient une valeur numérique et que les plages sont cohérentes. Évitez également d’inclure une ligne d’en-tête dans votre plage de calcul afin d’obtenir des résultats précis. Lorsque vous travaillez avec de grands jeux de données, envisagez d’utiliser des plages nommées pour plus de clarté et une maintenance facilitée.

La formule précédente calcule le prix moyen pondéré pour tous les articles. Dans une analyse pratique, vous souhaiterez peut-être plutôt obtenir la moyenne pondérée pour des catégories spécifiques, par exemple le prix moyen pondéré des pommes uniquement. Dans ce cas, vous pouvez améliorer la formule afin d’y inclure une condition basée sur vos critères.

Pour ce faire, sélectionnez une cellule vide, telle que F8, puis saisissez la formule suivante :

=SUMPRODUCT((B2:B18="Apple")*C2:C18*D2:D18)/SUMIF(B2:B18,"Apple",C2:C18)

Appuyez ensuite sur la touche Entrée pour calculer la moyenne pondérée répondant à vos critères spécifiques. Cette formule multiplie chaque couple poids-prix uniquement si l’article correspond à la condition (« Pomme » dans cet exemple), additionne ces produits, puis divise le tout par la somme des poids associés à cet article uniquement.

Remarque : Ici, B2:B18 correspond à la colonne Fruit, C2:C18 au Poids et D2:D18 au Prix. Remplacez « Pomme » par un autre article selon vos besoins. Cette méthode convient parfaitement au filtrage selon un seul critère ; si vous devez appliquer plusieurs critères (par exemple, type de fruit et fournisseur), une colonne auxiliaire ou une formule plus avancée sera nécessaire.

Après avoir appliqué la formule, vous souhaiterez peut-être ajuster le nombre de décimales pour plus de clarté. Sélectionnez la cellule contenant le résultat, puis utilisez les boutons Augmenter les décimales ou Réduire les décimales situés sous l’onglet Accueil pour modifier le nombre de décimales affichées.

Si la formule renvoie un résultat inattendu, vérifiez que le critère correspond bien à des éléments présents dans votre plage cible et veillez à l’absence de cellules vides ou de valeurs textuelles dans les colonnes censées contenir des données numériques.

Dans certaines situations, vous devez fréquemment calculer des moyennes pondérées sur des plages de taille variable, contenant des valeurs manquantes ou nécessitant un filtrage flexible — comme l’application simultanée de plusieurs critères. Plutôt que de mettre à jour manuellement les formules ou les plages, automatiser ce calcul à l’aide d’une macro VBA vous fait gagner du temps et réduit les risques d’erreur, une solution particulièrement efficace avec de grands jeux de données ou des tableaux régulièrement mis à jour.

Voici comment créer et utiliser une macro VBA pour les moyennes pondérées :

1. Cliquez sur Développeur > Visual Basic(ou appuyez sur)Alt + F11) pour ouvrir la fenêtre de l’éditeur Microsoft Visual Basic pour Applications. Ensuite, cliquez sur Insertion > Module, puis collez le code suivant dans la nouvelle fenêtre de module :

Sub WeightedAverageVBA()
    Dim rngCriteria As Range
    Dim rngWeight As Range
    Dim rngValue As Range
    Dim criteriaStr As String
    Dim totalWeighted As Double
    Dim totalWeight As Double
    Dim i As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rngCriteria = Application.InputBox("Select the range for criteria (optional, press Cancel to skip):", xTitleId, Type:=8)
    criteriaStr = Application.InputBox("Enter criteria for filtering (leave blank for all):", xTitleId, Type:=2)
    Set rngWeight = Application.InputBox("Select the Weight (numeric) range:", xTitleId, Type:=8)
    Set rngValue = Application.InputBox("Select the Value (e.g. Price) range:", xTitleId, Type:=8)
    
    totalWeighted = 0
    totalWeight = 0
    
    If rngCriteria Is Nothing Or criteriaStr = "" Then
        For i = 1 To rngWeight.Cells.Count
            If IsNumeric(rngWeight.Cells(i).Value) And IsNumeric(rngValue.Cells(i).Value) Then
                totalWeighted = totalWeighted + rngWeight.Cells(i).Value * rngValue.Cells(i).Value
                totalWeight = totalWeight + rngWeight.Cells(i).Value
            End If
        Next i
    Else
        For i = 1 To rngWeight.Cells.Count
            If rngCriteria.Cells(i).Value = criteriaStr Then
                If IsNumeric(rngWeight.Cells(i).Value) And IsNumeric(rngValue.Cells(i).Value) Then
                    totalWeighted = totalWeighted + rngWeight.Cells(i).Value * rngValue.Cells(i).Value
                    totalWeight = totalWeight + rngWeight.Cells(i).Value
                End If
            End If
        Next i
    End If
    
    If totalWeight = 0 Then
        MsgBox "Weighted average cannot be calculated: total weight is zero.", vbExclamation, xTitleId
    Else
        MsgBox "Weighted average: " & totalWeighted / totalWeight, vbInformation, xTitleId
    End If
End Sub

2. Appuyez sur F5(ou cliquez sur le) bouton Exécuter) pour lancer l’exécution.
Vous serez invité(e) à sélectionner les plages étape par étape (plage de critères – que vous pouvez ignorer si elle n’est pas nécessaire –, plage des poids et plage des valeurs). Vous pouvez également saisir des critères spécifiques pour filtrer votre calcul ou laisser ce champ vide afin d’intégrer toutes les données. La macro prend en charge des plages dynamiques, ce qui la rend particulièrement pratique si votre tableau s’agrandit ou évolue régulièrement.

Vous obtiendrez enfin une boîte de message affichant le résultat de la moyenne pondérée.

Conseils :

• Cette approche automatise l’analyse répétitive de la moyenne pondérée et peut être facilement étendue pour prendre en charge des options supplémentaires de filtrage ou de sortie.
• Assurez-vous que les plages sélectionnées ont la même longueur et que leurs types de données sont cohérents.
• Incluez une gestion d’erreurs de base, comme illustré, par exemple lorsque aucun poids valide n’est trouvé ou lorsque la somme des poids est nulle.
• Si vous souhaitez appliquer le calcul uniquement aux lignes filtrées ou visibles, vous pouvez encore améliorer le code en utilisant une énumération spécifique des cellules.

Si vous rencontrez des problèmes liés aux autorisations ou à la sécurité des macros, veillez à ce que les macros soient activées dans les paramètres d’Excel avant d’exécuter le code.

Moyenne d’une plage avec arrondi dans Excel

Taux de variation moyen dans Excel

Calculer le taux de croissance annuel moyen/composé dans Excel

Moyenne mobile/glissante dans Excel

Total cumulé / moyenne mobile dans Excel