Общая формула
=AVERAGE(OFFSET(firstcell,0,COUNT(rng)-N,1,N))
Резюме
Чтобы усреднить последние 5 значений данных в диапазоне столбцов, вы можете использовать функцию AVERAGE вместе с функциями COUNT и OFFSET. В показанном примере формула в F6:
=AVERAGE(OFFSET(D5,0,COUNT(D5:J5)-5,1,5))
Объяснение
Функцию OFFSET можно использовать для построения динамических диапазонов с использованием начальной ячейки и заданных строк, столбцов, высоты и ширины.
Аргументы строк и столбцов действуют как «смещения» от начальной ссылки. Аргументы высоты и ширины, необязательные, определяют, сколько строк и столбцов включает окончательный диапазон. Мы хотим, чтобы OFFSET возвращал диапазон, который начинается с последней записи и расширяется «назад», поэтому мы предоставляем следующие аргументы:
ссылка - начальная ссылка - это D5 - ячейка справа от формулы и первая ячейка в диапазоне значений, с которыми мы работаем.
rows - мы используем 0 для аргумента rows, потому что мы хотим оставаться в той же строке.
columns - в качестве аргумента columns мы используем функцию COUNT для подсчета всех значений в диапазоне, затем вычитаем 5. Это сдвигает начало диапазона на 5 столбцов влево.
height - мы используем 1, так как в качестве конечного результата нам нужен диапазон из 1 строки.
width - мы используем 5, так как нам нужен окончательный диапазон из 5 столбцов.
Для формулы в C5 СМЕЩЕНИЕ возвращает окончательный диапазон F5: J5. Это входит в функцию AVERAGE, которая возвращает среднее из 5 значений в диапазоне
Менее 5 значений
Если имеется менее 5 значений, формула вернет ошибку циклической ссылки, поскольку диапазон расширится до ячейки, содержащей формулу. Чтобы предотвратить эту ошибку, вы можете изменить формулу следующим образом:
=AVERAGE(OFFSET(first,0,COUNT(rng)-MIN(N,COUNT(rng)),1,MIN(N,COUNT(rng))))
Здесь мы используем функцию MIN, чтобы «поймать» ситуации, когда имеется менее 5 значений, и использовать фактическое количество, когда они есть.
