Chapitre 4 · Expert · Leçon 25 / 26
Power Pivot
C'est quoi Power Pivot, concrètement ?
Power Pivot est un complément d'Excel qui introduit un modèle de données : un espace interne où plusieurs tables cohabitent, reliées les unes aux autres. Là où un tableau croisé classique n'analyse qu'une seule table, Power Pivot te permet de croiser des données issues de plusieurs tables sans jamais recopier de colonnes.
Deux limites d'Excel disparaissent avec lui. D'abord, le plafond du million de lignes d'une feuille : le modèle compresse les données et en avale plusieurs millions sans ralentir le classeur. Ensuite, l'obligation de tout rassembler dans un seul tableau à coups de RECHERCHEX : tu gardes tes tables séparées et tu les relies par une clé commune.
Le fil rouge de cette leçon
On va travailler avec trois tables qui reviennent partout dans cette leçon. 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.
Le moteur de Power Pivot est exactement celui de Power BI. Le modèle de données et le langage DAX que tu apprends ici se transposent tels quels dans Power BI. Beaucoup de contrôleurs de gestion et de data analysts commencent par Power Pivot dans Excel avant de basculer vers Power BI quand ils ont besoin de visuels interactifs et de partage en ligne.
Activer le complément Power Pivot
Power Pivot est inclus dans Excel depuis la version 2013 et dans la plupart des abonnements Microsoft 365, mais il faut souvent l'activer manuellement. Tant que tu ne vois pas d'onglet « Power Pivot » dans ton ruban, suis ces étapes.
Activer en quatre clics
Fichier > Options > Compléments.
En bas, dans le menu « Gérer », choisis « Compléments COM » puis clique sur « Atteindre ».
Coche « Microsoft Power Pivot pour Excel » et valide.
Un onglet « Power Pivot » apparaît dans le ruban. C'est ton point d'entrée vers la fenêtre du modèle.
Sur Mac et sur certaines éditions
Power Pivot n'est pas disponible dans Excel pour Mac, ni dans Excel pour le web, ni dans certaines éditions familiales d'Office. Il faut une version Windows avec un abonnement ou une licence qui l'inclut (Microsoft 365, Office Pro Plus, Excel 2016 et plus en édition complète). Vérifie d'abord la présence de l'onglet avant d'aller plus loin.
Charger des tables et construire le modèle
Un modèle de données se construit en quatre temps : préparer des tableaux propres, les charger dans le modèle, passer en vue de diagramme, puis tracer les relations. Reprenons notre fil rouge avec les tables Ventes, Produits et Calendrier.
Mets tes données en tableaux structurés
Chaque source (ventes, produits, dates) devient un tableau Excel via Ctrl + L. Un nom clair par tableau (Ventes, Produits, Calendrier) facilite la suite. Un modèle se construit sur des tableaux nommés, pas sur des plages floues.
Charge chaque tableau dans le modèle
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. Répète pour chaque source. Le modèle vit dans une fenêtre séparée d'Excel.
Passe en vue de diagramme
Dans la fenêtre Power Pivot, clique sur Accueil > Vue de diagramme. Tu vois tes tables sous forme de boîtes, chacune avec ses colonnes. C'est ici que tu vas tracer les relations en reliant les colonnes communes.
Relie les tables par leur clé commune
Glisse la colonne IdProduit de la table Ventes vers la colonne IdProduit de la table Produits. Une ligne apparaît : la relation est créée. Recommence pour relier Ventes à Calendrier par la date. Le modèle est prêt à être analysé.
Tu peux aussi charger directement dans le modèle depuis Power Query : à l'étape « Fermer et charger sous », coche « Ajouter ces données au modèle de données ». C'est le pipeline idéal pour des sources externes, un fichier CSV de ventes ou un export de base de données : Power Query nettoie, le modèle relie.
Les quatre concepts à maîtriser
Power Pivot repose sur quatre notions qui s'emboîtent. Comprends-les une fois et tout le reste découle naturellement.
D'abord, le modèle de données. C'est un espace de stockage interne à Excel qui contient plusieurs tables compressées et reliées. Il vit en mémoire, dépasse la limite du million de lignes d'une feuille et sert de socle commun à tes tableaux croisés. Tu n'y saisis rien à la main : tu y charges des tableaux, comme nos tables Ventes, Produits et Calendrier.
Ensuite, les relations. Une relation est un lien entre deux tables via une colonne commune, la clé. La table Ventes contient un IdProduit, la table Produits aussi : la relation permet d'afficher le nom du produit dans un TCD de ventes, sans jamais recopier la colonne avec un RECHERCHEX. C'est ce qui remplace les fusions manuelles d'antan.
Puis les mesures DAX. Ce sont des calculs définis une fois dans le modèle et recalculés selon le contexte du TCD. Une mesure « Chiffre d'affaires » renvoie le bon total dans chaque cellule, par région, par mois, par produit. Le DAX est le langage de ces calculs, proche dans l'esprit des formules Excel. On y revient en détail juste après.
Enfin, le TCD branché sur le modèle. Au lieu d'analyser une seule table, le tableau croisé puise dans toutes les tables reliées du modèle. Tu places le nom du produit en ligne, le mois en colonne et ta mesure de chiffre d'affaires en valeur : Excel croise trois tables d'un coup. C'est l'aboutissement vers lequel tend tout le reste de la leçon.
La règle d'or des relations
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 doublon. Si Power Pivot refuse une relation, c'est presque toujours parce que la clé n'est pas unique de ce côté-là.
Les mesures DAX : le calcul qui s'adapte au contexte
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 : des calculs que tu définis une fois et que le tableau croisé recalcule pour chaque cellule, selon le contexte (la ligne, la colonne, le filtre).
DAX (Data Analysis Expressions) ressemble beaucoup aux formules Excel : mêmes fonctions familières (SUM, AVERAGE, COUNT), mais appliquées à des colonnes entières du modèle plutôt qu'à des cellules. Tu crées une mesure dans la fenêtre Power Pivot (zone de calcul en bas d'une table) ou via Power Pivot > Mesures > Nouvelle mesure. Voici deux exemples pour saisir l'idée.
Une mesure de somme simple
Chiffre d'affaires := SUM(Ventes[Montant])La mesure la plus courante. Elle additionne la colonne Montant de la table Ventes, et le TCD la recalcule pour chaque ligne, colonne et filtre. Place-la en valeur dans ton TCD et tu obtiens le CA par produit, par mois, par région, automatiquement.
Une mesure avec CALCULATE et un filtre
CA France := CALCULATE([Chiffre d'affaires], Ventes[Pays] = "France")CALCULATE est la fonction reine du DAX : elle évalue une mesure en forçant un filtre. Ici, le chiffre d'affaires limité à la France, quel que soit le contexte du TCD. C'est la brique qui ouvre la porte aux ratios, parts de marché et comparaisons.
Mesure ou colonne calculée ?
Une colonne calculée ajoute une valeur figée à chaque ligne d'une table (utile pour une catégorie ou un indicateur ligne par ligne). Une mesure ne stocke rien : elle se recalcule à la volée selon le contexte du TCD. Règle simple : si tu veux agréger (somme, moyenne, ratio) qui change selon le filtre, c'est une mesure. Si tu veux enrichir chaque ligne d'un attribut, c'est une colonne calculée.
Brancher un tableau croisé sur le modèle
C'est l'aboutissement : un TCD qui puise dans toutes les tables reliées d'un coup. Avec notre fil rouge, tu vas analyser le chiffre d'affaires par catégorie de produit et par trimestre, alors que ces informations vivent dans trois tables différentes.
La procédure
Dans la fenêtre Power Pivot : Accueil > Tableau croisé dynamique. Ou, depuis Excel : Insertion > Tableau croisé dynamique > « Utiliser le modèle de données de ce classeur ».
La liste des champs affiche désormais toutes tes tables. Glisse Catégorie (table Produits) en lignes.
Glisse Trimestre (table Calendrier) en colonnes.
Glisse ta mesure Chiffre d'affaires en valeurs. Le TCD croise les trois tables grâce aux relations : 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 et restent en place quand les données changent.
Power Pivot, Power Query ou TCD classique : qui fait quoi ?
Ces trois outils ne s'opposent pas, ils se relaient le long d'un même pipeline. Voici comment les situer.
Power Query importe et nettoie. C'est le premier maillon : il connecte Excel à des sources externes, nettoie, dépivote et structure les données. Son rôle s'arrête une fois les tables propres. C'est le « avant » du pipeline.
Power Pivot relie et calcule. Le deuxième maillon prend le relais : il relie plusieurs tables propres dans un modèle, gère 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.
Le TCD classique analyse une seule table. Le maillon final reste parfait quand tout tient dans une table propre de taille raisonnable : rapide à monter, sans modèle ni relation. Pour une analyse multi-tables ou volumineuse, c'est le TCD branché sur le modèle Power Pivot qui prend la suite.
Le pipeline complet
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 bien. Comprendre ce découpage évite de tordre un seul outil pour tout faire.
Quand sortir Power Pivot (et quand s'en passer)
Sors Power Pivot quand…
- Ton analyse croise plusieurs tables (ventes + produits + clients + dates).
- Tu dépasses le million de lignes ou ton classeur rame.
- Tu enchaînes des RECHERCHEX à n'en plus finir pour rassembler des données.
- Tu veux des calculs avancés réutilisables (ratios, parts, cumuls) via DAX.
Passe-t'en quand…
- Toutes tes données tiennent déjà dans une seule table propre.
- Ton volume est modeste (quelques milliers de lignes).
- Un TCD classique répond déjà à ta question en deux minutes.
- Tu es sur Mac ou sur le web (Power Pivot n'y est pas disponible).
Le bon réflexe
Compte tes tables. Une seule table propre de taille raisonnable ? Reste sur un tableau croisé classique. 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.
Questions fréquentes sur Power Pivot
Un TCD classique analyse une seule table. Power Pivot ajoute un modèle de données qui relie plusieurs tables entre elles, puis branche un TCD dessus. Tu peux ainsi croiser des ventes, des produits et des dates issus de tables séparées, sans recopier de colonnes avec RECHERCHEX. Power Pivot gère aussi des volumes bien au-delà du million de lignes d'une feuille.
Oui, dans la plupart des versions. Va dans Fichier > Options > Compléments, choisis « Compléments COM » en bas, clique sur « Atteindre », puis coche « Microsoft Power Pivot pour Excel ». Un nouvel onglet « Power Pivot » apparaît dans le ruban. Le complément est inclus dans Excel 2013 et plus, ainsi que dans la plupart des abonnements Microsoft 365.
Non, ils sont complémentaires. Power Query importe et nettoie les données (le « avant »). Power Pivot relie les tables propres dans un modèle et calcule des mesures DAX (le « après »). Le pipeline complet : Power Query nettoie chaque source, charge dans le modèle, puis Power Pivot relie et un TCD analyse. Les deux travaillent ensemble.
Une mesure DAX est un calcul que tu définis une fois dans le modèle et que le TCD recalcule automatiquement selon le contexte (la ligne, la colonne, le filtre en cours). Par exemple une mesure « Chiffre d'affaires » écrite SUM(Ventes[Montant]) renvoie le total adapté à chaque cellule du TCD. Une mesure se réutilise partout, contrairement à une colonne calculée figée.
Non, pas pour commencer. Tu peux créer un modèle, relier des tables et bâtir un TCD sans écrire une seule mesure (Excel propose les sommes et moyennes automatiques). Le DAX devient utile quand tu veux des calculs sur mesure : un ratio, une part du total, une comparaison à l'an dernier. Tu apprends alors deux ou trois fonctions à la fois (SUM, CALCULATE), pas tout d'un coup.
Non, mais c'est le même moteur. Le modèle de données et le langage DAX de Power Pivot sont identiques à ceux de Power BI. Ce que tu apprends dans Excel se transpose directement. La différence : Power BI ajoute des visuels interactifs et un partage en ligne, là où Power Pivot reste dans le confort d'Excel et de ses tableaux croisés. C'est souvent une première marche vers Power BI.
Tu veux pratiquer Power Pivot et le DAX sur des cas réels ?
Le Dojo Club te donne accès à des exercices guidés, des lives experts chaque semaine et une communauté active pour progresser sur Excel, Power Pivot et Power BI.
Rejoindre le Dojo