Excel · Power Pivot · A3iformations

Power Pivot dans Excel : modèle de données, DAX, KPI et tableau de bord

Un tableau croisé dynamique classique lit une seule plage. Power Pivot, lui, charge plusieurs tables (ventes, clients, produits, calendrier), venues d’Excel, d’un CSV, d’Access, du Web ou d’une base SQL, les relie entre elles dans un modèle de données et calcule sur des millions de lignes avec le langage DAX. Cette page suit une démarche complète sur un même jeu de données de formations : activer l’outil, importer, préparer, relier, calculer, fixer des objectifs et construire un tableau de bord.

Sources multiplesImporter

Tables reliéesModèle

Colonnes et mesuresDAX

Objectifs, segmentsKPI et tableau de bord

Activer Power Pivot et découvrir l’interface

Power Pivot est inclus dans Excel pour Windows (Microsoft 365, Excel 2016 et suivants) mais doit être activé une fois. Il n’existe pas dans Excel pour Mac ni dans Excel pour le Web.

Fichier › Options › Compléments.
En bas, dans la liste Gérer, choisissez Compléments COM puis cliquez sur Atteindre.
Cochez Microsoft Power Pivot for Excel et validez : l’onglet Power Pivot apparaît dans le ruban.
Cliquez sur Power Pivot › Gérer pour ouvrir la fenêtre Power Pivot, où vivent les tables du modèle.

Simulateur : la fenêtre Power Pivot

Modèle de donnéesLes tables ne sont pas stockées dans des feuilles mais dans le modèle, compressé en mémoire : un classeur peut contenir des millions de lignes, bien au-delà du 1 048 576 d’une feuille.

Deux vuesLa vue de données montre le contenu des tables et la zone de calcul des mesures ; la vue de diagramme montre les tables et leurs relations (séquence 4).

Connexions et import de données

Une donnée interne est déjà dans le classeur (plage, tableau) : Power Pivot › Ajouter au modèle de données. Une donnée externe vient d’un fichier ou d’un serveur : Données › Obtenir des données (Power Query), en cochant Ajouter ces données au modèle de données dans la boîte Importer des données, ou dans la fenêtre Power Pivot : Accueil › Obtenir des données externes. Chaque source crée une connexion ; un modèle peut en réunir plusieurs, de bases différentes, et les actualiser ensemble.

Simulateur : alimenter le modèle depuis plusieurs sources

Tables du modèle

Connexions du classeur

Gérer les connexionsDonnées › Requêtes et connexions liste les requêtes et connexions ; Power Pivot › Gérer › Accueil › Connexions existantes permet de modifier une source (chemin, serveur, identifiants) sans refaire l’import.

Chemins et droitsUne connexion à un fichier garde son chemin complet : déplacez le fichier source et l’actualisation échoue. Pour une base partagée (SQL Server, Access sur le réseau), chaque utilisateur doit avoir les droits de lecture.

Nettoyer, combiner, fusionner et grouper

La préparation se fait dans Power Query avant le chargement dans le modèle (voir notre page importer et nettoyer des données). Trois opérations reviennent sans cesse : ajouter des requêtes (empiler des tables de même structure), fusionner des requêtes (joindre deux tables sur une colonne commune, comme une RECHERCHEX) et regrouper par (résumer une table). Elles sont dans Éditeur Power Query › Accueil › Combiner et Transformer › Regrouper par.

Simulateur : combiner les ventes des deux agences

Ajouter un dossier entierObtenir des données › À partir d’un fichier › À partir d’un dossier puis Combiner et transformer empile tous les CSV d’un dossier : déposez l’export du mois suivant, actualisez, il est intégré.

Fusionner ou relier ?Fusionner copie des colonnes d’une table dans l’autre. Dans un modèle Power Pivot, on préfère souvent garder les tables séparées et les relier (séquence 4) : moins de données dupliquées, un modèle plus léger.

Définir le modèle de données

Un bon modèle suit le schéma en étoile : une table de faits au centre (les ventes, une ligne par événement, avec des codes et des nombres) et des tables de dimensions autour (clients, formations, calendrier : une ligne par élément, avec des libellés). Chaque relation relie une colonne de la table de faits (côté plusieurs, *) à la clé unique d’une dimension (côté un, 1). Créez-les dans la vue de diagramme en faisant glisser une colonne sur l’autre, ou par Conception › Créer une relation.

Simulateur : relier les tables, puis tester un tableau croisé

Créer une relation

Table Colonne Table associée Colonne associée

TCD : lignesValeurs : Somme de Ventes › Heures

Le symptôme d’une relation manquanteDans le TCD, toutes les lignes affichent le même total et un bandeau jaune annonce « Des relations entre les tables peuvent être nécessaires ». Le champ de ligne vient d’une table qui ne filtre pas la table des valeurs.

Table de datesFenêtre Power Pivot › Conception › Table de dates › Nouveau crée une table Calendrier (Année, Mois, Jour de la semaine…) couvrant vos dates. Marquez-la comme table de dates pour les fonctions DAX de temps.

Les formules DAX

Le DAX (Data Analysis Expressions) s’écrit dans la fenêtre Power Pivot. Une colonne calculée est évaluée ligne par ligne et stockée dans la table (=Ventes[Heures]*RELATED(Formations[Tarif])). Une mesure est une agrégation calculée à la volée selon les filtres du TCD : on la saisit dans la zone de calcul (CA:=SUM(Ventes[Montant])) ou par Power Pivot › Mesures › Nouvelle mesure. Dans Excel en français, le séparateur d’arguments est le point-virgule ; les noms de fonctions restent en anglais.

Simulateur : une mesure, plusieurs contextes de filtre

Lignes du TCDSegment Année :

Fonction	Rôle
`SUM`, `AVERAGE`, `COUNTROWS`, `DISTINCTCOUNT`	Agrégations de base
`RELATED`	Aller chercher une valeur dans la table du côté « un » de la relation (équivalent d’une RECHERCHEX)
`SUMX`, `AVERAGEX`	Calculer ligne par ligne puis agréger, sans colonne calculée
`CALCULATE`	Évaluer une mesure en modifiant les filtres
`ALL`, `ALLEXCEPT`	Retirer des filtres (pour un pourcentage du total)
`DIVIDE`	Division sans erreur si le diviseur est nul
`SAMEPERIODLASTYEAR`, `TOTALYTD`	Intelligence temporelle (nécessite une table de dates)

Colonne ou mesure ?Colonne calculée : pour une valeur à placer en ligne, en colonne ou en segment (une tranche d’âge, un libellé). Mesure : pour tout ce qui s’additionne ou se compare dans la zone Valeurs. En cas de doute, une mesure.

Le piège du pourcentageALL(Formations) ne retire que les filtres venant de la table Formations. Placez les villes en lignes : chaque ville vaut 100 %, car le filtre Ville, lui, reste actif.

Les KPI

Un KPI (indicateur clé de performance) compare une mesure de base à une valeur cible (une autre mesure ou un nombre fixe) et affiche un état selon des seuils exprimés en pourcentage de la cible. Créez d’abord la mesure, puis Power Pivot › Indicateurs de performance clés › Nouvel indicateur de performance clé. Dans le TCD, le KPI apporte trois champs : Valeur, Objectif et État.

Simulateur : CA 2026 par ville face aux objectifs

Mesure de base[CA 2026]

Valeur cible : Mesure [Objectif] Valeur absolue

Seuil bas (%)Seuil haut (%)

Où ranger les objectifs ?Dans une petite table Objectifs (Ville, Objectif 2026) reliée à Clients[Ville], avec la mesure Objectif:=SUM(Objectifs[Objectif]). Placez Objectifs[Ville] en lignes du TCD : c’est elle qui filtre à la fois les objectifs et, par les relations, les ventes.

Modifier un KPIClic droit sur le KPI dans la liste des champs du TCD › Modifier l’indicateur de performance clé, ou Power Pivot › Indicateurs de performance clés › Gérer.

Transfert vers des TCD et graphiques, tableau de bord

Depuis la fenêtre Power Pivot : Accueil › Tableau croisé dynamique (TCD, graphique croisé, ou les deux). Depuis Excel : Insertion › Tableau croisé dynamique › À partir du modèle de données. La liste des champs affiche alors toutes les tables du modèle, les mesures (fx) et les KPI. Un tableau de bord réunit plusieurs TCD et graphiques pilotés par les mêmes segments et chronologies, reliés par Segment › Connexions de rapport.

Simulateur : le tableau de bord des formations

Année

Catégorie

Des formules plutôt qu’un TCDDans un TCD sur le modèle : Analyse du tableau croisé dynamique › Outils OLAP › Convertir en formules. Chaque cellule devient une formule VALEURCUBE que l’on peut déplacer librement dans une mise en page de tableau de bord.

Et Power BI ?Power BI Desktop utilise le même moteur, les mêmes relations et le même DAX : un modèle Power Pivot s’importe directement (Fichier › Importer › Contenu du classeur Power Query, Power Pivot, Power View).

Exercice de synthèse : le modèle de ventes des formations

Classeur d’exercice (Énoncé et Corrigé)

Activez Power Pivot, puis ajoutez au modèle les tableaux tVentes, tFormations, tClients et tObjectifs ; créez une table de dates.
Reliez tVentes à tClients (Code client), à tFormations (Code formation) et au Calendrier (Date), puis tClients[Ville] à tObjectifs[Ville].
Colonne calculée Montant dans tVentes, mesures CA, Nb sessions, % du CA, CA N-1, Objectif.
TCD CA par catégorie : Data 11 025 €, Tableur 7 105 €, Bureautique 4 480 €, total 22 610 € (2025 : 12 285 € ; 2026 : 10 325 €).
KPI CA 2026 face à l’objectif, seuils 80 % et 100 % : Arras et Lens au vert, Hesdin et Saint-Omer à l’orange, Béthune au rouge.
Tableau de bord : TCD, graphique croisé CA par ville, segments Année et Catégorie connectés aux deux.