ATTENTION : vous consultez une ANCIENNE version du site >>>>> Nouvelle adresse
L'une des stars d'XL, c'est le Tableau Croisé Dynamique (TCD).
Le Tableau Croisé Dynamique permet d'analyser 1 tableau existant afin d'en tirer des statistiques ou des décomptes. Il est capable d'analyser des milliers de lignes en quelques secondes.
Contrairement à une idée reçue, le TCD est un outil simple - dépourvue de technicité - et facile à mettre en oeuvre. XL travaille, pas vous !
Réussir un TCD, repose sur 2 choses... :
un tableau de base "solide" (c'est à dire simple, avec un maximum de cellules remplies...) (voir aussi 'Tableaux Magiques')
un utilisateur équipé de "bon sens" (ou à défaut qui sait écouter les "commandes" de son supérieur... :-)
Ce tuto vous accompagne dans la compréhension et la prise en main de cet outil surpuissant afin de pouvoir utiliser la fonctionnalité TCD quel que soit votre domaine d'activité (budgets, comptabilités, finances, ressources humaines, enfance, etc...)
Le TCD est 1 fonctionnalité d'Excel qui permet d'analyser un tableau existant pour en faire une synthèse chiffrée (XL analyse 1 ou plusieurs colonnes pour en faire un 'inventaire' et faire un bilan chiffré (somme, décompte, etc.)
Le TCD s'utilise surtout sur des tableaux qui contiennent beaucoup d'informations ou de chiffres et surtout de même type (tableaux d'enregistrements, bases de données, etc.)
il permet de "faire parler " un tableau, en demandant à XL de l'analyser à votre place...
Par exemple si une mairie possède le tableau d'agents suivant... (prenez le temps de vous familiariser avec...)
Un agent pourra répondre en moins de 2 minutes à une demande d'un Elu qui souhaite savoir :
"Est-ce que la mixité homme/femme est respectée dans les directions ?" (et votre réponse sera "oui, mais pas à la santé..."),
"Y'a t'il égalité dans le versement des primes, selon qu'on est Homme ou Femme dans nos services ?" (et vous pourrez lui répondre qu'en moyenne "il vaut mieux travailler à la santé quand on est une femme à la ville... Donc "peut mieux faire" ;-)
Retenez de cet exemple que :
un TCD permet d'analyser un tableau pour répondre à 1 ou plusieurs questions
un TCD dresse un inventaire des valeurs trouvées dans 1 ou plusieurs colonnes
un TCD permet de faire faire des calculs sans même utiliser 1 seule formule !
Il n'y a pas vraiment d'étapes pour réaliser un TCD, sinon lancer la fonctionnalité et effectuer quelques choix...
mais pour bien comprendre nous préférons baliser les étapes suivantes :
bien préparer son tableau de départ...
bien écouter... bien comprendre la Question qu'on vous pose / la Commande que l'on vous fait ...
c'est ici que vous faites vos choix et paramétrez les calculs qu'XL va réaliser... et comme souvent en bureautique, on distingue le fond...
... de la forme !
Le tableau de départ doit être structuré et rempli dans les règles de l'art... :
chaque colonne contient 1 seul type d'information (on ne mélange pas texte et nombres)
chaque intitulé de colonnes doit être clair et sans ambiguïté
chaque ligne d'enregistrement doit être remplie au maximum (à quoi bon un tableau, si l'information n'est pas présente ?)
les informations remplies doivent êtres "normalisées" au maximum (éviter de mettre Mme, Madame, Féminin, F... choisir !)
Votre tableau ne doit PAS contenir de fusions de cellules !
Le tableau ci-dessous s'y prête parfaitement... :
les directions, services et sexe sont saisies proprement (saisies de la même manière, avec la même orthographe)
les informations sont toujours remplies (à l'exception de la ligne 11...)
Sur les tableaux qui "accumulent les infos" : il est largement recommandé de travailler avec les "Tableaux magiques" (la fonctionnalité Mettre sous forme de tableau - parmi les plus utiles d'XL, si ce n'est LA fonctionnalité à connaitre :-)
Cette fonctionnalité est la porte d'entrée royale vers les TCD...
Sélectionnez votre tableau et QUE votre tableau ! (du titre de la 1ère colonne à la dernière cellule en bas de la dernière colonne...)
Dans le ruban Insertion, cliquer sur le 1er bouton "TblCroiséDynamique"
Cliquer n'importe où dans le tableau...
Dans le ruban Outils de Tableau, cliquer sur Synthétiser avec un tableau croisé dynamique
une fenêtre s'ouvre... ne toucher à rien et cliquer sur OK
NOTES :
Les TCD sont habituellement créés par XL sur une nouvelle feuille... (penser à nommer cette feuille, par exemple 'TCD agents" :-). C'est la solution la plus pratique.
si vous ne souhaitez pas ajouter une feuille dédiée, cocher l'option 'Feuille de calcul existante' (et cliquer sur la feuille souhaitée) . Cette option est rarement la meilleure :-)
Sur la feuille créée, XL affiche les éléments suivants :
1 espace TCD (où XL construit le tableau...)
1 volet Latéral avec :
des Champs de colonnes en haut (XL reprend le titresde chaque colonne de votre tableau de base...)
1 espace de Construction TCD en bas...
IMPORTANT !
La construction d'un TCD entraîne l'apparition du Volet Latéral de construction,
mais aussi d'1 nouveau ruban Outils de Tableaux Croisés Dynamiques
il est OBLIGATOIRE de cliquer sur l'espace TCD pour afficher ces outils !
(si vous ne cliquez PAS sur le TCD... aucun outil n’apparaît !)
Le TCD est techniquement simplissime à réaliser...
en quelques cliquer-déplacer seulement XL construit pour vous le tableau...
XL construit en directe le tableau demandé...
XL analyse des milliers de lignes presque instantanément...
c'est cette simplicité qui est le 'talon d'Achile' de nombreux TCD.
Il n'est pas rare de rencontrer des beaux tableaux, mais qui comptent et/ou analysent n'importe et n'importe comment... :-)
Réaliser un 'bon' TCD, ne se limite donc pas à 3 manipulations à connaître (30% du travail), mais repose finalement sur la qualité de réflexion et de compréhension de l'utilisateur qui le construit (70% du travail)... :
il faut du bon sens (pour ne pas sortir des chiffres qui ne veulent rien dire)
sa compréhension de l'objectif à atteindre
Alors pour ne PAS 'aller dans le mur' : suivez ces 3 conseils... :
Lorsque vous devez réaliser un TCD, écouter attentivement la commande ou la question qui vous est faîte par votre responsable et traduire chaque élément en colonne ! (Si vous ne pouvez pas traduire chaque élément avec un nom de colonne, alors vous aurez un problème :-)
Avec le tableau ci-dessus :
dans la question "Est-ce que la mixité homme/femme est respectée dans les directions ?" : il faut 1 colonne Direction & 1 Colonne Sexe
"Y'a t'il égalité dans le versement des primes, selon qu'on est Homme ou Femme dans nos services ?" : il faudra cette fois 1 colonne Service, 1 colonne Sexe & 1 colonne Prime
Vous devez repérer l'opération à réaliser (somme, moyenne, décompte) dans la commande de votre supérieur et identifier l'unité dans laquelle on compte (en Euro ? en agents ville ? en voiture ? etc)
Avec le tableau ci-dessus :
dans la question "Est-ce que la mixité homme/femme est respectée dans les directions ?" : ici, on souhaite compter un nombre d'agents
"Y'a t'il égalité dans le versement des primes, selon qu'on est Homme ou Femme dans nos services ?" : il faudra cette compter en Euro
Construire un TCD est simplissime... Mais construire un TCD qui réponde au mieux à la Question/Commande posée est plus délicat ! (Voir Observation l'ordre des champs, plus bas). Soyez attentif à ce qui se cache derrière la Question/ Commande pour y répondre au mieux et 'captez l'esprit'... :-)
La 'méthodo' pour les préparer la construction de TCD tient donc en 3 phrases :
repérer les colonnes à analyser
identifier ce qui doit être compté
comprendre l'esprit / l'objectif à atteindre
voilà le plus important pour que XL construise un 'bon TCD' plutôt qu'un 'con TCD' ;-)
Un TCD se construit par cliquer-déplacer les champs de colonne vers les zones en bas du volet d'affichage. (Les zones Etiquettes de lignes (1) et Valeurs (2) sont les plus importantes)
ZONE 1 : Étiquettes de lignes est destiné à recevoir les colonnes qui sont analysées
ZONE 2 : Valeurs est destiné à recevoir les colonnes qui sont comptées
Les zones Filtre du Rapport et Etiquettes de colonnes sont abordées plus bas dans la section Zones Filtres & Etiquettes de colonnes
Tout peut s'effectuer par cliquer-déplacer :-)
Effacer : cliquer-déplacer 1 champ sur la feuille pour le retirer de l'espace de construction) (ou le sélectionner et le décocher dans la partie supérieure)
Déplacer un champ au dessus ou en dessous d'un autres pour modifier l'ordre d'analyse
Déplacer librement les champs d'1 zone à l'autre pour varier les possibilités de construction
Notre objectif est de répondre à la question "Est-ce que la mixité homme/femme est respectée dans les Directions ?"
L'analyse de cette question nous a permis d'identifier les colonnes à utiliser (Direction et Sexe) et comprendre ce que nous cherchons à compter (des Agents)...
Glisser sur le champ Etiquettes de ligne les colonnes à analyser... (à chaque colonne déplacée, XL construit le tableau croisé dans l'espace TCD.)
Champ Direction :
Exemple commenté
Quand la colonne Direction est déposer sur la Zone Etiquettes de lignes, XL analyse la colonne et affiche ce qu'il y a trouvé.
Dans notre exemple XL indique trouve 2 Directions différentes dans la colonne Direction (il les liste instantanément...)
Champ Sexe : en glissant une deuxième colonne, nous apportons un, 2ème niveau d'analyse.
Exemple commenté
Quand la colonne Sexe est déposée sur la Zone Etiquettes de lignes, XL analyse la colonne et affiche ce qu'il a trouvé pour chacun des éléments trouvés dans la 1ère colonne (2ème niveau d'analyse).
Dans notre exemple XL nous dit que :
pour Santé, il a trouvé des 'M' et des 'F'...
pour Enfance, il a aussi trouvé des 'M' et des 'F'
S'il n'y avait pas de Femmes à la santé, il n'aurait affiche que 'F' :-)
Il faut maintenant demander à XL de compter le nombre d'enregistrements qu'il a trouvé pour chaque cas découlant de son analyse... (nous cherchons à compter des Agents...)
Glisser sur le champ Valeurs les éléments à Compter...
Champ Agent ; ici ce que nous cherchons à compter ce sont des agents :-)
IMPORTANT !
Il est indispensable de choisir 1 colonne remplie au maximum (objectif 100%) pour 'donner à compter' à XL
Exemple commenté
Quand la colonne Agent est déposée sur la Zone Valeurs, XL va compter les agents pour chacune des situation rencontrées...
Dans notre exemple XL nous dit maintenant :
J'ai trouvé 2 directions...
dans la 1ère direction, j'ai trouvé des Femmes et aussi des Hommes... (13 agents)
voici le nombre d'agent 'F' que j'ai trouvé dans la 1ère direction (7 agents femmes)
voici le nombre d'agent ' M' que j'ai trouvé dans la 1ère direction (6 agents hommes)
dans la 2ème direction, j'ai aussi trouvé des Femmes et aussi des Hommes...(14 agents)
voici le nombre d'agent 'F' que j'ai trouvé dans la 2éme direction (11 agents femmes)
voici le nombre d'agent ' M' que j'ai trouvé dans la 2ème direction (3 agents hommes)
S'il n'y avait pas de Femmes à la santé, il n'aurait affiche que 'F' :-)
A ce stade zone de construction ci-dessous fournit un Rapport de TCD (les champs analysés sont imbriqués en décalés...). La présentation Rapport d'un TCD n'est adaptée que pour 2 à 3 colonnes analysées seulement.
Lorsque plus de colonnes sont analysées dans un TCD, il est préférable de passer en présentation Tableau (voir Contrôler la présentation du TCD)
Présentation 'Rapport' du TCD
Présentation 'Tableau' du TCD
Les 2 exemples ci-dessous montrent la différence qui résulte de l'ordre dans lequel XL analyse les colonnes... (voir La "Question" plus haut).
IMPORTANT :
Les résultats affichés dépendent de l'ordre d'analyse des colonnes ! il faut comprendre qu'XL réalise les colonnes suivantes comme des 'sous analyses', c'est à dire en fonction de l'inventaire trouvé précédemment !. Testez et Observer est le meilleur moyen pour comprendre et se familiariser avec ce fonctionnement :-)
Alors : "Est-ce que la mixité homme/femme est respectée dans les directions ?"
Analyser le Sexe en 1er fait ressortir qu'il y a 2 fois plus de femmes que d'hommes sur la Ville...
...la mixité à la ville n'est PAS équilibrée
Analyser la Direction en 1er fait ressortir qu'1 Direction sur 2 ...
...la mixité sur la ville est équilibrée SAUF à la Santé...
IMPORTANT !
Toujours vérifier l'opération réalisée par XL !
XL prend l'initiative de choisir seul l'opération qu'il effectue...
C'est à dire que :
avec des informations de type Texte (Agents contient des noms...) , XL ne peut QUE compter le Nombre de cellules qui sont pleines...
avec des informations de type Nombres ((la colonne Prime contient des données monétaires), XL choisit seul de faire la Somme des nombres qui peuple la colonne
MAIS ATTENTION : si 1 colonne qui comporte des nombres n'est PAS remplie à 100%, XL peut bascule en mode Nombre de cellules qui contiennent des nombres (comme en texte !)
Selon les données contenues dans la colonne, XL peut réaliser les opérations habituelles :
Nombre (décompte des cellules remplies)
Somme
Moyenne
etc...
cliquer sur la flèche Menu du Champ dans la zone Valeurs
dans le menu qui s'ouvre, choisir Paramètres du Champ de valeurs...
choisir l'opération que doit réaliser XL
NOTE :
La manipulation peut aussi d'effectuer parc clic-droit sur les résultats calculés : choisir Synthétiser les valeurs par....
cliquer sur la flèche Menu du Champ dans la zone Valeurs
dans le menu qui s'ouvre, choisir Paramètres du Champ de valeurs...
cliquer sur l'onglet 'Afficher les Valeurs'
choisir l'affichage % du total général l'opération que doit réaliser XL
NOTE :
La manipulation peut aussi d'effectuer parc clic-droit sur les résultats calculés : choisir Afficher les valeurs par....
Les 2 autres zones de la Zone de Construction sont à utiliser non plus selon Question/ Commande posée, mais plutôt en fonction de préférences d'utilisation ou de présentation.
Nous avons actuellement le tableau suivant...
...réalisé avec la construction suivante
Les 2 exemples ci-dessous montrent par l'exemple l'utilisation de ces 2 zones...
L'utilisation de cette zone équipe le TCD d'un filtre, et permet ainsi de basculer le TCD de l'ensemble des cas vers 1 cas spécifiques.
1. cliquer-déplacer le champ Sexe dans la zone Filtres du Rapport...
2. XL sort la colonne du TCD, et la place au-dessus sous forme de d'un Menu filtre...
3. .l'ouverture de ce menu permet de sélectionner 1 valeur... (cocher la case Sélectionner plusieurs éléments pour sélectionner plusieurs valeurs)
NOTE : un champ Recherche permet une recherche efficace sur les colonnes avec un grand nombre de valeurs...
4. (après validation avec OK) XL focalise le TCD sur les valeurs sélectionnées seulement !
L'utilisation de cette zone permet la construction par XL d'un tableau à double entrée...
1. cliquer-déplacer le champ Sexe dans la zone Etiquettes de colonnes...
2. XL transforme le TCD en tableau à double entrée...
L'utilisation (ou pas) de ces 2 zones est à l'appréciation de l'utilisateur et dépendent de chaque TCD : de l'objectif de l'analyse, du volume de colonnes (ou de données dans chaque) ou des résultats obtenus...
Il n'y a donc pas de règle, mais :
Pour les Filtres :
Plus il y a d’occurrences, plus les filtres peuvent être pratiques pour simplifier la lecture et 'focaliser' l'analyse sur quelques items seulement...
Les Filtres sont surtout destinés à une utilisation à l'écran d'un TCD.
Ils peuvent permettre d'exclure certains résultats d'un tableau à imprimer.
Pour les Etiquettes de colonne
Moins il y a d’occurrences dans 1 colonne, plus il peut être intéressant de jouer la carte de la double entrée pour faciliter la comparaison des résultats...
Plus il y a de colonnes à analyser, plus il sera utile d'utiliser les Etiquettes de colonnes pour 'alléger' la lecture.
les possibilités de constructions sont nombreuses...
il n'y a pas de recette : les bons choix à faire dépendent des tableaux de base et des données qu'ils contiennent
il est souvent Souhaitable de tester plusieurs combinaisons pour trouver les bonnes configurations
vérifiez TOUJOURS les opérations de calculs effectuées par XL (somme, moyenne, etc...)
Ne reste maintenant qu'à mettre en valeurs l'analyse d'XL avec une belle présentation :-)
Puisque les TCD sont entièrement construits par XL, et que chaque TCD est différent, il n'y a pas de recette type pour contrôler et gérer leur apparence. Aussi XL dispose de multiples outils et options à utiliser selon les cas : tous se trouvent dans les Rubans (et principalement le ruban Création)...
Nous vous présentons ci-dessous les plus utiles et les plus puissants.
RAPPEL : il faut impérativement cliquer sur le TCD pour faire apparaître le Ruban Outils de Tableau Croisé Dynamique
XL dispose de 2 rubans pour piloter et gérer les TCD :
1 ruban Option (plutôt destiné au paramétrage et au fonctionnement du TCD)
1 ruban Création qui est spécialement orienté vers l'apparence du TCD
Surtout réservé au paramétrage et à la manière de fonctionner d'un TCD, ce ruban est assez 'technique' et plutôt réservé à des usages 'avancés'.
Seulement 2 outils sont à retenir :
Actualiser : nous n'en avons pas encore parlé, mais si les données de votre tableau de base s'enrichissent, Actualiser viendra mettre à jour votre TCD ((Voir Chapitre Actualiser plus bas).
"Options, Options Option !" : pour ne pas devenir fous lors de ces mises à jour...
Un TCD est par nature "dynamique", et ses données peuvent être mises à jour... ce qui a pour effet dérégler systématiquement les tailles des colonnes lors ces dernières...
Mais une option existe pour 'figer' le TCD et ne pas perdre les réglages de colonne (Ne réfléchissez pas, et appliquez !)
1. sitôt le TCD construit, cliquez dans le TCD et dans le Ruban, cherchez "Option, Option, Option !" pour atteindre Option du tableau croisé dynamique :-)
2. décochez Ajuster automatiquement la largeur de colonne lors de la mise à jour :-)
Voici 3 pistes pour gérer et contrôler l'apparence de TCD :
gérer le look du TCD
gérer en Tableau (plutôt qu'en rapport)
gérer les totaux et les Sous Totaux
Les TCD ne se mettent pas en forme, comme les tableaux simples, avec des fonds, des bordures, etc... Entièrement réalisés par XL, il est logique de confier aussi leur présentation à XL.
Pour celà, rien de plus simple :
1. aller dans le Ruban Création, et dans la section Styles de tableau, ouvrir le Menu...
2. ne reste qu'à choisir le modèle qui va le mieux (à votre TCD !
Sitôt cliqué, XL habille le TCD !
Lors de la construction, XL impose une présentation "Rapport' du TCD... Mais cette présentation devient difficile à lire si le TCD utilise plus de 2 champs, car XL ne distingue les champs que par un décalage...
Passer par Disposition du rapport pour tester les différentes options de présentations :
1. choisir Afficher sous forme Tabulaire
Présentation Rapport de TCD
(forme compactée dans le menu ci-dessus)
Dans cette forme, tous les champs sont imbriqués et hiérarchisés dans 1 seule colonne de la feuille de calcul XL...
Présentation Tableau
(Afficher sous forme Tabulaire, dans le menu ci-dessus)
Dans cette forme chaque champ est dans une colonne indépendante de la feuille de calcul...
Là encore il faut tester pour voir les options qui aident le plus à simplifier et clarifier la lecture et la compréhension du TCD...
Reprenons notre TCD exemple... auquel nous avons simplement appliqué un style, et mis sous forme Tabulaire...
Affiche (ou pas) les lignes & colonnes de totaux en bord du TCD
Affiche (ou pas) les lignes & colonnes de totaux en à l'intérieur du TCD
1. Sélectionner le tableau de base
2. lancer le TCD via les rubans (Ruban Outils de tableau OU Ruban Insertion)
3. choisir Nouvelle feuille (ou cliquer sur la feuille où l'insérer)
4. construite le TCD (dans exemple analyse des agents M et F par Direction)
FALCULTATIF MAIS FORTEMENT RECOMMANDE : gérer maintenant la protection des largeurs de colonnes via "Option, Option, Option !" du ruban Outils TCD...)
5. gérer la mise en forme du TCD
choix du style
choix Rapport TCD OU Tabulaire
Totaux et Sous totaux
Il s'agit d'un outil d'interface qui transforme un filtre en bouton et surtout permet de piloter l'affichage de TOUS les TCD d'un classeur !
Sans entrer dans les détails, découvrez ci-dessous les Segments... (essayez... testez...)
1. Dans le Ruban Outils du TCD, cliquer sur Insérer un segment (cocher la colonne dans la fenêtre...)
2 Dans le Segment qui apparaît, chaque choix possible est un bouton qui pilote le TCD. (le panneau segment est déplaçable, redimensionnable et dispose d'un Ruban qui lui est propre)
3. faire 1 clic-droit sur le Segment, et choisir Connexion de tableau croisé dynamique pour que le segment créée pilote plusieurs TCD (dans la fenêtre qui s'ouvre, cliquer sur les TCD à lier... Voir ci-dessous pour Nommer les TCD)
EXEMPLE : ci-dessous 2 TCD ont été créés sur la même feuille.
le TCD violet fait un décompte par Direction
le TCD rouge dresse la liste des noms des agents
si les 2 sont liés sur le même segment, 1 seul clic permettra :
Le décompte des agents Homme de la Direction dans le TCD violet (comme un filtre le ferait)...
l'affichage de la liste nominative dans le TCD rouge :-)
Partout dans XL il est recommandé de nommer ses plages ou tableaux... (Tableaux Magiques et TCD n'échappent pas à cette règle)
Pour nommer un TCD :
au début du Ruban Outils de TCD se trouve la zone où nommer : sélectionner et écrivez votre nom, tout simplement (il est conseillé de n'utiliser aucun espace, ni de caractères accentués, ni de ponctuation SAUF le "_ ")
Repérer vos TCD sera largement facilité lors de la connexion des TCD au segment... :-)