On exporte un fichier de ventes, on monte un tableau croisé dynamique pour synthétiser les données par client ou par référence produit, et les totaux semblent gonflés. Le réflexe est de chercher les doublons directement dans le TCD. Le problème, c’est qu’Excel ne propose aucune détection native des doublons dans un tableau croisé dynamique. La recherche doit se faire en amont, dans la table source, avant que le TCD n’agrège quoi que ce soit.
Tenter de repérer les lignes en double après coup, dans un TCD déjà construit, expose à des oublis liés aux branches repliées, aux filtres de rapport ou aux regroupements hiérarchiques. Voici comment procéder sans se tromper.
A lire également : Ouvrir un fichier Word sans Microsoft Office : Astuces et solutions simples
Pourquoi le TCD masque les doublons au lieu de les signaler
Un tableau croisé dynamique regroupe les données. C’est sa fonction : agréger des valeurs par catégorie. Si deux lignes identiques existent dans la source, le TCD les additionne silencieusement. On ne voit qu’un seul total, sans savoir qu’il inclut un doublon.
Le piège s’aggrave avec les champs hiérarchiques. Quand on imbrique plusieurs niveaux de lignes (région, puis ville, puis client), une branche repliée peut cacher des entrées dupliquées. Le doublon existe dans les données, mais il reste invisible à l’écran tant qu’on ne déplie pas chaque niveau.
A voir aussi : Modélisation des données dans Excel : fonctionnement et utilisation
Les filtres de rapport ajoutent une couche de risque. En filtrant sur un seul segment, on peut conclure qu’il n’y a pas de doublon alors que le doublon se trouve dans un autre segment, exclu par le filtre. Ce comportement n’est pas un bug : c’est la logique même du TCD, qui travaille sur des agrégats et non sur des lignes individuelles.
Marquer les doublons dans la table source Excel avec COUNTIF
La méthode la plus fiable consiste à ajouter une colonne d’aide directement dans la table source, avant de créer ou d’actualiser le TCD. On utilise la formule NB.SI (COUNTIF en anglais) pour compter les occurrences de chaque valeur.
Mise en place de la colonne d’aide
Supposons que la colonne A contienne les références à vérifier, avec des données à partir de la ligne 2. Dans une nouvelle colonne, on saisit :
=NB.SI($A$2:$A$5000;A2)
Cette formule compte combien de fois la valeur de A2 apparaît dans la plage. Toute cellule renvoyant un résultat supérieur à 1 signale un doublon. On peut nommer cette colonne « Nb_occurrences » pour la retrouver facilement dans le TCD.
Exploiter le marquage dans le TCD
Une fois la colonne d’aide en place, on actualise le tableau croisé dynamique (clic droit, Actualiser). Le champ « Nb_occurrences » apparaît dans la liste des champs disponibles. On peut alors :
- Ajouter ce champ en filtre de rapport et sélectionner uniquement les valeurs supérieures à 1 pour isoler les doublons
- Placer le champ en colonne ou en ligne pour visualiser combien de fois chaque entrée apparaît dans la source
- Combiner ce filtre avec les autres dimensions du TCD pour identifier précisément quels clients, produits ou transactions sont dupliqués
Cette approche a un avantage direct : on travaille sur la donnée brute, pas sur un résumé. Le TCD devient un outil de lecture, pas un outil de détection.
Détecter les doublons avec Power Query avant d’alimenter le TCD
Pour les fichiers volumineux ou les sources qui changent régulièrement, Power Query offre un nettoyage reproductible des doublons sans formule manuelle. L’outil est intégré à Excel depuis la version 2016 (onglet Données, Obtenir et transformer).
Étapes dans l’éditeur Power Query
On charge la table source dans Power Query, puis on sélectionne la ou les colonnes servant de critère d’unicité. Le menu Accueil propose deux options distinctes : « Supprimer les doublons » (ne garde qu’une occurrence) et « Conserver les doublons » (ne garde que les lignes qui apparaissent plus d’une fois).
« Conserver les doublons » est l’option la plus utile pour notre objectif. Elle isole toutes les lignes dupliquées, qu’on peut ensuite examiner ou exporter. On garde ainsi une trace complète des entrées en double avant suppression.
Connecter Power Query au TCD
Une fois la requête nettoyée, on la charge dans une feuille ou directement dans le modèle de données. Le TCD alimenté par cette source propre ne contiendra plus de doublons parasites. Si la source évolue, un simple clic sur « Actualiser tout » relance la requête et met à jour le TCD.
Les retours varient sur ce point : certains utilisateurs préfèrent garder les doublons visibles dans une table annexe plutôt que de les supprimer automatiquement via Power Query, pour conserver une piste d’audit.
Erreurs fréquentes qui faussent la détection de doublons dans Excel
Même avec la bonne méthode, plusieurs pièges techniques peuvent faire passer un doublon sous le radar.
- Les espaces invisibles en fin de cellule : « Client A » et « Client A » (avec un espace final) sont considérés comme deux valeurs distinctes par NB.SI et par Power Query. La fonction SUPPRESPACE (TRIM) appliquée à la source règle le problème
- Les différences de casse : selon le contexte, « REF-001 » et « ref-001 » peuvent être traités différemment. NB.SI est insensible à la casse, mais Power Query respecte la casse par défaut dans certaines opérations de regroupement
- Les formats de date incohérents : une date stockée comme texte (« 15/06/2024 ») et une vraie date Excel (le nombre série correspondant) ne seront jamais reconnues comme doublons. Vérifier le format de la colonne avant toute analyse
- L’oubli d’actualisation du TCD : après avoir nettoyé la source, le TCD ne se met pas à jour automatiquement. Sans actualisation manuelle ou macro, on continue à voir les anciennes données agrégées
Un dernier point souvent négligé : la plage source du TCD. Si on ajoute des lignes à la table source sans étendre la plage du TCD, les nouvelles entrées (et leurs éventuels doublons) ne seront pas prises en compte. Convertir la source en Tableau Excel (Ctrl+T) résout ce problème, car la plage s’étend automatiquement.
Le réflexe à garder : toujours traiter les doublons dans la source, jamais dans le TCD lui-même. Le tableau croisé dynamique agrège, il ne diagnostique pas. En structurant la donnée en amont, avec une colonne NB.SI ou un passage par Power Query, on obtient un TCD fiable sans mauvaise surprise à la lecture des totaux.
