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

 

Функція HLOOKUP (ГПР) робить те ж саме з даними, що розташовані горизонтально, тобто по рядках. Але наші очі краще спраймають дані в стовпцях, тому ВПР вживається частіше.

 

ВПР допускає неточний пошук, тобто якщо якийсь символ невідомий, ви його можете опустити, використавши символи підстановки:

·        ? — замінює один символ

·        * — замінює будь-яку кількість символів у рядку

 

Синтаксис функції VLOOKUP:

 

= ВПР або VLOOKUP (шукане значення, діапазон таблиці, номер стовпчика, [TRUE/FALSE])

 

• шукане значення — що шукаємо; посилання на клітинку з шуканим значенням

• діапазон таблиці — де шукаємо; посилання на діапазон клітинок, які містять шукане значення у першому стовпці

• номер стовпчика  діапазону, з якого потрбно повернути значення

• TRUE/FALSE — необов'язковий параметр. Якщо перший стовпчик діапазону пошуку відсортовано за зростанням, ми вказуємо TRUE чи 1. Якщо ні, то FALSE чи 0.

 

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

 

Недоліки ВПР

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

2. Пошук здійснюється лише по вертикалі.

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

4. Функція видає неправильний результат, якщо ми додаємо чи видаляємо стовпець у своїх даних: формула посилається на конкретний номер стовпця, з якого ми вилучаємо дані.

5. Довжина шуканого значення не може перевищувати 255 символів.

 

Що робити, коли шукані значення знаходяться лівіше від стовпчика з критеріями? Розташувати їх у потрібному порядку може бути складно (або взагалі неможливо, якщо ви, наприклад, працюєте у чужому документі).

 

У цьому випадку як більш гнучка альтернатива VLOOKUP використовується зв’язка функції INDEX (ІНДЕКС) та MATCH (ПОИСКПОЗ) INDEX відраховує необхідну кількість комірок униз у діапазоні шуканих значень. MATCH визначає кількість комірок, що відраховуються, за стовпчиком критеріїв. Комбінація дозволяє:

 

• здійснювати пошук та повернення значення, яке знаходиться ліворуч від шуканого значення,

• обробляти горизонтально та/або вертикально структуровані дані,

• обробляти рядки/стовпці, якщо ви вставляєте або видаляєте деякі з набору даних,

• працювати з великою кількістю рядків та стовпчиків.

Більш детально тонкощам ВПР навчаємо на курсі Excel: бізнес-аналіз та прогнозування

 

Синтаксис INDEX та MATCH:

 

= INDEX або ІНДЕКС (масив, який ми повертаємо, MATCH або ПОИСКПОЗ (шукане значення, масив, який необхідно переглянути [тип зіставлення]), [номер стовпчика])

 

• масив, що потрібно повернути — стовпчик, з якого витягуємо дані

• шукане значення — що шукаємо; посилання на клітинку з шуканим значенням

• масив, який потрібно переглянути — діапазон, у якому шукаємо

• [тип зіставлення] (необов'язковий аргумент) — вказуємо 0 для точного збігу

• [номер стовпчика] (необов'язковий аргумент)

 

INDEX та MATCH також використовуються окремо

 

Функція INDEX: 

1.  Переглядає вказаний діапазон значень.

2.  Бере комірку з вказаним порядковим номером та показує значення із цієї комірки.

 

Синтаксис INDEX

 

= INDEX або ІНДЕКС (діапазон даних, номер рядка, [номер стовпчика])

 

INDEX корисна в наступних випадках:

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

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

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

 

Функція MATCH виконує зворотню операцію: 

1. Переглядає вказаний діапазон значень.

2. Шукає комірку, яка містить вказане значення, та показує порядковий номер цієї комірки.

 

Синтаксис MATCH:

 

= MATCH або ПОИСКПОЗ (шукане значення, діапазон таблиці, [тип збігу])

 

MATCH можна використовувати: 

1. Для пошуку значення у відсортованому діапазоні.

2. При пошуку значення у великому масиві.

3. Для пошуку ближнього значення:

 

Кожна з цих функцій Excel має своє місце. Просто в деяких випадках одна може бути кращою за іншу. Так, VLOOKUP використовується частіше. Її легше зрозуміти та опанувати. Для людини, яка добре володіє ВПР, але не знає, як використовувати INDEX та MATCH, має сенс використовувати саме ту функцію, яку вона знає. Але INDEX + MATCH забезпечує набагато більше гнучкості та швидкості. Найкраще ця комбінація підходить для створення моделей даних або роботи з великими  масивами складних даних.

 

P.S. Ознайомитися із нашими навчальними програмами та зареєєструватися на обраний аурс (Excel, Power BI, VBA, Excel Advanced, SQL) ви зможете за посиланням

 

Дізнайтеся більше про повну програму "EXCEL: бізнес-аналіз і прогнозування"

 

 

Дізнайтеся більше про повну програму "EXCEL ADVANCED: поглиблені інструменти"

 

 

Дізнайтеся більше про повну програму "Поглиблена аналітика в Power BI"

 

 

Дізнайтеся більше про повну програму "SQL для бізнес-користувачів"

 

 

Дізнайтеся більше про повну програму "EXCEL VBA (МАКРОСИ): АВТОМАТИЗАЦІЯ БІЗНЕС-ЗАДАЧ"