Загальна формула
=AVERAGE(OFFSET(A1,COUNT(A:A),0,-N))
Резюме
Щоб усереднити останні 5 точок даних, ви можете використовувати функцію AVERAGE разом із функціями COUNT і OFFSET. Ви можете використовувати цей підхід для усереднення останніх N точок даних: останні 3 дні, останні 6 вимірювань тощо. У наведеному прикладі формула в F6 така:
=AVERAGE(OFFSET(C3,COUNT(C:C),0,-5))
Примітка: від’ємне значення висоти не буде працювати на аркушах Google. Дивіться нижче для отримання додаткової інформації.
Пояснення
Функцію OFFSET можна використовувати для побудови динамічних прямокутних діапазонів на основі вихідного посилання та заданих рядків, стовпців, висоти та ширини. Аргументи рядків і стовпців функціонують як "зміщення" від початкового посилання. Аргументи висоти та ширини (обидва додаткові) визначають, скільки рядків і стовпців включає кінцевий діапазон. У цьому прикладі OFFSET налаштовано так:
- посилання = C3
- рядки = COUNT (A: A)
- cols = 0
- висота = -5
- ширина = (не надається)
Початкове посилання надається як клітинка С3 над фактичними даними. Оскільки ми хочемо, щоб OFFSET повернув діапазон, що походить від останнього запису в стовпці C, ми використовуємо функцію COUNT для підрахунку всіх значень у стовпці C, щоб отримати необхідне зміщення рядка. COUNT враховує лише числові значення, тому заголовок у рядку 3 автоматично ігнорується.
З 8 числових значень у стовпці C формула OFFSET вирішує:
OFFSET(C3,8,0,-5)
З цими значеннями OFFSET починається з C3, зміщує 8 рядків до C11, потім використовує -5 для розширення прямокутного діапазону вгору "назад" на 5 рядків, щоб створити діапазон C7: C11.
Нарешті, OFFSET повертає діапазон C7: C11 функції AVERAGE, яка обчислює середнє значення в цьому діапазоні.
Excel проти Таблиць
Дивною химерністю цієї формули є те, що вона не буде працювати з Google Sheets, оскільки функція OFFSET у Sheets не допускає від’ємного значення для аргументів висоти або ширини. Документація Excel також стверджує, що висота чи ширина не можуть бути від’ємними, але, схоже, негативні значення в Excel працювали з 1990-х років.
Щоб уникнути від’ємних значень висоти або ширини, можна скористатися такою формулою:
=OFFSET(C4,COUNT(C:C)-5,0,5)
Примітка C4 є початковим посиланням у цьому випадку. Загальна форма:
=AVERAGE(OFFSET(A1,COUNT(A:A)-N,0,N))
де А1 - перша комірка в числах, які ви хочете усереднити.
