Általános képlet
=AVERAGE(OFFSET(firstcell,0,COUNT(rng)-N,1,N))
Összegzés
Az oszlopok tartományának utolsó 5 adatértékének átlagolásához használhatja az ÁTLAG funkciót a COUNT és OFFSET függvényekkel együtt. A bemutatott példában az F6 képlete a következő:
=AVERAGE(OFFSET(D5,0,COUNT(D5:J5)-5,1,5))
Magyarázat
Az OFFSET függvény felhasználható dinamikus tartományok összeállításához egy kezdő cellával, adott sorokkal, oszlopokkal, magassággal és szélességgel.
A sorok és oszlopok argumentumai "kiegyenlítésként" működnek a kezdő referenciától. A magasság és a szélesség argumentumok, amelyek nem kötelezőek, meghatározzák, hogy az utolsó tartomány hány sort és oszlopot tartalmaz. Azt akarjuk, hogy az OFFSET olyan tartományt adjon vissza, amely az utolsó bejegyzésnél kezdődik és "visszafelé" bővül, ezért az alábbiak szerint adunk meg argumentumokat:
referencia - a kiindulási referencia D5 - a képlet jobb oldalán található cella és az első cella az értéktartományban, amellyel dolgozunk.
sorok - 0-t használunk a sorok argumentumhoz, mert ugyanabban a sorban akarunk maradni.
oszlopok - az oszlopok argumentumhoz a COUNT függvény segítségével számoljuk a tartomány összes értékét, majd kivonjuk az 5. Ez balra tolja el az 5. oszlop elejét.
magasság - 1-et használunk, mivel 1 soros tartományt akarunk végeredményként.
szélesség - 5-öt használunk, mivel 5 oszlopos végső tartományt szeretnénk.
A C5 képletnél az OFFSET az F5: J5 végső tartományt adja vissza. Ez az AVERAGE funkcióba megy, amely a tartomány 5 értékének átlagát adja vissza
5-nél kevesebb érték
Ha kevesebb mint 5 érték van, a képlet egy kör alakú referencia hibát ad vissza, mivel a tartomány vissza fog nyúlni a képletet tartalmazó cellába. A hiba megelőzése érdekében a képletet az alábbiak szerint módosíthatja:
=AVERAGE(OFFSET(first,0,COUNT(rng)-MIN(N,COUNT(rng)),1,MIN(N,COUNT(rng))))
Itt a MIN függvényt használjuk olyan helyzetek "fogására", ahol kevesebb, mint 5 érték van, és a tényleges számot használjuk, ha vannak.
