Excel : préparer et nettoyer les données pour un tableau croisé dynamique (TCD)

Objectif du chapitre

Vous allez partir de données de ventes « brutes » et obtenir une table propre, structurée et fiable, prête à alimenter un tableau croisé dynamique (TCD). Le principe : avant d’analyser, on sécurise la qualité des données (structure, formats, cohérence, référentiels).

Jeu de données de départ (exemple)

Table Ventes (données transactionnelles)

Colonnes attendues : Date, Produit, Catégorie, Commercial, Région, Canal, Quantité, Prix, Remise, CA.

Table Produits (référentiel)

But : fiabiliser les libellés et éviter les variations (ex. catégorie, nom produit). Exemple de colonnes : CodeProduit, NomProduit, Catégorie, PrixCatalogue.

Table Régions (référentiel)

But : normaliser les régions et les rattacher à une zone. Exemple de colonnes : Région, Zone (Nord/Sud/Est/Ouest, etc.).

Diagnostic rapide : problèmes fréquents à repérer

Avant de « corriger », faites un scan visuel et quelques tris/filtrages pour identifier les erreurs typiques :

Continuez dans notre application.

Vous pouvez écouter le livre audio écran éteint, recevoir un certificat gratuit pour ce cours et accéder également à 5 000 autres cours en ligne gratuits.

Ou poursuivez votre lecture ci-dessous...

Téléchargez l'application

• En-têtes incohérents : doublons d’en-têtes, colonnes sans nom, accents/espaces différents (Région vs Region ).
• Lignes vides / colonnes vides au milieu de la plage : cassent l’analyse et les sélections.
• Doublons : même vente saisie deux fois (ou lignes quasi identiques).
• Cellules fusionnées : empêchent un tri/filtre fiable, perturbent le TCD.
• Formats incorrects : dates stockées en texte, nombres en texte, devise mal interprétée.
• Espaces invisibles : espaces en début/fin, caractères non imprimables (copier-coller depuis un outil externe).
• Valeurs manquantes : région vide, remise vide, quantité vide.
• Libellés variables : IDF vs Île-de-France, Web vs Site, Revendeur vs Reseller.
• Colonnes “mélangées” : sous-totaux manuels au milieu, commentaires, titres de sections, lignes “Total”.

Mini-checklist de diagnostic (rapide)

• Les en-têtes sont-ils sur une seule ligne, uniques et explicites ?
• Y a-t-il des lignes vides, des colonnes vides, des cellules fusionnées ?
• Les champs clés (Date, Produit, Région, Quantité, Prix) contiennent-ils des vides ?
• Les libellés se répètent-ils avec des variantes (abréviations, casse, accents) ?
• Les dates et nombres se comportent-ils comme des dates/nombres (tri chronologique, calculs) ?

Normaliser la plage : règles de structure “TCD-ready”

Un TCD fonctionne au mieux avec une table « longue » (liste d’enregistrements) respectant ces règles :

• Une ligne = un enregistrement (une vente, une transaction, une ligne de facture).
• Une colonne = un attribut (Date, Produit, Région…). Pas de colonnes qui mélangent plusieurs infos.
• En-têtes uniques et stables (pas de colonnes sans nom, pas de doublons).
• Pas de sous-totaux manuels ni de lignes “Total” dans la table de base.
• Pas de cellules fusionnées dans la zone de données.
• Données brutes séparées des calculs : évitez de mélanger des calculs “ad hoc” au milieu des colonnes brutes. Si vous ajoutez des colonnes calculées, faites-le clairement et de façon cohérente (voir plus bas).

Nettoyage dans Excel : procédure pas à pas

1) Convertir la plage en Tableau (Ctrl+T) et nommer le Tableau

Objectif : obtenir une plage structurée, extensible, avec filtres, références structurées et meilleure fiabilité pour le TCD.

• Sélectionnez une cellule de la plage de ventes.
• Appuyez sur Ctrl+T (ou Insertion > Tableau).
• Vérifiez que “Mon tableau comporte des en-têtes” est coché.
• Dans l’onglet Tableau (Création de tableau), renommez le tableau (ex. tblVentes).

Bon réflexe : créez aussi tblProduits et tblRegions pour les référentiels.

2) Supprimer les doublons (avec méthode)

Avant de supprimer, clarifiez ce qui définit un doublon : une vente identique sur toutes les colonnes ? ou un identifiant unique (si vous en avez un) ?

• Cliquez dans tblVentes.
• Données > Supprimer les doublons.
• Cochez les colonnes qui définissent l’unicité (souvent Date + Produit + Commercial + Région + Canal + Quantité + Prix + Remise).
• Validez et notez le nombre de doublons supprimés (à consigner dans vos contrôles).

Astuce : si vous n’êtes pas sûr, dupliquez l’onglet avant suppression pour garder une version “avant”.

3) Contrôler et traiter les valeurs manquantes

Un TCD tolère les vides, mais vos résultats peuvent devenir trompeurs (ex. “(vide)” en ligne/colonne). L’objectif est de décider : compléter, corriger, ou exclure.

• Utilisez les filtres du Tableau pour afficher les (Vides) sur des colonnes clés (Date, Produit, Région, Quantité, Prix).
• Pour chaque champ, choisissez une action :
  • Compléter si l’information est récupérable (ex. région connue via le commercial).
  • Remplacer par une valeur explicite (ex. Inconnu) si c’est acceptable.
  • Exclure la ligne si elle est inutilisable (à justifier).

Conseil : évitez de remplacer une valeur manquante par 0 si cela change le sens (ex. remise inconnue n’est pas remise à 0).

4) Nettoyage de texte : espaces, caractères invisibles, casse

Les variations de texte créent des catégories multiples dans un TCD (ex. IDF et Île-de-France apparaissent comme deux régions différentes). Utilisez des colonnes de nettoyage, puis remplacez les originales si besoin.

Fonctions utiles

• SUPPRESPACE(texte) : supprime les espaces en trop (début/fin et multiples).
• NETTOYER(texte) : retire certains caractères non imprimables.
• MAJUSCULE(), MINUSCULE(), NOMPROPRE() : uniformiser la casse (à utiliser selon le contexte).

Exemple : créer une colonne Région_net pour fiabiliser les libellés.

=NOMPROPRE(SUPPRESPACE(NETTOYER([@Région])))

Exemple : créer une colonne Canal_net en majuscules (utile si vous avez “web”, “Web”, “WEB”).

=MAJUSCULE(SUPPRESPACE(NETTOYER([@Canal])))

Ensuite, vous pouvez : (1) conserver les colonnes *_net* pour le TCD, ou (2) copier-coller les valeurs nettoyées sur les colonnes d’origine (Collage spécial > Valeurs) si vous voulez simplifier.

5) Uniformiser les libellés variables (ex. IDF vs Île-de-France)

Deux approches simples dans Excel (sans outils avancés) :

a) Correction directe via filtre

• Filtrez la colonne Région et repérez les variantes (IDF, Ile de France, Île-de-France).
• Remplacez les valeurs pour converger vers un libellé unique (ex. toujours Île-de-France).

b) Normalisation via référentiel (recommandé)

Ajoutez dans tblRegions une colonne “Alias” si nécessaire (ex. IDF) et une colonne “RégionStandard” (ex. Île-de-France). L’objectif est de rattacher les ventes à une valeur standard.

Si vous utilisez une recherche, assurez-vous que les clés sont elles-mêmes nettoyées (SUPPRESPACE/NETTOYER) des deux côtés.

6) Vérifier les types : dates, nombres, devise, pourcentages

Un TCD agrège correctement uniquement si les types sont corrects (une date doit être une date, un nombre doit être un nombre).

Dates

• Test rapide : changez le format en Date et triez du plus ancien au plus récent. Si l’ordre est incohérent, certaines dates sont probablement du texte.
• Test de calcul : dans une cellule, faites =[@Date]+1. Si Excel renvoie une erreur, la date est peut-être du texte.

Nombres (Quantité, Prix, CA)

• Test rapide : faites une somme en bas de colonne. Si le résultat est 0 ou incomplet, des valeurs sont peut-être du texte.
• Vérifiez les séparateurs (virgule/point) selon vos paramètres régionaux.

Pourcentages (Remise)

• Assurez-vous que 10% est bien stocké comme 0,10 (format Pourcentage) et pas comme texte "10%" ou nombre 10.
• Contrôle : une remise de 10% doit être comprise entre 0 et 1 si le format est numérique (0,10), ou entre 0% et 100% si format pourcentage.

7) Colonnes calculées simples utiles au TCD

Ajoutez des colonnes calculées uniquement si elles servent l’analyse et restent stables. Dans un Tableau, la formule se recopie automatiquement.

CA (si non fourni ou à recalculer)

Exemple (si Remise est un pourcentage) :

=[@Quantité]*[@Prix]*(1-[@Remise])

Si votre remise est saisie en “10” pour 10%, convertissez d’abord (ou adaptez) :

=[@Quantité]*[@Prix]*(1-[@Remise]/100)

Mois et Année (pour regrouper dans le TCD)

=MOIS([@Date])

=ANNEE([@Date])

Option plus lisible pour le TCD : un libellé de mois (attention au tri) :

=TEXTE([@Date];"aaaa-mm")

Ce format aaaa-mm se trie correctement et facilite les analyses mensuelles.

Contrôles de cohérence : sécuriser avant TCD

1) Totaux attendus

• Calculez le total de CA (somme) et comparez-le à une source de référence si vous en avez une (ex. export ERP, total mensuel).
• Comparez le nombre de lignes avant/après nettoyage (doublons supprimés, lignes exclues).

2) Détection d’anomalies (règles simples)

• Quantité négative : filtrez Quantité < 0 (retours ? erreur de saisie ?).
• Prix négatif ou nul : filtrez Prix <= 0.
• Remise incohérente : filtrez Remise > 100% (ou > 1 si stockée en décimal).
• CA incohérent : si vous avez CA_calculé et CA, calculez un écart et filtrez les écarts non nuls.

Exemple d’écart :

=[@CA]-([@Quantité]*[@Prix]*(1-[@Remise]))

3) Échantillonnage

Contrôle manuel sur un petit échantillon (5 à 10 lignes) :

• Vérifiez que la Date est correcte, que la Région est standardisée, que la Remise est bien interprétée.
• Recalculez à la main 2 ou 3 lignes de CA pour valider la formule et les formats.

Exercices

Exercice guidé : rendre un extrait “TCD-ready”

But : transformer un extrait de ventes brutes en Tableau Excel propre.

Étapes :

• Collez l’extrait de données brutes dans une feuille Ventes_Brutes.
• Supprimez les lignes vides et défusionnez les cellules si nécessaire.
• Vérifiez la ligne d’en-têtes : renommez pour obtenir des noms uniques (ex. Date, Produit, Région…).
• Convertissez la plage en Tableau (Ctrl+T) et nommez-le tblVentes.
• Supprimez les doublons (Données > Supprimer les doublons) en documentant les colonnes utilisées.
• Créez des colonnes de nettoyage texte (Région_net, Canal_net) avec SUPPRESPACE + NETTOYER + casse.
• Vérifiez les types (Date, Quantité, Prix, Remise) et corrigez les valeurs en texte.
• Ajoutez les colonnes calculées : CA_calc, Année, Mois (ou aaaa-mm).
• Effectuez 3 contrôles de cohérence (ex. quantité négative, remise > 100%, total CA).

Résultat attendu : un tableau tblVentes sans vides critiques non traités, avec libellés homogènes et colonnes calculées prêtes pour l’analyse.

Exercice autonome : corriger 10 erreurs injectées

But : identifier et corriger des erreurs typiques, puis produire une version “TCD-ready”.

Liste d’erreurs à corriger (exemples) :

• 3 dates stockées en texte (tri incohérent).
• 2 valeurs de Prix en texte (avec espace ou séparateur incorrect).
• 2 libellés de région incohérents (IDF vs Île-de-France).
• 1 canal avec espaces invisibles ("Web ").
• 1 remise saisie à 150%.
• 1 quantité négative non justifiée.

Consignes :

• Travaillez dans une copie de la feuille pour garder une trace.
• Documentez chaque correction (quoi, où, comment corrigé).
• Livrez une table finale en Tableau Excel nommé tblVentes_Propre (ou remplacez tblVentes si vous préférez).

Livrables attendus

• Fichier Excel contenant :
  • une Table structurée des ventes (ex. tblVentes_Propre),
  • la table Produits (tblProduits) et la table Régions (tblRegions) si fournies,
  • les colonnes de nettoyage et/ou les colonnes standardisées utilisées pour l’analyse.
• Liste des contrôles effectués (dans une feuille “Contrôles” ou un bloc dans le fichier) : doublons supprimés, champs vérifiés, anomalies détectées/corrigées, règles de standardisation appliquées, totaux comparés.