ПОИСКПОЗ и ИНДЕКС в Excel: пример и объяснения

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

Сразу уточню важный момент: ПОИСКПОЗ не возвращает сам товар, цену, фамилию или адрес ячейки. Эта функция возвращает номер позиции внутри выбранного диапазона. Например, если в диапазоне A2:A6 слово «Молоко» стоит третьим по счету, то ПОИСКПОЗ вернет число 3, даже если реальный адрес ячейки будет A4. Из-за этого начинающие часто путаются и думают, что Excel ошибся, хотя функция просто считает позицию внутри массива.

ПРИМЕЧАНИЕ! Если вы только начинаете работать с формулами, советую сначала понять сам принцип ввода формул, ссылок на ячейки и диапазонов. У нас есть отдельный урок – как вставить формулу в Excel. После него будет проще разобраться, почему в формулах используются адреса ячеек, точки с запятой и вложенные функции.

Как работает оператор ПОИСКПОЗ

ПРИМЕЧАНИЕ! Если вам что-то будет в этой главе не понятно, не переживайте, мы еще раз все повторим на примерах. Ваша задача внимательно прочесть эту главу и попытаться уловить саму суть. Саму структуру мы уже разберем далее.

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

Проще всего представить обычную очередь. Первый человек в очереди имеет позицию 1, второй – позицию 2, третий – позицию 3. Excel делает примерно то же самое, только вместо людей у нас ячейки. Если мы ищем слово «Молоко» в списке товаров, функция не говорит «ячейка A4», а говорит «это третий элемент в выбранном диапазоне». Именно поэтому ПОИСКПОЗ чаще всего используют вместе с другими функциями, которым нужен номер строки или столбца.

Теперь давайте рассмотрим синтаксис:

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

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

ОЧЕНЬ ВАЖНО! Самая частая ошибка в ПОИСКПОЗ связана именно с аргументами «1» и «-1». Если вы ставите «1», список должен идти по возрастанию: от меньшего к большему, от А к Я. Если вы ставите «-1», список должен идти по убыванию: от большего к меньшему, от Я к А. Если порядок сортировки неправильный, Excel может вернуть не тот результат или показать ошибку.

Если функция не находит совпадений, то выводит:

#Н/Д

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

Как правило, данная функция не используется отдельно – только вместе с другими функциями для работы с позициями строк, столбцов и массивами. Но для понимания ее все равно лучше сначала разобрать обычный простой пример. Еще один момент: при точном поиске по тексту функция не учитывает регистр букв. Для нее «молоко», «Молоко» и «МОЛОКО» – это одно и то же значение. Если вам нужно различать большие и маленькие буквы, то это уже отдельная задача, где обычно подключают другие функции.

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

Пример 1: Поиск позиции элемента

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

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

3. Ставим «Категорию» – «Ссылки и массивы».
4. Ищем нашу функцию, выделяем ее и жмем «ОК».

5. Теперь уже заполняем значения. В первой строке указываем адрес элемента, который мы хотим найти. Вы можете указать как адрес, кликнув мышкой, так и вписать вручную – например:

“Молоко”

6. Далее ниже указываем диапазон адресов – их можно выбрать мышкой, или вписать вручную. Например:

A2:A6

7. «Тип_сопоставления» – это как раз та самая точность. Так как мы работаем с текстом, то лучше указать полную точность:

0

8. Жмем «ОК».

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

Вот простой способ себя проверить. Посмотрите не на номера строк слева в Excel, а только на выделенный диапазон A2:A6. Внутри этого диапазона первая ячейка A2 – это позиция 1, A3 – позиция 2, A4 – позиция 3. Поэтому результат «3» в нашем случае правильный. Если вы расширите диапазон, например начнете искать не с A2, а с A1, результат может измениться, потому что поменяется начало массива.

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

Пример 2: Поиск товара и использование адресов

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

1. Давайте создадим еще две строки – «Поиск товара» и «Позиция». Первую строку мы будем использовать в качестве исходного значения, которое в любой момент будет меняться. Для примера введем туда название любого элемента из массива.
2. Теперь ставим курсор в поле «Позиция» и вставляем нашу функцию.

3. По сути, мы делаем все то же самое, только вместо «Искомого значения» мы используем адрес соседней ячейки.

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

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

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

Пример 3: Работа с числовыми значениями

С текстом и символами работать куда проще, так как обычно нам нужно найти элементы с точным совпадением. Давайте же посмотрим пример работы с числами и примерными значениями. Наша задача – найти товар с прибылью 30 000 или с максимально приближенным значением.

1. Так как функция работает последовательно и при приблизительном поиске опирается на порядок значений, нам нужно отсортировать колонку. Выделите колонку с цифрами, нажав по букве сверху.
2. Перейдите на вкладку «Главная».

3. Теперь справа в разделе «Редактирование» находим значок «Сортировка и фильтр» и выбираем «Сортировка по убыванию».

4. Оставляем настройку по умолчанию и жмем по кнопке сортировки. Вы увидите, что сортировка произошла также со строками.

5. Выбираем любую ячейку и вставляем в нее функцию.

6. Теперь вставляем в первую строчку наше приближенное значение, которое мы хотим найти. Указываем диапазон массива. И в конце ставим «-1», чтобы попробовать найти не точное совпадение, а ближайшее значение сверху или равное искомому. Напомню: при «-1» массив должен быть отсортирован по убыванию.

7. И мы нашли товар с прибылью, приближенной к 30 000 – им оказалось «Молоко».

А теперь попробуйте взять 2-ой пример с автоматизацией и двумя дополнительными ячейками и применить его к данной ситуации с числами. Еще один момент – если вы сортируете числа по возрастанию, то используем значение «1». На самом деле это самая сложная часть этой функции и нужно будет несколько раз попрактиковаться, чтобы понять, как именно она работает.

КАК ЗАПОМНИТЬ ПРОЩЕ? Для точного поиска почти всегда ставим «0». Для приблизительного поиска по возрастающему списку ставим «1». Для приблизительного поиска по убывающему списку ставим «-1». Если вы не уверены в сортировке, лучше не используйте приблизительный поиск, потому что результат может выглядеть правдоподобно, но быть неправильным.

Есть еще одна частая проблема с числами. Иногда число в ячейке выглядит как число, но на самом деле хранится как текст. Например, вы скопировали таблицу с сайта, и Excel видит «30 000» как текстовую строку, а не как число. В таком случае сортировка и приблизительный поиск могут работать странно. Проверьте формат ячеек, уберите лишние пробелы и убедитесь, что значения действительно являются числами. Если вы часто работаете с форматами, можете отдельно посмотреть материал про формат ячеек в Excel.

Пример 4: Использование с другими функциями

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

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

Если вам пока ничего не понятно, не стоит переживать – сейчас мы все разберем на примере. В нашем примере наша задача вывести не просто позицию в массиве, а наименование товара, которое имеет приближенную сумму к числу 32 000.

1. Давайте теперь попробуем отсортировать сумму по возрастанию – аналогично выделяем весь столбец.

2. Выбираем «Сортировку по возрастанию».

3. В качестве суммы мы будем использовать значение – 32 000. Теперь нам нужно найти товар – вставляем туда функцию.

4. Используем:

ИНДЕКС

5. Нам нужна обычная формула, поэтому оставьте настройки по умолчанию.

6. В качестве массива указываем диапазон товаров.
7. В «Номер строки» нам нужно вписать уже формулу ПОИСКПОЗ. В качестве значения, по которому мы будем искать, указываем соседнюю строку 32 000. Далее указываем диапазон всех сумм. В конце ставим «1», так как мы до этого делали сортировку по возрастанию. «Номер столбца» не указываем, потому что массив товаров у нас состоит из одного столбца.

Далее он выведет правильную информацию. Если вы посмотрите на картинку ниже, то вы можете немного запутаться – почему «Хлеб», который имеет сумму «23013», максимально приближен к 32 000, а не «Молоко» с суммой в «33670».

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

Если вы попытаетесь поставить аргумент «-1», то, скорее всего, вы увидите ошибку, так как сортировка идет по возрастанию. Если же вы хотите получить значение «Молоко», то нужно сначала отсортировать товары по убыванию, а потом использовать эту формулу с аргументом «-1».

Я понимаю, что понять это сразу достаточно сложно, поэтому я вам советую потренироваться и попробовать оба варианта с различными аргументами. Таким образом вы сможете понять логику функции и сможете ее применять в ваших примерах и задачах. Лучше всего использовать вместе формулы индекса и поиска позиции в Эксель – да, так сложнее, но в итоге вы и поймете, как их обычно используют на практике.

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

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

Частые ошибки при работе с ПОИСКПОЗ

Первая ошибка – забыть поставить «0» при поиске текста. Если вы ищете товар, город, фамилию или артикул, почти всегда нужен точный поиск. Без третьего аргумента Excel может использовать приблизительный вариант, и результат получится не таким, как вы ожидали. Поэтому формула вида =ПОИСКПОЗ(“Молоко”;A2:A6;0) обычно надежнее, чем формула без последнего аргумента.

Вторая ошибка – неправильная сортировка при «1» и «-1». Для «1» нужен порядок по возрастанию, для «-1» – по убыванию. Если значения не отсортированы, Excel не обязан искать так, как человек глазами. Он работает по своему алгоритму и может вернуть позицию, которая кажется случайной.

Третья ошибка – лишние пробелы в тексте. Например, в ячейке написано не «Молоко», а «Молоко » с пробелом в конце. Визуально вы можете этого не заметить, но для точного совпадения это уже другое значение. Если формула возвращает #Н/Д, хотя значение точно есть в списке, проверьте лишние пробелы, невидимые символы и одинаковый ли тип данных у искомого значения и массива.

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

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

FAQ – короткие ответы для новичков

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

 

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

 

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

 

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

 

Что лучше ставить в третий аргумент: 0, 1 или -1?
Если ищете точное текстовое значение, артикул, фамилию или код – ставьте «0». Если ищете ближайшее число в списке по возрастанию – ставьте «1». Если ищете ближайшее число в списке по убыванию – ставьте «-1». Если сомневаетесь, начните с «0», потому что точный поиск проще проверить.

 

Можно ли искать часть слова через ПОИСКПОЗ?
Да, при точном типе сопоставления «0» можно использовать подстановочные знаки. Звездочка * заменяет любое количество символов, а вопросительный знак ? заменяет один символ. Например, формула может искать не полное слово «Молоко», а значение по маске вроде “Мол*”. Но с такими формулами нужно быть аккуратнее, потому что при нескольких похожих значениях функция вернет первое найденное совпадение.

 

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

На этом все, дорогие читатели портала WiFiGiD.RU. Я понимаю, что урок получился достаточно сложный. Если что-то было непонятно, или вам нужно что-то объяснить – пишите свои вопросы в комментариях, и я вам помогу.