Power Pivot Excel : modèle de données et mesures DAX

Power Pivot est un complément d'Excel qui introduit un modèle de données, c'est-à-dire un espace interne où plusieurs tables cohabitent, reliées les unes aux autres.
Là où un tableau croisé classique ne sait analyser qu'une seule table, Power Pivot te permet de croiser des données venues de plusieurs tables sans jamais recopier la moindre colonne.

Avec lui, deux limites bien connues d'Excel s'effacent d'un coup.
Le plafond du million de lignes d'une feuille saute, puisque le modèle compresse les données et en avale plusieurs millions sans jamais ralentir le classeur.
Quant à l'obligation de tout rassembler dans un seul tableau à coups de RECHERCHEX, elle disparaît elle aussi, parce que tu gardes désormais tes tables séparées et tu les relies par une clé commune.

Le conseil du pro

Toute cette leçon s'appuie sur trois tables qui reviennent partout. Une table Ventes (date, identifiant produit, montant), une table Produits (identifiant produit, nom, catégorie) et une table Calendrier (date, mois, trimestre, année). On va les charger dans le modèle, les relier, puis poser des mesures DAX pour analyser le chiffre d'affaires sous tous les angles.

Et la bonne nouvelle, c'est que le moteur de Power Pivot est exactement celui de Power BI, si bien que le modèle de données et le langage DAX que tu apprends ici se transposent tels quels d'un outil à l'autre. C'est d'ailleurs pour ça que beaucoup de contrôleurs de gestion et de data analysts commencent par Power Pivot dans Excel, avant de basculer vers Power BI le jour où ils ont besoin de visuels interactifs et de partage en ligne.

Power Pivot est inclus dans Excel depuis la version 2013 et dans la plupart des abonnements Microsoft 365, mais il faut souvent l'activer à la main avant de le voir.
Tant qu'aucun onglet « Power Pivot » n'apparaît dans ton ruban, c'est qu'il dort dans les compléments, et quatre clics suffisent à le réveiller.

Comment activer le complément Power Pivot en 4 étapes

1. 1

   Va dans Fichier > Options > Compléments, la page qui recense tout ce qui peut se greffer à Excel.

2. 2

   En bas, dans le menu « Gérer », choisis « Compléments COM » puis clique sur « Atteindre » pour ouvrir la bonne liste.

3. 3

   Coche « Microsoft Power Pivot pour Excel » et valide, c'est ce qui charge le moteur dans Excel.

4. 4

   Repère le nouvel onglet « Power Pivot » dans le ruban, désormais ton point d'entrée vers la fenêtre du modèle.

Le savais-tu ?

Power Pivot n'existe ni dans Excel pour Mac, ni dans Excel pour le web, ni dans certaines éditions familiales d'Office. Il te faut une version Windows avec une licence qui l'inclut (Microsoft 365, Office Pro Plus, ou Excel 2016 et plus en édition complète), donc vérifie d'abord la présence de l'onglet avant d'aller plus loin.

Un modèle de données se monte en quatre temps qui s'enchaînent naturellement, depuis des tableaux propres jusqu'aux relations entre eux, en passant par le chargement et la vue de diagramme. Reprenons notre fil rouge avec les tables Ventes, Produits et Calendrier pour voir chacun de ces temps à l'œuvre.

Comment construire le modèle de données en 4 étapes

1. 1

   Mets chaque source (Ventes, Produits, Calendrier) en tableau structuré avec Ctrl + L, puis donne-lui un nom clair, car un modèle se construit sur des tableaux nommés, jamais sur des plages floues.

2. 2

   Place le curseur dans un tableau, puis Power Pivot > Ajouter au modèle de données. Le tableau apparaît comme un onglet dans la fenêtre Power Pivot, qui vit à part d'Excel. Recommence pour chaque source.

3. 3

   Dans la fenêtre Power Pivot, clique sur Accueil > Vue de diagramme. Les tables s'affichent en boîtes, chacune avec ses colonnes, prêtes à être reliées.

4. 4

   Glisse la colonne IdProduit de la table Ventes vers l'IdProduit de la table Produits, une ligne apparaît, signe que la relation est créée. Recommence pour relier Ventes à Calendrier par la date.

Tu peux aussi charger directement dans le modèle depuis Power Query, sans passer par une feuille intermédiaire. À l'étape « Fermer et charger sous », coche « Ajouter ces données au modèle de données », et le tableau atterrit aussitôt dans Power Pivot. C'est le pipeline idéal pour des sources externes, qu'il s'agisse d'un fichier CSV de ventes ou d'un export de base de données, parce que Power Query nettoie tandis que Power Pivot relie, chacun sur son terrain.

Power Pivot repose sur quatre notions qui s'emboîtent les unes dans les autres.
Comprends-les une bonne fois et tout le reste de la leçon découle naturellement, parce que chaque chapitre n'en est qu'une mise en pratique.

Le modèle de données, le socle qui réunit toutC'est un espace de stockage interne à Excel qui contient plusieurs tables compressées et reliées entre elles. Il vit en mémoire, dépasse largement la limite du million de lignes d'une feuille et sert de base commune à tes tableaux croisés, si bien que tu n'y saisis rien à la main, tu y charges simplement des tableaux comme nos tables Ventes, Produits et Calendrier.

Les relations, le lien qui remplace RECHERCHEXUne relation est un lien entre deux tables via une colonne commune, la clé. La table Ventes contient un IdProduit, la table Produits aussi, et dès que la relation est tracée le nom du produit apparaît dans un TCD de ventes sans qu'il faille jamais recopier la colonne à coups de RECHERCHEX. C'est ce mécanisme qui remplace les fusions manuelles d'antan.

Les mesures DAX, le calcul qui s'adapte au contexteCe sont des calculs définis une fois dans le modèle et recalculés par le TCD selon le contexte en cours, qu'il s'agisse de la ligne, de la colonne ou du filtre actif. Une mesure « Chiffre d'affaires » renvoie ainsi le bon total dans chaque cellule, par région, par mois ou par produit, grâce au DAX, un langage proche dans l'esprit des formules Excel. On y revient en détail juste après.

Le TCD branché sur le modèle, l'aboutissementAu lieu d'analyser une seule table, le tableau croisé puise d'un coup dans toutes les tables reliées, puisque tu places le nom du produit en ligne, le mois en colonne et ta mesure de chiffre d'affaires en valeur, et Excel croise les trois tables grâce aux relations. C'est vers ce résultat que tend tout le reste de la leçon.

L’erreur fatale

Une relation lie un côté « plusieurs » (la table Ventes, où un même produit revient des centaines de fois) à un côté « un » (la table Produits, où chaque produit n'apparaît qu'une seule fois). La colonne du côté « un » doit contenir des valeurs uniques, sans le moindre doublon, et si Power Pivot refuse une relation, c'est presque toujours parce que la clé n'est pas unique de ce côté-là.

Une fois les tables reliées, tu peux déjà bâtir un TCD avec les sommes automatiques.
Mais la vraie puissance de Power Pivot vient des mesures DAX, ces calculs que tu définis une seule fois et que le tableau croisé recalcule pour chaque cellule, selon le contexte du moment, la ligne, la colonne ou le filtre.

DAX (Data Analysis Expressions) ressemble beaucoup aux formules Excel, avec ses fonctions familières comme SUM, AVERAGE ou COUNT, à ceci près qu'elles s'appliquent à des colonnes entières du modèle plutôt qu'à des cellules isolées. Crée une mesure dans la fenêtre Power Pivot, via la zone de calcul en bas d'une table, ou par Power Pivot > Mesures > Nouvelle mesure. Voici deux exemples pour t'en donner l'intuition.

Le combo gagnant

Mesure et colonne calculée se répartissent le travail dans le modèle. Une colonne calculée ajoute une valeur figée à chaque ligne d'une table, ce qui sert pour une catégorie ou un indicateur ligne par ligne. Une mesure, elle, ne stocke rien et se recalcule à la volée selon le contexte du TCD. La règle tient en une phrase : dès que tu veux agréger une somme, une moyenne ou un ratio qui change selon le filtre, c'est une mesure, alors que pour enrichir chaque ligne d'un attribut, c'est une colonne calculée.

Brancher un tableau croisé sur le modèle

Où le trouver :Insertion›Tableau croisé dynamique

C'est l'aboutissement de toute la leçon, un TCD qui puise d'un coup dans toutes les tables reliées.
Avec notre fil rouge, tu vas analyser le chiffre d'affaires par catégorie de produit et par trimestre, alors même que ces trois informations vivent dans trois tables séparées.

Comment brancher un tableau croisé sur le modèle en 4 étapes

1. 1

   Depuis la fenêtre Power Pivot, fais Accueil > Tableau croisé dynamique. Ou, depuis Excel, fais Insertion > Tableau croisé dynamique et coche « Utiliser le modèle de données de ce classeur ».

2. 2

   La liste des champs affiche désormais toutes tes tables d'un coup. Glisse Catégorie, qui vient de la table Produits, dans la zone des lignes.

3. 3

   Glisse Trimestre, issu cette fois de la table Calendrier, dans la zone des colonnes.

4. 4

   Glisse enfin ta mesure Chiffre d'affaires dans la zone des valeurs. Le TCD croise alors les trois tables grâce aux relations, puisque Excel sait relier chaque vente à sa catégorie et à son trimestre.

Sans Power Pivot, ce même tableau t'aurait demandé de fusionner les trois tables avec des RECHERCHEX, colonne par colonne, avant de lancer le TCD. Avec le modèle, les relations font ce travail en arrière-plan une fois pour toutes, si bien que quand les données changent, il n'y a rien à refaire.

Ces trois outils ne s'opposent pas, ils se relaient le long d'un même pipeline.
Une fois qu'on sait lequel intervient à quel moment, le choix devient évident à chaque fois.

Power Query importe et nettoieC'est le premier maillon, celui qui connecte Excel à des sources externes, nettoie, dépivote et structure les données, puis s'efface une fois les tables propres. C'est le « avant » du pipeline, et sans lui les données qui arrivent dans Power Pivot seraient le plus souvent inutilisables.

Power Pivot relie et calculeLe deuxième maillon prend alors le relais, puisqu'il relie plusieurs tables propres dans un modèle, encaisse les gros volumes et calcule des mesures DAX. Il devient indispensable dès que ton analyse touche plus d'une table ou dépasse le million de lignes, là où Excel seul montre ses limites.

Le TCD classique analyse une seule tableCe maillon final reste parfait tant que tout tient dans une table propre de taille raisonnable, parce qu'il se monte vite, sans modèle ni relation. Mais pour une analyse multi-tables ou volumineuse, c'est le TCD branché sur le modèle Power Pivot qui prend la suite, en s'appuyant sur tout le travail mené en amont.

Ce qu’il faut retenir

Le pipeline complet enchaîne quatre maillons : sources brutes → Power Query (nettoie chaque table) → modèle Power Pivot (relie les tables, mesures DAX) → TCD branché sur le modèle (croise et analyse) → graphique. Chaque outil fait une chose, et il la fait bien. Comprendre ce découpage évite de tordre un seul outil pour tout faire.

Power Pivot impressionne, et la tentation est grande de le dégainer pour tout.
Pourtant il a un coût d'entrée, donc autant savoir reconnaître les situations où il change vraiment la donne et celles où un simple tableau croisé fait l'affaire.

Le conseil du pro

Compte tes tables. Une seule table propre de taille raisonnable ? Reste sur un tableau croisé classique, dont la leçon pose justement les bases que Power Pivot vient ensuite démultiplier. Deux tables ou plus à croiser, ou un gros volume ? C'est le signal pour passer au modèle Power Pivot. Tu y gagnes en clarté autant qu'en performance.

Avec le modèle de données et le DAX, tu touches déjà au plafond de ce qu'Excel fait nativement, mais il reste une dernière marche, et elle vient d'un tout autre univers.
La leçon bonus Python dans Excel te montre comment exécuter du vrai Python directement dans une cellule pour filtrer, regrouper et tracer tes données là où les formules s'arrêtent, histoire de pousser ton classeur encore un cran plus loin.