Empêcher les erreurs de formule dans Excel en utilisant IFERROR ou IFNA. Ceux-ci sont meilleurs que les anciens ISERROR ISERR et ISNA. En savoir plus ici.
Les erreurs de formule peuvent être courantes. Si vous avez un ensemble de données avec des centaines d'enregistrements, une division par zéro ou un # N / A sont tenus de s'afficher de temps en temps.
Dans le passé, la prévention des erreurs exigeait des efforts herculéens. Hochez la tête en connaissance de cause si vous avez déjà assommé =IF(ISNA(VLOOKUP(A2,Table,2,0),"Not Found",VLOOKUP(A2,Table,2,0)). En plus d'être très longue à taper, cette solution nécessite qu'Excel effectue deux fois plus de RECHERCHEV. Tout d'abord, vous effectuez une RECHERCHEV pour voir si la RECHERCHEV va produire une erreur. Ensuite, vous refaites la même recherche VLOOKUP pour obtenir le résultat sans erreur.
Excel 2010 a introduit le = IFERROR (Formula, Value If Error) grandement amélioré. Je sais que IFERROR ressemble aux anciennes fonctions ISERROR, ISERR, ISNA, mais c'est complètement différent.
Cette fonction est brillante: =IFERROR(VLOOKUP(A2,Table,2,0),"Not Found"). Si vous avez 1 000 RECHERCHEV et que seulement 5 renvoient # N / A, alors les 995 qui ont fonctionné ne nécessitent qu'une seule RECHERCHEV. Seuls les 5 qui ont renvoyé # N / A doivent passer au deuxième argument de IFERROR.
Curieusement, Excel 2013 a ajouté la fonction IFNA (). C'est comme IFERROR mais ne recherche que les erreurs # N / A. On pourrait imaginer une situation étrange où la valeur dans la table de recherche est trouvée, mais la réponse résultante est une division par 0. Si vous vouliez conserver l'erreur de division par zéro pour une raison quelconque, alors IFNA () le fera.
Bien sûr, la personne qui a construit la table de consultation aurait dû utiliser IFERROR pour empêcher la division par zéro en premier lieu. Dans la figure ci-dessous, "nm" est le code d'un ancien responsable pour "pas significatif".
Merci à Justin Fishman, Stephen Gilmer et Excel de Joe.
Regarder la vidéo
- Dans l'ancien temps, vous utilisiez
=IF(ISNA(Formula),0,Formula) - À partir d'Excel 2010,
=IFERROR(Formula,0) - Mais IFERROR traite les erreurs DIV / 0 de la même manière qu'un # N / A! les erreurs
- À partir d'Excel 2013, utilisez
=IFNA(Formula,0)pour détecter uniquement les erreurs # N / A - Merci à Justin Fishman, Stephen Gilmer et Excel de Joe.
Transcription vidéo
Apprenez Excel à partir du podcast, épisode 2042 - IFERROR et IFNA!
Je vais baladoer tous mes conseils de ce livre, cliquez sur le «i» dans le coin supérieur droit pour accéder à la playlist entière!
Très bien, revenons à l'ancien temps, Excel 2007 ou avant, si vous deviez éviter le # N / A! à partir d'une formule RECHERCHEV comme celle-ci, nous avions l'habitude de faire cette chose folle. Ramassez tous les caractères de RECHERCHEV, sauf pour le =, Ctrl + C pour le mettre dans le presse-papiers, = IF (ISNA (, appuyez sur la touche Fin pour arriver à la fin, si c'est # N / A !, alors nous dites "Not Found", virgule, sinon nous faisons la RECHERCHEV! Bon, je le colle là-dedans, et regarde la longueur de cette formule, Ctrl + Entrée, ajoutez a) juste là, Ctrl + Entrée, d'accord, regardez, c'est gentil. D'accord, mais le problème est que, même si cela résout le problème du # N / A! Chaque formule fait le VOOKUP deux fois. Elle ne veut pas que nous disions "Hé, est-ce une erreur? Oh, non, ce n'est pas une erreur. Nous allons recommencer! »Donc, il faut deux fois plus de temps pour effectuer toutes ces RECHERCHEV. Dans Excel 2010,ils nous donnent la formule géniale et géniale d'IFERROR!
Maintenant, je sais que cela ressemble à ce que nous avions avant, ISERROR ou ISERR, mais c'est IFERROR. Et la façon dont cela fonctionne, prenez n'importe quelle formule qui pourrait renvoyer une erreur, et ensuite vous dites = IFERROR, il y a la formule, une virgule, que faire si c'est une erreur, donc «Not Found». Très bien maintenant, la belle chose à ce sujet est, disons que vous avez un millier de RECHERCHEV à faire, et 980 d'entre eux fonctionnent. Pour le 980, il fait juste la RECHERCHEV, ce n'est pas une erreur, il renvoie la réponse, il ne fait jamais la deuxième RECHERCHEV, c'est la beauté d'IFERROR. L'IFERROR d'Excel est venu dans Excel 2010, mais bien sûr, le seul problème vraiment stupide ici est que la table elle-même renvoie une erreur. Bon, quelqu'un avait 0 quantité, revenu / quantité, et nous obtenons donc un # DIV / 0! erreur là, et cette erreur va également être détectée comme une erreur par IFERROR. Bien,et il semblera que le n'est pas trouvé, il est trouvé, c'est juste que celui qui a construit cette table n'a pas fait du bon travail pour construire cette table.
D'accord, choix n ° 1, Excel 2013 ou plus récent, passez à la fonction qu'ils ont ajoutée en 2013 qui ne recherche pas toutes les erreurs, ne recherche que # N / A! Ctrl + Entrée, et maintenant nous allons obtenir le non trouvé pour ExcelIsFun, mais renvoie le # DIV / 0! Erreur. Mais vraiment, ce que nous devrions faire, c'est ici dans cette formule, nous devrions gérer cette formule pour empêcher cette division par zéro en premier lieu. Donc = IFERROR, cela fonctionne pour toutes sortes de choses, appuyez sur la touche Fin pour arriver à la fin, virgule, et que faire ensuite? Je mettrais toujours un zéro ici comme prix moyen.
J'ai eu un manager une fois, Susanna (?), "Ce n'est pas mathématiquement correct, vous devez mettre" nm "pour ne pas avoir de sens." Exactement comme ça, c'est fou combien de temps mon manager m'a rendu fou avec leur demande folle, sso, copions ça, Coller les formules spéciales, alt = "" ES F. Maintenant, nous obtenons une erreur "non significative" ici, le " Not Found »est trouvé par l'IFERROR, et le« non significatif »est en fait le résultat de la RECHERCHEV. D'accord, il y a donc deux façons différentes de procéder, la meilleure chose à faire ici est probablement d'utiliser l'IFNA, à condition que vous ayez Excel 2013 et que toutes les personnes avec lesquelles vous partagez votre classeur disposent d'Excel 2013. Ce livre, ainsi que tout un tas d'autres sont dans ce livre, est ruisselant avec des conseils épicés, 200 pages, facile à lire, en couleur génial, livre génial. Vérifiez-le, cliquez sur le "I" dans le coin supérieur droit,vous pouvez acheter le livre.
D'accord, récapitulatif de l'épisode: à l'époque, nous utilisions un format long = IF (ISNA (la formule, 0, sinon la formule, la formule était calculée deux fois, ce qui est horrible pour RECHERCHEV. À partir d'Excel 2010, = IFERROR , mettez simplement la formule une fois, virgule, que faire si c'est une erreur. IFERROR, cependant, traite les erreurs # DIV / 0! de la même manière que les erreurs # N / A!, donc à partir d'Excel 2013, la fonction IFNA fonctionne comme IFERROR, mais il ne détectera que les erreurs # N / A !.
Cette astuce d'ailleurs, suggérée par les lecteurs Justin Fishman, Stephen Gilmer et Excel par Joe. Merci à eux d'avoir suggéré cela, et merci à vous d'être passé, nous vous verrons la prochaine fois pour une autre diffusion sur le net!
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Podcast2042.xlsm
