VAIHTOEHTO - Excel ja Google Sheets

Tämä opetusohjelma osoittaa, miten Excel VARIANCE -toiminto Excelissä varianssin arvioimiseksi tietyn näytteen perusteella.

VARIANCE -toiminnon yleiskatsaus

VARIANCE -toiminto Laskee estimaatin varianssin annetun otoksen perusteella.

Jos haluat käyttää VARIANCE Excel -laskentataulukkotoimintoa, valitse solu ja kirjoita:

(Huomaa, miten kaavasyötteet näkyvät)

VARIANCE -toiminto Syntaksi ja tulot:

1	= VAR (luku1, [luku2],…)

numeroita- Arvot varianssin saamiseksi

Varianssin laskeminen Excelissä

Varianssi kertoo, kuinka hajautetut arvot tietojoukossa ovat keskiarvosta. Matemaattisesti ottaen dispersio on kunkin pistemäärän neliöeron keskiarvo (mutta tulemme siihen pian).

Excel antaa sinulle useita toimintoja varianssin laskemiseksi - VAR.S, VAR.P, VARA, VARPA ja kaksi vanhempaa funktiota, VAR ja VARP.

Ennen kuin perehdymme näihin toimintoihin ja opimme käyttämään niitä, puhutaan varianssista ja siitä, miten se lasketaan.

Mikä on varianssi?

Tietoja analysoitaessa yleinen ensimmäinen askel on laskea keskiarvo. Tämä on tietysti hyödyllinen tilasto laskettavaksi, mutta se ei anna sinulle täydellistä kuvaa siitä, mitä tiedoillesi tapahtuu.

Ota seuraava tietojoukko, joka voi olla ryhmä testituloksia 100: sta:

1	48,49,50,51,52

Tämän alueen keskiarvo on 50 (summaa luvut ja jaa n: llä, missä n on arvojen lukumäärä).

Ota seuraavaksi seuraavat testitulokset:

1	10,25,50,75,90

Tämän alueen keskiarvo on myös 50 - mutta ilmeisesti meillä on täällä kaksi hyvin erilaista tietoaluetta.

Keskiarvo ei itsessään voi kertoa sinulle mitään siitä, kuinka pisteet ovat hajallaan. Se ei kerro sinulle, ovatko kaikki arvot nipussa kuten ensimmäisissä esimerkeissä vai kaukana toisistaan. Varianssi voi auttaa sinua oppimaan tämän.

Varianssia käytetään myös peruspisteenä monille monimutkaisemmille tilastollisille menettelyille.

Varianssin laskeminen

Käydään läpi perusesimerkki ja lasketaan varianssit käsin. Näin tiedät, mitä kulissien takana tapahtuu, kun todella aloitat Excelin varianssitoimintojen toteuttamisen.

Oletetaan, että meillä on tietojoukko, joka edustaa kolmea pelikorttia, 4, 6 ja 8.

Voit laskea varianssin seuraavasti:

1) Laske keskiarvo

Ensin laskemme keskiarvon. Tiedämme, että tietoalueemme on 4, 6, 8, joten keskiarvo on seuraava:

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

Olen vahvistanut tämän alla Excel AVERAGE -toiminnolla <>:

1	= KESKIMÄÄRÄINEN (C4: C6)

2) Vähennä keskiarvo jokaisesta tietojoukon arvosta

Seuraavaksi vähennämme keskiarvon jokaisesta arvostamme.

Olen tehnyt tämän seuraavan kaavan avulla:

1	= C4- $ H $ 4

Keskiarvo on tallennettu H4: ään, joten vähennän sen vain jokaisesta taulukon arvosta. Dollarimerkit vain "lukitsevat" sen soluviittauksen H4: ään, niin että kun kopioin sen sarakkeeseen, se pysyy samana.

Tulokset:

Meillä on:

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

Meidän on saatava näiden erojen keskiarvo keskiarvosta, mutta näiden kolmen arvon keskiarvo on nolla! Meidän on siis korostettava eroja, jotka teemme neliöimällä ne.

3) Neliö eroja

Lisätään uusi sarake ja neliöidään numerot sarakkeessa D:

1

= D4*D4

Okei, se on parempi. Nyt kun erot eivät ole keskimäärin nollassa, voimme laskea varianssin.

4) Laske neliöerojen keskiarvo

Täällä tapaamme tienhaarukan. Varianssin voi laskea kahdella tavalla, ja käyttämäsi tapa riippuu käytettävistä tiedoista.

• Jos käytät väestötiedot, otat vain keskiarvon normaalina (summaa arvot ja jaa n: llä)
• Jos käytät näytetiedot, summaat arvot ja jaat n-1: llä

Väestötiedot tarkoittavat, että sinulla on kaikki tarvitsemasi tiedot, esimerkiksi jos haluat tietyn koulun opettajien keski -iän ja sinulla on jokaisen kyseisen opettajan ikädata, sinulla on väestötietoja.

Esimerkkidata tarkoittaa, että sinulla ei ole kaikkia tietojasi, vain näyte suuremmalta populaatiolta. Joten jos haluat opettajien keski -iän koko maassa ja sinulla on tietoja vain yhden koulun opettajista, sinulla on otostietoja.

Esimerkissämme meillä on väestötietoja. Olemme kiinnostuneita vain kolmesta kortistamme - tämä on väestö, emmekä ole ottaneet niistä näytettä. Joten voimme vain ottaa neliöerojen keskiarvon normaalilla tavalla:

1	= KESKIMÄÄRÄINEN (E4: E8)

Joten väestömme dispersio on 2,666.

Jos tämä oli näytetietoja (ehkä olisimme vetäneet nämä kolme korttia suuremmasta joukosta), laskemme keskiarvon seuraavasti:

1	Näytteen varianssi = (4 + 0 + 4) / (3 - 1)

Tai:

1	Näytteen varianssi = 8 /2 = 4

Miksi jakaa n-1: llä näytetiedoilla vain n: n sijaan?

Lyhyt vastaus tähän kysymykseen on "Koska se antaa oikean vastauksen". Mutta luulen, että haluat vähän enemmän! Tämä on monimutkainen aihe, joten annan vain lyhyen yleiskatsauksen täällä.

Ajattele asiaa näin: jos otat otoksen populaatiosta, nämä arvot ovat yleensä lähempänä keskiarvoa näyte kuin ne ovat väestö.

Tämä tarkoittaa sitä, että jos jaat vain n: llä, aliarvioit väestön vaihtelua hieman. Jakaminen n-1: llä korjaa tätä hieman.

Kolmen korttimme ansiosta meillä on hyvä paikka testata tätä teoriaa. Koska kortteja on vain kolme, voimme ottaa pienen määrän näytteitä.

Otetaan näytteet kahdesta kortista. Valitsemme yhden kortin, asetamme sen takaisin, sekoitamme ja valitsemme sitten toisen kortin. Tämä tarkoittaa, että voimme valita yhdeksän yhdistelmää kahdesta kortista.

Vain yhdeksän mahdollisen näytteen avulla voimme laskea kaikki mahdolliset näytteen varianssit molemmilla menetelmillä (jakaa n: llä ja jaa n-1: llä), ottaa niiden keskiarvon ja katsoa, ​​mikä antaa meille oikean vastauksen.

Alla olevassa taulukossa olen esittänyt kaiken. Taulukon jokainen rivi on erilainen näyte, ja sarakkeet B ja C näyttävät kustakin näytteestä valitut kaksi korttia. Sitten olen lisännyt vielä kaksi saraketta: yhden, jossa laskin kahden kortin otoksen varianssin jakamalla n: llä, ja toisen, jossa jaoin n - 1: llä.

Katso:

Taulukon oikealla puolella olen näyttänyt sarakkeiden D ja E keskiarvot.

Sarakkeen D keskiarvo, kun se jaetaan n: llä, antaa meille varianssin 1,333.

Sarakkeen E keskiarvo, kun se jaetaan n-1: llä, antaa meille varianssin 2,666.

Tiedämme jo aiemmasta esimerkistämme, että populaation variansi on 2,666. Joten jakaminen n-1: llä otantatietoja käytettäessä antaa meille tarkempia arvioita.

Excel -toiminnot varianssin laskemiseksi

Nyt kun olet nähnyt esimerkin varianssin laskemisesta, siirrytään Excel -toimintoihin.

Tässä on useita vaihtoehtoja:

• P palauttaa populaatiotietojen varianssin (jako n -menetelmällä)
• S palauttaa näytetietojen varianssin (jakaa n-1: llä)
• VAR on vanhempi toiminto, joka toimii täsmälleen samalla tavalla kuin VAR.S
• VARA on sama kuin VAR.S, paitsi että se sisältää tekstisoluja ja Boolen arvoja
• VARPA on sama kuin VAR.P, paitsi että se sisältää tekstisoluja ja Boolen arvoja

Käydään nämä läpi yksi kerrallaan.

Excelin VAR.P -toiminto

VAR.P laskee populaatiotietojen varianssin (jako n -menetelmällä). Käytä sitä näin:

1	= VAR.P (C4: C6)

Määrität vain yhden argumentin VAR.P: ssä: tietoalueen, jonka varianssin haluat laskea. Tässä tapauksessa tässä ovat kortin arvot C4: C6: ssa.

Kuten yllä näet, VAR.P palauttaa 2,666 kolmen kortin sarjallemme. Tämä on sama arvo, jonka laskimme aiemmin käsin.

Huomaa, että VAR.P jättää kokonaan huomiotta solut, jotka sisältävät tekstiä tai Boolen (TRUE/FALSE) -arvoja. Jos haluat sisällyttää nämä, käytä sen sijaan VARPAa.

Excel VAR.S -toiminto

VAR.S laskee näytetietojen varianssin (jakamalla n-1). Käytät sitä näin:

1	= VAR.S (C4: C6)

Jälleen on vain yksi argumentti - tietoalueesi.

Tässä tapauksessa VAR.S palauttaa 4. Saimme saman luvun vaiheessa 4, kun teimme manuaalisen laskelman.

VAR.S jättää kokonaan huomiotta solut, jotka sisältävät tekstiä tai Boolen (TRUE/FALSE) -arvoja. Jos haluat sisällyttää nämä, käytä VARA: ta.

Excelin VAR -toiminto

VAR on täysin vastaava kuin VAR.S: se laskee näytetietojen varianssit (käyttäen n-1-menetelmää). Käytä sitä seuraavasti:

1	= VAR (C4: C6)

VAR on "yhteensopivuustoiminto". Tämä tarkoittaa, että Microsoft on poistamassa tätä toimintoa Excelistä. Tällä hetkellä se on edelleen käytettävissä, mutta sinun on käytettävä sen sijaan VAR.S: ää, jotta laskentataulukot pysyvät yhteensopivina tulevien Excel -versioiden kanssa.

Excelin VARA -toiminto

VARA palauttaa myös näytetietojen varianssin, mutta sillä on joitain keskeisiä eroja VAR- ja VAR.S. Nimittäin se sisältää laskennassaan Boolen ja tekstiarvot:

• TOSI -arvot lasketaan yhdeksi
• FALSE -arvot lasketaan 0: ksi
• Tekstimerkkijonot lasketaan 0: ksi

Näin käytät sitä:

1	= VARA (C4: C11)

Olemme lisänneet taulukkoon vielä viisi riviä: J, Q, K, TRUE ja FALSE. Sarake D osoittaa, miten VARA tulkitsee nämä arvot.

Koska taulukossamme on nyt uusi erä alhaisia ​​arvoja, dispersio on noussut 10,268: een.

Excelin VARPA -toiminto

VARPA laskee populaatiotietojen varianssin. Se on samanlainen kuin VAR.P, paitsi että se sisältää myös Boolen arvot ja tekstimerkkijonot laskennassa:

• TOSI -arvot lasketaan yhdeksi
• FALSE -arvot lasketaan 0: ksi
• Tekstimerkkijonot lasketaan 0: ksi

Käytät sitä näin:

1	= VARPA (C4: C12)

Olemme lisänneet taulukkoon vielä viisi riviä: J, Q, K, TRUE ja FALSE. Sarake D osoittaa, miten VARPA tulkitsee nämä arvot.

Tämän alempien arvojen ryhmän lisäämisen tuloksena dispersio on noussut 8,984: een.

VARIANCE -toiminto Google Sheetsissä

CORREL -toiminto toimii täsmälleen samalla tavalla Google Sheetsissä kuin Excelissä: