Excel séance 1 : lire un fichier, et le faire nettoyer

Ouvrir un export, savoir en dix minutes s’il est utilisable, et le faire nettoyer avec un journal

À lire une fois, avant le premier atelier

Ce guide est écrit pour être suivi sans rien savoir à l’avance. Si vous êtes à l’aise avec un tableur, ne lisez pas les guides : allez directement à la consigne de chaque atelier. Si vous ne l’êtes pas, suivez-les ligne par ligne, ils fonctionnent.

Une convention pour tout le document : 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 ; 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.

Le fichier est sale, et vous ne le nettoyez pas vous-même. Il ressemble à ce qu’un logiciel de gestion exporte réellement : des lignes en double, une région écrite de dix-neuf façons, des nombres stockés en texte, des noms manquants. Vous apprenez aujourd’hui à le lire, à le faire auditer et nettoyer par un assistant, et à contrôler ce qu’il a fait. Deux gestes seulement se font à la main, parce qu’ils sont courts et qu’on les refera toute sa vie.

Un assistant à côté du tableur. Ouvrez, dans un autre onglet du navigateur, l’assistant que vous utilisez déjà, Claude, ChatGPT, Gemini ou Copilot, en compte gratuit. Les prompts sont dans la bibliothèque remise avec ce guide. Le fichier de Kertavel est fictif : vous pouvez l’envoyer entier. Quand vous êtes bloqué, demandez le chemin clic par clic à l’assistant avant de lever la main : il connaît votre tableur mieux que ce guide.

Le fichier de repli. kertavel-ventes-propre.xlsx est exactement ce que l’assistant doit vous rendre, avec son onglet Journal. Si votre assistant refuse la pièce jointe ou rend des chiffres qui ne retombent pas sur ceux du guide 1C, ouvrez le repli et continuez avec lui. C’est aussi le fichier des séances 2 et 3 : tout le monde y repartira du même point.

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 module, et le fichier

Les blocs 2 et 3 de votre certification vous demanderont de mesurer une performance commerciale et d’analyser des résultats. En entreprise, cela se fait dans un tableur, sur un export que personne n’a nettoyé. Ce module vous donne les gestes en quatre séances : lire et faire nettoyer un fichier, le croiser, le mettre en graphiques et en tableau de bord, puis refaire le tout sur un fichier jamais vu. Il n’est rattaché à aucune épreuve et ne sera pas noté.

Kertavel distribue des fournitures et des équipements à des restaurants et des hôtels, en Occitanie et dans les régions voisines. Son export de ventes compte 1 372 lignes, de janvier 2025 à juin 2026 : une ligne par produit d’une commande, avec le client, la région, le commercial, le produit, la quantité, le prix, le montant et le statut. Kertavel est une entreprise fictive, ses données sont inventées.

Le fichier contient volontairement des défauts. Ce n’est pas un piège, c’est le vrai travail : en entreprise vous ne recevrez jamais un fichier propre, et la première compétence d’un analyste est de savoir en dix minutes ce qu’un fichier vaut, et ce qu’il faut lui faire avant de l’additionner.

Guide 1A. Ouvrir le fichier, et le lire

À quoi ça sert. À savoir en dix minutes, avant tout calcul, combien de lignes un fichier contient, ce qu’il y a dans chaque colonne, et s’il est utilisable tel quel.

Ce qu’il vous faut. kertavel-ventes.xlsx, et dix minutes.

A. Une copie, toujours

1. Ouvrez kertavel-ventes.xlsx. Si une bande jaune « Mode protégé » apparaît en haut, cliquez « Activer la modification ».
2. « Fichier » puis « Enregistrer sous ». Donnez-lui votre nom : kertavel-ventes-AB.xlsx, avec vos initiales. Le fichier d’origine reste intact : vous en aurez besoin.

B. Compter les lignes

1. En bas de la fenêtre, quatre onglets : « Lisez-moi », « Ventes », « Produits », « Commerciaux ». Cliquez « Ventes ».
2. Cliquez sur la cellule A1, celle qui contient « Date ». Appuyez sur « Ctrl » et « Flèche bas » en même temps : le curseur saute à la dernière ligne remplie, la ligne 1373, soit 1 372 lignes de données sous la ligne de titres. « Ctrl » et « Flèche haut » pour remonter.

C. Lire une colonne avec le filtre

1. Cliquez sur A1, puis onglet « Données », bouton « Filtrer ». Une petite flèche apparaît dans chaque cellule de titre.
2. Cliquez la flèche de la colonne E « Région ». La liste déroulante montre toutes les valeurs distinctes de la colonne : « Occitanie », « OCCITANIE », « occitanie », « Occitanie » suivi d’un espace. Comptez-les : 19 valeurs, pour quatre régions réelles. Cliquez « Annuler » pour refermer sans rien filtrer.
3. Même geste sur la colonne C « Client » : 43 écritures pour vingt clients.

D. Le test de l’alignement

1. Regardez la colonne L « Quantité » en descendant un peu : la plupart des nombres sont collés à droite de leur cellule, quelques-uns sont collés à gauche. Un tableur aligne les nombres à droite et le texte à gauche, sans qu’on lui demande rien. Un chiffre collé à gauche est un texte, et une somme l’ignore. Même chose sur la colonne M « Prix unitaire » et sur la colonne A « Date ».

E. La barre d’état

1. Cliquez sur la lettre N, en tête de la colonne « Montant ». En bas de la fenêtre, la barre d’état affiche « Somme : 946 938,51 € ». Notez ce nombre : il est faux, et vous saurez à la fin de la matinée de combien.

Et dans les autres tableurs

Si ça ne marche pas

Comment savoir que c’est réussi. Une copie à vos initiales, 1 372 lignes de données, 19 écritures dans la colonne Région pour quatre régions, 43 dans la colonne Client pour vingt clients, et une somme de la colonne Montant à 946 938,51 €.

Atelier 1, ouvrir et lire

Sur votre poste, en autonomie, guide 1A du début à la fin. Puis, sur une feuille, quatre lignes : le nombre de lignes, le nombre d’écritures de la colonne Région et le nombre de régions réelles, la même chose pour la colonne Client, et la somme de la colonne Montant. Ajoutez une cinquième ligne : trois choses qui vous paraissent anormales dans ce fichier.

Comment savoir que c’est réussi : les quatre chiffres du guide 1A sur votre feuille, et trois anomalies écrites.

Version courte si le temps manque : les étapes 1 à 6, le nombre de lignes et le nombre d’écritures de la colonne Région.

Activité bonus : même filtre sur la colonne G « Commercial ». Cinq noms, et une ligne « (Vides) » : combien de lignes n’ont pas de commercial ? Sélectionnez « (Vides) » seul, « OK », et lisez le nombre de lignes affichées en bas à gauche.

Guide 1B. L’audit express par l’IA

À quoi ça sert. À obtenir en deux minutes la liste complète des défauts d’un fichier, avec le nombre de lignes touchées, avant de décider quoi en faire. Ce que vous avez vu à l’œil au guide 1A, l’assistant le compte sur les 1 372 lignes.

Ce qu’il vous faut. kertavel-ventes.xlsx, votre assistant, et le prompt 3.1 de la bibliothèque.

A. Envoyer le fichier

1. Dans l’assistant, glissez le fichier kertavel-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, « L’audit express », tel quel. Envoyez.

B. Lire l’audit

1. Lisez le tableau rendu, ligne par ligne, et comparez-le à celui-ci. C’est ce qu’un audit complet trouve sur ce fichier :

1. S’il manque une famille de défauts, demandez-la : « Tu n’as pas comparé la colonne Montant au produit de la quantité par le prix unitaire. Refais l’inventaire. » Un assistant recompte volontiers ; il ne devine pas ce qu’on ne lui a pas demandé.
2. Si un nombre diffère du tableau, ce n’est pas grave tant que la famille est nommée : deux assistants ne comptent pas un espace de la même façon. Notez l’écart.

Et dans les autres tableurs

Si ça ne marche pas

Comment savoir que c’est réussi. Un tableau à quatre colonnes qui nomme les sept familles du tableau ci-dessus, avec des nombres proches : 11 doublons, 19 écritures pour 4 régions, 43 pour 20 clients, 15 commerciaux vides.

Atelier 2, l’audit express

Sur votre poste, en autonomie, guide 1B : l’audit express par l’assistant, puis la comparaison, ligne à ligne, avec le tableau du guide. Cochez les familles trouvées, notez celles qui manquent et les nombres qui diffèrent.

Comment savoir que c’est réussi : les sept familles cochées, ou la relance envoyée pour celles qui manquaient.

Version courte si le temps manque : lisez le tableau du guide 1B et cochez ce que vous aviez vu vous-même à l’atelier 1.

Activité bonus : posez à l’assistant la question 3.3, « quels sont mes cinq meilleurs clients », sur ce fichier sale. Puis regardez son classement : un client écrit de deux façons y apparaît deux fois, et le premier doublon le gonfle. C’est pour cela qu’on nettoie avant de compter.

Guide 1C. Le nettoyage par l’IA, avec son journal

À quoi ça sert. À obtenir 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. La conversation du guide 1B, avec le fichier déjà joint, et le prompt de nettoyage de Kertavel, recopié dans la partie « Demander à l’IA » de ce guide.

A. Demander le nettoyage

1. Dans la même conversation, collez le prompt de nettoyage de Kertavel. Il reprend le prompt 3.2 de la bibliothèque et demande une opération de plus : compléter les commerciaux manquants à partir du matricule et de l’onglet « Commerciaux ».
2. L’assistant rend un fichier .xlsx à télécharger. Enregistrez-le sous kertavel-ventes-propre-AB.xlsx, avec vos initiales.

B. Lire le journal en premier

1. Ouvrez-le. Cliquez sur l’onglet « Journal ». Cherchez : les doublons supprimés, 11 ; les commerciaux complétés, 15 ; les lignes conservées, 1 361 sur 1 372.
2. Chaque autre ligne du journal doit porter un nombre : les régions ramenées de 19 écritures à 4, les clients de 43 à 20, 18 quantités, 12 prix et 14 dates convertis. Une ligne sans nombre est une opération dont on ne sait pas ce qu’elle a fait.

C. Contrôler sur la feuille Ventes

1. Cliquez « Ventes », puis A1, puis « Ctrl » et « Flèche bas » : ligne 1362.
2. Cliquez la lettre N : la barre d’état affiche « Somme : 941 844,96 € ». C’est le vrai total du fichier, 5 093,55 € de moins que ce matin : les onze doublons.
3. « Données » puis « Filtrer », flèche de la colonne E « Région » : 4 valeurs. Flèche de la colonne G « Commercial » : cinq noms, et plus de « (Vides) ».
4. Regardez les colonnes L et M : plus rien de collé à gauche.

Et dans les autres tableurs

Si ça ne marche pas

Comment savoir que c’est réussi. Un fichier avec un onglet Journal qui annonce 11 doublons supprimés, 15 commerciaux complétés, 1 361 lignes conservées sur 1 372. Sur « Ventes », la dernière ligne est la 1362, la somme de la colonne N vaut 941 844,96 €, et le filtre de la colonne Région affiche 4 valeurs.

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

Sur votre poste, en autonomie, guide 1C : le nettoyage par l’assistant, la lecture du journal, puis les quatre contrôles sur la feuille. Écrivez sur votre feuille, en trois lignes : ce que le fichier avait, ce que l’assistant a corrigé, ce qu’il a signalé sans corriger.

Comment savoir que c’est réussi : 1 361 lignes, 941 844,96 €, 4 régions au filtre, et vos trois lignes écrites.

Version courte si le temps manque : ouvrez kertavel-ventes-propre.xlsx, lisez son Journal, faites les étapes 5 à 8 dessus.

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

Guide 1D. Deux gestes à la main

À quoi ça sert. Ces deux gestes reviennent sur tous les fichiers, ils prennent une minute chacun, et il arrive qu’on n’ait pas d’assistant sous la main. Ils se font sur la copie du fichier sale, celle du guide 1A.

Ce qu’il vous faut. kertavel-ventes-AB.xlsx, votre copie du fichier sale.

A. Supprimer les doublons

1. Cliquez sur une cellule quelconque du tableau, A2 par exemple.
2. « Données » puis « Supprimer les doublons ». Une fenêtre liste les quinze colonnes, toutes cochées.
3. Laissez toutes les colonnes cochées : une ligne n’est en double que si elle est identique partout. « OK ».
4. Le message dit : « 11 valeurs en double trouvées et supprimées ; il reste 1 361 valeurs uniques. » Notez-le : c’est le premier chiffre de votre journal.

B. Convertir un texte en nombre

1. Cliquez sur la cellule L2, puis « Ctrl » et « Maj » et « Flèche bas » : toute la colonne « Quantité » est sélectionnée.
2. Un petit losange jaune avec un point d’exclamation apparaît près de la sélection. Cliquez-le, puis « Convertir en nombre ». Les 18 chiffres collés à gauche passent à droite.
3. Même geste sur la colonne M « Prix unitaire » : 12 conversions.
4. Le test : cliquez la lettre L, la barre d’état affiche une « Somme ». Avant la conversion, elle en affichait une plus petite, sans les 18.

Et dans les autres tableurs

Si ça ne marche pas

Comment savoir que c’est réussi. Excel a annoncé 11 doublons supprimés et 1 361 valeurs uniques. Sur les colonnes L et M, plus aucun chiffre collé à gauche. La somme de la colonne N vaut 941 844,96 €.

Atelier 4, les deux gestes à la main

Sur votre copie du fichier sale, en autonomie, guide 1D : les doublons, puis la conversion des colonnes L et M. Puis comparez votre copie au fichier rendu par l’assistant : même nombre de lignes, même somme.

Comment savoir que c’est réussi : 11 doublons annoncés, 1 361 lignes, 941 844,96 €, et plus rien collé à gauche dans L et M.

Version courte si le temps manque : les doublons seulement, étapes 1 à 4.

Activité bonus : la colonne A « Date » porte 14 dates en texte, collées à gauche. Sélectionnez la colonne, « Données » puis « Convertir », « Suivant », « Suivant », puis « Date » et « JMA », « Terminer ». Le filtre de la colonne les groupe alors par mois.

Guide 1E. La première formule, demandée à l’IA

À quoi ça sert. À obtenir une formule qu’on ne sait pas écrire, à la coller, et à prouver qu’elle est juste. Ici : rapatrier le prix catalogue de chaque produit depuis l’onglet « Produits », pour mesurer la remise réellement consentie.

Ce qu’il vous faut. Votre fichier propre, celui de l’assistant ou le repli, et la bibliothèque de prompts, partie 1 et prompt 2.1.

A. Décrire ses colonnes une fois

1. La description des colonnes de Kertavel est dans la bibliothèque, partie 1, prête à copier : la feuille « Ventes » va de la ligne 2 à la ligne 1362, avec ses quinze colonnes de A à O, et la feuille « Produits » a le code produit en A et le prix catalogue en D, lignes 2 à 18. Copiez-la : elle servira à chaque prompt de formule du module.

B. Demander la formule

1. Collez le prompt 2.1, avec votre tableur, la description des colonnes, et la question : « écris-moi la formule à mettre en P2 qui rapatrie, depuis la feuille Produits, le prix catalogue du code produit de la colonne H, recopiable vers le bas, avec les plages figées par des $ ».
2. Vous devez recevoir un RECHERCHEV : =RECHERCHEV(H2;Produits!$A$2:$D$18;4;FAUX). Il cherche le code de H2 dans la première colonne de la plage, et rapporte la quatrième colonne, le prix catalogue. Sans le FAUX, il rendrait un prix approximatif.

C. Coller, et prouver

1. Cliquez P1, tapez Prix catalogue, Entrée. Cliquez P2, collez la formule, Entrée : 78. Ligne 3 : 38.
2. Double-cliquez sur le petit carré en bas à droite de P2 : la formule descend jusqu’à la ligne 1362. Ligne 152 : 4,20.
3. Ouvrez l’onglet « Produits » et vérifiez à l’œil : KV-5001 vaut bien 78, KV-1015 vaut 38. Une formule reçue est plausible ; trois lignes vérifiées la rendent juste.

D. La remise, et sa moyenne

1. Cliquez Q1, tapez Remise, Entrée. En Q2 : =1-M2/P2, Entrée, puis double-clic sur le petit carré. Sélectionnez la colonne Q, « Accueil », bouton « % », puis « Ajouter une décimale ».
2. Dans une cellule libre, S2 par exemple : =MOYENNE(Q2:Q1362) : 3,1 %.
3. Combien de lignes sans remise ? En S3 : =NB.SI(Q2:Q1362;0) : 620. Le filtre de la colonne Q dit la même chose, et montre qu’il n’existe que cinq valeurs.

Et dans les autres tableurs

Si ça ne marche pas

Comment savoir que c’est réussi. Une colonne P « Prix catalogue » qui affiche 78 en ligne 2, 38 en ligne 3 et 4,20 en ligne 152. Une colonne Q « Remise » dont la moyenne vaut 3,1 %, avec 620 lignes à 0 %.

Atelier 5, la première formule, et la grille de remise

Sur votre fichier propre, en autonomie, guide 1E : le prix catalogue par la formule demandée à l’assistant, la remise, sa moyenne, puis le nombre de lignes à chacune des valeurs de remise que le filtre affiche. Puis répondez sur votre feuille : les remises tombent-elles au hasard, ou se regroupent-elles ? Qu’est-ce que cela vous apprend sur la politique commerciale de Kertavel ?

Comment savoir que c’est réussi : 3,1 % de remise moyenne, et cinq valeurs de remise seulement : 0 % sur 620 lignes, 3 % sur 254, 5 % sur 241, 8 % sur 168, 12 % sur 78.

Version courte si le temps manque : le prix catalogue et la moyenne, étapes 1 à 8.

Activité bonus : demandez à l’assistant la formule qui compte les lignes à 12 % de remise pour chaque commercial. Qui accorde le plus souvent le palier le plus élevé ?

Demander à l’IA

Les prompts du jour. Le fichier de Kertavel est fictif : vous pouvez l’envoyer entier, comme le dit la partie 3 de la bibliothèque.

Le nettoyage de Kertavel, avec journal, guide 1C. C’est le prompt 3.2 avec une opération 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 « Région » et « Client » : une seule écriture par valeur, en gardant la forme la plus fréquente
4. convertis en nombres les colonnes « Quantité », « Prix unitaire » et « Montant », et en dates la colonne « Date »
5. complète les cellules vides de la colonne « Commercial » à partir de la colonne « Matricule » et de l’onglet « Commerciaux »
6. signale, sans les modifier, les lignes dont le montant ne correspond pas à la quantité multipliée par le prix unitaire

Rends-moi un fichier .xlsx contenant 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 : 11 doublons, 15 commerciaux complétés, 1 361 lignes conservées, une somme de « Montant » à 941 844,96 €.

La première formule, guide 1E. Le prompt 2.1, avec la description des colonnes de la bibliothèque, partie 1, et cette question :

Écris-moi la formule à mettre en P2 qui rapatrie, depuis la feuille « Produits », le prix catalogue du code produit de la colonne H, recopiable vers le bas, avec les plages figées par des $. Donne d’abord la formule seule, puis chaque argument en une ligne.

Attendu : =RECHERCHEV(H2;Produits!$A$2:$D$18;4;FAUX), 78 en ligne 2.

Le chemin clic par clic, à tout moment : le prompt 2.3, avec votre tableur et le geste du guide qui ne ressemble pas à votre écran.

Mémo de la séance

Lire un fichier : une copie ; « Ctrl » et « Flèche bas » pour compter les lignes ; le filtre pour lire les valeurs distinctes d’une colonne ; un chiffre collé à gauche est un texte ; la barre d’état pour la somme.

Faire auditer : le fichier joint, pas collé ; le prompt 3.1, sans rien corriger ; sept familles de défauts sur Kertavel, et un total faux de 5 093,55 €.

Faire nettoyer : le prompt 3.2 ; le journal se lit en premier ; trois nombres à contrôler, doublons, lignes conservées, somme. Le repli kertavel-ventes-propre.xlsx est ce que l’assistant doit rendre.

À la main : « Données » puis « Supprimer les doublons », toutes les colonnes cochées ; le losange jaune, « Convertir en nombre ».

La formule : le tableur, le séparateur, les colonnes avec leur lettre, la question, la sortie ; puis trois lignes vérifiées. RECHERCHEV cherche dans la première colonne d’une plage et rapporte la énième, avec FAUX.

Une moyenne ne décrit jamais une distribution : 3,1 % de remise moyenne, et cinq paliers.