Hoe maak je een normale verdeling Bell Curve in Excel

Deze zelfstudie laat zien hoe u een belcurve voor normale distributie kunt maken in alle versies van Excel: 2007, 2010, 2013, 2016 en 2022.

In de statistiek is een belcurve (ook bekend als een standaard normale verdeling of Gauss-curve) een symmetrische grafiek die de neiging van gegevens illustreert om te clusteren rond een centrale waarde, of gemiddelde, in een bepaalde gegevensset.

De y-as vertegenwoordigt de relatieve kans dat een bepaalde waarde in de dataset voorkomt, terwijl de x-as de waarden zelf in de grafiek uitzet om een ​​klokvormige curve te creëren, vandaar de naam.

De grafiek helpt ons te analyseren of een bepaalde waarde deel uitmaakt van de verwachte variatie of statistisch significant is en daarom nader moet worden onderzocht.

Aangezien Excel geen ingebouwde oplossingen te bieden heeft, zult u het zelf moeten plotten. Daarom hebben we de Chart Creator Add-in ontwikkeld, een tool waarmee je met slechts een paar klikken geavanceerde Excel-diagrammen kunt maken.

In deze stapsgewijze zelfstudie leert u hoe u vanaf het begin een belcurve voor normale distributie in Excel kunt maken:

Om een ​​Gauss-curve te plotten, moet u twee dingen weten:

• De betekenis (ook wel de standaardmeting genoemd). Dit bepaalt het middelpunt van de curve, wat op zijn beurt de positie van de curve kenmerkt.
• De standaarddeviatie (SD) van de metingen. Dit definieert de spreiding van uw gegevens in de normale verdeling - of in gewoon Engels, hoe breed de curve zou moeten zijn. In de hierboven getoonde klokcurve bijvoorbeeld vertegenwoordigt één standaarddeviatie van het gemiddelde het bereik tussen examenscores van 53 en 85.

Hoe lager de SD, hoe groter de curve en hoe minder uw gegevens worden verspreid, en omgekeerd.

Het is de moeite waard om de 68-95-99,7-regel te noemen die kan worden toegepast op elke normale verdelingscurve, wat betekent dat ongeveer 68% van uw gegevens binnen één SD van het gemiddelde wordt geplaatst, 95% binnen twee SD en 99,7% binnen drie SD.

Nu u de essentie kent, gaan we van theorie naar praktijk.

Beginnen

Laten we ter illustratie aannemen dat je de testscores van 200 studenten hebt en ze 'op een curve' wilt beoordelen, wat betekent dat de cijfers van de studenten worden gebaseerd op hun relatieve prestaties ten opzichte van de rest van de klas:

Stap #1: Vind het gemiddelde.

Meestal krijgt u vanaf het begin de gemiddelde en SD-waarden, maar als dat niet het geval is, kunt u deze waarden eenvoudig in slechts een paar eenvoudige stappen berekenen. Laten we eerst het gemiddelde aanpakken.

Aangezien het gemiddelde de gemiddelde waarde van een steekproef of populatie van gegevens aangeeft, kunt u uw standaardmeting vinden met de functie GEMIDDELDE.

Typ de volgende formule in een lege cel (F1 in dit voorbeeld) naast uw werkelijke gegevens (kolommen A en B) om het gemiddelde van de examenscores in de dataset te berekenen:

1	=GEMIDDELDE(B2:B201)

Een korte opmerking: vaker wel dan niet, moet u mogelijk de formule-uitvoer naar boven afronden. Om dat te doen, wikkelt u het eenvoudig als volgt in de ROUND-functie:

1	=ROND(GEMIDDELDE(B2:B201),0)

Stap 2: Zoek de standaarddeviatie.

Eén af, één te gaan. Gelukkig heeft Excel een speciale functie om al het vuile werk van het vinden van de standaarddeviatie voor je te doen:

1	=STDEV.P(B2:B201)

Nogmaals, de formule kiest alle waarden uit het opgegeven celbereik (B2:B201) en berekent de standaarddeviatie - vergeet niet om ook de uitvoer naar boven af ​​te ronden.

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

Stap 3: Stel de x-aswaarden voor de curve in.

Kortom, de grafiek bestaat uit een enorm aantal intervallen (zie ze als stappen) die zijn samengevoegd met een lijn om een ​​vloeiende curve te creëren.

In ons geval worden de x-aswaarden gebruikt om een ​​bepaalde examenscore te illustreren, terwijl de y-aswaarden ons vertellen hoe groot de kans is dat een student die score op het examen haalt.

Technisch gezien kunt u zoveel intervallen opnemen als u wilt - u kunt de overtollige gegevens later moeiteloos wissen door de schaal van de horizontale as te wijzigen. Zorg ervoor dat u een bereik kiest dat de drie standaarddeviaties bevat.

Laten we beginnen met tellen bij één (aangezien er geen enkele manier is waarop een student een negatieve examenscore kan krijgen) en helemaal tot 150 gaan - het maakt niet echt uit of het 150 of 1500 is - om nog een hulptafel in te stellen.

1. Kies een lege cel onder de grafiekgegevens (zoals E 4) en typ “1,” de waarde die het eerste interval definieert.
2. Navigeer naar de Huis tabblad.
3. In de Bewerken groep, kies "Vullen.”
4. Onder "Serie in," selecteer "Kolom.”
5. Voor "Stap waarde,type “1.” Deze waarde bepaalt de stappen die automatisch worden opgeteld totdat Excel het laatste interval bereikt.
6. Voor "Stopwaarde,” typ”150,” de waarde die staat voor het laatste interval, en klik op “OKE.”

Wonder boven wonder 149 cellen in kolom E (E5:E153) zijn gevuld met waarden van 2 tot 150.

OPMERKING: verberg de originele gegevenscellen niet zoals weergegeven op de schermafbeeldingen. Anders werkt de techniek niet.

Stap 4: Bereken de normale verdelingswaarden voor elke x-aswaarde.

Zoek nu de normale verdelingswaarden - de kans dat een student een bepaalde examenscore behaalt, weergegeven door een bepaalde x-aswaarde - voor elk van de intervallen. Gelukkig voor jou heeft Excel het werkpaard om al deze berekeningen voor je uit te voeren: de functie NORM.DIST.

Typ de volgende formule in de cel aan de rechterkant (F4) van uw eerste interval (E 4):

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

Hier is de gedecodeerde versie om u te helpen dienovereenkomstig aan te passen:

1	=NORM.VERD([het eerste interval],[het gemiddelde(absolute referentie)],[de standaarddeviatie(absolute referentie),FALSE)

Je vergrendelt de gemiddelde en SD-waarden zodat je de formule moeiteloos kunt uitvoeren voor de resterende intervallen (E5:E153).

Dubbelklik nu op de vulgreep om de formule naar de rest van de cellen te kopiëren (F5:F153).

Stap #5: Maak een spreidingsplot met vloeiende lijnen.

Eindelijk is het tijd om de belcurve te bouwen:

1. Selecteer een waarde in de hulptabel die de x- en y-aswaarden bevat (E4:F153).
2. Ga naar de Invoegen tabblad.
3. Klik op de "Spreidingsdiagram (X, Y) of bellendiagram invoegen" knop.
4. Kiezen "Verspreid met vloeiende lijnen.”

Stap #6: Stel de labeltabel in.

Technisch gezien heb je je belcurve. Maar het zou moeilijk te lezen zijn omdat er geen gegevens zijn die het beschrijven.

Laten we de normale verdeling informatiever maken door de labels toe te voegen die alle standaarddeviatiewaarden onder en boven het gemiddelde illustreren (u kunt ze in plaats daarvan ook gebruiken om de z-scores weer te geven).

Stel daarvoor nog een andere hulptabel als volgt in:

Kopieer eerst de gemiddelde waarde (F1) naast de corresponderende cel in kolom X-Waarde (I5).

Bereken vervolgens de standaarddeviatiewaarden onder het gemiddelde door deze eenvoudige formule in te voeren in: cel I4:

1

=I5-$F$2

Simpel gezegd, de formule trekt de som van de voorgaande standaarddeviatiewaarden af ​​van het gemiddelde. Sleep nu de vulgreep omhoog om de formule naar de resterende twee cellen te kopiëren (I2:I3).

Herhaal hetzelfde proces voor de standaarddeviaties boven het gemiddelde met behulp van de spiegelformule:

1

=I5+$F$2

Voer op dezelfde manier de formule uit voor de andere twee cellen (I7:I8).

Vul ten slotte de labelwaarden van de y-as in (J2:J8) met nullen zoals u wilt dat de gegevensmarkeringen op de horizontale as worden geplaatst.

Stap #7: Voeg de labelgegevens in de grafiek in.

Voeg nu alle gegevens toe die u hebt voorbereid. Klik met de rechtermuisknop op de kaartplot en kies “Selecteer Gegevens.”

Selecteer in het dialoogvenster dat verschijnt "Toevoegen.”

Markeer de respectieve cellenbereiken uit de helpertabel-I2:I8 voor "Serie X-waarden" en J2:J8 voor "Serie Y-waarden”-en klik op "OKE.”

Stap #8: Wijzig het grafiektype van de labelreeks.

Onze volgende stap is om het grafiektype van de nieuw toegevoegde reeks te wijzigen om de gegevensmarkeringen als stippen te laten verschijnen. Om dat te doen, klikt u met de rechtermuisknop op de kaartplot en selecteert u "Wijzig grafiektype.”

Ontwerp vervolgens een combografiek:

1. Navigeer naar de combinatie tabblad.
2. Voor Serie "Serie 2", verandering "Grafiektype" tot "Verstrooien.”
   • Let op: zorg ervoor dat “Serie1” blijft als “Verstrooien met vloeiende lijnen.” Soms verandert Excel het wanneer u een maakt combinatie Zorg er ook voor “Serie1” niet naar de secundaire as wordt gepusht - het selectievakje naast het diagramtype mag niet worden gemarkeerd.
3. Klik "Oke.”

Stap #9: Pas de schaal van de horizontale as aan.

Centreer de grafiek op de belcurve door de schaal van de horizontale as aan te passen. Klik met de rechtermuisknop op de horizontale as en kies “Formaat as” uit het menu.

Zodra het taakvenster verschijnt, doet u het volgende:

• Ga naar de Asopties tabblad.
• Stel de Minimale grenzen waarde aan “15.”
• Stel de Maximale grenzen waarde aan “125.”

U kunt het bereik van de asschaal naar eigen inzicht aanpassen, maar aangezien u de standaarddeviatiebereiken kent, stelt u de grenswaarden een beetje uit de buurt van elk van uw derde standaarddeviaties om de "staart" van de curve weer te geven.

Stap #10: Voeg de aangepaste gegevenslabels in en plaats ze.

Zorg ervoor dat u de aangepaste gegevenslabels toevoegt terwijl u uw diagram oppoetst. Klik eerst met de rechtermuisknop op een punt dat staat voor Serie "Serie 2" en selecteer "Voeg gegevenslabels toe.”

Vervang vervolgens de standaardlabels door degene die u eerder hebt ingesteld en plaats ze boven de gegevensmarkeringen.

1. Klik met de rechtermuisknop op een willekeurige Serie "Serie 2" gegevenslabel.
2. Selecteer "Gegevenslabels opmaken.”
3. Schakel in het taakvenster naar de Labelopties tabblad.
4. Controleer de "X-waarde" doos.
5. Haal het vinkje weg bij "Y-waarde" doos.
6. Onder "Labelpositie," Kiezen "Bovenstaand.”

U kunt nu ook de rasterlijnen verwijderen (klik er met de rechtermuisknop op > Verwijderen).

Stap #11: Kleur de gegevensmarkeringen opnieuw (optioneel).

Kleur ten slotte de stippen opnieuw zodat ze in uw grafiekstijl passen.

1. Klik met de rechtermuisknop op een willekeurige Serie "Serie 2" gegevenslabel.
2. Klik op de "Vullen" knop.
3. Kies je kleur uit het palet dat verschijnt.

Verwijder ook de randen rond de stippen:

1. Klik nogmaals met de rechtermuisknop op dezelfde gegevensmarkering en selecteer "Overzicht.”
2. Kiezen "Geen overzicht.”

Stap #12: Verticale lijnen toevoegen (optioneel).

Als laatste aanpassing kunt u verticale lijnen aan de grafiek toevoegen om de SD-waarden te benadrukken.

• Selecteer de kaartplot (op die manier worden de lijnen direct in de kaart ingevoegd).
• Ga naar de Invoegen tabblad.
• Klik op de "Vormen" knop.
• Kiezen "Lijn.”

Houd de ingedrukt "VERSCHUIVING" toets terwijl u de muis sleept om perfect verticale lijnen te tekenen van elke stip tot waar elke lijn de belcurve ontmoet.

Wijzig de titel van de grafiek en uw verbeterde belcurve toont uw waardevolle distributiegegevens.

En zo doe je het. U kunt nu elke dataset kiezen en een belcurve met normale verdeling maken door deze eenvoudige stappen te volgen!