deze tutorial zal laten zien hoe je een normale distributiebelcurve maakt in alle versies van Excel: 2007, 2010, 2013, 2016 en 2019.

in statistieken is een klokcurve (ook bekend als een standaard normale distributie of Gaussiaanse curve) een symmetrische grafiek die de neiging van gegevens om te cluster rond een centrum waarde, of gemiddelde, in een gegeven dataset illustreert.

de y-as vertegenwoordigt de relatieve waarschijnlijkheid dat een bepaalde waarde in de dataset voorkomt, terwijl de x-as de waarden zelf in de grafiek zet om een klokvormige kromme 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.

omdat Excel geen ingebouwde oplossingen te bieden heeft, moet u het zelf plotten. Daarom hebben we de Chart Creator Add-in ontwikkeld, een tool waarmee u geavanceerde Excel-diagrammen in slechts een paar klikken kunt bouwen.

in deze stapsgewijze zelfstudie leert u hoe u vanaf de basis een normale distributiebelcurve in Excel kunt maken:

om een Gaussiaanse curve te plotten, moet je twee dingen weten:

• het gemiddelde (ook wel de standaardmeting genoemd). Dit bepaalt het midden van de kromme—die op zijn beurt de positie van de kromme kenmerkt.
• de standaardafwijking (SD) van de metingen. Dit definieert de verspreiding van uw gegevens in de normale distributie—of in gewoon Engels, hoe breed de curve moet zijn. In de hierboven getoonde klokcurve bijvoorbeeld vertegenwoordigt één standaardafwijking van het gemiddelde het bereik tussen examenscores van 53 en 85.

hoe lager de SD, hoe hoger de curve en hoe minder uw gegevens zullen worden verspreid, en vice versa.

het is de moeite waard om de 68-95-99.7-regel te vermelden die kan worden toegepast op elke normale distributiecurve, wat betekent dat ongeveer 68% van uw gegevens binnen één SD verwijderd 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.

aan de slag

ter illustratie, laten we aannemen dat u de testscores van 200 studenten hebt en ze “op een curve” wilt rangschikken, wat betekent dat de cijfers van de studenten gebaseerd zullen zijn op hun relatieve prestaties ten opzichte van de rest van de klas.:

Stap # 1: Vind Het gemiddelde.

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

aangezien het gemiddelde De gemiddelde waarde van een steekproef of populatie van gegevens aangeeft, kunt u uw standaardmeting vinden met behulp van 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:

een snelle opmerking: vaker wel dan niet, moet u de formule-uitvoer afronden. Om dat te doen, wikkel het gewoon in de ronde functie als volgt:

naar boven af Stap # 2: Zoek de standaardafwijking.

Eén klaar, nog één te gaan. Gelukkig heeft Excel een speciale functie om al het vuile werk van het vinden van de standaardafwijking voor u te doen:

nogmaals, de formule pakt alle waarden uit het opgegeven celbereik (B2:B201) en berekent de standaarddeviatie—vergeet niet om ook de uitvoer af te ronden.

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

in principe bestaat de grafiek uit een groot aantal intervallen (zie ze als stappen) die samen met een lijn worden samengevoegd om een gladde kromme te creëren.

in ons geval zullen de x-as waarden worden gebruikt om een bepaalde examenscore te illustreren, terwijl de y-as waarden ONS de kans geven dat een student die score op het examen krijgt.

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 aan te passen. Zorg er wel voor dat je een bereik kiest dat de drie standaarddeviaties omvat.

laten we een telling bij één beginnen (omdat het onmogelijk is dat een student een negatieve examenscore kan krijgen) en helemaal tot 150 gaan—het maakt niet echt uit of het 150 of 1500 is—om een andere helptafel op te zetten.

1. Kies een lege cel onder de grafiekgegevens (zoals E4) en typ “1”, de waarde die het eerste interval definieert.
2. Navigeer naar het tabblad Home.
3. kies in de Bewerkingsgroep “vullen.”
4. onder” Series in, “select” kolom.”
5. Voor “Step value, “type” 1.”Deze waarde bepaalt de stappen die automatisch worden opgeteld totdat Excel Het Laatste interval bereikt.
6. voor” Stop value, “typ” 150, “de waarde die staat voor het laatste interval, en klik op” OK.”

op miraculeuze wijze zijn 149 cellen in kolom E (E5:E153) gevuld met de waarden van 2 tot 150.

noot: Verberg de originele gegevenscellen niet zoals weergegeven op de schermafbeeldingen. Anders zal de techniek niet werken.

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

zoek nu de normale verdelingswaarden—de kans dat een student een bepaalde examenscore krijgt, vertegenwoordigd door een bepaalde X-as—waarde-voor elk van de intervallen. Gelukkig voor u heeft Excel het werkpaard om al deze berekeningen voor u te doen: de NORM.DIST functie.

Typ de volgende formule in de cel rechts (F4) van uw eerste interval (E4):

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

u vergrendelt de gemiddelde en SD waarden zodat u moeiteloos de formule 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 scatter plot met vloeiende lijnen.

eindelijk is de tijd gekomen om de klokcurve te bouwen:

1. selecteer een waarde in de Hulptabel die de x – en y-aswaarden bevat (E4: F153).
2. Ga naar het tabblad Invoegen.
3. klik op de” Insert Scatter (X, Y) or Bubble Chart ” knop.
4. kies ” verstrooien met vloeiende lijnen.”

Stap #6: Stel de labeltabel in.

technisch gezien heeft u uw belcurve. Maar het zou moeilijk zijn om te lezen als het ontbreekt aan gegevens beschrijven.

laten we de normale verdeling informeler maken door de labels toe te voegen die alle standaarddeviatiewaarden onder en boven het gemiddelde illustreren (u kunt ze ook gebruiken om de Z-scores te tonen).

hiervoor moet nog een andere Hulptabel als volgt worden opgesteld:

kopieer eerst de gemiddelde waarde (F1) naast de overeenkomstige cel in kolom X-waarde (I5).

bereken vervolgens de standaarddeviatiewaarden Onder het gemiddelde door deze eenvoudige formule in cel I4 in te voeren:

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

herhaal hetzelfde proces voor de standaardafwijkingen boven het gemiddelde met behulp van de spiegelformule:

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

vul tot slot de Y-as label waarden (J2:J8) met nullen als u wilt dat de gegevensmarkeringen op de horizontale as worden geplaatst.

Stap # 7: Plaats het label gegevens in de grafiek.

Voeg nu alle gegevens toe die u hebt voorbereid. Klik met de rechtermuisknop op de grafiek plot en kies “Select Data.”

in het dialoogvenster dat verschijnt, selecteer “Toevoegen.”

markeer de respectieve celbereiken uit de helpertabel-I2:I8 voor “Series X values” en J2: J8 voor “Series Y values”—en klik op “OK.”

Stap # 8: Wijzig het diagramtype van de labelreeks.

onze volgende stap is om het diagramtype van de nieuw toegevoegde serie te wijzigen om de gegevensmarkeringen als punten te laten verschijnen. Om dat te doen, klik met de rechtermuisknop op de grafiek plot en selecteer “Change diagramtype.”

:

1. Ga naar het tabblad Combo.
2. voor serie “Series2,” verander “Chart Type” in ” Scatter.”
   • Noot: Zorg ervoor dat ” Series1 “blijft als” Scatter met vloeiende lijnen.”Soms zal Excel het wijzigen wanneer u een Combo maakt, zorg er ook voor dat “Series1” niet naar de secundaire as wordt geduwd—het selectievakje naast het diagramtype mag niet worden gemarkeerd.
3. klik op ” OK.”

stap # 9: Wijzig de horizontale as schaal.

centreer de grafiek op de klokcurve door de schaal van de horizontale as aan te passen. Klik met de rechtermuisknop op de horizontale as en kies “Format Axis” uit het menu.

zodra het taakvenster verschijnt, gaat u als volgt te werk:

• Ga naar het tabblad Asopties.
• Stel de Minimumgrenswaarde in op ” 15.”
• Stel de Maximumgrenswaarde in op ” 125.”

u kunt het bereik van de asschaal aanpassen zoals u dat wilt, maar omdat u de standaarddeviatiebereiken kent, stelt u de grenswaarden een beetje weg van elk van uw derde standaarddeviaties in om de “staart” van de curve weer te geven.

stap # 10: Voeg de aangepaste gegevenslabels in en plaats deze.

als u uw grafiek polijst, moet u de aangepaste gegevenslabels toevoegen. Klik eerst met de rechtermuisknop op een punt die serie “Series2” vertegenwoordigt en selecteer “Gegevenslabels toevoegen.”

in. vervang vervolgens de standaardlabels door de labels die u eerder hebt ingesteld en plaats ze boven de gegevensmarkeringen.

1. Klik met de rechtermuisknop op een gegevenslabel van de serie “Series2”.
2. Selecteer ” Gegevenslabels Opmaken.”
3. schakel in het taakvenster naar het tabblad Labelopties.
4. Vink het vakje “X-waarde” aan.
5. Schakel het selectievakje “Y-waarde” uit.
6. onder” Labelpositie”, kies ” hierboven.”

ook kunt u nu de rasterlijnen verwijderen (Klik er met de rechtermuisknop op > verwijderen).

stap # 11: kleur de gegevensmarkeringen opnieuw (optioneel).

tenslotte, herkleur de stippen om ze te helpen passen in uw grafiek stijl.

1. Klik met de rechtermuisknop op een gegevenslabel van de serie “Series2”.
2. klik op de knop “vullen”.
3. Kies uw kleur uit het palet dat verschijnt.

verwijder ook de randen rond de puntjes:

1. Klik met de rechtermuisknop op dezelfde data marker opnieuw en selecteer ” Outline.”
2. Kies ” Geen Omtrek.”

stap #12: verticale lijnen toevoegen (optioneel).

als laatste aanpassing kunt u verticale lijnen aan het diagram toevoegen om de SD-waarden te benadrukken.

• Selecteer de grafiek plot (op die manier zullen de regels direct in de grafiek worden ingevoegd).
• Ga naar het tabblad Invoegen.
• klik op de” Shapes ” knop.
• Kies ” Line.”

Houd de “SHIFT” toets ingedrukt terwijl u de muis sleept om perfect verticale lijnen te tekenen van elke punt naar waar elke lijn de belcurve ontmoet.

Wijzig de titel van het diagram, en uw verbeterde belcurve is klaar-uw waardevolle distributiegegevens tonen.

en zo doe je het. U kunt nu elke dataset kiezen en een normale distributiebelcurve maken na deze eenvoudige stappen!