Excel - Power Query - Power Pivot - VBA

EX16 | Construire un modèle de données Power Pivot

Document fictif à usage pédagogique.

Énoncé

Contexte

  • Entreprise : Brévalis Industries (fictive), début janvier 2027.
  • Demande : la direction veut suivre le chiffre d'affaires et le taux de marge par gamme et par année, avec la comparaison à l'année précédente et un zoom sur la Bretagne.
  • Démarche : plutôt que d'empiler les colonnes dans une seule table, relier la table des ventes aux référentiels et à une table de dates, puis écrire des mesures DAX.
  • Durée : 60 min | Niveau : intermédiaire confirmé.
  • Départ : EX14_ventes_consolidees.xlsx (Ventes : 6 056 lignes 2025-2026, tblProduit, tblClient) | Fin : EX16_modele.xlsx.
  • Prérequis poste : le complément Power Pivot est désactivé au départ.

Travail demandé

  • Activer le complément Power Pivot.
  • Ajouter Ventes, tblProduit et tblClient au modèle de données.
  • Créer une table de dates Calendrier.
  • Créer les 3 relations : Ventes vers tblProduit, tblClient et Calendrier.
  • Ajouter les colonnes calculées MontantHT et Marge dans Ventes.
  • Créer les mesures CA Total, Marge Totale, Taux de marge, CA Bretagne et CA N-1.
  • Construire un tableau croisé dynamique à partir du modèle : Gamme en lignes, Année en colonnes, CA Total et Taux de marge en valeurs.
  • Enregistrer sous EX16_modele.xlsx.

Simulation

💡
Simulation interactive EX16 (8 étapes guidées, fenêtre Power Pivot simulée, calcul DAX réel, validation automatique) | lien à ajouter

Corrigé

E00 | Ouvrir le fichier de départ

  • Fichier > Ouvrir > Parcourir > Documents\excel-lab\depart\EX14_ventes_consolidees.xlsx.
  • Si un bandeau Avertissement de sécurité s'affiche (connexions de données externes désactivées), cliquer sur Activer le contenu.
💡
Capture : boîte Ouvrir, dossier depart, fichier EX14_ventes_consolidees.xlsx sélectionné

E01 | Activer Power Pivot

  • Fichier > Options > Compléments.
  • En bas, Gérer : Compléments COM > Atteindre.
  • Cocher Microsoft Power Pivot for Excel > OK : l'onglet Power Pivot apparaît.
💡
Capture : boîte Compléments COM, case Microsoft Power Pivot for Excel

E02 | Ajouter au modèle de données

  • Volet Requêtes et connexions > clic droit sur Ventes > Charger dans... > cocher Ajouter ces données au modèle de données > OK.
  • tblProduit et tblClient : Charger dans... > Créer uniquement la connexion + Ajouter ces données au modèle de données.
  • Variante : feuille Ventes active > Power Pivot > Ajouter au modèle de données.
  • Pourquoi : le modèle stocke les tables dans le moteur de Power Pivot, compressées, sans la limite de 1 048 576 lignes d'une feuille.
💡
Capture : boîte Importer des données, case Ajouter ces données au modèle de données

E03 | Table de dates

  • Power Pivot > Gérer.
  • Fenêtre Power Pivot > Conception > Table de dates > Nouvelle.
  • Résultat : table Calendrier (Date, Année, Numéro du mois, Mois, etc.) couvrant les années de la table des ventes.
💡
Capture : ruban Conception de Power Pivot, menu Table de dates

E04 | Relations

  • Accueil > Vue de diagramme.
  • Glisser Ventes[RefProduit] sur tblProduit[RefProduit], Ventes[CodeClient] sur tblClient[CodeClient], Ventes[DateCommande] sur Calendrier[Date].
  • Variante : Conception > Créer une relation.
  • Chaque relation va du côté plusieurs (*, Ventes) vers le côté un (1, table de référence aux valeurs uniques).
💡
Capture : vue de diagramme avec les 4 tables et les 3 relations

E05 | Colonnes calculées

  • Accueil > Vue de données > onglet Ventes > colonne Ajouter une colonne.
  • Formule : =[Quantite]*[PrixUnitaireHT]*(1-[Remise]) > Entrée ; double-clic sur l'en-tête pour renommer en MontantHT.
  • Formule : =[MontantHT]-[Quantite]*RELATED(tblProduit[CoutRevient]) ; renommer en Marge.
  • RELATED va chercher le coût de revient dans tblProduit grâce à la relation sur RefProduit.
💡
Capture : vue de données, barre de formule avec RELATED

E06 | Mesures DAX

  • Excel > Power Pivot > Mesures > Nouvelle mesure, table Ventes.
    • CA Total = SUM(Ventes[MontantHT]), catégorie Devise.
    • Marge Totale = SUM(Ventes[Marge]), catégorie Devise.
    • Taux de marge = DIVIDE([Marge Totale],[CA Total]), catégorie Pourcentage.
    • CA Bretagne = CALCULATE([CA Total],tblClient[Region]="Bretagne").
    • CA N-1 = CALCULATE([CA Total],SAMEPERIODLASTYEAR(Calendrier[Date])).
  • Variante : saisir CA Total:=SUM(Ventes[MontantHT]) dans la zone de calcul de la fenêtre Power Pivot.
  • Colonne calculée ou mesure : la colonne est calculée ligne par ligne et stockée ; la mesure est calculée à la demande dans le contexte de filtre du tableau croisé dynamique.
💡
Capture : boîte Mesure, formule CALCULATE et catégorie

E07 | Tableau croisé dynamique

  • Insertion > flèche de Tableau croisé dynamique > À partir du modèle de données > Nouvelle feuille > OK.
  • Lignes : tblProduit > Gamme ; Colonnes : Calendrier > Année ; Valeurs : Ventes > CA Total et Taux de marge (icône fx).
  • Résultats attendus :
GammeCA Total 2025Taux 2025CA Total 2026Taux 2026
Pièces composites5 228 550,59 €37,59 %5 103 435,23 €37,07 %
Services et maintenance2 194 123,00 €35,22 %2 411 102,67 €34,79 %
Sous-ensembles3 356 240,06 €39,90 %3 735 352,49 €39,85 %
Usinage métal1 698 935,25 €34,23 %1 652 430,39 €34,07 %
(vide)51 158,24 €100,00 %
Total général12 529 007,14 €37,59 %12 902 320,78 €37,06 %
  • Ajouter CA N-1 : la colonne 2026 affiche le CA 2025 de chaque gamme (5 228 550,59 € pour Pièces composites).
  • Point de vigilance : la ligne (vide) correspond aux 12 ventes 2025 d'articles absents de tblProduit (EX13). Sans coût de revient, leur marge vaut 100 % : il faut compléter le référentiel.
💡
Capture : tableau croisé dynamique et liste des champs du modèle

E08 | Enregistrer

  • Fichier > Enregistrer sous > OneDrive > Brévalis > EX16_modele.xlsx.
💡
Capture : résultat final

Pour aller plus loin