Excel - усреднение последующих ячеек с использованием первой неиспользуемой ячейки в столбце

Question

Доброе утро,

Мне нужно, чтобы этот вывод был на отдельном листе, но, по сути, проблему, которую я пытаюсь решить, можно объяснить следующим образом.

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

В B2 на Листе 2 мне бы хотелось average(Sheet1!a2:a5) , в B3 average(Sheet1!a6:a9) , в B4 , (Лист1!а10: а13) и т. д.

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

=AVERAGE(OFFSET(Sheet1!$a$2, 4*(ROW()-ROW(Sheet1!$a$2)), 0, 4, 1))

Который работал! Однако затем мне нужен еще один столбец для отображения других данных, показанных ниже:

=AVERAGE(OFFSET(Sheet1!$a$118, 4*(ROW()-ROW(Sheet1!$a$118)), 0, 4, 1))

Что дает мне ошибку ссылки. Может кто-нибудь помочь мне понять, почему это не работает? Простое решение будет приветствоваться, но объяснение того, почему было бы особенно здорово!

Answer 1

Правильно, поэтому мы будем использовать другую формулу для вашего первого -

=AVERAGE(INDEX(A:A,1+4*(ROW()-ROW($B$1))):INDEX(A:A,4*(ROW()-ROW($B$1)+1)))

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

Скажем, вы хотели сделать это снова, но начиная с A118, о чем я думаю, вы спрашиваете

=AVERAGE(INDEX(A118:A999,1+4*(ROW()-ROW($B$118))):INDEX(A118:A999,4*(ROW()-ROW($B$118)+1)))

Теперь, это требует, чтобы вы выполнили свою работу в строке 118, но, скажем, вы хотите просто начать новый столбец в строке 1 -

=AVERAGE(INDEX(A118:A999,1+4*(ROW()-ROW($B$1))):INDEX(A118:A999,4*(ROW()-ROW($B$1)+1)))

Так же, как первый.