Функція XLOOKUP
Застосовується до
Скористайтеся функцією XLOOKUP, щоб знайти об'єктів у таблиці або діапазоні за рядком. Наприклад, знайдіть ціну автомобільної частини на номер деталі або знайдіть ім'я працівника на основі його ідентифікатора працівника. За допомогою XLOOKUP можна шукати пошуковий запит в одному стовпці та повертати результат із того самого рядка в іншому стовпці, незалежно від того, з якого боку ввімкнуто зворотний стовпець.
Примітка.: XLOOKUP недоступна в Excel 2016 і Excel 2019. Проте може виникнути ситуація використання книги в Excel 2016 або Excel 2019 із функцією XLOOKUP в ній, якщо її створив інший користувач за допомогою новішої версії Програми Excel.
Синтаксис
Функція XLOOKUP шукає діапазон або масив, а потім повертає елемент, який відповідає першому збігу, який він знаходить. Якщо збігів немає, XLOOKUP можете повернути найближчий (приблизний) збіг.
=XLOOKUP(lookup_value;lookup_array;return_array;[if_not_found];[match_mode];[search_mode])
|
Аргумент |
Опис |
|---|---|
|
lookup_value Необхідний* |
Значення для пошуку *Якщо його не вказано, XLOOKUP повертає пусті клітинки, знайдені в lookup_array. |
|
lookup_array Обов’язковий |
Масив або діапазон, які потрібно знайти |
|
return_array Обов’язковий |
Масив або діапазон, який потрібно повернути |
|
[if_not_found] Необов’язковий |
Якщо не знайдено припустимий збіг, поверніть введений текст [if_not_found]. Якщо не знайдено припустимий збіг і [if_not_found] відсутній, повертається #N/A . |
|
[match_mode] Необов’язковий |
Укажіть тип збігу: 0 - Точний збіг. Якщо не знайдено, поверніть #N/A. Цей параметр установлено за промовчанням. -1 - Точний збіг. Якщо нічого не знайдено, поверніть наступний менший елемент. 1 - Точний збіг. Якщо не знайдено, поверніть наступний більший елемент. 2 – збіг символу підстановки, де *, ? та ~ мають особливе значення |
|
[search_mode] Необов’язковий |
Укажіть режим пошуку для використання: 1. Виконайте пошук, починаючи з першого елемента. Цей параметр установлено за промовчанням. -1 . Виконайте зворотний пошук, починаючи з останнього елемента. 2. Виконайте двійковий пошук, який залежить від lookup_array сортування за зростанням . Якщо його невідсортовано, буде повернено недійсні результати. 2 – здійснити бінарний пошук, який покладається на те, що масив lookup_array посортовано за спаданням. Якщо його невідсортовано, буде повернено недійсні результати. |
Приклади
У прикладі 1 використовується XLOOKUP для пошуку назви країни в діапазоні, а потім повертається код країни телефонного зв'язку. Вона містить аргументи lookup_value (клітинка F2), lookup_array (діапазон B2:B11) і return_array (діапазон D2:D11). Він не містить аргумент match_mode, оскільки XLOOKUP за замовчуванням створює точний збіг.
Примітка.: XLOOKUP використовує масив підстановки та масив повернення, тоді як функція VLOOKUP використовує масив однієї таблиці з номером індексу стовпця. Еквівалентна формула VLOOKUP у цьому випадку матиме такий вигляд: =VLOOKUP(F2;B2:D11;3;FALSE)
———————————————————————————
Приклад 2 підбирає відомості про працівників на основі ідентифікаційного номера працівника. На відміну від функції VLOOKUP, XLOOKUP можете повернути масив із кількома елементами, тому одна формула може повертати як ім'я працівника, так і відділ із клітинок C5:D14.
———————————————————————————
Приклад 3 додає аргумент if_not_found до попереднього прикладу.
———————————————————————————
Приклад 4 шукає в стовпці C особистий дохід, введений у клітинці E2, і знаходить відповідну ставку податку у стовпці B. Задає аргумент if_not_found повертати 0 (нуль), якщо нічого не знайдено. Для аргументу match_mode встановлено значення 1, що означає, що функція шукатиме точний збіг, а якщо не вдається його знайти, вона повертає наступний більший елемент. Нарешті, аргумент search_mode має значення 1, що означає, що функція здійснюватиме пошук від першого елемента до останнього.
Примітка.: Стовпець lookup_array XARRAY розташовано праворуч від стовпця return_array , тоді як функція VLOOKUP може виглядати лише зліва направо.
———————————————————————————
Приклад 5 використовує вкладену функцію XLOOKUP для виконання вертикального та горизонтального збігів. Спочатку він шукає валовий прибуток у стовпці B, а потім шукає Qtr1 у верхньому рядку таблиці (діапазон C5:F5), а потім повертає значення на перетині двох. Це схоже на спільне використання функцій INDEX та MATCH.
Порада.: Ви також можете замінити функцію HLOOKUP за допомогою XLOOKUP.
Примітка.: Формула у клітинках D3:F3 така: =XLOOKUP(D2;$B 6:$B 17;XLOOKUP($C 3;$C 5:$G 5;$C 6:$G 17))).
———————————————————————————
Приклад 6 використовує функцію SUM і дві вкладені функції XLOOKUP, щоб підсумувати всі значення між двома діапазонами. У цьому випадку ми хочемо підсумувати значення винограду, бананів і включити груші, які знаходяться між ними.
Формула у клітинці E3 така: =SUM(XLOOKUP(B3;B6:B10;E6:E10):XLOOKUP(C3;B6:B10;E6:E10))
Принцип роботи XLOOKUP повертає діапазон, тому, коли вона обчислюється, формула закінчується так: =SUM($E$7:$E$9). Ви можете побачити, як це працює самостійно, вибравши клітинку з XLOOKUP формулою, подібною до цієї, а потім виберіть Пункт Формули > Аудит формули > Обчислити формулу, а потім натисніть кнопку Обчислити, щоб виконати обчислення.
Примітка.: Завдяки Mvp Microsoft Excel Біллу Джелену за те, що він запропонував цей приклад.
———————————————————————————
Додаткові відомості
Ви завжди можете поставити запитання експерту в спільноті Tech у розділі Excel чи отримати підтримку в спільнотах.
Потрібна додаткова довідка?
Потрібні додаткові параметри?
Ознайомтеся з перевагами передплати, перегляньте навчальні курси, дізнайтесь, як захистити свій пристрій тощо.
Спільноти допомагають ставити запитання й відповідати на них, надавати відгуки та дізнаватися думки висококваліфікованих експертів.
