Üldine valem
=AVERAGE(OFFSET(firstcell,0,COUNT(rng)-N,1,N))
Kokkuvõte
Veergude vahemiku viimase 5 andmeväärtuse keskmiseks arvutamiseks võite kasutada funktsiooni AVERAGE koos funktsioonidega COUNT ja OFFSET. Näidatud näites on valem F6-s järgmine:
=AVERAGE(OFFSET(D5,0,COUNT(D5:J5)-5,1,5))
Selgitus
Funktsiooni OFFSET saab kasutada dünaamiliste vahemike koostamiseks, kasutades algrakku ning antud ridu, veerge, kõrgust ja laiust.
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. Soovime, et OFFSET tagastaks vahemiku, mis algab viimasest sisestusest ja laieneb "tagurpidi", nii et esitame järgmised argumendid:
viide - algviide on D5 - lahter otse valemist paremal ja esimene lahter väärtuste vahemikus, millega töötame.
read - kasutame ridade argumendiks 0, sest tahame jääda samasse ritta.
veerud - argumendi veerud jaoks loome funktsiooni COUNT, et loendada kõik vahemikus olevad väärtused, seejärel lahutada 5. See nihutab vahemiku 5 veeru algust vasakule.
kõrgus - kasutame 1, kuna soovime lõpptulemuseks 1-realist vahemikku.
laius - kasutame 5, kuna soovime lõplikku vahemikku 5 veeruga.
C5 valemi korral tagastab OFFSET lõpliku vahemiku F5: J5. See läheb funktsiooni AVERAGE, mis tagastab vahemiku 5 väärtuse keskmise
Vähem kui 5 väärtust
Kui väärtusi on vähem kui 5, tagastab valem ümmarguse võrdlusvea, kuna vahemik ulatub tagasi valemit sisaldavasse lahtrisse. Selle tõrke vältimiseks saate valemit kohandada järgmiselt:
=AVERAGE(OFFSET(first,0,COUNT(rng)-MIN(N,COUNT(rng)),1,MIN(N,COUNT(rng))))
Siin kasutame funktsiooni MIN, et "tabada" olukordi, kus väärtusi on vähem kui 5, ja tegelikku arvu, kui neid on.
