Microsoft Excel – мощный аналитический инструмент и средство для работы с таблицами. Изучив доступные функции и команды, вы сможете выполнять сложные операции и оптимизировать большие базы данных. В данной инструкции пойдет речь о функции ПОИСКПОЗ, ее назначении и комбинациях с другими командами.
Мы рассмотрим следующие темы:
- назначение и описание ПОИСКПОЗ;
- синтаксис формулы;
- пошаговая инструкция по набору;
- примеры использования;
- комбинации с другими командами в «Экселе».
Назначение ПОИСКПОЗ
ПОИСКПОЗ – функция для определения позиции значения относительно выбранного диапазона ячеек или таблицы. После ввода формулы данная функция автоматически ищет совпадения в указанном массиве. Если совпадения по заданным параметрам найдены, то ПОИСКПОЗ возвращает номер позиции.
На английском написание команды в «Эксель» выглядит как MATCH. Чаще всего используется в качестве вспомогательной функции вкупе с ИНДЕКС, ВПР, ВЫБОР и другими. Однако мы рассмотрим и одиночное использование.
Правильное написание
Формула включает в себя следующие элементы:
- ПОИСКПОЗ() – указание самой функции;
- Искомое значение, позицию которого необходимо узнать. Можно указывать ссылки на ячейки или вписывать само значение.
- Диапазон данных – массив (таблица, строка, столбец) данных, среди которых будет осуществляться поиск;
- Тип сопоставления – уточнение того, какое значение предстоит искать (равное указанному в формуле, меньшее или большее).
Итак, финальный вариант формулы выглядит следующим образом:
[kod]ПОИСКПОЗ(искомое_значение;диапазон_данных;[тип_сопоставления]).[/kod]
Если вам необходимо одиночное использование функции, то она должна иметь строго указанный вид. В противном случае вы можете получить неверный результат или ошибку Н/Д.
Типы сопоставления
Рассмотрим подробнее разницу между несколькими типами сопоставления:
- 1 или без указания данного параметра – определяет наибольшее значение, которое максимально приближено к указанному в формуле;
- 0 – первое значение в диапазоне, которое равно искомому;
- -1 – наименьшее значение относительно искомого.
Данный аргумент необязателен к использованию в каждой формуле. Актуальным становится при работе с числовыми массивами, поскольку определить меньшее или большее текстовое значение невозможно.
Ввод команды в Excel
Теперь разберемся, как вводится формула на практике. Первый вариант добавления – это отдельное меню со списком функций:
- Сверху над основной таблицей находим иконку функции и жмем ее.
- В открывшемся окне нужно выбрать категорию «Ссылки и массивы», затем найти нужный вариант в списке и нажать «ОК».
- Теперь появятся поля для ввода данных. Заполнять их можно вручную или указывать с помощью выделения ячеек таблицы.
Для редактирования формулы можно использовать верхнюю строку. С помощью кнопки креста удаляется введенная строка.
Примеры использования
Теперь рассмотрим несколько примеров использования ПОИСКПОЗ. Ниже представлены основные сценарии, когда может пригодиться команда.
Поиск по одному критерию
Начнем с простого варианта – это поиск с одним искомым значением. Работает по строкам и по столбцам. Также можно использовать массив в виде таблицы. Для нахождения позиции значения делаем следующее:
- Попробуем найти позицию значения 4. Для этого вписываем команду ПОИСКПОЗ и в скобках указываем адрес ячейки с цифрой. Вы можете вписать само значение, но тогда придется менять формулу каждый раз. А если указать ссылку, то пользователь сможет автоматизировать процесс.
- Ставим точку с запятой и выделяем весь массив. Он может состоять как из одного столбца, как в примере, так и из полноценной таблицы.
- Теперь указываем тип сопоставления. Если хотим найти точное совпадение, вписываем 0.
- Жмем клавишу [knopka]Enter[/knopka] и видим готовый результат.
- Если поменять значение в ячейке, ссылка на которую указана в начале формулы, то итоговое значение сменится.
Подобным образом функция работает и для текстовых значений. Видим таблицу с месяцами года. Допустим, хотим узнать расположение августа относительно данного массива. Так же указываем ссылку на ячейку с названием месяца, затем выделяем массив и добавляем тип сопоставления. Если речь идет о текстовых значениях, то необходимо ставить 0.
И снова получаем правильный результат. Теперь мы убедились, что функция работает исправно, поэтому можно применять ее в более сложных и объемных таблицах, где не получится решить данную задачу вручную.
Поиск по двум критериям
Этот пример включает в себя указание нескольких условий. Применяется для массивов с несколькими значениями. Для примера рассмотрим простую таблицу с автомобилями и годами выпуска. Мы видим, что несколько марок авто повторяются, но даты напротив них разные. Попробуем найти позицию автомобиля с конкретной датой выпуска.
Для последующей автоматизации поиска можно создать простую таблицу. В дальнейшем в ней можно менять только название машины и год выпуска.
Поскольку поиск будет осуществляться по двум признакам, придется использовать правила при работе с массивами. Начинаем вводить формулы с привычного ПОИСКПОЗ и указываем ссылку на ячейку с названием марки. После этого ставим знак & и указываем ссылку на ячейку с нужной датой выпуска.
Теперь нужно указать два диапазона – столбцы с марками и датами. Для этого выделяем первый и после знака & выделяем второй столбец. В конце ставим 0 для поиска точного значения.
Для вычисления позиции необходимо воспользоваться комбинацией [knopka]Ctrl[/knopka]+[knopka]Shift[/knopka]+[knopka]Enter[/knopka]. Так вы запустите выполнение функции в массиве.
[expert_bq id=22954]Использование функции ПОИСКПОЗ через массив возможно не только по двум признакам. После указания каждого параметра необходимо ставить знак & и запускать поиск только указанной комбинацией клавиш.[/expert_bq]
Взаимодействие с другими операторами
Теперь рассмотрим пример использования функции ПОИСКПОЗ с оператором ИНДЕКС. На практике ее можно комбинировать со многими другими функциями, если знать, по каким принципам она работает.
ИНДЕКС – функция поиска совпадений по двум параметрам. Данная формула является аналогом вышеописанного примера, когда необходимо вернуть значение по нескольким условиям. ИНДЕКС используется только в случае двумерного поиска, в то время, когда ВПР, ГПР или ПРОСМОТР предназначены для одномерного.
Допустим, у нас есть таблица со статистикой продаж сети автосалонов по месяцам. Попробуем найти количество проданных автомобилей марки Audi в сентябре. Начинаем составление формулы с ИНДЕКС и указания диапазона всего массива.
Вторым аргументом будет функция ПОИСКПОЗ с указанием ссылки на ячейку с названием месяца и диапазона столбца массива:
Последний аргумент – еще одна ПОИСКПОЗ, но теперь с ссылками на название марки и строкой с автомобилями:
Жмем на [knopka]Enter[/knopka] и видим результат в соответствующей ячейке. Проверяем по нашей таблице.
Теперь можно менять значения в нашей таблице справа и получать новые значения:
Вы можете вычислить позицию каждой ячейки отдельно через ПОИСКПОЗ, а затем воспользоваться короткой функцией ИНДЕКС с указанием ссылок на расположение марки и месяца в таблице.
По представленному примеру описываемый оператор может использоваться во многих формулах. Например:
- ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ));
- ЕСЛИ(ЕНД(ПОИСКПОЗ(ИСТИНА;(СОВПАД))));
- ИНДЕКС(ПОИСКПОЗ(МИН(ЕСЛИ))) и другие.
Если вы знакомы с функционалом VBA для Microsoft Excel, то сможете создать надстройку для автоматического вычисления и поиска необходимых значений.
Видеоинструкция
Подробно и наглядно про данную возможность Excel рассказывается в представленном видеоролике.
Заключение
Мы рассмотрели основные случаи использования оператора ПОИСКПОЗ. Теперь вы сможете применять его по назначению и комбинировать с подходящими функциями.
