Votre TCD affiche les mêmes totaux pour toutes les questions ? La cause vient presque toujours d’un tableau déjà pivoté. Voici une méthode éprouvée pour normaliser (dé‑pivoter) avec Power Query, bâtir un TCD propre et obtenir un graphique dynamique vraiment fidèle aux réponses.
Problème rencontré
Dans un classeur dédié au reporting d’une enquête, un tableau croisé dynamique (TCD) et un graphique dynamique affichaient, pour chaque question, exactement les mêmes totaux (par exemple : 393 / 24 / 179 / 416 / 2). Pourtant, en consultant les données brutes, on observait des distributions différentes, par exemple pour la dimension « Proud » : 687 — 267 — 59.
Symptômes typiques
- Chaque ligne du TCD (chaque question) affiche la même série de totaux pour les choix « Strongly agree → Strongly disagree ».
- Le graphique dynamique se met à jour, mais ne reflète que la première colonne de la table source (souvent « Wellbeing », « Engagement », etc.).
- Le nombre d’éléments « Réponse » est correct, mais tous se cumulent sur la mauvaise variable.
Cause identifiée
Les réponses importées étaient déjà « pivotées » dans la feuille source, c’est‑à‑dire :
- les titres de questions en lignes (Wellbeing, Proud, Leadership…) ;
- les intitulés des choix de réponse en colonnes (Strongly agree, Agree, Neutral, Disagree, Strongly disagree).
En important tel quel ce tableau dans un TCD, Excel a traité chaque colonne comme un champ indépendant. Résultat : il a agrégé la première colonne rencontrée (ex. « Wellbeing ») pour toutes les questions, d’où l’illusion de doublons.
Pourquoi Excel se « trompe » dans ce cas
Techniquement, le TCD attend des données normalisées (schéma base de données) : une colonne par variable (Question, Réponse, Nombre) et une ligne par observation. Un tableau déjà « largo » (large, pivoté) perturbe la logique d’agrégation : chaque colonne « Strongly agree », « Agree » etc. est vue comme une mesure distincte et non comme des valeurs d’un même champ. C’est précisément ce que corrige la transformation Dé‑pivoter (Unpivot) de Power Query.
Solution retenue : normaliser puis analyser
La stratégie gagnante se déroule en deux temps : (1) dé‑pivoter pour revenir à un format « base de données », puis (2) analyser dans un TCD / graphique dynamique basé sur le modèle de données.
| Étape | Action (Power Query) | But |
|---|---|---|
| 1 | Charger la table (ruban Données > Obtenir & transformer). | Passer par l’éditeur Power Query pour des transformations robustes. |
| 2 | Sélectionner toutes les colonnes de réponses → clic droit Dé‑pivoter les colonnes (Unpivot). | Convertir les en‑têtes de colonnes (« Strongly agree », etc.) en valeurs de ligne (« Réponse »), et créer une colonne « Valeur » (le Nombre). |
| 3 | Renommer les colonnes : Question / Réponse / Nombre. | Lisibilité et compatibilité TCD. |
| 4 | Fermer & charger dans le modèle de données. | Permettre relations, tris personnalisés et calculs propres. |
| 5 (optionnel) | Créer une table Réponses avec les 5 choix + colonne Ordre (1→5). Établir une relation. | Garantir l’ordre logique « Strongly agree → Strongly disagree » dans le TCD et le graphique. |
| 6 | Construire le TCD : Question en lignes, Réponse en colonnes, Nombre en valeurs. | Afficher les vrais totaux par question et par réponse. |
| 7 | Ajouter des segments (slicers) et convertir en graphique dynamique. | Filtrage interactif et dataviz à jour instantanément. |
Avant / Après : ce que change le dé‑pivoter
Avant (tableau pivoté) — chaque colonne est un choix de réponse :
| Question | Strongly agree | Agree | Neutral | Disagree | Strongly disagree |
|---|---|---|---|---|---|
| Wellbeing | 393 | 24 | 179 | 416 | 2 |
| Proud | 687 | 267 | 59 | … | … |
Après (tableau normalisé) — une ligne = une combinaison (Question, Réponse) :
| Question | Réponse | Nombre |
|---|---|---|
| Wellbeing | Strongly agree | 393 |
| Wellbeing | Agree | 24 |
| Wellbeing | Neutral | 179 |
| Wellbeing | Disagree | 416 |
| Wellbeing | Strongly disagree | 2 |
| Proud | Strongly agree | 687 |
| Proud | Agree | 267 |
| Proud | Neutral | 59 |
Pas à pas détaillé dans Power Query
- Importer la plage source en tant que Table (Ctrl+T), en cochant « Mon tableau comporte des en‑têtes ».
- Données > À partir d’un tableau/plage pour ouvrir l’éditeur Power Query (PQ).
- Vérifier les types de données (icône de type dans l’en‑tête). Les compteurs doivent être Nombre entier. Éviter « Texte » sur les colonnes numériques.
- Sélectionner uniquement les colonnes de choix (les 5 options). Clic droit > Dé‑pivoter les colonnes.
- Renommer la colonne « Attribut » en Réponse et « Valeur » en Nombre. Renommer la 1re colonne en Question si nécessaire.
- Supprimer les lignes nulles si présentes (Filtrer « Nombre » > Supprimer les null).
- Cliquer Fermer & charger dans… > Créer uniquement une connexion + Ajouter ces données au modèle de données.
Extrait de code M (pour les curieux)
let
Source = Excel.CurrentWorkbook(){[Name="tbl_Enquete"]}[Content],
Types = Table.TransformColumnTypes(Source,{{"Question", type text},
{"Strongly agree", Int64.Type},{"Agree", Int64.Type},{"Neutral", Int64.Type},
{"Disagree", Int64.Type},{"Strongly disagree", Int64.Type}}),
Unpivot = Table.Unpivot(
Types,
{"Strongly agree","Agree","Neutral","Disagree","Strongly disagree"},
"Réponse","Nombre"
),
CleanNulls = Table.SelectRows(Unpivot, each [Nombre] <> null)
in
CleanNulls
Note : si vos intitulés contiennent des espaces ou des diacritiques, PQ les gère nativement. Évitez toutefois les colonnes en double.
Garantir l’ordre logique des réponses
Deux approches complémentaires :
- Avec modèle de données : créer une table de dimension Réponses (5 lignes) avec une colonne Ordre (1→5). Établir une relation 1‑N avec la table des faits. Puis, dans le TCD, définir « Trier par colonne » (Ordre) pour le champ Réponse.
- Sans modèle : définir une liste personnalisée (Fichier > Options > Avancé > Listes personnalisées) avec la séquence souhaitée, puis appliquer ce tri au champ Réponse dans le TCD.
Construire le TCD et le graphique dynamique
- Insertion > Tableau croisé dynamique > À partir du modèle de données.
- Placer Question en Lignes, Réponse en Colonnes, Nombre en Valeurs. Vérifier que l’agrégation est Somme (ou Total) si la colonne est entière ; sinon, Excel proposera Nombre (count).
- Dans l’onglet Analyse du TCD, insérer des Segments (slicers) utiles : période, service, site, etc., si ces colonnes existent dans la table des faits.
- Insertion > Graphique croisé dynamique pour obtenir un visuel réactif aux filtres et segments.
Résultat : chaque question affiche désormais ses propres totaux réels, et le graphique s’actualise instantanément au gré des segments.
Points d’attention et alternatives
| Contrainte | Contournement / bonne pratique |
|---|---|
| Excel Mac : certaines versions ne gèrent pas le modèle de données ni les relations. | • Utiliser Power Query macOS (disponible depuis 2021) pour le dé‑pivoter, puis créer un TCD « classique ». • Ou importer le fichier dans Excel 365 Web ou Windows pour profiter du modèle. |
| Ordre des réponses | Sans modèle : définir une liste personnalisée avec la séquence Strongly agree → Strongly disagree, puis trier le champ dans le TCD. Avec modèle : table Réponses + colonne Ordre et « Trier par colonne ». |
| Versions antérieures sans Power Query | • Dé‑pivoter manuellement (copier-coller transposé, consolidation), ou petite macro VBA. • Migrer temporairement le jeu de données vers Power BI Desktop (gratuit) pour la transformation puis ré‑exporter. |
| Qualité des données | Toujours stocker les sources sous forme de table « base de données » (colonnes = variables ; lignes = observations) avant toute analyse. |
Mini‑modèle en étoile (recommandé)
Si vous avez d’autres dimensions (période, pays, BU, métier, tranche d’ancienneté), profitez du modèle de données pour relier des tables de dimension (Date, Site, Service…) à votre table des faits (Question‑Réponse‑Nombre). Avantages :
- segments et filtres fiables sur toute la visualisation ;
- tris et formats cohérents ;
- base extensible pour de futures vagues d’enquête.
Vérifications indispensables après le dé‑pivoter
- Sommes de contrôle : total par Question avant vs après (doivent coïncider).
- Types : « Nombre » en entier ; « Question »/« Réponse » en texte.
- Valeurs manquantes : lignes nulles supprimées avec traçabilité (étape PQ dédiée).
- Doublons : s’il y a deux fois (Question, Réponse) avec des nombres, vérifier s’il faut les agréger (Group By dans PQ).
Bonnes pratiques TCD & Graphique dynamique
Présentation du TCD
- Disposition tabulaire, Ne pas répéter les étiquettes d’élément désactivé si vous avez besoin d’un export lisible.
- Afficher les totaux généraux par ligne et par colonne selon l’usage (souvent actifs).
- Activer Développer/Réduire si vous avez des sous‑niveaux (thèmes → questions).
- Formater Nombre sans décimales (ou % si vous affichez « % du total »).
Graphique dynamique lisible
- Privilégier barres horizontales si vous avez beaucoup de questions.
- Conserver l’ordre « Strongly agree → … → Strongly disagree » via tri personnalisé.
- Ajouter des segments pour les variables clés (période, service). Un Timeline fonctionne bien sur la date d’enquête.
Transformer les volumes en %
Dans le TCD, utilisez Afficher les valeurs en : « % du total de la ligne » pour comparer la structure des réponses entre questions, indépendamment des effectifs. Conservez un TCD de contrôle en valeurs absolues à côté.
Erreurs fréquentes et diagnostics
| Symptôme | Cause probable | Remède |
|---|---|---|
| Toutes les lignes affichent les mêmes totaux | Tableau source déjà pivoté | Dé‑pivoter avec Power Query ; construire le TCD sur la table normalisée |
| Tri des réponses incohérent | Tri alphabétique par défaut | Liste personnalisée ou table Réponses + colonne Ordre |
| Somme au lieu de Nombre (count) | Type numérique sur une colonne qui devait être binaire | Vérifier le type et l’agrégation dans les paramètres de champ |
| Segments n’affectent pas tous les visuels | Objets basés sur des tables différentes sans relation | Utiliser le modèle de données et définir des relations cohérentes |
| Graphique « vide » sur Mac | Version ne prenant pas en charge le modèle | Créer un TCD classique ou ouvrir dans Excel 365 Windows/Web |
Automatiser la chaîne de traitement
- Classeur maître : stockez la requête PQ et les TCD/graphes prêts à l’emploi.
- Remise des nouvelles vagues : écraser uniquement l’onglet « Données brutes » (même structure), puis cliquer Actualiser tout.
- Journal des transformations : dans PQ, renommez clairement chaque étape (ex. TypeCorrect, UnpivotReponses, NettoyageNulls).
- Contrôles automatiques : ajoutez un TCD « Sommes de contrôle » (Total par Question) pour détecter tout écart dès l’actualisation.
Exemple de petite macro VBA (optionnel, pour versions sans PQ)
Si Power Query n’est pas disponible, vous pouvez dé‑pivoter avec une macro simple :
Sub Depivoter_Enquete()
Dim src As ListObject, dst As Worksheet, r As Long, c As Long, out As Long
Set src = ActiveSheet.ListObjects(1)
Set dst = Worksheets.Add
dst.[A1:D1] = Array("Question","Réponse","Nombre","SourceLigne")
out = 2
For r = 2 To src.DataBodyRange.Rows.Count + 1
For c = 2 To src.ListColumns.Count
dst.Cells(out, 1).Value = src.ListColumns(1).DataBodyRange.Cells(r - 1, 1).Value
dst.Cells(out, 2).Value = src.HeaderRowRange.Cells(1, c).Value
dst.Cells(out, 3).Value = src.DataBodyRange.Cells(r - 1, c - 1).Value
dst.Cells(out, 4).Value = r - 1
out = out + 1
Next c
Next r
End Sub
Cette routine parcourt la table source et génère une table normalisée prête pour un TCD classique.
Comparaison : TCD classique vs modèle de données
| Critère | TCD classique (sans modèle) | TCD via modèle de données |
|---|---|---|
| Volume de données | Limité par la feuille | Gère de plus gros volumes (xVelocity) |
| Relations entre tables | Non | Oui (Power Pivot) |
| Tri par colonne (dimension) | Non | Oui (« Trier par colonne ») |
| Calculs avancés | Limités | Mesures DAX possibles |
| Portabilité Mac | Bonne | Selon version |
Bénéfices obtenus
- Exactitude : fin des doublons, chaque question reflète sa distribution réelle.
- Flexibilité : segments et filtres pour croiser par période, service, pays, etc.
- Lisibilité : ordre stable « Strongly agree → Strongly disagree » sur tout le reporting.
- Réutilisabilité : rafraîchissez la requête PQ à chaque nouvelle vague.
Conseils supplémentaires
- Automatiser : placez la requête dans un classeur maître et ne remplacez que l’onglet « Données brutes ».
- Documenter : une note claire sur la transformation facilite la reprise par un collègue ou un changement de version d’Excel.
- Sauvegarder : conservez une copie intégrale des données originales avant toute manipulation.
Checklist finale
- ✅ Les colonnes de choix ont été dé‑pivotées en un champ Réponse.
- ✅ La colonne Nombre est bien de type entier (et non « Texte »).
- ✅ Le TCD est alimenté par la table normalisée (idéalement via le modèle de données).
- ✅ Un tri logique est appliqué sur Réponse.
- ✅ Les sommes de contrôle coïncident avant / après transformation.
- ✅ Les segments filtrent correctement toutes les visualisations.
FAQ express
Q : Pourquoi ne pas rester avec le tableau « large » si le TCD semble marcher ?
R : Parce qu’il n’agrège pas ce que vous croyez : il additionne des colonnes différentes comme si elles appartenaient à la même variable. À la moindre évolution (nouveau choix, renomme), les erreurs explosent.
Q : Puis‑je calculer des % d’accord net (Top‑2 / Bottom‑2) ?
R : Oui. Définissez des regroupements (Top‑2 = Strongly agree + Agree, etc.) via une table de correspondance ou une colonne conditionnelle dans PQ. Ensuite, utilisez un TCD avec « Afficher les valeurs en % de la ligne » et/ou une mesure DAX si vous passez par le modèle.
Q : Comment gérer les « Non‑réponses » ?
R : Ajoutez une valeur NR / NA dans la table Réponses avec un Ordre approprié, ou filtrez‑la à l’affichage selon vos règles de reporting.
En résumé
Si votre TCD et votre graphique dynamique renvoient, pour chaque question, la même série de totaux, c’est un signal clair : vos données étaient déjà pivotées. La normalisation via Power Query (dé‑pivoter), suivie de l’analyse dans un TCD adossé au modèle de données, élimine ces doublons et garantit un reporting fiable, interactif et pérenne.