Среднее пяти последних значений в 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

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

• Результатом вычисления формулы СТРОКА(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 значений.

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