Średnia z ostatnich 7 niepustych niepustych komórek w Arkuszach Google

Mam liczby w zakresie D7: D i zdarza się, że niektóre komórki są puste. Chcę średnią z ostatnich 7 liczb, ale pomijam puste. Więc jeśli w ostatnich 7 są tylko 3, które są pełne, chcę, aby cofnął się dalej i znalazł 7 w sumie i uśrednił je.

Oto jedno podejście:

=average(indirect("D" & iferror(large(filter(row(D7:D), len(D7:D)), 7), row(D7)) & ":D"))

Wyjaśnienie:

• filter(row(D7:D), len(D7:D)) zwraca tablicę składającą się z numerów wierszy niepustych wpisów w podanym zakresie.
• large(..., 7) wybiera 7. największą liczbę z tej tablicy: jest to numer wiersza, w którym chcesz rozpocząć uśrednianie.
• iferror(..., row(D7))jest zabezpieczeniem na wypadek, gdyby w twoim zasięgu było mniej niż 7 niepustych wpisów: w takim przypadku uśrednianie rozpocznie się od D7. Mógłbym po prostu wstawić „7” zamiast row(D7), ale row(D7)czyni formułę bardziej przenośną na wypadek, gdybyś zdecydował się skopiować ją gdzie indziej.
• indirect("D" & ... & ":D")tworzy zakres uśredniania, np. D9:Djeśli wynikiem poprzedniego obliczenia było 9.
• Wreszcie averagerobi średnia. Można tu również umieścić inne funkcje agregujące.

TL; DR

=AVERAGE(OFFSET(A:A;COUNT(A:A)-7 + MATCH(TRUE;INDEX(A:A<>0);0)-1;0))

Przykład

Wyjaśnienie

• Najpierw policzymy, ile liczb jest w wierszu A z =COUNT(A:A). Komórki zawierające tekst nie będą liczone. Nasz przykład dałby nam9
• Od tej liczby odejmujemy 7 ponieważ chcesz średnią z ostatnich 7 wartości: =COUNT(A:A)-7. To da nam 2.

• Teraz używamy funkcji przesunięcia, aby uzyskać zakres pełnej kolumny, ale z przesunięciem 2 komórek, co oznacza, że ​​mamy tylko część kolumny z dokładnie 7 wartościami pozostałymi

  Wyobraź sobie kolumnę bez pustych komórek przed pierwszą komórką z wartościami. Wtedy może wyglądać uproszczony przykład =OFFSET(A:A; COUNT(A:A)-7 ;0). Ale to nie zadziała, gdy kolumna zacznie się od pustych komórek, jak pokazano na powyższym obrazku

• Teraz musimy rozszerzyć nasze przesunięcie o liczbę pustych komórek. Nie oznacza to, że zliczamy wszystkie puste wartości z rzędu. Tylko te przed pierwszą niepustą komórką.

  Możesz je policzyć =MATCH(TRUE;INDEX(A:A<>0);0)-1i otrzymać 6w wyniku lub nasz przykład

  Teraz modyfikujemy naszą funkcję przesunięcia i dodajemy tę liczbę, aby uzyskać prawidłowy zakres zawierający tylko 7 wartości
  OFFSET(A:A; COUNT(A:A)-7 + MATCH(TRUE;INDEX(A:A<>0);0)-1 ;0)

  

• W ostatnim kroku obliczamy średnią tego zakresu. Nasz przykład dałby nam 4 jako wynik, ponieważ średnia 1,2,3,4,5,6,7 wynosi 4.
  =AVERAGE(OFFSET(A:A;COUNT(A:A)-7 + MATCH(TRUE;INDEX(A:A<>0);0)-1;0))

Jeszcze inny sposób:

=AVERAGE(ARRAY_CONSTRAIN(SORT(FILTER(A:A, ISNUMBER(A:A)), 1, 1), 7, 1))

Wyjaśnienie:

1. odfiltruj wszystkie wartości liczbowe
2. obróć kolumnę do góry nogami (tak, aby ostatnie wartości były powyżej) za pomocą sort ()
3. ogranicz wyjście tej kolumny do 7 wierszy (używając ARRAY_CONSTRAIN)
4. Weź średnią ...

EDYCJA: Przepraszamy za nieporozumienie. Powyższe uśrednia wartości NAJWIĘKSZYCH wartości 7, a nie ostatnich 7 wartości w kolumnie. Uśrednianie ostatnich 7 wartości Rozwiązanie Normal Human jest świetne. Alternatywnym sposobem z mniejszą liczbą funkcji byłoby użycie QUERY ()

Zakładając dane liczbowe w kolumnie A, spróbuj:

=AVERAGE(QUERY(ARRAYFORMULA({ROW(A:A),A:A}),"select Col2 where Col2 is not null order by Col1 desc limit 7", 0))

=AVERAGE(IFERROR(QUERY(D7:D, 
 "where D is not null 
  offset "&COUNTA(QUERY(D7:D, "where D is not null", 0)) - 7, 0)))