Analyse des données Excel - Tableaux croisés dynamiques

L'analyse des données sur un grand ensemble de données est souvent nécessaire et importante. Il s'agit de résumer les données, d'obtenir les valeurs nécessaires et de présenter les résultats.

Excel fournit un tableau croisé dynamique pour vous permettre de résumer des milliers de valeurs de données facilement et rapidement afin d'obtenir les résultats requis.

Considérez le tableau suivant de données sur les ventes. À partir de ces données, vous devrez peut-être résumer le total des ventes par région, par mois ou par vendeur. Le moyen simple de gérer ces tâches consiste à créer un tableau croisé dynamique que vous pouvez modifier dynamiquement pour résumer les résultats comme vous le souhaitez.

Créer un tableau croisé dynamique

Pour créer des tableaux croisés dynamiques, assurez-vous que la première ligne comporte des en-têtes.

Cliquez sur le tableau.
Cliquez sur l'onglet INSÉRER sur le ruban.
Cliquez sur Tableau croisé dynamique dans le groupe Tables. La boîte de dialogue Tableau croisé dynamique s'affiche.

Comme vous pouvez le voir dans la boîte de dialogue, vous pouvez utiliser une table ou une plage du classeur actuel ou utiliser une source de données externe.

Dans la zone Table / Plage, saisissez le nom de la table.
Cliquez sur Nouvelle feuille de calcul pour indiquer à Excel où conserver le tableau croisé dynamique.
Cliquez sur OK.

Un tableau croisé dynamique vide et une liste de champs de tableau croisé dynamique apparaissent.

Tableaux croisés dynamiques recommandés

Si vous êtes nouveau dans les tableaux croisés dynamiques ou que vous ne savez pas quels champs sélectionner dans les données, vous pouvez utiliser le Recommended PivotTables fourni par Excel.

Cliquez sur le tableau de données.
Cliquez sur l'onglet INSÉRER.
Cliquez sur Tableaux croisés dynamiques recommandés dans le groupe Tables. La boîte de dialogue Tableaux croisés dynamiques recommandés s'affiche.

Dans la boîte de dialogue Tableau croisé dynamique recommandé, les tableaux croisés dynamiques personnalisés possibles qui correspondent à vos données sont affichés.

Cliquez sur chacune des options de tableau croisé dynamique pour voir l'aperçu sur le côté droit.
Cliquez sur le tableau croisé dynamique Somme du montant de la commande par vendeur et par mois.

Cliquez sur OK. Le tableau croisé dynamique sélectionné apparaît dans une nouvelle feuille de calcul. Vous pouvez observer les champs de tableau croisé dynamique qui ont été sélectionnés dans la liste des champs de tableau croisé dynamique.

Champs de tableau croisé dynamique

Les en-têtes de votre table de données apparaîtront sous forme de champs dans le tableau croisé dynamique.

Vous pouvez les sélectionner / les désélectionner pour modifier instantanément votre tableau croisé dynamique pour afficher uniquement les informations souhaitées et de la manière que vous souhaitez. Par exemple, si vous souhaitez afficher les informations du compte au lieu des informations sur le montant de la commande, désélectionnez Montant de la commande et sélectionnez Compte.

Zones de tableau croisé dynamique

Vous pouvez même modifier instantanément la disposition de votre tableau croisé dynamique. Vous pouvez utiliser les zones de tableau croisé dynamique pour ce faire.

Dans les zones de tableau croisé dynamique, vous pouvez choisir -

Quels champs afficher sous forme de lignes
Quels champs afficher sous forme de colonnes
Comment résumer vos données
Filtres pour l'un des champs
Quand mettre à jour votre disposition de tableau croisé dynamique
- Vous pouvez le mettre à jour instantanément en faisant glisser les champs dans les zones, ou
- Vous pouvez différer la mise à jour et la mettre à jour uniquement lorsque vous cliquez sur MISE À JOUR

Une mise à jour instantanée vous aide à jouer avec les différentes mises en page et à choisir celle qui convient à vos besoins en matière de rapport.

Vous pouvez simplement faire glisser les champs sur ces zones et observer la disposition du tableau croisé dynamique pendant que vous le faites.

Imbrication dans le tableau croisé dynamique

Si vous avez plus d'un champ dans l'une des zones, l'imbrication se produit dans l'ordre dans lequel vous placez les champs dans cette zone. Vous pouvez modifier l'ordre en faisant glisser les champs et observer comment l'imbrication change. Dans les options de mise en page ci-dessus, vous pouvez observer que

Les mois sont en colonnes.
Région et vendeur en lignes dans cet ordre. c'est-à-dire que les valeurs de vendeur sont imbriquées sous les valeurs de région.
Le résumé se fait par somme du montant de la commande.
Aucun filtre n'est choisi.

Le tableau croisé dynamique résultant est le suivant -

Dans les zones de tableau croisé dynamique, dans les lignes, cliquez sur la région et faites-la glisser sous le vendeur de telle sorte qu'elle ressemble à ceci:

L'ordre d'imbrication change et le tableau croisé dynamique résultant est le suivant:

Note- Vous pouvez clairement observer que la mise en page avec l'ordre d'imbrication - Région puis vendeur donne un rapport meilleur et compact que celui avec l'ordre d'imbrication - Vendeur puis Région. Dans le cas où le vendeur représente plus d'un domaine et que vous devez résumer les ventes par vendeur, la deuxième mise en page aurait été une meilleure option.

Filtres

Vous pouvez attribuer un filtre à l'un des champs afin de pouvoir modifier dynamiquement le tableau croisé dynamique en fonction des valeurs de ce champ.

Faites glisser la région des lignes vers les filtres dans les zones de tableau croisé dynamique.

Le filtre avec l'étiquette comme région apparaît au-dessus du tableau croisé dynamique (au cas où vous n'avez pas de lignes vides au-dessus de votre tableau croisé dynamique, le tableau croisé dynamique est poussé vers le bas pour faire de la place pour le filtre.

Vous pouvez voir que -

Les valeurs du vendeur apparaissent dans les lignes.
Les valeurs du mois apparaissent dans les colonnes.
Le filtre de région apparaît en haut avec la sélection par défaut comme TOUT.
La valeur récapitulative est la somme du montant de la commande
- La somme du montant de la commande par vendeur apparaît dans la colonne Total général
- La somme du montant de la commande par mois apparaît dans la ligne Total général

Cliquez sur la flèche dans la zone à droite de la zone de filtre. Une liste déroulante avec les valeurs de la zone de champ apparaît.

Cochez l'option Select Multiple Items. Des cases à cocher apparaissent pour toutes les valeurs.
Sélectionnez Sud et Ouest, désélectionnez les autres valeurs et cliquez sur OK.

Les données relatives aux régions du Sud et de l'Ouest uniquement seront résumées comme indiqué dans la capture d'écran ci-dessous -

Vous pouvez voir qu'à côté de la région de filtrage, Multiple Itemss'affiche, indiquant que vous avez sélectionné plus d'un élément. Cependant, le nombre d'éléments et / ou les éléments sélectionnés ne sont pas connus à partir du rapport affiché. Dans un tel cas, l'utilisation de Slicers est une meilleure option pour le filtrage.

Trancheurs

Vous pouvez utiliser des segments pour avoir une meilleure clarté sur les éléments sur lesquels les données ont été filtrées.

Cliquez sur ANALYSER sous OUTILS PIVOTABLES sur le ruban.
Cliquez sur Insérer un segment dans le groupe Filtre. La boîte de dialogue Insérer des segments apparaît. Il contient tous les champs de vos données.
Sélectionnez les champs Région et mois. Cliquez sur OK.

Les segments pour chacun des champs sélectionnés apparaissent avec toutes les valeurs sélectionnées par défaut. Les outils de segment apparaissent sur le ruban pour travailler sur les paramètres, l'apparence et la convivialité du segment.

Sélectionnez Sud et Ouest dans le segment de la région.
Sélectionnez Février et Mars dans le Slicer pour le mois.
Maintenez la touche Ctrl enfoncée tout en sélectionnant plusieurs valeurs dans un segment.

Les éléments sélectionnés dans les segments sont mis en surbrillance. Un tableau croisé dynamique avec des valeurs résumées pour les éléments sélectionnés sera affiché.

Récapitulation des valeurs par d'autres calculs

Dans les exemples jusqu'à présent, vous avez vu la synthèse des valeurs par Sum. Cependant, vous pouvez également utiliser d'autres calculs si nécessaire.

Dans la liste des champs de tableau croisé dynamique

Sélectionnez le compte de terrain.
Désélectionnez le montant de la commande de champ.

Faites glisser le champ Compte vers la zone Synthèse des valeurs. Par défaut, Sum of Account sera affiché.
Cliquez sur la flèche sur le côté droit de la boîte.
Dans la liste déroulante qui apparaît, cliquez sur Paramètres du champ de valeur.

La boîte de dialogue Paramètres du champ de valeur apparaît. Plusieurs types de calculs apparaissent sous forme de liste sous le champ Récapituler la valeur par -

Sélectionnez Count dans la liste.
Le nom personnalisé devient automatiquement Nombre de comptes. Cliquez sur OK.

Le tableau croisé dynamique récapitule les valeurs de compte par nombre.

Outils de tableau croisé dynamique

Suivez les étapes ci-dessous pour apprendre à utiliser les outils de tableau croisé dynamique.

Sélectionnez le tableau croisé dynamique.

Les outils de tableau croisé dynamique suivants apparaissent sur le ruban -

ANALYZE
DESIGN

ANALYSER

Certains ANALYZE Les commandes du ruban sont -

Définir les options du tableau croisé dynamique
Paramètres du champ de valeur pour le champ sélectionné
Développer le champ
Réduire le champ
Insérer un segment
Insérer une chronologie
Actualiser les données
Changer la source de données
Déplacer le tableau croisé dynamique
Ordre de résolution (s'il y a plus de calculs)
PivotChart

CONCEPTION

Certains DESIGN Les commandes du ruban sont -

Disposition du tableau croisé dynamique
- Options pour les sous-totaux
- Options pour les totaux généraux
- Formulaires de mise en page de rapport
- Options pour les lignes vides
Options de style de tableau croisé dynamique
Styles de tableau croisé dynamique

Champ d'extension et de réduction

Vous pouvez développer ou réduire tous les éléments d'un champ sélectionné de deux manières:

En sélectionnant le symbole

ou

à gauche du champ sélectionné.
En cliquant sur le champ Développer ou Réduire le champ sur le ruban ANALYSER.

En sélectionnant le symbole Développer

ou Réduire le symbole

à gauche du champ sélectionné

Sélectionnez la cellule contenant l'Est dans le tableau croisé dynamique.
Cliquez sur le symbole Réduire

à gauche de l'Est.

Tous les éléments sous Est seront réduits. Le symbole Réduire

à gauche de l'Est se transforme en symbole Développer

Vous pouvez observer que seuls les éléments en dessous de l'Est sont réduits. Les autres éléments du tableau croisé dynamique sont tels quels.

Cliquez sur le symbole Développer

à gauche de l'Est. Tous les éléments ci-dessous Est seront affichés.

Utilisation d'ANALYSE sur le ruban

Vous pouvez réduire ou développer tous les éléments du tableau croisé dynamique à la fois avec les commandes Développer le champ et Réduire le champ du ruban.

Cliquez sur la cellule contenant l'Est dans le tableau croisé dynamique.
Cliquez sur l'onglet ANALYSER sur le ruban.
Cliquez sur Réduire le champ dans le groupe Champ actif.

Tous les éléments du champ Est dans le tableau croisé dynamique seront réduits.

Cliquez sur Développer le champ dans le groupe Champ actif.

Tous les éléments seront affichés.

Styles de présentation des rapports

Vous pouvez choisir le style de présentation de votre tableau croisé dynamique comme vous le feriez en tant que rapport. Sélectionnez un style adapté au reste de votre présentation ou de votre rapport. Cependant, ne vous ennuyez pas trop avec les styles car un rapport qui donne un impact sur l'affichage des résultats est toujours meilleur qu'un rapport coloré, qui ne met pas en évidence les points de données importants.

Cliquez sur Est dans le tableau croisé dynamique.
Cliquez sur ANALYSER.
Cliquez sur Paramètres de champ dans le groupe Champ actif. La boîte de dialogue Paramètres du champ s'affiche.
Cliquez sur l'onglet Mise en page et impression.
Cochez Insérer une ligne vierge après chaque étiquette d'élément.

Des lignes vides seront affichées après chaque valeur du champ Région.

Vous pouvez insérer des lignes vides à partir du DESIGN onglet également.

Cliquez sur l'onglet CONCEPTION.
Cliquez sur Mise en page du rapport dans le groupe Mise en page.
Sélectionnez Afficher sous forme de plan dans la liste déroulante.

Passez la souris sur les styles de tableau croisé dynamique. Un aperçu du style sur lequel la souris est placée apparaîtra.
Sélectionnez le style qui convient à votre rapport.

Le tableau croisé dynamique sous forme de contour avec le style sélectionné sera affiché.

Chronologie dans les tableaux croisés dynamiques

Pour comprendre comment utiliser la chronologie, considérons l'exemple suivant dans lequel les données de vente de divers articles sont données par vendeur et par emplacement. Il y a au total 1891 lignes de données.

Créez un tableau croisé dynamique à partir de cette plage avec -

Emplacement et vendeur dans les lignes dans cet ordre
Produit en colonnes
Somme des montants dans les valeurs de synthèse

Cliquez sur le tableau croisé dynamique.
Cliquez sur l'onglet INSÉRER.
Cliquez sur Chronologie dans le groupe Filtres. Les chronologies d'insertion apparaissent.