Садржај
Ова лекција објашњава како се брзо носити са ситуацијом у којој функција ВПР (ВЛООКУП) не жели да ради у програму Екцел 2013, 2010, 2007 и 2003 и како да идентификује и поправи уобичајене грешке и превазиђе ограничења ВПР.
У неколико претходних чланака истражили смо различите аспекте функције ВПР у Екцел-у. Ако сте их пажљиво прочитали, сада бисте требали бити стручњак у овој области. Међутим, многи стручњаци за Екцел верују не без разлога ВПР једна од сложенијих особина. Има гомилу ограничења и карактеристика које постају извор многих проблема и грешака.
У овом чланку ћете наћи једноставна објашњења грешака #АТ (#Н/А), #НАМЕ? (#НАМЕ?) и #ВАЛУЕ! (#ВРЕДНОСТ!) који се појављују када радите са функцијом ВПР, као и технике и методе поступања са њима. Почећемо са најчешћим случајевима и најочигледнијим разлозима зашто. ВПР не ради, па је боље проучити примере редоследом којим су дати у чланку.
Исправљање грешке #Н/А у функцији ВЛООКУП у Екцел-у
У формулама са ВПР порука о грешци #АТ (#Н/А) значи није доступна (без података) – појављује се када Екцел не може да пронађе вредност коју тражите. Ово се може догодити из неколико разлога.
1. Жељена вредност је погрешно написана
Добра идеја да прво проверите ову ставку! Грешке у куцању се често јављају када радите са веома великим количинама података, који се састоје од хиљада редова, или када је вредност коју тражите уписана у формулу.
2. #Н/А грешка при тражењу приближног подударања са ВЛООКУП-ом
Ако користите формулу са приближним условом претраге подударања, тј. аргументом ранге_лоокуп (ранге_лоокуп) је ТРУЕ или није наведено, ваша формула може пријавити грешку # Н / А у два случаја:
- Вредност коју треба потражити је мања од најмање вредности у низу који се тражи.
- Колона за претрагу није сортирана у растућем редоследу.
3. #Н/А грешка при тражењу тачног подударања са ВЛООКУП-ом
Ако тражите тачно подударање, односно аргумент ранге_лоокуп (ранге_лоокуп) је ФАЛСЕ и тачна вредност није пронађена, формула ће такође пријавити грешку # Н / А. Сазнајте више о томе како да тражите тачна и приближна подударања са функцијом ВПР.
4. Колона за претрагу није крајња лева
Као што вероватно знате, једно од најзначајнијих ограничења ВПР то је да не може да буде окренута на лево, па стога колона за тражење у вашој табели мора бити крајња лева. У пракси често заборављамо на ово, што доводи до нерадне формуле и грешке. # Н / А.
Одлука: Ако није могуће променити структуру података тако да колона за претрагу буде крајња лева, можете користити комбинацију функција ИНДЕКС (ИНДЕКС) и ИЗЛОЖЕНИЈИ (МАТЦХ) као флексибилнија алтернатива за ВПР.
5. Бројеви су форматирани као текст
Још један извор грешке # Н / А у формулама са ВПР су бројеви у текстуалном формату у главној табели или табели за претрагу.
Ово се обично дешава када увезете информације из спољних база података или када унесете апостроф испред броја да бисте задржали почетну нулу.
Најочигледнији знаци броја у текстуалном формату приказани су на слици испод:
Поред тога, бројеви се могу чувати у формату општи (Генерал). У овом случају постоји само једна уочљива карактеристика – бројеви су поравнати са леве ивице ћелије, док су подразумевано поравнати са десном ивицом.
Одлука: Ако је то једна вредност, само кликните на икону грешке и изаберите Претвори у број (Претвори у број) из контекстног менија.
Ако је ово ситуација са много бројева, изаберите их и кликните десним тастером миша на изабрано подручје. У контекстном менију који се појави изаберите Формат Целлс (Формат ћелије) > картица Број (Број) > формат Број (Нумерички) и притисните OK.
6. На почетку или на крају постоји размак
Ово је најмање очигледан разлог за грешку. # Н / А у функцији ВПР, пошто је визуелно тешко видети ове додатне просторе, посебно када радите са великим табелама, када је већина података ван екрана.
Решење 1: Додатни размаци у главној табели (где је функција ВЛООКУП)
Ако се у главној табели појављују додатни размаци, можете осигурати да формуле раде исправно тако што ћете приложити аргумент лоокуп_валуе (лоокуп_валуе) у функцију ТРИМ (ТРИМ):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Решење 2: Додатни размаци у табели за тражење (у колони за претрагу)
Ако су додатни размаци у колони за претрагу - једноставни начини # Н / А у формули са ВПР не може се избећи. Уместо ВПР Можете користити формулу низа са комбинацијом функција ИНДЕКС (ИНДЕКС), ИЗЛОЖЕНИЈИ (МАТЦХ) и ТРИМ (ТРИМ):
=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))
Пошто је ово формула низа, не заборавите да притиснете Цтрл + Схифт + Ентер уместо уобичајеног унетида правилно унесете формулу.
Грешка #ВАЛУЕ! у формулама са ВЛООКУП
У већини случајева, Мицрософт Екцел пријављује грешку #ВАЛУЕ! (#ВРЕДНОСТ!) када се вредност коришћена у формули не подудара са типом података. Се односе ВПР, онда обично постоје два разлога за грешку #ВАЛУЕ!.
1. Вредност коју тражите је дужа од 255 знакова
Будите опрезни: функција ВПР не може да тражи вредности које садрже више од 255 знакова. Ако вредност коју тражите премашује ово ограничење, добићете поруку о грешци. #ВАЛУЕ!.
Одлука: Користите гомилу функција ИНДЕКС+ПОДРЖАВАЊЕ (ИНДЕКС + ПОДРЖАВАЊЕ). Испод је формула која ће бити добра за овај задатак:
=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 на листу СхеетКСНУМКС у радној свесци Нове цене и извући одговарајућу вредност из колоне D.
Ако је било који део путање табеле изостављен, ваша функција ВПР неће радити и пријавиће грешку #ВАЛУЕ! (чак и ако је радна свеска са табелом за тражење тренутно отворена).
За више информација о функцији ВПРпозивајући се на другу Екцел датотеку, погледајте лекцију: Претраживање друге радне свеске помоћу ВЛООКУП-а.
3. Аргумент Цолумн_нум је мањи од 1
Тешко је замислити ситуацију да неко унесе вредност мању од 1да означи колону из које треба издвојити вредност. Иако је могуће ако вредност овог аргумента израчуна друга Екцел функција угнежђена унутар ВПР.
Дакле, ако се деси да аргумент цол_индек_нум (број_колоне) мањи од 1функција ВПР такође ће пријавити грешку #ВАЛУЕ!.
Ако аргумент цол_индек_нум (број_колоне) је већи од броја колона у датом низу, ВПР пријавиће грешку #РЕФ! (#ССИЛ!).
Грешка #НАМЕ? у ВЛООКУП
Најједноставнији случај је грешка #НАМЕ? (#НАМЕ?) – појавиће се ако случајно напишете име функције са грешком.
Решење је очигледно – проверите правопис!
ВЛООКУП не ради (ограничења, упозорења и одлуке)
Поред прилично компликоване синтаксе, ВПР има више ограничења од било које друге Екцел функције. Због ових ограничења, наизглед једноставне формуле са ВПР често доводе до неочекиваних резултата. У наставку ћете наћи решења за неколико уобичајених сценарија где ВПР није у реду.
1. ВЛООКУП не разликује велика и мала слова
функција ВПР не прави разлику између великих и малих слова и прихвата мала и велика слова као исте. Стога, ако у табели постоји неколико елемената који се разликују само у малим и малим словима, функција ВЛООКУП ће вратити први пронађени елемент, без обзира на велика и мала слова.
Одлука: Користите другу Екцел функцију која може да изврши вертикалну претрагу (ЛООКУП, СУМПРОДУЦТ, ИНДЕКС и МАТЦХ) у комбинацији са ЕКСАЦТА који разликује случај. За више детаља, можете научити из лекције – 4 начина да се ВЛООКУП разликује од великих и малих слова у Екцел-у.
2. ВЛООКУП враћа прву пронађену вредност
Као што већ знаш, ВПР враћа вредност из дате колоне која одговара првом пронађеном подударању. Међутим, можете наложити да извуче 2., 3., 4. или било које друго понављање вредности коју желите. Ако треба да издвојите све дупликате вредности, биће вам потребна комбинација функција ИНДЕКС (ИНДЕКС), ЛЕАСТ (МАЛА) и ЛИНЕ (РЕД).
3. Колона је додата или уклоњена у табелу
Нажалост, формуле ВПР престати да ради сваки пут када се нова колона дода или уклони у табелу за тражење. Ово се дешава због синтаксе ВПР захтева да наведете цео опсег претраге и одређени број колоне за екстракцију података. Наравно, и дати опсег и број колоне се мењају када избришете колону или уметнете нову.
Одлука: И опет функције журе да помогну ИНДЕКС (ИНДЕКС) и ИЗЛОЖЕНИЈИ (МЕЧ). У формули ИНДЕКС+ПОДРЖАВАЊЕ Засебно дефинишете колоне за претрагу и проналажење, и као резултат тога, можете да избришете или уметнете онолико колона колико желите без бриге да ћете морати да ажурирате све повезане формуле за претрагу.
4. Референце ћелије су искривљене када се копира формула
Овај наслов исцрпно објашњава суштину проблема, зар не?
Одлука: Увек користите апсолутне референце ћелије (са симболом $) на бележи опсег, на пример $А$2:$Ц$100 or $А:$Ц. У траци формуле можете брзо да промените тип везе кликом F4.
ВЛООКУП – рад са функцијама ИФЕРРОР и ИСЕРРОР
Ако не желите да уплашите кориснике порукама о грешци # Н / А, #ВАЛУЕ! or #НАМЕ?, можете приказати празну ћелију или своју поруку. То можете учинити постављањем ВПР у функцију ИФЕРРОР (ИФЕРРОР) у Екцел 2013, 2010 и 2007 или користите гомилу функција ИФ+ИСЕРРОР (ИФ+ИСЕРРОР) у старијим верзијама.
ВЛООКУП: рад са функцијом ИФЕРРОР
Синтакса функције ИФЕРРОР (ИФЕРРОР) је једноставан и говори сам за себе:
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;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
ВЛООКУП: рад са функцијом ИСЕРРОР
Пошто је функција ИФЕРРОР појавио у Екцел 2007, када радите у старијим верзијама мораћете да користите комбинацију IF (ИФ) и ЕОСХИБКА (ИСЕРРОР) овако:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",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;ЛОЖЬ))
То је све за данас. Надам се да ће вам овај кратки водич помоћи да се носите са свим могућим грешкама. ВПР и учините да ваше формуле раде исправно.