Yleinen kaava
=AVERAGE(OFFSET(A1,COUNT(A:A),0,-N))
Yhteenveto
Voit keskittää viimeiset 5 datapistettä käyttämällä AVERAGE-funktiota yhdessä COUNT- ja OFFSET-toimintojen kanssa. Voit käyttää tätä lähestymistapaa viimeisten N datapisteen keskiarvoon: viimeiset 3 päivää, viimeiset 6 mittausta jne. Esitetyssä esimerkissä kaava kohdassa F6 on:
=AVERAGE(OFFSET(C3,COUNT(C:C),0,-5))
Huomaa: negatiivisen korkeuden arvo ei toimi Google-taulukoissa. Katso lisätietoja alla.
Selitys
OFFSET-toimintoa voidaan käyttää dynaamisten suorakulmaisten alueiden muodostamiseen alkuviitteen ja annettujen rivien, sarakkeiden, korkeuden ja leveyden perusteella. Rivit ja sarakkeet argumentit toimivat kuten "siirtymät" alkuviitteestä. Korkeuden ja leveyden argumentit (molemmat valinnaiset) määrittävät, kuinka monta riviä ja saraketta lopullinen alue sisältää. Tässä esimerkissä OFFSET on määritetty seuraavasti:
- viite = C3
- rivit = COUNT (A: A)
- cols = 0
- korkeus = -5
- leveys = (ei toimiteta)
Lähtöviite annetaan C3-soluna todellisen datan yläpuolella. Koska haluamme, että OFFSET palauttaa sarakkeen C viimeisestä merkinnästä peräisin olevan alueen, käytämme COUNT-funktiota laskemaan kaikki sarakkeessa C olevat arvot saadaksesi vaaditun rivinsiirtymän. COUNT laskee vain numeeriset arvot, joten rivin 3 otsikko ohitetaan automaattisesti.
Kun sarakkeessa C on 8 numeerista arvoa, OFFSET-kaava ratkaisee:
OFFSET(C3,8,0,-5)
Näillä arvoilla OFFSET alkaa kohdasta C3, siirtää 8 riviä C11: een ja käyttää sitten -5 laajentaakseen suorakulmaista aluetta ylöspäin "taaksepäin" 5 riviä luodakseen alueen C7: C11.
Lopuksi OFFSET palauttaa alueen C7: C11 AVERAGE-funktiolle, joka laskee kyseisen alueen arvojen keskiarvon.
Excel vs. arkit
Pariton omituisuus tällä kaavalla on, että se ei toimi Google Sheetsin kanssa, koska Sheetsin OFFSET-toiminto ei salli negatiivisia arvoja korkeus- tai leveysargumenteille. Excel-dokumentaatiossa todetaan myös, että korkeus tai leveys eivät voi olla negatiivisia, mutta näyttää siltä, että negatiiviset arvot ovat toimineet hyvin Excelissä 1990-luvulta lähtien.
Negatiivisten korkeus- tai leveysarvojen välttämiseksi voit käyttää tällaista kaavaa:
=OFFSET(C4,COUNT(C:C)-5,0,5)
Huomautus C4 on lähtötaso tässä tapauksessa. Yleinen muoto on:
=AVERAGE(OFFSET(A1,COUNT(A:A)-N,0,N))
missä A1 on ensimmäinen solu keskimääräisissä numeroissa.
