Перемещение Excel AVERAGE по нескольким столбцам

Question

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

=AVERAGE(C4:F4)

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

Есть ли простой способ скопировать формулу и сделать так, чтобы номера столбцов смещались на четыре в среднем в последующие годы?

Answer 1

Одним из способов является использование AVERAGE вместе с функцией INDEX, которая возвращает ссылку на ячейку, а не значение ячейки, чтобы достичь этого.

Смотрите скриншот ниже. В качестве примера ваши данные находятся в диапазоне от C4 до N6, а среднее значение ожидается в C9 и далее в соседних столбцах.

В C9 поместите следующую формулу и перетащите ее.

{=AVERAGE(INDEX($C4:$N4,1,MATCH(C$8,YEAR($C$3:$N$3),0)):INDEX($C4:$N4,1,MATCH(C$8,YEAR($C$3:$N$3),0)+3))}

Поместите эту формулу без фигурных скобок, а затем в строке формул нажмите CTRL+SHIFT+ENTER, чтобы сделать ее формулой массива. Перетащите его ниже и поперек.

И на всякий случай вам нужно среднее значение для всего набора (все три кольца, собранные за год, его

{=AVERAGE(INDEX($C$4:$N$6,1,MATCH(C8,YEAR($C$3:$N$3),0)):INDEX($C$4:$N$6,3,MATCH(C8,YEAR($C$3:$N$3),0)+3))}

Предположения - Ваши данные всегда в блоках по 4 квартала в год. Ваш год находится в смежных клетках в столбцах C8, D8, E8 и так далее.

Answer 2

Используйте следующую формулу:

=AVERAGE(IF(YEAR($C$3:$N$3)=C8,$C$4:$N$4))
одновременно нажмите Ctrl+ Shift+ Enter (формула массива) для Inner и перетащите его в ту же строку
Он проверит дату (март-90, июнь-90 ...) в C3:N3, чтобы она была равна году (1990) в C8, и вычислит среднее значение соответствующих данных.

=AVERAGE(IF(YEAR($C$3:$N$3)=C8,$C$5:$N$5)) для среднего (Ctrl+ Shift+ Enter одновременно)

=AVERAGE(IF(YEAR($C$3:$N$3)=C8,$C$6:$N$6)) для Внешнего (Ctrl+ Shift+ Enter одновременно)