Excel - Power Query - Power Pivot - VBA

EX13 | Enrichir les ventes avec les référentiels

Document fictif à usage pédagogique.

Produits.xlsx8.9 KB⬇
Clients.xlsx11 KB⬇
EX12_ventes.xlsx182 KB⬇

Énoncé

Contexte

  • Entreprise : Brévalis Industries (fictive).
  • Demande : la table Ventes2025 ne contient que des codes. La direction commerciale veut voir la gamme, la région du client, le chiffre d'affaires et la marge de chaque vente.
  • Durée : 40 min | Niveau : intermédiaire.
  • Départ : EX12_ventes.xlsx (requête Ventes2025) + Produits.xlsx (tableau tblProduit) + Clients.xlsx (tableau tblClient) | Fin : EX13_ventes_enrichies.xlsx (sert de départ à EX14).

Travail demandé

  • Charger tblProduit et tblClient en connexion uniquement.
  • Fusionner Ventes2025 et tblProduit dans une nouvelle requête, sur RefProduit, en jointure externe gauche.
  • Récupérer Designation, CodeGamme, Gamme et CoutRevient.
  • Ajouter Region et Secteur depuis tblClient (fusion sur CodeClient).
  • Calculer CA HT = [Quantite]*[PrixUnitaireHT]*(1-[Remise]) et Marge = [CA HT]-[Quantite]*[CoutRevient].
  • Repérer les ventes dont l'article est absent du référentiel, puis retirer ce filtre de contrôle.
  • Nommer la requête VentesEnrichies, la charger et enregistrer sous EX13_ventes_enrichies.xlsx.

Simulation

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

Corrigé

E00 | Ouvrir le fichier de départ

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

E01 | Référentiels en connexion uniquement

  • Données > Obtenir des données > À partir d'un fichier > À partir d'un classeur Excel > Documents\excel-lab\donnees\Produits.xlsx.
  • Navigateur : sélectionner tblProduit > flèche de Charger > Charger dans... > Créer uniquement la connexion.
  • Même démarche avec Clients.xlsx et tblClient.
  • Pourquoi : un référentiel sert aux fusions, il n'a pas besoin d'occuper une feuille.
💡
Capture : boîte Importer des données, option Créer uniquement la connexion

E02 | Fusionner comme nouvelle requête

  • Double-clic sur Ventes2025 dans le volet Requêtes et connexions pour ouvrir l'éditeur.
  • Accueil > flèche de Fusionner des requêtes > Fusionner des requêtes comme nouvelles.
  • Table du haut Ventes2025, table du bas tblProduit, colonne RefProduit cliquée dans les deux, Type de jointure Externe gauche.
  • Message attendu : 2 988 lignes sur 3 000 correspondent.
💡
Capture : boîte Fusionner, colonnes RefProduit sélectionnées, message de correspondance

E03 | Développer tblProduit

  • Icône de développement de l'en-tête tblProduit.
  • Cocher Designation, CodeGamme, Gamme, CoutRevient ; décocher Utiliser le nom de la colonne d'origine comme préfixe.
💡
Capture : liste de développement des colonnes

E04 | Ajouter le client

  • Accueil > Fusionner des requêtes (sur la requête en cours, pas comme nouvelle) > tblClient, CodeClient des deux côtés.
  • Développer Region et Secteur, sans préfixe.
💡
Capture : colonnes Region et Secteur ajoutées

E05 | CA HT et marge

  • Ajouter une colonne > Colonne personnalisée : nom CA HT, formule [Quantite]*[PrixUnitaireHT]*(1-[Remise]).
  • Nouvelle colonne personnalisée : nom Marge, formule [CA HT]-[Quantite]*[CoutRevient].
  • Astuce : double-cliquer sur les colonnes disponibles pour les insérer dans la formule sans faute de frappe.
💡
Capture : boîte Colonne personnalisée avec la formule CA HT

E06 | Articles absents du référentiel

  • Filtre de Designation : décocher (Sélectionner tout), cocher (null).
  • Résultat : 12 lignes, articles ART-0041, ART-0042 et ART-0043, absents de tblProduit.
  • Supprimer l'étape Lignes filtrées (croix dans Étapes appliquées) : retour à 3 000 lignes.
  • Point de vigilance : la jointure externe gauche conserve ces ventes, mais leur coût de revient est vide ; leur marge sera fausse tant que le référentiel n'est pas complété. Une jointure interne les aurait fait disparaître sans alerte.
💡
Capture : filtre (null) appliqué, 12 lignes visibles

E07 | Nommer et charger

  • Nom : VentesEnrichies.
  • Accueil > Fermer et charger : 3 000 lignes ; tblProduit et tblClient restent en connexion uniquement.
💡
Capture : volet Requêtes et connexions avec les quatre requêtes

E08 | Enregistrer

  • Fichier > Enregistrer sous > OneDrive > Brévalis > EX13_ventes_enrichies.xlsx.
💡
Capture : résultat final, feuille VentesEnrichies

Pour aller plus loin

  • Vue d'ensemble des fusions : Source
  • Jointure externe gauche : Source
  • Colonne personnalisée : Source

Simulation