Les #ERREURS!

Fonctions et formules d’Excel renvoient parfois des valeurs inattendues : #DIV/O!, #VALEUR!, … ce sont des valeurs d’erreur. Il en existe 7 en tout et pour tout. Il est très important d’en comprendre la signification pour pouvoir les corriger ou leur appliquer le bon traitement.

Les classiques

#VALEUR!

L’erreur #VALEUR! apparaît chaque fois que vous faites un calcul avec une valeur non numérique ou incompatible avec le calcul, ou, encore, lorsqu’une valeur non numérique est passée en paramètre d’une fonction à la place d’une valeur numérique.

Dans le cas illustré, la formule est correcte mais la valeur « TOTO » n’étant pas un nombre, elle ne peut être utilisée pour le calcul.

Dans certains cas, par exemple dans la formule « = A1 + B1:C1″, l’incompatibilité tient au fait qu’une plage de cellule (B1:C1) ne peut pas être utilisée dans un calcul arithmétique.

#DIV/O!

Nous avons juste remplacé « TOTO » de l’exemple précédent par zéro. L’erreur #DIV/O! nous rappelle ce que nous savons depuis l’école primaire : on ne peut pas diviser par zéro.

Cette erreur est très fréquente dans les tableaux où un ratio est calculé mais où certaines valeurs sont absentes (une cellule vide vaut zéro). Nous verrons dans le prochain article comment masquer ou éviter cette erreur.

#NOM?

Cette erreur apparaît lorsqu’un nom de fonction ou de plage, une référence a été mal orthographié. Dans l’exemple la fonction « some » n’existe pas. On ajoute un « m » et tout rentre dans l’ordre. Notez bien que l’on peut sans inconvénient écrire en minuscule les noms de fonction et même les références.

 

Si vous utilisez les plages nommées, un bon moyen d’éviter les erreurs de nom est d’en commencer la saisie comme dans l’exemple ci-contre où l’on saisit « Tau ». Excel affiche la liste des fonctions et des noms de plage commençant par le groupe de lettres « Tau ». Il suffit de sélectionner la fonction ou le nom que l’on souhaite insérer dans la formule.

Si vous ne connaissez pas les noms de plage, voici une excellente occasion de consulter les articles Les noms (épisode 1) et Les noms (épisode 2).

 #REF!

Voici deux cas fréquents d’apparition de l’erreur #REF! Dans le premier, la cellule A1 référencée dans la formule de la cellule C1 est désintégrée avec ses copines de la colonne A. Dans le second cas, lors du copier-coller en B1 de la formule de la cellule C1, Excel ne parvient pas à décaler la référence A1 d’une cellule vers la gauche.

Dans les deux cas c’est une action de l’utilisateur qui a causé directement l’erreur #REF! Vous remarquez que c’est la référence de cellule erronée qui est remplacée par la valeur d’erreur. Attention si une formule référence une cellule contenant la valeur #REF!, elle prend elle-même la valeur #REF! Pour remonter à la source de l’erreur, il suffit de regarder si #REF! apparaît dans la formule.

Que faire ? On peut essayer d’annuler les dernières actions (Ctrl + Z) jusqu’à disparition de l’erreur. Si l’erreur est trop ancienne pour être annulée, restaurez les modifications annulées en appliquant le raccourci Ctrl + Y, puis corrigez la formule.

L’erreur #REF! apparaît dans l’utilisation de certaines fonctions pour vous signaler que la cellule que vous souhaitez atteindre n’existe pas. La fonction INDEX() renvoie la valeur contenue dans la nième (ici 5ème) cellule de la plage passée en premier paramètre (ici A2:A5). Or la plage ne contient ici que 4 cellules. La fonction renvoie donc #REF!

#N/A

L’erreur #N/A mérite d’occuper sa propre catégorie. C’est une valeur d’erreur renvoyée par les fonctions de recherche (RECHERCHEV(), RECHERCHEH() et EQUIV()). Elle signifie simplement : je n’ai pas trouvé la valeur cherchée.

Dans l’exemple ci-contre la valeur « Concombre » n’est pas trouvée dans la colonne A, la fonction renvoie #N/A.

Je qualifie souvent cette erreur de fausse erreur dans la mesure où elle correspond à un état des données et non à une erreur au strict sens du terme.

Petite question en passant : quelle erreur serait renvoyée par la fonction si au lieu de 2 (en deuxième paramètre) on avait écrit 3 ?

Les rarissimes

Les deux dernières erreurs sont à peu près inconnues mais l’exhaustivité à laquelle nous prétendons ne nous permet pas de les ignorer.

#NOMBRE!

L’erreur #NOMBRE! est ce que les informaticiens appellent un dépassement de capacité.

Le plus grand nombre que peut manipuler Excel est 2 puissance 1024 moins 1, soit à peu près 1,8 fois 10 puissance 308 ou encore 18 suivi de 307 zéros.

Dans l’exemple illustré, on calcule la factorielle de 171 (soit 1 x 2 x 3 x 4 x 5 x … x 168 x 169 x 170 x 171). Excel ne peut pas le calculer car il est plus grand que 1,8 fois 10 puissance 308 (noté 1,8E+308 dans Excel). Il dépasse la capacité de stockage d’un nombre dans Excel. Essayez avec 170, ça marche.

L’erreur #NOMBRE! peut aussi se produire dans certains calculs en boucle (dit récursifs) ou une valeur est utilisée dans son propre calcul. Si Excel ne parvient pas à trouver un résultat stable (convergent), il finit par renvoyer la valeur #NOMBRE!

 #NUL!

Pour comprendre cette erreur, il faut d’abord découvrir une notation assez rare : l’intersection de deux plages.

 

 

 

 

La notation A1:B3 espace B2:C4 signifie « intersection des plages A1:B3 et B2:C4″, c’est à dire la plage B2:B3. Et, de fait, la fonction SOMME() renvoie bien la somme des cellules B2 et B3 soit 144.

Essayons à présent avec deux plages ayant une intersection vide comme ici A1:A4 et B1:C4.

 

 

L’intersection étant vide la fonction SOMME() ne peut renvoyer de nombre. En informatique, on dit qu’elle renvoie la valeur NULL (avec deux ailes).

Conclusion

Voici donc notre petit tour des erreurs terminé.

Certains mentionnent les # qui apparaissent lorsqu’une cellule n’est pas assez large pour afficher un nombre mais il s’agit d’un problème d’affichage et non d’une erreur au sens calculatoire du terme.

Maintenant que nous connaissons toutes les erreurs nous pourrons dans un prochain article nous intéresser à la manière de les masquer, ou au contraire de les mettre en évidence ou encore à les traiter pour qu’elles ne soient pas gênantes, car, comme il est signalé pour l’erreur #REF!, l’utilisation d’une cellule en erreur rend erronés tous les calculs basés sur elle.

Mais cela, ce sera dans un prochain article, car écrire fatigue. Et si je continuais, je risquerais de vous donner de mauvaises informations qui pourraient vous induire en erreur et non vous enduire d’erreur comme on dit parfois … par erreur, justement !