Kako ustvariti običajno krivuljo zvonjenja normalne distribucije v Excelu

Ta vadnica bo pokazala, kako ustvariti običajno krivuljo zvonjenja distribucije v vseh različicah Excela: 2007, 2010, 2013, 2016 in 2022.

V statistiki je zvončasta krivulja (znana tudi kot standardna normalna porazdelitev ali Gaussova krivulja) simetrični graf, ki ponazarja težnjo podatkov, da se združijo okoli osrednje vrednosti ali povprečja v danem naboru podatkov.

Os y predstavlja relativno verjetnost, da se določena vrednost pojavi v naboru podatkov, medtem ko os x nariše vrednosti same na grafikonu, da ustvari zvonasto krivuljo, od tod tudi ime.

Graf nam pomaga analizirati, ali je določena vrednost del pričakovane variacije ali je statistično pomembna, zato jo je treba podrobneje preučiti.

Ker Excel nima vgrajenih rešitev, ga boste morali pripraviti sami. Zato smo razvili dodatek Chart Creator-orodje, ki vam omogoča, da z nekaj kliki sestavite napredne Excelove grafikone.

V tej vadnici po korakih se boste naučili, kako ustvariti normalno krivuljo zvonjenja distribucije v Excelu od začetka:

Če želite narisati Gaussovo krivuljo, morate vedeti dve stvari:

• Povprečje (znana tudi kot standardna meritev). To določa središče krivulje, ki pa označuje položaj krivulje.
• Standardni odklon (SD) meritev. To določa širjenje vaših podatkov v običajni distribuciji-ali v navadni angleščini, kako široka mora biti krivulja. Na primer, na zgornji krivulji zvonca eno standardno odstopanje povprečja predstavlja razpon med ocenami izpitov 53 in 85.

Nižja kot je SD, višja je krivulja in manj se bodo vaši podatki razširili in obratno.

Omeniti velja pravilo 68-95-99,7, ki ga je mogoče uporabiti za katero koli običajno krivuljo porazdelitve, kar pomeni, da bo približno 68% vaših podatkov postavljenih znotraj enega SD stran od povprečja, 95% v dveh SD in 99,7% znotraj tri SD.

Zdaj, ko poznate bistvo, preidimo iz teorije v prakso.

Začetek

Za ilustracijo predpostavimo, da imate ocene 200 učencev in jih želite oceniti "na krivulji", kar pomeni, da bodo ocene učencev temeljile na njihovi relativni uspešnosti do preostalega razreda:

1. korak: Poiščite sredino.

Običajno ste od začetka dobili povprečne in SD vrednosti, če pa temu ni tako, jih lahko preprosto izračunate v le nekaj preprostih korakih. Najprej se lotimo povprečja.

Ker povprečje označuje povprečno vrednost vzorca ali populacije podatkov, lahko svoje standardne meritve poiščete s funkcijo AVERAGE.

V katero koli prazno celico vnesite naslednjo formulo (F1 v tem primeru) poleg vaših dejanskih podatkov (stolpci A in B) za izračun povprečja rezultatov izpitov v naboru podatkov:

1	= POVPREČNO (B2: B201)

Na kratko: pogosteje kot ne, boste morda morali zaokrožiti izhod formule. Če želite to narediti, ga preprosto zavijte v funkcijo ROUND:

1	= OKROGLA (POVPREČNO (B2: B201), 0)

2. korak: Poiščite standardni odklon.

Enega dol, enega do konca. Na srečo ima Excel posebno funkcijo, ki opravi vsa umazana dela pri iskanju standardnega odstopanja za vas:

1	= STDEV.P (B2: B201)

Ponovno formula izbere vse vrednosti iz navedenega obsega celic (B2: B201) in izračuna njegovo standardno odstopanje-samo ne pozabite zaokrožiti tudi izhoda.

1	= OKROGLA (STDEV.P (B2: B201), 0)

3. korak: Nastavite vrednosti osi x za krivuljo.

V bistvu grafikon sestavlja veliko število intervalov (mislimo jih kot korake), ki so združeni skupaj s črto, da ustvarijo gladko krivuljo.

V našem primeru bodo vrednosti osi x prikazane za ponazoritev določenega izpita, vrednosti osi y pa nam bodo povedale verjetnost, da bo študent dobil to oceno na izpitu.

Tehnično lahko vključite poljubno število intervalov-kasneje lahko brez težav izbrišete odvečne podatke s spreminjanjem lestvice vodoravne osi. Samo izberite območje, ki bo vključevalo tri standardna odstopanja.

Začnimo šteti pri enem (saj študent nikakor ne more dobiti negativne ocene izpita) in se povzpnemo vse do 150-ni pomembno, ali je 150 ali 1500-za postavitev druge pomožne mize.

1. Izberite katero koli prazno celico pod podatki grafikona (npr E4) in tip “1,” vrednost, ki določa prvi interval.
2. Pomaknite se do Domov zavihek.
3. V Urejanje skupina, izberite »Izpolnite.”
4. Spodaj "Serija v,"Izberite"Stolpec.”
5. Za "Vrednost koraka,”Tip “1.” Ta vrednost določa korake, ki se bodo samodejno seštevali, dokler Excel ne doseže zadnjega intervala.
6. Za "Stop vrednost,"Tip"150,” vrednost, ki pomeni zadnji interval, in kliknite »V REDU.”

Čudežno 149 celic v stolpcu E (E5: E153) so bile napolnjene z vrednostmi od 2 do 150.

OPOMBA: Ne skrivajte izvirnih podatkovnih celic, kot je prikazano na posnetkih zaslona. V nasprotnem primeru tehnika ne bo delovala.

4. korak: Izračunajte normalne vrednosti porazdelitve za vsako vrednost osi x.

Zdaj poiščite normalne vrednosti porazdelitve-verjetnost, da bo študent dobil določeno oceno izpita, predstavljeno z določeno vrednostjo osi x-za vsak interval. Na srečo za vas ima Excel delovnega konja, ki za vas opravi vse te izračune: funkcijo NORM.DIST.

V celico na desni vnesite naslednjo formulo (F4) vašega prvega intervala (E4):

1	= NORM.DIST (E4, $ F $ 1, $ F $ 2, FALSE)

Tukaj je dekodirana različica, ki vam bo pomagala pri ustrezni prilagoditvi:

1	= NORM.DIST ([prvi interval], [povprečje (absolutna referenca)], [standardni odklon (absolutna referenca), FALSE)

Srednje vrednosti in vrednosti SD zaklenete, tako da lahko brez težav izvedete formulo za preostale intervale (E5: E153).

Zdaj dvokliknite ročico za polnjenje, da kopirate formulo v preostale celice (F5: F153).

5. korak: Ustvarite razpršeno ploskev z gladkimi črtami.

Končno je prišel čas za izgradnjo zvonove krivulje:

1. Izberite katero koli vrednost v pomožni tabeli, ki vsebuje vrednosti osi x in y (E4: F153).
2. Pojdite na Vstavi zavihek.
3. Kliknite na “Vstavite razpršilec (X, Y) ali mehurček”Gumb.
4. Izberite “Razpršite z gladkimi črtami. "

6. korak: Nastavite tabelo oznak.

Tehnično imate zvončno krivuljo. Vendar bi bilo težko prebrati, ker nima podatkov, ki bi to opisovali.

Naredimo običajno distribucijo bolj informativno, tako da dodamo oznake, ki ponazarjajo vse vrednosti standardnega odstopanja pod in nad povprečjem (lahko jih uporabite tudi za prikaz z-rezultatov).

Za to nastavite še eno pomožno tabelo:

Najprej kopirajte srednjo vrednost (F1) poleg ustrezne celice v stolpcu X-Value (I5).

Nato izračunajte vrednosti standardnega odklona pod povprečjem tako, da vnesete to preprosto formulo celica I4:

1	= I5- $ F $ 2

Preprosto povedano, formula odšteje vsoto prejšnjih vrednosti standardnega odklona od povprečja. Zdaj povlecite ročico za polnjenje navzgor, da kopirate formulo v preostali dve celici (I2: I3).

Isti postopek ponovite za standardna odstopanja nad srednjo vrednostjo z uporabo zrcalne formule:

1	= I5+$ F $ 2

Na enak način izvedite formulo za drugi dve celici (I7: I8).

Na koncu vnesite vrednosti oznake osi y (J2: J8) z ničlami, kot želite, da so podatkovni označevalci postavljeni na vodoravno os.

Korak #7: Vstavite podatke o oznaki v grafikon.

Zdaj dodajte vse podatke, ki ste jih pripravili. Z desno miškino tipko kliknite grafikon in izberite »Izberite Podatki.”

V pogovornem oknu, ki se prikaže, izberite »Dodaj.”

Označite ustrezne obsege celic v pomožni tabeli-I2: I8 za "Vrednosti serije X”In J2: J8 za "Vrednosti serije Y ”-in kliknite »V REDU.”

8. korak: spremenite vrsto grafikona serije nalepk.

Naslednji korak je, da spremenimo vrsto grafikona na novo dodane serije, tako da bodo podatkovni označevalci prikazani kot pike. Če želite to narediti, z desno miškino tipko kliknite grafikon in izberite »Spremeni vrsto grafikona.”

Nato oblikujte kombinirani grafikon:

1. Pomaknite se do Combo zavihek.
2. Za Serija "Serija2", spremeni "Vrsta grafikona"Do"Razpršilo.”
   • Opomba: Poskrbite, da "Serija1"Ostane kot"Razpršite z gladkimi črtami. " Včasih ga Excel spremeni, ko naredite Combo Prepričajte se tudi »Serija1”Ni potisnjen na sekundarno os-potrditveno polje poleg vrste grafikona ne bi smelo biti označeno.
3. Kliknite »v redu.”

9. korak: spremenite lestvico vodoravne osi.

Postavite grafikon na krivuljo zvonca s prilagoditvijo lestvice vodoravne osi. Z desno miškino tipko kliknite vodoravno os in izberite »Oblika osi«Iz menija.

Ko se prikaže podokno opravil, naredite naslednje:

• Pojdite na Možnosti osi zavihek.
• Nastavite Najmanjše meje vrednost na "15.”
• Nastavite Največje meje vrednost na "125.”

Območje osi lahko prilagodite tako, kot se vam zdi primerno, a ker poznate območja standardnih odklonov, nastavite vrednosti meja nekoliko stran od vsakega od tretjih standardnih odstopanj, da prikažete "rep" krivulje.

10. korak: Vstavite in postavite podatkovne oznake po meri.

Med poliranjem grafikona dodajte podatkovne oznake po meri. Najprej z desno miškino tipko kliknite katero koli piko, ki predstavlja Serija "Serija 2" in izberite »Dodajte podatkovne oznake.”

Nato zamenjajte privzete oznake z tistimi, ki ste jih predhodno nastavili, in jih postavite nad označevalce podatkov.

1. Z desno miškino tipko kliknite katero koli Serija "Serija 2" podatkovna nalepka.
2. Izberite »Oblikujte podatkovne oznake.”
3. V podoknu opravil preklopite na Možnosti oznak zavihek.
4. Preverite »Vrednost X" škatla.
5. Počistite polje »Y Vrednost" škatla.
6. Spodaj "Položaj nalepke,"Izberite"Zgoraj.”

Zdaj lahko odstranite tudi mrežne črte (z desno tipko miške kliknite nanje> Izbriši).

11. korak: Prebarvajte podatkovne označevalce (neobvezno).

Nazadnje prebarvajte pike, da se bodo prilegale vašemu slogu grafikona.

1. Z desno miškino tipko kliknite katero koli Serija "Serija 2" podatkovna nalepka.
2. Kliknite na “Izpolnite”Gumb.
3. Iz prikazane palete izberite svojo barvo.

Odstranite tudi obrobe okoli pik:

1. Znova kliknite desni gumb na istem označevalniku podatkov in izberite »Oris.”
2. Izberite “Brez orisa.”

Korak #12: Dodajte navpične črte (neobvezno).

Kot zadnjo prilagoditev lahko grafikonu dodate navpične črte, ki pomagajo poudariti vrednosti SD.

• Izberite grafikon grafikona (tako bodo črte vstavljene neposredno v grafikon).
• Pojdite na Vstavi zavihek.
• Kliknite na “Oblike”Gumb.
• Izberite “Linija.”

Držite tipko "SHIFT" med vlečenjem miške potegnite popolnoma navpične črte od vsake pike do mesta, kjer se vsaka črta ujema s krivuljo zvonca.

Spremenite naslov grafikona in izboljšana krivulja zvonca bo pripravljena za prikaz vaših dragocenih distribucijskih podatkov.

In tako to počnete. Zdaj lahko izberete kateri koli nabor podatkov in po teh preprostih korakih ustvarite običajno krivuljo zvonjenja distribucije!