Функция XMATCH в Excel

Функция XMATCH в Excel выполняет поиск и возвращает положение в вертикальных или горизонтальных диапазонах. Это более надежный и гибкий преемник функции ПОИСКПОЗ. XMATCH поддерживает приблизительное и точное совпадение, обратный поиск и подстановочные знаки (* ?) для частичных совпадений.

Что делает функция XMATCH в Excel?

Функция XMATCH в Excel позволяет получить позицию элемента в списке или таблице.

Возвращаемое значение

Числовая позиция в массиве поиска.

Аргументы

• lookup_value — искомое значение.
• search_array — Массив или диапазон для поиска.
• match_mode — [необязательный] 0 = точное совпадение (по умолчанию), -1 = точное совпадение или следующее наименьшее, 1 = точное совпадение или следующее большее, 2 = совпадение с подстановочными знаками.
• search_mode — [необязательный] 1 = поиск с первого (по умолчанию), -1 = поиск с последнего, 2 = двоичный поиск по возрастанию, -2 = двоичный поиск по убыванию.

Синтаксис

=XMATCH(lookup_value, search_array, [match_mode], [search_mode])

Примечания по использованию

Функция XMATCH выполняет поиск и в результате возвращает числовую позицию искомого значения. XMATCH принимает четыре аргумента: lookup_value, search_array, match_mode и search_mode. Общий синтаксис выглядит следующим образом:

=XMATCH(lookup_value,lookup_array,[match_mode],[search_mode])

Lookup_value — это значение, которое нужно искать, а search_array — это диапазон или массив для поиска. Оба аргумента являются обязательными. Аргумент match_mode определяет, какой тип соответствия выполняется (точное, следующее по наименьшему, следующее по величине или подстановочный знак). Search_mode  управляет направлением поиска (от первого к последнему или от последнего к первому) и опцией двоичного поиска, оптимизированной по скорости.

XMATCH против MATCH

XMATCH — это обновленная замена функции MATCH. Как и функция MATCH, XMATCH выполняет поиск и возвращает числовую позицию. Также, функция может выполнять поиск в вертикальных или горизонтальных диапазонах, поддерживает как приблизительные, так и точные совпадения, а также допускает подстановочные знаки (* ?) для частичных совпадений. Пять ключевых отличий между XMATCH и MATCH:

1. По умолчанию XMATCH соответствует точному совпадению, а MATCH — приблизительному совпадению.
2. XMATCH может найти следующий больший элемент или следующий меньший элемент.
3. Может выполнять обратный поиск (т.е. поиск от последнего к первому).
4. Не требует сортировки значений при приблизительном сопоставлении.Мможет выполнять двоичный поиск, специально оптимизированный по скорости.

В формулах ИНДЕКС и ПОИСКПОЗ использование ХПОИСКПОЗ вместо функции ПОИСКПОЗ «обновляет» формулу, включив в нее преимущества, перечисленные выше.

Замена MATCH на XMATCH

В некоторых случаях XMATCH может быть заменой функции MATCH. Например, для точных совпадений синтаксис идентичен:

=MATCH(value,array,0) // точное совпадение
=XMATCH(value,array,0) //точное совпадение

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

=MATCH(value,array,1) // точное совпадение или следующий наименьший
=XMATCH(value,array,1) // точное совпадение или следующий *по величине*

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

=XMATCH(value,array,-1) // точное совпадение или следующий наименьший

Примечание. Функция MATCH вообще не предоставляет аргумент режима поиска.

Режим матча

Третий аргумент XMATCH — match_mode. Это необязательный аргумент, который управляет поведением совпадений следующим образом:

Режим матча	Поведение
0 (по умолчанию)	Полное совпадение. Вернет #N/A, если совпадений нет.
-1	Точное совпадение или следующий меньший элемент.
1	Точное совпадение или следующий более крупный предмет.
2	Подстановочный знак (*, ?, ~)

Режим поиска

Четвертый аргумент XMATCH — режим поиска. Это необязательный аргумент, который управляет поведением поиска следующим образом:

Режим поиска	Поведение
1 (по умолчанию)	Поиск от первого значения до последнего
-1	Поиск от последнего значения к первому (обратный)
2	Значения двоичного поиска отсортированы по возрастанию.
-2	Значения двоичного поиска отсортированы по убыванию.

Бинарный поиск выполняется очень быстро, но будьте осторожны: данные сортируются по мере необходимости. Если данные не отсортированы должным образом, двоичный поиск может вернуть неверные результаты, которые выглядят совершенно нормально.

Полное совпадение

На листе ниже XMATCH используется для получения положения «Марса» в списке планет в диапазоне B6:B13. Формула в G6:

=XMATCH(G4,B5:B13) // возвращает 4

По умолчанию XMATCH соответствует точному совпадению, поэтому включать такое поведение нет необходимости.

Поведение в режиме матча

В приведенном ниже примере показано поведение режима сопоставления со значением поиска 3,1 и значениями поиска в B5:B11:

E6=XMATCH(E4,B5:B11) // возвращает #N/A
E7=XMATCH(E4,B5:B11,-1) // возвращает 3
E8=XMATCH(E4,B5:B11,1) // возвращает 4

ИНДЕКС и XMATCH

XMATCH можно использовать так же, как ПОИСКПОЗ с функцией ИНДЕКС . Чтобы получить диаметр Марса на основе исходного примера выше, используйте формулу:

=INDEX(C6:C14,XMATCH(G5,B6:B14)) / возвращает 6792

XMATCH с подстановочным знаком

Если для параметра match_mode установлено значение 2, XMATCH может выполнять сопоставление с использованием подстановочных знаков . В примере, показанном ниже, формула в E5 имеет следующий вид:

=XMATCH(E4,B5:B13,2) // returns 6

Это эквивалентно:

=XMATCH("pq*",B5:B13,2)

XMATCH находит первый код, который начинается с «pq» и возвращает 6, поскольку PQR-121 появляется в строке 6 диапазона B5:B13. Обратите внимание, что XMATCH не учитывает регистр.

Несколько критериев

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

Совпадение с учетом регистра

Функция XMATCH не чувствительна к регистру. Однако функцию XMATCH можно настроить для выполнения сопоставления с учетом регистра в сочетании с функцией EXACTв такой общей формуле:

=XMATCH(TRUE,EXACT(lookup_value,array),0))

Функция EXACT сравнивает каждое значение в массиве с искомым_значением с учетом регистра.

Бинарный поиск

XMATCH имеет опцию режима двоичного поиска, которая выполняет очень быстро. Чтобы включить режим двоичного поиска, данные необходимо отсортировать по возрастанию или убыванию. Если значения отсортированы по возрастанию , используйте значение 2 для search_mode. Если значения отсортированы в порядке убывания, используйте значение -2. Ниже приведен общий синтаксис для включения режима двоичного поиска для поиска точного соответствия:

=XMATCH(value,array,0,2) // бинарный поиск A-Z
=XMATCH(value,array,0,-2) // бинарный поиск Z-A

Основной пример основан на функции XLOOKUP, но в объяснении показано, как использовать INDEX и XMATCH для решения той же проблемы.

Примечания

1. XMATCH может работать как с вертикальными, так и с горизонтальными массивами.
2. Возвращает #N/A, если искомое значение не найдено.
3. По умолчанию XMATCH соответствует точному совпадению, а MATCH — приблизительному совпадению.
4. XMATCH может найти следующий больший элемент или следующий меньший элемент.
5. Может выполнять обратный поиск (т.е. поиск от последнего к первому).
6. Не требует сортировки значений при приблизительном сопоставлении.
7. Может выполнять двоичный поиск, специально оптимизированный по скорости.