Üldine valem
=AVERAGE(OFFSET(A1,COUNT(A:A),0,-N))
Kokkuvõte
Viie viimase andmepunkti keskmiseks saab kasutada funktsiooni AVERAGE koos funktsioonidega COUNT ja OFFSET. Selle lähenemisviisi abil saate keskmistada viimased N andmepunkti: viimased 3 päeva, viimased 6 mõõtmist jne. Näidatud näites on valem F6-s järgmine:
=AVERAGE(OFFSET(C3,COUNT(C:C),0,-5))
Märkus. Google'i lehtedes ei tööta kõrguse negatiivne väärtus. Lisateavet leiate allpool.
Selgitus
Funktsiooni OFFSET saab kasutada dünaamiliste ristkülikukujuliste vahemike koostamiseks algviite ja antud ridade, veergude, kõrguse ja laiuse põhjal. Ridade ja veergude argumendid toimivad algviidetelt nagu "nihked". Kõrguse ja laiuse argumendid (mõlemad valikulised) määravad, mitu rida ja veergu viimane vahemik sisaldab. Selles näites on OFFSET konfigureeritud järgmiselt:
- viide = C3
- read = COUNT (A: A)
- veerud = 0
- kõrgus = -5
- laius = (pole komplektis)
Lähteviide esitatakse lahtrina C3 tegelike andmete kohal. Kuna me tahame, et OFFSET tagastaks veeru C viimasest kirjest pärineva vahemiku, kasutame funktsiooni COUNT kõigi veerus C olevate väärtuste loendamiseks, et saada vajalik rea nihe. COUNT loeb ainult arvväärtusi, seega ignoreeritakse 3. rea pealkirja automaatselt.
Kui veerus C on 8 arvväärtust, on OFFSET-valem järgmine:
OFFSET(C3,8,0,-5)
Nende väärtuste korral algab OFFSET C3-st, nihutab 8 rida C11-ni, seejärel kasutab -5, et laiendada ristkülikukujulist vahemikku 5 rida "tagurpidi" ülespoole, et luua vahemik C7: C11.
Lõpuks tagastab OFFSET vahemiku C7: C11 funktsioonile AVERAGE, mis arvutab selle vahemiku väärtuste keskmise.
Excel vs lehed
Selle valemi kummaline pilk on see, et see ei tööta Google Sheetsiga, kuna arvutustabelite funktsioon OFFSET ei võimalda kõrguse ega laiuse argumentide negatiivset väärtust. Exceli dokumentatsioon väidab ka, et kõrgus või laius ei saa olla negatiivsed, kuid tundub, et negatiivsed väärtused on Excelis alates 1990ndatest hästi töötanud.
Negatiivsete kõrguse või laiuse väärtuste vältimiseks võite kasutada järgmist valemit:
=OFFSET(C4,COUNT(C:C)-5,0,5)
Teade C4 on antud juhul algviide. Üldine vorm on:
=AVERAGE(OFFSET(A1,COUNT(A:A)-N,0,N))
kus A1 on esimene lahter arvudes, mida soovite keskmiselt arvutada.
