Функція 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 у діапазоні_пошуку) або приблизним (TRUE або 1).
- При орієнтовному пошуку переконайтеся, що список відсортований у порядку зростання (зверху вниз), інакше результат може бути неточним.
- Коли range_lookup має значення TRUE (приблизний пошук) і дані сортуються у порядку зростання:
- Якщо функція VLOOKUP не може знайти значення, вона повертає найбільше значення, яке менше значення lookup_value.
- Він повертає помилку #N/A, якщо lookup_value менше найменшого значення.
- Якщо lookup_value є текстом, можна використовувати символи підстановки (див. Приклад нижче).
Тепер, сподіваючись, що у вас є базове уявлення про те, що може зробити функція VLOOKUP, давайте очистимо цю цибулю від лушпиння і побачимо кілька практичних прикладів функції VLOOKUP.
10 прикладів Excel VLOOKUP (базові та розширені)
Ось 10 корисних прикладів використання Excel Vlookup, які покажуть вам, як використовувати його у повсякденній роботі.
Приклад 1 - Визначення математичного балу Бреда
У наведеному нижче прикладі VLOOKUP у мене є список імен студентів у крайньому лівому стовпці та позначки з різних предметів у стовпцях В-Е.
Тепер приступаємо до роботи та використовуємо функцію 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, МАТЧ (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, МАТЧ (H3, $ A $ 2: $ E $ 2,0), 0)
Значення пошуку перетворено у випадаючі списки.
Нижче наведено кроки для створення розкривного списку:
- Виберіть у розкривному списку клітинку, у якій ви хочете. У цьому прикладі в G4 нам потрібні імена студентів.
- Перейдіть до Дані -> Інструменти даних -> Перевірка даних.
- У діалоговому вікні Перевірка даних на вкладці налаштувань виберіть Список зі спадного меню Дозволити.
- У джерелі виберіть $ A $ 3: $ A $ 10
- Натисніть OK.
Тепер у клітині G4 буде випадаючий список. Аналогічно, ви можете створити один у H3 для суб'єктів.
Приклад 4 - Тристоронній пошук
Що таке тристоронній пошук?
У прикладі 2 ми використовували одну таблицю пошуку з оцінками для студентів з різних предметів. Це приклад двостороннього пошуку, оскільки ми використовуємо дві змінні для отримання балів (ім’я учня та ім’я суб’єкта).
Припустимо, що через рік студент має три різні рівні іспитів, одиничний тест, проміжний і підсумковий іспит (це те, що я мав, коли був студентом).
Тристоронній пошук-це можливість отримувати оцінки студента за певний предмет із зазначеного рівня іспиту.
Щось, як показано нижче:
У наведеному вище прикладі функція VLOOKUP може шукати у трьох різних таблицях (одиничний тест, проміжний та підсумковий іспит) і повертає оцінку для зазначеного студента з зазначеного предмета.
Ось формула, яка використовується у клітинці 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), МАТЧ (H3, $ A $ 2: $ E $ 2,0), 0)
Ця формула використовує функцію ВИБІР, щоб переконатися, що на правильну таблицю посилається. Давайте проаналізуємо частину формули ВИБРАТИ:
ВИБРАТИ (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, який містить бали за Unit Unit. Якщо це середньостроковий період, він повертає $ A $ 11: $ E $ 15, інакше повертає $ A $ 19: $ E $ 23.
Це робить масив таблиць VLOOKUP динамічним, а отже, робить його тристороннім пошуком.
Приклад 5 - Отримання останнього значення зі списку
Ви можете створити формулу VLOOKUP, щоб отримати останнє числове значення зі списку.
Найбільше позитивне число, яке можна використати в Excel 9.99999999999999E+307. Це також означає, що найбільший номер пошуку в номері VLOOKUP також однаковий.
Я не думаю, що вам коли -небудь знадобиться будь -який розрахунок за такою великою кількістю. І це саме те, що ми можемо використовувати, щоб отримати останнє число у списку.
Припустимо, у вас є набір даних (у A1: A14) як показано нижче, і ви хочете отримати останній номер у списку.
Ось формула, яку ви можете використовувати:
= VLOOKUP (9.99999999999999E+307, $ A $ 1: $ A $ 14,ІСТИНА)
Зауважте, що у формулі вище використовується приблизна відповідність VLOOKUP (зверніть увагу на TRUE в кінці формули, замість FALSE або 0). Також зверніть увагу, що для роботи цієї формули VLOOKUP список не потрібно сортувати.
Ось як працює приблизна функція VLOOKUP. Він сканує крайній лівий стовпець зверху вниз.
- Якщо він знаходить точну відповідність, він повертає це значення.
- Якщо він знаходить значення, яке перевищує значення пошуку, воно повертає значення у комірці над ним.
- Якщо значення пошуку більше всіх значень у списку, воно повертає останнє значення.
У наведеному вище прикладі діє третій сценарій.
З тих пір 9.99999999999999E+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.
Наприклад, клітинка С2 має ABC, тому функція VLOOKUP переглядає імена в A2: A8 і шукає ABC. Він знаходить відповідність у комірці A2, оскільки містить ABC у компанії ABC Ltd. Не має значення, чи є символи ліворуч чи праворуч від ABC. Поки в текстовому рядку немає символу ABC, він вважатиметься відповідним.
Примітка: Функція VLOOKUP завжди повертає перше відповідне значення і перестає шукати далі. Отже, якщо у вас є ABC ТОВ, і ABC Corporation у списку, він поверне перше і проігнорує решту.
Приклад 7 - VLOOKUP Повертає помилку, незважаючи на збіг у значенні пошуку
Це може звести вас з розуму, коли ви побачите відповідне значення пошуку та функція VLOOKUP повертає помилку.
Наприклад, у наведеному нижче випадку є збіг (Метт), але функція VLOOKUP все одно повертає помилку.
Тепер, коли ми бачимо, що є збіг, ми не можемо побачити неозброєним оком, це те, що можуть бути пробіли на початку або на кінці. Якщо у вас є ці додаткові пробіли до, після або між значеннями пошуку, це НЕ точна відповідність.
Часто це відбувається, коли ви імпортуєте дані з бази даних або отримуєте їх від когось іншого. Ці провідні/кінцеві простори мають тенденцію проникати.
Рішенням тут є функція TRIM. Він видаляє будь -які провідні або кінцеві пробіли або зайві пробіли між словами.
Ось формула, яка дасть вам правильний результат.
= VLOOKUP ("Метт", 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, що є оцінкою для Метта в A2.
Щоб зробити його чутливим до регістру, нам потрібно використовувати допоміжний стовпець (як показано нижче):
Щоб отримати значення у стовпці -помічнику, використовуйте функцію = ROW (). Він просто отримає номер рядка у комірці.
Коли у вас є стовпець-помічник, ось формула, яка дасть результат пошуку з урахуванням регістру.
= VLOOKUP (МАКС. (ТОЧНО (E2, $ A $ 2: $ A $ 9)*(РЯД ($ A $ 2: $ A $ 9))), $ B $ 2: $ C $ 9,2,0)
Тепер давайте розберемося і зрозуміємо, що це робить:
- EXACT (E2, $ A $ 2: $ A $ 9) - Ця частина порівнює значення пошуку в E2 з усіма значеннями в A2: A9. Він повертає масив TRUEs/FALSEs, де 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 (ТОЧНО (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 з Меттом як значенням для пошуку, він завжди поверне 91, що є оцінкою для першого входження Метта у списку. Щоб отримати оцінку для Метта за кожен тип іспиту (одиничний тест, середньостроковий та підсумковий), вам потрібно створити унікальне значення пошуку.
Це можна зробити за допомогою стовпця -помічника. Перший крок - вставити допоміжний стовпець ліворуч від балів.
Тепер, щоб створити унікальний кваліфікатор для кожного екземпляра імені, використовуйте таку формулу в C2: = A2 & ”|” & B2
Скопіюйте цю формулу до всіх клітинок у стовпці -помічнику. Це створить унікальні значення пошуку для кожного екземпляра імені (як показано нижче):
Тепер, хоча імена повторювалися, повторення немає, коли ім’я поєднується з рівнем експертизи.
Це полегшує роботу, оскільки тепер ви можете використовувати значення допоміжного стовпця як значення пошуку.
Ось формула, яка дасть вам результат у G3: I8.
= VLOOKUP ($ F3 & "|" & G $ 2, $ C $ 2: $ D $ 19,2,0)
Тут ми об’єднали ім’я студента та рівень іспиту, щоб отримати значення пошуку, і ми використовуємо це значення пошуку та перевіряємо його у стовпці -помічнику, щоб отримати відповідний запис.
Примітка: У наведеному вище прикладі ми використовували | як роздільник під час приєднання тексту до допоміжного стовпця. У деяких виключно рідкісних (але можливих) умовах у вас можуть бути два критерії, які відрізняються, але в кінцевому підсумку дають однаковий результат. Ось приклад:
Зауважте, що хоча А2 і А3 різні, а В2 і В3 різні, комбінації в кінцевому підсумку однакові. Але якщо ви використовуєте роздільник, то навіть комбінація буде іншою (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 (ПОМИЛКА (VLOOKUP (D2, $ A $ 2: $ B $ 7,2,0)), "Не знайдено", VLOOKUP (D2, $ A $ 2: $ B $ 7,2,0))
Дивіться також: Як обробляти помилки VLOOKUP в Excel.
Ось і все в цьому підручнику VLOOKUP.
Я намагався охопити основні приклади використання функції Vlookup у Excel. Якщо ви хочете побачити більше прикладів, доданих до цього списку, дайте мені знати в розділі коментарів.
Примітка: Я доклав усіх зусиль, щоб вичитувати цей підручник, але у разі виявлення помилок чи орфографічних помилок, будь ласка, дайте мені знати 🙂
Використання Функція VLOOKUP в Excel - Відео
- Функція Excel HLOOKUP.
- Функція Excel XLOOKUP
- Функція Excel INDEX.
- Непряма функція Excel.
- Функція Excel MATCH.
- Функція Excel OFFSET.
