змест
Гэты ўрок тлумачыць, як хутка справіцца з сітуацыяй, калі функцыя ВПР (VLOOKUP) не хоча працаваць у Excel 2013, 2010, 2007 і 2003, і як вызначыць і выправіць распаўсюджаныя памылкі і пераадолець абмежаванні ВПР.
У некалькіх папярэдніх артыкулах мы даследавалі розныя аспекты функцыі ВПР у Excel. Калі вы ўважліва іх прачыталі, то цяпер вы павінны быць экспертам у гэтай галіне. Аднак нездарма лічаць многія знаўцы Excel ВПР адна з найбольш складаных функцый. Ён мае кучу абмежаванняў і функцый, якія становяцца крыніцай шматлікіх праблем і памылак.
У гэтым артыкуле вы знойдзеце простыя тлумачэнні памылак #AT (#Н/Д), #NAME? (#ІМЯ?) і #VALUE! (#ЗНАЧЭННЕ!), якія з'яўляюцца пры працы з функцыяй ВПР, а таксама прыёмы і спосабы барацьбы з імі. Мы пачнем з найбольш распаўсюджаных выпадкаў і найбольш відавочных прычын. ВПР не працуе, таму лепш вывучаць прыклады ў тым парадку, у якім яны прыведзены ў артыкуле.
Выпраўленне памылкі #N/A у функцыі VLOOKUP у Excel
У формулах с ВПР паведамленне пра памылку #AT (#N/A) азначае няма ў наяўнасці (няма дадзеных) – з'яўляецца, калі Excel не можа знайсці патрэбнае значэнне. Гэта можа адбыцца па некалькіх прычынах.
1. Патрэбнае значэнне напісана з памылкай
Добрая ідэя спачатку праверыць гэты элемент! Памылкі друку часта ўзнікаюць, калі вы працуеце з вельмі вялікімі аб'ёмамі дадзеных, якія складаюцца з тысяч радкоў, або калі значэнне, якое вы шукаеце, запісваецца ў формулу.
2. Памылка #N/A пры пошуку прыблізнага супадзення з дапамогай VLOOKUP
Калі вы выкарыстоўваеце формулу з прыблізным умовай пошуку супадзення, г.зн дыяпазон_пошуку (дыяпазон_пошуку) мае значэнне TRUE або не вызначана, ваша формула можа паведаміць пра памылку # N / A у двух выпадках:
- Значэнне для пошуку меншае за найменшае значэнне ў масіве, які шукаецца.
- Слупок пошуку не адсартаваны ў парадку ўзрастання.
3. Памылка #N/A пры пошуку дакладнага супадзення з дапамогай VLOOKUP
Калі вы шукаеце дакладнае супадзенне, г.зн дыяпазон_пошуку (дыяпазон_пошуку) ХЛУСНЯ і дакладнае значэнне не знойдзена, формула таксама паведаміць пра памылку # N / A. Даведайцеся больш аб тым, як шукаць дакладныя і прыблізныя супадзенні з дапамогай функцыі ВПР.
4. Слупок пошуку не самы левы
Як вы напэўна ведаеце, адно з самых істотных абмежаванняў ВПР гэта тое, што ён не можа глядзець налева, таму слупок пошуку ў вашай табліцы павінен быць крайнім злева. На практыцы мы часта пра гэта забываемся, што прыводзіць да непрацуючай формуле і памылцы. # N / A.
Рашэнне: Калі няма магчымасці змяніць структуру даных так, каб слупок пошуку быў крайнім злева, вы можаце выкарыстоўваць камбінацыю функцый індэкс (ІНДЭКС) і БОЛЬШ ВЫКЛЮЧАНЫ (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 у формуле с ВПР нельга пазбегнуць. Замест ВПР Вы можаце выкарыстоўваць формулу масіва з камбінацыяй функцый індэкс (ІНДЭКС), БОЛЬШ ВЫКЛЮЧАНЫ (МАТЧ) і 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!.
1. Значэнне, якое вы шукаеце, больш за 255 сімвалаў
Будзьце асцярожныя: функцыя ВПР не можа шукаць значэнні, якія змяшчаюць больш за 255 знакаў. Калі значэнне, якое вы шукаеце, перавышае гэты ліміт, вы атрымаеце паведамленне пра памылку. #VALUE!.
Рашэнне: Выкарыстоўвайце мноства функцый INDEX+MATCH (ІНДЭКС + СУПАДЗЕННЕ). Ніжэй прыведзена формула, якая выдатна справіцца з гэтай задачай:
=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, укладзенай унутр ВПР.
Такім чынам, калі так здарыцца, што аргумент номер_столбца (нумар_слупка) менш 1функцыя ВПР таксама паведаміць пра памылку #VALUE!.
Калі аргумент номер_столбца (нумар_слупка) больш, чым колькасць слупкоў у дадзеным масіве, ВПР паведаміць пра памылку #REF! (#SSYL!).
Памылка #NAME? у VLOOKUP
Самы просты выпадак - памылка #NAME? (#NAME?) – з'явіцца, калі вы выпадкова напішаце назву функцыі з памылкай.
Рашэнне відавочнае - праверце правапіс!
VLOOKUP не працуе (абмежаванні, засцярогі і рашэнні)
У дадатак да даволі складанага сінтаксісу, ВПР мае больш абмежаванняў, чым любая іншая функцыя Excel. З-за гэтых абмежаванняў, здавалася б, простыя формулы з ВПР часта прыводзяць да нечаканых вынікаў. Ніжэй вы знойдзеце рашэнні для некалькіх распаўсюджаных сцэнарыяў, калі ВПР не так.
1. VLOOKUP не адчувальны да рэгістра
функцыя ВПР не адрознівае рэгістр і прымае малыя і вялікія сімвалы як аднолькавыя. Такім чынам, калі ў табліцы ёсць некалькі элементаў, якія адрозніваюцца толькі рэгістрам, функцыя VLOOKUP верне першы знойдзены элемент, незалежна ад рэгістра.
Рашэнне: Выкарыстоўвайце іншую функцыю Excel, якая можа выконваць вертыкальны пошук (LOOKUP, SUMPRODUCT, INDEX і MATCH) у спалучэнні з EXACTA, які адрознівае рэгістр. Для больш падрабязнай інфармацыі вы можаце даведацца з урока - 4 спосабы зрабіць VLOOKUP адчувальным да рэгістра ў Excel.
2. VLOOKUP вяртае першае знойдзенае значэнне
Як вы ўжо ведаеце, ВПР вяртае значэнне з дадзенага слупка, адпаведнае першаму знойдзенаму супадзенню. Тым не менш, вы можаце дазволіць яму атрымаць 2-е, 3-е, 4-е або любое іншае паўтарэнне патрэбнага вам значэння. Калі вам трэба атрымаць усе паўтаральныя значэнні, вам спатрэбіцца камбінацыя функцый індэкс (ІНДЭКС), МЕНШ (МАЛЫ) і ЛІНІЯ (ШЭРАГ).
3. Слупок быў дададзены або выдалены ў табліцу
На жаль, формулы ВПР перастаюць працаваць кожны раз, калі ў табліцу пошуку дадаецца або выдаляецца новы слупок. Гэта адбываецца таму, што сінтакс ВПР патрабуе, каб вы ўказалі поўны дыяпазон пошуку і канкрэтны нумар слупка для здабывання даных. Натуральна, і зададзены дыяпазон, і нумар слупка змяняюцца, калі вы выдаляеце слупок або ўстаўляеце новы.
Рашэнне: І зноў функцыі спяшаюцца на дапамогу індэкс (ІНДЭКС) і БОЛЬШ ВЫКЛЮЧАНЫ (МАТЧ). У формуле INDEX+MATCH Вы асобна вызначаеце слупкі для пошуку і пошуку, і ў выніку вы можаце выдаляць або ўстаўляць колькі заўгодна слупкоў, не турбуючыся аб неабходнасці абнаўляць усе звязаныя формулы пошуку.
4. Спасылкі на вочкі скажаюцца пры капіраванні формулы
Гэты загаловак вычарпальна тлумачыць сутнасць праблемы, праўда?
Рашэнне: Заўсёды выкарыстоўвайце абсалютныя спасылкі на ячэйкі (з сімвалам $) на дыяпазон запісаў, напрыклад $A$2:$C$100 or $A:$C. У радку формул вы можаце хутка пераключыць тып спасылкі, націснуўшы F4.
VLOOKUP – праца з функцыямі IFERROR і ISERROR
Калі вы не хочаце палохаць карыстальнікаў паведамленнямі пра памылкі # N / A, #VALUE! or #NAME?, вы можаце паказаць пустую ячэйку або ўласнае паведамленне. Вы можаце зрабіць гэта шляхам размяшчэння ВПР у функцыю КАЛІПАМЫЛКА (IFERROR) у Excel 2013, 2010 і 2007 або выкарыстоўваць кучу функцый КАЛІ+ЁСЦЬ ПАМЫЛКА (КАЛІ+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 формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Напрыклад, формула КАЛІ+ISПАМЫЛКА+ВПР, аналагічна формуле КАЛІПАМЫЛКА+ВПРпаказана вышэй:
=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;ЛОЖЬ))
Вось і ўсё на сёння. Я спадзяюся, што гэты кароткі падручнік дапаможа вам справіцца з усімі магчымымі памылкамі. ВПР і прымусіць вашы формулы працаваць правільна.