Tu as déjà regroupé tes ventes dans un tableau croisé dynamique. Le chiffre d'affaires est là, les coûts aussi, mais il manque la marge. Ajouter une formule à côté du tableau fonctionne jusqu'au prochain filtre, déplacement ou ajout de lignes. Un champ calculé permet de garder ce calcul dans le TCD.
Si tu découvres les zones Lignes et Valeurs, commence par le guide des tableaux croisés dynamiques. Ici, on part d'un TCD classique créé depuis une table Excel, puis on ajoute une marge dont le total reste contrôlable.
- Créer un champ Marge à partir du chiffre d'affaires et du coût.
- Modifier sa formule et son format.
- Vérifier les sous-totaux et le total général.
- Reconnaître les calculs qui demandent une autre méthode.
Un champ calculé utilise les champs de ta source
Un champ calculé ajoute une formule au TCD. Elle fait référence aux noms des colonnes source, pas aux adresses des cellules affichées. Notre formule sera =CA-Cout. Quand tu changes le regroupement par produit ou par ville, le calcul suit la structure du tableau.
Cette méthode concerne les tableaux croisés classiques. Un TCD créé avec l'option « Ajouter ces données au modèle de données » utilise un autre moteur de calcul. Le menu Champ calculé peut y être indisponible ; une mesure du modèle de données devient alors la voie adaptée.
Étape 1 : préparer quatre lignes faciles à vérifier
Prenons un suivi de ventes avec deux villes et deux produits. Les colonnes CA et Cout contiennent déjà des montants de transaction. Un coût est enregistré comme un nombre positif, que l'on soustrait du chiffre d'affaires. Cette convention évite de soustraire une seconde fois un coût déjà négatif.
| Ville | Produit | CA | Cout | Marge attendue |
|---|---|---|---|---|
| Lille | Clavier | 300 | 180 | 120 |
| Lille | Souris | 200 | 130 | 70 |
| Lyon | Clavier | 450 | 270 | 180 |
| Lyon | Souris | 150 | 100 | 50 |
La colonne Marge attendue sert ici de contrôle sur papier ; elle n'est pas nécessaire dans la source du TCD. Les quatre marges donnent 420. Pour Lille, on attend 190 ; pour Lyon, 230. Ces résultats permettent de vérifier le tableau à plusieurs niveaux au lieu de regarder une seule cellule.
Dans Excel, saisis seulement Ville, Produit, CA et Cout, puis transforme cette plage en tableau structuré. Vérifie que les deux colonnes de montants sont numériques : un nombre stocké comme texte peut conduire le TCD à proposer un comptage au lieu d'une somme.
Étape 2 : construire la synthèse de départ
Clique dans la source, puis utilise Insertion, Tableau croisé dynamique. Place Ville dans Lignes, puis CA et Cout dans Valeurs. Pour ces deux champs, vérifie le paramètre Somme. Avec nos données, tu dois retrouver 500 et 310 pour Lille, 600 et 370 pour Lyon.
Le total général affiche donc 1 100 de chiffre d'affaires et 680 de coût. Garde ces deux colonnes le temps de créer le champ calculé : elles rendent l'écart visible et permettent de repérer une erreur de saisie ou de signe avant de compliquer la présentation.
Évite de renommer les en-têtes source pendant cette manipulation. La formule dépend de ces noms. Tu pourras changer le libellé visible du résultat dans le TCD sans transformer toute la structure de départ.
Étape 3 : ajouter le champ Marge
Sélectionne une cellule du TCD pour afficher son onglet d'analyse. Dans Excel de bureau sous Windows, ouvre Analyse du tableau croisé dynamique, puis Champs, éléments et jeux, Champ calculé. Selon la largeur du ruban, cette commande se trouve dans un groupe Calculs.
Dans Nom, saisis Marge. Dans Formule, supprime le contenu proposé et construis le calcul suivant en insérant les champs depuis la liste. Clique ensuite sur Ajouter, puis sur OK pour fermer la fenêtre.
=CA-CoutLe nouveau champ apparaît dans les valeurs du TCD. Si tes colonnes s'appellent « Chiffre affaires » et « Coût total », insère leurs noms depuis la liste plutôt que de retaper les espaces et les apostrophes. La formule doit viser les noms source, même si le tableau affiche « Somme de CA ».
Étape 4 : vérifier le total avant la mise en forme
Pour Lille, le champ Marge doit donner 190. Pour Lyon, il doit donner 230. Le total général doit afficher 420, identique à 1 100 moins 680 et à la somme des quatre marges calculées séparément. Si ces trois contrôles concordent, notre calcul additif fonctionne sur cet exemple.
Ajoute maintenant Produit sous Ville dans la zone Lignes. Tu retrouves 120 et 70 sous Lille, puis 180 et 50 sous Lyon. Retire Produit : les marges par ville restent les mêmes. Cette manipulation permet de vérifier que tu comprends ce que chaque niveau synthétise.
Clique sur une valeur de marge, ouvre Paramètres des champs de valeurs, puis Format de nombre. Choisis un format adapté à tes montants. Passer par ce réglage du champ est préférable à une mise en forme isolée de quelques cellules, qui devient difficile à maintenir quand le TCD change de taille.
Étape 5 : modifier, retrouver ou supprimer la formule
Pour changer le calcul, retourne dans Champ calculé et sélectionne Marge dans la liste des noms. Modifie la formule, puis clique sur Modifier avant de fermer. Retaper un nom différent crée un autre champ ; cela ne remplace pas forcément celui déjà utilisé.
Dans Champs, éléments et jeux, la commande Liste des formules crée une feuille récapitulative. Elle aide à documenter un classeur contenant plusieurs champs calculés. Conserve-la avec les éléments utiles au contrôle, surtout si une autre personne doit reprendre le reporting.
Pour retirer seulement la colonne de l'affichage, enlève Marge de la zone Valeurs. Pour supprimer sa définition, sélectionne-la dans la fenêtre Champ calculé et utilise Supprimer. Ces deux gestes n'ont pas le même effet : un champ masqué peut être réutilisé plus tard.
Pourquoi quantité multipliée par prix peut tromper
Un champ calculé classique ne réalise pas systématiquement le calcul sur chaque transaction avant d'additionner les résultats. Pour une multiplication, Excel peut calculer le produit des sommes des champs. Avec deux ventes de 2 articles à 10 et 3 articles à 20, le montant attendu est 80 ; multiplier les totaux donne 5 fois 30, soit 150.
La soustraction de notre exemple convient parce que somme des CA moins somme des coûts donne bien la somme des marges. Cette propriété ne s'étend pas automatiquement à la multiplication, à la division ni aux moyennes. Microsoft décrit ce comportement dans sa documentation sur les totaux des champs calculés.
Pour calculer quantité fois prix transaction par transaction, ajoute plutôt cette colonne dans la source, puis additionne-la dans le TCD. Dans un modèle de données, une mesure adaptée au contexte et au calcul ligne par ligne constitue une autre solution. Pour afficher simplement une part du total, utilise les options décrites dans le guide du pourcentage dans un TCD.
Le résultat : une marge qui suit le regroupement
Notre synthèse dispose maintenant d'un champ Marge intégré, contrôlé sur les villes, les produits et le total général. Quand de nouvelles transactions arrivent, ajoute-les à la table source, puis actualise le TCD. Vérifie que l'étendue de la source comprend bien les nouvelles lignes avant d'interpréter le résultat.
Pour aller plus loin
Le champ calculé répond ici à une soustraction de montants. Pour travailler sur la lisibilité, les regroupements et les filtres, poursuis avec le guide pour trier et filtrer un tableau croisé dynamique. Garde le calcul et son contrôle séparés de la présentation : un tableau élégant reste facile à lire, mais seul le rapprochement avec la source permet de valider ses chiffres.
Questions fréquentes
Où se trouve le champ calculé dans Excel ?
Clique dans un TCD classique, puis ouvre Analyse du tableau croisé dynamique, Champs, éléments et jeux, Champ calculé. Selon le ruban, passe d'abord par le groupe Calculs. Le parcours décrit concerne Excel de bureau sous Windows.
Pourquoi le bouton Champ calculé est-il grisé ?
Vérifie si le tableau repose sur le modèle de données ou une source OLAP. Les champs calculés classiques ne s'y utilisent pas de la même façon. Pour un modèle de données, envisage une mesure adaptée au besoin.
Peut-on utiliser une référence comme B5 dans la formule ?
Le champ calculé fait référence aux champs de la source, pas aux cellules de la synthèse affichée. Insère les noms depuis la liste de la fenêtre. Pour un calcul cellule par cellule, prépare plutôt une colonne dans la source.
Pourquoi le total général est-il différent du résultat attendu ?
Avec une multiplication ou une division, le TCD peut calculer à partir des sommes des champs, au lieu d'additionner les calculs détaillés. Compare le total à un calcul indépendant. Une colonne calculée dans la source peut résoudre ce besoin.
Quelle différence entre champ calculé et élément calculé ?
Un champ calculé combine des champs, comme CA et Cout. Un élément calculé combine des éléments appartenant à un même champ, comme certaines catégories. Le second n'est pas nécessaire pour notre marge.
Le champ calculé se met-il à jour avec de nouvelles lignes ?
Il est recalculé lors de l'actualisation du TCD sur sa source à jour. Si les nouvelles lignes sont hors de la plage source, elles ne seront pas prises en compte. Une table structurée aide à gérer l'extension des données.
