Як виконати VLOOKUP в електронній таблиці Excel

Зміст

Основні моменти

Функція VLOOKUP в Excel може здаватися складною спочатку, проте з практикою вона стає досить легкою для використання.
Ця формула дає можливість знаходити певне значення в таблиці та показувати відповідний йому результат.
VLOOKUP є корисною для аналізу даних, розрахунку середнього балу (GPA) та порівняння різних стовпців.

Хоча VLOOKUP може бути не такою інтуїтивно зрозумілою, як деякі інші функції, вона є потужним інструментом, який вартий вивчення. Розгляньте декілька прикладів, щоб навчитися застосовувати VLOOKUP у своїх власних проєктах Excel.

Що таке VLOOKUP в Excel?

Функція VLOOKUP в Excel працює подібно до телефонної книги. Ви вказуєте значення для пошуку (наприклад, ім’я), і вона видає вам відповідне значення (наприклад, номер телефону).

На початку VLOOKUP може здатися трохи заплутаною, але, попрактикувавшись з декількома прикладами, ви зможете вільно її використовувати без жодних проблем.

Синтаксис VLOOKUP виглядає наступним чином:

=VLOOKUP(значення_пошуку; масив_таблиці; номер_стовпця; [точність])

Для наочності, розглянемо таблицю з даними, що ілюструє застосування VLOOKUP до рядків та стовпців:

У цьому прикладі, формула VLOOKUP буде виглядати так:

=VLOOKUP(I3; A2:E26; 2)

Давайте детально розглянемо, що означає кожен з цих параметрів:

значення_пошуку – це значення, яке ви шукаєте в таблиці. У цьому прикладі, це значення в комірці I3, що дорівнює 4.
масив_таблиці – це діапазон, який містить таблицю. У даному випадку, це діапазон A2:E26.
номер_стовпця – це номер стовпця, з якого потрібно отримати значення. Колір знаходиться у другому стовпці масиву, тому тут використовується 2.
точність – це необов’язковий параметр, який визначає, чи має пошук бути точним (FALSE) чи приблизним (TRUE, за замовчуванням).

Ви можете використовувати VLOOKUP для відображення отриманого значення або поєднувати її з іншими функціями для подальших обчислень.

Літера “V” у VLOOKUP означає “вертикальний”, що вказує на пошук у вертикальному стовпці даних. VLOOKUP повертає дані лише зі стовпців, які знаходяться праворуч від стовпця пошуку. Незважаючи на обмеження вертикальної орієнтації, VLOOKUP є важливим інструментом, який спрощує багато завдань в Excel. Він може бути корисним при обчисленні середнього балу або порівнянні двох стовпців.

VLOOKUP здійснює пошук у першому стовпці
масив_таблиці
для
значення_пошуку. Якщо потрібно здійснити пошук в іншому стовпці, можна перемістити посилання на таблицю, але необхідно пам’ятати, що значення, що повертається, завжди має бути праворуч від стовпця пошуку. За необхідності, структуру таблиці можна змінити, щоб відповідати цій вимозі.

Як застосувати VLOOKUP в Excel

Тепер, коли ви розумієте, як працює функція VLOOKUP в Excel, її використання зводиться до правильного введення аргументів. Якщо ви маєте труднощі з введенням аргументів, почніть формулу, набравши знак рівності (=), а потім VLOOKUP. Далі, ви можете клацати на комірках, щоб додати їх як аргументи до формули.

Давайте попрактикуємося на прикладі таблиці вище. Уявіть, що у вас є таблиця зі стовпцями: “Назва продуктів”, “Відгуки” та “Ціна”. Ви хочете, щоб Excel показав кількість відгуків для конкретного продукту, наприклад, піци.

Ви можете легко зробити це за допомогою VLOOKUP. Просто визначте, яким буде кожен аргумент. У цьому прикладі, оскільки ми шукаємо інформацію про значення в комірці E6, E6 є значенням_пошуку. Масив_таблиці – це діапазон, що містить таблицю, тобто A2:C9. Включати потрібно лише самі дані, без заголовків у першому рядку.

Нарешті, номер_стовпця – це другий стовпець у таблиці, де зберігаються відгуки. Тепер давайте почнемо писати цю формулу.

Виберіть комірку, де ви хочете відобразити результат. У цьому прикладі, ми використаємо F6.

У рядку формул введіть =VLOOKUP(.

Виділіть комірку, що містить шукане значення. Це E6 у нашому випадку, яка містить “Pizza”.

Введіть кому (,) та пробіл, а потім виділіть масив таблиці. У цьому прикладі це A2:C9.

Далі введіть номер стовпця, з якого потрібно отримати значення. Відгуки знаходяться у другому стовпці, тому це буде 2.

Для останнього аргументу введіть FALSE, якщо вам потрібна точна відповідність введеному елементу. В іншому випадку введіть TRUE, щоб отримати найближчу відповідність.

Закінчіть формулу закритою дужкою. Не забувайте ставити коми між аргументами. Ваша кінцева формула має виглядати так:

=VLOOKUP(E6;A2:C9;2;FALSE)

Натисніть Enter, щоб отримати результат.

Тепер Excel покаже кількість відгуків про піцу в комірці F6.

Як використовувати VLOOKUP для кількох елементів в Excel

Ви також можете шукати кілька значень у стовпці за допомогою VLOOKUP. Це може стати в нагоді, коли потрібно, наприклад, створити графіки або діаграми Excel на основі отриманих даних. Ви можете це зробити, написавши формулу VLOOKUP для першого елемента, а потім автоматично заповнити решту.

Секрет полягає у використанні абсолютних посилань на комірки, щоб вони не змінювалися під час автоматичного заповнення. Розглянемо, як це зробити:

Виділіть комірку поруч із першим елементом. У цьому прикладі, це F6.

Введіть формулу VLOOKUP для першого елемента.

Перемістіть курсор до аргументу масиву_таблиці у формулі і натисніть F4 на клавіатурі, щоб зробити його абсолютним посиланням.

Ваша кінцева формула має виглядати так:

=VLOOKUP(E6;$A$2:$C$9;2;FALSE)

Натисніть Enter, щоб отримати результати.

Коли з’явиться результат, потягніть комірку з результатом вниз, щоб автоматично заповнити формулу для всіх інших елементів.

Якщо залишити останній аргумент порожнім або ввести
TRUE
, VLOOKUP прийме приблизні збіги. Однак, приблизний збіг може бути занадто неточним, особливо якщо ваш список не відсортований. Як правило, для останнього аргументу краще використовувати
FALSE
при пошуку унікальних значень, як у цьому прикладі.

Як з’єднати таблиці Excel за допомогою VLOOKUP

Ви також можете переносити інформацію з одного аркуша Excel на інший, використовуючи VLOOKUP. Це особливо корисно, коли ви хочете зберегти вихідну таблицю без змін, наприклад, якщо ви імпортували дані з веб-сайту до Excel.

Уявіть, що у вас є друга таблиця, де ви хочете відсортувати продукти з попереднього прикладу за їх ціною. Ви можете отримати ці ціни з першої таблиці, використовуючи VLOOKUP. Різниця тут полягає в тому, що вам потрібно перейти до першого аркуша, щоб вибрати масив таблиці. Ось як це зробити:

Виберіть комірку, де ви хочете відобразити ціну першого товару. У цьому прикладі, це B3.

Введіть =VLOOKUP( у рядку формул, щоб запустити формулу.

Клацніть комірку з назвою першого елемента, щоб додати її як значення_пошуку. У цьому прикладі, це A3 (шоколад).

Як і раніше, введіть кому (,) та пробіл, щоб перейти до наступного аргументу.

Перейдіть до першого аркуша і виділіть масив таблиці.

Натисніть F4, щоб зробити посилання абсолютним. Це зробить формулу застосовною до решти елементів.

Перебуваючи на першому аркуші, подивіться на рядок формул та введіть номер стовпця для стовпця з ціною. У цьому прикладі, це 3.

Введіть FALSE, оскільки в цьому випадку вам потрібна точна відповідність для кожного продукту.

Закрийте дужки та натисніть Enter. Кінцева формула має виглядати так:

=VLOOKUP(A3; Sheet1!$A$2:$C$9; 3; FALSE)

Перетягніть результат першого продукту вниз, щоб побачити результати для інших продуктів на другому аркуші.

Тепер ви можете сортувати дані Excel, не впливаючи на вихідну таблицю. Помилки друку є однією з поширених причин помилок VLOOKUP в Excel, тому уникайте їх у новому списку.

VLOOKUP – це чудовий спосіб швидше отримувати дані в Microsoft Excel. Хоча VLOOKUP виконує пошук лише в межах стовпця та має деякі інші обмеження, Excel має інші функції, які можуть виконувати більш потужний пошук.

Функція XLOOKUP в Excel схожа на VLOOKUP, але може здійснювати пошук як вертикально, так і горизонтально в електронній таблиці. Більшість функцій пошуку в Excel використовують однаковий процес, лише з деякими відмінностями. Використання будь-якої з них для конкретної мети пошуку є ефективним способом оптимізації ваших запитів в Excel.