Green-sell.info

Новые технологии
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Формула индекс в excel примеры

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Открывается небольшое окошко, в котором нужно выбрать один из типов функции: «Массив» или «Ссылка». Нужный нам вариант «Массив». Он расположен первым и по умолчанию выделен. Поэтому нам остается просто нажать на кнопку «OK».

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

      Прежде всего, узнаем, какую заработную плату получает работник Парфенов Д. Ф. Вписываем его имя в соответствующее поле.

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

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

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

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

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

    Результат количества заработка Парфенова Д. Ф. после обработки выводится в поле «Сумма».

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Читать еще:  Как записать видео на 2 диска

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

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

    5 вариантов использования функции ИНДЕКС (INDEX)

    Бывает у вас такое: смотришь на человека и думаешь «что за @#$%)(*?» А потом при близком знакомстве оказывается, что он знает пять языков, прыгает с парашютом, имеет семеро детей и черный пояс в шахматах, да и, вообще, добрейшей души человек и умница?

    Так и в Microsoft Excel: есть несколько похожих функций, про которых фраза «внешность обманчива» работает на 100%. Одна из наиболее многогранных и полезных — функция ИНДЕКС (INDEX) . Далеко не все пользователи Excel про нее знают, и еще меньше используют все её возможности. Давайте разберем варианты ее применения, ибо их аж целых пять.

    Вариант 1. Извлечение данных из столбца по номеру ячейки

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

    =ИНДЕКС( Диапазон_столбец ; Порядковый_номер_ячейки )

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

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

    Вариант 2. Извлечение данных из двумерного диапазона

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

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

    Т.е. функция извлекает значение из ячейки диапазона с пересечения строки и столбца с заданными номерами.

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

    Вариант 3. Несколько таблиц

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

    =ИНДЕКС( (Диапазон1;Диапазон2;Диапазон3) ; Номер_строки ; Номер_столбца ; Номер_диапазона )

    Обратите особое внимание, что в этом случае первый аргумент – список диапазонов — заключается в скобки, а сами диапазоны перечисляются через точку с запятой.

    Вариант 4. Ссылка на столбец / строку

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

    Обратите внимание, что поскольку ИНДЕКС выдает в этом варианте не конкретное значение ячейки, а ссылку на диапазон, то для подсчета потребуется заключить ее в дополнительную функцию, например СУММ (SUM) , СРЗНАЧ (AVERAGE) и т.п.

    Вариант 5. Ссылка на ячейку

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

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

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

    Формула индекс в excel примеры

    В Excel встроено порядка 500 формул. Однако львиную долю всей работы делают штук 10 основных формул, а из этих десяти особо выделяется формула ИНДЕКС (INDEX). ИНДЕКС — многогранная и мощнейшая формула Excel. Это, можно сказать, священный грааль Excel, его философский камень. При этом большинство пользователей Excel про неё не знают практически ничего, а из тех, кто знает и использует, поняли её лишь немногие. Если хотите быть в числе этих немногих, то продолжайте чтение 🙂

    Основной синтаксис

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

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

    Возврат элемента диапазона

    Обычное, типовое использование ИНДЕКС это:

    = ИНДЕКС ( A1:C5 ; 2 ; 2 )

    Формула вернёт значение из ячейки на пересечении второй строки и второго столбца диапазона A1:C5 . Это значение 5 из B2 . Для многих на этом понимание предназначения функции ИНДЕКС и заканчивается. Но только не для нас с вами, мой пытливый читатель!

    Гораздо менее известным фактом является то, как ИНДЕКС работает с векторами. Вектор — это диапазон, состоящий из одного столбца или одной строки.

    = ИНДЕКС ( A3:C3 ; 3 ) вернёт 9, и

    = ИНДЕКС ( C1:C5 ; 3 ) тоже вернёт 9!

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

    Что возвращает формула?

    Ещё одна удивительная особенность — формула ИНДЕКС возвращает ссылку на ячейку, а не значение ячейки. Внимательный читатель, конечно же, запальчиво воскрикнет, что, мол за ерунда, почему тогда предыдущий пример не вернул нам С3 , а вернул 9? Дело в том, что данный факт маскируется оператором присваивания (знак равно), с которого начинается любая формула.

    = ИНДЕКС ( C1:C5 ; 3 ) фактически проходит этап = C3 и только затем превращается в 9.

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

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

    Конструкция ИНДЕКС ( A5 ;1): ИНДЕКС ( C5 ;1) должна возвращать нам A5:C5 .

    И это действительно так, что можно подтвердить косвенно, ибо

    = СУММ ( ИНДЕКС ( A5 ;1): ИНДЕКС ( C5 ;1)) возвращает 42 (13+14+15), а

    = ЧИСЛСТОЛБ ( ИНДЕКС ( A5 ;1): ИНДЕКС ( C5 ;1)) возвращает 3.

    А можно подтвердить и напрямую через ввод формулы массива в любые 3 горизонтальных ячейки. Для этого выделите 3 ячейки за пределами A1:C5 , вытянутые в строку, нажмите знак равно и введите буквально

    = ИНДЕКС ( A5 ;1): ИНДЕКС ( C5 ;1)

    и после этого нажмите Ctrl + Shift + Enter . После чего вы увидите следующую картину:

    Далее у меня будут всречаться формулы с такими фигурными скобками

    <= ИНДЕКС ( A5 ;1): ИНДЕКС ( C5 ;1)>знайте, что это формулы массивов, которые надо вводить в ячейку или группу ячеек способом, который описан мною выше.

    Примечание: надеюсь вы понимаете, что ИНДЕКС ( A5 ;1) вернёт ссылку на A5 , так как любая ячейка может рассматриваться в качестве вектора с числом элементов равным 1. ЧИСЛСТОЛБ — формула, возвращающая количество столбцов в диапазоне.

    = ОБЛАСТИ (( ИНДЕКС ( A1 ;1); ИНДЕКС ( C3 ;1))) вернёт нам 2.

    Напомню, что функция ОБЛАСТИ возвращает количество областей внутри переданной ссылки. Этот вызов идентичен вызову = ОБЛАСТИ (( A1 ; C3 )), то есть ИНДЕКС нам успешно сгенерировал 2 области через оператор «;».

    Чтобы продемонстрировать вам, что ИНДЕКС можно использовать с оператором пересечения диапазонов, я должен сненерировать при помощи ИНДЕКС диапазоны с размером больше, чем одна ячейка, но это как раз то, что я только собираюсь вам объяснить ниже, поэтому доказательство я вам приведу, но его синтаксическая конструкция будет сложнее, чем могла бы быть. Я хочу сгенерировать через ИНДЕКС такой результат = B1:B5 A5:C5

    Формула кого-то может испугать, но на самом деле всё просто. Я буквально конструирую из ИНДЕКС именно то, что нам надо.

    = ИНДЕКС ( B1 ;1): ИНДЕКС ( B5 ;1) ИНДЕКС ( A5 ;1): ИНДЕКС ( C5 ;1)

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

    Возврат столбца / строки

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

    Конструкция ИНДЕКС ( A1:C5 ;;3) — вернёт вектор C1:C5 (столбец), так как, если пропустить параметр номер_строки (или указать его равным нулю), но указать параметр номер_столбца, то ИНДЕКС возвращает вектор указанного столбца в пределах диапазона из первого параметра.

    = СУММ ( ИНДЕКС ( A1:C5 ;;3)) вернёт 45,

    = ЧСТРОК ( ИНДЕКС ( A1:C5 ;;3)) вернёт 5, а

    В случае формулы массива обратите внимание, что, если вы ввели формулу массива в диапазон ячеек, который превышает размер возвращаемого массива, то лишние ячейки получат значения #Н/Д (смотрите ситуацию 2 на рисунке). А, если формула возвращает вертикальный массив, а принимающий диапазон расположен горизонтально, то все ячейки горизонтального диапазона получат одинаковое значение — из первого элемента возвращаемого вектора (смотрите ситуацию 3 на рисунке), и наоборот, то есть направление векторов должно совпадать.

    Читать еще:  Как научиться делать таблицу в excel

    По аналогии, конструкция ИНДЕКС ( A1:C5 ;3;) вернёт вектор A3:C3 , то есть строку. Заметьте, что наличие второй точки с запятой принципиально, и за ней должна быть либо пустота (пробелы игнорируются), либо ноль.

    А теперь вернёмся к формуле:

    = ИНДЕКС ( B1 ;1): ИНДЕКС ( B5 ;1) ИНДЕКС ( A5 ;1): ИНДЕКС ( C5 ;1) и с учётом новых знаний упростим её до вида:

    = ИНДЕКС ( A1:C5 ;;2) ИНДЕКС ( A1:C5 ;5;) — выглядит гораздо более лаконично!

    Выбор области

    Помните, что в описании синтаксиса ИНДЕКС первый параметр назывался диапазон(ы). Пришло время поговорить вот про эту букву » ы «. Выходит диапазонов может быть несколько? Да, их может быть несколько, когда они перечислены через уже упоминаемый выше оператор объединения, — «;» и взяты в скобки. Вот так:

    = СУММ ( ИНДЕКС ( ( A1:B5;B1:C5 ); ; ; A9 ) )

    Если A9 содержит 1, то ИНДЕКС вернёт диапазон A1:B5 , а если 2, то B1:C5 . Обратите внимание, что второй и третий параметры опущены, это означает, что исходные диапазоны вообще не будут подвергаться какому-либо усечению и вернутся, как есть (до этого мы «отщипывали» то строку, то столбец). В первом случае сумма будет 75, во втором — 85.

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

    Файл примера

    Скачать

    Динамические диапазоны

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

    = A1 : ИНДЕКС ( A:A ; СЧЁТЗ ( A:A )) — классика жанра, нижняя граница диапазона определяется при помощи ИНДЕКС .

    Наиболее распространенные способы определения нижней границы можно посмотреть тут:

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

    = ИНДЕКС ( ME [ Начисление ]; ПОИСКПОЗ ( ‘ L3 ‘! $G$2 ; ME [ Телефон ]; 0 ) ): ИНДЕКС ( ME [ Начисление ]; ПОИСКПОЗ ( ‘ L3 ‘! $G$2 ; ME [ Телефон ]; 1 ) )

    ПОИСКПОЗ с третьим параметром, равным 0, используется для определения первой строки с номер телефона, а она же с параметром, равным 1, — для определения последней строки. ИНДЕКС же помогает сформировать динамический диапазон. Аналогично формируется ИД Период . ИД Начисления использован для построения ряда диаграммы, а ИД Период для значений оси.

    Более мощная замена ВПР

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

    Прощайте ВПР() и СМЕЩ()

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

    Подводим итоги

    Возвращает ссылки, а не значения, что позволяет с его помощью формировать очень эффективные динамические диапазоны

    Может возвращать ссылки как на отдельные ячейки, на вектора и на целые диапазоны

    Позволяет выбирать области — ещё одна степень свободы

    Это не летучая функция

    ИНДЕКС + ПОИСКПОЗ быстрее и гибче ВПР для целей извлечения данных

    Незаменим в формулах массивов

    Это ключевая формула в Excel. Знать её просто необходимо. Серьёзная работа в Excel без неё немыслима. Ура!

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

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

    Под массивом тут подразумеваются конструкции вида <12:34:76:99>— столбец, или <12;34;76;99>— строка. Ясно, что ссылку от такого массива не вернёшь, — только значение и поэтому вроде бы как получается, что это отдельная синтаксическая форма. Пример:

    Массив <1;2;3:4;5;6:7;8;9>— не что иное, как

    Функция ИНДЕКС (INDEX). Особенности использования

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

    Функцию ИНДЕКС можно найти на вкладке «Формулы» в библиотеке функций в категории «Ссылки и массивы».

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

    Общий синтаксис функции выглядит так:

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

    Разберем аргументы подробнее.

    Аргумент 1 — Диапазон(ы)

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

    • ссылка на одномерный диапазон, например А1:А10 или А1:X1
    • ссылка на двумерный диапазон, например А1:B20
    • ссылка на несмежные диапазоны, например (А1:B5;D1:E5;H1:I5) (такую ссылку всегда следует брать в скобки, чтобы Excel понял, что «;» в этом случае выступает как оператор объединения диапазонов, а не как разделитель аргументов в функции). Диапазоны должны находиться на одном листе.
    • массив констант, например <1;2;3:4;5;6:7;8;9>. При использовании такого аргумента, функция принимает так называемую Форму массива и не может вернуть ссылку на диапазон. Также недоступен аргумент «номер области». Все остальные примеры использования аргумента относятся к «Ссылочной форме» синтаксиса функции ИНДЕКС.

    Аргумент 2 — Номер строки

    Указывает на номер строки, из которой нужно вернуть значение, или ссылку на которую нужно сделать в результате вычисления функции. Имеются следующие особенности:

    • если в первом аргументе задан одномерный диапазон (строка или столбец), то аргумент «Номер строки» указывает на порядковый номер извлекаемого из диапазона элемента;
    • если аргумент опущен или равен нулю, то возвращаются все строки;
    • аргумент должен указывать на строку в пределах диапазона. Например, если для диапазона A1:A10 указать номер строки 15, то функция вернет ошибку #ССЫЛКА!, так как строка находится за пределами диапазона.

    Аргумент 3 — Номер столбца

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

    • если в первом аргументе задан одномерный диапазон (строка или столбец), то аргумент «Номер столбца» можно не указывать (номер элемента определится по аргументу «Номер строки»);
    • если аргумент опущен или равен нулю, то возвращаются все столбцы;
    • аргумент должен указывать на столбец в пределах диапазона. Например, если для диапазона A1:C1 указать номер столбца 5, то функция вернет ошибку #ССЫЛКА!, так как столбец находится за пределами диапазона.

    Аргумент 4 — Номер области

    Используется, когда в качестве первого аргумента указан несмежный диапазон. В таком случае Excel нумерует все диапазоны в порядке, в котором они введены в функцию, и возвратит результат из того диапазона, номер которого указан в этом аргументе. Если указать номер больше, чем указано диапазонов, то формула вернет ошибку #ССЫЛКА!

    В зависимости от контекста, результатом вычисления функции может быть:

    • значение из ячейки (когда аргументы указывают строго на одну ячейку);
    • массив значений (когда аргументы указывают на массив, например, опущен аргумент № 2, или № 3, или оба);
    • ссылка на диапазон (когда в формуле перед функцией находится один из операторов работы с диапазонами: двоеточие, точка с запятой или пробел).

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

    Связка ИНДЕКС + ПОИСКПОЗ

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

    Чтобы в ячейку B18 всегда выводилось значение, находящееся в таблице A2:H14 на пересечении месяца, указанного в B16, и года, указанного в B17, нужно ввести формулу: =ИНДЕКС($B$3:$H$14;ПОИСКПОЗ(B16;$A$3:$A$14;0);ПОИСКПОЗ(B17;$B$2:$H$2;0)).

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

    Возврат значения из нужной таблицы

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

    Чтобы в ячейку B18 всегда выводилось значение, находящееся в таблице соответствующего года (указанного в B17) на пересечении месяца, указанного в B16, и магазина, указанного в B15, нужно ввести формулу: =ИНДЕКС((B2:D13;G2:I13;L2:N13);ПОИСКПОЗ(B16;A2:A13;0);ПОИСКПОЗ(B15;B1:D1;0);ПОИСКПОЗ(B17;<2015;2016;2017>;0)).

    Функции ПОИСКПОЗ находят номера строки, столбца и таблицы, а ИНДЕКС возвращает значение, находящееся на их пересечении (обратите внимание, что в последней функции ПОИСКПОЗ в качестве аргумента используется массив констант <2015;2016;2017>). Гифка ниже в хорошем качестве

    Сумма всего столбца / всей строки

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

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

    Чтобы отобразить сразу суммарное значение для одного месяца за все года, нужно ввести в B18 следующую формулу:

    Динамический диапазон

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

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

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

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

    Поддержать наш проект и его дальнейшее развитие можно вот здесь .

    Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot

    Функция ИНДЕКС (INDEX). Особенности использования

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

    Функцию ИНДЕКС можно найти на вкладке «Формулы» в библиотеке функций в категории «Ссылки и массивы».

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

    Общий синтаксис функции выглядит так:

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

    Разберем аргументы подробнее.

    Аргумент 1 — Диапазон(ы)

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

    • ссылка на одномерный диапазон, например А1:А10 или А1:X1
    • ссылка на двумерный диапазон, например А1:B20
    • ссылка на несмежные диапазоны, например (А1:B5;D1:E5;H1:I5) (такую ссылку всегда следует брать в скобки, чтобы Excel понял, что «;» в этом случае выступает как оператор объединения диапазонов, а не как разделитель аргументов в функции). Диапазоны должны находиться на одном листе.
    • массив констант, например <1;2;3:4;5;6:7;8;9>. При использовании такого аргумента, функция принимает так называемую Форму массива и не может вернуть ссылку на диапазон. Также недоступен аргумент «номер области». Все остальные примеры использования аргумента относятся к «Ссылочной форме» синтаксиса функции ИНДЕКС.

    Аргумент 2 — Номер строки

    Указывает на номер строки, из которой нужно вернуть значение, или ссылку на которую нужно сделать в результате вычисления функции. Имеются следующие особенности:

    • если в первом аргументе задан одномерный диапазон (строка или столбец), то аргумент «Номер строки» указывает на порядковый номер извлекаемого из диапазона элемента;
    • если аргумент опущен или равен нулю, то возвращаются все строки;
    • аргумент должен указывать на строку в пределах диапазона. Например, если для диапазона A1:A10 указать номер строки 15, то функция вернет ошибку #ССЫЛКА!, так как строка находится за пределами диапазона.

    Аргумент 3 — Номер столбца

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

    • если в первом аргументе задан одномерный диапазон (строка или столбец), то аргумент «Номер столбца» можно не указывать (номер элемента определится по аргументу «Номер строки»);
    • если аргумент опущен или равен нулю, то возвращаются все столбцы;
    • аргумент должен указывать на столбец в пределах диапазона. Например, если для диапазона A1:C1 указать номер столбца 5, то функция вернет ошибку #ССЫЛКА!, так как столбец находится за пределами диапазона.

    Аргумент 4 — Номер области

    Используется, когда в качестве первого аргумента указан несмежный диапазон. В таком случае Excel нумерует все диапазоны в порядке, в котором они введены в функцию, и возвратит результат из того диапазона, номер которого указан в этом аргументе. Если указать номер больше, чем указано диапазонов, то формула вернет ошибку #ССЫЛКА!

    В зависимости от контекста, результатом вычисления функции может быть:

    • значение из ячейки (когда аргументы указывают строго на одну ячейку);
    • массив значений (когда аргументы указывают на массив, например, опущен аргумент № 2, или № 3, или оба);
    • ссылка на диапазон (когда в формуле перед функцией находится один из операторов работы с диапазонами: двоеточие, точка с запятой или пробел).

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

    Связка ИНДЕКС + ПОИСКПОЗ

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

    Чтобы в ячейку B18 всегда выводилось значение, находящееся в таблице A2:H14 на пересечении месяца, указанного в B16, и года, указанного в B17, нужно ввести формулу: =ИНДЕКС($B$3:$H$14;ПОИСКПОЗ(B16;$A$3:$A$14;0);ПОИСКПОЗ(B17;$B$2:$H$2;0)).

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

    Возврат значения из нужной таблицы

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

    Чтобы в ячейку B18 всегда выводилось значение, находящееся в таблице соответствующего года (указанного в B17) на пересечении месяца, указанного в B16, и магазина, указанного в B15, нужно ввести формулу: =ИНДЕКС((B2:D13;G2:I13;L2:N13);ПОИСКПОЗ(B16;A2:A13;0);ПОИСКПОЗ(B15;B1:D1;0);ПОИСКПОЗ(B17;<2015;2016;2017>;0)).

    Функции ПОИСКПОЗ находят номера строки, столбца и таблицы, а ИНДЕКС возвращает значение, находящееся на их пересечении (обратите внимание, что в последней функции ПОИСКПОЗ в качестве аргумента используется массив констант <2015;2016;2017>). Гифка ниже в хорошем качестве

    Сумма всего столбца / всей строки

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

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

    Чтобы отобразить сразу суммарное значение для одного месяца за все года, нужно ввести в B18 следующую формулу:

    Динамический диапазон

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

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

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

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

    Поддержать наш проект и его дальнейшее развитие можно вот здесь .

    Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot

    Ссылка на основную публикацию
    Adblock
    detector