Excel : Comment modifier un champ calculé dans un tableau croisé dynamique

Avez-vous déjà créé un tableau croisé dynamique dans votre classeur Excel et vous souhaitez apprendre à ajouter des champs calculés ? Le processus n’est pas assez difficile mais ce n'est pas toujours intuitif. Dans ce tutoriel, vous allez apprendre à ajouter, modifier ou supprimer un champ calculé dans un tableau croisé dynamique dans Excel.

Lorsque vous créez un tableau croisé dynamique, vous n’avez pas la possibilité de modifier les données, comme vous-le faites habituellement pour les données contenues dans les feuilles de calcul Excel. En fait, cela se produit car un tableau croisé dynamique est par défaut personnalisé avec des fonctions et formules et si la modification est possible, toute sa structure sera détruite. Heureusement, Microsoft intègre des fonctionnalisées permettant de dépasser ce problème au niveau des tableaux croisés dynamiques. Il s’agit de la fonction des champs calculés.

L'option de champ calculé dans Excel, vous permet d'ajouter, de modifier ou de supprimer de nouveaux champs, mais vous ne pouvez modifier que les champs ajoutés à l'aide du champ calculé. En outre, le grand avantage est que grâce aux champs calculés vous pourrez créer des champs de tableau croisé dynamique qui effectuent des calculs personnalisés. Un exemple courant pourrait être la multiplication d'un nombre par un pourcentage pour calculer les taxes.

Table des matières

Pourquoi utiliser des champs calculés ?

Comment ajouter un champ calculé à un tableau croisé dynamique

Comment ajouter un champ calculé sur un champ calculé

Comment modifier un champ calculé de tableau croisé dynamique

Comment supprimer des champs calculés d'un tableau croisé dynamique Excel

Pourquoi utiliser des champs calculés ?

Parfois, vous créez un tableau croisé dynamique en utilisant des données sources contenues dans une feuille de calcul. Ensuite, vous souhaiterez peut-être que les données de votre tableau croisé dynamique résultant puissent effectuer des calculs supplémentaires pour plus d'informations tout en gardant les données sources intactes. Dans ce cas, l’utilisation de la fonction de champ calculé qui peut vous aider à faire les calculs que vous souhaitez ajouter.

Pour bien comprendre, examinons le problème suivant : nous avons une liste de ventes de 8 vendeurs pour les mois Juin, Juillet, Aout et Septembre et après avoir créé un tableau croisé dynamique, nous souhaitons calculer la commission d’un pourcentage de 0,05 sur les ventes de chaque vendeur :

Généralement parlant, il y a plusieurs façons de le faire :

• La première méthode que la plupart des gens utilisent consiste à revenir à l'ensemble de données sources et apporter les modifications nécessaires pour inclure le nouveau détail. Le processus est donc d’insérer une nouvelle colonne dans les données d’origine et y calculer la commission. Votre travail ne se termine pas ici, mais vous devrez après, mettre à jour les données du tableau croisé dynamique pour obtenir cette nouvelle colonne en tant que partie de celui-ci :

• La deuxième solution est d’ajouter les calculs souhaités en dehors du tableau croisé dynamique. Vous pouvez utiliser cette solution si vous êtes sûr que la structure de votre tableau croisé dynamique ne changera pas. Comme vous pouvez le voir dans la capture ci-dessous, une modification au niveau du tableau croisé dynamique vous donnera des erreurs et un résultat erroné puisque le calcul est placé en dehors du tableau et ne sera pas mise à jour en conséquence.

• Nous ne vous recommandons l’utilisation d’aucune des deux solutions ci-dessus. Le moyen le plus efficace d'utiliser les données de tableau croisé dynamique existantes et de calculer la mesure souhaitée consiste à utiliser des champs calculés. Pour vous simplifier les choses, considérez un champ calculé comme une colonne virtuelle que vous ajoutez à l'aide des colonnes existantes du tableau croisé dynamique. En outre, en utilisant des champs calculés de tableau croisé dynamique vous bénéficiez de plusieurs avantages :
• Vous ne serez pas obligé de gérer des formules ou de mettre à jour les données source.
• A chaque fois que vous ajoutiez de nouvelles données à votre tableau croisé dynamique le champ calculé tiendra automatiquement compte de toutes ces nouvelles données.
• Une fois ajouté, un champ calculé peut être utilisé comme n'importe quel autre champ de votre tableau croisé dynamique.
• Vous pouvez facilement gérer et mettre à jour un champ calculé. Ainsi, si les mesures changent ou si vous devez modifier le calcul, vous pouvez le faire directement à partir du tableau croisé dynamique lui-même.

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

Comment ajouter un champ calculé à un tableau croisé dynamique

Lorsque vous ajoutez un champ ou un élément calculé à un tableau croisé dynamique cela vous permet d'insérer une nouvelle ligne ou colonne dans le tableau, et vous pouvez ensuite remplir la nouvelle ligne ou colonne avec une formule.

Vous trouverez ci-dessous le processus étape par étape pour ajouter un champ calculé à un tableau croisé dynamique. Si vous n’avez pas encore créé de tableau croisé dynamique, suivez les quatre premières étapes, sinon vous pouvez passer directement à la cinquième étape.

1. Pour créer le tableau croisé dynamique, sélectionnez les données sources à utiliser.
2. Accédez à l'onglet « Insertion » dans le ruban, et cliquez sur le bouton de commande « Tableau croisé dynamique » dans le groupe « Tableaux » :

3. Faites ensuite glisser les champs du tableau croisé dynamique vers les zones où vous voulez les placer. Dans notre exemple, nous mettons le champ « Vendeur » dans la zone des lignes et les ventes « Juin », « Juillet », « Aout », « Septembre » dans la zone des valeurs :

4. Le tableau croisé dynamique est maintenant prêt :

Le tableau croisé dynamique ci-dessus résume les ventes des vendeurs pour quatre mois. Maintenant, que faire si vous souhaitez également calculer la commission de ventes pour chaque vendeur ? Vous pouvez le faire en créant un champ calculé. Suivez le reste des étapes.

5. Après avoir inséré le tableau croisé dynamique, accédez à l'onglet « Analyse ». Si vous ne trouvez pas cet onglet, cliquez à l’intérieur du tableau croisé dynamique.
6. Dans l'onglet « Analyse », cliquez sur le menu déroulant « Calcules », puis sur le menu « Champs, éléments et jeux » et sélectionnez l’option « Champ calculé » comme illustré dans la capture ci-dessous :

7. La boîte de dialogue « Insertion d’un champ calculé » s’ouvre. Tapez le nom que vous souhaitez attribuer au champ dans la zone de texte « Nom ».
8. Ensuite, dans la zone de texte « Formule », entrez la formule souhaitée pour le champ calculé. Vous pouvez utiliser les noms de champs répertoriés dans la liste en dessous si vous voulez les utiliser dans le calcul. Notez également qu’il existe certaines règles à respecter au niveau de la formule que vous utilisez pour le champ calculé de tableau croisé dynamique :

• La formule que vous créez ne doit pas contenir des références ou des plages nommées. Vous ne pourrez donc utiliser un grand nombre de formules telles que « RECHERCHEV », « INDEX », « DECALER », etc. Toutefois, vous pouvez utiliser des formules qui peuvent fonctionner sans références comme par exemple « SOMME », « SI », « COMPTE », etc.
• Comme dans l’exemple de formule que nous allons créer, vous pouvez utiliser une constante dans la formule de champ calculé.
• La formule du champ calculé suit l'ordre de priorité. Nous vous recommandons donc l’utilisation des parenthèses si la formule est longue pour vous assurer d’obtenir le bon résultat.

9. Dans notre exemple, la formule est « = 'Total des ventes'*0,05 ». Pour le champ « Total des ventes » vous pouvez le saisir manuellement ou double-cliquer sur le nom répertorié dans la liste « Champs ». Et comme nous l’avons déjà cité, nous utilisons le pourcentage « 0,05 » comme une constante. La capture ci-dessous illustre le processus :

10. Nous venons donc de créer une formule qui calculera la commission de 0,05% sur les ventes de chaque vendeur. Cliquez sur « Ajouter » et fermez la boîte de dialogue.
11. Dès que vous cliquez sur « Ajouter », votre champ calculé apparaîtra comme l'un des champs de la liste « Champs » de tableau croisé dynamique :

Désormais, vous pouvez utiliser ce champ calculé comme les autres champs de tableau croisé dynamique avec une seule exception : vous ne pouvez pas utiliser le champ calculé comme filtre ou segment de rapport.

Comment ajouter un champ calculé sur un champ calculé

Excel vous permet de créer des champs calculés en utilisant les champs obtenus à partir des données sources et vous permet également d’ajouter des champs calculés qui utilisent d'autres champs calculés dans vos tableaux croisés dynamiques. Pour ce faire, suivez le même processus que précédemment, mais lorsque vous arrivez à la boite de dialogue « Insertion d’un champ calculé » sélectionnez les champs nouvellement créés que vous voulez inclure dans vos calculs. Par exemple, nous souhaitons calculer le total des ventes avec commission pour chaque vendeur, pour cela nous ajoutons alors un champ calculé « Ventes net » qui utilise le champ calculé « Commission » :

Votre nouveau champ de tableau croisé dynamique sera affiché comme vous le voyez dans la capture ci-dessous :

Comment modifier un champ calculé de tableau croisé dynamique

Parfois, vous pouvez commettre une erreur au niveau de la formule d’un champ calculé de tableau croisé dynamique. Et des fois, vous souhaiterez peut-être modifier le nom d’un champ calculé. Excel vous donne également la possibilité de résoudre ces deux scénarios mais ce n'est pas aussi intuitif. Pour modifier un champ calculé suivez les instructions ci-dessous :

1. Pour commencer, cliquez d'abord à l’intérieur du tableau croisé dynamique pour activer les outils de tableaux croisés dynamiques.
2. Accédez ensuite à l’onglet « Analyse », cliquez sur « Calcules » puis sur « Champs, éléments et jeux » et sélectionnez « Champ calculé… » :

3. La boîte de dialogue « Insertion d’un champ calculé » s'ouvre. Lorsque vous l’ouvrez pour la première fois, la boîte de dialogue est prête à insérer un nouveau champ calculé appelé « Champ1 ». Vous pouvez cliquer sur la flèche déroulante à côté de « Champ1 », pour voir la liste de tous les champs calculés existants :

4. Dans l’exemple de notre classeur, il n'existe que deux champs calculés nommées « Commission » et « Ventes net » comme vous pouvez le voir dans la capture ci-dessus.
5. Vous pouvez sélectionner le champ calculé que vous souhaitez modifier. Dans notre exemple, nous sélectionnons le champ « Commission ». Et comme vous pouvez voir, il calcule actuellement un pourcentage de 5% du champ « Total des ventes ». Vous remarquerez également que le bouton « Ajouter » change pour dire « Modifier » :

6. A ce stade, vous pouvez modifier la formule utilisée par le champ calculé. Par exemple vous pouvez modifier le pourcentage de commission.
7. Cliquez sur « Modifier » pour enregistrer vos modifications et fermez la boite de dialogue.

Comment supprimer des champs calculés d'un tableau croisé dynamique Excel

Si vous travaillez sur tableau croisé dynamique vous pouvez remarquer que vous ne pouvez pas supprimer une Ligne ou colonne. Lorsque vous essayez de supprimer une colonne d'un champ, Excel génère une erreur indiquant « Nous ne pouvons pas modifier les cellules sélectionnées car cela affecterait un tableau croisé dynamique. Pour modifier le rapport, veuillez utiliser la liste des champs. Si vous essayez d’insérer ou de supprimer des cellules, nous vous conseillons de déplacer le tableau croisé dynamique puis de réessayer ».

Vous pouvez donc facilement supprimer des colonnes et des champs calculés d'un tableau croisé dynamique Excel mais pas avec la méthode traditionnelle (sélectionner et supprimer). Nous expliquons ci-dessous deux scénarios pour la suppression des champs calculés :

• Dans le premier scenario, nous allons étudier le cas où vous souhaitez supprimer temporairement le champ calculé et pouvoir l'appliquer à nouveau lorsque vous en aurez besoin. Pour cela, il vous suffit de masquer le champ dans la liste des champs du tableau croisé dynamique :

1. Cliquez d’abord à l’intérieur du tableau croisé dynamique pour le sélectionner et afficher le volet « Liste des champs de tableau croisé dynamique ».
2. Dans le volet des champs, cherchez le champ calculer que vous souhaitez masquer et décochez la case correspondante. Voir capture d'écran ci-dessous :

3. Après avoir décoché la case du champ calculé, ce champ sera supprimé du tableau croisé dynamique.

Remarque : pour afficher à nouveau le champ calculé dans le tableau croisé dynamique, cochez simplement la case du champ dans « le volet Liste des champs de tableau croisé dynamique ».

• Le deuxième scénario est lorsque vous souhaitez supprimer définitivement un champ calculé. Si vous êtes sûr que vous en aurez plus besoin à l’avenir procédez comme suit :

1. Cliquez sur une cellule du tableau croisé dynamique.
2. Accédez ensuite à l’onglet « Analyse », cliquez sur le menu « Calcules » puis sur la commande « Champs, éléments et jeux » et choisissez « Champ calculé » dans le sous-menu qui apparaît.
3. La boite de dialogue « Insertion d’un champ calculé », cliquez sur la flèche en regard de la zone de liste « Nom » et sélectionnez le champ calculé que vous voulez supprimer :

4. Cliquez ensuite sur le bouton « Supprimer » et Excel supprimera le champ calculé pour vous.