Un tableau croisé dynamique Excel peut intégrer un nouveau résultat calculé à partir des champs de sa source. Cette fonctionnalité permet, par exemple, de calculer des charges patronales en appliquant un taux au salaire, sans ajouter une nouvelle colonne dans la base de données.
Dans l’exemple de cette page, nous allons créer un champ nommé Charges patronales à partir du champ Salaire. La formule appliquera un taux de 45 % :
=Salaire*0,45
Le nouveau champ sera ensuite disponible dans la liste des champs et pourra être affiché dans la zone Valeurs comme n’importe quel autre champ numérique.
Qu’est-ce qu’un champ calculé dans un tableau croisé dynamique ?
Un champ calculé est un champ supplémentaire dont les valeurs sont obtenues à l’aide d’une formule. Cette formule utilise les noms des champs de la source du TCD et peut contenir des opérateurs, des constantes et certaines fonctions.
Un champ calculé possède plusieurs caractéristiques :
- il crée un nouveau résultat sans modifier la base de données d’origine
- sa formule utilise les noms des champs et non des références de cellules
- il apparaît dans la liste des champs du tableau croisé dynamique
- il peut être ajouté ou retiré de la zone Valeurs
- sa formule est conservée avec le tableau croisé dynamique
Il est ainsi possible de calculer une marge, une commission, des charges ou un montant après remise directement dans le TCD.
Différence entre un champ calculé et un calcul personnalisé
Un champ calculé crée une nouvelle valeur à partir des champs de la source. La formule =Salaire*0,45 produit ainsi un nouveau champ Charges patronales.
Les commandes comme Différence par rapport à ou Différence en % par rapport à ne créent pas un nouveau champ. Elles modifient la manière de présenter un champ déjà placé dans la zone Valeurs.
Si votre objectif consiste à comparer deux périodes ou deux colonnes, utilisez les options permettant d’afficher une variation dans un tableau croisé dynamique plutôt que de créer un champ calculé.
Créer un champ calculé dans un tableau croisé dynamique
Ouvrir la commande Champ calculé
Cliquez dans une cellule du tableau croisé dynamique afin d’afficher les outils spécifiques, puis :
- ouvrez l’onglet Analyse du tableau croisé dynamique
- cliquez sur Champs, éléments et jeux dans le groupe Calculs
- sélectionnez Champ calculé
Le même menu permet également d’accéder à la liste des formules ou aux éléments calculés. Pour notre exemple, choisissez uniquement la commande Champ calculé.
Selon la version d’Excel, l’intitulé de l’onglet peut légèrement varier, mais la commande se trouve dans les outils d’analyse du tableau croisé dynamique.
Nommer le nouveau champ
La fenêtre Insertion d’un champ calculé s’ouvre. Dans la zone Nom, remplacez le nom proposé par un intitulé décrivant clairement le résultat.
Dans notre exemple, saisissez :
Charges patronales
Évitez d’utiliser exactement le même nom qu’un champ déjà présent dans la source. Un nom explicite permettra de retrouver plus facilement le calcul dans la liste des champs et dans la zone Valeurs.
Construire la formule avec les champs de la source
La formule d’un champ calculé commence par le signe =. Elle ne doit pas utiliser une référence de cellule comme D2 ou E5, car la disposition et les cellules d’un TCD peuvent évoluer.
Pour construire la formule :
- supprimez, si nécessaire, la formule proposée automatiquement
- sélectionnez Salaire dans la liste Champs
- cliquez sur Insérer un champ
- ajoutez l’opérateur de multiplication et le taux de 45 %
La formule finale est :
=Salaire*0,45
Le bouton Insérer un champ est préférable à une saisie entièrement manuelle. Il reprend exactement le nom utilisé par Excel et évite les erreurs lorsque le champ contient des espaces ou des caractères particuliers.
Ajouter et valider le champ calculé
Cliquez sur Ajouter pour enregistrer le champ calculé, puis sur OK pour fermer la fenêtre. Le champ Charges patronales apparaît dans la liste des champs du tableau croisé dynamique.
Excel l’ajoute généralement dans la zone Valeurs. Si ce n’est pas le cas, cochez sa case ou faites-le glisser vers cette zone.
Comprendre le résultat du champ calculé
Dans la capture suivante, le champ Charges patronales est visible dans la liste des champs, sous les champs issus de la base. Il est également placé dans la zone Valeurs à côté de Somme de Prime.
Le tableau présente les charges patronales pour chaque fonction et chaque ville. Il calcule également les totaux par ville et le total général. Le nouveau champ peut être déplacé, filtré ou retiré de la zone Valeurs comme un autre champ numérique.
Excel utilise automatiquement la fonction Somme, d’où l’intitulé Somme de Charges patronales. Cette mention indique la fonction de synthèse appliquée au champ ; elle ne fait pas partie du nom défini dans la fenêtre du champ calculé.
Formater les résultats du calcul
Pour appliquer un format monétaire durable :
- ouvrez le menu du champ Somme de Charges patronales dans la zone Valeurs
- choisissez Paramètres des champs de valeurs
- cliquez sur Format de nombre
- sélectionnez le format Monétaire ou Comptabilité
- choisissez le symbole € et le nombre de décimales
- validez les deux fenêtres
Il est préférable d’utiliser le bouton Format de nombre des paramètres du champ plutôt que de formater directement quelques cellules. Le format restera ainsi associé au champ lorsque le TCD sera réorganisé.
Exemples de formules de champs calculés
Un champ calculé peut combiner plusieurs champs numériques et des valeurs constantes. Voici quelques exemples directement utilisables après adaptation des noms de champs.
| Calcul recherché | Formule du champ calculé |
|---|---|
| Charges patronales de 45 % | =Salaire*0,45 |
| Marge en euros | =Chiffre_affaires-Coût |
| Commission de 5 % | =Ventes*0,05 |
| Montant après remise | =Montant*(1-Remise) |
Ces formules sont des exemples : les noms doivent correspondre exactement aux champs présents dans votre source. Si le champ s’appelle Chiffre d’affaires et non Chiffre_affaires, sélectionnez-le dans la liste puis utilisez le bouton Insérer un champ.
Si le champ Remise contient 10 %, la formule
=Montant*(1-Remise) est adaptée. S’il contient le nombre 10, il faudra tenir compte de cette différence dans la formule. Vérifiez toujours la nature et l’unité des données utilisées.Modifier un champ calculé
Vous pouvez modifier la formule sans supprimer puis recréer le champ :
- cliquez dans le tableau croisé dynamique
- ouvrez Champs, éléments et jeux, puis Champ calculé
- ouvrez la liste de la zone Nom
- sélectionnez le champ à modifier
- corrigez sa formule
- cliquez sur Modifier, puis sur OK
Les résultats sont recalculés dans le tableau. Si le taux des charges patronales passe, par exemple, de 45 % à 47 %, remplacez simplement 0,45 par 0,47.
Supprimer un champ calculé
Retirer le champ de la zone Valeurs ne supprime pas sa formule. Le champ reste présent dans la liste et peut être réutilisé ultérieurement.
Pour le supprimer définitivement :
- rouvrez la fenêtre Champ calculé
- sélectionnez le champ dans la liste Nom
- cliquez sur Supprimer
- fermez la fenêtre
Le champ disparaît de la liste et de toutes les zones du tableau croisé dynamique dans lesquelles il était utilisé.
Pourquoi le champ calculé ne fonctionne-t-il pas ?
La formule utilise des références de cellules
Une formule comme =D2*0,45 n’est pas adaptée à un champ calculé. Utilisez le nom du champ :
=Salaire*0,45
Les références des cellules du TCD peuvent changer en fonction des filtres et de sa disposition, alors que le nom du champ reste stable.
Le nom du champ n’est pas reconnu
Une différence d’orthographe, un espace oublié ou un caractère particulier peut empêcher Excel d’interpréter la formule. Sélectionnez le champ dans la liste et cliquez sur Insérer un champ pour reprendre son nom exact.
La formule contient une division par zéro
Une formule utilisant une division peut produire une erreur lorsque le dénominateur contient une valeur nulle. Contrôlez les champs concernés dans la source et vérifiez que le calcul reste pertinent lorsque l’une des valeurs est égale à zéro.
Le résultat d’un ratio semble incorrect
Les ratios nécessitent une vigilance particulière. Selon la formule et l’organisation des données, le résultat d’un champ calculé peut différer d’un pourcentage calculé directement à partir des totaux visibles.
Vérifiez le résultat sur un exemple simple avant de généraliser le calcul. Pour une marge en pourcentage, comparez notamment le résultat obtenu avec le calcul attendu à partir du total de la marge et du total du chiffre d’affaires.
La commande Champ calculé est grisée
Vérifiez d’abord qu’une cellule du tableau croisé dynamique est sélectionnée. La commande peut également être indisponible pour certains TCD utilisant un modèle de données ou une source qui ne prend pas en charge les champs calculés classiques.
Afficher la liste des champs calculés
Le menu Champs, éléments et jeux contient également la commande Liste des formules. Elle crée une nouvelle feuille recensant les champs et les éléments calculés du tableau croisé dynamique.
Cette liste est utile pour :
- retrouver les formules enregistrées dans un classeur
- contrôler le nom des champs utilisés
- documenter les calculs avant de transmettre le fichier
- repérer plus facilement un calcul devenu inutile
Questions fréquentes
Peut-on utiliser une référence de cellule dans un champ calculé ?
Non. La formule doit utiliser les noms des champs de la source, par exemple =Salaire*0,45, et non une référence comme =D2*0,45.
Pourquoi Excel affiche-t-il « Somme de » devant le nom du champ ?
Excel applique automatiquement la fonction Somme aux champs calculés numériques placés dans la zone Valeurs. Cette mention désigne la fonction de synthèse utilisée.
Comment modifier la formule d’un champ calculé ?
Rouvrez la fenêtre Champ calculé, sélectionnez le champ dans la liste Nom, modifiez la formule puis cliquez sur Modifier.
Comment supprimer définitivement un champ calculé ?
Sélectionnez-le dans la liste Nom de la fenêtre Champ calculé puis cliquez sur Supprimer. Le retirer de la zone Valeurs ne suffit pas à supprimer sa formule.
Un champ calculé modifie-t-il les données sources ?
Non. Le calcul est enregistré dans le tableau croisé dynamique et n’ajoute aucune colonne à la base de données d’origine.
La création et le contrôle des champs calculés peuvent être abordés à partir de vos propres fichiers dans le cadre d’une formation Excel en entreprise.
