Formuła ogólna
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)),ROW(data), data)))
Podsumowanie
Aby uśrednić ostatnie 3 wartości liczbowe w zakresie, możesz użyć formuły tablicowej opartej na kombinacji funkcji, aby przekazać ostatnie n wartości liczbowych do funkcji ŚREDNIA. W pokazanym przykładzie formuła w D6 to:
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)), ROW(data), data)))
gdzie „dane” to nazwany zakres B5: B13.
Uwaga: to jest formuła tablicowa i należy ją wprowadzić za pomocą Ctrl + Shift + Enter.
Wyjaśnienie
Funkcja ŚREDNIA obliczy średnią liczb przedstawionych w tablicy, więc prawie cała praca w tej formule polega na wygenerowaniu tablicy trzech ostatnich wartości liczbowych w zakresie. Działając od wewnątrz, funkcja JEŻELI służy do „filtrowania” wartości liczbowych:
IF(ISNUMBER(data),ROW(data))
Funkcja ISNUMBER zwraca TRUE dla wartości liczbowych i FALSE dla innych wartości (w tym spacji), a funkcja ROW zwraca numery wierszy, więc wynikiem tej operacji jest liczba wierszy tablicy, które odpowiadają wpisom numerycznym:
(5;6;FALSE;8;9;10;FALSE;12;13)
Ta tablica przechodzi do funkcji LARGE ze stałą tablicową (1, 2, 3) dla k. LARGE automatycznie ignoruje wartości FAŁSZ i zwraca tablicę z trzema największymi liczbami, które odpowiadają trzem ostatnim wierszom z wartościami liczbowymi:
(13,12,10)
Ta tablica trafia do funkcji WYSZUKAJ jako wartość wyszukiwania. Tablica przeglądowa jest udostępniana przez funkcję WIERSZ, a tablicą wyników jest nazwany zakres „dane”:
LOOKUP((13,12,10), ROW(data), data))
WYSZUKAJ zwraca następnie tablicę zawierającą odpowiednie wartości w „danych”, które są wprowadzane do ŚREDNIA:
=AVERAGE((100,92,90))
Obsługa mniejszej liczby wartości
Jeśli liczba wartości liczbowych spadnie poniżej 3, ta formuła zwróci błąd #LICZBA, ponieważ LARGE nie będzie w stanie zwrócić 3 wartości zgodnie z żądaniem. Jednym ze sposobów rozwiązania tego problemu jest zastąpienie zakodowanej na stałe tablicy stałej (1, 2, 3) tablicą dynamiczną utworzoną przy użyciu opcji POŚREDNIE w następujący sposób:
ROW(INDIRECT("1:"&MIN(3,COUNT(data))))
Tutaj MIN służy do ustawienia górnej granicy tablicy na 3 lub rzeczywistej liczby wartości liczbowych, w zależności od tego, która z nich jest mniejsza.
Uwaga: spotkałem się z tym sprytnym podejściem na chandoo.org, w odpowiedzi Sajana na podobne pytanie.
