Objectif
À la fin de cet atelier, vous saurez formuler à ChatGPT une demande de formule de recherche avancée, de macro VBA et de requête Power Query, et vous saurez construire une analyse simple (tableau récapitulatif, graphique, KPI) sur un jeu de données de ventes.
Télécharger le fichier de l’atelierexercice-chatgpt-excel.xlsx — 5 feuilles : Consignes, Ventes (à compléter), Produits, Corrigé et VBA & Power Query.
Avant de commencer
Ouvrez la feuille Ventes du classeur : la ligne 2 montre l’exemple de formule attendu, les lignes 3 à 31 ont les colonnes G à J en surbrillance jaune, à compléter. La feuille Corrigé permet de vous autocorriger à la fin.
Formule de recherche avancée
Récupérer désignation et prix depuis la feuille Produits
- Repérez la ligne 2 de la feuille Ventes : les colonnes G (Désignation), H (Prix unitaire HT), I (Montant HT) et J (Statut) y sont déjà calculées.
- Demandez à ChatGPT la formule à recopier vers le bas pour les colonnes G et H, en vous appuyant sur la feuille Produits.
- Complétez la colonne I (Montant HT = Quantité × Prix unitaire) et la colonne J (« Grand compte » si le montant est supérieur ou égal à 200 €, sinon « Standard »).
- Comparez votre résultat avec la feuille Corrigé.
Macro VBA
Automatiser la mise en évidence des grands comptes
- Objectif : une macro qui met en surbrillance toutes les lignes dont le Montant HT (colonne I) est supérieur ou égal à 200 €.
- Demandez le code à ChatGPT, puis enregistrez le classeur au format .xlsm pour pouvoir l’exécuter.
- Ouvrez l’éditeur VBA (Alt+F11 > Insertion > Module), collez le code, puis exécutez-le (F5).
- Comparez votre macro avec l’exemple fourni dans la feuille VBA & Power Query du classeur.
Requête Power Query
Fusionner Ventes et Produits, puis filtrer
- Objectif : fusionner la feuille Ventes et la feuille Produits en une seule requête, en ne conservant que la catégorie « Informatique ».
- Chargez les deux feuilles comme requêtes (Données > À partir d’un tableau).
- Fusionnez-les sur la colonne Référence produit / Référence, en jointure gauche.
- Filtrez sur la catégorie « Informatique », puis chargez le résultat dans une nouvelle feuille.
Analyse
Récapitulatif, graphique et KPI
- Une fois vos formules complétées, construisez un tableau récapitulant le Montant HT total par région (formule SUMIFS ou tableau croisé dynamique).
- Ajoutez un graphique en barres à partir de ce tableau.
- Calculez trois KPI : chiffre d’affaires total, panier moyen, nombre de commandes « Grand compte ».
- Comparez votre résultat avec le tableau, le graphique et les KPI déjà présents dans la feuille Corrigé.
En un coup d’œil
| Étape | Livrable | Feuille de référence |
|---|---|---|
| 1. Formule | Colonnes G à J complétées (Désignation, Prix, Montant, Statut) | Ventes / Corrigé |
| 2. VBA | Macro de surbrillance des grands comptes | VBA & Power Query |
| 3. Power Query | Requête fusionnée et filtrée sur « Informatique » | VBA & Power Query |
| 4. Analyse | Récapitulatif par région, graphique, 3 KPI | Corrigé |
J’ai réussi si…
- Toutes les commandes de la feuille Ventes ont une désignation, un prix, un montant et un statut corrects.
- Ma macro VBA met en surbrillance les commandes « Grand compte » sans erreur.
- Ma requête Power Query fusionne les deux tables et filtre correctement sur la catégorie.
- Mon tableau récapitulatif, mon graphique et mes KPI correspondent à ceux de la feuille Corrigé.
À retenir
Une même méthode (contexte précis + prompt structuré + vérification) permet de faire produire par ChatGPT une formule, une macro VBA et une requête Power Query. Le gain de temps est réel, à condition de toujours contrôler le résultat sur vos propres données avant de le généraliser.