in

Le Tableau Croisé Dynamique Enfin Démystifié [Guide Pratique]

tableau croisé dynamique

Pour quelqu’un qui n’est pas très à l’aise avec Excel, un tableau croisé dynamique reste un grand mystère réservé à un cercle fermé d’experts. 

Pourtant, il s’agit de l’une des fonctionnalités Excel les plus faciles à paramétrer.

Et le plus difficile reste à pouvoir jongler avec les données pour obtenir un tableau dynamique fonctionnel et représentatif. 

Quelle Est L’Utilité D’Un Tableau Croisé Dynamique ?

Extraire des données spécifiques manuellement à partir d’un tableau de bord reste non seulement fastidieux,  pas pratique mais surtout une source indéniable d’erreurs.

C'est là qu'on comprend tout l’intérêt du tableau croisé dynamique. 

Cette fonctionnalité nous offre une interface très pratique pour créer facilement des tableaux sur mesure permettant d’extraire, synthétiser et  interpréter des données d'une façon pertinente et efficace.

Comment Créer Un Tableau Croisé Dynamique ?

Tout au long de ce tuto, nous allons utiliser le Classeur suivant qui contient un tableau de bord de suivi de commandes :

Fichier Excel Pour Tableau Croisé Dynamique

Ce fichier contient beaucoup de données à partir desquelles nous allons créer un tableau croisé dynamique.

tableau croisé dynamique

Vérifier La Validité des données à utiliser

Afin de pouvoir créer un tableau croisé dynamique, notre tableau source doit respecter certaines exigences :

  • Il ne doit pas y avoir de colonnes, lignes ou cellules vides.
  • Toutes les colonnes doivent avoir des entêtes uniques et valides (pas trop longues).
  • Les cellules des entêtes ne doivent pas être fusionnées.

Si vous avez dans votre tableau des cases vides, il est très simples de les localiser.

Voici ce qu'il faut faire :

  • Sélectionner tout le tableau.
  • Cliquer sur l'Onglet "Rechercher et sélectionner" du menu "Accueil".
  • Choisir l'option "Atteindre".
tableau croisé dynamique
  • Sur la nouvelle fenêtre qui s'affiche, Cliquer sur le bouton "Cellules":
tableau croisé dynamique
  • Cocher la case "Cellules vides" pour demander à Excel de repérer les cellules vides du tableau:
tableau croisé dynamique

Les cellules vides apparaitront en surbrillance.

tableau croisé dynamique

Il suffit de les remplir même avec des valeurs symboliques sans importance juste pour ne pas les laisser vides. 

Création du TCD (Tableau Croisé Dynamique)

Pour créer un tableau croisé dynamique, vérification faite de la validité des données, on procède de cette manière :

  •  Sélectionner le tableau source.
  • Cliquer sur l'onglet "Insertion".
  • Choisir "Tableau croisé dynamique".
  • Cliquer sur l'option "tableau croisé dynamique".
tableau croisé dynamique

Sur la nouvelle fenêtre, vous avez la possibilité de redéfinir la sélection en cas de besoin (si vous voulez utiliser uniquement une plage ou bien une partie du tableau) grâce à l'option "Sélectionner les données à analyser".

Vous pouvez aussi choisir l'emplacement du TCD :

  • Soit vous le créer une nouvelle feuille de calcul (c'est l'option recommandée).
  • Soit vous le créer sur une feuille de calcul existante. (Vous devrez indiquer son emplacement).
tableau croisé dynamique

Le tableau croisé dynamique apparaîtra sur une nouvelle feuille du classeur (si vous avez choisi de le placer sur une nouvelle feuille).

Au début le tableau n'affichera pas de valeur puisque nous n'avons pas encore choisi les champs concernés.

Ces champs apparaissent à votre droite : 

tableau croisé dynamique

Comment Analyser Les Données D’Un TCD

Supposons qu’on ait besoin d’optimiser les frais de livraison. (en utilisant toujours le même exemple qui est en téléchargement au début de ce tuto)

Pour cela, nous allons commencer par analyser la tendance des choix du mode de livraison sur trois années.

Les champs dont nécessaires sont : 

  • Année.
  • Référence de la commande.
  • Canal de livraison.

Les champs doivent être disposés dans l'ordre suivant :

Année : Filtre du rapport.

Canal de livraison : Etiquettes de lignes.

Référence de la commande : Somme des valeurs.

Voici comment faire :

  • Cocher les éléments nécessaires dans la liste des champs du tableau.
tableau croisé dynamique
  • Les champs seront affichés dans les zones carrées juste en dessous de la liste.
tableau croisé dynamique
  • La disposition des champs n'est pas celle qui nous permettra d'afficher les valeurs comme nous le voulons. La somme doit figurer comme étant un critère de filtre. Il suffit de "Glisser" le champ "Somme des années" avec la souris et le mettre dans la case "Filtre".
tableau croisé dynamique

En premier temps, voici comment le TCD devrait paraître :

tableau croisé dynamique

Les consignes à suivre pour parfaire la présentation et l'analyse des données sont :

Pour le champ "Référence de la commande" :

Nous n'avons pas besoin de la somme des valeurs mais plutôt du nombre des valeurs.

Nous devons donc changer la formule de la façon suivante :

  • Cliquer sur le champ "Référence de la commande"
  • Choisir l'option "paramètres des champs de valeurs"
tableau croisé dynamique

Nous devons choisir le type de calcul souhaité pour notre champ, soit "Nombre" : 

tableau croisé dynamique

Le champ apparaît avec la nouvelle formule comme ceci :

tableau croisé dynamique

Pour avoir au final le tableau croisé dynamique suivant :

tableau croisé dynamique

Je vais par la suite vous montrer comment filtrer et analyser les données du tableau créé.

Filtrer Les Données D’Un Tableau Croisé Dynamique

Une fois le TCD généré, il suffit de savoir comment utiliser les filtres pour afficher les résultats attendus.

Si par exemple, nous voulons suivre année par année la tendance des livraisons, voilà ce qu'il faut faire :

  • Cliquer sur le filtre de la ligne "Année".
  • Sélectionner l'année correspondante.
  • Cocher la case "Sélectionner plusieurs éléments"
  • Valider.
tableau croisé dynamique

Pour l’année 2016 les modes de livraison se partagent comme suit :

tableau croisé dynamique

Nous pouvons aussi suivre la tendance globale des modes de livraison sur toutes les années et cela par type de livraison comme suit :

  • Sélectionner le filtre "Tous" dans la ligne "Année"
  • Choisir la valeur "Livraison interne" dans le filtre des "Etiquettes de lignes".
tableau croisé dynamique

Nous obtenons ainsi des résultats beaucoup plus pratique à analyser et à interpréter :

  • Pour les livraisons internes :
tableau croisé dynamique
  • Pour les livraisons clients :
tableau croisé dynamique
  • Pour les prestataires indépendants :
tableau croisé dynamique

Modifier L’Ordre D’Un Tableau Croisé Dynamique

Lorsqu’on nous avons plusieurs critères par zone, nous pouvons avoir besoin de changer l’ordre du TCD fin optimiser l’affichage des résultats.

Toujours avec le même exemple, nous allons modifier l'ordre de notre tableau.

Nous voulons afficher une corrélation en ajoutant le champ "Client" comme critère de sélection.

Pour cela, commençons par ajouter le champ "client" sous la zone "Etiquettes des lignes" de la manière suivante :

  • Cocher la case du champ "Client" dans la liste des champs.
  • Le champ s'ajoute directement et le résultat est immédiat.
tableau croisé dynamique

L'affichage n'est pas suffisamment clair puisque nous voulons afficher les livraisons pour chaque client et non l'inverse : 

tableau croisé dynamique

Avant la modification de l'ordre des champs

tableau croisé dynamique

Après la modification de l'ordre des champs

Donc, pour changer l'ordre du champ "Client" :
  • Glisser le champ" Client" avec la souris juste au-dessus du champ "Canal de livraison".
tableau croisé dynamique

Actualiser Les Données D’Un Tableau Croisé Dynamique

Comme son nom l’indique, le tableau croisé est dynamique.

Ce qui suggère que nous pouvons l’actualiser suite à des mises à jour du tableau de bord source.

Si par exemple, nous ajoutons au tableau de base les données de l’année 2015 :

tableau croisé dynamique

Pour actualiser notre TCD, on procède comme suit :

  • Cliquer sur le menu "Options"
  • Choisir l'onglet "Actualiser".
  • Appuyer sur "Actualiser tout".
tableau croisé dynamique

Les données de l’année 2015 s’ajoutent instantanément au tableau croisé dynamique :

tableau croisé dynamique

Supprimer Des Données D’Un Tableau Croisé Dynamique

S’il s’avère que l’une des données du tableau croisé dynamique n’est pas ou n’est plus pertinente.

Ou que l'on veuille tout simplement se focaliser sur certaines données seulement.

Il suffit de supprimer le ou les champs dont nous n'avons plus besoin.

La procédure est simple à réaliser :

  • Cliquer sur le champ à supprimer.
  • Dans le menu qui s'affiche, appuyer sur "Supprimer le champ".
tableau croisé dynamique

Le rapport synthétisé apparaît donc comme ceci :

tableau croisé dynamique

Mise En Forme D’Un Tableau Croisé Dynamique

Pour optimiser l’usage et la visibilité de notre TCD, Excel nous offre plusieurs possibilités de mise en forme.

Il suffit de cliquer sur l’onglet « Création » et de paramétrer plusieurs critères tel que la disposition du rapport, le style du tableau, l’affichage des lignes et l’affichage des totaux et sous totaux.

Voici comment faire en détail :

Changer La Présentation D’Un TCD

Pour changer la disposition du rapport :

  • Cliquer sur le menu "Création".
  • Choisir l'onglet "Disposition du rapport".
  • Cliquer sur "Afficher en mode plan".
tableau croisé dynamique

Pour aérer l'affichage des données sur le tableau, nous pouvons insérer des lignes vides après chaque élément comme suit :

  • Dans le menu "Création" cliquer sur l'onglet "Lignes vides".
  • Appuyer ensuite sur "Insérer un saut de ligne après chaque élément"
tableau croisé dynamique

Nous obtenons ainsi l’affichage suivant:

tableau croisé dynamique

Toujours dans un souci de clarté, nous pouvons également, afficher des bandes alternées sur certaines lignes pour faciliter la lecture de notre tableau :

  • Dans le menu "Création" cocher la case "Lignes à bandes" 

Modifier Les Titres

Le choix des titres a aussi un impact important sur la visibilité et la lecture du rapport.

Il faut opter pour des titres pertinents et qui ne sont pas trop long.

Pour changer les titres, voici ce qu'il faut faire : 

  • Cliquer sur le champ concerné dans la zone des champs affichés dans le TCD.
  • Choisir l'option "Paramètres des champs de valeurs.
  • Dans la fenêtre qui s'affiche, changer le nom dans la zone "Nom personnalisé"

De la même façon, vous pouvez remplacer tous les autres titres par d'autres plus clairs et plus pratiques :

  • « Nombre de référence de la commande » par « Nombre de commandes »
  • « Canal de livraison » par « Mode de livraison »
  • « Client » par « Nom du client »

Appliquer Un Style

La touche finale pour la mise en forme de notre tableau, consiste à choisir le style le plus approprié.

Pour cela :

  • Sélectionner une ligne du tableau croisé dynamique.
  • Dans le menu "Création" choisir un style de ceux proposés.

Il est possible d'appliquer n'importe quel style de la liste :

Personnaliser L’Affichage (Pourcentage Et Groupement De données)

Une autre fonctionnalité attrayante de la mise en forme d'un tableau dynamique est la personnalisation de l’affichage sous forme de pourcentage par groupement de données.

Dans notre exemple, il serait logique et pertinent de regrouper tous les prestataires étant donné qu’il utilise finalement le même mode de livraison. 

On commence donc par sélectionner les données comme suit :

Il existe deux façons pour grouper les données.

Soit en cliquant sur le bouton droit, soit en allant sur l’onglet « Options » :

Les données groupées apparaissent avec la disposition suivante :

Chaque ensemble de données appartient au même groupe.

Excel attribue à chaque groupe le nom par défaut "Groupe 1", "Groupe 2", ...etc.

Il est possible de changer le nom d'un groupe de donnée de la façon suivante :

  • Sélectionner la ligne du titre du groupe.
  • Changer le nom en écrivant le nouveau dans la barre d'affichage.

Pour notre exemple, nous allons changer le nom "Groupe 1" en "Prestataires de livraison" :

Une fois toutes les données groupées, il serait intéressant de pouvoir les afficher sous forme de pourcentage pour une interprétation plus pratique et significative.

Pour y arriver, il faut passer par deux étapes.

La première consiste à afficher le nombre de prestataires de livraison en utilisant la manière suivante :

  • Cliquer sur le champ concerné dans la zone des champs présents dans le TCD.
  • Appuyer ensuite sur "Paramètres de champ".
  • Cocher "Personnalisé" et choisir "Nombre" pour modifier l'affichage en nombre.

L'affichage se modifie en nombre de prestataires :

A présent, pour afficher ces nombres sous forme de pourcentages, on procède comme suit :

  • Sélectionner la cellule puis choisir l'option « Paramètres des champs de valeurs »  du menu contextuel (celui du bouton droit de la souris).
  • Dans la fenêtre qui s'affiche, cliquer sur l'onglet « Afficher les valeurs ». 
  • Choisir ensuite « % du total » afin de pouvoir afficher le pourcentage de chaque mode de livraison. 

Nous obtenons ainsi les pourcentages selon les modes de livraison :

Supprimer Un Tableau Croisé Dynamique

Pour supprimer un tableau croisé dynamique :

  • Sélectionner une cellule du tableau.
  • Cliquer sur l'onglet "Options" ou "Actions" (Selon la version d'Excel utilisée).
  • Cliquer ensuite sur "Effacer" et enfin sur "Effacer tout".

Quelques Options Pratiques 

Pour parfaire et faciliter la manipulation d'un tableau croisé dynamique, Excel met à notre disposition des fonctions supplémentaires.

Elles sont accessibles grâce à l'onglet "Options" (ou "Actions") du menu "Outils de tableau croisé dynamique. 

Déplacer un TCD

Cette fonction permet de déplacer un tableau facilement.

Soit sur une nouvelle feuille de calcul, soit sur une feuille existante.

Il suffit de cliquer sur « Déplacer le tableau croisé dynamique » puis choisir le nouvel emplacement :

tableau croisé dynamique

Créer Un Graphique Croisé Dynamique

Une autre fonctionnalité à la fois pratique et intéressante qui est la possibilité de créer directement un graphique à partir de notre tableau croisé dynamique.

C'est possible grâce à la fonction « Graphique croisé dynamique » :

  • Cliquer sur l'onglet "Options" (ou "Analyse") du menu "Outils de tableau croisé dynamique". 
  • Cliquer ensuite sur "Graphique croisé dynamique".
  • Choisir le graphique qui convient à votre analyse.
tableau croisé dynamique

Voici le résultat :

tableau croisé dynamique

Pour pousser plus loin la personnalisation du graphique, reportez-vous à notre tutoriel complet : Voulez-Vous Maîtriser Le Graphique Excel Maintenant?

Changer La Source De Données

Si nous avons besoin de garder tous les critères qu’on a paramétré (mise en forme, affichage...etc) sur un tableau croisé dynamique et que nous voulons juste changer une des source de données, voici ce qu'il faut faire :

  • Dans le menu "Outils de tableau croisé dynamique", cliquer sur l'onglet "Options" (ou "Analyse").
  • Cliquer ensuite sur "Changer la source de données".
  • Spécifier le nouveau tableau source.
tableau croisé dynamique

Il est possible de sélectionner un autre tableau ou juste une plage d'un tableau :

tableau croisé dynamique

En sélectionnant juste une plage du tableau source, nous obtenons un TCD avec juste les critères sélectionnés.

Il est nécessaire de redéfinir les champs sur les zones de la même manière :

tableau croisé dynamique

Conclusion

Pour conclure, un tableau croisé dynamique s’avère facile à créer et à manipuler.

Il suffit de connaître toutes les possibilités que nous offre l’interface d'Excel afin de pouvoir optimiser le temps, l’affichage et l’interprétation de toutes les données.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

tableau-Excel

Voici À Quoi Doit Ressembler Votre Tableau Excel [Tutoriel]

Créer Un Calendrier Excel : Le Seul Guide À Mettre Dans Ses Favoris