Comment ajouter et utiliser des champs calculés de tableau croisé dynamique Excel

En règle générale, une fois que vous avez créé un tableau croisé dynamique, vous devez étendre votre analyse et y inclure davantage de données/calculs.

Si vous avez besoin d'un nouveau point de données pouvant être obtenu à l'aide d'un point de données existant dans le tableau croisé dynamique, il n'est pas nécessaire de revenir en arrière et de l'ajouter aux données source.Au lieu de cela, vous pouvez utiliserChamps calculés de tableau croisé dynamiquepour faire ça.

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

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

Supposons que vous disposiez d'un ensemble de données de détaillants et que vous créiez un tableau croisé dynamique comme celui-ci :

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

Maintenant, que se passerait-il si vous vouliez également savoir quelles étaient les marges bénéficiaires de ces détaillants (la marge bénéficiaire est le "bénéfice" divisé par les "ventes") ?

Il y a plusieurs moyens de le faire:

1. Revenez au jeu de données d'origine et ajoutez ce nouveau point de données.Vous pouvez donc insérer une nouvelle colonne dans les données source et y calculer la marge bénéficiaire.Une fois cela fait, vous devez mettre à jour les données source du tableau croisé dynamique pour obtenir cette nouvelle colonne dans le cadre de celui-ci.
   • Bien que cette approche fonctionne, vous devez revenir manuellement à l'ensemble de données et effectuer les calculs.Par exemple, vous pouvez ajouter une autre colonne pour calculer les ventes moyennes par unité (ventes/quantité).Encore une fois, vous devez ajouter cette colonne aux 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 des tableaux croisés dynamiques.Choisissez cette option s'il est peu probable que la structure de votre tableau croisé dynamique change.Cependant, si vous modifiez le tableau croisé dynamique, les calculs peuvent ne pas être mis à jour en conséquence et peuvent vous donner des résultats incorrects ou des erreurs.Comme indiqué ci-dessous, j'ai calculé la marge bénéficiaire lorsqu'il y a des détaillants dans la rangée.Mais quand je change de client en zone, la formule se trompe.
3. Calculez des champs à l'aide de tableaux croisés dynamiques.c'estUtilisez les données de tableau croisé dynamique existantes et calculez lemanière la plus efficace.Considérez les champs calculés comme des colonnes virtuelles que vous ajoutez à l'aide de colonnes existantes dans votre tableau croisé dynamique.L'utilisation des champs calculés du tableau croisé dynamique présente de nombreux avantages (comme nous le verrons plus tard) :
   • Il ne vous oblige pas à travailler avec des formules ou à mettre à jour les données source.
   • Il est extensible car il prend automatiquement en compte toutes les nouvelles données que vous pourriez ajouter au tableau croisé dynamique.Après avoir ajouté un champ calculé, vous pouvez l'utiliser comme n'importe quel autre champ dans un tableau croisé dynamique.
   • Il est facile à mettre à jour et à gérer.Par exemple, si une métrique change ou si vous devez modifier un calcul, vous pouvez facilement le faire à partir du tableau croisé dynamique lui-même.

Ajouter des champs calculés au tableau croisé dynamique

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

Supposons que vous ayez un tableau croisé dynamique qui ressemble à ceci et que vous souhaitiez calculer la marge bénéficiaire de chaque détaillant :

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

Après avoir ajouté le champ calculé, il apparaîtra comme l'un des champs de la liste 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 un champ calculé de tableau croisé dynamique comme filtre ou segment de rapport).

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 glisse et dépose la région dans la zone de ligne, vous obtiendrez des résultats comme celui-ci, où les valeurs de marge bénéficiaire pour le détaillant et la région sont rapportées.

 

Dans l'exemple ci-dessus, j'ai utilisé une formule simple (= Profit/Ventes) pour insérer le 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 calculé de tableau croisé dynamique, voici quelques éléments que vous devez savoir :

• Vous ne pouvez pas utiliser de références ou de plages nommées lors de la création de champs calculés de tableau croisé dynamique.Cela exclura de nombreuses formules telles que VLOOKUP, INDEX, OFFSET, etc.Cependant, vous pouvez utiliser des formules qui fonctionnent sans références (par exemple SUM, IF, COUNT, etc...).
• Vous pouvez utiliser des constantes dans les formules.Par exemple, si vous vouliez connaître la croissance prévue des ventes qui devrait augmenter de 10 %, vous utiliseriez la formule = Ventes * 1.1 (où 1.1 est une constante).
• Suivez l'ordre de priorité dans les formules qui génèrent des champs calculés.En tant que meilleure pratique, utilisez des parenthèses pour vous assurer que vous n'avez pas à vous souvenir de la priorité.

Examinons maintenant un exemple de création d'un champ calculé à l'aide d'une formule avancée.

Supposons que vous disposiez de l'ensemble de données ci-dessous et que vous deviez afficher les ventes prévues dans un tableau croisé dynamique.

Pour les valeurs prévisionnelles, vous utiliseriez une augmentation des ventes de 300 % pour les grands détaillants (plus de 5 millions de ventes) et une augmentation des ventes de 300 % pour les petits et moyens détaillants (moins de 10 millions de ventes).

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

Voici comment procéder :

Cela ajoutera une nouvelle colonne au tableau croisé dynamique avec les valeurs de prévision des ventes.

Problème de champ calculé de tableau croisé dynamique

Les champs calculés sont une fonctionnalité étonnante qui augmente vraiment la valeur des tableaux croisés dynamiques avec des calculs de champs, tout en gardant tout évolutif et gérable.

Cependant, il existe un problème avec les champs calculés de tableau croisé dynamique que vous devez comprendre avant de les utiliser.

Supposons que j'ai un tableau croisé dynamique comme ci-dessous et que j'utilise un champ calculé pour obtenir les chiffres de vente prévus.

Notez que les sous-totaux et les totaux sont incorrects.

Bien que ceux-ci devraient ajouter des valeurs de prévision des ventes distinctes pour chaque détaillant, en réalité, cela 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 22,287,000 XNUMX, South Total la signale à tort comme XNUMX XNUMX XNUMX.Cela se produit car il utilise la formule 21,225,800 1.05 XNUMX * XNUMX pour obtenir la valeur.

Malheureusement, vous ne pouvez pas corriger ce problème.

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

Vous pouvez également passer Présenté par DebraQuelques solutions de contournement innovantes pour faire face à ce problème.

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

Après avoir créé un champ calculé de tableau croisé dynamique, vous pouvez modifier la formule ou la supprimer en procédant comme suit :

Comment obtenir une liste de toutes les formules de champs calculés ?

Si vous créez un grand nombre de champs calculés de tableau croisé dynamique, vous n'avez pas à vous soucier du suivi des formules utilisées dans chaque champ.

Excel vous permet de créer rapidement une liste de toutes les formules utilisées pour créer des champs calculés.

Voici les étapes pour obtenir rapidement une liste de toutes les formules de champs calculés :

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

Une fois que vous avez cliqué sur la formule de liste, Excel insère automatiquement une nouvelle feuille de calcul avec 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 du travail à un client ou le partager avec votre équipe.