Formuła ogólna
=AVERAGE(OFFSET(A1,COUNT(A:A),0,-N))
Podsumowanie
Aby uśrednić ostatnie 5 punktów danych, możesz użyć funkcji ŚREDNIA razem z funkcjami LICZNIK i PRZESUNIĘCIE. Możesz użyć tego podejścia do uśrednienia ostatnich N punktów danych: ostatnich 3 dni, ostatnich 6 pomiarów itp. W pokazanym przykładzie formuła w F6 to:
=AVERAGE(OFFSET(C3,COUNT(C:C),0,-5))
Uwaga: ujemna wartość wysokości nie będzie działać w arkuszach Google. Więcej informacji znajdziesz poniżej.
Wyjaśnienie
Funkcja PRZESUNIĘCIE może służyć do konstruowania dynamicznych prostokątnych zakresów w oparciu o początkowe odniesienie i podane wiersze, kolumny, wysokość i szerokość. 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. W tym przykładzie OFFSET jest skonfigurowany w następujący sposób:
- odniesienie = C3
- rzędy = COUNT (A: A)
- cols = 0
- wysokość = -5
- szerokość = (nie podano)
Początkowe odniesienie jest dostarczane jako C3 komórka powyżej rzeczywistych danych. Ponieważ chcemy, aby funkcja OFFSET zwracała zakres pochodzący od ostatniego wpisu w kolumnie C, używamy funkcji COUNT do zliczania wszystkich wartości w kolumnie C, aby uzyskać wymagane przesunięcie wiersza. COUNT liczy tylko wartości liczbowe, więc nagłówek w wierszu 3 jest automatycznie ignorowany.
Z 8 wartościami liczbowymi w kolumnie C, formuła PRZESUNIĘCIE rozkłada się na:
OFFSET(C3,8,0,-5)
Z tymi wartościami OFFSET zaczyna się od C3, przesuwa 8 wierszy do C11, a następnie używa -5, aby rozszerzyć prostokątny zakres w górę o 5 wierszy „wstecz” w celu utworzenia zakresu C7: C11.
Na koniec funkcja PRZESUNIĘCIE zwraca zakres C7: C11 do funkcji ŚREDNIA, która oblicza średnią wartości w tym zakresie.
Excel a Arkusze
Dziwnym dziwactwem związanym z tą formułą jest to, że nie będzie działać z Arkuszami Google, ponieważ funkcja PRZESUNIĘCIE w Arkuszach nie zezwala na wartości ujemne dla argumentów wysokości lub szerokości. Dokumentacja programu Excel stwierdza również, że wysokość lub szerokość nie mogą być ujemne, ale wydaje się, że wartości ujemne działają dobrze w programie Excel od lat 90-tych.
Aby uniknąć ujemnych wartości wysokości lub szerokości, możesz użyć następującego wzoru:
=OFFSET(C4,COUNT(C:C)-5,0,5)
Zauważ, że C4 jest punktem odniesienia w tym przypadku. Ogólna forma to:
=AVERAGE(OFFSET(A1,COUNT(A:A)-N,0,N))
gdzie A1 to pierwsza komórka w liczbach, które chcesz uśrednić.
