TCD Excel affiche « Nombre » au lieu de « Somme » : que corriger avant de forcer le calcul ?

Extrait — Quand un tableau croisé dynamique compte vos montants au lieu de les additionner, ne changez pas immédiatement le calcul. Vérifiez d’abord le type des valeurs, l’agrégat attendu, la source et le total de contrôle.

Un tableau croisé dynamique (TCD) qui affiche Nombre de Montant au lieu de Somme de Montant donne souvent un indice utile : Excel n’a pas reconnu toute la colonne comme numérique. Microsoft indique que la somme est l’agrégat par défaut des champs numériques, tandis que la présence de valeurs vides ou non numériques peut conduire le TCD à compter les valeurs. La documentation Microsoft détaille ce comportement.

La bonne réaction n’est donc pas toujours « remplacer Nombre par Somme ». Il faut d’abord décider si vous voulez vraiment compter des lignes, ou si un montant stocké comme texte, une formule qui renvoie une chaîne vide, un symbole monétaire ou une importation mal typée fausse la lecture. Forcer la somme trop tôt peut masquer le défaut : dans un TCD, les cellules vides et non numériques d’un champ additionné sont traitées comme des zéros.

Voici une méthode réutilisable pour trancher sans perdre la trace du problème.

La recette « TYPE — AGRÉGAT — SOURCE — CONTRÔLE »

1. TYPE : Excel reconnaît-il réellement des nombres ?

Regardez la colonne source, pas seulement le format d’affichage. Une cellule alignée à droite ou affichée en euros n’est pas une preuve suffisante : le format visuel et le type de donnée sont deux choses différentes.

Dans un tableau Excel nommé TableauVentes, trois contrôles donnent rapidement l’alerte :

=NB(TableauVentes[Montant])
=NBVAL(TableauVentes[Montant])
=SOMME(TableauVentes[Montant])

• NB compte les cellules numériques.
• NBVAL compte les cellules non vides, y compris du texte.
• SOMME additionne les nombres reconnus.

Si NBVAL est supérieur à NB alors que toutes les lignes renseignées devraient contenir un montant, il existe au moins une valeur non numérique à examiner. Ce n’est pas une preuve définitive d’erreur : une mention comme « annulé » peut être légitime, et une formule qui renvoie "" peut être comptée comme non vide. C’est un signal de contrôle.

Ajoutez si besoin une colonne temporaire :

=ESTNUM([@Montant])

Filtrez ensuite sur FAUX. Vous isolez ainsi les lignes dont le montant n’est pas reconnu comme un nombre, sans modifier la donnée d’origine.

Les causes fréquentes sont :

• un nombre importé comme texte ;
• un symbole ou un texte saisi dans la cellule (1 100 €, N/A, à confirmer) ;
• un séparateur décimal ou de milliers incompatible avec les paramètres utilisés ;
• une formule qui renvoie une chaîne vide ;
• une colonne où montants, commentaires et codes sont mélangés.

2. AGRÉGAT : voulez-vous additionner ou compter ?

« Nombre » n’est pas forcément une erreur.

Pour le champ Montant, on attend généralement une somme. Pour le champ Numéro de dossier, une somme n’aurait aucun sens : on veut compter les dossiers, éventuellement en contrôlant les doublons. Pour une colonne Quantité, le choix dépend de la question : total d’unités ou nombre de lignes de commande ?

Formulez la question avant de toucher au TCD :

Microsoft précise que Nombre fonctionne comme NBVAL pour les valeurs d’un TCD. La page sur les calculs de TCD distingue également somme, nombre et nombre de valeurs numériques. Le libellé du champ n’est donc pas un détail cosmétique : il décrit l’opération appliquée.

3. SOURCE : réparer sans écraser la trace

Travaillez sur une copie du classeur ou conservez la colonne brute. Créez une colonne Montant_corrigé plutôt que de remplacer silencieusement la source.

Si les cellules contiennent uniquement des nombres stockés comme texte, Excel propose plusieurs conversions. La fonction VALEUR peut convertir un texte représentant un nombre, mais le résultat dépend des séparateurs et paramètres régionaux. Microsoft recommande notamment une colonne d’assistance avant de remplacer les valeurs. N’utilisez donc pas une substitution aveugle des espaces, virgules, points ou symboles : elle peut transformer un montant de façon erronée.

Procédure prudente :

1. conservez Montant_brut ;
2. documentez le format attendu (ex. nombre décimal en euros, deux décimales) ;
3. convertissez dans Montant_corrigé ;
4. filtrez les erreurs ou valeurs encore non numériques ;
5. comparez les totaux et le nombre de lignes ;
6. seulement ensuite, utilisez la colonne corrigée dans le TCD.

Si la source vient d’un export récurrent, corriger manuellement cinq cellules aujourd’hui ne règle pas le problème de demain. Il faut identifier l’étape qui crée le mauvais type : logiciel source, CSV, copie-collage ou requête. Une transformation Power Query documentée peut alors être plus robuste qu’une correction manuelle répétée. Pour choisir l’outil adapté, consultez Excel : formules, TCD ou Power Query — comment arrêter le copier-coller ?.

4. CONTRÔLE : réconcilier le résultat avant diffusion

Après correction :

1. actualisez le TCD ;
2. retirez puis replacez le champ dans la zone Valeurs si l’ancien réglage persiste ;
3. ouvrez Paramètres des champs de valeurs et vérifiez l’agrégat ;
4. comparez le total du TCD à SOMME(TableauVentes[Montant_corrigé]) ;
5. comparez aussi le nombre de lignes attendues, les filtres, les dates et les catégories ;
6. notez l’écart éventuel au lieu de l’arrondir ou de l’ignorer.

Un total identique ne suffit pas toujours : deux erreurs peuvent se compenser. Contrôlez au moins un sous-total significatif — par mois, client ou catégorie — et ouvrez le détail d’une cellule du TCD pour vérifier les lignes qui la composent.

Exemple fictif : pourquoi forcer la somme peut tromper

Supposons cinq lignes de source :

Le total métier attendu des quatre ventes renseignées est 4 200 €. Pourtant, la colonne contient seulement trois nombres reconnus, pour une somme de 3 100 €. Un TCD peut proposer Nombre de Montant et compter quatre valeurs non vides. Si vous forcez Somme sans réparer la ligne C, le résultat peut rester à 3 100 € : la valeur textuelle et la cellule vide sont alors traitées comme zéro dans le calcul du TCD.

La correction n’est pas d’imposer le libellé « Somme ». Elle consiste à convertir la ligne C selon une règle validée, à décider du traitement de la ligne D, puis à réconcilier 4 200 € entre la source corrigée et le TCD.

Exercice corrigé : trouvez la décision avant le clic

Vous recevez un TCD affichant Nombre de Prix = 18. La colonne source comporte 20 lignes, dont deux cellules vides, et plusieurs prix ont été copiés depuis un PDF.

Avant d’ouvrir les paramètres du champ, répondez :

1. Le résultat attendu est-il un total de prix, un prix moyen ou un nombre de lignes ?
2. Combien de cellules renvoient VRAI avec ESTNUM ?
3. L’écart NBVAL - NB correspond-il à des prix mal typés ou à des mentions légitimes ?
4. Quel total indépendant servira de référence après correction ?

Correction. Le nombre 18 ne permet pas de conclure. Il peut correspondre aux 18 cellules non vides, tandis que certaines restent du texte. Il faut préciser l’agrégat attendu, isoler les valeurs non numériques, convertir les prix selon leur format réel, décider explicitement du traitement des deux lignes vides, puis comparer le résultat du TCD à la somme de la colonne corrigée. Changer directement Nombre en Somme ne démontre pas que les 20 lignes ont été traitées correctement.

Quand le simple changement d’agrégat suffit-il ?

Vous pouvez choisir Somme dans les paramètres du champ si ces quatre conditions sont réunies :

• toutes les valeurs attendues sont bien numériques ;
• les cellules vides sont comprises et acceptées ;
• la question métier demande une addition ;
• un total de contrôle indépendant confirme le résultat.

Sinon, réparez d’abord la source.

Dans certains classeurs connectés à une source externe ou à un modèle OLAP, les calculs disponibles peuvent être définis côté serveur. Microsoft signale que certaines fonctions récapitulatives y sont limitées. Si l’option attendue n’est pas disponible, vérifiez donc le modèle et la mesure plutôt que de supposer un dysfonctionnement d’Excel.

Le bon niveau de compétence à demander

Savoir changer Nombre en Somme est une manipulation. Savoir expliquer pourquoi le TCD a choisi cet agrégat, protéger la donnée brute, corriger un type sans altérer les montants et réconcilier le résultat relève d’une méthode de reporting.

Si vous utilisez déjà régulièrement Excel mais que vos TCD dépendent de corrections manuelles ou produisent des totaux difficiles à justifier, la formation StraFormation Excel — tableaux croisés dynamiques travaille précisément l’audit de la source, le choix des agrégats, l’actualisation et la fiabilisation du résultat. La page indique un parcours de 14 heures, proposé à Strasbourg, à distance et en intra-entreprise, pour des utilisateurs réguliers d’Excel maîtrisant notamment la création de tableaux, le tri, le filtre et les formules simples.

Avant de demander une orientation, préparez : votre version d’Excel et votre plateforme, le type de source, l’opération attendue, un petit échantillon anonymisé, les résultats de NB, NBVAL et SOMME, la fréquence du reporting, le livrable attendu et votre échéance. Ces éléments permettent de distinguer un réglage ponctuel d’un besoin de fiabilisation plus large, sans promettre qu’un format ou un financement conviendra automatiquement.

Pour vérifier si vos bases sont suffisantes avant ce parcours, lisez aussi Tableau croisé dynamique : quel niveau Excel faut-il vraiment ?.

Sources

• Microsoft — Additionner des valeurs dans un tableau croisé dynamique
• Microsoft — Calculer des valeurs dans un rapport de tableau croisé dynamique
• Microsoft — Modifier la fonction de synthèse ou le calcul personnalisé d’un champ
• Microsoft — Convertir des nombres stockés sous forme de texte
• StraFormation — Excel : tableaux croisés dynamiques