В excel функция index: ИНДЕКС (функция ИНДЕКС) — Служба поддержки Майкрософт

Связь таблиц в Excel. ИНДЕКС, СМЕЩ и ПОИСКПОЗ.

Количество просмотров: 1 715

Функция ИНДЕКС, ПОИСКПОЗ и СМЕЩ. Постановка задачи

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

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

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

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

• Искомое значение. Это значение, которое мы будем искать в списке. Значение должно быть уникальным, иначе Excel найдет только первое!
• Диапазон списка. Может состоять только из одного столбца или одной сроки. Это важно! Если попробовать выделить две колонки или две строки, программа выдаст ошибку!
• Способ поиска. Тут надо указать вариант, согласно которому приложение выполнит поиск позиции для нашего искомого значения. Тут возможны следующие типы.

1  Будет найдено ближайшее к нашему значение, которое не превышает его, то есть НЕ БОЛЬШЕ нужного. Равным может быть, но не больше. Список должен располагаться ПО ВОЗРАСТАНИЮ.

-1  Excel найдет значение, которое самое близкое к заданному нами, но НЕ МЕНЬШЕ ЕГО. Список должен быть ПО УБЫВАНИЮ.

0   Запустится поиск ТОЧНОГО СООТВЕТСТВИЯ значения из списка заданному нами. Сортировка тут НЕ ТРЕБУЕТСЯ.

Наглядное применение ПОИСКПОЗ.

Посмотрите на скриншот.

Особое внимание обратите вот на что. Во второй и третьей формуле задан тип поиска равный 1 (единице). Однако если в первой из них Excel искал число, которое действительно присутствует в списке, то есть число 50, то он его порядковый номер и указал. А вот числа 68 в списке нет, поэтому он указал порядковый номер значения из списка, которое находится ближе всего к нужному нам, то есть к 68, но его не превышает. А это – число 60.

И еще одна тонкость. Если использовать тип поиска, или, как пишется в справке Excel по этой функции, тип сопоставления, единицу, и в качестве искомого задать значение, которое будет явно больше любого в списке, то Excel покажет номер самого последнего элемента. Таким образом можно узнать, какая строка является последней в таблице. Вот пример:

Замечу, что для текстовых списков надо указывать максимально возможное ТЕКСТОВОЕ значение, например, так: «ЯЯЯ». В случае с числовыми данными указывает максимальное ЧИСЛОВОЕ значение, например, 99999999.

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

Отметим, что функция индекс, включенная в состав другой функции, начинает срабатывать как часть адреса. В следующем примере Excel рассчитывает сумму за первые 6 месяцев. Информацию о номере последнего месяца он берет из ячейки R4, закрашенной зеленым цветом.

Стоит поменять в ней значение, и результат функции СУММ поменяется.

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

1. Области, которые будут обработаны. Их пишут в отдельных скобках, разделяя точкой с запятой или запятой в зависимости от ваших настроек.
2. Строка в выбранной области, которая интересует
3. Столбец, на пересечении которого с указанной строкой надо взять значение
4. Область из перечисленных в начале.

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

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

Функция СМЕЩ в Excel и тонкости ее применения.

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

СМЕЩ(1;2;3;4;5)

1. Ссылка, от которой отсчитывается перемещение.
2. На сколько строк надо переместиться.
3. Сколько  столбцов надо отсчитать для перемещения.
4. Объем строк в диапазоне, на которые перемещаемся. Указывать не обязательно.
5. Количество столбцов, на которые перемещаемся. Тоже указывать не обязательно.

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

Приведем пример.

В примере выше происходит перемещение от ячейки G11 сначала на 2 строчки вверх, затем на 4 колонки влево. В найденной ячейки находится число 10, которое и является результатом работы функции.

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

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

Первый вариант создания “левого ВПР”. Комбинируем ИНДЕКС и ПОИСКПОЗ. 

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

Данные формулы конечно можно объединить в одну. Если в последней формуле вместо адреса К8 указать ее содержимое, то есть записать ПОИСКПОЗ(K6;C7:C86;0), то результат работы не изменится. Итоговая формула будет уже такой:

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

Второй вариант создания “левого ВПР”. Комбинируем СМЕЩ и ПОИСКПОЗ.

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

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

Особенности связки СМЕЩ и ПОИСКПОЗ

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

Делаем выводы.

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

На этом наше занятие можно считать законченным. Попробуйте сами применить полученные знания. Вначале поработайте на учебных материалах, а уже потом и в рабочих документах. Всем удачи и успехов в использовании Excel !

50302cookie-checkФункция СМЕЩ, функция ИНДЕКС, функция ПОИСКПОЗ в Excel.no

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

Содержание

• Использование функции ИНДЕКС
  • Способ 1: использование оператора ИНДЕКС для массивов
  • Способ 2: применение в комплексе с оператором ПОИСКПОЗ
  • Способ 3: обработка нескольких таблиц
  • Способ 4: вычисление суммы
• Вопросы и ответы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

5. Результат обработки выводится в ячейку, которая была указана в первом пункте данной инструкции. Именно выведенная фамилия является третьей в списке в выделенном диапазоне данных.

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

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

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

Урок: Мастер функций в Экселе

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

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

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

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

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

• Искомое значение – это значение, позицию которого в диапазоне мы ищем;
• Просматриваемый массив – это диапазон, в котором находится это значение;
• Тип сопоставления – это необязательный параметр, который определяет, точно или приблизительно искать значения. Мы будем искать точные значения, поэтому данный аргумент не используется.

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

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

1. Прежде всего, узнаем, какую заработную плату получает работник Парфенов Д. Ф. Вписываем его имя в соответствующее поле.
2. Выделяем ячейку в поле «Сумма», в которой будет выводиться итоговый результат. Запускаем окно аргументов функции ИНДЕКС для массивов.

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

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

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

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

3. Результат количества заработка Парфенова Д. Ф. после обработки выводится в поле «Сумма».
4. Теперь, если в поле «Имя» мы изменим содержимое с «Парфенов Д.Ф.», на, например, «Попова М. Д.», то автоматически изменится и значение заработной платы в поле «Сумма».

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

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

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

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

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

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

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

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

3. После этого в предварительно выделенную ячейку выводятся результаты вычисления. Там отображается сумма заработной платы второго по счету работника (Сафронова В. М.) за третий месяц.

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

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

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

=СУММ(адрес_массива)

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

=СУММ(C4:C9)

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

=СУММ(C4:ИНДЕКС(C4:C9;6))

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

Урок: Полезные функции Excel

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

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

Главная » Встроенные функции Excel » Функции поиска и ссылки в Excel » Функция индекса Excel

Описание функции

Функция индекса Excel возвращает ссылку на ячейку, которая находится в указанная строка и столбец диапазона ячеек.

Существует два формата функции: формат массива (самый простой формат) и формат диапазона функции. Они описаны отдельно ниже.

Формат массива функции ИНДЕКС

Формат перехода ДИАПАЗОН функции ИНДЕКС

Формат массива функции ИНДЕКС Excel

Формат массива функции индекса используется для поиска ссылка на ячейку в пределах одного диапазона.

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

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

Где аргументы следующие:

Обратите внимание, что аргументы row_num и [col_num] не могут быть оба должны быть нулевыми или пустыми.

Функция индекса Excel (формат массива) Примеры

Пример 1

В следующем примере функция индекса возвращает ссылку на строку 5 диапазона C1:C5, которая является ячейкой C5 . Это имеет значение 8 .

Пример 20072 Д5

. Это имеет значение 3 .

Пример 3 диапазон, который является диапазоном

C5:D5 .

В этом примере результат функции Index затем передается в функцию Sum, поэтому формула возвращает сумму диапазона C5:D5, что равно 11 .

В приведенном выше примере 3 функция Index вернула диапазон C5:D5. Если бы мы хотели ввести значения каждой из ячеек, C5 и D5 в нашу электронную таблицу, функцию индекса нужно было бы ввести как формулу массива. Это показано в примере ниже.

Пример 4

Формулы массива:

Чтобы ввести формулу массива, необходимо сначала выделить диапазон ячеек, который будет содержать результат функции. Введите свою функцию в первую ячейку диапазона и нажмите Ctrl + Shift + Enter.

Перейдите на страницу Формулы массива Excel для получения более подробной информации.

В следующем примере предоставленный row_num равен 0, поэтому функция Index возвращает ссылку на все столбца 2 предоставленного диапазона C1:D5. т.е. функция возвращает ссылку на диапазон D1:D5 .

В этом случае функция вводится в ячейки A1:A5 как формула массива. Это видно по фигурным скобкам, окружающим функцию в строке формул электронной таблицы результатов.

Формат диапазона, используемый функцией ИНДЕКС Excel область.

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

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

Где аргументы следующие:

Обратите внимание, что любой (но не оба) аргумент row_num или [col_num] может быть нулевым или пустым. Если они и равны нулю или пусты, функция Index вернет ошибку.

Функция индекса Excel (формат диапазона) Примеры

Пример 1

В следующем примере функция индекса возвращает ссылку на строку 4 и столбец 2 первой области в предоставленном диапазоне. Это ячейка D4 , которая оценивается как значение 5 9.0073 .

Пример 2 Это ячейка

B12 , которая оценивается как значение 7 .

Пример 3

ноль поставляемого ассортимента. это диапазон B12:D12 .

Результат функции индекса затем передается в функцию СУММ, которая возвращает значение 10 .

Дополнительные сведения и примеры функции индекса Excel см. на веб-сайте Microsoft Office.

Ошибки индексной функции

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

Распространенные ошибки

Вернуться к Функциям поиска и справочника Excel Страница
Вернуться к списку Все встроенные функции Excel

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

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

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

Функция ИНДЕКС в Excel имеет два формата: формат массива (самый простой формат) и формат диапазона. В этом руководстве вы узнаете, как использовать оба формата функции ИНДЕКС в Excel.

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

Синтаксис

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

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

Аргументы

Массив

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

номер_строки – Требуется. Это позиция строки, из которой должно быть извлечено значение. Если опущено, требуется col_num.

[номер_столбца] — Необязательно. Это позиция столбца в ссылке или массиве. Если опущено, row_num требуется.

Возврат

Значение элемента в таблице или массиве, выбранное с помощью индексов номеров строк и столбцов.

Примечания

• Если используются аргументы row_num и col_num, функция ИНДЕКС возвращает значение в ячейке на пересечении этих двух аргументов.
• Если для row_num и col_num установлено значение 0 (ноль), ИНДЕКС возвращает массив значений для всего столбца или строки соответственно. Чтобы использовать значения, возвращенные в виде массива, введите функцию ИНДЕКС как формулу массива в горизонтальном диапазоне ячеек для строки и в вертикальном диапазоне ячеек для столбца. Чтобы ввести формулу массива, нажмите CTRL+SHIFT+ENTER.

Формат диапазона

Синтаксис

Формат Range функции Index можно использовать для извлечения ссылок из диапазонов, состоящих из более чем одной области. Синтаксис функции:

ИНДЕКС(диапазон, row_num, [col_num], [area_num] )

Аргументы

Диапазон – Обязательный. Указанный массив или диапазон ячеек. Если несколько областей вставляются непосредственно в функцию, отдельные области должны быть разделены запятыми и заключены в квадратные скобки (например, A1:B2, C3:D4 и т. д.).

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

[номер_столбца] — необязательно. Обозначает номер столбца указанной области. Если установлено значение ноль или пусто, по умолчанию используются все столбцы указанной области в пределах предоставленного диапазона.

[номер_зоны] — необязательно. Это диапазон в ссылке, который следует использовать. Если этот аргумент опущен, по умолчанию он принимает значение 1 (т. е. ссылка берется из первой области в предоставленном диапазоне.

Возврат

Извлечение ссылок из диапазонов, состоящих более чем из одной области.

Примечания

• После того, как Reference и Area_num выбрали конкретный диапазон, row_num и col_num выбирают конкретную ячейку: row_num 1 — первая строка в диапазоне, col_num 1 — первый столбец и т. д. Ссылка, возвращаемая INDEX, является пересечением Row_num и Column_num.
• Если задать для row_num или column_num значение 0 (ноль), ИНДЕКС возвращает массив ссылок для всего столбца или строки соответственно.
• row_num, column_num и area_num должны указывать на ячейку в пределах ссылки; в противном случае ИНДЕКС возвращает #ССЫЛКА! ошибка. Если номер_строки и номер_столбца опущены, ИНДЕКС возвращает область в ссылке, указанную номером_области.
• Результат функции ИНДЕКС является ссылкой и интерпретируется как таковая другими формулами. В зависимости от формулы возвращаемое значение ИНДЕКС может использоваться как ссылка или как значение.

Примеры

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

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

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

пример изображения:

1. Нажмите на ячейку E2 , где будет отображаться результат.
2. Нажмите на вкладку Формулы в меню ленты.
3. Выберите Lookup and Reference на ленте, чтобы открыть раскрывающийся список функций.
4. Щелкните ИНДЕКС в списке, чтобы вызвать диалоговое окно функции.
5. В поле Select Arguments выберите параметр, относящийся к массиву 9Массив 0011, row_num, col_num .
6. Выделите ячейки с A2 по A8 на листе, чтобы ввести массив в диалоговом окне.
7. Щелкните строку row_num в диалоговом окне.
8. Вставьте число 4, откуда Excel будет извлекать значение.
9. Нажмите на строку col_num в диалоговом окне.
10. Введите число 2 в этой строке, чтобы вернуть значение из столбца 2, которое равно лет опыта.
11. Нажмите OK , чтобы завершить функцию и закрыть диалоговое окно.
12. Число 7 появляется в ячейке E2, поскольку сотрудник в четвертой строке, Свен Аткинс , имеет 7 лет опыта.

При нажатии на ячейку E2 полная функция =ИНДЕКС(A2 :B7,4,2 ) появляется в строке формул над рабочим листом.

Индексная функция Excel (формат массива)

В следующем примере назначьте формулу =ИНДЕКС(A2:B7,4,2) ячейке E2. Функция Index возвращает ссылку на строку 4 диапазона A2:B7, которая является ячейкой B5.