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

Document fictif à usage pédagogique.

Énoncé

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).

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 interactive EX13 (8 étapes guidées, validation automatique) | lien à ajouter

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

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

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

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

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

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

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

💡

Capture : résultat final, feuille VentesEnrichies