Intercepter et gérer les erreurs présentes dans des formules Excel

Pour rendre votre travail attrayant, Formation Excel vous aide dans cet article à gérer les erreurs des formules que contiennent les cellules d’un tableau Excel et ceci en les masquant ou en les remplaçant par des messages compréhensifs pour l’utilisateur.
Intercepter et gérer les erreurs présentes dans des formules Excel


Vous avez vu dans L’article « Erreur dans Excel » pourquoi sont produites les erreurs que contiennent vos formules comme #N/A, #VALEUR!, #REF!, #DIV/0!, #NOMBRE!, #NOM?, ou #NUL!, et comment les résoudre.

Parfois ces valeurs d’erreur s’affichent non parce que vos formules sont incorrectes, mais parce que les cellules référencées dans ces formules ne sont pas remplies de données indispensables pour effectuer les calculs nécessaires.

Une fois donc ces données sont saisies, aucune valeur d’erreur ne s’affichera.

C’est pour cela qu’il s’avère très important de gérer ces erreurs en les faisant masquer ou en les remplaçant par des messages sous forme d’expressions significatives pour rendre le travail attrayant.

Comment intercepter ou gérer les erreurs dans Excel ?

L’exemple suivant est un tableau de notes dans lequel nous avons créé des fonctions Moyennes.

Fonction moyenne effectuée


Vous remarquez que le calcul des moyennes est effectué car les notes des matières sont saisies.

Si l’utilisateur n’a pas encore saisi les notes des trois matières, le tableau sera affiché comme ça :

Erreur d'execution de la fonction Moyenne


Pour contourner donc cet affichage, nous allons masquer la valeur d’erreur #DIV/0! en utilisant la fonction SIERREUR.

Comment Insérer la fonction SIERREUR ?

La fonction SIERREUR fait partie des fonctions logiques d’Excel et qui est apparue avec la version d’Excel 2007. Pour l’insérer donc vous pouvez choisir parmi les 5 méthodes d’insertion de fonction expliquées dans l’article Les fonctions Excel : Définition, types et méthodes d’insertion.

Pour cet exemple, procédez ainsi :
  • Sélectionnez la cellule F2.
  • Dans la barre de formule cliquez à droite du signe égal puis tapez SIERREUR ou cliquez sur le nom de la fonction SIERREUR dans le menu des fonctions qui s’affiche.
Insertion de la fonction SIERREUR

  • Remarquez qu’Excel vous affiche aussi la syntaxe de cette fonction :
SIERREUR(Valeur ;Valeur_si_erreur)
  • Pour l’argument valeur, laissez la fonction Moyenne insérée.
  • Pour l’argument valeur_si_erreur, vous allez demander à Excel de rendre la cellule vide lorsque l’argument valeur renvoie une erreur.
  • L’argument valeur dans notre cas est la fonction Moyenne, et lorsqu’elle renvoie une erreur, la cellule qui la contient sera vide. Tapez donc "" .
  • Alors la cellule F2 contient la syntaxe suivante : =SIERREUR(MOYENNE(C2:E2);"")
Syntaxe de la fonction SIERREUR

  • Quand on valide la fonction, la valeur d’erreur #DIV/0 est masquée et la cellule F2 devient vide.

  • Copiez-collez la fonction SIERREUR dans les autres cellules, et votre tableau donnera cet affichage.
Erreurs masquées à l'aide de la fonction SIERREUR


Alors quand on saisit les notes des matières pour chaque étudiant, leur Moyenne est affichée. Si non, la cellule reste vide.

Exemple d'utilisation de la fonction SIERREUR


Il nous reste maintenant la cellule F13 qui contient la fonction qui calcule la moyenne de classe, n’est-ce pas ?!
Allez c’est votre tour maintenant ! (je vous laisse la solution tout en bas de cet article*)

Masquer la valeur d’erreur de la fonction RECHERCHV

Regardez l’exemple suivant :

Erreur NA dans RECHERCHV


Chaque cellule de la plage C5 :C10 contient la fonction RECHERCHV qui permet d’afficher les données de l’étudiant lorsqu’on tape son numéro dans la cellule C2.

Bien sûr ces données se trouvent dans le tableau de notes vu dans l’exemple précédent.

Vous voyez que ces cellules affichent l’erreur #N/A parce que nous n’avons rien écris dans la cellule C2.

  • Alors quand je tape le numéro 1, les données de l’étudiant ayant le numéro 1 s’affichent.
  • Si je tape un numéro qui n’existe pas dans notre base de données (le tableau de notes), Excel affiche encore l’erreur #N/A.
Exemple d'utilisation de RECHERCHV



Pour rendre donc ce jeu très logique et non nuisible pour l’utilisateur, nous allons utiliser aussi la fonction SIERREUR.

  • Alors, nous sélectionnons la cellule C5 et nous tapons F2 dans le clavier.
  • Excel nous donne la main pour modifier notre fonction.
  • Nous tapons donc directement à droite du signe égal : SIERREUR(
  • Puis nous laissons la fonction RECHERCHV intacte et nous tapons point-virgule à la fin.
  • Maintenant nous allons entrer le message qui va s’afficher à la place de la valeur d’erreur #N/A
  • Nous écrivons par exemple : « Le numéro d’étudiant est manquant ou n'est pas valide !» , et quand l’utilisateur lit ce message ; il va découvrir rapidement la cause du problème : soit la cellule C2 est vide ou soit le numéro saisi est incorrect ou ne se trouve pas dans la base de données.
  • Enfin nous insérons la parenthèse fermante et nous validons par Entrée.
SIERREUR et RECHERCHV



  • Voici le résultat
Application de SIERREUR


  • Nous procédons de la même façon pour les autres cellules C6, C7, C8, C9 et C10 et nous remplaçons le message par le vide: ""
  • Et voici ce que nous obtiendrons :
Résultat RECHERCHV imbriquée dans SIERREUR



Que pensez-vous ?

L’avantage de la fonction SIERREUR

Excel fournit d’autres fonctions qui pourront vous aider à gérer les erreurs dans vos formules comme ESTERR et ESTERREUR … cependant la fonction SIERREUR est la plus préférée, parce que :

N°1 : la fonction SIERREUR gère toutes les valeurs d’erreurs :#N/A, #VALEUR!, #REF!, #DIV/0!, #NOMBRE!, #NOM?, ou #NUL!,  à la différence de la fonction ESTERR qui exclue l’erreur #N/A.

N°2 : la syntaxe de la fonction facilite notre travail.

Même si la fonction ESTERREUR gère elle aussi toutes les erreurs comme la fonction SIERREUR, son utilisation est nuisible.

En effet,

La syntaxe de ESTERREUR par exemple est la suivante =ESTERREUR(valeur) et pour gérer nos erreurs, on doit l’imbriquer dans une fonction SI.

La même chose est dite également pour la fonction ESTERR.

Et pour vous éclaircir les choses, voyons ceci :

Revenons à notre exemple précédent sur l’utilisation de la fonction RECHERCHV et essayons d’appliquer cette fonction ESTERREUR

  • Nous allons donc écrire dans la cellule C5 :

=SI(ESTERREUR(RECHERCHEV($C$2;Feuil1!$A$1:$F$11;2;0));" Le numéro d’étudiant est manquant ou n'est pas valide !";RECHERCHEV($C$2;Feuil1!$A$1:$F$11;2;0))

  • Vous remarquez que c’est trop long et perturbant !

Pourquoi nous devons donc imbriquer ESTERREUR dans SI ?

Parce que la fonction ESTERREUR ne fait que vérifier l’existence de l’erreur et renvoie le résultat VRAI si elle la trouve ou FAUX si elle ne la trouve pas.

Dans cet exemple, nous devons en premier vérifier si la fonction RECHERCHV renvoie une erreur ou non.

Et si elle renvoie une erreur, la cellule C5 va afficher le message (Le numéro d’étudiant est manquant ou n'est pas valide !). Et si au contraire, elle ne renvoie aucune erreur, la cellule C5 affiche le résultat en exécutant la fonction RECHERCHV de nouveau.

Et alors pour réduire tout ce travail, on a créé la fonction SIERREUR.

Fonction spéciale pour la valeur d’erreur #N/A

Vous pouvez gérer uniquement la valeur d’erreur #N/A, en utilisant une autre fonction que SIERREUR. Cette fonction est SI.NON.DISP

Sa syntaxe est la suivante :

=SI.NON.DISP(valeur, valeur_si_na)

 Et si nous l’appliquons dans notre exemple précédent, on va écrire dans la cellule C5 :

= SI.NON.DISP(RECHERCHEV($C$2;Feuil1!$A$1:$F$11;2;0));" Le numéro d’étudiant est manquant ou n'est pas valide !")

Utilisation de SI.NON.DISP


Note : la fonction SI.NON.DISP est apparue avec la version Excel 2013, et est disponible pour les versions récentes.

******************

*Solution : =SIERREUR(MOYENNE(F2:F11);"")

Les messages d'erreur liés aux formules incorrectes

L’article présent de la formation Excel traite le sujet des messages d’erreur qui s’affichent en cas d’utilisation incorrecte d'une formule. Il traite aussi le problème des formules correctement créées mais qui ne fonctionnent pas et comment le résoudre.
Messages d'erreurs excel


En cas d’erreur, vous pouvez remarquer que Excel n’affiche pas les valeurs d’erreur que nous avons vues dans l’article erreurs dans Excel : #N/A, #VALEUR!, #REF!, #DIV/0!, #NOMBRE!, #NOM?, ou #NUL!.
Mais il affiche des messages d’erreur sous formes de boîtes de dialogue.

Comme vous pouvez aussi remarquer que lorsque vous utilisez une formule, elle ne fonctionne pas et aucun message d’erreur n’est émis.

Formule incompréhensible !

Quand Excel ne comprend pas la formule que vous avez créée, il affiche ce message d’erreur.

Excel - Message erreur formule incompréhensible


Et lorsque vous cliquez sur OK, Excel sélectionne la partie de votre formule où il rencontre le problème.
Essayez donc de réviser ce que vous avez tapé et assurez-vous que vous avez bien respecté la syntaxe de la fonction utilisée et que votre formule ne contient aucun élément inapproprié.

Dans l’exemple suivant, j’ai utilisé la fonction NB.SI pour chercher combien de fois le "Produit A" s’est vendu, mais Excel m’affiche la même fenêtre en dessus.

Je clique sur OK et je détecte que dans la partie sélectionnée j’ai tapé virgule au lieu de point-virgule dans ma formule.

Erreur sélectionnée par Excel


Problème de liaison externe

Excel affiche un message d’alerte lorsque vous essayez d’ouvrir un classeur qui contient une ou plusieurs formules qui utilisent des références de cellules externes, c’est-à-dire appartenant à un autre classeur.

Message détection de liaison externe


Cliquez sur Mettre à jour pour mettre à jour votre formule, ou bien cliquez sur Ne pas mettre à jour pour annuler toute liaison avec la source externe.

Si la source externe, c’est-à-dire le classeur qui contient la référence de la cellule liée à votre formule est supprimé, déplacé ou renommé, et quand vous ouvrez votre classeur contenant votre formule, Excel affiche un message d’erreur de liaison.

Message erreur de liaison externe


En cliquant sur Continuer vous allez donc ignorer cette liaison.

Et si vous choisissez Modifier les liaisons, Excel vous affiche la fenêtre suivante :

Fenêtre Modifier les liaisons


Dans la zone source, Excel affiche le nom du classeur en liaison et en bas son emplacement.

A droite de cette zone, utilisez la commande qui vous convient pour résoudre votre problème :
  • Mettre à jour les valeurs et Modifier la source : ces deux commandes vous permettent de sélectionner votre classeur qui contient la référence de la cellule utilisée dans votre formule, si ce classeur est renommé ou déplacé.
  • Ouvrir la source : Cette commande essaie de vous rouvrir le classeur en liaison.
  • Rompre la liaison : pour annuler toute liaison avec ce classeur et dans ce cas votre formule sera remplacée par le dernier résultat affiché.

Note : Vous pouvez aussi afficher la boîte de dialogue Modifier la liaison à partir de l’onglet Données en cliquant sur Modifier les liens.

La commande Modifier les liens - Excel


Astuce :
Vous pouvez mettre à jour la liaison avec la référence de la cellule externe en procédant ainsi :
  • Sélectionnez la cellule contenant votre formule.
  • Dans la barre de formule, cliquez sur la formule puis tapez Entrée.
  • Dans la boîte de dialogue qui s’affiche, sélectionnez le classeur externe en liaison.


Problème de parenthèse fermante

Lorsqu’on utilise des parenthèses dans notre formule, on oublie parfois de taper la parenthèse fermante.
Dans ce cas Excel affiche un message d’erreur et vous propose sa correction.

Message erreur de parenthèse fermante - Excel


Cliquez donc sur OK pour confirmer.

La fonction ou la formule ne fonctionne pas !

Cas n°1

Vous créez la formule et quand vous tapez Entrée, elle reste affichée sans effectuer de calcul.

Formule ne fonctionne pas - Excel


Par exemple =(A1+A2+A3)/3 ne donne aucun résultat.

Vérifiez le format défini pour cette cellule, s’il est texte, vous devez le modifier en standard.

Sélectionner Format Standard


Voir Format de cellule pour plus de détails.

Cas n°2

La formule ne se met pas à jour automatiquement lors de changement des données qu’elle utilise.

Vérifiez dans les options Excel si le calcul automatique est désactivé.

  • Cliquez sur Fichier puis Options.
  • Ensuite cliquez sur Formules et cochez Automatique sous Calcul du classeur.
Calcul de classeur automatique activé



Cas n°3

Vous pouvez remarquer que votre formule affiche 0 même si vous utilisiez des fonctions simples comme la fonction Somme.

Dans ce cas vérifiiez si votre formule contient une référence circulaire. C’est-à-dire que la formule fait référence à la cellule dans laquelle elle se trouve.

Dans cet exemple =SOMME(A1:A4) la référence A4 est une référence circulaire.

Référence circulaire


Pour résoudre ce problème, il faut donc rectifier la formule en laissant la référence de la plage de cellules comme ça A1:A3.

Comment savoir si ma feuille de calcul contient des références circulaires ?

Pour savoir si votre feuille de calcul contient des références circulaires, cliquez sur Vérification des erreurs sous l’onglet Formules.

Dans le menu qui apparaît, sélectionnez Références circulaires, Excel affiche une liste de ces références.

Cliquez sur la référence circulaire voulue et Excel la sélectionnera pour vous.

Menu Références circulaires - Excel


Note : lorsque la commande Références circulaires est désactivée, cela signifie qu’il n’existe aucune référence circulaire dans votre feuille.

Commande références circulaires désactivées



Les erreurs dans Excel

Dans cet article de la formation Excel, vous allez découvrir les types d'erreurs qui surviennent pendant votre travail sur un tableau et précisément lorsque vous utilisez des formules. Vous allez aussi voir deux méthodes pour rechercher les erreurs dans de grands tableaux Excel.
Erreurs excel


Nous avons vu dans les articles précédents les formules et les fonctions et comment les créer, nous avons parlé aussi de la syntaxe d’une fonction qui se compose d’éléments différents.

Alors, lorsque vous aurez mal utilisé une fonction ou une formule : son nom n’est pas écris correctement, une parenthèse qui manque, des arguments inappropriés ; Excel vous affichera un message d’erreur. Ce message est un texte spécial qui remplace le résultat que vous estimez avoir dans la cellule contenant votre formule.

Par exemple, Excel peut afficher #DIV/0! ; #Nom? … etc.

Ces expressions vous semblent bizarre ?! Ne vous en faites pas ! Il vous suffit tout simplement de connaître leurs significations et quand est-ce que se produisent-elles pour corriger les erreurs rencontrées.

Dans les lignes qui suivent, vous allez découvrir les types d’erreurs que peut afficher Excel.

Que remarquez-vous quand Excel affiche un message d’erreur ?

Prenez ce simple exemple :

La cellule A3 contient le message d’erreur #DIV/0! avec un tout petit triangle vert dans son coin supérieur gauche, et à sa côte s’affiche un losange jaune avec un point d’exclamation.

message erreur excel


Lorsque vous amenez le pointeur de la souris sur ce losange, Excel vous affiche une info-bulle qui vous indique le type d’erreur commise.

Pour notre exemple : Excel affiche donc :

La formule ou la fonction utilisée effectue une division par zéro ou par des cellules vides.

Info-bulle erreur excel


Si vous cliquez également sur ce losange, Excel affiche un menu où vous voyez la définition de l’erreur affichée avec d’autres options en rapport avec cette erreur.

Menu erreur excel


Remarquez en plus que la barre de formule affiche toujours la fonction utilisée sans être écrasée par le message d’erreur, ce qui vous rend facile sa rectification.

Barre de formule et erreur excel


Allons maintenant découvrir les types d’erreurs dans Excel.

Les types d’erreurs dans Excel

Voici les différents types d'erreurs susceptibles de se présenter lors de l’utilisation des formules Excel, et comment vous allez procéder pour résoudre les problèmes qui peuvent se poser.

Erreur #DIV/0! 

Comme vous l’avez constaté, cette erreur s'est produite lors d'une division par 0. Cherchez donc dans votre formule saisie la référence de la cellule qui renvoie une valeur nulle.

erreur #DIV

Erreur #NOM?

Cette erreur apparaît lorsque :

  • Vous n’avez pas saisi correctement le nom de votre formule :
Erreur #NOM



Dans cet exemple le nom de la fonction Moyenne est écrit avec un seul « n ».
  • La référence de la cellule utilisée dans la formule n’est pas bien écrite :
Erreur #NOM référence incorrect



Ici nous avons écrit E au lieu de E2.

Et ici nous n’avons pas bien saisi le nom de la plage des cellules utilisée : ListeMoyennes au lieu de ListeDesMoyennes

Erreur #NOM nom plage incorrect


Pour résoudre ce genre de problème, sélectionnez le nom de la plage de cellules dans la liste Zone de nom puis collez-le dans votre formule.

Erreur #Nom solution


Ou bien, sélectionnez le nom erroné dans votre formule puis cliquez sur l’onglet Formules, et dans le groupe Noms définis cliquez sur Dans une formule ensuite cliquez sur le nom de la plage de cellules convenable.

Erreur #Nom Solution utilisant le bouton Dans une formule


  • Il manque des guillemets pour des valeurs de type texte dans l’un des arguments d’une formule.
Erreur #NOM problème guillemets



Ici nous avons oublié les guillemets pour les valeurs Oui et Non.

  • Vous avez omis les deux points dans une référence de plage de cellules.

Regardez l’exemple suivant où nous avons supprimé les deux points dans la référence de la plage de cellules B2:B11

Erreur #NOM problèmes dans référence plage


Erreur #REF!

Elle est liée au problème avec des références de cellules utilisées dans une formule, lorsque ces références sont supprimées, déplacées ou remplacées.

Dans l’exemple suivant on a calculé le montant TTC, dans la colonne D, en multipliant le montant HT, dans la colonne C, par 1,2.

Formule pour calculer le Montant TTC


Et si on supprime la colonne C nous obtiendrons l’erreur #REF!

Erreur #REF


Remarquez aussi que la barre de formule indique qu’il y a une référence de cellule non valide.

Erreur #NOMBRE!

  • Cette erreur apparaît lorsque les valeurs numériques utilisées dans une formule ou une fonction ne sont pas valides.


Par exemple la fonction PETITE.VALEUR renvoie ici une erreur #NOMBRE!, parce que dans ses arguments nous avons défini la référence de la plage de cellules B2:B6 qui contient des valeurs textuelles au lieu d’insérer la référence de la plage de cellules qui contient des nombres.

Erreur #Nombre problème et solution


  • En plus, l’erreur #NOMBRE! s’affiche si le résultat produit par une formule renvoie un nombre trop grand ou trop petit. Le résultat obtenu doit être entre -1*10^307 et 1*10^307.


Erreur #Nombre problème nombre trop grand


  • L’erreur #NOMBRE! apparaît également si la fonction produit une itération.

Erreur #N/A

L’erreur #N/A se produit lorsqu’il manque une valeur pour une formule ou une fonction. Cette erreur apparaît le plus souvent avec les fonctions RECHRECHV, RECHERCHH, RECHERCH ou EQUIV. Nous traiterons ces fonctions après.

Erreur #N_A


Erreur #VALEUR!

Lorsque cette erreur survient, vérifiez un argument ou un élément de votre formule qui est inapproprié.

  • Par exemple si vous faites la somme d’une valeur numérique et d’une valeur textuelle: =150+ "a", l'erreur #VALEUR! survient.
Erreur #Valeur


  • l'erreur #VALEUR! se produit aussi si un argument d’une formule fait référence à une valeur textuelle ou à une cellule qui contient seulement un espace. 
    • Dans cet exemple, la division affiche une erreur car la cellule F6 est vide sauf d’un espace.


Erreur #Valeur problème d'espace dans une cellule


  • L’erreur #VALEUR! s’affiche aussi si la cellule référencée dans une formule renvoie elle aussi une valeur d’erreur.


Erreur #Valeur problème erreur dans référence


  • Cette erreur se produit également s’il y a une erreur dans la syntaxe de la fonction utilisée.
  • Une autre cause qui fait afficher cette erreur est que la formule matricielle entrée est incorrecte.
  • On a fourni une plage de valeurs à un opérateur ou une fonction, alors qu’elle exige une valeur unique.

Erreur #NUL! :

Cette erreur se produit lorsque vous spécifiez deux zones qui ne se coupent pas.
Par exemple =SOMME(B2:B5 C2:C5)

Erreur #NUL


Dans ce cas, ajoutez un point-virgule entre les deux références des deux plages de cellules pour que la fonction effectue le calcul souhaité.

Erreur #NUL Solution


L’erreur existe, mais Excel n’affiche aucun message !

Ça pourrait vous arriver parfois, quand vous remarquez que le triangle vert et le losange Jaune apparaissent dans une cellule et que celle-ci ne contient aucun message d’erreur.

Cette erreur "silencieuse" peut survenir lorsque Excel détecte que dans une plage de cellules, il y a une cellule qui contient une formule différente d'une formule qui se répète dans les autres cellules de cette plage.

Erreur silencieuse Excel


Par exemple, dans ce tableau la cellule F6 contient une formule de somme, alors que les autres cellules de la même plage de cellules utilisent la fonction MOYENNE

Erreur existante sans message


Comment chercher les erreurs dans une feuille Excel ?

Si vous voulez vous assurer que vous avez corrigé toutes les erreurs survenues, surtout si vous travaillez sur de grands tableaux qui contiennent plusieurs formules ou fonctions, vous pouvez procéder de deux façons :
  • Cliquez sur une cellule de votre tableau puis sous l’onglet Accueil et dans le groupe Edition, cliquez sur Rechercher et sélectionner puis cliquez sur Sélectionnez les cellules.
Menu Rechercher et Sélectionner dans Excel

    • Dans la boîte de dialogue qui apparaît, cochez Formules, ensuite laissez seulement le choix Erreurs coché puis cliquez sur OK .
Chercher Erreurs des formules