Поиск позиции элемента в списке с ПОИСКПОЗ (MATCH)

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

Синтаксис этой функции следующий:

=ПОИСКПОЗ(Что_ищем; Где_ищем; Режим_поиска)

где

  • Что_ищем - это значение, которое надо найти
  • Где_ищем - это одномерный диапазон или массив (строка или столбец), где производится поиск
  • Режим_поиска - как мы ищем: точно (0), с округлением в большую строну (-1) или в меньшую сторону (1)

Давайте рассмотрим несколько полезных вариантов ее применения на практике.

Точный поиск

Классический сценарий - поиск точного текстового совпадения для нахождения позиции нужного нам текста или числа в списке:

ПОИСКПОЗ в Excel

Поиск первой или последней текстовой ячейки

Если в качестве искомого значения задать звездочку, то функция будет искать первую ячейку с текстом и выдавать её позицию. Для поиска последней текстовой ячейки можно изменить третий аргумент Режим_поиска с нуля на минус 1:

Поиск первого или последнего текста

Числа и пустые ячейки в этом случае игнорируются.

Поиск ближайшего числа или даты

Если последний аргумент задать равным 1 или -1, то можно реализовать поиск ближайшего наименьшего или наибольшего числа. Таблица при этом обязательно должна быть отсортирована по возрастанию или убыванию соответственно. В общем и целом, это чем-то похоже на интервальный просмотр у функции ВПР (VLOOKUP), но там возможен только поиск ближайшего наименьшего, а здесь - есть выбор.

Например, нам нужно выбрать генератор из прайс-листа для расчетной мощности в 47 кВт. Если последний аргумент задать равным 1 и отсортировать таблицу по возрастанию, то мы найдем ближайшую наименьшую по мощности модель (Зверь):

ПОИСКПОЗ поиск ближайшего наименьшего числа

Если же третий аргумент равен -1 и таблица отсортирована по убыванию, то мы найдем ближайшую более мощную модель (Бомба):

ПОИСКПОЗ поиск ближайшего наибольшего

Связка функций ПОИСКПОЗ и ИНДЕКС

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

Так, в предыдущем примере получить не номер, а название модели генератора можно очень легко:

ПОИСКПОЗ и ИНДЕКС

Ну, и поскольку Excel внутри хранит и обрабатывает даты как числа, то подобный подход на 100% работает и с датами. Например, мы можем легко определить на каком этапе сейчас находится наш проект:

ПОИСКПОЗ и даты

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

Двумерный поиск с ПОИСКПОЗ и ИНДЕКС

Ссылки по теме



16.03.2017 09:40:36
Для последнего примера, есть еще красивая альтернатива: ВПР + ПОИСКПОЗ, т.е.

=ВПР(H4;A1:E10;ПОИСКПОЗ(H2;A1:E1;0);0)

В сочетании со «списком» из «проверки данных» получается очень крутая штука – можно, например делать динамические «прайс-листы».
23.04.2019 18:10:11
Здравствуйте. Подскажите как задать формулу, чтобы определить конкретную дату, с которой сумма продаж превысила 300 тыс. руб. у конкретного менеджера. Таблица состоит из трех столбцов - ФИО, дата продажи, сумма продажи, а строк более 10000.
29.11.2019 10:37:42
Добрый день.
Комбинация ИНДЕКС+ПОИСКПОЗ очень практичная, бесспорно. Только вот она подтягивает первое найденное значение. Подскажите, пожалуйста, как дополнить формулу, чтобы она складывала все подходящие значения. Формула СУММЕСЛИМН не подходит, т.к. условие поиска выбирается из выпадающего списка и не реально под каждый случай выделять столбец с искомыми данными. Сводную таблицу настроить тоже нет возможности (нельзя менять источник данных). Буду очень благодарна за помощь!
21.08.2020 11:03:13
Добрый день!
Подскажите пожалуйста, почему формула не работает, если ее прописывать на другом листе?


Что я упускаю?
Наверх