Как использовать функцию Excel XMATCH -

Резюме

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

Цель

Получить позицию элемента в списке или таблице

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

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

Синтаксис

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

Аргументы

  • 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(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

Примечание: функция ПОИСКПОЗ вообще не предлагает аргумент режима поиска.

Тип соответствия

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

Тип соответствия Поведение
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 имеет точное соответствие. Если "Mars" 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 - ИНДЕКС и XMATCH

XMATCH можно использовать так же, как MATCH, с функцией INDEX. Чтобы получить диаметр Марса на основе исходного примера выше, формула:

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

Заметки

  1. XMATCH может работать как с вертикальными, так и с горизонтальными массивами.
  2. XMATCH вернет # N / A, если значение поиска не найдено.

Интересные статьи...