Резюме
- Функция XMATCH была представлена в Microsoft Excel в 2021 году как обновление функции MATCH.
- Аргументы по умолчанию в синтаксисе XMATCH более интуитивно понятны, чем в синтаксисе MATCH, и отдают предпочтение точному совпадению, а не приблизительному.
- Помимо возможности указывать использование подстановочных знаков, XMATCH позволяет выполнять поиск как сверху вниз, так и снизу вверх, что делает процесс поиска гораздо более понятным и гибким.
Существует множество способов извлечения значений из данных в Microsoft Excel, и два таких примера — функции MATCH и XMATCH. Однако с тех пор, как Microsoft представила XMATCH в Excel в 2021 году, я полностью отказался от MATCH, и в этом руководстве я объясню, почему вам тоже стоит это сделать.
Поскольку функция XMATCH представляет собой современную модернизацию функции MATCH, она доступна только тем, кто использует Excel для Microsoft 365, Excel для веб-сайта, мобильные и планшетные приложения Excel, а также одноразовые версии Excel, выпущенные в 2021 году или позже.
Получите постоянный доступ к Word, Excel, PowerPoint и OneNote с пакетом Office Home 2024. Это не полная замена подписке Microsoft 365, так как в нем отсутствуют некоторые приложения и не включены обновления функций, но это разовая покупка, поэтому она идеально подходит тем, кому нужны только основные функции.
Посмотреть на Amazon Посмотреть на Microsoft
Функция ПОИСКПОЗ в Excel
Функция ПОИСКПОЗ в Microsoft Excel сообщает вам позицию элемента в диапазоне. Синтаксис:
= ПОИСКПОЗ (a,b,c)
в котором
- a (обязательно) — значение для поиска,
- b (обязательно) — диапазон ячеек, содержащих массив поиска, и
- c (необязательно) — тип соответствия (1 или опущено = наибольшее значение, меньшее или равное аргументу b когда массив расположен в порядке возрастания; 0 = точное совпадение; -1 = наименьшее значение, большее или равное аргументу b когда массив находится в порядке возрастания).
Функция ПОИСКПОЗ ищет значение (a) сверху вниз в массиве (b) — поиск снизу вверх невозможен.
В этом примере введите:
=ПОИСКПОЗ(D1,A1:A4,0)
в ячейку D2 ищет слово «Груши» в ячейках A1–A4 и возвращает его позицию в этом диапазоне.
MATCH часто используется вместе с INDEX для поиска значения в массиве, где MATCH определяет номер строки, а INDEX возвращает значение.
Во втором примере введите:
=ИНДЕКС($A$2:$A$24,ПОИСКПОЗ($D2,$B$2:$B$24,0))
в ячейку E2 возвращает дату из ячеек A2 — A24 (ИНДЕКС), где номер строки определяется первым точным совпадением (на что указывает «0» в аргументе c) между значением в ячейке D2 и идентификаторами продуктов в ячейках B2–B24 (ПОИСКПОЗ).
Символ доллара ($), помещенный перед буквой в ссылке на ячейку, блокирует столбец (известный как смешанная ссылка), так что формула по-прежнему работает правильно при копировании по строке. Ссылки с символами доллара, помещенными перед столбцом и строки являются абсолютными ссылками, то есть они остаются фиксированными независимо от того, копируется ли формула вниз по столбцу или по всей строке.
Функция XMATCH в Excel
Функция XMATCH в Microsoft Excel также сообщает вам положение элемента в диапазоне, но значения по умолчанию для пропущенных аргументов отличаются, вы можете выполнять поиск снизу вверх, и вы можете включать подстановочные знаки. Синтаксис:
=XПОИСКПОЗ(a,b,c,d)
в котором
- a (обязательно) — значение для поиска,
- b (обязательно) — диапазон ячеек, содержащих массив поиска, и
- c (необязательно) — режим соответствия (0 или пропущено = точное совпадение; -1 = точное совпадение или следующий наименьший элемент; 1 = точное совпадение или следующий наибольший элемент; 2 = совпадение с подстановочным знаком), и
- d (необязательно) — режим поиска (1 или пропущено = сверху вниз; -1 = снизу вверх; 2 = двоичный поиск, где массив расположен в порядке возрастания; -2 = двоичный поиск, где массив расположен в порядке убывания).
Даже если аргумент c называется «типом соответствия» в MATCH и «режимом соответствия» в XMATCH, они по сути выполняют одну и ту же задачу, но с разными значениями по умолчанию и немного разными аргументами.
В этом примере введите:
=ИНДЕКС($A$2:$A$24,XMATCH($D2,$B$2:$B$24))
в ячейку E2 возвращает тот же результат, что и в примере выше, где я использовал ИНДЕКС и ПОИСКПОЗ, хотя мне не нужно было указывать режим соответствия, поскольку по умолчанию используется точное соответствие. В результате XMATCH требует ввода меньшего количества аргументов, чем MATCH в простых случаях поиска сверху вниз.
Однако, где XMATCH действительно превосходит своего предшественника, так это когда вы хотите найти последний сопоставить в массиве, ища снизу вверх. Вот почему XMATCH имеет еще один необязательный аргумент (a в d) чем СОВПАДЕНИЕ (a в c).
Вот, набрав:
=ИНДЕКС($A$2:$A$24,XПОИСКПОЗ($D2,$B$2:$B$24,,-1))
в ячейку F2 возвращает последний дата из ячеек A2 по A24, потому что аргумент d содержит «-1», что означает, что номер строки определяется последний точное совпадение между значением в ячейке D2 и идентификаторами продуктов в ячейках B2–B24. Аргумент режима соответствия (c) опущено, поскольку по умолчанию используется точное совпадение, а именно его мы и хотим вернуть.
Еще одним преимуществом использования XMATCH перед MATCH является то, что первый вариант позволяет сообщить Excel, что при поиске используются подстановочные знаки.
Здесь ИНДЕКС и XMATCH используются для поиска первой и последней даты продажи товара из садового отдела.
Обратите внимание, что в ячейке D2 подстановочный символ звездочки (*) используется для поиска кода продукта, который начинается с букв «GD» и содержит любое количество символов после них.
Затем в ячейке E2 я набрал:
=ИНДЕКС($A$2:$A$24,XMATCH($D2,$B$2:$B$24,2))
где «2» в аргументе режима сопоставления (c) сообщает Excel, что я использовал подстановочный знак в искомом значении (a) и аргумент режима поиска (d) был пропущен, так как я хотел выполнить поиск сверху вниз, чтобы найти первое совпадение.
Если вы не укажете в аргументе режима сопоставления (c) используется подстановочный знак, XMATCH предполагает, что вы ищете значения, содержащие * и ? сами по себе, а не рассматривает эти символы как подстановочные знаки.
В ячейке F2 я набрал:
=ИНДЕКС($A$2:$A$24,XMATCH($D2,$B$2:$B$24,2,-1))
где, а также добавление подстановочного знака для аргумента режима соответствия (c), я также ввел «-1» для режима поиска (d), чтобы указать Excel выполнять поиск снизу вверх.
Чтобы достичь того же результата с помощью функции MATCH, тип соответствия должен быть указан как точное совпадение («0»). Однако, поскольку это режим соответствия по умолчанию для функции XMATCH, и поскольку существует специальный режим поиска для подстановочных знаков, это гораздо более интуитивный и чистый процесс.
Другой способ поиска значений в таблице — использование функции XLOOKUP, усовершенствованной версии функций VLOOKUP и HLOOKUP. Как и в случае с XMATCH, XLOOKUP по умолчанию использует точное совпадение и позволяет выполнять поиск сверху вниз и снизу вверх. Более того, XLOOKUP также позволяет включать аргумент «если не найдено», который предохраняет результат от отображения «N/A», если искомое значение не может быть найдено в массиве.
