Одним из наиболее востребованных операторов среди пользователей Excel является функция . В её задачи входит определение номера позиции элемента в заданном массиве данных. Наибольшую пользу она приносит, когда применяется в комплексе с другими операторами. Давайте разберемся, что же собой представляет функция , и как её можно использовать на практике.
Применение оператора ПОИСКПОЗ
Оператор принадлежит к категории функций . Он производит поиск заданного элемента в указанном массиве и выдает в отдельную ячейку номер его позиции в этом диапазоне. Собственно на это указывает даже его название. Также эта функция при применении в комплексе с другими операторами сообщает им номер позиции конкретного элемента для последующей обработки этих данных.
Синтаксис оператора выглядит так:
=ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])
Теперь рассмотрим каждый из трех этих аргументов в отдельности.
– это тот элемент, который следует отыскать. Он может иметь текстовую, числовую форму, а также принимать логическое значение. В качестве данного аргумента может выступать также ссылка на ячейку, которая содержит любое из вышеперечисленных значений.
– это адрес диапазона, в котором расположено искомое значение. Именно позицию данного элемента в этом массиве и должен определить оператор .
указывает точное совпадение нужно искать или неточное. Этот аргумент может иметь три значения: , и . При значении оператор ищет только точное совпадение. Если указано значение , то в случае отсутствия точного совпадения выдает самый близкий к нему элемент по убыванию. Если указано значение , то в случае, если не обнаружено точное совпадение, функция выдает самый близкий к нему элемент по возрастанию. Важно, если ведется поиск не точного значения, а приблизительного, чтобы просматриваемый массив был упорядочен по возрастанию (тип сопоставления ) или убыванию (тип сопоставления ).
Аргумент не является обязательным. Он может быть пропущенным, если в нем нет надобности. В этом случае его значение по умолчанию равно . Применять аргумент , прежде всего, имеет смысл только тогда, когда обрабатываются числовые значения, а не текстовые.
В случае, если при заданных настройках не может найти нужный элемент, то оператор показывает в ячейке ошибку .
При проведении поиска оператор не различает регистры символов. Если в массиве присутствует несколько точных совпадений, то выводит в ячейку позицию самого первого из них.
Способ 1: отображение места элемента в диапазоне текстовых данных
Давайте рассмотрим на примере самый простой случай, когда с помощью можно определить место указанного элемента в массиве текстовых данных. Узнаем, какую позицию в диапазоне, в котором находятся наименования товаров, занимает слово .
- Выделяем ячейку, в которую будет выводиться обрабатываемый результат. Щелкаем по значку около строки формул.
- Производится запуск . Открываем категорию или . В списке операторов ищем наименование . Найдя и выделив его, жмем на кнопку в нижней части окна.
- Активируется окно аргументов оператора . Как видим, в данном окне по числу количества аргументов имеется три поля. Нам предстоит их заполнить.
Так как нам нужно найти позицию слова в диапазоне, то вбиваем это наименование в поле .
В поле нужно указать координаты самого диапазона. Его можно вбить вручную, но проще установить курсор в поле и выделить этот массив на листе, зажимая при этом левую кнопку мыши. После этого его адрес отобразится в окне аргументов.
В третьем поле ставим число , так как будем работать с текстовыми данными, и поэтому нам нужен точный результат.
После того, как все данные установлены, жмем на кнопку .
- Программа выполняет вычисление и выводит порядковый номер позиции в выделенном массиве в той ячейке, которую мы задали ещё на первом шаге данной инструкции. Номер позиции будет равен .
Способ 2: автоматизация применения оператора ПОИСКПОЗ
Выше мы рассмотрели самый примитивный случай применения оператора , но даже его можно автоматизировать.
- Для удобства на листе добавляем ещё два дополнительных поля: и . В поле вбиваем то наименование, которое нужно найти. Пусть теперь это будет . В поле устанавливаем курсор и переходим к окну аргументов оператора тем же способом, о котором шел разговор выше.
- В окне аргументов функции в поле указываем адрес ячейки, в которой вписано слово . В полях и указываем те же самые данные, что и в предыдущем способе – адрес диапазона и число соответственно. После этого жмем на кнопку .
- После того, как мы произвели вышеуказанные действия, в поле отобразится позиция слова в выбранном диапазоне. В данном случае она равна .
- Данный способ хорош тем, что если мы захотим узнать позицию любого другого наименования, то не нужно будет каждый раз заново набирать или изменять формулу. Достаточно просто в поле вписать новое искомое слово вместо предыдущего. Обработка и выдача результата после этого произойдет автоматически.
Способ 3: использование оператора ПОИСКПОЗ для числовых выражений
Теперь давайте рассмотрим, как можно использовать для работы с числовыми выражениями.
Ставится задача найти товар на сумму реализации 400 рублей или самый ближайший к этой сумме по возрастанию.
- Прежде всего, нам нужно отсортировать элементы в столбце по убыванию. Выделяем данную колонку и переходим во вкладку . Щелкаем по значку , который расположен на ленте в блоке . В появившемся списке выбираем пункт .
- После того, как была сортировка произведена, выделяем ячейку, где будет выводиться результат, и запускаем окно аргументов тем же путем, о котором шла речь в первом способе.
В поле вбиваем число . В поле указываем координаты столбца . В поле устанавливаем значение , так как мы производим поиск равного или большего значения от искомого. После выполнения всех настроек жмем на кнопку .
- Результат обработки выводится в предварительно указанную ячейку. Это позиция . Ей соответствует . Действительно, сумма выручки от реализации этого продукта самая близкая к числу 400 по возрастанию и составляет 450 рублей.
Аналогичным образом можно произвести поиск и самой близкой позиции к по убыванию. Только для этого нужно произвести фильтрацию данных по возрастанию, а в поле аргументов функции установить значение .
Способ 4: использование в сочетании с другими операторами
Эффективнее всего эту функцию использовать с другими операторами в составе комплексной формулы. Наиболее часто её применяют в связке с функцией . Данный аргумент выводит в указанную ячейку содержимое диапазона заданное по номеру его строки или столбца. Причем нумерация, как и в отношении оператора , выполняется не относительно всего листа, а только внутри диапазона. Синтаксис этой функции следующий:
=ИНДЕКС(массив;номер_строки;номер_столбца)
При этом, если массив одномерный, то можно использовать только один из двух аргументов: или .
Особенность связки функций и заключается в том, что последняя может использоваться в качестве аргумента первой, то есть, указывать на позицию строки или столбца.
Давайте взглянем, как это можно сделать на практике, используя всю ту же таблицу. У нас стоит задача вывести в дополнительное поле листа наименование товара, общая сумма выручки от которого равна 350 рублям или самому близкому к этому значению по убыванию. Данный аргумент указан в поле .
- Отсортировываем элементы в столбце по возрастанию. Для этого выделяем необходимый столбец и, находясь во вкладке , кликаем по значку , а затем в появившемся меню кликаем по пункту .
- Выделяем ячейку в поле и вызываем обычным способом через кнопку .
- В открывшемся окне в категории ищем наименование , выделяем его и жмем на кнопку .
- Далее открывается окошко, которое предлагает выбор варианта оператора : для массива или для ссылки. Нам нужен первый вариант. Поэтому оставляем в этом окне все настройки по умолчанию и жмем на кнопку .
- Открывается окно аргументов функции . В поле указываем адрес того диапазона, где оператор будет искать название продукции. В нашем случае – это столбец .
В поле будет располагаться вложенная функция . Её придется вбить вручную, используя синтаксис, о котором говорится в самом начале статьи. Сразу записываем название функции – без кавычек. Затем открываем скобку. Первым аргументом данного оператора является . Оно располагается на листе в поле . Указываем координаты ячейки, содержащей число . Ставим точку с запятой. Вторым аргументом является . будет просматривать тот диапазон, в котором находится сумма выручки и искать наиболее приближенную к 350 рублям. Поэтому в данном случае указываем координаты столбца . Опять ставим точку с запятой. Третьим аргументом является . Так как мы будем искать число равное заданному или самое близкое меньшее, то устанавливаем тут цифру . Закрываем скобки.
Третий аргумент функции оставляем пустым. После этого жмем на кнопку .
- Как видим, функция при помощи оператора в заранее указанную ячейку выводит наименование . Действительно, сумма от реализации чая (300 рублей) ближе всего по убыванию к сумме 350 рублей из всех имеющихся в обрабатываемой таблице значений.
- Если мы изменим число в поле на другое, то соответственно автоматически будет пересчитано и содержимое поля .
Как видим, оператор является очень удобной функцией для определения порядкового номера указанного элемента в массиве данных. Но польза от него значительно увеличивается, если он применяется в комплексных формулах.
Мы рады, что смогли помочь Вам в решении проблемы.
Помимо этой статьи, на сайте еще 13048 полезных инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам. Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.