Загальна формула
=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 - комірка безпосередньо праворуч від формули і перша комірка в діапазоні значень, з якими ми працюємо.
рядки - ми використовуємо 0 для аргументу рядків, оскільки ми хочемо залишатися в тому самому рядку.
стовпці - для аргументу стовпці ми використовуємо функцію COUNT для підрахунку всіх значень у діапазоні, а потім віднімаємо 5. Це зміщує початок стовпця 5 діапазону вліво.
висота - ми використовуємо 1, оскільки ми хочемо діапазон в 1 рядок як кінцевий результат.
width - ми використовуємо 5, оскільки нам потрібен кінцевий діапазон з 5 стовпцями.
Для формули в C5 OFFSET повертає кінцевий діапазон F5: J5. Це переходить у функцію AVERAGE, яка повертає середнє з 5 значень у діапазоні
Менше 5 значень
Якщо значення менше 5, формула поверне кругову посилальну помилку, оскільки діапазон буде поширюватися назад до комірки, що містить формулу. Щоб запобігти цій помилці, ви можете адаптувати формулу наступним чином:
=AVERAGE(OFFSET(first,0,COUNT(rng)-MIN(N,COUNT(rng)),1,MIN(N,COUNT(rng))))
Тут ми використовуємо функцію MIN, щоб "вловити" ситуації, коли є менше 5 значень, і використовувати фактичний рахунок, коли є.
