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

Функции ИНДЕКС и ПОИСКПОЗ в Excel
На чтение
11 мин.
Просмотров
56
Дата обновления
25.10.2024

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

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

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

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

 

В следующем примере формула вернет 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

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

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

ИНДЕКС и ПОИСК в Excel

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

= ИНДЕКС (A2: 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.

0 Комментариев
Комментариев на модерации: 0
Оставьте комментарий