Excel индексы для группы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ссылки по теме

• Трехмерный поиск данных по нескольким листам (ВПР 3D)
• Поиск и подстановка по нескольким условиям (ВПР по нескольким столбцам)
• Подстановка данных из одной таблицы в другую с помощью функции ВПР (VLOOKUP)

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

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

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

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

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

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

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

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

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

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

Способ 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

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

Время на прочтение
16 мин

Количество просмотров 18K

Иногда мне бывает скучно и я, вооружившись отладчиком, начинаю копаться в разных программах. В этот раз мой выбор пал на Excel и было желание разобраться как он оперирует высотами рядов, в чём хранит, как считает высоту диапазона ячеек и т.д. Разбирал я Excel 2010 (excel.exe, 32bit, version 14.0.4756.1000, SHA1 a805cf60a5542f21001b0ea5d142d1cd0ee00b28).

Начнём с теории

Если обратиться к документации по VBA для Microsoft Office, то можно увидеть, что высоту ряда так или иначе можно получить через два свойства:

• RowHeight — Returns or sets the height of the first row in the range specified, measured in points. Read/write Double;
• Height — Returns a Double value that represents the height, in points, of the range. Read-only.

Причём если заглянуть ещё и сюда: Excel specifications and limits. То можно обнаружить, что максимальная высота ряда составляет 409 точек. Это к сожалению далеко не единственный случай, когда официальные документы Microsoft немножко лукавят. На самом деле в коде Excel максимальная высота ряда задана как 2047 пикселя, что в точках будет 1535.25. А максимальный размер шрифта 409.55 пунктов. Получить ряд такой огромной высоты простым присваиванием в VBA/Interop не получится, но можно взять ряд, задать его первой ячейке шрифт Cambria Math, а размер шрифта выставить в 409.55 пунктов. Тогда Excel своим хитрым алгоритмом посчитает высоту ряда на основе формата ячейки, получит число превышающее 2047 пикселей (поверьте на слово) и сам выставит у ряда максимально возможную высоту. Если спросить высоту этого ряда через UI, то Excel соврёт что высота 409.5 точек, но если запросить высоту ряда через VBA, то получится честные 1535,25 точек, что равняется 2047 пикселям. Правда после сохранения документа высота всё равно сбросится до 409,5 точек. Эту манипуляцию можно пронаблюдать вот на этом видео: http://recordit.co/ivnFEsELLI

Я не зря упомянул в предыдущем абзаце пиксели. Excel на самом деле хранит и рассчитывает размеры ячеек в целых числах (он вообще максимально всё делает в целых числах). Чаще всего это пиксели, умноженные на некоторый коэффициент. Интересно, что Excel хранит масштаб внешнего вида в виде обыкновенной дроби, например, масштаб 75% будет храниться как два числа 3 и 4. И когда надо будет вывести на экран ряд, Excel возьмёт высоту ряда как целое число пикселей, умножит на 3 и разделит на 4. Но выполнять эту операцию он будет уже в самом конце от этого создаётся эффект, что всё считается в дробных числах. Чтобы в этом убедиться напишите в VBA вот такой код:

w.Rows(1).RowHeight = 75.375
Debug.Print w.Rows(1).Height

VBA выдаст 75, т.к. 75,375 точек будет 100,5 пикселей, а Excel такое себе позволить не может и отбросит дробную часть до 100 пикселей. Когда VBA будет запрашивать высоту ряда в точках, Excel честно переведёт 100 пикселей в точки и вернёт 75.

В принципе мы уже подобрались к тому, чтобы написать класс на C#, который будет описывать информацию о высоте ряда:

class RowHeightInfo {
    public ushort Value { get; set; } //высота ряда в целых пикселях, умноженная на 4.
    public ushort Flags { get; set; } //дополнительные флаги
}

Вам пока что придётся поверить мне на слово, но в Excel высота ряда хранится именно так. Т.е., если задано, что высота ряда 75 точек, в пикселях это будет 100, то в Value будет хранится 400. Что обозначают все биты в Flags я до конца не выяснил (выяснять значения флагов сложно и долго), но знаю точно, что 0x4000 выставляется для рядов у которых высота задана вручную, а 0x2000 — выставляется у скрытых рядов. В целом у видимых рядов с заданной вручную высотой Flags чаще всего равняется 0x4005, а для рядов у которых высота высчитывается на основе форматирования Flags равняется либо 0xA, либо 0x800E.

Спрашиваем высоту ряда

Теперь в принципе можно взглянуть на метод из excel.exe, который возвращает высоту ряда по его индексу (спасибо HexRays за красивый код):

int __userpurge GetRowHeight@<eax>(signed int rowIndex@<edx>, SheetLayoutInfo *sheetLayoutInfo@<esi>, bool flag)
{
  RowHeightInfo *rowHeightInfo; // eax
  int result; // ecx

  if ( sheetLayoutInfo->dword1A0 )
    return sheetLayoutInfo->defaultFullRowHeightMul4 | (~(sheetLayoutInfo->defaultRowDelta2 >> 14 << 15) & 0x8000);
  if ( rowIndex < sheetLayoutInfo->MinRowIndexNonDefault )
    return sheetLayoutInfo->defaultFullRowHeightMul4 | (~(sheetLayoutInfo->defaultRowDelta2 >> 14 << 15) & 0x8000);
  if ( rowIndex >= sheetLayoutInfo->MaxRowIndexNonDefault )
    return sheetLayoutInfo->defaultFullRowHeightMul4 | (~(sheetLayoutInfo->defaultRowDelta2 >> 14 << 15) & 0x8000);
  rowHeightInfo = GetRowHeightCore(sheetLayoutInfo, rowIndex);
  if ( !rowHeightInfo )
    return sheetLayoutInfo->defaultFullRowHeightMul4 | (~(sheetLayoutInfo->defaultRowDelta2 >> 14 << 15) & 0x8000);
  result = 0;
  if ( flag || !(rowHeightInfo->Flags & 0x2000) )
    result = rowHeightInfo->Value;
  if ( !(rowHeightInfo->Flags & 0x4000) )
    result |= 0x8000u;
  return result;
}

Что такое dword1A0 я так и не выяснил, т.к. не смог найти место где этот флаг выставляется
Что такое defaultRowDelta2 для меня тоже до сих пор остаётся загадкой. Когда excel рассчитывает высоту ряда на основе формата, то представляет её как сумму двух чисел. defaultRowDelta2 — это второе число из этой суммы для стандартной высоты ряда. Значение параметра flag тоже загадочно, т.к. везде где я видел вызов этого метода в flag передавался false.
В этом методе также появляется класс SheetLayoutInfo. Я его назвал именно так, потому что в нём хранится много всякой информации о внешнем виде листа. В SheetLayoutInfo есть такие поля как:

• DefaultFullRowHeightMul4 — стандартная высота ряда;
• MinRowIndexNonDefault — индекс первого ряда, у которого высота отличается от стандартной;
• MaxRowIndexNonDefault — индекс ряда, следующего за последним, у которого высота отличается от стандартной;
• DefaultRowDelta2 — та самая часть от суммы стандартной высоты ряда.
• GroupIndexDelta — об этом позже

В принципе логика данного метода вполне понятна:

1. Если индекс ряда меньше первого с нестандартной высотой, то возвращаем стандартную;
2. Если индекс ряда больше последнего с нестандартной высотой, то возвращаем стандартную;
3. В противном случае получаем объект rowHeightInfo для ряда из метода GetRowHeightCore;
4. Если rowHeightInfo == null возвращаем стандартную высоту ряда;
5. Тут магия с флагами, но в общем виде мы возвращаем то, что находится в rowHeightInfo.Value и выставляем 16-й бит в ответе, если высота ряда не была задана вручную.

Если переписать этот код на C#, то получится примерно следующее:

const ulong HiddenRowMask = 0x2000;
const ulong CustomHeightMask = 0x4000;
const ushort DefaultHeightMask = 0x8000;
public static ushort GetRowHeight(int rowIndex, SheetLayoutInfo sheetLayoutInfo) {
    ushort defaultHeight = (ushort) (sheetLayoutInfo.DefaultFullRowHeightMul4 | (~(sheetLayoutInfo.DefaultRowDelta2 >> 14 << 15) & DefaultHeightMask));
    if (rowIndex < sheetLayoutInfo.MinRowIndexNonDefault)
        return defaultHeight;
    if (rowIndex >= sheetLayoutInfo.MaxRowIndexNonDefault)
        return defaultHeight;
    RowHeightInfo rowHeightInfo = GetRowHeightCore(sheetLayoutInfo, rowIndex);
    if (rowHeightInfo == null)
        return defaultHeight;
    ushort result = 0;
    if ((rowHeightInfo.Flags & HiddenRowMask) == 0)
        result = rowHeightInfo.Value;
    if ((rowHeightInfo.Flags & CustomHeightMask) == 0)
        result |= DefaultHeightMask;
    return result;
}

Теперь можно взглянуть что происходит внутри GetRowHeightCore:

RowHeightInfo *__fastcall GetRowHeightCore(SheetLayoutInfo *sheetLayoutInfo, signed int rowIndex)
{
  RowHeightInfo *result; // eax
  RowsGroupInfo *rowsGroupInfo; // ecx
  int rowInfoIndex; // edx

  result = 0;
  if ( rowIndex < sheetLayoutInfo->MinRowIndexNonDefault || rowIndex >= sheetLayoutInfo->MaxRowIndexNonDefault )
    return result;
  rowsGroupInfo = sheetLayoutInfo->RowsGroups[sheetLayoutInfo-GroupIndexDelta + (rowIndex >> 4)];
  result = 0;
  if ( !rowsGroupInfo )
    return result;
  rowInfoIndex = rowsGroupInfo->Indices[rowIndex & 0xF];
  if ( rowInfoIndex )
    result = (rowsGroupInfo + 8 * (rowInfoIndex + rowsGroupInfo->wordBA + rowsGroupInfo->wordBC - rowsGroupInfo->wordB8));
  return result;
}

1. Опять в начале Excel проверяет находится ли индекс ряда среди рядов с изменённой высотой и если нет, то возвращает null.
2. Находит нужную группу рядов, если такой группы нет, то возвращает null.
3. Получает индекс ряда в группе.
4. Далее по индексу ряда находит нужный объект класса RowHeightInfo. wordBA, wordBC, wordB8 — какие-то константы. Они изменяются только вместе с историей. В принципе на понимание алгоритма они не влияют.

Тут стоит отклониться от темы и рассказать подробнее про RowsGroupInfo. Excel хранит RowHeightInfo группами по 16 штук, где i-я группа, представленная классом RowsGroupInfo, будет хранить в себе информацию о рядах с i × 16 до i × 16 + 15 включительно.

Но информация о высоте рядов в RowsGroupInfo хранится несколько необычным способом. Скорее всего из-за необходимости поддерживать историю в Excel.

В RowsGroupInfo есть три важных поля: Indices, HeightInfos, и RowsCount, второе в коде выше не видно (оно должно быть в этой строчке: (rowsGroupInfo + 8 × (…)), т.к. rowInfoIndex может принимать очень разные значения, я видел даже больше 1000 и я понятия не имею как в IDA задать такую структуру. Поле RowsCount вообще в коде выше не встречается, но именно там хранится сколько реально нестандартных рядов хранится в группе.
Кроме того, в SheetLayoutInfo есть GroupIndexDelta — разница между реальным индексом группы и индексом первой группы с изменённой высотой ряда.

В поле Indices хранятся смещения RowHeightInfo для каждого индекса ряда внутри группы. Они хранятся там по порядку, а вот в HeightInfos RowHeightInfo уже хранятся в порядке изменения.

Допустим у нас есть новый пустой лист и мы каким-то образом изменили высоту ряда номер 23. Это ряд лежит во второй группе из 16 рядов, тогда:

1. Excel определит индекс группы для этого ряда. В текущем случае индекс будет равен 1 и изменит GroupIndexDelta = -1.
2. Создаст для группы рядов объект класса RowsGroupInfo и положит его в sheetLayoutInfo->RowsGroups под индексом 0 (sheetLayoutInfo->GroupIndexDelta + 1);
3. В RowsGroupInfo Excel выделит память под 16 4-х байтовых Indices, под RowsCount, wordBA, wordBC и wordB8 и т.д..;
4. Потом Excel вычисляет индекс ряда в группе через операцию побитового И (это сильно быстрее чем брать остаток от деления): rowIndex & 0xF. Искомый индекс в группе будет равняться: 23 & 0xF = 7;
5. После этого Excel получает смещение для индекса 7: offset = Indices[7]. Если offset = 0, то Excel выделяет 8 байт в конце RowsGroupInto, увеличивает RowsCount на единицу и записывает новое смещение в Indices[7]. В любом случае в конце Excel запишет по смещению в RowsGroupInfo информацию о новой высоте ряда и флагах.

Сам класс RowsGroupInfo на C# выглядел бы вот так:

class RowsGroupInfo {
    public int[] Indices { get; }
    public List<RowHeightInfo> HeightInfos { get; }

    public RowsGroupInfo() {
        Indices = new int[SheetLayoutInfo.MaxRowsCountInGroup];
        HeightInfos = new List<RowHeightInfo>();
        for (int i = 0; i < SheetLayoutInfo.MaxRowsCountInGroup; i++) {
            Indices[i] = -1;
        }
    }
}

Метод GetRowHeightCore выглядел бы вот так:

static RowHeightInfo GetRowHeightCore(SheetLayoutInfo sheetLayoutInfo, int rowIndex) {
    if (rowIndex < sheetLayoutInfo.MinRowIndexNonDefault || rowIndex >= sheetLayoutInfo.MaxRowIndexNonDefault)
        return null;
    RowsGroupInfo rowsGroupInfo = sheetLayoutInfo.RowsGroups[sheetLayoutInfo.GroupIndexDelta + (rowIndex >> 4)];
    if (rowsGroupInfo == null)
        return null;
    int rowInfoIndex = rowsGroupInfo.Indices[rowIndex & 0xF];
    return rowInfoIndex != -1 ? rowsGroupInfo.HeightInfos[rowInfoIndex] : null;
}

И вот так выглядел бы SetRowHeight (его код из excel.exe я не приводил):

public static void SetRowHeight(int rowIndex, ushort newRowHeight, ushort flags, SheetLayoutInfo sheetLayoutInfo) {
    sheetLayoutInfo.MaxRowIndexNonDefault = Math.Max(sheetLayoutInfo.MaxRowIndexNonDefault, rowIndex + 1);
    sheetLayoutInfo.MinRowIndexNonDefault = Math.Min(sheetLayoutInfo.MinRowIndexNonDefault, rowIndex);
    int realGroupIndex = rowIndex >> 4;
    if (sheetLayoutInfo.RowsGroups.Count == 0) {
        sheetLayoutInfo.RowsGroups.Add(null);
        sheetLayoutInfo.GroupIndexDelta = -realGroupIndex;
    }
    else if (sheetLayoutInfo.GroupIndexDelta + realGroupIndex < 0) {
        int bucketSize = -(sheetLayoutInfo.GroupIndexDelta + realGroupIndex);
        sheetLayoutInfo.RowsGroups.InsertRange(0, new RowsGroupInfo[bucketSize]);
        sheetLayoutInfo.GroupIndexDelta = -realGroupIndex;
    }
    else if (sheetLayoutInfo.GroupIndexDelta + realGroupIndex >= sheetLayoutInfo.RowsGroups.Count) {
        int bucketSize = sheetLayoutInfo.GroupIndexDelta + realGroupIndex - sheetLayoutInfo.RowsGroups.Count + 1;
        sheetLayoutInfo.RowsGroups.AddRange(new RowsGroupInfo[bucketSize]);
    }
    RowsGroupInfo rowsGroupInfo = sheetLayoutInfo.RowsGroups[sheetLayoutInfo.GroupIndexDelta + realGroupIndex];
    if (rowsGroupInfo == null) {
        rowsGroupInfo = new RowsGroupInfo();
        sheetLayoutInfo.RowsGroups[sheetLayoutInfo.GroupIndexDelta + realGroupIndex] = rowsGroupInfo;
    }
    int rowInfoIndex = rowsGroupInfo.Indices[rowIndex & 0xF];
    RowHeightInfo rowHeightInfo;
    if (rowInfoIndex == -1) {
        rowHeightInfo = new RowHeightInfo();
        rowsGroupInfo.HeightInfos.Add(rowHeightInfo);
        rowsGroupInfo.Indices[rowIndex & 0xF] = rowsGroupInfo.HeightInfos.Count - 1;
    }
    else {
        rowHeightInfo = rowsGroupInfo.HeightInfos[rowInfoIndex];
    }
    rowHeightInfo.Value = newRowHeight;
    rowHeightInfo.Flags = flags;
}

Немного практики

После разобранного выше примера с изменением высоты ряда 23 в памяти Excel будет примерно такая картина (я задал ряду 23 высоту 75 точек):

Здесь и в следующем примере я буду выкладывать схематичное представление о том, как выглядят данные в памяти Excel, сделанное в Visual Studio из самописных классов, потому что прямой дамп из памяти не сильно информативен.
Теперь попробуем спрятать ряд 23. Для этого надо выставить бит 0x2000 у Flags. Будем изменять память на живую. Результат можно увидеть на этом видео: http://recordit.co/79vYIbwbzB.
При любом скрытии рядов Excel поступает также.
Теперь зададим высоту ряда не явно, а через формат ячейки. Пускай у ячейки A20 шрифт станет высотой 40 пунктов, тогда высота ячейки в точках станет 45,75 и в памяти Excel будет примерно такое:

Можно заметить, что Excel всегда хранит высоту ряда, если она не стандартная. Даже если высота не задана явно, а рассчитывается на основе содержимого ячеек или формата, то Excel всё равно посчитает её один раз и занесёт результат в соответствующую группу.

Разбираемся со вставкой/удалением рядов

Интересно было бы разобрать что происходит при вставке/удалении рядов. Соответствующий код в excel.exe найти несложно, но разбирать его не было желания, можете сами взглянуть на часть из него:

int __userpurge sub_305EC930@<eax>(int a1@<eax>, int a2@<edx>, int a3@<ecx>, int a4, int a5, int a6)
{
  int v6; // esi
  int v7; // ebx
  int v8; // edi
  int v9; // edx
  int v10; // ecx
  size_t v11; // eax
  _WORD *v12; // ebp
  size_t v13; // eax
  size_t v14; // eax
  int v15; // eax
  unsigned __int16 *v16; // ecx
  _WORD *v17; // eax
  _WORD *v18; // ecx
  int v19; // edx
  __int16 v20; // bx
  int v21; // eax
  _WORD *v22; // ecx
  int v24; // edx
  int v25; // eax
  int v26; // esi
  int v27; // ebx
  size_t v28; // eax
  int v29; // ebp
  size_t v30; // eax
  int v31; // esi
  size_t v32; // eax
  int v33; // eax
  unsigned __int16 *v34; // ecx
  int v35; // eax
  _WORD *v36; // edx
  _WORD *v37; // ecx
  int v38; // eax
  __int16 v39; // bx
  int v40; // eax
  _WORD *v41; // ecx
  int v42; // [esp+10h] [ebp-48h]
  int v43; // [esp+10h] [ebp-48h]
  int v44; // [esp+14h] [ebp-44h]
  char v45; // [esp+14h] [ebp-44h]
  int Dst[16]; // [esp+18h] [ebp-40h]
  int v47; // [esp+5Ch] [ebp+4h]
  int v48; // [esp+60h] [ebp+8h]

  v6 = a1;
  v7 = a1 & 0xF;
  v8 = a2;
  if ( !a5 )
  {
    v24 = a4 - a1;
    v25 = a1 - a3;
    v43 = a4 - v6;
    if ( v7 >= v25 )
      v7 = v25;
    v47 = a4 - v7;
    v26 = v6 - v7;
    v27 = v7 + 1;
    v48 = v27;
    if ( !v8 )
      return v27;
    v28 = 4 * v24;
    if ( (4 * v24) > 0x40 )
      v28 = 64;
    v45 = v27 + v26;
    v29 = (v27 + v26) & 0xF;
    memmove(Dst, (v8 + 4 * v29), v28);
    v30 = 4 * v27;
    if ( (4 * v27) > 0x40 )
      v30 = 64;
    v31 = v26 & 0xF;
    memmove((v8 + 4 * (v47 & 0xF)), (v8 + 4 * v31), v30);
    v32 = 4 * v43;
    if ( (4 * v43) > 0x40 )
      v32 = 64;
    memmove((v8 + 4 * v31), Dst, v32);
    if ( !a6 )
      return v48;
    v33 = v29;
    if ( v29 < v29 + v43 )
    {
      v34 = (v8 + 4 * v29 + 214);
      do
      {
        Dst[v33++] = *v34 >> 15;
        v34 += 2;
      }
      while ( v33 < v29 + v43 );
    }
    v35 = (v45 - 1) & 0xF;
    if ( v35 >= v31 )
    {
      v36 = (v8 + 4 * ((v27 + v47 - 1) & 0xF) + 214);
      v37 = (v8 + 4 * ((v45 - 1) & 0xF) + 214);
      v38 = v35 - v31 + 1;
      do
      {
        v39 = *v37 ^ (*v37 ^ *v36) & 0x7FFF;
        v37 -= 2;
        *v36 = v39;
        v36 -= 2;
        --v38;
      }
      while ( v38 );
      v27 = v48;
    }
    v40 = v31;
    if ( v31 >= v31 + v43 )
      return v27;
    v41 = (v8 + 4 * v31 + 214);
    do
    {
      *v41 = *v41 & 0x7FFF | (LOWORD(Dst[v40++]) << 15);
      v41 += 2;
    }
    while ( v40 < v31 + v43 );
    return v27;
  }
  v9 = a1 - a4;
  v10 = a3 - a1;
  v42 = a1 - a4;
  v48 = 16 - v7;
  if ( 16 - v7 >= v10 )
    v48 = v10;
  if ( !v8 )
    return v48;
  v11 = 4 * v9;
  if ( (4 * v9) > 0x40 )
    v11 = 64;
  v12 = (v8 + 4 * (a4 & 0xF));
  v44 = a4 & 0xF;
  memmove(Dst, v12, v11);
  v13 = 4 * v48;
  if ( (4 * v48) > 0x40 )
    v13 = 64;
  memmove(v12, (v8 + 4 * v7), v13);
  v14 = 4 * v42;
  if ( (4 * v42) > 0x40 )
    v14 = 64;
  memmove((v8 + 4 * ((a4 + v48) & 0xF)), Dst, v14);
  if ( !a6 )
    return v48;
  v15 = a4 & 0xF;
  if ( v44 < v44 + v42 )
  {
    v16 = (v8 + 4 * v44 + 214);
    do
    {
      Dst[v15++] = *v16 >> 15;
      v16 += 2;
    }
    while ( v15 < v44 + v42 );
  }
  if ( v7 < v48 + v7 )
  {
    v17 = (v8 + 4 * v7 + 214);
    v18 = v12 + 107;
    v19 = v48;
    do
    {
      v20 = *v17 ^ (*v17 ^ *v18) & 0x7FFF;
      v17 += 2;
      *v18 = v20;
      v18 += 2;
      --v19;
    }
    while ( v19 );
  }
  v21 = a4 & 0xF;
  if ( v44 >= v44 + v42 )
    return v48;
  v22 = (v8 + 4 * (v44 + v48) + 214);
  do
  {
    *v22 = *v22 & 0x7FFF | (LOWORD(Dst[v21++]) << 15);
    v22 += 2;
  }
  while ( v21 < v44 + v42 );
  return v48;
}

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

Получается то, что и ожидалось: Excel вставляет во вторую группу новый ряд с индексом 7 (39 & 0xF), у которого смещение равняется 0x05, копирует высоту ряда у индекса 6, при этом последний ряд, который был со смещением 05, выталкивается в следующую группу, а оттуда последний ряд выталкивается в четвёртую и т.д.

Теперь посмотрим, что происходит если удалить 29-й ряд.

В принципе при удалении ряда происходят операции обратные вставке. При этом четвёртая группа продолжает существовать и значение высоты ряда там заполняется стандартной высотой с соответствующим флагом — 0x8005.

Этих данных достаточно для того, чтобы воспроизвести этот алгоритм на C#:

public static void InsertRow(SheetLayoutInfo sheetLayoutInfo, int rowIndex) {
    if (rowIndex >= sheetLayoutInfo.MaxRowIndexNonDefault)
        return;
    RowHeightInfo etalonRowHeightInfo = GetRowHeightCore(sheetLayoutInfo, rowIndex);
    RowHeightInfo newRowHeightInfo = etalonRowHeightInfo != null ? etalonRowHeightInfo.Clone() : CreateDefaultRowHeight(sheetLayoutInfo);
    int realGroupIndex = (rowIndex + 1) >> 4;
    int newRowInGroupIndex = (rowIndex + 1) & 0xF;
    int groupIndex;
    for (groupIndex = realGroupIndex + sheetLayoutInfo.GroupIndexDelta; groupIndex < sheetLayoutInfo.RowsGroups.Count; groupIndex++, newRowInGroupIndex = 0) {
        if (groupIndex < 0)
            continue;
        if (groupIndex == SheetLayoutInfo.MaxGroupsCount)
            break;
        RowsGroupInfo rowsGroupInfo = sheetLayoutInfo.RowsGroups[groupIndex];
        if (rowsGroupInfo == null) {
            if ((newRowHeightInfo.Flags & CustomHeightMask) == 0)
                continue;
            rowsGroupInfo = new RowsGroupInfo();
            sheetLayoutInfo.RowsGroups[groupIndex] = rowsGroupInfo;
        }
        int rowInfoIndex = rowsGroupInfo.Indices[newRowInGroupIndex];
        RowHeightInfo lastRowHeightInGroup;
        if (rowInfoIndex == -1 || rowsGroupInfo.HeightInfos.Count < SheetLayoutInfo.MaxRowsCountInGroup) {
            lastRowHeightInGroup = GetRowHeightCore(sheetLayoutInfo, ((groupIndex - sheetLayoutInfo.GroupIndexDelta) << 4) + SheetLayoutInfo.MaxRowsCountInGroup - 1);
            Array.Copy(rowsGroupInfo.Indices, newRowInGroupIndex, rowsGroupInfo.Indices, newRowInGroupIndex + 1, SheetLayoutInfo.MaxRowsCountInGroup - 1 - newRowInGroupIndex);
            rowsGroupInfo.HeightInfos.Add(newRowHeightInfo);
            rowsGroupInfo.Indices[newRowInGroupIndex] = rowsGroupInfo.HeightInfos.Count - 1;
        }
        else {
            int lastIndex = rowsGroupInfo.Indices[SheetLayoutInfo.MaxRowsCountInGroup - 1];
            lastRowHeightInGroup = rowsGroupInfo.HeightInfos[lastIndex];
            Array.Copy(rowsGroupInfo.Indices, newRowInGroupIndex, rowsGroupInfo.Indices, newRowInGroupIndex + 1, SheetLayoutInfo.MaxRowsCountInGroup - 1 - newRowInGroupIndex);
            rowsGroupInfo.HeightInfos[lastIndex] = newRowHeightInfo;
            rowsGroupInfo.Indices[newRowInGroupIndex] = lastIndex;
        }
        newRowHeightInfo = lastRowHeightInGroup ?? CreateDefaultRowHeight(sheetLayoutInfo);
    }
    if ((newRowHeightInfo.Flags & CustomHeightMask) != 0 && groupIndex != SheetLayoutInfo.MaxGroupsCount) {
        SetRowHeight(((groupIndex - sheetLayoutInfo.GroupIndexDelta) << 4) + newRowInGroupIndex, newRowHeightInfo.Value, newRowHeightInfo.Flags, sheetLayoutInfo);
    }
    else {
        sheetLayoutInfo.MaxRowIndexNonDefault = Math.Min(sheetLayoutInfo.MaxRowIndexNonDefault + 1, SheetLayoutInfo.MaxRowsCount);
    }
}

public static void RemoveRow(SheetLayoutInfo sheetLayoutInfo, int rowIndex) {
    if (rowIndex >= sheetLayoutInfo.MaxRowIndexNonDefault)
        return;
    int realGroupIndex = rowIndex >> 4;
    int newRowInGroupIndex = rowIndex & 0xF;
    int groupIndex;
    for (groupIndex = realGroupIndex + sheetLayoutInfo.GroupIndexDelta; groupIndex < sheetLayoutInfo.RowsGroups.Count; groupIndex++, newRowInGroupIndex = 0) {
        if (groupIndex < -1)
            continue;
        if (groupIndex == -1) {
            sheetLayoutInfo.RowsGroups.Insert(0, null);
            sheetLayoutInfo.GroupIndexDelta++;
            groupIndex = 0;
        }
        if (groupIndex == SheetLayoutInfo.MaxGroupsCount)
            break;
        var newRowHeightInfo = groupIndex == SheetLayoutInfo.MaxGroupsCount - 1 ? null : GetRowHeightCore(sheetLayoutInfo, (groupIndex - sheetLayoutInfo.GroupIndexDelta + 1) << 4);
        RowsGroupInfo rowsGroupInfo = sheetLayoutInfo.RowsGroups[groupIndex];
        if (rowsGroupInfo == null) {
            if (newRowHeightInfo == null || (newRowHeightInfo.Flags & CustomHeightMask) == 0)
                continue;
            rowsGroupInfo = new RowsGroupInfo();
            sheetLayoutInfo.RowsGroups[groupIndex] = rowsGroupInfo;
        }
        if (newRowHeightInfo == null) {
            newRowHeightInfo = CreateDefaultRowHeight(sheetLayoutInfo);
        }
        int rowInfoIndex = rowsGroupInfo.Indices[newRowInGroupIndex];
        if (rowInfoIndex == -1) {
            for (int i = newRowInGroupIndex; i < SheetLayoutInfo.MaxRowsCountInGroup - 1; i++) {
                rowsGroupInfo.Indices[i] = rowsGroupInfo.Indices[i + 1];
            }
            rowsGroupInfo.HeightInfos.Add(newRowHeightInfo);
            rowsGroupInfo.Indices[SheetLayoutInfo.MaxRowsCountInGroup - 1] = rowsGroupInfo.HeightInfos.Count - 1;
        }
        else {
            for(int i = newRowInGroupIndex; i < rowsGroupInfo.HeightInfos.Count - 1; i++) {
                rowsGroupInfo.Indices[i] = rowsGroupInfo.Indices[i + 1];
            }
            rowsGroupInfo.Indices[rowsGroupInfo.HeightInfos.Count - 1] = rowInfoIndex;
            rowsGroupInfo.HeightInfos[rowInfoIndex] = newRowHeightInfo;
        }
    }
    if(rowIndex <= sheetLayoutInfo.MinRowIndexNonDefault) {
        sheetLayoutInfo.MinRowIndexNonDefault = Math.Max(sheetLayoutInfo.MinRowIndexNonDefault - 1, 0);
    }
}

Весь вышеописанный код вы можете найти на GitHub

Выводы

Код Excel уже не в первый раз удивляет интересными приёмами. В этот раз я узнал как он хранит информацию о высотах рядов. Если сообществу будет интересно, то в следующей статье покажу как Excel считает высоту диапазона ячеек (спойлер: там что-то похожее на SQRT-декомпозицию, но почему-то без кеширования сумм), там же можно будет увидеть как он применяет масштабирование в целых числах.

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

Описание функции ИНДЕКС

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

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

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

Синтаксис

Существует четыре вариации синтаксиса этой функции. Две русские версии:

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

Две английские версии:

1. =INDEX (array, row_num, [col_num]).
2. =INDEX (array, row_num, [col_num], [area_num]).

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

Разберемся более подробно, что означает каждый из приведенных ниже аргументов:

• array – обозначает диапазон секторов или массив данных, по которому будет осуществляться поиск;
• row_num – обозначает номер строчки, где располагаются необходимые значения;
• [col_num] – обозначает номер столбика, где располагаются необходимые значения. Этот параметр является необязательным. В случае, когда в функции ИНДЕКС не описан аргумент номера строчки, этот параметр нужно указывать;
• [area_num] – используется в тех случаях, когда массив имеет некоторое количество диапазонов. Параметр является необязательным и применяется для осуществления выбора абсолютно всех диапазонов.

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

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

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

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

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

Функция ИНДЕКС в Excel пошаговая инструкция

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

Функция ИНДЕКС для массивов

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

Цель: в выделенном секторе показать название пятой в списке позиции. Пошаговая инструкция выглядит так:

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

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

1. На экране появилось маленькое окошко, предлагающее выбрать набор аргументов. Выбираем 1-й предложенный вариант и нажимаем «ОК».

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

1. В поле «Номер_строки» вбиваем число 5, так как этого требует поставленная перед нами задача.
2. В поле «Номер_столбца» вбиваем цифру 1, так как названия значений располагаются в 1-м столбике массива.
3. После проведения всех настроек жмем «ОК».

1. В выбранном секторе появился итоговый результат.

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

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

1. В строке «Массив» необходимо выделить только ячейки первого столбика. Вводим номер строки – 5, а номер столбика не заполняем, потому что массив является одномерным.

1. Щелкаем на «ОК». В итоге, в выделенном секторе получаем аналогичный результат.

Функция ИНДЕКС для ссылок

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

Цель: выявить количество продаж в 4-й позиции за 2-й квартал в штуках. Пошаговое руководство выглядит следующим образом:

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

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

1. На экране появилось маленькое окошко, предлагающее выбрать набор аргументов. Выбираем второй предложенный вариант и нажимаем «ОК».

1. В следующем окошке необходимо указать аргументы. В поле «Ссылка» вводим те же значения, которые вводили в поле «Массив» в предыдущем примере. Главное отличие – указываем не один диапазон, а сразу четыре, разделяя их знаком «;». После описания необходимо поставить «(» в начале и «)» в конце.

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

1. После проведения всех манипуляций щелкаем на кнопку «ОК». Готово, выбранном секторе появился необходимый нам ответ.

Использование с оператором СУММ

Функцию ИНДЕКС часто применяют совместно с оператором СУММ. Общий вид оператора: =СУММ(Адрес_массива). Применив СУММ, к рассматриваемой нами табличке, мы сможем получить итоговую сумму. Формула для подсчета суммы будет выглядеть следующим образом: =СУММ(D2:D9).

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

1. Для первого аргумента оператора СУММ выставляем координаты сектора, являющегося точкой начала суммирующего диапазона.
2. 2-й аргумент задается при помощи функции ИНДЕКС. Кликаем на строчку для ввода формул и вбиваем следующее значение: =СУММ(D2:ИНДЕКС(D2:D9;8)). Число 8 говорит о том, что мы выставляем ограничение для выбранного диапазона между секторами D2 и D9.

1. Жмем на клавишу «Enter», чтобы вывести конечный результат в выделенном изначально секторе.

Сочетание с функцией ПОИСКПОЗ

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

• Искомое значение. Этот аргумент указывает значение, которое нужно отыскать в выделенной области.
• Просматриваемый массив. Область секторов для поиска искомого показателя.
• Тип сопоставления. Аргумент не является обязательным и применяется для более точного поиска.

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

1. Первоначально заполняем ячейку F1.

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

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

1. На экране появилось маленькое окошко, предлагающее выбрать набор аргументов. Выбираем первый предложенный вариант и нажимаем «ОК».

1. В строку «Массив» вводим сектор столбика, в котором располагается стоимость позиций.

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

1. В поле «Искомое_значение» вводим координаты ячейки, по наполнению которой будет осуществляться поиск в главном массиве. В поле «Просматриваемый_массив» вбиваем диапазон для поиска искомого показателя. Вводим 0 в поле «Тип_сопоставления».

1. В строке для ввода формул необходимо кликнуть на «ИНДЕКС».

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

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

Обработка нескольких таблиц

Рассмотри процесс обработки нескольких таблиц. К примеру, у нас есть 3 таблички. В них отображается зарплата сотрудников по месяцам. Цель: выявить зарплату второго сотрудника за 3-й месяц. Пошаговое руководство выглядит следующим образом:

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

1. В выбранный заранее сектор вывелись необходимые результаты.

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

Дополнительно разберем еще один пример использования. Например, у нас есть следующая табличная информация:

Чтобы узнать результат Андрея по дисциплине «Физика», необходимо применить следующую формулу: =ИНДЕКС($B$3:$E$9;3;2). Здесь мы произвели определение показателей нужного диапазона: $B$3:$E$9. Цифра 3 означает номер строчки, где располагается результат Андрея. Цифра 2 означает номер столбца, где располагается дисциплина «Физика».

Ошибки

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

Ошибка возникает, если табличный процессор не понимает, какой сектор необходимо вернуть.

Заключение

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

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

Пример работы функции ИНДЕКС в Excel от простого к сложному

Сначала рассмотрим самый простой пример применения. У нас есть несколько городов. Пусть нашей первой задачей будет извлечь пятый по счету город из нашей одномерной базы данных. Синтаксис нашей формулы будет следующий: ИНДЕКС(массив; номер_строки). Начинаем писать формулу (яч. B9). Первый аргумент – это все города B3:C7, а второй – необходимый нам номер ряда, в котором хранится информация, которую мы хотим получить – пятый город:

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

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



Функция ИНДЕКС двумерный и многомерный массив

Теперь наш массив двухмерный. Пусть теперешним заданием будет узнать количество туристов в Ливерпуле. В ячейке B19 пишем формулу. Синтаксис тоже видоизменится: ИНДЕКС(массив; номер_строки; номер_столбца), у нас добавится третий аргумент – номер столбца, в котором хранится нужные нам данные:

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

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

И получаем готовую формулу с решением поставленной задачи:

Пример формулы комбинации функций ИНДЕКС и СУММ

Функцию ИНДЕКC так же можно комбинировать и с другими функциями. Например, наша база данных содержит дополнительно количество туристов Великобритании за 2018-2019 года, мы хотим узнать сумму туристов:

1. за 2018 год.
2. по городу Манчестер за весь период.

В этом нам поможет известная любому пользователю несложная функция СУММ. Она охватит результат поиска, сделанным ИНДЕКC и выполнит операцию сложения найденных значений. Чтобы выполнить первую задачу, для начала нам нужно выбрать диапазон с одними числами(без столбца с городами, поскольку будем суммировать численные значения C43:E47). Затем на месте, где мы прописывали номер строки, пишем 0. Благодаря этому ИНДЕКC не будет «искать» данные по строкам, а просто перейдет к следующей операции. А следующая операция – прописываем столбец 2 и получаем ответ на первый пункт:

Сумма туристов за 2018 год по пяти городам Великобритании составляет 40 987 людей.

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

И получаем ответ на второй пункт – всего посетителей за три года в Манчестере было 2 474 человека.

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

Формулировку функции можно видоизменять в зависимости от постановки задачи. Пусть нам нужно определить и показать сколько туристов вместе было в 2017-м году в первых трёх городах по списку – Кембридже, Манчестере и Лондоне. Тогда мы снова используем функцию СУММ, но в месте, где указывается конец диапазона значений, по которым мы проводим поиск, вставляем нашу ИНДЕКС. Проще говоря, диапазон для суммирования выглядит так: (начало:конец). Началом будет первая ячейка массива, как обычно, а конец мы заменим. Укажем количество городов, по которым будем считать людей (действие 1). Затем, когда будем составлять функцию ИНДЕКC, укажем, что первый аргумент – числовой диапазон всех пяти городов за 2017-й год, а второй – наше кол-во городов (ячейка D64):

Точно так же можно выполнить следующее задание: найти сумму посетителей Оксфорде за 2017-2018 года. В этом случае делаем всё точно так же, только выбираем горизонтальный диапазон C61:E61 :

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

и

идентичны.

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

Формула ИНДЕКС и ПОИСКПОЗ лучшая замена функции ВПР

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

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

Рассмотрим более наглядный и реальный пример, где можно заменить ВПР. У нас есть два отчета: один — о количестве продаж определенного товара, а второй – о цене на упаковку одного товара. И как раз вторая таблица имеет такое расположение столбцов, которое не позволяет использовать нам ВПР – первыми занимают место значения, а вторыми – критерии:

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

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

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

Скачать примеры функции ИНДЕКС и ПОИСКПОЗ в Excel

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

Функция

ИНДЕКС(

)

, английский вариант INDEX(),

возвращает значение из диапазона ячеек по номеру строки и столбца. Например, формула

=ИНДЕКС(A9:A12;2)

вернет значение из ячейки

А10

, т.е. из ячейки расположенной во второй строке диапазона.

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

ИНДЕКС

(массив; номер_строки; номер_столбца)

Массив

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

Номер_строки

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

Номер_столбца

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

Если используются оба аргумента — и «номер_строки», и «номер_столбца», — то функция

ИНДЕКС()

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

Значения аргументов «номер_строки» и «номер_столбца» должны указывать на ячейку внутри заданного массива; в противном случае функция

ИНДЕКС()

возвращает значение ошибки #ССЫЛКА! Например, формула

=ИНДЕКС(A2:A13;22)

вернет ошибку, т.к. в диапазоне

А2:А13

только 12 строк.

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

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

А6:А9.

Выведем значение из 2-й строки диапазона, т.е. значение

Груши

. Это можно сделать с помощью формулы

=ИНДЕКС(A6:A9;2)

Если диапазон горизонтальный (расположен в одной строке, например,

А6:D6

), то формула для вывода значения из 2-го столбца будет выглядеть так

=ИНДЕКС(A6:D6;;2)

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

Пусть имеется таблица в диапазоне

А6:B9.

Выведем значение, расположенное в 3-й строке и 2-м столбце таблицы, т.е. значение

200

. Это можно сделать с помощью формулы

=ИНДЕКС(A6:B9;3;2)

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

Если задать для аргумента «номер_строки» или «номер_столбца» значение 0, функция

ИНДЕКС()

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

ИНДЕКС()

как

формулу массива

.

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

А6:А9.

Выведем 3 первых значения из этого диапазона, т.е. на

А6

,

А7

,

А8

. Для этого выделите 3 ячейки (

А21

,

А22

,

А23

), в

Строку формул

введите формулу

=ИНДЕКС(A6:A9;0)

, затем нажмите

CTRL+SHIFT+ENTER

.

Зачем это нужно? Теперь удалить по отдельности значения из ячеек

А21

,

А22

,

А23

не удастся, мы получим предупреждение «нельзя изменять часть массива».

Хотя можно просто ввести в этих 3-х ячейках ссылки на диапазон

А6:А8.

Выделите 3 ячейки и введите формулу

=A6:A8.

Затем нажмите

CTRL+SHIFT+ENTER

и получим тот же результат.

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

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

массив констант

:

Формула

=ИНДЕКС({«первый»:»второй»:»третий»:»четвертый»};4)

вернет текстовое значение

четвертый

.

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

Функция

ИНДЕКС()

часто используется в связке с функцией

ПОИСКПОЗ()

, которая возвращает позицию (строку) содержащую искомое значение. Это позволяет создать формулу, аналогичную функции

ВПР()

.

Формула

=ВПР(«яблоки»;A35:B38;2;0)

аналогична формуле

=ИНДЕКС(B35:B38;ПОИСКПОЗ(«яблоки»;A35:A38;0))

которая извлекает цену товара

Яблоки

из таблицы, размещенную в диапазоне

A35:B38

Связка

ПОИСКПОЗ()

+

ИНДЕКС()

даже гибче, чем функция

ВПР()

, т.к. с помощью ее можно, например, определить товар с заданной ценой (обратная задача, так называемый «левый ВПР()»). Формула

=ИНДЕКС(A35:A38;ПОИСКПОЗ(200;B35:B38;0))

определяет товар с ценой 200. Если товаров с такой ценой несколько, то будет выведен первый сверху.

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

Функция

ИНДЕКС()

позволяет использовать так называемую ссылочную форму. Поясним на примере.

Пусть имеется диапазон с числами (

А2:А10

) Необходимо найти сумму первых 2-х, 3-х, …9 значений. Конечно, можно написать несколько формул

=СУММ(А2:А3)

,

=СУММ(А2:А4)

и т.д. Но, записав формулу ввиде:

=СУММ(A2:ИНДЕКС(A2:A10;4))

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

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

ИНДЕКС()

в этом примере принципиально отличается от примеров рассмотренных выше, т.к. функция возвращает не само значение, а ссылку (адрес ячейки) на значение. Вышеуказанная формула

=СУММ(A2:ИНДЕКС(A2:A10;4))

эквивалентна формуле

=СУММ(A2:A5)

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

СМЕЩ()

=СУММ(СМЕЩ(A2;;;4))

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

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

Задав

Товар

,

год

и

полугодие

, можно вывести соответствующий объем продаж с помощью формулы

=ИНДЕКС((B9:C12;D9:E12;F9:G12);B15;A19;B17)

Вся таблица как бы разбита на 3 подтаблицы (области), соответствующие отдельным годам:

B9:C12

;

D9:E12

;

F9:G12

. Задавая номер строки, столбца (в подтаблице) и номер области, можно вывести соответствующий объем продаж. В

файле примера

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

Условного форматирования

.