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

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

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

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

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

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

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

В случае отсутствия совпадений функция возвращает:

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

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

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

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

1. Выбираем раздел «Категория» и устанавливаем значение «Ссылки и массивы».
2. Находим нужную функцию, выделяем её и нажимаем кнопку «ОК».

1. Теперь нужно ввести данные. В первой строке указываем адрес элемента, который требуется найти. Можно выбрать адрес, кликнув мышкой, либо ввести вручную, например:
2. Далее указываем диапазон адресов ниже – их можно выбрать мышкой или вписать вручную. Например:
3. «Тип_сопоставления» – это именно та самая точность. Поскольку мы работаем с текстом, лучше всего указать полную точность:

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

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

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

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

1. Фактически, наши действия одинаковы, за исключением того, что вместо «Искомого значения» мы заполняем адрес соседней ячейки.

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

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

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

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

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

1. Если оставить настройки по умолчанию и нажать на кнопку сортировки, вы увидите, что строки также отсортированы.
2. Можно выбрать любую ячейку и вставить в неё функцию.

1. Вставляем нужное значение в начало строки. Задаем диапазон массива. В конце добавляем «-1» для поиска приближенного, а не точного совпадения.

1. В результате обнаружения нами товара, мы пришли к выводу, что прибыль составляет около 30 000 и оказалось, что это было «Молоко».

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

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

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

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

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

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

1. Необходимо отметить опцию «Сортировать по возрастанию».

1. Значение 32 000 будет нашей суммой. Следующим шагом будет поиск нужного товара и вставка функции.

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

В дальнейшем будут представлены точные данные. При просмотре нижеприведенной картинки вы, возможно, запутаетесь — почему товар «Хлеб» с ценой «23013» ближе всего к 32 000, а не «Молоко» с ценой «33670».

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

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

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