Utilisez les Super-TCD et Power Pivot pour analyser vos données Excel
Dans ce tutoriel, je vais vous montrer comment utiliser Power Pivot, une fonctionnalité méconnue, mais ultrapuissante d'Excel, pour analyser des données complexes en quelques clics au travers des Super-TCD.
Vous allez découvrir comment relier plusieurs tableaux, créer des calculs personnalisés et obtenir des indicateurs précieux sans avoir besoin de formules compliquées.
Si vous avez déjà utilisé des tableaux croisés dynamiques, vous allez adorer les Super-TCD !
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. Présentation
Pour illustrer ce tutoriel, nous allons pouvoir utiliser l’exemple suivant dans lequel nous allons souhaiter analyser les ventes d'une entreprise, synthétisées dans deux tableaux :
Pour analyser ces données, nous allons donc créer un Super-TCD.
Il s’agit d’un TCD qui va tirer parti de la puissance de Power Pivot est une fonctionnalité avancée d'Excel qui permet de créer des modèles de données complexes en reliant plusieurs tableaux.
Il permet de démultiplier les possibilités offertes par ces derniers, car il intègre un moteur de calcul performant (DAX - Data Analysis Expressions) et une capacité à gérer des millions de lignes de données, bien au-delà des limites traditionnelles d'Excel.
Imaginez que vous avez des données provenant de différentes sources, comme des ventes, des objectifs ou des données clients.
Power Pivot vous permet de les combiner et de les analyser sans avoir à tout fusionner manuellement. Grâce à ses relations entre tables, vous pouvez créer des modèles de données cohérents et interroger ces données de manière dynamique. Par exemple, vous pouvez calculer des indicateurs clés (KPI), créer des mesures personnalisées ou générer des rapports interactifs avec des tableaux croisés dynamiques.
C'est un outil puissant pour ceux qui travaillent avec des volumes de données importants ou des sources multiples, car il offre une flexibilité accrue, une meilleure performance et une analyse plus approfondie que les outils traditionnels d'Excel. De plus, il s'intègre parfaitement avec Power BI pour une visualisation et une diffusion des données encore plus poussées.
Pourquoi utiliser Power Pivot plutôt qu'un tableau croisé dynamique classique ?
Avec un tableau croisé dynamique classique, vous êtes limité à un seul tableau de données.
Si vous avez plusieurs tableaux, vous devez les fusionner manuellement, ce qui peut être fastidieux et source d'erreurs.
Power Pivot, en revanche, vous permet de relier plusieurs tableaux via des clés communes (comme un identifiant unique).
De plus, il introduit le langage DAX (Data Analysis Expressions), qui permet de créer des calculs avancés, comme des ratios ou des cumuls, directement dans votre modèle de données.
2. Créer un Modèle de Données avec Power Pivot
Pour commencer, nous allons importer nos deux tableaux dans Power Pivot :
- Sélectionnez les données du premier tableau ("Ventes par Vendeur").
- Allez dans le menu Insérer > Tableau Croisé Dynamique.
- Dans la fenêtre qui s'ouvre, cochez l'option « Ajouter ces données au modèle de données ». C’est en effet ici que se trouve la subtilité qui permet de passer d’un simple Tableau Croisé Dynamique (TCD) à Power Pivot. Le modèle de données est une structure relationnelle qui permet de lier plusieurs tables entre elles et d'effectuer des analyses complexes.
- Répétez cette étape pour le deuxième tableau ("Objectifs par Vendeur »).
- Maintenant, nous avons deux TCD vides reposant sur les données du modèle, nous pouvons retrouver toutes les tables du modèle dans chaque nouveau Super-TCD en cliquant sur « Tous » dans le panneau de construction du tableau croisé dynamique :
- Vous pouvez maintenant combiner des données des deux tables (par exemple, comparer les ventes réalisées aux objectifs par vendeur).
- Excel nous informe alors qu’une relation doit être établi entre les deux tables pour pouvoir combiner les informations de deux tables différentes. Nous cliquons alors sur « CRÉER » :
- Sélectionnez alors les deux tables et la colonne commune (« Vendeur ») :
Ici, nous allons au plus simple en partant directement de données déjà présentes dans Excel, mais l’une des forces de Power Pivot, c’est de pouvoir également inclure des données issues d’une base de données ou un fichier CSV en utilisant l'option « Power Pivot > Gérer > Obtenir des données externes ».
3. Créer des Calculs Avancés avec DAX
3.1. Calculer le Total des Ventes par Vendeur
Maintenant, que nos tableaux sont reliés, nous allons créer une mesure pour calculer le total des ventes par vendeur.
Une mesure est…
Dans la barre de formule, tapez la formule DAX suivante (attention DAX est sensible à la casse et aux espaces, alors assurez-vous de bien écrire les noms des colonnes et des tableaux), heureusement, nous sommes bien assistés lors de la création de la formule :
=[Somme de Montant des Ventes]
Cette formule additionne toutes les ventes du tableau 1.
Cliquez sur « Ok » pour valider.
Une fois la mesure ajoutée, nous pouvons constater que le résultat est identique à ce que nous avions déjà dans le tableau. Cela permet de vérifier que la mesure est bien définie.
3.2. Calculer le Pourcentage de l'Objectif Atteint
Ensuite, nous allons créer une mesure pour calculer le pourcentage de l'objectif atteint par chaque vendeur.
Toujours dans Power Pivot, créez une nouvelle mesure avec la formule suivante :
=DIVIDE([Somme de Montant des Ventes];[Somme de Objectif Annuel];0)
Ici, nous utilisons la fonction DIVIDE à la place de l'opérateur de division (/), car elle gère automatiquement les erreurs de division par zéro, ce qui évite des erreurs dans vos calculs.
Cette formule divise le total des ventes par l'objectif annuel pour obtenir un pourcentage.
Le résultat sera affiché sous forme décimale (par exemple, 0,75 pour 75 %).
3.3. Ajouter les Mesures au Tableau Croisé Dynamique
Recommençons la création d’une nouvelle mesure nommée « % de l’objectif » qui repose sur la formule suivante :
=DIVIDE([Somme de Montant des Ventes];[Somme de Objectif Annuel])
Pour aller plus loin, nous pouvons utiliser les filtres pour explorer les données. Par exemple, ajoutez « Vendeur » dans la zone des filtres pour afficher les résultats uniquement pour Alice, Bob ou Charlie.
Vous pouvez également utiliser des segments de filtre pour une exploration plus interactive.
Comme vous pouvez le constater, les Super-TCD permettent de manipuler et analyser des volumes de données massifs de manière rapide et efficace, en bénéficiant de la puissance de Power Pivot directement intégrée dans Excel. Grâce à cette combinaison, vous pouvez :
- Agréger des données provenant de plusieurs sources (bases de données, fichiers Excel, services en ligne, etc.) en un seul modèle de données cohérent.
- Créer des relations entre différentes tables pour des analyses multidimensionnelles complexes.
- Utiliser des mesures et des calculs DAX (Data Analysis Expressions) pour effectuer des analyses avancées et personnalisées.
- Gérer des millions de lignes de données sans ralentir les performances d'Excel.
- Automatiser et actualiser les rapports en temps réel avec des données dynamiques.
En résumé, les Super-TCD et Power Pivot transforment Excel en un outil puissant pour l'analyse de données, rivalisant avec des solutions BI professionnelles, tout en restant accessibles et intuitifs pour les utilisateurs d'Excel.