Slankiojo vidurkio skaičiavimas „Excel“ (paprastas, svertinis ir eksponentinis)

Kaip ir daugelis mano „Excel“ vadovėlių, šį taip pat įkvėpė viena iš mano draugo užklausų. Ji norėjo apskaičiuoti slankųjį vidurkį „Excel“, o aš paprašiau jos ieškoti internete (arba pažiūrėti „YouTube“ vaizdo įrašą apie tai).

Bet tada aš nusprendžiau pats parašyti (faktas, kad kolegijoje buvau šiek tiek statistikos durnas, taip pat atliko nedidelį vaidmenį).

Dabar, prieš jums pasakydamas, kaip apskaičiuoti slankųjį vidurkį „Excel“, leiskite man greitai apžvelgti, ką reiškia slankusis vidurkis ir kokie yra slankiųjų vidurkių tipai.

Jei norite pereiti prie dalies, kurioje parodau, kaip apskaičiuoti slankųjį vidurkį „Excel“, spustelėkite čia.

Pastaba: nesu statistikos ekspertas ir šioje pamokoje ketinu neapimti visko, kas susiję su slankiuoju vidurkiu. Aš tik noriu parodyti jums, kaip apskaičiuoti slankiuosius vidurkius „Excel“ (trumpai pristatydamas, ką reiškia slankieji vidurkiai).

Kas yra slenkamasis vidurkis?

Esu tikras, kad žinote, kokia yra vidutinė vertė.

Jei turiu trijų dienų dienos temperatūros duomenis, galite lengvai pasakyti paskutinių trijų dienų vidurkį (užuomina: tam galite naudoti „Excel“ funkciją VIDUTINIS).

Slenkamasis vidurkis (dar vadinamas slenkamuoju vidurkiu arba einamuoju vidurkiu) yra tada, kai vidurkio laikotarpis išlieka toks pats, tačiau toliau juda, kai pridedami nauji duomenys.

Pavyzdžiui, 3 dieną, jei paklausiu jūsų 3 dienų slenkančios vidutinės temperatūros, jūs man pateiksite 1, 2 ir 3 dienos vidutinę temperatūros vertę. Ir jei 4 dieną klausiu jūsų 3 dienų slenkančios vidutinės temperatūros , jūs man duosite 2, 3 ir 4 dienų vidurkį.

Pridedant naujų duomenų, laiko tarpas (3 dienos) išlieka tas pats, tačiau slankiojo vidurkio apskaičiavimui naudojate naujausius duomenis.

Slankusis vidurkis yra labai naudojamas techninei analizei, o daugelis bankų ir akcijų rinkos analitikų jį naudoja kasdien (žemiau pateiktas pavyzdys, kurį gavau iš „Market Realist“ svetainės).

Vienas iš slankiųjų vidurkių naudojimo pranašumų yra tas, kad jis suteikia jums tendenciją ir tam tikru mastu išlygina svyravimus. Pvz., Jei yra tikrai karšta diena, trijų dienų slenkamasis temperatūros vidurkis vis tiek užtikrintų, kad vidutinė vertė būtų išlyginta (užuot parodžiusi tikrai didelę vertę, kuri gali būti didesnė- viena ne egzempliorius).

Slankiųjų vidurkių tipai

Yra trys slankiųjų vidurkių tipai:

• Paprastas slenkamasis vidurkis (SMA)
• Svertinis slenkamasis vidurkis (WMA)
• Eksponentinis slenkamasis vidurkis (EMA)

Paprastas slenkamasis vidurkis (SMA)

Tai yra paprastas duomenų taškų vidurkis per nurodytą trukmę.

Mūsų dienos temperatūros pavyzdyje, kai paprasčiausiai imate paskutinių 10 dienų vidurkį, tai suteikia 10 dienų paprastą slankųjį vidurkį.

Tai galima pasiekti apskaičiuojant duomenų taškų vidurkį per nurodytą laikotarpį. „Excel“ tai galite lengvai padaryti naudodami funkciją AVERAGE (tai aptarta vėliau šiame vadove).

Svertinis slenkamasis vidurkis (WMA)

Tarkime, kad su kiekviena diena oras tampa vėsesnis, o jūs naudojate 10 dienų slankųjį vidurkį, kad gautumėte temperatūros tendenciją.

10 dienos temperatūra greičiausiai bus geresnis tendencijos rodiklis, palyginti su 1 diena (nes temperatūra krenta kiekvieną dieną).

Taigi, mums geriau, jei labiau pasikliausime 10 dienos verte.

Jei norite, kad tai atsispindėtų mūsų slankiuoju vidurkiu, galite daugiau dėmesio skirti naujausiems duomenims ir mažiau - ankstesniems duomenims. Tokiu būdu jūs vis dar matote tendenciją, tačiau turėdami didesnę įtaką naujausiems duomenims.

Tai vadinama svertiniu slankiuoju vidurkiu.

Eksponentinis slenkamasis vidurkis (EMA)

Eksponentinis slankusis vidurkis yra svertinio slankiojo vidurkio tipas, kai daugiau dėmesio skiriama naujausiems duomenims, o senesniems duomenų taškams jis mažėja eksponentiškai.

Jis taip pat vadinamas eksponentiniu svertiniu slenkamuoju vidurkiu (EWMA)

Skirtumas tarp WMA ir EMA yra tas, kad naudodami WMA galite priskirti svorius pagal bet kokius kriterijus. Pvz., 3 taškų slankiojo vidurkio atveju galite priskirti 60% svorio amžių naujausiam duomenų taškui, 30% viduriniam duomenų taškui ir 10% seniausiam duomenų taškui.

EMA didesnis svoris suteikiamas naujausiai vertei, o ankstesnėms vertėms svoris nuolat mažėja.

Užteks statistikos paskaitos.

Dabar pasinerkime ir pažiūrėkime, kaip „Excel“ apskaičiuoti slankiuosius vidurkius.

Paprastas slenkamasis vidurkis (SMA) apskaičiuojamas naudojant „Excel“ duomenų analizės įrankių paketą

„Microsoft Excel“ jau turi įmontuotą įrankį paprastiems slenkamiesiems vidurkiams apskaičiuoti.

Jis vadinamas Duomenų analizės įrankių rinkinys.

Prieš naudodami duomenų analizės įrankių paketą, pirmiausia turite patikrinti, ar turite jį „Excel“ juostelėje, ar ne. Yra didelė tikimybė, kad pirmiausia turite atlikti kelis veiksmus, kad jį įjungtumėte.

Jei skirtuke „Duomenys“ jau turite parinktį „Duomenų analizė“, praleiskite toliau nurodytus veiksmus ir peržiūrėkite slankiųjų vidurkių apskaičiavimo veiksmus.

Spustelėkite skirtuką Duomenys ir patikrinkite, ar matote parinktį Duomenų analizė, ar ne. Jei jo nematote, atlikite toliau nurodytus veiksmus, kad jis būtų pasiekiamas juostelėje.

1. Spustelėkite skirtuką Failas
2. Spustelėkite Parinktys
3. Dialogo lange „Excel“ parinktys spustelėkite „Papildiniai“
4. Dialogo lango apačioje išskleidžiamajame meniu pasirinkite „Excel“ priedai, tada spustelėkite Eiti.
5. Atsidariusiame dialogo lange „Papildiniai“ pažymėkite parinktį „Analysis Toolpak“
6. Spustelėkite Gerai.

Pirmiau minėti veiksmai įgalintų duomenų analizės įrankių paketą ir dabar pamatysite šią parinktį skirtuke Duomenys.

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite apskaičiuoti paskutinių trijų intervalų slankųjį vidurkį.

Žemiau pateikiami žingsniai, kaip naudoti duomenų analizę, norint apskaičiuoti paprastą slankųjį vidurkį:

1. Spustelėkite skirtuką Duomenys
2. Spustelėkite duomenų analizės parinktį
3. Dialogo lange Duomenų analizė spustelėkite parinktį „Judantis vidurkis“ (gali tekti šiek tiek slinkti, kad jį pasiektumėte).
4. Spustelėkite Gerai. Bus atidarytas dialogo langas „Slenkantis vidurkis“.
5. Įvesties diapazone pasirinkite duomenis, kuriems norite apskaičiuoti slankųjį vidurkį (šiame pavyzdyje B2: B11)
6. Parinktyje Intervalas įveskite 3 (nes skaičiuojame trijų taškų slankųjį vidurkį)
7. Išvesties diapazone įveskite langelį, kuriame norite rezultatų. Šiame pavyzdyje aš naudoju C2 kaip išvesties diapazoną
8. Spustelėkite Gerai

Pirmiau nurodyti veiksmai suteiks jums slankiojo vidurkio rezultatą, kaip parodyta žemiau.

Atminkite, kad pirmųjų dviejų C stulpelio langelių rezultatas yra #N/A klaida. Taip yra todėl, kad tai yra trijų taškų slenkamasis vidurkis ir norint gauti pirmąjį rezultatą reikia mažiausiai trijų duomenų taškų. Taigi faktinės slankiojo vidurkio vertės prasideda po trečiojo duomenų taško.

Taip pat pastebėsite, kad visa ši duomenų analizės priemonių rinkinys padarė langeliams AVERAGE formulę. Taigi, jei norite tai padaryti rankiniu būdu be duomenų analizės įrankių paketo, tai tikrai galite padaryti.

Tačiau yra keletas dalykų, kuriuos lengviau padaryti naudojant duomenų analizės įrankių paketą. Pavyzdžiui, jei norite gauti standartinę klaidos vertę ir slankiojo vidurkio diagramą, jums tereikia pažymėti langelį ir tai bus išvesties dalis.

Slankiųjų vidurkių (SMA, WMA, EMA) apskaičiavimas naudojant „Excel“ formules

Taip pat galite apskaičiuoti slankiuosius vidurkius naudodami formulę AVERAGE.

Tiesą sakant, jei viskas, ko jums reikia, yra slenkančio vidurkio vertė (o ne standartinė klaida ar diagrama), formulės naudojimas gali būti geresnė (ir greitesnė) parinktis nei naudojant duomenų analizės įrankių paketą.

Be to, duomenų analizės įrankių paketas pateikia tik paprastą slankųjį vidurkį (SMA), tačiau jei norite apskaičiuoti WMA arba EMA, turite pasikliauti tik formulėmis.

Paprasto slenkančio vidurkio apskaičiavimas naudojant formules

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite apskaičiuoti 3 taškų SMA:

Langelyje C4 įveskite šią formulę:

= VIDUTINIS (B2: B4)

Nukopijuokite šią formulę visoms ląstelėms ir gausite kiekvienos dienos SMA.

Atminkite: apskaičiuodami SMA naudodami formules, turite įsitikinti, kad formulės nuorodos yra santykinės. Tai reiškia, kad formulė gali būti = VIDUTINIS (B2: B4) arba = VIDUTINIS ($ B2: $ B4), bet negali būti = VIDUTINIS ($ B $ 2: $ B $ 4) arba = VIDUTINIS (B $ 2: B $ 4) ). Nuorodos eilutės numeris turi būti be dolerio ženklo. Daugiau apie absoliučias ir santykines nuorodas galite perskaityti čia.

Kadangi mes skaičiuojame 3 taškų paprastą slankųjį vidurkį (SMA), pirmosios dvi ląstelės (pirmas dvi dienas) yra tuščios ir mes pradedame naudoti formulę nuo trečios dienos. Jei norite, pirmąsias dvi reikšmes galite naudoti tokias, kokios yra, ir naudoti SMA reikšmę nuo trečiosios.

Svertinio slankiojo vidurkio apskaičiavimas naudojant formules

Norėdami gauti WMA, turite žinoti svorius, kurie būtų priskirti vertėms.

Pvz., Tarkime, kad turite apskaičiuoti 3 taškų WMA žemiau esančiam duomenų rinkiniui, kuriame 60% svoris pateikiamas naujausiai vertei, 30% - prieš tai esančiai vertei ir 10% - prieš tai esančiai vertei.

Norėdami tai padaryti, įveskite šią formulę langelyje C4 ir nukopijuokite visas ląsteles.

= 0,6*B4+0,3*B3+0,1*B2

Kadangi mes skaičiuojame 3 taškų svertinį slenkamąjį vidurkį (WMA), pirmosios dvi ląstelės (pirmas dvi dienas) yra tuščios ir mes pradedame naudoti formulę nuo trečios dienos. Jei norite, galite naudoti dvi pirmąsias reikšmes ir WMA reikšmę nuo trečiosios.

Eksponentinio slenkančio vidurkio apskaičiavimas naudojant formules

Eksponentinis slenkamasis vidurkis (EMA) suteikia didesnę reikšmę naujausiai vertei, o ankstesnių verčių svoris nuolat mažėja.

Žemiau pateikiama trijų taškų slankiojo vidurkio EMA apskaičiavimo formulė:

EMA = [Paskutinė vertė - ankstesnė EMA vertė] * (2 / N + 1) + Ankstesnė EMA

… Čia N būtų 3 šiame pavyzdyje (kaip mes skaičiuojame trijų taškų EMA)

Pastaba: Norėdami gauti pirmąją EMA vertę (kai neturite jokios ankstesnės vertės EMA apskaičiuoti), tiesiog paimkite vertę tokią, kokia ji yra, ir laikykite ją EMA verte. Tada galite naudoti šią vertę ateityje.

Tarkime, kad turite toliau nurodytą duomenų rinkinį ir norite apskaičiuoti trijų laikotarpių EMA:

C2 langelyje įveskite tą pačią vertę, kaip ir B2. Taip yra todėl, kad nėra ankstesnės vertės EMA apskaičiuoti.

C3 langelyje įveskite žemiau esančią formulę ir nukopijuokite visas ląsteles:

= (B3-C2)*(2/4)+C2

Šiame pavyzdyje aš laikiau paprastą ir dabartinei EMA apskaičiavimui naudojau naujausią ir ankstesnę EMA vertę.

Kitas populiarus būdas tai padaryti yra iš pradžių apskaičiuoti paprastą slankųjį vidurkį ir tada jį naudoti vietoj faktinės naujausios vertės.

Slankiojo vidurkio tendencijų linijos pridėjimas prie stulpelių diagramos

Jei turite duomenų rinkinį ir naudodami jį kuriate juostinę diagramą, keliais paspaudimais taip pat galite pridėti slankiojo vidurkio tendencijų liniją.

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau:

Toliau pateikiami žingsniai, kaip sukurti juostinę diagramą naudojant šiuos duomenis ir prie šios diagramos pridėti trijų dalių slankaus vidurkio tendencijų liniją:

1. Pasirinkite duomenų rinkinį (įskaitant antraštes)
2. Spustelėkite skirtuką Įterpti
3. Diagramos grupėje spustelėkite piktogramą „Įterpti stulpelį arba juostinę diagramą“.
4. Spustelėkite diagramą „Klasterinių stulpelių diagrama“. Tai įterps diagramą į darbalapį.
5. Pasirinkę diagramą, spustelėkite skirtuką Dizainas (šis skirtukas rodomas tik pasirinkus diagramą)
6. Diagramos išdėstymo grupėje spustelėkite „Pridėti diagramos elementą“.
7. Užveskite žymeklį ant parinkties „Trendline“, tada spustelėkite „Daugiau tendencijos linijos parinkčių“
8. Lange Format Trendline pasirinkite parinktį „Moving Average“ ir nustatykite laikotarpių skaičių.

Viskas! Atlikus aukščiau nurodytus veiksmus, jūsų stulpelių diagrama būtų įtraukta į judančią tendencijų liniją.

Jei norite įterpti daugiau nei vieną slankiojo vidurkio tendencijų liniją (pavyzdžiui, vieną 2 laikotarpiams ir vieną 3 laikotarpiams), pakartokite veiksmus nuo 5 iki 8).

Tais pačiais veiksmais galite įterpti slenkančio vidurkio tendencijų liniją ir į linijinę diagramą.

Slankiojo vidurkio tendencijų linijos formatavimas

Skirtingai nuo įprastos linijinės diagramos, slankiojančio vidurkio tendencijų linija neleidžia daug formatuoti. Pavyzdžiui, jei tendencijų linijoje norite paryškinti konkretų duomenų tašką, to padaryti negalėsite.

Keletas dalykų, kuriuos galite formatuoti tendencijų linijoje, yra šie:

• Spalva linijos. Tai galite naudoti norėdami paryškinti vieną iš tendencijų linijų, padarydami viską diagramoje šviesią spalvą ir išryškindami tendencijos liniją ryškia spalva
• The storis linijos
• The skaidrumo linijos

Norėdami formatuoti slenkančio vidurkio tendencijų liniją, dešiniuoju pelės mygtuku spustelėkite ją ir pasirinkite parinktį Formatuoti tendencijos liniją.

Taip atsidarys „Format Trendline“ sritis dešinėje. Ši sritis, kaip ir visos formatavimo parinktys (skirtinguose skyriuose - užpildymas ir linija, efektai ir tendencijų linijos parinktys).