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.

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.

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.

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);"")

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 :

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.

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.

Voici le résultat

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 !")

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);"")