Les #N/A ne sont pas en tant que telles des erreurs sur Excel. Il s’agit davantage d’une information renvoyée par Excel.
On rencontre les #N/A sur les fonctions de RECHERCHE comme : RECHERCHEV, RECHERCHEH, RECHERCHEX, RECHERCHE et MATCH. Découvrons ensemble comment traiter les #N/A au travers d’exemples.
[lwptoc]
Pourquoi Excel affiche #N/A sur ma RECHERCHE ?
Les #N/A ne sont pas des erreurs. Ils sont des informations. Ils vous indiquent qu’Excel n’a pas réussi à trouver la valeur cherchée. C’est fréquent et normal sur les fonctions de RECHERCHE comme la RECHERCHEV, RECHERCHEH, RECHERCHEX, RECHERCHE et MATCH.
De là, vous devez vous poser deux types de questions :
- Pourquoi Excel ne trouve pas ma valeur ?
- Comment ne plus afficher les #N/A ?
En effet, il arrive régulièrement que l’on croit que notre valeur existe mais Excel nous affiche #N/A. Plusieurs raisons peuvent expliquer pourquoi Excel ne trouve pas la valeur cherchée.
Votre valeur n’existe pas.
Si vous saviez combien de fois mes collègues m’ont appelés à cause d’un #N/A sur une RECHERCHEV. Je regarde la formule. Elle a l’air bonne. Je cherche à comprendre les données et ce qu’ils tentent de faire. Enfin, je regarde si la valeur cherchée est présente. Vous l’aurez deviné, elle n’est pas présente. C’est humain. Quand vous utilisez une RECHERCHEV et que 1309 lignes sur 1314 sont correctement ramenées, mentalement les 5 #N/A restant ne peuvent être que des erreurs. Avec un tel ratio de réussite, il y a peu de chance que votre formule soit erronée ou sinon vous avez un problème de format.
Pour vous permettre de vérifier rapidement, sélectionnez votre colonne de RECHERCHE et tapez ctrl+F. Excel va rechercher la valeur dans votre colonne.
Votre matrice de recherche est fausse.
C’est une autre erreur très fréquente. Elle est facile à identifier. Imaginons que vous avez tapé votre RECHERCHEV, les premières lignes renvoyées semblent fonctionner correctement. Vous faites un filtre et vous vous rendez compte qu’il y a beaucoup de lignes qui ne fonctionnent pas. Par acquis de conscience, vous vérifiez que vos valeurs sont présentes dans la matrice et vous découvrez que c’est le cas. Vous regardez votre formule et elle semble correcte. Elle SEMBLE correcte en apparence. Il arrive régulièrement que vous oubliez de figer votre matrice de RECHERCHE. En étirant votre formule vers le bas, votre matrice s’est déplacée. Pour corriger, vous avez alors 2 solutions :
- Vous sélectionnez les colonnes entièrement ;
- Vous figez votre colonnes avec les dollars.
Le format des cellules n’est pas bon.
C’est le cas le plus complexe à identifier. Excel nous indique que notre valeur est absente alors que nous la voyons dans le tableau. Pour Excel, la valeur d’une cellule peut avoir plusieurs formats. Elle peut avoir un format numérique ou être en format texte. Ici, Excel a identifié votre valeur cherchée en tant que texte et les valeurs dans votre colonne de recherche en tant que nombre (ou inversement). Ainsi, pour Excel, 113907 en format texte est différent de 113907 en format nombre.
Il y a plusieurs façons d’identifier si nous nous situons dans ce cas. La première est de sélectionner plusieurs valeurs. Si les valeurs sont reconnues comme du texte, Excel va afficher en bas à droite de l’écran le nombre de cellulles non vides (image gauche). Dans le cas contraire, il va faire la somme des valeurs.
L’autre solution est d’observer l’alignement des valeurs dans les cellules. Cette solution fonctionne si nous n’avons pas appliquer d’alignement spécifique aux cellules. Dans l’exemple ci-dessous, les premières valeurs de la colonne “numéro de commande” sont au format numérique. Excel, par défaut, les aligne à droite. Si Excel les identifie comme du texte, il va les aligner à gauche.
Si vous êtes dans ce cas, rendez vous directement à la section suivante.
Comment régler les problèmes de format (numérique/texte) ?
Vous êtes dans le cas où vous avez identifié que les #N/A cachent un problème en lien avec la reconnaissance d’Excel des formats texte et nombre. Vos chiffres sont identifiés comme étant du texte. C’est très courant en entreprise de rencontrer ce type de problématique avec les matricules des salariés ou les comptes comptables. Excel se mélange régulièrement les pinceaux. Heureusement, pour régler cette erreur, la solution n’est pas très compliquée : la formule CNUM.
Nous allons préciser à Excel que notre valeur cherchée et notre colonne de recherche contiennent des valeurs numériques. Votre recherche va se transformer de la façon suivante :
= RECHERCHEV( CNUM(M2) ; CNUM($A$2:$C200) ;3;FAUX)
A présent, les seules cellules affichant #N/A sont logiques. Nous allons voir à présent les différentes solutions qui s’offrent à nous pour ne plus les afficher ou afficher une autre valeur à la place.
Comment éviter #N/A sur Excel ?
Comme expliqué précédemment, #N/A est la façon qu’à Excel d’indiquer que la valeur cherchée est absente dans la colonne de recherche. Excel offre beaucoup de solutions pour que nous puissions ne pas afficher ces #N/A. Voici une liste non exhausitve de ces solutions :
- La formule SIERREUR ;
- La formule SI.NON.DISP ;
- La formule SI accompagnée de la formule ESTERREUR ;
- La formule SI accompagnée de la formule ESTNA ;
Les formules SIERREUR et SI.NON.DISP permettront de remplacer les #N/A par une valeur choisie (vide, 0, texte, nombre, formule,…). La formule SI couplée à ESTERREUR ou à ESTNA permettra de remplacer le résultat de notre recherche qu’elle aboutisse ou non. Il peut être intéressant quelques fois de ne pas ressortir le résultat de la RECHERCHEV mais simplement un “ok” pour dire que notre valeur est bien présente.
Comment utiliser SIERREUR pour traiter les #N/A sur Excel ?
La formule SIERREUR permet de remplacer le résultat d’une formule par une autre valeur si celui-ci retourne une erreur. C’est très pratique dans le cas des RECHERCHEV et les #N/A qu’on ne veut pas afficher. La formule est simple à utiliser.
=SIERREUR( ma_recherche ; valeur_que_je_veux_afficher_si_na )
Ce qui nous donnera :
=SIERREUR( RECHERCHEV(M2;$A$2:$C200;3;FAUX) ; “valeur absente” )
Comment utiliser la fonction SI.NON.DISP pour traiter les #N/A sur Excel ?
Uniquement pour Excel 365.
La formule SI.NON.DISP remplace l’issue d’une formule si celle-ci renvoie un #N/A. A la différence de la formule SIERREUR qui permet de traiter l’ensemble des erreurs comme #REF, #ERR, #NOM,… la formule SI.NON.DISP ne traite que les #N/A. A première vue ça semble être une limite peu utile mais c’est exactement l’inverse. Comme expliqué, le #N/A n’est pas en tant que telle une erreur. Il s’agit d’une information d’Excel que la valeur cherchée est absente. Dans la plupart des cas, nous voulons masquer les #N/A mais nous voulons pour autant que les autres erreurs restent apparentes afin que notre travail renvoie les bons résultats. La composition de la formule est identique à la formule SIERREUR :
=SI.NON.DISP( ma_recherche ; valeur_que_je_veux_afficher_si_na )
Ce qui nous donnera :
=SI.NON.DISP( RECHERCHEV(M2;$A$2:$C200;3;FAUX) ; “valeur absente” )
Comment utiliser la fonction ESTERREUR pour masquer les #N/A sur Excel ?
A la différence de la fonction SIERREUR, la formule ESTERREUR seule n’est pas suffisante. La formule ESTERREUR renvoie un VRAI/FAUX en fonction du résultat de la formule intégrée. La formule ESTERREUR est donc à placer autour d’une autre formule. Si cette formule ne renvoie pas d’erreur, le résultat du ESTERREUR sera FAUX. A l’inverse, si la formule incorporée renvoie une erreur, le ESTERREUR donnera VRAI. En utilisant la formule ESTERREUR avec la formule SI, on peut décider des résultats spécifiques que notre recherche aboutisse ou non. Voici un exemple dans lequel je souhaite afficher “valeur trouvée” si ma recherche aboutit. Dans le cas contraire, je soushaite afficher “valeur absente”. Le formule sera alors la suivante :
=SI ( ESTERREUR( RECHERCHEV(M2;$A$2:$C200;3;FAUX) ) ; “valeur absente” ; “valeur trouvée” )
Comment utiliser ESTNA pour traiter les #N/A sur Excel ?
La formule ESTNA fonctionne comme la formule ESTERREUR mais se limite au cas des #N/A et non de l’ensemble des erreurs. Comme pour la formule SI.NON.DISP, c’est une véritable force. En effet, nous souhaitons généralement identifier les #N/A et les traiter. Nous ne souhaitons pas masquer de réelles erreurs comme #NOM, #REF, #ERR,…
La formule ESTNA renverra le résultat VRAI si la formule intégrée renvoie #N/A lors de sa recherche. A l’inverse, elle renverra FAUX. Associé à la formule SI, nous pouvons traiter aisément nos recherches peu importe si le résultat renvoyé est bon ou mauvais. La formule sera la suivante :
=SI ( ESTNA( RECHERCHEV(M2;$A$2:$C200;3;FAUX) ) ; “valeur absente” ; “valeur trouvée” )
Comment mettre 0 au lieu de #N/A sur Excel ?
Il existe beaucoup de solutions pour afficher 0 à la place des #N/A dans vos RECHERCHEV, RECHERCHEH, RECHERCHEX,… Je vais vous donner les 2 qui ont ma préférence. Ici il s’agit simplement d’indiquer une valeur si le résultat renvoyé est #N/A.
La première formule que je privilégie est la formule SI.NON.DISP. Elle a l’avantage de se limiter aux #N/A et s’écrira de la façon suivante :
=SI.NON.DISP( RECHERCHEV(M2;$A$2:$C200;3;FAUX) ; 0 )
Malheureusement, cette formule n’est disponible qu’à partir d’Excel 365. Pour les autres, je vous conseille donc la formule SIERREUR. Elle aura cependant le désavantage de masquer de réelles erreurs. Si vous souhaitez éviter cet écueil, reportez vous à la section SI+ESTNA. La formule s’écrira de la façon suivante :
=SIERREUR( RECHERCHEV(M2;$A$2:$C200;3;FAUX) ; 0 )
Comment mettre un vide à la place des #N/A sur Excel ?
En reprenant les formules choisies à la section précédente, voici les 2 solutions que je préconise pour remplacer les #N/A par un vide dans vos RECHERCHEV, RECHERCHEH, RECHERCHEX,… sur Excel :
=SI.NON.DISP( RECHERCHEV(M2;$A$2:$C200;3;FAUX) ; “” )
A savoir que cette première formule est disponible uniquement à partir d’Excel 365.
=SIERREUR( RECHERCHEV(M2;$A$2:$C200;3;FAUX) ; “” )
Conclusion sur comment corriger l’erreur #N/A sur Excel ?
Au cours de cet article, nous avons abordé divers points afin de savoir traiter les #N/A dans vos différentes RECHERCHES sur Excel (RECHERCHEV, RECHERCHEH, RECHERCHEX, MATCH).
- Nous avons vu ce qu’était le #N/A.
- Ce que l’on peut faire pour vérifier que les #N/A ne cachent pas de réelles erreurs (matrice non figée, format).
- Les formules que l’on peut mettre en oeuvre pour remplacer les #N/A par une autre valeur et notamment par un vide ou un 0.
Maintenant, vous allez pouvoir frimer à la machine à café et impressionner vos collèges par ces nouvelles connaissances.
