Comment calculer une moyenne pondérée dans Excel avec des pourcentages

Le calcul d'une moyenne pondérée dans Excel avec des pourcentages vous permet de traiter des données d'importance variable.

Une moyenne pondérée est similaire à la moyenne standard, mais elle met l'accent sur des valeurs spécifiques en fonction de leurs poids attribués. Lorsque les différents poids sont exprimés en pourcentage, les résultats sont souvent plus significatifs.

Dans cet article, vous apprendrez les étapes pour calculer une moyenne pondérée à l'aide de pourcentages dans Excel. Nous couvrons les formules, fonctions et astuces Excel nécessaires que vous devez connaître.

Allons-y.

Table des matières

• Qu'est-ce qu'une moyenne pondérée ?
  • Utilisation de pourcentages ou d'unités
• Configuration de votre feuille de calcul Excel
  • Formatage des cellules pour les pourcentages
• Comment calculer une moyenne pondérée dans Excel
  • Utilisation de la fonction Excel SOMMEPROD
  • Diviser par le poids total
  • Instructions pour Excel
  • Décomposer la formule
• Scénarios du monde réel
  • Systèmes de notation
  • Analyse financière
• 4 erreurs courantes et dépannage
  • 1. Données et pondérations mal alignées
  • 2. Ne pas tenir compte des pourcentages
  • 3. Références de cellules incorrectes
  • 4. Utiliser la mauvaise fonction
• Notre dernier mot

Qu'est-ce qu'une moyenne pondérée ?

Les moyennes pondérées et les pourcentages sont des concepts essentiels dans de nombreux domaines, notamment la finance, les statistiques et l'analyse de données.

Une moyenne pondérée est un type de moyenne qui tient compte de l'importance relative d'un ensemble de valeurs.

Comparons cela à la moyenne simple que vous avez probablement utilisée à plusieurs reprises. La moyenne arithmétique simple traite toutes les valeurs de la même manière lors du calcul du résultat.

Avec une moyenne pondérée, toutes les valeurs n'ont pas le même poids. Les poids sont attribués aux valeurs en fonction de leur importance ou de leur pertinence.

En termes mathématiques, une moyenne pondérée est calculée par :

1. multiplier chaque valeur par son poids correspondant
2. additionner les produits pour obtenir le total
3. diviser le total par la somme des poids

La formule mathématique peut être représentée par :

Moyenne pondérée = (?(valeur × poids)) / ?(poids)

Vous pouvez écrire la formule mathématique lorsque vous travaillez avec des macros et VBA.

Cependant, ce didacticiel utilise quelques fonctions utiles de Microsoft Excel pour simplifier le calcul. Vous constaterez qu'il s'agit de raccourcis faciles à utiliser.

Utilisation de pourcentages ou d'unités

Certains calculs pondérés utilisent des pourcentages et d'autres utilisent des unités.

Par exemple, si vous faites la moyenne des prix dans un magasin, vous utiliserez des unités monétaires qui représentent le prix d'un article.

Si un crayon vaut cinquante cents et qu'un stylo vaut un dollar, les poids sont respectivement de 0,5 et 1,0.

Les notes des collèges sont plus susceptibles d'être basées sur des pourcentages. Par exemple, l'examen de mi-session peut se voir attribuer 40 % de la note totale, tandis que 60 % sont attribués à l'examen final.

Cet article se concentre sur le calcul et l'affichage de moyennes à l'aide de pourcentages.

Configuration de votre feuille de calcul Excel

Avant de calculer une moyenne pondérée dans Excel, vous devez configurer votre feuille de calcul avec les données nécessaires. Suivez ces étapes:

1. Entrez vos valeurs de données dans une colonne (par exemple, la colonne B).
2. Attribuez des pondérations sous forme de pourcentage dans une colonne distincte (par exemple, la colonne C).
3. Vérifiez que les poids totalisent 100 %.

Ce tutoriel commence par un exemple tiré du monde du sport. Les entraîneurs évaluent leurs joueurs selon différents attributs, et certains attributs sont plus précieux que d'autres.

L'entraîneur décide du pourcentage de poids pour chaque attribut, puis marque le joueur. La moyenne pondérée donne une image plus complète de la façon dont le joueur se mesure.

Copiez ce tableau dans votre modèle Excel :

Les poids ci-dessus sont représentés en pourcentages et leur somme est de 100 %.

Formatage des cellules pour les pourcentages

Pour plus de clarté, il est essentiel de formater les cellules contenant les valeurs de poids en pourcentages. Voici les tâches étape par étape :

1. Sélectionnez les cellules de pondération qui doivent être formatées (par exemple, la colonne B).
2. Cliquez sur l'onglet "Accueil" dans la barre d'outils Excel.
3. Localisez le groupe "Nombre" et cliquez sur le bouton "Style de pourcentage" (%). Cela formatera les cellules sélectionnées en pourcentages et les affichera avec le signe %.

Une fois ces étapes terminées, votre feuille de calcul Excel devrait maintenant être configurée et prête pour le calcul de la moyenne pondérée. Assurez-vous que vos données et vos valeurs de pondération sont saisies correctement et que les cellules contenant les pondérations sont formatées en pourcentages pour une meilleure lisibilité.

Comment calculer une moyenne pondérée dans Excel

Le calcul d'une moyenne pondérée implique une série d'étapes simples utilisant les fonctions SOMMEPROD et SOMME.

Ces fonctions peuvent être combinées pour trouver une valeur moyenne finale qui tient compte de l'importance variable ou des pondérations accordées aux différentes valeurs.

Utilisation de la fonction Excel SOMMEPROD

La formule Excel SUMPRODUCT est utilisée pour multiplier deux tableaux de valeurs ou plus et renvoyer la somme de leurs produits.

Un tableau fait référence à une liste de valeurs. La plage de cellules qui contiennent les quantités est un tableau. Le tableau qui contient le prix est un deuxième tableau.

Diviser par le poids total

Après avoir utilisé la fonction SOMMEPROD pour multiplier et additionner les quantités et les prix des articles, vous devez le diviser par la somme des poids pour calculer la moyenne pondérée.

Supposons que le poids total représente la somme des quantités dans la colonne B.

En utilisant la fonction SUM, comme ceci : SUM(B2:B4), vous obtiendrez le poids total.

Ce chiffre est utilisé pour diviser le résultat du résultat SOMMEPROD.

Instructions pour Excel

Suivez ces étapes dans votre feuille de calcul Excel :

1. Cliquez sur une cellule vide, où la moyenne pondérée sera affichée.
2. Saisissez la formule suivante : =SOMMEPROD(B2:B4, C2:C4)/SOMME(B2:B4)
3. Appuyez sur Entrée'.

Une fois ces étapes terminées, Excel affichera le prix moyen pondéré dans la cellule que vous avez sélectionnée.

Si vous suivez cet exemple, vous pouvez voir un long nombre dans la cellule de résultat avec de nombreuses décimales.

À ce stade, vous devez ranger l'affichage en formatant la cellule de résultat pour afficher deux décimales.

Décomposer la formule

Voici une ventilation de la formule de la section précédente :

• SUMPRODUCT(B2:B4, C2:C4) obtient la somme des produits des quantités d'articles multipliée par leurs prix respectifs
• SUM(B2:B4) calcule le poids total (somme des quantités d'articles)
• SUMPRODUCT(B2:B4, C2:C4)/SUM(B2:B4) divise la somme des produits par le poids total, fournissant la moyenne pondérée

Scénarios du monde réel

Deux applications courantes de cette formule comprennent les systèmes de notation et l'analyse financière.

Ces exemples montrent comment appliquer des moyennes pondérées dans Excel en utilisant des pourcentages comme poids.

Systèmes de notation

Lorsque les enseignants et les professeurs évaluent les performances des étudiants dans un cours, ils utilisent souvent des moyennes pondérées pour déterminer la note finale. Dans ce cas, chaque devoir, quiz ou examen a une pondération ou un pourcentage spécifique contribuant à la note globale.

Par exemple, supposons la répartition des notes suivante :

• Missions : 40 % (total de 4, chacune valant 10 %)
• Quiz : 20% (total de 2, chacun valant 10%)
• Examen de mi-session : 20 %
• Examen final : 20 %

Pour suivre l'exemple, vous pouvez copier les données de ce tableau dans votre feuille de calcul. Placez la première colonne du tableau dans la colonne A.

Pour calculer la moyenne pondérée dans Excel, vous pouvez utiliser la fonction SOMMEPROD.

En supposant que les notes se trouvent dans la colonne B (de B2 à B9) et que les poids correspondants exprimés en pourcentage se trouvent dans la colonne C (de C2 à C9), la formule ressemblera à ceci :

=SOMMEPROD(B2:B9, C2:C9)/SOMME(C2:C9)

Cette formule Excel calcule la moyenne pondérée à l'aide des notes et des pourcentages fournis.

Analyse financière

Les moyennes pondérées jouent également un rôle essentiel dans l'analyse financière, en particulier lors de l'évaluation de la performance d'un portefeuille d'actions . Dans de tels cas, le poids accordé à chaque action peut être basé sur le pourcentage d'investissement alloué.

Supposons que vous déteniez les actions suivantes dans votre portefeuille :

En supposant que les cours des actions se trouvent dans la colonne B (de B2 à B4), les actions dans la colonne C (de C2 à C4) et les pourcentages d'investissement dans la colonne D (de D2 à D4), la formule moyenne pondérée sera la suivante :

=SOMMEPROD(B2:B4, D2:D4)/SOMME(D2:D4)

La formule ci-dessus calcule le cours moyen pondéré des actions en fonction du pourcentage d'investissement pour chaque action. Cette valeur aide à évaluer la performance globale du portefeuille.

Cette capture d'écran montre le calcul et les résultats :

Vous pouvez rendre vos présentations encore meilleures en codant par couleur les valeurs qui se situent au-dessus ou au-dessous de la moyenne pondérée. Par exemple, des valeurs plus élevées peuvent être surlignées en vert.

Pour ce faire, utilisez la mise en forme conditionnelle pour ajouter une règle de "cellule en surbrillance" avec la condition "supérieur à". Cela vous permet de spécifier le seuil et de choisir la couleur de surbrillance.

L'analyse des prix des actions combine souvent la puissance des moyennes pondérées avec la fréquence des cours de clôture dans certaines fourchettes de prix. Les analystes attribuent des pondérations à différentes fourchettes de prix et utilisent Excel pour créer un tableau de fréquence qui compte les fois où le cours d'une action est tombé dans une fourchette de prix sur trente jours.

Cela permet le calcul des cours de clôture quotidiens moyens pondérés en fonction de la fréquence. Cette vidéo utile vous montre comment créer des tableaux de fréquences dans Excel :

4 erreurs courantes et dépannage

Voici les quatre erreurs les plus courantes que vous rencontrerez lors du calcul d'une moyenne pondérée dans Excel.

1. Données et pondérations mal alignées

Des données et des pondérations mal alignées peuvent également entraîner des résultats moyens pondérés inexacts. La précision dépend de l'alignement correct des données et des pondérations dans Excel.

Les données doivent être répertoriées dans des colonnes adjacentes, chaque valeur correspondant à son poids respectif :

Si vous remarquez que les données et les pondérations ne sont pas alignées, ajustez les colonnes de votre feuille de calcul pour garantir une correspondance correcte entre les valeurs et leurs pondérations correspondantes.

2. Ne pas tenir compte des pourcentages

Lorsque vous utilisez des pourcentages en tant que poids, assurez-vous que les poids sont exprimés en valeurs décimales. Par exemple, si un poids est représenté par 20 %, l'équivalent décimal doit être 0,20.

Pour convertir un pourcentage en nombre décimal, divisez le pourcentage par 100 :

=C2 / 100

En tenant compte correctement des pourcentages, le calcul de la moyenne pondérée donnera des résultats précis.

3. Références de cellules incorrectes

Il est essentiel de s'assurer que les références des cellules sont exactes. Des références de cellule incorrectes peuvent entraîner des erreurs ou des résultats inexacts dans le calcul.

Vérifiez que les formules incluent les plages de cellules appropriées pour les valeurs et les pondérations.

4. Utiliser la mauvaise fonction

Vous avez probablement utilisé la fonction de moyenne normale d'Excel à plusieurs reprises. Cette fonction n'est pas utilisée pour les moyennes pondérées.

L'écart type et la moyenne mobile sont des moyens de saisir les changements dans les moyennes des données. Ces calculs utilisent différentes formules.

La fonction AVERAGEIF vous permet de filtrer les valeurs qui ne répondent pas à des critères spécifiques. Cependant, il est préférable d'attribuer aux valeurs un faible pourcentage lors du calcul d'une moyenne pondérée.

Notre dernier mot

Dans cet article, nous avons démontré le processus de calcul des moyennes pondérées dans Excel avec des pourcentages. Les moyennes pondérées fournissent des informations précieuses sur les ensembles de données, soulignant l'importance relative d'un certain nombre de valeurs.

À l'aide de la fonction SOMMEPROD, Excel peut facilement calculer la moyenne pondérée en multipliant les points de données correspondants et en divisant le résultat par la somme totale des poids. La formule est la suivante :

=SUMPRODUCT(data_range, weight_range) / SOMME(weight_range)

Cette méthode est particulièrement utile lorsqu'il s'agit de notes, de résultats d'enquête ou de données financières, où certaines valeurs ont plus d'importance que d'autres.

La maîtrise de cette technique peut améliorer les capacités d'analyse des données, permettant aux utilisateurs de prendre des décisions plus éclairées sur la base de données précises et pertinentes.

Prêt à commencer à acquérir de solides compétences en Excel ? Découvrez notre