Formuła ogólna
=AVERAGE(OFFSET(firstcell,0,COUNT(rng)-N,1,N))
Podsumowanie
Aby uśrednić ostatnie 5 wartości danych w zakresie kolumn, można użyć funkcji ŚREDNIA razem z funkcjami LICZBA i PRZESUNIĘCIE. W pokazanym przykładzie formuła w F6 to:
=AVERAGE(OFFSET(D5,0,COUNT(D5:J5)-5,1,5))
Wyjaśnienie
Funkcji PRZESUNIĘCIE można użyć do konstruowania zakresów dynamicznych przy użyciu komórki początkowej oraz określonych wierszy, kolumn, wysokości i szerokości.
Argumenty wierszy i kolumn działają jak „przesunięcia” od początkowego odniesienia. Argumenty wysokości i szerokości, oba opcjonalne, określają, ile wierszy i kolumn obejmuje końcowy zakres. Chcemy, aby funkcja OFFSET zwracała zakres, który zaczyna się od ostatniego wpisu i rozwija się „wstecz”, więc podajemy argumenty w następujący sposób:
odwołanie - odniesieniem początkowym jest D5 - komórka bezpośrednio po prawej stronie formuły i pierwsza komórka w zakresie wartości, z którymi pracujemy.
wiersze - używamy 0 jako argumentu wiersze, ponieważ chcemy pozostać w tym samym wierszu.
kolumny - dla argumentu kolumny korzystamy z funkcji COUNT, aby policzyć wszystkie wartości w zakresie, a następnie odjąć 5. Spowoduje to przesunięcie początku zakresu o 5 kolumn w lewo.
wysokość - używamy 1, ponieważ jako wynik końcowy chcemy zakres 1-wierszowy.
szerokość - używamy 5, ponieważ chcemy ostatecznego zakresu z 5 kolumnami.
W przypadku formuły w C5 funkcja OFFSET zwraca końcowy zakres F5: J5. Przechodzi to do funkcji ŚREDNIA, która zwraca średnią z 5 wartości w zakresie
Mniej niż 5 wartości
Jeśli jest mniej niż 5 wartości, formuła zwróci cykliczny błąd odwołania, ponieważ zakres rozszerzy się z powrotem do komórki zawierającej formułę. Aby zapobiec temu błędowi, możesz dostosować formułę w następujący sposób:
=AVERAGE(OFFSET(first,0,COUNT(rng)-MIN(N,COUNT(rng)),1,MIN(N,COUNT(rng))))
Tutaj używamy funkcji MIN do „wychwytywania” sytuacji, w których jest mniej niż 5 wartości, i używamy rzeczywistej liczby, gdy są.
