Съдържание
Този урок обяснява как бързо да се справите със ситуация, в която функция ВПР (VLOOKUP) не иска да работи в Excel 2013, 2010, 2007 и 2003 и как да идентифицирате и поправите често срещани грешки и да преодолеете ограниченията ВПР.
В няколко предишни статии проучихме различните аспекти на функцията ВПР в Excel. Ако сте ги прочели внимателно, вече трябва да сте експерт в тази област. Въпреки това не е без причина много експерти по Excel смятат ВПР една от по-сложните функции. Той има куп ограничения и функции, които се превръщат в източник на много проблеми и грешки.
В тази статия ще намерите прости обяснения на грешките #AT (#N/A), #NAME? (#ИМЕ?) и #VALUE! (#VALUE!), които се появяват при работа с функцията ВПР, както и техники и методи за справяне с тях. Ще започнем с най-често срещаните случаи и най-очевидните причини. ВПР не работи, така че е по-добре да изучавате примерите в реда, в който са дадени в статията.
Коригиране на грешка #N/A във функцията VLOOKUP в Excel
Във формули с ВПР съобщение за грешка #AT (#N/A) означава не е наличен (няма данни) – появява се, когато Excel не може да намери стойността, която търсите. Това може да се случи по няколко причини.
1. Желаната стойност е изписана неправилно
Добра идея първо да проверите този артикул! Печатни грешки често възникват, когато работите с много големи количества данни, състоящи се от хиляди редове, или когато стойността, която търсите, е записана във формула.
2. Грешка #N/A при търсене на приблизително съвпадение с VLOOKUP
Ако използвате формула с условие за търсене на приблизително съответствие, т.е търсене_обхват (диапазон_търсене) е TRUE или не е посочено, формулата ви може да съобщи за грешка # N / A в два случая:
- Стойността за търсене е по-малка от най-малката стойност в масива, който се търси.
- Колоната за търсене не е сортирана във възходящ ред.
3. Грешка #N/A при търсене на точно съвпадение с VLOOKUP
Ако търсите точно съвпадение, т.е търсене_обхват (диапазон_търсене) е FALSE и точната стойност не е намерена, формулата също ще отчете грешка # N / A. Научете повече за това как да търсите точни и приблизителни съвпадения с функция ВПР.
4. Колоната за търсене не е най-лявата
Както вероятно знаете, едно от най-значимите ограничения ВПР това е, че не може да гледа наляво, следователно колоната за справка във вашата таблица трябва да е най-лявата. На практика често забравяме за това, което води до неработеща формула и грешка. # N / A.
Решение: Ако не е възможно да промените структурата на данните, така че колоната за търсене да е най-лявата, можете да използвате комбинация от функции INDEX (ИНДЕКС) и ПО-ИЗЛОЖЕН (MATCH) като по-гъвкава алтернатива за ВПР.
5. Числата са форматирани като текст
Друг източник на грешка # N / A във формули с ВПР са числа в текстов формат в главната таблица или таблицата за справка.
Това обикновено се случва, когато импортирате информация от външни бази данни или когато въвеждате апостроф преди число, за да запазите водещата нула.
Най-очевидните знаци на число в текстов формат са показани на фигурата по-долу:
В допълнение, числата могат да се съхраняват във формат общ (Общ). В този случай има само една забележима особеност – числата са подравнени към левия край на клетката, докато по подразбиране са подравнени към десния край.
Решение: Ако е една стойност, просто щракнете върху иконата за грешка и изберете Преобразуване в число (Преобразуване в число) от контекстното меню.
Ако това е ситуацията с много числа, изберете ги и щракнете с десния бутон върху избраната област. В контекстното меню, което се показва, изберете Форматиране на клетки (Форматиране на клетки) > раздел Телефон за връзка: (Число) > формат Телефон за връзка: (Цифрово) и натиснете OK.
6. В началото или в края има интервал
Това е най-малко очевидната причина за грешката. # N / A във функция ВПР, тъй като визуално е трудно да се видят тези допълнителни интервали, особено при работа с големи таблици, когато повечето от данните са извън екрана.
Решение 1: Допълнителни интервали в главната таблица (където е функцията VLOOKUP)
Ако в главната таблица се появят допълнителни интервали, можете да се уверите, че формулите работят правилно, като оградите аргумента търсена_стойност (търсена_стойност) във функция TRIM (ПОДРЯЗВАНЕ):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Решение 2: Допълнителни интервали в таблицата за справка (в колоната за справка)
Ако в колоната за търсене има излишни интервали – прости начини # N / A във формулата с ВПР не може да се избегне. Вместо ВПР Можете да използвате формула за масив с комбинация от функции INDEX (ИНДЕКС), ПО-ИЗЛОЖЕН (МАЧ) и TRIM (ПОДРЯЗВАНЕ):
=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))
Тъй като това е формула за масив, не забравяйте да натиснете Ctrl + Shift + Enter вместо обичайното Въведетеза правилно въвеждане на формулата.
Грешка #VALUE! във формули с VLOOKUP
В повечето случаи Microsoft Excel отчита грешка #VALUE! (#VALUE!), когато стойността, използвана във формулата, не съответства на типа данни. Относно ВПР, тогава обикновено има две причини за грешката #VALUE!.
1. Стойността, която търсите, е по-дълга от 255 знака
Внимавайте: функция ВПР не може да търси стойности, съдържащи повече от 255 знака. Ако стойността, която търсите надвишава това ограничение, ще получите съобщение за грешка. #VALUE!.
Решение: Използвайте куп функции ИНДЕКС+МАЧ (ИНДЕКС + МАЧ). По-долу е дадена формула, която ще се справи добре с тази задача:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Пълният път до работната книга за търсене не е посочен
Ако извличате данни от друга работна книга, трябва да посочите пълния път до този файл. По-конкретно, трябва да включите името на работната книга (включително разширението) в квадратни скоби [ ], последвано от името на листа, последвано от удивителен знак. Цялата тази конструкция трябва да бъде оградена с апостроф, в случай че името на книгата или листа съдържа интервали.
Ето пълната структура на функцията ВПР за търсене в друга книга:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Истинската формула може да изглежда така:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Тази формула ще търси стойността на клетката A2 в колона B върху листа Sheet1 в работната книга Нови цени и извлечете съответната стойност от колоната D.
Ако някоя част от пътя на таблицата е пропусната, вашата функция ВПР няма да работи и ще отчете грешка #VALUE! (дори ако работната книга с таблицата за справка е отворена в момента).
За повече информация относно функцията ВПРпрепращане към друг Excel файл, вижте урока: Търсене в друга работна книга с помощта на VLOOKUP.
3. Аргумент Column_num е по-малко от 1
Трудно е да си представим ситуация, в която някой въвежда стойност, по-малка от 1за да посочите колоната, от която да извлечете стойността. Въпреки че е възможно, ако стойността на този аргумент се изчислява от друга функция на Excel, вложена вътре ВПР.
Така че, ако се случи, че аргументът col_index_num (колона_номер) по-малко от 1функция ВПР също ще съобщи за грешка #VALUE!.
Ако аргументът col_index_num (номер_на_колона) е по-голям от броя на колоните в дадения масив, ВПР ще съобщи за грешка #REF! (#SSYL!).
Грешка #NAME? във VLOOKUP
Най-простият случай е грешка #NAME? (#NAME?) – ще се появи, ако случайно напишете име на функция с грешка.
Решението е очевидно – проверете си правописа!
VLOOKUP не работи (ограничения, предупреждения и решения)
В допълнение към доста сложния синтаксис, ВПР има повече ограничения от всяка друга функция на Excel. Поради тези ограничения, на пръв поглед прости формули с ВПР често водят до неочаквани резултати. По-долу ще намерите решения за няколко често срещани сценария, при които ВПР не е наред.
1. VLOOKUP не е чувствителен към главни и малки букви
функция ВПР не прави разлика между главни букви и приема малки и главни букви като еднакви. Следователно, ако има няколко елемента в таблицата, които се различават само по регистър, функцията VLOOKUP ще върне първия намерен елемент, независимо от регистъра.
Решение: Използвайте друга функция на Excel, която може да извършва вертикално търсене (LOOKUP, SUMPRODUCT, INDEX и MATCH) в комбинация с ТОЧНОA, който разграничава регистъра. За повече подробности можете да научите от урока – 4 начина да направите VLOOKUP чувствителен към главни и малки букви в Excel.
2. VLOOKUP връща първата намерена стойност
Както вече знаете, ВПР връща стойността от дадената колона, съответстваща на първото намерено съвпадение. Можете обаче да го накарате да извлече 2-ро, 3-то, 4-то или всяко друго повторение на желаната от вас стойност. Ако трябва да извлечете всички дублирани стойности, ще ви трябва комбинация от функции INDEX (ИНДЕКС), ПОНЕ (МАЛКО) и LINE (РЕД).
3. Колона е добавена или премахната към таблицата
За съжаление, формулите ВПР спрат да работят всеки път, когато се добави или премахне нова колона към таблицата за справка. Това се случва, защото синтаксисът ВПР изисква да посочите пълния обхват на търсенето и конкретния номер на колона за извличане на данни. Естествено, както зададеният диапазон, така и номерът на колоната се променят, когато изтриете колона или вмъкнете нова.
Решение: И отново функциите бързат да помогнат INDEX (ИНДЕКС) и ПО-ИЗЛОЖЕН (СЪВПАДА). Във формулата ИНДЕКС+МАЧ Отделно дефинирате колони за търсене и извличане и в резултат на това можете да изтриете или вмъкнете толкова колони, колкото искате, без да се притеснявате, че трябва да актуализирате всички свързани формули за търсене.
4. Препратките към клетките са изкривени при копиране на формула
Това заглавие обяснява изчерпателно същността на проблема, нали?
Решение: Винаги използвайте абсолютни препратки към клетки (със символа $) на записи диапазона, например $A$2:$C$100 or $A:$C. В лентата с формули можете бързо да превключите типа връзка, като щракнете F4.
VLOOKUP – работа с функциите IFERROR и ISERROR
Ако не искате да плашите потребителите със съобщения за грешки # N / A, #VALUE! or #NAME?, можете да покажете празна клетка или свое собствено съобщение. Можете да направите това, като поставите ВПР във функция АКО ГРЕШКА (IFERROR) в Excel 2013, 2010 и 2007 или използвайте куп функции АКО+ISГРЕШКА (IF+ISERROR) в по-ранни версии.
VLOOKUP: работа с функцията IFERROR
Синтаксис на функцията АКО ГРЕШКА (IFERROR) е проста и говори сама за себе си:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Тоест за първия аргумент вмъквате стойността, която да се проверява за грешка, а за втория аргумент посочвате какво да се върне, ако се открие грешка.
Например, тази формула връща празна клетка, ако стойността, която търсите, не е намерена:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Ако искате да покажете свое собствено съобщение вместо стандартното съобщение за грешка на функцията ВПР, поставете го в кавички, така:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: работа с функцията ISERROR
Тъй като функцията АКО ГРЕШКА се появи в Excel 2007, когато работите в по-ранни версии, ще трябва да използвате комбинацията IF (АКО) и ЕОШИБКА (ISERROR) като това:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Например формулата IF+ISERROR+VLOOKUP, подобно на формулата IFERROR+VLOOKUPпоказано по-горе:
=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;ЛОЖЬ))
Това е всичко за днес. Надявам се този кратък урок да ви помогне да се справите с всички възможни грешки. ВПР и накарайте вашите формули да работят правилно.