В эксель существует большое количество разнообразных опций. Индекс – функция, которая дает возможность выбирать из массива сведений нужное значение. При этом структура, число строк, столбцов и в том числе самих массивов не имеют значения.
На данном сайте вы найдете и другие статьи по работе с программой Excel.
Содержание
- 1 Что возвращает функция
- 2 Синтаксис
- 3 Аргументы функции
- 4 Дополнительная информация
- 5 Примеры использования функции индекс в excel
- 5.1 Пример 1 самый простой способ использования
- 5.2 Пример 2 создаем динамический поиск значений с использованием функций индекс и поискпоз
- 5.3 Пример 3 создаем динамический поиск значений с использованием функций index индекс и match поискпоз и выпадающего списка
- 5.4
- 5.5 Пример 4 использование трехстороннего поиска с помощью index индекс match поискпоз
- 6 Видеоролик на данную тему.
Что возвращает функция
Функция индекс возвращает информацию, указанную в конкретной строке и столбце.
Синтаксис
Синтаксис индекс в 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))
Алгоритм создания выпадающего списка
- Выбрать ячейку, в которой будет расположен выпадающий список.
- Нажать на вкладку Данные. Выбрать пункт проверка данных.
- В пункте «Тип данных» выбрать список.
- В разделе «Источник» указать диапазон ячеек для поиска.
Пример 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)))
Статья расширила мой кругозор, огромное спасибо за труд!