Содержание:
Вчера на марафоне 30 функций Excel за 30 дней мы нашли текстовые строки с помощью функции ПОИСК (ПОИСК) а также использовал IFERROR (ЕСЛИОШИБКА) и ISNUMBER (ISNUMBER) в ситуациях, когда функция выдает ошибку.
В 19 день нашего марафона мы изучим функцию МАТЧ (ПОИСК). Он ищет значение в массиве и, если значение найдено, возвращает его позицию.
Итак, обратимся к справочной информации по функции МАТЧ (MATCH) и посмотрите на несколько примеров. Если у вас есть свои примеры или подходы для работы с этой функцией, поделитесь ими в комментариях.
Функция 19: СООТВЕТСТВИЕ
Функция МАТЧ (MATCH) возвращает позицию значения в массиве или ошибку. #В (#Н/Д), если не найден. Массив может быть отсортированным или несортированным. Функция МАТЧ (MATCH) не чувствителен к регистру.
Как можно использовать функцию ПОИСКПОЗ?
Функция МАТЧ (MATCH) возвращает позицию элемента в массиве, и этот результат может использоваться другими функциями, такими как ИНДЕКС (ИНДЕКС) или ВПР (ВПР). Например:
- Найдите позицию элемента в несортированном списке.
- Использовать с ВЫБЕРИТЕ (ВЫБРАТЬ), чтобы преобразовать успеваемость учащихся в буквенные оценки.
- Использовать с ВПР (ВПР) для гибкого выбора столбцов.
- Использовать с ИНДЕКС (ИНДЕКС), чтобы найти ближайшее значение.
Синтаксис ПОИСКПОЗ
Функция МАТЧ (MATCH) имеет следующий синтаксис:
MATCH(lookup_value,lookup_array,[match_type])
ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])
- искомое_значение (искомое_значение) – может быть текстовым, числовым или логическим.
- искомый_массив (просматриваемый_массив) – массив или ссылка на массив (соседние ячейки в одном столбце или одной строке).
- тип_соответствия (match_type) может принимать три значения: -1, 0 or 1. Если аргумент опущен, это эквивалентно 1.
Ловушки МАТЧ (MATCH)
Функция МАТЧ (MATCH) возвращает позицию найденного элемента, но не его значение. Если вы хотите вернуть значение, используйте МАТЧ (MATCH) вместе с функцией ИНДЕКС (ИНДЕКС).
Пример 1. Поиск элемента в несортированном списке
Для несортированного списка вы можете использовать 0 как значение аргумента тип_соответствия (match_type) для поиска точного соответствия. Если вы хотите найти точное совпадение текстовой строки, вы можете использовать подстановочные знаки в значении поиска.
В следующем примере, чтобы найти позицию месяца в списке, мы можем написать название месяца полностью или частично, используя подстановочные знаки.
=MATCH(D2,B3:B7,0)
=ПОИСКПОЗ(D2;B3:B7;0)
В качестве аргумента искомый_массив (lookup_array) вы можете использовать массив констант. В следующем примере нужный месяц вводится в ячейку D5, а названия месяцев подставляются в качестве второго аргумента функции. МАТЧ (MATCH) как массив констант. Например, если вы введете более поздний месяц в ячейку D5, окт (октябрь), то результатом функции будет #В (#Н/Д).
=MATCH(D5,{"Jan","Feb","Mar"},0)
=ПОИСКПОЗ(D5;{"Jan";"Feb";"Mar"};0)
Пример 2. Изменение оценок учащихся с процентов на буквы
Преобразовать оценки учащихся в буквенную систему можно с помощью функции МАТЧ (МАТЧ) точно так же, как вы сделали с ВПР (ВПР). В этом примере функция используется вместе с ВЫБЕРИТЕ (ВЫБОР), который возвращает нужную нам оценку. Аргумент тип_соответствия (match_type) устанавливается равным -1, поскольку баллы в таблице отсортированы по убыванию.
Когда аргумент тип_соответствия (тип_соответствия) -1, результатом является наименьшее значение, которое больше или эквивалентно желаемому значению. В нашем примере искомое значение — 54. Поскольку такого значения в списке оценок нет, возвращается элемент, соответствующий значению 60. Поскольку 60 находится на четвертом месте в списке, результат функции ВЫБЕРИТЕ (ВЫБРАТЬ) будет значением, находящимся на 4-й позиции, т. е. ячейке C6, содержащей оценку D.
=CHOOSE(MATCH(B9,B3:B7,-1),C3,C4,C5,C6,C7)
=ВЫБОР(ПОИСКПОЗ(B9;B3:B7;-1);C3;C4;C5;C6;C7)
Пример 3: Создание гибкого выбора столбцов для ВПР (VLOOKUP)
Чтобы придать большей гибкости функции ВПР (ВПР) Вы можете использовать МАТЧ (MATCH), чтобы найти номер столбца, а не жестко запрограммировать его значение в функцию. В следующем примере пользователи могут выбрать регион в ячейке H1, это и есть искомое значение. ВПР (ВПР). Далее они могут выбрать месяц в ячейке H2 и функцию МАТЧ (MATCH) вернет номер столбца, соответствующий этому месяцу.
=VLOOKUP(H1,$B$2:$E$5,MATCH(H2,B1:E1,0),FALSE)
=ВПР(H1;$B$2:$E$5;ПОИСКПОЗ(H2;B1:E1;0);ЛОЖЬ)
Пример 4: Поиск ближайшего значения с помощью ИНДЕКС (INDEX)
Функция МАТЧ (MATCH) отлично работает в сочетании с функцией ИНДЕКС (ИНДЕКС), который мы рассмотрим более подробно чуть позже в этом марафоне. В этом примере функция МАТЧ (MATCH) используется для нахождения ближайшего к правильному числа из нескольких угаданных чисел.
- Функция АБС возвращает модуль разницы между каждым угаданным и правильным числом.
- Функция MIN (MIN) находит наименьшую разницу.
- Функция МАТЧ (MATCH) находит адрес наименьшего различия в списке различий. Если в списке несколько совпадающих значений, будет возвращено первое.
- Функция ИНДЕКС (ИНДЕКС) возвращает имя, соответствующее этой позиции, из списка имен.
=INDEX(B2:B5,MATCH(MIN(ABS(C2:C5-F1)),ABS(C2:C5-F1),0))
=ИНДЕКС(B2:B5;ПОИСКПОЗ(МИН(ABS(C2:C5-F1));ABS(C2:C5-F1);0))
