Generisk formel
=AVERAGE(OFFSET(A1,COUNT(A:A),0,-N))
Sammendrag
For å gjennomsnittliggjøre de siste 5 datapunktene, kan du bruke AVERAGE-funksjonen sammen med COUNT- og OFFSET-funksjonene. Du kan bruke denne tilnærmingen til å gjennomsnittlig de siste N datapunktene: siste 3 dager, siste 6 målinger osv. I eksemplet som er vist er formelen i F6:
=AVERAGE(OFFSET(C3,COUNT(C:C),0,-5))
Merk: en negativ verdi for høyde fungerer ikke i Google-ark. Se nedenfor for mer informasjon.
Forklaring
OFFSET-funksjonen kan brukes til å konstruere dynamiske rektangulære områder basert på en startreferanse og gitt rader, kolonner, høyde og bredde. Radene og kolonneargumentene fungerer som "forskyvninger" fra startreferansen. Argumentene for høyde og bredde (begge valgfrie) bestemmer hvor mange rader og kolonner det endelige området inkluderer. For dette eksemplet er OFFSET konfigurert slik:
- referanse = C3
- rader = COUNT (A: A)
- cols = 0
- høyde = -5
- bredde = (ikke oppgitt)
Startreferansen er gitt som C3 cellen over de faktiske dataene. Siden vi vil at OFFSET skal returnere et område som kommer fra den siste oppføringen i kolonne C, bruker vi COUNT-funksjonen til å telle alle verdiene i kolonne C for å få ønsket radforskyvning. COUNT teller bare numeriske verdier, så overskriften i rad 3 blir automatisk ignorert.
Med 8 numeriske verdier i kolonne C løser OFFSET-formelen til:
OFFSET(C3,8,0,-5)
Med disse verdiene starter OFFSET ved C3, forskyver 8 rader til C11, bruker deretter -5 for å utvide det rektangulære området opp "bakover" 5 rader for å lage området C7: C11.
Til slutt returnerer OFFSET området C7: C11 til AVERAGE-funksjonen, som beregner gjennomsnittet av verdiene i det området.
Excel mot ark
En merkelig særegenhet med denne formelen er at den ikke vil fungere med Google Sheets, fordi OFFSET-funksjonen i Sheets ikke tillater en negativ verdi for høyde- eller breddeargumenter. Excel-dokumentasjon sier også at høyde eller bredde ikke kan være negativ, men det ser ut til at negative verdier har fungert bra i Excel siden 1990-tallet.
For å unngå negative høyde- eller breddeverdier, kan du bruke en formel som denne:
=OFFSET(C4,COUNT(C:C)-5,0,5)
Merknad C4 er startreferansen i dette tilfellet. Den generelle formen er:
=AVERAGE(OFFSET(A1,COUNT(A:A)-N,0,N))
hvor A1 er den første cellen i tallene du vil gjennomsnittlig.
