ПОИСКПОЗ в Excel

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

Мы рассмотрим следующие темы:

  • назначение и описание ПОИСКПОЗ;
  • синтаксис формулы;
  • пошаговая инструкция по набору;
  • примеры использования;
  • комбинации с другими командами в «Экселе».

Назначение ПОИСКПОЗ

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

На английском написание команды в «Эксель» выглядит как MATCH. Чаще всего используется в качестве вспомогательной функции вкупе с ИНДЕКС, ВПР, ВЫБОР и другими. Однако мы рассмотрим и одиночное использование.

Правильное написание

Формула включает в себя следующие элементы:

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

ПОИСКПОЗ

Итак, финальный вариант формулы выглядит следующим образом:

[kod]ПОИСКПОЗ(искомое_значение;диапазон_данных;[тип_сопоставления]).[/kod]

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

Типы сопоставления

Рассмотрим подробнее разницу между несколькими типами сопоставления:

  • 1 или без указания данного параметра – определяет наибольшее значение, которое максимально приближено к указанному в формуле;
  • 0 – первое значение в диапазоне, которое равно искомому;
  • -1 – наименьшее значение относительно искомого.

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

Ввод команды в Excel

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

  1. Сверху над основной таблицей находим иконку функции и жмем ее.

Ввод команды в Excel

  1. В открывшемся окне нужно выбрать категорию «Ссылки и массивы», затем найти нужный вариант в списке и нажать «ОК».

Ссылки и массивы

  1. Теперь появятся поля для ввода данных. Заполнять их можно вручную или указывать с помощью выделения ячеек таблицы.

Поля для ввода данных

Для редактирования формулы можно использовать верхнюю строку. С помощью кнопки креста удаляется введенная строка.

Редактирование формулы

Примеры использования

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

Поиск по одному критерию

Начнем с простого варианта – это поиск с одним искомым значением. Работает по строкам и по столбцам. Также можно использовать массив в виде таблицы. Для нахождения позиции значения делаем следующее:

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

Простой поиск

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

Выделение массива

  1. Теперь указываем тип сопоставления. Если хотим найти точное совпадение, вписываем 0.

Тип сопоставления

  1. Жмем клавишу [knopka]Enter[/knopka] и видим готовый результат.

Ввод результата

  1. Если поменять значение в ячейке, ссылка на которую указана в начале формулы, то итоговое значение сменится.

Смена значения

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

Поиск по датам

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

Результат

Поиск по двум критериям

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

Поиск по двум критериям

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

Простая таблица

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

Поиск нужной ячейки

Теперь нужно указать два диапазона – столбцы с марками и датами. Для этого выделяем первый и после знака & выделяем второй столбец. В конце ставим 0 для поиска точного значения.

Поиск точного значения

Для вычисления позиции необходимо воспользоваться комбинацией [knopka]Ctrl[/knopka]+[knopka]Shift[/knopka]+[knopka]Enter[/knopka]. Так вы запустите выполнение функции в массиве.

Выполнение функции

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

Взаимодействие с другими операторами

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

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

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

ИНДЕКС

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

ПОИСКПОЗ с указанием ссылки на ячейку

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

ПОИСКПОЗ по маркам

Жмем на [knopka]Enter[/knopka] и видим результат в соответствующей ячейке. Проверяем по нашей таблице.

Правильный результат

Теперь можно менять значения в нашей таблице справа и получать новые значения:

Подмена значений

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

По представленному примеру описываемый оператор может использоваться во многих формулах. Например:

  • ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ));
  • ЕСЛИ(ЕНД(ПОИСКПОЗ(ИСТИНА;(СОВПАД))));
  • ИНДЕКС(ПОИСКПОЗ(МИН(ЕСЛИ))) и другие.

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

Видеоинструкция

Подробно и наглядно про данную возможность Excel рассказывается в представленном видеоролике.

Заключение

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

Оцените статью
OS Helper
Добавить комментарий