Tutoriel: le tableau croisé dynamique sur Excel

Exemple de Tableau Croisé dynamique
Exemple de Tableau Croisé dynamique

Aujourd’hui nous allons parler d’une d’une des fonctions avancées les plus intéressantes d’Excel : les tableaux croisés dynamiques.
Voici brièvement ce que cet outil va vous permettre de faire:

  • Analyser une grande quantité de données
  • Créer un rapport statistique de manière automatique
  • Mettre à jour ce rapport automatiquement avec de nouvelles données
  • Personnaliser et modifier ce rapport par glisser déposer

En Cadeau: Télécharge le tutoriel complet de 24 pages, la Bible du Tableau Croisé Dynamique

Voici à quoi ressemble un tableau croisé dynamique ( abrégé en TCD) si vous n’en avez jamais vu auparavant:

Exemple de Tableau Croisé dynamique

Exemple de Tableau Croisé dynamique

C’est l’une des fonctionnalités d’Excel susceptible de vous faire gagner un temps fou, alors je vous explique tout de suite comment ça marche

  1. Structurer sa base de données
  2. Créer un tableau croisé dynamique
  3. Structure du tableau
    1. Positionner les champs du tableau
    2. Changer le positionnement des champs
    3. Choisir le type de données
    4. Filtrer les données
    5. Grouper les champs
    6. Afficher plusieurs critères par ligne ou par colonne
  4. Changer le format du tableau croisé dynamique
  5. Modifier les données du tableau croisé dynamique

1 ) Structurer sa base de données

En Cadeau: Télécharge le tutoriel complet de 24 pages, la Bible du Tableau Croisé Dynamique

Avant de créer votre premier tableau croisé dynamique, il vous faut créer et organiser votre bases de données. Excel à besoin d’une base qui respecte les règles suivantes:

  • Les données sont organisées en colonnes
  • La première cellule de chaque colonne est un en-tête décrivant les données de la colonne
  • Pas d’en-tête vide (sinon Excel ne sait pas comment interpréter la colonne)
  • Pas de ligne vide (sinon Excel considère que votre table s’arrête à la ligne vide et oublie une partie de vos données
  • Vérifiez que les champs de texte contiennent du texte, et les champs numériques des nombres

Une base de données structurée pour un TCD ressemble à ça:

Base de données optimisée pour un tableau croisé dynamique

Données structurées avec des en-têtes et aucune ligne vide

 

2) Créer un tableau croisé dynamique

En Cadeau: Télécharge le tutoriel complet de 24 pages, la Bible du Tableau Croisé Dynamique

Pour créer un tableau croisé dynamique, il vous suffit de vous rendre dans le menu Données > Tableau croisé dynamique

Menu Données / tableau croisé dynamique

Dans le menu Données, cliquez sur Tableau Croisé Dynamique

Une boite de dialogue s’ouvre ensuite pour vous demander comment créer le TCD

Choisir la plage de données pour un tableau croisé dynamique

Menu de création du TCD

Dans ce menu, sélectionnez vos données, puis l’emplacement ou Excel doit placer votre nouveau TCD.
Quelques recommandations importantes

  • Sélectionnez les colonnes entières de vos données sans restrictions de lignes. Ainsi si vous rajoutez plus tard des données, le rapport pourra les prendre en compte automatiquement sans avoir à changer la plage de données
  • Ne sélectionnez pas de colonne vide en trop (sinon Excel vous retournera une erreur car il ne trouvera pas d’en-tête sur la colonne vide)
  • Pour l’emplacement de votre tableau croisé dynamique, choisissez une nouvelle feuille. Par la suite, si vous créez d’autres tableaux ou données, ne les mettez pas dans un onglet qui contient déjà un TCD. En effet, si les données du TCD changent, il peut être amené à s’agrandir et à effacer les autres éléments qui se trouvent sur sa feuille. En gardant chaque TCD sur une feuille séparée, vous lui permettez d’évoluer sans endommager le reste de votre classeur.

3) Structure du tableau

En Cadeau: Télécharge le tutoriel complet de 24 pages, la Bible du Tableau Croisé Dynamique

3.1) Positionner les champs du tableau

Le tableau créé sera en premier lieu vide, comme ci-dessous

création d'un tableau croisé dynamique vide

Tableau Croisé dynamique vide

Si le menu « Conception de TCD » ne s’affiche pas, il suffit de cliquer sur le TCD pour le faire apparaitre.
Pour ajouter des données au tableau, cliquez dans la partie « Nom de champ » sur chaque champ que vous souhaitez ajouter dans le rapport. Excel va automatiquement les répartir entre les catégories « Etiquettes de lignes »,  « Etiquettes de colonnes » et « Valeurs ». Vous pouvez ensuite changer la répartition par simple glisser-déposer entre les catégories.

TCD créé automatiquement avec l'assistant

TCD créé par défaut

Prenons un moment pour comprendre ce que Excel représente dans ce tableau créé de manière automatique.

Dans la partie « Etiquettes de lignes » se trouve le champ « Pays », Excel crée donc une ligne par pays.

Dans la partie  « Etiquettes de colonnes », aucun des champs spécifiés n’est renseigné. Si nous avions spécifié « Client », Excel aurait créé une ligne dans le tableau par client. A la place, Excel renseigne par défaut « Valeurs ». Cela signifie qu’il créera une ligne dans le tableau par type de Valeur renseigné dans la catégorie « Valeurs ». Dans notre cas il y a 2 valeurs: « Client » et « Chiffre d’affaires ». Excel crée donc une colonne pour indiquer le chiffre d’affaires et une colonne pour indiquer les clients

Dans la partie « Valeurs », sont utilisés les champs « Client » et « Chiffre d’affaires ». Vous remarquerez qu’Excel n’indique pas « Client » mais « NB sur Client ». Cela signifie que dans chaque case du tableau de la colonne Client, il va indiquer le nombre de clients qui satisfont aux critères de la case dans l’ensemble de notre base de données.

Dans ce premier TCD, on voit qu’Excel crée un tableau avec une ligne par pays, et sur chaque ligne , le nombre de clients dans ce pays, puis le nombre de « Chiffres d’affaires » (ie en réalité le nombre de ventes) réalisés dans chaque pays.

On se rend également compte que les 2 nombres de chaque ligne sont égaux, cela signifie que dans chaque pays, chaque client n’a acheté qu’une seule fois. D’autre part la somme de chaque colonne fait 30, ce qui est le nombre de ligne de notre base de données (ouf, tout a bien été pris en compte!).

3.2) Changer le positionnement des champs

En Cadeau: Télécharge le tutoriel complet de 24 pages, la Bible du Tableau Croisé Dynamique

On va corser les choses et faire un TCD qui permet d’afficher le chiffre d’affaire réparti par client (un client par colonne), et par pays (un pays par ligne). Nous allons donc positionner le champ « Pays » dans « Etiquettes de lignes » et « Client » dans  « Etiquettes de colonnes ». Excel a par défaut mis le champ « Client » dans la catégorie valeurs, aussi on utilise un glisser-déposer pour le mettre dans « Etiquettes de colonnes ». Enfin on laisse « Chiffre d’affaires » dans la partie « Valeurs ».

utilisation du glisser-déposer pour modifier un tableau croisé dynamique

Vous pouvez modifier les champs du TCD par glisser déposer

Cela nous donne un nouveau TCD que voici. La répartition est bien faite par pays et par client mais Excel affiche toujours le nombre de ventes et non le chiffre d’affaires.

TCD avec un champ par ligne et un champ par colonne

Notre nouveau TCD

3.3) Choisir le type de données

En Cadeau: Télécharge le tutoriel complet de 24 pages, la Bible du Tableau Croisé Dynamique

Pour afficher le chiffre d’affaire par pays et par client, il faut en fait demander à Excel de sommer toutes les entrées de chiffres d’affaires qui correspondent au client et au pays de la case donnée (alors que jusqu’a présent Excel ne fait que les compter). Pour faire cela, cliquez sur le symbole à droite de la barre « NB sur Chiffre d’affaires ».

Dans le menu qui s’ouvre à vous, cliquez sur Synthèse par Somme.

Comment insérer une somme calculée dans un tableau croisé dynamique

Afficher une somme des valeurs dans un TCD

Quelques précisions:

  • Selon votre version d’Excel, le menu peut ne pas s’afficher tout de suite et vous devrez faire un clic intermédiaire sur « Paramètre des champs de valeurs »
  • Dans ce menu vous voyez que vous pouvez insérer plusieurs champs calculés: une somme des valeurs, mais aussi leur moyenne, le maximum, le minimum… Bref matière à s’amuser si on veut sortir des statistiques poussées (Par exemple : le panier moyen est il plus élevé en Allemagne ou en Chine? Ecart type de la taille des commande passées par chaque client?)

On retombe donc sur le TCD ci dessous:

comment insérer un Champ calculé somme dans un TCD

Chiffre d’affaires par client et par pays

3.4 Filtrer les données

En Cadeau: Télécharge le tutoriel complet de 24 pages, la Bible du Tableau Croisé Dynamique

Parfois vous pouvez avoir envie d’afficher un tableau qui ne prennent en compte qu’une partie de la base de données. Il existe 2 manières de filtrer les données selon que le filtre porte sur un des champs utilisé dans le tableau ou non.

Pour appliquer un filtre sur un champ du tableau, cliquez sur le symbole à droite de ce champ. Vous pouvez alors choisir quelles valeurs vous souhaitez afficher ou voir disparaitre. C’est également dans ce menu que vous pouvez choisir dans quel ordre s’affichent les valeurs. Pour notre exemple, choisissons de ne pas afficher les ventes aux USA, ainsi que les cases vides (les cases vides apparaissent car nous avons sélectionné des colonnes entières au début, il y a donc beaucoup de lignes vides dans notre base de données. Nous reviendrons sur l’utilité de ce paramètre plus tard).

filtrer un champ d'un tableau croisé dynamique

Filtrer un champ du tableau

Le deuxième type de filtre qu’il est possible d’appliquer porte sur un champ qui n’est pas utilisé dans le tableau. Il faut alors le rajouter dans le menu « Concepteur de tableau croisé dynamique » (qui revient au premier plan si vous cliquez sur le TCD) en le cochant dans « Nom de champ », puis le glisser dans la catégorie « Filtre du rapport ».

Votre TCD va changer et la première ligne fera alors apparaitre un symbole de filtre dans lequel vous pouvez choisir quelles valeurs vous voulez inclure. Dans notre exemple, cap sur le marché de la banane, seul ce produit est sélectionné.

Filtrer un TCD avec un critere qui n'est pas un champ

Filtrer selon un nouveau critère

3.5) Grouper les champs

En Cadeau: Télécharge le tutoriel complet de 24 pages, la Bible du Tableau Croisé Dynamique

Il est possible de regrouper certains champs dans notre tableau en sous catégories. Par exemple, regroupons les pays dans leurs continents. Pour cela, sélectionnez les champs concernés et faites clic droit > Grouper et créer un plan > Grouper. C’est le même menu pour dégrouper les champs.

Dégrouper ou séparer les champs d'un TCD

Grouper les champs

Une fois les groupes effectués, vous 2 actions sont disponibles très simplement:

  • Editer le nom du groupement: il suffit de le sélectionner et vous pouvez modifier le nom dans la barre de formule
  • Afficher ou masquer chaque groupement: en cliquant sur la flèche à gauche du groupement
afficher ou masquer les groupes d'un TCD

Afficher ou masquer les groupes

3.6) Afficher plusieurs critères par ligne ou par colonne

En Cadeau: Télécharge le tutoriel complet de 24 pages, la Bible du Tableau Croisé Dynamique

Un point intéressant que vous remarquerez en groupant les pays est la manière dont change la zone « Etiquettes de lignes ». Il y a maintenant 2 champs, celui des pays, et un autre champ que nous avons crée manuellement: Pays2, avec une priorité plus importante que le champ Pays. Excel affiche les lignes dans l’ordre dans lequel les étiquettes sont triées: d’abord par champ Pays2, puis par champ Pays.

La zone Etiquettes de lignes a changé

La zone Etiquettes de lignes a changé

Cet exemple va nous permettre d’embrayer tout de suite sur un type de rapport plus compliqué: comment afficher plusieurs critères par ligne ou par colonne? Il suffit de définir une succession de critères dans Etiquettes de lignes ou de colonnes. Par exemple, si l’on souhaite afficher une répartition par Pays, puis dans chaque pays, par Produit, il faut mettre Produit en dessous de Pays comme ci-dessous.

TCD avec plusieurs critères en ligne

TCD a 2 critères

4) Changer le format du tableau

En Cadeau: Télécharge le tutoriel complet de 24 pages, la Bible du Tableau Croisé Dynamique

Le menu Tableau croisé dynamique vous offre plusieurs options pour change le format de votre TCD:

changer le format d'un tableau croisé dynamique

Options de format dans le menu Tableau Croisé Dynamique

Voici les principales:

  • Sous-Totaux: En haut ou en bas. Cette première option vous permet de choisir si vous souhaitez afficher les sous totaux de chaque catégorie en dessous ou au dessus de la catégorie.
  • Totaux: Lignes et/ou colonnes: Cette option vous permet de choisir si vous souhaitez afficher ou non les totaux globaux du tableau, sur la dernière ligne et sur la dernière colonne.
  • Disposition: Compacte, Mode Plan, Tabulaire. Cette option vous permet de choisir la manière dont est représentée la première colonne. Le mode compact représente dans une seule colonne l’en-tête, puis les valeurs du champ. Le mode Plan représente l’en-tête dans la première colonne et les valeurs du champ dans la seconde colonne. L’en-tête et la première valeur sont sur la même ligne. Le mode Tabulaire est similaire au mode Plan, mais la première valeur du champ est placée une ligne en dessous de l’en-tête.
    Mode compact d'un TCD

    Mode compact

    Mode Plan d'un TCD

    Mode Plan

    Mode Tabulaire pour un TCD

    Mode Tabulaire

  • Styles de tableau croisé dynamique: Excel vous propose par défaut plusieurs styles de TCD avec différents formats que vous pouvez utiliser immédiatement. Vous pouvez aussi créer le votre et le sauver si vous voulez le réutiliser plus tard ( par souci de cohésion dans votre rapport).

    Styles par défaut de tableau croisé dynamique

    Styles par défaut de TCD

  • Lignes et colonnes: En-tête de ligne/ colonne et Ligne/colonne à bande. Cette option permet de choisir si vous souhaitez surligner les en-têtes de ligne/ colonne. Vous pouvez aussi choisir si vous voulez afficher un trait pour séparer chaque ligne/colonne.

5) Modifier les données du tableau croisé dynamique

En Cadeau: Télécharge le tutoriel complet de 24 pages, la Bible du Tableau Croisé Dynamique

Un des principaux avantages des TCD est que vous pouvez changer les données source et le rapport sera facilement mis à jour.

Pour changer la plage de données source du TCD, cliquez sur « Modifier la source »

Changer les données source d'un TCD

Changer la source d’un TCD

La fenêtre qui s’ouvre ensuite vous permet de changer la plage de données source, si vous l’avez déplacée ou bien si vous souhaitez travailler sur d’autres données.

Comment modifier la plage source d'un TCD

Choisir la plage source du TCD

 

Si vous modifiez des données dans la plage source et que vous souhaitez mettre à jour le TCD: Il vous suffit de faire un clic-droit puis « Actualiser les données »

Actualiser les Données du Tableau croisé dynamique

Actualiser les Données du TCD

Avez-vous d’autres questions sur les tableaux croisés dynamiques? Posez les-en commentaires pour que j’y réponde! Et si ce tuto vous a appris quelque chose, n’hésitez pas à le partager!

About Docteur Excel
Tu souffres sur Excel ? Ça se soigne ! Un traitement de choc pour tous tes problèmes de tableur! Docteur Excel t'apprends à manipuler Excel avec une précision chirurgicale pour gagner un temps fou, éblouir ton patron et devenir un pro du tableur.

16 Comments

  1. très bien fait ! MERCI

  2. TRES CLAIR!!! merci et bravo

  3. HOUDA MARTINE7 avril 2016 at 10 h 22 min

    merci pour ce tuto. Le TCD n’est plus un secret pour moi

  4. Super tuto. Merci. Je cherche à afficher simplement la valeur dans un TCD, mais Excel calcule toujours une somme automatique. Je sais qu’il est possible de lui demander d’afficher simplement un valeur, mais je ne sais plus comment. Si vous avez la réponse, par avance, merci.

    • Excel va faire naturellement la somme par catégorie. Si je comprends bien votre tableau devrait afficher une seule valeur par intersection, mais comme il affiche une somme il y a en fait plusieurs valeurs à sommer par intersection. Peut-être devez vous ajouter un niveau de détail, c’est à dire un champ supplémentaire en ligne ou en colonne?

  5. NGBENZI Junior Ludovic28 octobre 2016 at 11 h 55 min

    Cet outil m’a beaucoup aidé dans mes travaux sur excel. Un grand merci!!!!!

  6. Bonjour, et merci beaucoup pour le tutoriel
    J’ai cependant une question sur laquelle je bloque :
    Dans ma base de donnée j’ai des ventes (datées), comment puis-je faire un graphique qui m’affiche une courbe des ventes sur 52 semaines si des ventes ont eu lieu pendant seulement 48 semaines?

    De même est-il possible de faire un prorata du nombre de ventes par jour travaillé?

    Merci beaucoup par avance.

    • Bonjour Romain
      As tu essayé de rajouter les dates pour les 4 semaines manquantes et des ventes à 0?
      Sinon tu peux essayer de modifier le maximum de l’axe.
      Je ne comprends pas tellement ce que tu veux faire avec le prorata?
      Tu peux poser ta question sur le forum:http://forum.formuleexcel.com/
      Tu pourras y mettre un fichier d’exemple afin de mieux comprendre.

  7. Bonjour
    Est-il possible d’avoir un exemple de comment créer un tableau croisé dynamique concernant les données transport personnel en société

    Merci

  8. Merci pour ce tuto très complet.
    J’ai un problème dans mon TCD.
    Je perds les sommes de valeurs quand j’actualise les données.
    Que dois-je modifier ?
    Merci

    • Bonjour Beuno
      C’est difficile à dire comme ca, je te conseille de poser ta question sur le forum
      Tu pourras y inclure le fichier qui te pose problème pour pouvoir reproduire l’erreur et y trouver une solution.
      A bientôt

  9. Bonsoir, je ne trouve aucune solution à mon problème, pouvez-vous m’aider?

    Excel affiche par défaut dans ses tableaux croisés dynamiques, le nombre de valeurs (NB) et pas la somme. Il faut manuellement spécifier dans dans la colonne où je veux afficher mes totaux que c’est la somme que je veux et pas le nombre de valeurs.

    Avez-vous une solution que la somme se fasse et non plus le nombre de valeur?

    Milles mercis!!!

  10. comment ajouter automatiquement des lignes et colonnes dans le TCD. Merci

Comments are closed.