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

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

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

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

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

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

=ПОИСКПОЗ(Значение;Массив; [Точность поиска])

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

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

#Н/Д

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

Пример 1: Поиск адреса элемента

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

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

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

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

“Молоко”

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

A2:A6

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

0

8. Жмем «ОК».

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ИНДЕКС

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

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

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

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

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

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

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