VLOOKUP (функція VLOOKUP)

Порада.: Скористайтеся новою функцією xlookup , вдосконаленою ВЕРСІЄЮ VLOOKUP, яка працює в будь-якому напрямку, і повертає точні відповідники за замовчуванням, завдяки чому простіше і зручніше використовувати, ніж його попередник.

Функція VLOOKUP використовується, коли потрібно шукати елементи в таблиці або в діапазоні за рядками. Наприклад, знайдіть вартість автомобільної частини за номером частини або знайдіть ім'я працівника на основі ІДЕНТИФІКАТОРА працівника.

Порада.: Ознайомтеся з цими відео YouTube від творців Microsoft , щоб отримати докладнішу довідку з функції VLOOKUP!

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

= VLOOKUP (що потрібно знайти, де потрібно знайти його, номер стовпця в діапазоні, який містить значення, що повертається, повертає приблизний або точний збіг – вказано як 1/TRUE або 0/FALSE).

Ваші оголошення завжди будуть мати великий вплив

Порада.: Основне завдання функції VLOOKUP – упорядкування ваших даних, щоб значення, яке ви шукаєте (фрукт), розташовувалося ліворуч від повернутого значення, що потрібно знайти (кількість).

За допомогою функції VLOOKUP можна шукати значення в таблиці.

Синтаксис

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

Наприклад:

  • = VLOOKUP (A2; A10: C20; 2; TRUE)

  • =VLOOKUP("Самойленко";B2:E7;2;FALSE)

  • = VLOOKUP (a2, "відомості про клієнта!"! A:F, 3, FALSE)

Ім’я аргументу

Опис

шукане_значення    (обов’язково)

Значення, яке потрібно перевірити. Значення, яке потрібно знайти, має бути в першому стовпці діапазону клітинок, які ви вказали в аргументі table_array .

Наприклад, якщо таблиця-масив охоплює клітинки B2: D7, то ваш lookup_value має бути у стовпці B.

Шукане_значення може бути значенням або посиланням на клітинку.

таблиця    (обов’язково)

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

Перший стовпець діапазону в діапазоні має містити lookup_value. Діапазон значень також має містити повернуте значення, яке потрібно знайти.

Докладні відомості про вибір діапазонів на аркуші.

номер_стовпця    (обов’язково)

Номер стовпця (починаючи з 1 для самого лівого стовпця table_array), який містить повернуте значення.

точність_пошуку    (необов’язково)

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

  • Приблизний збіг – 1/True – перший стовпець таблиці сортується числами або за алфавітом, а потім буде шукати найближче значення. Це стандартний метод, якщо не вказано інший. Наприклад, = VLOOKUP (90, a1: B100; 2; TRUE).

  • Точна функція MATCH-0/false шукає точне значення у першому стовпці. Наприклад, = VLOOKUP ("Коваль", a1: B100; 2; FALSE).

Початок роботи

Щоб побудувати синтаксис функції VLOOKUP, потрібно задати чотири параметри.

  1. Шукане значення.

  2. Діапазон, який його містить. Пам’ятайте, що функція VLOOKUP працює належним чином, лише якщо шукане значення міститься в першому стовпці діапазону. Наприклад, якщо його розташовано в клітинці C2, діапазон має починатися зі стовпця C.

  3. Номер стовпця в діапазоні, який містить значення, що повертається. Наприклад, якщо вказати значення B2: D11, як діапазон, потрібно обчислити значення B як перший стовпець, C як другий тощо.

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

Тепер давайте об’єднаємо все описане вище разом:

= VLOOKUP (значення підстановки, діапазон, що містить значення підстановки, номер стовпця в діапазоні, який містить повернуте значення, приблизна відповідність (істина) або точна відповідність (FALSE)).

Приклади

Нижче наведено кілька прикладів того, як можна використовувати функцію VLOOKUP.

Приклад 1

Приклад 1: застосування функції VLOOKUP

Приклад 2

Приклад 2: застосування функції VLOOKUP

Приклад 3

Приклад 3: застосування функції VLOOKUP

Приклад 4

Приклад 4: застосування функції VLOOKUP

Приклад 5

Приклад 5: застосування функції VLOOKUP

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

Аркуш із стовпцями, які використовують ФУНКЦІЯ VLOOKUP, щоб отримати дані з інших таблиць

У цій статті стовпці A-F та H мають значення або формули, які використовують лише значення на аркуші, а решта стовпців за допомогою функції VLOOKUP використовують значення "а" (код клієнта) і стовпець B (адвокат), щоб отримати дані з інших таблиць.

  1. Скопіюйте таблицю, що містить поширені поля на новий аркуш, і надайте йому ім'я.

  2. Щоб відкрити діалогове вікно керування зв'язками, виберіть елемент Знаряддя для даних> дані >зв'язки .

    Діалогове вікно "керування зв'язками"
  3. Для кожного вказаного зв'язку зверніть увагу на таке:

    • Поле, у якому посилається таблиця (у дужках, у діалоговому вікні). Це lookup_value для формули VLOOKUP.

    • Ім'я зв'язаної таблиці підстановки. Це table_array у ФОРМУЛІ VLOOKUP.

    • Поле (стовпець) у таблиці пов'язаної підстановки з даними, які потрібно додати до нового стовпця. Ці відомості не відображаються в діалоговому вікні "керування зв'язками", щоб дізнатися, яке поле потрібно отримати. Потрібно відмітити номер стовпця (A = 1) – це col_index_num у формулі.

  4. Щоб додати поле до нової таблиці, введіть формулу VLOOKUP в першому пустому стовпці, використовуючи відомості, зібрані на кроці 3.

    У нашому прикладі стовпець G використовує адвоката ( lookup_value), щоб отримати дані рахунка за рахунок від четвертого стовпця (col_index_num = 4) зі таблиці "адвокати", tblattorneys ( table_array), з формулою = vlookup ([@Attorney]; tbl_Attorneys; 4; false).

    Формула також може використовувати посилання на клітинку та посилання на діапазон. У нашому прикладі це буде = VLOOKUP (a2; "адвокати"! A:D, 4, FALSE).

  5. Продовжуйте додавати поля, доки не буде всі потрібні поля. Якщо ви намагаєтеся підготувати книгу, що містить функції даних, у яких використовується кілька таблиць, змініть джерело даних функції даних на нову таблицю.

Проблема

Помилка

Повернуто помилкове значення

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

#N/A у клітинці

  • Якщо аргумент точність_пошуку має значення TRUE, а значення аргументу шукане_значення менше за найменше значення в першому стовпці таблиці, буде повернуто значення помилки #N/A.

  • Якщо аргумент точність_пошуку має значення FALSE, значення помилки свідчить про те, що точний номер не знайдено.

Докладні відомості про виправлення помилки #N/A у функції VLOOKUP див. в цій статті.

#REF! у клітинці

Якщо значення номер_стовпця більше кількості стовпців у таблиці-масиві, буде повернуто значення #REF!.

Докладні відомості про виправлення помилки #REF! у функції VLOOKUP див..

#VALUE! у клітинці

Якщо значення таблиця_масив менше за 1, .

Докладні відомості про виправлення помилки #VALUE! у функції VLOOKUP див..

#NAME? у клітинці

Значення помилки #NAME? зазвичай вказує на те, що у формулі немає лапок. Щоб знайти прізвище особи, переконайтеся, що його взято в лапки у формулі. Наприклад, введіть прізвище Самойленко у формулі =VLOOKUP("Самойленко";B2:E7;2;FALSE).

Докладні відомості про виправлення помилки #NAME! наведено в цій статті..

Помилки #SPILL! у клітинці

Цей конкретний #SPILL! помилка зазвичай це означає, що формула покладається на неявне перехрестя для значення підстановки, а також за допомогою всього стовпця як посилання. Наприклад, = VLOOKUP (A:a, a:a, 2, false). Щоб вирішити цю проблему, можна визначити посилання на підстановку з оператором @, наприклад: = VLOOKUP (@A: A, a:c, 2, false). Крім того, можна скористатися традиційним методом VLOOKUP і посилатися на одну клітинки, а не весь стовпець: = VLOOKUP (a2, a:c, 2, false).

Дія

Причина

Використання абсолютних посилань для значення точність_пошуку

Абсолютні посилання дають змогу заповнити формулу вниз, щоб вона завжди була спрямована в один і той самий діапазон пошуку.

Дізнайтесь, як використовувати абсолютні посилання на клітинку.

Не зберігайте числа чи дати як текст.

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

Відсортуйте перший стовпець

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

Використовуйте символи узагальнення

Якщо точність_пошуку має значення FALSE, а шукане_значення – це текст, у шуканому_значенні можна використовувати символи узагальнення – знак питання (?) і зірочку (*). Знак питання відповідає будь-якому одному символу. Зірочка відповідає будь-якій послідовності символів. Якщо потрібно знайти власне знак питання або зірочку, перед відповідним символом введіть тильду (~).

Наприклад, = VLOOKUP ("фонтан?"; B2: E7; 2, FALSE) шукатиме всі екземпляри фонтана з останньою буквою, яка може відрізнятися.

Переконайтеся, що ваші дані не містять помилкові символи.

Під час пошуку текстових значень у першому стовпці таблиці переконайтеся, що дані в ньому не містять пробілів на початку або в кінці, неузгоджених прямих (' або ") і фігурних (‘ або “) лапок або недрукованих символів. У таких випадках функція VLOOKUP може повернути хибне або неочікуване значення.

Щоб отримати точні результати, можливо, знадобиться видалити пробіли наприкінці клітинки після значень таблиці за допомогою функції CLEAN або TRIM.

Потрібна додаткова довідка?

Ви завжди можете поставити запитання експерту в спільноті Tech (у розділі Excel), отримати підтримку в спільноті, що допомагає знайти відповіді на запитання, або запропонувати нову функцію чи вдосконалення на форумі Excel User Voice.

Додаткові відомості

Короткий довідник:
функція VLOOKUP –короткий довідник
: функція VLOOKUP поради з виправлення неполадокYouTube: VLOOKUP відео від творців
Microsoftяк виправити #VALUE! помилка в функції VLOOKUP
Виправлення помилки #N/A у функції VLOOKUP
Огляд формул в Excel
Способи уникнення недійсних формул
Виявлення помилок у формулах
Функції Excel (за алфавітом)
Функції Excel (за категоріями)
Функція VLOOKUP (безкоштовне ознайомлення)

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

Удосконалення навичок роботи з Office
Ознайомтеся з навчальними матеріалами
Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

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

×