Функция автозаполнения не выполняет итерацию чисел должным образом при использовании формулы AVERAGE() в Microsoft Excel

Question

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

СРЕДНИЙ (D13:D15) Y1 (январь - март)

СРЕДНИЙ (D16:D18) Y1 (апрель - июнь)

СРЕДНИЙ (D19:D21) Y1 (июль - сентябрь)

СРЕДНИЙ (D22:D24) Y1 (октябрь - декабрь)

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

СРЕДНИЙ (D13:D15) Y1 (январь - март)

СРЕДНИЙ (D16:D18) Y1 (апрель - июнь)

СРЕДНИЙ (D19:D21) Y1 (июль - сентябрь)

СРЕДНИЙ (D22:D24) Y1 (октябрь - декабрь)

СРЕДНИЙ (D17:D19) Y1 (май - июль)

СРЕДНИЙ (D20:D22) Y1 (август - октябрь)

СРЕДНИЙ (D23:D25) Y1 (ноябрь, декабрь) - Y2 (январь)

СРЕДНИЙ (D26:D28) Y2 (февраль - апрель)

Скорее, чем:

СРЕДНИЙ (D13:D15) Y1 (январь - март)

СРЕДНИЙ (D16:D18) Y1 (апрель - июнь)

СРЕДНИЙ (D19:D21) Y1 (июль - сентябрь)

СРЕДНИЙ (D22:D24) Y1 (октябрь - декабрь)

СРЕДНИЙ (D25:D27) Y2 (январь - март)

СРЕДНИЙ (D28:D30) Y2 (апрель - июнь)

СРЕДНИЙ (D31:D33) Y2 (июль - сентябрь)

СРЕДНИЙ (D34:D36) Y2 (октябрь - декабрь)

Есть ли обходной путь к этому или мне придется вручную конвертировать все мои ежемесячные данные в квартальные данные?

Answer 1

Предположим, что ваша первая квартальная ячейка (та, которая содержит =AVERAGE(D13:D15)) - это Q42 .  Замените эту формулу на =AVERAGE(OFFSET($D$13, 3*(ROW()-42), 0, 3, 1)) и перетащите ее вниз.  Функция OFFSET позволяет получить доступ к ячейкам без необходимости буквально вводить их адреса (например, D25); можно сказать, что 12-я ячейка ниже, чем ячейка D13 .  Эта формула говорит,

• Возьмите текущий номер строки (ROW()) и вычтите 42 (номер строки ячейки Q42 , где вы хотите получить среднее значение Y1 Q1).  Очевидно, это оценивается как 0 в ячейке Q42 .  Когда значение уменьшается до ячейки Q43 , оно оценивается как 1 и т.д. В Q46 (где вы хотите получить среднее значение Y2 за Q1) вы получаете 4.
• Умножьте на 3.  Очевидно, это дает вам количество месяцев с начала (январь Y1).
• Начиная с D13 , уменьшите число только что вычисленных месяцев и пройдите нулевые столбцы вправо.  Затем возьмите AVERAGE диапазон, который составляет три строки в высоту и один столбец в ширину.

Answer 2

Автозаполнение будет увеличивать ссылки только на 1 строку в каждой строке. Чтобы получить другой интервал, используйте функцию =row() с множителем.

=ROW(A1)*3+10 соответствует номеру строки A1 * 3, плюс 10, то есть 13 с шагом 3 на каждую строку

INDIRECT берет текстовую строку и превращает ее в ссылку, поэтому

=AVERAGE(INDIRECT("D"&ROW(A1)*3+10&":D"&ROW(A1)*3+12))

оценивает

average(indirect("D"&13&":D"&15)

который далее идет к

=AVERAGE(D13:D15)

И это будет тянуть вниз следующим образом

=AVERAGE(D13:D15)
=AVERAGE(D16:D18)
=AVERAGE(D19:D21)
=AVERAGE(D22:D24)
=AVERAGE(D25:D27)
=AVERAGE(D28:D30)
=AVERAGE(D31:D33)
=AVERAGE(D34:D36)
=AVERAGE(D37:D39)
=AVERAGE(D40:D42)
=AVERAGE(D43:D45)
=AVERAGE(D46:D48)
=AVERAGE(D49:D51)

и т.п.

---

В качестве бонуса, следующая формула перетянет ваши ярлыки, хотя это можно сделать с помощью некоторого умного автозаполнения из 2 ячеек (1, содержащая часть года, и одну, содержащую часть месяца), соединенных вместе с &)

="Y"&ROUNDUP(ROW(A1)/4,0)&" "&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MOD(ROW(A1),4),1,"(January - March)"),2,"(April - June)"),3,"(July - September)"),0,"(October - December)")

будет тянуть вниз как:

Y1 (January - March)
Y1 (April - June)
Y1 (July - September)
Y1 (October - December)
Y2 (January - March)
Y2 (April - June)
Y2 (July - September)
Y2 (October - December)
Y3 (January - March)
Y3 (April - June)
Y3 (July - September)
Y3 (October - December)
Y4 (January - March)
Y4 (April - June)
Y4 (July - September)
Y4 (October - December)
Y5 (January - March)

и т.п.