Общая формула
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)),ROW(data), data)))
Резюме
Чтобы усреднить последние 3 числовых значения в диапазоне, вы можете использовать формулу массива, основанную на комбинации функций, чтобы передать последние n числовых значений в функцию СРЕДНЕЕ. В показанном примере формула в D6:
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)), ROW(data), data)))
где «данные» - именованный диапазон B5: B13.
Примечание: это формула массива, и ее необходимо вводить с помощью Ctrl + Shift + Enter.
Объяснение
Функция СРЗНАЧ вычисляет среднее значение чисел, представленных в массиве, поэтому почти вся работа в этой формуле заключается в создании массива из трех последних числовых значений в диапазоне. Работая изнутри, функция ЕСЛИ используется для «фильтрации» числовых значений:
IF(ISNUMBER(data),ROW(data))
Функция ISNUMBER возвращает TRUE для числовых значений и FALSE для других значений (включая пробелы), а функция ROW возвращает номера строк, поэтому результатом этой операции являются номера строк массива, которые соответствуют числовым записям:
(5;6;FALSE;8;9;10;FALSE;12;13)
Этот массив переходит в функцию НАИБОЛЬШИЙ с константой массива (1,2,3) для k. LARGE автоматически игнорирует значения FALSE и возвращает массив с 3 наибольшими числами, которые соответствуют последним 3 строкам с числовыми значениями:
(13,12,10)
Этот массив входит в функцию ПРОСМОТР в качестве значения поиска. Массив поиска предоставляется функцией ROW, а массив результатов - это именованный диапазон «data»:
LOOKUP((13,12,10), ROW(data), data))
Затем ПРОСМОТР возвращает массив, содержащий соответствующие значения в «данных», которые передаются в СРЕДНЕЕ:
=AVERAGE((100,92,90))
Обработка меньшего количества значений
Если количество числовых значений упадет ниже 3, эта формула вернет ошибку #NUM, поскольку LARGE не сможет вернуть 3 значения по запросу. Один из способов справиться с этим - заменить жестко запрограммированную константу массива (1,2,3) динамическим массивом, созданным с помощью INDIRECT, например:
ROW(INDIRECT("1:"&MIN(3,COUNT(data))))
Здесь MIN используется для установки верхнего предела массива равным 3 или фактического количества числовых значений, в зависимости от того, что меньше.
Примечание: я столкнулся с этим умным подходом на сайте chandoo.org в ответе Саяна на аналогичный вопрос.
