Функция VLOOKUP на Excel

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

• Стъпка по стъпка обяснение на аргументите
• Синтаксис и аргументи
• VLOOKUP Примери
  Точно съвпадение срещу приблизително съвпадение   |   Vlookup с множество условия
• Често срещани грешки и решения на VLOOKUP
  Грешка #N/A   |   Грешка #VALUE   |   Грешка #REF   |   Неправилна стойност

VLOOKUP примери .xlsx VLOOKUP грешки .xlsx

Свързани видеоклипове

Стъпка по стъпка обяснение на аргументите

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

Стъпка 1: Стартирайте функцията VLOOKUP

Изберете клетка (H6 в този случай), за да изведете резултата, след което стартирайте функцията VLOOKUP, като въведете следното съдържание в Формула Бар.

=VLOOKUP(

Стъпка 2: Посочете търсената стойност

Първо, посочете стойността за търсене (което е това, което търсите) във функцията VLOOKUP. Тук препращам към клетка G6, която съдържа определен идентификационен номер 1005.

=VLOOKUP(G6

Стъпка 3: Посочете табличния масив

След това посочете диапазон от клетки, съдържащ както стойността, която търсите, така и стойността, която искате да върнете. В този случай избирам диапазона B6:E12. Сега формулата изглежда, както следва:

=VLOOKUP(G6,B6:E12

=VLOOKUP(G6,$B$6:$E$12

Стъпка 4: Посочете колоната, от която искате да върнете стойност

След това посочете колоната, от която искате да върнете стойност.

В този пример, тъй като трябва да върна имейла въз основа на идентификационен номер, тук въвеждам число 4, за да кажа на VLOOKUP да върне стойност от четвъртата колона на диапазона от данни.

=VLOOKUP(G6,B6:E12,4

Стъпка 5: Намерете приблизително или точно съвпадение

И накрая, определете дали търсите приблизително или точно съвпадение.

• За да намерите точно съвпадение, трябва да използвате FALSE като последен аргумент.
• За да намерите приблизително съвпадение, Използвайте TRUE като последен аргумент или просто го оставете празно.

В този пример използвам FALSE за точно съвпадение. Формулата сега изглежда така:

=VLOOKUP(G6,B6:E12,4,FALSE

Натиснете клавиша Enter, за да получите резултата

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

Синтаксис и аргументи

=VLOOKUP (lookup_value, table_array, col_index, [range_lookup])

• Търсене_стойност (задължително): Стойността (реална стойност или препратка към клетка), която търсите. Не забравяйте, че тази стойност трябва да е в първата колона на table_array.
• Таблица_масив (задължително): Диапазон от клетки съдържа както колоната на търсената стойност, така и колоната на върнатата стойност.
• Col_index (задължително): Цяло число представлява номера на колоната, която съдържа върнатата стойност. Започва с номер 1 за най-лявата колона на table_array.
• Търсене_обхват (по избор): Логическа стойност, която определя дали искате VLOOKUP да намери приблизително или точно съвпадение.
  • Приблизително съвпадение - Задайте този аргумент на TRUE, 1 или го остави празен.
    важно: За да намерите приблизително съвпадение, стойностите в първата колона на table_array трябва да бъдат сортирани във възходящ ред, в случай че VLOOKUP върне грешен резултат.
  • Точно съвпадение - Задайте този аргумент на FALSE or 0.

Примери

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

Пример 1: Точно съвпадение срещу приблизително съвпадение във VLOOKUP

Ако сте объркани относно точното съвпадение и приблизителното съвпадение, когато използвате VLOOKUP, този раздел може да ви помогне да изчистите това объркване.

Точно съвпадение във VLOOKUP

В този пример ще намеря съответните имена въз основа на резултатите, изброени в диапазона E6:E8, така че въвеждам следната формула в клетка F6 и плъзгам манипулатора за автоматично попълване надолу до F8. В тази формула последният аргумент е посочен като FALSE за извършване на търсене на точно съвпадение.

=VLOOKUP(E6,$B$6:$C$12,2,FALSE)

Въпреки това, тъй като резултатът 98 не съществува в първата колона на диапазона от данни, VLOOKUP връща резултат за грешка #N/A.

Приблизително съвпадение във VLOOKUP

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

=VLOOKUP(E6,$B$6:$C$12,2,TRUE)

Тъй като резултатът 98 не съществува, VLOOKUP намира следващата най-голяма стойност, която е по-малка от 98, която е 95, и връща името на резултата 95 като най-близък резултат.

Пример 2: Използвайте VLOOKUP с множество критерии

Този раздел демонстрира как да използвате VLOOKUP с множество условия в Excel. Както е показано на екранната снимка по-долу, ако се опитвате да намерите заплата въз основа на предоставено име (в клетка H5) и отдел (в клетка H6), следвайте стъпките по-долу, за да го направите.

Стъпка 1: Добавете помощна колона, за да свържете стойностите от колоните за справка

В този случай трябва да създадем помощна колона, за да свържем стойностите от Име колона и отдел колона.

1. Добавете помощна колона отляво на вашия диапазон от данни и дайте заглавие на тази колона. Вижте екранна снимка:
   
   
2. В тази помощна колона изберете първата клетка под заглавката, въведете следната формула в Лента с формули, и натиснете Въведете.
   

   =C6&" "&D6

   
   бележки: В тази формула използваме амперсанд (&), за да обединим текста в две колони, за да създадем един текст.

   • C6 е първото име на Име колона за присъединяване, D6 е първият отдел на отдел колона за присъединяване.
   • Стойностите на тези две клетки са свързани с интервал между тях.
3. Изберете тази клетка с резултати, след което плъзнете Манипулатор за автоматично попълване надолу, за да приложите тази формула към други клетки в същата колона.
   
   

Стъпка 2: Приложете функцията VLOOKUP с дадените критерии

Изберете клетка, в която искате да изведете резултата (тук избирам I7), въведете следната формула в Лента с формули, след което натиснете Въведете.

=VLOOKUP(I5& " "&I6,B6:F12,5,FALSE)

Резултат

Често срещани грешки и решения на VLOOKUP

Този раздел изброява често срещаните грешки, които може да срещнете, когато използвате VLOOKUP, и предоставя решения за коригирането им.

Връща се грешка #N/A

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

Причина 1: Търсената стойност не е в първата колона на table_array

Едно от ограниченията на Excel VLOOKUP е, че ви позволява да гледате само отляво надясно. Така че търсените стойности трябва да са в първата колона на table_array.

Както е показано на екранната снимка по-долу, искам да върна име въз основа на дадената длъжност. Тук стойността за търсене (мениджър продажби) е във втората колона на table_array и върнатата стойност е вляво от колоната за търсене, така че VLOOKUP връща грешка #N/A.

Решения

Можете да приложите някое от следните решения, за да коригирате тази грешка.

• Пренаредете колоните
  
  Можете да пренаредите колоните, за да поставите колоната за справка в първата колона на table_array.
• Използвайте функциите INDEX и MATCH заедно
  
  Тук използваме функциите INDEX и MATCH заедно като алтернатива на VLOOKUP за решаване на този проблем.

  =INDEX(B6:B12,MATCH(F6,C6:C12,0))

  
• Използвайте функцията XLOOKUP (достъпна в Excel 365, Excel 2021 и по-нови версии)
  

  =XLOOKUP(F6,C6:C12,B6:B12)

Причина 2: Търсената стойност не е намерена в колоната за справка (точно съвпадение)

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

Както е показано в примера по-долу, ние ще намерим името въз основа на даден резултат от 98 в E6. Този резултат обаче не съществува в първата колона на диапазона от данни, така че VLOOKUP връща резултат за грешка #N/A.

Решения

За да коригирате тази грешка, можете да опитате едно от следните решения.

• Ако искате VLOOKUP да търси следващата най-голяма стойност, която е по-малка от търсената стойност, променете последния аргумент FALSE (точно съвпадение) към TRUE (приблизително съвпадение). За повече информация, моля вижте Пример 1: Точно съвпадение срещу приблизително съвпадение с помощта на VLOOKUP.
• За да избегнете промяна на последния аргумент и да получите напомняне, в случай че търсената стойност не бъде намерена, можете да включите функцията VLOOKUP във функцията IFERROR:
  

  =IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")

Причина 3: Стойността за търсене е по-малка от най-малката стойност в колоната за търсене (приблизително съвпадение)

Както е показано на екранната снимка по-долу, вие извършвате търсене на приблизително съвпадение. Стойността, която търсите (ID номер 1001 в този случай) е по-малка от най-малката стойност 1002 в колоната за търсене, следователно VLOOKUP връща грешка #N/A.

Решения

Ето две решения за вас.

• Уверете се, че търсената стойност е по-голяма или равна на най-малката стойност в справочната колона.
• Ако искате Excel да ви напомни, че търсената стойност не е намерена, просто вмъкнете функцията VLOOKUP във функцията IFERROR, както следва:
  

  =IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")

Причина 4: Числата са форматирани като текст

Както можете да видите на екранната снимка по-долу, резултатът за грешка #N/A в този пример се дължи на несъответствие на типа данни между клетката за справка (G6) и колоната за справка (B6:B12) на оригиналната таблица. Тук стойността в G6 е число, а стойностите в диапазона B6:B12 са числа, форматирани като текст.

Решения

За да разрешите този проблем, трябва да преобразувате търсената стойност обратно в число. Ето два метода за вас.

• Приложете функцията Преобразуване в число
  
  Щракнете върху клетката, в която искате да преобразувате текста в число, изберете този бутон  до клетката и след това изберете Преобразуване в число.
  
• Приложете удобен инструмент за пакетно конвертиране между текст и число
  
  - Преобразуване между текст и число функция на Kutools за Excel ви помага лесно да конвертирате диапазон от клетки от текст в число и обратно. Вземете 30-дневен пълнофункционален безплатен пробен период сега!

Причина 5: table_array не е константа при плъзгане на формулата VLOOKUP към други клетки

Както е показано на екранната снимка по-долу, има две стойности за търсене в E6 и E7. След като получите първия резултат във F6, плъзнете формулата VLOOKUP от клетка F6 до F7, върнат резултат за грешка #N/A. Това е така, защото препратките към клетки (B6:C12) са относителни по подразбиране и се коригират, докато се движите надолу през редовете. Масивът на таблицата е преместен надолу към B7:C13, който вече не съдържа резултата за търсене 73.

Решение

Трябва да заключите табличния масив, за да го поддържате постоянен, като добавите a $ знак преди редовете и колоните в препратките към клетките. За да научите повече за абсолютната препратка в Excel, разгледайте този урок: Абсолютна справка на Excel (как се прави и използва).

Връща се грешка #VALUE

Следните условия могат да накарат VLOOKUP да върне резултат за грешка #VALUE.

Причина 1: Стойността за търсене надвишава 255 знака

Както е показано на екранната снимка по-долу, търсената стойност в клетка H4 надхвърля 255 знака, така че VLOOKUP връща резултат за грешка #VALUE.

Решения

За да заобиколите това ограничение, можете да приложите различна функция за търсене, която може да обработва по-дълги низове. Опитайте една от следните формули.

• ИНДЕКС и МАЧ:
  

  =INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=H4, 0), 0))

  
• Функция XLOOKUP (налично в Excel 365, Excel 2021 и по-нови версии):
  

  =XLOOKUP(H4,B5:B11,E5:E11)

Причина 2: Аргументът col_index е по-малък от 1

Индексът на колоната указва номера на колоната в масива на таблицата, който съдържа стойността, която искате да върнете. Този аргумент трябва да е положително число, което съответства на валидна колона в масива на таблицата.

Ако въведете индекс на колона, който е по-малък от 1 (т.е. нула или отрицателен), VLOOKUP няма да може да намери колоната в масива на таблицата.

Решение

За да коригирате този проблем, уверете се, че аргументът за индекс на колона във вашата VLOOKUP формула е положително число, което съответства на валидна колона в масива на таблицата.

Връща се грешка #REF

Този раздел изброява една причина, поради която VLOOKUP връща грешка #REF и предоставя решения на този проблем.

Причина: Аргументът col_index е по-голям от броя на колоните

Както можете да видите на екранната снимка по-долу, масивът от таблици има само 4 колони. Обаче индексът на колоната, който сте посочили във формулата на VLOOKUP, е 5, което е по-голямо от броя на колоните в масива на таблицата. В резултат на това VLOOKUP няма да може да намери колоната и в крайна сметка ще върне грешка #REF.

Решения

• Посочете правилен номер на колона
  Уверете се, че аргументът за индекс на колона във вашата VLOOKUP формула е число, което съответства на валидна колона в масива на таблицата.
• Автоматично получаване на номера на колоната въз основа на посочената заглавка на колона
  Ако таблицата съдържа много колони, може да имате проблеми с определянето на правилния номер на индекс на колона. Тук можете да вложите функцията MATCH във функцията VLOOKUP, за да намерите позицията на колоната въз основа на заглавка на определена колона.

  =VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)

  Забележка: В горната формула, MATCH("Имейл";B5:E5; 0) функцията се използва за получаване на номера на колоната на "Имейл" в диапазона от дати B6:E12. Тук резултатът е 4, който се използва като col_index във функцията VLOOKUP.

Връща се неправилна стойност

Ако установите, че VLOOKUP не връща правилния резултат, това може да се дължи на следните причини

Причина 1: Колоната за справка не е сортирана във възходящ ред

Ако сте задали последния аргумент на TRUE (или остави го празен) за приблизително съвпадение и колоната за справка не е сортирана във възходящ ред, получената стойност може да е неправилна.

Решение

Сортирането на колоната за справка във възходящ ред може да ви помогне да разрешите този проблем. За да направите това, моля, следвайте стъпките по-долу:

1. Изберете клетките с данни в колоната за справка, отидете на Дата кликнете върху Сортиране от най-малкото към най-голямото в Сортиране и филтриране група.
2. в Предупреждение за сортиране диалогов прозорец, изберете Разширете селекцията и щракнете върху OK.

Причина 2: Колона е вмъкната или премахната

Както е показано на екранната снимка по-долу, стойността, която първоначално исках да върна, е в четвъртата колона на табличния масив, така че посочвам номера на col_index като 4. Когато се вмъкне нова колона, колоната с резултати става петата колона на таблицата масив, карайки VLOOKUP да върне резултата от грешна колона.

Решения

Ето две решения за вас.

• Можете ръчно да промените номера на индекса на колоната, за да съответства на позицията на колоната за връщане. Формулата тук трябва да се промени на:

  =VLOOKUP(H6,B6:F12,5,FALSE)

• Ако винаги искате да връщате резултата от определена колона, като например колоната Имейл в този пример. Следната формула може да помогне за автоматично съпоставяне на индекса на колона въз основа на дадената заглавка на колона, независимо дали колоните са вмъкнати или премахнати от масива на таблицата.

  =VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)

Други функционални бележки

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

Свързани статии