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

Нам понадобится три функции:

  • СЧЁТ(значения) – подсчитывает количество чисел в указанных значениях
  • СРЗНАЧ(значения) – определяет среднее арифметическое переданных ему значений
  • СМЕЩ(ссылка; смещение_по_строкам; смещение_по_столбцам; [высота]; [ширина]) – возвращает диапазон ячеек, на основании аргументов:
    • Ссылка – стартовая ячейка, относительно которой строится диапазон
    • Смещение по строкам – задаёт начальную строку массива относительно стартовой ячейки (количество строк, на которые нужно сдвинуться вниз)
    • Смещение по столбцам — указывает начальный столбец, т.е. количество колонок, на которые сдвигаемся вправо
    • Высота – количество строк в диапазоне (по умолчанию – 1)
    • Ширина – количество столбцов (по умолчанию – 1)

Чтобы написать формулу, воспользуемся примером. Есть 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))

Такая запись немного длиннее, но в Гугл Таблицах работать будет.

На этом всё, задавайте вопросы, если что-то не получилось!

Скачать пример