Среднее пяти последних значений в MS EXCEL

Найдем, среднее последних 5 последних значений в списке. Если столбец со значениями постоянно заполняется, то эта задача перестает быть тривиальной.

Найдем среднее пяти последних значений в постоянно пополняемом диапазоне.

Столбец не содержит пустых ячеек

Если столбец не содержит пустых ячеек, то для нахождения ссылки на диапазон, содержащий 5 последних значений можно использовать формулу (см. Статью Пять последних значений):
=СМЕЩ($A$5;СЧЁТЗ(Список)-5;0;5)

Динамический диапазон Список образован формулой =СМЕЩ(Лист1!$A$5;;;СЧЁТЗ(Лист1!$A$5:$A$19))

Соответственно, среднее последних 5 значений можно вычислить так:
=СРЗНАЧ(СМЕЩ($A$5;СЧЁТЗ(Список)-5;0;5))

Если в столбец добавить еще значения, то функция СМЕЩ() автоматический вернет ссылку на диапазон, содержащий 5 последних значений, с учетом только что добавленного значения.

Последние 5 значений выделены Условным форматированием (см. статью Выделение последних 5 заполненных ячеек)

Столбец содержит пропуски

Если диапазон содержит пропуски, то, для нахождения среднего последних пяти значений, нужно использовать другую формулу:
=СУММПРОИЗВ((СТРОКА(A1:A30)*(A1:A30<>"")>=
НАИБОЛЬШИЙ(СТРОКА(A1:A30)*(A1:A30<>"");5))*A1:A30)/5

Предполагается, что диапазон значений находится в A1:A30.

  • Результатом вычисления формулы СТРОКА(A1:A30)*(A1:A30<>"") является массив номеров строк с непустыми ячейками.
  • Пятый наибольший номер строки (НАИБОЛЬШИЙ(СТРОКА(A1:A30)*(A1:A30<>"");5)) – это номер строки, начиная с которой располагаются последние 5 значений.
  • Результатом вычисления
  • (СТРОКА(A1:A30)*(A1:A30<>"")>= НАИБОЛЬШИЙ(СТРОКА(A1:A30)*(A1:A30<>"");5))*A1:A30 является массив значений ИСТИНА/ЛОЖЬ, где ИСТИНА соответствует номерам строк последних 5 значений. Поэлементное умножение этого массива на сам массив значений, с последующим суммированием (функция СУММПРОИЗВ()), даст сумму последних 5 значений.
  • И наконец, разделив на 5, получим среднее последних 5 значений.

Как видно, наличие пропусков в диапазоне существенно усложняет формулу. Поэтому имеет смысл при заполнении и проектировании таблиц придерживаться правил приведенных в статье Советы по построению таблиц.

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (1 оценка)
Яндекс.Метрика