Tuto Excel: Comment gérer le code d’erreur # N/A dans Excel

Cours et tutoriel excel / cours excel gratuit / tutoriel excel gratuit

Bienvenue dans ce cours Excel qui vous montre le code d’erreur # N / A – appliquer les fonctions IFERROR et IFNOT.DISP.

Le code d’erreur # N / A avertit que, par rapport à une recherche effectuée par une fonction, la valeur demandée n’est pas disponible.

N / A est le message d’erreur classique qui est renvoyé après avoir défini une recherche d’une valeur inexistante avec les fonctions suivantes : VLOOKUP, LOOK.HORIZ, SEARCH, MATCH.

La présence sur la feuille de travail du message #N/A, est non seulement une “perturbation” par rapport à la possibilité d’imprimer le document ou de l’envoyer à un client, mais empêche les opérations de calcul correctes dans les colonnes contenant le code d’erreur. .
Par exemple, il est impossible d’appliquer une somme à une zone de données contenant des codes d’erreur.

Éliminer le code d’erreur # N / A des feuilles de presse Excel

Si le message d’erreur # N / A ne pose pas de problèmes dans les formules et les fonctions, mais ne représente qu’une présence gênante dans l’impression du document, vous pouvez résoudre le problème en définissant une option sur l’onglet Feuille de la boîte de dialogue Mise en page .
Par défaut, les codes d’erreur de programme sont imprimés de la même manière qu’ils apparaissent sur la grille.

Dans l’onglet Feuille de la boîte de dialogue Mise en page sous Erreurs de cellule en tant que : définissez l’option
Il est également possible de définir deux symboles “-” à imprimer à la place du code d’erreur.
Évidemment les codes d’erreur resteront visibles sur la feuille de travail mais ne seront pas imprimés !

:: Observation. Une fois que vous avez défini Cell Errors si vous créez un fichier PDF, il ne contiendra pas de codes d’erreur, tout comme s’il s’agissait d’une impression.

Comment calculer les valeurs d’une zone de données contenant des codes d’erreur

Si une fonction SOMME doit être définie, pour une zone de données qui contient des codes d’erreur, le même code d’erreur trouvé dans la zone de données à ajouter est renvoyé comme résultat de la fonction.
Pour remédier à ce problème, à la place de la fonction SUM il est conseillé d’appliquer la fonction AGGREGATE.
La fonction AGGREGATE vous permet de prendre en charge 19 fonctions Excel différentes, avec la possibilité de pouvoir ignorer les lignes masquées et/ou les valeurs d’erreur.

La fonction AGGREGATE a deux syntaxes différentes appelées respectivement Reference et Array.
Référence = AGGREGATE (num_fonction, options, ref1, [ref2], …)

Array = AGREGATE (num_fonction ; options, matrice [k])

Dans le cas de l’image ci-dessus, il est possible de remplacer la Fonction = SOMME (F19 ; F26) par l’expression suivante :
= AGRÉGÉ (9 ; 6 ; F19 : F26)
Dans lequel le nombre Fonction 9 représente la SOMME ; l’option 6 indique à la fonction d’ignorer les messages d’erreur ; et enfin F19 : F26 représente la matrice de données sur laquelle appliquer la Fonction 9, c’est-à-dire une somme.
les fonctions et options prises en compte par la fonction AGGREGATE sont illustrées dans l’image ci-dessous.

:: Pour une étude détaillée de la fonction AGGREGATE, merci de vous référer au tutoriel : La fonction AGGREGATE d’Excel

Éliminer les codes d’erreur de la feuille Excel avec la fonction SIERREUR

La fonction SIERREUR représente certainement une solution définitive aux problèmes produits par les messages d’erreur, à la fois dans les impressions et dans les fonctions présentes sur la feuille de calcul.
Dans sa syntaxe, la fonction SIERREUR a deux arguments, le premier (valeur) est la formule dans laquelle vous souhaitez évaluer le retour d’une erreur. Le deuxième argument (if_error) est le texte (ou une chaîne vide) à renvoyer si la formule renvoie une erreur.

Syntaxe de la fonction SIERREUR – exemple d’application

La fonction IFEROR (Function name en anglais : IFEROR) permet de personnaliser un résultat si une formule génère tout type d’erreur.
Syntaxe de la fonction
= SIERREUR (valeur ; si_erreur)
Dans lequel:
valeur : représente la référence ou une formule dans laquelle vérifier une erreur.
if_error : représente la valeur à renvoyer si une erreur est détectée
La fonction SIERREUR considère les types de codes suivants : # N / A, #VALEUR !, #REF !, # DIV / 0 !, #NUM !, #NOM ? ou #NULL !.
Pour plus d’informations sur la fonction SIERREUR, voir Application de la fonction SIERREUR pour gérer les messages d’erreur Excel

Exemple d’application de la fonction SIERREUR

Dans l’image ci-dessous, la fonction RECHERCHEV a été imbriquée dans l’argument valeur de la fonction SIERREUR
Si la fonction Rechercher renvoie un code d’erreur #N/A, le deuxième argument de la fonction SIERREUR indique de renvoyer une chaîne vide, indiquée dans l’expression par des guillemets “”.
= SIERREUR (RECHERCHEV (B19, liste, 4, FAUX), “”)
Évidemment, vous pouvez choisir de renvoyer un message d’erreur tel que = IFERROR (VLOOKUP (B19; list; 4; FALSE); “value not found”)

Éliminez uniquement le code d’erreur # N / A avec la FONCTION IFNOT.

La fonction IFNOT a été introduit dans Excel avec la version 2013.
Sa syntaxe à deux arguments est similaire à la fonction SIERREUR, mais elle est plus sélective dans la mesure où elle ne gère que le code d’erreur #N/A et n’intervient pas dans le cas des autres codes d’erreur.
La raison d’intérêt de cette fonction est qu’elle évite de masquer d’éventuels codes d’erreur dus à de mauvaises fonctions ou liés à la présence de valeurs incorrectes (par exemple textuelles plutôt que numériques).

Syntaxe de la fonction SE.NON.DISP. – exemple d’application

La Fonction SE.NON.DISP (Function name en anglais : IFNA) permet d’intercepter et par conséquent de gérer uniquement le code d’erreur #N/A qui est renvoyé par de nombreuses Fonctions de Recherche et de Référence telles que MATCH ; RECHERCHEV ; RECHERCHE HORIZ
Syntaxe de la fonction
= SI.NON.AFFICH.(Valeur ; valeur_se_nd)
dans lequel:
valeur Représente la référence ou la formule dans laquelle évaluer la présence d’une erreur
Value_se_nd vous pouvez définir ce qu’il faut retourner si le code d’erreur # N / A est rencontré
:: Faites attention au nom de la fonction, contrairement à de nombreuses autres fonctions, il y a aussi le point d’abréviation à la fin.

Exemple d’application de la fonction SI.NON.AFFICH

La formule suivante a été saisie dans la cellule C8 = IF.NOT. (VLOOKUP (B8 ; B3 : D6 ; 2 ; FALSE) ; “valeur introuvable”)
dans ce cas ne trouvant pas le code 11 le résultat renvoyé serait #N/A et donc la fonction IF.NON.DISP. renvoie le résultat “valeur introuvable”
Dans la cellule C11 (voir image) le nom de la fonction “SEARCH.VET” a été mal renseigné dans ce cas le code erreur qui est renvoyé est #NOM ? et évidemment il n’est pas remplacé par le message “value not found”
Si au lieu d’utiliser SE.NON.DISP. si la fonction SIERREUR avait été appliquée, cette dernière aurait retourné “valeur non disponible”

Du point de vue de la vérification d’éventuelles erreurs, lors de l’utilisation des fonctions de recherche, l’application de la fonction SE.NON.DISP est certainement préférable. au lieu de la fonction SIERREUR plus fréquemment utilisée