PowerPivot : Un complément d’Excel d’analyse des données
PowerPivot, c’est quoi ?
PowerPivot est un add-in i.e. un complément avancé pour Excel. Il permet de :
- effectuer des analyses de données complexes et de partager les résultats.
- créer des modèles sophistiqués
- établir des relations entre différentes tables, l’intérêt est alors de faire des analyses croisées.
- s’intégrer avec d’autres outils d’analyse de données comme PowerBi. D’ailleurs, PowerPivot utilise DAX permettant d’effectuer des calculs.
En conséquence, PowerPivot est un outil qui est capable de traiter une grande quantité de données dans Excel. Aussi, cet outil est à la base de la création de tableaux de bord et de rapports.
Mais comment intégrer PowerPivot dans un classeur Excel ?
A priori ce complément doit être ajouté dans le classeur Excel. Selon le site de Microsoft, PowerPivot est inclus dans les versions Office suivantes :
Microsoft 365 – Toutes les versions incluant les versions de bureau d’Excel pour Windows
Produits à achat définitif (licence pour la durée des droits de propriété intellectuelle)
Office Professionnel 2021
Office Famille & Business 2021
Office Famille & Étudiant 2021
Office Professionnel 2019
Microsoft Office Famille et Petite Entreprise 2019
Microsoft Office Famille et Étudiant 2019
Office 2016 Professionnel Plus (disponible via le programme de licence en volume uniquement)
Office 2013 Professionnel Plus
Excel 2013 autonome
Excel 2016 autonome
source : Microsoft
Ainsi, une fois installé, PowerPivot se trouve dans le ruban.
Tout d’abord, il faut aller dans :
Fichier/Options, puis on sélectionne : Compléments
Ensuite, en bas à dans la partie droite, on peut voir : Gérer : on sélectionne « compléments Excel »
Enfin, il faut cliquer sur atteindre : une nouvelle boite de dialogue apparaît : Compléments COM
Ici, on sélectionne : Microsoft PowerPivot pour Excel, puis OK
Maintenant, on peut voir Power Pivot dans le ruban à droite.
Comment créer un modèle de données pour PowerPivot dans Excel ?
Prenons l’exemple suivant : dans notre classeur, nous avons deux feuilles de calcul :
– l’une relative aux informations des clients
– l’autre relative aux commandes des clients
Notre objectif ici est de relier les deux feuilles de calcul, à savoir pour un client donné ayant fait une commande, quelles sont ses coordonnées ?
Comment ? par « ClientID » qui est unique et présent dans nos deux feuilles de calcul
A ce stade, pour cela, on va créer un modèle de données :
– tout d’abord, il faut cliquer sur n’importe quelle cellule de la feuille n°1 : « informations clients »
– puis, dans le ruban, on va cliquer sur Power Pivot et sur l’icône « Ajouter au modèle de données »
Une nouvelle feuille de calcul apparaît : « Power Pivot pour Excel »
Nous avons ici dans cette feuille de calcul des options pour travailler avec Power Pivot
De même, on refait la même opération pour la feuille de calcul n°2 intitulée « commandes clients »
Désormais, Excel a crée un modèle de données entre ces deux feuilles de calcul. Cette opération ne pourra pas être refaite par la suite. Par exemple, une fois que vous avez enregistré votre travail, si vous ouvrez à nouveau votre classeur et refaites l’opération, Excel affichera un message d’erreur.
Comment créer une relation dans un modèle de données ?
Tout d’abord, pour relier deux feuilles de calcul dans un modèle de données, on clique sur l’icône « Vue de diagramme » dans le groupe Affichage.
Ici, on peut voir les deux feuilles de calcul :
– informations clients
– commandes clients
Pour relier ces deux feuilles, on clique sur « ClientID » dans « Informations clients » en faisant une pression à gauche sur la souris de votre ordinateur. Une flèche apparaît que vous faîtes correspondre à « ClientID » dans « Commandes clients » à droite.
Ici, on peut analyser le type de relations qui existe entre les deux tables : il s’agit d’une relation de un à plusieurs.
En effet, un client peut effectuer plusieurs transactions.
Imaginons maintenant que l’on veut faire apparaître les coordonnées d’un client correspondant à une commande. On clique sur « vue de données » dans le groupe affichage.
On sélectionne la colonne « ClientID » dans « Informations clients », puis clic droit : créer une relation.
Une boite de dialogue apparaît : « créer une relation »
Ensuite, il suffit de cliquer sur le second encadré pour faire apparaître « commandes clients »
Ici, »clientID », identifiant unique, permet de relier les tables « informations clients » et « commandes clients »
Mais on ne peut pas valider par OK car, comme dit précédemment, Excel a déjà relié ces deux tables. On obtient un message d’erreur :
Comment créer un tableau croisé dynamique à partir d’un modèle de données ?
Un tableau croisé dynamique permet de regrouper et de comparer des données spécifiques.
Tout d’abord, on commence par cliquer sur l’icône du ruban : « tableau croisé dynamique », puis on sélectionne « tableau croisé dynamique ». On revient alors sur notre classeur originel.
Une boite de dialogue apparaît : « créer un tableau croisé dynamique ». On choisit « Nouvelle feuille de calcul ».
A droite, nous pouvons voir : « champs de tableau croisé dynamique » avec les deux feuilles de calcul :
– commandes clients
– informations clients
Ensuite, en cliquant sur ces deux champs, on retrouve la structure des feuilles de calcul avec les intitulés des colonnes
Imaginons maintenant que nous souhaitons pour chaque ville connaître le montant des commandes
On sélectionne alors « ville » de « Informations clients » dans la case « lignes » et »commandeID » de « commandes clients » dans « somme valeurs »
Imaginons maintenant que l’on souhaite savoir le nombre de commandes pour chaque ville.
Alors, on fait clic gauche dans somme de commandeID et on sélectionne : « Paramètres des champs de valeurs … »
La boite de dialogue « Paramètres des champs de valeurs » apparaît et dans l’onglet « synthèse des valeurs par », on sélectionne : » Nombre », puis OK
Enfin, on obtient alors le résultat suivant
Conclusion
Finalement, à partir de la création d’un modèle de données entre deux feuilles d’un classeur Excel, il est possible de traiter les données par la création d’un tableau croisé dynamique. Ainsi, ce tableau permet d’extraire des informations spécifiques.
