Excel : insérer ou modifier un champ calculé dans un tableau croisé dynamique
Je ne suis pas un grand adepte des tableaux croisés dynamiques, mais c'est certainement une erreur. A vrai dire, j'ai toujours préféré recréer mes propres tableaux en utilisant des formules telles que les fonctions RECHERCHEV ou SOMME.SI.ENS par exemple. Mais il faut avouer que ces formules demandent énormément de ressources d'une part et une grande maîtrise d'Excel d'autre part. Les tableaux croisés dynamiques sont donc recommandés dans la majorité des cas.
L'intérêt du champ calculé
Les tableaux croisés dynamiques sont très utiles, mais sans la fonctionnalité du champ calculé, nous sommes très vite limité. Le champ calculé permet en effet de créer un nouveau champ dans notre tableau, un champ qui n'est pas présent initialement dans la base de données.
Comme toujours, le plus simple sera d'utiliser un exemple. Voici donc une base de données ultra simplifiée. Dans le cas ci-dessous, je suis entrepreneur, j'émets et je reçois des factures (des factures Excel bien sûr).
Nous créons un tableau croisé dynamique très simple pour nous permettre de visualiser par mois, la somme des recettes et la somme des dépenses.
Il vous faut donc sélectionner la base de données, puis dans la section Insertion, cliquer sur le bouton "Insérer un tableau croisé dynamique".
La fenêtre suivante s'ouvre. Conserver les premières informations (les données à analyser) et choisissez l'emplacement du tableau. Souvent, le plus simple est de cocher "Nouvelle feuille de calcul", mais afin que ce soit plus visuel ici, nous allons placer le tableau croisé dynamique à côté de la base de données.
Notre tableau est créé, nous pouvons donc sélectionner les champs qui nous intéressent et les placer dans les bonnes zones. Ici en l'occurrence, nous plaçons (un glisser-déposer suffit) le champ date dans la zone Étiquettes de lignes et les champs Recette et Dépense dans la zone Valeurs.
Notre tableau est créé, mais avant de passer au champ calculé, il nous faut encore :
- modifier les étiquettes de lignes pour grouper les dates par mois
- modifier l'affichage des recettes et dépenses puisqu'ici le tableau croisé dynamique affiche par défaut le nombre de factures au lieu de la somme.
Pour grouper les dates par mois, il suffit de se positionner sur l'intitulé "Etiquettes de lignes" ou bien sur l'un des dates, puis de cliquer droit, de sélectionner Grouper et de choisir Mois.
Et voilà le résultat.
Ensuite, pour afficher les valeurs en somme et non plus en nombre, il suffit de cliquer sur le champ en question, puis de sélectionner "Paramètres des champs" de valeurs et de modifier Nombre en Somme.
D'ailleurs, si vous le souhaitez, vous pouvez également cliquer sur "Format de nombre" et passer en affichage monétaire pour afficher le sigle EURO.
Voilà donc notre tableau croisé dynamique :
Comment insérer et modifier un champ calculé dans Excel 2010
Mais finalement, ce que nous aimerions connaître, c'est le bénéfice réalisé par mois. Autrement dit, simplement soustraire les champs Recette et Dépense. Il est impossible de le faire "à l'extérieur" du tableau croisé dynamique le format de ce dernier peut-être amené à évoluer (des lignes s'ajouteront au fur et à mesure que les mois passeront) et nous pourrions souhaiter ajouter d'autres champs dans le tableau (l'année par exemple) ce qui aurait pour conséquence d'élargir le tableau d'une colonne, écrasant alors obligatoirement tout ce qui s'y trouve. Bref.
Pour créer un champ calculé, il vous faut aller dans "Outils de tableau croisé dynamique", section Options, puis de cliquer sur "Champs, éléments et jeux" et Champ calculé...
Il faudra ensuite donner un nom à ce champ, un nom unique bien entendu (autrement dit qui n'est pas déjà utilisé dans le TCD) puis définir une formule. Pour définir la formule, il suffira de cliquer sur le ou les champs dont nous avons besoin et d'utiliser les sigles classiques d'Excel. Ici = Recette - Dépense. Puis cliquer sur Ajouter.
Et voilà, notre champ calculé a été automatiquement ajouté à notre tableau 🙂
Téléchargement du fichier
Comme toujours, si certains souhaitent télécharger le fichier d'exemple pour s'en inspirer, le voici :
