У меня есть группы формул в Excel 2016:
=CLASSSTRUCTURE!B43
=CLASSSTRUCTURE!B43
=CLASSSTRUCTURE!B43
=CLASSSTRUCTURE!B43
=CLASSSTRUCTURE!B44
=CLASSSTRUCTURE!B44
=CLASSSTRUCTURE!B44
=CLASSSTRUCTURE!B44
=CLASSSTRUCTURE!B45
=CLASSSTRUCTURE!B45
=CLASSSTRUCTURE!B45
=CLASSSTRUCTURE!B45
Формулы повторяются четыре раза в каждой группе.
Я хотел бы автоматически заполнить следующие четыре строки (и так далее):
...
=CLASSSTRUCTURE!B46
=CLASSSTRUCTURE!B46
=CLASSSTRUCTURE!B46
=CLASSSTRUCTURE!B46
Есть ли способ сделать это?
решение1
Обычный способ сделать это — использовать функцию INDEX(), которая позволяет искать ячейку в диапазоне ячеек, указывая ее номер строки и столбца.
Используйте что-то вроде этого:
=INDEX($A$1:$A$100, INT((ROW($A1)-ROW($A$1))/4)+1)
где
$A$1:$A$100 — это диапазон ячеек, на которые вы ссылаетесь (он может уменьшаться настолько, насколько вы пожелаете).
ROW() возвращает номер строки ячейки, на которую она ссылается, поэтому ROW($A1)-ROW($A$1) возвращает нам количество ячеек ниже первой ячейки, которую мы заполняем. (Обратите внимание на знаки $)
Разделите это на 4 и округлите вниз, и мы получим номер строки ячейки, из которой мы берем данные. Эту формулу можно скопировать вниз по странице так далеко, как вам нужно.
Аналогичным образом можно использовать функции «СМЕЩ» или «ДВССЫЛ», но они значительно замедлят работу вашей электронной таблицы, поскольку обе являются «изменчивыми» функциями, которые не могут воспользоваться преимуществами интеллектуальной оптимизации процесса вычислений в Excel.
Оптимизации:
Мы можем сократить количество использований "INDEX", поскольку 3 из каждых 4 ячеек такие же, как и выше. Поэтому мы используем:
Cell Formula
C1 =INDEX($A$1:$A$100, INT((ROW($A1)-ROW($A$1))/4)+1)
C2 =C1
C3 =C2
C4 =C3
C5 =INDEX($A$1:$A$100, INT((ROW($A1)-ROW($A$1))/4)+1)
C6 =C5
C7 ...
Его по-прежнему можно копировать и вставлять на лист, но только блоками по 4 строки.