Bendroji formulė
=AVERAGE(OFFSET(firstcell,0,COUNT(rng)-N,1,N))
Santrauka
Norėdami apskaičiuoti 5 paskutinių duomenų reikšmes stulpelių diapazone, galite naudoti funkciją AVERAGE kartu su COUNT ir OFFSET funkcijomis. Parodytame pavyzdyje formulė F6 yra:
=AVERAGE(OFFSET(D5,0,COUNT(D5:J5)-5,1,5))
Paaiškinimas
Funkcija OFFSET gali būti naudojama kuriant dinaminius diapazonus naudojant pradinę ląstelę ir nurodant 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 neprivalomi) nustato, kiek eilučių ir stulpelių apima galutinis diapazonas. Norime, kad OFFSET grąžintų diapazoną, kuris prasideda nuo paskutinio įrašo ir išsiplečia „atgal“, todėl pateikiame argumentus taip:
nuoroda - pradinė nuoroda yra D5 - langelis, esantis tiesiai nuo formulės, ir pirmoji reikšmių diapazono ląstelė, su kuria dirbame.
eilutės - argumentui eilutės naudojame 0, nes norime likti toje pačioje eilutėje.
stulpeliai - argumentui „column“ mes naudojame funkciją COUNT, kad suskaičiuotume visas diapazono reikšmes, tada atimame 5. Tai perkelia 5 diapazono stulpelių pradžią į kairę.
aukštis - mes naudojame 1, nes norime, kad galutinis rezultatas būtų 1 eilutės diapazonas.
plotis - mes naudojame 5, nes norime galutinio diapazono su 5 stulpeliais.
C5 formulėje OFFSET pateikia galutinį F5: J5 diapazoną. Tai eina į AVERAGE funkciją, kuri pateikia 5 diapazono verčių vidurkį
Mažiau nei 5 vertės
Jei yra mažiau nei 5 reikšmės, formulė pateiks apskrito atskaitos klaidą, nes diapazonas tęsis atgal į langelį, kuriame yra formulė. Norėdami išvengti šios klaidos, galite pritaikyti formulę taip:
=AVERAGE(OFFSET(first,0,COUNT(rng)-MIN(N,COUNT(rng)),1,MIN(N,COUNT(rng))))
Čia mes naudojame MIN funkciją, norėdami „sugauti“ situacijas, kai reikšmių yra mažiau nei 5, ir faktinį skaičių, kai jų yra.
