Table des matières
- Correction de l'erreur #N/A dans la fonction VLOOKUP dans Excel
- 1. La valeur souhaitée est mal orthographiée
- 2. Erreur #N/A lors de la recherche d'une correspondance approximative avec VLOOKUP
- 3. Erreur #N/A lors de la recherche d'une correspondance exacte avec VLOOKUP
- 4. La colonne de recherche n'est pas la plus à gauche
- 5. Les nombres sont formatés en tant que texte
- 6. Il y a un espace au début ou à la fin
- Erreur #VALEUR ! dans les formules avec VLOOKUP
- Erreur #NAME ? dans RECHERCHEV
- VLOOKUP ne fonctionne pas (limitations, mises en garde et décisions)
- RECHERCHEV – travailler avec les fonctions SIERREUR et ESTERREUR
Cette leçon explique comment traiter rapidement une situation où une fonction VPR (VLOOKUP) ne veut pas fonctionner dans Excel 2013, 2010, 2007 et 2003, et comment identifier et corriger les erreurs courantes et surmonter les limitations VPR.
Dans plusieurs articles précédents, nous avons exploré les différentes facettes de la fonction VPR dans Excel. Si vous les avez lus attentivement, vous devriez maintenant être un expert dans ce domaine. Cependant, ce n'est pas sans raison que de nombreux experts d'Excel pensent VPR l'une des caractéristiques les plus complexes. Il a un tas de limitations et de fonctionnalités qui deviennent la source de nombreux problèmes et erreurs.
Dans cet article, vous trouverez des explications simples sur les erreurs # AT (#N / A), # NOM? (#Nom et #VALEUR! (#VALEUR !) qui apparaissent lorsque vous travaillez avec la fonction VPR, ainsi que des techniques et des méthodes pour y faire face. Nous allons commencer par les cas les plus courants et les raisons les plus évidentes. VPR ne fonctionne pas, il est donc préférable d'étudier les exemples dans l'ordre dans lequel ils sont donnés dans l'article.
Correction de l'erreur #N/A dans la fonction VLOOKUP dans Excel
Dans les formules avec VPR un message d'erreur # AT (#N/A) signifie pas disponible (pas de données) - apparaît lorsque Excel ne trouve pas la valeur que vous recherchez. Cela peut arriver pour plusieurs raisons.
1. La valeur souhaitée est mal orthographiée
Bonne idée de vérifier cet article en premier! Les fautes de frappe se produisent souvent lorsque vous travaillez avec de très grandes quantités de données, composées de milliers de lignes, ou lorsque la valeur que vous recherchez est écrite dans une formule.
2. Erreur #N/A lors de la recherche d'une correspondance approximative avec VLOOKUP
Si vous utilisez une formule avec une condition de recherche de correspondance approximative, c'est-à-dire un argument range_lookup (range_lookup) est VRAI ou n'est pas spécifié, votre formule peut signaler une erreur #N / A dans deux cas :
- La valeur à rechercher est inférieure à la plus petite valeur du tableau recherché.
- La colonne de recherche n'est pas triée par ordre croissant.
3. Erreur #N/A lors de la recherche d'une correspondance exacte avec VLOOKUP
Si vous recherchez une correspondance exacte, c'est-à-dire un argument range_lookup (range_lookup) est FALSE et la valeur exacte n'a pas été trouvée, la formule signalera également une erreur #N / A. En savoir plus sur la recherche de correspondances exactes et approximatives avec une fonction VPR.
4. La colonne de recherche n'est pas la plus à gauche
Comme vous le savez probablement, l'une des limitations les plus importantes VPR c'est qu'il ne peut pas faire face à gauche, donc la colonne de recherche de votre table doit être la plus à gauche. En pratique, on l'oublie souvent, ce qui conduit à une formule qui ne fonctionne pas et à une erreur. #N / A.
Décision: S'il n'est pas possible de modifier la structure des données afin que la colonne de recherche soit la plus à gauche, vous pouvez utiliser une combinaison de fonctions INDEX (INDICE) et PLUS EXPOSÉ (MATCH) comme alternative plus flexible pour VPR.
5. Les nombres sont formatés en tant que texte
Une autre source d'erreur #N / A dans les formules avec VPR sont des nombres au format texte dans la table principale ou la table de recherche.
Cela se produit généralement lorsque vous importez des informations à partir de bases de données externes ou lorsque vous tapez une apostrophe avant un nombre pour conserver le zéro non significatif.
Les signes les plus évidents d'un nombre au format texte sont illustrés dans la figure ci-dessous :
De plus, les nombres peuvent être stockés au format Général (Général). Dans ce cas, il n'y a qu'une seule caractéristique notable - les nombres sont alignés sur le bord gauche de la cellule, alors que par défaut, ils sont alignés sur le bord droit.
Décision: S'il s'agit d'une valeur unique, cliquez simplement sur l'icône d'erreur et sélectionnez Convertir en nombre (Convertir en nombre) dans le menu contextuel.
Si tel est le cas avec de nombreux numéros, sélectionnez-les et cliquez avec le bouton droit sur la zone sélectionnée. Dans le menu contextuel qui s'affiche, sélectionnez Format de cellule (Formater les cellules) > onglet Numéro (Nombre) > format Numéro (Numérique) et appuyez sur OK.
6. Il y a un espace au début ou à la fin
C'est la raison la moins évidente de l'erreur. #N / A en fonction VPR, car il est visuellement difficile de voir ces espaces supplémentaires, en particulier lorsque vous travaillez avec de grandes tables, lorsque la plupart des données sont hors écran.
Solution 1 : Espaces supplémentaires dans la table principale (où se trouve la fonction VLOOKUP)
Si des espaces supplémentaires apparaissent dans le tableau principal, vous pouvez vous assurer que les formules fonctionnent correctement en entourant l'argument valeur de recherche (lookup_value) dans une fonction TRIM (GARNITURE):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Solution 2 : Espaces supplémentaires dans la table de recherche (dans la colonne de recherche)
Si des espaces supplémentaires sont dans la colonne de recherche - des moyens simples #N / A dans la formule avec VPR ne peut être évité. À la place de VPR Vous pouvez utiliser une formule matricielle avec une combinaison de fonctions INDEX (INDICE), PLUS EXPOSÉ (MATCH) è TRIM (GARNITURE):
=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))
Comme il s'agit d'une formule matricielle, n'oubliez pas d'appuyer sur Ctrl + Maj + Entrée au lieu de l'habituel Entrerpour saisir correctement la formule.
Erreur #VALEUR ! dans les formules avec VLOOKUP
Dans la plupart des cas, Microsoft Excel signale une erreur #VALEUR! (#VALEUR !) lorsque la valeur utilisée dans la formule ne correspond pas au type de données. Concernant VPR, alors il y a généralement deux raisons pour l'erreur #VALEUR!.
1. La valeur que vous recherchez contient plus de 255 caractères
Attention : fonction VPR ne peut pas rechercher des valeurs contenant plus de 255 caractères. Si la valeur que vous recherchez dépasse cette limite, vous recevrez un message d'erreur. #VALEUR!.
Décision: Utiliser un tas de fonctionnalités INDEX + CORRESPONDANCE (INDEX + CORRESPONDANCE). Vous trouverez ci-dessous une formule qui conviendra parfaitement à cette tâche :
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Le chemin d'accès complet au classeur de recherche n'est pas spécifié
Si vous récupérez des données d'un autre classeur, vous devez spécifier le chemin d'accès complet à ce fichier. Plus précisément, vous devez inclure le nom du classeur (y compris l'extension) entre crochets [ ], suivi du nom de la feuille, suivi d'un point d'exclamation. Toute cette construction doit être entourée d'apostrophes, au cas où le nom du livre ou de la feuille contiendrait des espaces.
Voici la structure complète de la fonction VPR chercher dans un autre livre :
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
La vraie formule pourrait ressembler à ceci :
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Cette formule recherchera la valeur de la cellule A2 dans une colonne B sur la feuille Sheet1 dans le cahier Nouveaux prix et extraire la valeur correspondante de la colonne D.
Si une partie du chemin de la table est omise, votre fonction VPR ne fonctionnera pas et signalera une erreur #VALEUR! (même si le classeur avec la table de recherche est actuellement ouvert).
Pour plus d'informations sur la fonction VPRfaisant référence à un autre fichier Excel, consultez la leçon : Recherche d'un autre classeur à l'aide de VLOOKUP.
3. L'argument Column_num est inférieur à 1
Il est difficile d'imaginer une situation où quelqu'un entre une valeur inférieure à 1pour indiquer la colonne dont extraire la valeur. Bien qu'il soit possible si la valeur de cet argument est calculée par une autre fonction Excel imbriquée dans VPR.
Donc, s'il arrive que l'argument col_index_num (numéro_colonne) inférieur à 1fonction VPR signalera également une erreur #VALEUR!.
Si l'argument col_index_num (column_number) est supérieur au nombre de colonnes dans le tableau donné, VPR signalera une erreur #REF! (#SSYL !).
Erreur #NAME ? dans RECHERCHEV
Le cas le plus simple est une erreur # NOM? (#NAME ?) - apparaîtra si vous écrivez accidentellement un nom de fonction avec une erreur.
La solution est évidente : vérifiez votre orthographe !
VLOOKUP ne fonctionne pas (limitations, mises en garde et décisions)
En plus de la syntaxe assez compliquée, VPR a plus de limitations que toute autre fonction Excel. En raison de ces limitations, des formules apparemment simples avec VPR conduisent souvent à des résultats inattendus. Vous trouverez ci-dessous des solutions pour plusieurs scénarios courants où VPR est faux.
1. VLOOKUP n'est pas sensible à la casse
Fonction VPR ne fait pas de distinction entre la casse et accepte les caractères minuscules et majuscules comme identiques. Par conséquent, s'il y a plusieurs éléments dans la table qui ne diffèrent que par la casse, la fonction RECHERCHEV renverra le premier élément trouvé, quelle que soit la casse.
Décision: Utilisez une autre fonction Excel qui peut effectuer une recherche verticale (RECHERCHE, SOMMEPROD, INDEX et CORRESPONDANCE) en combinaison avec EXACTA qui distingue cas. Pour plus de détails, vous pouvez apprendre de la leçon - 4 façons de rendre VLOOKUP sensible à la casse dans Excel.
2. VLOOKUP renvoie la première valeur trouvée
Comme tu le sais déjà, VPR renvoie la valeur de la colonne donnée correspondant à la première correspondance trouvée. Cependant, vous pouvez lui faire extraire la 2e, la 3e, la 4e ou toute autre répétition de la valeur souhaitée. Si vous devez extraire toutes les valeurs en double, vous aurez besoin d'une combinaison de fonctions INDEX (INDICE), MOINS (PETIT) et LINE (LIGNE).
3. Une colonne a été ajoutée ou supprimée du tableau
Malheureusement, les formules VPR cesser de fonctionner chaque fois qu'une nouvelle colonne est ajoutée ou supprimée de la table de recherche. Cela se produit parce que la syntaxe VPR vous oblige à spécifier la plage complète de la recherche et le numéro de colonne spécifique pour l'extraction de données. Naturellement, la plage donnée et le numéro de colonne changent lorsque vous supprimez une colonne ou en insérez une nouvelle.
Décision: Et encore une fois les fonctions sont pressées d'aider INDEX (INDICE) et PLUS EXPOSÉ (MATCH). Dans la formule INDEX + CORRESPONDANCE Vous définissez séparément les colonnes de recherche et de récupération et, par conséquent, vous pouvez supprimer ou insérer autant de colonnes que vous le souhaitez sans vous soucier de devoir mettre à jour toutes les formules de recherche associées.
4. Les références de cellule sont brouillées lors de la copie d'une formule
Cette rubrique explique l'essence du problème de manière exhaustive, n'est-ce pas ?
Décision: Utilisez toujours des références de cellule absolues (avec le symbole $) sur les enregistrements de la plage, par exemple 2 $ AU: 100 $ C $ or $ A: $ C. Dans la barre de formule, vous pouvez changer rapidement de type de lien en cliquant sur F4.
RECHERCHEV – travailler avec les fonctions SIERREUR et ESTERREUR
Si vous ne voulez pas effrayer les utilisateurs avec des messages d'erreur #N / A, #VALEUR! or # NOM?, vous pouvez afficher une cellule vide ou votre propre message. Vous pouvez le faire en plaçant VPR dans une fonction IFERREUR (IFERREUR) dans Excel 2013, 2010 et 2007 ou utilisez un tas de fonctions SI+ESTERREUR (IF+ISERROR) dans les versions antérieures.
VLOOKUP : travailler avec la fonction SIERREUR
Syntaxe de la fonction IFERREUR (IFERREUR) est simple et parle de lui-même :
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Autrement dit, pour le premier argument, vous insérez la valeur à vérifier pour une erreur, et pour le deuxième argument, vous spécifiez ce qu'il faut renvoyer si une erreur est trouvée.
Par exemple, cette formule renvoie une cellule vide si la valeur recherchée n'est pas trouvée :
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Si vous souhaitez afficher votre propre message au lieu du message d'erreur standard de la fonction VPR, mettez-le entre guillemets, comme ceci :
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP : travailler avec la fonction ISERROR
Depuis la fonction IFERREUR est apparu dans Excel 2007, lorsque vous travaillez dans des versions antérieures, vous devrez utiliser la combinaison IF (SI) et EOSHIBKA (ISERROR) comme ceci :
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Par exemple, la formule SI+ERREUR+RECHERCHEV, semblable à la formule SIERREUR+RECHERCHEVmontré ci-dessus:
=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))
C'est tout pour aujourd'hui. J'espère que ce court tutoriel vous aidera à faire face à toutes les erreurs possibles. VPR et faire fonctionner correctement vos formules.