Excel match функция не работает

Обновлено: 22.01.2025

Объяснение и предыстория: у меня есть список животных и их цвет в столбце G (животное) и H (цвет). У меня есть список уникальных цветов в столбце A и список уникальных животных в столбце D. В столбце B, рядом со списком уникальных цветов, мне нужно знать обо всех животных, животное которых имеет этот цвет чаще всего (необработанное число, а не пропорция.) Я не могу использовать дополнительные вспомогательные ячейки.

Я установил max по животному для каждого цвета с формулой с результатом 7, но это насколько я могу получить. Я могу установить оператор COUNTIFS в оператор MAX следующим образом: COUNTIFS(H:H,A2,G:G,E1:E19)=MAX(COUNTIFS(H:H,A2,G:G,E1:E19)) который может использоваться как массив TRUE / FALSE в формуле массива. Наконец, я пытаюсь использовать MATCH с предыдущей формулой в качестве своего массива и искать TRUE-значение, чтобы попытаться получить позицию единственного истинного значения в массиве, но, похоже, не сможет его найти и вместо этого дает мне 19 , который является длиной всего массива.

Когда я перехожу через формулу, вот шаг прямо перед выходом 19:

шаг до конечного результата

Почему этот MATCH не работает?

поместите это in = CHOOSE (IF (MAX (COUNTIFS (G: G, $ E $ 1, H: H, A2), COUNTIFS (G: G, $ E $ 2, H: H, A2), COUNTIFS (G: G, $ E $ 3, Н: Н, А2), COUNTIFS (G: G, $ E $ 4, Н: Н, А2), COUNTIFS (G: G, $ E $ 5, Н: Н, А2), COUNTIFS (G: G , $ E $ 6, Н: Н, А2), COUNTIFS (G: G, $ E $ 7, H: H, А2), COUNTIFS (G: G, $ E $ 8, H: H, А2), COUNTIFS (G: G, $ E $ 9, Н: Н, А2))> MAX (COUNTIFS (G: G, $ E $ 10, Н: Н, А2), COUNTIFS (G: G, $ E $ 11, Н: Н, А2), COUNTIFS (G: G, $ E $ 12, H: Н, А2), COUNTIFS (G: G, $ E $ 13, H: H, А2), COUNTIFS (G: G, $ E $ 14, H: Н, А2) , COUNTIFS (G: G, $ E $ 15, H: H, А2), COUNTIFS (G: G, $ E $ 16, H: H, А2), COUNTIFS (G: G, $ E $ 17, H: H, A2 ), COUNTIFS (G: G, $ E $ 18, H: H, А2), COUNTIFS (G: G, $ E $ 19, H: H, А2)), 1,2), вЫБРАТЬ (ЕСЛИ (MAX (COUNTIFS ( G: G, $ E $ 1, Н: Н, А2), COUNTIFS (G: G, $ E $ 2, Н: Н, А2), COUNTIFS (G: G, $ E $ 3, Н: Н, А2), COUNTIFS (G: G, $ E $ 4, Н: Н, А2))> MAX (COUNTIFS (G: G, $ E $ 5, Н: Н, А2), COUNTIFS (G: G, $ E $ 6, H: Н, А2), COUNTIFS (G: G, $ E $ 7, Н: Н, А2), COUNTIFS (G: G, $ E $ 8, Н: Н, А2), COUNTIFS (G: G, $ E $ 9, Н: Н , А2)), 1,2), вЫБРАТЬ (ЕСЛИ (MAX (COUNTIFS (G: G, $ E $ 1, H: Н, А2), COUNTIFS (G: G, $ E $ 2, H: H, А2)) > MAX (COUNTIFS (G: G, $ E $ 3, H: Н, А2), COUNTIFS (G: G, $ E $ 4, H: H, А2)), 1,2), IF (COUNTIFS (G: G , $ E $ 1, Н: Н, А2)> COUNTIFS (G: G, $ E $ 2, H: H, A2), "летучая мышь", "енот" ), IF (COUNTIFS (G: G, $ E $ 3, H: H, A2)> COUNTIFS (G: G, $ E $ 4, H: H, А2), "медведь", "коза")), ВЫБРАТЬ ( IF (MAX (COUNTIFS (G: G, $ E $ 5, H: H, А2), COUNTIFS (G: G, $ E $ 6, H: H, А2))> MAX (COUNTIFS (G: G, $ E $ 7 , Н: Н, А2), COUNTIFS (G: G, $ E $ 8, H: H, А2), COUNTIFS (G: G, $ E $ 9, H: H, А2)), 1,2), ЕСЛИ ( COUNTIFS (G: G, $ E $ 5, H: Н, А2)> COUNTIFS (G: G, $ E $ 6, H: H, A2), "лось", "черепаха"), вЫБРАТЬ (ЕСЛИ (COUNTIFS (G : G, $ E $ 7, Н: Н, А2)> MAX (COUNTIFS (G: G, $ E $ 8, H: H, А2), COUNTIFS (G: G, $ E $ 9, H: H, А2)) , 1,2), "белки", ЕСЛИ (COUNTIFS (G: G, $ E $ 8, Н: Н, А2)> COUNTIFS (G: G, $ E $ 9, Н: Н, А2), "змейки", "птица")))), вЫБРАТЬ (IF (MAX (COUNTIFS (G: G, $ E $ 10, H: H, A2), COUNTIFS (G: G, $ E $ 11, H: H, A2), COUNTIFS ( G: G, $ E $ 12, Н: Н, А2), COUNTIFS (G: G, $ E $ 13, Н: Н, А2), COUNTIFS (G: G, $ E $ 14, Н: Н, А2))> MAX (COUNTIFS (G: G, $ E $ 15, Н: Н, А2), COUNTIFS (G: G, $ E $ 16, Н: Н, А2), COUNTIFS (G: G, $ E $ 17, Н: Н, А2), COUNTIFS (G: G, $ E $ 18, Н: Н, А2), COUNTIFS (G: G, $ E $ 19, Н: Н, А2)), 1,2), вЫБРАТЬ (ЕСЛИ (MAX (COUNTIFS (G: G, $ E $ 10, Н: Н, А2), COUNTIFS (G: G, $ E $ 11, Н: Н, А2))> MAX (COUNTIFS (G: G, $ E $ 12, Н: Н, A2), COUNTIFS (G: G, $ E $ 13, Н: Н, А2), COUNTIFS (G: G, $ E $ 14, Н: Н, А2)), 1,2), IF (COUNTIFS (G: G , $ E $ 10, Н: Н, А2)> COUNTIFS (G: G, $ E $ 11, H: H, A2), "кошка », "Собака"), ВЫБРАТЬ (ЕСЛИ (COUNTIFS (G: G, $ E $ 12, Н: Н, А2)> MAX (COUNTIFS (G: G, $ E $ 13, Н: Н, А2), COUNTIFS (G : G, $ E $ 14, H: Н, А2)), 1,2), "кролика", ЕСЛИ (COUNTIFS (G: G, $ E $ 13, Н: Н, А2)> COUNTIFS (G: G, $ Е $ 14, Н: Н, А2), "овцы", "корова"))), вЫБРАТЬ (IF (MAX (COUNTIFS (G: G, $ E $ 15, Н: Н, А2), COUNTIFS (G: G, $ E $ 16, Н: Н, А2))> MAX (COUNTIFS (G: G, $ E $ 17, Н: Н, А2), COUNTIFS (G: G, $ E $ 18, H: H, А2), COUNTIFS ( G: G, $ E $ 19, Н: Н, А2)), 1,2), ЕСЛИ (COUNTIFS (G: G, $ E $ 15, Н: Н, А2)> COUNTIFS (G: G, $ E $ 16, Н: Н, А2), "курица", "лам"), вЫБРАТЬ (ЕСЛИ (COUNTIFS (G: G, $ E $ 17, H: H, А2)> MAX (COUNTIFS (G: G, $ E $ 18, Н : Н, А2), COUNTIFS (G: G, $ E $ 19, Н: Н, А2)), 1,2), "свинья", ЕСЛИ (COUNTIFS (G: G, $ E $ 18, H: H, А2 )> COUNTIFS (G: G, $ E $ 19, H: H, A2), "лошадь", "олень"))))):

затем перетащите до B10.

Основные вещи, которые я узнал здесь:

Выбрать () является хорошей альтернативой сильно вложенным if (). Это каким-то образом помогло мне «не потеряться» в расчете формулы.
Каскадная двоичная оценка - хороший способ разбить список повторных оценок.
«Я не могу использовать дополнительные вспомогательные ячейки». <- Если OP не против, вы всегда можете использовать вспомогательный лист. Полагая это требование, действительно толкнул предел формулы excel. Моя 1-я версия решения нуждается в> 10000 символах, и она превосходит 8192 символов excel.

Попробуйте эту формулу массива, а B2 затем скопируйте в B3:B10 :

= INDEX( $G$1:$G$510, MATCH( MAX( IF( $H$1:$H$510 = A2, COUNTIFS( $G$1:$G$510, $G$1:$G$510, $H$1:$H$510, $H$1:$H$510), "" ) ), IF( $H$1:$H$510 = A2, COUNTIFS( $G$1:$G$510, $G$1:$G$510, $H$1:$H$510, $H$1:$H$510), "" ), 0 ) )

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

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

Воспользуемся функцией ПОИСКПОЗ. Ее синтаксис следующий.

Искомое_значение – то значение, которое ищется в списке.

Просматриваемый_массив – столбец, где происходит просмотр данных.

С одной стороны, типы -1 и 1 позволяют искать неточное совпадение, чтобы расширяет возможности поиска, с другой, требует дополнительного действия (сортировки), что резко снижает уровень автоматизации. Поэтому в большинстве случаев выбирают 0 для точного соответствия.

Итак, в примере выше для поиска ячейки со значением 82 потребуется указать следующую функцию:

Ответом будет 5, т.к. число 82 находится в 5-й сверху ячейке. Разумеется, вместо числа 82 можно указать ссылку, где находится критерий поиска.

Функция ПОПИСКПОЗ раскрывается по полной при использовании в комплексе с другими формулами, т.к. номер ячейки сам по себе мало, что дает.

5 вариантов использования функции ИНДЕКС (INDEX)

Вариант 1. Извлечение данных из столбца по номеру ячейки

Самый простой случай использования функции ИНДЕКС – это ситуация, когда нам нужно извлечь данные из одномерного диапазона-столбца, если мы знаем порядковый номер ячейки. Синтаксис в этом случае будет:

=ИНДЕКС( Диапазон_столбец ; Порядковый_номер_ячейки )

Этот вариант известен большинству продвинутых пользователей Excel. В таком виде функция ИНДЕКС часто используется в связке с функцией ПОИСКПОЗ (MATCH) , которая выдает номер искомого значения в диапазоне. Таким образом, эта пара заменяет легендарную ВПР (VLOOKUP) :

. но, в отличие от ВПР, могут извлекать значения левее поискового столбца и номер столбца-результата высчитывать не нужно.

Вариант 2. Извлечение данных из двумерного диапазона

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

=ИНДЕКС( Диапазон ; Номер_строки ; Номер_столбца )

Т.е. функция извлекает значение из ячейки диапазона с пересечения строки и столбца с заданными номерами.

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

Вариант 3. Несколько таблиц

Если таблица не одна, а их несколько, то функция ИНДЕКС может извлечь данные из нужной строки и столбца именно заданной таблицы. В этом случае используется следующий синтаксис:

=ИНДЕКС( (Диапазон1;Диапазон2;Диапазон3) ; Номер_строки ; Номер_столбца ; Номер_диапазона )

Вариант 4. Ссылка на столбец / строку

Если во втором варианте использования функции ИНДЕКС номер строки или столбца задать равным нулю (или просто не указать), то функция будет выдавать уже не значение, а ссылку на диапазон-столбец или диапазон-строку соответственно:

Обратите внимание, что поскольку ИНДЕКС выдает в этом варианте не конкретное значение ячейки, а ссылку на диапазон, то для подсчета потребуется заключить ее в дополнительную функцию, например СУММ (SUM) , СРЗНАЧ (AVERAGE) и т.п.

Вариант 5. Ссылка на ячейку

Общеизвестно, что стандартная ссылка на любой диапазон ячеек в Excel выглядит как Начало-Двоеточие-Конец, например A2:B5. Хитрость в том, что если взять функцию ИНДЕКС в первом или втором варианте и подставить ее после двоеточия, то наша функция будет выдавать уже не значение, а адрес, и на выходе мы получим полноценную ссылку на диапазон от начальной ячейки до той, которую нашла ИНДЕКС:

Нечто похожее можно реализовать функцией СМЕЩ (OFFSET) , но она, в отличие от ИНДЕКС, является волатильной, т.е. пересчитывается каждый раз при изменении любой ячейки листа. ИНДЕКС же работает более тонко и запускает пересчет только при изменении своих аргументов, что ощутимо ускоряет расчет в тяжелых книгах по сравнению со СМЕЩ.

ПОИСКПОЗ (функция ПОИСКПОЗ)

Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне ячеек и возвращает относительную позицию этого элемента в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 25 и 38, то формула =ПОИСКПОЗ(25;A1:A3;0) возвращает значение 2, поскольку элемент 25 является вторым в диапазоне.

Совет: Функцией ПОИСКПОЗ следует пользоваться вместо одной из функций ПРОСМОТР, когда требуется найти позицию элемента в диапазоне, а не сам элемент. Например, функцию ПОИСКПОЗ можно использовать для передачи значения аргумента номер_строки функции ИНДЕКС.

Аргументы функции ПОИСКПОЗ описаны ниже.

Искомое_значение. Обязательный аргумент. Значение, которое сопоставляется со значениями в аргументе просматриваемый_массив. Например, при поиске номера в телефонной книге имя абонента указывается в качестве искомого значения, а нужным значением будет номер телефона.

Аргумент искомое_значение может быть значением (числом, текстом или логическим значением) или ссылкой на ячейку, содержащую такое значение.

Просматриваемый_массив Обязательный аргумент. Диапазон ячеек, в которых производится поиск.

Тип_сопоставления. Необязательный аргумент. Число -1, 0 или 1. Аргумент тип_сопоставления указывает, каким образом в Microsoft Excel искомое_значение сопоставляется со значениями в аргументе просматриваемый_массив. По умолчанию в качестве этого аргумента используется значение 1.

В приведенной ниже таблице описано, как функция находит значения в зависимости от аргумента тип_сопоставления.

30 функций Excel за 30 дней: ПОИСКПОЗ (MATCH)

Вчера в марафоне 30 функций Excel за 30 дней мы находили текстовые строки при помощи функции SEARCH (ПОИСК), а также использовали IFERROR (ЕСЛИОШИБКА) и ISNUMBER (ЕЧИСЛО) в ситуациях, когда функция выдаёт ошибку.

В 19-й день нашего марафона мы займёмся изучением функции MATCH (ПОИСКПОЗ). Она ищет значение в массиве и, если значение найдено, возвращает его позицию.

Итак, давайте обратимся к справочной информации по функции MATCH (ПОИСКПОЗ) и разберем несколько примеров. Если у Вас есть собственные примеры или подходы по работе с этой функцией, пожалуйста, делитесь ими в комментариях.

Функция 19: MATCH (ПОИСКПОЗ)

Как можно использовать функцию MATCH (ПОИСКПОЗ)?

Функция MATCH (ПОИСКПОЗ) возвращает позицию элемента в массиве, и этот результат может быть использован другими функциями, такими как INDEX (ИНДЕКС) или VLOOKUP (ВПР). Например:

Найти положение элемента в несортированном списке.
Использовать вместе с CHOOSE (ВЫБОР), чтобы перевести успеваемость учащихся в буквенную систему оценок.
Использовать вместе с VLOOKUP (ВПР) для гибкого выбора столбца.
Использовать вместе с INDEX (ИНДЕКС), чтобы найти ближайшее значение.

Синтаксис MATCH (ПОИСКПОЗ)

Функция MATCH (ПОИСКПОЗ) имеет следующий синтаксис:

lookup_value (искомое_значение) – может быть текстом, числом или логическим значением.
lookup_array (просматриваемый_массив) – массив или ссылка на массив (смежные ячейки в одном столбце или в одной строке).
match_type (тип_сопоставления) – может принимать три значения: -1, или 1. Если аргумент пропущен, это равносильно 1.

Ловушки MATCH (ПОИСКПОЗ)

Функция MATCH (ПОИСКПОЗ) возвращает положение найденного элемента, но не его значение. Если требуется вернуть значение, используйте MATCH (ПОИСКПОЗ) вместе с функцией INDEX (ИНДЕКС).

Пример 1: Находим элемент в несортированном списке

Для несортированного списка можно использовать в качестве значения аргумента match_type (тип_сопоставления), чтобы выполнить поиск точного совпадения. Если требуется найти точное совпадение текстовой строки, то в искомом значении допускается использовать символы подстановки.

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

Пример 2: Изменяем оценки учащихся c процентов на буквы

Вы можете преобразовать оценки учащихся в буквенную систему, используя функцию MATCH (ПОИСКПОЗ) так же, как Вы делали это с VLOOKUP (ВПР). В этом примере функция использована в сочетании с CHOOSE (ВЫБОР), которая и возвращает нужную нам оценку. Аргумент match_type (тип_сопоставления) принимаем равным -1, поскольку баллы в таблице отсортированы в порядке убывания.

Когда аргумент match_type (тип_сопоставления) равен -1, результатом будет наименьшее значение, которое больше искомого или эквивалентное ему. В нашем примере искомое значение равно 54. Поскольку такого значения нет в списке баллов, то возвращается элемент, соответствующий значению 60. Так как 60 стоит на четвёртом месте списка, то результатом функции CHOOSE (ВЫБОР) будет значение, которое находится на 4-й позиции, т.е. ячейка C6, в которой находится оценка D.

Пример 3: Создаем гибкий выбор столбца для VLOOKUP (ВПР)

Чтобы придать больше гибкости функции VLOOKUP (ВПР), Вы можете использовать MATCH (ПОИСКПОЗ) для поиска номера столбца, а не жестко вписывать его значение в функцию. В следующем примере пользователи могут выбрать регион в ячейке H1, это искомое значение для VLOOKUP (ВПР). Далее, они могут выбрать месяц в ячейке H2, и функция MATCH (ПОИСКПОЗ) возвратит номер столбца, соответствующий этому месяцу.

Пример 4: Находим ближайшее значение при помощи INDEX (ИНДЕКС)

Функция MATCH (ПОИСКПОЗ) отлично работает в сочетании с функцией INDEX (ИНДЕКС), которую мы рассмотрим более пристально чуть позже в рамках данного марафона. В этом примере функция MATCH (ПОИСКПОЗ) использована для того, чтобы найти из нескольких угаданных чисел ближайшее к правильному.

Функция ABS возвращает модуль разницы между каждым угаданным и правильным числами.
Функция MIN (МИН) находит наименьшую из разниц.
Функция MATCH (ПОИСКПОЗ) находит адрес наименьшей разницы в списке разниц. Если в списке есть несколько совпадающих значений, то возвращено будет первое.
Функция INDEX (ИНДЕКС) возвращает имя, соответствующее этой позиции, из списка имен.

Excel MATCH Function

MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, the INDEX function is combined with MATCH to retrieve the value at the position returned by MATCH.

Use the MATCH function to get the position of a value in an array. Match offers three different matching modes, which makes it more flexible than other lookup functions. When used with INDEX, MATCH can retrieve the value at the matched position.

Match type information

Basic exact match

When match type is set to zero, MATCH performs an exact match. In the example below, the formula in E3 is:

Basic approximate match

When match type is set to 1, MATCH will perform an approximate match on values sorted A-Z, finding the largest value less than or equal to the lookup value. In the example shown below, the formula in E3 is:

Basic wildcard match

When match type is set to zero (0), MATCH can perform a match using wildcards. In the example shown below, the formula in E3 is:

Функция 19: MATCH (ПОИСКПОЗ)

Как можно использовать функцию MATCH (ПОИСКПОЗ)?

Найти положение элемента в несортированном списке.
Использовать вместе с CHOOSE (ВЫБОР), чтобы перевести успеваемость учащихся в буквенную систему оценок.
Использовать вместе с VLOOKUP (ВПР) для гибкого выбора столбца.
Использовать вместе с INDEX (ИНДЕКС), чтобы найти ближайшее значение.

Синтаксис MATCH (ПОИСКПОЗ)

Функция MATCH (ПОИСКПОЗ) имеет следующий синтаксис:

lookup_value (искомое_значение) – может быть текстом, числом или логическим значением.
lookup_array (просматриваемый_массив) – массив или ссылка на массив (смежные ячейки в одном столбце или в одной строке).
match_type (тип_сопоставления) – может принимать три значения: -1, 0 или 1. Если аргумент пропущен, это равносильно 1.

Ловушки MATCH (ПОИСКПОЗ)

Пример 1: Находим элемент в несортированном списке

Для несортированного списка можно использовать 0 в качестве значения аргумента match_type (тип_сопоставления), чтобы выполнить поиск точного совпадения. Если требуется найти точное совпадение текстовой строки, то в искомом значении допускается использовать символы подстановки.

Пример 2: Изменяем оценки учащихся c процентов на буквы

Пример 3: Создаем гибкий выбор столбца для VLOOKUP (ВПР)

Пример 4: Находим ближайшее значение при помощи INDEX (ИНДЕКС)

В Microsoft Excel есть так много различных поисков / справочных функций, которые могут помочь вам найти определенное значение в диапазоне ячеек, и MATCH - одна из них. По сути, он идентифицирует относительную позицию или местоположение любого элемента в диапазоне ячеек. Тем не менее, матч может сделать гораздо больше, чем его чистая сущность.

Функция MATCH используется для поиска местоположения искомого значения в таблице или столбце строки. MATCH находит приблизительные и точные совпадения и подстановочные знаки (*?) Для ограниченных совпадений. Большую часть времени функция INDEX интегрирована с функцией MATCH для получения значения в местоположении, возвращаемом MATCH.

МАТЧ Формула в Excel

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

Формула для функции MATCH выглядит следующим образом:

Lookup_value (обязательно) - значение, которое вы ищете. Это может быть числовое, текстовое или логическое значение, а также ссылка на ячейку.
Lookup_array (обязательно) - поиск по диапазону ячеек.
Match_type (необязательно) - объясняет тип соответствия. Это может быть любое из следующих значений: 1, 0, -1. Аргумент match_type при установке значения 0 возвращает точное совпадение, в то время как два других типа значений допускают приблизительное совпадение.

1 или опущено (по умолчанию) - поиск наибольшего значения в массиве поиска, которое меньше или равно значению поиска. Требуется сортировка массива поиска в порядке возрастания, от наименьшего к наибольшему или от А до Я.

0 - находит первое значение в массиве, которое абсолютно равно значению поиска. Сортировка не требуется.

-1 - находит наименьшее значение в массиве, которое равно или превышает значение поиска. Массив поиска должен быть отсортирован в порядке убывания, от наибольшего к наименьшему или от Z до A.

MATCH в Excel - Использование

Используйте функцию MATCH, чтобы получить соответствующее местоположение элемента в массиве. Match имеет разные типы режимов соответствия, что делает его более универсальным, чем функции поиска. Обычно, когда функция MATCH объединяется с INDEX, она может получить значение зафиксированной позиции.

Информация о типе соответствия

Если 1 соответствует match_type, MATCH находит наибольшее значение, которое равно или меньше, чем lookup_value. Массив lookup_array должен быть отсортирован в порядке возрастания.
Если 0 соответствует match_type, MATCH находит первое значение, точно равное lookup_value. lookup_array не требует никакой сортировки.
Если -1 соответствует match_type, MATCH дает наименьшее значение, которое равно или больше

искомое_значение. Массив lookup_array должен быть отсортирован в порядке убывания.

Если match_type опущен, он принимается равным 1.

Примечание: все типы совпадений обычно находят точное совпадение.

Типы функции MATCH в Excel

1. Точное совпадение

Функция MATCH выполняет точное совпадение, когда тип совпадения установлен на ноль. В приведенном ниже примере формула в E3:

Вы можете скачать этот шаблон Excel функции MATCH здесь - Шаблон Excel функции MATCH

= ПОИСКПОЗ (E2, B3: B10, 0)

Функция MATCH возвращает точное совпадение как 4 .

2. Приблизительное совпадение

MATCH будет выполнять приблизительное сопоставление для значений, отсортированных по AZ, когда тип соответствия установлен на 1, находя наибольшее значение, которое меньше или равно значению поиска. В приведенном ниже примере формула в E3:

MATCH в Excel возвращает приблизительное совпадение как 7.

3. Подстановочный знак

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

Функция MATCH возвращает результат подстановочных знаков как «pq».

Примечания:

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

Функция MATCH в Excel очень проста и удобна в использовании. Давайте рассмотрим работу функции Match в Excel с некоторыми примерами.

Пример № 1

Чтобы лучше понять функцию MATCH, давайте составим простую формулу, основанную на этих данных: имена планет в столбце A с их позициями. Чтобы выяснить, где находится конкретная планета (скажем, Марс), используйте следующую простую формулу:

Функция MATCH возвращает позицию MARS как 4.

Как вы видите на скриншоте выше, имена планет вводятся в произвольном порядке, и поэтому мы устанавливаем аргумент match_type в 0 (точное совпадение), потому что только этот тип совпадения не требует сортировки значений в массиве поиска. Технически, формула соответствия возвращает относительное положение Марса в диапазоне поиска.

Пример № 2 - Если ячейка содержит одну из многих вещей

Общая формула: (= INDEX (результаты, MATCH (TRUE, ISNUMBER (SEARCH (вещи, A1))), 0)))

Объяснение: Для проверки ячейки на одну из многих вещей и выдачи пользовательского результата для первого найденного соответствия можно использовать функцию INDEX / MATCH, сформированную в функции ПОИСК.

В приведенном ниже примере формула в ячейке C5:

(= INDEX (результаты, MATCH (TRUE, ISNUMBER (ПОИСК (вещи, В5)), 0)))

Поскольку выше приведена формула массива, ее следует вводить с помощью клавиш Control + Shift + Enter .

Как работает эта формула?

Эта формула использует два диапазона имен: E5: E8 назван как «вещи», а F5: F8 назван как «результаты». Убедитесь, что вы используете диапазоны имен с одинаковыми именами (в зависимости от данных). Если вы не хотите использовать диапазоны имен, используйте как абсолютные ссылки.

Основной частью этой формулы является следующий фрагмент:

ISNUMBER (SEARCH (вещи, B5)

Это основано на другой формуле, которая проверяет ячейку на наличие одной подстроки. Если в ячейке есть подстрока, формула дает значение ИСТИНА. Если нет, формула дает ЛОЖЬ.

Пример № 3 - Поиск с использованием наименьшего значения

Общая формула = INDEX (диапазон, MATCH (MIN (значения), значения, 0))

Объяснение: Для поиска информации, связанной с самым низким значением в таблице, вы можете использовать формулу в зависимости от функций MATCH, INDEX и MIN.

В примере, показанном ниже, формула используется, чтобы найти имя подрядчика, который имеет самую низкую ставку. Формула в F6:

= ИНДЕКС (В5: В9, MATCH (MIN (С5: С9), С5: C9, 0)))

Как работает эта формула?

Работая изнутри, функция MIN обычно используется, чтобы найти самую низкую ставку в диапазоне C5: C9:

Результат, 99500, подается в функцию MATCH в качестве значения поиска:

Match затем возвращает позицию этого значения в диапазоне 4, который переходит в INDEX в качестве номера строки вместе с B5: B9 в качестве массива:

= INDEX (B5: B9, 4) // возвращает тарелку

Затем функция INDEX возвращает значение в этой позиции: Cymbal .

Ошибки функции соответствия

Кроме того, некоторые пользователи испытывают следующую общую проблему с функцией соответствия:

Вывод

Функция MATCH - это популярная функция, которую большинство из нас использует, не углубляясь в ее механику. Даже после своей тупой цели (чтобы вернуть позицию значения в массиве), функция явно имеет ряд жизнеспособных применений в Excel.