VARIANȚĂ - Foi de calcul Excel și Google

Acest tutorial arată cum să utilizați fișierul Funcția VARIANȚĂ Excel în Excel pentru a estima varianța pe baza unui eșantion dat.

VARIANȚĂ Prezentare generală a funcției

Funcția VARIANCE Calculează varianța estimată pe baza unui eșantion dat.

Pentru a utiliza funcția Foaie de lucru VARIANCE Excel, selectați o celulă și tastați:

(Observați cum apar datele introduse de formulă)

Funcția VARIANCE Sintaxă și intrări:

1	= VAR (număr1, [număr2], …)

numere- Valorile pentru a obține Varianța

Cum se calculează varianța în Excel

Varianța vă arată cât de răspândite sunt valorile dintr-un set de date din medie. Din punct de vedere matematic, varianța este media diferenței pătrate a fiecărui scor din medie (dar vom ajunge la asta în scurt timp).

Excel vă oferă o serie de funcții pentru a calcula varianța - VAR.S, VAR.P, VARA, VARPA și două funcții mai vechi, VAR și VARP.

Înainte să analizăm aceste funcții și să învățăm cum să le folosim, să vorbim despre varianță și despre modul în care este calculată.

Ce este varianța?

La analizarea datelor, un prim pas obișnuit este calcularea mediei. Aceasta este, desigur, o statistică utilă de calculat, dar nu vă oferă o imagine completă a ceea ce se întâmplă cu datele dvs.

Luați următorul set de date, care ar putea fi un grup de rezultate ale testelor obținute din 100:

1	48,49,50,51,52

Media acestui interval este 50 (suma numerelor și împărțirea la n, unde n este numărul de valori).

Apoi, luați următorul set de rezultate ale testului:

1	10,25,50,75,90

Media acestui interval este de asemenea 50 - dar, evident, avem aici două game de date foarte diferite.

În sine, media nu vă poate spune nimic despre cât de repartizate sunt scorurile. Nu vă spune dacă valorile sunt toate grupate, cum ar fi în primele exemple, sau extinse ca în al doilea. Varianța vă poate ajuta să învățați acest lucru.

Varianța este, de asemenea, utilizată ca punct de bază pentru o serie de proceduri statistice mai complexe.

Cum este calculată varianța

Să analizăm un exemplu de bază și să calculăm varianța manual. În acest fel, veți ști ce se întâmplă în culise atunci când începeți efectiv să puneți în funcțiune funcțiile de varianță Excel.

Să presupunem că avem un set de date care reprezintă trei cărți de joc, 4, 6 și 8.

Pentru a calcula varianța, lucrați prin acest proces:

1) Calculați media

În primul rând, calculăm media. Știm că intervalul nostru de date este 4, 6, 8, deci media va fi:

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

Am confirmat acest lucru mai jos cu funcția Excel AVERAGE <>:

1	= MEDIE (C4: C6)

2) Scădeți media din fiecare valoare din setul de date

Apoi, scădem media din fiecare dintre valorile noastre.

Am făcut acest lucru cu următoarea formulă:

1	= C4- $ H $ 4

Media este stocată în H4, așa că o scad din fiecare valoare din tabel. Semnele de dolar aici „blochează” acea referință de celulă la H4, astfel încât atunci când o copiez în coloană, rămâne aceeași.

Rezultatele:

Noi avem:

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

Trebuie să obținem media acestor diferențe față de medie, dar media acestor trei valori este zero! Deci, trebuie să subliniem diferențele, pe care le facem prin pătrarea lor.

3) Păstrați diferențele

Să adăugăm o nouă coloană și să pătratem numerele din coloana D:

1

= D4 * D4

Bine, este mai bine. Acum, că diferențele nu ajung la zero, putem calcula varianța.

4) Calculați media diferențelor pătrate

Aici întâlnim o bifurcație în drum. Există două moduri de a calcula varianța, iar cea pe care o utilizați depinde de tipul de date pe care îl aveți.

• Dacă utilizați date despre populație, pur și simplu luați media ca normal (însumați valorile și împărțiți la n)
• Dacă utilizați date eșantion, însumați valorile și împărțiți la n-1

Datele despre populație înseamnă că aveți totalitatea datelor de care aveți nevoie, de exemplu, dacă doriți vârsta medie a profesorilor într-o anumită școală și aveți datele de vârstă pentru fiecare profesor din acea școală, aveți date despre populație.

Exemple de date înseamnă că nu aveți toate datele, ci doar un eșantion preluat de la o populație mai mare. Deci, dacă doriți vârsta medie a profesorilor din întreaga țară și aveți doar date despre profesori la o școală, aveți date eșantion.

În exemplul nostru, avem date despre populație. Ne interesează doar cele trei cărți - aceasta este populația și nu am luat un eșantion de la ele. Deci, putem lua media diferențelor pătrate în mod normal:

1	= MEDIE (E4: E8)

Deci, varianța populației noastre este de 2.666.

Dacă aceasta a fost eșantion de date (probabil am fi scos aceste trei cărți dintr-un set mai mare), am fi calculat media după cum urmează:

1	Varianța eșantionului = (4 + 0 + 4) / (3 - 1)

Sau:

1	Varianța eșantionului = 8/2 = 4

De ce să împărțiți prin n-1 cu date eșantion, în loc de doar n?

Răspunsul scurt la această întrebare este „Pentru că oferă răspunsul corect”. Dar îmi imaginez că veți dori ceva mai mult decât atât! Acesta este un subiect complex, așa că voi face o scurtă prezentare aici.

Gândiți-vă astfel: dacă luați un eșantion de date dintr-o populație, valorile respective vor tinde să fie mai aproape de media probă decât sunt la mijlocul populației.

Aceasta înseamnă că, dacă împărțiți doar la n, veți subestima puțin varianța populației. Împărțirea la n-1 corectează puțin acest lucru.

Cu setul nostru de trei cărți, suntem într-un loc bun pentru a testa această teorie. Deoarece există doar trei cărți, există un număr mic de probe pe care le putem lua.

Să luăm mostre de două cărți. Vom alege o carte, o vom pune înapoi, vom amesteca și apoi vom alege o altă carte. Asta înseamnă că putem alege nouă combinații de două cărți.

Cu doar nouă eșantioane posibile, putem calcula fiecare varianță posibilă a eșantionului utilizând ambele metode (împărțiți la n și împărțiți la n-1), luați media acestora și vedeți care dintre acestea ne oferă răspunsul corect.

În tabelul de mai jos, am prezentat totul. Fiecare rând al tabelului este un eșantion diferit, iar coloanele B și C arată cele două cărți care au fost culese în fiecare eșantion. Apoi am adăugat încă două coloane: una în care am calculat varianța acelui eșantion de două cărți împărțind la n, iar alta în care am împărțit la n - 1.

Aruncați o privire:

În dreapta tabelului, am arătat mediile coloanelor D și E.

Media coloanei D, atunci când se împarte la n, ne oferă o varianță de 1.333.

Media coloanei E, atunci când se împarte la n-1, ne oferă o varianță de 2.666.

Știm deja din exemplul nostru anterior că varianța populației este de 2.666. Deci, împărțirea la n-1 atunci când se utilizează date eșantion ne oferă estimări mai precise.

Funcțiile Excel pentru a calcula variația

Acum, că ați văzut un exemplu de calcul al varianței, să trecem la funcțiile Excel.

Aveți mai multe opțiuni aici:

• P returnează varianța pentru datele populației (folosind metoda împărțiți prin n)
• S returnează varianța pentru datele eșantionului (se împarte la n-1)
• VAR este o funcție mai veche care funcționează exact la fel ca VAR.S
• VARA este același cu VAR.S, cu excepția faptului că include celule de text și valori booleene
• VARPA este la fel ca VAR.P, cu excepția faptului că include celule text și valori booleene

Haideți să le parcurgem una câte una.

Funcția Excel VAR.P

VAR.P calculează varianța pentru datele populației (folosind metoda împărțirii la n). Folosiți-l astfel:

1	= VAR.P (C4: C6)

Definiți un singur argument în VAR.P: intervalul de date pentru care doriți să calculați varianța. În cazul nostru aici, acestea sunt valorile cardului în C4: C6.

După cum vedeți mai sus, VAR.P returnează 2.666 pentru setul nostru de trei cărți. Aceasta este aceeași valoare pe care am calculat-o manual mai devreme.

Rețineți că VAR.P ignoră complet celulele care conțin text sau valori booleene (TRUE / FALSE). Dacă trebuie să le includeți, utilizați în schimb VARPA.

Funcția Excel VAR.S

VAR.S calculează varianța pentru datele eșantionului (împărțind la n-1). Îl folosești astfel:

1	= VAR.S (C4: C6)

Din nou, există un singur argument - intervalul dvs. de date.

În acest caz, VAR.S returnează 4. Am obținut aceeași cifră la pasul 4 când am făcut calculul manual de mai sus.

VAR.S ignoră complet celulele care conțin text sau valori booleene (TRUE / FALSE). Dacă trebuie să le includeți, utilizați în schimb VARA.

Funcția Excel VAR

VAR este complet echivalent cu VAR.S: calculează varianțele pentru datele eșantionului (folosind metoda n-1). Iată cum să îl utilizați:

1	= VAR (C4: C6)

VAR este o „funcție de compatibilitate”. Aceasta înseamnă că Microsoft este în curs de eliminare a acestei funcții din Excel. În acest moment este încă disponibil pentru utilizare, dar ar trebui să utilizați VAR.S în schimb, astfel încât foile dvs. de calcul să rămână compatibile cu viitoarele versiuni de Excel.

Funcția Excel VARA

VARA returnează, de asemenea, varianța datelor eșantion, dar are unele diferențe cheie față de VAR și VAR.S. Și anume, include valori booleene și text în calculul său:

• Valorile TRUE sunt numărate ca 1
• Valorile FALSE sunt numărate ca 0
• Șirurile de text sunt numărate ca 0

Iată cum îl utilizați:

1	= VARA (C4: C11)

Am adăugat încă cinci rânduri în tabel: J, Q, K, TRUE și FALSE. Coloana D arată modul în care VARA interpretează aceste valori.

Deoarece acum avem un nou lot de valori scăzute în tabelul nostru, varianța a crescut la 10.268.

Funcția Excel VARPA

VARPA calculează varianța pentru datele populației. Este similar cu VAR.P, cu excepția faptului că include și valori booleene și șiruri de text în calcul:

• Valorile TRUE sunt numărate ca 1
• Valorile FALSE sunt numărate ca 0
• Șirurile de text sunt numărate ca 0

Îl folosești astfel:

1	= VARPA (C4: C12)

Am adăugat încă cinci rânduri în tabel: J, Q, K, TRUE și FALSE. Coloana D arată modul în care VARPA interpretează aceste valori.

Ca urmare a adăugării acestui grup de valori mai mici la date, varianța a crescut la 8,984.

Funcția VARIANȚĂ în Foi de calcul Google

Funcția CORREL funcționează exact la fel în Foi de calcul Google ca în Excel: