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

Тази тема описва най-често срещаните причини, поради които функцията VLOOKUP може да не работи.

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

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

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

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

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

Получаваме грешка, тъй като търсената стойност "Зеле" е във втората колона ("Плодове и зеленчуци") в аргумента масив_на_таблица A2:C10, затова Excel търси стойността в колона A, а не в колона B.

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

Използване на INDEX/MATCH вместо VLOOKUP

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

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

  • При INDEX/MATCH не е задължително върнатата стойност да е в същата колона, в която се извършва търсенето, за разлика от VLOOKUP, където върнатата стойност трябва да е в зададения диапазон. Какво означава това? При VLOOKUP трябва да знаете номера на колоната, която съдържа върнатата стойност. Въпреки че не изглежда кой знае какво, това може да създаде проблеми, когато имате голяма таблица и трябва да преброите броя колони. Освен това, ако добавяте/премахвате от таблицата, ще трябва да преброите отново и да актуализирате аргумента кол_индекс_бр. При INDEX/MATCH не се изисква преброяване, тъй като колоната за търсене е различна от колоната, която съдържа върнатата стойност.

  • При INDEX/MATCH можете да зададете ред или колона в масива, или дори и двете. Това означава, че можете да търсите стойности както вертикално, така и хоризонтално.

  • INDEX и MATCH могат да се използват за търсене на стойности във всяка колона. За разлика от VLOOKUP, с която можете да търсите стойност само в първата колона на таблицата, INDEX и MATCH ще работят, независимо дали търсената стойност е в първата колона, в последната колона, или някъде по средата.

  • Комбинацията INDEX/MATCH предлага гъвкавостта на динамичните препратки към колоната, която съдържа върнатата стойност. Това означава, че можете да добавяте колони към вашата таблица, без това да попречи на работата на INDEX/MATCH. Докато VLOOKUP се срива, ако се наложи да добавите колона в таблицата, тъй като използва статична препратка към таблицата.

  • INDEX/MATCH предлага повече гъвкавост при съвпаденията. INDEX/MATCH може да намери точно съвпадение или стойност, по-голяма или по-малка от търсената стойност. 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), което е първата стойност, която съответства на "Зеле".

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

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

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

Използвайте функцията CLEAN или TRIM, за да изчистите данните в клетките.

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

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

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

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

Решение:

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

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

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

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

Решение:

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

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

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

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

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

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

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

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

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

Вж. също

Коригиране на грешка #N/A

VLOOKUP: Без повече грешки #N/A

HLOOKUP, VLOOKUP и LOOKUP връщат грешни стойности в Excel

Изчисленията с плаваща запетая може да дадат грешни резултати в Excel

Карта за бързи справки: припомняне на VLOOKUP

VLOOKUP функция

Общ преглед на формулите в Excel

Начини за избягване на повредени формули

Използване на проверката за грешки за откриване на грешки във формули

Всички функции на Excel (по азбучен ред)

)

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

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

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

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

×