Приклад VLOOKUP - Як зробити VLOOKUP в Excel

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

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

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

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

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

Що таке VLOOKUP?

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

VLOOKUP - це функція бази даних, тому вона призначена для таблиць баз даних. Такі таблиці в основному являють собою списки різних елементів. Наприклад, ви можете використовувати цю функцію під час роботи зі списками продуктів, співробітників, клієнтів тощо.

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

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

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

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

Як працює VLOOKUP

Ось синтаксис формул, що описують функцію VLOOKUP:

=VLOOKUP(value, table, column_index, [range])

  • value це те, що ця функція буде шукати в першому стовпці
  • table- це таблиця, з якої функція отримуватиме необхідну інформацію
  • column_index - номер стовпця, з якого функція отримуватиме інформацію
  • range- це логічний параметр, який може бути TRUE або FALSE. ІСТИНА - це значення за замовчуванням, і воно відповідає приблизному збігу. FALSE покаже вам лише точні збіги.

Навіть назва цієї функції включає "вертикальний", а VLOOKUP призначений лише для таблиць, де дані упорядковані у вертикальні стовпці. Тому, якщо ви впорядковуєте дані горизонтально, ця функція буде марною. У цьому випадку ви можете використовувати подібну функцію для горизонтального пошуку - HLOOKUP.

Також слід пам’ятати, що ця функція працює лише зліва направо. Іншими словами, якщо унікального ідентифікатора немає в першому стовпці вашої таблиці, функція не зможе отримати інформацію зі стовпців ліворуч від ідентифікатора.

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

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

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

Отже, обидві наведені нижче формули отримуватимуть дані за допомогою приблизної відповідності:

=VLOOKUP(value, table, column_index)

=VLOOKUP(value, table, column_index, TRUE)

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

Наступна формула буде застосовувати режим точного збігу:

=VLOOKUP(value, table, column_index, FALSE)

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

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

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

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

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

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

# Помилки не застосовуються

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

Ви можете отримати цю помилку з кількох причин. Ось кілька найпоширеніших випадків:

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

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

Ви можете налаштувати текст помилки # N / A за допомогою функції IFNA. У цьому випадку вам потрібно написати довшу формулу за допомогою IFNA, яка включає VLOOKUP.

Ось приклад формули, яка поверне "не знайдено" замість # N / A:

=IFNA(VLOOKUP(value, table, column_index, FALSE), "not found")

І ось формула, яка поверне порожній результат:

=IFNA(VLOOKUP(value, table, column_index, FALSE), "")

Хоча ви можете налаштувати повідомлення, ви можете розглянути помилку # N / A, оскільки вона негайно приверне вашу увагу та повідомить вас, якщо щось піде не так.

Як користуватися VLOOKUP

Ви можете писати формули з нуля, а також скористатися меню Excel. Виділіть клітинку, де потрібно відобразити результат, а потім виберіть вкладку «Формули».

Після цього натисніть «Вставити функцію». Ви побачите вікно, де ви можете вибрати категорії функцій та вибрати функцію VLOOKUP. Ви також можете скористатися вікном "Пошук функції" та ввести "vlookup".

Виберіть функцію, і з’явиться вікно “Аргумент функції”. Тут ви можете ввести необхідні параметри функції. У цьому вікні ви повинні вказати унікальний ідентифікатор, який ви шукаєте, розташування бази даних та інформацію, яка відповідає ідентифікатору, який ви хочете отримати.

Такими аргументами є "Lookup_value", "Table_array" і "Col_index_num". Ці поля написані жирним шрифтом, оскільки ці аргументи є обов’язковими.

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

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

Тепер потрібно ввести другий аргумент. База даних не обов'язково повинна починатися з верхнього лівого кута. Наприклад, у вас також може бути рядок, що описує стовпці, який служить заголовком.

"Однією з найкращих речей функції VLOOKUP є те, що розташування бази даних також може бути налаштоване", - зазначає Бріджит Аллен, бухгалтер Best Writers Online.

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

Наприклад, якщо ваша таблиця починається з верхнього лівого кута, а її перший рядок є заголовком, ви можете вибрати всю базу даних без першого рядка. Якщо у вашій базі даних є чотири стовпці (AD) і п’ять елементів, масив таблиці буде мати формат A2: D6, оскільки комірки A1-D1 будуть містити заголовок.

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

‘database_name’!A2:D6

У цьому випадку “ім’я_бази даних” - це назва вкладки робочого аркуша.

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

Наприклад, якщо ви хочете отримати ціну товару з таблиці на початку цієї статті, вам слід використовувати третій рядок, і значення “Col_index_num” буде 3.

Підведенню

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

Якщо ви не знайомі з Excel, у вас можуть виникнути труднощі з цією функцією, оскільки вона має чотири аргументи.

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