Vispārēja formula
=AVERAGE(OFFSET(firstcell,0,COUNT(rng)-N,1,N))
Kopsavilkums
Lai vidēji aprēķinātu pēdējās 5 datu vērtības kolonnu diapazonā, varat izmantot funkciju AVERAGE kopā ar funkcijām COUNT un OFFSET. Parādītajā piemērā formula F6 ir:
=AVERAGE(OFFSET(D5,0,COUNT(D5:J5)-5,1,5))
Paskaidrojums
Funkciju OFFSET var izmantot, lai izveidotu dinamiskos diapazonus, izmantojot sākuma šūnu un norādot rindas, kolonnas, augstumu un platumu.
Rindu un kolonnu argumenti darbojas kā “nobīdes” no sākuma atsauces. Argumenti par augstumu un platumu, kas nav obligāti, nosaka, cik rindu un kolonnu ietver galīgais diapazons. Mēs vēlamies, lai OFFSET atgriež diapazonu, kas sākas ar pēdējo ierakstu un paplašinās "atpakaļ", tāpēc mēs sniedzam šādus argumentus:
atsauce - sākuma atsauce ir D5 - šūna tieši pa labi no formulas, un pirmā šūna vērtību diapazonā, ar kuru mēs strādājam.
rindas - rindu argumentam izmantojam 0, jo vēlamies palikt vienā rindā.
kolonnas - kolonnu argumentam mēs izmantojam funkciju COUNT, lai saskaitītu visas vērtības diapazonā, pēc tam atņemot 5. Tas pārvieto diapazona 5 kolonnu sākumu pa kreisi.
augstums - mēs izmantojam 1, jo kā gala rezultātu vēlamies 1 rindu diapazonu.
platums - mēs izmantojam 5, jo mēs vēlamies galīgo diapazonu ar 5 kolonnām.
Formulai C5 OFFSET atgriež galīgo F5: J5 diapazonu. Tas attiecas uz funkciju AVERAGE, kas atgriež vidējo vērtību no 5 diapazona vērtībām
Mazāk par 5 vērtībām
Ja ir mazāk par 5 vērtībām, formula atgriezīs apļveida atsauces kļūdu, jo diapazons atkal paplašināsies šūnā, kurā ir formula. Lai novērstu šo kļūdu, formulu var pielāgot šādi:
=AVERAGE(OFFSET(first,0,COUNT(rng)-MIN(N,COUNT(rng)),1,MIN(N,COUNT(rng))))
Šeit mēs izmantojam MIN funkciju, lai "noķertu" situācijas, kad vērtības ir mazākas par 5, un faktisko skaitu, ja tādas ir.
