Statisztikai elemzésben a variancia egy adatkészlet tagjai között megmutatja, hogy az adatpontok milyen távolságra vannak egy trendvonalból, más néven a regressziós vonal. Minél nagyobb a variancia, annál szélesebb az adatpontok. A varianciaanalízis vizsgálata azt mutatja, hogy a variancia mely részei magyarázhatók az adatok jellemzőivel, és amelyek a véletlen tényezőknek tulajdoníthatók. A variancia azon része, amely nem magyarázható, a maradék variancia.
Az Excel táblázatkezelők használata a fennmaradó variancia kiszámításához
A fennmaradó variancia kiszámítására szolgáló képlet számos összetett számítást tartalmaz. Kis adatállományok esetében a maradék variancia kézi kiszámítása folyamatos lehet. Nagy adatállományok esetén a feladat kimerítő lehet. Az Excel táblázatkezelő használatával csak az adatpontokat kell megadnia, és a megfelelő képletet kell kiválasztania. A program kezeli a komplex számításokat, és gyorsan eredményt ad.
Adat pontok
Nyisson meg egy új Excel-táblázatot, és írja be az adatpontokat két oszlopba. A regressziós vonalak megkövetelik, hogy minden adatpontnak két eleme legyen. A statisztikusok jellemzően ezeket az elemeket "X" és "Y" címkékkel jelölik. Például a Generic Insurance Co. szeretné megtalálni a munkavállalók magasságának és súlyának fennmaradó varianciáját. Az X változó a magasságot és az Y változót jelenti a súly. Adja meg a magasságokat az A oszlopba, és a súlyokat a B oszlopba.
Az átlag keresése
A átlagos az adategység minden elemének átlagát jelenti. Ebben a példában a Generic Insurance meg kívánja találni az átlagot, a szórást és a kovarianciát 10 alkalmazott magasságával és súlyával. Az A oszlopban felsorolt magasságok átlagát az "AVERAGE (A1: A10)" funkció beírásával lehet megtalálni az F1 cellába. A B oszlopban felsorolt súlyok átlagát úgy lehet megtalálni, hogy beírjuk a függvényt "= AVERAGE (B1: B10)" az F3 cellába.
A standard eltérés és a kovariancia keresése
A szórás méri, hogy az adatpontok milyen távolságra vannak elosztva az átlagtól. A kovariancia méri, hogy az adatpont két eleme változik együtt. A magasságok szórását úgy találjuk, hogy az "= STDEV (A1: A10)" függvényt beírjuk az F2 cellába. A súlyok standard deviációját úgy találjuk, hogy a függvény "= STDEV (B1: B10)" bemenetre kerül az F4 cellába. A magasságok és a súlyok közötti kovarianciát az "= COVAR (A1: A10; B1: B10)" funkció beírásával találjuk az F5 cellába.
A regressziós vonal megtalálása
A regressziós vonal egy olyan lineáris függvényt jelent, amely követi az adatpontok trendjét. A regressziós sor képlete így néz ki: Y = aX + b.
A felhasználó megtalálja az "a" és "b" értékeket az eszközök, a standard eltérések és a kovariancia számításai alapján. A "b" értéke azt a pontot jelenti, ahol a regressziós vonal elfogja az Y tengelyt. Az érték megtalálható a kovariancia felvételével és az X-értékek standard deviációjának négyzetével való megosztásával. Az Excel képlet az F6 cellába megy, és így néz ki: = F5 / F2 ^ 2.
Az "a" értéke a regressziós vonal lejtését jelenti. Az Excel képlet az F7 cellába megy, és így néz ki: = F3-F6 * F1.
A regressziós sor képletének megtekintéséhez adja meg ezt a karakterlánc-összefűzést az F8 cellába:
= CONCATENATE ("Y ="; ROUND (F6; 2); "X"; IF (SIGN (F7) = 1; "+"; "-"); ABS (ROUND (F7; 2)))
Számítsa ki az Y értékeket
A következő lépés az Y-értékek kiszámítását jelenti a megadott X-értékek regressziós sorában. Az Y értékek megtalálására szolgáló képlet a C oszlopba kerül, és így néz ki:
= $ F $ 6 * A (i) + $ F $ 7
Ahol A (i) az A oszlop oszlopának értéke az i. Sorban. A képletek így hasonlítanak a táblázatban:
= $ F $ 6 * A1 + $ F $ 7
= $ F $ 6 * A2 + $ F $ 7
= $ F $ 6 * A3 + $ F $ 7, és így tovább
A D oszlopban szereplő bejegyzések az Y várható és tényleges értékei közötti különbségeket mutatják. A képletek így néz ki:
= B (i) -C (i), Ahol a B (i) és a C (i) értékek a B és C oszlopokban az (i) sorban megadott értékek.
A maradék variancia megtalálása
A a fennmaradó variancia képlete belép az F9 cellába, és így néz ki:
= SUMSQ (D1: D10) / (COUNT (D1: D10) -2)
Ahol a SUMSQ (D1: D10) a tényleges és a várható Y-értékek közötti különbségek négyzetének összege, és (COUNT (D1: D10) -2) az adatpontok száma, mínusz 2 a szabadságfokok között adat.
