KutoolsforOffice — Une solution unique, cinq outils puissants.Faire plus avec moins d'efforts.Soldes de mars : 20 % de réduction

Comment calculer une moyenne pondérée dans un tableau croisé dynamique Excel ?

AuteurKelly Date de modification

Le calcul de la moyenne pondérée pour des données dans Excel est une exigence courante, notamment lorsque vos points de données contribuent de manière inégale au résultat final. Pour des plages simples, les fonctions SOMMEPROD et SOMME offrent une solution rapide. Toutefois, lorsqu’on travaille avec des tableaux croisés dynamiques, on constate souvent que les champs calculés ne prennent pas nativement en charge ces fonctions. Cela peut compliquer les choses si vous souhaitez calculer directement des moyennes pondérées dans un tableau croisé dynamique. Comprendre ces limites et découvrir des approches alternatives vous permet de résumer efficacement vos données dans divers scénarios. Cet article explore différentes méthodes pour calculer une moyenne pondérée dans un tableau croisé dynamique, couvrant à la fois des solutions classiques et des fonctionnalités plus récentes disponibles dans Excel.

Calculer la moyenne pondérée dans un Tableau croisé dynamique Excel
Code VBA – Automatiser le calcul de la moyenne pondérée dans un Tableau croisé dynamique
Power Pivot (modèle de données) – Utiliser DAX pour calculer la moyenne pondérée dans un Tableau croisé dynamique

Calculer la moyenne pondérée dans un Tableau croisé dynamique Excel

Imaginons que vous disposiez d’un tableau présentant les données de ventes de divers fruits, avec des colonnes telles que Fruit, Weight et Price per unit, et que vous ayez créé un tableau croisé dynamique résumant ces valeurs, comme illustré ci-dessous.
une capture d’écran des données d’origine et du tableau croisé dynamique correspondant

Lorsque vous devez calculer le prix moyen pondéré pour chaque fruit — c’est-à-dire refléter précisément la contribution de chaque point de données en fonction de son poids — le tableau croisé dynamique ne permet pas d’utiliser directement la fonction SOMMEPROD ni d’autres fonctions avancées similaires dans un champ calculé. La solution suivante contourne cette limitation : ajoutez une colonne auxiliaire à vos données sources, puis laissez le tableau croisé dynamique calculer automatiquement la moyenne pondérée grâce à ses options intégrées.

1. Commencez par ajouter une colonne auxiliaire intitulée Amount dans vos données sources.
Insérez une nouvelle colonne vide, donnez-lui le titre Amount, puis, dans la première cellule (par exemple, C2), saisissez la formule =D2*E2(où)D2 correspond au poids et E2 au prix unitaire — adaptez selon vos en-têtes). Ensuite, faites glisser la poignée de recopie vers le bas pour appliquer la formule à toutes les lignes. Cette étape multiplie le poids de chaque article par son prix afin d’obtenir le prix total pondéré pour cet article. Voir la capture d’écran :
une capture d’écran de l’utilisation d’une formule pour calculer le montant

Conseils :
- Assurez-vous que votre tableau source ne contient aucune cellule fusionnée, ce qui pourrait provoquer des erreurs de formule.
- Si vous travaillez avec de grands jeux de données, vérifiez bien que la formule est appliquée à toutes les lignes concernées.
- Si les affectations de colonnes changent, mettez à jour la formule en conséquence.

2. Ensuite, mettez à jour votre tableau croisé dynamique pour qu’il intègre la colonne auxiliaire ajoutée. Sélectionnez n’importe quelle cellule du tableau croisé dynamique afin d’afficher l’onglet contextuel Outils de tableau croisé dynamique. Cliquez sur Analyser(ou)Options, selon votre version d’Excel) > Actualiser. Cette étape garantit que le nouveau champ Amount apparaît bien dans la liste des champs du tableau croisé dynamique.
une capture d’écran de la mise à jour du tableau croisé dynamique

3. Pour ajouter un champ calculé de moyenne pondérée, accédez à Analyser > Champs, éléments et jeux > Champ calculé. La boîte de dialogue Insérer un champ calculé s’ouvre alors, vous permettant de définir votre calcul personnalisé.

une capture d’écran de l’activation de la boîte de dialogue Champ calculé

Remarque : Le champ calculé utilise des champs déjà définis dans vos données. Assurez-vous que toutes les colonnes nécessaires ont été ajoutées et mises à jour avant cette étape.

4. Dans la boîte de dialogue Insérer un champ calculé, saisissez Moyenne pondérée (ou un autre nom distinctif) dans le champ Nom. Pour le champ Formule, entrez =Amount/Weight. Veillez à utiliser les noms exacts de vos données sources — ceux-ci sont sensibles à la casse et doivent correspondre précisément. Cliquez ensuite sur OK pour ajouter le champ de pondération calculé.
une capture d’écran de la configuration de la boîte de dialogue Insérer un champ calculé

Dépannage :
- Si vous voyez des erreurs #DIV/0!, vérifiez que vos valeurs de poids ne contiennent aucun zéro.
- Si le champ calculé n’apparaît pas, assurez-vous que l’orthographe et la casse du nom de la condition sont correctes.

Le prix moyen pondéré pour chaque type de fruit s’affiche désormais dans les lignes de sous-total de votre Tableau croisé dynamique, garantissant ainsi que le calcul du prix moyen reflète fidèlement l’impact du poids de chaque entrée.
une capture d’écran affichant la moyenne pondérée dans le tableau croisé dynamique

Avantages : Compatible avec les anciennes versions d’Excel ; aucune extension ni fonctionnalité avancée requise.
Inconvénients : Nécessite la modification des données sources via des colonnes auxiliaires ; le recalcul peut être moins dynamique lorsque les données sont mises à jour.
Conseil pratique : Pour les rapports récurrents, envisagez de rendre la formule de la colonne auxiliaire dynamique ou d’automatiser l’actualisation à l’aide d’une macro.

Power Pivot (modèle de données) – Utiliser DAX pour calculer la moyenne pondérée dans un Tableau croisé dynamique

Avec les versions récentes d’Excel, le complément Power Pivot (également appelé modèle de données) déverrouille de nouvelles options de calcul grâce aux formules DAX (Analyse des données Expressions). Cela vous permet de calculer directement des moyennes pondérées dans le Tableau croisé dynamique sans créer de colonnes auxiliaires supplémentaires dans vos données sources.

Scénarios applicables : Idéal pour les grands jeux de données ou les tables liées, ainsi que lorsque vous souhaitez que vos calculs s’actualisent automatiquement à mesure que vos données évoluent. Cette approche est particulièrement efficace pour les analyses métier et les tableaux de bord, où il est essentiel de conserver un tableau source propre.

Instructions :

  1. Activer le complément Power Pivot
    Accédez à Fichier > Options > Compléments. Dans la liste déroulante Gérer, sélectionnez Compléments COM, cliquez sur Atteindre, puis cochez la case Power Pivot.
  2. Ajouter des données à Power Pivot
    Sélectionnez votre tableau dans la feuille de calcul, puis cliquez sur Power Pivot>Gérerpour ouvrir la fenêtre Power Pivot.
    une capture d’écran de l’ajout de données à Power Pivot
  3. Créer un tableau croisé dynamique à partir de Power Pivot
    Dans la fenêtre Power Pivot, accédez à Accueil>Tableau croisé dynamique.
    une capture d’écran de la création d’un tableau croisé dynamique à partir de Power Pivot
    Choisissez ensuite l’emplacement d’insertion (par exemple,)Feuille de calcul existante) et cliquez sur OK.
    une capture d’écran de la spécification de l’emplacement du tableau croisé dynamique
  4. Construire le tableau croisé dynamique et ajouter une mesure
    Dans la liste des champs de votre nouveau tableau croisé dynamique, faites glisser les champs vers les zones correspondantes. Cliquez ensuite avec le bouton droit sur le nom du tableau, puis sélectionnez Ajouter une mesure.
    une capture d’écran de la construction du tableau croisé dynamique et de l’ajout d’une mesure
  5. Définir la mesure
    Dans la boîte de dialogue Mesure:
    1. Donnez un nom à la mesure (par exemple, Prix moyen pondéré).
    2. Saisissez l’expression DAX suivante pour la moyenne pondérée.
      =SUMX(Table1, Table1[Weight] * Table1[Price]) / SUM(Table1[Weight])
      (Remplacez)Table1, [Weight] et [Price] par le nom réel de votre tableau et vos Nom de la condition.)
    3. Cliquez sur OKpour l’ajouter.
      une capture d’écran de la définition de la mesure
  6. Utiliser la mesure dans le tableau croisé dynamique
    La mesure nouvellement ajoutée apparaît dans la liste des champs et peut être glissée dans la zone Valeurscomme n’importe quel autre champ.
    une capture d’écran affichant la moyenne pondérée dans le tableau croisé dynamique 2

Conseils et dépannage :
– Les formules DAX ne tiennent pas compte de la casse, mais les noms de champs et de tables doivent correspondre exactement à ceux de votre modèle.
– Dès que vous modifiez les données sous-jacentes, la mesure est automatiquement actualisée dans votre tableau croisé dynamique.
– Si vos résultats sont vides ou inattendus, vérifiez la présence de valeurs de poids nulles ou manquantes, et assurez-vous que votre modèle de données s’actualise correctement.

Avantages : Aucune modification n’est nécessaire dans les données sources ; les calculs se mettent à jour instantanément dès que les données changent et permettent des agrégations avancées.
Inconvénients : Power Pivot n’est pas disponible dans toutes les éditions d’Excel et peut nécessiter une configuration initiale ; les utilisateurs peu familiers avec DAX peuvent faire face à une courbe d’apprentissage.

une capture d’écran de kutools for excel ia

Libérez la magie d’Excel avec Kutools IA

  • Exécution intelligente : effectuez des opérations sur les cellules, analysez des données et créez des graphiques, le tout à partir de commandes simples.
  • formules personnalisées : créez des formules sur mesure pour rationaliser vos flux de travail.
  • Programmation VBA : Écrivez et implémentez du code VBA en toute simplicité.
  • Interprétation de formules : Décryptez facilement les formules complexes.
  • Traduction de texte : Franchissez les barrières linguistiques directement depuis vos feuilles de calcul.
Améliorez vos capacités Excel grâce à des outils alimentés par l’intelligence artificielle.Téléchargez dès maintenantet découvrez une efficacité inégalée !

Articles associés :

Meilleurs outils de productivité Office

🤖Kutools IA Aide: Révolutionnez Analyse des données grâce à :Exécution intelligente   |  Générez du code|  Créez formules personnalisées  |  Analysez des données et générez des graphiques|  Appelez Fonctions améliorées…
Fonctionnalités populaires:Rechercher, mettre en surbrillance ou Marquer les doublons   |  Supprimer les lignes vides   |  Combinez les colonnes ou cellules sans perdre de données   |   Arrondi sans utiliser de formule...
Super RECHERCHEV:RechercheValeurs avec critères multiples  |  RechercheValeurs avec valeurs multiples  |   RechercheValeurs dans plusieurs feuilles   |   Correspondance floue....
Liste déroulante avancée:Créez rapidement une liste déroulante   |  Liste déroulante dépendante   |  Liste déroulante à sélection multiple....
Gestionnaire de colonnes:Ajouter un nombre précis de colonnes|Déplacer des colonnes|Basculer la visibilité des colonnes masquées|Comparer des plages et des colonnes...
Fonctionnalités vedettes:Mise au point de la grille   |  Vue de conception   |Barre de formule améliorée   | Gestionnaire de classeurs et de feuilles   |  Bibliothèque de ressources(Texte automatique)|  Sélecteur de date   |  Consolider les feuilles de calcul  |  Chiffrer/Déchiffrer les cellules   | Envoyer des e-mails par liste   |  Super Filtre   |   Filtre spécial(Filtrer les cellules avec une police en gras/italique/barré...) ...
… et bien plus encore… et plus encore:(,)Supprimer des caractères spécifiques, ...)|   50+Typesde graphiques(, ...)|   40+ Formules pratiques(Calculer l'âge en fonction de la date de naissance, ...)|   19 Outils d’insertion(,Insérer une image depuis un chemin , ...)|   de conversion (Convertir en mots,Conversion de devises, ...)|Fusionner et scinder   (Fusion avancée des lignes,Diviser les cellules , ...)|, ...)   |
Utilisez Kutools dans la langue de votre choix – disponible en anglais, espagnol, allemand, français, chinois et 40+ autres langues !

Boostez vos compétences Excel avec Kutools pour Excel et découvrez une efficacité inégalée.Kutools pour Excel propose plus de 300 fonctionnalités avancées pour améliorer votre productivité et Gagner du temps.Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...

Office Tab apporte une interface à onglets à Office et rend votre travail bien plus facile

  • Activez l’édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50 % et vous fait économiser des centaines de clics de souris chaque jour !

Tous les compléments Kutools. Un seul installateur

Kutools for Office regroupe les compléments pour Excel, Word, Outlook et PowerPoint, ainsi que Office Tab Pro, ce qui en fait le choix idéal pour les équipes travaillant à travers les applications Office.

ExcelWordOutlookTabsPowerPoint
  • Suite tout-en-un— Compléments Excel, Word, Outlook et PowerPoint + Office Tab Pro
  • Un seul installateur, une seule licence— installation en quelques minutes (compatible MSI)
  • Fonctionne mieux ensemble— productivité optimisée dans toutes les applications Office
  • Essai gratuit de 30 jours avec toutes les fonctionnalités— aucune inscription, aucune carte bancaire
  • Meilleur rapport qualité-prix— économisez par rapport à l’achat de compléments individuels