Совместное использование функций ИНДЕКС и ПОИСК в Excel — хорошая альтернатива ВПР, ПОИСК ПО ГОРИЗОНТУ и ПОИСК. Этот пакет универсален и обладает всеми возможностями этих функций. А в некоторых случаях, например, при поиске двумерных данных на листе, он будет просто незаменим. В этом уроке мы последовательно рассмотрим функции ПОИСК и ИНДЕКС, а затем рассмотрим пример их совместного использования в Excel.
Функция ПОИСКПОЗ в Excel
ПОИСКПОЗ возвращает относительное положение ячейки в указанном диапазоне Excel, которое соответствует искомому значению. Те эта функция возвращает не сам контент, а его позицию в массиве данных.
Например, на изображении ниже формула вернет 5, потому что имя «Дарья» находится в пятой строке диапазона A1: A9.
В следующем примере формула вернет 3, потому что 300 находится в третьем столбце диапазона B1: I1.
Из приведенных выше примеров видно, что первым аргументом функции ПОИСКПОЗ является желаемое значение. Второй аргумент — это диапазон, содержащий желаемое значение. У функции также есть третий аргумент, определяющий тип сравнения. Вы можете выбрать один из трех вариантов:
- 0 — функция ПОИСКПОЗ ищет первое значение, точно равное заданному. Сортировка не требуется.
- 1 или вообще опущено — функция ПОИСКПОЗ ищет наибольшее значение, меньшее или равное заданному. Сортировка по возрастанию обязательна.
- -1 — функция ПОИСКПОЗ ищет наименьшее значение, большее или равное заданному. Требуется порядок убывания.
Сама по себе функция ПОИСКПОЗ обычно не очень полезна, поэтому в Excel она часто используется вместе с функцией ИНДЕКС.
Функция ИНДЕКС в Excel
Функция ИНДЕКС возвращает содержимое ячейки на пересечении указанной строки и столбца. Например, на изображении ниже формула возвращает значение из диапазона A1: C4, который находится на пересечении 3 строк и 2 столбцов.
Стоит отметить, что номера строк и столбцов указаны относительно левой верхней ячейки диапазона. Например, если та же таблица вставлена в другой диапазон, формула вернет тот же результат:
Если массив содержит только одну строку или один столбец, например — вектор, второй аргумент функции ИНДЕКС указывает номер значения в этом векторе. В этом случае третий аргумент не обязателен.
Например, следующая формула возвращает пятое значение в диапазоне A1: A12 (вертикальный вектор):
Эта формула возвращает третье значение в диапазоне A1: L1 (горизонтальный вектор):
Совместное использование ПОИСКПОЗ и ИНДЕКС в Excel
Если вы работали с функциями VLOOKUP, HORIZ LOOK и SEARCH в Excel, имейте в виду, что они просматривают только одномерный массив. Но иногда вам приходится иметь дело с двумерным поиском, когда вам нужно искать совпадения на основе двух параметров одновременно. Именно в этих случаях комбинация ПОИСКПОС и ИНДЕКС в Excel оказывается просто незаменимой.
На следующем рисунке показана таблица, содержащая ежемесячные продажи каждого из четырех типов товаров. Наша задача — получить объем продаж, указав месяц и вид необходимого товара.
Пусть ячейка C15 содержит указанный нами месяц, например май. А ячейка C16 — это тип продукта, например Овощи. Введите следующую формулу в ячейку C17 и нажмите Enter:
= ИНДЕКС (A2: E13; СООТВЕТСТВИЕ (C15; A2: A13,0); СООТВЕТСТВИЕ (C16; B1: E1,0))
Как видите, мы получили правильный результат. Если вы измените месяц и тип продукта, формула снова вернет правильный результат:
В этой формуле функция ИНДЕКС принимает все 3 аргумента:
- Первый аргумент — это диапазон B2: E13, который мы ищем.
- Второй аргумент функции ИНДЕКС — номер строки. Мы получаем число с помощью функции ПОИСКПОЗ (C15; A2: A13; 0). Для наглядности давайте посчитаем, что дает нам эта формула:
- Третий аргумент функции ИНДЕКС — номер столбца. Мы получаем это число с помощью функции ПОИСКПОЗ (C16; B1: E1; 0). Для наглядности рассчитаем это значение:
Если вместо функций ПОИСК подставить уже рассчитанные данные из ячеек D15 и D16 в исходную громоздкую формулу, формула преобразуется в более компактный и понятный вид:
= ИНДЕКС (B2: E13; D15; D16)
Как видите, все довольно просто!
На этой приятной записи мы закончим. В этом уроке вы познакомились с двумя другими полезными функциями Microsoft Excel — ПОИСКПОС и ИНДЕКС, проанализировали возможности на простых примерах, а также рассмотрели их совместное использование. Надеюсь, этот урок был вам полезен. Следите за обновлениями и успешно изучайте Excel.