Как работает поискпоз в excel

Функция ПОИСКПОЗ в Excel

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

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

Синтаксис функции выглядит следующим образом:

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

Чтобы было понятнее, давайте рассмотрим на конкретном примере. Предположим у нас есть список городов (диапазон B2:B6 ). Наша задача состоит в том, чтобы находить порядковый номер указанного нами города.

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

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

=ПОИСКПОЗ( D2 ; B2:B6 ;0)

где D2 – искомое_значение (т.е слово “Киев”)

B2:B6 – просматриваемый_массив (т.е список наших городов)

0 – тип_сопоставления, означает, что нам необходимо искать точное значение города.

Теперь в зависимости от того, какой город будет указан в ячейке D2 будет отображаться его порядковый номер из диапазона городов.

В следующих статьях мы рассмотрим с вами действие ПОИСКПОЗ совместно с другими функциями Excel.

Спасибо за внимание.

SirExcel.ru – Безграничные возможности Excel

Пример функции ПОИСКПОЗ в Excel без функции ИНДЕКС

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

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

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

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

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

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

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

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

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

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Функция ПОИСКПОЗ() в 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. В этом случае функция ПОИСКПОЗ() находит наименьшее значение, которое больше либо равно чем искомое значение.

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

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

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

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

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

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

Функция СМЕЩ, функция ИНДЕКС, функция ПОИСКПОЗ в Excel.

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

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

Данная функция позволяет найти порядковый номер элемента в списке. В ней по очереди, разделяя точкой с запятой в русской версии Windows или запятой в русифицированной версии, надо указать такие данные:

• Искомое значение. Это значение, которое мы будем искать в списке. Значение должно быть уникальным, иначе Excel найдет только первое!
• Диапазон списка. Может состоять только из одного столбца или одной сроки. Это важно! Если попробовать выделить две колонки или две строки, программа выдаст ошибку!
• Способ поиска. Тут надо указать вариант, согласно которому приложение выполнит поиск позиции для нашего искомого значения. Тут возможны следующие типы.

1 Будет найдено ближайшее к нашему значение, которое не превышает его, то есть НЕ БОЛЬШЕ нужного. Равным может быть, но не больше. Список должен располагаться ПО ВОЗРАСТАНИЮ.

-1 Excel найдет значение, которое самое близкое к заданному нами, но НЕ МЕНЬШЕ ЕГО. Список должен быть ПО УБЫВАНИЮ.

0 Запустится поиск ТОЧНОГО СООТВЕТСТВИЯ значения из списка заданному нами. Сортировка тут НЕ ТРЕБУЕТСЯ.

Наглядное применение ПОИСКПОЗ.

Посмотрите на скриншот.

Особое внимание обратите вот на что. Во второй и третьей формуле задан тип поиска равный 1 (единице). Однако если в первой из них Excel искал число, которое действительно присутствует в списке, то есть число 50, то он его порядковый номер и указал. А вот числа 68 в списке нет, поэтому он указал порядковый номер значения из списка, которое находится ближе всего к нужному нам, то есть к 68, но его не превышает. А это – число 60.

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

Замечу, что для текстовых списков надо указывать максимально возможное ТЕКСТОВОЕ значение, например, так: «ЯЯЯ». В случае с числовыми данными указывает максимальное ЧИСЛОВОЕ значение, например, 99999999.

Функция ИНДЕКС в Excel и ее особенности.

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

Отметим, что функция индекс, включенная в состав другой функции, начинает срабатывать как часть адреса. В следующем примере Excel рассчитывает сумму за первые 6 месяцев. Информацию о номере последнего месяца он берет из ячейки R4, закрашенной зеленым цветом.

Стоит поменять в ней значение, и результат функции СУММ поменяется.

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

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

Результат работы может выглядеть так, как на рисунке.

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

Функция СМЕЩ в Excel и т онкости ее применения.

. Функция СМЕЩ показывает значение ячейки или диапазона, которые смещен относительно указанного адреса на нужное количество строк и столбцов. Ее синтаксис следующий.

СМЕЩ(1;2;3;4;5)

1. Ссылка, от которой отсчитывается перемещение.
2. На сколько строк надо переместиться.
3. Сколько столбцов надо отсчитать для перемещения.
4. Объем строк в диапазоне, на которые перемещаемся. Указывать не обязательно.
5. Количество столбцов, на которые перемещаемся. Тоже указывать не обязательно.

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

В примере выше происходит перемещение от ячейки G11 сначала на 2 строчки вверх, затем на 4 колонки влево. В найденной ячейки находится число 10, которое и является результатом работы функции.

Вариант же, когда указывается диапазон, на который переходим, чаще используется как исходные данные для другой функции. В примере ниже с помощью функции СМЕЩ вычисляется диапазон, по которому затем рассчитывается сумма. Если быть честным, то данный вариант на практике бесполезен, проще было бы сразу указать столбец Е для вычисления. Однако он хорошо показывает возможности СМЕЩ.

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

Первый вариант создания “левого ВПР”. Комбинируем ИНДЕКС и ПОИСКПОЗ.

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

Данные формулы конечно можно объединить в одну. Если в последней формуле вместо адреса К8 указать ее содержимое, то есть записать ПОИСКПОЗ(K6;C7:C86;0), то результат работы не изменится. Итоговая формула будет уже такой:

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

Второй вариант создания “левого ВПР”. Комбинируем СМЕЩ и ПОИСКПОЗ.

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

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

Особенности связки СМЕЩ и ПОИСКПОЗ

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

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

На этом наше занятие можно считать законченным. Попробуйте сами применить полученные знания. Вначале поработайте на учебных материалах, а уже потом и в рабочих документах. Всем удачи и успехов в использовании Excel !

Related Posts:

• План краткосрочных тренингов
• Как работает функция ВПР в Excel. Особенности применения.
• Консолидация данных в Excel.
• Суммирование в Excel по одному и нескольким условиям.
• Ошибки в формулах Excel и их источники.
• Консолидация данных в Excel. Практические примеры.