Функція XLOOKUP (ПРОСМОТРХ)
- Назва в Excel
XLOOKUP- У російській версії
ПРОСМОТРХ- Версії Excel
-
2003200720102013365 - Категорія
- Посилання та масиви
Функція XLOOKUP (у російській версії Excel — ПРОСМОТРХ) використовує можливості динамічних масивів і покликана замінити одночасно функції VLOOKUP і HLOOKUP. У жовтні 2019 року, до офіційного анонсу, Microsoft оновила синтаксис функції.
Старий синтаксис — XLOOKUP(искомое_значение; просматриваемый_массив; возвращаемый_массив; [режим_сопоставления]; [режим_поиска]) Новий синтаксис — XLOOKUP(искомое_значение; просматриваемый_массив; возвращаемый_массив; [если_ничего_не_найдено];[режим_сопоставления]; [режим_поиска])
Назви функцій подано англійською, як в українському Excel. Скриншоти й книги-приклади — з російської версії.
Опис функції XLOOKUP
Функція XLOOKUP була анонсована Microsoft практично через рік після представлення динамічних масивів які Microsoft анонсувала 24 вересня 2018 року. Ця функція, як і можливість використання динамічних масивів, не повинна бути доступна користувачам, які придбали Office 2019, і тим більше — більш раннім версіям.
Новими можливостями зможуть скористатися користувачі з підпискою Office 365, а в майбутньому — користувачі Office 2021 (наступної версії після Office 2019), якщо до того часу Microsoft не залишить лише варіант із підпискою.
Функція своїм функціоналом повністю заміщує можливості VLOOKUP, HLOOKUP, а також зв'язку ІНДЕКС + MATCH, яка використовувалася як заміна VLOOKUP, коли потрібно було обійти деякі її обмеження.
Синтаксис XLOOKUP
=XLOOKUP(шукане_значення; масив_для_пошуку; масив_для_повернення; [якщо_нічого_не_знайдено];[режим_зіставлення]; [режим_пошуку])
Аргументи
шукане_значення
Обов'язковий. Значення, яке потрібно знайти
масив_для_пошуку
Обов'язковий. Масив або діапазон для пошуку
масив_для_повернення
Обов'язковий. Масив або діапазон, який потрібно повернути
якщо_нічого_не_знайдено
Необов'язковий. Значення, яке буде повернено функцією у випадку, якщо нічого не знайдено. Параметр необов'язковий, і за його відсутності функція поверне помилку, якщо нічого не буде знайдено — цю помилку, як і раніше, можна обробити функцією IFERROR.
режим_зіставлення
Необов'язковий. Використовуються такі варіанти для аргумента:
- 0 — точне співпадіння. Якщо нічого не знайдено, буде повернено помилку #N/A. Цей параметр використовується за замовчуванням.
- -1 — точне співпадіння. Якщо нічого не знайдено, буде повернено наступний менший елемент.
- 1 — точне співпадіння. Якщо нічого не знайдено, буде повернено наступний більший елемент.
- 2 — використання підстановочних знаків при пошуку.
режим_пошуку
Необов'язковий. Використовуються наступні режими пошуку:
- 1 — стандартний пошук з початку списку. Цей параметр використовується за замовчуванням.
- -1 — зворотний пошук з кінця списку.
- 2 — бінарний пошук (пошук по числах). Список повинен бути відсортований за зростанням.
- -2 — бінарний пошук (пошук по числах). Список повинен бути відсортований за спаданням.
Зауваження
- Функція VLOOKUP за замовчуванням повертала приблизне співпадіння, якщо користувач не передав необов'язковий параметр «інтервальний_просмотр». За заявою Microsoft, непередбачувані результати вводили в оману непідготовлених користувачів, адже для приблизного співпадіння масив має бути відсортований. Нова функція за замовчуванням повертає точне співпадіння — це особливість її роботи і це слід враховувати.
- Якщо в полі «режим_зіставлення» ввести значення 2, то в полі «шукане_значення» можна буде використовувати підстановочні знаки, де "?" означає заміну одного символу, а "*" — будь-яку кількість символів. Насправді, за замовчуванням передавати підстановочні знаки у VLOOKUP можна, але в новій функції для цього є спеціальний перемикач. Ймовірно, це зроблено з міркувань підвищення продуктивності.
- Розмірність масивів, що передаються в параметри «масив_для_пошуку» і «масив_для_повернення», має бути однаковою, інакше функція поверне помилку #VALUE!. При використанні VLOOKUP (HLOOKUP) пошук і повернення значення фактично здійснювались із одного масиву, тому неправильно вказати розмірність було неможливо; тут слід звернути на це увагу.
- XLOOKUP, як і VLOOKUP, шукає перше значення в списку; якщо потрібно знайти всі співпадіння, використовуйте функцію FILTER
Основні переваги перед VLOOKUP (і HLOOKUP)
Оскільки ця функція представлена як вдосконалена заміна такої популярної функції, як VLOOKUP (та HLOOKUP), доречно розглянути, чим нова функція вигідно відрізняється від знайомих користувачам аналогів.
Одним із поширених недоліків VLOOKUP є пошук значення виключно в першому стовпці діапазону. Рішення у вигляді зміни джерела даних так, щоб шуканий стовпець опинився першим, розглядати неправильно.

Є рішення у вигляді використання зв'язки функцій INDEX і MATCH. Єдиним недоліком такого підходу (якщо не зважати на питання продуктивності) є складність для непідготовленого користувача і відносна громіздкість рішення. Громіздкість пізніше може негативно вплинути на читабельність формул.
У функціях VLOOKUP і HLOOKUP стовпці, в яких здійснюється пошук і з яких повертається результат, мають входити до одного масиву. Це негативно позначається на продуктивності, адже в формулу завжди потрібно передавати багато даних, які фактично не використовуються.

Справедливості заради, варто зазначити, що Microsoft буквально за місяць до анонсу нової функції покращила продуктивність алгоритмів VLOOKUP (HLOOKUP). Але це не змінює факту, що завжди доводиться передавати «непотрібний» пласт інформації, який, окрім того, може ускладнити читабельність формул.
У VLOOKUP повертаємий масив передається числом, тобто формула читається як «знайти значення «А» в першому стовпці і повернути відповідне значення зі стовпця «n», де n — число, наприклад, 3». Для великих таблиць незручно користувачу вираховувати номер стовпця, звідки потрібно повернути результат, наприклад, якщо замість 3 буде 23 або 33. Ще більший дискомфорт виникає, якщо до вихідного діапазону додається або видаляється стовпець. Якщо новий/видалений стовпець знаходиться між першим і шуканим стовпцем, доведеться змінювати формулу.
У новій функції XLOOKUP діапазон, з якого повертається результат, вказується як діапазон, і додавання/видалення стовпців ніяк не вплине на результати обчислення формули.
Використовуючи VLOOKUP, ви можете знайти, наприклад, працівника і повернути його заробітну плату; якщо потрібно також повернути підрозділ, у якому він працює, доведеться написати ще одну формулу. За допомогою XLOOKUP можна повернути одразу двовимірний масив.

До того ж у версіях Excel з підтримкою динамічних масивів у VLOOKUP також можна передати не просто номер стовпця, а масив чисел, і функція поверне кілька значень. Але це з'явилося з підтримкою динамічних масивів.
Оскільки XLOOKUP покликана замінити одразу дві функції, то очевидно, що вона має працювати з масивами будь-якої орієнтації.

Нагадаємо, що перша буква в функціях VLOOKUP і HLOOKUP і позначала, як має бути організовано масив: «В» — вертикально, «Г» — горизонтально.
У випадку використання VLOOKUP (HLOOKUP) пошук завжди здійснювався з початку списку, і обійти це обмеження без переформатування самого вихідного діапазону було неможливо.
Тепер же пошук можна виконати з «кінця списку», наприклад, якщо список відсортований за показниками продажів за днями, можна одразу знайти й найкращий, й найгірший результат для певного працівника.
У разі використання старих функцій був варіант неточного пошуку: якщо діапазон був відсортований і точного значення не знаходилося, поверталося максимально можливе значення, що менше за шукане. У випадку використання XLOOKUP можна знайти як значення, менше за шукане, так і більше, якщо точного співпадіння не буде.

Більше того, попереднє сортування в разі неточного співпадіння не потрібне — функція правильно спрацює в будь-якому випадку.