Comment repérer les valeurs aberrantes et les erreurs de saisie dans un tableur

Il suffit d’un zéro de trop pour qu’une commande de 150 devienne 1500 et que le total du mois s’envole. Les erreurs de saisie sautent rarement aux yeux dans une feuille de plusieurs centaines de lignes, mais elles faussent moyennes, graphiques et décisions.

Vous apprendrez ici à repérer les valeurs suspectes avec des méthodes simples et fiables : un premier coup d’œil avec des fonctions de base, la méthode de l’écart interquartile, le score z, la mise en forme conditionnelle et quelques formules pour des erreurs précises. Vous verrez enfin comment empêcher les erreurs d’entrer dans la feuille.

Une valeur aberrante n’est pas toujours une erreur

Une valeur aberrante est une valeur très éloignée des autres. Ce peut être une erreur, mais aussi une donnée réelle : un client qui a passé une commande énorme ou un mois aux ventes exceptionnelles. D’où la règle d’or : signaler, vérifier, puis décider, sans jamais supprimer automatiquement.

Les erreurs de saisie les plus courantes sont :

• Zéros en trop ou en moins : 1500 au lieu de 150.
• Mauvais séparateur décimal : 1250 au lieu de 12,50.
• Chiffres inversés : 516 au lieu de 156.
• Unités mélangées : des grammes dans une colonne de kilos.
• Valeurs impossibles : quantités négatives, âges de 250 ans ou dates futures dans un registre de ventes.
• Texte dans des colonnes numériques : « n/d », « en attente » ou un tiret.
• Enregistrements en double : la même ligne saisie deux fois.

Premier coup d’œil : minimum, maximum et médiane

Avant d’appliquer des méthodes plus fines, calculez trois valeurs sur la colonne (dans l’exemple, des montants en B2:B200) :

=MIN(B2:B200)

=MAX(B2:B200)

=MEDIANE(B2:B200)

Si la médiane est de 140 et le maximum de 14000, vous savez déjà par où commencer. Comparez aussi la médiane avec MOYENNE : quand la moyenne est nettement au-dessus de la médiane, quelques valeurs extrêmes la tirent vers le haut.

Autre méthode rapide : triez la colonne du plus grand au plus petit avec Données › Trier et examinez les premières et dernières lignes. Si vous ne voulez pas perdre l’ordre d’origine, ajoutez d’abord une colonne de numéros consécutifs pour pouvoir le rétablir.

La méthode de l’écart interquartile

C’est la méthode la plus robuste dans un tableur, car elle ne se laisse pas influencer par les valeurs extrêmes elles-mêmes. Elle divise les données en quatre parts et considère comme aberrant ce qui s’éloigne beaucoup de la moitié centrale.

1. Calculez le premier quartile en F1 : =QUARTILE.INCLURE($B$2:$B$200;1)
2. Calculez le troisième quartile en F2 : =QUARTILE.INCLURE($B$2:$B$200;3)
3. Calculez l’écart interquartile en F3 : =F2-F1
4. Limite inférieure en F4 : =F1-1,5*F3
5. Limite supérieure en F5 : =F2+1,5*F3

Ensuite, dans une colonne auxiliaire, signalez chaque ligne qui sort de ces limites :

=OU(B2<$F$4;B2>$F$5)

Avec des données d’exemple, cela donne :

Le facteur 1,5 est le plus courant. Pour ne signaler que les cas les plus extrêmes, utilisez 3.

Le score z

Le score z indique à combien d’écarts types chaque valeur se trouve de la moyenne. En C2 :

=(B2-MOYENNE($B$2:$B$200))/ECARTYPE.STANDARD($B$2:$B$200)

Et pour signaler les valeurs éloignées :

=ABS(C2)>3

Une valeur absolue supérieure à 3 est très rare si les données suivent une loi normale. Gardez en tête deux limites : les valeurs aberrantes gonflent elles-mêmes l’écart type et peuvent se cacher, et avec peu de données (moins de 30, par exemple) la méthode est peu fiable. Dans ces cas, l’écart interquartile fonctionne mieux.

Mettre en évidence les valeurs suspectes avec la mise en forme conditionnelle

Si vous préférez voir les valeurs aberrantes sans colonnes auxiliaires :

1. Sélectionnez B2:B200.
2. Allez dans Accueil › Mise en forme conditionnelle › Nouvelle règle.
3. Choisissez Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué.
4. Saisissez =OU($B2<$F$4;$B2>$F$5) et choisissez une couleur de remplissage.

Pour une analyse rapide sans calcul, les Règles des valeurs plus/moins élevées du même menu mettent en évidence, par exemple, les 10 % de valeurs les plus élevées. C’est utile pour une première exploration, même si cela signalera toujours quelque chose, qu’il y ait des erreurs ou non.

Formules pour des erreurs de saisie précises

Texte dans une colonne numérique

La différence entre les cellules non vides et celles qui contiennent des nombres révèle combien de textes se sont glissés :

=NBVAL(B2:B200)-NB(B2:B200)

Valeurs impossibles

Comptez les quantités négatives et les dates postérieures à aujourd’hui (avec les dates en colonne D) :

=NB.SI(B2:B200;"<0")

=NB.SI(D2:D200;">"&AUJOURDHUI())

Un zéro ou une virgule décalés

Divisez chaque valeur par la médiane. Des résultats proches de 10 ou de 0,1 indiquent généralement un zéro de trop ou une décimale mal placée :

=B2/MEDIANE($B$2:$B$200)

Enregistrements répétés

Pour savoir si un numéro de commande apparaît plus d’une fois :

=NB.SI($A$2:$A$200;A2)>1

Doublons et comparaison de versions

Les lignes saisies deux fois font partie des erreurs de saisie les plus fréquentes, et avec plusieurs milliers d’enregistrements, les vérifier à la main n’est pas réaliste. Notre outil gratuit de suppression des doublons élimine les lignes répétées d’un fichier Excel, CSV ou ODS en quelques secondes. Et une fois les corrections terminées, vous pouvez comparer le fichier d’origine avec le fichier corrigé pour vérifier exactement quelles cellules vous avez modifiées.

Prévenir les erreurs avec la validation des données

La meilleure façon de réduire les erreurs est de les empêcher d’entrer. Avec la validation des données, Excel refuse les valeurs hors de la plage que vous définissez :

1. Sélectionnez la colonne où l’on saisit les quantités.
2. Allez dans Données › Validation des données.
3. Sous Autoriser, choisissez Nombre entier ou Décimal, et fixez un minimum et un maximum raisonnables, par exemple entre 1 et 500.
4. Dans l’onglet Alerte d’erreur, rédigez un message clair pour la personne qui remplit la feuille.

Pour les champs à valeurs fixes, comme des catégories ou des statuts, utilisez l’option Liste : vous évitez des variantes comme « En attente », « en attente » et « En att. ». Notez que la validation ne contrôle ni les données déjà présentes dans la feuille ni celles collées par-dessus les cellules.

Conclusion

Repérer les erreurs de saisie est une question de méthode : commencez par le minimum, le maximum et la médiane, signalez les valeurs aberrantes avec l’écart interquartile ou le score z, mettez-les en évidence avec la mise en forme conditionnelle et utilisez des formules précises pour les textes, les valeurs impossibles et les doublons. Vérifiez chaque cas avant de le corriger et, pour l’avenir, protégez la saisie avec la validation des données.

Questions fréquentes