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

Supposons que vous ayez une liste de courses comme illustré dans la capture d'écran ci-dessous. Bien que la fonction MOYENNE d'Excel vous donnerait la moyenne des prix sans tenir compte du poids ou de la quantité, une approche plus précise dans ces cas serait de calculer la moyenne pondérée. Cela reflète mieux le coût réel par unité en donnant aux articles ayant des poids ou des fréquences plus élevés une influence plus forte sur le résultat final.

Pour calculer la moyenne pondérée des prix, utilisez une combinaison des fonctions SOMMEPROD et SOMME comme suit :

Sélectionnez une cellule vide, comme F2, puis entrez 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 fait référence à la colonne Poids, et D2:D18 fait référence à la colonne Prix. Ajustez ces plages selon vos besoins pour votre propre disposition des données. La fonction SOMMEPROD multiplie chaque poids par le prix correspondant et additionne les résultats, tandis que SOMME totalise les poids — produisant ainsi la moyenne pondérée correcte. Assurez-vous d'utiliser des plages de longueur égale et vérifiez qu'il n'y ait pas de cellules non appariées ou vides dans vos données, car cela pourrait entraîner des erreurs de calcul.

Si la moyenne pondérée calculée affiche trop peu ou trop de décimales pour vos préférences, sélectionnez la cellule, puis cliquez sur Augmenter les décimales bouton ou Réduire les décimales bouton sur l'onglet Accueil pour ajuster le nombre de décimales affichées selon vos besoins.

Si vous rencontrez 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 toute ligne d'en-tête dans votre plage de calcul pour garantir des résultats précis. Lorsque vous travaillez avec des jeux de données volumineux, envisagez d'utiliser des plages nommées pour plus de clarté et de facilité de maintenance.

La formule précédente calcule la moyenne pondérée des prix pour tous les articles. Dans une analyse pratique, vous pourriez plutôt souhaiter la moyenne pondérée pour des catégories spécifiques, comme trouver la moyenne pondérée des prix uniquement pour les pommes. Dans ce cas, vous pouvez améliorer la formule pour inclure une condition basée sur vos critères.

Pour ce faire, sélectionnez une cellule vide, comme F8, et entrez 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 paire de poids et de prix uniquement si l'article correspond à la condition (« Pomme » dans ce cas), les additionne, et divise par la somme des poids correspondant à cet article uniquement.

Remarque : Ici, B2:B18 est la colonne Fruit, C2:C18 est le Poids, et D2:D18 est le Prix. Remplacez « Pomme » par un autre article si nécessaire. Cette méthode fonctionne bien pour filtrer selon un critère ; si vous devez filtrer selon plusieurs critères (par exemple, type de fruit et fournisseur), une colonne d'aide ou une formule plus avancée peut être nécessaire.

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

Si la formule renvoie un résultat inattendu, assurez-vous que les critères correspondent à des valeurs dans votre plage cible, et surveillez les cellules vides ou les entrées textuelles dans les colonnes destinées à être numériques.

Dans certaines situations, vous pourriez souvent avoir besoin de calculer des moyennes pondérées sur des plages dont la taille change, contenant des valeurs manquantes ou nécessitant un filtrage flexible, comme appliquer plusieurs critères à la fois. Au lieu de mettre à jour manuellement les formules ou les plages, automatiser le calcul avec une macro VBA peut vous faire gagner du temps et réduire les risques d'erreurs — particulièrement utile lorsque vous traitez de grands jeux de données ou des données régulièrement mises à 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'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 exécuter.
Vous serez invité à sélectionner étape par étape les plages (plage de critères — celle-ci peut être ignorée si non nécessaire, plage de poids et plage de valeurs). Vous pouvez également entrer des critères spécifiques pour filtrer votre calcul ou laisser vide pour prendre en compte toutes les données. La macro prend en charge des plages de données dynamiques, ce qui est pratique si votre tableau grossit ou change régulièrement.

Enfin, vous obtiendrez une boîte de dialogue indiquant le résultat de la moyenne pondérée.

Conseils :

• Cette approche automatise les analyses répétitives de moyennes pondérées et peut être étendue pour gérer des options de filtrage ou de sortie supplémentaires.
• Assurez-vous que les plages sélectionnées ont la même longueur et que les types de données sont cohérents.
• Incluez une gestion d'erreurs de base comme indiqué (par exemple, dans les cas où aucun poids valide n'est trouvé ou la somme des poids est zéro).
• Si vous souhaitez appliquer uniquement aux lignes visibles/filtrées, vous pouvez encore améliorer le code avec une énumération spéciale des cellules.

Si vous rencontrez des problèmes de permission ou de sécurité des macros, assurez-vous que les macros sont activées dans vos paramètres Excel avant d'exécuter le code.

Moyenne avec arrondi dans Excel

Taux moyen de variation dans Excel

Calculer la moyenne/le taux de croissance annuel composé dans Excel

Moyenne mobile/glissante dans Excel

Total cumulé/moyenne dans Excel