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

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

Более подробно о функциях ВПР и ПРОСМОТР.

Функция ПОИСКПОЗ в Excel

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

Например, на рисунке ниже формула вернет число 5, поскольку имя "Дарья" находится в пятой строке диапазона A1:A9.

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

В следующем примере формула вернет 3, поскольку число 300 находится в третьем столбце диапазона B1:I1.

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

Из приведенных примеров видно, что первым аргументом функции ПОИСКПОЗ является искомое значение. Вторым аргументом выступает диапазон, который содержит искомое значение. Также функция имеет еще и третий аргумент, который задает тип сопоставления. Он может принимать один из трех вариантов:

  • 0 - функция ПОИСКПОЗ ищет первое значение в точности равное заданному. Сортировка не требуется.

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

  • 1 или вовсе опущено - функция ПОИСКПОЗ ищет самое большое значение, которое меньше или равно заданному. Требуется сортировка в порядке возрастания.

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

  • -1 - функция ПОИСКПОЗ ищет самое маленькое значение, которое больше или равно заданному. Требуется сортировка в порядке убывания.

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

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

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

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

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

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

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

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

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

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

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

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

Совместное использование ПОИСКПОЗ и ИНДЕКС в Excel

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

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

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

Пускай ячейка C15 содержит указанный нами месяц, например, Май. А ячейка C16 - тип товара, например, Овощи. Введем в ячейку C17 следующую формулу и нажмем Enter:

=ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))

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

Как видите, мы получили верный результат. Если поменять месяц и тип товара, формула снова вернет правильный результат:

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

В данной формуле функция ИНДЕКС принимает все 3 аргумента:

  1. Первый аргумент – это диапазон B2:E13, в котором мы осуществляем поиск.

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

  2. Вторым аргументом функции ИНДЕКС является номер строки. Номер мы получаем с помощью функции ПОИСКПОЗ(C15;A2:A13;0). Для наглядности вычислим, что же возвращает нам данная формула:

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

  3. Третьим аргументом функции ИНДЕКС является номер столбца. Этот номер мы получаем с помощью функции ПОИСКПОЗ(C16;B1:E1;0). Для наглядности вычислим и это значение:

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

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

=ИНДЕКС(B2:E13;D15;D16)

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

Как видите, все достаточно просто!

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

25.01.2015 01:17
29624

Комментарии

Нет комментариев. Ваш будет первым!