Le tableau croisé dynamique pour les nuls : c’est fini !
Ce nouvel article « le tableau croisé dynamique pour les nuls » vient en complément de l’article « Réussir son premier tableau croisé dynamique« . Après avoir lu l’article, vous saurez faire la différence entre les tableaux d’Excel simples et les Tableaux Croisés Dynamiques (TCD) !
Tableau croisé dynamique pour les nuls : Poser les bases
Excel est un tableur. Au départ, il a été conçu pour manipuler des données sous forme de tableaux : des lignes et des colonnes de valeurs.
Qui dit manipuler, dit faire des opérations avec ces données. On peut les compter, les additionner, les soustraire, etc.
Pour cet objectif, Excel propose toute une panoplie de fonctions (J’en ai dénombré 480 sur la page officielle de Microsoft). Avec toutes celles-ci, on pense qu’il doit bien exister une fonction pour chacun de nos besoins.
C’est justement sur ce dernier mot qu’il faut s’arrêter : nos besoins !
Définir ses besoins avant d’expliquer les tableaux croisés dynamiques (pour les nuls et pour les autres !)
Comme une image vaut mieux qu’un long discours, prenons un exemple de tableau. À partir de là, nous pourrons déterminer les différents besoins.
Voici un fichier exemple que vous pouvez récupérer sur le site de Microsoft.
TCD – Fichier exemple
Vous avez ici des chiffres de ventes par branche, par pays, par produit et par date. Avec toutes ces données, on peut imaginer un bon nombre d’analyses à réaliser…
Commençons par un besoin « simple »
Votre responsable vient vous voir et vous demande : « Peux-tu me faire le top 5 des pays où nous vendons le plus ? ».
À cet instant, votre cerveau commence à entrer en ébullition…
Alors d’abord, il faudrait que je fasse un tri par pays… Ensuite, il faudra que je fasse le total pour chacun d’entre eux. Après je pourrai le noter dans une liste et faire un tri sur le total.
Entre nous, cela fait déjà pas mal de manipulations à faire pour juste « un top 5 », vous ne trouvez pas ?
Excel ne recèlerait pas une fonction pour faire cela en quelques clics ? Vous me voyez venir ? Mais d’abord, continuons avec un besoin plus élaboré.
Et si la demande était plutôt… celle-ci
« J’aurais besoin de l’évolution des ventes par produit, par pays et par mois »
Là, vous vous dîtes que cela va certainement vous prendre des heures de construire manuellement ce type de tableau. Consolider tous les chiffres manuellement prendra du temps.
Même si des fonctions Excel existent pour vous aider à cela, à chaque fois, il faut un peu de temps pour construire votre tableau.
>> Je vous propose d’ailleurs d’essayer par vous-même de répondre à cette demande sans les tableaux croisés dynamiques. Ainsi même les « nuls » (je préfère quand même l’expression « à qui on n’a jamais expliqué comment ça fonctionne ») auront une solution dans cette situation.
Le tableau croisé dynamique aurait toutes les réponses ? (pour les nuls et pour les autres !)
Contrairement au tableau contenant les chiffres de ventes, un tableau croisé dynamique (TCD) ne permet pas de saisir de valeur. Un TCD a BESOIN d’un simple tableau de valeurs pour fonctionner. Il va puiser toutes ses données de ce dernier.
Un TCD sert à faire toutes les consolidations automatiquement. Le mot « dynamique » vient d’ailleurs de là. Derrière le mot « consolidation », il faut comprendre la liste des opérations ci-dessous :
Tableau croisé dynamique pour les nuls – Opérations de consolidation
Un autre nom, plus pompeux certes, mais peut-être plus clair, serait « tableau consolidant automatiquement les valeurs d’un autre tableau selon les axes sélectionnés par l’utilisateur ». Finalement, on va s’en tenir à TCD, OK ?
Passer au tableau croisé dynamique pour tous
Récapitulons ce que vous devez avoir appris jusqu’ici :
- Un TCD a besoin d’un tableau normal avec des données pour fonctionner.
- Un TCD n’est pas modifiable. Son contenu n’est que le résultat d’une consolidation des données du tableau source.
- Il faut établir la question à laquelle on souhaite répondre avant de construire un TCD.
Sur ce dernier point, il faut être clair. On ne peut avec un seul tableau répondre à deux questions différentes.
Un tableau croisé dynamique répond à une question. Deux questions = Deux TCD.
Charles Exceller
Déterminer le top 5 des pays où il y a eu le plus de ventse et présenter l’évolution des ventes par produit, par pays et par mois n’est pas la même chose.
Voici donc un nouvel apprentissage : vous devrez construire autant de TCD que vous avez de questions ! Mais, rassurez-vous, Excel sait faire cela parfaitement ! C’est la mémoire de votre ordinateur qui fera défaut avant Excel, je vous l’assure !
Passer de l’idée à l’action : Dessinez votre tableau croisé dynamique !
Avant de commencer à jouer avec l’objet dans Excel, prenez une feuille à côté de vous et représentez la réponse à votre question.
| Pays | Ventes |
|---|---|
| pays 1 | 1’234 € |
| pays 2 | 2’345 € |
| etc. | x’xxx € |
À partir de ce moment, vous êtes prêt à construire votre premier tableau croisé dynamique.
On pourra noter qu’en ligne se trouvent les pays, et les valeurs qui nous intéressent sont les sommes des ventes.
Deuxième question, deuxième représentation :
| Produit | Pays | Mois 1 | Mois 2 | Total |
|---|---|---|---|---|
| Article 1 | ||||
| Pays 1 | 12 € | 23 € | 35 € | |
| Pays 2 | 45 € | 56 € | 101 € | |
| … | … | … | … | |
| Article 2 | ||||
| … | … | … | … |
Première observation, ce tableau n’a rien à voir avec le premier. Vous comprenez donc aisément qu’un deuxième TCD est nécessaire pour le représenter.
Vous noterez les éléments suivants :
- Le nombre de lignes est déterminé par le nombre de produits puis par le nombre de pays.
- Les colonnes contiennent les mois.
- Les valeurs représentées sont les sommes des ventes (correspondantes au mois, au produit et au pays donnés).
En représentant ce tableau, vous pourriez vous rendre compte qu’il manque l’indication de l’année. Si vos données contiennent des valeurs sur plusieurs années, les ventes pourraient concerner tous les mois de janvier (toutes années confondues). Ce point doit rester à votre esprit. Attendons de voir comment Excel se comporte.
Créez votre tableau croisé dynamique
Maintenant que vous avez les idées claires sur votre tableau (ou vos tableaux) cible, vous pouvez prendre les rênes des TCDs.
Cliquez sur « Insertion » dans le ruban, puis cliquez sur « Tableau croisé dynamique ».
Tableau croisé dynamique pour les nuls – Insérer votre TCD
L’écran suivant apparaît alors :
TCD – Sélection des données
Plusieurs observations sur cet écran :
- Excel essaie de détecter automatiquement le tableau qui sera la source de votre TCD. Il peut être juste comme faux. Toujours vérifier.
- Sur votre feuille de données, la source de données se trouve encadrée par des pointillés verts (cf. zoom de la capture d’écran). C’est un autre moyen de vérifier que toutes vos données sont correctement sélectionnées.
- Par défaut, Excel insérera votre TCD sur une nouvelle feuille de calcul. Ne soyez pas surpris.
TCD – Composition de votre feuille
Votre nouvelle feuille se composera de trois zones :
- À cet emplacement, se trouvera votre tableau résultat. Cette zone ne servira qu’à quelques actions limitées (filtrer et trier).
- Ici, vous verrez l’ensemble des colonnes de votre tableau source. Ce sont celles que vous pourrez utiliser pour construire votre TCD. Si une colonne manque à l’appel, vérifier à nouveau votre source, contient-elle bien toutes les colonnes ?
- La clé de voûte de votre TCD se trouve ici. Vous construirez ici le contenu des lignes, des colonnes ou des valeurs de votre TCD.
Répondre à notre première question : le top 5 des pays par volume de ventes
Si vous remontez de quelques paragraphes, lorsque je vous ai demandé de dessiner votre tableau cible, j’ai décrit ce tableau d’une manière particulière. J’ai mis l’accent sur les données qui se trouvaient en lignes, en colonnes ou en valeurs.
C’était une manière d’introduire ce qu’il vous reste à faire. Glissez-déposez simplement les colonnes de votre tableau source (en 2) vers les emplacements correspondants (en 3).
Vous obtiendrez pour la première question (le top 5 des pays) le tableau suivant :
TCD – Première réponse
Nous avons une petite chance ici, car il n’y a que 5 pays dans la source de données, sinon, quelques clics supplémentaires auraient été nécessaires… 😉
Par ailleurs, j’ai dû mettre en forme les valeurs sous forme monétaires (onglet Accueil, format nombre comptabilité). Souvent, Excel saura reconnaître le bon format des données.
Répondre à notre deuxième question : Les chiffres de ventes des produits, par pays et par mois.
Comme annoncé, vous aurez à insérer un nouveau TCD. Après l’avoir fait, vous aurez à réaliser les opérations suivantes :
- Glissez-déposez d’abord les produits, puis les pays en lignes.
- Passez ensuite aux ventes vers les valeurs (Sales)
- En dernier, placez la date en colonnes.
Vous verrez alors le résultat suivant :
TCD – Deuxième réponse
Notez qu’en 4, Excel a reconnu automatiquement que la colonne « Date » contenait effectivement des données de dates (Excel ne se fie qu’au contenu, pas au nom…). Et réalise automatiquement des regroupements par année et par trimestre !
En plus, votre tableau contient des sous-totaux intermédiaires et les totaux par lignes et par colonnes ! Que demander de plus ! Tous ces résultats consolidés en quelques clics…
Merci les tableaux croisés dynamiques ! Pour ceux qui le souhaitent, vous pouvez télécharger le fichier résultat ou encore continuer votre lecture avec « Réussir son premier tableau croisé dynamique« .
Je vous présente également dans cette vidéo un autre exemple d’utilisation des tableaux croisés dynamiques avec les données sur le Covid-19 mis à disposition par le gouvernement via https://data.gouv.fr.
https://www.youtube.com/watch?v=H0iao5mAtcs
C’est exactement comme pour les 
Pour réaliser ce tri manuel, il suffit de sélectionner le champ que l’on souhaite déplacer (ici, le type “NA”). Lorsqu’on le déplace, soudain, Excel montre une barre verte et montre que c’est un ensemble qui va être déplacé et pas uniquement un champ.
