Как да коригирате грешка #N/A във функцията VLOOKUP

Тази тема описва най-често срещаните причини за VLOOKUP за грешен резултат във функцията и предлага вместо това предложения за използване на index и Match .

Съвет: Също така вижте карта за бързи справки: съвети за отстраняване на неизправности на VLOOKUP , която представя често срещаните причини за #NA проблеми в удобен PDF файл. Можете да споделите PDF файла с други хора или да го отпечатате за справка.

Проблем: Търсената стойност не е в първата колона на аргумента масив_на_таблица

Една пречка на VLOOKUP е, че може да търси стойности само в лявата колона в масива от таблицата. Ако вашата стойност за търсене не е в първата колона на масива, ще видите #N/грешка.

В таблицата по-долу искаме да извлечем продадените бройки от артикула "Зеле".

#NA грешка във VLOOKUP: Търсената стойност не е в първата колона на масива на таблицата

#N/резултати от грешки, тъй като стойността на търсенето "Кейл" се появява във втората колона (продукти) на table_array аргумент a2: C10. В този случай Excel го търси в колона A, а не в колона B.

Решение: Може да опитате да решите проблема, като настроите VLOOKUP да препраща към правилната колона. Ако това не е възможно, опитайте да преместите колоните си. Това може да е твърде неосъществимо, ако имате големи или сложни електронни таблици, където стойностите на клетките са резултат от други изчисления, а може би има и други логични причини, поради които не можете просто да премествате колоните наоколо. Решението е да използвате комбинация от функциите INDEX и MATCH, която може да търси стойност в дадена колона, независимо от позицията в таблицата за търсене. Вижте следващия раздел.

Помислете за използване на INDEX/MATCH вместо това

Индекс и Match са добри опции за много случаи, в които VLOOKUP не отговаря на вашите нужди. Основното предимство на INDEX/MATCH е, че можете да търсите стойност в колона на произволно място в таблицата за справки. INDEX връща стойност от зададена таблица/диапазон – според позицията си. MATCH връща относителната позиция на стойност в таблица/диапазон. Използвайте INDEX и MATCH заедно във формула, за да намерите стойност в таблица/масив, като зададете относителната позиция на стойността в таблицата/масива.

Има няколко предимства от използването на INDEX/MATCH вместо VLOOKUP:

  • С индекс и СЪВПАДЕНИЕ върнатата стойност не трябва да бъде в същата колона като колоната за справка. Това е различно от VLOOKUP, в което върнатата стойност трябва да бъде в указания диапазон. Какво означава това? При VLOOKUP трябва да знаете номера на колоната, която съдържа върнатата стойност. Макар че това може да не изглежда предизвикателно, може да е неудобно, когато имате голяма таблица и трябва да преброите колоните. Освен това, ако добавите/премахнете колона във вашата таблица, трябва да преброите и актуализирате аргумента col_index_num . При INDEX/MATCH не се изисква преброяване, тъй като колоната за търсене е различна от колоната, която съдържа върнатата стойност.

  • С индекс и СЪВПАДЕНИЕ можете да зададете ред или колона в масив – или да укажете и двете. Това означава, че можете да търсите стойности както вертикално, така и хоризонтално.

  • INDEX и MATCH могат да се използват за търсене на стойности във всяка колона. За разлика от VLOOKUP, в която можете да търсите само стойност в първата колона в таблица – ИНДЕКСът и MATCH ще работят, ако стойността на търсене е в първата колона, последната или навсякъде между тях.

  • ИНДЕКС и MATCH предлагат гъвкавостта да се направи динамична препратка към колоната, която съдържа върнатата стойност.Това означава, че можете да добавите колони към вашата таблица, без да чупите индекс и СЪВПАДЕНИЕ. От друга страна, VLOOKUP прекъсвания, ако трябва да добавите колона към таблицата, тъй като тя прави статична препратка към таблицата.

  • ИНДЕКС и СЪВПАДЕНИЕ предлага по-голяма гъвкавост с мачовете.ИНДЕКСът и СЪВПАДЕНИЕто могат да намерят точно съвпадение или стойност, която е по-голяма или по-малка от стойността за справка. VLOOKUP търси само съвпадение, най-близко до дадената стойност (по подразбиране) или точната стойност. VLOOKUP също така приема по подразбиране, че първата колона на масива на таблицата е сортирана по азбучен ред, но ако таблицата не е сортирана по този начин, VLOOKUP ще се върне първото най-близко съвпадение в таблицата, което може да не е това, което търсите.

Синтаксис

За да създадете синтаксис за INDEX/MATCH, трябва да използвате аргумента за масив/препратка от функцията INDEX и да вложите синтаксиса на MATCH вътре в него. Това е формуляр:

=INDEX(масив или препратка; MATCH(търсена_стойност;масив_за_търсене;[тип_съвпадение])

Използвайте INDEX/MATCH, за да заместите VLOOKUP от примера по-горе. Синтаксисът ще изглежда така:

=INDEX(C2:C10;MATCH(B13;B2:B10;0))

Казано на обикновен език, това означава следното:

= INDEX (връща стойност от C2: C10, която ще СЪВПАДА (Кейл), която е някъде в масива B2: B10, в която върнатата стойност е първата стойност, съответстваща на Кейл))

Функциите INDEX и MATCH могат да се използват за заместване на VLOOKUP

Формулата търси първата стойност в C2:C10, която съответства на "Зеле" (в B7), и връща стойността в C7 (100), което е първата стойност, която съответства на "Зеле".

Проблем: Не е намерено точно съвпадение

Когато аргументът Range_lookup е FALSE – и VLOOKUP не може да намери точно съвпадение във вашите данни – връща #N/грешка.

Решение: Ако сте сигурни, че съответните данни съществуват във вашата електронна таблица и VLOOKUP не го улавя, отделете време да проверите дали адресираните клетки нямат скрити интервали или непечатаеми знаци. Също така се уверете, че клетките следват правилния тип данни. Например клетки с числа трябва да се форматират като число, а не като текст.

Освен това можете да използвате функцията Clean или Trim , за да изчистите данните в клетките.

Проблем: Търсената стойност е по-малка от най-малката стойност в масива

Ако аргументът Range_lookup е зададен на "TRUE" и стойността за справка е по-малка от най-малката стойност в масива, ще видите #N/грешка. TRUE търси приблизително съвпадение в масива и връща най-близката стойност, която е по-малка от търсената.

В следващия пример търсената стойност е 100, но в диапазона B2:C10 няма стойности, които да са по-малки от 100; това води до грешката.

N/A грешка във VLOOKUP, когато търсената стойност е по-малка от най-малката стойност в масив

Решение:

  • Коригирайте търсената стойност както е необходимо.

  • Ако не можете да промените стойността за търсене и имате нужда от по-голяма гъвкавост със съвпадащи стойности, обмислете използването на INDEX/MATCH вместо VLOOKUP – вижте раздела по-горе в тази статия. С INDEX/MATCH можете да търсите стойности, по-големи от, по-малки от или равни на търсената стойност. За повече информация относно използването на INDEX/MATCH вместо VLOOKUP вижте предишния раздел в тази тема.

Проблем: Колоната за търсене не е сортирана във възходящ ред

Ако аргументът Range_lookup е зададен на "TRUE" и една от колоните за търсене не е сортирана във възходящ ред (от а до я), ще видите #N/грешка.

Решение:

  • Променете функцията VLOOKUP, така че да търси точно съвпадение. За да направите това, задайте аргумента диапазон_за_търсене на FALSE. Не е необходимо сортиране за FALSE.

  • Използвайте функциите INDEX/MATCH за търсене на стойност в несортирана таблица.

Проблем: Стойността е голямо число с плаваща запетая

Ако в клетките има стойности за дата и час или големи десетични числа, Excel връща грешката #N/A поради препълване при плаваща запетая. Числата с плаваща запетая имат цифри след десетичния знак. (Excel записва времевите стойности като числа с плаваща запетая.) Excel не може да съхранява числа с много големи плаващи точки, така че функцията за правилната работа на числата с плаваща запетая трябва да бъде закръглена до 5 цифри след десетичния знак.

Решение: Скъсете числата, като ги за закръглите до 5 цифри след десетичния знак с помощта на функцията ROUND.

Имате въпрос за конкретна функция?

Публикувайте въпрос във форума на общността на Excel

Помогнете ни да подобрим на Excel

Имате предложения как можем да подобрим следващата версия на Excel? Ако е така, вижте темите в Гласът на потребителите на Excel.

Вж. също

Забележка:  Тази страница е преведена чрез автоматизация и може да съдържа граматически грешки и несъответствия. Нашата цел е това съдържание да ви бъде полезно. Можете ли да ни кажете дали информацията е била полезна за вас? Ето статията на английски за справка.

Разширете уменията си в Office
Преглед на обучението
Получавайте първи новите функции
Присъединете се към участниците в Office Insider

Беше ли полезна тази информация?

Благодарим ви за обратната връзка!

Благодарим ви за вашата обратна връзка. Изглежда, че ще бъде полезно да ви свържем с един от нашите агенти по поддръжката на Office.

×