Функція XLOOKUP
Applies To
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Вебпрограма Excel Excel 2024 Excel 2024 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2016 Excel для iPad Excel для iPhone Excel для планшетів Android Excel для телефонів AndroidСкористайтеся функцією 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 чи отримати підтримку в спільнотах.
Потрібна додаткова довідка?
Потрібні додаткові параметри?
Ознайомтеся з перевагами передплати, перегляньте навчальні курси, дізнайтесь, як захистити свій пристрій тощо.
Спільноти допомагають ставити запитання й відповідати на них, надавати відгуки та дізнаватися думки висококваліфікованих експертів.
