Analyse de données avec Excel, séance 1

Structurer et nettoyer un jeu de données commercial

À quoi sert ce module

Les blocs 2 et 3 de votre certification vous demanderont de mesurer une performance commerciale, de suivre un budget et d’analyser des résultats. Dans la vraie vie, cela se fait sous Excel. Ce module vous donne les gestes.

Il n’est rattaché à aucune épreuve et ne sera pas noté. Il outille directement les autres modules, et c’est pour cela qu’il est placé en début d’année.

Le fil rouge des quatre séances

Vous travaillerez sur un seul et même fichier, kertavel-ventes.xlsx : 1 372 lignes de commandes d’un distributeur de fournitures et d’équipements pour l’hôtellerie-restauration, de janvier 2025 à juin 2026.

Kertavel est une entreprise fictive et ses données sont inventées. Elles sont construites pour se comporter comme des données réelles : saisonnalité, remises variables, commandes multi-articles, et surtout des défauts de saisie.

Une précision avant de commencer

Le fichier que vous allez ouvrir contient volontairement des erreurs. Il ressemble à ce qu’un export de logiciel de gestion produit réellement : des régions écrites de six façons différentes, des nombres stockés en texte, des lignes en double, des cellules vides.

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 rendre exploitable ce qu’on lui donne.

Avant tout, la règle de survie

Travaillez toujours sur une copie.

1. Ouvrez le dossier contenant kertavel-ventes.xlsx
2. Clic droit sur le fichier, Copier, puis clic droit dans le dossier, Coller
3. Renommez la copie avec vos initiales : kertavel-ventes-AB.xlsx
4. Travaillez uniquement sur cette copie

Le fichier d’origine reste intact. Vous en aurez besoin aux séances suivantes, et le jour où vous détruirez vos données par une mauvaise manipulation, vous serez content de l’avoir.

Ce réflexe vaut aussi en entreprise. Il vaut même surtout en entreprise.

1. Ce qui fait une donnée exploitable

1.1 Les cinq règles d’un tableau propre

Excel sait tout faire, à une condition : que vos données respectent cinq règles. Quand un tableau croisé dynamique refuse de fonctionner, la cause est presque toujours ici.

Une. Une seule ligne d’en-tête, en haut. Pas deux lignes fusionnées, pas de titre au-dessus, pas de logo dans la première ligne.

Deux. Une colonne = une information. « Dupont Jean » dans une colonne est un problème. « Nom » et « Prénom » en deux colonnes est une solution. De même, une colonne « Date » ne doit pas contenir aussi le nom du commercial.

Trois. Une ligne = un enregistrement. Dans notre fichier, une ligne est un produit d’une commande. Pas une commande, pas un client : un produit d’une commande. Une commande de trois articles occupe donc trois lignes, avec le même numéro de commande.

Quatre. Aucune ligne ni colonne vide à l’intérieur du tableau. Une ligne vide coupe le tableau en deux aux yeux d’Excel, et la moitié de vos données disparaît de vos calculs sans avertissement.

Cinq. Un type de donnée par colonne. Une colonne de nombres ne contient que des nombres. Une colonne de dates ne contient que des dates. Mélanger du texte et des nombres est la cause numéro un des totaux faux.

1.2 Les cinq problèmes que vous rencontrerez toujours

1.3 Le geste qui révèle tout en dix secondes

Avant toute analyse, faites systématiquement ceci sur chaque colonne à surveiller :

1. Cliquez sur une cellule de la colonne Région
2. Onglet Données, bouton Filtrer (l’entonnoir)
3. Cliquez sur la petite flèche apparue dans l’en-tête de la colonne
4. Regardez la liste des valeurs proposées

Cette liste vous montre toutes les valeurs distinctes de la colonne. S’il y a quatre régions et que la liste en affiche dix-neuf, vous savez immédiatement où est le problème, et combien de travail vous attend.

Ce geste prend dix secondes et fait gagner des heures. Prenez-le comme un réflexe.

Atelier 1, le diagnostic

Sur votre copie du fichier, feuille Ventes, 15 minutes.

1. Activez le filtre sur toute la ligne d’en-tête.
2. Passez en revue les colonnes Région, Client, Commercial, Statut, Famille.
3. Pour chacune, notez : combien de valeurs distinctes le filtre affiche-t-il, et combien devrait-il en afficher ?
4. Repérez au moins trois types de problèmes différents.
5. Descendez dans la colonne Quantité et cherchez des cellules alignées à gauche.

Écrivez vos constats sur papier. Nous les mettrons en commun avant de corriger.

Ne corrigez rien pour l’instant. Diagnostiquer avant de traiter : c’est vrai en médecine, c’est vrai ici.

2. Nettoyer, geste par geste

2.1 Supprimer les doublons

1. Cliquez sur une cellule quelconque du tableau
2. Onglet Données, groupe Outils de données, bouton Supprimer les doublons
3. Excel sélectionne automatiquement toute la plage et affiche la liste des colonnes
4. Laissez toutes les colonnes cochées : une ligne n’est un vrai doublon que si elle est identique partout
5. Cliquez sur OK
6. Excel affiche combien de doublons ont été supprimés. Notez ce nombre.

Attention. Si vous ne cochez qu’une seule colonne, Excel supprimera toutes les lignes ayant la même valeur dans cette colonne, ce qui détruira des données parfaitement valides. C’est une erreur irréversible et fréquente.

Sur ce fichier précis, l’erreur est spectaculaire. Une commande contient souvent plusieurs produits, donc plusieurs lignes portent le même numéro de commande : c’est normal, c’est même la structure attendue d’un fichier de ventes. Si vous ne cochez que N° commande, Excel considère toutes ces lignes comme des doublons et n’en garde qu’une par commande. Vous perdez alors 651 lignes de vente réelles, soit près de la moitié du fichier, et votre chiffre d’affaires s’effondre sans que rien ne vous prévienne. En cochant toutes les colonnes, vous n’en supprimez qu’une poignée : les vraies lignes saisies deux fois.

Retenez la règle générale : le doublon se définit par l’identité de toute la ligne, jamais par celle d’un seul champ.

2.2 Uniformiser une colonne, sans écrire une seule formule

Le problème : « Occitanie », « occitanie », « OCCITANIE » et « Occitanie » avec un espace final sont quatre valeurs différentes pour Excel, alors qu’elles désignent la même région. Le filtre en affichait dix-neuf pour quatre régions réelles.

La plupart des tutoriels traitent cela avec des fonctions de texte imbriquées. Le geste ci-dessous fait la même chose en quatre clics, se voit à l’écran, et ne crée aucune colonne supplémentaire.

1. Activez le filtre sur la colonne Région
2. Décochez (Sélectionner tout), puis cochez une seule variante, par exemple « occitanie »
3. Sélectionnez les cellules affichées de la colonne, de la première à la dernière
4. Tapez la bonne graphie, Occitanie, sans valider
5. Validez avec Ctrl + Entrée

Toutes les cellules sélectionnées reçoivent la valeur d’un seul coup. C’est le seul point qui surprend : Entrée seul ne remplirait que la première.

Recommencez pour chaque variante, puis retirez le filtre. La colonne Région doit maintenant proposer quatre valeurs, et non dix-neuf.

Pourquoi ce geste plutôt qu’une formule : il corrige la casse et les espaces invisibles en même temps, il travaille sur la colonne d’origine, et vous voyez le résultat pendant que vous le faites. Une formule vous obligerait à créer une colonne de travail, à la recopier, puis à la recoller en valeurs pour vous en débarrasser.

Le contrôle qui prouve que c’est fait : le filtre de la colonne ne doit plus proposer que le nombre de valeurs attendu. Si vous en voyez une de plus, c’est presque toujours un espace final sur une seule ligne.

2.4 Convertir un texte en nombre

Le symptôme : un nombre aligné à gauche de la cellule, parfois avec un petit triangle vert dans le coin.

Méthode 1, la plus rapide

1. Sélectionnez les cellules concernées
2. Un losange jaune avec un point d’exclamation apparaît
3. Cliquez dessus, choisissez Convertir en nombre

Méthode 2, pour les nombres à virgule française mal importés

Quand le prix unitaire est écrit 71,76 et refuse de se convertir, c’est souvent que le séparateur décimal ne correspond pas à celui du système. Passez alors par Données, Convertir, et choisissez le séparateur adapté à l’étape 3 de l’assistant.

Pourquoi ça compte : une somme ignore purement et simplement les nombres stockés en texte. Votre total sera faux, et rien ne vous préviendra. Et, comme vous le verrez à l’atelier 4, un prix unitaire resté en texte fera planter tous les calculs qui s’appuient dessus.

2.5 Repérer les valeurs manquantes

1. Sélectionnez la colonne Commercial
2. Onglet Accueil, Rechercher et sélectionner, Sélectionner les cellules
3. Cochez Cellules vides, puis OK

Excel sélectionne toutes les cellules vides et vous indique leur nombre dans la barre d’état, en bas de l’écran.

Que faire ensuite ? Cela dépend, et c’est une décision de gestion, pas une décision technique :

• Si l’information est retrouvable ailleurs, on la reconstitue. Dans notre fichier, la colonne Matricule est remplie même quand Commercial est vide : le nom est donc récupérable.
• Si elle n’est pas retrouvable, on écrit explicitement Non renseigné. On ne laisse jamais une cellule vide et on n’invente jamais une valeur.

Ne remplissez jamais un trou par une moyenne ou par la valeur du dessus sans le documenter. C’est une falsification, même involontaire.

Atelier 2, nettoyer pour de bon

Sur votre copie, 25 minutes. Travaillez dans l’ordre.

1. Supprimez les doublons. Notez combien Excel en a supprimé.
2. Uniformisez la colonne Région au filtre, variante par variante, en validant chaque fois par Ctrl + Entrée. Vérifiez au filtre qu’il ne reste que quatre régions.
3. Faites de même pour la colonne Client dans une colonne Client nettoyé. Combien de clients distincts obtenez-vous ?
4. Convertissez en nombres les quantités et les prix unitaires stockés en texte.
5. Repérez les cellules vides de la colonne Commercial et comptez-les.
6. Question de réflexion, à noter : comment retrouver le nom du commercial manquant ? Quelle colonne vous le permet ?

Version courte si le temps manque : faites les points 1, 2 et 4. Ce sont ceux qui faussent les calculs, et le point 4 conditionne l’atelier 4.

Activité bonus si vous allez vite : la colonne Montant devrait toujours valoir Quantité × Prix unitaire. Créez une colonne de contrôle qui compare les deux et signale les écarts. Une piste : =SI(ARRONDI(L2*M2;2)=N2;"OK";"Écart"). Attention, le nombre d’écarts que vous trouverez dépend de la qualité de votre conversion à l’étape 4 : c’est normal, et c’est instructif.

Pause, 15 minutes.

3. Le tableau structuré

3.1 Pourquoi convertir une plage en tableau

Jusqu’ici vous avez travaillé sur une plage de cellules ordinaire. Excel propose un objet supérieur : le tableau structuré. Trois avantages décisifs.

Il s’étend tout seul. Ajoutez une ligne en bas : les formules, les formats et les filtres s’appliquent automatiquement. Sur une plage ordinaire, il faut tout refaire.

Les formules deviennent lisibles. Au lieu de =SOMME(N2:N1373), vous écrivez =SOMME(Ventes[Montant]). Six mois plus tard, vous comprendrez encore ce que vous avez écrit.

Les tableaux croisés se mettent à jour proprement. C’est la séance prochaine, et vous m’en serez reconnaissants.

3.2 Comment faire

1. Cliquez sur une cellule quelconque de vos données
2. Onglet Insertion, bouton Tableau. Raccourci : Ctrl + L
3. Excel propose automatiquement la plage. Vérifiez qu’elle couvre bien toutes vos données
4. Cochez Mon tableau comporte des en-têtes
5. OK
6. Un nouvel onglet Création de tableau apparaît dans le ruban. Dans la case Nom du tableau, à gauche, remplacez Tableau1 par Ventes

Ce dernier point n’est pas cosmétique : c’est ce nom que vous utiliserez dans toutes vos formules.

3.3 Les références structurées

Une fois le tableau nommé, vous désignez les colonnes par leur nom :

Essayez : dans une cellule vide en dehors du tableau, saisissez =SOMME(Ventes[Montant]). Vous obtenez le chiffre d’affaires total, et cette formule restera juste même si vous ajoutez mille lignes demain.

4. Les fonctions de comptage et de somme conditionnels

Ce sont les quatre fonctions les plus utilisées en analyse commerciale. Elles répondent toutes à la même question : combien, ou combien d’euros, pour les lignes qui remplissent telle condition ?

4.1 NB.SI, compter selon une condition

Question : combien de commandes ont été passées en Occitanie ?

=NB.SI(Ventes[Région nettoyée];"Occitanie")

Deux arguments : la plage où chercher, puis la condition.

La condition peut être plus souple :

=NB.SI(Ventes[Montant];">1000")

Compte les lignes dont le montant dépasse 1 000 euros. Notez que la condition avec un opérateur s’écrit entre guillemets.

4.2 SOMME.SI, additionner selon une condition

Question : quel chiffre d’affaires a été réalisé en Occitanie ?

=SOMME.SI(Ventes[Région nettoyée];"Occitanie";Ventes[Montant])

Trois arguments, dans cet ordre : où chercher la condition, la condition, puis quelle colonne additionner.

L’erreur classique consiste à inverser le premier et le troisième argument. Le moyen mnémotechnique : on dit d’abord où on cherche, ensuite ce qu’on cherche, enfin ce qu’on additionne.

4.3 NB.SI.ENS et SOMME.SI.ENS, plusieurs conditions

Dès qu’il y a plus d’une condition, on ajoute .ENS et l’ordre des arguments change.

Question : quel chiffre d’affaires en Occitanie sur les commandes facturées ?

=SOMME.SI.ENS(Ventes[Montant];Ventes[Région nettoyée];"Occitanie";Ventes[Statut];"Facturée")

Ici, la colonne à additionner vient en premier, puis les couples plage-condition, autant qu’on veut.

C’est déroutant : SOMME.SI met la colonne à additionner en dernier, SOMME.SI.ENS la met en premier. Ce n’est pas logique, c’est ainsi. Beaucoup de professionnels n’utilisent que la version .ENS, y compris avec une seule condition, précisément pour n’avoir qu’une syntaxe à retenir. C’est un choix défendable.

4.4 Le piège des conditions

Le troisième cas mérite attention : pour comparer à une cellule, il faut concaténer avec &. Écrire ">B1" cherche littéralement le texte « >B1 », ce qui ne renvoie jamais rien.

Atelier 3, interroger les données

Sur votre fichier nettoyé, 15 minutes. Créez une nouvelle feuille nommée Analyse et répondez par des formules, jamais par un comptage manuel.

1. Combien de lignes de commande au total ?
2. Quel est le chiffre d’affaires total, toutes lignes confondues ?
3. Quel chiffre d’affaires pour chacune des quatre régions ?
4. Combien de lignes ont le statut Annulée ?
5. Quel chiffre d’affaires réel, c’est-à-dire hors commandes annulées ?
6. Combien de lignes dépassent 1 000 euros ?
7. Quel chiffre d’affaires en Occitanie sur les seules commandes facturées ?

Question de réflexion : entre le total de la question 2 et celui de la question 5, quel est l’écart ? Lequel des deux communiqueriez-vous à votre direction, et pourquoi ?

C’est cette question qui compte. Les six premières sont de la mécanique.

5. Retrouver une information dans une autre table

Le besoin : la feuille Ventes contient un code produit. Le prix catalogue, lui, est dans la feuille Produits. Comment rapatrier le prix catalogue en face de chaque ligne de vente, pour calculer la remise réellement consentie ?

C’est le besoin le plus fréquent en gestion commerciale, et il a deux solutions selon votre version d’Excel.

5.1 Vérifiez d’abord votre version

Ce point est important, faites-le avant d’aller plus loin.

Dans une cellule vide, commencez à taper =RECHERCHEX. Si Excel vous propose la fonction dans la liste déroulante, vous l’avez. Sinon, votre version ne la connaît pas.

RECHERCHEX n’existe qu’à partir d’Excel 2021 et dans les abonnements Microsoft 365. Sur Excel 2016 ou 2019, elle est absente, et il faut utiliser RECHERCHEV.

Les deux méthodes sont présentées ci-dessous. Utilisez celle qui correspond à votre poste, et sachez que les deux existent : en entreprise, vous ne choisissez pas la version installée.

5.2 RECHERCHEX, si vous l’avez

=RECHERCHEX(H2;Produits!$A$2:$A$18;Produits!$D$2:$D$18;"Introuvable")

Quatre arguments :

1. Ce qu’on cherche : H2, le code produit de la ligne courante
2. Où on le cherche : la colonne des codes dans la feuille Produits
3. Ce qu’on ramène : la colonne des prix catalogue
4. Que faire si on ne trouve pas : ici, afficher « Introuvable » plutôt qu’un message d’erreur

Le quatrième argument est facultatif mais fortement recommandé. Sans lui, une valeur absente produit #N/A, qui contamine ensuite tous vos calculs.

5.3 Ce que vous verrez en entreprise : RECHERCHEV

Vous rencontrerez partout, dans les fichiers de vos collègues, une fonction plus ancienne qui fait la même chose :

=RECHERCHEV(H2;Produits!$A$2:$D$18;4;FAUX)

Vous n’avez pas à l’écrire. Vous devez seulement savoir la lire, et connaître ses trois limites, parce qu’elles expliquent la plupart des fichiers cassés que l’on vous demandera de réparer :

• la valeur cherchée doit être dans la première colonne de la plage, elle ne sait pas regarder à gauche ;
• le quatrième argument est un rang de colonne, un simple numéro : insérez une colonne dans la table et toutes les formules deviennent fausses sans le moindre message ;
• si la valeur est absente, elle renvoie #N/A et il faut l’envelopper dans une seconde fonction pour l’afficher proprement.

RECHERCHEX n’a aucun de ces trois défauts : elle regarde dans n’importe quelle direction, elle désigne les colonnes au lieu de les compter, et elle prend en quatrième argument ce qu’il faut afficher quand la valeur n’existe pas. C’est la raison pour laquelle ce cours ne vous en fait apprendre qu’une.

Si votre poste ne propose pas RECHERCHEX : signalez-le, on fera la manipulation ensemble avec RECHERCHEV. Ne restez pas bloqué.

5.4 Le rôle des dollars

Dans $A$2:$A$18, les signes $ figent la référence. Quand vous recopiez la formule vers le bas, la plage de recherche ne se décale pas.

Sans les dollars, la plage glisserait d’une ligne à chaque recopie, et les dernières lignes chercheraient dans une zone vide. Le résultat serait partiellement faux, ce qui est bien pire que totalement faux : vous ne le verriez pas.

Raccourci : après avoir sélectionné une plage dans une formule, appuyez sur F4 pour ajouter les dollars automatiquement.

Atelier 4, la remise réelle

1. Ajoutez une colonne Prix catalogue et rapatriez-y le prix depuis la feuille Produits.
2. Ajoutez une colonne Remise % qui calcule l’écart entre le prix catalogue et le prix unitaire réellement facturé. Formule si vous bloquez : =1-(M2/[prix catalogue]), mise en forme en pourcentage.
3. Quel est le taux de remise moyen ? Utilisez =MOYENNE(...).
4. Combien de lignes ne se calculent pas ? Pourquoi ?
5. Regardez les valeurs obtenues. Se répartissent-elles au hasard, ou se regroupent-elles sur quelques valeurs ? Qu’est-ce que cela vous apprend sur la politique commerciale de Kertavel ?

Les questions 4 et 5 sont les vraies questions de l’atelier.

La question 4 fait le lien avec le nettoyage : si vous n’avez pas converti les prix unitaires restés en texte à l’atelier 2, le calcul de remise échoue sur ces lignes. Le nettoyage n’est pas une corvée préalable, c’est ce qui rend l’analyse possible.

La question 5 est du métier pur : découvrir une politique de remise en observant des données, sans que personne ne vous l’ait décrite, c’est exactement ce qu’on attend d’un analyste commercial.

Ce qu’il faut retenir

Toujours travailler sur une copie. En cours comme en entreprise.

Diagnostiquer avant de corriger. Le filtre sur une colonne révèle en dix secondes toutes ses valeurs distinctes. C’est le geste le plus rentable du tableur.

Les cinq règles d’un tableau propre : une seule ligne d’en-tête, une colonne par information, une ligne par enregistrement, aucune ligne vide à l’intérieur, un seul type de donnée par colonne.

Nettoyer : le filtre puis Ctrl + Entrée pour uniformiser une colonne, le losange jaune ou Données → Convertir pour les nombres restés en texte, Supprimer les doublons en gardant toutes les colonnes cochées.

Ne jamais inventer une donnée manquante. On la reconstitue si c’est possible, sinon on écrit « Non renseigné ».

Le tableau structuré rend les formules lisibles et s’étend tout seul. Ctrl + L, puis nommez-le.

Les quatre fonctions du quotidien : NB.SI, SOMME.SI, NB.SI.ENS, SOMME.SI.ENS. Attention à l’ordre des arguments, qui change entre les versions simples et les versions .ENS.

Pour rapatrier une information d’une autre table : RECHERCHEX, en figeant les plages avec $ et en remplissant toujours le quatrième argument, celui qui dit quoi afficher si la valeur n’existe pas.

Un nettoyage incomplet se paie plus tard. Les prix restés en texte ne cassent rien à l’étape du nettoyage. Ils cassent le calcul de remise trois heures après.

Mémo des fonctions de la séance