Vispārēja formula
=AVERAGE(OFFSET(A1,COUNT(A:A),0,-N))
Kopsavilkums
Lai vidēji aprēķinātu pēdējos 5 datu punktus, varat izmantot funkciju AVERAGE kopā ar funkcijām COUNT un OFFSET. Šo pieeju varat izmantot, lai vidēji aprēķinātu pēdējos N datu punktus: pēdējās 3 dienas, pēdējie 6 mērījumi utt. Parādītajā piemērā formula F6 ir:
=AVERAGE(OFFSET(C3,COUNT(C:C),0,-5))
Piezīme: negatīva augstuma vērtība Google lapās nedarbosies. Plašāku informāciju skatiet zemāk.
Paskaidrojums
Funkciju OFFSET var izmantot, lai izveidotu dinamiskus taisnstūra diapazonus, pamatojoties uz sākuma atsauci un norādītajām rindām, kolonnām, augstumu un platumu. Rindu un kolonnu argumenti darbojas kā “nobīdes” no sākuma atsauces. Argumenti par augstumu un platumu (abi nav obligāti) nosaka, cik rindu un kolonnu ietver galīgais diapazons. Šajā piemērā OFFSET ir konfigurēts šādi:
- atsauce = C3
- rindas = COUNT (A: A)
- cols = 0
- augstums = -5
- platums = (nav paredzēts)
Sākuma atsauce tiek sniegta kā C3 šūna virs faktiskajiem datiem. Tā kā mēs vēlamies, lai OFFSET atgriež diapazonu, kas sākas no C slejas pēdējā ieraksta, mēs izmantojam funkciju COUNT, lai saskaitītu visas vērtības C slejā, lai iegūtu vajadzīgo rindas nobīdi. COUNT skaita tikai skaitliskās vērtības, tāpēc 3. rindas virsraksts tiek automātiski ignorēts.
Ar 8 skaitliskām vērtībām C slejā OFFSET formula tiek atrisināta šādi:
OFFSET(C3,8,0,-5)
Ar šīm vērtībām OFFSET sākas ar C3, nobīda 8 rindas uz C11, pēc tam izmanto -5, lai paplašinātu taisnstūra diapazonu uz augšu "atpakaļ" 5 rindas, lai izveidotu diapazonu C7: C11.
Visbeidzot, OFFSET atgriež diapazonu C7: C11 funkcijai AVERAGE, kas aprēķina vidējo vērtību šajā diapazonā.
Excel pret izklājlapām
Nepāra dīvainība ar šo formulu ir tāda, ka tā nedarbosies ar Google izklājlapām, jo funkcija OFFSET izklājlapās neļaus negatīvu vērtību argumentiem augstums vai platums. Excel dokumentācijā arī norādīts, ka augstums vai platums nevar būt negatīvi, bet šķiet, ka negatīvās vērtības programmā Excel darbojās labi kopš 1990. gadiem.
Lai izvairītos no negatīvām augstuma vai platuma vērtībām, varat izmantot šādu formulu:
=OFFSET(C4,COUNT(C:C)-5,0,5)
Paziņojums C4 šajā gadījumā ir sākuma atsauce. Vispārīgā forma ir:
=AVERAGE(OFFSET(A1,COUNT(A:A)-N,0,N))
kur A1 ir pirmā šūna skaitļos, kurus vēlaties vidēji aprēķināt.
