Нова функція XLOOKUP в Excel стає гідною заміною VLOOKUP, яка є однією з найбільш затребуваних функцій. Ця інноваційна функція не лише усуває деякі недоліки VLOOKUP, а й пропонує розширений набір можливостей. Розгляньмо детальніше, що вона собою являє.
Що таке XLOOKUP?
XLOOKUP – це вдосконалений інструмент, що вирішує ряд проблем, властивих VLOOKUP. Крім того, вона успішно замінює HLOOKUP, надаючи більшу гнучкість та функціональність. XLOOKUP здатна здійснювати пошук ліворуч від ключового стовпця, за замовчуванням виконує точний пошук і дозволяє вказувати діапазони клітинок, а не номери стовпців. На відміну від VLOOKUP, вона значно простіша у використанні та універсальніша. Розберемо, як це все працює на практиці.
Наразі XLOOKUP доступна лише для користувачів програми Insiders. Кожен може приєднатися до програми інсайдерів, щоб першим отримувати доступ до нових можливостей Excel. Проте, Microsoft планує поступово впровадити її для всіх користувачів Office 365.
Як користуватися XLOOKUP
Розглянемо конкретний приклад використання XLOOKUP. Припустимо, у нас є таблиця з даними, де ми хочемо знайти відділ (стовпець F) для кожного ідентифікатора (стовпець A).
Це типове завдання на точний пошук. Для XLOOKUP потрібно лише три ключових елементи.
Хоча XLOOKUP може використовувати шість аргументів, лише перші три є необхідними для точного пошуку. Розгляньмо їх:
Lookup_value: значення, яке потрібно знайти.
Lookup_array: діапазон для пошуку.
Return_array: діапазон зі значеннями, які потрібно повернути.
Для цього прикладу формула матиме вигляд: =XLOOKUP(A2;$E$2:$E$8;$F$2:$F$8)
Тепер давайте порівняємо переваги XLOOKUP з VLOOKUP.
Відмова від номерів стовпців
Однією з проблем VLOOKUP було використання номера стовпця як третього аргументу для визначення інформації, що повертається. XLOOKUP дозволяє вибирати діапазон для повернення (у цьому випадку, стовпець F), усуваючи цю проблему.
Також важливо зазначити, що XLOOKUP, на відміну від VLOOKUP, може здійснювати пошук ліворуч від заданої клітинки. Про це докладніше нижче.
Ще одна перевага – відсутність проблем зі зламаними формулами при додаванні нових стовпців. Діапазон повернення налаштується автоматично.
Точна відповідність за замовчуванням
При вивченні VLOOKUP часто виникало питання, чому потрібно окремо вказувати точну відповідність.
XLOOKUP вирішує цю проблему, використовуючи точну відповідність як налаштування за замовчуванням, що відповідає найпоширенішому сценарію застосування формул пошуку. Це зменшує кількість необхідних аргументів та мінімізує помилки користувачів.
Підсумовуючи, XLOOKUP є більш дружньою до користувача, інтуїтивною та надійною, ніж VLOOKUP.
XLOOKUP може шукати зліва
Можливість вибирати діапазон пошуку робить XLOOKUP значно універсальнішою. Порядок стовпців у таблиці більше не має значення.
VLOOKUP був обмежений пошуком тільки в крайньому лівому стовпці таблиці, а потім повертав дані з заданої кількості стовпців праворуч.
У наведеному прикладі, ми шукаємо ідентифікатор (стовпець E) і хочемо отримати ім’я особи (стовпець D).
Це можна зробити за допомогою формули: =XLOOKUP(A2;$E$2:$E$8;$D$2:$D$8)
Обробка помилок
Користувачі функцій пошуку добре знають повідомлення про помилку #N/A, яке з’являється, коли VLOOKUP або MATCH не можуть знайти відповідне значення. Часто це має логічну причину.
Тому користувачі часто шукають способи приховати цю помилку, оскільки вона не завжди корисна. Існують різні методи, як це зробити.
XLOOKUP має вбудований аргумент “if not found” для обробки таких помилок. Подивимося на приклад з помилково введеним ідентифікатором.
У наступній формулі, замість помилки, буде відображено текст “Неправильний ідентифікатор”: =XLOOKUP(A2;$E$2:$E$8;$D$2:$D$8,”Неправильний ідентифікатор”)
Пошук діапазону за допомогою XLOOKUP
Хоча точний пошук є найпоширенішим, пошук значень у діапазонах також є важливим завданням. Розглянемо приклад, де ми хочемо визначити знижку залежно від суми витрат.
Цього разу нам потрібно не конкретне значення, а діапазон, до якого потрапляє значення в стовпці B. Це дозволить визначити відповідну знижку.
XLOOKUP має необов’язковий п’ятий аргумент “match_mode”, який визначає режим пошуку (нагадаємо, що точний пошук є значенням за замовчуванням).
Видно, що XLOOKUP має більше можливостей для наближених збігів порівняно з VLOOKUP.
Можна знайти найближчий збіг, менший (-1) або більший (1) за значення, що шукається. Також є можливість використовувати символи підстановки (2), наприклад, ? або *. Ця опція не ввімкнена за замовчуванням, як у VLOOKUP.
У цьому прикладі формула повертає найближче значення, менше за шукане, якщо точного збігу не знайдено: =XLOOKUP(B2;$E$3:$E$7;$F$3:$F$7;,-1)
У клітинці C7 є помилка #N/A, оскільки не вказано аргумент “if not found”. Вона повинна повернути 0%, оскільки витрати 64 не потрапляють у жоден діапазон знижок.
Ще одна перевага XLOOKUP – відсутність вимоги до сортування діапазону пошуку за зростанням, як це потрібно у VLOOKUP.
Додайте новий рядок внизу таблиці пошуку, а потім розширте діапазон, перетягуючи кути.
Формула автоматично виправляє помилку. Наявність “0” внизу діапазону не є проблемою.
Хоча особисто я б все одно відсортував таблицю за стовпцем пошуку, важливо, що формула не зламалася.
XLOOKUP замінює HLOOKUP
XLOOKUP також успішно замінює функцію HLOOKUP, об’єднуючи їх в одну функцію. Це дуже зручно!
HLOOKUP використовується для горизонтального пошуку вздовж рядків.
Вона менш відома, ніж VLOOKUP, але корисна в ситуаціях, коли заголовки розташовані у стовпці A, а дані – у рядках 4 та 5.
XLOOKUP може шукати як вертикально (вниз по стовпцях), так і горизонтально (вздовж рядків). Дві різні функції тепер не потрібні.
У цьому прикладі формула повертає вартість продажу, що відповідає назві у клітинці A2. Вона шукає ім’я в рядку 4 і повертає значення з рядка 5: =XLOOKUP(A2;B4:E4;B5:E5)
Пошук знизу вгору з XLOOKUP
Зазвичай потрібно знайти перше входження значення у списку. XLOOKUP має шостий аргумент “search_mode”, що дозволяє перемкнути пошук на зворотний напрямок, тобто починати знизу.
У наведеному прикладі ми хочемо знайти рівень запасів для кожного продукту в стовпці A.
Таблиця пошуку містить дані про перевірку запасів у хронологічному порядку. Для кожного продукту є кілька записів. Нам потрібно отримати рівень запасів з останньої перевірки.
Шостий аргумент XLOOKUP має чотири варіанти. Нас цікавить опція “Шукати від останнього до першого”.
Повна формула виглядає так: =XLOOKUP(A2;$E$2:$E$9;$F$2:$F$9;;;-1)
Четвертий та п’ятий аргументи було проігноровано, оскільки нам потрібен точний пошук.
Підсумок
XLOOKUP є довгоочікуваною заміною як для VLOOKUP, так і HLOOKUP.
У цій статті ми розглянули різні приклади, які підкреслюють переваги XLOOKUP, зокрема, можливість використовувати її в таблицях, на аркушах та у робочих книгах. Приклади були навмисно спрощені для кращого розуміння.
Завдяки динамічним масивам, що впроваджуються в Excel, XLOOKUP незабаром зможе повертати діапазони значень. Це перспективна можливість для подальшого дослідження.
Дні VLOOKUP добігають кінця. XLOOKUP вже тут і незабаром стане стандартом для формул пошуку.