Een normale distributieklokcurve maken in Excel

In deze tutorial wordt gedemonstreerd hoe je een normale distributieklokcurve maakt in alle versies van Excel: 2007, 2010, 2013 , 2016 en 2019.

In statistieken is een belcurve (ook bekend als een standaard normale verdeling of Gaussiaanse curve) een symmetrische grafiek die de neiging van gegevens om te clusteren illustreert rond een middelste waarde, of gemiddelde, in een bepaalde dataset.

De y-as geeft de relatieve kans weer dat een bepaalde waarde in de dataset voorkomt, terwijl de x-as de waarden zelf in de grafiek plot om een klokvormige curve, 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 nauwkeuriger moet worden onderzocht.

Aangezien Excel geen ingebouwde oplossingen heeft, moet u het zelf uitzetten. Daarom hebben we de invoegtoepassing Chart Creator ontwikkeld, een tool waarmee u met een paar klikken geavanceerde Excel-grafieken kunt maken.

In deze stap- voor stapsgewijze zelfstudie leert u hoe u vanaf de basis een normale verdelingsklok in Excel kunt maken:

Aan een Gauss-curve uitzetten, moet u twee dingen weten:

• Het gemiddelde (ook bekend als de standaardmeting). Dit bepaalt het midden 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 distributie – of in gewoon Engels, hoe breed de curve moet zijn. In de bovenstaande klokcurve bijvoorbeeld vertegenwoordigt één standaarddeviatie van het gemiddelde het bereik tussen examenscores van 53 en 85.

Hoe lager de SD, hoe hoger de curve en hoe kleiner uw gegevens worden verspreid en vice versa.

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 wordt geplaatst binnen één SD verwijderd van het gemiddelde, 95% binnen twee SD en 99,7% binnen drie SD.

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

Aan de slag

Laten we ter illustratie aannemen dat u de testscores van 200 studenten heeft en deze wilt beoordelen “op een curve , “Wat betekent dat de cijfers van de leerlingen gebaseerd zijn op hun relatieve prestaties ten opzichte van de rest van de klas:

Stap # 1: zoek het gemiddelde.

Meestal krijgt u het gemiddelde en SD-waarden vanaf het begin, 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 gegevensset te berekenen:

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

Stap 2: Vind de standaarddeviatie.

Een naar beneden, een te gaan. Gelukkig heeft Excel een speciale functie om al het vuile werk te doen om de standaarddeviatie voor u te vinden:

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

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

In wezen bestaat de grafiek uit een enorm aantal intervallen ( beschouw ze als stappen) samengevoegd met een lijn om een vloeiende curve te creëren.

In ons geval worden de x-aswaarden gebruikt om een bepaalde onderzoeksscore te illustreren, terwijl de y-aswaarden het aangeven ons de kans dat een student die score op het examen behaalt.

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 er wel voor dat u een bereik kiest waarin de drie standaarddeviaties zijn opgenomen.

Laten we beginnen met tellen bij één (aangezien een student op geen enkele manier een negatieve examenscore kan krijgen) en helemaal doorgaan naar 150 (het maakt niet echt uit of het 150 of 1500 is) om nog een helptabel in te stellen.

1. Kies een lege cel onder de diagramgegevens (zoals E4) en typ 1, de waarde die het eerste interval definieert.
2. Navigeer naar het tabblad Home.
3. Kies in de groep Bewerken Vullen.
4. Onder Reeks in, “Selecteer” Kolom “.
5. Typ voor” Stapwaarde “” 1 “. Deze waarde bepaalt de verhogingen die automatisch zullen worden opgeteld totdat Excel het laatste interval bereikt.
6. Typ voor “Stopwaarde” “150”, de waarde die staat voor het laatste interval, en klik op “OK. ”

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

OPMERKING: verberg de originele datacellen niet zoals getoond op de screenshots. Anders werkt de techniek niet.

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

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

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

Hier is de gedecodeerde versie om u te helpen bij het aanpassen:

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

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

Stap # 5: Maak een scatterplot met vloeiende lijnen.

Eindelijk is het tijd om de belcurve op te bouwen:

1. Selecteer een waarde in de helper-tabel die de x- en y-aswaarden bevat (E4: F153).
2. Ga naar het tabblad Invoegen.
3. Klik op de knop Spreidingsdiagram (X, Y) of bellengrafiek invoegen.
4. Kies Verspreiding met vloeiende lijnen.

Stap # 6: Stel de labeltabel in.

Technisch gezien hebben uw belcurve. Maar het zou moeilijk te lezen zijn omdat er geen gegevens zijn die deze beschrijven.

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

Stel daarvoor nog een andere helptabel in als volgt:

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 cel I4 in te voeren:

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:

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

Vul tot slot de y-as labelwaarden (J2: J8) met nullen zoals je wilt dat de gegevensmarkeringen op de horizontale as.

Stap # 7: voeg de labelgegevens in het diagram in.

Voeg nu alle gegevens toe die u heeft voorbereid. Klik met de rechtermuisknop op de grafiekplot en kies “Gegevens selecteren”.

Selecteer in het dialoogvenster dat verschijnt ” Toevoegen. ”

Markeer de respectieve celbereiken in de helptabel – I2: I8 voor” Reeks X-waarden “en J2 : J8 voor “Reeks Y-waarden” – en klik op “OK”.

Stap # 8: Wijzig het diagramtype van de labelreeks.

Onze volgende stap is het wijzigen van het grafiektype van de nieuw toegevoegde reeks om de gegevensmarkeringen als stippen te laten verschijnen. Klik hiervoor met de rechtermuisknop op de grafiekplot en selecteer Wijzigen Diagramtype. ”

Ontwerp vervolgens een combinatiegrafiek:

1. Navigeer naar de Combo-tab.
2. Voor serie “Series2” wijzigt u “Chart Type” in “Scatter”.
   • Opmerking: zorg ervoor dat “Series1” blijft staan als “Scatter with Smooth Lines”. Soms verandert Excel het 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 schaal van de horizontale as.

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 “Format Axis” in het menu.

Zodra het taakvenster verschijnt, doet u het volgende:

• Ga naar het tabblad Asopties.
• Stel de waarde voor Minimum Bounds in op “15”.
• Zet de waarde voor Maximum Bounds op “125”.

U kunt het bereik van de asschaal naar eigen inzicht aanpassen, maar aangezien u de standaarddeviatiebereiken kent, stelt u de waarden voor Bounds een beetje weg van elk van uw derde standaarddeviaties om de “staart” van de curve te tonen .

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

Zorg ervoor dat u uw diagram bijwerkt om de aangepaste gegevenslabels toe te voegen. Klik eerst met de rechtermuisknop op een willekeurige punt die serie “Series2” vertegenwoordigt en selecteer “Gegevenslabels toevoegen”.

Vervang vervolgens de standaardlabels met de labels die u eerder heeft ingesteld en plaats ze boven de gegevensmarkeringen.

1. Klik met de rechtermuisknop op een gegevenslabel van Series “Series2”.
2. Selecteer “Formaat Gegevenslabels. ”
3. Schakel in het taakvenster naar het tabblad Labelopties.
4. Vink het vakje” X-waarde “aan.
5. Schakel het selectievakje” Y-waarde ”.
6. Kies onder Labelpositie voor Boven.

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

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

Tenslotte, kleur de stippen opnieuw om ze in uw diagramstijl te laten passen.

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

Ook r verwijder de randen rond de stippen:

1. Klik nogmaals met de rechtermuisknop op dezelfde gegevensmarkering en selecteer “Omtrek”.
2. Kies “Geen omtrek”.

Stap # 12: voeg verticale lijnen toe (optioneel).

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

• Selecteer de grafiekplot (op die manier worden de lijnen rechtstreeks in de grafiek ingevoegd).
• Ga naar het tabblad Invoegen.
• Klik op de knop “Vormen”.
• Kies “Lijn”.

Houd de “SHIFT” -toets ingedrukt terwijl door met de muis te slepen om perfect verticale lijnen te tekenen vanaf elke punt tot waar elke lijn de belcurve ontmoet.

Verander de kaarttitel , en je verbeterde belcurve is klaar – laat je waardevolle distributiegegevens zien.

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