V štatistickej analýze odchýlka medzi členmi súboru údajov ukazuje, ako ďaleko od seba sú dátové body z trendovej čiary známej aj ako a regresná čiara, Čím väčšia je rozptyl, tým viac sú rozmiestnené dátové body. Štúdia analýzy rozdielov ukazuje, ktoré časti rozptylu možno vysvetliť charakteristikami údajov a ktoré možno pripísať náhodným faktorom. Časť rozptylu, ktorú nemožno vysvetliť, sa nazýva reziduálna odchýlka.
Použitie tabuliek programu Excel na výpočet zostatkovej odchýlky
Vzorec na výpočet reziduálnej odchýlky zahŕňa množstvo komplexných výpočtov. Pri malých dátových súboroch môže byť proces výpočtu rušivého rozptylu ručne nudný. Pri veľkých množinách údajov môže byť úloha vyčerpávajúca. Použitie tabuľky programu Excel, stačí zadať body údajov a vybrať správny vzorec. Program spracováva komplexné výpočty a prináša výsledky rýchlo.
Dátové body
Otvorte novú tabuľku programu Excel a zadajte dátové body do dvoch stĺpcov. Regresné riadky vyžadujú, aby každý dátový bod mal dva prvky. Štatistici obvykle označujú tieto prvky "X" a "Y." Napríklad Generic Insurance Co chce nájsť zostatkovú odchýlku výšky a váhy svojich zamestnancov. Premenná X predstavuje výšku a premenná Y predstavuje hmotnosť. Zadajte výšky do stĺpca A a hmotnosti do stĺpca B.
Hľadanie prostriedku
znamenať predstavuje priemer pre každý prvok v súbore údajov. V tomto príklade chce Generic Insurance nájsť priemernú, štandardnú odchýlku a koeficienty výšok a závaží 10 zamestnancov. Priemer výšok uvedených v stĺpci A možno nájsť zadaním funkcie "= AVERAGE (A1: A10)" do bunky F1. Priemer váh uvedených v stĺpci B možno nájsť zadaním funkcie "= AVERAGE (B1: B10)" do bunky F3.
Hľadanie štandardnej odchýlky a
štandardná odchýlka meria vzdialenosť od dátových bodov od priemeru. kovariancie meria, koľko sa zmení spoločne dva prvky dátového bodu. Štandardná odchýlka výšky sa nájde zadaním funkcie "= STDEV (A1: A10)" do bunky F2. Štandardná odchýlka váh sa nájde zadaním funkcie "= STDEV (B1: B10)" do bunky F4. Kovariancia medzi výškami a hmotnosťami sa zistí vložením funkcie "= COVAR (A1: A10; B1: B10)" do bunky F5.
Vyhľadanie regresnej čiary
regresná čiara predstavuje lineárnu funkciu, ktorá sleduje trend dátových bodov. Vzorec pre regresnú čiaru vyzerá takto: Y = aX + b.
Užívateľ môže nájsť hodnoty pre "a" a "b" pomocou výpočtov pre prostriedky, štandardné odchýlky a kovariantnosť. Hodnota pre "b" predstavuje bod, v ktorom regresná čiara zachytáva os Y. Hodnotu možno nájsť tak, že použijeme kovarianciu a delíme ju štvorcom štandardnej odchýlky hodnôt X. Vzorec programu Excel prechádza do bunky F6 a vyzerá takto: = F5 / F2 ^ 2.
Hodnota pre "a" predstavuje sklon regresnej čiary. Vzorec programu Excel prechádza do bunky F7 a vyzerá takto: = F3-F6 * F1.
Ak chcete vidieť vzorec pre regresnú čiaru, zadajte reťazec reťazec do bunky F8:
= KOKATENÁT ("Y ="; KRUH (F6; 2); "X"; IF (SIGN (F7) = 1; "+";
Vypočítajte hodnoty Y
Ďalším krokom je výpočet hodnôt Y na regresnej línii pre dané hodnoty X v súbore údajov. Vzorec na nájdenie hodnôt Y prechádza do stĺpca C a vyzerá takto:
= $ F $ 6 * A (i) + $ F $ 7
Ak A (i) je hodnota pre stĺpec A v riadku (i). Vzorce vyzerajú takto v tabuľke:
= $ F $ 6 * A1 + $ F $ 7
= $ F $ 6 * A2 + $ F $ 7
= $ F $ 6 * A3 + $ F $ 7 a tak ďalej
Položky v stĺpci D zobrazujú rozdiely medzi očakávanými a skutočnými hodnotami pre Y. Vzorce vyzerajú takto:
= B (i) -C (i), Kde B (i) a C (i) sú hodnoty v riadku (i) v stĺpcoch B a C.
Hľadanie zostatkovej odchýlky
vzorec pre zostatkovú odchýlku ide do bunky F9 a vyzerá takto:
= SUMSQ (D1: D10) / (COUNT (D1: D10) -2)
Kde SUMSQ (D1: D10) je súčet štvorcov rozdielov medzi skutočnými a očakávanými hodnotami Y a (COUNT (D1: D10) -2) je počet dátových bodov, mínus 2 pre stupne voľnosti v dát.
