Comment ajouter et utiliser un champ calculé de tableau croisé dynamique Excel

Souvent, une fois que vous avez créé un tableau croisé dynamique, vous avez besoin d'étendre votre analyse et d'y inclure davantage de données/calculs.

Si vous avez besoin d'un nouveau point de données pouvant être obtenu en utilisant des points de données existants dans le tableau croisé dynamique, vous n'avez pas besoin de revenir en arrière et de l'ajouter dans les données source. Au lieu de cela, vous pouvez utiliser un Champ calculé du tableau croisé dynamique pour faire ça.

Téléchargez le jeu de données et suivez-le.

Qu'est-ce qu'un champ calculé de tableau croisé dynamique ?

Commençons par un exemple de base de tableau croisé dynamique.

Supposons que vous ayez un ensemble de données de détaillants et que vous créiez un tableau croisé dynamique comme indiqué ci-dessous :

Le tableau croisé dynamique ci-dessus résume les valeurs des ventes et des bénéfices des détaillants.

Maintenant, que se passe-t-il si vous voulez également savoir quelle était la marge bénéficiaire de ces détaillants (où la marge bénéficiaire est « Profit » divisé par « Ventes »).

Il y a plusieurs façons de faire ça:

1. Revenez à l'ensemble de données d'origine et ajoutez ce nouveau point de données. Ainsi, vous pouvez insérer une nouvelle colonne dans les données source et calculer la marge bénéficiaire qu'elle contient. Une fois cela fait, vous devez mettre à jour les données source du tableau croisé dynamique pour que cette nouvelle colonne en fasse partie.
   • Bien que cette méthode soit une possibilité, vous devrez revenir manuellement à l'ensemble de données et effectuer les calculs. Par exemple, vous devrez peut-être ajouter une autre colonne pour calculer la vente moyenne par unité (Ventes/Quantité). Encore une fois, vous devrez ajouter cette colonne à vos données source, puis mettre à jour le tableau croisé dynamique.
   • Cette méthode gonfle également votre tableau croisé dynamique lorsque vous y ajoutez de nouvelles données.
2. Ajoutez des calculs en dehors du tableau croisé dynamique. Cela peut être une option s'il est peu probable que la structure de votre tableau croisé dynamique change. Mais si vous modifiez le tableau croisé dynamique, le calcul peut ne pas être mis à jour en conséquence et peut vous donner des résultats erronés ou des erreurs. Comme indiqué ci-dessous, j'ai calculé la marge bénéficiaire lorsqu'il y avait des détaillants dans la rangée. Mais quand je l'ai changé de clients en régions, la formule a donné une erreur.
3. Utilisation d'un champ calculé de tableau croisé dynamique. C'est le moyen le plus efficace pour utiliser les données existantes du tableau croisé dynamique et calculer la métrique souhaitée. Considérez Champ calculé comme une colonne virtuelle que vous avez ajoutée à l'aide des colonnes existantes du tableau croisé dynamique. Il y a beaucoup d'avantages à utiliser un champ calculé de tableau croisé dynamique (comme nous le verrons dans une minute) :
   • Il ne vous oblige pas à gérer des formules ou à mettre à jour les données sources.
   • Il est évolutif car il tiendra automatiquement compte de toutes les nouvelles données que vous pourrez ajouter à votre tableau croisé dynamique. Une fois que vous avez ajouté un champ de calcul, vous pouvez l'utiliser comme n'importe quel autre champ de votre tableau croisé dynamique.
   • Il est facile à mettre à jour et à gérer. Par exemple, si les métriques changent ou si vous devez modifier le calcul, vous pouvez facilement le faire à partir du tableau croisé dynamique lui-même.

Ajout d'un champ calculé au tableau croisé dynamique

Voyons comment ajouter un champ calculé de tableau croisé dynamique dans un tableau croisé dynamique existant.

Supposons que vous ayez un tableau croisé dynamique comme indiqué ci-dessous et que vous souhaitiez calculer la marge bénéficiaire pour chaque détaillant :

Voici les étapes pour ajouter un champ calculé de tableau croisé dynamique :

• Sélectionnez n'importe quelle cellule du tableau croisé dynamique.
• Accédez à Outils de tableau croisé dynamique -> Analyser -> Calculs -> Champs, éléments et ensembles.
• Dans la liste déroulante, sélectionnez Champ calculé.
• Dans la boîte de dialogue Insérer un fichier calculé :
  • Donnez-lui un nom en le saisissant dans le champ Nom.
  • Dans le champ Formule, créez la formule souhaitée pour le champ calculé. Notez que vous pouvez choisir parmi les noms de champs répertoriés en dessous. Dans ce cas, la formule est « = Profit/Ventes ». Vous pouvez soit saisir manuellement les noms de champ, soit double-cliquer sur le nom de champ répertorié dans la zone Champs.
• Cliquez sur Ajouter et fermez la boîte de dialogue.

Dès que vous ajoutez le champ calculé, il apparaîtra comme l'un des champs dans la liste des champs de tableau croisé dynamique.

Vous pouvez désormais utiliser ce champ calculé comme n'importe quel autre champ de tableau croisé dynamique (notez que vous ne pouvez pas utiliser le champ calculé de tableau croisé dynamique comme filtre de rapport ou segment).

Comme je l'ai mentionné précédemment, l'avantage d'utiliser un champ calculé de tableau croisé dynamique est que vous pouvez modifier la structure du tableau croisé dynamique et qu'il s'ajustera automatiquement.

Par exemple, si je fais glisser et dépose la région dans la zone des lignes, vous obtiendrez le résultat comme indiqué ci-dessous, où la valeur de la marge bénéficiaire est signalée pour les détaillants ainsi que la région.

Dans l'exemple ci-dessus, j'ai utilisé une formule simple (=Profit/Sales) pour insérer un champ calculé. Cependant, vous pouvez également utiliser certaines formules avancées.

Avant de vous montrer un exemple d'utilisation d'une formule avancée pour créer un champ de calcul de tableau croisé dynamique, voici certaines choses que vous devez savoir :

• Vous NE POUVEZ PAS utiliser des références ou des plages nommées lors de la création d'un champ calculé de tableau croisé dynamique. Cela exclurait de nombreuses formules telles que RECHERCHEV, INDEX, DÉCALAGE, etc. Cependant, vous pouvez utiliser des formules qui peuvent fonctionner sans références (telles SUM, IF, COUNT, etc.).
• Vous pouvez utiliser une constante dans la formule. Par exemple, si vous voulez connaître les ventes prévues où il est prévu qu'elles augmentent de 10 %, vous pouvez utiliser la formule =Sales*1.1 (où 1.1 est constant).
• L'ordre de priorité est suivi dans la formule qui crée le champ calculé. En tant que meilleure pratique, utilisez des parenthèses pour vous assurer de ne pas avoir à vous souvenir de l'ordre de priorité.

Voyons maintenant un exemple d'utilisation d'une formule avancée pour créer un champ calculé.

Supposons que vous ayez l'ensemble de données comme indiqué ci-dessous et que vous deviez afficher la valeur des ventes prévue dans le tableau croisé dynamique.

Pour la valeur prévue, vous devez utiliser une augmentation des ventes de 5 % pour les grands détaillants (ventes supérieures à 3 millions) et une augmentation des ventes de 10 % pour les petits et moyens détaillants (ventes inférieures à 3 millions).

Remarque : Les chiffres de vente ici sont faux et ont été utilisés pour illustrer les exemples de ce didacticiel.

Voici comment procéder :

• Sélectionnez n'importe quelle cellule du tableau croisé dynamique.
• Accédez à Outils de tableau croisé dynamique -> Analyser -> Calculs -> Champs, éléments et ensembles.
• Dans la liste déroulante, sélectionnez Champ calculé.
• Dans la boîte de dialogue Insérer un fichier calculé :
  • Donnez-lui un nom en le saisissant dans le champ Nom.
  • Dans le champ Formule, utilisez la formule suivante : =IF(Region =”South”,Sales *1.05,Sales *1.1)
• Cliquez sur Ajouter et fermez la boîte de dialogue.

Cela ajoute une nouvelle colonne au tableau croisé dynamique avec la valeur de prévision des ventes.

Cliquez ici pour télécharger l'ensemble de données.

Un problème avec les champs calculés du tableau croisé dynamique

Le champ calculé est une fonctionnalité étonnante qui améliore vraiment la valeur de votre tableau croisé dynamique avec des calculs de champ, tout en gardant tout évolutif et gérable.

Il existe cependant un problème avec les champs calculés du tableau croisé dynamique que vous devez connaître avant de l'utiliser.

Supposons que j'ai un tableau croisé dynamique comme indiqué ci-dessous où j'ai utilisé le champ calculé pour obtenir les chiffres de vente prévus.

Notez que les totaux partiels et généraux ne sont pas corrects.

Bien que ceux-ci devraient ajouter la valeur de prévision des ventes individuelles pour chaque détaillant, en réalité, il suit la même formule de champ calculé que nous avons créée.

Ainsi, pour South Total, alors que la valeur devrait être de 22 824 000, le South Total la rapporte à tort comme 22 287 000. Cela se produit car il utilise la formule 21 225 800 * 1,05 pour obtenir la valeur.

Malheureusement, il n'y a aucun moyen de corriger cela.

La meilleure façon de gérer cela serait de supprimer les sous-totaux et les totaux généraux de votre tableau croisé dynamique.

Vous pouvez également utiliser des solutions de contournement innovantes que Debra a montrées pour gérer ce problème.

Comment modifier ou supprimer un champ calculé de tableau croisé dynamique ?

Une fois que vous avez créé un champ calculé de tableau croisé dynamique, vous pouvez modifier la formule ou la supprimer en procédant comme suit :

• Sélectionnez n'importe quelle cellule du tableau croisé dynamique.
• Accédez à Outils de tableau croisé dynamique -> Analyser -> Calculs -> Champs, éléments et ensembles.
• Dans la liste déroulante, sélectionnez Champ calculé.
• Dans le champ Nom, cliquez sur la flèche déroulante (petite flèche vers le bas à la fin du champ).
• Dans la liste, sélectionnez le champ calculé que vous souhaitez supprimer ou modifier.
• Modifiez la formule au cas où vous voudriez la modifier ou cliquez sur Supprimer au cas où vous voudriez la supprimer.

Comment obtenir une liste de toutes les formules de champ calculé ?

Si vous créez beaucoup de champs calculés de tableau croisé dynamique, ne vous inquiétez pas de garder une trace de la formule utilisée dans chacun d'entre eux.

Excel vous permet de créer rapidement une liste de toutes les formules utilisées dans la création de champs calculés.

Voici les étapes pour obtenir rapidement la liste des formules de tous les champs calculés :

• Sélectionnez n'importe quelle cellule du tableau croisé dynamique.
• Accédez à Outils de tableau croisé dynamique -> Analyser -> Champs, éléments et ensembles -> Formules de liste.

Dès que vous cliquez sur Liste des formules, Excel insère automatiquement une nouvelle feuille de calcul contenant les détails de tous les champs/éléments calculés que vous avez utilisés dans le tableau croisé dynamique.

Cela peut être un outil très utile si vous devez envoyer votre travail au client ou le partager avec votre équipe.

Vous pouvez également trouver les didacticiels de tableau croisé dynamique suivants utiles :

• Préparation des données source pour le tableau croisé dynamique.
• Utilisation des trancheurs dans le tableau croisé dynamique d'Excel : Guide du débutant.
• Comment regrouper des dates dans des tableaux croisés dynamiques dans Excel.
• Comment regrouper des nombres dans un tableau croisé dynamique dans Excel.
• Comment filtrer les données dans un tableau croisé dynamique dans Excel.
• Comment remplacer les cellules vides par des zéros dans les tableaux croisés dynamiques Excel
• Comment appliquer une mise en forme conditionnelle dans un tableau croisé dynamique dans Excel
• Cache Pivot dans Excel - Qu'est-ce que c'est et comment l'utiliser au mieux ?