Функция ПОИСКПОЗ в Excel. Назначение и особенности.
В текущем материале рассмотрим, как работает функция ПОИСКПОЗ в Excel. Назначение данной функции – определение порядкового номера элемента в списке. Этот порядковый номер определяют, как индекс элемента, или его позиция в списке. Напомним, что в Excel список – это перечень элементов одного типа. Обычно список располагают в одномерном диапазоне, состоящем из одной строки или одной колонке. Такой диапазон часто называют вектором.
Функция ПОИСКПОЗ имеет три параметра:
- Искомое значение. Именно для этого элемента определяется позиция в списке.
- Просматриваемый массив. Это перечень элементов в виде списка, среди которых надо найти порядковый номер для указанного в первом параметре значения.
- Тип сопоставления. Задает вариант определения позиции для искомого значения. Может быть не указан. Есть три варианта для данного параметра
- -1. Это означает, что программа определит позицию для элемента списка, который наиболее близок к искомому, может быть равен ему, но не может быть меньше. Список должен быть отсортирован по убыванию
- 1. Такой вариант предполагает нахождение позиции для элемента, который максимально близок к заданному, но не больше его. Как и в предыдущем случае, найденный элемент, для которого определена позиция, может совпадать с искомым. Элементы списка должны располагаться по возрастанию. Если третий параметр не указан, то считается, что это – единица.
- 0 Означает, что необходимо определить позицию элемента списка, который совпадает с искомым значением, другими словами – имеется полное соответствие между элементом списка и заданным значением. Список можно не сортировать.
Приведем три примера использования функции ПОИСКПОЗ. Сразу оговоримся, что примеры довольно абстрактные и к реальным расчетам отношения не имеют. Однако они хорошо показывают работу функции. Итак, имеется ряд чисел – от 0 до 30 с шагом арифметической прогрессии, равным 5. Наша задача – определить номер позиции для чисел 20 и 19 с использованием вариантов типа сопоставления 0, 1 и -. Так как число 20 есть в списке, то все три варианты третьего параметра 1 и 0 дадут один и тот же результат – 5. И действительно, число 20 в списке располагается на пятом месте. А вот вариант «-1» даст ошибку, так как список не упорядочен по убыванию.
Теперь попробуем найти позицию для числа 19. Оно отсутствует в списке, поэтому указание в качестве типа сопоставления 0 теперь приводит к ошибке. Вариант с «1» показывает верное значение – 4, так как ближайший к искомому значению элемент списка, не превышающий его, это число 19, а оно действительно находится на 4 месте.
Теперь снова запустим предыдущий поиск, но предварительно отсортируем список по убыванию. Теперь верно отрабатывает вариант с «-1», так ка сейчас ближайшим к заданному значением из списка, которое не меньше его, является число 20, а оно сейчас на третьем месте. Обратите внимание, что вариант»0» дал явную ошибку, а вот вариант «1» ошибку не показал, но тем не менее вывел ошибочное значение, показав номер позиции для последнего элемента.
В чем же назначение и особенности данной функции? Прежде всего еще раз посмотрим как повела себя эта функция при сортировке по убыванию и выборе типа сопоставления равным «1». Это очень важная особенность данной функции. Если задать в качестве искомого значения значений, которое заведомо больше, чем любой элемент списка, то будет найдена позиция последнего элемента.
Рассмотрим несколько вариантов использования ПОИСКПОЗ. Для начала применим ее, как говорится, в лоб. Представим, что есть список организаций, расположенных с убыванием величины ежегодного оборота. Надо определить, имеется ли нужная нам в этом списке, и если да – на каком месте. Применим ПОИСКПОЗ. Укажем в качестве критерия поиска наименование нашей организации, в качестве списка перечень организаций, и тип сопоставления зададим равным 0.
На самом деле конечно такой вариант если и используется, то крайне редко. Гораздо чаще ее применяют в сочетании с другими функциями.
Приведем следующий пример. Необходимо рассчитать сумму заказов в прайс-листе, причем цена заказа зависит от объема.
Конечно, можно применить функцию ЕСЛИ, поверив несколько условий по очереди. Но такой вариант очень длинный и неудобный. Придется применить такую формулу:
=ЕСЛИ(I2
Гораздо проще применить функцию ИНДЕКС в сочетании с ПОИСКПОЗ. Для Функции ИНДЕКС в качестве искомой таблицы задаем диапазон цен для нужного товара, номер строки укажем равным 1, так как цены товара расположены в одной сроке, ну а номер столбца в таблице найдем с помощью ПОИСКПОЗ. Для нее искомым значением будет количество заказанного товара, список зададим в явном виде, причем вначале укажем значение, заведомо превышающее объем однократной покупки, а для остальных значений используем ограничения по объему, взятые из таблицы и расположенные в убывающем порядке. Тип сопоставления укажем «-1»
Получаем формулу:
=ИНДЕКС(E2:H2;1;ПОИСКПОЗ(I2;{999999;100;50;25};-1))*I2
Очевидно, что при изменении условий в варианте с вложенным ЕСЛИ придется добавлять новые условия, а вот в способе с ПОИСКПОЗ достаточно просто поменять список поиска. Да и сама формула выглядит проще и короче.
Теперь предположим такую ситуацию. У нас имеется заполненный табель, в котором указаны значения отработанных дней, рабочие часы, количество больничных, отпускных и так далее.
Необходимо получить для конкретного сотрудника определенное значение, например, количество отработанных дней. Это можно сделать с помощью ВПР, но при большом количестве столбцов это непросто, особенно если необходимо получение различных данных, причем каждый раз для нового сотрудника из списка.
Гораздо проще создать два выпадающих списка. В одном указать список фамилий, в другом – перечень значений из заголовка таблицы табеля, начиная с табельного номера. Теперь номер столбца можно найти с помощью ПОИСКПОЗ. В качестве исходного значения будем задавать вариант из выпадающего списка по данным из заголовка, список – это все значения заголовка, начиная опять-таки с табельного номера, тип сопоставления укажем 0. Данная функция найдет порядковый номер столбца с указанным заголовком, что и требуется. Итоговая формула выглядит так:
=ВПР(B202;B6:AT199;ПОИСКПОЗ(B204;B6:AT6;0);0)
Пример третий. Необходимо получить сумму пяти последних поступлений из таблицы с учетом того, что информация в таблице регулярно обновляется и значения добавляются. Здесь можно воспользоваться функцией СМЕЩ для автоматического формирования диапазона суммирования, а также функцию ПОИСКПОЗ для определения первой ячейки этого диапазона. Применим особенность функции ПОИСКПОЗ с нахождением номера последнего элемента списка при использовании заведомо значения большего, чем значения списка и типа сопоставления «1». Результат на изображении ниже. Формула:
=СУММ(СМЕЩ(B1;ПОИСКПОЗ(“ЯЯЯ”;B:B;1)-5;1;5;1))
Подведем итоги. Функция ПОИСКПОЗ в стандартном виде нужна крайне редко. Гораздо важнее использование данной функции для определения позиции искомого элемента, которую затем можно применить для работы функций связывания таблиц, получения нужного результата и других расчетов. Именно поэтому знание и умение применять данную функцию является важным условием для автоматизации получения нужной информации из таблиц.
На этом наше занятие завершается. Желаем всем успешной работы и хорошего настроения. Ждем вас на наших занятиях в очном формате или в формате онлайн тренингов. Дополнительную информацию можно получить, связавшись с нами по почте или используя данные со страницы контакты. Кроме этого, вы можете скачать учебный файл для отработки изученных на нашем занятии формул. Еще раз желаем успеха!
