для чего предназначена функция индекс в ms excel

Функция ИНДЕКС() в EXCEL

history 29 декабря 2013 г.

Синтаксис функции

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

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

Номер_строки — номер строки в массиве, из которой требуется возвратить значение. Если аргумент «номер_строки» опущен, аргумент «номер_столбца» является обязательным.

Номер_столбца — номер столбца в массиве, из которого требуется возвратить значение. Если аргумент «номер_столбца» опущен, аргумент «номер_строки» является обязательным.

Если используются оба аргумента — и «номер_строки», и «номер_столбца», — то функция ИНДЕКС() возвращает значение, находящееся в ячейке на пересечении указанных строки и столбца.

Значения аргументов «номер_строки» и «номер_столбца» должны указывать на ячейку внутри заданного массива; в противном случае функция ИНДЕКС() возвращает значение ошибки #ССЫЛКА! Например, формула =ИНДЕКС(A2:A13;22) вернет ошибку, т.к. в диапазоне А2:А13 только 12 строк.

Значение из заданной строки диапазона

Пусть имеется одностолбцовый диапазон А6:А9.

Если диапазон горизонтальный (расположен в одной строке, например, А6:D6 ), то формула для вывода значения из 2-го столбца будет выглядеть так =ИНДЕКС(A6:D6;;2)

Значение из заданной строки и столбца таблицы

Пусть имеется таблица в диапазоне А6:B9.

Использование функции в формулах массива

Зачем это нужно? Теперь удалить по отдельности значения из ячеек А21 , А22 , А23 не удастся, мы получим предупреждение «нельзя изменять часть массива».

Хотя можно просто ввести в этих 3-х ячейках ссылки на диапазон А6:А8. Выделите 3 ячейки и введите формулу =A6:A8. Затем нажмите CTRL+SHIFT+ENTER и получим тот же результат.

Использование массива констант

Вместо ссылки на диапазон можно использовать массив констант :

ПОИСКПОЗ() + ИНДЕКС()

Формула =ВПР(«яблоки»;A35:B38;2;0) аналогична формуле =ИНДЕКС(B35:B38;ПОИСКПОЗ(«яблоки»;A35:A38;0)) которая извлекает цену товара Яблоки из таблицы, размещенную в диапазоне A35:B38

Ссылочная форма

Функция ИНДЕКС() позволяет использовать так называемую ссылочную форму. Поясним на примере.

получим универсальное решение, в котором требуется изменять только последний аргумент (если в формуле выше вместо 4 ввести 5, то будет подсчитана сумма первых 5-и значений).

Использование функции ИНДЕКС() в этом примере принципиально отличается от примеров рассмотренных выше, т.к. функция возвращает не само значение, а ссылку (адрес ячейки) на значение. Вышеуказанная формула =СУММ(A2:ИНДЕКС(A2:A10;4)) эквивалентна формуле =СУММ(A2:A5)

Аналогичный результат можно получить используя функцию СМЕЩ()

Теперь более сложный пример, с областями.

Пусть имеется таблица продаж нескольких товаров по полугодиям.

Источник

Функция ИНДЕКС в программе Microsoft Excel

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

Использование функции ИНДЕКС

Оператор ИНДЕКС относится к группе функций из категории «Ссылки и массивы». Он имеет две разновидности: для массивов и для ссылок.

Вариант для массивов имеет следующий синтаксис:

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

Синтаксис для ссылочного варианта выглядит так:

Тут точно так же можно использовать только один аргумент из двух: «Номер строки» или «Номер столбца». Аргумент «Номер области» вообще является необязательным и он применяется только тогда, когда в операции участвуют несколько диапазонов.

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

Способ 1: использование оператора ИНДЕКС для массивов

Давайте, прежде всего, разберем на простейшем примере алгоритм использования оператора ИНДЕКС для массивов.

Имеем таблицу зарплат. В первом её столбце отображены фамилии работников, во втором – дата выплаты, а в третьем – величина суммы заработка. Нам нужно вывести имя работника в третьей строке.

В поле «Массив» нужно указать адрес обрабатываемого диапазона данных. Его можно вбить вручную. Но для облегчения задачи мы поступим иначе. Ставим курсор в соответствующее поле, а затем обводим весь диапазон табличных данных на листе. После этого адрес диапазона тут же отобразится в поле.

В поле «Номер строки» ставим цифру «3», так как по условию нам нужно определить третье имя в списке. В поле «Номер столбца» устанавливаем число «1», так как колонка с именами является первой в выделенном диапазоне.

После того, как все указанные настройки совершены, щелкаем по кнопке «OK».

Мы разобрали применение функции ИНДЕКС в многомерном массиве (несколько столбцов и строк). Если бы диапазон был одномерным, то заполнение данных в окне аргументов было бы ещё проще. В поле «Массив» тем же методом, что и выше, мы указываем его адрес. В данном случае диапазон данных состоит только из значений в одной колонке «Имя». В поле «Номер строки» указываем значение «3», так как нужно узнать данные из третьей строки. Поле «Номер столбца» вообще можно оставить пустым, так как у нас одномерный диапазон, в котором используется только один столбец. Жмем на кнопку «OK».

Результат будет точно такой же, что и выше.

Это был простейший пример, чтобы вы увидели, как работает данная функция, но на практике подобный вариант её использования применяется все-таки редко.

Способ 2: применение в комплексе с оператором ПОИСКПОЗ

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

Основной задачей функции ПОИСКПОЗ является указание номера по порядку определенного значения в выделенном диапазоне.

Синтаксис оператора ПОИСКПОЗ такой:

=ПОИСКПОЗ(искомое_значение, просматриваемый_массив, [тип_сопоставления])

С помощью этого инструмента можно автоматизировать введение аргументов «Номер строки» и «Номер столбца» в функцию ИНДЕКС.

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

В поле «Массив» вносим координаты столбца, в котором находятся суммы заработных плат работников.

Поле «Номер столбца» оставляем пустым, так как мы используем для примера одномерный диапазон.

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

После того, как все значения внесены, жмем на кнопку «OK».

Способ 3: обработка нескольких таблиц

Теперь посмотрим, как с помощью оператора ИНДЕКС можно обработать несколько таблиц. Для этих целей будет применяться дополнительный аргумент «Номер области».

Имеем три таблицы. В каждой таблице отображена заработная плата работников за отдельный месяц. Нашей задачей является узнать заработную плату (третий столбец) второго работника (вторая строка) за третий месяц (третья область).

В поле «Номер строки» указываем цифру «2», так как ищем вторую фамилию в списке.

В поле «Номер столбца» указываем цифру «3», так как колонка с зарплатой является третьей по счету в каждой таблице.

В поле «Номер области» ставим цифру «3», так как нам нужно найти данные в третьей таблице, в которой содержится информация о заработной плате за третий месяц.

После того, как все данные введены, щелкаем по кнопке «OK».

Способ 4: вычисление суммы

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

При сложении суммы СУММ имеет следующий синтаксис:

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

Но можно её немного модифицировать, использовав функцию ИНДЕКС. Тогда она будет иметь следующий вид:

В этом случае в координатах начала массива указывается ячейка, с которой он начинается. А вот в координатах указания окончания массива используется оператор ИНДЕКС. В данном случае первый аргумент оператора ИНДЕКС указывает на диапазон, а второй – на последнюю его ячейку – шестую.

Как видим, функцию ИНДЕКС можно использовать в Экселе для решения довольно разноплановых задач. Хотя мы рассмотрели далеко не все возможные варианты её применения, а только самые востребованные. Существует два типа этой функции: ссылочный и для массивов. Наиболее эффективно её можно применять в комбинации с другими операторами. Созданные таким способом формулы смогут решать самые сложные задачи.

Помимо этой статьи, на сайте еще 12509 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Источник

ИНДЕКС (функция ИНДЕКС)

Функция ИНДЕКС возвращает значение или ссылку на значение из таблицы или диапазона.

Функцию ИНДЕКС можно использовать двумя способами:

Если вы хотите возвращать значение указанной ячейки или массива ячеек, см. раздел Форма массива.

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

Форма массива

Описание

Возвращает значение элемента таблицы или массива, выбранного индексами номеров строк и столбцов.

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

Синтаксис

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

Аргументы формы массива функции ИНДЕКС следующую:

Массива — обязательный аргумент. Диапазон ячеек или константа массива.

Если массив содержит только одну строку или один столбец, row_num или column_num аргумент не является обязательным.

Если массив имеет несколько строк и несколько столбцов и используется только row_num или column_num, индекс возвращает массив всей строки или столбца в массиве.

Номер_строки Обязательно, если column_num нет. Выбирает строку в массиве, из которой требуется возвратить значение. Если row_num опущен, column_num требуется.

Номер_столбца — необязательный аргумент. Выбирает столбец в массиве, из которого требуется возвратить значение. Если column_num опущен, row_num требуется.

Замечания

Если используются row_num и column_num, индекс возвращает значение ячейки на пересечении row_num и column_num.

row_num и column_num должны указать на ячейку в массиве; в противном случае индекс возвращает #REF! ошибку «#ВЫЧИС!».

Примеры

Пример 1

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

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — ВВОД.

Значение ячейки на пересечении второй строки и второго столбца в диапазоне A2:B3.

Значение ячейки на пересечении второй строки и первого столбца в диапазоне A2:B3.

Пример 2

В этом примере функция ИНДЕКС используется в формуле массива для поиска значений двух заданных ячеек в массиве с диапазоном 2 x 2.

Значение ячейки на пересечении первой строки и второго столбца в массиве. Массив содержит значения 1 и 2 в первой строке и значения 3 и 4 во второй строке.

Значение ячейки на пересечении второй строки и второго столбца в массиве, указанном выше.

Форма справки

Описание

Возвращает ссылку на ячейку, расположенную на пересечении указанной строки и указанного столбца. Если ссылка состоит из несмежных выборок, можно выбрать выбор, который нужно найти.

Синтаксис

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

Аргументы функции ИНДЕКС могут быть следующими:

Ссылки — обязательный аргумент. Ссылка на один или несколько диапазонов ячеек.

Если для ссылки ввести несмежный диапазон, заключите ссылку в скобки.

Если каждая область в ссылке содержит только одну строку или столбец, row_num или column_num аргумент соответственно является необязательным. Например, для ссылки на единственную строку нужно использовать формулу ИНДЕКС(ссылка,,номер_столбца).

Номер_строки — обязательный аргумент. Номер строки в диапазоне, заданном аргументом «ссылка», из которого требуется возвратить ссылку.

Номер_столбца — необязательный аргумент. Номер столбца в диапазоне, заданном аргументом «ссылка», из которого требуется возвратить ссылку.

area_num Необязательный. Выбирает диапазон в ссылке, из которого будут возвращаться пересечения row_num и column_num. Первая выбранная или введенная область имеет номер 1, вторая — 2 и так далее. Если area_num опущен, в индексе используется область 1. Перечисленные здесь области должны быть расположены на одном листе. Если указать области, которые не находятся на одном листе друг с другом, это приведет к #VALUE! ошибку «#ВЫЧИС!». Если вам нужно использовать диапазоны, расположенные на разных листах друг от друга, рекомендуется использовать форму массива функции ИНДЕКС и использовать другую функцию для вычисления диапазона, который составляет массив. Например, с помощью функции ВЫБОР можно вычислить диапазон, который будет использоваться.

Например, если в справочнике описаны ячейки (A1:B4;D1:E4,G1:H4), area_num 1 — диапазон A1:B4, area_num 2 — диапазон D1:E4, а area_num 3 — диапазон G1:H4.

Замечания

После ссылки и area_num вы выбрали определенный диапазон, row_num и column_num выберем определенную ячейку: row_num 1 — первую строку диапазона, column_num 1 — первый столбец и так далее. Ссылка, возвращаемая индексом, является пересечением row_num и column_num.

Если для row_num или column_num 0, индекс возвращает ссылку на весь столбец или строку соответственно.

row_num, column_num и area_num должны указать на ячейку в ссылке; в противном случае индекс возвращает #REF! ошибку «#ВЫЧИС!». Если row_num и column_num опущены, индекс возвращает область в ссылке, указанной area_num.

Результатом вычисления функции ИНДЕКС является ссылка, которая интерпретируется в качестве таковой другими функциями. В зависимости от формулы значение, возвращаемое функцией ИНДЕКС, может использоваться как ссылка или как значение. Например, формула ЯЧЕЙКА(«ширина»;ИНДЕКС(A1:B2;1;2)) эквивалентна формуле ЯЧЕЙКА(«ширина»;B1). Функция ЯЧЕЙКА использует значение, возвращаемое функцией ИНДЕКС, как ссылку. С другой стороны, такая формула, как 2*ИНДЕКС(A1:B2;1;2), преобразует значение, возвращаемое функцией ИНДЕКС, в число в ячейке B1.

Примеры

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД.

Источник

Excel-plus

Пошаговые инструкции, как использовать функции, формулы и другие инструменты табличного редактора Microsoft Excel

Функция ИНДЕКС в Excel. Описание и примеры применения.

Функция ИНДЕКС в Excel. Описание и примеры применения.

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

Как вызвать функцию ИНДЕКС в Excel.

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

Как работает функция ИНДЕКС в Excel.

После того, как в диалоговом окне Вставка функции выбрана функция ИНДЕКС, откроется диалоговое окно Мастер функции — шаг 1а из 2. В данном диалоговом окне будет предложено два способы использования функции ИНДЕКС:

Вариант использования функции ИНДЕКС: массив; номе_строки; номер_столбца.

Выбираем первый вариант: массив; номе_строки; номер_столбца. Открывается диалоговое окно Аргументы функции. В данном диалогов окне есть три поля:

ВАЖНО: В полем Массив можно указывать диапазон конкретной таблицы или всего Листа. В зависимости от того, какой диапазон будет указан, зависит от куда необходимо отсчитывать номер строки и столбца. Если указывается конкретный диапазон (таблица), то номер строки необходимо отсчитывать от самой верхней строки этого диапазона (таблицы), а номер столбца необходимо отсчитывать от самого левого столбца диапазона (таблицы). Если указывается весь Лист, то отсчет видеться от самой верхней строки и самого левого столбца Листа, соответственно.

В нашем примере укажем конкретный диапазон ячеек, в котором содержаться имена (Таблица: В3:D6). Номер строки укажем — 4, а номер столбца — 3.

Итак, функция ИНДЕКС вернула значение Владимир. Это значение находиться в ячейки, которая находиться на пересечении 4 строки и 3 столбца указанного диапазона ячеек.

Теперь, в качестве диапазона, в диалоговом окне Аргументы функции, укажем весь Лист (поле Массив). А номер строки и столбца оставим те же. В результате получим:

Как видно в первом случае функция вернула нам значение Владимир, а во втором значение Егор. Разница возникла потому что в первом случае отсчет строк и столбцов шёл от границ конкретного указного диапазоне (таблицы), а во втором от крайней верхней строки и крайнего левого столбца Листа.

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

Пример: все имена расположены в одном столбце. Диапазон ячеек: B2:B13 (значение в поле Массив). Тогда в поле Номер_ строки указываем значение 5, а в поле Номер_столбца ни чего не указываем.

Нажимаем ОК, функция ИНДЕКС возвращает значение: Петр.

Вариант использования функции ИНДЕКС: ссылка; номе_строки; номер_столбца; номер _области.

В диалоговом окне Мастер функции — шаг 1а из 2. выбираем способ использования функции ИНДЕКС: ссылка; номе_строки; номер_столбца; номер _области. Появляется диалоговое окно: Аргументы функции. В данном диалогов окне есть три поля:

Рассмотрим данный способ использования функции ИНДЕКС на конкретном примере. Добавим в нашу таблицу четвертый столбец с именами. В диалоговом окне Аргументы функции, в поле Ссылка укажем два диапазона: B3:C6 и D3:E6. В поле Номер_строки укажем значение 3, в поле Номер_столбца укажем значение 2. В поле Номер_области укажем значение 2.

Важно: несколько диапазонов, которые указываются в поле Ссылка, необходимо заключать в скобки и перечислять через точку с запятой: (B3:C6;D3:E6)

Функция ИНДЕКС вернула значение Борис, которое находиться на пересечение 3 строки и 2 столбца. При этом, отсчет данных координат проходил относительно границ диапазона №2 ( D3:E6).

Источник