Kako izračunati srednjo kvadratno napako (MSE) v Microsoft Excelu

V statistiki je povprečna kvadratna napaka (MSE) pomembna metrika, ki meri količino napake v statističnem modelu. Pogosto se uporablja za oceno točnosti modela, ki se uporablja za napovedovanje prihodnjih vrednosti.

V tej vadnici bomo podrobneje razložili, kaj je MSE, nato pa vam bomo pokazali, kako izračunati MSE v Excelu.

Kaj je povprečna kvadratna napaka?

Povprečna kvadratna napaka (MSE) je izračun, ki meri povprečno kvadratno razliko med ocenjenimi in dejanskimi vrednostmi v naboru podatkov. Z drugimi besedami, oceni količino napake v statističnem modelu. Za ljubitelje statistike izračuna, kako natančno se regresijska črta prilega nizu podatkovnih točk.

Recimo, da imate model, ki napoveduje vrednost prodaje, ki jo boste prejeli kot programsko podjetje v enem letu. Ob koncu leta prištejete dejanske prodajne vrednosti, ki ste jih ustvarili. Nato lahko izračunate MSE, da vidite, kako dobro je vaš model napovedal rezultat.

MSE se izračuna tako, da se vzame povprečje kvadratov razlik med predvidenimi in dejanskimi vrednostmi ciljne spremenljivke.

Formula MSE izgleda takole:

Kje:

1. Σ pomeni vsoto vrednosti;
2. n je velikost vzorca ali število opazovanj;
3. Yjazso opazovane vrednosti in;
4. Ŷjazso predvidene vrednosti.

Nižja vrednost napake pomeni, da ima model manjšo standardno napako in je boljši pri napovedovanju ciljne spremenljivke. MSE se pogosto uporablja v statistični regresiji in analizi podatkov ter je lahko v pomoč pri primerjavi različnih modelov ali parametrov uglaševanja za izboljšanje natančnosti napovedi.

To je videti zmedeno, vendar mu ni pretežko slediti, ko ga razčlenimo v naslednjih razdelkih.

Kako izračunati srednjo kvadratno napako v programu Microsoft Excel.

Obstajata dva glavna načina za izračun MSE v Microsoft Excelu: funkcija SUMSQ, funkcija AVERAGEin formulo MSE. Uporabili bomo spodnji primer, da vam pokažemo, kako izračunati MSE z vsako od teh funkcij:

V tem poenostavljenem primeru si bomo ogledali izmišljene prodajne vrednosti za vsak mesec (stolpec A). Ocenjene vrednosti so predstavljene v stolpcu B, dejanske vrednosti pa v stolpcu C.

Izračunajte MSE s funkcijo SUMSQ.

Funkcija SUMSQ v Excelu se uporablja za izračun vsote kvadratov števil v obsegu. Tukaj je opisano, kako ga uporabite za izračun MSE:

1. V stolpcu D ustvarite nov stolpec z imenom »Razlika”. To bo predstavljalo razliko med pričakovano in dejansko vrednostjo.

1. Uporabite formulo "C2-B2" za izračun razlike za podatkovne točke v vsaki vrstici.

1. Izberite novo celico in vnesite naslednjo sintakso: »=SUMSQ(D2:D13/COUNT(D2:D13)). Zamenjajte celice v formuli s pravilnimi celicami iz Excelove preglednice. Končni rezultat v tej celici je vaš MSE.

Izračunajte MSE s funkcijo AVERAGE.

Funkcija AVERAGE v MS Excelu se uporablja za izračun povprečja obsega števil. Ta metoda vrne enak rezultat kot funkcija SUMSQ, vendar se vsak korak izvede ročno.

1. Izvedite koraka 1 in 2, opisana v metodi SUMSQ. To bo vrnilo razlike med napovedanimi vrednostmi in dejanskimi podatki.

1. Ime stolpca E “Kvadrat razlik«, nato uporabite formulo »=D2^2« za izračun kvadrata razlike vsake vrednosti v stolpcu D.

1. Izberite novo celico in izračunajte povprečno vrednost stolpca E. To bo vrnilo srednjo vrednost kvadratov, ki ste jih predhodno izračunali. To naredite tako, da vnesete “=POVP.(E2:E12)” in pritisnete Vnesite. Zamenjajte celice s celicami, ki ste jih uporabili v svojem delovni list. Dobljena vrednost podatkov je vaš MSE.

Statistična analiza v Microsoft Excelu.

Microsoft Excel je že dolgo eno najmočnejših orodij za analizo podatkov. Upajmo, da lahko s to vadnico zdaj uporabite Excel za preprost izračun MSE. In ker sta si tako podobna, bi morali imeti možnost izvajati enake izračune Google Preglednice.

Naročite se na Help Desk Geek.

Pridružite se več kot 30.000 drugim, ki dnevno prejemajo nasvete, trike in bližnjice naravnost v svoj nabiralnik.