Посібник Excel VLOOKUP для початківців
Шарлотта Міллер
⚡ Розумний підсумок
У посібнику з Excel VLOOKUP пояснюється, як функція вертикального пошуку виконує пошук у першому стовпці таблиці та повертає відповідне значення з іншого стовпця. У цьому посібнику розглядаються синтаксис, точні та приблизні збіги, перехресний пошук, поширені помилки та сучасний альтернативний варіант XLOOKUP.
Що таке VLOOKUP?
VLOOKUP (V розшифровується як Vertical — вертикальний) — це вбудована функція Excel, яка встановлює зв'язок між стовпцями в електронній таблиці. Вона дозволяє шукати значення в одному стовпці та повертати відповідне значення з іншого стовпця в тому ж рядку.
Синтаксис та аргументи VLOOKUP
Перш ніж застосовувати функцію VLOOKUP, корисно зрозуміти структуру формули. Функція приймає чотири аргументи та дотримується однакової схеми в усіх версіях Excel.
- lookup_value — значення, яке потрібно знайти (посилання на клітинку або літерал).
- table_array — діапазон комірок, що містить стовпець пошуку та стовпець повернення.
- col_index_num — номер стовпця в table_array, з якого потрібно повернути значення (1 — крайній лівий).
- пошук_діапазону — FALSE для точного збігу, TRUE (або пропущено) для приблизного збігу для відсортованих даних.
Важливо: Значення пошуку має знаходитися в крайньому лівому стовпці масиву table_array, а функція VLOOKUP шукає лише зліва направо.
Використання VLOOKUP
Коли вам потрібно знайти певну інформацію у великій електронній таблиці або неодноразово отримувати одне й те саме значення, функція VLOOKUP значно заощаджує час порівняно з ручним фільтруванням.
Розглянемо a Таблиця заробітної плати компанії підтримується фінансовою командою. Ви починаєте з відомої інформації — індексу — і використовуєте функцію VLOOKUP для отримання невідомого значення.
Наприклад, ви вже знаєте ім'я співробітника:
І ви хочете переглянути зарплату працівника:
Таблиця Excel для вищезгаданого прикладу:
Завантажте наведений вище файл Excel
Щоб знайти невідому зарплату працівника, ми вводимо Employee Code що вже доступно.
Застосовуючи функцію VLOOKUP, значення зарплати, що відповідає цьому співробітнику Code з’являється автоматично.
Як використовувати функцію VLOOKUP в Excel
Дотримуйтесь цієї покрокової інструкції, щоб застосувати функцію VLOOKUP в Excel:
Крок 1) Перейдіть до цільової комірки
Клацніть клітинку, де потрібно відобразити зарплату вибраного працівника — у цьому прикладі клітинку H3.
Крок 2) Введіть функцію VLOOKUP =VLOOKUP()
Введіть функцію в клітинку. Почніть зі знака рівності (який вказує Excel на наступну формулу), а потім додайте ключове слово VLOOKUP: =VLOOKUP().
У дужках міститься набір аргументів (фрагменти даних, необхідні функції).
VLOOKUP вимагає чотирьох аргументів:
Крок 3) Перший аргумент — значення пошуку
Перший аргумент – це посилання на клітинку для значення, яке потрібно знайти. У цьому випадку, це Employee Code — це значення пошуку, тому першим аргументом є H2 — комірка, вміст якої має збігатися в Excel.
Крок 4) Другий аргумент — масив таблиці
Це стосується блоку значень, які потрібно шукати, відомого в Excel як табличний масив або таблицю пошуку. У нашому прикладі таблиця пошуку працює від B2 до E25.
ПРИМІТКА: Стовпець підстановки має бути крайнім лівим стовпцем масиву таблиці.
Крок 5) Третій аргумент — col_index_num
Це вказує функції VLOOKUP, який стовпець у масиві таблиці містить повернуте значення. Зарплата співробітника знаходиться в четвертому стовпці, тому індекс стовпця дорівнює 4.
Крок 6) Четвертий аргумент — точне або приблизне збіг
Останній аргумент – це прапорець пошуку в діапазоні. Він контролює, чи повертає VLOOKUP точний чи приблизний збіг. Тут нам потрібен точний збіг (FALSE).
- ПОМИЛКОВИЙ — точна відповідність.
- ІСТИНА — приблизний збіг.
Крок 7) Натисніть Enter
Натисніть Enter, щоб завершити формулу. Спочатку ви побачите помилку, оскільки немає співробітника Code вже введено в H2.
Після введення дійсного імені співробітника Code У клітинці H2 повертається відповідна зарплата працівника.
Коротше кажучи, формула повідомляє Excel, що відомі значення знаходяться в крайньому лівому стовпці даних (Співробітник Code). Потім функція VLOOKUP сканує таблицю та повертає значення четвертого стовпця у відповідному рядку — зарплату співробітника.
У цьому прикладі розглядалися точні збіги (ключове слово FALSE). У наступному розділі пояснюються приблизні збіги.
VLOOKUP для приблизних збігів (TRUE Ключове слово як останній параметр)
Розглянемо сценарій, у якому таблиця розраховує знижки для клієнтів, які не купують рівно десятки чи сотні товарів.
Як показано нижче, компанія застосовує знижки на кількості від 1 до 10 000:
Завантажте наведений вище файл Excel
Клієнт рідко купує рівно 100 або 1,000 одиниць. Режим приблизного збігу дозволяє функції VLOOKUP знайти найближче менше значення, а не наполягати на точному числі. Кроки:
Крок 1) Клацніть клітинку, куди потрібно ввести функцію VLOOKUP — посилання на клітинку I2.
Крок 2) Введіть =VLOOKUP() у клітинку та додайте аргументи в дужках.
Крок 3) Аргумент 1: Введіть посилання на клітинку, значення якої потрібно зіставити з таблицею підстановки.
Крок 4) Аргумент 2: Виберіть таблицю підстановки — тут стовпці «Кількість» та «Знижка».
Крок 5) Аргумент 3: Введіть індекс стовпця в таблиці пошуку, з якого потрібно повернути відповідне значення.
Крок 6) Аргумент 4: Встановіть останній аргумент на ІСТИНА для приблизних збігів.
Крок 7) Натисніть клавішу Enter. Формула тепер застосовується до клітинки. Коли ви вводите будь-яку кількість, Excel повертає діапазон знижок на основі приблизного збігу.
ПРИМІТКА: Якщо залишити четвертий аргумент порожнім, Excel за замовчуванням використовуватиме значення TRUE (приблизний збіг). Для приблизних збігів стовпець пошуку має бути відсортований у порядку зростання.
Функція Vlookup застосовується між 2 різними аркушами, розміщеними в одній робочій книзі
Тепер розглянемо робочу книгу з двома аркушами. На аркуші 1 перелічено співробітників. Code, Ім'я та посада; на аркуші 2 перелічено співробітника Code та Заробітна плата працівника.
АРКУШ 1:
АРКУШ 2:
Завантажте наведений вище файл Excel
Мета полягає в тому, щоб об'єднати всі дані на Аркуші 1, як показано нижче:
VLOOKUP може агрегувати дані, щоб співробітник Code, Ім'я та Зарплата відображаються разом на одному аркуші.
Ми починаємо з Аркуша 2, оскільки він надає два аргументи — тут знаходиться стовпець «Зарплата співробітника», а індекс стовпця дорівнює 2.
Ми хочемо знайти зарплату, яка підходить кожному працівнику Code.
Дані охоплюють клітинки від A2 до B25 — це наш табличний масив.
Крок 1) Перейдіть на Аркуш 1 та введіть показані заголовки.
Крок 2) Клацніть клітинку поруч із «Зарплата співробітника» — клітинку F3 — куди буде додано формулу VLOOKUP.
Введіть функцію VLOOKUP: =VLOOKUP().
Крок 3) Аргумент 1: Введіть F2 — клітинку, що містить співробітника Code для збігу в таблиці пошуку.
Крок 4) Аргумент 2: Таблиця підстановки знаходиться на іншому аркуші, тому посилайтеся на неї з назвою аркуша: Аркуш2!A2:B25.
Крок 5) Аргумент 3: Введіть індекс стовпця в таблиці пошуку, який містить повернуте значення.
Крок 6) Аргумент 4: Використовуйте FALSE для точного збігу, оскільки нам потрібна точна зарплата, яка відповідає кожному співробітнику. Code.
Крок 7) Натисніть Enter. Коли ви вводите ім'я співробітника Code, клітинка повертає відповідну зарплату, взяту з Аркуша 2.
Поширені помилки VLOOKUP та їх виправлення
Навіть досвідчені користувачі стикаються з помилками VLOOKUP. Найпоширеніші та швидкі виправлення:
- # N / A — Функція VLOOKUP не може знайти значення пошуку. Перевірте наявність зайвих пробілів, невідповідних типів даних (чисел, що зберігаються як текст) або чи дійсно значення існує в першому стовпці table_array.
- #REF! — col_index_num більше, ніж кількість стовпців у table_array. Зменште індекс стовпця або розширте діапазон.
- #VALUE! — значення col_index_num менше за 1 або аргумент недійсний. Перевірте синтаксис формули.
- Повернуто неправильний результат — четвертий аргумент має значення TRUE або пропущено, але стовпець пошуку не відсортовано. Перейдіть до значення FALSE або відсортуйте стовпець за зростанням.
- Заблоковані посилання — під час копіювання формули використовуйте абсолютні посилання (наприклад, $B$2:$E$25), щоб table_array не зміщувався.
VLOOKUP проти XLOOKUP: що слід використовувати?
Microsoft представив XLOOKUP у Microsoft 365 та Excel 2021 як сучасна заміна функції VLOOKUP. Вона усуває кілька обмежень функції VLOOKUP і тепер є рекомендованим вибором у підтримуваних версіях.
| особливість | ВЛООКУП | XLOOKUP |
|---|---|---|
| Напрямок пошуку | Тільки зліва направо | Будь-який напрямок (ліворуч, праворуч, вгору, вниз) |
| Тип відповідності за замовчуванням | Приблизно (TRUE) | Точний |
| Обробка у разі незнаходження | Повернення #N/A | Вбудований аргумент if_not_found |
| Індекс стовпця | Жорстко закодований номер | Посилання на діапазон стовпців return |
| доступність | Усі версії Excel | Microsoft 365, Excel 2021, Excel для Інтернету |
Коли слід використовувати функцію VLOOKUP: Книга має працювати в Excel 2019 або ранішій версії, інакше ви зберігаєте застарілі формули. Коли варто обрати XLOOKUP: Ви створюєте нові книги в сучасному Excel і хочете використовувати пошук зліва, чіткішу обробку помилок і точний збіг за замовчуванням. Дізнайтеся більше про функції пошуку в Підручники з Excel серії.
Висновок
Три наведені вище сценарії пояснюють, як працює функція VLOOKUP для точних збігів, приблизних збігів та перехресних посилань. Потренуйтеся на власних наборах даних, щоб відпрацювати плавність. VLOOKUP залишається важливою функцією в MS Excel для ефективного керування даними, а XLOOKUP розширює цей набір інструментів у сучасному Excel.
