10 примера за VLOOKUP за начинаещи и напреднали потребители

Функцията VLOOKUP е еталонът.

Ако знаете как да използвате функцията VLOOKUP, знаете нещо за Excel.

Ако не го направите, по-добре не изброявайте Excel като една от силните си страни в автобиографията си.

Направих групово интервю и след като кандидат спомена Excel като своя област на експертиза, първото нещо, което беше попитано, беше – разбирате идеята – функцията VLOOKUP.

Сега, когато знаем важността на тази функция на Excel, има смисъл да я разберем напълно, за да можем с гордост да кажем – „Знам нещо или две в Excel“.

Това ще бъде огромен урок за VLOOKUP (по моите стандарти).

Ще покрия всичко за това и след това ще ви покажа полезен и практичен пример за VLOOKUP.

Кога да използвате функцията VLOOKUP в Excel?

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

Нека вземем прост пример тук, за да разберем кога да използваме Vlookup в Excel.

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

Ето как работи:

• Отивате до таблото за обяви и започвате да търсите вашето име или регистрационен номер (движете пръста си нагоре и надолу по списъка).
• След като намерите името си, ще преместите очите си вдясно от името/регистрационния номер, за да видите резултата си.

Точно това прави функцията VLOOKUP на Excel за вас (не се колебайте да използвате този пример в следващото си интервю).

Функцията VLOOKUP търси определена стойност в колона (в примера по-горе това е вашето име) и когато намери определено съвпадение, връща стойност (токенът, който получавате) в същия ред.

синтаксис

=VLOOKUP(търсенна_стойност, таблица_масив, номер_индекс_столбец, [търсен_обхват])

Входни параметри

• търсеща_стойност –Това е стойността за търсене, която се опитвате да намерите в най-лявата колона на таблицата.Тя може да бъде стойност, препратка към клетка или текстов низ.В примера с таблицата с резултати това би било вашето име.
• таблица_масив –Това е масивът от таблици, в които търсите стойности.Това може да бъде препратка към диапазон от клетки или наименуван диапазон.В примера за таблица с резултати това би била цялата таблица, съдържаща оценките на всеки за всеки предмет
• col_index –Това е индексният номер на колоната, от който искате да получите съвпадащи стойности.В примера за таблица с дроби, ако искате дроб за математика (това е първата колона в таблица, съдържаща дроби), можете да погледнете колона 1.Ако искате физически резултат, можете да погледнете колони 1 и 2.
• [диапазон_търсене] –Тук можете да посочите дали искате точно или приблизително съвпадение.Ако е пропуснато, по подразбиране е TRUE - приблизително съвпадение(вижте допълнителни бележки по-долу).

Допълнителни бележки (скучни, но важно да се знае)

• Съвпаденията могат да бъдат точни (FALSE или 0 в range_lookup) или приблизителни (TRUE или 1).
• При приблизително търсене се уверете, че списъкът е сортиран във възходящ ред (отгоре надолу), в противен случай резултатите може да са неточни.
• Когато range_lookup е TRUE (приблизително търсене) и данните се сортират във възходящ ред:
  • Ако функцията VLOOKUP не може да намери стойността, тя връща най-голямата стойност, по-малка от lookup_value.
  • Връща грешка #N/A, ако lookup_value е по-малка от минималната стойност.
  • Ако lookup_value е текст, могат да се използват заместващи знаци (вижте примера по-долу).

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

10 примера за VLOOKUP в Excel (основни и разширени)

Ето 10 полезни примера за използване на Excel Vlookup, за да ви покажем как да го използвате в ежедневната си работа.

Пример 1 - Намерете математическия резултат на Брад

В примера с VLOOKUP по-долу имам списък с имена на ученици в най-лявата колона и оценки за различни предмети в колони B до E.

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

От данните по-горе трябва да знам какво е отбелязал Брад по математика.

Ето формулата VLOOKUP, която връща оценката на Брад по математика:

=VLOOKUP("Брад",$A$3:$E$10,2,0)

Горната формула има четири параметъра:

• "Брад": - Това е стойността за търсене.
• $A$3:$E$10 - Това е диапазонът от клетки, който разглеждаме.Не забравяйте, че Excel търси стойности за търсене в най-лявата колона.В този пример той търси името Брад в A3:A10, което е най-лявата колона на посочения масив.
• 2 – След като функцията намери името на Брад, тя отива във втората колона на масива и връща стойността в същия ред като Брад.Стойност 2 тук означава, че търсим резултати от втората колона на посочения масив.
• 0 – Това казва на функцията VLOOKUP да търси само точни съвпадения.

Ето как работи формулата VLOOKUP в примера по-горе.

Първо, той търси стойността Brad в най-лявата колона.Той търси стойността в клетка A6 отгоре надолу.

След като намери стойността, се придвижва надясно във втората колона и грабва стойността в нея.

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

Например, за да намерите резултата на Мария по химия, използвайте следната формула VLOOKUP:

=VLOOKUP("Мария",$A$3:$E$10,4,0)

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

Предимството от използването на препратки към клетки е, че прави формулата динамична.

Например, ако имате клетка с името на ученик и получавате оценки по математика, когато промените името на ученика, резултатите се актуализират автоматично (както е показано по-долу):

Ако въведете стойност за търсене, която не е намерена в най-лявата колона, се връща грешка #N/A.

Пример 2 - Двупосочно търсене

В пример 1 по-горе, ние твърдо кодирахме стойностите на колоните.Следователно формулата винаги ще връща резултата по математика, защото използваме 2 като индекс на колоната.

Но какво ще стане, ако искате да направите динамични стойността на VLOOKUP и номера на индекса на колоната.Например, както е показано по-долу, можете да промените името на ученика или името на предмета и формулата VLOOKUP ще получи правилния резултат.Ето пример за двупосочна формула VLOOKUP.

Ето пример за двупосочна функция VLOOKUP.

За да направите тази формула за двупосочно търсене, трябва също да направите колоната динамична.Така че, когато потребителят промени темата, формулата автоматично избира правилната колона (2 за математика, 3 за физика и т.н.).

За да направите това, трябва да използвате функцията MATCH като параметър на колона.

Ето формулата VLOOKUP, която ще направи това:

=VLOOKUP(G4,$A$3:$E$10,MATCH(H3,$A$2:$E$2,0),0)

Горната формула използва MATCH(H3,$A$2:$E$2,0) като номер на колоната.Функцията MATCH приема името на темата като стойност за търсене (в H3) и връща нейната позиция в A2:E2.Така че, ако използвате Math, той ще върне 2, защото Math се намира в B2 (което е втората клетка в посочения диапазон на масив).

Пример 3 - Използване на падащо меню като стойност за търсене

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

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

В зависимост от избора, формулата автоматично ще актуализира резултата.

Както е показано по-долу:

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

как да направя това:

Формулата VLOOKUP, използвана в този пример, е същата като използваната в Пример 2.

=VLOOKUP(G4,$A$3:$E$10,MATCH(H3,$A$2:$E$2,0),0)

Стойностите за търсене са преобразувани в падащи списъци.

Ето стъпките за създаване на падащ списък:

• Изберете клетката, която се нуждае от падащ списък.В този пример в G4 се нуждаем от името на ученика.
• Отидете на Данни -> Инструменти за данни -> Проверка на данни.
• В раздела Настройки на диалоговия прозорец Проверка на данни изберете Списък от падащия списък Разрешаване.
• В Източник изберете $A$3:$A$10
• Щракнете върху OK.

Сега ще видите падащия списък в клетка G4.По същия начин можете да създадете такъв в H3 за тема.

Пример 4 - Трипосочно търсене

Какво е тристранно търсене?

В Пример 2 използвахме справочна таблица, съдържаща резултати за ученици по различни предмети.Това е пример за двупосочно търсене, защото използваме две променливи, за да получим резултатите (име на ученик и име на предмет).

Сега, да кажем, че след една година един студент ще вземе три различни нива на изпити, единични изпити, междинни изпити и финални изпити (това е изпитът от студентските ми дни).

Тристранното търсене ще може да получи оценка на студента за определен предмет от определено ниво на изпит.

Както е показано по-долу:

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

Ето формулата, използвана в клетка H4:

=VLOOKUP(G4,CHOOSE(IF(H2="Unit Test",1,IF(H2="Midterm",2,3)),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23),MATCH(H3,$A$2:$E$2,0),0)

Тази формула използва функцията ИЗБИРАНЕ, за да гарантира, че е посочена правилната таблица.Нека анализираме ИЗБЕРЕТЕ част от формулата:

CHOOSE(IF(H2=”Unit Test”,1,IF(H2=”Midterm”,2,3)),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23)

Първият аргумент на формулата е IF(H2=”Unit Test”,1,IF(H2=”Midterm”,2,3)), който разглежда клетка H2 и вижда референтното тестово ниво.Ако е единичен тест, се връща $A$3:$E$7, което съдържа резултата от единичния тест.Връща $A$11:$E$15, ако е междинен изпит, в противен случай връща $A$19:$E$23.

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

Пример 5 - Вземете последната стойност от списък

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

Най-голямото положително число, което можете да използвате в Excel, е 9.99999999999999 307 + . Това също означава, че най-големият номер за търсене в номера на VLOOKUP е същият.

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

Да предположим, че имате набор от данни като следния(в A1:A14), и искате да получите последното число в списъка.

Ето формулата, която можете да използвате:

=VLOOKUP(9.99999999999999E+307,$A$1:$A$14,ВЯРНО)

Имайте предвид, че горната формула използва приблизително съвпадение на VLOOKUP  (Обърнете внимание на TRUE в края на формулата, а не на FALSE или 0).Също така имайте предвид, че тази формула VLOOKUP работи без сортиране на списъка.

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

• Тази стойност се връща, ако бъде намерено точно съвпадение.
• Ако намери стойност, по-висока от стойността за търсене, връща стойността в клетката над нея.
• Ако стойността за търсене е по-голяма от всички стойности в списъка, се връща последната стойност.

В горния пример действа третият случай.

поради9.99999999999999 307 +е най-голямото число, което може да се използва в Excel, така че когато се използва като стойност за търсене, връща последното число в списъка.

По същия начин можете да го използвате и за връщане на последния текстов елемент в списъка.Ето формулата, която може да го направи:

=VLOOKUP("zzz",$A$1:$A$8,1, TRUE )

Следвайте същата логика.Excel разглежда всички имена и тъй като zzz се счита за по-голям от всяко име/текст, започващ с буква преди zzz, той ще върне последния елемент в списъка.

Пример 6 - Частично търсене с помощта на заместващи знаци и VLOOKUP

Заместващите знаци в Excel са полезни в много ситуации.

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

Частично търсене е необходимо, когато трябва да търсите стойност в списък и няма точно съвпадение.

Например, да предположим, че имате набор от данни като този по-долу и искате да намерите компания ABC в списък, но в списъка има ABC Ltd вместо ABC.

Не можете да използвате ABC като стойност за търсене, защото няма точно съвпадение в колона A.Приблизителните съвпадения също могат да доведат до неправилни резултати и списъкът трябва да бъде сортиран във възходящ ред.

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

Въведете следната формула в клетка D2 и я плъзнете към други клетки:

=VLOOKUP("*"&C2&"*",$A$2:$A$8,1,FALSE)

Как действа тази формула?

В горната формула, вместо да се използва стойността за търсене такава, каквато е, тя е заобиколена от заместващи звездички (*) - "*"&C2&"*"

Звездичките са заместващи знаци в Excel, които могат да представляват произволен брой знаци.

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

Например клетка C2 съдържа ABC, така че функцията VLOOKUP разглежда имената в A2:A8 и търси ABC.Той намира съвпадение в клетка A2, защото съдържа ABC от ABC Ltd. Няма значение дали има знаци отляво или отдясно на ABC.Ще се счита за съвпадение, докато в текстовия низ има ABC.

Забележка: Функцията VLOOKUP винаги връща първата съответстваща стойност и спира по-нататъшните търсения.Така че, ако има ABC в списъка ООДи ABC Corporation, ще върне първото и ще игнорира останалите.

Пример 7 - VLOOKUP връща грешка въпреки съвпадението на стойностите за търсене

Ще ви подлуди, когато видите, че има съвпадаща стойност за търсене и функцията VLOOKUP връща грешка.

Например в случая по-долу има съвпадение (Matt), но функцията VLOOKUP все още връща грешка.

Сега, докато можем да видим, че има съвпадение, това, което не можем да видим с просто око, е, че може да има водещи или изоставащи интервали.Ако имате тези допълнителни интервали преди, след или между стойността за търсене, това не е точно съвпадение.

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

Решението тук е функцията TRIM.Той премахва всички начални или крайни интервали или допълнителни интервали между думите.

Ето формулата, която ще ви даде правилния резултат.

=VLOOKUP("Мат",ОТРЕЖИ ($A$2:$A$9),1,0)

Тъй като това е формула за масив, използвайте Control+Shift+Enter вместо Enter.

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

Пример 8 - Извършване на търсене, чувствително към главни букви

По подразбиране стойностите за търсене във функцията VLOOKUP не са чувствителни към малки и големи букви.Например, ако вашата стойност за търсене е MATT, matt или Matt, всичко е същото за функцията VLOOKUP.Връща първата съответстваща стойност, независимо от регистра.

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

Това е пример:

Както можете да видите, трите клетки имат едно и също име (в A2, A4 и A5), но с различни главни букви.Вдясно имаме три имена (Мат, МАТ и мат) и техните резултати по математика.

Функцията VLOOKUP понастоящем не е оборудвана да обработва стойности за търсене, чувствителни към главни букви.В примера по-горе той винаги връща 38, което е резултатът на Мат в A2.

За да го направим чувствителен към главни букви, трябва да използваме помощна колона (както е показано по-долу):

За да получите стойността в колоната за помощ, използвайте функцията =ROW().Ще получи само номера на реда в клетката.

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

=VLOOKUP(MAX(EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9))),$B$2:$C$9,2,0)

Сега нека го разбием и разберем какво прави:

• ТОЧНО(E2,$A$2:$A$9) – Тази част сравнява стойността за търсене в E2 с всички стойности в A2:A9.Връща масив от TRUE/FALSE, където TRUE се връща в случай на точно съвпадение.В този случай той ще върне следния масив: {TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.
• EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9) - Тази част умножава масива TRUE/FALSE с номера на реда. Докато има TRUE, тя ще даде номера на реда , в противен случай дава 0. В този случай връща {2;0;0;0;0;0;0;0}.
• MAX(EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9))) – 這部分返回數字數組中的最大值。В този случай ще върне 2 (което е точният номер на реда).
• Сега просто използваме това число като стойност за търсене и използваме масива за търсене като B2:C9

Забележка: Тъй като това е формула за масив, използвайте Control + Shift + Enter, вместо просто да пишете.

Пример 9 - Използване на VLOOKUP с множество условия

Функцията VLOOKUP на Excel, в основната си форма, търси стойност за търсене и връща съответната стойност от определен ред.

Но обикновено VLOOKUP е необходим в Excel с множество критерии.

Да предположим, че имате данни, съдържащи имена на ученици, типове тестове и резултати по математика (показани по-долу):

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

Например, ако се опитате да използвате VLOOKUP с Matt като стойност за търсене, той винаги ще връща 91, резултатът за първото появяване на Matt в списъка.За да получите резултата Matt за всеки тип изпит (единица, междинен и окончателен), трябва да създадете уникална стойност за търсене.

Това може да стане с помощта на помощни колони.Първата стъпка е да вмъкнете помощна колона отляво на дроба.

Сега, за да създадете уникален квалификатор за всеки екземпляр на име, използвайте следната формула в C2: =A2&”|”&B2

Копирайте тази формула във всички клетки в колоната за помощ.Това ще създаде уникална стойност за търсене за всеки екземпляр на името (както е показано по-долу):

Сега, докато има дублиращи се имена, когато имената се комбинират с изпитни нива, няма дубликати.

Това е лесно, защото сега можете да използвате стойността на помощната колона като стойност за търсене.

Това е формулата, която ви дава резултата в G3:I8.

=VLOOKUP($F3&"|"&G$2,$C$2:$D$19,2,0)

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

Забележка: В горния пример използвахме | Използва се като разделител при добавяне на текст към помощни колони.В някои много редки (но възможни) случаи може да имате два различни критерия, но комбинирането им в крайна сметка ще даде същия резултат.Това е пример:

Имайте предвид, че докато A2 и A3 са различни, а B2 и B3 са различни, комбинацията в крайна сметка е една и съща.Въпреки това, ако използвате разделители, тогава дори комбинацията ще бъде различна (D2 и D3).

Ето урок за това как да използвате VLOOKUP с множество условия, без да използвате помощни колони.

Пример 10 - Обработка на грешки при използване на функцията VLOOKUP

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

Можете лесно да премахнете стойностите за грешки с пълен текст с произволно значение, като „Не е налично“ или „Не е намерено“.

Например, в примера по-долу, когато се опитате да намерите резултата на Брад в списъка, той връща грешка, защото името на Брад не е в списъка.

За да премахнете тази грешка и да я замените с нещо смислено, увийте функцията VLOOKUP във функция IFERROR.

Ето формулата:

=АКО ГРЕШКА(VLOOKUP(D2,$A$2:$B$7,2,0),"Не е намерено")

Функцията IFERROR проверява дали стойността, върната от първия аргумент (в този случай функцията VLOOKUP), е грешка.Ако не е грешка, върнете стойността чрез функцията VLOOKUP, в противен случай върнете Not Found.

Функцията IFERROR е налична от Excel 2007.Ако използвате предишна версия, моля, използвайте следните функции:

=IF(ISERROR(VLOOKUP(D2,$A$2:$B$7,2,0)),"Not Found",VLOOKUP(D2,$A$2:$B$7,2,0))

Това е всичко за този урок за VLOOKUP.

Опитвам се да представя основния пример за използване на функцията Vlookup в Excel.Кажете ми в секцията за коментари, ако искате да видите още примери, добавени към този списък.

Свързани функции на Excel:

• Excel функция HLOOKUP.
• Excel XLOOKUP функция
• Индексна функция на Excel.
• Непряка функция на Excel.
• Функция за съвпадение в Excel.
• Функция за изместване на Excel.