Jak vypočítat střední čtvercovou chybu (MSE) v aplikaci Microsoft Excel

Ve statistice je střední kvadratická chyba (MSE) důležitou metrikou, která měří množství chyb ve statistickém modelu. Často se používá k posouzení přesnosti modelu, který se používá k předpovídání budoucích hodnot.

V tomto tutoriálu podrobněji vysvětlíme, co je MSE, a poté vám ukážeme, jak vypočítat MSE v Excelu.

Co je střední kvadratická chyba?

Průměrná kvadratická chyba (MSE) je výpočet, který měří průměrný kvadratický rozdíl mezi odhadovanými a skutečnými hodnotami v sadě dat. Jinými slovy, odhaduje množství chyb ve statistickém modelu. Pro statistiky vypočítává, jak přesně regresní přímka odpovídá sadě datových bodů.

Řekněme například, že máte model, který předpovídá hodnotu tržeb, které jako softwarová společnost za rok získáte. Na konci roku zapojíte skutečné prodejní hodnoty, které jste vygenerovali. Poté můžete vypočítat MSE a zjistit, jak dobře váš model předpověděl výsledek.

MSE se vypočítá jako průměr druhých mocnin rozdílů mezi předpokládanými a skutečnými hodnotami cílové proměnné.

Vzorec MSE vypadá takto:

Kde:

Σznamená součet hodnot;

nje velikost vzorku nebo počet pozorování;

Yijsou pozorované hodnoty a;

Ŷijsou předpokládané hodnoty.

Nižší hodnota chyby znamená, že model má menší standardní chybu a lépe předpovídá cílovou proměnnou. MSE je široce používán ve statistické regresi a analýze dat a může být užitečný při porovnávání různých modelů nebo ladění parametrů pro zlepšení přesnosti předpovědí.

Vypadá to zmateně, ale není příliš těžké to sledovat, když to rozebereme v následujících částech.

Jak vypočítat střední čtvercovou chybu v aplikaci Microsoft Excel

Existují dva hlavní způsoby, jak můžete vypočítat MSE v aplikaci Microsoft Excel: funkce SUMSQ, Funkce AVERAGE a vzorec MSE. Níže uvedený příklad vám ukážeme, jak vypočítat MSE pomocí každé z těchto funkcí:

V tomto zjednodušeném příkladu se podíváme na fiktivní hodnoty prodeje za každý měsíc (sloupec A). Odhadované hodnoty jsou uvedeny ve sloupci B a skutečné hodnoty ve sloupci C..

Výpočet MSE pomocí funkce SUMSQ

Funkce SUMSQ v aplikaci Excel se používá k výpočtu součtu druhých mocnin čísel v rozsahu. Zde je návod, jak jej použít k výpočtu MSE:

Vytvořte ve sloupci D nový sloupec s názvem „Rozdíl“. To bude představovat rozdíl mezi očekávanými a skutečnými hodnotami.

Pro výpočet rozdílu pro datové body v každém řádku použijte vzorec „C2-B2“.

Vyberte novou buňku a zadejte následující syntaxi: „=SUMSQ(D2:D13/COUNT(D2:D13)“. Nahraďte buňky ve vzorci správnými buňkami z tabulky aplikace Excel. Konečný výsledek v této buňce je vaše MSE.

Vypočítat MSE pomocí funkce AVERAGE

Funkce PRŮMĚR v MS Excel se používá k výpočtu průměru z rozsahu čísel. Tato metoda vrací stejný výsledek jako funkce SUMSQ, ale každý krok se provádí ručně.

Proveďte kroky 1 a 2 popsané v metodě SUMSQ. Tím se vrátí rozdíly mezi předpokládanými hodnotami a skutečnými údaji.

Sloupec E pojmenujte „Čtverec rozdílů“, poté pomocí vzorce „=D2^2“ vypočítejte druhou mocninu rozdílu každé hodnoty ve sloupci D.

Vyberte novou buňku a vypočítejte průměrnou hodnotu sloupce E. Tím se vrátí střední hodnota umocněných hodnot, které jste dříve vypočítali. Chcete-li to provést, zadejte „=AVERAGE(E2:E12)“ a stiskněte Enter. Nezapomeňte nahradit buňky buňkami, které jste použili v pracovní list. Výsledná hodnota dat je vaše MSE.

Statistická analýza v aplikaci Microsoft Excel

Microsoft Excel je již dlouho jedním z nejvýkonnějších nástrojů pro analýzu dat. Doufejme, že s tímto tutoriálem nyní můžete použít Excel k snadnému výpočtu MSE. A protože jsou si tak podobné, měli byste být schopni provádět stejné výpočty v Tabulky Google.

.

Související příspěvky: