Формула поискпоз в excel примеры

Как используется функция ПОИСКПОЗ в Excel

Добрый день!

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

Итак, знакомство начнём прямо сейчас, и первым шагом будет, знакомство с орфографией функции ПОИСКПОЗ. Эта функция ищет значение указанное вами и возвращает позицию полученного значения в диапазоне. К примеру, у нас есть диапазон B1:B3 в котором прописаны значения: Январь, Февраль, Март и последующая формула должна вернуть цифру 2, поскольку значение «Февраль», является вторым элементом в перечне.

=ПОИСКПОЗ(«Февраль»;B2:B13;0)

Синтаксис функции ПОИСКПОЗ

Как видно из примера функция ПОИСКПОЗ имеет следующий синтаксис, который будем рассматривать более, подробнее:

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

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

• 1 или же без аргумента – будет искать максимальное значение, которое равно или же меньше искомого. В обязательном порядке массив, который просматривает функция ПОИСКПОЗ, вы должны упорядочить по возрастанию, от меньшего к большему.
• 0 – возвращает первое же значение, которое соответствует искомому. Этот аргумент позволяет произвести точный поиск.
• -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 или ИСТИНА, а если нет, то 0 или ЛОЖЬ. После делаем то же самое для столбика «Продукты». После всего этого перемножаем результаты и если совпадения найдены результат будет 1, если же нет, то получим – 0. Итак, функция ПОИСКПОЗ возвращает позицию в случае выполнения обоих критериев. Обязательно используем в формуле третий аргумент «3», так как была указана вся таблица и нужно уточнение, из какого же столбика нужно извлечь значения, в нашем случае столбик был третьим. Еще замечу, что это формула массива, поэтому мы заключаем ее в фигурные скобки и вводим «горячей» комбинацией Ctrl+Shift+Enter. Я думаю, что эти 3 примера, как используется функция ПОИСКПОЗ, поможет вам лучше понять эту функцию и научитесь ее использовать в своей работе.

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

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

Функция ПОИСКПОЗ() в 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 – хорошая альтернатива ВПР, ГПР и ПРОСМОТР. Эта связка универсальна и обладает всеми возможностями этих функций. А в некоторых случаях, например, при двумерном поиске данных на листе, окажется просто незаменимой. В данном уроке мы последовательно разберем функции ПОИСКПОЗ и ИНДЕКС, а затем рассмотрим пример их совместного использования в Excel.

Более подробно о функциях ВПР и ПРОСМОТР.

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

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

Например, на рисунке ниже формула вернет число 5, поскольку имя “Дарья” находится в пятой строке диапазона A1:A9.

В следующем примере формула вернет 3, поскольку число 300 находится в третьем столбце диапазона B1:I1.

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

• 0 – функция ПОИСКПОЗ ищет первое значение в точности равное заданному. Сортировка не требуется.
• 1 или вовсе опущено – функция ПОИСКПОЗ ищет самое большое значение, которое меньше или равно заданному. Требуется сортировка в порядке возрастания.
• -1 – функция ПОИСКПОЗ ищет самое маленькое значение, которое больше или равно заданному. Требуется сортировка в порядке убывания.

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

Функция ИНДЕКС в Excel

Функция ИНДЕКС возвращает содержимое ячейки, которая находится на пересечении заданных строки и столбца. Например, на рисунке ниже формула возвращает значение из диапазона A1:C4, которое находится на пересечении 3 строки и 2 столбца.

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

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

Например, следующая формула возвращает пятое значение из диапазона A1:A12 (вертикальный вектор):

Данная формула возвращает третье значение из диапазона A1:L1(горизонтальный вектор):

Совместное использование ПОИСКПОЗ и ИНДЕКС в Excel

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

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

Пускай ячейка C15 содержит указанный нами месяц, например, Май. А ячейка C16 – тип товара, например, Овощи. Введем в ячейку C17 следующую формулу и нажмем Enter:

=ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))

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

В данной формуле функция ИНДЕКС принимает все 3 аргумента:

1. Первый аргумент – это диапазон B2:E13, в котором мы осуществляем поиск.
2. Вторым аргументом функции ИНДЕКС является номер строки. Номер мы получаем с помощью функции ПОИСКПОЗ(C15;A2:A13;0). Для наглядности вычислим, что же возвращает нам данная формула:
3. Третьим аргументом функции ИНДЕКС является номер столбца. Этот номер мы получаем с помощью функции ПОИСКПОЗ(C16;B1:E1;0). Для наглядности вычислим и это значение:

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

=ИНДЕКС(B2:E13;D15;D16)

Как видите, все достаточно просто!

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

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

В этой статье рассматривается синтаксис функции ПОИСКПОЗ Excel, а также как использовать функцию ПОИСКПОЗ в Excel с примерами формул.

Также в этой статье рассматривается комбинация формул ИНДЕКС ПОИСКПОЗ в Excel.

В Microsoft Excel существует множество различных функций поиска, которые могут помочь найти определенное значение в ряде ячеек, а ПОИСКПОЗ — одна из них. ПОИСКПОЗ на английском MATCH , в основном, идентифицирует относительное положение элемента в диапазоне ячеек. Однако функция ПОИСКПОЗ Excel в сочетании с другими функциями может сделать гораздо больше.

Функция ПОИСКПОЗ в Excel – синтаксис и использование

Функция ПОИСКПОЗ в Excel ищет заданное значение в диапазоне ячеек и возвращает относительное положение этого значения.

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

Искомое_значение (обязательный аргумент) – значение, которое вы хотите найти. Это может быть числовое, текстовое или логическое значение, а также ссылка на ячейку.

Просматриваемый_массив (обязательный аргумент) – диапазон ячеек для поиска.

Тип_сопоставления (необязательный аргумент) – определяет тип соответствия. Это может быть одно из следующих значений: 1, 0, -1.

• 1 или отсутствует (по умолчанию) – находит наибольшее значение в массиве, которое меньше или равно значению поиска. Массив поиска должен быть отсортирован в порядке возрастания, от самого маленького до большого или от A до Z.
• 0 – находит первое значение в массиве, которое в точности равно значению поиска. Сортировка не требуется.
• -1 – находит наименьшее значение в массиве, которое больше или равно значению поиска. Массив поиска должен быть отсортирован в порядке убывания, от самого большого до самого маленького или от Z до A.

Чтобы лучше разобраться в данной функции, давайте напишем простую формулу ПОИСКПОЗ, основанную на следующих данных: фамилии студентов в столбце A и их баллы по экзаменам в столбце B, отсортированные от самых высоких до самых низких. Чтобы узнать, где среди других находится конкретный студент (например, студентка Виноградова), используйте эту простую формулу:

=ПОИСКПОЗ(E1; A2:A8; 0)

При желании вы можете поместить значение поиска в ячейку (E1 в данном примере) и сослаться на эту ячейку в формуле ПОИСКПОЗ Excel:

Функция ПОИСКПОЗ в Excel – Пример использования функции ПОИСКПОЗ в Excel

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

Особенности функции ПОИСКПОЗ Excel

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

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

Использование ПОИСКПОЗ в Excel – примеры формул

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

Формула ПОИСКПОЗ Excel с подстановочными знаками

Как и многие другие функции Excel, ПОИСКПОЗ распознает следующие подстановочные знаки:

• Вопросительный знак (?) — заменяет любой символ
• Звездочка (*) — заменяет любую последовательность символов

Примечание . Подстановочные знаки могут использоваться только в формулах ПОИСКПОЗ с параметром тип_сопоставления равному 0.

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

Предположим, что у вас есть список компаний и данные их продаж за последнюю неделю. Вы хотите найти относительное положение определенной компании в списке (отсортированному по объему продаж в порядке убывания), но вы не можете точно вспомнить его имя, хотя вы помните несколько первых букв.

Предполагая, что названия компаний находятся в диапазоне A2:A8, и вы ищете название, начинающееся с «восток», формула выглядит следующим образом:

=ПОИСКПОЗ(«восток*»; A2:A8; 0)

Чтобы сделать нашу формулу ПОИСКПОЗ более универсальной, вы можете ввести значение поиска в любой ячейке (E1 в этом примере) и объединить эту ячейку с подстановочным символом, например:

=ПОИСКПОЗ(E1&»*»; A2:A8; 0)

Как показано на изображении ниже, формула возвращает 2 позицию, которая соответствует компании « Восток-авто »:

Функция ПОИСКПОЗ в Excel – Пример функции ПОИСКПОЗ Excel с подстановочными знаками

Чтобы заменить только один символ в значении поиска, используйте подстановочный оператор «?», например:

Вышеуказанная формула будет соответствовать названию « Бейкер » и вернет его относительное положение, которое равно 5.

Формула ПОИСКПОЗ с учетом регистра

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

Чувствительность к регистру формулы для сопоставления данных в Excel заключается в следующем:

Логика этой формулы такова:

• Функция СОВПАД сравнивает значение поиска с каждым элементом просматриваемого массива. Если сравниваемые ячейки в точности равны, функция возвращает ИСТИНУ, или ЛОЖЬ в противном случае.
• Далее функция ПОИСКПОЗ сравнивает ИСТИНУ (которая является ее искомым_значением) с каждым значением в массиве, возвращаемом СОВПАД, и возвращает позицию первого совпадения

Обратите внимание , что это формула массива, которая требует нажатия Ctrl+Shift+Enter для правильной обработки.

Пусть значение поиска находится в ячейке E1, а массив поиска — A2:A8, тогда формула выглядит следующим образом:

=ПОИСКПОЗ(ИСТИНА; СОВПАД(A2:A8; E1);0)

На следующем изображении демонстрируется формула ПОИСКПОЗ с учетом регистра в Excel:

Функция ПОИСКПОЗ в Excel – Пример функции ПОИСКПОЗ Excel с учетом регистра

Сравнить 2 столбца на совпадения и различия (ЕНД и ПОИСКПОЗ)

Проверка двух списков на совпадения и различия – одна из наиболее распространенных задач в Excel, и это можно сделать различными способами. Использование формулы ЕНД/ПОИСКПОЗ является одним из них:

=ЕСЛИ(ЕНД(ПОИСКПОЗ (1-е значение в Списке 1; Список 2; 0)); «Нет в Списке 1»; «»)

Для любого значения из Списка 2, отсутствующего в Списке 1, формула возвращает «Нет в Списке 1». И вот как это происходит:

• Функция ПОИСКПОЗ выполняет поиск значения из Списка 1 в Списке 2. Если значение найдено, она возвращает относительное положение, в противном случае ошибку #Н/Д.
• Функция ЕНД в Excel выполняет только одно: проверяет наличие ошибок #Н/Д (что означает «недоступно»). Если заданное значение является ошибкой #Н/Д, функция возвращает ИСТИНА, в противном случае – ЛОЖЬ. В нашем случае ИСТИНА означает, что значение из Списка 1 не найдено в Списке 2 (ПОИСКПОЗ возвращает ошибку #Н/Д).
• Поскольку для других пользователей будет сложно интерпретировать результаты в формате ИСТИНА/ЛОЖЬ, то мы будем использовать функцию ЕСЛИ для отображения текста «Нет в Списке 1» для значений, которых нет в Списке 1.

Предположим, что нам необходимо сравнить значения в столбце B со значениями в столбце A, формула примет следующую форму (где B2 — самая верхняя ячейка):

=ЕСЛИ(ЕНД(ПОИСКПОЗ(B2;A:A;0));»Нет в Списке 1″; «»)

Как вы помните, функция ПОИСКПОЗ Excel сама по себе не учитывает регистр. Чтобы заставить ее различать регистр, вставьте функцию СОВПАД в аргумент просматриваемый_массив и не забудьте нажать Ctrl+Shift+Enter , чтобы завершить эту формулу массива :

=ЕСЛИ(ЕНД(ПОИСКПОЗ(ИСТИНА; СОВПАД(A:A;B2); 0));»Нет в Списке 1″; «»)

На следующем изображении демонстрируются обе формулы в действии:

Функция ПОИСКПОЗ в Excel – Сравнение 2 столбцов на совпадения с использованием ЕНД и ПОИСКПОЗ

ИНДЕКС и ПОИСКПОЗ в Excel

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

Синтаксис и использование функции ИНДЕКС

Функция ИНДЕКС Excel возвращает значение в массиве, основанное на указанных вами строках и столбцах. Синтаксис функции ИНДЕКС прост:

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

Вот очень простое объяснение каждого параметра:

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

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

Вот простейший пример формулы ИНДЕКС:

Формула ищет в ячейках от A1 до C10 и возвращает значение ячейки во второй строке и третьем столбце, то есть в ячейке C2.

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

ИНДЕКС ПОИСКПОЗ в Excel пример

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

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

Комбинацию ИНДЕКС ПОИСКПОЗ в Excel можно представить таким образом:

=ИНДЕКС(столбец для возвращения значения, ПОИСКПОЗ (значение поиска, столбец для поиска, 0))

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

Функция ПОИСКПОЗ в Excel – Исходные данные для формулы ИНДЕКС ПОИСКПОЗ в Excel

Найдем численность населения, например, России, используя следующую формулу ИНДЕКС ПОИСКПОЗ:

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

• Функция ПОИСКПОЗ выполняет поиск значения «Россия» в столбце B, точнее в ячейках B2:B11 и возвращает номер 7, потому что «Россия» находится на седьмом месте в списке.
• Функция ИНДЕКС принимает «7» во втором параметре (номер_строки), который указывает, из какой строки вы хотите вернуть значение, и превращается в простую формулу =ИНДЕКС($C$2:$C$11, 7). То есть формула производит поиск в ячейках C2-C11 и возвращает значение ячейки в седьмой строке, то есть ячейке C8, потому что мы начинаем отсчет со второй строки.

И вот результат, который мы получаем в Excel:

Функция ПОИСКПОЗ в Excel – ИНДЕКС ПОИСКПОЗ пример формулы

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

Поиск позиции элемента в списке с ПОИСКПОЗ (MATCH)

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

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

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

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

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

Точный поиск

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

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

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

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

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

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

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

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

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

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

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

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

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