Зміст
×
Формули та функції
Формули та функції

XLOOKUP

Скористайтеся функцією XLOOKUP , щоб знайти об'єктів у таблиці або діапазоні за рядком. Наприклад, знайдіть ціну автомобільної частини на номер деталі або знайдіть ім'я працівника на основі ідентифікатора працівника. Функція XLOOKUP дає змогу шукати пошуковий запит в одному стовпці в одному стовпці та повертати результат із того самого рядка в іншому стовпці, незалежно від того, з якого боку ввімкнуто зворотний стовпець.

Примітка.: Функція XLOOKUP недоступна в Excel 2016 і Excel 2019, однак може виникнути ситуація використання книги в Excel 2016 або Excel 2019 р. за допомогою функції XLOOKUP, створеної іншим користувачем, використовуючи новішу версію Excel.

Ваш браузер не підтримує відео. Інсталюйте Microsoft Silverlight, Adobe Flash Player або Internet Explorer 9.

Синтаксис

Функція 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, яка використовується для повернення імені працівника та відділу на основі ідентифікатора працівника. Формула = XLOOKUP(B2;B5:B14;C5:C14).

Примітка.: Функція XLOOKUP використовує масив підстановки та масив, що повертається, тоді як функція VLOOKUP використовує масив однієї таблиці та номер індексу стовпця. Еквівалентна формула VLOOKUP у цьому випадку матиме такий вигляд: =VLOOKUP(F2;B2:D11;3;FALSE)

———————————————————————————

Приклад 2    підбирає відомості про працівників на основі ідентифікаційного номера працівника. На відміну від функції VLOOKUP, функція XLOOKUP може повертати масив із кількома елементами, тому одна формула може повертати як ім'я працівника, так і відділ із клітинок C5:D14.

Приклад функції XLOOKUP, яка використовується для повернення імені працівника та відділу на основі IDt працівника. Формула: =XLOOKUP(B2;B5:B14;C5:D14;0;1)

———————————————————————————

Приклад 3    додає аргумент if_not_found до попереднього прикладу.

Приклад функції XLOOKUP, яка використовується для повернення імені працівника та відділу на основі ідентифікатора працівника з аргументом if_not_found. Формула = XLOOKUP(B2;B5:B14;C5:D14;0;1;"Працівник не знайдено")

———————————————————————————

Приклад 4    шукає в стовпці C особистий дохід, введений у клітинці E2, і знаходить відповідну ставку податку у стовпці B. Задає аргумент if_not_found повертати 0 (нуль), якщо нічого не знайдено. Для аргументу match_mode встановлено значення 1, що означає, що функція шукатиме точний збіг, а якщо не вдається його знайти, вона повертає наступний більший елемент. Нарешті, аргумент search_mode має значення 1, що означає, що функція здійснюватиме пошук від першого елемента до останнього.

Зображення функції XLOOKUP, яка використовується для повернення податкової ставки на основі максимального доходу. Це приблизний збіг. Формула: =XLOOKUP(E2;C2:C7;B2:B7;1;1)

Примітка.: Стовпець lookup_array XARRAY розташовано праворуч від стовпця return_array , тоді як функція VLOOKUP може виглядати лише зліва направо.

———————————————————————————

Приклад 5    використовує вкладену функцію XLOOKUP для виконання вертикального та горизонтального збігів. Спочатку він шукає валовий прибуток у стовпці B, а потім шукає Qtr1 у верхньому рядку таблиці (діапазон C5:F5), а потім повертає значення на перетині двох. Це схоже на спільне використання функцій INDEX і MATCH .

Порада.: Функцію HLOOKUP також можна замінити за допомогою функції XLOOKUP .

Зображення функції XLOOKUP, яка використовується для повернення горизонтальних даних із таблиці шляхом вкладення 2 XLOOKUPs. Формула: =XLOOKUP(D2;$B 6:$B 17;XLOOKUP($C 3;$C 5:$G 5;$C 6:$G 17))

Примітка.: Формула у клітинках D3:F3 така: =XLOOKUP(D2;$B 6:$B 17;XLOOKUP($C 3;$C 5:$G 5;$C 6:$G 17)))..

———————————————————————————

Приклад 6    використовує функцію SUM і дві вкладені функції XLOOKUP, щоб підсумувати всі значення між двома діапазонами. У цьому випадку ми хочемо підсумувати значення винограду, бананів і включити груші, які знаходяться між ними.

Використання функції XLOOKUP із функцією SUM для підсумовування діапазону значень, які перебувають між двома виділеними фрагментами

Формула у клітинці E3 така: =SUM(XLOOKUP(B3;B6:B10;E6:E10):XLOOKUP(C3;B6:B10;E6:E10))

Принцип роботи Функція XLOOKUP повертає діапазон, тому, коли вона обчислюється, формула закінчується так: =SUM($E$7:$E$9). Ви можете побачити, як це працює самостійно, вибравши клітинку з формулою XLOOKUP, подібною до цієї, а потім виберіть Пункт Формули > Аудит формули > Обчислити формулу, а потім натисніть кнопку Обчислити, щоб виконати обчислення.

Примітка.: Дякуємо Microsoft Excel MVP Біллу Джелену за те, що він запропонував цей приклад.

———————————————————————————

Потрібна додаткова довідка?

Ви завжди можете поставити запитання експерту в спільноті Tech у розділі Excel чи отримати підтримку в спільноті Answers.

Додаткові відомості

Функція XMATCH

Функції Excel (за алфавітом)

Функції Excel (за категоріями)

Потрібна додаткова довідка?

Отримуйте нові функції раніше за інших
Приєднатися до оцінювачів Microsoft Office

Ця інформація корисна?

Наскільки ви задоволені якістю мови?
Що вплинуло на ваші враження?

Дякуємо за відгук!

×