Поиск в Эксель с использование функции «ПОИСКПОЗ» и «ИНДЕКС»

При использовании формулы важно понимать ее особенностей и точно соблюдать синтаксис.

Сама формула выглядит так: ПОИСКПОЗ (Значение_для_поиска; ПромежутокЗначений; Сопоставление).

Расшифруем ее параметры:

1. «Значение_для_поиска» — искомый аргумент (число, слово, символ и т.п.).
2. «ПромежутокЗначений» — массив из ячеек, в которых происходит поиск.
3. «Сопоставление» — задает соответствие, по которому будет происходить поиск:
   • «1»– поиск происходит до наибольшего. Массив при этом, должен быть упорядочен по возрастанию;
   • «0»– для точного поиска;
   • «-1»– противоположно «1», проводит поиск наименьшего. Массив должен быть упорядочен по убыванию.

=INDEX (array, row_num, [col_num]) – английская версия

=INDEX (array, row_num, [col_num], [area_num]) – английская версия

=ИНДЕКС(массив; номер_строки; [номер_столбца]) – русская версия

=ИНДЕКС(ссылка; номер_строки; [номер_столбца]; [номер_области]) – русская версия

• array (массив) – диапазон ячеек или массив данных для поиска;
• row_num (номер_строки) – номер строки, в которой находятся искомые данные;
• [col_num] ([номер_столбца])(необязательный аргумент) – номер колонки, в которой находятся искомые данные. Этот аргумент необязательный. Но если в аргументах функции не указаны критерии для row_num (номер_строки), необходимо указать аргумент col_num (номер_столбца);
• [area_num] ([номер_области]) – (необязательный аргумент) – если аргумент массива состоит из нескольких диапазонов, то это число будет использоваться для выбора всех диапазонов.

Использование индекса и MATCH вместо ВРОТ

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

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

Дополнительные примеры использования индексов и MATCH вместо В ПРОСМОТР см. в статье билла Https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ Билла Джилена (Bill Jelen), MVP корпорации Майкрософт.

Применение оператора ПОИСКПОЗ

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

Синтаксис оператора ПОИСКПОЗ выглядит так:

Теперь рассмотрим каждый из трех этих аргументов в отдельности.

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

«Просматриваемый массив» – это адрес диапазона, в котором расположено искомое значение. Именно позицию данного элемента в этом массиве и должен определить оператор ПОИСКПОЗ.

«Тип сопоставления» указывает точное совпадение нужно искать или неточное. Этот аргумент может иметь три значения: «1», «0» и «-1». При значении «0» оператор ищет только точное совпадение. Если указано значение «1», то в случае отсутствия точного совпадения ПОИСКПОЗ выдает самый близкий к нему элемент по убыванию. Если указано значение «-1», то в случае, если не обнаружено точное совпадение, функция выдает самый близкий к нему элемент по возрастанию. Важно, если ведется поиск не точного значения, а приблизительного, чтобы просматриваемый массив был упорядочен по возрастанию (тип сопоставления «1») или убыванию (тип сопоставления «-1»).

Аргумент «Тип сопоставления» не является обязательным. Он может быть пропущенным, если в нем нет надобности. В этом случае его значение по умолчанию равно «1». Применять аргумент «Тип сопоставления», прежде всего, имеет смысл только тогда, когда обрабатываются числовые значения, а не текстовые.

В случае, если ПОИСКПОЗ при заданных настройках не может найти нужный элемент, то оператор показывает в ячейке ошибку «#Н/Д».

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

Способ 1: отображение места элемента в диапазоне текстовых данных

Давайте рассмотрим на примере самый простой случай, когда с помощью ПОИСКПОЗ можно определить место указанного элемента в массиве текстовых данных. Узнаем, какую позицию в диапазоне, в котором находятся наименования товаров, занимает слово «Сахар».

Выделяем ячейку, в которую будет выводиться обрабатываемый результат. Щелкаем по значку «Вставить функцию» около строки формул.

Так как нам нужно найти позицию слова «Сахар» в диапазоне, то вбиваем это наименование в поле «Искомое значение».

В поле «Просматриваемый массив» нужно указать координаты самого диапазона. Его можно вбить вручную, но проще установить курсор в поле и выделить этот массив на листе, зажимая при этом левую кнопку мыши. После этого его адрес отобразится в окне аргументов.

В третьем поле «Тип сопоставления» ставим число «0», так как будем работать с текстовыми данными, и поэтому нам нужен точный результат.

Способ 2: автоматизация применения оператора ПОИСКПОЗ

Выше мы рассмотрели самый примитивный случай применения оператора ПОИСКПОЗ, но даже его можно автоматизировать.

Для удобства на листе добавляем ещё два дополнительных поля: «Заданное значение» и «Номер». В поле «Заданное значение» вбиваем то наименование, которое нужно найти. Пусть теперь это будет «Мясо». В поле «Номер» устанавливаем курсор и переходим к окну аргументов оператора тем же способом, о котором шел разговор выше.

Способ 3: использование оператора ПОИСКПОЗ для числовых выражений

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

Ставится задача найти товар на сумму реализации 400 рублей или самый ближайший к этой сумме по возрастанию.

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

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

Способ 4: использование в сочетании с другими операторами

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

При этом, если массив одномерный, то можно использовать только один из двух аргументов: «Номер строки» или «Номер столбца».

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

Давайте взглянем, как это можно сделать на практике, используя всю ту же таблицу. У нас стоит задача вывести в дополнительное поле листа «Товар» наименование товара, общая сумма выручки от которого равна 350 рублям или самому близкому к этому значению по убыванию. Данный аргумент указан в поле «Приблизительная сумма выручки на листе».

Отсортировываем элементы в столбце «Сумма выручки» по возрастанию. Для этого выделяем необходимый столбец и, находясь во вкладке «Главная», кликаем по значку «Сортировка и фильтр», а затем в появившемся меню кликаем по пункту «Сортировка от минимального к максимальному».

В поле «Номер строки» будет располагаться вложенная функция ПОИСКПОЗ. Её придется вбить вручную, используя синтаксис, о котором говорится в самом начале статьи. Сразу записываем название функции – «ПОИСКПОЗ» без кавычек. Затем открываем скобку. Первым аргументом данного оператора является «Искомое значение». Оно располагается на листе в поле «Приблизительная сумма выручки». Указываем координаты ячейки, содержащей число 350. Ставим точку с запятой. Вторым аргументом является «Просматриваемый массив». ПОИСКПОЗ будет просматривать тот диапазон, в котором находится сумма выручки и искать наиболее приближенную к 350 рублям. Поэтому в данном случае указываем координаты столбца «Сумма выручки». Опять ставим точку с запятой. Третьим аргументом является «Тип сопоставления». Так как мы будем искать число равное заданному или самое близкое меньшее, то устанавливаем тут цифру «1». Закрываем скобки.

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

Мы рады, что смогли помочь Вам в решении проблемы.

Помимо этой статьи, на сайте еще 12447 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Exceltip

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

Функция ВПР()

Предположим, у вас есть таблица с данными о работниках. В первой колонке хранится табельный номер сотрудника, в остальных – другие данные (ФИО, отдел и т.д.). Если у вас есть табельный номер, то можно воспользоваться функцией ВПР, чтобы вернуть определенную информацию о сотруднике. Синтаксис формулы =ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр]). Она говорит Excel: «Найди в таблице строку, первая ячейка которой совпадает с искомым_значением, и верни значение ячейки с порядковым номером номер_столбца».

Но случаются ситуации, когда у вас есть имя сотрудника и необходимо вернуть табельный номер. На рисунке в ячейке A10 – имя работника и требуется определить табельный номер в ячейке B10.

Когда ключевое поле находится правее данных, которые вы хотите получить, ВПР не поможет. Если, конечно, была бы возможность задать номер_столбца -1, тогда проблем бы не было. Одним из распространенных решений является добавление нового столбца A, копирование имен сотрудников в этот столбец, заполнить табельные номера с помощью ВПР, сохранить их как значения и удалить временную колонку A.

Функция ИНДЕКС()

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

Начнем с функции ИНДЕКС. Кошмарное название. Когда кто-нибудь говорит «индекс», у меня в голове не возникает ни единой ассоциации, чем же занимается эта функция. А требует она целых три аргумента: =ИНДЕКС(массив; номер_строки; [номер_столбца]).

Говоря по-простому, Excel идет в массив данных и возвращает значение, находящееся на пересечении указанной строки и столбца. Как будто бы просто. Таким образом, формула =ИНДЕКС($A$2:$C$6;4;2) вернет значение, находящееся в ячейке B5.

Применительно к нашей проблеме, чтобы вернуть табельный номер работника, формула должна выглядеть следующим образом =ИНДЕКС($A$2:$A$6;?;1). Выглядит как бессмыслица, но если мы заменим знак вопроса формулой ПОИСКПОЗ, у нас есть решение.

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

Синтаксис этой функции таков: =ПОИСКПОЗ(искомое_значение; просматриваемы_массив; [тип_сопоставления]).

Она говорит Excel: «Найди искомое_значение в массиве данных и верни номер строки массива, в которой это значение встречается». Таким образом, чтобы найти в какой строке находиться имя сотрудника в ячейке A10, необходимо прописать формулу =ПОИСКПОЗ(A10; $B$2:$B$6; 0). Если в ячейке A10 будет имя «Колин Фарел», тогда ПОИСКПОЗ вернет 5-ю строку массива B2:B6.

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

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

Как используется функция ПОИСКПОЗ в 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 примера, как используется функция ПОИСКПОЗ, поможет вам лучше понять эту функцию и научитесь ее использовать в своей работе.

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

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

Функция МОДА.ОДН

Статистическая функция МОДА. ОДН и ее устаревший вариант МОДА находит максимально часто встречающееся значение в диапазоне (массиве) данных и возвращает это значение.

Синтаксис функции: =МОДА((число1;[число2];…), где

• число 1 – это обязательный аргумент, представляющий собой число , ссылку на числовую ячейку, массив или диапазон ячеек.
• число 2 – необязательный аргумент. Таких аргументов может быть от 1 до 255.

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

Для чего можно использовать эту функцию в финансовых расчетах? Например, для того, чтобы на основании сводных данных выяснить, какие товары покупаются чаще всего. Критерием отбора может быть цена изделия, размер, объем, габариты и так далее. В качестве иллюстрации рассмотрим таблицу, в которой отражены продажи товаров с указанием даты продажи, размера изделия и его цены. Для того, чтобы выяснить, каких размеров изделия продаются чаще всего, используем формулу: =МОДА(C2:C6)

По рисунку видно, что изделия с размером 36 пользуются наибольшим спросом. Таким образом, функция МОДА определяет наиболее часто случающееся событие в диапазоне событий.

Таким образом, использование финансовых функций офисного приложения Excel существенно облегчает проведение финансово-коммерческих расчетов по кредитам и займам, финансово-инвестиционный анализ и работу с ценными бумагами. Значительным достоинством приложения является возможность установить пароль для защиты важных документов. Это предотвращает несанкционированный доступ третьих лиц к финансово-экономическим отчетам. В случае его потери, можно найти забытый пароль к Excel документам при помощи специальных программ.