Generisk formel
=AVERAGE(OFFSET(A1,COUNT(A:A),0,-N))
Resumé
For at gennemsnitliggøre de sidste 5 datapunkter kan du bruge AVERAGE-funktionen sammen med COUNT- og OFFSET-funktionerne. Du kan bruge denne fremgangsmåde til at gennemsnitliggøre de sidste N-datapunkter: sidste 3 dage, sidste 6 målinger osv. I det viste eksempel er formlen i F6:
=AVERAGE(OFFSET(C3,COUNT(C:C),0,-5))
Bemærk: En negativ værdi for højden fungerer ikke i Google-ark. Se nedenfor for mere information.
Forklaring
OFFSET-funktionen kan bruges til at konstruere dynamiske rektangulære områder baseret på en startreference og givne rækker, kolonner, højde og bredde. Række- og kolonneargumenterne fungerer som "forskydninger" fra starthenvisningen. Argumenterne for højde og bredde (begge valgfri) bestemmer, hvor mange rækker og kolonner det endelige interval inkluderer. I dette eksempel er OFFSET konfigureret således:
- reference = C3
- rækker = COUNT (A: A)
- cols = 0
- højde = -5
- bredde = (medfølger ikke)
Startreferencen tilvejebringes som C3 cellen over de faktiske data. Da vi ønsker, at OFFSET skal returnere et interval, der stammer fra den sidste post i kolonne C, bruger vi COUNT-funktionen til at tælle alle værdier i kolonne C for at få den krævede rækkeforskydning. COUNT tæller kun numeriske værdier, så overskriften i række 3 ignoreres automatisk.
Med 8 numeriske værdier i kolonne C løser OFFSET-formlen til:
OFFSET(C3,8,0,-5)
Med disse værdier starter OFFSET ved C3, forskyder 8 rækker til C11 og bruger derefter -5 til at udvide det rektangulære område op "baglæns" 5 rækker for at oprette området C7: C11.
Endelig returnerer OFFSET området C7: C11 til AVERAGE-funktionen, som beregner gennemsnittet af værdier i dette interval.
Excel vs. ark
En mærkelig finurlighed med denne formel er, at den ikke fungerer med Google Sheets, fordi OFFSET-funktionen i Sheets ikke tillader en negativ værdi for højde- eller breddeargumenter. Excel-dokumentation angiver også, at højde eller bredde ikke kan være negativ, men det ser ud til, at negative værdier har fungeret fint i Excel siden 1990'erne.
For at undgå negative højde- eller breddeværdier kan du bruge en formel som denne:
=OFFSET(C4,COUNT(C:C)-5,0,5)
Bemærkning C4 er starthenvisningen i dette tilfælde. Den generelle form er:
=AVERAGE(OFFSET(A1,COUNT(A:A)-N,0,N))
hvor A1 er den første celle i de tal, du vil have et gennemsnit.
