Переменное скользящее окно в Excel

Question

Я использую Excel 2010, но я предполагаю, что это будет решено одинаково в любой версии Excel.

У меня есть данные, которые я хотел бы суммировать - по сути, предоставляя скользящую совокупную стоимость. Например, в таблице с 1000 строками мне бы хотелось, чтобы столбец отображал сумму значения текущей строки и предыдущих строк X, где X - это число, которое я хотел бы указать в другой ячейке. Например, последние 6 строк совокупного значения или последние 5 или 8 или что-то еще.

Я пробовал разные комбинации, используя ADDRESS и INDIRECT, INDEX и т.д., Но ничего не получалось.

Вот пример, показывающий желаемые результаты, где размер окна равен 2:

  |A     |B
1 |Value |Cumulative
2 | 1    |   
3 | 2    |3
4 |15    |17
5 |10    |25
6 |11    |21
7 | 8    |19

и для размера окна 3

  |A     |B
1 |Value |Cumulative
2 | 1    |   
3 | 2    |
4 |15    |18
5 |10    |27
6 |11    |36
7 | 8    |29

Как я уже сказал, я бы хотел, чтобы размер окна указывался в другой ячейке электронной таблицы (например, с именем "WindowSize").

Если размер окна таков, что он выходит за рамки действительных данных, было бы неплохо, если бы он возвратил пустое значение или значение 0 - но это не обязательно. Я не против игнорировать #Refs или вручную корректировать начало столбца, чтобы учесть это.

Я хотел бы верить, что это может быть сделано с формулой и без необходимости прибегать к визуальным основам. Может это?

Спасибо Ёш

Answer 1

Это должно работать на ячейке B2:

=SUM(A2:INDIRECT(ADDRESS(ROW(A2)-($D$2-1),1,4)))

Значение окна находится в ячейке $D$2 .

Answer 2

Я бы использовал функцию смещения

=sum(offset(A2,0,0,D1))

где D1 имеет количество строк в нем.

Answer 3

Вот метод индекса:

=IF(ROW(1:1)<$E$1,"",SUM(INDEX(A:A,(ROW()-$E$1)+1):INDEX(A:A,ROW())))

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

И OFFSET(), и INDIRECT() являются энергозависимыми функциями. Функция Volatile рассчитывает каждый раз, когда Excel пересчитывает. Поэтому, если их много, это замедляет вычисления с ненужными вычислениями.