Общая формула
=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 можно использовать для создания динамических прямоугольных диапазонов на основе начальной ссылки и заданных строк, столбцов, высоты и ширины. Аргументы строк и столбцов действуют как «смещения» от начальной ссылки. Аргументы высоты и ширины (оба необязательные) определяют, сколько строк и столбцов включает окончательный диапазон. В этом примере смещение настроено следующим образом:
- ссылка = C3
- строки = СЧЁТ (A: A)
- cols = 0
- высота = -5
- ширина = (не предоставляется)
Начальная ссылка представлена как C3 - ячейка над фактическими данными. Поскольку мы хотим, чтобы OFFSET возвращал диапазон, исходящий из последней записи в столбце C, мы используем функцию COUNT для подсчета всех значений в столбце C, чтобы получить необходимое смещение строки. COUNT считает только числовые значения, поэтому заголовок в строке 3 автоматически игнорируется.
С 8 числовыми значениями в столбце C формула СМЕЩЕНИЕ принимает следующий вид:
OFFSET(C3,8,0,-5)
С этими значениями OFFSET начинается с C3, смещает 8 строк до C11, затем использует -5, чтобы расширить прямоугольный диапазон «назад» на 5 строк, чтобы создать диапазон C7: C11.
Наконец, OFFSET возвращает диапазон C7: C11 функции AVERAGE, которая вычисляет среднее значение в этом диапазоне.
Excel против таблиц
Странная особенность этой формулы заключается в том, что она не будет работать с Google Таблицами, потому что функция СМЕЩЕНИЕ в Таблицах не допускает отрицательного значения для аргументов высоты или ширины. В документации Excel также указано, что высота или ширина не могут быть отрицательными, но, похоже, отрицательные значения отлично работают в Excel с 1990-х годов.
Чтобы избежать отрицательных значений высоты или ширины, вы можете использовать следующую формулу:
=OFFSET(C4,COUNT(C:C)-5,0,5)
Примечание C4 - это начальная ссылка в этом случае. Общая форма:
=AVERAGE(OFFSET(A1,COUNT(A:A)-N,0,N))
где A1 - первая ячейка в числах, которые вы хотите усреднить.
