Я использую 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»).
Если размер окна таков, что он выходит за пределы допустимых данных, было бы неплохо, если бы он возвращал пустое или нулевое значение, но это не принципиально. Я не против игнорировать #Refs или вручную корректировать начало столбца, чтобы учесть это.
Мне бы хотелось верить, что это можно сделать с помощью формулы и без необходимости прибегать к Visual Basic. Так ли это?
Спасибо, Йош.
решение1
Вот метод индекса:
=IF(ROW(1:1)<$E$1,"",SUM(INDEX(A:A,(ROW()-$E$1)+1):INDEX(A:A,ROW())))
INDEX — энергонезависимая функция, то есть она пересчитывается только при изменении данных, на которые она ссылается.
ОбаСМЕЩ() и ДВУСТОРОННИЙ()являются изменчивыми функциями. Изменчивая функция вычисляется каждый раз, когда Excel пересчитывает. Поэтому, если их много, это замедляет вычисления ненужными вычислениями.
решение2
Это должно работать на сотовых B2:
=SUM(A2:INDIRECT(ADDRESS(ROW(A2)-($D$2-1),1,4)))
Значение окна находится в ячейке $D$2.
решение3
Я бы использовал функцию смещения
=sum(offset(A2,0,0,D1))
где D1 — количество строк в нем.