VARIANCE - Excel in Google Preglednice

Ta vadnica prikazuje, kako uporabljati Funkcija Excel VARIANCE v Excelu za oceno variance na podlagi danega vzorca.

VARIANCE Pregled funkcije

Funkcija VARIANCE Izračuna oceno variance na podlagi danega vzorca.

Če želite uporabiti funkcijo delovnega lista Excel VARIANCE, izberite celico in vnesite:

(Upoštevajte, kako so prikazani vnosi formule)

Funkcija VARIANCE Sintaksa in vhodi:

1	= VAR (številka1, [številka2],…)

številke- Vrednosti za pridobivanje variacije

Kako izračunati razliko v Excelu

Razlika vam pove, kako razporejene so vrednosti v naboru podatkov od povprečja. Matematično gledano je varianca povprečje kvadratne razlike vsakega rezultata od povprečja (vendar bomo kmalu prišli do tega).

Excel vam ponuja številne funkcije za izračun variance - VAR.S, VAR.P, VARA, VARPA in dve starejši funkciji, VAR in VARP.

Preden se poglobimo v te funkcije in se naučimo, kako jih uporabljati, se pogovorimo o varianci in kako se izračuna.

Kaj je varianca?

Pri analizi podatkov je običajen prvi korak izračun povprečja. To je seveda uporabna statistika za izračun, vendar vam ne daje popolne slike o tem, kaj se dogaja z vašimi podatki.

Vzemite naslednji niz podatkov, ki je lahko skupina rezultatov testov, ocenjenih med 100:

1	48,49,50,51,52

Povprečje tega območja je 50 (seštejte številke in delite z n, kjer je n število vrednosti).

Nato vzemite naslednji niz rezultatov testa:

1	10,25,50,75,90

Povprečje tega območja je tudi 50 - očitno pa imamo tukaj dva zelo različna obsega podatkov.

Samo po sebi vam povprečje ne more povedati o tem, kako razpršene so ocene. Ne pove vam, ali so vse vrednosti združene kot v prvih primerih ali narazen kot drugi. Različica vam lahko pomaga pri tem.

Različica se uporablja tudi kot izhodišče za vrsto bolj zapletenih statističnih postopkov.

Kako se varianca izračuna

Poglejmo si osnovni primer in ročno izračunajmo varianco. Tako boste vedeli, kaj se dogaja v zakulisju, ko boste dejansko začeli izvajati Excelove funkcije variance.

Recimo, da imamo nabor podatkov, ki predstavljajo tri igralne karte, 4, 6 in 8.

Za izračun variance naredite ta postopek:

1) Izračunajte povprečje

Najprej izračunamo povprečje. Vemo, da je naš razpon podatkov 4, 6, 8, zato bo povprečje:

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

To sem spodaj potrdil s funkcijo Excel AVERAGE <>:

1	= POVPREČNO (C4: C6)

2) Odštejte povprečje od vsake vrednosti v nizu podatkov

Nato od vsake naše vrednosti odštejemo povprečje.

To sem naredil po naslednji formuli:

1	= C4- $ H $ 4

Povprečje je shranjeno v H4, zato to le odštejem od vsake vrednosti v tabeli. Znaki za dolar tukaj samo »zaklenejo« sklic te celice na H4, tako da, ko ga skopiram po stolpcu, ostane nespremenjen.

Rezultati:

Imamo:

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

Povprečje teh razlik moramo dobiti iz povprečja, vendar je povprečje teh treh vrednosti nič! Zato moramo poudariti razlike, ki jih naredimo s kvadraturiranjem.

3) Poravnajte razlike

Dodajmo nov stolpec in poravnamo številke v stolpcu D:

1

= D4*D4

V redu, tako je bolje. Zdaj, ko razlike niso povprečne na nič, lahko izračunamo odstopanje.

4) Izračunajte povprečje kvadratnih razlik

Tu srečamo razcep na cesti. Obstajata dva načina za izračun variance in tisti, ki ga uporabljate, je odvisen od vrste podatkov, ki jih imate.

• Če uporabljate podatki o prebivalstvu, preprosto vzamete povprečje kot običajno (seštejte vrednosti in delite z n)
• Če uporabljate vzorčni podatki, seštejete vrednosti in delite z n-1

Podatki o prebivalstvu pomenijo, da imate na voljo vse potrebne podatke, na primer, če želite povprečno starost učiteljev v določeni šoli in imate podatke o starosti za vsakega učitelja v tej šoli, imate podatke o prebivalstvu.

Vzorčni podatki pomenijo, da nimate vseh svojih podatkov, samo vzorec iz večje populacije. Če torej želite povprečno starost učiteljev v celotni državi in ​​imate podatke samo o učiteljih na eni šoli, imate vzorčne podatke.

V našem primeru imamo podatke o prebivalstvu. Zanimajo nas le naše tri karte - to je število prebivalcev in od njih nismo vzeli vzorca. Tako lahko na običajen način vzamemo povprečje kvadratnih razlik:

1	= POVPREČNO (E4: E8)

Tako je varianca našega prebivalstva 2,666.

Če to je bil vzorčne podatke (morda bi te tri kartice potegnili iz večjega niza), bi povprečje izračunali na naslednji način:

1	Vzorčna varianca = (4 + 0 + 4) / (3 - 1)

Ali:

1	Vzorčna varianca = 8 /2 = 4

Zakaj deliti z n-1 z vzorčnimi podatki, namesto samo n?

Kratek odgovor na to vprašanje je "Ker daje pravi odgovor". Predstavljam pa, da boste želeli malo več od tega! To je zapletena tema, zato bom tukaj le na kratko predstavil.

Pomislite na to tako: če vzamete vzorec podatkov iz populacije, bodo te vrednosti ponavadi bližje povprečju vzorec kot so v povprečju prebivalstva.

To pomeni, da če delite le z n, boste malo podcenili varianco prebivalstva. Delitev z n-1 to nekoliko popravi.

S svojim nizom treh kart smo na dobrem mestu, da preizkusimo to teorijo. Ker obstajajo le tri kartice, je mogoče vzeti majhno število vzorcev.

Vzemimo vzorce dveh kart. Izbrali bomo eno kartico, jo dali nazaj, premešali in nato izbrali drugo. To pomeni, da lahko izberemo devet kombinacij dveh kart.

Z le devetimi možnimi vzorci lahko izračunamo vsako možno varianco vzorca z uporabo obeh metod (deljenje z n in deljenje z n-1), njihovo povprečje in poglejmo, katera nam daje pravilen odgovor.

V spodnji tabeli sem vse predstavil. Vsaka vrstica tabele je drugačen vzorec, stolpca B in C pa prikazujeta dve kartici, ki sta bili izbrani v vsakem vzorcu. Nato sem dodal še dva stolpca: enega, kjer sem izračunal varianco vzorca dveh kartic z deljenjem z n, in drugega, kjer sem delil z n - 1.

Poglej:

Desno od tabele sem prikazal povprečja stolpcev D in E.

Povprečje stolpca D pri deljenju z n nam daje varianco 1,333.

Povprečje stolpca E pri deljenju z n-1 nam daje variacijo 2,666.

Iz prejšnjega primera že vemo, da je varianca populacije 2,666. Tako deljenje z n-1 pri uporabi vzorčnih podatkov daje natančnejše ocene.

Excelove funkcije za izračun variacije

Zdaj, ko ste videli primer izračunavanja variance, pojdimo na funkcije Excel.

Tukaj imate več možnosti:

• P vrne varianco za podatke o prebivalstvu (z uporabo metode deljenja z n)
• S vrne varianco vzorčnih podatkov (deljeno z n-1)
• VAR je starejša funkcija, ki deluje popolnoma enako kot VAR.S
• VARA je enako kot VAR.S, le da vključuje besedilne celice in logične vrednosti
• VARPA je enako kot VAR.P, le da vključuje besedilne celice in logične vrednosti

Pojdimo skozi te enega za drugim.

Funkcija Excel VAR.P

VAR.P izračuna varianco za podatke o prebivalstvu (z uporabo metode deljenja z n). Uporabite ga tako:

1	= VAR.P (C4: C6)

V VAR.P določite samo en argument: obseg podatkov, za katerega želite izračunati varianco. V našem primeru so to vrednosti kartic v C4: C6.

Kot vidite zgoraj, VAR.P vrne 2,666 za naš niz treh kart. To je ista vrednost, ki smo jo prej ročno izračunali.

Upoštevajte, da VAR.P popolnoma ignorira celice, ki vsebujejo besedilo ali logične vrednosti (TRUE/FALSE). Če jih želite vključiti, namesto tega uporabite VARPA.

Funkcija Excel VAR.S

VAR.S izračuna varianco vzorčnih podatkov (deljeno z n-1). Uporabljate ga tako:

1	= VAR.S (C4: C6)

Še enkrat, obstaja le en argument - vaš obseg podatkov.

V tem primeru VAR.S vrne 4. Enako številko smo dobili v 4. koraku, ko smo opravili zgornji ročni izračun.

VAR.S popolnoma ignorira celice, ki vsebujejo besedilo ali logične vrednosti (TRUE/FALSE). Če jih želite vključiti, namesto tega uporabite VARA.

Funkcija Excel VAR

VAR je popolnoma enakovreden VAR.S: izračunava variacije vzorčnih podatkov (z uporabo metode n-1). Takole ga uporabite:

1	= VAR (C4: C6)

VAR je "funkcija združljivosti". To pomeni, da Microsoft trenutno odstranjuje to funkcijo iz Excela. Trenutno je še vedno na voljo za uporabo, vendar namesto tega uporabite VAR.S, da bodo vaše preglednice ostale združljive s prihodnjimi različicami Excela.

Funkcija Excel VARA

VARA tudi vrača varianco vzorčnih podatkov, vendar ima nekaj ključnih razlik v VAR in VAR.S. V svoj izračun namreč vključuje logične in besedilne vrednosti:

• Vrednosti TRUE se štejejo kot 1
• Vrednosti FALSE se štejejo kot 0
• Besedilni nizi se štejejo kot 0

Tako ga uporabljate:

1	= VARA (C4: C11)

V tabelo smo dodali še pet vrstic: J, Q, K, TRUE in FALSE. V stolpcu D je prikazano, kako si VARA razlaga te vrednosti.

Ker imamo v tabeli novo serijo nizkih vrednosti, se je varianca povečala na 10,268.

Funkcija Excel VARPA

VARPA izračuna varianco za podatke o prebivalstvu. Podoben je VAR.P, le da v izračun vključuje tudi logične vrednosti in besedilne nize:

• Vrednosti TRUE se štejejo kot 1
• Vrednosti FALSE se štejejo kot 0
• Besedilni nizi se štejejo kot 0

Uporabljate ga tako:

1	= VARPA (C4: C12)

V tabelo smo dodali še pet vrstic: J, Q, K, TRUE in FALSE. V stolpcu D je prikazano, kako si VARPA razlaga te vrednosti.

Zaradi dodajanja te skupine nižjih vrednosti k podatkom se je varianca povečala na 8,984.

Funkcija VARIANCE v Google Preglednicah

Funkcija CORREL deluje v Google Preglednicah popolnoma enako kot v Excelu: