ПОИСКПОЗ в Excel и Google Таблицах¶
ПОИСКПОЗ (англ. MATCH) — функция электронных таблиц, которая возвращает относительную позицию (номер) элемента в одномерном диапазоне или массиве, соответствующего заданному значению. Относится к категории функций поиска и ссылок. В Microsoft Excel и Google Таблицах применяется как самостоятельно, так и в связке с функцией ИНДЕКС (INDEX), образуя одну из самых распространённых конструкций для гибкого поиска данных.
¶Назначение и синтаксис
Функция решает задачу: «под каким номером в списке находится нужное значение». В отличие от ВПР (VLOOKUP), которая ищет значение и сразу возвращает данные из соседнего столбца, ПОИСКПОЗ возвращает только числовую позицию. Это делает её универсальным «навигатором»: зная номер строки, можно извлечь любое поле через ИНДЕКС.
Синтаксис (русские имена аргументов):
`` ПОИСКПОЗ(искомое_значение; просматриваемый_массив; [тип_сопоставления]) ``
- искомое_значение — число, текст, дата или ссылка на ячейку, которые нужно найти.
- просматриваемый_массив — одномерный диапазон (строка или столбец) либо массив.
- тип_сопоставления — необязательный аргумент, задающий логику поиска; по умолчанию равен 1.
В английской версии функция называется MATCH, аргументы — lookup_value, lookup_array, match_type.
¶Типы сопоставления
Третий аргумент определяет поведение функции и часто становится источником ошибок.
| Значение | Название | Логика работы |
|---|---|---|
| 1 (или опущен) | Приблизительное, «наибольшее ≤» | Массив должен быть отсортирован по возрастанию; возвращается позиция наибольшего значения, не превышающего искомое |
| 0 | Точное совпадение | Порядок не важен; возвращается позиция первого точного совпадения |
| −1 | Приблизительное, «наименьшее ≥» | Массив должен быть отсортирован по убыванию; возвращается позиция наименьшего значения, не меньшего искомого |
На практике чаще всего используют тип 0 — точный поиск. Типы 1 и −1 применяются для работы с интервальными шкалами (например, при определении налоговой ставки или тарифного разряда по величине дохода).
¶Примеры использования
Пусть в диапазоне A2:A6 находятся фамилии: Иванов, Петров, Сидоров, Кузнецов, Смирнов.
=ПОИСКПОЗ("Сидоров"; A2:A6; 0)вернёт 3 — Сидоров третий в списке.=ПОИСКПОЗ("Смирнов"; A2:A6; 0)вернёт 5.=ПОИСКПОЗ("Орлов"; A2:A6; 0)вернёт ошибку #Н/Д, поскольку такого значения нет.
Числовой пример с типом 1: диапазон B1:B5 содержит пороги 0, 100, 500, 1000, 5000. Формула =ПОИСКПОЗ(750; B1:B5; 1) вернёт 3, так как 500 — наибольшее значение, не превышающее 750.
¶Связка с функцией ИНДЕКС
Классический приём — комбинация ИНДЕКС и ПОИСКПОЗ. Функция ИНДЕКС(массив; номер_строки; [номер_столбца]) возвращает значение по координатам, а ПОИСКПОЗ вычисляет нужную строку:
`` =ИНДЕКС(C2:C100; ПОИСКПОЗ("Петров"; A2:A100; 0)) ``
Преимущества перед ВПР:
- поиск возможен в любом направлении — влево и вправо от ключевого столбца;
- можно динамически выбирать столбец для вывода;
- устойчивость к вставке и удалению столбцов внутри таблицы;
- меньшая зависимость от структуры исходных данных.
¶Особенности и ограничения
- Одномерность. Просматриваемый массив должен быть одной строкой или одним столбцом. Для двумерного поиска применяют два вызова ПОИСКПОЗ — отдельно по строкам и столбцам.
- Точность текста. При типе 0 регистр не учитывается, но лишние пробелы и невидимые символы приводят к ошибке #Н/Д. Для очистки используют СЖПРОБЕЛЫ (TRIM).
- Подстановочные знаки. При типе 0 допустимы символы
?(один любой символ) и*(любая последовательность), что позволяет искать по частичному совпадению. - Сортировка. При типах 1 и −1 несоблюдение порядка сортировки даёт неверный, но не ошибочный результат — это типичный источник скрытых ошибок в расчётах.
- Ошибки. #Н/Д означает отсутствие значения; #ЗНАЧ! — несовместимые типы данных.
¶Применение в России
Функция входит в стандартный набор электронных таблиц, изучаемых в курсах информатики и офисных программ в российских школах, вузах и на курсах повышения квалификации. Используется в бухгалтерском учёте, кадровом делопроизводстве, логистике, аналитике продаж, при построении сводных отчётов и автоматизации расчётов. Аналогичные функции присутствуют в LibreOffice Calc (ПОИСКПОЗ) и в отечественных офисных пакетах, поддерживающих формат XLSX, что обеспечивает совместимость формул при переносе файлов.
¶Связанные функции
- ВПР / ГПР — прямой поиск с возвратом значения из указанного столбца или строки.
- ПРОСМОТРX (XLOOKUP) — современная функция, объединяющая возможности ИНДЕКС и ПОИСКПОЗ; доступна в новых версиях Excel и в Google Таблицах.
- ПОИСКПОЗX (XMATCH) — расширенная версия с поиском в обратном направлении и режимами бинарного поиска.
- СЧЁТЕСЛИ — подсчёт совпадений, часто применяется вместе с ПОИСКПОЗ для проверки уникальности.
Источники: официальная документация Microsoft Office по функциям ПОИСКПОЗ и ИНДЕКС; справочная система Google Таблиц; учебные пособия по информатике и работе с электронными таблицами.