Splošna formula
=AVERAGE(OFFSET(A1,COUNT(A:A),0,-N))
Povzetek
Če želite povprečiti zadnjih 5 podatkovnih točk, lahko uporabite funkcijo AVERAGE skupaj s funkcijama COUNT in OFFSET. Ta pristop lahko uporabite za povprečenje zadnjih N podatkovnih točk: zadnjih 3 dni, zadnjih 6 meritev itd. V prikazanem primeru je formula v F6:
=AVERAGE(OFFSET(C3,COUNT(C:C),0,-5))
Opomba: negativna vrednost višine v Googlovih listih ne bo delovala. Za več informacij glejte spodaj.
Pojasnilo
Funkcijo OFFSET lahko uporabimo za izdelavo dinamičnih pravokotnih obsegov na podlagi začetne reference in podanih vrstic, stolpcev, višine in širine. Argumenti vrstic in stolpcev delujejo kot "odmiki" od začetne reference. Argumenta višine in širine (oba neobvezna) določata, koliko vrstic in stolpcev vključuje končni obseg. V tem primeru je OFFSET konfiguriran tako:
- sklic = C3
- vrstice = ŠTEVILO (A: A)
- stolpci = 0
- višina = -5
- širina = (ni na voljo)
Začetna referenca je podana kot celica C3 nad dejanskimi podatki. Ker želimo, da OFFSET vrne obseg, ki izvira iz zadnjega vnosa v stolpcu C, s funkcijo COUNT štejemo vse vrednosti v stolpcu C, da dobimo zahtevani odmik vrstice. COUNT šteje samo številske vrednosti, zato se naslov v vrstici 3 samodejno prezre.
Z 8 številskimi vrednostmi v stolpcu C formula OFFSET določa:
OFFSET(C3,8,0,-5)
Pri teh vrednostih se OFFSET začne pri C3, 8 vrstic premakne na C11, nato pa s -5 podaljša pravokotni obseg navzgor za 5 vrstic, da ustvari obseg C7: C11.
Končno funkcija OFFSET vrne obseg C7: C11 funkciji AVERAGE, ki izračuna povprečje vrednosti v tem obsegu.
Excel v primerjavi s tabelami
Nenavadna domislica s to formulo je, da ne bo delovala z Google Sheets, ker funkcija OFFSET v Sheets ne dovoli negativne vrednosti za argumente višine ali širine. V dokumentaciji programa Excel tudi piše, da višina ali širina ne moreta biti negativni, vendar se zdi, da so negativne vrednosti v Excelu dobro delovale od devetdesetih let prejšnjega stoletja.
Da bi se izognili negativnim vrednostim višine ali širine, lahko uporabite naslednjo formulo:
=OFFSET(C4,COUNT(C:C)-5,0,5)
Obvestilo C4 je v tem primeru začetna referenca. Splošna oblika je:
=AVERAGE(OFFSET(A1,COUNT(A:A)-N,0,N))
kjer je A1 prva celica v številih, ki jih želite povprečiti.
