Як користуватися функцією Excel XLOOKUP -

Резюме

Функція Excel XLOOKUP - це сучасна та гнучка заміна старих функцій, таких як VLOOKUP, HLOOKUP та LOOKUP. XLOOKUP підтримує приблизну та точну відповідність, символи підстановки (*?) Для часткових збігів та пошук у вертикальному або горизонтальному діапазонах.

Призначення

Значення пошуку в діапазоні або масиві

Повернене значення

Відповідні значення (значення) із масиву, що повертається

Синтаксис

= XLOOKUP (пошук, масив пошуку, масив повернення, (не знайдений), (режим збігу), (режим_пошуку))

Аргументи

  • lookup - значення пошуку.
  • lookup_array - масив або діапазон для пошуку.
  • return_array - масив або діапазон для повернення.
  • not_found - (необов’язково) Значення, яке потрібно повернути, якщо збіг не знайдено.
  • match_mode - (необов’язково) 0 = точний збіг (за замовчуванням), -1 = точний збіг або наступний найменший, 1 = точний збіг або наступний більший, 2 = підстановочний знак.
  • search_mode - (необов’язково) 1 = пошук з першого (за замовчуванням), -1 = пошук з останнього, 2 = двійковий пошук за зростанням, -2 = двійковий пошук за спаданням.

Версія

Excel 365

Примітки щодо використання

XLOOKUP - це сучасна заміна функції VLOOKUP. Це гнучка та універсальна функція, яка може бути використана в самих різних ситуаціях.

XLOOKUP може знаходити значення у вертикальному або горизонтальному діапазонах, може виконувати приблизні та точні збіги, а також підтримує символи підстановки (*?) Для часткових збігів. Крім того, XLOOKUP може здійснювати пошук даних, починаючи з першого значення або останнього значення (див. Тип відповідності та деталі режиму пошуку нижче). У порівнянні зі старими функціями, такими як VLOOKUP, HLOOKUP та LOOKUP, XLOOKUP пропонує кілька ключових переваг.

Повідомлення не знайдено

Коли XLOOKUP не може знайти збіг, він повертає помилку # N / A, як і інші функції збігу в Excel. На відміну від інших функцій відповідності, XLOOKUP підтримує необов’язковий аргумент з назвою not_found, який може бути використаний для заміни помилки # N / A, коли вона з’являється в іншому випадку. Типовими значеннями для not_found можуть бути "Not found", "No match", "No result" тощо. При введенні значення not_found, додайте текст у подвійні лапки ("").

Примітка: Будьте обережні, якщо ви вводите порожній рядок ("") для not_found. Якщо збігу не знайдено, XLOOKUP нічого не відображатиме замість # N / A. Якщо ви хочете побачити помилку # N / A, коли збіг не знайдено, повністю пропустіть аргумент.

Тип відповідності

За замовчуванням XLOOKUP виконає точну відповідність. Поведінка збігів контролюється необов’язковим аргументом з назвою match_type, який має такі параметри:

Тип відповідності Поведінка
0 (за замовчуванням) Точний збіг. Поверне # N / A, якщо немає збігів.
-1 Точна відповідність або наступний менший предмет.
1 Точна відповідність або наступний більший предмет.
2 Збіг підстановок (*,?, ~)

Режим пошуку

За замовчуванням XLOOKUP почне збігатися з першого значення даних. Поведінка пошуку управляється необов’язковим аргументом, який називається search_mode , який надає такі опції:

Режим пошуку Поведінка
1 (за замовчуванням) Шукати з першого значення
-1 Пошук за останнім значенням (зворотне)
2 Значення двійкового пошуку, відсортовані за зростанням
-2 Значення двійкового пошуку, відсортовані за спаданням

Двійковий пошук дуже швидкий, але дані потрібно сортувати за необхідністю. Якщо дані не сортуються належним чином, двійковий пошук може повернути невірні результати, які виглядають цілком нормально.

Приклад №1 - основна точна відповідність

За замовчуванням XLOOKUP виконає точну відповідність. У наведеному нижче прикладі XLOOKUP використовується для отримання продажів на основі точної відповідності фільму. Формула в H5 така:

=XLOOKUP(H4,B5:B9,E5:E9)

Детальніше пояснення тут.

Приклад №2 - основний приблизний збіг

Щоб увімкнути приблизний збіг, вкажіть значення для аргументу "match_mode". У наведеному нижче прикладі XLOOKUP використовується для обчислення знижки на основі кількості, яка вимагає приблизної відповідності. Формула у F5 містить -1 для match_mode, щоб увімкнути приблизний збіг із поведінкою "точне збіг або наступне найменше":

=XLOOKUP(E5,B5:B9,C5:C9,,-1)

Детальніше пояснення тут.

Приклад №3 - множинні значення

XLOOKUP може повертати більше одного значення одночасно за один і той же збіг. У наведеному нижче прикладі показано, як XLOOKUP можна налаштувати на повернення трьох значень відповідності за допомогою однієї формули. Формула в C5 така:

=XLOOKUP(B5,B8:B15,C8:E15)

Зверніть увагу, що масив повернення (C8: E15) включає 3 стовпці: Перший, Останній, Відділ. Всі три значення повертаються і потрапляють у діапазон C5: E5.

Приклад №4 - двосторонній пошук

XLOOKUP можна використовувати для двостороннього пошуку, вклавши один XLOOKUP всередину іншого. У прикладі нижче "внутрішній" XLOOKUP отримує цілий рядок (усі значення для Glass), який передається "зовнішньому" XLOOKUP як масив повернення. Зовнішній XLOOKUP знаходить відповідну групу (B) і повертає відповідне значення (17.25) як кінцевий результат.

=XLOOKUP(I6,C4:F4,XLOOKUP(I5,B5:B9,C5:F9))

Детальніше тут.

Приклад №5 - повідомлення не знайдено

Як і інші функції пошуку, якщо XLOOKUP не знаходить значення, він повертає помилку # N / A. Щоб відобразити власне повідомлення замість # N / A, введіть значення для необов’язкового аргументу "не знайдено", укладеного у подвійні лапки (""). Наприклад, щоб відобразити "Не знайдено", коли не знайдено відповідного фільму, на основі робочого аркуша нижче, використовуйте:

=XLOOKUP(H4,B5:B9,E5:E9,"Not found")

Ви можете налаштувати це повідомлення як завгодно: "Немає збігів", "Фільм не знайдено" тощо.

Приклад №6 - складні критерії

Завдяки можливості обробляти масиви, XLOOKUP можна використовувати зі складними критеріями. У наведеному нижче прикладі XLOOKUP відповідає першому запису, де: рахунок починається з "х", а регіон - "схід", а місяць - не квітень:

=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)

Деталі: (1) простий приклад, (2) більш складний приклад.

Переваги XLOOKUP

XLOOKUP пропонує кілька важливих переваг, особливо в порівнянні з VLOOKUP:

  • XLOOKUP може шукати дані праворуч або ліворуч від значень пошуку
  • XLOOKUP може повернути кілька результатів (приклад №3 вище)
  • За замовчуванням XLOOKUP має точний збіг (за замовчуванням VLOOKUP є приблизним)
  • XLOOKUP може працювати з вертикальними та горизонтальними даними
  • XLOOKUP може виконувати зворотний пошук (від останнього до першого)
  • XLOOKUP може повертати цілі рядки або стовпці, а не лише одне значення
  • XLOOKUP може власноруч працювати з масивами, застосовуючи складні критерії

Примітки

  1. XLOOKUP може працювати як з вертикальними, так і з горизонтальними масивами.
  2. XLOOKUP поверне # N / A, якщо значення пошуку не знайдено.
  3. Поісковий_массів повинен мати розмір , сумісний з return_array аргументу, в іншому випадку XLOOKUP поверне #VALUE!
  4. Якщо XLOOKUP використовується між книгами, обидві книги повинні бути відкритими, інакше XLOOKUP поверне #REF !.
  5. Як і функція INDEX, XLOOKUP повертає посилання як результат.

Пов’язані відео

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

Цікаві статті...