Si vous lisez ce didacticiel, il y a de fortes chances que vous ayez entendu parler (ou même utilisé) du tableau croisé dynamique Excel. C'est l'une des fonctionnalités les plus puissantes d'Excel (sans blague).
La meilleure partie de l'utilisation d'un tableau croisé dynamique est que même si vous ne connaissez rien à Excel, vous pouvez toujours faire des choses assez impressionnantes avec une compréhension très basique de celui-ci.
Commençons.
Cliquez ici pour télécharger les exemples de données et suivre.
Qu'est-ce qu'un tableau croisé dynamique et pourquoi devriez-vous vous en soucier ?
Un tableau croisé dynamique est un outil dans Microsoft Excel qui vous permet de résumer rapidement d'énormes ensembles de données (en quelques clics).
Même si vous êtes absolument nouveau dans le monde d'Excel, vous pouvez facilement utiliser un tableau croisé dynamique. C'est aussi simple que de faire glisser et déposer les en-têtes de lignes/colonnes pour créer des rapports.
Supposons que vous ayez un ensemble de données comme indiqué ci-dessous :
Il s'agit de données de vente constituées d'environ 1 000 lignes.
Il contient les données de vente par région, type de détaillant et client.
Maintenant, votre patron voudra peut-être savoir certaines choses à partir de ces données :
- Quelles ont été les ventes totales dans la région Sud en 2016 ?
- Quels sont les cinq principaux détaillants en termes de ventes ?
- Comment la performance de Home Depot se compare-t-elle à celle d'autres détaillants du Sud ?
Vous pouvez continuer et utiliser les fonctions d'Excel pour vous donner les réponses à ces questions, mais que se passe-t-il si soudainement votre patron propose une liste de cinq questions supplémentaires.
Vous devrez revenir aux données et créer de nouvelles formules à chaque fois qu'il y a un changement.
C'est là que les tableaux croisés dynamiques Excel sont vraiment utiles.
En quelques secondes, un tableau croisé dynamique répondra à toutes ces questions (comme vous l'apprendrez ci-dessous).
Mais le véritable avantage est qu'il peut accueillir votre patron capricieux axé sur les données en répondant immédiatement à ses questions.
C'est si simple, vous pouvez aussi prendre quelques minutes et montrer à votre patron comment le faire lui-même.
J'espère que vous avez maintenant une idée de la raison pour laquelle les tableaux croisés dynamiques sont si géniaux. Allons de l'avant et créons un tableau croisé dynamique à l'aide de l'ensemble de données (illustré ci-dessus).
Insertion d'un tableau croisé dynamique dans Excel
Voici les étapes pour créer un tableau croisé dynamique à l'aide des données ci-dessus :
- Cliquez n'importe où dans le jeu de données.
- Allez dans Insertion -> Tableaux -> Tableau croisé dynamique.
- Dans la boîte de dialogue Créer un tableau croisé dynamique, les options par défaut fonctionnent correctement dans la plupart des cas. Voici quelques éléments à vérifier :
- Tableau/Gamme : Il est rempli par défaut en fonction de votre ensemble de données. Si vos données n'ont pas de lignes/colonnes vides, Excel identifiera automatiquement la plage correcte. Vous pouvez le modifier manuellement si nécessaire.
- Si vous souhaitez créer le tableau croisé dynamique à un emplacement spécifique, sous l'option « Choisissez l'emplacement où vous souhaitez placer le rapport de tableau croisé dynamique », spécifiez l'emplacement. Sinon, une nouvelle feuille de calcul est créée avec le tableau croisé dynamique.
- Cliquez sur OK.
Dès que vous cliquez sur OK, une nouvelle feuille de calcul est créée avec le tableau croisé dynamique.
Pendant que le tableau croisé dynamique a été créé, vous n'y verrez aucune donnée. Tout ce que vous voyez est le nom du tableau croisé dynamique et une instruction sur une seule ligne à gauche, et les champs du tableau croisé dynamique à droite.
Maintenant, avant de nous lancer dans l'analyse des données à l'aide de ce tableau croisé dynamique, comprenons quels sont les écrous et les boulons qui composent un tableau croisé dynamique Excel.
Les écrous et boulons d'un tableau croisé dynamique Excel
Pour utiliser efficacement un tableau croisé dynamique, il est important de connaître les composants qui créent un tableau croisé dynamique.
Dans cette section, vous découvrirez :
- Cache de pivot
- Zone des valeurs
- Zone de lignes
- Zone des colonnes
- Zone des filtres
Cache de pivot
Dès que vous créez un tableau croisé dynamique à l'aide des données, quelque chose se passe dans le backend. Excel prend un instantané des données et les stocke dans sa mémoire. Cet instantané s'appelle le cache pivot.
Lorsque vous créez différentes vues à l'aide d'un tableau croisé dynamique, Excel ne revient pas à la source de données, mais utilise plutôt le cache croisé dynamique pour analyser rapidement les données et vous fournir le résumé/les résultats.
La raison pour laquelle un cache pivot est généré est d'optimiser le fonctionnement du tableau croisé dynamique. Même lorsque vous avez des milliers de lignes de données, un tableau croisé dynamique est très rapide pour résumer les données. Vous pouvez faire glisser et déposer des éléments dans les zones lignes/colonnes/valeurs/filtres et les résultats seront instantanément mis à jour.
Remarque : l'un des inconvénients du cache de pivot est qu'il augmente la taille de votre classeur. Puisqu'il s'agit d'une réplique des données source, lorsque vous créez un tableau croisé dynamique, une copie de ces données est stockée dans le cache pivot.
Lire la suite: Qu'est-ce que Pivot Cache et comment l'utiliser au mieux.
Zone des valeurs
La zone des valeurs est ce qui contient les calculs/valeurs.
Sur la base de l'ensemble de données présenté au début du didacticiel, si vous souhaitez rapidement calculer les ventes totales par région pour chaque mois, vous pouvez obtenir un tableau croisé dynamique comme indiqué ci-dessous (nous verrons comment le créer plus tard dans le didacticiel) .
La zone surlignée en orange est la zone des valeurs.
Dans cet exemple, il contient les ventes totales de chaque mois pour les quatre régions.
Zone de lignes
Les en-têtes à gauche de la zone Valeurs constituent la zone Lignes.
Dans l'exemple ci-dessous, la zone Lignes contient les régions (surlignées en rouge) :
Zone des colonnes
Les en-têtes en haut de la zone Valeurs constituent la zone Colonnes.
Dans l'exemple ci-dessous, la zone Colonnes contient les mois (surlignés en rouge) :
Zone des filtres
La zone Filtres est un filtre facultatif que vous pouvez utiliser pour approfondir l'ensemble de données.
Par exemple, si vous souhaitez uniquement voir les ventes des détaillants Multiline, vous pouvez sélectionner cette option dans la liste déroulante (surlignée dans l'image ci-dessous), et le tableau croisé dynamique se mettra à jour avec les données des détaillants Multiline uniquement.
Analyse des données à l'aide du tableau croisé dynamique
Essayons maintenant de répondre aux questions en utilisant le tableau croisé dynamique que nous avons créé.
Cliquez ici pour télécharger les exemples de données et suivre.
Pour analyser des données à l'aide d'un tableau croisé dynamique, vous devez décider de la manière dont vous souhaitez que le résumé des données apparaisse dans le résultat final. Par exemple, vous voudrez peut-être toutes les régions à gauche et le total des ventes juste à côté. Une fois que vous avez cette clarté à l'esprit, vous pouvez simplement faire glisser et déposer les champs pertinents dans le tableau croisé dynamique.
Dans la section Champs du tableau croisé dynamique, vous avez les champs et les zones (comme souligné ci-dessous) :
Les champs sont créés en fonction des données principales utilisées pour le tableau croisé dynamique. La section Zones est l'endroit où vous placez les champs, et selon l'endroit où va un champ, vos données sont mises à jour dans le tableau croisé dynamique.
C'est un simple mécanisme de glisser-déposer, où vous pouvez simplement faire glisser un champ et le placer dans l'une des quatre zones. Dès que vous faites cela, il apparaîtra dans le tableau croisé dynamique de la feuille de calcul.
Essayons maintenant de répondre aux questions de votre responsable à l'aide de ce tableau croisé dynamique.
Q1 : Quelles ont été les ventes totales dans la région Sud ?
Faites glisser le champ Région dans la zone Lignes et le champ Chiffre d'affaires dans la zone Valeurs. Cela mettrait automatiquement à jour le tableau croisé dynamique dans la feuille de calcul.
Notez que dès que vous déposez le champ Revenu dans la zone Valeurs, il devient Somme des revenus. Par défaut, Excel additionne toutes les valeurs d'une région donnée et affiche le total. Si vous le souhaitez, vous pouvez changer cela en Nombre, Moyenne ou d'autres métriques statistiques. Dans ce cas, la somme est ce dont nous avions besoin.
La réponse à cette question serait 21225800.
Q2 Quels sont les cinq principaux détaillants en termes de ventes ?
Faites glisser le champ Client dans la zone Ligne et le champ Chiffre d'affaires dans la zone des valeurs. Dans le cas où il y a d'autres champs dans la section de zone et que vous souhaitez le supprimer, sélectionnez-le simplement et faites-le glisser hors de celui-ci.
Vous obtiendrez un tableau croisé dynamique comme indiqué ci-dessous :
Notez que par défaut, les articles (en l'occurrence les clients) sont triés par ordre alphabétique.
Pour obtenir les cinq meilleurs détaillants, vous pouvez simplement trier cette liste et utiliser les cinq principaux noms de clients. Pour faire ça:
- Cliquez avec le bouton droit sur n'importe quelle cellule de la zone Valeurs.
- Allez dans Trier -> Trier du plus grand au plus petit.
Cela vous donnera une liste triée en fonction des ventes totales.
Q3 : Comment la performance de Home Depot se compare-t-elle à celle d'autres détaillants du Sud ?
Vous pouvez faire beaucoup d'analyses pour cette question, mais ici, essayons simplement de comparer les ventes.
Faites glisser le champ Région dans la zone Lignes. Faites maintenant glisser le champ Client dans la zone Lignes sous le champ Région. Lorsque vous faites cela, Excel comprendra que vous souhaitez classer vos données d'abord par région, puis par clients au sein des régions. Vous aurez quelque chose comme indiqué ci-dessous :
Faites maintenant glisser le champ Revenu dans la zone Valeurs et vous aurez les ventes pour chaque client (ainsi que la région globale).
Vous pouvez trier les détaillants en fonction des chiffres de vente en suivant les étapes ci-dessous :
- Faites un clic droit sur une cellule qui a la valeur des ventes pour n'importe quel détaillant.
- Allez dans Trier -> Trier du plus grand au plus petit.
Cela trierait instantanément tous les détaillants en fonction de la valeur des ventes.
Maintenant, vous pouvez parcourir rapidement la région du Sud et identifier que les ventes de Home Depot étaient de 3004600 et qu'elles ont fait mieux que quatre détaillants de la région du Sud.
Il existe maintenant plusieurs façons de dépecer le chat. Vous pouvez également placer la région dans la zone de filtre, puis sélectionner uniquement la région sud.
Cliquez ici pour télécharger les exemples de données.
J'espère que ce didacticiel vous donnera un aperçu de base des tableaux croisés dynamiques Excel et vous aidera à démarrer.
Voici d'autres didacticiels sur les tableaux croisés dynamiques que vous pourriez aimer :
- Préparation des données source pour le tableau croisé dynamique.
- Comment appliquer une mise en forme conditionnelle dans un tableau croisé dynamique dans Excel
- Comment regrouper des dates dans des tableaux croisés dynamiques dans Excel.
- Comment regrouper des nombres dans un tableau croisé dynamique dans Excel.
- Comment filtrer les données dans un tableau croisé dynamique dans Excel.
- Utilisation des trancheurs dans le tableau croisé dynamique Excel.
- Comment remplacer les cellules vides par des zéros dans les tableaux croisés dynamiques Excel
- Comment ajouter et utiliser des champs calculés de tableau croisé dynamique Excel.
- Comment actualiser le tableau croisé dynamique dans Excel.
