VARIANCE - Excel és Google Táblázatok

Ez a bemutató bemutatja, hogyan kell használni Excel VARIANCE függvény az Excelben, hogy megbecsülje a szórást egy adott minta alapján.

A VARIANCE funkció áttekintése

A VARIANCE függvény Kiszámítja a becsült varianciát egy adott minta alapján.

A VARIANCE Excel munkalap funkció használatához válasszon ki egy cellát és írja be:

(Figyelje meg, hogyan jelennek meg a képletbemenetek)

VARIANCE funkció Szintaxis és bemenetek:

1	= VAR (szám1, [szám2],…)

számokat- Értékek a szóráshoz

Hogyan lehet kiszámítani az eltérést az Excelben?

A szórás megmutatja, hogy az adathalmaz értékei mennyire oszlanak el az átlagtól. Matematikailag a szórás az egyes pontszámok átlagban mért négyzeteltérésének átlaga (de rövidesen erre is eljutunk).

Az Excel számos függvényt kínál a szórás kiszámításához - VAR.S, VAR.P, VARA, VARPA, és két régebbi függvény, a VAR és a VARP.

Mielőtt beleásnánk magunkat ezekbe a funkciókba, és megtanulnánk használni őket, beszéljünk a szórásról és annak kiszámításáról.

Mi az eltérés?

Az adatok elemzésekor gyakori első lépés az átlag kiszámítása. Ez természetesen hasznos statisztika a számításhoz, de nem ad teljes képet arról, hogy mi történik az adataival.

Vegye ki a következő adatkészletet, amely a 100 -ból kapott teszteredmények csoportja lehet:

1	48,49,50,51,52

Ennek a tartománynak az átlaga 50 (összegezzük a számokat és osztjuk el n -el, ahol n az értékek száma).

Ezután vegye be a következő vizsgálati eredményeket:

1	10,25,50,75,90

Ennek a tartománynak az átlaga az is 50 - de nyilvánvalóan két nagyon eltérő adattartomány van itt.

Az átlag önmagában nem mondhat semmit arról, hogy a pontszámok mennyire eloszlanak. Nem árulja el, hogy az értékek mindegyike csomóba van -e állítva, mint az első példákban, vagy egymástól távol, mint a második. A szórás segíthet ennek megtanulásában.

A varianciát bonyolultabb statisztikai eljárások bázisaként is használják.

A szórás kiszámítása

Feldolgozzunk egy alapvető példát, és kézzel számoljuk ki az eltérést. Így tudni fogja, mi történik a kulisszák mögött, amikor ténylegesen elkezdi működtetni az Excel varianciafunkcióit.

Tegyük fel, hogy van egy adathalmazunk, amely három kártyát, egy 4 -es, 6 -os és 8 -as kártyát jelent.

A szórás kiszámításához ezt a folyamatot kell elvégeznie:

1) Számítsa ki az átlagot

Először kiszámítjuk az átlagot. Tudjuk, hogy az adattartományunk 4, 6, 8, így az átlag a következő lesz:

1	(6 + 4 + 8) / 3 = 6

Ezt az alábbiakban megerősítettem az Excel ÁTLAG függvénnyel <>:

1	= ÁTLAG (C4: C6)

2) Vonja le az átlagot az adatkészlet minden értékéből

Ezután minden értékünkből kivonjuk az átlagot.

Ezt a következő képlettel csináltam:

1	= C4- $ H $ 4

Az átlagot a H4 tárolja, ezért ezt csak kivonom a táblázat minden értékéből. A dollárjelek itt csak „zárolják” azt a cellahivatkozást a H4 -re, hogy amikor lemásolom az oszlopba, az ugyanaz maradjon.

Az eredmények:

Megvan:

123	4 - 6 = -26 - 6 = 08 - 6 = 2

E különbségek átlagát az átlagból kell lekérnünk, de e három érték átlaga nulla! Tehát hangsúlyoznunk kell a különbségeket, amelyeket négyzetekkel teszünk.

3) Nézd meg a különbségeket!

Adjunk hozzá egy új oszlopot, és négyzeteljük a számokat a D oszlopban:

1

= D4*D4

Oké, ez jobb. Most, hogy a különbségek nem nulla, átlagosan kiszámíthatjuk a szórást.

4) Számítsa ki a négyzeteltérések átlagát

Itt egy útelágazással találkozunk. Kétféleképpen lehet kiszámítani a szórást, és a használt módszer a rendelkezésre álló adatok típusától függ.

• Ha használja lakossági adatok, egyszerűen vegye az átlagot normálnak (összegezze az értékeket, és ossza el n -el)
• Ha használja minta adat, összeadja az értékeket, és elosztja n-1-gyel

A népességi adatok azt jelentik, hogy a szükséges adatok összessége megvan, például ha egy adott iskola tanárainak átlagéletkorát szeretné megadni, és az adott iskola minden egyes tanárának életkori adatai megvannak, akkor lakossági adatai vannak.

A mintaadatok azt jelentik, hogy nincsenek meg az összes adatod, csak egy nagyobb populációból vett minta. Tehát ha az egész ország tanárainak átlagéletkorát szeretné, és csak egy iskola tanárairól van adatai, akkor mintaadatok vannak.

Példánkban népességi adatokkal rendelkezünk. Csak a három lapunk érdekel minket - ez a lakosság, és nem vettünk mintát belőlük. Tehát a szokásos módon vehetjük a négyzetes különbségek átlagát:

1	= ÁTLAG (E4: E8)

Tehát népességünk szórása 2,666.

Ha ez volt mintadatokat (talán ezt a három kártyát húztuk ki egy nagyobb készletből), az átlagot a következőképpen dolgozzuk ki:

1	Minta variancia = (4 + 0 + 4) / (3 - 1)

Vagy:

1	Minta szórás = 8 /2 = 4

Miért kell osztani n-1-vel mintaadatokkal, csak n helyett?

A rövid válasz erre a kérdésre: „Mert a helyes választ adja”. De azt hiszem, ennél kicsit többet akarsz! Ez egy összetett téma, ezért csak egy rövid áttekintést adok itt.

Gondoljon erre: ha mintát vesz egy populációból, akkor ezek az értékek általában közelebb állnak a minta mint azok átlagosan népesség.

Ez azt jelenti, hogy ha csak osztunk n -vel, akkor egy kicsit alábecsüljük a népesség szórását. Az n-1-el való osztás ezt kissé korrigálja.

A három kártyás készletünkkel jó helyen vagyunk, hogy kipróbáljuk ezt az elméletet. Mivel csak három kártya van, kevés mintát vehetünk.

Vegyünk mintát két kártyából. Kiválasztunk egy kártyát, visszatesszük, megkeverjük, majd kiválasztunk egy másik kártyát. Ez azt jelenti, hogy két kártya kilenc kombinációja közül választhatunk.

Mindössze kilenc lehetséges mintával minden lehetséges minta varianciát kiszámíthatunk mindkét módszerrel (osztjuk n-el, és osztjuk n-1-el), vegyük átlagukat, és nézzük meg, melyik adja meg a helyes választ.

Az alábbi táblázatban mindent leírtam. A táblázat minden sora más -más minta, a B és C oszlopban pedig az egyes mintákban kiválasztott két kártya látható. Ezután hozzáadtam még két oszlopot: az egyik, ahol kiszámítottam a két kártya mintájának varianciáját n -el osztva, a másik pedig n -1 -el osztva.

Nézd meg:

A táblázat jobb oldalán a D és E oszlopok átlagát mutattam.

A D oszlop átlaga, ha n -el osztjuk, 1,333 szórást ad.

Az E oszlop átlaga, ha n-1-el osztjuk, 2,666 szórást ad.

Korábbi példánkból már tudjuk, hogy a populáció szórása 2,666. Tehát a mintadatok használatakor n-1-el való osztás pontosabb becsléseket eredményez.

Az Excel függvények a variancia kiszámításához

Most, hogy látott egy példát a szórás kiszámítására, térjünk át az Excel függvényekre.

Itt több lehetőség közül választhat:

• P a populációs adatok varianciáját adja vissza (az osztás n módszerrel)
• S a mintaadatok varianciáját adja vissza (oszt n-1-el)
• VAR egy régebbi funkció, amely pontosan ugyanúgy működik, mint a VAR.S
• VARA ugyanaz, mint a VAR.S, csak szöveges cellákat és logikai értékeket tartalmaz
• VARPA ugyanaz, mint a VAR.P, csak szöveges cellákat és logikai értékeket tartalmaz

Nézzük végig ezeket egyesével.

Az Excel VAR.P függvénye

A VAR.P kiszámítja a populációs adatok varianciáját (az osztás n módszerrel). Használja így:

1	= VAR.P (C4: C6)

A VAR.P -ben csak egy argumentumot határozhat meg: azt az adattartományt, amelynek a varianciáját ki szeretné számítani. Esetünkben ezek a C4: C6 kártya értékei.

Amint a fentiekben látható, a VAR.P három kártya készletünk esetén 2.666 -ot ad vissza. Ez ugyanaz az érték, amelyet korábban kézzel számoltunk.

Vegye figyelembe, hogy a VAR.P teljesen figyelmen kívül hagyja a szöveget vagy logikai (IGAZ/HAMIS) értékeket tartalmazó cellákat. Ha ezeket fel kell vennie, használja inkább a VARPA -t.

Az Excel VAR.S függvénye

A VAR.S kiszámítja a mintaadatok varianciáját (osztva n-1-el). Így használod:

1	= VAR.S (C4: C6)

Ismét csak egy érv van - az adattartomány.

Ebben az esetben a VAR.S 4. értéket ad vissza. Ugyanezt az értéket kaptuk a 4. lépésben, amikor a fenti manuális számítást végeztük.

A VAR.S teljesen figyelmen kívül hagyja a szöveget vagy logikai (IGAZ/HAMIS) értékeket tartalmazó cellákat. Ha ezeket fel kell vennie, használja inkább a VARA -t.

Az Excel VAR függvénye

A VAR teljesen egyenértékű a VAR.S-szal: kiszámítja a mintaadatok szórásait (az n-1 módszerrel). Használata a következő:

1	= VAR (C4: C6)

A VAR egy „kompatibilitási funkció”. Ez azt jelenti, hogy a Microsoft éppen eltávolítja ezt a funkciót az Excelből. Jelenleg még mindig használható, de a VAR.S -t kell használnia, hogy a táblázatok továbbra is kompatibilisek legyenek az Excel későbbi verzióival.

Az Excel VARA függvénye

A VARA a mintaadatok varianciáját is visszaadja, de van néhány lényeges különbség a VAR és a VAR.S. Nevezetesen logikai és szöveges értékeket tartalmaz a számításban:

• Az IGAZ értékek 1 -nek számítanak
• A HAMIS értékek 0 -nak számítanak
• A szöveg karakterláncok 0 -nak számítanak

Használja a következőképpen:

1	= VARA (C4: C11)

További öt sorral bővítettük a táblázatot: J, Q, K, TRUE és FALSE. A D oszlop azt mutatja, hogy a VARA hogyan értelmezi ezeket az értékeket.

Mivel új alacsony értékű tétel van a táblázatunkban, a szórás 10,268 -ra nőtt.

Az Excel VARPA funkciója

A VARPA kiszámítja a populációs adatok szórását. Hasonló a VAR.P -hez, azzal a kivétellel, hogy logikai értékeket és szöveges karakterláncokat is tartalmaz a számításban:

• Az IGAZ értékek 1 -nek számítanak
• A HAMIS értékek 0 -nak számítanak
• A szöveg karakterláncok 0 -nak számítanak

Így használod:

1	= VARPA (C4: C12)

További öt sorral bővítettük a táblázatot: J, Q, K, TRUE és FALSE. A D oszlop azt mutatja, hogy a VARPA hogyan értelmezi ezeket az értékeket.

Ennek az alacsonyabb értékek csoportnak az adatokhoz való hozzáadása következtében a szórás 8,984 -re nőtt.

VARIANCE funkció a Google Táblázatokban

A CORREL funkció pontosan ugyanúgy működik a Google Táblázatokban, mint az Excelben: