Объяснение функции Excel MATCH

В Excel есть несколько функций, облегчающих работу с большими таблицами. Наряду с LOOKUP, VLOOKUP, INDEX или INDIRECT, функция Excel MATCH также может упростить работу с большими объемами данных. С помощью функции MATCH можно быстро находить определенные элементы даже в больших таблицах.

В больших таблицах иногда бывает сложно найти конкретное значение. Хотя Excel, как и другие приложения Office, предлагает функцию поиска, результат нельзя обработать. Если вы хотите узнать, где в таблице находится определенный элемент, а затем перенести результат в другую формулу, необходимо использовать специальную функцию Excel. В то время как различные функции поиска воспроизводят содержимое конкретной ячейки в контексте поискового запроса, MATCH может указать позицию ячейки, в которой содержится поисковый термин.

Результат поиска выводится не в виде абсолютной информации о ячейке, а в виде относительной позиции: Результат всегда относится к позиции искомой области. Поиск можно вести как в столбце, так и в строке. Результат поиска показывает, на сколько значений слева (для строки) или сверху (для столбца) расположен критерий поиска.

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

Значение отдельных параметров:

• Критерий поиска: Здесь вы вводите искомое значение. Это может быть либо число, либо текст, который вы заключаете в кавычки.
• Массив поиска: На этом этапе вы вводите диапазон ячеек для поиска. Область от до обозначается двоеточием.
• Тип совпадения: Эта информация является необязательной. Она определяет, как именно должен выполняться поиск. Спецификация -1 находит наименьшее значение, которое больше или равно критерию поиска; 1 — наибольшее значение, которое меньше или равно критерию поиска; 0 — просто ищет точное совпадение.

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

Также можно задать массив поиска не в виде ссылок на ячейки, а записать значения непосредственно в ячейку. При этом значения вводятся в фигурных скобках и разделяются точкой с запятой.

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

Предположим, вы создали таблицу с тремя столбцами. В среднем столбце находятся имена. Теперь в этом столбце можно найти конкретного человека, выполнив точный поиск (тип соответствия 0) по тексту (в кавычках) в функции МАТЧ.

Результат подскажет вам, где находится Саймон в области поиска. Если критерии поиска не находятся в указанном диапазоне, Excel выводит ошибку #N/A.

Преимущества функции MATCH проявляются, прежде всего, в сочетании с другими формулами Excel. Вложенность позволяет передавать результат MATCH в другую функцию.

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

В нашем примере мы хотим узнать, какой ключ принадлежит Саймону. Вместо того чтобы вводить «2» в качестве индекса столбца, можно использовать функцию MATCH для определения позиции. Это также позволяет создавать динамические формулы. Критерии поиска затем встраиваются в функции через ссылки на ячейки.

Термин поиска находится в ячейке F8, а столбец, из которого должен быть получен результат, — в F9. Для упрощения работы можно также ввести выпадающий список. Функция MATCH обращается к поисковому запросу в ячейке F9 и ищет его в верхнем столбце. Функция передает результат в VLOOKUP, где он становится индексом столбца. Преимущество: если вы хотите теперь найти результат в другом столбце, функция динамически адаптируется.

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

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