Découvrez le TOP3 des erreurs les plus communes sur Excel (et comment les corriger)

Dans ce tutoriel, je vais vous montrer comment identifier et corriger les erreurs les plus courantes dans Excel.

Que vous soyez débutant ou utilisateur avancé, ces astuces vous aideront à éviter les pièges classiques et à optimiser votre travail avec Excel.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Pour illustrer ce tutoriel, nous allons utiliser un tableau d'exemple qui répertorie les ventes de livres d'une librairie en ligne.

Ce tableau contient plusieurs erreurs intentionnelles que nous allons identifier et corriger.

Voici le tableau :

Comme vous pouvez le constater, dans ce tableau des erreurs courantes ont volontairement été introduites.

Nous allons maintenant les analyser et les corriger une par une.

2. Les erreurs de formules

   2.1. Erreur due à des données non numériques

La première erreur à corriger se trouve dans la cellule E10 dans laquelle est saisie la formule "=C10*D10".

Cette formule fait donc référence à la cellule C10, qui contient le mot « sept » au lieu du nombre 7, ce qui empêche Excel de multiplier cette valeur par le prix unitaire indiqué en D10.

Excel ne peut pas effectuer de calculs mathématiques avec des chaînes de texte, ce qui provoque l'erreur.

La solution est donc aussi simple que logique : nous devons remplacer « sept » par 7 pour permettre à la formule de fonctionner correctement.

Pour éviter de telles erreurs à l'avenir, une astuce consiste à utiliser la validation des données pour nous assurer que seules des valeurs numériques sont entrées dans la colonne « Quantité ».

Pour ce faire, nous sélectionnons la colonne C, nous allons dans le menu « Données » et cliquons sur « Validation des données ».

Nous choisissons ensuite « Nombre entier » et définissons des critères de validation.

   2.2. Erreur de valeur

Ensuite, nous pouvons également constater que la cellule E11 affiche elle aussi une erreur #VALEUR!, ce qui indique une impossibilité d’effectuer le calcul.

Comme nous venons de le voir, cette erreur provient généralement d'un problème avec le type de données dans l'une des cellules référencées par la formule.

Dans notre cas, la cellule D11 contient une erreur de type #VALEUR!, ce qui empêche la formule de fonctionner correctement. Cela peut arriver pour plusieurs raisons, comme par exemple si les données ont été extraites d'une application tierce où les formats de données ne sont pas compatibles avec Excel. Parfois, les données importées peuvent inclure des caractères non numériques, des espaces, ou des formats de texte mal interprétés par Excel.

Une fois la source de l'erreur identifiée, nous devons remplacer la valeur incorrecte dans D11 par une valeur numérique correcte. Dans cet exemple, nous remplacerons la valeur de D11 par 30, ce qui représente le prix unitaire du « Livre de cuisine ». Une fois la correction effectuée, la formule "=C11*D11" dans la cellule E11 calculera correctement le total en multipliant la quantité par le prix unitaire, c'est-à-dire 5 (quantité) par 30 (prix unitaire), donnant un total de 150.

Pour éviter ce type d'erreurs à l'avenir et améliorer la robustesse de nos formules, nous pouvons utiliser la fonction "SIERREUR". Cette fonction permet de gérer les erreurs en affichant un message personnalisé ou une valeur de substitution en cas d'erreur dans le calcul. Par exemple, nous pourrions modifier la formule dans E11 pour utiliser "SIERREUR", ce qui donnerait "=SIERREUR(C11*D11; "Donnée non valide")". Ainsi, au lieu de montrer une erreur #VALEUR!, Excel afficherait le message « Donnée non valide » si une erreur était détectée, rendant le problème plus compréhensible pour l'utilisateur. Cette approche est particulièrement utile pour les tableaux partagés ou utilisés par plusieurs personnes, car elle aide à diagnostiquer les problèmes rapidement et à les corriger efficacement.

   2.3. Erreur de syntaxe

L’erreur suivante se trouve dans la cellule E14, dans laquelle nous retrouvons la formule "=SOME(E9:E13)", ce qui provoque une erreur, car « SOME » n'est pas une fonction valide en Excel.

Le nom correct de la fonction est "SOMME", qui est utilisée pour additionner une plage de cellules.

Pour corriger cette erreur, il suffit donc de remplacer « SOME » par « SOMME ».

La formule correcte devient donc "=SOMME(E9:E13)".

Cette modification calculera correctement le total des ventes.

   2.4. Erreur division par zéro

La formule "=E14/C14" dans la cellule D14 pose également problème, car elle essaie de diviser par zéro, ce qui génère une erreur #DIV/0!.

En effet, en mathématiques, la division par zéro est indéfinie et Excel le reflète en affichant cette erreur.

Cela peut se produire lorsque la cellule utilisée comme diviseur dans une formule contient la valeur zéro ou est vide, ce qui est une situation courante lorsque des données sont mal saisies ou manquent.

Pour corriger cette erreur, nous devons d'abord nous assurer que la cellule C14 contient une valeur différente de zéro et non vide.

Ici, cette cellule contient la quantité totale de livres vendue.

Nous allons donc souhaiter y calculer cette valeur en utilisant "=SOMME(C9:C13)".

3. Erreurs de formatage

Le format des nombres est crucial pour assurer la clarté des données et la précision des calculs. Par exemple, si des nombres sont enregistrés en tant que texte, Excel ne les traitera pas comme des valeurs numériques, ce qui peut entraîner des erreurs dans les calculs, comme l'addition ou la moyenne.

Un autre exemple d'erreur de formatage peut se produire lorsque nous utilisons un format personnalisé pour les durées dans un tableau de suivi du temps de travail, comme le montre l'exemple ci-dessous :

Dans ce tableau, les durées sont saisies dans un format texte avec les heures et minutes, mais sans être correctement formatées comme des nombres ou des heures.

Par conséquent, Excel ne peut pas traiter ces valeurs pour les calculs, comme la somme totale des heures travaillées, car il les considère comme du texte et non comme des données numériques.

Pour corriger cette erreur, nous devons d'abord convertir les données en un format numérique. Par exemple, au lieu d'avoir "5h25", nous devons entrer "5:25" en utilisant le format horaire standard d'Excel.

Ensuite, nous devons vérifier le format des cellules de la colonne « Durée », en effectuant un clic droit, puis en choisissant « Format de cellule ».

Dans la boîte de dialogue, assurez-vous que le format est défini sur « Heure » ou « Personnalisé » avec un format qui interprète correctement les heures et minutes (par exemple, "[h]:mm" pour un total horaire cumulé).

En ajustant le format des données de cette manière, nous permettons à Excel de traiter correctement les durées et de calculer le total des heures travaillées avec la formule "=SOMME(B2:B6)".

Cela assure que le résultat reflète précisément la somme des durées hebdomadaires, ce qui est crucial pour le suivi des heures de travail ou pour la facturation dans un contexte professionnel.

4. Erreurs de référence

Les erreurs de référence sont fréquentes dans Excel, surtout lorsqu'on modifie les données ou les structures des tableaux.

Elles se manifestent souvent par le message d'erreur #REF!, qui indique qu'une formule ne peut plus localiser une cellule ou une plage de cellules nécessaires pour le calcul.

En effet, l'erreur #REF! survient lorsque des cellules ou des plages de cellules référencées par des formules sont supprimées ou déplacées.

Par exemple, supposons que nous supprimions accidentellement la ligne 22, pourtant utilisée dans la formule de la cellule B24.

Excel ne peut alors plus trouver cette cellule, et la formule renverra une erreur #REF!.

Pour éviter cette situation, avant de supprimer ou de déplacer des cellules, examinez les formules qui dépendent de ces cellules. Cela peut être fait en sélectionnant une cellule et en utilisant la fonctionnalité « Dépendants » d'Excel (sous l'onglet « Formules » > « Dépendants ») pour voir quelles formules utilise cette cellule.

Un autre type d’erreur de référence commune est l’utilisation d’une référence circulaire.

Cela se produit lorsqu'une formule fait référence à sa propre cellule, directement ou indirectement, créant ainsi une boucle infinie.

Par exemple, supposons que nous avons les données suivantes dans notre tableau :

Dans cet exemple, la cellule B30 contenant le calcul de la marge brute utilise la formule =B28*B31, où B28 est le chiffre d'affaires et B31 le taux de marge brute. Si par erreur, la cellule B31 (qui doit contenir le taux) est calculée en utilisant B30 (=B30/B28), une référence circulaire se crée.

Cela se produit parce que B30 dépend de B31 et vice versa, formant une boucle où les deux cellules se référencent mutuellement.

Comme vous pouvez le constater, la présence de référence circulaire est clairement indiquée par Excel avec des flèches bleues indiquant l’origine du problème.

Nous retrouvons également un message dans la barre d’état en bas à gauche avec les coordonnées d’une des cellules concernées :

Les références circulaires peuvent conduire à des résultats erronés ou à des messages d'erreur, et Excel tentera souvent de calculer les valeurs jusqu'à ce qu'une limite soit atteinte ou qu'une erreur soit signalée.

Pour résoudre le problème, nous devons ajuster au moins l'une des formules pour éliminer la boucle.

Par exemple, pour déterminer le montant de la marge brute, nous pouvons déduire du chiffre d’affaires le montant des achats :

 =B28-B29 

En évitant les références circulaires, nous assurons que les calculs dans Excel sont corrects et que les feuilles de calcul fonctionnent de manière fluide sans erreurs ou comportements inattendus.