EXCEL

LES TABLEAUX CROISES DYNAMIQUES

Objectifs à atteindre
 Créer un tableau croisé dynamique interactif qui permet d'effectuer une
synthèse rapide et croisée à partir d'une liste de données

Savoir-faire

 Gérer les données sources d'un tableau croisé dynamique

 Remanier, compléter des données pour mieux les exploiter
 Construire un tableau croisé dynamique
 Mettre en forme un tableau croisé dynamique
 Ajouter un graphique croisé dynamique

Exercice guidé

 Suivi des représentants

1 Version du 02/2018
S. LUTZ
 SOMMAIRE

I. LA CREATION DU TABLEAU CROISE DYNAMIQUE - 3

1. PRINCIPE ------------------------------------------------------------------------------------------------------------- 3
2. SAISIR LES DONNEES CI-DESSOUS, SANS MISE EN FORME EN RESPECTANT L’ORDRE DES
COLONNES ET DES LIGNES -------------------------------------------------------------------------------------- 3
3. ENREGISTRER ET NOMMER LA FEUILLE ------------------------------------------------------------------- 3
4. LES PIEGES A EVITER LORS DE LA CREATION D’UN TCD --------------------------------------------- 4
a. Il faut nettoyer la base ------------------------------------------------------------------------------------------ 4
b. Les limites d’excel ---------------------------------------------------------------------------------------------- 4
5. LA CREATION DU TCD------------------------------------------------------------------------------------------- 4
6. RENOMMER UN TCD----------------------------------------------------------------------------------------------- 6
a. Tableau/plage ----------------------------------------------------------------------------------------------------- 6
b. Autres --------------------------------------------------------------------------------------------------------------- 6
7. LES QUATRE ZONES DE LA FEUILLE ------------------------------------------------------------------------- 8
8. DETERMINATION DES CHAMPS DE COLONNES ET DE LIGNES ---------------------------------------- 9
a. TCD 1 --------------------------------------------------------------------------------------------------------------- 9
b. TCD 2 ------------------------------------------------------------------------------------------------------------- 10
c. TCD 3 ------------------------------------------------------------------------------------------------------------- 11
d. TCD 4 ------------------------------------------------------------------------------------------------------------- 12
9. RECUPERER LE VOLET DU TCD ------------------------------------------------------------------------------ 14
10. DESACTIVER LES TOTAUX ------------------------------------------------------------------------------------ 15
11. DESACTIVER LES SOUS TOTAUX ----------------------------------------------------------------------------- 15
12. NOMMER LES COLONNES -------------------------------------------------------------------------------------- 16
13. FILTRER ------------------------------------------------------------------------------------------------------------ 18
14. ENLEVER LES # VALEUR OU DIV/0 -------------------------------------------------------------------------- 18
15. METTRE UN FORMAT MONETAIRE SUR LES CHIFFRES ------------------------------------------------ 20
16. TRIER DES DONNEES -------------------------------------------------------------------------------------------- 21
17. FILTRES ELEMENTAIRES -------------------------------------------------------------------------------------- 22
18. FILTRES PAR VALEURS ----------------------------------------------------------------------------------------- 23
19. SUPPRIMER LES FEUILLES GENEREES --------------------------------------------------------------------- 23
20. AJOUTER UNE LIGNE A LA FIN DU TABLEAU ------------------------------------------------------------- 23
21. MODIFIER LE TYPE DE SYNTHESE--------------------------------------------------------------------------- 24
22. REPETER LES ETIQUETTES D’ELEMENTS SUR TOUTES LES LIGNES ------------------------------- 25
23. MODIFIER LES MODES D’AFFICHAGE ---------------------------------------------------------------------- 26
a. Établir un plan -------------------------------------------------------------------------------------------------- 26
b. Les autres formes d’affichage ------------------------------------------------------------------------------- 27
24. MAITRISER LES MISES EN FORME -------------------------------------------------------------------------- 28
25. GENERER LE DETAIL D’UNE DONNEE---------------------------------------------------------------------- 29

III. GRAPHIQUE DE TCD ------------------------------------------- 30

2 Version du 02/2018
S. LUTZ
 I. LA CREATION DU TABLEAU CROISE DYNAMIQUE

1. PRINCIPE

Un tableau croisé dynamique est un tableau interactif qui permet d'effectuer une synthèse
rapide et croisée à partir d'une liste de données. Il est usuel de nommer TCD un tableau croisé
dynamique. Comme pour tout travail dans une liste de données, vous ne devez sélectionner
qu'une seule cellule de votre liste avant de commencer l'élaboration de votre tableau croisé
dynamique. Excel sélectionnera automatiquement votre liste.

2. SAISIR LES DONNEES CI-DESSOUS, SANS MISE EN FORME EN

RESPECTANT L’ORDRE DES COLONNES ET DES LIGNES

3. ENREGISTRER ET NOMMER LA FEUILLE

 Enregistrer le classeur sous le nom TCD et nommer la feuille TAB_CROIS_DYN.

3 Version du 02/2018
S. LUTZ
 4. LES PIEGES A EVITER LORS DE LA CREATION D’UN TCD

a. Il faut nettoyer la base

 Les titres de colonnes sur deux lignes (retour à la ligne). Vont mettre des caractères
désagréables dans le TCD
 Le slash : pose des problèmes gênants dans le TCD
 Le symbole % peut poser des problèmes
 Les chiffres en tant que texte pose des problèmes, il faut les convertir en nombre
standard
Ex : ’13 : soit les réécrire ou Format – cellule – nombre
 Les cellules « NC » : impact sur le TCD
 Prendre toutes les lignes : attention aux colonnes masquées
 Attention aux cellules fusionnées = pas de cellules fusionnées
 On n’aura pas besoin de sélectionner les lignes jusqu’à 1 million. Toutes les lignes
ajoutées seront prises en compte avec le TCD

b. Les limites d’excel

 Le logiciel ne peut pas aller à 1 million de lignes
 Le logiciel ne peut pas aller à 10000 colonnes
 Si les tableaux de données sont trop importants, il vaut mieux une SGDB

Les caractères spéciaux qui gênent : ce sont les mêmes que ceux qui gênent pour renommer
une feuille

5. LA CREATION DU TCD

 Dans la feuille TAB_CROIS_DYN, cliquer n’importe où dans le tableau

 Cliquer sur INSERTION – TABLEAU CROISE DYNAMIQUE

4 Version du 02/2018
S. LUTZ
Il détecte la plage du tableau

 Cliquer le bouton NOUVELLE FEUILLE DE CALCUL

5 Version du 02/2018
S. LUTZ
 6. RENOMMER UN TCD

Il est nécessaire de modifier le nom car cela peut poser des problèmes par la suite.
a. Tableau/plage
Avant la création du TCD, on peut nommer la base de données sous le nom Représentant
(pour mémoire : sélectionner la plage A27 : E27, onglet Formules, zone noms définis,

cliquer sur l’icône Définir un nom et le nommer Représentant)

 Se positionner dans Tableau/plage et saisir le nom défini précédemment soit

représentant ou onglet Formules, onglet Formules, zone noms définis, cliquer sur
l’icône Utiliser dans la formule et sélectionner Représentant

 Cliquer sur OK

Vous allez arriver directement au point 7, le point 6b ne peut se faire que

lorsque le TCD est terminé

b. Autres
Si, lors de la création, le TCD n’a pas été nommé, il faut procéder de la manière
suivante :

 Cliquer dans le tableau croisé dynamique

 Cliquer sur l’onglet OPTIONS DU TABLEAU
CROISE DYNAMIQUE
 Dans « nom du tableau croisé dynamique »
 Renommer le tableau sous le nom de Représentant

6 Version du 02/2018
S. LUTZ
Ou
 Se positionner dans le TCD
 Clic droit
 Cliquer sur OPTIONS DU TABLEAU CROISE DYNAMIQUE

 Renommer le tableau sous le nom de Représentant

7 Version du 02/2018
S. LUTZ
 7. LES QUATRE ZONES DE LA FEUILLE

Votre feuille présente alors 4 zones entourées de bleu :

 Champs de colonne
 Champs de ligne
 Valeurs. Seule celle-ci permet d'effectuer les synthèses.
 Champs de filtre pour n'afficher qu'une partie des données. Dans les versions 2007 et
2010, cette zone nommée Filtre est plus aboutie que dans les versions précédentes.
 Dans Excel 2007 et 2010, toutes les commandes disponibles pour le TCD se trouvent
dans les onglets contextuels Outils de tableau croisé dynamique : Options et Créations.

8 Version du 02/2018
S. LUTZ
 8. DETERMINATION DES CHAMPS DE COLONNES ET DE LIGNES

On vous demande de réaliser quatre tableaux croisés dynamiques :

a. TCD 1
 Se positionner dans le tableau du TCD1
 Cocher et glisser les champs nécessaires au TCD :

Etiquettes de Ligne : Années

: Produit
: Représentants
: Région

Valeurs : Ventes

 Nommer la feuille TCD1

9 Version du 02/2018
S. LUTZ
 b. TCD 2
Nous allons réaliser un nouveau tableau croisé dynamique

 Dans la feuille TAB_CROIS_DYN, cliquer n’importe où dans le tableau

 Cliquer sur INSERTION – TABLEAU CROISE DYNAMIQUE
 Cliquer le bouton NOUVELLE FEUILLE DE CALCUL
 Renommer le TCD sous le nom Représentant

 Cocher et glisser les champs nécessaires au TCD :

Etiquettes de Ligne : Représentant

Etiquettes de Colonne : Produit
Données : Ventes

 Nommer la feuille TCD2

10 Version du 02/2018
S. LUTZ
 c. TCD 3
Nous allons réaliser un nouveau tableau croisé dynamique

 Dans la feuille TAB_CROIS_DYN, cliquer n’importe où dans le tableau

 Cliquer sur INSERTION – TABLEAU CROISE DYNAMIQUE
 Cliquer le bouton NOUVELLE FEUILLE DE CALCUL
 Renommer le TCD sous le nom Représentant

 Cocher et glisser les champs nécessaires au TCD :

Etiquettes de Ligne : Années

: Représentants
: Région

Etiquettes de Colonne : Produit

Valeurs : Ventes

 Nommer la feuille TCD3

11 Version du 02/2018
S. LUTZ
 d. TCD 4
Nous allons réaliser un nouveau tableau croisé dynamique

 Dans la feuille TAB_CROIS_DYN, cliquer n’importe où dans le tableau

 Cliquer sur INSERTION – TABLEAU CROISE DYNAMIQUE
 Cliquer le bouton NOUVELLE FEUILLE DE CALCUL
 Renommer le TCD sous le nom Représentant
 Cocher et glisser les champs nécessaires au TCD :
Filtre du rapport : Années

Etiquettes de Ligne : Représentants

: Région

Etiquettes de Colonne : Produit

Valeurs : Ventes

 Nommer la feuille TCD4

12 Version du 02/2018
S. LUTZ
 e. TCD 5
Nous allons réaliser un nouveau tableau croisé dynamique

 Dans la feuille TAB_CROIS_DYN, cliquer n’importe où dans le tableau

 Cliquer sur INSERTION – TABLEAU CROISE DYNAMIQUE
 Cliquer le bouton NOUVELLE FEUILLE DE CALCUL
 Renommer le TCD sous le nom Représentant

 Cocher et glisser les champs nécessaires au TCD :
Etiquettes de Ligne : Produit
: Année
: Représentant
: Région
Etiquettes de Colonne : Ventes

 Nommer la feuille TCD5

13 Version du 02/2018
S. LUTZ
 9. RECUPERER LE VOLET DU TCD

 Se positionner sur la feuille TCD4

 Lorsqu’on a perdu le volet du TCD : cliquer à l’intérieur du TCD4 et le volet apparaît

Ou
 Cliquer sur l’onglet OPTIONS
 Cliquer sur le bouton « LISTE DES CHAMPS »

14 Version du 02/2018
S. LUTZ
 10. DESACTIVER LES TOTAUX

On peut faire des listings à plusieurs dimensions. Afin de les réaliser, vous devez :

Lorsque l’on fait des listings : on peut désactiver le total général :

 Se positionner dans le TCD

 Cliquer onglet CREATION – zone DISPOSITION
 Cliquer sur le menu déroulant TOTAUX GENERAUX.

 Choisir « désactiver pour les lignes et les colonnes »

11. DESACTIVER LES SOUS TOTAUX

 Se positionner dans le TCD

 Cliquer sur l’onglet CREATION – zone DISPOSITION
 Cliquer sur le menu déroulant SOUS TOTAUX

 Sélectionner « ne pas afficher les sous totaux »

15 Version du 02/2018
S. LUTZ
 12. NOMMER LES COLONNES

 Se positionner sur le TCD5

 Cliquer la cellule A3 et se positionner dans la barre de formule
 Supprimer le mot « Somme de »

 Saisir à la place de « Somme de », le titre « VENTES ANNUELLES »

 Revenir en arrière avec la touche arrière

16 Version du 02/2018
S. LUTZ
 Saisir à l’intérieur de la barre de formule le mot VENTES et appuyer sur la touche entrée
du clavier

 Excel affiche le message ci-dessous

 Si l’on souhaite donner à cette colonne le même nom qu’un de ces champs : excel refuse
 Une Astuce : mettre un espace à la fin du mot VENTES

17 Version du 02/2018
S. LUTZ
 13. FILTRER

 Se positionner dans le TCD 4

 Dans la Zone de filtre du rapport, mettre le champ « Année »
 Se positionner sur la liste déroulante de la cellule B1 et filtrer par année

14. ENLEVER LES # VALEUR OU DIV/0

Si le tableau comporte des # et des div/0, pour enlever les # valeur ou div/0 :

 Se positionner dans le TCD

 Cliquer sur l’onglet OPTIONS – zone OPTION DU TABLEAU CROISE
DYNAMIQUE

18 Version du 02/2018
S. LUTZ
  Cliquer de nouveau sur OPTIONS

 Dans l’onglet disposition et mise en forme

 Dans la zone FORMAT cocher « pour les valeurs d’erreur afficher »
 saisir 0

19 Version du 02/2018
S. LUTZ
15. METTRE UN FORMAT MONETAIRE SUR LES CHIFFRES
 Se positionner sur la valeur laitage « DAVOLIO EST »

 Clic droit sur la souris

 Sélectionner et modifier les PARAMETRES DE CHAMPS DE VALEURS
 Cliquer sur FORMAT DE NOMBRE

 Dans catégorie, sélectionner Monétaire et choisir €

20 Version du 02/2018
S. LUTZ
Votre TCD se présente ainsi :

16. TRIER DES DONNEES

 Se positionner sur un item de la colonne A dans le TCD

 Clic droit : TRIER – OPTIONS DE TRIS SUPPLEMENTAIRES, tris de A à Z ou Z
àA

21 Version du 02/2018
S. LUTZ
Ou onglet OPTIONS – zone TRIER ET FILTRER

17. FILTRES ELEMENTAIRES

 Se positionner dans le TCD1

 Cliquer sur la liste déroulante de la cellule A4 et décocher « sélectionner tout » et
cocher 1

22 Version du 02/2018
S. LUTZ
 18. FILTRES PAR VALEURS

 Se positionner dans le TCD3

 Cliquer sur la liste déroulante de la cellule D3
 Choisir Filtres s’appliquant aux valeurs
 Sélectionner les valeurs supérieures à 7000

19.SUPPRIMER LES FEUILLES GENEREES

 Sélectionner la feuille
 Clic droit et supprimer

20. AJOUTER UNE LIGNE A LA FIN DU TABLEAU

Attention : si on rajoute des données à la fin du tableau d’origine ; lorsque l’on fait
« actualiser » ça ne prend pas en compte la ligne supplémentaire. En revanche, si on clique sur
la liste déroulante, l’élément supplémentaire apparaît.

 Se positionner dans le TCD

Pour que le TCD prenne en compte les nouvelles données :

 Cliquer sur l’onglet CREATION – zone DISPOSITION

 Sélectionner Afficher tous les sous totaux au bas du groupe

 Clic droit « actualiser»

23 Version du 02/2018
S. LUTZ
 21. MODIFIER LE TYPE DE SYNTHESE

Excel, pendant l'Assistant, impose la synthèse Somme, mais on peut très bien la
modifier :

 Se positionner sur une donnée chiffrée comme par exemple la cellule B5 dans le TCD2
 Clic droit
 Sélectionner PARAMETRES DE CHAMPS ou onglet OPTION – CHAMP ACTIF

 Dans le menu, se positionner sur l’onglet AFFICHER LES VALEURS et sélectionner

une première fois % du total général puis visualiser le résultat

24 Version du 02/2018
S. LUTZ
 Recommencer et sélectionner le % du total de la colonne

22. REPETER LES ETIQUETTES D’ELEMENTS SUR TOUTES LES

LIGNES

Le tableau croisé dynamique permet d'effectuer une synthèse rapide et croisée à partir d'une
liste de données. Lorsque l’on souhaite récupérer cette synthèse pour l’utiliser en tant que
listing, excel permet de répéter le nom des étiquettes sur toutes les lignes.

 Se positionner dans le TCD

 Cliquer sur l’onglet CREATION – zone DISPOSITION
 Cliquer sur Répéter toutes les étiquettes d’élément

25 Version du 02/2018
S. LUTZ
Votre TCD se présente ainsi :

23. MODIFIER LES MODES D’AFFICHAGE

a. Établir un plan
 Se positionner sur la feuille TCD3
Pour plus de clarté, on peut regrouper des champs de colonnes ou de lignes et ainsi
établir une synthèse par groupe.

 Cliquer sur le bouton de la cellule A5 et recommencer avec la cellule A14

26 Version du 02/2018
S. LUTZ
Cela permet de générer une synthèse :

Pour revenir sur le TCD d’origine :

 Se positionner dans le TCD

 Cliquer sur l’onglet OPTION
 Cliquer sur l’icône CHAMP ACTIF
 Cliquer sur « Développer le champ entièrement »

b. Les autres formes d’affichage

 Se positionner dans le tableau du TCD3
 Cliquer sur l’onglet CREATION zone DISPOSITION
 Cliquer sur AFFICHER SOUS FORME COMPACTEE

27 Version du 02/2018
S. LUTZ
  Se positionner dans le tableau du TCD3
 Cliquer sur l’onglet CREATION zone DISPOSITION
 Cliquer sur AFFICHER SOUS FORME TABULAIRE

24. MAITRISER LES MISES EN FORME

Il n'est pas très pratique de mettre, soi-même, le tableau en forme. En effet, lorsque vous aurez
mis du temps à mettre des couleurs, des polices, du gras ou de l'italique, vous cliquez sur
l’onglet option- zone données et sur le bouton pour actualiser le tableau, et voilà, une
partie de votre travail a disparu.

Utilisez plutôt les Mises en forme automatique :

 Se positionner dans le TCD

 Cliquer sur l’onglet CREATION zone STYLE DE TABLEAU CROISE
DYNAMIQUE
 Descendre le menu déroulant et choisir une mise en forme.

Elles sont peu nombreuses, mais relativement bien adaptées aux tableaux croisés dynamiques
et vous montreront les éléments possibles à mettre en forme.

28 Version du 02/2018
S. LUTZ
 25. GENERER LE DETAIL D’UNE DONNEE

Si l’on souhaite avoir le détail d’une cellule, on double clic sur la donnée souhaitée, cela
génère une nouvelle feuille avec le détail :

 Double clic sur la cellule F5

 Une nouvelle feuille s’est générée et permet d’avoir le détail de la cellule F5

29 Version du 02/2018
S. LUTZ
 II. GRAPHIQUE DE TCD

 Se positionner sur la base de données Tab_crois_dyn

 Cliquer sur l’onglet Insertion
 Sélectionner Graphique croisé dynamique

30 Version du 02/2018
S. LUTZ