Изтеглете примерната работна книга
Този урок демонстрира как да използвате Функция Excel VLOOKUP в Excel, за да търсите стойност.
Преглед на функцията VLOOKUP
Функцията VLOOKUP Vlookup означава вертикално търсене. Той търси стойност в най -лявата колона на таблица. След това връща стойност определен брой колони вдясно от намерената стойност. Той е същият като hlookup, освен че търси стойности вертикално, а не хоризонтално.
(Забележете как се появяват входовете на формулата)
Синтаксис и аргументи на функцията VLOOKUP:
| 1 | = VLOOKUP (lookup_value, table_array, col_index_num, range_lookup) |
lookup_value - Стойността, която искате да търсите.
таблица_масив - Диапазонът от данни, който съдържа както стойността, която искате да търсите, така и стойността, която искате vlookup да върне. Колоната, съдържаща стойностите за търсене, трябва да е най-лявата колона.
col_index_num - Номерът на колоната на диапазона от данни, от който искате да върнете стойност.
range_lookup - Истина или лъжа. FALSE търси точно съвпадение. TRUE търси най -близкото съвпадение, което е по -малко или равно на lookup_value. Ако е избрано TRUE, най-лявата колона (колоната за търсене) трябва да бъде сортирана възходящо (от най-ниското до най-високото).
Какво представлява функцията VLOOKUP?
Като една от по -старите функции в света на електронните таблици, функцията VLOOKUP се използва за извършване Vертичен Търсения. Той има няколко ограничения, които често се преодоляват с други функции, като INDEX/MATCH. Въпреки това, той има предимството да бъде единична функция, която може да направи търсене на точно съвпадение сама. Той също така е една от първите функции, за които хората научават.
Основен пример
Нека да разгледаме извадка от данни от класната книга. Ще разгледаме няколко примера за извличане на информация за конкретни студенти.
Ако искаме да намерим в кой клас е Боб, бихме написали формулата:
| 1 | = VLOOKUP ("Боб", A2: C5, 2, FALSE) |
Важни неща, които трябва да запомните, са, че елементът, който търсим (Боб), трябва да бъде в първата колона на нашия диапазон за търсене (A2: C5). Казахме на функцията, че искаме да върнем стойност от 2nd колона, която в този случай е колона B. Накрая посочихме, че искаме да направим an точно съвпадение като поставите False като последен аргумент. Тук отговорът ще бъде „Четене“.
Страничен връх: Можете също да използвате числото 0 вместо False като краен аргумент, тъй като те имат същата стойност. Някои хора предпочитат това, тъй като е по -бързо да се пише. Просто знайте, че и двете са приемливи.
Преместени данни
За да добавите някои пояснения към първия ни пример, елементът за търсене не трябва да е в колона А на електронната ви таблица, а само в първата колона от диапазона за търсене. Нека използваме същия набор от данни:
Сега нека намерим оценката за класа на науката. Нашата формула би била
| 1 | = VLOOKUP ("Наука", B2: C5, 2, FALSE) |
Това все още е валидна формула, тъй като първата колона от нашия диапазон за търсене е колона B, където ще бъде намерен нашият термин за търсене на „Наука“. Връщаме стойност от 2nd колона от диапазона за търсене, която в този случай е колона C. Отговорът тогава е „A-“.
Използване на заместващи символи
Функцията VLOOKUP поддържа използването на заместващи знаци „*“ и „?“ когато правите търсения. Да приемем например, че сме забравили как се изписва името на Франк и просто искахме да търсим име, което започва с „F“. Можем да напишем формулата
| 1 | = VLOOKUP ("F*", A2: C5, 2, FALSE) |
Това би могло да намери името Франк в ред 5 и след това да върне стойността от 2nd относителна колона. В този случай отговорът ще бъде „Наука“.
Неточно съвпадение
През повечето време ще искате да се уверите, че последният аргумент във VLOOKUP е False (или 0), така че да получите точно съвпадение. Има обаче няколко случая, когато може да търсите неточно съвпадение. Ако имате списък с сортирани данни, можете също да използвате VLOOKUP, за да върнете резултата за елемента, който е или същият, или следващият най -малък. Това често се използва при работа с увеличаващи се диапазони от числа, като например в данъчна таблица или комисионни.
Да приемем, че искате да намерите данъчната ставка за доход, въведен в клетка D2. Формулата в D4 може да бъде:
| 1 | = VLOOKUP (D2, A2: B6, 2, TRUE) |
Разликата в тази формула е, че последният ни аргумент е „Вярно“. В нашия конкретен пример можем да видим, че когато нашите индивиди въведат доход от 45 000 долара, те ще имат данъчна ставка от 15%.
Забележка: Въпреки че обикновено искаме точно съвпадение с False като аргумент, забравяте да посочите 4th аргумент във VLOOKUP, по подразбиране е True. Това може да доведе до неочаквани резултати, особено когато се занимавате с текстови стойности.
Динамична колона
VLOOKUP изисква да зададете аргумент, от който колона искате да върнете стойност, но може да възникне повод, когато не знаете къде ще бъде колоната или искате да позволите на потребителя да промени коя колона да се върне. В тези случаи може да бъде полезно да използвате функцията MATCH, за да определите номера на колоната.
Нека да разгледаме отново нашия пример с книгата с някои входни данни в E2 и E4. За да получим номера на колоната, можем да напишем формула на
| 1 | = MATCH (E2, A1: C1, 0) |
Това ще се опита да намери точната позиция на „Степен“ в диапазона A1: C1. Отговорът ще бъде 3. Знаейки това, можем да го включим във функция VLOOKUP и да напишем формула в E6 така:
| 1 | = VLOOKUP (E4, A2: C5, MATCH (E2, A1: C1, 0), 0) |
Така че функцията MATCH ще оцени на 3 и това казва на VLOOKUP да върне резултат от 3rd колона в диапазона A2: C5. Като цяло получаваме желания резултат от „С“. Нашата формула сега е динамична, тъй като можем да променим или колоната, която да гледаме, или името, което да търсим.
Ограничения на VLOOKUP
Както бе споменато в началото на статията, най -големият спад на VLOOKUP е, че изисква терминът за търсене да бъде намерен в най -лявата колона на диапазона за търсене. Въпреки че има някои фантастични трикове, които можете да направите, за да преодолеете това, общата алтернатива е да използвате INDEX и MATCH. Тази комбинация ви дава повече гъвкавост и понякога дори може да бъде по -бързо изчисление.
VLOOKUP в Google Таблици
Функцията VLOOKUP работи абсолютно същото в Google Sheets като в Excel:
допълнителни бележки
Използвайте функцията VLOOKUP, за да извършите VERTICAL търсене. VLOOKUP търси точно съвпадение (range_lookup = FALSE) или най -близкото съвпадение, което е равно или по -малко от lookup_value (range_lookup = TRUE, само числови стойности) в първия ред на масива table_array. След това връща съответна стойност, n брой редове под съвпадението.
Когато използвате VLOOKUP за намиране на точно съвпадение, първо определяте идентифицираща стойност, която искате да търсите като lookup_value. Тази идентификационна стойност може да бъде SSN, идентификационен номер на служител, име или друг уникален идентификатор.
След това определяте диапазона (наречен таблица_масив), който съдържа идентификаторите в горния ред и всички стойности, които в крайна сметка искате да търсите в редовете под него. ВАЖНО: Уникалните идентификатори трябва да са в горния ред. Ако не са, трябва или да преместите реда отгоре, или да използвате MATCH / INDEX вместо VLOOKUP.
Трето, дефинирайте номера на ред (row_index) от таблица_масив които искате да се върнете. Имайте предвид, че първият ред, съдържащ уникалните идентификатори, е ред 1. Вторият ред е ред 2 и т.н.
И накрая, трябва да посочите дали да търсите точно съвпадение (FALSE) или най -близко съвпадение (TRUE) в range_lookup. Ако е избрана опцията за точно съвпадение и точното съвпадение не е намерено, се връща грешка (#N/A). За да се върне формулата празна или „не е намерена“, или друга стойност вместо стойността на грешката (#N/A), използвайте функцията IFERROR с VLOOKUP.
За да използвате функцията VLOOKUP, за да върнете приблизителен набор от съвпадения: range_lookup = ИСТИНСКО. Тази опция е налична само за числови стойности. Стойностите трябва да бъдат сортирани във възходящ ред.
За да извършите хоризонтално търсене, използвайте вместо това функцията HLOOKUP.
VLOOKUP Примери във VBA
Можете също да използвате функцията VLOOKUP във VBA. Тип:application.worksheetfunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)
За аргументите на функцията (lookup_value и т.н.) можете или да ги въведете директно във функцията, или да определите променливи, които да използвате вместо това.
Връщане към списъка с всички функции в Excel
