Tableaux croisés dynamiques Excel - Guide rapide

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 et analyser des centaines de milliers de points de données avec un tableau compact qui peut être modifié dynamiquement pour vous permettre de trouver les différentes perspectives des données. C'est un outil simple à utiliser, mais puissant.

Les principales caractéristiques d'un tableau croisé dynamique sont les suivantes:

• La création d'un tableau croisé dynamique est extrêmement simple et rapide

• Permettre le barattage des données instantanément par simple glissement des champs, tri et filtrage et différents calculs sur les données.

• Arriver à la représentation appropriée pour vos données au fur et à mesure que vous en obtenez

• Possibilité de créer des rapports à la volée.

• Produire plusieurs rapports à partir du même tableau croisé dynamique en quelques secondes.

• Fournir des rapports interactifs pour se synchroniser avec le public.

Dans ce didacticiel, vous comprendrez ces fonctionnalités de tableau croisé dynamique en détail avec des exemples. Au moment où vous aurez terminé ce didacticiel, vous aurez suffisamment de connaissances sur les fonctionnalités du tableau croisé dynamique qui peuvent vous aider à explorer, analyser et rapporter des données en fonction des exigences.

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. Vous pouvez commencer avec un tableau croisé dynamique vide pour remplir les détails, si vous êtes conscient de ce que vous recherchez. Vous pouvez également utiliser les tableaux croisés dynamiques recommandés par Excel qui peuvent vous donner une idée des dispositions de tableau croisé dynamique les mieux adaptées pour résumer vos données.

Vous apprendrez à créer un tableau croisé dynamique à partir d'une plage de données ou d'un tableau Excel dans le chapitre - Création d'un tableau croisé dynamique à partir d'un tableau ou d'une plage.

Excel vous offre un moyen plus puissant de créer un tableau croisé dynamique à partir de plusieurs tables, différentes sources de données et sources de données externes. Il est nommé PowerPivot qui fonctionne sur sa base de données appelée modèle de données. Vous apprendrez ces outils électriques Excel dans d'autres didacticiels de cette bibliothèque de didacticiels.

Vous devez d'abord connaître le tableau croisé dynamique normal comme expliqué dans ce didacticiel, avant de vous aventurer dans les outils électriques.

Disposition du tableau croisé dynamique - Champs et zones

La disposition du tableau croisé dynamique dépend simplement des champs que vous avez sélectionnés pour le rapport et de la manière dont vous les avez organisés en zones. La sélection et la disposition peuvent être effectuées en faisant simplement glisser les champs. Lorsque vous faites glisser les champs, la disposition de tableau croisé dynamique continue de changer et cela se produit en quelques secondes.

Vous en apprendrez plus sur les champs et les zones de tableau croisé dynamique dans les chapitres - Champs de tableau croisé dynamique et zones de tableau croisé dynamique.

Exploration des données avec le tableau croisé dynamique

L'objectif principal de l'utilisation d'un tableau croisé dynamique est normalement d'explorer les données pour extraire les informations importantes et requises. Vous avez plusieurs options pour ce faire, notamment le tri, le filtrage, l'imbrication, la réduction et l'extension, le regroupement et le dégroupage, etc.

Vous aurez un aperçu de ces options dans le chapitre - Exploration des données avec le tableau croisé dynamique.

Résumer les valeurs

Une fois que vous avez rassemblé les données dont vous avez besoin par les différentes techniques d'exploration, l'étape suivante que vous souhaitez effectuer est de résumer les données. Excel vous fournit une variété de types de calcul que vous pouvez appliquer en fonction de la pertinence et des exigences. Vous pouvez également basculer entre différents types de calcul et afficher les résultats en quelques secondes.

Vous apprendrez comment appliquer les types de calcul sur un tableau croisé dynamique dans le chapitre - Synthèse des valeurs par différents types de calcul.

Mettre à jour un tableau croisé dynamique

Une fois que vous avez exploré les données et les avez résumées, vous n'avez pas besoin de répéter l'exercice si et quand les données sources sont mises à jour. Vous pouvez actualiser le tableau croisé dynamique afin qu'il reflète les modifications dans les données source.

Vous découvrirez les différentes manières d'actualiser les données dans le chapitre - Mise à jour d'un tableau croisé dynamique.

Rapports de tableau croisé dynamique

Après avoir exploré et résumé les données avec un tableau croisé dynamique, vous les présenterez sous forme de rapport. Les rapports de tableau croisé dynamique sont de nature interactive, avec la particularité que même une personne non familiarisée avec Excel peut les utiliser de manière intuitive. En raison de leur nature dynamique inhérente, ils vous permettront de changer rapidement la perspective du rapport pour afficher le niveau de détail requis ou de vous concentrer sur les éléments spécifiques pour lesquels le public exprime son intérêt.

En outre, vous pouvez structurer un rapport de tableau croisé dynamique pour une présentation autonome ou comme partie intégrante d'un rapport général, selon le cas. Vous apprendrez les différents rapports avec les tableaux croisés dynamiques dans le chapitre - Rapports de 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.

Si vous êtes sûr des champs à inclure dans le tableau croisé dynamique et de la mise en page souhaitée, vous pouvez commencer avec un tableau croisé dynamique vide et créer le tableau croisé dynamique.

Si vous n'êtes pas sûr de la disposition de tableau croisé dynamique qui convient le mieux à vos données, vous pouvez utiliser la commande Tableaux croisés dynamiques recommandés d'Excel pour afficher les tableaux croisés dynamiques personnalisés en fonction de vos données et choisir celui que vous aimez.

Création d'un tableau croisé dynamique à partir d'une plage de données

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. leCreate PivotTable la boîte de dialogue apparaît.

Dans la boîte de dialogue Créer un tableau croisé dynamique, sous Choose the data that you want to analyze, vous pouvez sélectionner une table ou une plage dans le classeur actuel ou utiliser une source de données externe.

Lorsque vous créez un tableau croisé dynamique à partir d'une plage de données, sélectionnez ce qui suit dans la boîte de dialogue:

• Sélectionner Select a table or range.

• Dans la zone Table / Plage, saisissez le nom de la plage - SalesData_Range.

• Sélectionnez Nouvelle feuille de calcul sous Choisissez l'emplacement où vous souhaitez placer le rapport de tableau croisé dynamique et cliquez sur OK.

Vous pouvez choisir d'analyser plusieurs tables en ajoutant cette plage de données au modèle de données. Vous pouvez apprendre à analyser plusieurs tables, à utiliser le modèle de données et à utiliser une source de données externe pour créer un tableau croisé dynamique dans le didacticiel Excel PowerPivot.

Une nouvelle feuille de calcul est 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, le PivotTable FieldsLa liste 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.

Ajout de champs au tableau croisé dynamique

Vous comprendrez en détail les champs et les zones de tableau croisé dynamique dans les chapitres suivants de ce didacticiel. Pour l'instant, observez les étapes pour ajouter des champs au tableau croisé dynamique.

Supposons que vous souhaitiez récapituler le montant de la commande par vendeur pour les mois de janvier, février et mars. Vous pouvez le faire en quelques étapes simples comme suit -

• 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 également glisser vers la zone ROWS.

• Cliquez sur Montant de la commande et faites-le glisser vers la zone ∑ VALEURS.

Votre premier tableau croisé dynamique est prêt comme indiqué ci-dessous

Observez que deux colonnes apparaissent dans le tableau croisé dynamique, l'une contenant les étiquettes de ligne que vous avez sélectionnées, c'est-à-dire le vendeur et le mois et une seconde contenant la somme du montant de la commande. En plus de la somme du montant de la commande par mois pour chaque vendeur, vous obtiendrez également des sous-totaux représentant les ventes totales de cette personne. Si vous faites défiler la feuille de calcul, vous trouverez la dernière ligne en tant que Grand Total représentant les ventes totales.

Vous en apprendrez plus sur la production de tableaux croisés dynamiques selon les besoins au fil de votre progression dans ce didacticiel.

Création d'un tableau croisé dynamique à partir d'un tableau

Considérez le tableau Excel suivant qui contient les mêmes données de ventes que dans la section précédente -

Un tableau Excel aura intrinsèquement un nom et les colonnes auront des en-têtes, ce qui est une condition requise pour créer un tableau croisé dynamique. Supposons que le nom de la table soit SalesData_Table.

Pour créer un tableau croisé dynamique à partir de ce tableau Excel, procédez comme suit:

• Cliquez sur la table - SalesData_Table.

• Cliquez sur l'onglet INSÉRER sur le ruban.

• Cliquez sur Tableau croisé dynamique dans le groupe Tables. leCreate PivotTable la boîte de dialogue apparaît.

• Cliquez sur Sélectionner une table ou une plage.

• Dans la zone Table / Plage, tapez le nom de la table - SalesData_Table.

• Sélectionnez Nouvelle feuille de calcul sous Choose where you want the PivotTable report to be placed. Cliquez sur OK.

Une nouvelle feuille de calcul est insérée dans votre classeur. La nouvelle feuille de calcul contient un tableau croisé dynamique vide. Nommez la feuille de calcul - Table-PivotTable. La feuille de calcul - Table-PivotTable ressemble à celle que vous avez obtenue dans le cas de la plage de données dans la section précédente.

Vous pouvez ajouter des champs au tableau croisé dynamique comme vous l'avez vu dans la section - Ajout de champs au tableau croisé dynamique, plus haut dans ce chapitre.

Création d'un tableau croisé dynamique avec des tableaux croisés dynamiques recommandés

Si vous n'êtes pas familiarisé avec les tableaux croisés dynamiques Excel ou si vous ne savez pas quels champs donneraient un rapport significatif, vous pouvez utiliser la commande Tableaux croisés dynamiques recommandés dans Excel. Les tableaux croisés dynamiques recommandés vous donnent tous les rapports possibles avec vos données ainsi que la mise en page associée. En d'autres termes, les options affichées seront les tableaux croisés dynamiques personnalisés en fonction de vos données.

Pour créer un tableau croisé dynamique à partir du tableau Excel SalesData-Table à l'aide des tableaux croisés dynamiques recommandés, procédez comme suit -

• Cliquez sur la table SalesData-Table.

• 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 Tableaux croisés dynamiques recommandés, les tableaux croisés dynamiques personnalisés possibles qui correspondent à vos données seront affichés.

• Cliquez sur chacune des options du 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, puis cliquez sur OK.

Vous obtiendrez l'aperçu sur le côté droit.

Le tableau croisé dynamique sélectionné apparaît sur une nouvelle feuille de calcul dans votre classeur.

Vous pouvez voir que les champs de tableau croisé dynamique - Vendeur, Région, Montant de la commande et Mois ont été sélectionnés. Parmi ceux-ci, la région et le vendeur sont dans la zone ROWS, le mois est dans la zone COLUMNS et la somme du montant de la commande est dans la zone ∑ VALUES.

Le tableau croisé dynamique a résumé les données par région, par vendeur et par mois. Les sous-totaux sont affichés pour chaque région, chaque vendeur et chaque mois.

Les champs de tableau croisé dynamique sont un volet des tâches associé à un tableau croisé dynamique. Le volet des tâches Champs de tableau croisé dynamique comprend des champs et des zones. Par défaut, le volet des tâches apparaît sur le côté droit de la fenêtre avec les champs affichés au-dessus des zones.

Les champs représentent les colonnes de votre plage de données ou de votre tableau Excel et comportent des cases à cocher. Les champs sélectionnés sont affichés dans le rapport. Les zones représentent la présentation du rapport et les calculs inclus dans le rapport.

Au bas du volet des tâches, vous trouverez une option - Différer la mise à jour de la mise en page avec un bouton MISE À JOUR à côté.

• Par défaut, ce n'est pas sélectionné et les modifications que vous apportez dans la sélection des champs ou dans les options de mise en page sont reflétées instantanément dans le tableau croisé dynamique.

• Si vous sélectionnez cette option, les modifications apportées à vos sélections ne sont pas mises à jour tant que vous n'avez pas cliqué sur le bouton UPDATE bouton.

Dans ce chapitre, vous comprendrez les détails sur les champs. Dans le chapitre suivant, vous comprendrez les détails sur les zones.

Volet des tâches Champs de tableau croisé dynamique

Vous pouvez trouver le volet des tâches des champs de tableau croisé dynamique dans la feuille de calcul où vous avez un tableau croisé dynamique. Pour afficher le volet des tâches des champs de tableau croisé dynamique, cliquez sur le tableau croisé dynamique. Dans le cas où le volet des tâches des champs de tableau croisé dynamique ne s'affiche pas, vérifiez le ruban pour ce qui suit -

• Cliquez sur l'onglet ANALYSER sous OUTILS PIVOTABLES sur le ruban.
• Vérifiez si la liste des champs est sélectionnée (c'est-à-dire en surbrillance) dans le groupe Afficher.
• Si la liste des champs n'est pas sélectionnée, cliquez dessus.

Le volet des tâches Champs de tableau croisé dynamique sera affiché sur le côté droit de la fenêtre, avec le titre - Champs de tableau croisé dynamique.

Déplacement du volet des tâches des champs de tableau croisé dynamique

À droite du titre Champs de tableau croisé dynamique du volet des tâches de tableau croisé dynamique, vous trouverez le bouton . Cela représente les options du volet des tâches. Cliquez sur le bouton . Les options du volet des tâches - Déplacer, Taille et Fermer apparaissent dans la liste déroulante.

Vous pouvez déplacer le volet des tâches des tableaux croisés dynamiques où vous voulez dans la fenêtre comme suit -

• Cliquez sur Déplacer dans la liste déroulante. Le bouton apparaît dans le volet des tâches.

• Cliquez sur l' icône et faites glisser le volet vers une position où vous souhaitez le placer. Vous pouvez placer le volet des tâches à côté du tableau croisé dynamique comme indiqué ci-dessous.

Vous pouvez placer le volet des tâches sur le côté gauche de la fenêtre comme indiqué ci-dessous.

Redimensionner le volet des tâches des champs de tableau croisé dynamique

Vous pouvez redimensionner le volet des tâches des tableaux croisés dynamiques - c'est-à-dire augmenter / diminuer la longueur et / ou la largeur du volet des tâches comme suit -

• Cliquez sur Options du volet des tâches - qui se trouve sur le côté droit du titre - Champs de tableau croisé dynamique.

• Cliquez sur Taille dans la liste déroulante.

• Utilisez le symbole ⇔ pour augmenter / réduire la largeur du volet des tâches.

• Utilisez le symbole ⇕ pour augmenter / réduire la largeur du volet des tâches.

Dans la zone ∑ VALEURS, pour rendre complètement visible la somme du montant de la commande, vous pouvez redimensionner le volet des tâches comme indiqué ci-dessous.

Champs de tableau croisé dynamique

La liste Champs de tableau croisé dynamique comprend toutes les tables associées à votre classeur et les champs correspondants. C'est en sélectionnant les champs dans la liste des champs de tableau croisé dynamique que vous créerez le tableau croisé dynamique.

Les tables et les champs correspondants avec des cases à cocher reflètent vos données de tableau croisé dynamique. Comme vous pouvez cocher / décocher les champs de manière aléatoire, vous pouvez modifier rapidement le tableau croisé dynamique, en mettant en évidence les données résumées que vous souhaitez signaler ou présenter.

Comme vous pouvez le constater, s'il n'y a qu'une seule table, le nom de la table ne sera pas affiché dans la liste Champs de tableau croisé dynamique. Seuls les champs seront affichés avec des cases à cocher.

Au-dessus de la liste des champs, vous trouverez l'action Choisir les champs à ajouter au rapport. À droite, vous trouverez le bouton - qui représente les outils.

• Cliquez sur le bouton Outils.

Dans la liste déroulante, vous trouverez les éléments suivants -

• Cinq options de mise en page différentes pour les champs et les zones.

• Deux options pour l'ordre de tri des champs dans la liste Champs -

  • Trier de A à Z.

  • Trier dans l'ordre des sources de données.

Comme vous pouvez l'observer dans la liste Champs ci-dessus, l'ordre de tri est par défaut - c'est-à-dire dans l'ordre des sources de données. Cela signifie qu'il s'agit de l'ordre dans lequel les colonnes de votre table de données apparaissent.

Normalement, vous pouvez conserver l'ordre par défaut. Cependant, parfois, vous pouvez rencontrer de nombreux champs dans une table et ne pas les connaître. Dans un tel cas, vous pouvez trier les champs par ordre alphabétique en cliquant sur - Trier de A à Z dans la liste déroulante des Outils. Ensuite, la liste des champs de tableau croisé dynamique se présente comme suit -

Les zones de tableau croisé dynamique font partie du volet des tâches des champs de tableau croisé dynamique. En organisant les champs sélectionnés dans les zones, vous pouvez arriver à différentes dispositions de tableau croisé dynamique. Comme vous pouvez simplement faire glisser les champs à travers les zones, vous pouvez rapidement basculer entre les différentes mises en page, résumant les données, comme vous le souhaitez.

Vous avez déjà découvert le volet des tâches des champs de tableau croisé dynamique dans le chapitre précédent sur les champs de tableau croisé dynamique de ce didacticiel. Dans ce chapitre, vous découvrirez les zones de tableau croisé dynamique.

Quatre zones de tableau croisé dynamique sont disponibles -

• ROWS.
• COLUMNS.
• FILTERS.
• ∑ VALEURS (lire comme valeurs de synthèse).

Le message - Drag fields between areas below apparaît au-dessus des zones.

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

• Quels champs afficher sous forme de lignes (zone ROWS).
• Quels champs afficher sous forme de colonnes (zone COLONNES).
• Comment résumer vos données (∑ zone VALEURS).
• Filtre pour n'importe lequel des champs (zone FILTRES).

Vous pouvez simplement faire glisser les champs sur ces zones et observer comment la disposition du tableau croisé dynamique change.

LIGNES

Si vous sélectionnez les champs dans les listes de champs de tableau croisé dynamique en cochant simplement les cases, tous les champs non numériques seront automatiquement ajoutés à la zone ROWS, dans l'ordre que vous sélectionnez.

Vous pouvez éventuellement faire glisser un champ vers la zone ROWS. Les champs placés dans la zone ROWS apparaissent sous forme de lignes dans le tableau croisé dynamique, les étiquettes de ligne correspondant aux valeurs des champs sélectionnés.

Par exemple, considérez la table de données Sales.

• Faites glisser le champ Vendeur vers la zone ROWS.
• Faites glisser le champ Mois vers la zone ROWS.

Votre tableau croisé dynamique apparaît avec une colonne contenant les étiquettes de ligne - Vendeur et Mois et une dernière ligne en tant que total général, comme indiqué ci-dessous.

COLONNES

Vous pouvez faire glisser des champs vers la zone COLONNES.

Les champs placés dans la zone COLONNES apparaissent sous forme de colonnes dans le tableau croisé dynamique, les étiquettes de colonne étant les valeurs des champs sélectionnés.

Faites glisser le champ Région vers la zone COLONNES. Votre tableau croisé dynamique apparaît avec la première colonne contenant les étiquettes de ligne - Vendeur et le mois, les quatre colonnes suivantes contenant les étiquettes de colonne - Région et une dernière colonne Grand total comme indiqué ci-dessous.

• Faites glisser le champ Mois de ROWS vers COLUMNS.

• Faites glisser le champ Region de COLUMNS vers ROWS. Votre disposition de tableau croisé dynamique change comme indiqué ci-dessous.

Vous pouvez voir qu'il n'y a plus que cinq colonnes maintenant - la première colonne avec les étiquettes de ligne, trois colonnes avec les étiquettes de colonne et une dernière colonne avec le total général.

Le nombre de lignes et de colonnes est basé sur le nombre de valeurs que vous avez dans ces champs.

∑ VALEURS

L'utilisation principale d'un tableau croisé dynamique est de résumer les valeurs. Par conséquent, en plaçant les champs par lesquels vous souhaitez résumer les données dans∑ VALUES zone, vous arrivez au tableau récapitulatif.

• Faites glisser le champ Montant de la commande vers ∑ VALUES.

• Faites glisser le champ Région au-dessus du champ Vendeur dans la zone ROWS. Cette étape consiste à modifier l'ordre d'imbrication. Vous apprendrez l'imbrication dans le chapitre - Imbrication dans le tableau croisé dynamique de ce didacticiel.

Comme vous pouvez le constater, les données sont résumées par région, par vendeur et par mois. Vous avez des sous-totaux pour chaque région, par mois. Vous avez également des totaux généraux par mois dans la région des totaux généraux de la ligne Grand Total dans la colonne Grand Total.

FILTRES

La zone Filtres consiste à placer des filtres dans le tableau croisé dynamique. Supposons que vous souhaitiez afficher les résultats séparément pour les régions sélectionnées uniquement.

Faites glisser le champ Région de la zone ROWS vers la zone FILTERS. La région de filtre sera placée au-dessus du tableau croisé dynamique. Si vous n'avez pas de lignes vides au-dessus du tableau croisé dynamique, le tableau croisé dynamique est poussé vers le bas en insérant des lignes au-dessus du tableau croisé dynamique pour le filtre.

Comme vous pouvez le constater, (TOUS) apparaît dans le filtre par défaut et le tableau croisé dynamique affiche des données pour toutes les valeurs de la région.

• Cliquez sur la flèche à droite du filtre.
• Cochez la case - Sélectionnez plusieurs éléments.

Des cases à cocher apparaîtront pour toutes les options de la liste déroulante. Par défaut, toutes les cases sont cochées.

• Cochez les cases - Nord et Sud.
• Videz les autres cases. Cliquez sur OK.

Le tableau croisé dynamique est modifié pour refléter les données filtrées.

Vous pouvez observer que le filtre affiche (Plusieurs éléments). Par conséquent, lorsque quelqu'un regarde le tableau croisé dynamique, il n'est pas immédiatement évident de quelles valeurs sont filtrées.

Excel vous fournit un autre outil appelé Slicers pour gérer le filtrage plus efficacement. Vous comprendrez en détail le filtrage des données dans un tableau croisé dynamique dans un chapitre ultérieur de ce didacticiel.

Le tableau croisé dynamique Excel vous permet d'explorer et d'extraire des données importantes d'un tableau Excel ou d'une plage de données. Il existe plusieurs façons de procéder et vous pouvez choisir celles qui conviennent le mieux à vos données. En outre, pendant que vous explorez les données, vous pouvez afficher les différentes combinaisons instantanément lorsque vous modifiez vos choix pour sélectionner les valeurs de données.

Vous pouvez effectuer les opérations suivantes avec un tableau croisé dynamique -

• Triez les données.
• Filtrez les données.
• Imbriquez les champs du tableau croisé dynamique.
• Développez et réduisez les champs.
• Regroupez et dissociez les valeurs de champ.

Tri et filtrage des données

Vous pouvez trier les données d'un tableau croisé dynamique par ordre croissant ou décroissant des valeurs de champ. Vous pouvez également trier par sous-totaux du plus grand au plus petit ou du plus petit au plus grand. Vous pouvez également définir des options de tri. Vous les apprendrez en détail dans le chapitre - Tri des données dans un tableau croisé dynamique de ce didacticiel.

Vous pouvez filtrer les données dans un tableau croisé dynamique pour vous concentrer sur certaines données spécifiques. Vous disposez de plusieurs options de filtrage dans le tableau croisé dynamique que vous apprendrez dans le chapitre - Filtrage des données dans un tableau croisé dynamique de ce didacticiel. Vous pouvez utiliser des segments pour le filtrage, ce que vous apprendrez dans le chapitre - Filtrage à l'aide de segments de ce tutoriel.

Champs d'imbrication, d'extension et de réduction

Vous pouvez imbriquer des champs dans un tableau croisé dynamique pour afficher une hiérarchie, si cela est pertinent pour vos données. Vous apprendrez cela dans le chapitre - Imbrication dans un tableau croisé dynamique de ce didacticiel.

Lorsque vous avez des champs imbriqués dans votre tableau croisé dynamique, vous pouvez développer et réduire les valeurs de ces champs. Vous les apprendrez dans le chapitre - Exploration des données avec les outils de tableau croisé dynamique de ce didacticiel.

Regroupement et dissociation des valeurs de champ

Vous pouvez regrouper et dissocier des valeurs spécifiques d'un champ dans un tableau croisé dynamique. Vous apprendrez cela dans le chapitre - Exploration des données avec les outils de tableau croisé dynamique de ce didacticiel.

Vous pouvez trier les données dans un tableau croisé dynamique afin qu'il vous soit facile de trouver les éléments que vous souhaitez analyser. Vous pouvez trier les données des valeurs les plus basses aux valeurs les plus élevées ou des valeurs les plus élevées aux valeurs les plus faibles ou dans tout autre ordre personnalisé de votre choix.

Considérez le tableau croisé dynamique suivant dans lequel vous avez les données de ventes résumées par région, par vendeur et par mois.

Tri sur les champs

Vous pouvez trier les données dans le tableau croisé dynamique ci-dessus sur les champs qui sont dans des lignes ou des colonnes - Région, vendeur et mois.

Pour trier le tableau croisé dynamique avec le champ Vendeur, procédez comme suit -

• Cliquez sur la flèche dans les étiquettes de ligne.

• Sélectionnez Vendeur dans la zone Sélectionner un champ dans la liste déroulante.

Les options de tri suivantes s'affichent -

• Trier de A à Z.
• Trier de Z à A.
• Plus d'options de tri.

De plus, le champ Vendeur est trié par ordre croissant, par défaut. Cliquez surSort Z to A. Le champ Vendeur sera trié par ordre décroissant.

De la même manière, vous pouvez trier le champ en colonne - Mois, en cliquant sur la flèche dans les libellés des colonnes.

Tri sur les sous-totaux

Supposons que vous souhaitiez trier le tableau croisé dynamique en fonction du montant total de la commande - du plus élevé au plus bas dans chaque région. Autrement dit, vous souhaitez trier le tableau croisé dynamique sur les sous-totaux.

Vous pouvez voir qu'il n'y a pas de flèche pour les sous-totaux. Vous pouvez toujours trier le tableau croisé dynamique sur les sous-totaux comme suit -

• Cliquez avec le bouton droit sur le sous-total de l'un des vendeurs dans la colonne Total général.

• Sélectionner Sort dans la liste déroulante.

• Une autre liste déroulante apparaît avec les options de tri - Trier du plus petit au plus grand, Trier du plus grand au plus petit et plus d'options de tri. Sélectionnez Trier du plus grand au plus petit.

Les sous-totaux de la colonne Total général sont triés des valeurs les plus élevées aux valeurs les plus faibles, dans chaque région.

De même, si vous souhaitez trier le tableau croisé dynamique sur les sous-totaux par région, procédez comme suit:

• Cliquez avec le bouton droit sur le sous-total de l'une des régions dans la colonne Grand Total.

• Cliquez sur Trier dans la liste déroulante.

• Cliquez sur Trier du plus grand au plus petit dans la deuxième liste déroulante. Le tableau croisé dynamique sera trié sur les sous-totaux par région.

Comme vous pouvez le constater, le sud a le montant de commande le plus élevé tandis que le nord a le plus bas.

Vous pouvez également trier le tableau croisé dynamique en fonction du montant total par mois comme suit -

• Cliquez avec le bouton droit sur l'un des sous-totaux de la ligne Grand total.
• Sélectionnez Trier dans la liste déroulante.
• Sélectionnez Trier du plus grand au plus petit dans la deuxième liste déroulante.

Le tableau croisé dynamique sera trié selon le montant total par mois.

Vous pouvez observer que février a le montant de la commande le plus élevé tandis que mars a le plus bas.

Plus d'options de tri

Supposons que vous souhaitiez trier le tableau croisé dynamique sur le montant total par région au mois de janvier.

• Cliquez sur la flèche dans les étiquettes de ligne.

• Sélectionnez Plus d'options de tri dans la liste déroulante. leSort (Region) la boîte de dialogue apparaît.

Comme vous pouvez le constater, sous Résumé, l'ordre de tri actuel est donné en tant que région de tri par ordre croissant. Croissant (de A à Z) par est sélectionné sous Options de tri. Dans la case ci-dessous,Region est affiché.

• Cliquez sur la case contenant Région.
• Cliquez sur Somme du montant de la commande.

Clique le More Optionsbouton. leMore Sort Options (Region) la boîte de dialogue apparaît.

Comme vous pouvez le constater, sous Trier par, le total général est sélectionné. Sous Résumé, l'ordre de tri actuel est donné commeSort Region by Sum of Order Amount Dans l'ordre croissant.

• Cliquez sur Values in selected column: sous Trier par.

• Dans la zone ci-dessous, tapez B5.

Comme vous pouvez le constater, sous Résumé, l'ordre de tri actuel est donné comme suit -

• Trier la région par Sum of Order Amountpar ordre croissant en utilisant les valeurs de cette colonne: janvier. Cliquez sur OK.

• La boîte de dialogue Trier (région) s'affiche. Sélectionnez Décroissant (Z à A) par: sous Options de tri.

Sous Résumé, l'ordre de tri actuel est donné comme suit -

Trier la région par somme du montant de la commande dans l'ordre décroissant, en utilisant les valeurs de cette colonne: janvier. Cliquez sur OK. Le tableau croisé dynamique sera trié par région, en utilisant les valeurs de janvier.

Comme vous pouvez le constater, au mois de janvier, l'Ouest a le montant de commande le plus élevé tandis que le Nord a le plus bas.

Trier les données manuellement

Dans le tableau croisé dynamique, les données sont triées automatiquement par l'option de tri que vous avez choisie. Cela s'appelle AutoSort.

Placez le curseur sur la flèche dans les étiquettes de ligne ou les étiquettes de colonne.

Le tri automatique apparaît, affichant l'ordre de tri actuel pour chacun des champs du tableau croisé dynamique. Maintenant, supposons que vous souhaitiez trier le champ Région dans l'ordre - Est, Ouest, Nord et Sud. Vous pouvez le faire manuellement, comme suit -

• Cliquez sur la flèche dans les étiquettes de ligne.

• Sélectionnez Région dans la zone Sélectionner un champ dans la liste déroulante.

• Cliquez sur More Sort Options. La boîte de dialogue Trier (région) s'affiche.

• Sélectionnez Manuel (vous pouvez faire glisser les éléments pour les réorganiser).

• Cliquez sur OK.

Sous Récapitulatif, l'ordre de tri actuel est donné sous forme d'éléments Glisser du champ Région pour les afficher dans n'importe quel ordre.

Cliquez sur l'Est et faites-le glisser vers le haut. Pendant que vous faites glisser vers l'est, une barre verte horizontale apparaît sur l'ensemble des mouvements de ligne.

Répétez le glissement avec d'autres éléments du champ Région jusqu'à ce que vous obteniez la disposition requise.

Vous pouvez observer ce qui suit -

• Les éléments du champ imbriqué - Vendeur se déplacent également avec l'élément de champ Région correspondant. En outre, les valeurs des autres colonnes ont également été déplacées en conséquence.

• Si vous placez le curseur sur la flèche dans Étiquettes de ligne ou Étiquettes de colonne, le tri automatique apparaît, affichant l'ordre de tri actuel des champs Vendeur et Mois uniquement. Comme vous avez trié le champ Région manuellement, il n'apparaîtra pas dans le tri automatique.

Note- Vous ne pouvez pas utiliser ce glissement manuel des éléments du champ qui se trouve dans la zone ∑ VALEURS de la liste Champs de tableau croisé dynamique. Par conséquent, vous ne pouvez pas faire glisser la somme des valeurs de montant de la commande dans ce tableau croisé dynamique.

Définition des options de tri

Dans la section précédente, vous avez appris à définir l'option de tri d'un champ sur manuel. Vous avez d'autres options de tri que vous pouvez définir comme suit -

• Cliquez sur la flèche dans les étiquettes de ligne.

• Sélectionnez Région dans la zone Sélectionner un champ.

• Cliquez sur Plus d'options de tri. La boîte de dialogue Trier (région) s'affiche.

• Cliquez sur le bouton Plus d'options.

La boîte de dialogue Plus d'options de tri (région) s'affiche. Vous pouvez définir plus d'options de tri dans cette boîte de dialogue.

Sous Tri automatique, vous pouvez cocher ou décocher la case - Trier automatiquement chaque fois que le rapport est mis à jour, pour autoriser ou arrêter le tri automatique chaque fois que les données du tableau croisé dynamique sont mises à jour.

• Décochez la case - Sort automatically chaque fois que le rapport est mis à jour.

Maintenant, l'option d'ordre de tri de la première clé devient disponible. Vous pouvez utiliser cette option pour sélectionner la commande personnalisée que vous souhaitez utiliser.

• Cliquez sur la case sous Ordre de tri de la première clé.

Comme vous pouvez le constater, les listes personnalisées du jour de la semaine et du mois de l'année sont fournies dans la liste déroulante. Vous pouvez utiliser n'importe lequel de ceux-ci, ou vous pouvez utiliser votre propre liste personnalisée telle que Elevé, Moyen, Faible ou la liste des tailles S, M, L, XL qui ne sont pas dans l'ordre alphabétique.

Vous pouvez créer vos listes personnalisées à partir de l'onglet FICHIER sur le ruban. FICHIER → Options. Dans la boîte de dialogue Options Excel, cliquez sur Avancé et accédez à Général. Vous trouverez le bouton Modifier les listes personnalisées à côté de Créer des listes à utiliser dans les séquences de tri et de remplissage.

Notez qu'un ordre de tri de liste personnalisé n'est pas conservé lorsque vous mettez à jour (actualisez) les données dans votre tableau croisé dynamique.

Sous Trier par, vous pouvez cliquer sur Grand Total or Valuesdans les colonnes sélectionnées pour trier par ces valeurs. Cette option n'est pas disponible lorsque vous définissez le tri sur Manuel.

Points à prendre en compte lors du tri des tableaux croisés dynamiques

Lorsque vous triez des données dans un tableau croisé dynamique, n'oubliez pas ce qui suit:

• Les données comportant des espaces de début affecteront les résultats du tri. Supprimez tous les espaces de début avant de trier les données.

• Vous ne pouvez pas trier les entrées de texte sensibles à la casse.

• Vous ne pouvez pas trier les données selon un format spécifique tel que la couleur de cellule ou de police.

• Vous ne pouvez pas trier les données à l'aide d'indicateurs de mise en forme conditionnelle, tels que des jeux d'icônes.

Vous devrez peut-être effectuer une analyse approfondie sur un sous-ensemble de vos données de tableau croisé dynamique. Cela peut être dû au fait que vous avez des données volumineuses et que vous devez vous concentrer sur une plus petite partie des données ou quelle que soit la taille des données, votre concentration est requise sur certaines données spécifiques. Vous pouvez filtrer les données du tableau croisé dynamique en fonction d'un sous-ensemble des valeurs d'un ou plusieurs champs. Il existe plusieurs façons de procéder comme suit -

• Filtrage à l'aide de segments.
• Filtrage à l'aide de filtres de rapport.
• 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 à filtrer les données à l'aide des segments dans le chapitre suivant. Vous comprendrez le filtrage par les autres méthodes mentionnées ci-dessus dans ce chapitre.

Considérez le tableau croisé dynamique suivant dans lequel vous avez les données de vente résumées par région, par vendeur et par mois.

Filtres de rapport

Vous pouvez affecter 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 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.

Vous observerez 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 case à droite de la zone de filtrage.

Une liste déroulante avec les valeurs du champ Région apparaît. Cochez la caseSelect Multiple Items.

Par défaut, toutes les cases sont cochées. Décochez la case (All). Toutes les cases seront décochées.

Cochez ensuite les cases - Sud et Ouest et cliquez sur OK.

Les données relatives aux régions du Sud et de l'Ouest uniquement seront résumées.

Dans la cellule à côté de la région de filtrage - (Plusieurs éléments) s'affiche, indiquant que vous avez sélectionné plusieurs éléments. Cependant, le nombre d'éléments et / ou les éléments ne sont pas connus à partir du rapport affiché. Dans un tel cas, l'utilisation de Slicers est une meilleure option pour le filtrage.

Filtrage manuel

Vous pouvez également filtrer le tableau croisé dynamique en sélectionnant manuellement les valeurs d'un champ. Vous pouvez le faire en cliquant sur la flèche dans la cellule Étiquettes de ligne ou Étiquettes de colonne.

Supposons que vous souhaitiez analyser uniquement les données de février. Vous devez filtrer les valeurs par le champ Mois. Comme vous pouvez le constater, le mois fait partie des étiquettes de colonne.

Cliquez sur la flèche dans la cellule Étiquettes de colonne.

Comme vous pouvez le constater, il y a une boîte de recherche dans la liste déroulante et sous la boîte, vous avez la liste des valeurs du champ sélectionné, c'est-à-dire le mois. Les cases de toutes les valeurs sont cochées, indiquant que toutes les valeurs de ce champ sont sélectionnées.

• Décochez la case (Tout sélectionner) en haut de la liste de valeurs.

• Cochez les cases des valeurs que vous souhaitez afficher dans votre tableau croisé dynamique, dans ce cas février et cliquez sur OK.

Le tableau croisé dynamique affiche uniquement les valeurs liées à la valeur du champ Mois sélectionné - Février. Vous pouvez observer que la flèche de filtrage se transforme en icône pour indiquer qu'un filtre est appliqué. Placez le curseur sur l' icône.

Vous pouvez observer que s'affiche indiquant que le filtre manuel est appliqué sur le champ Mois.

Si vous souhaitez modifier la valeur de sélection du filtre, procédez comme suit:

• Cliquez sur l' icône.

• Cochez / décochez les cases des valeurs.

Si toutes les valeurs du champ ne sont pas visibles dans la liste, faites glisser la poignée dans le coin inférieur droit de la liste déroulante pour l'agrandir. Sinon, si vous connaissez la valeur, saisissez-la dans la zone de recherche.

Supposons que vous souhaitiez appliquer un autre filtre sur le tableau croisé dynamique filtré ci-dessus. Par exemple, vous souhaitez afficher les données de celui de Walters, Chris pour le mois de février. Vous devez affiner votre filtrage en ajoutant un autre filtre pour le champ Vendeur. Comme vous pouvez le constater, le vendeur fait partie des étiquettes de ligne.

• Cliquez sur la flèche dans la cellule Étiquettes de ligne.

La liste des valeurs du champ - Région s'affiche. Cela est dû au fait que la région est au niveau externe du vendeur dans l'ordre d'imbrication. Vous avez également une option supplémentaire - Sélectionner un champ. Cliquez sur la case Sélectionner un champ.

• Cliquez sur Vendeur dans la liste déroulante. La liste des valeurs du champ - Vendeur sera affichée.

• Décochez (Tout sélectionner) et cochez Walters, Chris.

• Cliquez sur OK.

Le tableau croisé dynamique affiche uniquement les valeurs liées à la valeur du champ Mois sélectionné - Février et valeur du champ Vendeur - Walters, Chris.

La flèche de filtrage des étiquettes de ligne se transforme également en icône pour indiquer qu'un filtre est appliqué. Placez le curseur sur l' icône sur les étiquettes de ligne ou les étiquettes de colonne.

Une zone de texte s'affiche pour indiquer que le filtre manuel est appliqué aux champs - Mois et Vendeur.

Vous pouvez ainsi filtrer le tableau croisé dynamique manuellement en fonction de n'importe quel nombre de champs et de n'importe quel nombre de valeurs.

Filtrage par texte

Si vous avez des champs qui contiennent du texte, vous pouvez filtrer le tableau croisé dynamique par texte, à condition que l'étiquette de champ correspondante soit basée sur du texte. Par exemple, considérez les données d'employé suivantes.

Les données contiennent les détails des employés - EmployeeID, Title, BirthDate, MaritalStatus, Gender et HireDate. En outre, les données ont également le niveau de gestionnaire de l'employé (niveaux 0 à 4).

Supposons que vous ayez à faire une analyse sur le nombre d'employés faisant rapport à un employé donné par titre. Vous pouvez créer un tableau croisé dynamique comme indiqué ci-dessous.

Vous voudrez peut-être savoir combien d'employés avec «Manager» dans leur titre ont des employés qui relèvent d'eux. Comme le titre de l'étiquette est basé sur du texte, vous pouvez appliquer le filtre d'étiquette sur le champ Titre comme suit -

• Cliquez sur la flèche dans la cellule Étiquettes de ligne.

• Sélectionnez Titre dans la zone Sélectionner un champ dans la liste déroulante.

• Cliquez sur Filtres d'étiquettes.

• Cliquez sur Contient dans la deuxième liste déroulante.

La boîte de dialogue Filtre d'étiquette (titre) s'affiche. Tapez Manager dans la zone en regard de Contient. Cliquez sur OK.

Le tableau croisé dynamique sera filtré sur les valeurs de titre contenant «Manager».

• Cliquez sur l' icône.

Vous pouvez voir que s'affiche indiquant ce qui suit -

• Le filtre d'étiquette est appliqué sur le champ - Titre, et
• Qu'est-ce que le filtre d'étiquette appliqué?

Filtrage par valeurs

Vous voudrez peut-être connaître les titres des employés dont plus de 25 employés relèvent d'eux. Pour cela, vous pouvez appliquer le filtre de valeur sur le champ Titre comme suit -

• Cliquez sur la flèche dans la cellule Étiquettes de ligne.

• Sélectionner Title dans la zone Sélectionner un champ dans la liste déroulante.

• Cliquez sur Filtres de valeurs.

• Sélectionnez Supérieur ou égal à dans la deuxième liste déroulante.

La boîte de dialogue Filtre de valeur (titre) s'affiche. Tapez 25 dans la case de droite.

Le tableau croisé dynamique sera filtré pour afficher les titres des employés dont plus de 25 employés relèvent d'eux.

Filtrage par dates

Vous souhaiterez peut-être afficher les données de tous les employés qui ont été embauchés au cours de l'exercice 2015-2015. Vous pouvez utiliser les filtres de données pour la même chose comme suit -

• Incluez le champ HireDate dans le tableau croisé dynamique. Désormais, vous n'avez pas besoin de données de responsable et supprimez donc le champ ManagerLevel du tableau croisé dynamique.

Maintenant que vous avez un champ Date dans le tableau croisé dynamique, vous pouvez utiliser des filtres de date.

• Cliquez sur la flèche dans la cellule Étiquettes de ligne.

• Sélectionnez HireDate dans la zone Sélectionner un champ dans la liste déroulante.

• Cliquez sur Filtres de date.

• Seelct Between dans la deuxième liste déroulante.

La boîte de dialogue Filtre de date (HireDate) s'affiche. Tapez 4/1/2014 et 3/31/2015 dans les deux cases Date. Cliquez sur OK.

Le tableau croisé dynamique sera filtré pour afficher uniquement les données avec HireDate entre 1 ^er Avril 2014 et le 31 ^er Mars à 2015.

Vous pouvez regrouper les dates en trimestres comme suit -

• Faites un clic droit sur l'une des dates. leGrouping la boîte de dialogue apparaît.

• Tapez 4/1/2014 dans la case À partir de. Cochez la case.

• Tapez 31/03/2015 dans la case Fin à. Cochez la case.

• Cliquez sur Trimestres dans la zone sous By.

Les dates seront regroupées en trimestres dans le tableau croisé dynamique. Vous pouvez rendre la table compacte en faisant glisser le champ HireDate de la zone ROWS vers la zone COLUMNS.

Vous pourrez connaître le nombre d'employés embauchés au cours de l'exercice, par trimestre.

Filtrage à l'aide du filtre Top 10

Vous pouvez utiliser le filtre 10 premiers pour afficher les quelques valeurs supérieures ou inférieures d'un champ dans le tableau croisé dynamique.

• Cliquez sur la flèche dans la cellule Étiquettes de ligne.

• Cliquez sur Filtres de valeurs.

• Cliquez sur Top 10 dans la deuxième liste déroulante.

La boîte de dialogue Top 10 Filter (Title) s'affiche.

• Dans la première case, cliquez sur Haut (vous pouvez également choisir Bas).

• Dans la deuxième case, entrez un nombre, disons 7.

• Dans la troisième zone, vous avez trois options par lesquelles vous pouvez filtrer.

  • Cliquez sur Articles pour filtrer par nombre d'articles.

  • Cliquez sur Pourcentage pour filtrer par pourcentage.

  • Cliquez sur Somme pour filtrer par somme.

• Comme vous avez le nombre de EmployeeID, cliquez sur Articles.

• Dans la quatrième case, cliquez sur le champ Count of EmployeeID.

• Cliquez sur OK.

Les sept premières valeurs par nombre de EmployeeID seront affichées dans le tableau croisé dynamique.

Comme vous pouvez le constater, le nombre d'embauches le plus élevé au cours de l'exercice est celui de techniciens de production et un nombre prédominant d'entre eux se trouve au premier trimestre.

Filtrage à l'aide de la chronologie

Si votre tableau croisé dynamique a un champ de date, vous pouvez filtrer le tableau croisé dynamique à l'aide de la chronologie.

Créez un tableau croisé dynamique à partir des données d'employé que vous avez utilisées précédemment et ajoutez les données au modèle de données dans la boîte de dialogue Créer un tableau croisé dynamique.

• Faites glisser le champ Titre vers la zone ROWS.

• Faites glisser le champ EmployeeID vers la zone area VALUES et choisissez Count pour le calcul.

• Cliquez sur le tableau croisé dynamique.

• Cliquez sur l'onglet INSÉRER.

• Cliquez sur Chronologie dans le groupe Filtres. La boîte de dialogue Insérer des chronologies apparaît.

• Cochez la case HireDate.
• Cliquez sur OK. La chronologie apparaît dans la feuille de calcul.
• Les outils de chronologie apparaissent sur le ruban.

Comme vous pouvez le constater, toutes les périodes - en mois sont affichées sur la chronologie.

• Cliquez sur la flèche à côté de - MOIS.

• Sélectionnez QUARTIERS dans la liste déroulante. L'affichage de la chronologie passe à Toutes les périodes - par trimestre.

• Cliquez sur 2014 Q1.

• Maintenez la touche Maj enfoncée et faites glisser vers 2014 Q4. La période chronologique est sélectionnée du T1 au T4 2014.

• Le tableau croisé dynamique est filtré sur cette période de chronologie.

Effacer les filtres

Vous devrez peut-être effacer les filtres que vous avez définis de temps en temps pour basculer entre différentes combinaisons et projections de vos données. Vous pouvez le faire de plusieurs manières comme suit -

Effacer tous les filtres dans un tableau croisé dynamique

Vous pouvez effacer tous les filtres définis dans un tableau croisé dynamique en une seule fois comme suit -

• Cliquez sur l'onglet ACCUEIL sur le ruban.
• Cliquez sur Trier et filtrer dans le groupe Édition.
• Sélectionnez Effacer dans la liste déroulante.

Effacer un filtre d'étiquette, de date ou de valeur

Pour effacer une étiquette, une date ou un filtre de valeur, procédez comme suit:

• Cliquez sur l'icône dans les étiquettes de ligne ou les étiquettes de colonne.

• Cliquez sur le <nom du champ> dont vous souhaitez effacer le filtre dans la zone Sélectionner un champ dans la liste déroulante.

• Cliquez sur Clear Filter From <Filed Name> qui apparaît dans la liste déroulante.

• Cliquez sur OK. Le filtre spécifique sera effacé.

L'utilisation d'un ou de plusieurs segments est un moyen rapide et efficace de filtrer vos données. Des segments peuvent être insérés pour chacun des champs que vous souhaitez filtrer. Slicer aura des boutons indiquant les valeurs du champ qu'il représente. Vous pouvez cliquer sur les boutons d'un slicer pour sélectionner / désélectionner les valeurs dans le champ.

Les segments restent visibles avec le tableau croisé dynamique et vous saurez donc toujours quels champs sont utilisés pour le filtrage et quelles valeurs de ces champs sont affichées ou masquées dans le tableau croisé dynamique filtré.

Pour comprendre l'utilisation des segments, prenons l'exemple des données de vente par région, par mois et par vendeur. Supposons que vous disposez du tableau croisé dynamique suivant avec ces données.

Insertion de segments

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

• 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 s'affiche. Il contient tous les champs de votre table de données.

• Cochez les cases 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.

Filtrage avec des segments

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 Sud et Ouest et pour les mois de février et mars.

• Cliquez sur Sud dans le Slicer pour la 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 Slicer pour la région.

• Cliquez sur Février dans le Slicer pour le mois.

• Maintenez la touche Ctrl enfoncée et cliquez sur Mars dans le Slicer pour le mois.

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é.

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

Effacement du filtre dans un segment

Pour effacer le filtre dans un slicer, cliquez sur dans le coin supérieur droit du slicer.

Suppression d'un segment

Supposons que vous souhaitiez supprimer le segment du champ Région.

• Faites un clic droit sur le Slicer - Region.
• Cliquez sur Supprimer «Région» dans la liste déroulante.

Outils de trancheuse

Une fois que vous avez inséré un segment, les outils de segment apparaissent sur l'onglet Ruban avec OPTIONS. Pour afficher les outils de segment, cliquez sur un segment.

Comme vous pouvez le constater, sous l'onglet Outils de segment - OPTION, vous disposez de plusieurs options pour modifier l'apparence du segment, notamment:

• Légende du slicer
• Paramètres du segment
• Signaler les connexions
• Volet de sélection

Légende du slicer

Vous pouvez trouver la zone Slicer Caption dans le groupe Slicer. La légende du segment est l'en-tête qui s'affiche sur le segment. Par défaut,Slicer Caption est le nom du champ qu'il représente.

• Cliquez sur le segment pour la région.
• Cliquez sur l'onglet OPTIONS sur le ruban.

Le groupe Slicer sur le ruban, dans la zone Slicer Caption, Region est affiché comme en-tête du slicer. C'est le nom du champ pour lequel le segment est inséré. Vous pouvez modifier la légende du segment comme suit -

• Cliquez sur la case Slicer Caption dans le groupe Slicer du ruban.

• Supprimer la région. La case est effacée.

• Tapez Location dans la zone et appuyez sur Entrée. La légende du segment change en emplacement et le même est reflété comme en-tête dans le segment.

Note- Vous n'avez modifié que la légende du slicer, c'est-à-dire l'en-tête. Le nom du champ que le segment représente - La région reste telle quelle.

Paramètres du segment

Vous pouvez utiliser les paramètres du segment pour modifier le nom du segment, changer la légende du segment, choisir d'afficher ou non l'en-tête du segment et définir les options de tri et de filtrage des éléments -

• Cliquez sur le slicer - Location.

• Cliquez sur l'onglet OPTIONS sur le ruban. Vous pouvez trouver les paramètres de segment dans le groupe de segment sur le ruban. Vous pouvez également trouver les paramètres du segment dans la liste déroulante lorsque vous cliquez avec le bouton droit sur le segment.

• Cliquez sur les paramètres du segment. La boîte de dialogue Paramètres du segment s'affiche.

Comme vous pouvez le constater, les éléments suivants sont corrigés pour le segment -

• Nom de la source.
• Nom à utiliser dans les formules.

Vous pouvez modifier les éléments suivants pour le segment -

• Name.
• En-tête - Légende.
• Afficher l'en-tête.
• Options de tri et de filtrage des éléments affichés sur le segment.

Signaler les connexions

Vous pouvez connecter différents tableaux croisés dynamiques à un segment, à condition que l'un des éléments suivants soit valable:

• Les tableaux croisés dynamiques sont créés à l'aide des mêmes données.

• Un tableau croisé dynamique a été copié et collé en tant que tableau croisé dynamique supplémentaire.

• Plusieurs tableaux croisés dynamiques sont créés sur des feuilles distinctes avec Afficher les pages de filtre de rapport.

Considérez les tableaux croisés dynamiques suivants créés à partir des mêmes données -

• Nommez le tableau croisé dynamique supérieur en tant que tableau croisé dynamique-haut et celui du bas en tant que tableau croisé dynamique-bas.
• Cliquez sur le tableau croisé dynamique supérieur.
• Insérez un segment pour le champ Région.
• Sélectionnez Est et Nord sur le Slicer.

Observez que le filtrage est appliqué uniquement au tableau croisé dynamique supérieur et non au tableau croisé dynamique inférieur. Vous pouvez utiliser le même segment pour les deux tableaux croisés dynamiques en le connectant au tableau croisé dynamique inférieur également comme suit -

• Cliquez sur le slicer - Région. Les OUTILS DE TRANCHAGE apparaissent sur le ruban.
• Cliquez sur l'onglet OPTIONS sur le ruban.

Vous trouverez les connexions de rapport dans le groupe Slicer sur le ruban. Vous pouvez également trouver les connexions de rapport dans la liste déroulante lorsque vous cliquez avec le bouton droit sur le segment.

Cliquez sur Report Connections dans le groupe Slicer.

le Report Connectionsla boîte de dialogue apparaît. La case PivotTable-Top est cochée et les autres cases ne sont pas cochées. Cochez la case PivotTable-Bottom également et cliquez sur OK.

Le tableau croisé dynamique du bas sera filtré sur les éléments sélectionnés - Est et Nord.

Cela est devenu possible car les deux tableaux croisés dynamiques sont désormais connectés au segment. Si vous apportez des modifications aux sélections dans le segment, le même filtrage apparaîtra dans les deux tableaux croisés dynamiques.

Volet de sélection

Vous pouvez activer et désactiver l'affichage des segments sur la feuille de calcul à l'aide du volet de sélection.

• Cliquez sur le slicer - Location.

• Cliquez sur l'onglet OPTIONS sur le ruban.

• Cliquez sur le volet de sélection dans le groupe Organiser sur le ruban. Le volet de sélection apparaît sur le côté droit de la fenêtre.

Comme vous pouvez le constater, les noms de tous les segments sont répertoriés dans le volet Sélection. Sur le côté droit des noms, vous pouvez trouver le symbole de visibilité - indiquant que le segment est visible sur la feuille de calcul.

Cliquez sur le symbole du mois. Le symbole se transforme en symbole , indiquant que le segment est masqué (non visible).

Comme vous pouvez le constater, le slicer - Month n'est pas affiché sur la feuille de calcul. Cependant, rappelez-vous que vous n'avez pas supprimé le segment pour le mois, mais que vous venez de le masquer.

• Cliquez sur le symbole du mois.

• Le symbole se transforme en symbole , indiquant que le segment est maintenant visible.

Lorsque vous activez / désactivez la visibilité d'un segment, la sélection des éléments de ce segment pour le filtrage reste inchangée. Vous pouvez également modifier l'ordre des segments dans le volet Sélection en les faisant glisser vers le haut / le bas.

Si vous avez plusieurs champs dans l'une des zones de tableau croisé dynamique, la disposition de tableau croisé dynamique dépend de l'ordre dans lequel vous placez les champs dans cette zone. C'est ce qu'on appelle l'ordre d'imbrication.

Si vous savez comment vos données sont structurées, vous pouvez placer les champs dans l'ordre requis. Si vous n'êtes pas sûr de la structure des données, vous pouvez modifier l'ordre des champs qui modifie instantanément la disposition du tableau croisé dynamique.

Dans ce chapitre, vous comprendrez l'ordre d'imbrication des champs et comment vous pouvez modifier l'ordre d'imbrication.

Ordre d'imbrication des champs

Prenons l'exemple des données de vente, où vous avez placé les champs dans l'ordre suivant -

Comme vous pouvez le voir, dans la zone des lignes, il y a deux champs - vendeur et région dans cet ordre. Cet ordre des champs est appelé ordre d'imbrication, c'est-à-dire le vendeur d'abord et la région ensuite.

Dans le tableau croisé dynamique, les valeurs des lignes seront affichées en fonction de cet ordre, comme indiqué ci-dessous.

Comme vous pouvez le constater, les valeurs du deuxième champ dans l'ordre d'imbrication sont incorporées sous chacune des valeurs du premier champ.

Dans vos données, chaque commercial est associé à une seule région, alors que la plupart des régions sont associées à plusieurs commerciaux. Par conséquent, il est possible que si vous inversez l'ordre d'imbrication, votre tableau croisé dynamique semble plus significatif.

Modifier l'ordre d'imbrication

Pour modifier l'ordre d'imbrication des champs dans une zone, cliquez simplement sur le champ et faites-le glisser vers la position souhaitée.

Cliquez sur le champ Vendeur dans la zone ROWS, et faites-le glisser sous le champ Région. Par conséquent, vous avez modifié l'ordre d'imbrication en - Région d'abord et vendeur ensuite, comme suit -

Le tableau croisé dynamique résultant sera comme indiqué ci-dessous -

Vous pouvez clairement observer que la mise en page avec l'ordre d'imbrication - Région puis vendeur donne un rapport meilleur et plus compact que celui avec l'ordre d'imbrication - Vendeur puis Région.

Dans le cas où un vendeur représente plus d'un domaine et que vous avez besoin de résumer les ventes par vendeur, alors la mise en page précédente aurait été une meilleure option.

Dans la feuille de calcul contenant un tableau croisé dynamique, le ruban contiendra les outils de tableau croisé dynamique, avec les onglets ANALYSE et CONCEPTION. L'onglet ANALYSE comporte plusieurs commandes qui vous permettront d'explorer les données du tableau croisé dynamique. Les commandes de l'onglet CONCEPTION seront utiles pour structurer le tableau croisé dynamique avec diverses options de rapport et de style.

Vous apprendrez les commandes ANALYSER dans ce chapitre. Vous apprendrez les commandes de CONCEPTION dans le chapitre - Rapports esthétiques avec tableaux croisés dynamiques.

Commandes ANALYZE

Les commandes du ruban de l'onglet ANALYSER incluent les suivantes -

• Extension et réduction d'un champ.
• Regroupement et dissociation des valeurs de champ.
• Paramètres de champ actifs.
• Options de tableau croisé dynamique.

Développer et réduire un champ

Si vous avez des champs imbriqués dans votre tableau croisé dynamique, vous pouvez développer et réduire un seul élément ou vous pouvez développer et réduire tous les éléments du champ actif.

Considérez le tableau croisé dynamique suivant, dans lequel le champ Vendeur est imbriqué sous le champ Région.

Cliquez sur le symbole à gauche de l'Est. L'élément Est du champ Région se réduira.

Comme vous pouvez le constater, les autres éléments - Nord, Sud et Ouest du champ Région ne sont pas réduits. Si vous souhaitez réduire l'un d'entre eux, répétez les étapes que vous avez effectuées pour l'Est.

• Cliquez sur le symbole à gauche de l'Est. L'élément à l'est du champ Région se développera.

Si vous souhaitez réduire tous les éléments d'un champ à la fois, procédez comme suit:

• Cliquez sur l'un des éléments du champ - Région.
• 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 Région seront réduits.

Si vous souhaitez développer tous les éléments d'un champ à la fois, procédez comme suit:

• Cliquez sur l'un des éléments du champ - Région.
• Cliquez sur l'onglet ANALYSER sur le ruban.
• Cliquez sur Développer le champ dans le groupe Champ actif.

Tous les éléments du champ Région seront développés.

Regroupement et dissociation des valeurs de champ

Vous pouvez regrouper et dissocier des valeurs de champ pour définir votre propre clustering. Par exemple, vous souhaiterez peut-être connaître les données combinant les régions Est et Nord.

• Sélectionnez les éléments Est et Nord du champ Région dans le tableau croisé dynamique, ainsi que les éléments de champ Vendeur imbriqués.

• Cliquez sur l'onglet ANALYSER sur le ruban.

• Cliquez sur Sélection de groupe dans le groupe - Groupe.

Les éléments - Est et Nord seront regroupés sous le nom Group1. De plus, un nouveau Sud est créé sous lequel le Sud est imbriqué et un nouvel Ouest est créé sous lequel l'Ouest est imbriqué.

Vous pouvez également observer qu'un nouveau champ - Region2 est ajouté dans la liste Champs de tableau croisé dynamique, qui apparaît dans la zone ROWS.

• Sélectionnez les éléments Sud et Ouest du champ Région2 dans le tableau croisé dynamique, ainsi que les éléments de champ Région imbriqués et Vendeur.

• Cliquez sur l'onglet ANALYSER sur le ruban.

• Cliquez sur Sélection de groupe dans le groupe - Groupe.

Les éléments - Sud et Ouest du champ Région seront regroupés sous le nom Groupe2.

Pour dissocier un groupe, procédez comme suit -

• Cliquez sur le nom du groupe.
• Cliquez sur l'onglet ANALYSER.
• Cliquez sur Dissocier dans le groupe - Groupe.

Regroupement par un champ de date

Considérez le tableau croisé dynamique suivant, dans lequel vous avez les données des employés résumées par nombre d'ID d'employé, embauché et titre.

Supposons que vous souhaitiez regrouper ces données par le champ HireDate qui est un champ Date en années et trimestres.

• Cliquez sur un élément de date dans le tableau croisé dynamique.
• Cliquez sur l'onglet ANALYSER sur le ruban.
• Cliquez sur Champ de groupe dans le groupe - Groupe.

La boîte de dialogue Regroupement apparaît.

• Définissez les dates pour - à partir de et se terminant à.

• Sélectionnez Trimestres et années dans la zone sous Par. Pour sélectionner / désélectionner plusieurs éléments, maintenez la touche Ctrl enfoncée.

• Cliquez sur OK.

Les valeurs du champ HireDate seront regroupées en trimestres, imbriquées en années.

Si vous souhaitez dissocier ce regroupement, vous pouvez procéder comme indiqué précédemment en cliquant sur Ungroup dans le groupe - Groupe sur le ruban.

Paramètres du champ de valeur active

Vous pouvez définir des options de champ en cliquant sur une valeur de ce champ. Prenons l'exemple des données de ventes que nous avons utilisées plus tôt dans ce chapitre.

Supposons que vous souhaitiez définir les options du champ Région.

• Cliquez sur Est. Sur le ruban, dans le groupe Champ actif, dans la zone Champ actif, Région s'affiche.

• Cliquer sur Field Settings. La boîte de dialogue Paramètres du champ s'affiche.

Vous pouvez définir vos préférences pour le champ - Région.

Options de tableau croisé dynamique

Vous pouvez définir les options du tableau croisé dynamique en fonction de vos préférences.

• Cliquez sur le tableau croisé dynamique.
• Cliquez sur l'onglet ANALYSER.
• Cliquez sur Options dans le groupe Tableau croisé dynamique.

le PivotTable Optionsla boîte de dialogue apparaît. Vous pouvez définir vos préférences dans la boîte de dialogue.

Vous pouvez résumer un tableau croisé dynamique en plaçant un champ dans la zone ∑ VALEURS du volet des tâches Champs de tableau croisé dynamique. Par défaut, Excel prend la synthèse comme somme des valeurs du champ dans la zone ∑ VALEURS. Cependant, vous avez d'autres types de calcul, tels que, Nombre, Moyenne, Max, Min, etc.

Dans ce chapitre, vous apprendrez à définir un type de calcul en fonction de la manière dont vous souhaitez résumer les données dans le tableau croisé dynamique.

Somme

Considérez le tableau croisé dynamique suivant dans lequel vous disposez des données de vente résumées par région, par vendeur et par mois.

Comme vous pouvez le constater, lorsque vous faites glisser le champ Montant de la commande vers la zone ∑ VALEURS, il s'affiche sous la forme Somme du montant de la commande, indiquant que le calcul est considéré comme Somme. Dans le tableau croisé dynamique, dans le coin supérieur gauche, la somme du montant de la commande s'affiche. En outre, la colonne du total général et la ligne du total général sont affichées pour les sous-totaux par champ dans les lignes et les colonnes respectivement.

Paramètres du champ de valeur

Avec les paramètres de champ de valeurs, vous pouvez définir le type de calcul dans votre tableau croisé dynamique. Vous pouvez également décider de la manière dont vous souhaitez afficher vos valeurs.

• Cliquez sur Somme du montant de la commande dans la zone ∑ VALEURS.
• Sélectionnez Paramètres du champ de valeur dans la liste déroulante.

La boîte de dialogue Paramètres du champ de valeur s'affiche.

Le nom de la source est le champ et le nom personnalisé est la somme du champ. Le type de calcul est Sum. Clique leShow Values As languette.

Dans la zone Afficher les valeurs comme, No Calculationest affiché. Clique leShow Values Asboîte. Vous pouvez trouver plusieurs façons d'afficher vos valeurs totales.

% du total général

Vous pouvez afficher les valeurs du tableau croisé dynamique en% du total général.

• Dans la zone Nom personnalisé, tapez% du total général.
• Cliquez sur la case Afficher les valeurs sous.
• Cliquez sur% du total général dans la liste déroulante. Cliquez sur OK.

Le tableau croisé dynamique résume les valeurs en% du total général.

Comme vous pouvez le constater, la somme du montant de la commande dans le coin supérieur gauche du tableau croisé dynamique et dans la zone ∑ VALEURS du volet Champs de tableau croisé dynamique est remplacée par le nouveau nom personnalisé -% du total général.

• Cliquez sur l'en-tête de la colonne Grand Total.

• Tapez% du total général dans la barre de formule. Les en-têtes de colonne et de ligne changeront en% du total général.

% du total de la colonne

Supposons que vous souhaitiez résumer les valeurs en% du total de chaque mois.

• Cliquez sur Somme du montant de la commande dans la zone ∑ VALEURS.

• Sélectionnez Paramètres du champ de valeur dans la liste déroulante. La boîte de dialogue Paramètres du champ de valeur s'affiche.

• Dans la zone Nom personnalisé, tapez% du total mensuel.

• Cliquez sur la case Afficher les valeurs en tant que.

• Sélectionnez% du total de la colonne dans la liste déroulante.

• Cliquez sur OK.

Le tableau croisé dynamique résume les valeurs en% du total de la colonne. Dans les colonnes Mois, vous trouverez les valeurs en% du total mensuel spécifique.

• Cliquez sur l'en-tête de la colonne Grand Total.

• Tapez% du total de la colonne dans la barre de formule. Les en-têtes de colonne et de ligne changeront en% du total de la colonne.

% du total des lignes

Vous pouvez résumer les valeurs en% des totaux de la région et en% des totaux du vendeur, en sélectionnant% du total de la ligne dans la zone Afficher les valeurs sous dans la boîte de dialogue Paramètres du champ de valeur.

Compter

Supposons que vous souhaitiez résumer les valeurs en fonction du nombre de comptes par région, par vendeur et par mois.

• Désélectionnez Montant de la commande.

• Faites glisser Compte vers la zone ∑ VALEURS. La somme du compte sera affichée dans la zone ∑ VALEURS.

• Cliquez sur Sum of Account.

• Sélectionnez Paramètres du champ de valeur dans la liste déroulante. La boîte de dialogue Paramètres du champ de valeur s'affiche.

• Dans le champ Récapituler la valeur par zone, sélectionnez Nombre. Le nom personnalisé devient Nombre de comptes.

• Cliquez sur OK.

Le nombre de comptes sera affiché comme indiqué ci-dessous -

Moyenne

Supposons que vous souhaitiez résumer le tableau croisé dynamique par les valeurs moyennes du montant de la commande par région, par vendeur et par mois.

• Désélectionnez le compte.

• Faites glisser Order Amount vers la zone ∑ VALUES. La somme du montant de la commande sera affichée dans la zone ∑ VALEURS.

• Cliquez sur Somme du montant de la commande.

• Cliquez sur Paramètres du champ de valeur dans la liste déroulante. La boîte de dialogue Paramètres du champ de valeur s'affiche.

• Dans le champ Résumer la valeur par case, cliquez sur Moyenne. Le nom personnalisé devient Moyenne du montant de la commande.

• Cliquez sur OK.

La moyenne sera affichée comme indiqué ci-dessous -

Vous devez définir le format numérique des valeurs dans le tableau croisé dynamique pour le rendre plus présentable.

• Cliquez sur Moyenne du montant de la commande dans la zone ∑ VALEURS.

• Cliquez sur Paramètres du champ de valeur dans la liste déroulante. La boîte de dialogue Paramètres du champ de valeur s'affiche.

• Cliquez sur le bouton Format des nombres.

La boîte de dialogue Format de cellule s'affiche.

• Cliquez sur Nombre sous Catégorie.
• Tapez 2 dans la zone Nombre de décimales et cliquez sur OK.

Les valeurs du tableau croisé dynamique seront formatées en nombres avec deux décimales.

• Cliquez sur l'en-tête de la colonne Grand Total.

• Tapez Montant moyen de la commande dans la barre de formule. Les en-têtes de colonne et de ligne deviendront le montant moyen de la commande.

Max

Supposons que vous souhaitiez résumer le tableau croisé dynamique par les valeurs maximales de Montant de la commande par région, par vendeur et par mois.

• Cliquez sur Somme du montant de la commande.

• Sélectionnez Paramètres du champ de valeur dans la liste déroulante. La boîte de dialogue Paramètres du champ de valeur s'affiche.

• Dans le champ Résumer la valeur par zone, cliquez sur Max. Le nom personnalisé devient Max du montant de la commande.

Le tableau croisé dynamique affichera les valeurs maximales par région, par vendeur et par mois.

• Cliquez sur l'en-tête de la colonne Grand Total.

• Tapez Montant maximum de la commande dans la barre de formule. Les en-têtes de colonne et de ligne passeront tous deux à Montant de la commande max.

Min

Supposons que vous souhaitiez résumer le tableau croisé dynamique par les valeurs minimales du montant de la commande par région, par vendeur et par mois.

• Cliquez sur Somme du montant de la commande.

• Cliquez sur Paramètres du champ de valeur dans la liste déroulante. La boîte de dialogue Paramètres du champ de valeur s'affiche.

• Dans le champ Récapituler la valeur par zone, cliquez sur Min. Le nom personnalisé devient le montant minimum de la commande.

Le tableau croisé dynamique affichera les valeurs minimales par région, par vendeur et par mois.

• Cliquez sur l'en-tête de la colonne Grand Total.

• Tapez Montant minimum de la commande dans la barre de formule. Les en-têtes de colonne et de ligne changeront tous les deux en montant minimum de commande.

Vous avez appris à résumer des données avec un tableau croisé dynamique. Les données sur lesquelles le tableau croisé dynamique est basé peuvent être mises à jour périodiquement ou à l'occurrence d'un événement. En outre, vous devrez peut-être modifier la disposition du tableau croisé dynamique pour différents rapports.

Dans ce chapitre, vous apprendrez les différentes manières de mettre à jour la mise en page et / ou d'actualiser les données dans un tableau croisé dynamique.

Mise à jour de la disposition du tableau croisé dynamique

Vous pouvez décider si votre tableau croisé dynamique doit être mis à jour chaque fois que vous apportez des modifications à la disposition ou s'il doit être mis à jour par un déclencheur distinct.

Comme vous l'avez appris précédemment, dans le volet Office Champs de tableau croisé dynamique, en bas, vous trouverez une case à cocher pour différer la mise à jour de la mise en page. Par défaut, elle n'est pas cochée, ce qui signifie que la disposition du tableau croisé dynamique est mise à jour dès que vous apportez des modifications dans les zones de tableau croisé dynamique.

Cochez l'option - Defer Layout Update.

Le bouton UPDATE à côté sera activé. Si vous apportez des modifications aux zones de tableau croisé dynamique, les modifications ne seront reflétées qu'après avoir cliqué sur le bouton MISE À JOUR.

Actualisation des données de tableau croisé dynamique

Lorsque les données d'un tableau croisé dynamique sont modifiées dans sa source, la même chose peut être reflétée dans le tableau croisé dynamique en l'actualisant.

• Cliquez sur le tableau croisé dynamique.
• Cliquez sur l'onglet ANALYSER sur le ruban.
• Cliquez sur Actualiser dans le groupe Données.

Il existe différentes options pour actualiser les données dans la liste déroulante -

• Refresh - Pour obtenir les dernières données de la source connectée à la cellule active.

• Refresh All - Pour obtenir les dernières données en actualisant toutes les sources du classeur.

• Connection Properties - Pour définir les propriétés d'actualisation des connexions au classeur.

Modification des données source d'un tableau croisé dynamique

Vous pouvez modifier la plage des données source d'un tableau croisé dynamique. Par exemple, vous pouvez étendre les données source pour inclure plus de nombre de lignes de données.

Toutefois, si les données source ont été modifiées de manière substantielle, par exemple avoir plus ou moins de colonnes, envisagez de créer un nouveau tableau croisé dynamique.

• Cliquez sur le tableau croisé dynamique. PIVOTTABLE TOOLS apparaissent sur le ruban.

• Cliquez sur l'onglet ANALYSER.

• Cliquez sur Modifier la source de données dans le groupe Données.

Sélectionnez Modifier la source de données dans la liste déroulante.

La boîte de dialogue Modifier la source de données de tableau croisé dynamique s'affiche et la source de données actuelle est mise en surbrillance.

Sélectionnez la table ou la plage que vous souhaitez inclure dans la zone Table / plage sous Sélectionner une table ou une plage. Cliquez sur OK.

La source de données du tableau croisé dynamique sera remplacée par la table / plage de données sélectionnée.

Passer à une source de données externe

Si vous souhaitez modifier la source de données de votre tableau croisé dynamique qui est externe, il peut être préférable de créer un nouveau tableau croisé dynamique. Toutefois, si l'emplacement de votre source de données externe est modifié, par exemple, le nom de votre base de données SQL Server est le même, mais il a été déplacé vers un autre serveur ou votre base de données Access a été déplacée vers un autre partage réseau, vous pouvez modifier votre connexion de données actuelle pour refléter la même chose.

• Cliquez sur le tableau croisé dynamique.

• Cliquez sur l'onglet ANALYSER sur le ruban.

• Cliquez sur Change Data Sourcedans le groupe Données. leChange PivotTable Data Source la boîte de dialogue apparaît.

• Clique le Choose Connection bouton.

le Existing Connections la boîte de dialogue apparaît.

• Sélectionnez Toutes les connexions dans la zone Afficher. Toutes les connexions de votre classeur seront affichées.

• Clique le Browse for More bouton.

le Select Data Source la fenêtre apparaît.

• Cliquez sur le bouton Nouvelle source.
• Suivez les étapes de l'assistant de connexion de données.

Si votre source de données se trouve dans un autre classeur Excel, procédez comme suit -

• Cliquez sur la case Nom de fichier.
• Sélectionnez le nom de fichier du classeur.

Suppression d'un tableau croisé dynamique

Vous pouvez supprimer un tableau croisé dynamique comme suit -

• Cliquez sur le tableau croisé dynamique.
• Cliquez sur l'onglet ANALYSER sur le ruban.
• Cliquez sur Sélectionner dans le groupe Actions.

Sélectionner Entire PivotTabledans la liste déroulante. L'ensemble du tableau croisé dynamique sera sélectionné.

Appuyez sur la touche Suppr. Le tableau croisé dynamique sera supprimé.

Si le tableau croisé dynamique se trouve sur une feuille de calcul distincte, vous pouvez également supprimer le tableau croisé dynamique en supprimant la feuille de calcul entière.

Cliquez avec le bouton droit sur l'onglet de la feuille de calcul et sélectionnez Delete dans la liste déroulante.

La feuille de calcul entière avec le tableau croisé dynamique est supprimée.

L'utilisation principale du tableau croisé dynamique est la création de rapports. Une fois que vous avez créé un tableau croisé dynamique, exploré les données en organisant et réorganisant les champs dans ses lignes et ses colonnes, vous serez prêt à présenter les données à un large public. Avec des filtres, différentes synthèses, en se concentrant sur des données spécifiques, vous serez en mesure de générer plusieurs rapports obligatoires basés sur un seul tableau croisé dynamique.

Comme un rapport de tableau croisé dynamique est interactif, vous pouvez rapidement apporter les modifications nécessaires pour mettre en évidence les résultats spécifiques, tels que les tendances des données, les récapitulatifs des données, etc. tout en le présentant. Vous pouvez également fournir des indices visuels tels que des filtres de rapport, des segments, une chronologie, des graphiques croisés dynamiques, etc. aux destinataires afin qu'ils puissent visualiser les détails qu'ils souhaitent.

Dans ce chapitre, vous apprendrez les différentes façons de rendre vos rapports de tableau croisé dynamique attrayants avec des indices visuels qui permettent une exploration rapide des données.

Hiérarchies

Vous avez appris à imbriquer des champs pour former une hiérarchie, dans le chapitre - Imbrication dans un tableau croisé dynamique de ce didacticiel. Vous avez également appris à grouper / dissocier des données dans un tableau croisé dynamique dans le chapitre - Utilisation des outils de tableau croisé dynamique. Nous allons prendre quelques exemples pour vous montrer comment produire des rapports de tableau croisé dynamique interactifs avec des hiérarchies.

Si vous disposez d'une structure intégrée pour les champs de vos données, telle que Année-Trimestre-Mois, l'imbrication des champs pour former une hiérarchie vous permettra de développer / réduire rapidement les champs pour afficher les valeurs résumées au niveau requis.

Par exemple, supposons que vous ayez les données de ventes pour l'exercice 2015-2016 pour les régions - Est, Nord, Sud et Ouest, comme indiqué ci-dessous.

Créez un tableau croisé dynamique comme indiqué ci-dessous.

Comme vous pouvez le constater, il s'agit d'un moyen complet de rapporter les données en utilisant les champs imbriqués sous forme de hiérarchie. Si vous souhaitez afficher les résultats uniquement au niveau des trimestres, vous pouvez rapidement réduire le champ Trimestre.

Supposons que vous ayez un champ Date dans vos données comme indiqué ci-dessous.

Dans ce cas, vous pouvez regrouper les données par le champ Date comme suit -

Créez un tableau croisé dynamique.

Comme vous pouvez le constater, ce tableau croisé dynamique n'est pas pratique pour mettre en évidence des données importantes.

• Regroupez le champ Tableau croisé dynamique par date. (Vous avez appris le regroupement dans le chapitre - Exploration des données avec les outils de tableau croisé dynamique de ce didacticiel).

• Placez le champ Vendeur dans la zone Filtres.

• Filtrez les étiquettes de colonne sur la région Est.

Filtre de rapport

Supposons que vous souhaitiez un rapport pour chaque vendeur séparément. Vous pouvez le faire comme suit -

• Assurez-vous que vous avez le champ Vendeur dans la zone Filtres.
• Cliquez sur le tableau croisé dynamique.
• Cliquez sur l'onglet ANALYSER sur le ruban.
• Cliquez sur la flèche en regard de Options dans le groupe Tableau croisé dynamique.
• Sélectionnez Afficher les pages de filtre de rapport dans la liste déroulante.

le Show Report Filter Pagesla boîte de dialogue apparaît. Sélectionnez le champ Vendeur et cliquez sur OK.

Une feuille de calcul distincte pour chacune des valeurs du champ Vendeur est créée, avec le tableau croisé dynamique filtré sur cette valeur.

La feuille de calcul sera nommée par la valeur du champ, qui est visible sur l'onglet de la feuille de calcul.

Trancheurs

Une autre fonctionnalité sophistiquée des tableaux croisés dynamiques est le segment qui peut être utilisé pour filtrer visuellement les champs.

• Cliquez sur le tableau croisé dynamique.

• Cliquez sur l'onglet ANALYSER.

• Cliquez sur Insérer un segment dans le groupe Filtre.

• Cliquez sur Order Date, Quarters and Yearsdans la boîte de dialogue Insérer des segments. Trois segments: la date, les trimestres et l'année de la commande seront créés.

• Ajustez la taille des segments, en ajoutant plus de colonnes pour les boutons sur les segments.

• Créez également des segments pour les champs Vendeur et Région.

• Choisissez les styles de segment pour que les champs de date soient regroupés en une seule couleur et que les deux autres champs aient des couleurs différentes.

• Désélectionnez Quadrillage.

Comme vous pouvez le voir, vous disposez non seulement d'un rapport interactif, mais aussi d'un rapport attrayant, qui peut être facilement compris.

Chronologie dans le tableau croisé dynamique

Lorsque vous avez un champ Date dans votre tableau croisé dynamique, l'insertion d'une chronologie est également une option pour produire un rapport esthétique.

• Créez un tableau croisé dynamique avec le vendeur dans la zone ROWS et la région dans la zone COLUMNS.
• Insérez une chronologie pour le champ Date de commande.
• Filtrez la chronologie pour afficher les données sur 5 mois, de novembre 2015 à mars 2016.

Commandes DESIGN

le PIVOTTABLE TOOLS - DESIGN Les commandes du ruban vous fournissent les options pour formater un tableau croisé dynamique, y compris les suivantes -

• Layout
• Options de style de tableau croisé dynamique
• Styles de tableau croisé dynamique

Disposition

Vous pouvez avoir une disposition de tableau croisé dynamique en fonction de vos préférences pour les éléments suivants:

• Subtotals
• Grands totaux
• Présentation du rapport
• Lignes vierges

PivotTable Layout – Subtotals

Vous avez la possibilité d'afficher Subtotalsou pas. Par défaut, les sous-totaux sont affichés, en haut du groupe.

Comme vous pouvez observer le groupe en surbrillance - Est, les sous-totaux sont en haut du groupe. Vous pouvez modifier la position des sous-totaux comme suit -

• Cliquez sur le tableau croisé dynamique.
• Cliquez sur l'onglet CONCEPTION sur le ruban.
• Cliquez sur Sous-totaux dans le groupe Options de mise en page.
• Cliquez sur Afficher tous les sous-totaux en bas du groupe.

Les sous-totaux apparaîtront désormais au bas de chaque groupe.

Si vous n'avez pas à déclarer les sous-totaux, vous pouvez sélectionner - Ne pas afficher les sous-totaux.

Grands totaux

Vous pouvez choisir d'afficher ou non les totaux généraux. Vous avez quatre combinaisons possibles -

• Désactivé pour les lignes et les colonnes
• Activé pour les lignes et les colonnes
• Activé pour les lignes uniquement
• Activé pour les colonnes uniquement

Par défaut, il s'agit de la deuxième combinaison - Activé pour les lignes et les colonnes.

Présentation du rapport

Vous pouvez choisir parmi plusieurs présentations de rapport, celle qui convient le mieux à vos données.

• Forme compacte.
• Formulaire de contour.
• Forme tabulaire.

Vous pouvez également choisir de répéter ou non toutes les étiquettes d'élément, en cas d'occurrences multiples.

La mise en page de rapport par défaut est le formulaire compact que vous connaissez bien.

Compact Form

La forme compacte optimise le tableau croisé dynamique pour la lisibilité. Les deux autres formulaires affichent également les en-têtes de champ.

Cliquer sur Show sous forme de contour.

Cliquez sur Show sous forme tabulaire.

Considérez la disposition de tableau croisé dynamique suivante, dans laquelle le champ Mois est imbriqué sous le champ Région -

Comme vous pouvez le constater, les étiquettes de mois sont répétées et c'est la valeur par défaut.

Cliquez sur Ne pas répéter les étiquettes d'élément. Les étiquettes de mois ne seront affichées qu'une seule fois et le tableau croisé dynamique semble clair.

Lignes vierges

Pour rendre votre rapport de tableau croisé dynamique plus distinct, vous pouvez insérer une ligne vide après chaque élément. Vous pouvez supprimer ces lignes vides à tout moment ultérieurement.

Cliquez sur Insert Blank Line after Each Item.

Options de style de tableau croisé dynamique

Vous disposez des options de style de tableau croisé dynamique suivantes -

• En-têtes de ligne
• En-têtes de colonne
• Lignes en bandes
• Colonnes en bandes

Par défaut, les cases des en-têtes de ligne et des en-têtes de colonne sont cochées. Ces options permettent d'afficher un formatage spécial pour la première ligne et la première colonne respectivement. Cochez la caseBanded Rows.

Cochez la case Colonnes en bandes.

Styles de tableau croisé dynamique

Vous pouvez choisir plusieurs styles de tableau croisé dynamique. Sélectionnez celui qui convient à votre rapport. Par exemple, si vous choisissez Pivot Style Dark 5, vous obtiendrez le style suivant pour le tableau croisé dynamique.

Mise en forme conditionnelle dans le tableau croisé dynamique

Vous pouvez définir la mise en forme conditionnelle sur les cellules de tableau croisé dynamique par les valeurs.

Graphiques croisés dynamiques

Les graphiques croisés dynamiques ajoutent une emphase visuelle à vos rapports de tableau croisé dynamique. Vous pouvez insérer un graphique croisé dynamique lié aux données d'un tableau croisé dynamique comme suit -

• Cliquez sur le tableau croisé dynamique.
• Cliquez sur l'onglet ANALYSER sur le ruban.
• Cliquez sur Graphique croisé dynamique.

La boîte de dialogue Insérer un graphique s'affiche.

Cliquez sur Colonne dans le volet gauche et sélectionnez Colonne empilée. Cliquez sur OK.

Le graphique à colonnes empilées s'affiche.

• Cliquez sur Mois sur le graphique croisé dynamique.
• Filtrez sur février et cliquez sur OK.

Comme vous pouvez le constater, le tableau croisé dynamique est également filtré selon le graphique croisé dynamique.