Sok Excel -oktatóanyagomhoz hasonlóan ezt is az egyik barátomtól kapott kérdés ihlette. Ki akarta számítani a mozgóátlagot Excelben, és megkértem, hogy keressen rá az interneten (vagy nézzen meg egy YouTube -videót erről).
De aztán úgy döntöttem, hogy magam is írok egyet (az a tény, hogy az egyetemen némileg statisztika -csibész voltam, szintén kisebb szerepet játszott).
Mielőtt elmondanám, hogyan kell kiszámítani a mozgóátlagot Excelben, hadd adjak gyorsan egy áttekintést arról, hogy mit jelent a mozgóátlag, és milyen típusú mozgóátlagok vannak.
Ha arra a részre szeretne ugrani, ahol megmutatom, hogyan kell kiszámítani a mozgóátlagot Excelben, kattintson ide.
Megjegyzés: Nem vagyok szakértője a statisztikáknak, és ebben az oktatóanyagban nem az a szándékom, hogy mindenre kiterjedjen a mozgó átlagokkal kapcsolatban. Csak az a célom, hogy megmutassam, hogyan kell kiszámítani a mozgóátlagokat Excelben (röviden bemutatva, hogy mit jelentenek a mozgóátlagok).Mi az a mozgóátlag?
Biztos vagyok benne, hogy tudja, mi az átlagos érték.
Ha három napi napi hőmérsékleti adataim vannak, könnyen megmondhatja az elmúlt három nap átlagát (tipp: ehhez használhatja az Excel ÁTLAG funkcióját).
A mozgó átlag (más néven gördülő átlag vagy futóátlag) az, amikor az átlag időtartamát változatlanul tartja, de az új adatok hozzáadásával folyamatosan mozog.
Például a 3. napon, ha megkérdezem a 3 napos mozgóátlaghőmérsékletet, megadja az 1., 2. és 3. nap átlaghőmérsékletét. Ha pedig a 4. napon kérdezem a 3 napos mozgóátlaghőmérsékletet , megadod a 2., 3. és 4. nap átlagát.
Az új adatok hozzáadásával az időtartam (3 nap) változatlan marad, de a legfrissebb adatok alapján számítja ki a mozgóátlagot.
A mozgóátlagot erősen használják a technikai elemzésekhez, és sok bank és tőzsdei elemző használja ezt naponta (az alábbi példa a Market Realist webhelyről származik).
A mozgóátlagok típusai
Háromféle mozgóátlag létezik:
- Egyszerű mozgóátlag (SMA)
- Súlyozott mozgóátlag (WMA)
- Exponenciális mozgóátlag (EMA)
Egyszerű mozgóátlag (SMA)
Ez az adatpercek egyszerű átlaga az adott időtartamban.
Napi hőmérsékleti példánkban, ha egyszerűen az elmúlt 10 nap átlagát veszi, akkor a 10 napos egyszerű mozgóátlagot adja.
Ezt úgy érhetjük el, hogy az adott időtartamon átlagoljuk az adatpontokat. Az Excelben ezt könnyedén megteheti az ÁTLAG funkcióval (erről az oktatóanyag későbbi részében lesz szó).
Súlyozott mozgóátlag (WMA)
Tegyük fel, hogy az időjárás minden nappal hűvösebb lesz, és 10 napos mozgóátlagot használ a hőmérsékleti trendek kiszámításához.
A 10. nap hőmérséklete valószínűleg jobban jelzi a tendenciát az első naphoz képest (mivel a hőmérséklet minden nap csökken.)
Tehát jobban járunk, ha jobban bízunk a 10. nap értékében.
Annak érdekében, hogy ez tükröződjön a mozgó átlagunkban, nagyobb súlyt helyezhet a legfrissebb adatokra, és kevésbé a korábbi adatokra. Így továbbra is észlelheti a trendet, de nagyobb hatással van a legújabb adatokra.
Ezt hívják súlyozott mozgóátlagnak.
Exponenciális mozgóátlag (EMA)
Az exponenciális mozgóátlag a súlyozott mozgóátlag olyan típusa, ahol nagyobb súlyt kapnak a legújabb adatok, és a régebbi adatpontoknál exponenciálisan csökken.
Ezt exponenciális súlyozott mozgóátlagnak (EWMA) is nevezik
A különbség a WMA és az EMA között az, hogy a WMA segítségével bármilyen kritérium alapján hozzárendelhet súlyokat. Például egy 3 pontos mozgóátlagban 60% -os súlyú életkort rendelhet a legújabb adatponthoz, 30% -ot a középső adatponthoz és 10% -ot a legrégebbi adatponthoz.
Az EMA -ban nagyobb súlyt kap a legújabb érték, és a súly folyamatosan exponenciálisan csökken a korábbi értékekhez.
Elég a statisztikai előadásból.
Most merüljünk el, és nézzük meg, hogyan kell kiszámítani a mozgóátlagokat Excelben.
Egyszerű mozgóátlag (SMA) kiszámítása az Excel Adatelemző Toolpak használatával
A Microsoft Excel már rendelkezik egy beépített eszközzel az egyszerű mozgóátlagok kiszámításához.
Az úgynevezett Adatelemző Toolpak.
Mielőtt használhatná az Adatelemző eszközcsomagot, először ellenőriznie kell, hogy az Excel szalagban van -e. Jó esély van arra, hogy először néhány lépést kell tennie az engedélyezéshez.
Ha az Adatok lapon már rendelkezik az Adatelemzés lehetőséggel, hagyja ki az alábbi lépéseket, és tekintse meg a mozgóátlagok kiszámításának lépéseit.Kattintson az Adatok fülre, és ellenőrizze, hogy látja -e az Adatelemzés lehetőséget. Ha nem látja, kövesse az alábbi lépéseket, hogy elérhetővé tegye a szalagon.
- Kattintson a Fájl fülre
- Kattintson a Beállítások lehetőségre
- Az Excel beállításai párbeszédpanelen kattintson a Bővítmények elemre
- A párbeszédpanel alján válassza ki az Excel-bővítmények lehetőséget a legördülő menüben, majd kattintson az Ugrás gombra.
- A megnyíló Bővítmények párbeszédpanelen jelölje be az Analysis Toolpak lehetőséget
- Kattintson az OK gombra.
A fenti lépések lehetővé teszik az Adatelemző Eszközcsomagot, és ezt az opciót az Adatok lapon láthatja.
Tegyük fel, hogy rendelkezik az alábbi adatkészlettel, és ki szeretné számítani az utolsó három intervallum mozgóátlagát.
Az alábbi lépések az adatelemzés segítségével egyszerű mozgóátlag kiszámításához használhatók:
- Kattintson az Adatok fülre
- Kattintson az Adatelemzés lehetőségre
- Az Adatelemzés párbeszédpanelen kattintson a Mozgó átlag lehetőségre (előfordulhat, hogy egy kicsit görgetnie kell az eléréséhez).
- Kattintson az OK gombra. Ezzel megnyílik a „Mozgó átlag” párbeszédpanel.
- A Beviteli tartományban válassza ki azokat az adatokat, amelyekhez a mozgóátlagot szeretné kiszámítani (ebben a példában B2: B11)
- Az Időköz opcióban adja meg a 3-at (mivel hárompontos mozgóátlagot számítunk)
- A Kimeneti tartományban adja meg azt a cellát, ahol az eredményeket szeretné. Ebben a példában a C2 -t használom kimeneti tartományként
- Kattintson az OK gombra
A fenti lépések megadják a mozgóátlag eredményét, amint az alább látható.
Vegye figyelembe, hogy a C oszlop első két cellájának eredménye #N/A hiba. Ez azért van, mert ez egy hárompontos mozgóátlag, és legalább három adatpontra van szüksége az első eredmény megadásához. Tehát a tényleges mozgóátlag értékek a harmadik adatpont után kezdődnek.
Azt is észre fogja venni, hogy mindez az adatelemző eszközcsomag egy ÁTLAGOS képletet alkalmaz a cellákra. Tehát ha ezt manuálisan szeretné megtenni az Adatelemző eszközcsomag nélkül, akkor ezt biztosan megteheti.
Van azonban néhány dolog, amit könnyebb megtenni az adatelemző eszközcsomaggal. Például, ha meg akarja kapni a standard hibaértéket, valamint a mozgóátlag diagramját, csak annyit kell tennie, hogy bejelöl egy négyzetet, és az a kimenet része lesz.
Mozgóátlagok (SMA, WMA, EMA) kiszámítása az Excel képleteivel
A mozgóátlagokat az ÁTLAG képlet segítségével is kiszámíthatja.
Valójában, ha csak a mozgó átlagértékre van szüksége (és nem a standard hibára vagy diagramra), akkor egy képlet használata jobb (és gyorsabb) megoldás lehet, mint az Adatelemző Toolpak használata.
Ezenkívül az Adatelemző Toolpak csak az egyszerű mozgóátlagot (SMA) adja meg, de ha WMA -t vagy EMA -t szeretne kiszámítani, akkor csak a képletekre kell hagyatkoznia.
Egyszerű mozgóátlag kiszámítása képletek segítségével
Tegyük fel, hogy rendelkezik az alábbi adatkészlettel, és ki szeretné számítani a 3 pontos SMA-t:
A C4 cellába írja be a következő képletet:
= ÁTLAG (B2: B4)
Másolja ezt a képletet az összes cellára, és megadja az SMA -t minden napra.
Ne feledje: Amikor az SMA -t képletek segítségével számítja ki, meg kell győződnie arról, hogy a képlet hivatkozásai relatívak. Ez azt jelenti, hogy a képlet lehet = ÁTLAG (B2: B4) vagy = ÁTLAG ($ B2: $ B4), de nem lehet = ÁTLAG ($ B $ 2: $ B $ 4) vagy = ÁTLAG (B $ 2: B $ 4 ). A hivatkozás sorszámának részét a dollárjel nélkül kell megadni. Az abszolút és relatív referenciákról itt olvashat bővebben.
Mivel 3 pontos egyszerű mozgóátlagot (SMA) számolunk, az első két cella (az első két napban) üres, és a harmadik naptól kezdve használjuk a képletet. Ha szeretné, az első két értéket használhatja úgy, ahogy van, és az SMA értékét a harmadiktól kezdve.
Súlyozott mozgóátlag kiszámítása képletek segítségével
A WMA esetében ismernie kell az értékekhez rendelt súlyokat.
Tegyük fel például, hogy ki kell számítania a 3 pontos WMA -t az alábbi adatkészlethez, ahol 60% súlyt adnak a legújabb értéknek, 30% -ot az előtte lévőnek és 10% -át az előtte lévőnek.
Ehhez írja be a következő képletet a C4 cellába, és másolja az összes cellára.
= 0,6*B4+0,3*B3+0,1*B2
Mivel 3 pontos súlyozott mozgóátlagot (WMA) számolunk, az első két cella (az első két napon) üres, és a harmadik naptól kezdve használjuk a képletet. Ha szeretné, az első két értéket használhatja úgy, ahogy van, és a WMA -értéket a harmadiktól kezdve használja.
Exponenciális mozgóátlag kiszámítása képletek segítségével
Az exponenciális mozgóátlag (EMA) nagyobb súlyt ad a legújabb értéknek, és a súlyok folyamatosan csökkennek a korábbi értékeknél.
Az alábbi képlet kiszámítja az EMA-t egy hárompontos mozgóátlaghoz:
EMA = [Legújabb érték - előző EMA érték] * (2 / N + 1) + Előző EMA
… Ahol N ebben a példában 3 lenne (mivel hárompontos EMA-t számolunk)
Megjegyzés: Az első EMA -értékhez (ha nincs korábbi értéke az EMA kiszámításához), egyszerűen vegye az értéket úgy, ahogy van, és tekintse az EMA -értéknek. Ezt az értéket használhatja tovább.Tegyük fel, hogy rendelkezik az alábbi adatkészlettel, és ki szeretné számítani a három periódusú EMA-t:
A C2 cellába írja be ugyanazt az értéket, mint a B2. Ennek az az oka, hogy az EMA kiszámításához nincs korábbi érték.
A C3 cellába írja be az alábbi képletet, és másolja az összes cellára:
= (B3-C2)*(2/4)+C2
Ebben a példában egyszerűnek tartottam, és a legújabb értéket és a korábbi EMA értéket használtam az aktuális EMA kiszámításához.
Ennek másik népszerű módja az, hogy először kiszámítja az egyszerű mozgóátlagot, majd azt használja a tényleges legújabb érték helyett.
Mozgó átlag trendvonal hozzáadása oszlopdiagramhoz
Ha rendelkezik adatkészlettel, és oszlopdiagramot készít ezzel, akkor a mozgóátlag trendvonalat is hozzáadhatja néhány kattintással.
Tegyük fel, hogy rendelkezik az alább látható adatkészlettel:
Az alábbiakban bemutatjuk az oszlopdiagram létrehozásának lépéseit ezen adatok felhasználásával, és egy háromrészes mozgóátlag trendvonal hozzáadásával ehhez a diagramhoz:
- Válassza ki az adatkészletet (beleértve a fejléceket)
- Kattintson a Beszúrás fülre
- A Diagram csoportban kattintson az „Oszlop vagy oszlopdiagram beszúrása” ikonra.
- Kattintson a Fürtözött oszlopdiagram lehetőségre. Ezzel beilleszti a diagramot a munkalapba.
- A diagram kiválasztása után kattintson a Tervezés fülre (ez a lap csak a diagram kiválasztásakor jelenik meg)
- A Diagramelrendezések csoportban kattintson az „Diagramelem hozzáadása” gombra.
- Vigye a kurzort a „Trendline” opcióra, majd kattintson a „További Trendline Options” lehetőségre
- A Trendvonal formázása panelen válassza a „Mozgó átlag” opciót, és állítsa be a periódusok számát.
Ez az! A fenti lépések mozgó trendvonalat adnának az oszlopdiagramhoz.
Ha egynél több mozgóátlag trendvonalat szeretne beszúrni (például egyet 2 periódusra és egyet 3 periódusra), ismételje meg a lépéseket 5 -től 8 -ig).
Ugyanezekkel a lépésekkel mozgóátlag trendvonalat is beszúrhat a vonaldiagramba.
A mozgó átlag trendvonal formázása
A szokásos vonaldiagrammal ellentétben a mozgó átlag trendvonal nem tesz lehetővé sok formázást. Például, ha egy bizonyos adatpontot szeretne kiemelni a trendvonalon, akkor ezt nem tudja megtenni.
A trendvonalban formázható néhány dolog:
- Szín a sorból. Ezzel kiemelheti az egyik trendvonalat, ha mindent világosra színez a diagramon, és a trendvonalat élénk színűvé teszi.
- Az vastagság a sorból
- Az átláthatóság a sorból
A mozgó átlag trendvonal formázásához kattintson rá jobb gombbal, majd válassza a Trendvonal formázása lehetőséget.
Ezzel megnyílik a Trendline formázása ablaktábla a jobb oldalon. Ez az ablaktábla, mint az összes formázási lehetőség (különböző szakaszokban - Kitöltés és vonal, Effektusok és Trendvonal beállítások).
