В excel поискпоз

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Точный поиск

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

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

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

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

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

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

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

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

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

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

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

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

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

Как используется функция ПОИСКПОЗ в Excel

Добрый день!

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

Итак, знакомство начнём прямо сейчас, и первым шагом будет, знакомство с орфографией функции ПОИСКПОЗ. Эта функция ищет значение указанное вами и возвращает позицию полученного значения в диапазоне. К примеру, у нас есть диапазон B1:B3 в котором прописаны значения: Январь, Февраль, Март и последующая формула должна вернуть цифру 2, поскольку значение «Февраль», является вторым элементом в перечне.

=ПОИСКПОЗ(«Февраль»;B2:B13;0)

Синтаксис функции ПОИСКПОЗ

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

ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления]), где:

• «Искомое_значение» — этот аргумент отвечает за данные, которые вы ищите. Этими данными могут быть чиста, текст, любое логическое значение или просто ссылка на ячейку.
• «Просматриваемый_массив» — это аргумент показывает диапазон ячеек, где будет производиться поиск;
• «Тип_сопоставления» — этот аргумент позволяет узнать функции ПОИСКПОЗ, о том, какое совпадение искать: приблизительное или точное:

• 1 или же без аргумента – будет искать максимальное значение, которое равно или же меньше искомого. В обязательном порядке массив, который просматривает функция ПОИСКПОЗ, вы должны упорядочить по возрастанию, от меньшего к большему.
• – возвращает первое же значение, которое соответствует искомому. Этот аргумент позволяет произвести точный поиск.
• -1 – этот аргумент найдет самое наименьшее значение, которое равняется или больше, нежели значение, которое ищете. В этом случае данные нужно упорядочить по убыванию от большого к малому.

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

Преимущества функции ПОИСКПОЗ

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

Вторым преимуществом является возможность удалять и добавлять столбики в таблицу поиска. Я имею в виду, безопасность этого процесса для работы функции ПОИСКПОЗ, удаление не влияет на корректность работы формулы. А вот формула ВПР может возвращать ошибки или попросту не работать, так как синтаксис формулы требует указывать весь диапазон, а также номер столбика для извлечения значения.

Третьим преимуществом – это отсутствие ограничение на длину значения, которое ищет функции ПОИСКПОЗ, в 255 символов. Ограничение для нее нет. А в функции ВПР, значение, которое вы ищете, и если оно превышает указанную величину, то вы получите ошибку #ЗНАЧ.

Четвёртое преимущество – это увеличенная скорость работы. В принципе для тех, кто работает с небольшими таблицами, ускоренная производительность незаметна, а вот уже при тысячах строк и сотнях формул, работа функции ПОИСКПОЗ самостоятельно или в тандеме с функцией ИНДЕКС будет работать, судя по данным в авторитетных изданиях, относительно функции ВПР на 13-15% быстрее. Так как проверка каждого значения в диапазоне значений вызывает отдельно функцию ВПР, а это значит что чем больше данных и формул в массиве, тем более неторопливо работает Excel.

Примеры работы функции ПОИСКПОЗ

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

Найти СРЗНАЧ, МАКС, МИН с помощью комбинации функций ПОИСКПОЗ и ИНДЕКС

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

1. Функция МИН: =ИНДЕКС($C$2:$C$9;ПОИСКПОЗ(МИН($I$2:I$9);$I$2:I$9;0));
2. Функция МАКС: =ИНДЕКС($C$2:$C$9;ПОИСКПОЗ(МАКС($I$2:I$9);$I$2:I$9;0));
3. Функция СРЗНАЧ: =ИНДЕКС($C$2:$C$9;ПОИСКПОЗ(СРЗНАЧ($I$2:I$9);$I$2:I$9;1))

Каждая функция возвращает нужное нам значение.

Поиск функцией ПОИСКПОЗ с левой стороны

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

Рассмотрим формулу, более, подробнее. Во-первых, формула ПОИСКПОЗ(«Беларусь»;$D$2:$D$9;0) находит положение страны в списке. Во-вторых, функцией ИНДЕКС диапазона $I$2:$I$9 с которого будет производиться извлечение значения. В-третьих, соединим все две части формулы и получим нужный нам результат.

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

Производим поиск по нескольким критериям

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

Следующая формула решит нашу проблему:

Если разобрать детально, эту более сложную формулу, то описать можно так: начнем с функции ПОИСКПОЗ, ищем мы значение 1, а массив нашего поиска, является результатом умножения, а именно, берется значение в первом столбике «Страна» в Таблица1 и сравниваем с именами всех стран в Таблице2. Если было найдено совпадение, формула возвращает 1 или ИСТИНА, а если нет, то или ЛОЖЬ. После делаем то же самое для столбика «Продукты». После всего этого перемножаем результаты и если совпадения найдены результат будет 1, если же нет, то получим – . Итак, функция ПОИСКПОЗ возвращает позицию в случае выполнения обоих критериев. Обязательно используем в формуле третий аргумент «3», так как была указана вся таблица и нужно уточнение, из какого же столбика нужно извлечь значения, в нашем случае столбик был третьим. Еще замечу, что это формула массива, поэтому мы заключаем ее в фигурные скобки и вводим «горячей» комбинацией Ctrl+Shift+Enter. Я думаю, что эти 3 примера, как используется функция ПОИСКПОЗ, поможет вам лучше понять эту функцию и научитесь ее использовать в своей работе.

Если у вас есть предложения, пишите комментарии, буду рад помочь!

Кто-то может сидеть в тени сегодня только потому, что кто-то в свое время посадил дерево.
Уоррен Баффетт

ПОИСКПОЗ в Excel

Автор: Неструев Саня · Опубликовано 15.02.2019 · Обновлено 15.02.2019

Microsoft Excel – мощный аналитический инструмент и средство для работы с таблицами. Изучив доступные функции и команды, вы сможете выполнять сложные операции и оптимизировать большие базы данных. В данной инструкции пойдет речь о функции ПОИСКПОЗ, ее назначении и комбинациях с другими командами.

Мы рассмотрим следующие темы:

• назначение и описание ПОИСКПОЗ;
• синтаксис формулы;
• пошаговая инструкция по набору;
• примеры использования;
• комбинации с другими командами в «Экселе».

Назначение ПОИСКПОЗ

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

На английском написание команды в «Эксель» выглядит как MATCH. Чаще всего используется в качестве вспомогательной функции вкупе с ИНДЕКС, ВПР, ВЫБОР и другими. Однако мы рассмотрим и одиночное использование.

Правильное написание

Формула включает в себя следующие элементы:

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

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

Если вам необходимо одиночное использование функции, то она должна иметь строго указанный вид. В противном случае вы можете получить неверный результат или ошибку Н/Д.

Типы сопоставления

Рассмотрим подробнее разницу между несколькими типами сопоставления:

• 1 или без указания данного параметра – определяет наибольшее значение, которое максимально приближено к указанному в формуле;
• 0 – первое значение в диапазоне, которое равно искомому;
• -1 – наименьшее значение относительно искомого.

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

Ввод команды в Excel

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

1. Сверху над основной таблицей находим иконку функции и жмем ее.

1. В открывшемся окне нужно выбрать категорию «Ссылки и массивы», затем найти нужный вариант в списке и нажать «ОК».

1. Теперь появятся поля для ввода данных. Заполнять их можно вручную или указывать с помощью выделения ячеек таблицы.

Для редактирования формулы можно использовать верхнюю строку. С помощью кнопки креста удаляется введенная строка.

Примеры использования

Теперь рассмотрим несколько примеров использования ПОИСКПОЗ. Ниже представлены основные сценарии, когда может пригодиться команда.

Поиск по одному критерию

Начнем с простого варианта – это поиск с одним искомым значением. Работает по строкам и по столбцам. Также можно использовать массив в виде таблицы. Для нахождения позиции значения делаем следующее:

1. Попробуем найти позицию значения 4. Для этого вписываем команду ПОИСКПОЗ и в скобках указываем адрес ячейки с цифрой. Вы можете вписать само значение, но тогда придется менять формулу каждый раз. А если указать ссылку, то пользователь сможет автоматизировать процесс.

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

1. Теперь указываем тип сопоставления. Если хотим найти точное совпадение, вписываем 0.

1. Жмем клавишу Enter и видим готовый результат.

1. Если поменять значение в ячейке, ссылка на которую указана в начале формулы, то итоговое значение сменится.

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

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

Поиск по двум критериям

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

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

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

Теперь нужно указать два диапазона – столбцы с марками и датами. Для этого выделяем первый и после знака & выделяем второй столбец. В конце ставим 0 для поиска точного значения.

Для вычисления позиции необходимо воспользоваться комбинацией Ctrl + Shift + Enter . Так вы запустите выполнение функции в массиве.

Взаимодействие с другими операторами

Теперь рассмотрим пример использования функции ПОИСКПОЗ с оператором ИНДЕКС. На практике ее можно комбинировать со многими другими функциями, если знать, по каким принципам она работает.

ИНДЕКС – функция поиска совпадений по двум параметрам. Данная формула является аналогом вышеописанного примера, когда необходимо вернуть значение по нескольким условиям. ИНДЕКС используется только в случае двумерного поиска, в то время, когда ВПР, ГПР или ПРОСМОТР предназначены для одномерного.

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

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

Последний аргумент – еще одна ПОИСКПОЗ, но теперь с ссылками на название марки и строкой с автомобилями:

Жмем на Enter и видим результат в соответствующей ячейке. Проверяем по нашей таблице.

Теперь можно менять значения в нашей таблице справа и получать новые значения:

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

По представленному примеру описываемый оператор может использоваться во многих формулах. Например:

• ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ));
• ЕСЛИ(ЕНД(ПОИСКПОЗ(ИСТИНА;(СОВПАД))));
• ИНДЕКС(ПОИСКПОЗ(МИН(ЕСЛИ))) и другие.

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

Видеоинструкция

Подробно и наглядно про данную возможность Excel рассказывается в представленном видеоролике.

Заключение

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

Функция ПОИСКПОЗ() в MS EXCEL

Функция ПОИСКПОЗ( ) , английский вариант MATCH(), возвращает позицию значения в диапазоне ячеек. Например, если в ячейке А10 содержится значение «яблоки», то формула =ПОИСКПОЗ («яблоки»;A9:A20;0) вернет 2, т.е. искомое значение «яблоки» содержится во второй ячейке диапазона A9:A20:А9 — первая ячейка (предполагается, что в ней не содержится значение «яблоки»), А10 — вторая, А11 — третья и т.д. (подсчет позиции производится от верхней ячейки).

Функция ПОИСКПОЗ() возвращает позицию искомого значения, а не само значение. Например: ПОИСКПОЗ(«б»;<"а";"б";"в";"б">;0) возвращает число 2 — относительную позицию буквы «б» в массиве <"а";"б";"в";"б">. Позиция второй буквы «б» будет проигнорирована, функция вернет позицию только первой буквы. О том как вернуть ВСЕ позиции искомого значения читайте ниже в разделе Поиск позиций ВСЕХ текстовых значений, удовлетворяющих критерию.

Синтаксис функции

ПОИСКПОЗ(искомое_значение; просматриваемый_массив; тип_сопоставления)

Искомое_значение — значение, используемое при поиске значения в просматриваемом_массиве. Искомое_значение может быть значением (числом, текстом или логическим значением (ЛОЖЬ или ИСТИНА)) или ссылкой на ячейку, содержащую число, текст или логическое значение.

Просматриваемый_массив — непрерывный диапазон ячеек, возможно, содержащих искомые значения. Просматриваемый_массив может быть только одностолбцовым диапазоном ячеек, например А9:А20 или диапазоном, расположенным в одной строке, например, А2:Е2. Таким образом формула =ПОИСКПОЗ(«слива»;A30:B33;0) работать не будет (выдаст ошибку #Н/Д), так как Просматриваемый_массив представляет собой диапазон ячеек размещенный одновременно в нескольких столбцах и нескольких ячейках.

Тип_сопоставления — число -1, 0 или 1. Тип_сопоставления указывает, как MS EXCEL сопоставляет искомое_значение со значениями в аргументе просматриваемый_массив.

• Если тип_сопоставления равен 0, то функция ПОИСКПОЗ() находит первое значение, которое в точности равно аргументу искомое_значение. Просматриваемый_массив может быть не упорядочен.
• Если тип_сопоставления равен 1, то функция ПОИСКПОЗ() находит наибольшее значение, которое меньше либо равно, чем искомое_значение. Просматриваемый_массив должен быть упорядочен по возрастанию: . -2, -1, 0, 1, 2, . A-Z, ЛОЖЬ, ИСТИНА. Если тип_сопоставления опущен, то предполагается, что он равен 1.
• Если тип_сопоставления равен -1, то функция ПОИСКПОЗ() находит наименьшее значение, которое больше либо равно чем искомое_значение. Просматриваемый_массив должен быть упорядочен по убыванию: ИСТИНА, ЛОЖЬ, Z-A, . 2, 1, 0, -1, -2, . и так далее.

Функция ПОИСКПОЗ() не различает РеГИстры при сопоставлении текстов.

Если функция ПОИСКПОЗ() не находит соответствующего значения, то возвращается значение ошибки #Н/Д.

Поиск позиции в массивах с текстовыми значениями

Произведем поиск позиции в НЕ сортированном списке текстовых значений (диапазон B7:B13)

Столбец Позиция приведен для наглядности и не влияет на вычисления.

Формула для поиска позиции значения Груши: =ПОИСКПОЗ(«груши»;B7:B13;0)

Формула находит первое значение сверху и выводит его позицию в диапазоне, второе значение Груши учтено не будет.

Чтобы найти номер строки, а не позиции в искомом диапазоне, можно записать следующую формулу: =ПОИСКПОЗ(«груши»;B7:B13;0)+СТРОКА($B$6)

Если искомое значение не обнаружено в списке, то будет возвращено значение ошибки #Н/Д. Например, формула =ПОИСКПОЗ(«грейпфрут»;B7:B13;0) вернет ошибку, т.к. значения «грейпфрут» в диапазоне ячеек B7:B13 нет.

В файле примера можно найти применение функции при поиске в горизонтальном массиве.