Загальна формула
=SUM(OFFSET(start,0,0,N,1))
Резюме
Щоб підсумувати набір щомісячних даних через n кількість місяців, ви можете використовувати формулу на основі функцій SUM і OFFSET. У наведеному прикладі формула в G6 має вигляд:
=SUM(OFFSET(C5,0,0,G5,1))
Пояснення
У наведеному прикладі ми маємо щомісячні дані за 2017 та 2018 роки. Мета полягає в динамічному підсумовуванні значень за задану кількість місяців, кодованих як 6 у клітинці G5. Це робиться шляхом подачі посилання, побудованого за допомогою функції OFFSET, у функцію підсумовування.
Функція OFFSET дозволяє побудувати посилання, використовуючи вихідну точку, зміщення рядків і стовпців, а також висоту та ширину. OFFSET зручний у формулах, які динамічно усереднюють або підсумовують "останні n значень", "перші n значень" тощо. У комірці G6 функція OFFSET налаштована так:
OFFSET(C5,0,0,G5,1)
Перекладено: з вихідної позиції C5 побудуйте посилання 6 рядків х 1 стовпець без зміщення. З номером 6 у G5 OFFSET повертає посилання C5: C10 на SUM:
=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 - посилання на діапазон, що містить значення YTD.
