Qu'est-ce que la fonction ESTERREUR ?
ESTERREUR (ISERROR en anglais) te sert dès que tu veux savoir si un calcul a échoué avant même de regarder ce qu'il affiche à l'écran. Que tu analyses des données importées, nettoies un tableau ou construises des formules complexes, c'est le réflexe qui te permet de repérer un #DIV/0! ou un #N/A sans parcourir chaque ligne à l'œil.
Elle retourne simplement VRAI si la valeur testée est une erreur Excel, FAUX dans tous les autres cas. Seule ou combinée avec SI, elle te permet de construire des tableaux robustes qui restent propres même quand les données sources sont incomplètes ou incorrectes.
Comprendre chaque paramètre de la fonction ESTERREUR
valeur
: c'est la valeur ou la formule que tu veux testerÇa peut être une référence de cellule comme A1, une formule comme B2/C2, ou même une valeur directe. ESTERREUR évalue cette expression et vérifie si le résultat est une erreur Excel.
Les erreurs détectées sont : #DIV/0! (division par zéro), #N/A (valeur non disponible), #NOM? (nom de fonction inconnu), #NUL! (intersection vide), #NOMBRE! (valeur numérique incorrecte), #REF! (référence invalide) et #VALEUR! (mauvais type de données).
Exemples pratiques pas à pas
Comment détecter une erreur de division par zéro avec la fonction ESTERREUR
Le budget part ce soir et tu récupères un tableau de départements dont le CA et le coût ont été saisis par des personnes différentes. Plusieurs coûts sont encore à zéro, et tu sais que diviser par zéro va couvrir ta colonne de #DIV/0! au moment de calculer les ratios. Avant de construire quoi que ce soit, tu veux savoir où ça coince : la fonction ESTERREUR tente le calcul à ta place et répond VRAI ou FAUX sur chaque ligne.
- 1Dans une cellule, écris
=ESTERREUR(. - 2En 1er argument : écris le calcul à mettre à l'épreuve,
B2/C2, qui divise le CA par le coût. Tu n'y mets pas une colonne de ratios déjà calculée mais l'opération elle-même, parce que la fonction ESTERREUR l'exécute pour toi et juge son résultat : c'est ce qui t'évite d'afficher les#DIV/0!que tu cherches justement à repérer. - 3Ferme la parenthèse et appuie sur Entrée, puis recopie la formule vers le bas pour que chaque département soit testé avec son propre CA et son propre coût.
=ESTERREUR(B2/C2)30 000 €, la division passe sans encombre et la fonction ESTERREUR répond FAUX (rien à signaler). Recopiée vers le bas, la colonne bascule sur VRAI à chaque département dont le coût est resté à zéro, comme RH 2 : ces lignes-là sont celles qui produiraient une erreur #DIV/0! dans ton calcul de ratio.
Comment remplacer une erreur par un message avec les fonctions SI et ESTERREUR
Ton chef veut le tableau des ratios avant la réunion de dix heures, et il n'aime rien moins qu'une colonne truffée de messages d'erreur. Tes départements sont là avec leur CA et leur coût, sauf que certains coûts n'ont toujours pas été renseignés. Plutôt que de laisser Excel afficher #DIV/0! sur ces lignes, tu utilises la fonction ESTERREUR comme test à l'intérieur d'une fonction SI, qui écrit un message compréhensible à la place.
- 1Dans une cellule, écris
=SI(. - 2En 1er argument : écris le test qui décide de la suite et referme-le,
ESTERREUR(B2/C2). Il vaut VRAI quand la division du CA par le coût échoue et FAUX quand elle aboutit, et c'est sur cette réponse que la fonction SI s'appuie ensuite. - 3En 2ᵉ argument : saisis ce que la formule doit afficher quand le test est vrai,
"Coût à saisir". Les guillemets sont indispensables : ils indiquent à Excel que ce contenu est du texte à afficher tel quel, et non une valeur à calculer. - 4En 3ᵉ argument : écris ce que la formule doit afficher quand le test est faux,
B2/C2. C'est la même division qu'au premier argument, et Excel la calcule donc une fois pour le test, puis une seconde fois pour l'afficher. - 5Ferme la parenthèse de la fonction SI et appuie sur Entrée, puis recopie la formule vers le bas pour couvrir tous les départements.
=SI(ESTERREUR(B2/C2); "Coût à saisir"; B2/C2)
Comment compter le nombre d'erreurs d'un tableau avec les fonctions ESTERREUR et SOMMEPROD
L'import de tarifs vient de tomber et ton fournisseur attend un retour avant la fin de journée : combien de lignes sont à refaire ? Tu as une liste de références avec leur prix, dont certains sont arrivés en erreur au lieu d'un nombre, et les recompter à la main sur des centaines de lignes n'est pas une option. La fonction ESTERREUR sait tester une plage entière d'un coup, et il suffit d'additionner ses réponses pour avoir le compte exact.
- 1Dans une cellule, écris
=SOMMEPROD(. - 2En 1er argument : écris la liste de nombres à additionner et referme la fonction ESTERREUR,
--ESTERREUR(B2:B7). Appliquée à une plage entière plutôt qu'à une seule cellule, la fonction ESTERREUR teste chaque prix l'un après l'autre et rend autant de VRAI et de FAUX qu'il y a de lignes. La double négation--les convertit en 1 et en 0, parce qu'une valeur logique ne s'additionne pas alors qu'un nombre, si. - 3Ferme la parenthèse de la fonction SOMMEPROD et appuie sur Entrée. Celle de la fonction ESTERREUR a déjà été refermée à l'étape précédente.
=SOMMEPROD(--ESTERREUR(B2:B7))#VALEUR!, #N/A et #DIV/0! comptent pareil). Il ne reste qu'à additionner les drapeaux pour obtenir le chiffre à annoncer, ici 3 lignes à refaire.
Les erreurs fréquentes avec la fonction ESTERREUR
Les vrais pièges d'ESTERREUR ne sont pas des messages d'erreur, mais des choix de fonction qui te jouent des tours. Le plus fréquent : tu la branches sur une RECHERCHEV pour rattraper un #N/A, sauf qu'elle attrape aussi le #DIV/0! ou le #VALEUR! qui traînaient à côté, et tu maquilles sans le vouloir une vraie erreur de calcul.
Les deux autres cas concernent l'efficacité : un SI(ESTERREUR(...)) qui calcule ta formule deux fois là où SIERREUR suffit, et l'idée fausse qu'ESTERREUR « jette un œil » sans exécuter la formule testée.
Confondre ESTERREUR avec ESTNA pour les RECHERCHEV
Beaucoup utilisent ESTERREUR pour détecter uniquement les erreurs #N/A après une RECHERCHEV. Le problème : ESTERREUR retourne VRAI pour toutes les erreurs, pas seulement #N/A. Si ton tableau contient un #DIV/0!, il sera aussi détecté.
Solution : Utilise ESTNA si tu veux tester uniquement #N/A : =ESTNA(RECHERCHEV(...)). Réserve ESTERREUR pour les cas où tu veux détecter toutes les erreurs sans distinction.
Utiliser SI(ESTERREUR(...)) au lieu de SIERREUR
Écrire =SI(ESTERREUR(A1/B1); 0; A1/B1) est verbeux et inefficace : Excel calcule A1/B1 deux fois (une fois pour le test, une fois pour le résultat), ce qui ralentit ton fichier sur de grandes plages.
Solution : Utilise SIERREUR à la place : =SIERREUR(A1/B1; 0). C'est plus court, plus rapide et plus lisible. SIERREUR est disponible depuis Excel 2007, donc il n'y a aucune raison de ne pas l'utiliser.
Croire qu'ESTERREUR teste la formule sans l'exécuter
Certains pensent qu'ESTERREUR teste la formule sans l'exécuter. Faux : si tu écris =ESTERREUR(RECHERCHEV(...)), Excel exécute complètement la RECHERCHEV, ce qui peut être lent sur de gros tableaux.
Solution : Sois conscient que tester une erreur a un coût de calcul. Si la performance est critique, évite de multiplier les tests et utilise plutôt des formules qui ne génèrent pas d'erreurs, par exemple RECHERCHEX avec son argument si_absent.
ESTERREUR vs ESTNA vs SIERREUR vs SI.NON.DISP
| Critère | ESTERREUR | ESTNA | SIERREUR | SI.NON.DISP |
|---|---|---|---|---|
| Détecte toutes les erreurs | Oui | #N/A uniquement | Oui | #N/A uniquement |
| Type de résultat | VRAI/FAUX | VRAI/FAUX | Valeur ou remplacement | Valeur ou remplacement |
| Remplace automatiquement | Non (test seul) | Non (test seul) | Oui | Oui |
| Calcule la formule combien de fois | 1 fois | 1 fois | 1 fois | 1 fois |
| Disponible depuis | Excel 2003 | Excel 2003 | Excel 2007 | Excel 2013 |
| Usage recommandé | Tests conditionnels complexes, comptage | Après RECHERCHEV/X | Remplacer toute erreur | Remplacer #N/A |
Astuces avancées avec ESTERREUR
Préfère SIERREUR pour 90 % des cas
Pour simplement remplacer une erreur par une valeur de secours, SIERREUR est plus concis et performant qu'un SI(ESTERREUR(...)). Excel ne calcule la formule interne qu'une seule fois au lieu de deux.
=ESTERREUR(B2/C2)
Réserve ESTERREUR pour les cas où tu as besoin d'une logique conditionnelle complexe (plusieurs branches) ou pour compter les erreurs sur une plage entière.
Surligne les erreurs avec la mise en forme conditionnelle
Tu peux combiner ESTERREUR avec la mise en forme conditionnelle pour surligner automatiquement toutes les cellules en erreur d'un tableau. Sélectionne ta plage, crée une règle avec la formule =ESTERREUR(A1), et applique un fond rouge.
=ESTERREUR(A1)
Toutes les erreurs sauteront aux yeux sans que tu aies besoin de parcourir le tableau manuellement.
Compte les erreurs dans une grande plage
Pour évaluer la qualité d'un import et connaître le nombre d'erreurs d'un coup, combine ESTERREUR avec SOMMEPROD. La double négation (--) convertit les valeurs logiques VRAI/FAUX en 1/0 pour pouvoir les additionner.
=ESTERREUR(A1:A1000)
La formule complète : =SOMMEPROD(--ESTERREUR(A1:A1000)). Elle fonctionne sur toutes les versions d'Excel, même sans valider en matriciel.
Questions fréquentes
Oui, absolument. ESTERREUR reconnaît les 7 types d'erreurs Excel : #DIV/0!, #N/A, #NOM?, #NUL!, #NOMBRE!, #REF! et #VALEUR!. Si ta cellule contient n'importe laquelle de ces erreurs, ESTERREUR retournera VRAI.
ESTERREUR teste si une valeur est une erreur et retourne VRAI ou FAUX. SIERREUR va plus loin : elle remplace automatiquement l'erreur par une valeur de ton choix.
Utilise SIERREUR pour gérer les erreurs en une seule formule, c'est plus efficace que SI(ESTERREUR(...)). Garde ESTERREUR pour les cas où tu as besoin du résultat VRAI/FAUX dans une logique conditionnelle plus complexe.
Utilise la fonction ESTNA au lieu d'ESTERREUR. ESTNA retourne VRAI uniquement pour #N/A, alors qu'ESTERREUR retourne VRAI pour toutes les erreurs.
C'est utile quand tu veux gérer #N/A (valeur non trouvée dans une RECHERCHEV) différemment des autres erreurs comme #DIV/0!.
Non, ESTERREUR évalue toujours la formule. Si tu as =ESTERREUR(1/0), Excel calcule d'abord 1/0 (qui donne #DIV/0!), puis ESTERREUR détecte cette erreur et retourne VRAI.
La formule est donc bien exécutée, ce qui peut avoir un impact sur les performances pour des formules lourdes comme RECHERCHEV sur de grandes plages.
ESTERREUR reste utile dans certains cas : pour compter les erreurs dans une plage avec SOMMEPROD(--ESTERREUR(...)), pour appliquer une mise en forme conditionnelle aux erreurs, ou quand tu veux traiter différemment plusieurs types d'erreurs dans un SI complexe.
Mais pour simplement remplacer une erreur par une valeur de secours, SIERREUR est plus simple et plus rapide.
Oui, et c'est l'un de ses cas d'usage les plus puissants. Dans une formule matricielle, ESTERREUR peut tester toute une plage en une seule fois.
Par exemple, =SOMMEPROD(--ESTERREUR(A1:A100)) compte toutes les erreurs de la plage sans avoir besoin de valider en Ctrl+Maj+Entrée.
Pour aller plus loin
Continue sur ta lancée après la fonction ESTERREUR : la leçon associée, un modèle prêt à l'emploi et le guide pour progresser.
Tout voir
