Tutoriels > Tutoriels & Astuces Excel > Tableaux Croisés Dyn. > Mon Premier Tableau Croisé Dynamique avec Excel 2007

Mon Premier Tableau Croisé Dynamique avec Excel 2007

Publié par Mth le 28-03-2010 (132803 lectures)

Le Tableau Croisé Dynamique (TCD) est un outil très puissant permettant de synthétiser et analyser une base de données très rapidement.

Ce premier tutoriel vous présente la création d'un TCD et les premières manipulations de base; d'autres étapes comme la mise en page, les tris, les champs calculés, les regroupements de données et autres sujets seront abordés dans d'autres articles consacrés aux tableaux croisés dynamiques.

Pour nos exemples, nous utiliserons une base de données comme ci-dessous regroupant les ventes en quantité et en chiffre d'affaires sur deux ans, par produits/clients; et nous verrons comment synthétiser cette base en quelques clics:

base2_765

 



panneau alerte A noter:

Point bleu Il est important d'avoir une base propre et bien structurée pour pouvoir en exploiter les données correctement.


Point bleu La première ligne de la base de données contient les noms des champs, elle ne doit pas contenir de cellule vide, et deux champs ne doivent pas porter le même nom.


Point bleu Les lignes suivantes représentent tous les enregistrements de la base, il n'est pas recommandé de laisser des lignes vides au sein de cette base.


Point bleu Les enregistrements doivent être bruts, il ne doit pas y avoir de totaux ou sous-totaux au niveau des lignes;  en colonnes en revanche il est possible d'avoir des champs intermédiaires que vous souhaitez  calculer avant de construire votre TCD.


Point bleu Veillez à la qualité des données dans les différents champs; les champs de nombres contiennent des nombres et pas de texte, les champs de textes contiennent du texte, les champs de date contiennent des dates.


 

Etape 1: Sélection de la plage de données

Sélectionnez une cellule quelconque de la base de données, puis cliquez sur l'onglet "Insertion"; dans le groupe "Tableaux" à gauche choisissez "Tableau croisé dynamique":

menu Insertion d'un TCD


La fenêtre suivante apparaît, Excel a déterminé tout seul la plage de données en sélectionnant l'ensemble de notre base qui s'étend de la cellule A1 à la cellule J36, et propose par défaut de placer le TCD dans une nouvelle feuille qui sera créée pour l'occasion:

Plage de données d'un TCD

Excel à sélectionné par défaut toutes les cellules contigües à la cellules préalablement sélectionnée, mais vous pouvez préférer sélectionner vous même votre plage de données, ne serait-ce que pour vérifier visuellement votre sélection.

Ainsi, si les derniers ou premiers champs à droite ou à gauche de votre base ne sont pas utilisés pour vos analyses, il est inutile de les inclure dans la plage de données, vous gagnerez ainsi de l'espace mémoire.

Vous pouvez également  envisager d'ajouter ultérieurement des enregistrements à votre base, et choisir volontairement une plage contenant plus de lignes, votre plage s'étendant par exemple de la cellule A1 à la cellule J500:

Plage de données d'un TCD

Nous verrons ultérieurement qu'il y a une meilleure solution pour définir une plage, nous pouvons aussi utiliser une source de données externe ou choisir nous même l'emplacement du TCD, mais restons en là pour l'instant et cliquons sur OK pour continuer.

 

Etape 2: construction du TCD

Vous êtes maintenant sur le nouvel onglet qu'Excel vient de créer, on remarque à gauche le TCD vide, tel qu'il apparaît avant que les champs soient définis; à droite la liste des champs, en haut deux onglets Options et Création offrant de nombreux outils spécifiques aux TCD:
Menus Outils d'un TCD


icone info A noter

Lorsque l'on clique en dehors de ces zones, le menu Outils de TCD et la liste des champs disparaissent:
 

Menu et Liste de champs d'un TCD

Il suffit de sélectionner à nouveau le TCD pour les faire réapparaître.

Si vous avez fermé la fenêtre "Liste des champs" pour avoir plus de place à l'écran, elle ne réapparaît pas en cliquant sur le TCD, il faut cliquer sur le bouton Liste de champs de l'onglet Options du menu Outils de TCD:

Afficher la fenêtre Listre de champs d'un TCD

C'est à partir de la fenêtre Liste des champs que nous allons construire le TCD, en sélectionnant et déplaçant les champs qui nous intéressent.

La liste des champs contient deux menus permettant de personnaliser la fenêtre, de la déplacer ou la redimensionner:

Fenêtre liste de champs d'un TCD

Nous conservons volontairement ici la présentation standard d'Excel.

Commençons par un problème simple, nous voulons comparer le chiffre d'affaires An et An-1 par Représentant et Type d'enseigne, et souhaitons pouvoir afficher les chiffres pour l'ensemble de l'activité ou pour certaines villes.

Nous allons construire un tableau comme celui-ci:

Mon premier TCD avec Excel 2007

Une première méthode consiste à cocher dans la liste des champs, ceux qui nous intéressent (zone 1 ci-dessous)

Au fur et à mesure de nos clics, Excel renseigne les zones inférieures de la fenêtre "Liste de champs" et construit le TCD que nous voyons évoluer à chaque étape sur la gauche.

Une fois nos cinq champs sélectionnés nous avons ce résultat à l'écran:

Liste de champs d'un TCD

Excel a su placer tout seul certaines données au bon endroit, on retrouve le type d'enseigne et le représentant en étiquette de ligne (zone 4 ci-dessus et en en-tête de ligne dans le TCD), nous n'avons pas d'étiquette de colonne et  retrouvons bien les noms des champs de Chiffre d'Affaires dont nous voulons faire la somme.

En revanche, Excel ne pouvait pas deviner que nous souhaitions la ville non pas en étiquette de ligne ,mais en filtre au dessus du tableau.

Nous pouvons alors cliquer sur le champs Ville, et tout en maintenant le bouton gauche de la souris enfoncé, le faire glisser des Etiquettes de ligne vers l'emplacement Filtre du rapport, le TCD se modifie immédiatement en plaçant le champ ville en première ligne du TCD:
Liste de champs d'un TCD

Cette méthode "automatique", en ne faisant que cliquer sur les champs qui nous intéressent ,ne convient généralement que pour les cas très simples, il est globalement préférable d'utiliser la seconde méthode, qui consiste à sélectionner les champs et à les faire glisser en maintenant le bouton gauche de la souris enfoncé dans les zones appropriées:

Liste de champs d'un TCD

Les étiquettes de lignes correspondent aux en-têtes de ligne à gauche du TCD, les étiquettes de colonnes aux en-têtes de colonnes du TCD (nous n'en avons pas ici), les valeurs sont les données numériques que nous voulons synthétiser (ici les chiffres d'affaires dont nous voulons la somme), les filtres sont les anciens "Champs de page" des versions antérieures d'Excel, et permettent d'afficher ou filtrer certaines données de la base.

Pour supprimer un champ, il suffit de le sélectionner et de la faire glisser à l'extérieur de la fenêtre:
Liste de champs d'un TCD
Pour modifier l'ordre des champs, il suffit de les déplacer en les faisant glisser dans la fenêtre. Par exemple nous souhaitons voir le champ An-1 avant le champ An, et préférons un sous-total par type d'enseigne puis représentant au lieu de représentant / type d'enseigne; nous modifions l'ordre des champs dans la fenêtre et le TCD se modifie en temps réel:
Liste de champs d'un TCD
Si nous voulons voir le représentant en étiquette de colonne et ne voir que le chiffre d'affaire de l'année N, il suffit de faire glisser le champ représentant de la zone "Etiquette de ligne" vers la zone "Etiquette de colonne", puis de faire glisser le champ CA An-1 hors de la fenêtre, nous voyons immédiatement le TCD évoluer:
Liste de champs d'un TCD


Etape 3: Première mise en forme:

Notre tableau est construit, nous avons obtenu la synthèse ci-dessous, mais ce tableau manque un peu de mise en forme:
Mon 1er TCD avec Excel 2007
Dans la fenêtre Liste de champs, chaque champ comporte une petite flèche noire à coté de son nom, cette flèche permet d'ouvrir la fenêtre de gestion des paramètres.

Mise en forme des champs de valeur:

Commençons par les nombres en cliquant sur la petite flèche à côté du nom "Somme CA An-1":

Mise en forme d'un tableau croisé dynamique
La fenêtre suivante de gauche s'affiche, cliquez sur la dernière ligne "Paramètres des champs de valeur", la fenêtre de paramètres s'ouvre:

Mise en forme d'un tableau croisé dynamique

Cette petite fenêtre de paramètres est très riche, elle permet :

A De modifier le nom du champ dans le TCD, pour peu que le nom de la base ne soit pas clair ou trop long vous pouvez en choisir un autre,

B De choisir le format de nombre, en cliquant sur le bouton "Format de nombre" vous retrouvez la fenêtre classique d'Excel de "Format de cellule".

C De choisir le calcul effectué dans un champ, ce choix s'effectue dans l'onglet "Synthèse par"; ici nous avons laissé "Somme", mais nous aurions afficher le nombre de lignes de CA en choisissant "Nombre" au lieu de "Somme", calculer la Moyenne etc. , il suffit de faire défiler l'ascenseur pour choisir le calcul désiré.
Mise en forme d'un tableau croisé dynamique

D Dans l'onglet "Afficher les valeurs", vous pouvez choisir d'afficher les valeurs normales, ou un pourcentage par rapport au total, ou par ligne, ou une valeur cumulée, etc. Dans notre exemple nous souhaitons conserver l'affichage des valeurs normales, mais si nous souhaitions avoir le poids de chaque ligne par rapport au total, nous aurions choisi % du total et Excel aurait immédiatement affiché le résultat comme ci-dessous:
Mise en forme d'un tableau croisé dynamique

Ces calculs prédéfinis dans Excel sont très puissants et méritent un chapitre spécial à eux seuls. Nous ne développerons donc pas plus ici, mais vous pouvez consulter sur le site le fichier créé par notre amie Celeda : Passeport pourcentage qui fourmille d'exemples particulièrement intéressants pour vous permettre de découvrir la richesse de ces calculs.

icone info Pour mémoire, la fenêtre de paramètres des champs est accessible via la petite flèche noire de la fenêtre champs de valeurs, mais également par un clic droit sur le champs du TCD:
Mise en forme d'un tableau croisé dynamique

Paramètres des champs étiquettes de lignes et de colonnes:

On accède au paramétrage de la même façon, soit en cliquant sur la petite flèche noire dans la fenêtre Liste des champs, soit par clic droit de la souris sur le champ directement dans le TCD. Excel reconnaît automatiquement le type de champ et propose la fenêtre de paramètres adaptée.


Le premier onglet "Sous-totaux et filtres" permet de préciser le type de sous-total du champ sélectionné.  Automatique correspond généralement à une somme, Aucun retire l'affichage des sous-totaux comme ci-dessous, Personnalisé permet de choisir le type d'opération. Pour l'exemple nous avons affiché ci-dessous un calcul de moyenne ( mais dans ce cas il est recommandé de mentionner cette information quelque part pour qu'il n'y ait pas de confusion possible à la lecture du tableau).

Mise en forme d'un tableau croisé dynamique

Le second onglet Disposition et impression permet de gérer ces deux paramètres:
 
Mise en forme d'un tableau croisé dynamique

mais nous ne nous étendrons pas sur cet onglet car ces points peuvent être gérés bien plus facilement en utilisant l'onglet "Création" du menu Outils de tableau croisé dynamique.


Afin d'alléger le tableau, vous pouvez également supprimer l'affichage des en-têtes de champs en cliquant sur le bouton "En-têtes de champs" du groupe "Afficher/Masquer" de l'onglet Options du menu Outils de tableau croisé dynamique:
entetechampssupprsm_708

Utilisation de l'onglet Création:

Tableau croisé dynamique - Onglet Création


Point bleu  Parmi les trois groupe de l'onglet création le groupe "Style de tableau croisé dynamique" est assez spectaculaire.
Cliquez sur un des styles affiché dans le ruban, ou sur la flèche inférieure droite du groupe pour afficher tous les styles prédéfinis:
Tableau croisé dynamique - Onglet Création

Une palette de styles s'affiche, il suffit de promener la souris sur les différents modèles pour visualiser en temps réels l'aspect final de votre tableau:
Tableau croisé dynamique - Onglet Création

Tout en bas de cette fenêtre, vous avez même la possibilité de créer votre propre style de tableau croisé dynamique en précisant le format de chaque en-tête, chaque sous-total, etc.
Tableau croisé dynamique - Onglet Création
mais compte tenu des multiples possibilités nous n'aborderons pas ce point dans ce tutoriel, la palette de styles prédéfinis étant suffisante pour un première approche des tableaux croisés dynamiques.
 
Point bleu  Le groupe Options de style de tableau croisé dynamique et le groupe "Style" sont étroitement liés.
Ce groupe présente quatre options:
Tableau croisé dynamique - Onglet Création
Pour les en-têtes de lignes Excel met en valeur les éléments du TCD, ou pas, selon que les cases soient cochées ou pas (les styles prédéfinis de l'onglet "Styles" s'adaptent également en fonction de ces options) 
Tableau croisé dynamique - Onglet Création

Il en est de même pour les lignes à bandes ou colonnes à bandes; en cochant ces options Excel alterne les couleurs claires et plus foncées une ligne sur 2 ou une colonne sur deux (selon le style choisi, pour les colonnes à bandes, au lieu d'alterner les couleurs Excel insère un trait de séparation entre les colonnes)
Tableau croisé dynamique - onglet création

Point bleu  Le groupe Disposition permet de gérer d'une part l'affichage des totaux et sous-totaux:
 
Disposition du Tableau croisé dynamique

Il permet également d'insérer ou pas des lignes entre chaque groupe d'élément pour faciliter la lecture des tableaux denses :

Disposition du Tableau croisé dynamique
Reste bien sûr à choisir des options compatibles, par exemple vous ne pourrez pas afficher  les sous-totaux en haut des groupes en mode tabulaire puisqu'il n'y a pas de ligne d'accueil, Excel vous proposera dans ce cas d'afficher les sous-totaux en bas des groupes.


Trait bleu

Votre premier tableau croisé dynamique est maintenant terminé:

Mon premier tableau croisé dynamique

Premières manipulations:


Point bleu Vous pouvez utiliser le champ filtre du rapport pour n'afficher que certaines données, par exemple pour n'afficher que les données concernant Paris, cliquez sur la petite flèche du champ Ville, la liste de toutes les valeurs disponibles s'affiche, cliquez sur Paris puis OK, votre TCD affiche la synthèse des données concernant Paris et signale la présence du filtre en haut du tableau:
Tableau croisé dynamique - Champ Filtre
Vous pouvez également sélectionner plusieurs éléments, en cochant la case "Sélectionner plusieurs éléments", puis décochez "Tous" et cochez les éléments de votre choix:

Tableau croisé dynamique - Champ Filtre

L'intitulé du filtre étant un peu large par rapport aux données de la colonne B, vous pouvez modifier le format de la cellule B1 en la sélectionnant, puis clic droit de la souris, choisissez le menu format et sélectionnez "Renvoyer à la ligne automatiquement" dans l'onglet "Alignement":
 
Tableau croisé dynamique - Champ Filtre

Point bleu Vous pouvez également n'afficher le détail des lignes que sur certains éléments, par exemple pour ne pas afficher le détail concernant monsieur Dupont, cliquez sur le petit signe - "moins" à coté du nom. Les lignes de détail ne s'affichent pas, et le signe - "moins" s'est transformé en signe + "plus" pour vous signaler la présence de lignes de détail que vous pourrez consulter en cas de besoin:
 
Tableau croisé dynamique - Afficher ou masquer le détail

Trait bleu

Afin de vous entraîner à construire votre premier TCD, vous pouvez télécharger le fichier ayant servi de base à ce tutoriel et suivre les étapes pas à pas.
 

Téléchargez le fichier !

Pour toutes vos questions ou si vous rencontrez des difficultés, n'hésitez pas à rejoindre nos Forums de Discussions !

Tags TCD   Dynamique  

 

Autres articles dans cette catégorie Publié le Vues
TCD - Les Options d'un Tableau Croisé Dynamique 30-12-2012 100012
TCD - Trier un Tableau Croisé Dynamique 30-12-2012 83437
TCD - Les Filtres: Textes, Dates ou Valeurs 03-09-2012 63341
TCD - Les Filtres : Généralités 03-09-2012 47748
TCD - Les Filtres : Le filtre du Rapport 03-09-2012 31158
TCD - Extraire les données avec LIREDONNEESTABCROISDYNAMIQUE() 02-07-2012 120679
TCD - Grouper des champs Date dans un Tableau Croisé Dynamique 18-03-2012 131915
TCD - Grouper des champs numériques dans un Tableau Croisé Dynamique 18-03-2012 61308
TCD - Grouper ou Dégrouper les éléments d'un Tableau Croisé dynamique 18-03-2012 45130
TCD - Effectuer deux calculs différents sur un même champ 03-01-2012 38953
TCD - Actualiser un TCD 16-04-2011 68260
TCD - Source de données dynamique 17-10-2010 134727
TCD - Faire glisser / déplacer les champs 20-06-2010 56344
TCD - Créer un élément calculé 25-05-2010 65486
TCD - Intégrer un champ calculé 14-05-2010 223346
Mon Premier Tableau Croisé Dynamique avec Excel 2007 28-03-2010 132804
Comment reconstituer une base à partir d'un TCD ? 12-12-2009 23413
Passeport Pourcentages 03-09-2007 12069