Bendroji formulė
=AVERAGE(OFFSET(A1,COUNT(A:A),0,-N))
Santrauka
Norėdami apskaičiuoti 5 pastarųjų duomenų taškų vidurkį, galite naudoti funkciją AVERAGE kartu su COUNT ir OFFSET funkcijomis. Galite naudoti šį metodą, kad vidutiniškai įvertintumėte paskutinius N duomenų taškus: paskutines 3 dienas, paskutinius 6 matavimus ir kt. Parodytame pavyzdyje formulė F6 yra:
=AVERAGE(OFFSET(C3,COUNT(C:C),0,-5))
Pastaba: neigiama aukščio vertė neveiks „Google“ lapuose. Daugiau informacijos rasite žemiau.
Paaiškinimas
Funkcija OFFSET gali būti naudojama kuriant dinaminius stačiakampius diapazonus, atsižvelgiant į pradinę atskaitą ir pateiktas eilutes, stulpelius, aukštį ir plotį. Eilučių ir stulpelių argumentai veikia kaip „atskaitos“ nuo pradinės nuorodos. Aukčio ir pločio argumentai (abu nebūtini) nustato, kiek eilučių ir stulpelių apima galutinis diapazonas. Šiame pavyzdyje OFFSET sukonfigūruotas taip:
- nuoroda = C3
- eilučių = COUNT (A: A)
- coliai = 0
- aukštis = -5
- plotis = (nepateikta)
Pradinė nuoroda pateikiama kaip langelis C3 virš faktinių duomenų. Kadangi norime, kad OFFSET grąžintų diapazoną, kilusį iš paskutinio C stulpelio įrašo, mes naudojame funkciją COUNT, kad suskaičiuotume visas C stulpelio reikšmes, kad gautume reikiamą eilutės poslinkį. COUNT skaičiuoja tik skaitines vertes, todėl 3 eilutės antraštė automatiškai nepaisoma.
C stulpelyje esant 8 skaitinėms reikšmėms, OFFSET formulė pasirenka:
OFFSET(C3,8,0,-5)
Naudojant šias reikšmes, OFFSET prasideda nuo C3, 8 eilutes perkelia į C11, tada naudoja -5, kad stačiakampis diapazonas būtų išplėstas „atgal“ 5 eilutėmis, kad būtų sukurtas diapazonas C7: C11.
Galiausiai OFFSET grąžina diapazoną C7: C11 į AVERAGE funkciją, kuri apskaičiuoja to diapazono verčių vidurkį.
„Excel“ ir „Lapai“
Keistas šios formulės keiksmas yra tai, kad ji neveiks su „Google“ skaičiuoklėmis, nes „Sheets“ funkcija OFFSET neleis neigiamos aukščio ar pločio argumentų vertės. „Excel“ dokumentuose taip pat teigiama, kad aukštis ar plotis negali būti neigiami, tačiau atrodo, kad neigiamos vertės „Excel“ puikiai veikė nuo 1990 m.
Norėdami išvengti neigiamų aukščio ar pločio verčių, galite naudoti tokią formulę:
=OFFSET(C4,COUNT(C:C)-5,0,5)
C4 pranešimas yra pradinė nuoroda šiuo atveju. Bendra forma yra:
=AVERAGE(OFFSET(A1,COUNT(A:A)-N,0,N))
kur A1 yra pirmoji ląstelė skaičiuose, kuriuos norite vidurkinti.
