Excel - Power Query - Power Pivot - VBA

Excel - Power Query - Power Pivot - VBA

Power Pivot

Gonzague Ducos

Power Pivot est un complément d’Excel, qui permet de gérer 2 choses :

un modèle de données, qui permet de paramétrer les champs (format, tri...) et des relations entre tables,

des calculs avec le langage fonctionnel DAX (cumuls, % d’évolutions...)

Les relations permettent ensuite de faire des TCD avec des champs de plusieurs tables.

Notez que dans Power BI, ces fonctionnalités font partie intégrantes du logiciel alors qu’il faut utiliser le module Power Pivot dans Excel.

Le langage DAX est détaillé dans notre documentation spécifique

DAX⁠

Qu’est qu’un modèle de données ?

⁠

Aide Microsoft⁠

⁠

Un modèle de données vous permet de manipuler les données de plusieurs tables dans un même TCD, en créant une source de données relationnelle à l’intérieur d’un classeur Excel.

1- Ajouter des données au modèle

Les données d’un modèle peuvent provenir d’un Tableau dans le classeur actif, ou mieux d’un autre classeur.

A/ Provenant du classeur courant

Dans ce scénarios, vous manipulez des données directement depuis un classeur.

⁠

(1) Cliquer DANS le tableau.

(2) Power Pivot > Ajouter au modèle de données.

Power Pivot s’ouvre alors. Vous pouvez pour l’instant fermer la fenêtre Power Pivot ou la laisser en arrière plan.

B/ Provenant d’un autre classeur (recommandé)

Créer un classeur (vide donc) dans lequel vous placerez les requêtes Power Query et le modèle.

[Excel 2010 2013] Power Query > Obtenir des données externes [Excel >=2016] Données > Obtenir des données > A partir d’un fichier > A partir d’un classeur.

Sélectionner un fichier Excel contenant des données (tableaux ou plages).

Cocher la ou les éléments à importer (1) puis cliquer sur Charger > Charger dans (2) :

⁠

Laisser sélectionné Ne créer que la connexion (1), cocher Ajouter ces données au modèle de données (2) et valider en cliquant sur OK (3).

⁠

Excel affiche les requêtes ainsi générées dans le volet Requêtes et connexions, à droite d’Excel (pour afficher ce volet : Données > Requêtes et connexions).

⁠

Si vous cliquer sur Charger (sans faire Charger dans), le résultat de la requête est envoyé dans un “Tableau vert”. Le problème dans ce cas est que le classeur aura la même taille que la source (une source de 100 000 lignes affichera 100 000 lignes dans ce Tableau vert) et il faudra cliquer 2 fois sur Actualiser tout (la première actualisation mettra à jour la requête, la seconde le TCD). Pour éviter ces problèmes, modifier le mode de chargement (point C/ ci-dessous).

C/ Modifier le mode de chargement

Si le mode doit être changé (erreur) :

Clique droit sur la requête dans le volet Requêtes et connexions > Charger dans.

⁠

Dans la boîte Importation de données qui s’affiche, cocher Ne créer que la connexion. Après validation, un message vous préviens que le “Tableau vert” va être supprimé :

⁠

2| Gérer les relations

Vous pouvez créer des relations entre les tables du modèle, soit dans l’interface Power Pivot, soit au moment de créer un tableau croisé (point 3 ci-dessous).

Lancer Power Pivot : Données > Accéder à la fenêtre Power Pivot.

⁠

Accueil > Vue de diagramme.

Faites glisser le champ d’une table vers une autre table :

⁠

3| Paramétrer le modèle

Dans Power Pivot (Données > :

⁠

)

Format

Sélectionner la ou les colonnes à formater puis cocher les options de formatage souhaitées. Tous les TCD qui utilisent ce champ afficheront ce format.

⁠

Trier

On peut trier une colonne A (qui contient du texte) par une colonne B (qui contient un nombre utilisé pour le tri). Accueil > Trier par colonne :

⁠

Ici on tri une colonne Nom du mois (Janvier, Février...) par le mois (1, 2... 12).

Masquer

Clic droit sur une colonne > Masquer dans les outils client.

Créer une mesure

Dans un TCD, quand on place un champ dans la zone Valeur, une somme (ou un comptage) est automatiquement créée. C’est une mesure implicite. Il est conseillé de créer des mesures explicites.

Deux emplacements pour créer des mesures :

dans Power Pivot, se placer sur n’importe quelle cellule de la zone de calcul puis saisir dans la barre de formule.

dans un TCD d’Excel, clic droit sur le nom d’une table > Ajouter une mesures

Pour faire la somme d’une colonne (dans Power Pivot) :

Cliquer dans la zone de calcul (1)

Saisir la formule (2)

Noter le :=

Le DAX n’est pas sensible à la casse

Formater la formule (3)

⁠

4| Analyser les données du modèle (TCD)

Créer un tableau croisé dynamique : Insertion > Tableau croisé dynamique.

Laisser cocher Utiliser le modèle de données de ce classeur. Cocher Nouvelle feuille de calcul :

⁠

Dans le volet Champs de tableau croisé dynamique, vous notez un lien Tous :

⁠

Vous pouvez en effet utiliser les champs de toutes les tables du modèle dans vos TCD. Ce sont les relations entre les tables qui vont produire les bons résultats.

Si vous n’avez pas créé des relations dans Power Pivot, Excel vous en informe. Cliquer sur Détecter automatiquement. Si la création automatique échoue, cliquer sur Créer OU utiliser Power Pivot (cf. 2| Gérer les relations dans cette page).

⁠