Kā programmā Excel izveidot normālu sadalījuma zvana līkni

Šī apmācība parādīs, kā izveidot normāla sadalījuma zvana līkni visās Excel versijās: 2007, 2010, 2013, 2016 un 2022.

Statistikā zvana līkne (pazīstama arī kā standarta normālais sadalījums vai Gausa līkne) ir simetrisks grafiks, kas ilustrē datu tendenci grupēties ap centra vērtību vai vidējo noteiktā datu kopā.

Y ass attēlo noteiktas vērtības relatīvo varbūtību datu kopā, bet x ass diagrammā attēlo pašas vērtības, lai izveidotu zvana formas līkni, līdz ar to arī nosaukumu.

Diagramma palīdz mums analizēt, vai konkrēta vērtība ir daļa no paredzamajām izmaiņām vai ir statistiski nozīmīga, un tāpēc tā ir rūpīgi jāpārbauda.

Tā kā programmai Excel nav iebūvētu risinājumu, jums tas būs jāplāno pats. Tāpēc mēs izstrādājām diagrammu veidotāja pievienojumprogrammu-rīku, kas ļauj jums izveidot uzlabotas Excel diagrammas tikai ar dažiem klikšķiem.

Šajā soli pa solim apmācībā jūs uzzināsit, kā izveidot normāla sadalījuma zvana līkni programmā Excel no paša sākuma:

Lai uzzīmētu Gausa līkni, jums jāzina divas lietas:

• Vidējais (pazīstams arī kā standarta mērījums). Tas nosaka līknes centru, kas savukārt raksturo līknes stāvokli.
• Standarta novirze (SD) no mērījumiem. Tas nosaka jūsu datu izplatību normālā sadalījumā vai vienkāršā angļu valodā, cik lielai jābūt līknei. Piemēram, iepriekš parādītajā zvana līknē viena vidējā standarta novirze atspoguļo diapazonu starp eksāmenu rezultātiem 53 un 85.

Jo zemāks SD, jo augstāka ir līkne un jo mazāk tiks izkliedēti jūsu dati, un otrādi.

Ir vērts pieminēt 68-95-99,7 noteikumu, ko var piemērot jebkurai normālai sadalījuma līknei, kas nozīmē, ka aptuveni 68% jūsu datu tiks ievietoti vienā SD attālumā no vidējā, 95%-divos SD un 99,7%- trīs SD.

Tagad, kad jūs zināt būtisko, pāriesim no teorijas uz praksi.

Darba sākšana

Ilustrācijas nolūkos pieņemsim, ka jums ir 200 studentu pārbaudes rezultāti un vēlaties tos novērtēt pēc “līknes”, kas nozīmē, ka skolēnu atzīmes tiks balstītas uz viņu relatīvo sniegumu pārējā klasē:

1. solis: atrodiet vidējo.

Parasti jums no sākuma tiek norādītas vidējās un SD vērtības, bet, ja tas tā nav, varat viegli aprēķināt šīs vērtības, veicot dažas vienkāršas darbības. Vispirms pievērsīsimies vidējam.

Tā kā vidējais rādītājs norāda parauga vai datu kopas vidējo vērtību, jūs varat atrast savu standarta mērījumu, izmantojot funkciju AVERAGE.

Jebkurā tukšā šūnā ierakstiet šādu formulu (F1 šajā piemērā) blakus faktiskajiem datiem (kolonnas A un B), lai aprēķinātu eksāmenu rezultātu vidējo vērtību datu kopā:

1	= VIDĒJĀ (B2: B201)

Īsa piezīme: biežāk nekā nē, iespējams, vajadzēs noapaļot formulas izvadi. Lai to izdarītu, vienkārši iesaiņojiet to funkcijā ROUND šādi:

1	= APAĻA (VIDĒJĀ (B2: B201), 0)

2. solis: atrodiet standarta novirzi.

Viens lejā, viens iet. Par laimi, programmai Excel ir īpaša funkcija, lai veiktu visu netīro darbu, lai atrastu standarta novirzi:

1	= STDEV.P (B2: B201)

Formulā atkal tiek atlasītas visas vērtības no norādītā šūnu diapazona (B2: B201) un aprēķina tā standarta novirzi-vienkārši neaizmirstiet noapaļot arī izvadi.

1	= APAĻA (STDEV.P (B2: B201), 0)

3. solis: iestatiet līknes x ass vērtības.

Būtībā diagramma veido milzīgu intervālu skaitu (uzskatiet tos par soļiem), kas savienoti kopā ar līniju, lai izveidotu vienmērīgu līkni.

Mūsu gadījumā x ass vērtības tiks izmantotas, lai ilustrētu konkrētu eksāmena rezultātu, bet y ass vērtības mums parādīs varbūtību, ka students iegūs šo rezultātu eksāmenā.

Tehniski jūs varat iekļaut tik daudz intervālu, cik vēlaties-vēlāk bez piepūles varat izdzēst liekos datus, mainot horizontālās ass skalu. Vienkārši pārliecinieties, ka esat izvēlējies diapazonu, kurā būs iekļautas trīs standarta novirzes.

Sāksim skaitīšanu pie vienas (jo nekādā veidā students nevar iegūt negatīvu eksāmena rezultātu) un sāksim līdz 150-nav īsti svarīgi, vai ir 150 vai 1500-izveidot citu palīgu galdu.

1. Zem diagrammas datiem izvēlieties jebkuru tukšu šūnu (piemēram, E4) un veidu “1,” vērtība, kas nosaka pirmo intervālu.
2. Dodieties uz Mājas cilni.
3. Iekš Rediģēšana grupa, izvēlieties "Aizpildīt.”
4. Zem "Sērija,"Izvēlieties"Sleja.”
5. Priekš "Soļa vērtība,”Tipa “1.” Šī vērtība nosaka pieaugumus, kas tiks automātiski summēti, līdz Excel sasniegs pēdējo intervālu.
6. Priekš "Apturēt vērtību,"Ierakstiet"150,” vērtību, kas apzīmē pēdējo intervālu, un noklikšķiniet uz “LABI.”

Brīnumainā kārtā E slejā ir 149 šūnas (E5: E153) ir aizpildītas ar vērtībām no 2 līdz 150.

PIEZĪME. Neslēpiet sākotnējās datu šūnas, kā parādīts ekrānuzņēmumos. Pretējā gadījumā tehnika nedarbosies.

4. solis: aprēķiniet normālās sadalījuma vērtības katrai x ass vērtībai.

Tagad atrodiet normālā sadalījuma vērtības-varbūtību, ka students par katru intervālu saņems noteiktu eksāmena rezultātu, ko attēlo konkrēta x ass vērtība. Par laimi, programmai Excel ir darba zirgs, lai veiktu visus šos aprēķinus jūsu vietā: funkcija NORM.DIST.

Šūnā pa labi ierakstiet šādu formulu (F4) no pirmā intervāla (E4):

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

Šeit ir atšifrētā versija, kas palīdzēs jums attiecīgi pielāgoties:

1	= NORM.DIST ([pirmais intervāls], [vidējais (absolūtā atsauce)], [standarta novirze (absolūtā atsauce), FALSE)

Jūs bloķējat vidējās un SD vērtības, lai bez piepūles varētu izpildīt formulu atlikušajiem intervāliem (E5: E153).

Tagad veiciet dubultklikšķi uz aizpildīšanas roktura, lai kopētu formulu pārējās šūnās (F5: F153).

5. solis: izveidojiet izkliedes diagrammu ar gludām līnijām.

Visbeidzot, ir pienācis laiks veidot zvana līkni:

1. Palīgtabulā atlasiet jebkuru vērtību, kurā ir x un y ass vērtības (E4: F153).
2. Dodieties uz Ievietot cilni.
3. Noklikšķiniet uz “Ievietojiet izkliedes (X, Y) vai burbuļdiagrammu”Pogu.
4. Izvēlieties “Izkaisīt ar gludām līnijām. ”

6. solis: iestatiet etiķešu tabulu.

Tehniski jums ir zvana līkne. Bet to būtu grūti lasīt, jo tajā trūkst datu, kas to aprakstītu.

Padarīsim normālu sadalījumu informatīvāku, pievienojot etiķetes, kas ilustrē visas standarta novirzes vērtības zem un virs vidējās vērtības (tās var izmantot arī z-punktu rādīšanai).

Lai to izdarītu, izveidojiet vēl vienu palīgu galdu šādi:

Vispirms nokopējiet vidējo vērtību (F1) blakus attiecīgajai šūnai kolonnā X-Value (I5).

Pēc tam aprēķiniet standarta novirzes vērtības zem vidējā, ievadot šo vienkāršo formulu šūna I4:

1	= I5- $ F $ 2

Vienkārši sakot, formula atņem iepriekšējo standartnoviržu vērtību summu no vidējā. Tagad velciet aizpildīšanas rokturi uz augšu, lai kopētu formulu atlikušajās divās šūnās (I2: I3).

Atkārtojiet to pašu procesu standarta novirzēm virs vidējā, izmantojot spoguļa formulu:

1	= I5+2 ASV dolāri

Tādā pašā veidā izpildiet formulu pārējām divām šūnām (I7: I8).

Visbeidzot, aizpildiet y ass etiķetes vērtības (J2: J8) ar nullēm, kā vēlaties, lai datu marķieri tiktu novietoti uz horizontālās ass.

7. solis: ievietojiet etiķetes datus diagrammā.

Tagad pievienojiet visus sagatavotos datus. Ar peles labo pogu noklikšķiniet uz diagrammas diagrammas un izvēlieties “Atlasiet Dati.”

Parādītajā dialoglodziņā atlasiet “Pievienot.”

Iezīmējiet attiecīgos šūnu diapazonus no palīga galda-I2: I8 priekš "X sērijas vērtības" un J2: J8 priekš "Y sērijas vērtības ”-un noklikšķiniet uz "LABI.”

8. solis: nomainiet etiķešu sērijas diagrammas veidu.

Nākamais solis ir mainīt nesen pievienoto sēriju diagrammas veidu, lai datu marķieri tiktu parādīti kā punkti. Lai to izdarītu, ar peles labo pogu noklikšķiniet uz diagrammas diagrammas un atlasiet “Mainīt diagrammas veidu.”

Pēc tam izveidojiet kombinēto diagrammu:

1. Dodieties uz Kombinēts cilni.
2. Priekš Sērija “Sērija 2” mainīt "Diagrammas veids"Uz"Izkliedēt.”
   • Piezīme: pārliecinieties, ka "Sērija1"Paliek kā"Izkaisīt ar gludām līnijām. ” Dažreiz programma Excel to maina, kad veicat Kombinēts Pārliecinieties arī "Sērija1”Nav piespiests sekundārajai asij-izvēles rūtiņu blakus diagrammas tipam nevajadzētu atzīmēt.
3. Klikšķis "labi.”

9. solis: mainiet horizontālās ass skalu.

Centrējiet diagrammu zvana līknē, pielāgojot horizontālās ass skalu. Ar peles labo pogu noklikšķiniet uz horizontālās ass un izvēlieties “Asis formatēšana”No izvēlnes.

Kad parādās uzdevumrūtis, rīkojieties šādi:

• Dodieties uz Asu opcijas cilni.
• Iestatiet Minimālās robežas vērtība uz "15.”
• Iestatiet Maksimālās robežas vērtība uz "125.”

Asu skalas diapazonu var pielāgot pēc saviem ieskatiem, taču, tā kā jūs zināt standarta noviržu diapazonus, iestatiet robežu vērtības mazliet tālāk no katras trešās standarta novirzes, lai parādītu līknes “asti”.

10. solis: ievietojiet un novietojiet pielāgotās datu etiķetes.

Uzlabojot diagrammu, noteikti pievienojiet pielāgotas datu etiķetes. Vispirms ar peles labo pogu noklikšķiniet uz jebkura attēlojoša punkta Sērija “Sērija 2” un izvēlieties "Pievienojiet datu etiķetes.”

Pēc tam nomainiet noklusējuma etiķetes ar tām, kuras iepriekš iestatījāt, un novietojiet tās virs datu marķieriem.

1. Ar peles labo pogu noklikšķiniet uz jebkura Sērija “Sērija 2” datu etiķete.
2. Atlasiet “Datu etiķešu formatēšana.”
3. Uzdevumu rūtī pārslēdzieties uz Iezīmju opcijas cilni.
4. Pārbaudiet “X vērtība”Kaste.
5. Noņemiet atzīmi no "Y vērtība”Kaste.
6. Zem "Iezīmes pozīcija,"Izvēlieties"Virs.”

Tagad varat arī noņemt režģlīnijas (ar peles labo pogu noklikšķiniet uz tām> Dzēst).

11. solis: pārkrāsojiet datu marķierus (pēc izvēles).

Visbeidzot, pārkrāsojiet punktus, lai tie iekļautos diagrammas stilā.

1. Ar peles labo pogu noklikšķiniet uz jebkura Sērija “Sērija 2” datu etiķete.
2. Noklikšķiniet uz “Aizpildīt”Pogu.
3. Izvēlieties savu krāsu no parādītās paletes.

Noņemiet arī robežas ap punktiem:

1. Vēlreiz ar peles labo pogu noklikšķiniet uz tā paša datu marķiera un atlasiet “Kontūra.”
2. Izvēlieties “Nav kontūras.”

12. solis: pievienojiet vertikālas līnijas (pēc izvēles).

Kā galīgo korekciju diagrammai varat pievienot vertikālas līnijas, lai palīdzētu uzsvērt SD vērtības.

• Atlasiet diagrammas diagrammu (tādā veidā līnijas tiks ievietotas tieši diagrammā).
• Dodieties uz Ievietot cilni.
• Noklikšķiniet uz “Formas”Pogu.
• Izvēlieties “Līnija.”

Turiet nospiestu “SHIFT” taustiņu, velkot peli, lai no katra punkta novilktu pilnīgi vertikālas līnijas līdz vietai, kur tā atbilst zvana līknei.

Mainiet diagrammas nosaukumu, un uzlabotā zvana līkne ir gatava rādīt jūsu vērtīgos izplatīšanas datus.

Un tā jūs to darāt. Tagad varat izvēlēties jebkuru datu kopu un izveidot normāla sadalījuma zvana līkni, veicot šīs vienkāršās darbības!