Sommaire
Formation Excel Tableau de bord
Pour devenir un AS des TCD et percer tous leurs secrets, formez-vous !
Sommaire
Comment faire un TCD sur Excel en 5 étapes
- Prépare ta base de données. Une ligne par enregistrement, un titre unique par colonne, aucune ligne ou colonne vide. Transforme-la en tableau structuré (Ctrl + T) pour que ton TCD suive les nouvelles données. Si tes données arrivent en vrac, Power Query peut les nettoyer automatiquement.
- Insère le TCD. Clique dans ton tableau, onglet « Insertion », puis « Tableau croisé dynamique ». Choisis « Nouvelle feuille de calcul » et valide.
- Place tes champs. Glisse-les dans « Lignes » (ex. les commerciaux), « Colonnes » (ex. les mois), « Valeurs » (ex. le montant) et « Filtres ».
- Choisis le calcul. Dans « Valeurs », ouvre les paramètres du champ pour passer de la somme au nombre, à la moyenne, etc.
- Actualise-le. Quand tes données changent, onglet « Données » puis « Actualiser tout ». Pour filtrer visuellement ton TCD, découvre les segments Excel.
Une fois ton premier TCD créé, tu peux le transformer en véritable tableau de bord : c’est exactement ce que nous faisons dans notre tableau de bord commercial et notre tableau de bord RH, à télécharger gratuitement.
Mais qu'est-ce qu'un tableau croisé dynamique ?
Les tableaux croisés dynamiques (ou TCD) sont l’un des outils les plus importants et polyvalents d’Excel. Ils permettent de faire des analyses catégorielles en quelques clics, sans fonction ni formule, créer des synthèses de bases de données. Ce qui en fait à la fois un outil hyper efficace dans l’analyse de données, mais aussi un outil très facile à créer et à manipuler.
Astuce #1 - Comment afficher les résultats d’un TCD en %
Par défaut, tout ce qui est mis dans « Valeurs » dans un TCD est soit additionné, soit simplement compté (ou dénombré) puis affiché tel quel, en valeur, sans calcul.
Mais il peut être intéressant d’afficher les résultats en %, notamment pour comprendre pour chaque ligne du TCD le pourcentage que cette valeur représente par rapport au total. Cet affichage en pourcentage peut soit remplacer l’affichage en valeur, soit le compléter en ajoutant une seconde fois la colonne dans « Valeurs » dans le TCD.
Astuce #2 - Modifier les titres d’un TCD (étiquettes de colonnes)
A chaque ajout d’une colonne dans « Lignes », « Colonnes » ou « Valeurs » dans le TCD, un titre de colonne est automatiquement créé. C’est ce titre qui sera repris dans le titre et la légende des graphiques croisés dynamiques (GCD) correspondant à ce TCD.
Malheureusement, ce titre généré automatiquement n’est pas forcément très facile à comprendre, et il est souvent efficace de le modifier.
Pour faire ça, rien de plus simple. Cliquer sur le titre existant et saisir directement un nouveau titre. Certains titres sont interdits, afin de ne pas permettre de quiproquos entre le titre d’une colonne du TCD et le titre d’une colonne du tableau initial.
Astuce #3 - Empêcher l’ajustement automatique des colonnes des TCD
Tu crées un TCD parfait, qui s’intègre parfaitement à ta feuille et que tu as rendu très visuel (en gérant manuellement les largeurs de colonnes afin de tout bien espacer et que ce soit plus propre)… nickel !
Mais à chaque mise à jour des données (bouton Actualiser tout), tous tes efforts sont réduits à néant et tu dois tout recommencer. Une fois ça va. Une deuxième fois ça va. Mais à force c’est décourageant, et tu t’habitues à résultat visuellement moyen. Si tu te décourages pas, tu penses mêmes à du VBA et à une macro pour rétablir l’ordre et redevenir le maître de ton propre outil Excel !
Heureusement, pas besoin de faire tant d’efforts !
- Tu peux simplement sélectionner une cellule de ton TCD
- Faire un clic droit
- Cliquer sur « Options du tableau croisé dynamique »
- Décocher l’option « Ajuster automatiquement la largeur des colonnes lors de la mise à jour »
- Cliquer sur « Ok » pour appliquer la modification
5 clics et le tour est joué ! Tu n’as plus qu’à redéfinir une fois pour toutes la largeur que tu veux pour améliorer chaque colonne.
Retrouve tous nos conseils dans comment booster le design de tes tableaux Excel !
Astuce #4 - Trier le TCD par valeur (ordre croissant ou décroissant)
Par défaut, les lignes d’un TCD sont affichées par ordre alphanumérique croissant. D’abord les « A », puis les « B » et ainsi de suite. Ce qui permet de s’y retrouver facilement en recherchant par exemple dans l’ordre alphabétique le nom du client « SAS Ma petite entreprise » dans une liste de clients.
Mais le classement alphanumérique croissant n’est pas forcément l’option à privilégier par défaut pour afficher des résultats. Voici d’autres options communes très efficaces :
- Classement par ordre décroissant de chiffre d’affaires
- Classement par ordre décroissant de nombre d’employés par service
- Classement par ordre croissant de notes par étudiant
- Classement par ordre croissant de temps au marathon
- Et plein d’autres trucs, les possibilités sont presque infinies !
-
Pour faire, ça c’est assez facile et rapide. Et bien entendu, ça s’actualise à chaque mise à jour du TCD.
- Sélectionner une cellule de la colonne définissant l’ordre de tri de ton TCD
- Faire un clic droit
- Cliquer sur « Trier » puis sur « Options de tri supplémentaires »
- Sélectionner « Du plus petit au plus grand »
- Vérifier que la phrase affichée est bien cohérente par rapport à ton objectif initial
- Cliquer sur « Ok » pour appliquer la modification
Et bim. Emballé c’est pesé. (J’ai jamais tout à fait compris cette phrase d’ailleurs.)
Astuce #5 - Filtrer les X valeurs les plus élevées
C’est vrai que souvent c’est bien d’avoir une vision d’ensemble grâce à un TCD. De visualiser la répartition d’un chiffre d’affaires total par clients ou par région par exemple. Et les TCD permettent de faire ça de manière à la fois intuitive et sans risque d’erreur (même si le risque zéro n’existe pas).
Faisons simple, tu pourras pratiquer autant que tu veux !
Si t’as grâce à ton TCD une synthèse de 384 clients avec pour chacun son chiffre d’affaires total c’est bien. Si tu veux pouvoir visualiser dans ton tableau de bord le top 10 de tes clients (les 10 clients pour lesquels le chiffre d’affaires est le plus important)… c’est mieux d’avoir 10 lignes que 384.
Pour afficher un top X (top 3, top 5, top 10), c’est simple :
- Cliquer sur la flèche de filtre située à droite de la première cellule de ton TCD
- Cliquer sur « Filtre s’appliquant aux valeurs »
- Cliquer sur « 10 premiers » (tout en bas de la liste)
- Normalement changer uniquement le nombre
- Et cliquer sur « Ok » pour appliquer la modification
Voilà, terminé plié, tu as un top X.
N’hésite pas à nous contacter si tu veux te former sur Excel avec nous. A bientôt !
Astuce Bonus - Comment afficher une valeur sans calcul dans un TCD ?
Dans un TCD, tout ce que tu mets dans « Valeurs » est calculé : Excel additionne, compte ou fait une moyenne. Mais parfois, tu veux juste afficher la valeur telle qu’elle est dans ta base : un prix unitaire, un taux, une note…
Cas n°1 : revenir à la valeur brute après un calcul (%, écart, cumul…)
- Faire un clic droit sur une valeur du TCD
- Cliquer sur « Afficher les valeurs de »
- Sélectionner « Aucun calcul »
Cas n°2 : afficher la valeur d’origine sans qu’Excel la somme
Par défaut, Excel somme les valeurs. Si un produit apparaît sur 3 factures, son prix unitaire sera donc affiché 3 fois. Pour l’éviter :
- Faire un clic droit sur une valeur du TCD
- Cliquer sur « Synthétiser les valeurs par »
- Choisir « Max », « Min » ou « Moyenne »
Si la valeur est la même sur toutes les lignes d’un produit, ces calculs renvoient exactement ce prix.
⚠️ Attention : si la valeur change d’une ligne à l’autre, « Max » ou « Min » n’en afficheront qu’une seule.
Et pour afficher du texte ? Un TCD classique ne le permet pas dans « Valeurs ». Il faut passer par Power Pivot et le DAX, que nous voyons dans notre formation Excel Power Pivot & DAX. En attendant, place ton champ texte dans « Lignes » ou « Colonnes ».
