Excel séance 4 : le cas de synthèse, l’IA nettoie, vous analysez

Un fichier jamais vu, que l’assistant nettoie et que vous analysez, jusqu’à la note d’une page

À lire une fois, avant le premier atelier

Ce guide se suit comme ceux des séances 2 et 3 : les mots entre guillemets sont les libellés que vous cherchez à l’écran, le mot « puis » sépare deux clics, les étapes sont écrites pour Excel sur Windows, et chaque guide se termine par ce qui change sur Excel pour Mac et sur Google Sheets, par ce qu’il faut faire si ça ne marche pas, et par le chiffre qui prouve que c’est réussi.

Aujourd’hui, le fichier est sale et vous ne le nettoyez pas vous-même. Vous l’envoyez à l’assistant, avec un prompt qui exige un journal, et vous contrôlez ce journal ligne par ligne. C’est ce qu’un analyste fait en entreprise depuis deux ans : le nettoyage se délègue, le contrôle et l’analyse ne se délèguent pas.

Le fichier de repli. marepice-ventes-propre.xlsx est exactement ce que l’assistant doit vous rendre, journal compris. Si votre assistant refuse la pièce jointe, ne rend pas de fichier, ou rend des chiffres qui ne retombent pas sur ceux du guide 4A, ouvrez le repli et continuez avec lui : personne ne reste bloqué à l’atelier 1.

L’assistant sert quatre fois. Pour auditer le fichier reçu, pour le nettoyer avec journal, pour la formule qui compte les clients distincts, et pour écrire sa propre note, que vous confronterez à la vôtre. Les prompts sont dans la bibliothèque remise à la séance 2, partie 3, et ceux du jour sont recopiés à la fin de ce guide.

Ce que vous devez obtenir est écrit. Chaque guide se termine par « Comment savoir que c’est réussi », avec les chiffres exacts. Tant que vous n’obtenez pas ces chiffres, ce n’est pas fini ; quand vous les obtenez, vous n’avez pas besoin de demander si c’est bon.

Le mandat

Kertavel vient de racheter Marépice, un grossiste en épicerie fine pour la restauration : truffes, huiles, épices, conserves, chocolats. Six villes du Gard et de l’Hérault, vingt-quatre clients, quatre commerciaux.

Votre direction commerciale vous transmet l’export des ventes des deux derniers exercices et vous pose deux questions, dans cet ordre :

1. Faut-il conserver les quatre commerciaux de Marépice ?
2. Sur quelles familles de produits concentrer l’effort commercial ?

Elle attend une note d’une page, appuyée sur des chiffres, et le tableau de bord qui la soutient, remis en fin de séance. Elle ne veut pas de classeur en pièce jointe : elle veut une réponse, et de quoi la vérifier.

Ce fichier n’est pas celui des trois premières séances. Les colonnes ne portent pas les mêmes noms, les défauts ne sont pas aux mêmes endroits, et personne ne vous dira lesquels chercher. C’est l’exercice : vous avez appris des gestes, on regarde aujourd’hui s’ils vous suivent sur un fichier que vous n’avez jamais ouvert.

Comment ce travail est regardé. Aucun barème n’existe sur ce module et vous ne recevrez pas de note. Vous recevrez, en fin de séance, la liste des repères du guide 4C cochés ou non, et le commentaire qui va avec. C’est la même exigence qu’en entreprise : personne ne notera votre note, mais votre direction verra tout de suite si le chiffre est juste, si la conclusion tient, et si vous avez dit ce que vous ne saviez pas.

Guide 4A. Faire nettoyer, contrôler le journal

À quoi ça sert. À obtenir en dix minutes un fichier propre dont vous savez exactement ce qui lui a été fait, au lieu d’y passer la matinée. Ce que vous apprenez ici n’est pas le nettoyage, c’est le contrôle.

Ce qu’il vous faut. marepice-ventes.xlsx, votre assistant ouvert dans un autre onglet, en compte gratuit, et la bibliothèque de prompts.

A. Regarder le fichier reçu, deux minutes

1. Ouvrez marepice-ventes.xlsx. Deux onglets en bas de la fenêtre : « Ventes » et « Catalogue ».
2. Sur « Ventes », regardez la colonne B « Date pièce » et la colonne H « PU HT » : les valeurs sont collées à gauche de leur cellule. Un tableur aligne les nombres et les dates à droite, le texte à gauche. Ces deux colonnes sont donc du texte, et du texte ne s’additionne pas et ne se groupe pas par mois.
3. Cliquez sur la cellule A1, puis « Ctrl » et « Flèche bas » : le curseur s’arrête à la ligne 1833, soit 1 832 lignes reçues. Notez ce nombre.

B. L’audit express, sans rien corriger

1. Dans l’assistant, glissez le fichier marepice-ventes.xlsx dans la zone de saisie. Ne copiez pas les données dans la fenêtre : un fichier joint est réellement ouvert et compté, du texte collé est relu de mémoire.
2. Collez le prompt 3.1 de la bibliothèque, « L’audit express », tel quel. Envoyez.
3. Lisez le tableau rendu. Il doit nommer au moins : des doublons, des dates en texte, des prix en texte avec « EUR », des commerciaux, des villes et des statuts écrits de plusieurs façons, des quantités vides, des références absentes du catalogue. S’il manque une famille de défauts, demandez : « Tu n’as pas regardé la colonne Réf. article contre l’onglet Catalogue. Refais l’inventaire. »

C. Le nettoyage, avec son journal

1. Dans la même conversation, collez le prompt de nettoyage de Marépice, recopié dans la partie « Demander à l’IA » de ce guide. Il reprend le prompt 3.2 et demande trois colonnes de plus : « Famille », « Montant net », « Exercice ».
2. L’assistant rend un fichier .xlsx à télécharger. Enregistrez-le sous marepice-ventes-propre-AB.xlsx, avec vos initiales, dans le même dossier.
3. Ouvrez-le. Trois onglets au moins : « Ventes », « Journal », « À vérifier ». Cliquez sur « Journal » en premier.

D. Le contrôle, ligne par ligne

1. Sur « Journal », cherchez trois nombres : les doublons supprimés, 17 ; les lignes conservées, 1 815 ; les lignes signalées sans être corrigées, 20, onze quantités vides et neuf références absentes du catalogue.
2. Sur « Ventes », cliquez A1 puis « Ctrl » et « Flèche bas » : ligne 1816. Regardez les colonnes B et H : les valeurs sont maintenant collées à droite.
3. Cliquez sur la lettre L, en tête de la colonne « Montant net ». En bas de la fenêtre, la barre d’état affiche « Somme : 361 436,72 € ». Si elle n’affiche qu’un « Nombre », la colonne est restée en texte : reprenez à l’étape 7 en ajoutant « les montants doivent être des nombres, pas du texte ».
4. Si l’un des trois nombres de l’étape 10 ou la somme de l’étape 12 diffère, ouvrez marepice-ventes-propre.xlsx, lisez son Journal, et continuez la séance avec lui. Notez l’écart : il ira au point 1 de votre note, « ce que vous avez reçu et ce que vous en avez retiré ».

Et dans les autres tableurs

Si ça ne marche pas

Comment savoir que c’est réussi. Un fichier à trois onglets au moins. Le Journal annonce 17 doublons supprimés, 1 815 lignes conservées sur 1 832, 20 lignes signalées. Sur « Ventes », la dernière ligne est la 1816 et la somme de la colonne L « Montant net » vaut 361 436,72 €. Les colonnes K « Famille », L « Montant net » et M « Exercice » existent.

Atelier 1, faire nettoyer le fichier, et le contrôler

Sur votre fichier, en autonomie, guide 4A du début à la fin : l’audit express, le nettoyage avec journal, puis le contrôle des quatre nombres. Écrivez sur une feuille, en trois lignes, ce que le fichier reçu avait comme défauts, ce que l’assistant a corrigé, et ce qu’il a signalé sans corriger : c’est le premier paragraphe de votre note de fin de séance, et vous ne le réécrirez pas.

Comment savoir que c’est réussi : les quatre nombres du guide 4A, et vos trois lignes écrites.

Version courte si le temps manque : ouvrez directement marepice-ventes-propre.xlsx, lisez son Journal, et écrivez vos trois lignes à partir de lui.

Activité bonus : demandez le contre-calcul, prompt 3.5, sur le total de 361 436,72 €. L’assistant doit le retrouver par une autre méthode, ou montrer les lignes de l’écart.

Guide 4B. Les quatre questions, par tableau croisé

À quoi ça sert. À produire, sur un fichier propre, les quatre chiffres dont la note a besoin, chacun par un tableau croisé que vous savez déjà construire. Le guide 2B de la séance 2 donne le geste de zéro ; celui-ci donne seulement la recette de chaque question.

Ce qu’il vous faut. Votre fichier propre, ou le repli. Le geste de base : cliquez A1 sur « Ventes », « Insertion » puis « Tableau croisé dynamique », « Nouvelle feuille de calcul », « OK ». Puis vous glissez les champs dans les quatre zones, et vous nommez la feuille.

A. Question 1, le chiffre d’affaires net et la part des annulées

1. « Statut » dans « Lignes », « Montant net » dans « Valeurs ». Aucun filtre : les annulées sont ce qu’on mesure.
2. Trois lignes et un total : « Annulée » 22 558,38 €, « Total général » 361 436,72 €. Le chiffre d’affaires net est le total moins les annulées : 338 878,34 €. Les annulées pèsent 6,2 % du brut, et personne ne vous l’a demandé : une bonne note le signale.
3. Le contre-calcul, pour prouver le croisé par une formule, comme au guide 2D : dans une cellule libre de « Ventes », =SOMME.SI.ENS($L$2:$L$1816;$J$2:$J$1816;"Annulée") renvoie 22 558,38 €. Si le croisé et la formule ne tombent pas sur le même chiffre, l’un des deux a un filtre ou une plage de travers.

B. Question 2, la répartition par famille

1. Nouvelle feuille, nouveau croisé. « Famille » dans « Lignes », « Montant net » dans « Valeurs », « Statut » dans « Filtres » : cliquez sa flèche, « Sélectionner plusieurs éléments », décochez « Annulée », « OK ».
2. Clic droit sur un montant, « Trier », « Du plus grand au plus petit ». Total général : 338 878,34 €. Truffes et champignons en tête.
3. Pour la part : glissez « Montant net » une seconde fois dans « Valeurs », clic droit sur la nouvelle colonne, « Afficher les valeurs », « % du total général ».
4. La ligne « Référence inconnue » est là : neuf lignes, 1 431,10 €. Laissez-la visible. La faire disparaître sans le dire n’est pas une option.

C. Question 3, chaque commercial, et combien de clients il suit

1. Nouvelle feuille, nouveau croisé. « Commercial » dans « Lignes », « Montant net » dans « Valeurs », « Statut » dans « Filtres » sans « Annulée ». Triez du plus grand au plus petit.
2. Le nombre de clients ne se lit pas dans un compte de lignes : une commande fait plusieurs lignes, et un client passe plusieurs commandes. Glissez « Client » dans « Lignes », sous « Commercial ». Chaque commercial se déplie sur ses clients : comptez les lignes sous chacun. Brenier Paul 7, Ouhadi Samia 7, Lefranc Marc 7, Vasseur Élise 3.
3. Sur une feuille à côté, écrivez les quatre montants, les quatre nombres de clients, et le montant par client : le montant divisé par le nombre de clients. C’est ce tableau qui répond à la première question de la direction, et le guide 4C dit comment le lire.

D. Question 4, ce qui monte et ce qui descend

1. Nouvelle feuille, nouveau croisé. « Famille » dans « Lignes », « Exercice » dans « Colonnes », « Montant net » dans « Valeurs », « Statut » dans « Filtres » sans « Annulée ». Deux colonnes apparaissent, « 2024-2025 » et « 2025-2026 », puis le total.
2. Dans la première cellule libre à droite du tableau, sur la ligne de la première famille, tapez la formule d’évolution : =(C5-B5)/B5 si vos montants sont en B et C et votre première famille en ligne 5. Entrée, puis recopiez vers le bas, et « Accueil », bouton « % ». Les références s’adaptent aux lettres et aux numéros de votre croisé.
3. Conserves et terrines : -31,5 %. Truffes et champignons : +20,0 %.

Et dans les autres tableurs

Si ça ne marche pas

Comment savoir que c’est réussi. Quatre croisés. Question 1 : 361 436,72 € au total, dont 22 558,38 € annulées, soit 338 878,34 € net. Question 2 : Truffes et champignons 140 242,79 €, 41,4 %. Question 3 : quatre commerciaux, 7, 7, 7 et 3 clients, 24 au total. Question 4 : Conserves et terrines -31,5 %, Truffes et champignons +20,0 %.

Atelier 2, les quatre questions de la direction

Sur votre fichier, en autonomie, guide 4B. Quatre tableaux croisés, un par question, chacun sur sa feuille nommée, et pour chacun le chiffre du guide retrouvé. Puis, sur votre feuille, le tableau des commerciaux à trois colonnes : montant, clients, montant par client.

1. Le chiffre d’affaires net des deux exercices, et la part des commandes annulées.
2. La répartition par famille de produits.
3. Ce que fait chaque commercial, et combien de clients il suit.
4. Ce qui monte et ce qui descend d’un exercice à l’autre.

Comment savoir que c’est réussi : les huit chiffres de l’encadré du guide 4B, et votre tableau des commerciaux qui affiche 20 440,27 € par client pour Vasseur Élise.

Version courte si le temps manque : les questions 1, 2 et 3. La quatrième se reprend en autonomie.

Activité bonus : posez la question 2 à l’assistant, prompt 3.3, sur le fichier propre, et comparez ses chiffres aux vôtres. Un écart a toujours une cause : les annulées, ou les références inconnues.

Guide 4C. La note d’une page, et le tableau de bord

À quoi ça sert. À rendre ce que la direction a demandé : une réponse qu’elle peut vérifier, pas un classeur.

Ce qu’il vous faut. Vos quatre croisés, votre tableau des commerciaux, et votre tableau de bord de la séance 3 comme modèle.

A. Lire le classement avant d’écrire

1. Votre croisé de la question 3 classe les commerciaux : Brenier Paul 98 645,24 €, Ouhadi Samia 91 474,31 €, Lefranc Marc 87 437,99 €, Vasseur Élise 61 320,81 €. Ce classement répond à « qui fait le plus ». Ce n’est pas la question posée.
2. Rapportez chaque montant à son nombre de clients : 14 092,18 €, 13 067,76 €, 12 491,14 € et 20 440,27 €. Le dernier au total est le premier par client, de 45 %.
3. Ce que le fichier ne dit pas, et que la note doit écrire : depuis quand chacun est en poste, si ses clients sont comparables, et pourquoi un portefeuille est trois fois plus petit que les autres. Un chiffre d’affaires ne se lit pas seul. Rapporté à un effectif, à une période, à un potentiel, il change de sens.

B. La note, cinq points dans cet ordre

1. Ce que vous avez reçu, et ce que vous en avez retiré : 1 832 lignes, 17 doublons, 1 815 conservées, 20 lignes signalées, et ce que l’assistant a fait ou n’a pas fait.
2. Les chiffres de cadrage : 338 878,34 € net sur deux exercices, 6,2 % d’annulées.
3. La réponse sur les commerciaux, avec sa réserve : le classement brut, le classement par client, et ce qui manque pour trancher.
4. La réponse sur les familles : les truffes à 41,4 % et +20,0 %, les conserves à -31,5 %, et le fait qu’on ne sait pas pourquoi.
5. Ce que vous ne savez pas, et ce qu’il faudrait pour le savoir. Trois lignes suffisent, et elles protègent tout le reste.

C. Le tableau de bord, une page

1. Reprenez la structure de la séance 3 : la ligne de périmètre en tête, « Deux exercices, juillet 2024 à juin 2026, hors commandes annulées, 1 815 lignes » ; trois indicateurs, chiffre d’affaires net, part des annulées, nombre de clients ; le tableau des commerciaux à trois colonnes ; deux graphiques, les familles et l’évolution par famille. Trois ou quatre visuels, pas huit.
2. Chaque titre de graphique énonce un constat. « Les conserves perdent un tiers en un exercice » est un titre ; « Évolution par famille » est une légende.

Et dans les autres tableurs

Si ça ne marche pas

Comment savoir que c’est réussi. Une note d’une page qui suit les cinq points, où chaque chiffre porte son périmètre et se retrouve dans un de vos croisés, qui dit que Vasseur Élise est première par client, et qui écrit ce qu’elle ne sait pas. Un tableau de bord d’une page, périmètre en tête, avec le tableau des commerciaux à trois colonnes.

Atelier 3, la note et le tableau de bord

En binôme. Vous produisez ce que la direction a demandé : la note d’une page qui répond aux deux questions, puis le tableau de bord d’une page qui la soutient. La note d’abord : un tableau de bord sans conclusion écrite ne sert qu’à celui qui l’a fait.

Vos repères de contrôle, ce sont ceux qui seront cochés :

Comment savoir que c’est réussi : les sept repères cochés par vous-mêmes, cinq minutes avant la fin, puis votre note passée à un autre binôme, qui la lit au crayon et la rend sans commentaire oral.

Version courte si le temps manque : la note seule, avec un seul visuel. Une conclusion écrite sans tableau de bord reste défendable ; l’inverse, non.

Activité bonus : un bloc « ce qu’il faudrait demander à Marépice » sur le tableau de bord, trois questions, une par limite de la note.

Guide 4D. La note de l’assistant contre la vôtre

À quoi ça sert. À apprendre à lire une note produite par une IA comme votre direction lira la vôtre : avec un crayon, en cherchant ce qui ne tient pas.

Ce qu’il vous faut. Votre fichier propre, votre note terminée, et le prompt 3.4 de la bibliothèque.

A. Obtenir la note de l’assistant

1. Dans l’assistant, glissez votre fichier propre. Collez le prompt 3.4, « La note pour la direction », en ajoutant à la fin les deux questions du mandat, mot pour mot.
2. Vous recevez une page. Ne la lisez pas encore comme un texte : lisez-la chiffre par chiffre.

B. Trois écarts à trouver

1. Un chiffre faux : un montant qui ne retombe pas sur votre croisé. Le plus fréquent : un total calculé sur les 1 815 lignes, annulées comprises, 361 436,72 € présenté comme le chiffre d’affaires.
2. Un chiffre invérifiable : un pourcentage ou une évolution dont la note ne dit ni la période ni le périmètre. « Les ventes progressent de 3 % » ne se vérifie pas tant qu’on ne sait pas de quoi à quoi.
3. Une conclusion non déduite : une affirmation que les données ne portent pas. Se séparer d’un commercial sur le classement brut, expliquer la chute des conserves par une cause que le fichier ne contient pas, qualifier la « performance » d’une équipe sur deux exercices.
4. Écrivez les trois, chacun avec le chiffre de votre croisé qui le prouve.

Et dans les autres tableurs

Si ça ne marche pas

Comment savoir que c’est réussi. Trois écarts écrits, chacun avec le chiffre qui le prouve, tirés de vos propres croisés. Et une phrase : ce que la note de l’assistant fait mieux que la vôtre, s’il y a quelque chose.

Atelier 4, la note de l’assistant contre la vôtre

En binôme, guide 4D. La note de l’assistant sur votre fichier propre, puis trois écarts, un chiffre faux, un chiffre invérifiable, une conclusion non déduite, chacun prouvé par un de vos croisés.

Comment savoir que c’est réussi : les trois écarts écrits avec leur preuve.

Version courte si le temps manque : un seul écart, le chiffre faux, avec sa preuve.

Demander à l’IA

Les prompts du jour. Ils supposent que vous joignez le fichier à la conversation, comme le dit la partie 3 de la bibliothèque : les fichiers de Marépice sont fictifs, vous pouvez les envoyer entiers.

Le nettoyage de Marépice, avec journal, guide 4A. C’est le prompt 3.2 avec les trois colonnes en plus :

Nettoie l’onglet « Ventes » du fichier joint, dans cet ordre :

1. supprime les doublons exacts sur l’ensemble des colonnes
2. supprime les espaces en début et en fin de toutes les cellules texte
3. uniformise l’écriture des colonnes « Commercial », « Ville » et « Statut » : une seule écriture par valeur, en gardant la forme la plus fréquente
4. convertis la colonne « Date pièce » en dates et la colonne « PU HT » en nombres, en retirant le suffixe « EUR »
5. signale, sans les supprimer, les lignes dont la quantité est vide et les lignes dont la référence article est absente de l’onglet « Catalogue »

Ajoute ensuite trois colonnes à droite : « Famille », prise dans l’onglet « Catalogue » par la référence article, avec « Référence inconnue » quand elle n’y est pas ; « Montant net », égal à Qté × PU HT × (1 − Remise) ; « Exercice », qui vaut « 2024-2025 » pour les dates jusqu’au 30 juin 2025 et « 2025-2026 » ensuite.

Rends-moi un fichier .xlsx à trois onglets : « Ventes » nettoyé avec les noms de colonnes d’origine, « Journal » qui liste chaque opération avec le nombre de lignes touchées, et « À vérifier » avec les lignes signalées et la raison. Ne modifie aucune autre donnée, ne supprime aucune ligne autre que les doublons exacts, et indique le nombre de lignes reçues et conservées.

Attendu : 17 doublons, 1 815 lignes conservées, 20 lignes dans « À vérifier », et une somme de « Montant net » à 361 436,72 €.

Les clients distincts par commercial, guide 4B, si vous préférez une formule au croisé déplié :

Je travaille dans [Excel pour Windows / Excel pour Mac / Google Sheets], en français, avec le point-virgule comme séparateur d’arguments.

Ma feuille « Ventes » va de la ligne 2 à la ligne 1816. Colonnes : A Réf. pièce, B Date pièce, C Client, D Ville, E Commercial, F Réf. article, G Qté, H PU HT, I Remise, J Statut, K Famille, L Montant net, M Exercice.

Écris-moi la formule qui compte le nombre de clients distincts de la colonne C dont le commercial en E est « Vasseur Élise », hors lignes dont le statut en J est « Annulée ». Donne d’abord la formule seule, prête à coller, avec les plages figées par des $. Si une fonction n’existe pas dans mon tableur, propose l’équivalent.

Attendu : 3. Sur Google Sheets, une fonction COUNTUNIQUE avec FILTER ; sur Excel 365, NBVAL avec UNIQUE et FILTRE ; sur un Excel plus ancien, l’assistant proposera un tableau croisé, et c’est le geste du guide 4B.

La note de l’assistant, guide 4D : le prompt 3.4 tel quel, suivi des deux questions du mandat.

Mémo de la séance

Faire nettoyer : le fichier joint, pas collé ; l’audit express avant le nettoyage ; un journal exigé, et lu en premier. Trois nombres à contrôler : doublons, lignes conservées, lignes signalées.

Le repli : marepice-ventes-propre.xlsx est ce que l’assistant doit rendre. Un écart entre les deux se tranche par le journal, et s’écrit dans la note.

Quatre questions, quatre croisés : le statut sans filtre pour mesurer les annulées ; le filtre « Statut » sans « Annulée » pour tout le reste ; « Client » sous « Commercial » pour compter des clients ; « Exercice » en colonnes et l’évolution calculée à côté.

Un chiffre ne se lit pas seul : rapporté au nombre de clients, le classement de Marépice s’inverse. Un classement répond à « qui fait le plus » ; la direction pose une question de décision.

La note : cinq points, le dernier dit ce qu’on ne sait pas. Le tableau de bord : la structure de la séance 3, trois ou quatre visuels.

La note de l’assistant se lit au crayon : un chiffre faux, un chiffre sans périmètre, une conclusion de trop. La question qui vaut pour tout chiffre, d’où qu’il vienne : comment sais-tu que c’est juste ?