Здрасти! Представете си, че имате хиляди редове с данни за служители в Excel и вашият шеф на случаен принцип ви пита за заплатата и обозначението на Рамеш. Или по -лошо от това, той/тя ви дава списък със сто служители и ви моли да изпратите подробности за тези служители. Какво би направил? Не можете да го намерите ръчно. Просто не е осъществимо и изобщо не е умно. Но ти си умен, затова си тук. Нека ви запозная с функцията VLOOKUP на Excel.
Защо се нуждаем от VLOOKUP?
Какво е VLOOKUP? VLOOKUP означава вертикално търсене.
VLOOKUP просто търси реда с дадена стойност в първата колона на таблица и връща зададената стойност на колоната в този ред.
Синтаксисът на VLOOKUP:
= VLOOKUP (lookup_value, table_array, col_index_number, [range_lookup])
Lookup_value: Стойността, по която искате да търсите в първата колона на масива от таблици.
Таблица_масив: Таблицата, в която искате да търсите/търсите
col_index_number: Номерът на колоната в масива от таблици, от който искате да извлечете резултати.
[range_lookup]: FALSE, ако искате да търсите точна стойност, TRUE, ако искате приблизително съвпадение.
Добре, стига с теорията. Нека да преминем към пример за Excel VLOOKUP.
VLOOKUP Пример 1.
В лист на Excel имате тези данни от служители. В първата колона (колона А) имате името на служителите. След това имате тяхното обозначение и т.н., както е показано на изображението по -долу.
За да стане по -лесно, взех малка маса.
Сега шефът ви е изпратил този списък и поиска подробности в празни колони.
Трябва да кажете на заплатите и обозначенията на Рамеш и Дивия. Как бихте направили това?
За да направите това, можете да търсите ръчно Или … Или можете да използвате функцията Excel VLOOKUP.
Просто напишете тази формула VLOOKUP в клетка H2 и я копирайте в клетката под:
= VLOOKUP (G3, $ A $ 3: $ D $ 11,4, FALSE)
Ще получите резултатите, както е показано по -долу.
Обяснение:
Тук казахме на Excel да търси Рамеш (G3) в първата колона (А) на маса $ A $ 3: $ D $ 11.
Ако дадената стойност е намерена, върнете стойността в четвъртия (4) в тази таблица.
Като му дадете FALSE в range_lookup, вие казвате на VLOOKUP да намери точното съвпадение (No ifs n buts).
Тук Рамеш се намира в първа колона А и заплатата му се връща като 105347.
По подобен начин VLOOKUP търси Divya в колона и се връща #Н/Д грешка, тъй като записът на Divya не е в таблицата ви.
Виждате ли другата употреба на VLOOKUP тук? Можете да използвате функцията VLOOKUP, за да разберете дали дадена стойност съществува в списък или не. Можете също така да съпоставите два списъка и да знаете кои стойности са общи в тях.
Професионален съвет: винаги заключвайте препратката към Table_array, когато използвате VLOOKUP в Excel. В противен случай вашият table_array ще се промени, когато копирате формулата си в други клетки.
По подразбиране е, когато VLOOKUP от друг лист или работна книга, но в същия лист, трябва да го направите ръчно.
Можете ли да напишете формулата VLOOKUP, за да получите обозначението на Рамеш? Разделът за коментари е изцяло ваш.
В горния пример видяхме пример за VLOOKUP, където искахме точно съвпадение. Но какво, ако искаме да знаем приблизителното съвпадение?
Кога използвате приблизителното съвпадение във VLOOKUP като Range_lookup?
Нека го разберем с друг пример за VLOOKUP.
Пример 2: Използване на приблизително съвпадение във VLOOKUP
Правилно сте отговорили на запитването на шефа си с помощта на VLOOKUP по -рано. Сега вашият „скъп“ шеф иска да знае най -близката заплата до 150000 (само по -малко от).
За да отговорим на това, отново ще използваме VLOOKUP, но този път с приблизително съвпадение.
Важна забележка: За да намерите приблизително съвпадение, трябва вашите данни да бъдат сортирани. Тук съм сортирал данните във възходящ ред по заплата.
В клетка Н2 напишете тази формула и я плъзнете до клетката по -долу:
= VLOOKUP (G9, $ D $ 3: $ D $ 11,1,1)
Забележка: 1 се тълкува като ВЯРНО и 0 като НЕВЯРНО в Excel.
В крайна сметка ще получите резултат, който изглежда така:
Обяснение.
Нашата стойност за търсене е 150000 (G9) и искаме най -близката заплата до това.
Нашата гама е $ D $ 3: $ D $ 11 тъй като искаме да върнем стойността от същата колона. Следователно col_index_num също е 1. И тъй като искаме приблизително съвпадение, ние предоставихме range_lookup като 1 (ИСТИНА).
За приблизително съвпадение вашите данни трябва да бъдат сортирани. В противен случай Excel VLOOKUP ще служи като неправилен резултат.
Важни бележки:
-
- VLOOKUP винаги работи отляво надясно. Не можете да извлечете стойност отдясно на първата колона.
- Номерация на колони Започва от избрания диапазон. Например, ако предоставите табличен масив D4: F10. Тогава броят на колоните ще започне с D, а не от действителното начало на таблицата.
- Винаги заключвайте препратката към Таблица, като използвате знак $, за да избегнете грешка, докато копирате формулата за търсене V в Microsoft Excel 2016.
- Можете да използвате заместващ знак (*) в стойността за търсене, когато помните само част от стойността за търсене.
- Задайте Range_Lookup на FALSE (0), за да получите точно съвпадение с вашата стойност.
- Задайте Range_Lookup на TRUE (1), за да получите най -близкото до по -малко от дадената стойност.
- Винаги сортирайте данните във възходящ ред за приблизително съвпадение.
Сега можете да отговорите на запитването на шефа си за секунди. Можете лесно да сравните два списъка. Можете да направите групово търсене с помощта на VLOOKUP в Excel 2016, 2013, 2010 и в по -старите версии на Excel за секунди.
Значи беше полезно? Ако не, кажете ми точната ви заявка в секцията за коментари.
17 неща за Excel VLOOKUP
Vlookup Топ 5 стойности с дублирани стойности, използващи INDEX-MATCH в Excel
VLOOKUP Множество стойности
VLOOKUP с Dynamic Col Index
Частично съвпадение с функцията VLOOKUP
Използвайте VLOOKUP от две или повече таблици за търсене
Преглед по дата в Excel
Използване на формула VLOOKUP за проверка дали има стойност
Популярни статии
Как да VLOOKUP от различни Excel лист
VLOOKUP с цифри и текст
IF, ISNA и VLOOKUP функция
Функции ISNA и VLOOKUP
IFERROR и VLOOKUP функция