Въведение във функцията VLOOKUP
Функцията VLOOKUP в Excel без съмнение е най -използваната и обсъждана функция. При всяко интервю за работа, което изисква умения в Excel, това е най -често задаваният въпрос, който, ако знаете как да използвате функцията VLOOKUP.
Функцията VLOOKUP попада в категорията за търсене на Excel функции. V във VLOOKUP означава вертикално. Функцията се използва за търсене на данни, които са структурирани вертикално. За хоризонтално търсене има функция, наречена HLOOKUP.
Функцията Excel VLOOKUP търси първото съвпадение на дадена стойност, наречена справочна стойност, в таблица с данни и връща стойността от дадения индекс на колоната. Съответствието може да бъде приблизително или точно.
Въпреки че VLOOKUP е лесен за използване, той има свои собствени ограничения и фона. Ще обсъдим всичко, включително силните и слабите страни на функцията.
Синтаксис:
= VLOOKUP (lookup_value, table_array, col_index_number, [range_lookup])
Какво представлява търсената стойност
Lookup_value: Неговата стойност, която искате да търсите в табличен масив.
Например, ако търсите ролка не. 110 в таблицата на ученика, тогава неговата 110 е справочна стойност.
Таблица_масив: Таблицата, от която искате да търсите стойност за търсене.
Например, ако търсите 110 в масива на таблицата B5: E17. Тогава B5: E17 е вашият табличен масив.
col_index_number: Номерът на колоната в масива от таблици, от който искате да извлечете резултати.
Например, ако в табличен масив B5: E17 искате да получите стойност от колона D тогава индексът на колоната ви ще бъде 3 (броене от B до D (B, C, D)).
[range_lookup]: FALSE, ако искате да търсите точна стойност, TRUE, ако искате приблизително съвпадение.
Как да използвате функцията VLOOKUP
Когато имате структурирани данни и искате да потърсите данни в тези данни, функцията VLOOKUP е решението.
Режими на съвпадение на VLOOKUP
VLOOKUP има два режима на съвпадение, приблизително съвпадение и точно съвпадение. По подразбиране VLOOKUP изпълнява приблизително съвпадение. Но ако искате да принудите функцията VLOOKUP да направи точно съвпадение, можете да направите и това. В повечето случаи аз и аз силно вярваме, че и другите се опитват да направят точно съвпадение с помощта на функцията VLOOKUP. Не разбирам защо разработчиците на Excel смятат, че потребителите биха искали да използват най -вече приблизителното съвпадение.
1. Приблизително съвпадение на VLOOKUP
= VLOOKUP (lookup_value, table_array, col_index_number, 1)
Във VLOOKUP приблизително съвпадение, ако не се намери стойност, тогава последната стойност, която е по -малка от стойността за търсене, се съпоставя и се връща стойността от индекса на дадената колона.
VLOOKUP по подразбиране приблизително съвпада, ако пропуснем променливата за търсене на диапазон. Приблизителното съвпадение е полезно, когато искате да направите приблизително съвпадение и когато масивът на таблицата ви е сортиран във възходящ ред.
Vlookup търси стойността и ако не намери стойността в масива на таблицата, тя съответства на стойността, която е по -малка от тази стойност в масива на таблицата. Нека го разберем чрез пример.
Пример 1
Тук имаме списък с ученици. Всеки ученик е вкарал някои оценки в тест. Сега искам да дам оценка според техните оценки.
Всеки ученик, който е вкарал по -малко от 40, трябва да бъде маркиран с F, ученикът, който е отбелязал между 40 и 60, трябва да бъде маркиран с C и така нататък, както е показано на изображението по -долу.
Бихме написали тази формула VLOOKUP в E2 и я плъзнем надолу.
= VLOOKUP (D2, $ H $ 2: $ I $ 6,2, ИСТИНА)
Как работи?
В горния пример нашите стойности за търсене са в колона D, започвайки от D2. Масивът на таблицата е H2: I6 (имайте предвид, че е сортиран във възходящ ред и абсолютен). Колоната, от която получаваме данни, е 2. И типът на съвпадение сме дефинирали приблизително, като предаваме TRUE (нищо и 1 ще означават същото).
Така че нека разгледаме стойността за търсене D2, която съдържа 40.
- VLOOKUP търси 40 в първата колона (H) на табличен масив H2: I6.
- Намира 40 на втора позиция в клетка Н3.
- Сега се премества във втора колона от H3 до I3 и връща D.
В този случай vlookup намери точното съвпадение. Да видим следващия случай.
Втората стойност на търсене D3, която съдържа 36.
- VLOOKUP търси 36 в първата колона на табличен масив H2: I6.
- VLOOKUP не можа да намери 36 никъде в първата колона.
- Тъй като VLOOKUP не можа да намери 36, той го съпоставя с последната намерена стойност, която е по -малка от стойността за търсене.
- Първата стойност, която е по -малка от 36, е 0, следователно връща F, свързана с 0.
Това се случва за всеки случай тук. За 92 последната стойност, която е по -малка от 92, е 90. Следователно A се връща за приблизително съвпадение.
2. VLOOKUP Точно съвпадение
Тази най -използвана форма на VLOOKUP и може да бъде и най -полезна. Когато искате да търсите точна стойност в първата колона на масива от таблици, използвате точно съвпадение. Например, ако търсите формуляр за идентификация, най -много бихте искали да искате точно съвпадение вместо всяка стойност, която е по -малка от тази стойност.
За да принудим VLOOKUP към точно съвпадение в excel, предаваме 0 или FALSE като параметър range_lookup.
= VLOOKUP (lookup_value, table_array, col_index_number, 0)
Ако стойността не е намерена в първата колона на масива от таблици, формулата ще върне грешка #N/A.
Нека видим пример.
Пример 2
В този пример искам да напиша само ролка не на ученик в A2, а в B2 искам да извлека име на ученика и в C2 неговия Оценка. Прост. Масивът на таблицата е B5: D17. Защо?
За да направите това, напишете тези две формули в клетка съответно в клетка В2 и С2.
= VLOOKUP (A2, $ B $ 5: $ E $ 17,2,0)
= VLOOKUP (A2, $ B $ 5: $ E $ 17,3,0)
Сега който и идентификатор да напишете в клетка A2, функцията VLOOKUP ще търси точно съвпадение в колона B и ще върне стойност от дадения col_index.
Как работи VLOOKUP с точно съвпадение?
Точното съвпадение VLOOKUP в Excel е просто. Той търси стойността за търсене в първата колона на дадения табличен_масив и връща стойност от стойността от дадения номер на колона_индекс. Ако стойността не е намерена, VLOOKUP връща грешка #N/A.
Най -доброто използване на функцията VLOOKUP
Vlookup има много приложения. Ще обсъдя някои най -полезни случаи на употреба.
3. Използвайте Excel VLOOKUP за класиране вместо сложна вложена IF функция
Използвайте VLOOKUP приблизително съвпадение, за да замените вложената функция IF. В пример 1 видяхме как можем да използваме vlookup за оценяване на учениците според техните оценки.
Приблизителното съвпадение на VLOOKUP е по -бързо, отколкото вложено, ако, тъй като използва вътрешно двоично търсене.
4. Използвайте VLOOKUP за извличане на данни
Тази най -често срещана задача, която може да бъде изпълнена лесно с помощта на функцията VLOOKUP в Excel. В пример 2 направихме това, за да извлечем данни за учениците, използвайки техния номер на ролка.
5. Проверете дали дадена стойност е в запис с помощта на Vlookup.
VLOOKUP може да се използва за проверка дали дадена стойност е в някакъв списък или не.
В пример 2, когато пишем 152, той връща грешка #N/A. Ако VLOOKUP връща #N/A, това означава, че не е намерил дадената стойност никъде в дадените данни.
Подготвил съм кратка статия за това. Можете да я прочетете тук.
Използване на формула VLOOKUP за проверка дали има стойност
Автоматично получаване на индекс на колони
Аз принадлежа към тази група хора, които не искат да повтарят същата задача отново и отново. Така че промяната на номер на col_index отново и отново ме дразни и винаги го избягвам. Всъщност избягвам всякакъв вид редактиране във формулите си, след като съм го написал, освен ако и докато няма друга възможност.
Индексът на колоната може да се изчисли автоматично в Excel. Има няколко начина да го направите. Нека да видим някои от тях тук.
6. Използвайте функцията VLOOKUP с функцията MATCH
Знаем, че функцията MATCH връща намерения индекс на съответстваща стойност.
Така че, ако имаме точно същите заглавия на място за търсене като изходните данни, тогава можем да използваме, за да получим col_index. Как? Нека да видим…
В данните по -долу искаме просто да напишем номера на ролката в G2 и искаме да извлечем името на ученика, оценките и оценката в H2, което заглавие пишем там.
Напишете тази формула в клетка Н2
= VLOOKUP (G2, B2: E14, MATCH (H1, B1: E1,0), 0)
Сега, когато променяте заглавието в H1, тази стойност в H2 ще се показва от тази колона.
Можете да използвате падащо меню, като използвате валидиране на данни, което съдържа всички заглавия от таблицата, за да го направите по -лесен за употреба.
Тук VLOOKUP прави точно съвпадение. Сега VLOOKUP прави обичайните си неща за извличане на данни. Магията се извършва от функцията MATCH на позиция col_index.
Тук VLOOKUP вижда за всяка стойност в G2 в първата колона на табличен масив B2: E14. Сега на мястото на col_index имаме МАТЧ (H1, B1: E1,0).
Функцията MATCH търси всяка стойност в H1 в диапазона B1: E1 и връща индекс на стойността на съвпадението.
Например, когато пишем студент в Н1 го търси в обхвата B1: E1. Намира се в С2. следователно връща 2. Ако въведем Оценка ще върне оценка на ученик.
Имайте предвид, че когато въвеждаме Region, той връща #N/A. Тъй като това е извън обхвата на таблицата. Ще говорим за това по -късно в статията.
Това е най -добрият начин да направите VLOOKUP автоматичен. Има и други методи, но те не са толкова гъвкави от този.
7. Използвайте функцията VLOOKUP с функция COLUMN
Функцията колона връща номера на колоната на предоставената справка. И какво ни е необходимо за извличане на данни с помощта на VLOOKUP? Col_index. Така че, използвайки функцията COLUMN, разбира се, можем да получим данни от всеки набор от данни.
Да вземем горния пример. Но сега трябва да извлечем цели данни. Не само една колона.
Напишете тази формула в H2 и копирайте в I2 и J2.
= VLOOKUP ($ G2, $ B $ 2: $ E $ 14, КОЛОНА (B1), 0)
Това ще ви даде динамичен резултат. Как? Да видим.
VLOOKUP работи както обикновено. В column_index сме написали КОЛОНА (B1), което ще се преведе на 2, тъй като имаме име на студент в колона 2 от колона B, връща името на ученика.
Важно: Това работи, защото нашата таблица започва от колона B. Ако таблицата ви е в средата на листа, тогава ще трябва да извадите или добавите някои числа, така че да отговаря на вашите изисквания.
Например, ако горната таблица започва от C1 в същата структура, тогава всички формули по -долу работят добре.
= VLOOKUP ($ G2, $ C $ 2: $ F $ 14, КОЛОНА (B1), 0)
= VLOOKUP ($ G2, $ C $ 2: $ F $ 14, КОЛОНА (C1) -1,0)
= VLOOKUP ($ G2, $ C $ 2: $ F $ 14, КОЛОНА (A1) +1,0)
8. VLOOKUP за обединяване на данни
В повечето бази данни има една заявка, която се слива с таблици, наричана най -вече заявка за присъединяване, но MS Excel няма база данни, следователно няма заявка за присъединяване. Но това не означава, че не можем да се присъединим към две таблици, ако имаме някои общи колони в две таблици.
Така че, когато получите някои данни от два отдела на една и съща компания, бихте искали да ги обедините, за да анализирате тези данни.
Например, когато получавате оценки на ученици от учители и след това запис на посещаемост от административен отдел, бихте искали да видите кой ученик каква оценка е получил и какъв е процентът му на посещение на едно място.
Трябва да ги обединим в таблицата по -долу.
Оценки, трябва да вземем от таблицата с маркировки и посещаемостта от данните за посещаемостта.
Запишете по -долу формули в клетка D19, за да получите маркировки и плъзнете надолу.
= VLOOKUP (B19, $ B $ 2: $ D $ 15,3,0)
Запишете по -долу формулата в клетка E19
= VLOOKUP (B19, $ G $ 3: $ I $ 15,3,0)
И сме обединили две маси в една таблица. Разбира се, можете да добавите повече от две таблици. Просто трябва да получите данни от всички таблици, които искате, на едно място с помощта на VLOOKUP.
Най -добри практики на VLOOKUP
Така че всички горепосочени примери просто използвахме vlookup със необработени данни. Във всички горепосочени примери бяхме внимателни относно данните, които използвахме. Трябваше да бъдем внимателни, като същевременно даваме препратки към масиви от таблици. Но има някои начини, които могат значително да намалят това усилие.
9. Използвайте абсолютни справки с VLOOKUP
Може би сте забелязали, че в някои статии съм използвал абсолютни диапазони (диапазон $ знак). Е, абсолютните ярости се използват, когато не искаме диапазонът да се променя, докато копираме клетка с формула в други клетки.
Например, ако имам = VLOOKUP (G2,В2: Е14, 2,0), 0) в клетка Н2 и ако я копирам или плъзна надолу в клетка Н3, формулата ще се промени на = VLOOKUP (G3,B3: E15, 2,0). Всички препратки са променени относително. Тук може да искаме да променим препратката на стойността за търсене, но не и масива на таблицата. Тъй като B2 излиза или обхваща и може да искаме винаги да го имаме в нашите масиви на таблици.
Така че, за да предотвратим това, използваме абсолютни препратки. Ние правим тези диапазони абсолютни, които не искаме да променяме, като table_array в H2, = VLOOKUP (G2,$ B $ 2: $ E $ 14, 2,0), 0). Когато копирате H2 в H3 формулата ще бъде = VLOOKUP (G3,$ B $ 2: $ E $ 14, 2,0), 0). Обърнете внимание, че стойността за търсене се променя, но не и табличен_масив.
10. Използвайте именовани диапазони с VLOOKUP
В горния пример използвахме абсолютни препратки за фиксиране на таблица_масив. Изглежда доста размито и трудно се чете. Какво ще кажете, ако можете просто да пишете 'данни' на мястото на $ B $ 2: $ E $ 14 и ще се позовава на $ B $ 2: $ E $ 14 винаги. Това ще направи още по -лесно четенето и записването на формулата.
Така че просто изберете диапазона и го наречете „данни“.
За да дадете име на диапазон, изберете диапазона. Щракнете с десния бутон, щракнете върху Дефиниране на име и дайте име на диапазона, който искате. Сега просто = VLOOKUP (G3,данни,2,0), 0) формулата ще работи точно както преди.
Можете да прочетете повече за именуваните диапазони в Excel. Обясних тук всеки аспект на именования диапазон.
11. Заместващи символи за частично съвпадение с помощта на VLOOKUP
VLOOKUP позволява частично съвпадение. Да, докато правите точно съвпадение на VLOOKUP, можете да използвате заместващи оператори, за да направите частично съвпадение. Например, ако искате да потърсите стойност, която започва с „Gil“, можете да използвате заместващ оператор * с „Gil“ като „Gil *“. Нека видим пример.
Тук искам да получа оценки на студент, чието име започва с Гил. За да стане така, ще напиша формулата по -долу.
= VLOOKUP ("*хриле", C2: D14,2,0)
Налични са два заместващи символа в excel за използване с функцията VLOOKUP.
Звездичка (*) заместващ знак. Това се използва, когато потребителят не знае броя на знаците и знае само някои знаци на съвпадение. Например, ако потребителят знае име, което започва с „Sm“, той ще напише „Sm*“. Ако потребителят знае, че стойност за търсене, която завършва с „123“, той ще напише „*123“. (заместващата карта работи само с текстове). И от това, че знае, че „se“ влиза в текст, тогава той ще напише „*se*“.
Въпросителен знак ("?"). Това се използва, когато потребителят знае броя на липсващите знаци. Например, ако искам да потърся стойност, която има 4 знака, но не знам какви са, просто ще напиша „????“ на мястото на стойността за търсене.
Vlookup ще върне съответната стойност на първата намерена стойност, която съдържа точно четири знака.
Важно: Уайлд кардът работи само с текстови стойности. Преобразувайте числата в текст, ако искате да използвате заместващи символи с тях.
Слабостите на функцията VLOOKUP
VLOOKUP наистина е мощна и лесна функция за извличане на стойности, но има много области, в които VLOOKUP просто не е толкова полезен. Ако работите върху Excel, трябва също да ги познавате и как да направите задача, която VLOOKUP не може да изпълни.
12. Не може да се извлече стойност отляво на Table_Array
Най -големият недостатък на функцията VLOOKUP е, че тя не може да извлече стойност отляво на масива от таблици. VLOOKUP търси само вдясно от стойността за търсене.
Това е така, защото VLOOKUP търси дадено справочна стойност в първата колона на даденото табличен масив. Той никога няма да върне стойност извън таблицата. И ако се опитате да запазите лявата колона в масива на таблицата, тя става първата колона от масива на таблицата, следователно стойността за търсене ще бъде търсена в този диапазон. Което вероятно ще генерира грешка.
За да търсим стойности отляво на стойността за търсене, вместо това използваме INDEX-MATCH. Функцията INDEX-MATCH може да търси стойности от всяка колона на листа. Всъщност диапазонът за търсене и диапазонът, от който искате да търсите стойности, са напълно независими, което прави съвпадението на индекса силно персонализирано.
VLOOKUP връща #N/A дори когато съществува стойност за търсене.
#N/A Грешка се връща от функцията VLOOKUP, когато стойността за търсене не е намерена. Може да ви се стори досадно, когато стойност съществува в диапазон, но VLOOKUP връща грешка #N/A.
14. Когато числата са форматирани като текст
Това се случва най -вече, когато числата се оформят като текст. Когато откриете, че използвате CTRL+F, Excel ще го намери, но когато се опитате да използвате VLOOKUP, той връща #N/A. Тази задача се дава в повечето интервюта като трик въпроси.
В горното изображение можете да видите, че 101 е точно там, но формулата връща грешка. Как, справяте ли се? Е, има два метода.
1. Преобразувайте текст в номер в данни
Можете да видите този зелен етикет в горния ляв ъгъл в колона rollno. Те показват, че нещо е необичайно в стойността на клетката. Когато изберете клетката, тя ще покаже, че номерът е форматиран като текст.
Когато кликнете върху удивителен знак (!), Той ще ви покаже опция за Преобразуване в число. Изберете всички клетки и щракнете върху тази опция. Всички стойности ще бъдат преобразувани в текст.
2. Преобразувайте номера за търсене в текст във формула
В горния пример мутирахме оригинални данни. Може да не искате да променяте нищо в оригиналните данни. Така че вместо това бихте искали да направите това във формула. За да промените номера в текст във формулата VLOOKUP, напишете това.
= VLOOKUP (ТЕКСТ (G2, "0"), B2: D14,2,0)
Тук формулата динамично променя числото в текст и след това го съпоставя с зададения диапазон.
15. Текст с водещи и задни пространства
Някои данни, предоставени от операторите за въвеждане на данни, и данните от интернет не са чисти. Те могат да имат заключващи интервали или някои непечатани знаци. Докато търсите с помощта на тези стойности, може да видите грешка #N/A. За да избегнете това, първо почистете данните си. За да премахнете водещите интервали, използвайте функцията TRIM. TRIM премахва произволен брой предни или последни интервали от текста.
Така че, предлагам ви да добавите нова колона и да вземете изчистените си данни там. След това го поставете стойност. Сега, ако искате, можете да се отървете от оригиналната колона.
= TRIM (CLEAN (текст))
Обработка на грешки на VLOOKUP
Както знаете, VLOOKUP не успява да намери стойност, връща грешка #N/A. Добре е да получите грешка #N/A, може би понякога възнамерявате да получите такава, например когато искате да проверите дали стойността вече съществува в списъка или не. Там #N/A означава, че стойността не е там.
Но #N/A изглежда грозно. Какво ще кажете за получаване на удобен за потребителя изход, като „Id not found“ или „Customer not Found“. Можем да използваме IFERROR с функция VLOOKUP, за да избегнем #N/A.
Използвайте формулата по -долу, за да уловите грешка във VLOOKUP.
= IFERROR (VLOOKUP (150, B2: E14,2,0), "Roll no not found")
16. VLOOKUP с МНОГО критерии
VLOOKUP не може да работи с множество критерии.Да, можете да имате само един критерий за търсене на стойности, използвайки формулата VLOOKUP.
Например, ако имате име и фамилия в две отделни колони.
И сега, ако искате да потърсите стойност, чието име е Джон и Фамилията Дейв, тогава не можете да го направите нормално.
Но има решение за това. Можете да създадете отделна колона, като свържете едно име и фамилия и след това потърсете тази колона с име и фамилия.
Има INDEX-MATCH алтернатива, която може да търси множество критерии без помощна колона.
17. VLOOKUP Извлича само първата намерена стойност
Представете си, че имате някои данни в централния регион. Сега искате да получите първите 5 записа на данни от централния регион. VLOOKUP ще върне само първата централна стойност във всичките пет записа. Това е основен фон.
Но това не означава, че не можем да постигнем това. Можем да използваме формула за сложни масиви, както е показано по -долу.
= ИНДЕКС ($ C $ 2: $ C $ 14, МАЛКИ (АКО (G2 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1), ROW (1: 1)))
Напишете тази формула и натиснете CTRL+SHIFT+ENTER.
И свършено.
Обясних го в статия как да ПОГЛЕДАМЕ няколко стойности в 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 функция