Формула Excel: усередніть останні 3 числові значення -

Загальна формула

(=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, таким чином: