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

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

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

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

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

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

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

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

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

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

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

INDEX и MATCH са добри възможности за много случаи, в който VLOOKUP не отговаря на нуждите ви. Основното предимство на индекс/СЪВПАДЕНИЕ е, че можете да търсите на стойност в колона на всяко място в таблица за справки. 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(array or reference, MATCH(lookup_value,lookup_array,[match_type])

Нека Използвайте 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 функция за почистване на данни в клетки.

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

Ако аргументът диапазон_справка е зададено на "истина", както и търсената стойност е по-малка от най-малката стойност в масив – ще видите грешка #N/A. ВЯРНО изглежда приблизително съвпадение в масив и връща най-близкото стойност по-малка от търсената стойност.

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

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

Решение:

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

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

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

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

Решение:

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

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

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

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

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

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

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

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

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

Вж. също

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

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

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

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

×