V statistické analýze se odchylka mezi členy sady dat ukazuje, jak daleko od sebe jsou datové body z trendové linie, známé také jako a regresní čára. Čím vyšší je odchylka, tím více rozložených datových bodů je. Studie analýzy rozptylu ukazuje, které části rozptylu lze vysvětlit charakteristikami dat a které lze přičíst náhodným faktorům. Část odchylky, kterou nelze vysvětlit, se nazývá zbytkový rozptyl.
Použití tabulek aplikace Excel pro výpočet zbytkové odchylky
Vzorec pro výpočet zbytkové odchylky zahrnuje řadu složitých výpočtů. U malých datových sad může být proces výpočtu zbytkové odchylky ručně nudný. U velkých sad dat může být úkol vyčerpávající. Pomocí tabulky aplikace Excel stačí zadat datové body a vybrat správný vzorec. Program zpracovává složité výpočty a poskytuje výsledky rychle.
Datové body
Otevřete novou tabulku aplikace Excel a zadejte datové body do dvou sloupců. Regresní řádky vyžadují, aby každý datový bod měl dva prvky. Statistici obvykle označují tyto prvky "X" a "Y." Například Generic Insurance Co hledá zbytkovou odchylku výšky a váhy svých zaměstnanců. Proměnná X představuje výšku a proměnná Y představuje váhu. Zadejte výšku do sloupce A a váhy do sloupce B.
Hledání prostředku
The znamenat představuje průměr každého prvku v datové sadě. V tomto příkladě chce Generic pojištění najít průměrnou, směrodatnou odchylku a kovariantu ve výškách a závažích 10 zaměstnanců. Průměr výšek uvedených ve sloupci A lze nalézt zadáním funkce "= AVERAGE (A1: A10)" do buňky F1. Průměr závaží uvedených ve sloupci B lze nalézt zadáním funkce "= AVERAGE (B1: B10)" do buňky F3.
Zjištění standardní odchylky a
The standardní odchylka měří, jak daleko od sebe jsou datové body od středu. The kovariance měří, jak se společně mění dva prvky datového bodu. Standardní odchylka výšky se zjistí zadáním funkce "= STDEV (A1: A10)" do buňky F2. Standardní odchylka závaží se zjistí zadáním funkce "= STDEV (B1: B10)" do buňky F4. Kovarianta mezi výškami a závažími se nalézá zadáním do funkce "= COVAR (A1: A10; B1: B10)" do buňky F5.
Hledání regresní linky
The regresní čára představuje lineární funkci, která sleduje trend datových bodů. Vzorec pro regresní přímku vypadá takto: Y = aX + b.
Uživatel může najít hodnoty pro "a" a "b" pomocí výpočtů pro prostředky, standardní odchylky a kovarianci. Hodnota pro "b" představuje bod, ve kterém regresní přímka zachycuje os Y. Hodnotu lze zjistit tak, že použijeme kovarianci a dělíme ji o čtverec směrodatné odchylky hodnot X. Vzorec aplikace Excel přejde do buňky F6 a vypadá takto: = F5 / F2 ^ 2.
Hodnota pro "a" představuje sklon regresní čáry. Vzorec aplikace Excel přejde do buňky F7 a vypadá takto: = F3-F6 * F1.
Chcete-li vidět vzorec pro regresní přímku, zadejte toto zřetězení řetězce do buňky F8:
= FUNKCE (F7; 2)); KROKOVAT ("Y ="; KROK (F6; 2); X; IF (SIGN (F7) = 1;
Vypočítat hodnoty Y
Dalším krokem je výpočet hodnot Y na regresní lince pro zadané hodnoty X v datové sadě. Vzorec pro nalezení hodnot Y přejde do sloupce C a vypadá takto:
= $ F $ 6 * A (i) + $ F $ 7
Kde A (i) je hodnota pro sloupec A v řádku (i). Vzorce vypadají takto v tabulce:
= $ F $ 6 * A1 + $ F $ 7
= $ F $ 6 * A2 + $ F $ 7
= $ F $ 6 * A3 + $ F $ 7 a tak dále
Položky ve sloupci D ukazují rozdíly mezi očekávanými a skutečnými hodnotami pro Y. Formule vypadají takto:
= B (i) -C (i), Kde B (i) a C (i) jsou hodnoty v řádku (i) ve sloupcích B a C, resp.
Hledání zbytkové odchylky
The vzorec pro zbytkovou odchylku jde do buňky F9 a vypadá takto:
= SUMSQ (D1: D10) / (COUNT (D1: D10) -2)
Kde SUMSQ (D1: D10) je součet čtverců rozdílů mezi skutečnými a očekávanými hodnotami Y a (COUNT (D1: D10) -2) je počet datových bodů, mínus 2 pro stupně volnosti v data.
