Pourquoi utiliser un tableau croisé dynamique Excel ? comment créer un tableau croisé dynamique ? Conseils pour sélectionner et placer les champs dans votre tableau croisé dynamique. Exemple de tableau croisé dynamique !

 

1- Pourquoi utiliser un tableau croisé dynamique ?

La possibilité d’analyser toutes les données d’un tableau peut vous aider à prendre de meilleures décisions professionnelles (en faisant ressortir les éléments significatifs et révélant les tendances).

Mais, il est difficile de savoir par où commencer quand vous avez de très nombreuses données (par exemple des centaines de lignes et des dizaines de colonnes).

pas facile de calculer le montant des ventes de Christian par type de produits !

Excel peut vous aider en créant des tableaux croisés dynamiques, qui permettent de synthétiser, d’explorer et de présenter vos données. Vous pourrez ainsi analyser vos données selon différentes perspectives.

Le principe général est :

1. de limiter le nombre de colonnes (ex: vendeur, produit, montant vente) à analyser,
2. d’identifier les colonnes que vous souhaitez croiser entre elles (ex: vendeur et produit) pour votre analyse des données,
3. d’agréger l’ensemble des valeurs d’une colonne ‘numérique’ (ex: montant vente) relatives à vos colonnes croisées en les remplaçant par la somme ou la moyenne des valeurs, ou le nombre de valeurs,

2- Comment construire un tableau croisé dynamique ?

1. Assurez-vous que vos données ont des en-têtes de colonnes ou des en-têtes de tableaux, et qu’elles ne contiennent pas de lignes vides.
2. Cliquez sur n’importe quelle cellule dans la plage de cellules ou dans le tableau.
3. Cliquez sur Insertion > Tableaux croisés dynamiques. Choisir l’option “créez dans nouvelle feuille”.
4. Excel crée alors un tableau croisé dynamique vide dans lequel vous pouvez ajouter vos propres champs et choisir une disposition.
5. A droite, dans la liste des champs, cochez la case à coté de chaque champ à inclure dans votre tableau croisé dynamique.
   Par défaut, les champs non numériques sont automatiquement ajoutés à la zone LIGNES, les champs numériques sont ajoutés à la zone VALEURS.
6. mais vous pouvez réorganiser les champs vous-mêmes en faisant :
   cliquez et maintenir un nom de champ, puis le faire glisser de la section de champ vers une des 4 zones de la section de disposition (en bas à droite) : LIGNES, COLONNES, VALEURS ou FILTRES.

Astuce :  pour supprimer un champ, faites le glisser en dehors de la section disposition.

3- disposition des champs dans votre tableau croisé dynamique

Avec le paramétrage suivant :

• liste des champs cochés : vendeur, produit, montant vente
• zone LIGNES : vendeur
• zone COLONNES : produit
• zone VALEURS : montant vente avec la fonction de synthèse : somme
• zone : FILTRES :

 

La disposition des champs dans votre tableau croisé dynamique sera la suivante :

les champs de la zone Filtres s’affichent comme des filtres de rapport de premier niveau au-dessus du tableau croisé dynamique,

Le titre de votre tableau croisé numérique s’intitule : “tableau des nom du champ de la zone valeurs par étiquette de lignes et par étiquette de colonne”.

Exemple : tableau des ventes par vendeur et par produit.

4- Conseils pour sélectionner et placer les champs dans votre tableau croisé dynamique

 

4.1- travailler comme un explorateur (aventurier).

Vous ne savez pas encore ce que vous allez découvrir ni quels sont les éléments significatifs/remarquables qui vont émerger de votre analyse des données.
Vous devrez donc examiner vos données selon différentes perspectives.

Exemples :

• montants des ventes par vendeur et par produit
• montants des ventes par vendeur et par produit pour le mois de mai
• montants des ventes par mois et par vendeur
• montant des ventes par produit et par région …

4.2- travailler en mode itération

N’analyser qu’une seule perspective à la fois (et ensuite passer à la suivante).

Cela signifie de ne travailler au maximun qu’avec 2 ou 3 ou 4 champs significatifs pour la perspective traitée dans votre tableau croisé numérique.

Exemple : vendeur, produit, montant vente pour la perspective : montants des ventes par vendeur et par produit

4.3- identifier les champs numériques significatifs

a) Identifiez les champs numériques significatifs pour lesquels une synthèse des données sous la forme de somme, moyenne ou nombre de valeurs, a du sens.

Ces champs seront placés dans la zone valeurs. Exemple : montant vente.

b) Identifiez aussi pour chacun la fonction de synthèse: somme, moyenne, NB, Min ou Max la plus pertinente pour votre analyse.
Par défaut, Excel propose la fonction somme pour les champs numériques et la fonction Nombre pour les champs non numériques.
Pour modifier cette fonction, faîtes un clic droit sur le champ de la zone VALEURS puis cliquez sur “paramètres des champs de valeur“.

Exemple : la somme des ventes a du sens mais une somme des années 2000 à 2015 n’a pas de sens alors que la moyenne des années aurait du sens dans certains cas

4.4- identifiez ensuite, les autres champs significatifs

Identifiez les autres champs significatifs pour lesquels vous souhaitez examiner les séries de données des champs numériques de la zone valeurs :

a) placez de préférence un champ non numérique en étiquettes de ligne
Exemple : par vendeur …

b) placez en étiquette de colonne un champ que vous souhaitez croiser avec votre champ déjà en étiquette de ligne. 

Conseil : Le croisement de l’étiquette de ligne avec l’étiquette de colonne doit avoir du sens pour votre analyse des données.
Exemple : par produit ou par région ou par mois …

5- Exemples de tableau croisé dynamique

Vous pouvez télécharger cet exemple avec ce lien : Excel_Tableau_croise_1
et mettre en pratique vous-mêmes ces conseils.

5.1- tableau initial

On prend comme exemple le tableau complet des ventes de 2009 à 2010 de l’entreprise Kiventout. L’onglet “exo vente” du classeur Excel contient le tableau initial. 

5.2- tableau croisé dynamique du montant des ventes par vendeur et par produit

Sélectionnez toute la feuille de calcul (Ctrl A) cliquez sur Insertion> tableau croisé dynamique pour créer un « tableau croisé dynamique» avec le paramétrage suivant :

• liste des champs cochés : vendeur, produit, montant vente
• zone LIGNES : vendeur
• zone COLONNES : produit
• zone VALEURS : montant vente avec la fonction de synthèse : somme
• zone : FILTRES :

Ce paramétrage indique que les données ayant le même VENDEUR seront agrégées sur la même ligne, que les données ayant le même PRODUIT seront agrégées dans la même colonne, et qu’à l’intérieur du tableau, seront affichées uniquement les sommes de MONTANT VENTE correspondant à l’intersection VENDEUR, PRODUIT.

Ce tableau fournit un résumé très synthétique du tableau initial – qui comptait 6571 lignes – sur lequel on lit plus facilement le résultat des vendeurs par produits.

On peut constater que Christian est le meilleur vendeur (et Dominique la moins bonne vendeuse), que le produit rapportant le plus d’euros est le téléviseur.

5.3- tableau croisé dynamique du montant des ventes par vendeur et par produit pour le mois de mai

Sélectionnez toute la feuille de calcul cliquez sur Insertion> tableau croisé dynamique pour créer un « tableau croisé dynamique» avec le paramétrage suivant.

• liste des champs cochés : mois, vendeur, produit, montant vente
  
• zone LIGNES : vendeur
• zone COLONNES : produit
• zone VALEURS : montant vente avec la fonction de synthèse : somme
• zone : FILTRES : mois

Ce paramétrage indique qu’un filtre est activé en cellules A1 et A2.  Cliquez sur la flèche  situé en cellule A2 pour faire apparaître la liste des éléments de filtre puis cochez la case “sélectionner plusieurs éléments“ en bas de la liste, puis décochez la case “tous” avant de cocher les cases des éléments à afficher (ici le mois de Mai) dans votre tableau croisé dynamique.

astuce : égayez votre tableau croisé dynamique, en choississant un style parmi ceux prédéfinis dans le menu ‘style de tableau croisé dynamique’. Pour ce faire, se positionner dans le tableau croisé puis cliquez sur l’onglet “création” de l’onglet “outils de tableau croisé dynamique“.

5.4- tableau croisé dynamique du montant des ventes par produit et par région

Sélectionnez toute la feuille de calcul cliquez sur Insertion> tableau croisé dynamique pour créer un « tableau croisé dynamique» avec le paramétrage suivant.

• liste des champs cochés : région, produit, montant vente
• zone LIGNES : produit
• zone COLONNES : région
• zone VALEURS : montant vente avec la fonction de synthèse : somme
• zone : FILTRES :

On peut constater ici que la région “Centre” réalise les meilleurs ventes et que le produit ‘téléviseur’ génère les meilleurs revenus.

Astuce : vous pouvez retravailler la mise en forme du tableau croisé dynamique en choisissant par exemple un style de tableau et en remplissant certaines cellules que vous vouler faire ressortir.

5.5- tableau croisé dynamique du montant des ventes par mois et par vendeur

Sélectionnez toute la feuille de calcul cliquez sur Insertion> tableau croisé dynamique pour créer un « tableau croisé dynamique» avec le paramétrage suivant.

• liste des champs cochés : mois, vendeur, montant vente
• zone LIGNES : mois
• zone COLONNES : vendeur
• zone VALEURS : montant vente avec la fonction de synthèse : somme
• zone : FILTRES :

 

On conclura cette analyse en générant à partir de ce tableau synthétique, un graphique qui permettra de donner une vision globale des évolutions constatées. Les séries de données – en colonnes – ont été représentées par des histogrammes empilés, permettant de montrer pour chaque mois la répartition des montants de vente entre les différents vendeurs.

6- Tableau croisé numérique : pour aller plus loin

6.1- Comment modifier la fonction de synthèse des champs numériques

Pour synthétiser (agréger) des valeurs dans un tableau croisé dynamique, vous pouvez utiliser les fonctions de synthèse (agrégation), telles que Somme, Nombre et Moyenne.

La fonction Somme est utilisée par défaut pour les champs de valeurs numériques que vous placez dans votre tableau croisé dynamique, mais vous pouvez choisir une autre fonction de synthèse en procédant ainsi :

1. Dans le tableau croisé dynamique, cliquez avec le bouton droit sur le champ de valeur à modifier, puis cliquez sur Synthétiser les valeurs par.

1. enfin, cliquez sur la fonction de synthèse souhaitée.

 

6.2- Ajouter des sous-totaux à un tableau croisé dynamique

Dans un tableau croisé dynamique, les sous-totaux sont calculés automatiquement et affichés par défaut. Mais si vous ne les voyez pas, vous pouvez les ajouter.

1. Cliquez n’importe où dans le tableau croisé dynamique pour afficher les Outils de tableau croisé dynamique sur le ruban.
2. Cliquez sur Création> Sous-totaux, puis sélectionnez Afficher tous les sous-totaux au bas du groupe ou Afficher tous les sous-totaux en haut du groupe.

 Astuce : vous pouvez aussi déactiver les totaux généraux par ligne et/ou par colonne en cliquant sur Création> Totaux généraux, puis sélectionnez Activer ou désactiver pour les lignes et/ou colonnes

6.3- Comment mettre à jour les données d’un tableau croisé numérique

Si la source de données a été modifiée, vous pouvez cliquer sur Actualiser pour mettre à jour les données des tableaux croisés dynamiques de votre classeur.

6.4- Comment supprimer un tableau croisé numérique

Pour supprimer un tableau croisé dynamique dont vous n’avez plus besoin, sélectionnez-le dans son intégralité et appuyez sur Suppr.

6.5- Filtrer les données manuellement

1- Dans le tableau croisé dynamique, cliquez sur la flèche   sur Étiquettes de lignes ou Étiquettes de colonnes.

2- Dans la liste des étiquettes de ligne ou de colonne, décochez la case “Tout sélectionner“ en haut de la liste, puis cochez les cases pour les éléments à afficher dans votre tableau croisé dynamique.

Pour afficher davantage d’éléments dans la liste, faites glisser la poignée dans le coin inférieur droit de la galerie de filtres pour l’agrandir.

3- Cliquez sur OK. L’icône de la flèche de filtrage se présente désormais ainsi   pour indiquer qu’un filtre est appliqué. Cliquez dessus pour modifier le filtre ou le supprimer en cliquant sur Effacer le filtre de <nom de champ>.

CONSEIL   Pour supprimer tous les filtres en une fois, cliquez n’importe où sur le tableau croisé dynamique, puis cliquez sur Analyse > Effacer > Effacer les filtres.

6.6- Comment modifier le titre de vos étiquettes de colonnes

Positionnez vous sur le titre de la colonne à modifier puis cliquez (clic droit)  et sélectionnez paramètres des champs de valeurs et modifier le titre dans le champs nom personnalisé. Cliquez sur OK.

 

 Remarque : En fonction de la hiérarchie des champs, les colonnes (ou lignes) peuvent être imbriquées dans d’autres colonnes (ou lignes) de niveau supérieur.