Обща формула
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)),ROW(data), data)))
Обобщение
За да осредните последните 3 числови стойности в диапазон, можете да използвате формула на масив, базирана на комбинация от функции, за да подадете последните n числови стойности във функцията AVERAGE. В показания пример формулата в D6 е:
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)), ROW(data), data)))
където "данни" е наименованият диапазон B5: B13.
Забележка: това е формула на масив и трябва да се въведе с control + shift + enter.
Обяснение
Функцията AVERAGE ще изчисли средната стойност на числата, представени в масив, така че почти цялата работа в тази формула е да генерира масив от последните 3 числови стойности в диапазон. Работейки отвътре навън, функцията IF се използва за "филтриране" на числови стойности:
IF(ISNUMBER(data),ROW(data))
Функцията ISNUMBER връща TRUE за числови стойности и FALSE за други стойности (включително заготовки), а функцията ROW връща номера на редове, така че резултатът от тази операция е номера на редове в масив, които съответстват на числови записи:
(5;6;FALSE;8;9;10;FALSE;12;13)
Този масив влиза във функцията LARGE с константата на масива (1,2,3) за k. LARGE автоматично игнорира FALSE стойностите и връща масив с най-големите 3 числа, които съответстват на последните 3 реда с числови стойности:
(13,12,10)
Този масив влиза във функцията LOOKUP като стойност за търсене. Търсещият масив се предоставя от функцията ROW, а резултатният масив е наименованият диапазон "data":
LOOKUP((13,12,10), ROW(data), data))
След това LOOKUP връща масив, съдържащ съответните стойности в "data", който се подава в AVERAGE:
=AVERAGE((100,92,90))
Обработка на по-малко стойности
Ако броят на числовите стойности падне под 3, тази формула ще върне грешката #NUM, тъй като LARGE няма да може да върне 3 стойности, както е поискано. Един от начините да се справите с това е да замените твърдо кодираната константа на масива (1,2,3) с динамичен масив, създаден с помощта на INDIRECT по следния начин:
ROW(INDIRECT("1:"&MIN(3,COUNT(data))))
Тук MIN се използва за задаване на горната граница на масива на 3 или на действителния брой на числовите стойности, което от двете е по-малко.
Забележка: Сблъсках се с този умен подход на chandoo.org в отговор на Sajan на подобен въпрос.
