Обща формула
=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
- редове = БРОЙ (A: A)
- cols = 0
- височина = -5
- ширина = (не е предоставена)
Началната справка се предоставя като клетка C3 над действителните данни. Тъй като искаме 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 от 90-те години насам.
За да избегнете отрицателни стойности на височина или ширина, можете да използвате формула като тази:
=OFFSET(C4,COUNT(C:C)-5,0,5)
Известие C4 е началната справка в този случай. Общата форма е:
=AVERAGE(OFFSET(A1,COUNT(A:A)-N,0,N))
където A1 е първата клетка от числата, които искате да осредните.
