Берем данные с 5 строки и 1 столбца с помощью функции Индекс в Excel

В эксель существует большое количество разнообразных опций. Индекс – функция, которая дает возможность выбирать из массива сведений нужное значение. При этом структура, число строк, столбцов и в том числе самих массивов не имеют значения.

На данном сайте вы найдете и другие статьи по работе с программой Excel.

Что возвращает функция

Функция индекс возвращает информацию, указанную в конкретной строке и столбце.

Синтаксис

Синтаксис индекс в excel может быть в двух вариантах.

=ИНДЕКС(массив; номер_строки; [номер_столбца])

=ИНДЕКС(ссылка; номер_строки; [номер_столбца]; [номер_области])

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

Аргументы функции

Формула индекс состоит из 4 аргументов. Обязательными из них являются только два.

Обязательные аргументы:

Массив – диапазон ячеек для поиска.

Номер_строки – строка, которая содержит необходимые данные.

Необязательные аргументы:

Номер_столбца – столбец, который содержит нужные данные. Если в аргументе не указаны критерии для строки, то этот аргумент становится обязательным.

Номер_области: используется в тех случаях, когда аргумент массива включает в себя несколько диапазонов. число будет использовано для выбора сразу всех диапазонов.

Если в синтаксисе был выбран вариант с указанием ссылки, а не массива, то аргументы будут следующими.

Дополнительная информация

Если в указанных данных номер строки или столбца равняется нулю, то функция вернет данные всего столбца или строки.

Если после формулы указана ячейка, то вместо значения будет возращена ссылка на эту ячейку.

В большинстве случаев ИНДЕКС используют вместе с ПОИСКПОЗ.

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

Функция может быть использована в двух формах:

  • массив данных
  • ссылка на данные

Примеры использования функции индекс в excel

Пример 1 самый простой способ использования

Рассмотрим примеры использования индекс в excel.

Начнем с самой простой задачи. Имеется список из нескольких городов. Из него необходимо достать пятый по порядку город. Функция index будет иметь следующий вид: ИНДЕКС(массив; номер_строки). В ячейку A9 заносим формулу. Она состоит из двух аргументов:

  • Все города, среди которых производится поиск
  • Номер ряда, где содержится необходимая информация, т.е. пятый город.

Опция возвращает нужное значение, т.е. пятый по порядку город из списка.

Пример 2 создаем динамический поиск значений с использованием функций индекс и поискпоз

Функция индекс в excel в комбинации с ПОИСКПОЗ может быть аналогом для ВПР. Они имеют преимущество по сравнению с последней.

Для правильной работы ВПР необходимо, чтобы данные для поиска всегда были расположены справа от критериев. Следовательно, прежде всего потребуется упорядочить столбцы. Только после этого можно будет начинать работу с формулой.

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

Синтаксис будет выглядеть следующим образом: (массив; номер_строки; номер_столбца).

Важно! В самом начале указывается диапазон значений. На второй – ПОИСКПОЗН (необходимый критерий; диапазон критериев;0). 0 в завершении вписывается для того, чтобы результат был максимально точным.

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

В подобных случаях ВПР использовать нельзя, так как на первом месте стоят не критерии, а значения.

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

Проверяем, что формула правильно работаем.

= ИНДЕКС($F$2:$F$6; ПОИСКПОЗ(A2; $G$2:$G$7))

Растягиваем ее до конца столбца.

Важно! Обязательно нужно фиксировать ссылки. В противном случае массив спуститься дальше, что приведет к некорректным результатам.

Если во второй таблице не окажется нужной информации о препаратах и их цене, то таблица выдаст ошибку #Н/Д. Для того чтобы внешний вид таблицы, был более привлекательным, продолжим работу с ней. Добавим в формулу ЕСЛИОШИБКА. В ней укажем, что при отсутствии данных необходимо выводить прочерк.

Пример 3 создаем динамический поиск значений с использованием функций index индекс и match поискпоз и выпадающего списка

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

Общий вид в excel индекс-функции будет похож на тот, который представлен во втором примере. В него только потребуется добавить выпадающий список.

=ИНДЕКС($B$3:$E$9;ПОИСКПОЗ($G$4;$A$3:$A$9;0);ПОИСКПОЗ($H$3;$B$2:$E$2;0))

Алгоритм создания выпадающего списка

  1. Выбрать ячейку, в которой будет расположен выпадающий список.
  2. Нажать на вкладку Данные. Выбрать пункт проверка данных.
  3. В пункте «Тип данных» выбрать список.
  4. В разделе «Источник» указать диапазон ячеек для поиска.

Пример 4 использование трехстороннего поиска с помощью index индекс match поискпоз

Индекс – формула, которая помогает обрабатывать трехсторонние поиски.

Для справки:

Трехсторонний поиск – возможность получить результаты сразу по 2 критериям (например, по уровню экзамена и конкретному предмету).

Разберем подробнее на примере.

Представим, что в конце года подводятся итоги. За это время студент успел сдать экзамены трех разных уровней: вступительный, полугодовой и годовой.

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

Разберем подробнее синтаксис функции ИНДЕКС  в этом примере.

Массив: в данном случае их будет использовано 3. Указывать их нужно в круглых скобках.

Номер_строки: с помощью функции ПОИСКПОЗ выбирается нужное имя из списка студентов.

Номер_столбца: через функцию ПОИСКПОЗ находится нужное название из списка предметов.

Номер_области: на основе этих данных ИНДЕКС выбирает массив для анализа.

Здесь будет использовано 3 массива. В зависимости от выбранного типа экзамена ЕСЛИ вернет нужное значение. В свою очередь ИНДЕКС вернет нужный массив. Например, после выбора вступительного экзамена ЕСЛИ вернет «1», а ИНДЕКС выберет первый массив из предложенных.

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

=ИНДЕКС(($B$3:$E$7;$B$11:$E$15;$B$19:$E$23);ПОИСКПОЗ($G$4;$A$3:$A$7;0);ПОИСКПОЗ($H$3;$B$2:$E$2;0); ЕСЛИ($H$2=»Вступительный»;1;ЕСЛИ($H$2=»Полугодовой»;2;3)))

Видеоролик на данную тему.

Понравилась статья? Поделиться с друзьями:
Комментарии : 1
  1. yazdorova.com

    Статья расширила мой кругозор, огромное спасибо за труд!

Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: