Продукти, послуги та функції операційної системи
можуть бути недоступними в цій країні.
Formulas and Functions
Змінюючи цей елемент керування, ви перезавантажуєте цю сторінку

XLOOKUP

Функція XLOOKUP шукає діапазон для вказаного значення і повертає значення з того ж рядка в іншому стовпці.

XLOOKUP(значення-пошуку; діапазон-пошуку; діапазон-повернення; якщо-не-знайдено; тип-збігу; тип-пошуку)

значення-пошуку: значення, яке шукається в діапазоні-пошуку. Аргумент значення-пошуку може містити будь-який тип значення або рядок REGEX.

діапазон-пошуку: клітинки для пошуку.

діапазон-повернення: клітинки для повернення.

якщо-не-знайдено: необов’язковий аргумент повідомлення, яке відображається, якщо збіг не знайдено.

тип-збігу: необов’язковий аргумент, який вказує, який тип збігу шукати.

точний або наступний найменший (-1): якщо відповідника немає, повертає помилку.

точний відповідник (0 або пропускається): якщо точного відповідника немає, повертає помилку.

точний або наступний найбільший (1): якщо відповідника немає, повертає помилку.

символ узагальнення (2): *, ?, і ~ мають конкретні значення. REGEX можна використовувати у XLOOKUP, лише якщо ви використовуєте символ узагальнення.

тип-пошуку: необов’язковий аргумент, який вказує порядок, в якому шукати в діапазоні.

Бінарний спадний (-2): бінарний пошук, для якого діапазон має бути посортований у спадному порядку, інакше повертається помилка.

Від останнього до першого (-1): пошук в діапазоні від останнього елемента до першого.

Від першого до останнього (1 або пропускається): пошук в діапазоні від першого елемента до останнього.

Бінарний зростаючий (2): бінарний пошук, для якого діапазон має бути посортований у зростаючому порядку, інакше повертається помилка.

Примітки

  • Якщо або діапазон-пошуку або діапазон-повернення є посиланням з охопленням (наприклад, "B"), колонтитули ігноруються автоматично.

  • Щоб повернути результати з будь-якого масиву, використовуйте INDEX з XLOOKUP.

Приклад

У наведеній нижче таблиці під назвою «Товари» зазначено товари та їх атрибути, як-от розмір і ціна.

A

B

C

D

E

1

Товар

Довжина (см)

Ширина (см)

Вага (кг)

Ціна

2

Товар 1

16

17

10

$82.00

3

Товар 2

16

20

18

$77.00

4

Товар 3

11

11

15

$88.00

5

Товар 4

15

16

20

$63.00

Пошук за допомогою XLOOKUP

З функцією XLOOKUP ви можете вставляти формулу в електронну таблицю, яка повертатиме будь-яке пов’язане значення, спершу зазначаючи назву товару, а потім — стовпець зі значенням, яке ви хочете повернути. Наприклад, якщо ви хотіли повернути ширину товару 1 у таблиці вище, ви могли скористатися наведеною нижче формулою, яка повертає 17 см.

Редактор формул, у якому показано формулу =XLOOKUP(Товари::$A2;Товари::A;Ширина).

У цій формулі використовуються такі аргументи:

  • значення-пошуку: Товари::$A2 — абсолютне посилання на клітинку в таблиці «Товари», яке містить значення «Товар 1».

  • діапазон-пошуку: Товари::A — стовпець для пошуку значення «Товар 1».

  • діапазон-повернення: Ширина — стовпець зі значенням, яке слід повернути, і яке пов’язане зі значенням «Товар 1».

  • тип-збігу: Пропущено. Якщо пропустити аргумент тип-збігу, функція XLOOKUP усталено шукатиме точний збіг.

Як налаштувати рядок якщо-не-знайдено

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

Редактор формул, у якому показано формулу =XLOOKUP(13;Довжина;Ширина;"Немає збігу";0).

У цій формулі аргумент якщо-не-знайдено використовується для виконання більш конкретного пошуку.

  • значення-пошуку: 13 — значення, яке слід шукати в діапазоні, указаному в аргументі діапазон-пошуку.

  • діапазон-пошуку: Довжина — стовпець, у якому слід здійснювати пошук.

  • діапазон-повернення: Ширина — стовпець, який містить значення, яке потрібно повернути, якщо знайдено збіг для аргументу «значення-пошуку».

  • якщо-не-знайдено: «Немає збігу» — рядок, який слід відобразити, якщо товар довжиною 13 см не знайдено.

  • тип-збігу: точний відповідник (0). Це значення шукатиме лише довжину, що дорівнює 13 см.

Пошук найближчого значення

XLOOKUP може також забезпечувати широкий пошук на основі певного значення й наближених до нього значень. Якщо змінити тип-збігу з наведеної вище формули, ви зможете повернути ширину, яка відповідатиме довжині 13 см або наступне менше значення. Наведена нижче формула повертає ширину, що дорівнює 11 см.

Редактор формул, у якому показано формулу =XLOOKUP(13;Довжина;Ширина;"Немає збігу";1;-1).

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

  • тип-збігу: точний або наступний найменший (-1). Це значення шукатиме або довжину 13 см, або, якщо це значення не буде знайдено, наступне менше значення в стовпці «Довжина».

Як змінити порядок пошуку

У деяких випадках може бути корисно змінити порядок пошуку в таблиці за допомогою функції XLOOKUP. Наприклад, у таблиці вище є два товари з довжиною 16 см, тому є два потенційні збіги, якщо шукати значення «16 см» у стовпці «Довжина» за допомогою аргументу значення-пошуку та діапазон-пошуку. Порядок пошуку можна вказати за допомогою наведеної нижче формули, яка повертає значення «20 см».

Редактор формул, у якому показано формулу =XLOOKUP(16;Довжина;Ширина;"Немає збігу";1;-1).

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

  • значення-пошуку: 16 — значення, яке слід шукати в діапазоні, указаному в аргументі діапазон-пошуку.

  • діапазон-пошуку: Довжина — стовпець, у якому слід здійснювати пошук.

  • діапазон-повернення: Ширина — стовпець, який містить значення, яке потрібно повернути, якщо знайдено збіг для аргументу «значення-пошуку».

  • якщо-не-знайдено: «Немає збігу» — рядок, який слід відобразити, якщо товар довжиною 16 см не знайдено.

  • тип-збігу: точно такий або наступний найбільший (1). Це значення шукатиме або довжину 16 см, або, якщо це значення не буде знайдено, наступне більше значення в стовпці «Довжина».

  • тип-пошуку: від останнього до першого (-1). Це значення шукатиме стовпець від останнього до першого значень.

Використання XLOOKUP з іншими функціями

XLOOKUP також можна використовувати з іншими функціями, як-от SUM. Наприклад, ви можете використати формулу, подібну до наведеної нижче, щоб повернути $247 — результат застосування функції SUM до цін на Товар 1, 2 і 3.

Редактор формул, у якому показано формулу =SUM(XLOOKUP(Товари::$A2;Товари::A;Ціна):XLOOKUP(Товари::$A4;Товари::A;Ціна)).

У цьому прикладі перша функція XLOOKUP шукає ціну Товару 1, а друга XLOOKUP — ціну Товару 3. Двокрапка (:) між функціями XLOOKUP указує, що SUM має повернути не лише загальну ціну Товару 1 і Товару 3, але й проміжні значення.

У наведеній нижче формулі XLOOKUP використовується разом із REGEX для повернення Товару 2, першого продукту, ширина якого починається на "2".

Редактор формул, у якому показано формулу =XLOOKUP(REGEX("^2.*"); Товари::C2:C5; Товари::A2:A5; FALSE;2).

У цьому прикладі "символ узагальнення (2)" використовується для типу-збігу для введення символів узагальнення у функцію REGEX.

Додаткові приклади

Задано наведену нижче таблицю:

A

B

C

1

Ім’я

Вік

Платня

2

Емма

35

71000

3

Юрій

27

81000

4

Зоя

42

86000

5

Софія

51

66000

6

Констянтин

28

52000

7

Том

49

62000

8

Aaron

63

89000

9

Марія

22

34000

10

Еліс

29

52000

11

Борис

35

52500

=XLOOKUP(49;B2:B11;C2:C11) повертає "62000", що є платнею першого працівника з віком 49.

=XLOOKUP(60000;C2:C11;B2:B11;"Немає") повертає "Немає", оскільки немає працівника з платнею 60000.

=XLOOKUP(REGEX("^З.*"); A2:A11; B2:B11; FALSE, 2) повертає “42”, вік «Зої», першого працівника з діапазону, чиє ім’я починається на “З”.

=INDEX(XLOOKUP(A2;A2:A11;B2:C11);2) повертає 71000, друге значення в масиві, яке повертає функція XLOOKUP.

Дивіться такожXMATCH
Morty Proxy This is a proxified and sanitized view of the page, visit original site.
Чи була інформація корисна?
Максимальна кількість символів: 250
Максимальна кількість символів: 250.
Дякуємо вам за відгук.
Morty Proxy This is a proxified and sanitized view of the page, visit original site.