Функция поиск позиции в excel

Функция поиск позиции в 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 нет.

Читайте также:  Подключение видеокарты к ноутбуку через райзер

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

Поиск позиции в массиве констант

Поиск позиции можно производить не только в диапазонах ячеек, но и в массивах констант . Например, формула =ПОИСКПОЗ("груши";<"яблоки";"ГРУШИ";"мандарины">;0) вернет значение 2.

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

Если искомое значение точно не известно, то с помощью подстановочных знаков можно задать поиск по шаблону, т.е. искомое_значение может содержать знаки шаблона: звездочку (*) и знак вопроса (?). Звездочка соответствует любой последовательности знаков, знак вопроса соответствует любому одиночному знаку.

Предположим, что имеется перечень товаров и мы не знаем точно как записана товарная позиция относящаяся к яблокам: яблоки или яблоко .

В качестве критерия можно задать "яблок*" и формула =ПОИСКПОЗ("яблок*";B53:B62;0) вернет позицию текстового значения, начинающегося со слова яблок (если она есть в списке).

Подстановочные знаки следует использовать только для поиска позиции текстовых значений и Типом сопоставления = 0 (третий аргумент функции).

Поиск позиций ВСЕХ текстовых значений, удовлетворяющих критерию

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

Рассмотрим список с повторяющимися значениями в диапазоне B66:B72 . Найдем все позиции значения Груши .

Значение Груши находятся в позициях 2 и 5 списка. С помощью формулы массива

можно найти все эти позиции. Для этого необходимо выделить несколько ячеек (расположенных вертикально), в Строке формул ввести вышеуказанную формулу и нажать CTRL+SHIFT+ENTER . В позициях, в которых есть значение Груши будет выведено соответствующее значение позиции, в остальных ячейках быдет выведен 0.

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

Поиск позиции в массивах с Числами

1. Произведем поиск позиции в НЕ сортированном списке числовых значений (диапазон B8:B14 )

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

Найдем позицию значения 30 с помощью формулы =ПОИСКПОЗ(30;B8:B14;0)

Формула ищет точное значение 30. Если в списке его нет, то будет возвращена ошибка #Н/Д.

2. Произведем поиск позиции в отсортированном по возрастанию списке числовых значений (диапазон B31:B37 )

Сортированные списки позволяют искать не только точные значения (их позицию), но и позицию ближайшего значения. Например, в списке на картинке ниже нет значения 45, но можно найти позицию наибольшего значения, которое меньше либо равно, чем искомое значение, т.е. позицию значения 40.

Это можно сделать с помощью формулы =ПОИСКПОЗ(45;B31:B37;1)

Обратите внимание, что тип сопоставления =1 (третий аргумент функции).

3. Поиск позиции в списке отсортированном по убыванию выполняется аналогично, но с типом сопоставления = -1. В этом случае функция ПОИСКПОЗ() находит наименьшее значение, которое больше либо равно чем искомое значение.

Функции ПОИСКПОЗ() и ИНДЕКС()

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

Читайте также:  Где находится черный список в ватсапе

Найдем количество заданного товара на определенном складе. Для этого используем формулу

В файле примера , соответствующий столбец и строка выделены с помощью Условного форматирования .

СОВЕТ: Подробнее о поиске позиций можно прочитать в соответствующем разделе сайта: Поиск позиции .

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

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

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

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

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

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

Точный поиск

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также:  Как узнать качество картинки

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

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

Пример работы ПОИСКПОЗ по двум столбцам Excel

Для примера возьмем список автомобилей из автопарка средней фирмы, как показано ниже на рисунке:

В обеих столбцах названия автомобилей и отделов повторяются, но нет парных дубликатов. Например, в списке 2 автомобиля марки Ford, но оба они из разных отделов. Если мы захотим узнать номер позиции этого автомобиля, то в результате функция ПОИСПОЗ вернет нам позицию в диапазоне где встречается первое значение – 3. То есть Ford из отдела продаж:

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

  1. В ячейку B16 введите значение Ford, а в ячейку C16 название интересующего нас отдела – Маркетинговый.
  2. В ячейку C17 введите функцию со следующими аргументами:
  3. После ввода для подтверждения функции нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter, так как она должна выполнятся в массиве. Если все сделано правильно в строке формул появятся фигурные скобки.

Как видно функция самостоятельно справилась с решением поставленной задачи.

Описание примера как работает функция ПОИСКПОЗ:

Чтобы функция ПОИСКПОЗ работала с таблицей с двумя столбцами как с одним мы использовали в ее аргументах оператор &. Учитывая этот оператор первый аргументом для функции теперь является значение FordМаркетинговый. По этой причине первый Ford из отдела продаж не учитывается, ведь теперь для функции два форда – это разные значения (FordПродажи и FordМаркетинговый). Просматриваемый диапазон теперь распространяется на 2 столбца, так же благодаря оператору &, который мы применяем во втором аргументе для склейки значений из двух смежных диапазонов. Таким образом, значения берутся одновременно из двух столбцов Автомобиль и Отдел.

Ссылка на основную публикацию
Форд экоспорт белый фото
Компания Форд славится тем, что каждое обновление их машин несет в себе кучу перемен. Не стал исключением и недорогой городской...
Установка и настройка ip камеры
Системы видеонаблюдения используются давно. Старые аналоговые решения были дороги и громоздки. Они требовали большого количества дорогостоящего оборудования, квалифицированных специалистов и...
Установка и настройка операционной системы windows
Наши услуги УСТАНОВКА ОПЕРАЦИОННЫХ СИСТЕМ Определения: Операционная система (сокращенно ОС) – комплекс взаимосвязанных программ, предназначенных для управления ресурсами компьютера и...
Форм факторы корпусов пк размеры
Главная FAQ Железо Типы компьютерных корпусов Типы компьютерных корпусов Говоря слово "компьютер" многие подразумевают системный блок компьютера, и в принципе...
Adblock detector