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

Question

Нужна ваша помощь, потому что я не уверен в правильности формулы (если она вообще есть) в Excel 2010

Скажем, в столбце А у меня есть цифры (от А1 до А1000). Предположим, в столбце BI нужно получить среднее значение от А1 до А10. Так что для B1 моя формула =average(A1:A10) . Затем для B2, чтобы получить среднее значение следующих 10 цифр в столбце A, то есть от A11 до A20. Есть ли более простой способ сделать это? Потому что, если я просто скопирую / перетащу формулу, B2 становится =average(A2:A11) когда мне нужно, чтобы оно было =average(A11:A20) .

Answer 1

Вы должны использовать другой подход, поскольку простое копирование и перетаскивание, как вы заметили, просто смещают диапазон среднего значения на 1 строку за раз.

Эта формула использует функцию OFFSET для сдвига диапазона для каждого среднего на 10 строк.

=AVERAGE(OFFSET($A$1,10*(ROW(A1)-1),0,10,1))

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

Например, OFFSET(A1,10,0,10,1) относится к диапазону, который на 10 строк ниже и 0 столбцов справа от ячейки A1 и имеет 10 строк в высоту и 1 столбец в ширину, что соответствует диапазону ячейки А11: А20.

Копирование формулы вниз по столбцу приводит к усреднению сначала для ячеек A1:10, затем ячеек A11:A20, затем A21:30 и т.д.