Функции ИНДЕКС и ПОИСКПОЗ в Excel на простых примерах

Пример функции ПОИСКПОЗ в Excel без функции ИНДЕКС

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

Видео

Функция ВПР с несколькими условиями критериев поиска в Excel

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

Сочетание с функцией ПОИСКПОЗ

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

Оператор ПОИСКПОЗ возвращает положение указанного значения в выделенном диапазоне ячеек.

Формула функции выглядит следующим образом:

=ПОИСКПОЗ(Искомое_значение,Просматриваемый_массив,[Тип_сопоставления]).

Давайте “пробежимся” по аргементам функции:

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

Использование ПОИСКПОЗ позволяет автоматически заполнить аргументы “Номер строки” и/или “Номер столбца” функции ИНДЕКС.

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

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

1. Для начала, заполняем ячейку с наименованием. Можно просто скопировать и вставить значение из основной таблицы.
2. Теперь встаем в ячейку, в которой планируем отображать результат, и жмем кнопку “Вставить функцию” (fx).
3. Выбираем функцию ИНДЕКС из списка операторов.
4. Выбираем список аргументов для массива и жмем OK.
5. Приступаем к заполнению аргументов:
   • в значении “Массива” указываем ячейки столбца, содержащего цены позиций. Сделать это можно вручную или с помощью выделения ячеек в самой таблице.
   • в поле аргумента “Номер_строки” требуется добавить оператор ПОИСКПОЗ. Для этого делаем следующее:
     • перейдя в поле для заполнения данного аргумента щелкаем по небольшой стрелке вниз в поле с именем ячейки (в котором будет отображаться название текущей функции), расположенным слева от кнопки “Вставить функцию”.
     • в раскрывшемся перечне выбираем пункт “Другие функции”.
     • в очередном окне Мастера функций выбираем категорию “Ссылки и массивы”, находим оператор “ПОИСКПОЗ”, отмечаем его и жмем OK.
     • в аргументе “Искомое_значение” указываем адрес ячейки, по содержимому которого будет выполняться поиск в основном массиве (в нашем случае – это F2). В “Просматриваемом_массиве” указываем вручную или с помощью выделения в самой таблице диапазон ячеек, в котором будет производиться поиск искомого значения. В аргумент “Тип_сопоставления” указываем цифру .
   • Обращаем внимание на строку формул. Здесь мы левой кнопкой мыши щелкаем по слову “ИНДЕКС”.
   • появится снова список аргументов функции ИНДЕКС. Мы можем заметить, что в результате проделанных выше действий, поле “Номер_строки” заполнилось автоматически. Так как выделенный массив одномерный и содержит только один столбец, последний аргумент оставляем незаполненным и жмем кнопку OK.Примечание: заполнить аргумент “Номер_строки” можно и вручную, ориентируясь на синтаксис функции ПОИСКПОЗ.
6. Таким образом, мы получаем в выбранной ячейке нужный результат, а именно цену указанной рядом позиции.
7. Так как информация “подтягивается” с помощью формулы, изменение цены соответствующей позиции в основной таблице немедленно отразится в данной ячейке.
8. Также, если мы изменим наименование позиции во вспомогательной таблице, ее цена автоматически будет заполнена из основной.

Функция ПОИСКПОЗ в Excel – синтаксис и использование

Функция ПОИСКПОЗ в Excel ищет заданное значение в диапазоне ячеек и возвращает относительное положение этого значения.

Синтаксис формулы ПОИСКПОЗ выглядит следующим образом:

Искомое_значение (обязательный аргумент) – значение, которое вы хотите найти. Это может быть числовое, текстовое или логическое значение, а также ссылка на ячейку.

Просматриваемый_массив (обязательный аргумент) – диапазон ячеек для поиска.

Тип_сопоставления (необязательный аргумент) – определяет тип соответствия. Это может быть одно из следующих значений: 1, 0, -1.

• 1 или отсутствует (по умолчанию) – находит наибольшее значение в массиве, которое меньше или равно значению поиска. Массив поиска должен быть отсортирован в порядке возрастания, от самого маленького до большого или от A до Z.
• 0 – находит первое значение в массиве, которое в точности равно значению поиска. Сортировка не требуется.
• -1 – находит наименьшее значение в массиве, которое больше или равно значению поиска. Массив поиска должен быть отсортирован в порядке убывания, от самого большого до самого маленького или от Z до A.

Чтобы лучше разобраться в данной функции, давайте напишем простую формулу ПОИСКПОЗ, основанную на следующих данных: фамилии студентов в столбце A и их баллы по экзаменам в столбце B, отсортированные от самых высоких до самых низких. Чтобы узнать, где среди других находится конкретный студент (например, студентка Виноградова), используйте эту простую формулу:

=ПОИСКПОЗ(E1; A2:A8; 0)

При желании вы можете поместить значение поиска в ячейку (E1 в данном примере) и сослаться на эту ячейку в формуле ПОИСКПОЗ Excel:

Функция ПОИСКПОЗ в Excel – Пример использования функции ПОИСКПОЗ в Excel

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

Особенности функции ПОИСКПОЗ Excel

Как вы только что видели, использовать ПОИСКПОЗ в Excel легко. Однако, как и у любой функции Excel, у нее есть несколько особенностей, о которых следует знать:

• Функция ПОИСКПОЗ возвращает относительное положение значения поиска в массиве, а не самого значения.
• ПОИСКПОЗ не зависит от регистра , что означает, что она не различает строчные и прописные символы при работе с текстовыми значениями.
• Если массив поиска содержит несколько вхождений искомого значения, возвращается позиция первого значения.
• Если искомое значение не найдено в просматриваемом массиве, возвращается ошибка #Н/Д.

Примеры

Формула =ПОИСК(«к»;»Первый канал») вернет 8, т.к. буква к находится на 8-й позиции слева.

Пусть в ячейке А2 введена строка Первый канал — лучший . Формула =ПОИСК(СИМВОЛ(32);A2) вернет 7, т.к. символ пробела (код 32) находится на 7-й позиции.

Формула =ПОИСК(«#. #»;»Артикул #123# ID») будет искать в строке » Артикул #123# ID » последовательность из 5 символов, которая начинается и заканчивается на знак #.

Чтобы найти позицию второго вхождения буквы «а» в строке «мама мыла раму» используйте формулу =ПОИСК(«а»;»мама мыла раму»;ПОИСК(«а»;»мама мыла раму»)+1). Чтобы определить есть ли третье вхождение буквы «м» в строке «мама мыла раму» используйте формулу =ЕСЛИ(ДЛСТР(ПОДСТАВИТЬ(«мама мыла раму»;»м»;»»;3))=ДЛСТР(«мама мыла раму»);»Нет третьего вхождения м»;»Есть третье вхождение м»)

Формула =ПОИСК(«клад?»;»докладная») вернет 3, т.е. в слове «докладная» содержится слово из 5 букв, первые 4 из которых клад (начиная с третьей буквы слова докладная ).

Функция ИНДЕКС для массивов

Допустим, у нас есть таблица с наименованиями товаров, их ценой, количеством и итоговой суммой.

Наша задача – в заранее выбранной ячейке отобразить наименование 5-ой позиции в списке.

1. Встаем в ячейку, куда планируем вывести требуемые данные. Затем жмем кнопку “Вставить функцию” (fx).
2. В открывшемся окне вставки функции выбираем категорию “Ссылки и массивы” (или “Полный алфавитный перечень”), отмечаем строку “ИНДЕКС” и жмем OK.
3. Далее программа предложит на выбор один из двух наборов аргументов, о которых мы писали выше (для массива или для ссылок). В рамках поставленной задачи выбираем первый вариант и жмем OK.
4. Теперь нам нужно заполнить аргументы функции:
   • в значении “Массив” указываем координаты диапазона ячеек, в границах которого будет работать функция (за исключением шапки). Прописать адреса ячеек можно вручную или, находясь курсором в поле для ввода информации, с помощью зажатой левой кнопки мыши выделяем требуемую область данных в самой таблице.
   • в аргументе “Номер_строки” пишем цифру 5, так как согласно поставленной задаче требуется отобрать 5-ую позицию из списка.
   • в значении аргумента “Номер_столбца” пишем число 1, потому что наименования позиций находятся в первом столбце рассматриваемого массива.
   • по готовности жмем OK.
5. В выбранной ячейке отобразится результат согласно заданным условиям отбора в аргументах функции. В нашем случае – это содержимое ячейки, находящейся в 1-ом столбце и 5-ой строке выделенного массива.

Как мы ранее отмечали, один из аргументов функции (“Номер_столбца” или “Номер_строки”) может оставаться незаполненным, если выделенный массив будет одномерным, т.е. занимать либо одну строку, либо один столбец. На практике это выглядит следующим образом.

1. В окне аргументов функции в поле “Массив” выделяем только ячейки первого столбца. Указываем номер строки – 5, а номер столбца, соответственно, остается незаполненным, так как в выделенном нами массиве он только один.
2. Нажав кнопку OK мы получим тот же результат в ячейке таблицы, хотя аргументы функции и ее формула будут отличаться от первоначального варианта.

Поиск позиций ВСЕХ текстовых значений, удовлетворяющих критерию

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

Рассмотрим список с повторяющимися значениями в диапазоне B66:B72 . Найдем все позиции значения Груши .

Значение Груши находятся в позициях 2 и 5 списка. С помощью формулы массива

=(«груши»=$B$66:$B$72)*(СТРОКА($B$66:$B$72)-СТРОКА($D$65))

можно найти все эти позиции. Для этого необходимо выделить несколько ячеек (расположенных вертикально), в Строке формул ввести вышеуказанную формулу и нажать CTRL+SHIFT+ENTER . В позициях, в которых есть значение Груши будет выведено соответствующее значение позиции, в остальных ячейках быдет выведен 0.

C помощью другой формулы массива

=НАИБОЛЬШИЙ((«груши»=$B$66:$B$72)*(СТРОКА($B$66:$B$72)-СТРОКА($D$65));СТРОКА()-СТРОКА($D$65))

можно отсортировать найденные позиции, чтобы номера найденных позиций отображались в первых ячейках (см. файл примера ).

Поиск позиции в массивах с текстовыми значениями

Произведем поиск позиции в НЕ сортированном списке текстовых значений (диапазон B7:B13 )

Столбец Позиция приведен для наглядности и не влияет на вычисления.

Формула для поиска позиции значения Груши: =ПОИСКПОЗ(«груши»;B7:B13;0)

Формула находит первое значение сверху и выводит его позицию в диапазоне, второе значение Груши учтено не будет.

Чтобы найти номер строки, а не позиции в искомом диапазоне, можно записать следующую формулу: =ПОИСКПОЗ(«груши»;B7:B13;0)+СТРОКА($B$6)

Если искомое значение не обнаружено в списке, то будет возвращено значение ошибки #Н/Д. Например, формула =ПОИСКПОЗ(«грейпфрут»;B7:B13;0) вернет ошибку, т.к. значения «грейпфрут» в диапазоне ячеек B7:B13 нет.

В файле примера можно найти применение функции при поиске в горизонтальном массиве.

Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ

​ искомое слово. Думаю,​ результат.​ нужно из нескольких​ двумя столбцами как​ как вспомогательную в​ указанном диапазоне.​Формула ищет в C2:C10​(ПОИСКПОЗ) использована для​

​=ПОИСКПОЗ(D5;{«Jan»;»Feb»;»Mar»};0)​(ИНДЕКС), чтобы найти​

​ численность населения Воронежа​

​=ВПР​

• ​41​находит первое значение,​возвращает значение 2, поскольку​ ошибку. С помощью​ не соответствуют искомому​ что анимация, расположенная​Введите в строке формул​ сделать один!​

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

  ​ того, чтобы найти​​Вы можете преобразовать оценки​ ближайшее значение.​ в четвертом столбце​(2345678;A1:E7;5)​Формула​ равное аргументу​

• ​ элемент 25 является вторым​ функции ЕОШИБКА мы​ выражению, и номер​ выше, полностью показывает​ в нее следующую​

​Добавим рядом с нашей​ использовали в ее​ функциями такими как:​ разделе, посвященном функции​ значению​ из нескольких угаданных​ учащихся в буквенную​​Функция​​ (столбец D). Использованная​. Формула возвращает цену​Описание​искомое_значение​ в диапазоне.​ проверяем выдала ли​​ столбца, в котором​ ​ задачу.​​ формулу:​ таблицей еще один​ аргументах оператор &.​

​ ИНДЕКС, ВПР, ГПР​ ГПР.​Капуста​ чисел ближайшее к​ систему, используя функцию​MATCH​ формула показана в​ на другую деталь,​Результат​.​Совет:​​ функция ПОИСКПОЗ ошибку.​​ соответствующее значение было​​​Нажмите в конце не​ столбец, где склеим​ Учитывая этот оператор​ и др. Но​К началу страницы​(B7), и возвращает​ правильному.​MATCH​

​(ПОИСКПОЗ) имеет следующий​ ячейке A14.​ потому что функция​=ПОИСКПОЗ(39;B2:B5,1;0)​Просматриваемый_массив​ Функцией​ Если да, то​ найдено.​Пример 1. Первая идя​ Enter, а сочетание​ название товара и​ первый аргументом для​ какую пользу может​Для выполнения этой задачи​ значение в ячейке​Функция​(ПОИСКПОЗ) так же,​ синтаксис:​

​Краткий справочник: обзор функции​ ВПР нашла ближайшее​Так как точного соответствия​может быть не​ПОИСКПОЗ​ мы получаем значение​СУММ(($A$2:$D$9=F2)*СТОЛБЕЦ($A$2:$D$9))​ для решения задач​Ctrl+Shift+Enter​ месяц в единое​ функции теперь является​ приносить данная функция​ используется функция ГПР.​

​ C7 (​ABS​ как Вы делали​MATCH(lookup_value,lookup_array,[match_type])​

Использование функции ГПР

​ ВПР​ число, меньшее или​ нет, возвращается позиция​ упорядочен.​следует пользоваться вместо​ ИСТИНА. Быстро меняем​Наконец, все значения из​ типа – это​

Одновременное использование функций ИНДЕКС и ПОИСКПОЗ

​, чтобы ввести формулу​ целое с помощью​ значение FordМаркетинговый. По​ работая самостоятельно. Из​Важно:​100​возвращает модуль разницы​ это с​ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])​Функции ссылки и поиска​ равное указанному (2345678).​ ближайшего меньшего элемента​-1​ одной из функций​ её на ЛОЖЬ​ нашей таблицы суммируются​

​ при помощи какого-то​ не как обычную,​ оператора сцепки (&),​ этой причине первый​ самого названия функции​  Значения в первой​).​ между каждым угаданным​VLOOKUP​lookup_value​ (справка)​ Эта ошибка может​ (38) в диапазоне​Функция​ПРОСМОТР​ и умножаем на​ (в нашем примере​ вида цикла поочерёдно​ а как формулу​ чтобы получить уникальный​ Ford из отдела​ ПОИСКПОЗ понятно, что​

Еще о функциях поиска

• ​ строке должны быть​Дополнительные сведения см. в​

• ​ и правильным числами.​(ВПР). В этом​

• ​(искомое_значение) – может​Использование аргумента массива таблицы​

​ привести к неправильному​

support.office.com

Функция ИНДЕКС в Excel

Функция ИНДЕКС возвращает содержимое ячейки, которая находится на пересечении заданных строки и столбца. Например, на рисунке ниже формула возвращает значение из диапазона A1:C4, которое находится на пересечении 3 строки и 2 столбца.

Стоит отметить, что номера строк и столбцов задаются относительно верхней левой ячейки диапазона. Например, если ту же таблицу расположить в другом диапазоне, то формула вернет тот же результат:

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

Например, следующая формула возвращает пятое значение из диапазона A1:A12 (вертикальный вектор):

Данная формула возвращает третье значение из диапазона A1:L1(горизонтальный вектор):

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

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

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

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

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

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

Пример использования функции ПОИСКПОЗ

Предположим у нас есть список продуктов и нам нужно найти порядковый номер конкретного продукта в этом списке. В качестве искомого значения выбираем ячейку C3 («Апельсины») и задаем просматриваемый массив в виде диапазона ячеек A2:A13 (название продукта), тип сопоставления указываем 0:

Пример использования функции ПОИСКПОЗ

Теги