Поиск с функциями ИНДЕКС и ПОИСКПОЗ
Функции ИНДЕКС и ПОИСКПОЗ помогают искать данные, когда ВПР не помогает.
Давайте для начала разберемся как работают функции ИНДЕКС и ПОИСКПОЗ, а затем научимся осуществлять поиск данных в таблице.
Шлычков Константин
Эксперт и преподаватель Excel
Посмотрите на таблицу ниже. Она содержит информацию с категориями, названиями товара, стоимостью и статусом. Задача состоит в том, чтобы найти данные о товаре по его названию. Функция ВПР не будет работать для этой таблицы, так как название товара расположено во 2 столбце. Функции ИНДЕКС и ПОИСКПОЗ помогут решить эту задачу.
Функция ИНДЕКС
Функция ИНДЕКС возвращает значение из определенной ячейки массива, координаты которой являются параметрами функции. Т.е. задавая, как в игре морской бой, номер строки и столбца, в результате выполнения функции ИНДЕКС получаем значение ячейки.
Синтаксис функции ИНДЕКС
=ИНДЕКС(массив, номер строки, [номер столбца])
Массив - диапазон данных
Номер строки - обязательный параметр. Это номер строки из которой нужно вернуть значение
Номер столбца - номер столбца из которого нужно вернуть значение. Если в массиве указан только один столбец, то указывать этот параметр не нужно. Кроме того, если не указать номер столбца, то по умолчанию поиск будет происходить в 1 столбце.
На рисунке ниже отображена таблица. Для наглядности я выделил диапазон цветом и пронумеровал строки и столбцы. Несколько ячеек содержат значения. В ячейке D1 я записал функцию =ИНДЕКС(C5:I11;5;6). Первый параметр C5:I11 - это диапазон данных для функции, в котором будет выбираться значение. Далее указана 5 строка и 6 столбец. Функция записала в ячейку D1 значение "Текст 1".
Если изменить функцию и указать столбец 6, функция изменится на =ИНДЕКС(C5:I11;5;1). Результат в ячейке D1 изменится на "Текст2"
Обратите внимание, что нумерация строк и столбцов начинается от верхней левой ячейки диапазона, который указан первым параметром, а не с ячейки A1.
Шлычков Константин
Эксперт и преподаватель Excel
Выводить значение ячейки, если известны ее координаты (номер строки и столбца) Вы научились. Теперь осталось разобраться как же автоматически искать эти координаты. Для этого существует функция ПОИСКПОЗ.
Функция ПОИСКПОЗ
Функция ПОИСКПОЗ возвращает номер позиции в строке/столбце, в котором найдено искомое значение.
Синтаксис функции ПОИСКПОЗ
=ПОИСКПОЗ(искомое значение, просматриваемый массив, [тип соответствия])
Искомое значение - значение, которое нужно найти
Просматриваемый массив - столбец или строка, в которой происходит поиск
Тип соответствия - параметр может принимать значение -1,0 и 1. Если нужно найти точное совпадение, то указывайте 0.
В ячейку H14 вбиваем функцию =ПОИСКПОЗ(H13;H5:H11;0). Ячейка H13 содержит значение, которое нужно найти. Искать его нужно в диапазоне H5:H11 и искать точное совпадение (3-ий параметр 0). Так указав в H13 значение "Текст 1", функция ПОИСКПОЗ выведет значение 5 - это номер строки, которая содержит текст.
Заменив текст в ячейке H13 на "слово", функция ПОИСКПОЗ вернет значение 2. И обратите внимание, что функция ПОИСКПОЗ учитывает регистр букв (заглавные и строчные).
Второй параметр функции ПОИСКПОЗ не может быть шире одной строки или одного столбца. Если диапазон будет шире, функция вернет ошибку #Н/Д.
Шлычков Константин
Эксперт и преподаватель Excel
Поиск данных с функциями ИНДЕКС и ПОИСКПОЗ
Теперь осталось объединить работу этих функций для поиска данных. В файле с примером, который Вы можете скачать, Вы найдете таблицу с данными о товарах: категория, наименование, статус и стоимость. Справа от таблицы можно выбрать товар из списка. В примере на рисунке выбран товар Молоко. В ячейке H4 нужно вывести позицию товара с помощью функции ПОИСКПОЗ. Функция будет выглядеть так: =ПОИСКПОЗ(H3;C2:C14;0). Функция ищет в столбце С строку, которая содержит текст "Молоко" и возвращает значение 8.
Теперь зная позицию Молока, можно узнать к какой категории относится товар. В ячейку H5 пишем текст: =ИНДЕКС(B2:B14;H4). В диапазоне столбца B нужно вывести значение строки, указанной в ячейке H4. В примере это 8 строка выбранного диапазона. В результате функция возвращает значение категории товара "Молочные продукты, яйца".
Функцию ПОИСКПОЗ можно вложить в функцию ИНДЕКС вместо указания 2-ого параметра функции. Тогда функция для поиска примет следующий вид: =ИНДЕКС(B2:B14;ПОИСКПОЗ(H3;C2:C14;0)).
