Excel :tableaux Croises Dynamiques

TSTDI1GB 2007/2008

PERFECTIONNEMENT EXCEL

TABLEAUX CROISES DYNAMIQUES Qu'est ce qu'un tableau croisé dynamique ? Création d'un tableau croisé Comparer des valeurs entre elles Comparer des valeurs avec le total Ajouter un champ calculé Qu'est ce qu'un tableau croisé dynamique ? Un tableau croisé dynamique est un analyseur de données. Ces données sont généralement issues d'une liste Excel, mais peuvent également provenir de données externes. La création d'un tableau croisé dynamique est assistée par Excel. A partir d'une liste d'éléments contenus dans une liste, vous allez pouvoir obtenir des informations croisant plusieurs critères. Par exemple, prenons la liste ci-dessous. Elle représente le nombre de véhicule d'occasion vendus au cours d'un semestre. Ces véhicules sont classés par marque, par vendeur et par type de carburant. Origine Citroen

Carburant Diesel

Vendeur Alain

Citroen

Essence

Alain

4

Citroen

Diesel

Pierre

7

Citroen

Essence

Pierre

1

Fiat

Diesel

Alain

7

Fiat

Essence

Alain

4

Fiat

Diesel

Pierre

9

Fiat

Essence

Pierre

1

Ford

Diesel

Alain

8

Ford

Essence

Alain

7

Ford

Diesel

Pierre

10

Ford

Essence

Pierre

4

Opel

Diesel

Alain

5

Opel

Essence

Alain

4

Opel

Diesel

Pierre

7

M8 LOGICIEL D’APPLICATIONS

Nombre 5

FORMATEUR Mr AZZI

TSTDI1GB 2007/2008

PERFECTIONNEMENT EXCEL

Opel Peugeot

Essence Diesel

Pierre Alain

1 6

Peugeot

Essence

Alain

7

Peugeot

Diesel

Pierre

8

Peugeot

Essence

Pierre

4

Renault

Diesel

Alain

13

Renault

Essence

Alain

9

Renault

Diesel

Pierre

15

Renault

Essence

Pierre

7

Seat

Diesel

Alain

3

Seat

Essence

Alain

2

Seat

Diesel

Pierre

5

Seat

Essence

Pierre

3

A l'aide de l'assistant on va chercher à obtenir 2 tableaux : 1/ Le nombre de véhicules diesel et essence vendus par chaque vendeur. 2/ Pour chaque marque le nombre de véhicules vendus par type de carburant et par vendeur. Création d'un tableau croisé Exemple 1 : Le nombre de véhicules diesel et essence vendus par chaque vendeur.

Pour créer un tableau croisé, a) on sélectionne une cellule de la liste de données, b) on clique sur le menu " données - rapport de tableau croisé dynamique ", on obtient la boîte de dialogue suivante (étape 1) :

M8 LOGICIEL D’APPLICATIONS

FORMATEUR Mr AZZI

TSTDI1GB 2007/2008

PERFECTIONNEMENT EXCEL

On remarque dans cette boîte de dialogue que le tableau peut être construit à partir de données de provenances diverses. Ici nous choisirons, bien sûr, la liste de données Excel. c) on sélectionne " Liste… " et on clique sur le bouton " Suivant " d) la boîte de dialogue suivante nous indique la plage de données qui sera prise en compte pour la construction du tableau (étape 2). Vérifiez et cliquez sur le bouton " Suivant " e) C'est à partir de la boîte de dialogue suivante que vous allez indiquer les données à prendre en compte (étape 3)

M8 LOGICIEL D’APPLICATIONS

FORMATEUR Mr AZZI

TSTDI1GB 2007/2008

PERFECTIONNEMENT EXCEL

f) A l'aide de la souris on va faire glisser les étiquettes de champs de la liste vers les emplacements désirés dans le futur tableau. Le résultat obtenu sera le suivant :

g) en cliquant sur le bouton " Suivant " on obtient la dernière boîte de dialogue de l'assistant (étape 4) sur laquelle vous précisez si vous souhaitez voir le tableau sur un nouvelle feuille de classeur ou sur la feuille contenant la liste (dans ce cas il vous sera demandé d'indiquer à partir de quelle cellule de la feuille le tableau sera tracé). On obtient le résultat suivant :

Exemple 2 : Pour chaque marque (appelée dans notre liste " origine ") le nombre de véhicules vendus, par type de carburant et par vendeur. Pour obtenir ce tableau, procéder comme pour l'exemple 1. A l'étape 3 de l'assistant vous placerez vos champs de la façon suivante : M8 LOGICIEL D’APPLICATIONS

FORMATEUR Mr AZZI

TSTDI1GB 2007/2008

PERFECTIONNEMENT EXCEL

le résultat obtenu sera le suivant (extrait) :

Comparer des valeurs entre elles A partir d'un tableau réalisé, il est possible d'obtenir autre chose qu'une somme (ici la somme des véhicules vendus). Par exemple, il est possible d'obtenir un comparatif en pourcentage des ventes de véhicules diesel par rapport aux véhicules essence.

M8 LOGICIEL D’APPLICATIONS

FORMATEUR Mr AZZI

TSTDI1GB 2007/2008

PERFECTIONNEMENT EXCEL

Reprenons le 1er tableau : a) On clique, par exemple, sur la cellule C3 avec le bouton droit de la souris, b) dans le le menu contextuel on choisi "champs", c) on clique sur le bouton "options >>" d) dans la zone "Afficher les données", on sélectionne "Différence en % par rapport" e) cliquez "carburant" dans la liste "champs de base" puis "diesel" dans la liste "éléments de base", f) cliquez sur OK pour valider les nouveaux calculs. Le résultat fait apparaître que Alain a vendu 21,28% de véhicules essence de moins que de véhicules diesel. Pierre, lui en a vendu 65.57 de moins. Comparer des valeurs avec le total Reprenons le 2e tableau :

M8 LOGICIEL D’APPLICATIONS

FORMATEUR Mr AZZI

TSTDI1GB 2007/2008

PERFECTIONNEMENT EXCEL

a) On clique, par exemple, sur la cellule C7 avec le bouton droit de la souris, b) dans le le menu contextuel on choisi "champs", c) on clique sur le bouton "options >>" d) dans la zone "Afficher les données", on sélectionne " % du total" e) cliquez sur OK pour valider les nouveaux calculs.

Vous obtenez ainsi le pourcentage que représente chaque vente de véhicule par vendeur et par marque par rapport au total des véhicules vendus.

Ajouter un champ calculé A partir d'un tableau existant, il est possible de faire des projections. Par exemple, compléter le tableau des ventes de véhicules, par vendeur et par type de carburant, en ajoutant une colonne tenant compte d'une progression des ventes de 15%. M8 LOGICIEL D’APPLICATIONS

FORMATEUR Mr AZZI

TSTDI1GB 2007/2008

PERFECTIONNEMENT EXCEL

1/ En cliquant sur un nombre du tableau avec le bouton droit de la souris, on obtient le menu contextuel suivant :

2/ dans la boîte de dialogue : a) changer le nom (ici on a écrit "prévisions"), b) sélectionner "Nombre" dans la liste des champs, c) cliquer sur le bouton "insérer un champ" d) dans la zone "formule" complétez en multipliant par 1.15.

M8 LOGICIEL D’APPLICATIONS

FORMATEUR Mr AZZI

TSTDI1GB 2007/2008

PERFECTIONNEMENT EXCEL

On obient le résultat suivant :

M8 LOGICIEL D’APPLICATIONS

FORMATEUR Mr AZZI