Tableaux de bord Excel - Tableaux croisés dynamiques

Si vous avez vos données dans un seul tableau Excel, vous pouvez résumer les données de la manière requise à l'aide des tableaux croisés dynamiques Excel. Un tableau croisé dynamique est un outil extrêmement puissant que vous pouvez utiliser pour découper et découper des données. Vous pouvez suivre, analyser des centaines de milliers de points de données avec une table compacte qui peut être modifiée dynamiquement pour vous permettre de trouver les différentes perspectives des données. C'est un outil simple à utiliser, mais puissant.

Excel vous offre un moyen plus puissant de créer un tableau croisé dynamique à partir de plusieurs tables, de différentes sources de données et de sources de données externes. Il est nommé Power PivotTable qui fonctionne sur sa base de données appelée modèle de données. Vous apprendrez à connaître Power PivotTable et d'autres outils électriques Excel tels que Power PivotChart et Power View Reports dans d'autres chapitres.

Les tableaux croisés dynamiques, les tableaux croisés dynamiques Power, les graphiques croisés dynamiques Power et les rapports Power View sont utiles pour afficher les résultats résumés des ensembles de données volumineux sur votre tableau de bord. Vous pouvez maîtriser le tableau croisé dynamique normal avant de vous aventurer dans les outils électriques.

Créer un tableau croisé dynamique

Vous pouvez créer un tableau croisé dynamique à partir d'une plage de données ou d'un tableau Excel. Dans les deux cas, la première ligne des données doit contenir les en-têtes des colonnes.

Vous pouvez commencer avec un tableau croisé dynamique vide et le créer à partir de zéro ou utiliser la commande Tableau croisé dynamique recommandé par Excel pour prévisualiser les tableaux croisés dynamiques personnalisés possibles pour vos données et en choisir un qui convient à votre objectif. Dans les deux cas, vous pouvez modifier un tableau croisé dynamique à la volée pour obtenir des informations sur les différents aspects des données disponibles.

Considérez la plage de données suivante qui contient les données de ventes pour chaque commercial, dans chaque région et au cours des mois de janvier, février et mars -

Pour créer un tableau croisé dynamique à partir de cette plage de données, procédez comme suit:

Assurez-vous que la première ligne contient des en-têtes. Vous avez besoin d'en-têtes, car ils seront les noms de champ dans votre tableau croisé dynamique.
Nommez la plage de données comme SalesData_Range.
Cliquez sur la plage de données - SalesData_Range.
Cliquez sur l'onglet INSÉRER sur le ruban.
Cliquez sur Tableau croisé dynamique dans le groupe Tables.

La boîte de dialogue Créer un tableau croisé dynamique s'affiche.

Comme vous pouvez le constater, dans la boîte de dialogue Créer un tableau croisé dynamique, sous Choisir les données que vous souhaitez analyser, vous pouvez sélectionner un tableau ou une plage dans le classeur actuel ou utiliser une source de données externe. Par conséquent, vous pouvez utiliser les mêmes étapes pour créer un formulaire de tableau croisé dynamique soit une plage ou un tableau.

Cliquez sur Sélectionner une table ou une plage.
Dans la zone Table / Plage, saisissez le nom de la plage - SalesData_Range.
Cliquez sur Nouvelle feuille de calcul sous Choisissez où vous souhaitez placer le rapport de tableau croisé dynamique.

Vous pouvez également observer que vous pouvez choisir d'analyser plusieurs tables en ajoutant cette plage de données au modèle de données. Le modèle de données est la base de données Excel Power Pivot.

Cliquez sur le bouton OK. Une nouvelle feuille de calcul sera insérée dans votre classeur. La nouvelle feuille de calcul contient un tableau croisé dynamique vide.
Nommez la feuille de calcul - Range-PivotTable.

Comme vous pouvez le constater, la liste des champs de tableau croisé dynamique apparaît sur le côté droit de la feuille de calcul, contenant les noms d'en-tête des colonnes de la plage de données. En outre, sur le ruban, les outils de tableau croisé dynamique - ANALYSE et CONCEPTION apparaissent.

Vous devez sélectionner les champs de tableau croisé dynamique en fonction des données que vous souhaitez afficher. En plaçant les champs dans les zones appropriées, vous pouvez obtenir la mise en page souhaitée pour les données. Par exemple, pour résumer le montant de la commande par vendeur pour les mois de janvier, février et mars, vous pouvez effectuer les opérations suivantes:

Cliquez sur le champ Vendeur dans la liste Champs du tableau croisé dynamique et faites-le glisser vers la zone ROWS.
Cliquez sur le champ Mois dans la liste Champs de tableau croisé dynamique et faites-le glisser également vers la zone ROWS.
Cliquez sur Montant de la commande et faites-le glisser vers la zone ∑ VALEURS.

Votre tableau croisé dynamique est prêt. Vous pouvez modifier la disposition du tableau croisé dynamique en faisant simplement glisser les champs sur les zones. Vous pouvez sélectionner / désélectionner des champs dans la liste Champs de tableau croisé dynamique pour choisir les données que vous souhaitez afficher.

Filtrage des données dans un tableau croisé dynamique

Si vous devez vous concentrer sur un sous-ensemble de données de votre tableau croisé dynamique, vous pouvez filtrer les données du tableau croisé dynamique en fonction d'un sous-ensemble des valeurs d'un ou plusieurs champs. Par exemple, dans l'exemple ci-dessus, vous pouvez filtrer les données en fonction du champ Plage afin de ne pouvoir afficher les données que pour la ou les régions sélectionnées.

Il existe plusieurs façons de filtrer les données dans un tableau croisé dynamique -

Filtrage à l'aide de filtres de rapport.
Filtrage à l'aide de segments.
Filtrer les données manuellement.
Filtrage à l'aide de filtres d'étiquettes.
Filtrage à l'aide de filtres de valeurs.
Filtrage à l'aide de filtres de date.
Filtrage à l'aide du filtre Top 10.
Filtrage à l'aide de la chronologie.

Vous apprendrez à connaître l'utilisation des filtres de rapport dans cette section et des segments dans la section suivante. Pour d'autres options de filtrage, reportez-vous au didacticiel sur les tableaux croisés dynamiques Excel.

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 le champ Région vers la zone FILTRES.
Faites glisser le champ Vendeur vers la zone ROWS.
Faites glisser le champ Mois vers la zone COLONNES.
Faites glisser le champ Order Amount vers la zone ∑ VALUES.

Le filtre avec l'étiquette en tant que région apparaît au-dessus du tableau croisé dynamique (dans le 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).

Comme vous pouvez le constater,

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 le filtre de région.

La liste déroulante avec les valeurs du champ Région apparaît.

Cochez la case Sélectionner plusieurs éléments. Des cases à cocher apparaîtront pour toutes les valeurs. Par défaut, toutes les cases sont cochées.
Décochez la case (Tout). Toutes les cases seront décochées.
Cochez les cases - Sud et Ouest.

Cliquez sur le bouton OK. Les données relatives aux régions du Sud et de l'Ouest uniquement seront résumées.

Comme vous pouvez le constater, dans la cellule à côté du filtre de région - (Plusieurs éléments) s'affiche, indiquant que vous avez sélectionné plus d'une valeur. Mais combien de valeurs et / ou quelles valeurs ne sont pas connues du rapport qui est affiché. Dans un tel cas, l'utilisation de Slicers est une meilleure option pour le filtrage.

Utilisation de segments dans un tableau croisé dynamique

Le filtrage à l'aide de slicers présente de nombreux avantages -

Vous pouvez avoir plusieurs filtres en sélectionnant les champs des segments.
Vous pouvez visualiser les champs sur lesquels le filtre est appliqué (un segment par champ).
Un Slicer aura des boutons indiquant les valeurs du champ qu'il représente. Vous pouvez cliquer sur les boutons du Slicer pour sélectionner / désélectionner les valeurs dans le champ.
Vous pouvez visualiser les valeurs d'un champ utilisées dans le filtre (les boutons sélectionnés sont mis en surbrillance dans le segment).
Vous pouvez utiliser un segment commun pour plusieurs tableaux croisés dynamiques et / ou graphiques croisés dynamiques.
Vous pouvez masquer / afficher un slicer.

Pour comprendre l'utilisation des segments, considérez le tableau croisé dynamique suivant.

Supposons que vous souhaitiez filtrer ce tableau croisé dynamique en fonction des champs - Région et Mois.

Cliquez sur l'onglet 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 s'affiche. Il contient tous les champs de vos données.

Cochez les cases Région et Mois.

Cliquez sur le bouton 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.

Comme vous pouvez le constater, chaque segment a toutes les valeurs du champ qu'il représente et les valeurs sont affichées sous forme de boutons. Par défaut, toutes les valeurs d'un champ sont sélectionnées et donc tous les boutons sont mis en surbrillance.

Supposons que vous souhaitiez afficher le tableau croisé dynamique uniquement pour les régions du Sud et de l'Ouest et pour les mois de février et mars.

Cliquez sur Sud dans le segment de région. Seul le sud sera mis en évidence dans le Slicer - Region.
Maintenez la touche Ctrl enfoncée et cliquez sur Ouest dans le segment de région.
Cliquez sur Février dans le segment de mois.
Maintenez la touche Ctrl enfoncée et cliquez sur Mars dans le segment de mois. Les valeurs sélectionnées dans les segments sont mises en évidence. Le tableau croisé dynamique sera résumé pour les valeurs sélectionnées.

Pour ajouter / supprimer des valeurs d'un champ du filtre, maintenez la touche Ctrl enfoncée et cliquez sur ces boutons dans le segment respectif.