Standardno odstupanje - Excel i Google tablice

Ovaj vodič prikazuje kako se koristi Excel funkcija standardnog odstupanja u Excelu za izračun standardne devijacije za cijelu populaciju.

STANDARDNO ODSTUPANJE Pregled funkcije

Funkcija STANDARD DEVIATION Funkcija izračunava standardnu ​​devijaciju za cijelu populaciju.

Da biste koristili funkciju STANDARDNOG ODSTUPANJA Excel radnog lista, odaberite ćeliju i upišite:

(Obratite pažnju na to kako se pojavljuju unosi formule)

Funkcija STANDARD DEVIATION Sintaksa i ulazi:

1	= STDEV (broj1, [broj2],…)

brojevima- Vrijednosti za dobivanje standardne varijance

Kako izračunati standardnu ​​devijaciju u Excelu

Kad god se bavite podacima, htjet ćete pokrenuti neke osnovne testove koji će vam pomoći da ih razumijete. Obično ćete početi izračunavanjem srednje vrijednosti, koristeći funkciju PROSJEČNOG Excela <>.

To vam daje ideju o tome gdje je "sredina" podataka. Odatle ćete htjeti pogledati koliko su podaci raspoređeni oko ove središnje točke. Ovdje dolazi standardna devijacija.

Excel vam nudi niz funkcija za izračun standardne devijacije - STDEV, STDEV.P, STDEV.S i DSTDEV. Doći ćemo do svih njih, ali prvo, naučimo što je standardna devijacija je, točno.

Što je standardna devijacija?

Standardno odstupanje daje vam ideju o tome koliko su vaše podatkovne točke udaljene od srednje vrijednosti. Uzmite sljedeći skup podataka od 100 rezultata:

1	48,49,50,51,52

Prosjek ovog skupa podataka je 50 (zbrojite sve brojeve i podijelite s n, gdje je n broj vrijednosti u rasponu).

Sada pogledajte sljedeći skup podataka:

1	10,25,50,75,90

Srednja vrijednost ovog skupa podataka je također 50 - ali dva raspona pričaju vrlo različitu priču. Da ste samo koristili srednju vrijednost, mogli biste pomisliti da su dvije grupe približno jednake po svojoj sposobnosti - i u prosjeku jesu.

No, u prvoj skupini imamo 5 ljudi koji su dobili vrlo slične, vrlo osrednje ocjene. A u drugoj skupini, mi smo nekoliko visokih letača izbalansirali par loših strijelaca, s jednom osobom u sredini. The širenje bodova vrlo je različit, pa je i vaše tumačenje podataka vrlo različito.

Standardna devijacija je mjera ovog raspona.

Kako se izračunava standardna devijacija

Da biste razumjeli što je standardna devijacija i kako to funkcionira, može vam pomoći ručni rad kroz primjer. Na taj ćete način znati što se događa "ispod haube" kad dođemo do Excel funkcija koje možete koristiti.

Da biste izračunali standardnu ​​devijaciju, radite kroz ovaj postupak:

1) Izračunajte srednju vrijednost

Uzmimo naš prvi skup podataka gore: 48,49,50,51,52

Već znamo srednju vrijednost (50), koju sam ovdje potvrdio funkcijom Excel AVERAGE <>:

1	= PROSJEČNO (C4: C8)

2) Oduzmite srednju vrijednost od svake vrijednosti u skupu podataka

Učinio sam to sa sljedećom formulom:

1	= C4- $ H $ 4

Naša srednja vrijednost je u H4, a ja sam "zaključao" referencu ćelije stavljajući znakove dolara ispred stupca i retka (pritiskom na F4). To znači da mogu kopirati formulu u stupac bez ažuriranja reference ćelije.

Rezultat:

Sada, zastanimo ovdje na trenutak. Ako pogledate novi stupac, vidjet ćete da se ovdje brojevi zbrajaju s nulom. Srednja vrijednost tih brojeva također je nula.

Naravno, širenje naših podataka ne može biti nula - znamo da postoje neke varijacije. Potreban nam je način da predstavimo ovu varijaciju, a da se prosjek ne pokaže nulom.

3) Izravnajte razlike

To možemo postići kvadraturom razlika. Dakle, dodajmo novi stupac i kvadrirajmo brojeve u stupcu D:

1

= D4*D4

Ovo izgleda bolje. Sada imamo neke varijacije, a količina varijacije je povezana s time koliko je svaki rezultat udaljen od srednje vrijednosti.

4) Izračunajte varijancu - srednju vrijednost kvadrata razlika

Sljedeći korak je dobiti prosjek tih kvadrata razlika. Postoje dva načina za to pri izračunavanju standardne devijacije.

• Ako koristite podaci o stanovništvu, jednostavno uzmete srednju vrijednost (zbrojite vrijednosti i podijelite s n)
• Ako koristite uzorci podataka, uzmete vrijednosti i podijelite s n-1

Podaci o stanovništvu znače da imate "potpuni skup" svojih podataka, na primjer, imate podatke o svakom učeniku u danom razredu.

Uzorci podataka znače da nemate sve svoje podatke, samo uzorak uzet iz veće populacije. Obično je vaš cilj uz uzorke podataka procijeniti kolika je vrijednost u većoj populaciji.

Ispitivanje političkog mišljenja dobar je primjer uzorka podataka - istraživači su, recimo, anketirali 1.000 ljudi kako bi stekli predodžbu o tome što misli cijela država ili država.

Ovdje nemamo uzorak. Imamo samo pet članova obitelji koji misle na statistiku i koji žele izračunati standardnu ​​devijaciju testa koji su svi prošli. Imamo sve podatke i ne procjenjujemo veću skupinu ljudi. Ovo su podaci o stanovništvu - pa ovdje možemo uzeti samo prosjek:

1	= PROSJEČNO (E4: E8)

U redu, dakle imamo 2. Ova ocjena poznata je kao "varijansa" i temeljna je točka za mnoge statističke testove, uključujući standardnu ​​devijaciju. Više o varijanci možete pročitati na njezinoj glavnoj stranici: kako izračunati varijansu u Excelu <>.

5) Dobijte kvadratni korijen varijacije

Ranije smo izravnali svoje brojeve, što očito malo napuhuje vrijednosti. Dakle, kako bismo brojku vratili u skladu sa stvarnim razlikama ocjena od srednje vrijednosti, moramo kvadratni korijen rezultat iz koraka 4:

1	= SQRT (H4)

I imamo svoj rezultat: standardna devijacija je 1,414

Budući da smo kvadratno ukorijenili svoje prethodno kvadrirane brojeve, standardna devijacija data je u istim jedinicama kao i izvorni podaci. Dakle, standardna devijacija je 1.414 ispitnih točaka.

Standardno odstupanje kada su podaci rašireniji

Ranije smo imali drugi primjer raspona podataka: 10,25,50,75,90

Radi zabave, da vidimo što se događa kada izračunamo standardnu ​​devijaciju na ovim podacima:

Sve formule su potpuno iste kao i prije (imajte na umu da je ukupna sredina još uvijek 50).

Jedino što se promijenilo je širenje rezultata u stupcu C. No, sada je naša standardna devijacija mnogo veća, na 29,832 ispitnih točaka.

Naravno, budući da imamo samo 5 podatkovnih točaka, vrlo je lako vidjeti da se raspon rezultata razlikuje između dva skupa. No, kada imate 100 ili 1000 podatkovnih točaka, to ne možete utvrditi brzim skeniranjem podataka. I upravo zato koristimo standardnu ​​devijaciju.

Excel funkcije za izračunavanje standardne devijacije

Sada kada znate kako funkcionira standardna devijacija, ne morate prolaziti cijeli taj proces da biste došli do standardne devijacije. Možete jednostavno upotrijebiti jednu od Excel-ovih ugrađenih funkcija.

Excel u tu svrhu ima nekoliko funkcija:

• P izračunava standardnu ​​devijaciju za podatke o populaciji (koristeći točnu metodu koju smo koristili u gornjem primjeru)
• S izračunava standardnu ​​devijaciju za uzorke podataka (pomoću metode n-1 koje smo ranije dotaknuli)
• STDEV potpuno je isto što i STDEV.S. Ovo je starija funkcija koju su zamijenili STDEV.S i STDEV.P.
• STDEVA vrlo je sličan STDEV.S -u, osim što uključuje tekstualne ćelije i logičke (TRUE/FALSE) ćelije pri izračunu.
• STDEVPA vrlo je sličan STDEV.P -u, osim što uključuje tekstualne ćelije i logičke (TRUE/FALSE) ćelije pri izračunu.

Vau, ovdje ima mnogo opcija! Nemojte se zastrašiti - u velikoj većini slučajeva upotrebljavat ćete ili STDEV.P ili STDEV.S.

Prođimo redom kroz svaki od ovih, počevši od STDEV.P, budući da je to metoda kroz koju smo upravo radili.

Excel STDEV.P funkcija

STDEV.P izračunava standardnu ​​devijaciju za podatke o populaciji. Koristite ga ovako:

1	= STDEV.P (C4: C8)

U STDEV.P definirate jedan argument: raspon podataka za koji želite izračunati standardnu ​​devijaciju.

Ovo je isti primjer kroz koji smo prošli korak po korak gore kada smo ručno izračunali standardnu ​​devijaciju. I kao što možete vidjeti gore, dobivamo potpuno isti rezultat - 1.414.

Napomena STDEV.P zanemaruje sve ćelije koje sadrže tekst ili logičke (TRUE/FALSE) vrijednosti. Ako ih trebate uključiti, upotrijebite STDEVPA.

Excel STDEV.S funkcija

STDEV.S izračunava standardnu ​​devijaciju za uzorke podataka. Koristite ga ovako:

1	= STDEV.S (C4: C8)

Opet, potreban je jedan argument - raspon podataka za koje želite znati standardnu ​​devijaciju.

Prije nego uđemo u primjer, razgovarajmo o razlici između STDEV.S i STDEV.P.

Kao što smo već raspravljali, STDEV.S bi se trebao koristiti za uzorke podataka - kada su vaši podaci dio većeg skupa. Pretpostavimo sada da je u našem gore navedenom primjeru više ljudi prošlo test. Želimo procijeniti standardnu ​​devijaciju svih koji su polagali test, koristeći samo ovih pet bodova. Sada koristimo uzorke podataka.

Sada se izračun razlikuje od gore navedenog koraka (4), kada izračunavamo varijansu - prosjek kvadratne razlike svake ocjene od ukupne srednje vrijednosti.

Umjesto uobičajene metode - zbrojimo sve vrijednosti i podijelimo s n, zbrojili bismo sve vrijednosti i podijelili sa n-1:

1	= ZBIR (E4: E8) / (BROJ (E4: E8) -1)

U ovoj formuli:

• SUM dobiva zbroj kvadrata razlika
• COUNT vraća naše n, od čega oduzimamo 1
• Zatim jednostavno podijelimo svoj zbroj s našim n-1

Ovaj put je srednja vrijednost kvadratnih razlika 2,5 (možda se sjećate da je ranije bila 2, pa je malo veća).

Pa zašto dijelimo s n-1 umjesto s n kada se bavimo uzorcima podataka?

Odgovor je prilično složen i ako samo pokušavate provjeriti svoje brojeve kako biste razumjeli svoje podatke, to nije nešto što vas stvarno treba zabrinjavati. Samo provjerite koristite li STDEV.S za uzorke podataka i STDEV.P za podatke o populaciji, i bit ćete dobro.

Ako ste doista znatiželjni zašto, pogledajte glavnu stranicu o tome kako izračunati varijancu u Excelu <>.

U redu, sada imamo varijansu za uzorak, pa da bismo dobili standardnu ​​devijaciju za uzorak, samo bismo dobili kvadratni korijen varijance:

1	= SQRT (H4)

Dobivamo 1.581.

STDEV.S radi sve gore navedene izračune umjesto nas i vraća standardnu ​​devijaciju uzorka u samo jednu ćeliju. Pa da vidimo s čim dolazi …

1	= STDEV.S (C4: C8)

Da, opet 1.581.

Excel STDEV funkcija

Excel -ova funkcija STDEV radi na potpuno isti način kao i STDEV.S - odnosno izračunava standardnu ​​devijaciju za uzorak podataka.

Koristite ga na isti način:

1	= STDEV (C4: C8)

Opet smo dobili isti rezultat.

Važna nota: STDEV je "funkcija kompatibilnosti", što u osnovi znači da se Microsoft toga rješava. Za sada još radi, pa će sve starije proračunske tablice nastaviti raditi normalno. No, u budućim verzijama programa Excel Microsoft bi ga mogao potpuno napustiti, pa biste trebali koristiti STDEV.S umjesto STDEV gdje god je to moguće.

Excel STDEVA funkcija

STDEVA se također koristi za izračun standardne devijacije za uzorak, ali ima nekoliko važnih razlika o kojima morate znati:

• VRIJEDNE vrijednosti se računaju kao 1
• Vrijednosti FALSE računaju se kao 0
• Tekstualni nizovi broje se kao 0

Koristite ga na sljedeći način:

1	= STDEVA (C4: C8)

Još su četiri prijatelja i članovi obitelji dali rezultate na svojim testovima. Oni su prikazani u stupcu C, a stupac D pokazuje kako STDEVA tumači ove podatke.

Budući da se ove ćelije tumače kao tako niske vrijednosti, to stvara mnogo širi raspon među našim podacima nego što smo vidjeli prije, što je uvelike povećalo standardnu ​​devijaciju, sada na 26.246.

Excel STDEVPA funkcija

STDEVPA izračunava standardnu ​​devijaciju za populaciju na isti način kao i STDEV.P. Međutim, on također uključuje logičke vrijednosti i nizove teksta u izračun, koji se tumače na sljedeći način:

• VRIJEDNE vrijednosti se računaju kao 1
• Vrijednosti FALSE računaju se kao 0
• Tekstualni nizovi broje se kao 0

Koristite ga ovako:

1	= STDEVPA (C4: C12)

Filtriranje podataka prije izračuna standardnog odstupanja

U stvarnom svijetu nećete uvijek imati točne podatke koji su vam potrebni u lijepoj urednoj tablici. Često ćete imati veliku proračunsku tablicu punu podataka koje ćete morati filtrirati prije izračunavanja standardne devijacije.

To možete učiniti vrlo jednostavno pomoću Excel -ovih funkcija baze podataka: DSTDEV (za uzorke) i DSTDEVP (za populacije).

Ove vam funkcije omogućuju stvaranje tablice kriterija u kojoj možete definirati sve filtre koji su vam potrebni. Funkcije primjenjuju ove filtere iza kulisa prije nego što vrate standardnu ​​devijaciju. Na ovaj način ne morate dodirivati ​​autofilter niti izvlačiti podatke u zaseban list - DSTDEV i SDTDEVP sve to mogu učiniti umjesto vas.

Saznajte više na glavnoj stranici za funkcije Excel DSTDEV i DSTDEVP <>.

STANDARDNA ODSTUPANJE Funkcija u Google tablicama

Funkcija STANDARD DEVIATION funkcionira potpuno isto u Google tablicama kao i u Excelu: