Excel - Power Query - Power Pivot - VBA

EX15 | Comparer le budget 2026 au réalisé 2025

Document fictif à usage pédagogique.

EX14_ventes_consolidees_EX15-EX16.xlsx808 KB⬇
Budget_2026.xlsx7.0 KB⬇

Énoncé

Contexte

  • Entreprise : Brévalis Industries (fictive), début janvier 2027.
  • Demande : le contrôle de gestion a saisi le budget 2026 avec un mois par colonne et une ligne par gamme et par région. Pratique à lire, impossible à analyser ou à comparer au réalisé.
  • Durée : 50 min | Niveau : intermédiaire confirmé.
  • Départ : EX14_ventes_consolidees_EX15-EX16.xlsx (corrigé EX14 avec décembre, 6 056 lignes) + Budget_2026.xlsx (feuille Budget) | Fin : EX15_budget.xlsx.

Données de départ (extrait)

CodeGammeGammeRegionjanvierfévrier...décembre
P1Pièces compositesBretagne156 800144 900...100 600
P1Pièces compositesPays de la Loire113 300105 900...118 700
  • 16 lignes (4 gammes x 4 régions : Bretagne, Pays de la Loire, Normandie, Île-de-France) et 12 colonnes de mois.

Travail demandé

  • Importer la feuille Budget dans Power Query.
  • Supprimer le libellé Gamme, dépivoter les mois et regrouper par mois et par gamme : 48 lignes.
  • Préparer le réalisé 2025 par mois et par gamme à partir de VentesEnrichies (requête Realise2025, en connexion uniquement).
  • Rapprocher les deux requêtes sur deux colonnes, calculer l'écart et une tendance Hausse / Baisse.
  • Relire le code M dans l'Éditeur avancé.
  • Charger Budget2026 et enregistrer sous EX15_budget.xlsx.

Simulation

💡
Simulation interactive EX15 (8 étapes guidées, validation automatique) | lien à ajouter

Corrigé

E00 | Ouvrir le fichier de départ

  • Fichier > Ouvrir > Parcourir > Documents\excel-lab\depart\EX14_ventes_consolidees_EX15-EX16.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_EX15-EX16.xlsx sélectionné

E01 | Importer le budget

  • Données > Obtenir des données > À partir d'un fichier > À partir d'un classeur Excel > Documents\excel-lab\donnees\Budget_2026.xlsx.
  • Navigateur : feuille Budget > Transformer les données.
💡
Capture : Navigateur, feuille Budget sélectionnée et aperçu

E02 | Dépivoter

  • Sélectionner Gamme > Supprimer les colonnes.
  • Cliquer sur CodeGamme, Ctrl+clic sur Region > Transformer > flèche de Dépivoter les colonnes > Dépivoter les autres colonnes.
  • Résultat : 16 x 12 = 192 lignes, colonnes Attribut et Valeur.
  • Pourquoi « les autres colonnes » : le code M cite CodeGamme et Region, pas les mois ; la requête reste valable si une colonne de mois est ajoutée.
💡
Capture : menu Dépivoter les colonnes ouvert

E03 | Renommer et regrouper

  • Renommer Attribut en Mois et Valeur en Budget.
  • Accueil > Regrouper par > Avancé : regroupements Mois et CodeGamme ; nouvelle colonne Budget, opération Somme, colonne Budget.
  • Nom de la requête : Budget2026.
  • Résultat : 12 x 4 = 48 lignes.
💡
Capture : boîte Regrouper par en mode Avancé

E04 | Réalisé 2025

  • Clic droit sur VentesEnrichies > Référence ; renommer la nouvelle requête Realise2025.
  • Sélectionner DateCommande > Ajouter une colonne > Date > Mois > Nom du mois.
  • Filtre de CodeGamme : décocher (null) (les 12 ventes sans gamme repérées en EX13).
  • Regrouper par (Avancé) : Nom du mois et CodeGamme ; CA2025 = Somme de CA HT. Résultat : 48 lignes.
  • Clic droit sur Realise2025 > décocher Activer le chargement.
  • Point de vigilance : Nom du mois renvoie « janvier » en minuscules avec les paramètres régionaux français, comme les en-têtes du budget ; la fusion de l'étape suivante compare le texte exact, une majuscule de différence suffit à faire échouer la correspondance.
💡
Capture : menu Date > Mois > Nom du mois

E05 | Rapprocher budget et réalisé

  • Sélectionner Budget2026 > Accueil > Fusionner des requêtes > Realise2025.
  • En haut : clic sur Mois puis Ctrl+clic sur CodeGamme ; en bas, dans le même ordre : Nom du mois puis Ctrl+clic sur CodeGamme.
  • Message attendu : 48 lignes sur 48 correspondent.
  • Développer Realise2025 : CA2025 seule, sans préfixe.
💡
Capture : boîte Fusionner avec les numéros 1 et 2 sur les colonnes clés

E06 | Écart et tendance

  • Colonne personnalisée : Ecart = [Budget]-[CA2025].
  • Ajouter une colonne > Colonne conditionnelle : nom Tendance ; Si Ecart est supérieur ou égal à 0 Alors Hausse ; Sinon Baisse.
  • Résultat : 39 lignes Hausse et 9 lignes Baisse.
💡
Capture : boîte Ajouter une colonne conditionnelle

E07 | Éditeur avancé

  • Requête Budget2026 sélectionnée > Accueil > Éditeur avancé.
  • Repérer let ... in, une ligne par étape appliquée, Table.UnpivotOtherColumns et Table.Group.
💡
Capture : fenêtre Éditeur avancé avec le code M de Budget2026

E08 | Charger et enregistrer

  • Fermer l'Éditeur avancé > Accueil > Fermer et charger : Budget2026 chargée (48 lignes), Realise2025 en connexion uniquement.
  • Fichier > Enregistrer sous > OneDrive > Brévalis > EX15_budget.xlsx.
💡
Capture : résultat final, table Budget2026 avec colonnes Mois, CodeGamme, Budget, CA2025, Ecart, Tendance

Pour aller plus loin

  • Dépivoter des colonnes : Source
  • Regrouper des lignes : Source
  • Colonne conditionnelle : Source
  • Fusionner des requêtes : Source