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

Автор

Ключові висновки

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

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

Що таке VLOOKUP в Excel?

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

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

Синтаксис VLOOKUP:

=VLOOKUP(lookup_value, table_array, col_index_num[, exact])

Ця електронна таблиця містить зразки даних, щоб показати, як VLOOKUP застосовується до набору рядків і стовпців:

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

=VLOOKUP(I3, A2:E26, 2)

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

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

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

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

  Контролюйте мережу та використання даних вашого iPhone із Центру сповіщень

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

Як зробити VLOOKUP в Excel

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

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

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

Нарешті, col_index_no – це другий стовпець у таблиці, де містяться відгуки. Забравши їх, давайте приступимо до написання цієї формули.

  • Виберіть клітинку, у якій потрібно відобразити результати. У цьому прикладі ми використаємо F6.
  • У рядку формул введіть =VLOOKUP(.
  • Виділіть клітинку, що містить шукане значення. Це E6 у цьому прикладі, який містить Pizza.
  • Введіть кому (,) і пробіл, а потім виділіть масив таблиці. У цьому прикладі це A2:C9.
  • Далі введіть у стовпці номер потрібного значення. Відгуки знаходяться у другому стовпці, тож це 2 для цього прикладу.
  • Для останнього аргументу введіть FALSE, якщо вам потрібна точна відповідність введеному елементу. В іншому випадку введіть TRUE, щоб побачити найближчий відповідник для нього.
  • Закінчуйте формулу круглою дужкою. Не забудьте поставити кому між аргументами. Ваша кінцева формула має виглядати так: 
    =VLOOKUP(E6,A2:C9,2,FALSE)
  • Натисніть Enter, щоб отримати результат.
    •   11 найкращих платформ AiOps для аналізу та моніторингу даних, веб-сайтів, журналів, мережі тощо

    Тепер Excel повертатиме відгуки про піцу в клітинку F6.

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

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

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

  • Виділіть клітинку поруч із першим елементом. У цьому прикладі це F6.
  • Введіть формулу VLOOKUP для першого елемента.
  • Перемістіть курсор до аргументу масиву таблиці у формулі та натисніть F4 на клавіатурі, щоб зробити його абсолютним посиланням.
  • Ваша остаточна формула має виглядати так: 
    =VLOOKUP(E6,$A$2:$C$9,2,FALSE)
  • Натисніть Enter, щоб отримати результати.
  • Коли з’явиться результат, перетягніть клітинку з результатами вниз, щоб заповнити формулу для всіх інших елементів.

    • Якщо залишити останній аргумент порожнім або ввести
    ПРАВДА
    , VLOOKUP прийматиме приблизні збіги. Однак приблизний збіг може бути занадто м’яким, особливо якщо ваш список не відсортовано. Як правило, для кінцевого аргументу слід встановити значення
    ПОМИЛКОВИЙ
    під час пошуку унікальних значень, таких як у цьому прикладі.

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

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

      Як встановити Beautiful Soup і використовувати його для проектів веб-збирання?

    Уявіть, що у вас є друга електронна таблиця, де ви хочете відсортувати продукти з попереднього прикладу за їх ціною. Ви можете отримати ціни на ці продукти з батьківської електронної таблиці за допомогою 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.