Általános képlet
=AVERAGE(OFFSET(A1,COUNT(A:A),0,-N))
Összegzés
Az utolsó 5 adatpont átlagolásához használhatja az ÁTLAG funkciót a COUNT és OFFSET függvényekkel együtt. Ezt a megközelítést használhatja az utolsó N adatpont átlagolására: utolsó 3 nap, utolsó 6 mérés stb. A bemutatott példában az F6 képlete a következő:
=AVERAGE(OFFSET(C3,COUNT(C:C),0,-5))
Megjegyzés: a magasság negatív értéke nem fog működni a Google lapokban. További információkért lásd alább.
Magyarázat
Az OFFSET funkció segítségével dinamikus téglalap alakú tartományok készíthetők kiindulási referencia és megadott sorok, oszlopok, magasság és szélesség alapján. A sorok és oszlopok argumentumai "kiegyenlítésként" működnek a kezdő referenciától. A magasság és szélesség argumentumok (mindkettő opcionális) meghatározza, hogy az utolsó tartomány hány sort és oszlopot tartalmaz. Ebben a példában az OFFSET a következőképpen van konfigurálva:
- referencia = C3
- sorok = COUNT (A: A)
- oszlop = 0
- magasság = -5
- szélesség = (nincs megadva)
A kiindulási referencia C3 cellaként van megadva a tényleges adatok fölött. Mivel azt akarjuk, hogy az OFFSET a C oszlop utolsó bejegyzéséből származó tartományt adja vissza, a COUNT függvény segítségével a C oszlop összes értékét megszámoljuk, hogy megkapjuk a szükséges soreltolást. A COUNT csak a számértékeket számolja, ezért a 3. sor fejlécét automatikusan figyelmen kívül hagyja.
A C oszlopban 8 numerikus értékkel az OFFSET képlet a következőképpen határoz meg:
OFFSET(C3,8,0,-5)
Ezekkel az értékekkel az OFFSET C3-tól kezdődik, 8 sort eltol C11-re, majd a -5 segítségével kiterjeszti a téglalap alakú tartományt "hátra" 5 sorral feljebb a C7: C11 tartomány létrehozásához.
Végül az OFFSET visszaadja a C7: C11 tartományt az ÁTLAG függvénynek, amely kiszámítja az adott tartomány értékeinek átlagát.
Excel vs. Táblázatok
Különös furcsa ezzel a képlettel, hogy nem fog működni a Google Táblázatokkal, mert a Táblázatok OFFSET függvénye nem enged negatív értéket a magasság vagy szélesség argumentumokban. Az Excel dokumentációja azt is kimondja, hogy a magasság vagy a szélesség nem lehet negatív, de úgy tűnik, hogy a negatív értékek jól működtek az Excelben az 1990-es évek óta.
A negatív magasság- vagy szélességértékek elkerülése érdekében használhat egy következő képletet:
=OFFSET(C4,COUNT(C:C)-5,0,5)
Ebben az esetben a C4 megjegyzés a kiinduló referencia. Az általános forma:
=AVERAGE(OFFSET(A1,COUNT(A:A)-N,0,N))
ahol A1 az átlagolni kívánt számok első cellája.
