Функция VLOOKUP - Въведение
Функцията VLOOKUP е еталонът.
Знаете нещо в Excel, ако знаете как да използвате функцията VLOOKUP.
Ако не го направите, по -добре не посочвайте Excel като една от силните си страни в автобиографията си.
Бях част от панелните интервюта, където веднага щом кандидатът спомена Excel като своя област на експертиза, първото нещо, което беше попитано беше - разбрахте - функцията VLOOKUP.
Сега, когато знаем колко важна е тази функция на Excel, има смисъл да я оценим напълно, за да можем с гордост да кажем - „Знам нещо или две в Excel“.
Това ще бъде масивен урок за VLOOKUP (по моите стандарти).
Ще разгледам всичко, което трябва да се знае за него, и след това ще ви покажа полезни и практични примери за VLOOKUP.
Така че се закопчайте.
Време е за излитане.
Кога да използвате функцията VLOOKUP в Excel?
Функцията VLOOKUP е най -подходяща за ситуации, когато търсите съвпадаща точка от данни в колона и когато се намери съответстващата точка от данни, отивате вдясно в този ред и извличате стойност от клетка, която е определен брой колони вдясно.
Нека вземем прост пример тук, за да разберем кога да използваме Vlookup в Excel.
Спомнете си, когато списъкът с резултатите от изпитите беше излязъл и залепен на таблото за обяви и всички свиреха, като намериха имената и резултата си (поне така се случваше, когато бях в училище).
Ето как работи:
- Отивате до таблото за обяви и започвате да търсите вашето име или регистрационен номер (прекарвате пръст отгоре надолу в списъка).
- Веднага щом забележите името си, премествате очите си вдясно от името/номера за записване, за да видите резултатите си.
И точно това прави функцията Excel VLOOKUP за вас (не се колебайте да използвате този пример в следващото си интервю).
Функцията VLOOKUP търси определена стойност в колона (в горния пример това е вашето име) и когато намери посоченото съвпадение, връща стойност в същия ред (получените марки).
Синтаксис
= VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
Аргументи за въвеждане
- lookup_value - това е търсената стойност, която се опитвате да намерите в най-лявата колона на таблица. Това може да бъде стойност, препратка към клетка или текстов низ. В примера на протокола това ще бъде вашето име.
- таблица_масив - това е табличният масив, в който търсите стойността. Това може да бъде препратка към диапазон от клетки или именуван диапазон. В примера на резултата това ще бъде цялата таблица, която съдържа оценка за всеки за всеки предмет
- col_index - това е индексният номер на колоната, от който искате да извлечете съответната стойност. В примера с резултатите, ако искате резултатите за математика (която е първата колона в таблица, която съдържа резултатите), ще погледнете в колона 1. Ако искате резултатите за физика, ще погледнете в колона 2.
- [range_lookup] - тук посочвате дали искате точно съвпадение или приблизително съвпадение. Ако е пропуснато, по подразбиране е TRUE - приблизително съвпадение (вижте допълнителните бележки по -долу).
Допълнителни бележки (скучно, но важно да се знае)
- Съответствието може да бъде точно (FALSE или 0 в range_lookup) или приблизително (TRUE или 1).
- При приблизително търсене се уверете, че списъкът е сортиран във възходящ ред (отгоре надолу), в противен случай резултатът може да е неточен.
- Когато range_lookup е TRUE (приблизително търсене) и данните са сортирани във възходящ ред:
- Ако функцията VLOOKUP не може да намери стойността, тя връща най -голямата стойност, която е по -малка от lookup_value.
- Той връща грешка #N/A, ако lookup_value е по -малка от най -малката стойност.
- Ако lookup_value е текст, могат да се използват заместващи знаци (вижте примера по -долу).
Сега, надявайки се, че имате основно разбиране за това какво може да направи функцията VLOOKUP, нека да обелим този лук и да видим някои практически примери за функцията VLOOKUP.
10 примера за Excel VLOOKUP (основни и разширени)
Ето 10 полезни примера за използване на Excel Vlookup, които ще ви покажат как да го използвате в ежедневната си работа.
Пример 1 - Намиране на математическия рейтинг на Брад
В примера VLOOKUP по-долу имам списък с имената на учениците в най-лявата колона и маркировки по различни предмети в колони B до E.
Сега да се захващаме за работа и да използваме функцията VLOOKUP за това, което прави най -добре. От горните данни трябва да знам колко Брад е вкарал в математиката.
От горните данни трябва да знам колко Брад е вкарал в математиката.
Ето формулата VLOOKUP, която ще върне резултата по математика на Брад:
= VLOOKUP ("Брад", $ A $ 3: $ E $ 10,2,0)
Горната формула има четири аргумента:
- „Брад: - това е стойността за търсене.
- $ A $ 3: $ E $ 10 - това е диапазонът от клетки, в който търсим. Не забравяйте, че Excel търси стойността за търсене в най-лявата колона. В този пример той ще търси името Brad в 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 може да търси в три различни таблици (Unit Test, Midterm и Final Exam) и връща резултата за посочения ученик по посочения предмет.
Ето формулата, използвана в клетка H4:
= VLOOKUP (G4, ИЗБЕРЕТЕ (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 и вижда какво ниво на изпит се отнася. Ако това е Unit Test, той връща $ A $ 3: $ E $ 7, който има оценките за Unit Test. Ако е междинно, връща $ A $ 11: $ E $ 15, иначе връща $ A $ 19: $ E $ 23.
Това прави масива на таблиците VLOOKUP динамичен и следователно го прави трипосочно търсене.
Пример 5 - Получаване на последната стойност от списък
Можете да създадете формула VLOOKUP, за да получите последната числова стойност от списък.
Най -голямото положително число, което можете да използвате в Excel, е 9.9999999999999999E+307. Това също означава, че най -големият номер за търсене в номера на VLOOKUP също е същият.
Не мисля, че някога ще имате нужда от изчисления, включващи толкова голям брой. И точно това можем да използваме, за да получим последния номер в списък.
Да предположим, че имате набор от данни (в A1: A14) както е показано по -долу и искате да получите последния номер в списъка.
Ето формулата, която можете да използвате:
= VLOOKUP (9.99999999999999E+307, $ A $ 1: $ A $ 14,ВЯРНО)
Обърнете внимание, че формулата по -горе използва приблизително съвпадение VLOOKUP (забележете TRUE в края на формулата, вместо FALSE или 0). Също така имайте предвид, че не е необходимо списъкът да бъде сортиран, за да работи тази формула VLOOKUP.
Ето как работи приблизителната функция VLOOKUP. Той сканира най -лявата колона отгоре надолу.
- Ако намери точно съвпадение, връща тази стойност.
- Ако намери стойност, която е по -висока от стойността за търсене, тя връща стойността в клетката над нея.
- Ако стойността за търсене е по -голяма от всички стойности в списъка, тя връща последната стойност.
В горния пример действа третият сценарий.
От 9.9999999999999999E+307 е най -голямото число, което може да се използва в Excel, когато това се използва като стойност за търсене, връща последното число от списъка.
По същия начин можете да го използвате и за връщане на последния текстов елемент от списъка. Ето формулата, която може да направи това:
= VLOOKUP ("zzz", $ A $ 1: $ A $ 8,1,ВЯРНО)
Същата логика следва. Excel разглежда всички имена и тъй като zzz се счита за по -голям от всяко име/текст, започващ с азбуки преди zzz, той ще върне последния елемент от списъка.
Пример 6 - Частично търсене с помощта на заместващи знаци и VLOOKUP
Заместващите знаци на Excel могат да бъдат наистина полезни в много ситуации.
Това е магическата отвара, която дава на формулите ви супер сили.
Частично търсене е необходимо, когато трябва да търсите стойност в списък и няма точно съвпадение.
Да предположим например, че имате набор от данни, както е показано по -долу, и искате да потърсите компанията ABC в списък, но в списъка има ABC Ltd вместо ABC.
Не можете да използвате ABC като стойност за търсене, тъй като няма точно съвпадение в колона А. Приблизителното съвпадение също води до грешни резултати и изисква списъкът да бъде сортиран във възходящ ред.
Можете обаче да използвате заместващ знак във функцията 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 ("Matt", TRIM ($ A $ 2: $ A $ 9), 1,0)
Тъй като това е формула за масив, използвайте Control + Shift + Enter вместо просто Enter.
Друг начин може да бъде първо да третирате вашия масив за търсене с функцията TRIM, за да се уверите, че всички допълнителни пространства са изчезнали, и след това да използвате функцията VLOOKUP както обикновено.
Пример 8 - Извършване на регистър, чувствителен към регистър
По подразбиране стойността за търсене във функцията VLOOKUP не е чувствителна към регистъра. Например, ако вашата стойност за търсене е MATT, matt или Matt, всичко е същото за функцията VLOOKUP. Той ще върне първата съвпадаща стойност, независимо от случая.
Но ако искате да направите справка с регистър, трябва да използвате функцията EXACT заедно с функцията VLOOKUP.
Ето един пример:
Както можете да видите, има три клетки със същото име (в A2, A4 и A5), но с различен регистър на азбуката. Вдясно имаме трите имена (Matt, MATT и matt) заедно с техните резултати в математиката.
Сега функцията VLOOKUP не е оборудвана да обработва чувствителни към регистъра стойности за справка. В горния пример винаги ще връща 38, което е резултатът за Мат в А2.
За да го направим чувствителен към регистър, трябва да използваме помощна колона (както е показано по -долу):
За да получите стойностите в помощната колона, използвайте функцията = ROW (). Той просто ще получи номера на реда в клетката.
След като имате помощната колона, ето формулата, която ще даде резултатите от търсенето, чувствителни към регистъра.
= VLOOKUP (MAX (ТОЧНО (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9))), $ B $ 2: $ C $ 9,2,0)
Сега нека разберем и разберем какво прави това:
- ТОЧНО (E2, $ A $ 2: $ A $ 9) - Тази част ще сравни стойността на търсене в E2 с всички стойности в A2: A9. Той връща масив от TRUEs/FALSEs, където TRUE се връща, когато има точно съвпадение. В този случай той ще върне следния масив: {TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.
- ТОЧНО (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9) - Тази част умножава масива от TRUEs/FALSEs с номера на реда. Където има TRUE, тя дава номера на реда , иначе дава 0. В този случай ще връща {2; 0; 0; 0; 0; 0; 0; 0}.
- MAX (ТОЧНО (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9))) - Тази част връща максималната стойност от масива от числа. В този случай тя ще върне 2 (което е номерът на реда, където има точно съвпадение).
- Сега просто използваме този номер като стойност за търсене и използваме масива за търсене като B2: C9
Забележка: Тъй като това е формула за масив, използвайте Control + Shift + Enter вместо просто enter.
Пример 9 - Използване на VLOOKUP с множество критерии
Функцията Excel VLOOKUP в основната си форма може да търси една стойност за търсене и да връща съответната стойност от посочения ред.
Но често има нужда да се използва VLOOKUP в Excel с множество критерии.
Да предположим, че имате данни с име на ученици, вид на изпита и оценка по математика (както е показано по -долу):
Използването на функцията VLOOKUP за получаване на оценка по математика за всеки ученик за съответните нива на изпит може да бъде предизвикателство.
Например, ако опитате да използвате VLOOKUP с Matt като стойност за търсене, той винаги ще върне 91, което е резултатът за първото появяване на Matt в списъка. За да получите резултата за Matt за всеки тип изпит (Unit Test, Mid Term и Final), трябва да създадете уникална стойност за търсене.
Това може да стане с помощта на помощната колона. Първата стъпка е да вмъкнете помощна колона вляво от резултатите.
Сега, за да създадете уникален квалификатор за всеки екземпляр от името, използвайте следната формула в C2: = A2 & ”|” & B2
Копирайте тази формула във всички клетки в помощната колона. Това ще създаде уникални стойности за търсене за всеки екземпляр от име (както е показано по -долу):
Сега, докато имаше повторения на имената, няма повторение, когато името се комбинира с нивото на проверка.
Това го прави лесен, тъй като сега можете да използвате стойностите на помощната колона като стойности за търсене.
Ето формулата, която ще ви даде резултата в G3: I8.
= VLOOKUP ($ F3 & "|" & G $ 2, $ C $ 2: $ D $ 19,2,0)
Тук сме комбинирали името на ученика и нивото на изпита, за да получим стойността за търсене, и използваме тази стойност за търсене и я проверяваме в помощната колона, за да получим съответстващия запис.
Забележка: В горния пример използвахме | като разделител при присъединяване на текст в помощната колона. При някои изключително редки (но възможни) условия може да имате два критерия, които са различни, но в крайна сметка дават един и същ резултат, когато се комбинират. Ето един пример:
Обърнете внимание, че докато A2 и A3 са различни и B2 и B3 са различни, комбинациите в крайна сметка са еднакви. Но ако използвате разделител, дори комбинацията ще бъде различна (D2 и D3).
Ето урок за това как да използвате VLOOKUP с множество критерии, без да използвате помощни колони. Можете също да гледате моя видеоурок тук.
Пример 10 - Обработка на грешки при използване на функцията VLOOKUP
Функцията Excel VLOOKUP връща грешка, когато не може да намери посочената стойност за търсене. Може да не искате грозната стойност на грешката да наруши естетиката на вашите данни, в случай че 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 в Excel.
Това е всичко в този урок за VLOOKUP.
Опитах се да покрия основните примери за използване на функцията Vlookup в Excel. Ако искате да видите още примери, добавени към този списък, уведомете ме в секцията за коментари.
Забележка: Опитах всичко възможно да проверя този урок, но в случай, че откриете грешки или правописни грешки, моля, уведомете ме 🙂
Използвайки Функция VLOOKUP в Excel - Видео
- Функция Excel HLOOKUP.
- Функция Excel XLOOKUP
- Функция Excel INDEX.
- Функция Excel INDIRECT.
- Функция Excel MATCH.
- Функция Excel OFFSET.
