=SIERREUR(B2/C2;0) renvoie le résultat de B2/C2, ou 0 quand ce résultat est une erreur. Le premier argument est la formule voulue ; le second est ce qu'il faut afficher à la place de toute erreur qu'elle produit. SIERREUR s'appelle IFERROR dans un Excel anglais, et le tableau affiche la formule ainsi : =IFERROR(B2/C2,0). Vous pouvez aussi y taper les formules en français, avec des points-virgules.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Revenue | Units | Plain | With IFERROR |
| 2 | Pens | $120 | 80 | $1.50 | $1.50 |
| 3 | Paper | $300 | 50 | $6.00 | $6.00 |
| 4 | Ink | $90 | 0 | #DIV/0! | $0.00 |
| 5 | Tape | $45 | 30 | $1.50 | $1.50 |
| 6 | Clips | $0 | 0 | #DIV/0! | $0.00 |
=SIERREUR(B2/C2;0)Ink et Clips ont 0 unité, donc la division simple de la colonne D affiche #DIV/0!. La colonne E affiche $0.00 pour ces lignes et le prix normal pour toutes les autres. Tapez 15 en C4 et les deux colonnes affichent le prix de Ink.
Syntaxe de SIERREUR
=IFERROR(value, value_if_error)
value(valeur) est la formule à calculer.value_if_error(valeur_si_erreur) est renvoyé quandvalueest une erreur, quelle qu'elle soit : #N/A, #VALEUR! (en anglais #VALUE!), #REF!, #DIV/0!, #NOMBRE! (#NUM!), #NOM? (#NAME?), #NUL! (#NULL!), et les plus récentes comme #CALC!. Le tableau affiche les noms d'erreur anglais.- Si
valuen'est pas une erreur, SIERREUR la renvoie telle quelle.
Le remplacement peut être un nombre (0), du texte ("Not found"), un texte vide ("") ou une autre formule, par exemple une seconde recherche dans une autre table : =SIERREUR(RECHERCHEV(E2;A2:C6;3;FAUX);RECHERCHEV(E2;G2:I6;3;FAUX)).
SIERREUR avec RECHERCHEV
Une recherche renvoie #N/A quand la valeur n'est pas dans la table. L'entourer de SIERREUR affiche un message à la place. RECHERCHEV s'appelle VLOOKUP en anglais :
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | Pear | $1.50 | |
| 3 | Pear | Fruit | $1.50 | Kiwi | Not found | |
| 4 | Carrot | Vegetable | $0.80 | Milk | $1.10 | |
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
=SIERREUR(RECHERCHEV(E2;$A$2:$C$6;3;FAUX);"Not found")Kiwi n'est pas dans la liste, donc F3 affiche Not found. Tapez Kiwi en A4 à la place de Carrot et F3 le trouve. Avec RECHERCHEX (XLOOKUP en anglais), SIERREUR est inutile ici, car son quatrième argument est la valeur "non trouvé" : =RECHERCHEX(E2;A2:A6;C2:C6;"Not found").
SI.NON.DISP : n'intercepter que #N/A
SI.NON.DISP (IFNA en anglais) fonctionne comme SIERREUR mais ne remplace que #N/A. Pour les recherches, c'est généralement ce qu'il faut : #N/A signifie "non trouvé", une réponse normale, alors que toute autre erreur signifie que la formule elle-même est fausse. Dans ce tableau, les formules demandent la colonne 4 d'une table de trois colonnes, une faute de frappe :
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Look for | IFERROR | IFNA | |
| 2 | Apple | Fruit | 1.2 | Pear | Not found | #REF! | |
| 3 | Pear | Fruit | 1.5 | ||||
| 4 | Carrot | Vegetable | 0.8 | ||||
| 5 | Bread | Bakery | 2.4 | ||||
| 6 | Milk | Dairy | 1.1 |
=SIERREUR(RECHERCHEV(E2;$A$2:$C$6;4;FAUX);"Not found")Pear est dans la table, et pourtant F2 affiche Not found : SIERREUR a transformé le #REF! dû au mauvais numéro de colonne en même message qu'un produit absent. G2 laisse passer le #REF!, et vous voyez que la formule est cassée. Remplacez le 4 par 3 en G2 et elle renvoie 1.5. SI.NON.DISP demande Excel 2013 ou une version ultérieure.
Renvoyer une cellule vide au lieu d'une erreur
Pour n'afficher rien, utilisez un texte vide, deux guillemets, comme remplacement :
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Last year | This year | Growth |
| 2 | Jan | 200 | 240 | 20% |
| 3 | Feb | 0 | 150 | |
| 4 | Mar | 180 | 171 | -5% |
| 5 | Apr | 90 | ||
| 6 | May | 250 | 300 | 20% |
=SIERREUR((C2-B2)/B2;"")Février et avril n'avaient pas de ventes l'an dernier : leur croissance ne peut pas être calculée et la cellule reste vide. Les autres mois affichent 20%, -5% et 20%. Une cellule avec "" contient du texte : SOMME et MOYENNE l'ignorent, mais =D3*2 donne #VALEUR!. Si d'autres formules font des calculs sur la colonne, renvoyez plutôt 0.
Exercice : une recherche avec solution de repli
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Stock | Look for | Stock | ||
| 2 | Apple | 40 | Kiwi | |||
| 3 | Pear | 25 | ||||
| 4 | Carrot | 60 | ||||
| 5 | Bread | 12 | ||||
| 6 | Milk | 30 |
À vous : En F2, recherchez le stock du produit de E2 dans A2:B6, et affichez "Not found" quand il n'est pas dans la liste.
Pourquoi tout masquer peut cacher des erreurs
SIERREUR ne corrige rien ; elle décide de ce que la cellule affiche. Avant d'y entourer une formule :
- Cherchez pourquoi l'erreur se produit. Quand une cellule Units vide provoque #DIV/0!, la vraie solution est peut-être une donnée que quelqu'un doit saisir, pas un prix à zéro.
- Préférez SI.NON.DISP pour les recherches, pour qu'un mauvais numéro de colonne (#REF!), un nom mal orthographié (#NOM?) ou du texte dans une colonne de nombres (#VALEUR!) reste visible.
- Testez le cas précis pour les divisions.
=SI(C2=0;0;B2/C2)gère un diviseur nul et rien d'autre ; une mauvaise référence en B2 affiche toujours son erreur. La page sur #DIV/0! compare les deux approches. - Choisissez un remplacement qu'on ne peut pas confondre avec une donnée. Un 0 dans une colonne de prix ressemble à un vrai prix et fait baisser la moyenne ;
""ou "Not found" non.
Ajoutez SIERREUR en dernier, une fois que la formule donne le bon résultat sur les lignes qui doivent fonctionner.
Questions fréquentes
Comment utiliser SIERREUR avec RECHERCHEV ?
Entourez la recherche : =SIERREUR(RECHERCHEV(E2;A2:C6;3;FAUX);"Not found"). Quand E2 n'est pas dans la première colonne, la cellule affiche Not found au lieu de #N/A. =SI.NON.DISP(RECHERCHEV(E2;A2:C6;3;FAUX);"Not found") fait la même chose et laisse apparaître les autres erreurs.
Comment faire en sorte que SIERREUR renvoie une cellule vide ?
Utilisez un texte vide comme second argument : =SIERREUR(B2/C2;""). La cellule semble vide, mais elle contient du texte, donc =D2+1 sur elle donne #VALEUR! ; SOMME et MOYENNE l'ignorent.
Quelle est la différence entre SIERREUR et SI.NON.DISP ?
SIERREUR remplace toutes les erreurs : #N/A, #DIV/0!, #VALEUR!, #REF!, #NOM?, #NOMBRE! et #NUL!. SI.NON.DISP ne remplace que #N/A, le "non trouvé" des recherches, et laisse apparaître toutes les autres erreurs, pour qu'une formule cassée ne soit pas masquée.
Comment remplacer #N/A par 0 dans Excel ?
Entourez la formule de SI.NON.DISP avec 0 comme valeur : =SI.NON.DISP(RECHERCHEV(E2;A2:C6;3;FAUX);0). RECHERCHEX intègre ce remplacement dans son quatrième argument : =RECHERCHEX(E2;A2:A6;C2:C6;0).
Quelles versions d'Excel ont SIERREUR et SI.NON.DISP ?
SIERREUR existe depuis Excel 2007 et SI.NON.DISP depuis Excel 2013. Dans d'anciens fichiers, vous pouvez trouver =SI(ESTERREUR(B2/C2);0;B2/C2), qui fait le même travail que SIERREUR mais calcule la formule deux fois.
