Összegzés
A mozgó vagy gördülő átlag kiszámításához használjon egy egyszerű képletet, amely az ÁTLAG függvényen alapul, relatív hivatkozásokkal. A bemutatott példában az E7 képlete a következő:
=AVERAGE(C5:C7)
Amint a képletet lefelé másoljuk, 3 napos mozgóátlagot számol az aktuális és a két előző nap eladási értéke alapján.
Az alábbiakban látható egy rugalmasabb opció, amely az OFFSET függvényen alapul, amely változó időszakokat kezel.
A mozgó átlagokról
A mozgó átlag (gördülő átlagnak is nevezzük) egy adott időközönkénti adathalmazokon alapuló átlag. Az átlag meghatározott időközönként történő kiszámítása kisimítja az adatokat a véletlen ingadozások hatásának csökkentésével. Ez megkönnyíti az általános trendek megtekintését, különösen egy diagramban. Minél nagyobb az intervallum, amelyet a mozgó átlag kiszámításához használunk, annál nagyobb a simítás, mivel több adatpont szerepel az egyes számított átlagokban.
Magyarázat
A példában bemutatott képletek mindegyike az AVERAGE függvényt használja, az egyes intervallumokhoz beállított relatív referenciával. Az E7 3 napos mozgóátlagát úgy számítják ki, hogy az ÁTLAG egy olyan tartományt adnak meg, amely tartalmazza az aktuális napot és a két előző napot:
=AVERAGE(C5:C7) // 3-day average
Az 5 napos és a 7 napos átlagokat ugyanúgy számítják ki. Mindkét esetben az ÁTLAG értékig terjedő tartomány megnövekszik a szükséges napok számával:
=AVERAGE(C5:C7) // 5-day average =AVERAGE(C5:C11) // 7-day average
Valamennyi képlet relatív referenciát használ az ÁTLAG funkcióhoz megadott tartományhoz. Amint a képleteket lefelé másoljuk az oszlopban, a tartomány minden sorban megváltozik, és tartalmazza az egyes átlagokhoz szükséges értékeket.
Ha az értékeket vonaldiagramon ábrázoljuk, akkor a simító hatás egyértelmű:
Nincs elegendő adat
Ha a képleteket a táblázat első sorában kezdi, az első néhány képlet nem lesz elegendő adat a teljes átlag kiszámításához, mert a tartomány az első adatsor fölé fog terjedni:
Ez a munkalap felépítésétől és attól függően, hogy fontos-e, hogy minden átlag ugyanannyi értéken alapuljon, lehet, hogy nem. Az ÁTLAG funkció automatikusan figyelmen kívül hagyja a szöveges értékeket és az üres cellákat, így folytatja az átlag kiszámítását kevesebb értékkel. Ezért "működik" az E5-ben és az E6-ban.
Az elégtelen adat egyértelmű jelzésének egyik módja az aktuális sorszám ellenőrzése és az #NA használatával történő megszakítás, ha kevesebb mint n érték van. Például a 3 napos átlaghoz a következőket használhatja:
=IF(ROW()-ROW($C$5)+1<3,NA(),AVERAGE(C3:C5))
A képlet első része egyszerűen létrehoz egy "normalizált" sorszámot, kezdve 1-vel:
ROW()-ROW($C$5)+1 // relative row number
Az 5. sorban az eredmény 1, a 6. sorban 2 és így tovább.
Ha az aktuális sorszám kevesebb, mint 3, a képlet # N / A értéket ad vissza. Ellenkező esetben a képlet mozgó átlagot ad vissza, mint korábban. Ez utánozza a Moving Average Analysis Toolpak verziójának viselkedését, amely # N / A értéket ad ki az első teljes időszak eléréséig.
Azonban a periódusok számának növekedésével végül elfogynak az adatok fölötti sorok, és nem tudja megadni a szükséges tartományt az ÁTLAGON belül. Például a munkalapon nem állíthat be mozgó 7 napos átlagot az ábrán látható módon, mivel nem adhat meg olyan tartományt, amely 6 sorral hosszabbít a C5 felett.
Változó időszakok az OFFSET-mel
A mozgó átlag kiszámításának rugalmasabb módja az OFFSET funkció. Az OFFSET dinamikus tartományt hozhat létre, ami azt jelenti, hogy beállíthatunk egy képletet, ahol a periódusok száma változó. Az általános forma:
=AVERAGE(OFFSET(A1,0,0,-n,1))
ahol n az egyes átlagokba beszámítandó időszakok száma. Mint fent, az OFFSET egy olyan tartományt ad vissza, amely átkerül az ÁTLAG funkcióba. Az alábbiakban látható ez a képlet működés közben, ahol "n" az elnevezett E2 tartomány. A C5 cellától kezdve az OFFSET létrehoz egy tartományt, amely az előző sorokig terjed. Ez úgy érhető el, hogy negatív n értékkel egyenlő magasságot használunk. Ha az E5 más számra változik, a mozgóátlag újraszámítja az összes sort:
Az E5 képlete, lemásolva:
=AVERAGE(OFFSET(C5,0,0,-n,1))
A fenti eredeti képlethez hasonlóan az OFFSET-tel rendelkező verziónak is problémája lesz az első néhány sorban az elégtelen adat, attól függően, hogy az E5-ben hány periódus van megadva.
A bemutatott példában az átlagok sikeresen kiszámíthatók, mert az ÁTLAG függvény automatikusan figyelmen kívül hagyja a szöveges értékeket és az üres cellákat, és a C5 fölött nincs más numerikus érték. Tehát, míg az E5 ÁTLAGBA átadott tartomány C1: C5, az átlaghoz csak egy érték van, 100. Az időszakok növekedésével azonban az OFFSET továbbra is olyan tartományt hoz létre, amely meghaladja az adatok kezdetét, végül belefut a munkalap tetején, és #REF hibát ad vissza.
Az egyik megoldás az, ha a tartomány méretét a rendelkezésre álló adatpontok számához "kötjük". Ez a MIN funkció használatával korlátozhatja a magassághoz használt számot az alábbiak szerint:
=AVERAGE(OFFSET(C5,0,0,-(MIN(ROW()-ROW($C$5)+1,n)),1))
Ez elég ijesztőnek tűnik, de valójában meglehetősen egyszerű. A MIN funkcióval korlátozzuk az OFFSET-be adott magasságot:
MIN(ROW()-ROW($C$5)+1,n)
A MIN belsejében az első érték egy relatív sorszám, amelyet az alábbiakkal számolunk:
ROW()-ROW($C$5)+1 // relative row number… 1,2,3, etc.
A MIN-nek adott második érték a periódusok száma, n. Ha a relatív sorszám kisebb, mint n, a MIN visszaadja az aktuális sorszámot az OFFSET értékre. Ha a sor száma nagyobb, mint n, a MIN n-t ad vissza. Más szavakkal, a MIN egyszerűen a kisebb értéket adja vissza.
Az OFFSET opció jó tulajdonsága, hogy n könnyen megváltoztatható. Ha n-t 7-re változtatunk, és az eredményeket ábrázoljuk, egy ilyen diagramot kapunk:
Megjegyzés: A fenti OFFSET-képletekkel kapcsolatos furcsaság az, hogy nem fognak működni a Google Táblázatokban, mert a Táblázatok OFFSET-funkciója nem engedi meg a magasság vagy a szélesség negatív értékét. A csatolt táblázat a Google-lapok megkerülő képleteivel rendelkezik.
