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

XLOOKUP

Функція XLOOKUP

Використовуйте функцію Xlookup , щоб знайти елементи в таблиці або діапазоні за рядками. Наприклад, знайдіть вартість автомобільної частини за номером частини або знайдіть ім'я працівника на основі ІДЕНТИФІКАТОРА працівника. За допомогою XLOOKUP можна шукати в одному стовпці для пошукового терміна, а також повертати результат з того самого рядка в іншому стовпці, незалежно від того, з якого боку буде повернуто стовпець.

Ваш браузер не підтримує відео.

Синтаксис

Функція XLOOKUP шукає діапазон або масив, а потім повертає елемент, який відповідає першому збігу, що знаходить. Якщо немає збігів, ФУНКЦІЯ XLOOKUP може повернути найближчий (приблизний) збіг. 

= XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found]; [match_mode]; [search_mode]) 

Аргумент

Опис

значення_пошуку

Необхідні

Значення, яке потрібно знайти

* Якщо аргумент пропущено, функція XLOOKUP повертає пусті клітинки, які він знаходить в 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 використовує масив підстановки та повернуте масив, а ФУНКЦІЯ Lookup використовує один масив таблиці, а потім номер індексу стовпця. Еквівалентна формула 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 , а функція LOOKUP може шукати лише зліва направо.

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

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

Порада.: Ви також можете використовувати XLOOKUP для заміни функції HLOOKUP .

Зображення функції XLOOKUP, що використовується, щоб повертати горизонтальні дані з таблиці, вклавши 2 Xlookup. Формула: = 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 з СУМОЮ, щоб підсумувати діапазон значень, які припадають між двома виділеннями

Формула у клітинках 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 (за категоріями)

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

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

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

×