Общая формула
=AVERAGE(INDEX(table(column),ROWS(table)-(N-1)):INDEX(table(column),ROWS(table)))
Резюме
Чтобы вычислить среднее значение для последних N значений в таблице Excel (т.е. последние 3 строки, последние 5 строк и т. Д.), Вы можете использовать функцию AVERAGE вместе с функциями INDEX и ROWS. В показанном примере формула в F5:
=AVERAGE(INDEX(Table1(Sales),ROWS(Table1)-(F4-1)):INDEX(Table1(Sales),ROWS(Table1)))
Объяснение
Эта формула - хороший пример того, как структурированные ссылки могут значительно облегчить работу с данными в Excel. По сути, это то, что мы делаем:
=AVERAGE(first:last)
где «первая» - это ссылка на первую ячейку для включения в среднее значение, а «последняя» - это ссылка на последнюю ячейку для включения. В результате получается диапазон, включающий N ячеек для усреднения.
Чтобы получить первую ячейку в диапазоне, мы используем ИНДЕКС следующим образом:
INDEX(Table1(Sales),ROWS(Table1)-(F4-1))
Массив представляет собой весь столбец «Продажи», а номер строки обрабатывается путем вычитания (n-1) из общего количества строк.
В примере F4 содержит 3, поэтому номер строки 10- (3-1) = 8. При номере строки 8 ИНДЕКС возвращает C12.
Чтобы получить последнюю ячейку, мы снова используем ИНДЕКС следующим образом:
INDEX(Table1(Sales),ROWS(Table1))
В таблице 10 строк, поэтому ИНДЕКС возвращает C14.
Затем функция AVERAGE возвращает среднее значение C12: C14, что составляет 78,33 доллара США.
