Excel : comment gérer les erreurs à l'aide de la fonction SIERREUR
Ce tutoriel explique comment utiliser la fonction SIERREUR dans Excel pour détecter les erreurs et les remplacer par une cellule vide, une autre valeur ou un message personnalisé. Vous apprendrez comment utiliser la fonction SIERREUR avec RECHERCHEV et INDEX EQUIV, et comment elle se compare à SI ESTERREUR.
Lorsque vous travaillez avec des données et des formules dans Excel, vous risquez de rencontrer des erreurs. Pour gérer ces erreurs, Excel a fourni une fonction utile - la fonction SIERREUR.
Avant d'entrer dans la mécanique de l'utilisation de la fonction SIERREUR dans Excel, examinons d'abord les différents types d'erreurs que vous pouvez rencontrer lorsque vous travaillez avec des formules.
Table des matières
Fonction SIERREUR Excel - syntaxe et utilisations de base
Si erreur, afficher un message
À retenir sur la fonction Excel SIERREUR
Exemple 1 - Renvoyer « Non trouvé » lorsque RECHERCHEV ne parvient pas à trouver une valeur
Exemple 2 - Renvoyer 0 en cas d'erreur
Exemple 3 : Fonctions SIERREUR imbriquées pour effectuer des RECHERCHEV séquentielles dans Excel
SIERREUR dans les formules matricielles
Types d'erreurs dans Excel
Connaître les erreurs dans Excel vous permettra de mieux identifier la cause potentielle et la meilleure façon de la gérer. Vous trouverez ci-dessous les types d'erreurs que vous pourriez trouver dans Excel.
Erreur # N / A
C'est ce qu'on appelle l'erreur « Valeur non disponible ».
Vous verrez cette erreur lorsque vous utilisez une formule de recherche et que celle-ci ne peut pas trouver la valeur (donc non disponible).
Vous trouverez ci-dessous un exemple dans lequel j'ai utilisé la formule RECHERCHEV pour trouver le prix d'un produit, mais qui renvoie une erreur lorsqu'elle ne trouve pas cet article dans le tableau.
Erreur # DIV / 0!
Vous verrez probablement cette erreur lorsqu'un nombre est divisé par 0.
C'est ce qu'on appelle l'erreur de division. Dans l'exemple ci-dessous, la formule A4/B4 donne # DIV / 0! car la valeur de la quantité (le diviseur dans la formule) est 0.
Erreur #VALEUR!
L'erreur de valeur se produit lorsque vous utilisez un type de données incorrect dans une formule.
Par exemple, dans la capture ci-dessous, lorsque j'essaie d'ajouter des cellules contenant des nombres et le caractère A, cela donne une erreur de valeur.
Cette erreur se produit car vous ne pouvez ajouter que des valeurs numériques, mais à la place, j'ai essayé d'ajouter un nombre avec un caractère de texte.
Erreur #REF!
C'est ce qu'on appelle l'erreur de référence et vous la verrez lorsque la référence dans la formule n'est plus valide. Cela peut être le cas lorsque la formule fait référence à une cellule et que cette référence de cellule n'existe pas (se produit lorsque vous supprimez une ligne / colonne ou feuille de calcul à laquelle la formule fait référence).
Dans l'exemple ci-dessous, alors que la formule d'origine était =A2 / B2, lorsque j'ai supprimé la colonne B, toutes les références à celle-ci sont devenues #REF!. Ainsi, j’ai également obtenu #REF! dans le résultat de la formule.
Erreur #NOM?
Cette erreur est probablement due à une fonction mal orthographiée.
Par exemple, si au lieu de RECHERCHEV, vous écrivez par erreur RECHERCHV, cela donnera une erreur de nom.
Erreur #NOMBRE
Ce type d’erreur peut se produire si vous essayez de calculer une très grande valeur dans Excel. Par exemple, =187 ^ 549 renverra une erreur de nombre.
Une autre situation où vous pouvez obtenir l'erreur NOMBRE est lorsque vous donnez un argument numérique non valide à une formule. Par exemple, si vous calculez la racine carrée d’un nombre et que vous donnez un nombre négatif comme argument, la formule renverra une erreur numérique.
Bien que je n’aie cité que quelques exemples ici, il peut y avoir de nombreuses autres raisons pouvant entraîner des erreurs dans Excel.
Lorsque vous obtenez des erreurs dans Excel, vous ne pouvez pas les laisser dans votre feuille de calcul. Si les données sont utilisées dans les calculs, vous devez vous assurer que les erreurs sont gérées correctement.
La fonction SIERREUR est un excellent moyen de gérer tous les types d'erreurs dans Excel.
Fonction SIERREUR Excel - syntaxe et utilisations de base
La fonction SIERREUR dans Excel est conçue pour intercepter et gérer les erreurs dans les formules et les calculs. Plus spécifiquement, SIERREUR vérifie une formule et, si elle évalue une erreur, renvoie une autre valeur que vous spécifiez; sinon, elle renvoie le résultat de la formule.
La syntaxe de la fonction Excel SIERREUR est la suivante :
SIERREUR(valeur ; valeur_si_erreur)
Où :
- Valeur (obligatoire) - ce qu'il faut vérifier pour les erreurs. Il peut s'agir d'une formule, d'une expression, d'une valeur ou d'une référence de cellule.
- Valeur_si_erreur (obligatoire) - que renvoyer si une erreur est trouvée. Il peut s'agir d'une chaîne vide (cellule vide), d'un message texte, d'une valeur numérique, d'une autre formule ou d'un calcul.
Par exemple, lorsque vous divisez deux colonnes de nombres, vous pouvez obtenir un tas d'erreurs différentes si l'une des colonnes contient des cellules vides, des zéros ou du texte.
Pour éviter que cela ne se produise, utilisez la fonction SIERREUR pour détecter et gérer les erreurs comme vous le souhaitez.
Si erreur, laisser vide
Fournissez une chaîne vide ("") dans l'argument « valeur_si_erreur » pour renvoyer une cellule vide si une erreur est trouvée :
=SIERREUR(A2 / B2; "")
Si erreur, afficher un message
Vous pouvez également afficher votre propre message au lieu de la notation d'erreur standard d'Excel :
=SIERREUR(A2 / B2; "Erreur de calcul")
Gérer l’erreur #NOM?
Au lieu de SOMME, si l'utilisateur écrit la mauvaise formule, une erreur #NOM? sera générée.
En utilisant la fonction SIERREUR, nous pouvons gérer cette erreur en transmettant simplement au deuxième argument une valeur, par exemple, « Le nom de la fonction est mal saisi ».
Gérer l’erreur #NUL!
Dans cet exemple, nous avons 3 valeurs dans les cellules A1, A2 et A3 et nous souhaitons les additionner.
Dans la cellule A5, nous avons utilisé l'opérateur plus « + » pour effectuer le calcul. Et au lieu de placer l'opérateur «+» également entre A2 et A3, nous avons entré un séparateur en appuyant sur espace (A1 + A2 A3). Nous obtenons ainsi #NUL !.
Donc, pour corriger cette erreur, nous pouvons utiliser la fonction SIERREUR en changeant la formule comme suit :
=SIERREUR((A1 + A2 A3); SOMME(A1:A3))
Vous pouvez voir que nous avons utilisé une autre formule SOMME pour calculer la somme des trois valeurs données et l'avons passée comme deuxième argument dans la fonction SIERREUR.
Gestion de l'erreur #NOMBRE
Supposons que nous avons des nombres dans la colonne A et nous voulons trouver les racines carrées de ces nombres dans la colonne B.
Dans Excel, nous avons la fonction RACINE pour calculer la racine carrée d'un nombre. Lorsque nous appliquons cette formule pour calculer la racine carrée des nombres, nous obtenons l'erreur #NOMBRE! pour les valeurs négatives, car nous ne pouvons pas trouver la racine carrée d'un nombre négatif.
En utilisant la fonction SIERREUR, nous pouvons passer un message « Le nombre est négatif » pour les valeurs négatives afin éviter cette erreur :
Gestion de l’erreur #VALEUR!
Dans cet exemple, la cellule A1 contient une valeur numérique tandis que la cellule A2 contient une valeur de texte. Lorsque nous ajoutons ces deux valeurs, nous obtenons une erreur #VALEUR!.
Nous pouvons corriger cette erreur en utilisant la formule SIERREUR comme suit :
Ainsi, nous pouvons voir que la fonction SIERREUR pourrait être très utile dans les erreurs d'Excel et la personnalisation de la sortie lorsqu'une erreur se produit selon les besoins ou les exigences.
À retenir sur la fonction Excel SIERREUR
- Si vous utilisez "" comme argument « valeur_si_erreur », la cellule n'affiche rien en cas d'erreur.
- Si l'argument « valeur » ou « valeur_si_erreur » fait référence à une cellule vide, il est traité comme une valeur de chaîne vide par la fonction SIERREUR d'Excel.
- Si l'argument « valeur » est une formule matricielle, la fonction SIERREUR retournera un tableau de résultats pour chaque élément de la plage spécifiée.
- SIERREUR a été introduit dans Excel 2007 et est disponible dans toutes les versions ultérieures d'Excel 2010, Excel 2013 et Excel 2016.
- Pour intercepter les erreurs dans Excel 2003 et les versions antérieures, utilisez la fonction ESTERREUR en combinaison avec SI.
Exemples de formules SIERREUR
Les exemples suivants montrent comment utiliser SIERREUR dans Excel en combinaison avec d'autres fonctions pour accomplir des tâches plus complexes.
Exemple 1 - Renvoyer « Non trouvé » lorsque RECHERCHEV ne parvient pas à trouver une valeur
Lorsque vous utilisez la fonction RECHERCHEV Excel et qu'elle ne trouve pas la valeur de recherche dans la plage spécifiée, elle renvoie l'erreur # N / A.
Par exemple, vous trouverez ci-dessous un ensemble de données sur les noms des élèves et leurs notes. Nous avons utilisé la fonction RECHERCHEV pour récupérer les notes de trois étudiants (en D2, D3 et D4).
Bien que la formule RECHERCHEV dans cet exemple trouve les noms des deux premiers étudiants, elle ne trouve pas le nom de « Sihame » dans la liste et renvoie donc l'erreur # N/A.
Ici, nous pouvons utiliser la fonction SIERREUR pour renvoyer un blanc ou un texte significatif au lieu de l'erreur.
Vous trouverez ci-dessous la formule qui renverra "Non trouvé" au lieu de l'erreur par défaut d’Excel :
=SIERREUR(RECHERCHEV(D2; $A$2:$B$12; 2; 0); "Non trouvé")
Exemple 2 - Renvoyer 0 en cas d'erreur
Si vous ne spécifiez pas la valeur à renvoyer par SIERREUR en cas d'erreur, elle renverra automatiquement 0.
Par exemple, si nous divisons 100 par 0 comme indiqué ci-dessous, nous aurons une erreur.
Cependant, si nous utilisons la fonction SIERREUR comme le montre la capture d’écran ci-dessous, elle renverra un 0 à la place de l’erreur par défaut. Notez que vous devez toujours utiliser un point-virgule après le premier argument.
Exemple 3 : Fonctions SIERREUR imbriquées pour effectuer des RECHERCHEV séquentielles dans Excel
Parfois, lorsque vous utilisez RECHERCHEV, vous devrez peut-être parcourir plusieurs tableaux de données pour effectuer la recherche. Par exemple, si vous avez les enregistrements de transaction de vente dans 2 feuilles de calcul distinctes et vous souhaitiez rechercher un numéro d'article et voir sa valeur. Cela nécessite l'utilisation de la fonction SIERREUR imbriquée avec RECHERCHEV.
Supposons que vous ayez un ensemble de données comme indiqué ci-dessous :
Dans ce cas, pour trouver la note de Loubna, vous devez utiliser la formule SIERREUR imbriquée suivante :
=SIERREUR(RECHERCHEV(D8; $A$2:$B$5; 2; 0); SIERREUR(RECHERCHEV(D8; $D$2:$E$5; 2; 0); "Non trouvé"))
Ce type d'imbrication de formule garantit que vous obtenez la valeur de l'une des tables et que toute erreur renvoyée est gérée.
Notez que dans cet exemple les tableaux sont sur la même feuille de calcul, cependant, dans un exemple réel, il est probable que ce soit sur des feuilles de calcul différentes.
SIERREUR dans les formules matricielles
Comme vous pouvez le savoir, les formules matricielles dans Excel sont destinées à effectuer plusieurs calculs dans une seule formule. Si vous fournissez une formule matricielle ou une expression qui aboutit à un tableau dans l'argument valeur de la fonction SIERREUR, elle renverra un tableau de valeurs pour chaque cellule de la plage spécifiée. L'exemple ci-dessous montre les détails.
Disons que vous avez Total dans la colonne B et Prix dans la colonne C, et que vous souhaitez calculer la quantité totale. Cela peut être fait en utilisant la formule matricielle suivante, qui divise chaque cellule de la plage B2:B4 par la cellule correspondante de la plage C2:C4, puis additionne les résultats :
=SOMME($B$2:$B$4 / $C$2:$C$4)
La formule fonctionne correctement tant que la plage de diviseurs ne contient pas de zéros ou de cellules vides. S'il y a au moins une valeur 0 ou une cellule vide, l’erreur #DIV/0! est renvoyée :
Pour corriger cette erreur, effectuez simplement la division dans la fonction SIERREUR :
=SOMME(SIERREUR($B$2:$B$4 / $C$2:$C$4; 0))
Ce que fait réellement la formule est de diviser une valeur dans la colonne B par une valeur dans la colonne C dans chaque ligne (100/10, 116/8 et 0/0) et renvoyer le tableau de résultats {10; 14,5; # DIV/0!}. La fonction SIERREUR intercepte toutes les erreurs #DIV/0! et les remplace par des zéros. Et puis, la fonction SOMME additionne les valeurs dans le tableau résultant {10; 14,5; 0} et affiche le résultat final (10 + 14,5 = 24,5).
Remarque : n'oubliez pas que les formules matricielles doivent être complétées en appuyant sur le raccourci Ctrl + Shift + Entrée.
SIERREUR contre SI ESTERREUR
Maintenant que vous savez à quel point il est facile d'utiliser la fonction SIERREUR dans Excel, vous vous demandez peut-être pourquoi certaines personnes ont encore tendance à utiliser la combinaison SI ESTERREUR. Alors, est-ce qu’elle a des avantages par rapport à SIERREUR ? La réponse est : Non. En effet, dans Excel 2003 et les versions antérieures, lorsque SIERREUR n'existait pas, la combinaison des fonctions SI et ESTERREURétait le seul moyen possible de piéger les erreurs. Dans Excel 2007 et versions ultérieures, il s'agit d'un moyen un peu plus complexe d'obtenir le même résultat.
La syntaxe est simple et ne nécessite qu'un seul argument.
« =ESTERREUR(valeur) »
La « valeur » est simplement là où nous recherchons une erreur.
Par exemple, pour détecter les erreurs RECHERCHEV, vous pouvez utiliser l'une des formules ci-dessous.
Dans Excel 2007 - Excel 2016 :
SIERREUR(RECHERCHEV(…); "Non trouvé")
Dans toutes les versions d'Excel :
SI(ESTERREUR(RECHERCHEV(…)); "Non trouvé”; RECHERCHEV(…))
Notez que dans la formule SI ESTERREUR RECHERCHEV, vous devez utiliser RECHERCHEV deux fois. En clair, la formule peut être lue comme suit :
Si RECHERCHEV génère une erreur, renvoyer "Non trouvé", sinon afficher le résultat RECHERCHEV.
Et voici un exemple réel d'une formule Excel SI ESTERREUR RECHERCHEV :
=SI(ESTERREUR(RECHERCHEV(D2; A2:B5; 2; FAUX)); "Non trouvé"; RECHERCHEV(D2; A2:B5;2; FAUX))
À présent, vous savez déjà que la fonction SIERREUR est le moyen le plus simple de détecter les erreurs dans Excel et de les masquer avec des cellules vides, des valeurs nulles ou des messages personnalisés de votre choix. Cependant, cela ne signifie pas que vous devez encapsuler chaque formule avec une gestion des erreurs. Les recommandations suivantes peuvent vous aider à garder l'équilibre :
- Ne piégez pas les erreurs sans raison.
- Enveloppez la plus petite partie possible d'une formule dans SIERREUR.
- Pour gérer uniquement des erreurs spécifiques, utilisez une fonction de gestion des erreurs avec une portée plus petite:
- SI ESTNA pour détecter uniquement les erreurs # N/A.
- ESTERR pour détecter toutes les erreurs à l'exception de # N/A.
Télécharger le fichier Excel de ce tutoriel
Article publié le 01 Août 2020par Hanane Mouqqadim