Splošna formula
=AVERAGE(OFFSET(firstcell,0,COUNT(rng)-N,1,N))
Povzetek
Če želite povprečnih zadnjih 5 podatkovnih vrednosti v obsegu stolpcev, lahko uporabite funkcijo AVERAGE skupaj s funkcijama COUNT in OFFSET. V prikazanem primeru je formula v F6:
=AVERAGE(OFFSET(D5,0,COUNT(D5:J5)-5,1,5))
Pojasnilo
Funkcijo OFFSET lahko uporabite za ustvarjanje dinamičnih obsegov z uporabo začetne celice in podanih vrstic, stolpcev, višine in širine.
Argumenti vrstic in stolpcev delujejo kot "odmiki" od začetne reference. Argumenta višine in širine, neobvezna, določata, koliko vrstic in stolpcev vključuje končni obseg. Želimo, da OFFSET vrne obseg, ki se začne pri zadnjem vnosu in se razširi "nazaj", zato podajamo argumente na naslednji način:
reference - začetna referenca je D5 - celica neposredno na desni strani formule in prva celica v območju vrednosti, s katerimi delamo.
vrstice - za argument vrstice uporabimo 0, ker želimo ostati v isti vrstici.
stolpci - za argument stolpci uporabljamo funkcijo COUNT za štetje vseh vrednosti v obsegu, nato odštejemo 5. To premakne začetek stolpca 5 v levo.
višina - uporabljamo 1, saj želimo kot končni rezultat obseg 1 vrstice.
width - uporabimo 5, saj želimo končni razpon s 5 stolpci.
Za formulo v C5 OFFSET vrne končno območje F5: J5. To preide v funkcijo AVERAGE, ki vrne povprečje 5 vrednosti v območju
Manj kot 5 vrednosti
Če je vrednosti manj kot 5, bo formula vrnila krožno referenčno napako, saj se bo obseg razširil nazaj v celico, ki vsebuje formulo. Da bi preprečili to napako, lahko prilagodite formulo na naslednji način:
=AVERAGE(OFFSET(first,0,COUNT(rng)-MIN(N,COUNT(rng)),1,MIN(N,COUNT(rng))))
Tu uporabljamo funkcijo MIN za "lovljenje" situacij, ko je manj kot 5 vrednosti, in dejansko štetje, kadar obstajajo.
