[VIDEO]Comment comprendre et corriger les erreurs de formule Excel ?

Si vous lisez cet article, c'est que comme toutes les personnes qui conçoivent des formules sous Excel, vous avez été confronté à un moment ou un autre à un message d'erreur au lieu du résultat que vous pouviez attendre de cette formule. Les messages d'erreur sont très importants dans Excel, car ils signifient qu'il y a un problème dans l'élaboration des formules ou des tableaux. Il est donc primordial de bien comprendre ces messages, afin de corriger les dysfonctionnements présents dans vos feuilles de calcul.

Pour suivre cet article dans les meilleures conditions, je vous invite à télécharger le fichier dans le lien disponible en fin d’article (cliquez ici pour y accéder).

Dans cet article, nous allons passer en revue chacun des messages d'erreur afin de les analyser et de les corriger.

Le fichier que nous allons utiliser dans cet article est disponible au téléchargement en cliquant juste ci-dessous. Je vous invite vivement à le télécharger afin de réaliser différentes opérations en même temps que moi. Pour supprimer toutes les formules déjà présentes dans le fichier, il suffira juste d'appuyer sur le bouton « RAZ » tout en haut de la feuille de calcul.

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

Pour illustrer les différents exemples de cet article, nous partirons d’un tableau présentant les douze mois de l'année, avec les ventes réalisées chaque mois en milliers d'euros.

1. Erreur « #DIV/0 »

Le message d'erreur le plus courant que nous allons retrouver lorsque nous serons amenés à utiliser Excel est le message d'erreur division par zéro (#DIV/0). En effet, comme vous le savez il est strictement impossible de diviser un nombre par zéro, et c'est pourquoi Excel nous renverra cette erreur.

Nous aurons également le même message lorsque nous tenterons de diviser un nombre par une cellule vide de toute contenu. Le tableur considère en effet, qu'une cellule vide est égale à zéro.

2. Erreur « #NOM! »

    2.1. Erreur de syntaxe dans une formule

Le second message d'erreur est le suivant : « #NOM! », il est retourné lorsqu'une formule utilise un nom introuvable.

Par exemple si nous réalisons une somme en utilisant la fonction =SOMME() :

=SOMME(1;2)

Dans ce cas, le résultat bien « 3 ».

Par contre il s’il y a une erreur dans l'écriture de la fonction où par exemple « S » est remplacé par un « P », Excel nous renvoie alors une erreur « #NOM! », car il ne connaît pas la fonction =POMME().

    2.2. Plage nommée introuvable

Nous aurons également une erreur « #NOM! », lorsque nous allons utiliser une formule qui utilise une référence à une zone nommée inexistante.

Par exemple, nous allons donner un nom à la cellule situé sous le tableau, laquelle est destinées à la saisie du mois d’analyse en cours. Pour lui donner un nom, nous allons le saisir dans le champ « Zone de nom », en haut de la fenêtre.

Appelons cette cellule « _moisEnCours » (n’oublions pas de valider ce nom avec la touche [Entrée] du clavier).

Ainsi, pour connaître la valeur du mois en cours n’analyse, il nous suffira d’appeler la cellule par son nom, plutôt que par ces coordonnées.

=_moisEnCours

Par contre, si le nom que nous utilisons comprend une erreur (par exemple « _moisActuel »), alors le tableur ne pourra par retrouver cette formule dans son dictionnaire des noms et retournera alors une erreur.

    2.3. Omission des guillemets pour une chaîne de caractères

Le dernier cas dans lequel Excel nous renverra cette erreur « #NOM! », est lorsque nous omettons d'utiliser les guillemets pour une chaîne de caractères.

Par exemple si nous voulons afficher « jules césar » en majuscules, il nous suffit d'utiliser la fonction MAJUSCULE(), avec la chaîne à passer en majuscule entourée par des guillemets. En revanche, si nous omettons ces dernières Excel nous retournera une erreur.

En effet, en l’absence de guillemets, Excel interprète une chaîne de caractères comme une référence à une plage nommée, qui est alors introuvable (voir le point 2.2, ci-dessus).

3. Erreur « #N/A »

Cette erreur « #N/A » signifie qu'une valeur est manquante, ou introuvable.

    3.1. Valeur exacte non trouvée

Le cas le plus courant dans lequel nous pouvons nous retrouver face à une erreur « #N/A », est lorsque l'on utilise une fonction de recherche, telle de RECHERCHEV(), RECHERCHEH(), INDEX(), …. en spécifiant que la valeur retournée doit être une valeur exacte, mais que cette valeur ne figure pas dans la table de données.

Par exemple si nous voulons avoir les ventes du mois de janvier, nous allons utiliser la fonction RECHERCHEH(), qui permet de réaliser une recherche horizontale. Lorsque que nous recherchons le montant des ventes réalisées au cours du mois de janvier, nous utilisons la formule de la manière suivante :

=RECHERCHEH(13;B9:M10;2;FAUX)

Le dernier paramètre « FAUX » permet de spécifier que nous souhaitons uniquement effectuer la recherche sur une valeur exacte. La valeur recherchée (« 1 ») figure bien dans le tableau des ventes mensuelles, le résultat est alors 8092.

Si par contre, nous effectuons la même recherche, mais pour un treizième mois, qui ne figure pas dans notre table, alors Excel nous retourne une erreur.

    3.2. Erreur #N/A intentionnelle

Nous pouvons aussi pour certains cas particuliers vouloir intentionnellement qu’une cellule ait pour valeur une erreur de type #N/A. Pour cela nous allons simplement utiliser la fonction NA(), ou son équivalent qui consiste à taper directement la valeur « =#N/A ».

Cela va être utile notamment lorsque nous utilisons un graphique. En effet, supposons que nous ne connaissions pas les ventes réalisées au cours du mois de juin. Ledit graphique afficherait alors un creux sur le mois de juin, ce qui n'est pas forcément représentatif de la réalité.

Pour éviter cela, nous pouvons utiliser la fonction NA(), qui va se charger de spécifier au graphique que nous ne connaissons pas cette valeur. De cette manière, celui-ci n'affichera aucune valeur pour le mois de juin.

    3.3. Référence à une cellule dont la valeur est #N/A

Enfin, nous aurons également une valeur #N/A lorsque nous utilisons une formule qui fait référence à une autre cellule ayant elle-même pour valeur l’erreur #N/A.

À titre d’exemple, si nous effectuons la somme des ventes réalisées au cours de l'année, et que les ventes du mois de juin sont toujours égales à #N/A (voir le point 3.2. juste au-dessus), alors le résultat de cette somme sera également une erreur #N/A.

4. Erreur « #REF! »

L'erreur « #REF! » signifie que la plage de cellules à laquelle nous faisons référence dans une formule n'existe pas, ou n’existe plus.

Par exemple, imaginons qu’une formule calcul la somme de trois autres formules. Si d’aventure, ces cellules devaient être supprimées (clic-droit / Supprimer ou le raccourci clavier [Ctrl]+[-]), et non juste effacé, alors dans ce cas Excel perdrait la référence concernant ces cellules et renverrait alors une erreur de référence (« #REF! »).

Astuce : avant de supprimer une cellule, toujours vérifier si celle-ci est déjà utilisée dans une formule (Pour cela, il siffit d'utiliser l'outil de détection des cellules dépendantes (Menu Formules > Repérer les dépendants) :

5. Erreur « #VALEUR! »

L’erreur « #VALEUR! » sera retournée quant à elle lorsque le type d'une cellule n'est pas celui attendu par une formule.

Par exemple supposons que nous voulions effectuer l’addition suivante :

=1+"deux"

En écrivant le « "deux" » en toutes lettres et entre guillemets, Excel ne comprend pas que le texte que nous avons saisi est un nombre.

Il nous retourne donc une erreur « #VALEUR! » pour nous expliquer qu’il attendait une valeur numérique et que nous lui avons donné autre chose.

6. Erreur « #NUL! »

L'erreur « #NUL! » est retournée lorsque le type d'une cellule n'est pas celui attendu par la formule.

Par exemple, imaginons que nous allons vouloir effectuer la somme des cellules contenues à l'intersection des cellules « B10:C10 » et des cellules « C10:D10 ». Pour cela nous utilisons la fonction SOMME(), avec un espace pour séparer les coordonnées de ces deux plages de cellules.

=SOMME(B10:C10 C10:D10)

Excel va alors nous retourner la valeur des cellules contenues au croisement de ces deux plages (en l’occurrence, il n’y a qu’une seule cellule : « C10 », qui a pour valeur « 6540 »).

Supposons maintenant que nous effectuons la même opération, sauf que nous décalons la seconde plage de cellule :

=SOMME(B10:C10 D10:E10)

Il n’existe alors plus aucune intersection entre ces de deux plages de cellules, et donc dans ce cas nous aurons pour résultat une erreur « #NUL! », afin de nous avertir qu’aucune cellule n’est commune à ces deux plages.

7. Erreur « #NOMBRE! »

L'erreur « #NOMBRE! » est retournée lorsqu'une cellule retourne un résultat incorrect.

Par exemple, il faut savoir qu’Excel ne peut pas réaliser de calcul lorsque le résultat à retourner est supérieure à 10 puissance 308 (c’est-à-dire un « 1 », suivi de 308 « 0 »).

Dans notre feuille de calcul, la cellule située aux coordonnées « F54 » a pour valeur 10³⁰⁸. (valeur maximale qu’Excel peut retourner).

Si nous multiplions ce chiffre par un, Excel va bien l'interpréter ne va donc pas nous retourner d'erreur :

=F54*1

Si par contre, nous le multiplions la valeur contenue dans cette cellule par dix, le nombre sera trop grand pour être retournés par Excel et celui-ci va nous retourner une erreur « #NOMBRE! ».

=F54*10

8. Erreur « ######## »

Lorsque l’erreur retournée, est une série de dièses, cela veut simplement dire que la colonne n'est pas assez large pour afficher son contenu.

Il suffit alors d'élargir la largeur de la colonne contenante ladite cellule pour qu'automatiquement le résultat souhaité s’affiche correctement.

9. Pour aller plus loin : exploiter les erreurs

Il est également possible d'exploiter les erreurs obtenues au travers de fonctions (ESTERREUR(), SIERREUR(),…).

La fonction SIERREUR() par exemple permet de retourner une valeur ou une formule particulière, uniquement lorsqu’une          . En l’absence d’erreur, le résultat de la formule est alors affiché normalement.

Par exemple, prenons un tableau simple de synthétisation des ventes réalisées par trois commerciaux, avec le montant des commissions perçues par chacun d’entre eux. La « marge net » nette de ces ventes est donc égale au montant des ventes, auquel nous soustrayons la part de la commission perçue par le commercial.

Le pourcentage de la marge est ainsi égal au montant de la marge que l’on divise par le montant des ventes totales.

Nous constatons ainsi qu’Excel nous retourne une erreur pour le calcul du taux de marge de Stéphane. Cette erreur #N/A est due à l’absence du montant des ventes réalisées par ce dernier.

L’utilisation de la fonction SIERREUR() va nous permettre d’afficher un message personnalisé en cas d’erreur :

=SIERREUR(D64/B64;"Pas de ventes")

Nous validons la saisie de la formule avec la touche [Ctrl] enfoncée afin de valider la saisie des trois formules en même temps. Dorénavant le message d'erreur est plus explicite : il n'y a pas de vente.

10. Utiliser l’aide d’Excel pour identifier et corriger des erreurs

Lorsqu’Excel nous retourne une erreur, celui-ci nous propose également les moyens de la résoudre. En effet, pour identifier ses erreurs il suffit de regarder si la cellule contient un petit triangle vert dans le coin supérieur-gauche de la cellule. Dans ce cas, lorsque l'on clique sur cette cellule, un menu contextuel s’affiche, avec un certain nombre d'options :

• « Aide sur cette erreur » : permet d'expliquer l'erreur,
• « Afficher les étapes de calcul » : permet d'afficher une nouvelle fenêtre dans laquelle Excel va réaliser le calcul étape par étape afin de pouvoir suivre son cheminement de résolution. Ici il n'y a qu'une formule RECHERCHEV(), donc l'étape est constituée d'une seule opération qui nous renvoie directement nombre

• Il est également possible de demander à Excel d'ignorer l'erreur
• Ou encore de modifier directement la formule

Il est également possible de personnaliser le comportement qu’Excel doit avoir lorsque celui-ci est confronté à une erreur. Pour cela il faut aller dans le menu « Options », choisir l’onglet « Formule ».

Dans « Vérifier les erreurs », il est possible de décocher cette case, ce qui aura pour effet de désactiver l'affichage du petit triangle vert que nous venons de voir.

Juste en dessous il est également possible de personnaliser le comportement d’Excel, lorsque celui-ci est confrontée à une erreur.