No Image

Функция поискпоз в excel примеры

СОДЕРЖАНИЕ
1 просмотров
10 марта 2020

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

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

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

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

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

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

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

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

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

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

Функция ПОИСКПОЗ( ) , английский вариант 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 нет.

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

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

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

Синтаксис

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

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

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

Читайте также:  Два человека за столом

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

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

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

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

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

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

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

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

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

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

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Комментировать
1 просмотров
Комментариев нет, будьте первым кто его оставит

Это интересно
No Image Компьютеры
0 комментариев
No Image Компьютеры
0 комментариев
No Image Компьютеры
0 комментариев
No Image Компьютеры
0 комментариев
Adblock detector