Привет. Если вам нужно считать среднее значение последних n чисел, есть короткая формула для реализации этой логики. Да, можно просто использовать функцию СРЗНАЧ, указать нужные ячейки и получить результат. Но если вы будете дополнять набор данных, придётся постоянно в этой формуле исправлять ссылку на последние элементы. Мой же способ автоматизирован, всё пересчитывается само по мере добавления новых значений.
Нам понадобится три функции:
Чтобы написать формулу, воспользуемся примером. Есть 15 чисел, и есть ячейка F3, в которой задается N – количество последних чисел для расчёта среднего:

В ячейке F4 вычислим среднее арифметическое. Запишем формулу:
=СРЗНАЧ(СМЕЩ(C2; СЧЁТ(C:C); 0; -F3))
Как работает эта формула
Всё «волшебство» происходит в такой последовательности:
-
Функция СМЕЩ динамически формирует массив для подсчёта среднего. Работаем с такими аргументами:
- Ссылка: C2. Здесь нужно указать ячейку над первым числом списка;
- Смещение по строкам: СЧЁТ(C:C). Т.е. относительно ячейки С2 спускаемся вниз на столько строк, сколько чисел в столбце. В нашем примере 15 чисел. От координат C2 уходим вниз на 15 строчек, попадаем в C17. Это последняя ячейка списка;
- Смещение по столбцам: 0. То есть, вправо не смещаемся, диапазон будет в пределах колонки C
- Высота: -F3. В ячейке F3 у нас тройка. Это самое интересное место формулы, мы указываем, что высота массива будет -3. То есть, размер отсчитывается не вниз от C17, а вверх.
В итоге, функция СМЕЩ возвращает ссылку: C15:C17
- Функция СРЗНАЧ принимает сгенерированный диапазон C15:C17 и вычисляет среднее арифметическое чисел в этих ячейках
На картинке ниже можете убедиться, что формула отработала правильно:

Важно, что при добавлении новых данных в таблицу, формула будет пересчитывать координаты последних ячеек и обновлять их среднее арифметическое. Кроме того, вы всегда можете изменить N в ячейке F3, и это тоже будет работать.
Отличный результат, но кое-что уточню. Если Вы собираетесь использовать такой расчёт в Google-таблицах, это не сработает, т.к. там не поддерживаются (на момент написания статьи) отрицательный размер таблицы. Тогда можно изменить формулу:
=СРЗНАЧ(СМЕЩ(C3; СЧЁТ(C:C)-F3; 0; F3))
Такая запись немного длиннее, но в Гугл Таблицах работать будет.
На этом всё, задавайте вопросы, если что-то не получилось!
Скачать пример