Общая формула
=SUM(OFFSET(start,0,0,N,1))
Резюме
Чтобы суммировать набор ежемесячных данных за n месяцев, вы можете использовать формулу, основанную на функциях СУММ и СМЕЩЕНИЕ. В показанном примере формула в G6:
=SUM(OFFSET(C5,0,0,G5,1))
Объяснение
В показанном примере у нас есть ежемесячные данные за 2017 и 2018 годы. Цель состоит в том, чтобы динамически суммировать значения за заданное количество месяцев, жестко запрограммированных как 6 в ячейке G5. Это делается путем подачи ссылки, созданной с помощью функции OFFSET, в функцию суммы.
Функция СМЕЩЕНИЕ позволяет создать ссылку, используя начальную точку, смещение строки и столбца, а также высоту и ширину. OFFSET удобен в формулах, которые динамически усредняют или суммируют «последние n значений», «первые n значений» и т. Д. В ячейке G6 СМЕЩЕНИЕ настроено следующим образом:
OFFSET(C5,0,0,G5,1)
Переведено: из начальной позиции C5 построить ссылку 6 строк x 1 столбец без смещения. С номером 6 в G5, СМЕЩЕНИЕ возвращает ссылку C5: C10 на СУММ:
=OFFSET(C5,0,0,G5,1) // returns C5:C10 =SUM(C5:C10)) // returns 5775
Формула в G7 устроена так же. Единственное отличие - это отправная точка:
=OFFSET(D5,0,0,G5,1) // returns D5:D10 =SUM(D5:D10)) // returns 6380
При изменении числа в G5 обе формулы динамически обновляются и возвращают новый результат.
Изменение от года к дате
Вместо жесткого кодирования значения в формуле вы можете использовать функцию COUNT для подсчета существующих записей в заданном столбце и возврата этого количества в OFFSET. Общая версия этой формулы будет выглядеть так:
=SUM(OFFSET(start,0,0,COUNT(rng),1))
где rng - ссылка на диапазон, содержащий значения с начала года.
