EX CE L : TABLE AU CROI SE FORMATION

DY N AM I QU E – GUI D E UTI LI SAT E U R

OCTOBRE 2005

Excel : Tableau Croisé Dynamique

Guide Utilisateur

Confidentiel – Tous droits réservés – Ernst & Young 2005 1

 EX CE L : TABLE AU CROI SE
DY N AM I QU E – GUI D E UTI LI SAT E U R

Confidentiel – Tous droits réservés – Ernst & Young 2005 2

 EX CE L : TABLE AU CROI SE
DY N AM I QU E – GUI D E UTI LI SAT E U R

Sommaire
Introduction au Tableau Croisé Dynamique ............................................................................................. 4
Où récupérer les fichiers du cas pratique utilisé pour ce guide ? ............................................................... 4
Qu’est-ce qu’un Tableau Croisé Dynamique? ........................................................................................... 5
Quelques rappels sur la création des bases de données........................................................................... 5
Étape fondamentale : l'analyse de la base de données ............................................................................. 5

Exercice 1 ................................................................................................................................................... 6
Créer un Tableau Croisé Dynamique ........................................................................................................ 6

Exercice 2 ................................................................................................................................................. 12
Modifier la création du TCD .................................................................................................................... 12
Renommer un champ ............................................................................................................................. 13

Exercice 3 ................................................................................................................................................. 14
Utiliser des champs Page ....................................................................................................................... 14
Afficher les pages ................................................................................................................................... 14
Modifier le format des données (DATA) .................................................................................................. 15
Changer les fonctions de calculs............................................................................................................. 15

Ajout et modification de données sources ............................................................................................. 16

Ajouter des données à la source............................................................................................................. 16
Mettre à jour un TCD (Raffraîchir les données) ....................................................................................... 16
Redéfinir la source de données............................................................................................................... 17

Options du TCD........................................................................................................................................ 18
Trier les données.................................................................................................................................... 18

Calculs personnalisés, champ calculés, groupement des données...................................................... 18

Calculs personnalisés............................................................................................................................. 19
Champs calculés .................................................................................................................................... 19
Grouper des éléments de texte............................................................................................................... 21
Extraction des lignes de données ........................................................................................................... 22

Mise en forme d’un TCD .......................................................................................................................... 22

Confidentiel – Tous droits réservés – Ernst & Young 2005 3

 EX CE L : TABLE AU CROI SE
DY N AM I QU E – GUI D E UTI LI SAT E U R

Introduction au Tableau Croisé Dynamique

Où récupérer les fichiers du cas pratique utilisé pour ce guide ?

Vous pourrez télécharger l’énoncé du cas pratique utilisé dans ce guide ainsi que le fichier Excel base de
données depuis EYLeADS : [Link]

· Tapez votre nom d’utilisateur et mot de passe HTTP

· Sur la page d'accueil de EYLeADS, saisissez dans la zone Search ‘Excel Tableau Croisé’,
· Cliquez sur le bouton GO pour lancer la recherche :

La page de résultat s’affiche, repérez le guide utilisateur :

· Cliquez sur l’icône View pour télécharger le guide et les fichiers du cas pratique
· Sauvegardez le fichier sur votre disque dur en cliquant sur le bouton Save puis dézipper le
guide et les fichiers.

Confidentiel – Tous droits réservés – Ernst & Young 2005 4

 EX CE L : TABLE AU CROI SE
DY N AM I QU E – GUI D E UTI LI SAT E U R

Qu’est-ce qu’un Tableau Croisé Dynamique?

Un tableau croisé dynamique permet d’exploiter les données d’une base en les croisant.
Qu’est ce que cela signifie ? Dans la base de données, les étiquettes (ou champs) se trouvent sur la
première ligne de la base et les données sont ensuite disposées par colonnes sous ces étiquettes. Dans un
tableau croisé dynamique, il est possible de disposer les étiquettes/champs en ligne et en colonne pour qu’à
l’intersection un calcul de synthèse s’effectue.

Quelques rappels sur la création des bases de données

Il faut impérativement respecter 4 règles :

· Une ligne de titres sans cellule vide entre deux titres

· Ne pas dépasser 32 colonnes
· Pas de ligne de données vide
· Pas de ligne de sous-totaux faite à la main

Étape fondamentale : l'analyse de la base de données

Une base de données (aussi appelée source de données) est avant tout un lieu de stockage d'informations.
Il est par conséquent indispensable de réfléchir à la qualité de son organisation et à celle des données
inscrites.
La création d'un TCD nécessite plusieurs étapes, la première est fondamentale : l'analyse de la BDD.
Faire l'analyse préalable de la base de données :

1. Analyser le type d'information de chaque champ et comprendre la portée de la base de

données
2. Déterminer les objectifs du TCD et identifier les noms de champs à inclure
3. Déterminer quel champ contient les données à synthétiser et quelle fonction utiliser
4. Décider comment disposer les données : la disposition est cruciale pour délivrer le message voulu
5. Déterminer l'emplacement du TCD : on peut le placer dans n'importe quelle feuille de n'importe
quel classeur ou sur la même feuille que les données sources

Confidentiel – Tous droits réservés – Ernst & Young 2005 5

 EX CE L : TABLE AU CROI SE
DY N AM I QU E – GUI D E UTI LI SAT E U R

Exercice 1
Créer un Tableau Croisé Dynamique
Manipulation : Exercice [Link]
· Au sein d'un premier TCD, analysez la répartition des salaires sur
l'ensemble de l'entreprise selon le poste occupé dans un service.
· Quels sont les champs concernés ?
· Nommer la feuille qui contient le TCD "analyse"

Avant de créer un TCD, il faut d’abord sélectionner la base de données à partir de laquelle le TCD sera
créé.

Sélectionner une base de données

IMPORTANT :
On ne sélectionne JAMAIS une base de données autrement qu’en cliquant sur UNE SEULE cellule
lui appartenant.

Création du Tableau Croisé Dynamique

· Sélectionner une seule cellule de la base de données se trouvant sur la feuille de calculs Base
de données BMS
· Menu Data > Pivot Table and PivotChart Report…
· Etape 1 : préciser l’origine des données. Ici l’origine des données est la base de données
précédemment sélectionnée

Confidentiel – Tous droits réservés – Ernst & Young 2005 6

 EX CE L : TABLE AU CROI SE
DY N AM I QU E – GUI D E UTI LI SAT E U R

· Conserver l’option par défaut du type sur PivotTable (Tableau Croisé Dynamique)
· Confirmer en cliquant sur Next (Suivant)
· Etape 2 : vérifier la plage de cellules sélectionnée et corriger si nécessaire. A défaut, on peut
utiliser un nom déjà défini dans le classeur (voir rubrique Nommer une plage de cellules).
Faire aussi remarquer qu’il est possible de cliquer éventuellement sur Browse (Parcourir) pour
utiliser la base d’un autre classeur.

· Cliquer sur le bouton Next (Suivant)

· Etape 3 : Choisir la destination du TCD : sur une nouvelle feuille ou sur la même feuille (indiquer
la cellule d’origine).

Pour des raisons pratiques, il est préférable de créer le TCD sur une nouvelle
feuille de calculs. Dans le cadre de cet exercice, le TCD sera créé sur une
nouvelle feuille de calculs.

· Choix des champs du TCD (Assistant à la création du TCD) : Pour choisir les champs du
TCD, à l’étape 3, cliquer sur le bouton Layout (Disposition)

IMPORTANT :
A chaque fois que la disposition des champs du TCD devra être modifiée, il faudra
utiliser le bouton Layout à l’étape 3 de l’assistant de création.

Confidentiel – Tous droits réservés – Ernst & Young 2005 7

 EX CE L : TABLE AU CROI SE
DY N AM I QU E – GUI D E UTI LI SAT E U R

Manipulation : Exercice [Link]

· Quels sont les champs concernés ?

· Faire glisser les champs sur les zones en COLUMN (colonne), ROW (ligne), PAGE, et DATA
(données).

Dans l’exercice demandé, il s’agit d’analyser la répartition des salaires sur l'ensemble de l'entreprise selon le
poste occupé dans un service. Les champs concernés sont :Service, Poste et Salaire annuel.

Disposez les données les plus longues en lignes car la feuille Excel ne contient que 256
colonnes au total. Les données texte sont davantage liées à des critères de lignes/colonnes,
tandis que les données de chiffres sont davantage destinées à devenir des données calculées
(centre du tableau = DATA)

Confidentiel – Tous droits réservés – Ernst & Young 2005 8

 EX CE L : TABLE AU CROI SE
DY N AM I QU E – GUI D E UTI LI SAT E U R

· Comment envisagez-vous de disposer les données et pourquoi ? Quelle est la disposition la

plus intéressante pour le résultat que l’on souhaite obtenir (répartition des salaires sur l'ensemble de
l'entreprise selon le poste occupé dans un service).

Disposition Remarques
· Trop de colonnes, la lecture du tableau
est difficile.

· Trop de colonnes, la lecture du tableau

est difficile.

· Disposition la plus intéressante

· Il est possible d’avoir plusieurs
champs en lignes et l’ordre des
champs à une importance selon le
résultat que l’on souhaite obtenir :
Ici le champ Service a été positionné
avant le champ Poste car on souhaite
d’abord obtenir la répartition des salaires
sur l'ensemble de l'entreprise selon le
poste occupé dans un service.
· Positionnez le champ Poste avant le
champ Service. En quoi diffère cette
disposition ?

REMARQUE :
Par défaut, Excel applique la fonction SUM (SOMME) au champ positionné dans la zone DATA
(DONNEES). Ceci est valable quand le contenu de ce champ est de type numérique.

Si le contenu du champ est de type alphanumérique, c’est la fonction COUNT (NB) qui est
appliquée (fonction qui compte le nombre de valeurs se trouvant dans le champ).

· Champ de valeur numérique = SUM (SOMME)

· Champ de valeurs alphanumériques = COUNT (NB)

Il est possible d’appliquer d’autres fonctions de calculs (AVERAGE (MOYENNE), MIN

(MINIMUM), MAX (MAXIMUM)…). Ce thème est présenté plus loin dans ce guide.

· Une fois les champs positionnés, cliquer sur OK pour valider puis sur le bouton Finish
· Le TCD est crée sur une nouvelle feuille de calculs

Confidentiel – Tous droits réservés – Ernst & Young 2005 9

 EX CE L : TABLE AU CROI SE
DY N AM I QU E – GUI D E UTI LI SAT E U R

Manipulation : Exercice [Link]

· Nommer la feuille qui contient le TCD "analyse"

Il est préférable de renommer immédiatement la feuille de calculs juste après la création d’un
TCD car on s’y perd rapidement lorsqu’on manipule les TCD.

Nommer une plage de cellules

Nommer une plage de cellules est une fonctionnalité très pratique qui consiste à utiliser un nom défini par
l’utilisateur pour faire directement référence à une plage de cellules. Dans ce cas, le nom remplace la
référence à la plage de cellules. Cela évite surtout d’avoir à sélectionner un grand nombre de lignes et de
colonnes. Un nom peut être utilisé n’importe où dans le classeur, y compris dans les formules et fonctions de
calculs.

Pour nommer une plage de cellules :

· Sélectionner d’abord toute la plage de cellules

Pour sélectionner un tableau entier :

· Sélectionner une seule cellule du tableau
· CTRL + * (touche étoile « * » = signe multiplier du clavier)

· Nommer la plage sélectionnée en tapant le nom directement dans la zone de nom située à
gauche de la barre de formules et valider avec la touche ENTREE

· Le nom peut maintenant être utilisé à n’importe quel endroit du classeur

· Depuis une formule, fonction de calculs ou plage de données à définir, pour faire appel à un
nom défini dans un classeur appuyer sur la touche F3. La fenêtre Paste Name (Coller un Nom)
apparaît, sélectionner le nom souhaité puis OK

Confidentiel – Tous droits réservés – Ernst & Young 2005 10

 EX CE L : TABLE AU CROI SE
DY N AM I QU E – GUI D E UTI LI SAT E U R

Confidentiel – Tous droits réservés – Ernst & Young 2005 11

 EX CE L : TABLE AU CROI SE
DY N AM I QU E – GUI D E UTI LI SAT E U R

Exercice 2
Manipulation : Exercice [Link]
· Au sein de ce même tableau, affiner l'analyse préalablement réalisée
en rajoutant le critère d'évaluation de la performance (Cote).

· Pour une meilleure compréhension de cette analyse, remplacer

"Cote" par "Performance".

Il s’agit ici de modifier le TCD et de mettre le champ cote en colonne puis de renommer ce champ.

Modifier la création du TCD

· Sélectionner une cellule (n’importe laquelle) du TCD
· Menu PivotTable > PivotTable Wizard de la barre d’outils TCD

ou clic-droit sur une cellule du TCD > PivotTable Wizard

· Cliquer sur le bouton Layout

Pour gagner du temps dans la manipulation des TCD, il est possible de personnaliser la barre
d’outils du TCD en rajoutant l’icône PivotTable Report.
· Aller dans le menu View > Toolbar > Customize
· Onglet Commands, catégorie Data
· Cliquer-glisser l’icône PivotTable and PivotChart Report vers la barre d’outils TCD

Confidentiel – Tous droits réservés – Ernst & Young 2005 12

 EX CE L : TABLE AU CROI SE
DY N AM I QU E – GUI D E UTI LI SAT E U R

Renommer un champ
Pour renommer un champ du TCD
· Afficher le panneau disposition du TCD
· Double-clic sur le champ à renommer
· Renommer le champ dans la zone Name puis OK

Confidentiel – Tous droits réservés – Ernst & Young 2005 13

 EX CE L : TABLE AU CROI SE
DY N AM I QU E – GUI D E UTI LI SAT E U R

Exercice 3
Manipulation : Exercice [Link]
· Notre client souhaite analyser la répartition des salaires selon les
critères précédents et selon un seul service à la fois.

Il s’agit ici d’utiliser des champs Page

Utiliser des champs Page

Les champs page permettent d’afficher les données sur demande à l’aide d’un filtre.

· Mettre le champ Service en Page

· Cliquer sur la flèche de liste déroulante pour faire un choix.

REMARQUE :
Le tableau s’adapte automatiquement et se redessine…d’où le terme dynamique dans la
dénomination de cet outil !

Manipulation : Exercice [Link]

· Notre client, satisfait de ces résultats, veut créer un TCD par
service.

Il s’agit ici d’afficher les pages.

Afficher les pages

Cette commande permet de créer autant de pages qu’il y a d’éléments dans la liste du champ page.
Ces pages reproduisent un tableau croisé dynamique qui peut être modifié individuellement et chaque onglet
de feuille porte le nom de l’élément.
Pour créer des pages :

· Menu PivotTable de la barre d’outils TCD > Show Pages

· Choisir le champ à afficher
· OK

ATTENTION :
Cette fonction à l’inconvénient de créer autant de pages qu’il y a d’éléments dans le contenu du
champ page sélectionné. On peut rapidement se trouver avec beaucoup de feuilles de calculs..

Confidentiel – Tous droits réservés – Ernst & Young 2005 14

 EX CE L : TABLE AU CROI SE
DY N AM I QU E – GUI D E UTI LI SAT E U R

Manipulation : Exercice [Link]

· Mettre en forme les données chiffrées en rajoutant le symbole
Euro.
· Modifier la fonction de synthèse et remplacer la somme par la
moyenne

Modifier le format des données (DATA)

· Sélectionner une cellule de la zone DATA du TCD :

1. Cliquer sur le bouton Field Settings (Options des champs) de la barre d’outils TCD
2. Cliquer sur le bouton Number
3. Choisir la catégorie de formats et OK

Changer les fonctions de calculs

Pour changer la fonction de calcul d’un champ
· Afficher le panneau disposition du TCD
· Double-clic sur le champ pour lequel la fonction de calcul doit être changée
· Sélectionner la fonction de calcul souhaitée dans la catégorie Summarize by puis OK

Confidentiel – Tous droits réservés – Ernst & Young 2005 15

 EX CE L : TABLE AU CROI SE
DY N AM I QU E – GUI D E UTI LI SAT E U R

Ajout et modification de données sources

Manipulation : Exercice [Link]
1. Les données de la Filiale BMS viennent de vous parvenir. Le service
Téléphonie a été créé.
2. Afin de mettre à jour le TCD préalablement construit, rajouter les
données Filiale à la base d'origine.

Ajouter des données à la source

Données Filiale :

- Le responsable du service Téléphonie se nomme DURAND Hervé.

- Il a été embauché le 01/03/2005.
- Il occupe un poste de Consultant
- Son salaire annuel est de 45,000 €
- Sa cote est C
- Sa prime de fin d’année est de 1500 €

· ajouter cette ligne à la source de données de départ

· L’ajout des nouvelles données a-til été pris en compte dans le TCD ? Vérifiez…

Réponse : NON ! Car le tableau n’a pas été mis à jour et/ou les nouvelles données rajoutées ne
se trouvent pas dans la définition de la source de données de départ faite lors de la création du
TCD.

Mettre à jour un TCD (Raffraîchir les données)

ATTENTION :
Après avoir modifié les données sources, il faut mettre à jour le TCD en le sélectionnant puis
cliquer sur le bouton Refresh Data de la barre d’outils TCD :

Confidentiel – Tous droits réservés – Ernst & Young 2005 16

 EX CE L : TABLE AU CROI SE
DY N AM I QU E – GUI D E UTI LI SAT E U R

Redéfinir la source de données

ATTENTION :
Ne pas oublier qu’après avoir ajouté des lignes de données à la source, il est nécessaire de les
inclure en redéfinissant la source de données initiale.

Pour redéfinir la source de données :

· Revenir sur l’Assistant à la création du TCD
· Cliquer sur le bouton Back pour revenir à l’étape 2
· Redéfinir la source de données en y incluant les nouvelles lignes

Confidentiel – Tous droits réservés – Ernst & Young 2005 17

 EX CE L : TABLE AU CROI SE
DY N AM I QU E – GUI D E UTI LI SAT E U R

Options du TCD
Trier les données
Manipulation : Exercice [Link]
1. Dans votre TCD, faîtes apparaître les salaires annuels les plus
importants. Dans un deuxième temps, faîtes également apparaître
les moyennes annuelles les plus élevées.

Il s’agit ici d’effectuer un tri croissant sur la colonne des salaires puis sur la colonne des moyennes
annuelles.

Pour trier un TCD par ordre croissant ou décroissant :

1. Sélectionner une seule cellule de la colonne à trier
2. Cliquer sur l’icône tri croissant ou décroissant :

Manipulation : Exercice [Link]

- Pour préparer le budget de l'année prochaine, il faut connaître les
2 services pour lesquels la prime de fin d'année est la plus
importante.

Calculs personnalisés, champ calculés,

groupement des données
Confidentiel – Tous droits réservés – Ernst & Young 2005 18
 EX CE L : TABLE AU CROI SE
DY N AM I QU E – GUI D E UTI LI SAT E U R

Calculs personnalisés

Manipulation : Exercice [Link]

Votre prochaine analyse : savoir pour chaque service, le poste qui représente
le plus gros budget. Vous devez donc effectuer une analyse
(en pourcentage !) des salaires de chaque poste en fonction de la part qu'ils
représentent dans le total des salaires.

Il s’agit ici d’afficher pour chaque service, le budget que représente chaque poste en pourcentage (par
rapport au total de tous les salaires du service) et de trier ensuite ces pourcentages par ordre croissant pour
voir quels postes représentent les budgets les plus importants dans le service :

· Disposer les champs Service en page (pour faciliter la lecture et pouvoir sélectionner le service)
· Le champ Poste en ligne
· Le champ Salaire en données. Pour afficher le budget représenté pour chaque poste en
pourcentage :
o double-cliquer sur le champ salaire
o Cliquer sur le bouton option pour choisir les options d’affichage du champ
o Sélectionner % of column dans la liste déroulante Show data as :
o Cliquer sur le bouton OK pour valider
o Effectuer ensuite un tri décroissant sur la colonne salaire du TCD

Champs calculés

Manipulation : Exercice [Link]

Vous devez proposer un budget prévisionnel à notre client et simuler pour
cela une augmentation de 5% sur l'ensemble des salaires.

Confidentiel – Tous droits réservés – Ernst & Young 2005 19

 EX CE L : TABLE AU CROI SE
DY N AM I QU E – GUI D E UTI LI SAT E U R

Il s’agit ici de créer un champ calculé qui appliquera une augmentation de 5% aux salaires.
Pour créer un champ calculé :
· Sélectionner le TCD
· Barre d’outils TCD > PivotTable > Formulas > Calculated Field
· Taper le nom du champ dans la zone Name, ici augmentation
· Dans la zone formule, taper la formule qui appliquera 5% au salaire annuel.
Pour cet exemple, il faut dans un premier temps calculer 5% du salaire : (Salaire annuel * 5)/100
· ..auquel on rajoutera le salaire annuel

· Cliquer sur OK pour valider

· Un nouveau champ augmentation est créé et apparaît dans le TCD avec l’augmentation de 5%
appliquée au salaire :

Confidentiel – Tous droits réservés – Ernst & Young 2005 20

 EX CE L : TABLE AU CROI SE
DY N AM I QU E – GUI D E UTI LI SAT E U R

Grouper des éléments de texte

Manipulation : Exercice [Link]
Le client vous a demandé d'évaluer le coût global des postes de directeurs,
quelque soit le service. Vous rassemblerez au sein de votre TCD tous les
directeurs sous le nom de groupe "Directeurs".

Lorsqu’un champ compte de nombreux éléments, il est possible de former des sous-groupes et de calculer
des synthèses pour ces sous-groupes.

· Sélectionner les éléments à grouper (en ligne ou en colonne)

· Afficher tous les postes en lignes
· Faire un tri croissant pour faciliter la sélection de tous les directeurs
· Sélectionner les directeurs
· Clic-droit sur les éléments à grouper > Group and Show Detail > Group

· Le groupe est créé et est nommé Group1 par défaut. Renommer ce groupe en Directeurs

Manipulation : Exercice [Link]

Au cours de votre réunion de synthèse mission, votre client vous demandera
également d'afficher certains détails relatifs aux directeurs, comme par
exemple, leur salaire. Comment vous préparez à cette éventualité ?

…en sélectionnant Directeurs dans le champ poste qui aura été positionné en page.

Confidentiel – Tous droits réservés – Ernst & Young 2005 21

 EX CE L : TABLE AU CROI SE
DY N AM I QU E – GUI D E UTI LI SAT E U R

Extraction des lignes de données

· Double-cliquer sur une valeur de synthèse dans le tableau croisé
· Toutes les données du tableau d’origine synthétisées dans la valeur sont rassemblées sur une
feuille séparée

Mise en forme d’un TCD

Manipulation : Exercice [Link]
Mettre en forme automatiquement le tableau afin qu'il soit présentable.

· Sélectionner le TCD
· Cliquer sur bouton Format Report de la barre d’outils TCD
· Choisir un format à appliquer
· Valider en cliquant sur OK

Confidentiel – Tous droits réservés – Ernst & Young 2005 22

 EX CE L : TABLE AU CROI SE
DY N AM I QU E – GUI D E UTI LI SAT E U R

Confidentiel – Tous droits réservés – Ernst & Young 2005 23

 EX CE L : TABLE AU CROI SE
DY N AM I QU E – GUI D E UTI LI SAT E U R

ERNST & YOUNG [Link]/fr

© 2005 Ernst & Young.

Tous droits réservés.
Ernst & Young est
une marque déposée.
Studio Ernst & Young.
0404SG433

Confidentiel – Tous droits réservés – Ernst & Young 2005 24