Comment modifier le résultat d’une formule renvoyant une erreur : la fonction SIERREUR() d’Excel ?
Dans ce tutoriel, nous allons découvrir la fonction SIERREUR() d’Excel qui permet de contrôler si le résultat d’une formule est une erreur afin de pouvoir renvoyer un résultat différent.
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
La fonction SIERREUR() que nous allons découvrir dans ce tutoriel permet de détecter si une formule ou un résultat quelconque retourne une erreur Excel.
Une fonction retourne une erreur lorsqu’Excel n’est pas en mesure de calculer correctement son résultat avec les informations passées en argument.
Il existe plusieurs cas qui peuvent conduire Excel à générer une erreur :
- L’erreur la plus courante est l’erreur #N/A. Celle-ci signifie qu’un élément permettant d’effectuer une recherche n’est pas accessible. Nous retrouverons notamment cette erreur dans le cadre de l’utilisation d’une fonction de recherche (EQUIV(), RECHERCHEV(),…) si la valeur à identifier n’existe pas dans la plage de recherche,
- Ensuite nous pouvons également être régulièrement confronté à l’erreur #DIV/0. Dans ce cas-là, Excel nous informe que nous avons tenté d’effectuer une division d’un nombre quelconque par zéro, ce qui n’est évidemment pas permis par Excel,
- L’erreur #VALEUR! Signifie quant à elle que nous avons tenté d’effectuer un calcul avec une valeur inappropriée (typiquement une valeur de type texte utilisée à la place d’une valeur numérique)
- Les autres erreurs renvoyées par Excel sont : #NUL!, #REF!, #NOM? et #NOMBRE!
Parfois, nous pouvons anticiper le fait qu’Excel nous présente ces erreurs afin de modifier le comportement à adopter.
Et c’est justement là qu’intervient la fonction SIERREUR().
2. Exemples
2.1. Capter l’erreur #DIV/0!
Pour comprendre l’intérêt de la fonction SIERREUR(), nous allons étudier l’exemple suivant dans lequel nous retrouvons une analyse des ventes réalisées par une entreprise au cours des années 2019 et 2020 :
Nous retrouvons ici une table de synthèse de ces ventes avec le montant du CA réalisé par cette entreprise au cours des deux années, pour les quatre produits A, B, C et D.
Dans la dernière colonne, nous cherchons à calculer la variation en pourcentage du CA réalisée entre ces deux années, mais comme nous pouvons le constater, étant donné que le produit B est un tout nouveau produit lancé au cours de l’année 2020, Excel nous retourne une erreur au lieu de la variation correspondante.
Plutôt que d’afficher cette erreur disgracieuse, nous préfèrerons informer le lecteur qu’il s’agit d’un nouveau produit.
Et c’est à cela que sert la fonction SIERREUR().
Celle-ci attend deux arguments
=SIERREUR(valeur;valeur_si_erreur)
Où :
- Valeur : correspond à la valeur que nous souhaitons insérer dans la cellule,
- Valeur_si_erreur : correspond à la valeur alternative à afficher uniquement lorsque le premier argument est une erreur. Cette valeur alternative peut être un nombre, un texte, un résultat retourné par une formule, … Si l’argument valeur n’est pas une erreur, alors c’est lui sera retourné par la fonction, et Valeur_si_erreur sera alors annulé. Toutes les erreurs énumérées dans la première partie de ce tutoriel (#N/A, #VALEUR!, #REF!, #DIV/0!, #NOMBRE!, #NOM?, ou #NUL!) sont prises en compte avec la fonction SIERREUR().
Pour en revenir à notre exemple, si nous souhaitons ne pas afficher l’erreur #DIV/0!, nous pourrons donc remplacer la formule :
=(C8-D8)/D8
Par :
=SIERREUR((C8-D8)/D8;"Nouveau")
De cette manière, Excel affichera le message correspondant (« Nouveau ») à la place de l’erreur.
2.2. Capter l’erreur #N/A!
Pour l’exemple suivant, nous cherchons à récupérer le montant du CA réalisé par un produit donné, ici le produit E :
Comme nous pouvons le constater, Excel nous retourne une erreur #N/A!, étant donné que le produit E ne figure pas dans la base de données…
Il suffit en effet de saisir la référence d’un autre article pour faire disparaître cette erreur et récupérer le montant du CA correspond :
Comme nous l’avons vu au cours de l’exemple précédent, nous pouvons utiliser la fonction SIERREUR() pour intercepter cette erreur et ainsi afficher un message personnalisé :
=SIERREUR(RECHERCHEV(B15;A8:C11;3;FAUX);"Article "&B15&" inconnu !")
Maintenant, si nous remettons la référence de l’article E, Excel nous affichera une fois encore le message correspondant :
2.3. Capter l’erreur #VALEUR!
Pour notre dernier exemple, nous allons souhaiter déterminer le montant du CA HT, à partir du montant TTC connu et du taux de TVA effectif de l’entreprise qui s’élève à 20%.
Nous avons déjà eu l’occasion de découvrir comment calculer un montant HT à partir d’un montant TTC dans un tutoriel précédent que vous trouverez en cliquant ici.
Pour rappel, la formule à utiliser est la suivante :
Montant HT = Montant TTC / (1+Taux de TVA)
La formule est bien insérée dans la cellule B21, mais nous pouvons constater que celle-ci nous retourne une erreur #VALEUR!, ce qui signifie que l’un des éléments de la formule n’est pas reconnu par Excel comme étant une valeur numérique :
(L’erreur vient de l’utilisation du point en tant que séparateur de décimales, à la place de la virgule)
Nous allons donc pouvoir utiliser la fonction SIERREUR() informer l’utilisateur du problème rencontré.
Nous modifions alors la formule :
=B19/(1+B20)
Par :
=SIERREUR(B19/(1+B20);"Valeur non numérique")
3. Alternative à la fonction SIERREUR()
Il faut savoir que la fonction SIERREUR() que nous venons de découvrir est un raccourci qui permet de gagner du temps lors de la saisie de la formule.
Nous pouvons en effet obtenir le même résultat en utilisant les fonctions SI() et ESTERREUR().
Nous avons déjà eu l’occasion d’aborder ces deux fonctions lors du tutoriel précédent :
- La fonction SI() permet d’effectuer un test logique afin de retourner une valeur donnée si le résultat de ce test est vrai et une autre valeur si ce résultat est faux,
- La fonction ESTERREUR() permet de savoir si la valeur passée en argument est une erreur. Il s’agit d’une fonction booléenne qui renverra la valeur VRAI si l’argument est reconnu comme étant une erreur, et FAUX dans le cas contraire.
À partir de là, nous voyons clairement il est possible de construire la formule équivalente à SIERREUR() :
=SI(ESTERREUR(valeur);valeur;valeur_si_erreur)
L’avantage c’est que nous pouvons utiliser la formule sur une autre cellule
En revanche et dans dehors du cas que nous venons de voir à l’instant, son inconvénient c’est qu’elle est beaucoup plus lourde à construire, pour arriver au résultat obtenu avec la fonction SIERREUR().
Il est notamment nécessaire de venir y saisir deux fois la même information (la valeur dont nous voulons savoir s'il s’agit d’une erreur, et le résultat à retourner lorsque c’est le cas sont en effet la même information).
Alors, pourquoi s’en passer ?