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

Резюме

Функція Excel XMATCH виконує пошук і повертає позицію у вертикальному або горизонтальному діапазонах. Це більш надійний та гнучкий наступник функції MATCH. XMATCH підтримує приблизну та точну відповідність, зворотний пошук та символи підстановки (*?) Для часткових збігів.

Призначення

Отримайте позицію елемента у списку або таблиці

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

Числове положення в масиві підстановки

Синтаксис

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

Аргументи

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

Версія

Excel 365

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

Функція Excel XMATCH виконує пошук і повертає позицію. XMATCH може виконувати пошук у вертикальному або горизонтальному діапазонах і призначений для більш гнучкого та потужного наступника функції MATCH. XMATCH підтримує приблизні та точні збіги, а також символи підстановки (*?) Для часткових збігів. Як і функція XLOOKUP, XMATCH може шукати дані, починаючи з першого значення або останнього значення (тобто зворотний пошук). Нарешті, XMATCH може виконувати двійковий пошук, який спеціально оптимізований для швидкості. Детальніше див. Тип відповідності та режим пошуку нижче.

XMATCH проти MATCH

У деяких випадках XMATCH може бути заміною функції MATCH. Наприклад, для точних збігів синтаксис ідентичний:

=MATCH(lookup, range, 0) // exact match =XMATCH(lookup, range, 0) // exact match

Однак для приблизних збігів поведінка відрізняється, коли для типу збігу встановлено значення 1:

=MATCH(lookup, range, 1) // exact match or next smallest =XMATCH(lookup, range, 1) // exact match or next *largest*

Крім того, XMATCH дозволяє -1 для типу збігу, який недоступний у MATCH:

=XMATCH(lookup, range, -1) // exact match or next smallest

Примітка: функція MATCH взагалі не пропонує аргумент режиму пошуку.

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

Третій аргумент XMATCH є тіп_сопоставленіем . Це необов’язковий аргумент, який керує поведінкою збігів наступним чином:

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

Режим пошуку

Четвертим аргументом для XMATCH є search_mode . Це необов’язковий аргумент, який керує поведінкою пошуку наступним чином:

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

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

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

У наведеному прикладі XMATCH використовується для отримання положення "Марса" у списку планет в діапазоні B6: B14. Формула в G6 така:

=XMATCH(G5,B6:B14) // returns 4

Зверніть увагу, що за замовчуванням XMATCH відповідає точному збігу. Якби "Марс" G5 був написаний з помилкою "Marz", XMATCH повернув би # N / A.

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

Наведений нижче приклад ілюструє поведінку режиму збігу зі значенням пошуку 3,1 в E4 та значеннями пошуку в B5: B11.

E6=XMATCH(E4,B5:B11) // returns #N/A E7=XMATCH(E4,B5:B11,-1) // returns 3 E8=XMATCH(E4,B5:B11,1) // returns 4

Приклад №3 - INDEX та XMATCH

XMATCH можна використовувати так само, як MATCH з функцією INDEX. Щоб отримати діаметр Марса на основі оригінального прикладу вище, формула:

=INDEX(C6:C14,XMATCH(G5,B6:B14)) / returns 6792

Примітки

  1. XMATCH може працювати як з вертикальними, так і з горизонтальними масивами.
  2. XMATCH поверне # N / A, якщо значення пошуку не знайдено.

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