Jimmy in Huntsville wil een belcurve uitzetten met de gemiddelde scores van verschillende mensen. Toen Jimmy de vraag stelde tijdens mijn Power Excel Seminar, dacht ik terug aan een van mijn meer populaire video's op YouTube.
In Podcast 1665 - Maak een belcurve in Excel, leg ik uit dat om een belcurve te maken, je het gemiddelde en de standaarddeviatie moet berekenen. Vervolgens genereer ik 30 punten langs de x-as die een hypothetische populatie van mensen omspannen. In die video heb ik dat gegenereerd van -3 standaarddeviaties tot + 3 standaarddeviaties rond een gemiddelde.
Als je bijvoorbeeld een gemiddelde hebt van 50 en een standaarddeviatie van 10, zou ik een x-as maken die loopt van 70 tot 130. De hoogte van elk punt wordt berekend met =NORM.DIST(x,mean,standard deviation,False).
In de bovenstaande afbeelding zijn de cijfers in A10: A40 in wezen "nepgegevenspunten". Ik genereer 31 nummers om een mooie vloeiende curve te creëren. Als ik slechts 7 datapunten had gebruikt, zou de curve er als volgt uitzien:
Voor de dataset van Jimmy zijn de feitelijke gemiddelde scores van zijn werknemers in wezen punten langs een x-as. Om ze op een belcurve te passen, moet u voor elke werknemer de hoogte of Y-waarde berekenen.
Volg deze stappen:
-
Sorteer de gegevens zodat de scores van laag naar hoog lijken.
Sorteer de gegevens - Bereken een gemiddelde met behulp van de GEMIDDELDE functie.
- Bereken een standaarddeviatie met behulp van de STDEV-functie.
-
Bereken de Y-waarde rechts van de scores met
=NORM.DIST(L2,$H$2,$H$3,FALSE). De Y-waarde genereert een hoogte van het punt van elke persoon langs de belcurve. De NORM.VERD-functie zorgt ervoor dat mensen dichtbij het gemiddelde op een hogere locatie worden geplot dan mensen dichtbij de boven- of onderkant.
Genereer een reeks Y-waarden. - Selecteer uw gegevens in L1: M15
-
Onlangs verscheen er een vreemde bug in Excel, dus om succes te garanderen, kiest u Alle grafieken op het tabblad Invoegen.
De dialoogstarter brengt u naar alle diagramtypen Klik in het dialoogvenster Grafiek invoegen op het tabblad Alle grafieken. Klik op XY (Scatter) aan de linkerkant. Kies het tweede pictogram bovenaan. Kies het voorbeeld aan de rechterkant.
Vier klikken om de grafiek te kiezen Uw eerste belcurve ziet er als volgt uit:
De belcurve
Volg deze stappen om de belcurve op te ruimen:
- Klik op de titel en druk op de Delete-toets.
- Dubbelklik op een nummer langs de Y-as onder aan het diagram. Het paneel Formaat-as verschijnt.
-
Typ nieuwe waarden voor Minimum en Maximum. Het bereik hier moet net breed genoeg zijn om iedereen op de kaart te laten zien. Ik gebruikte 50 tot 90.
Verander het minimum en maximum - Maak de grafiek breder door de rand van de grafiek te slepen.
- Klik op het + -pictogram rechts van het diagram en selecteer Gegevenslabels. Maak je geen zorgen dat de labels nog niet kloppen.
- Dubbelklik op een label om het paneel Labels opmaken te openen.
- Er zijn vier pictogrammen aan de bovenkant van het paneel. Kies het pictogram dat een kolomdiagram weergeeft.
- Klik op de pijl naast Labelopties om dat deel van het paneel uit te vouwen.
- Kies Waarde uit cellen. Er verschijnt een dialoogvenster waarin u wordt gevraagd naar de locatie van de labels. Kies de namen in K2: K15.
-
Deselecteer Y-waarden in het deelvenster Gegevenslabel opmaken. Het is belangrijk om stap 15 af te ronden voordat u stap 16 uitvoert, anders verwijdert u per ongeluk de labels.
Haal de labels van de cellen met namen.
Opmerking
De mogelijkheid om labels uit cellen te halen is toegevoegd in Excel 2013. Als u Excel 2010 of eerder gebruikt, download dan de XY Chart Labeler-invoegtoepassing van Rob Bovey. (Google om het te vinden).
Kijk op dit punt of u diagramlabels hebt die tegen elkaar crashen. Volg deze stappen zorgvuldig om ze op te lossen.
- Klik één keer op een diagramlabel. Dit selecteert alle labels.
- Klik één keer op een van de labels bovenop een ander label om alleen dat label te selecteren.
- Beweeg over verschillende delen van het label totdat je een vierpuntige pijl ziet. Klik en sleep het label naar een nieuwe positie.
-
Als u eenmaal slechts één label heeft geselecteerd, kunt u één keer klikken op een ander label om dat label te selecteren. Herhaal voor alle andere labels die moeten worden verplaatst.
De laatste grafiek
Bekijk video
Videotranscriptie
Leer Excel van Podcast, aflevering 2217: plaats mensen op een belcurve.
Hé, welkom terug bij de netcast, ik ben Bill Jelen. De vraag van vandaag, van Jimmy tijdens mijn seminar in Huntsville, Alabama. Jimmy heeft gegevens, hij wil deze gegevens samenvatten en de resultaten vervolgens uitzetten op een belcurve.
Oké? Nu is een van mijn meest populaire video's op YouTube deze: nummer 1663, Maak een belcurve in Excel. En gegeven een gemiddelde en een standaarddeviatie, bedacht ik de lage, die 3 keer de standaarddeviatie minder is dan het gemiddelde, en de hoge - 3 keer de standaarddeviatie meer dan het gemiddelde - waar de kloof is - en een reeks X-waarden hier, en om de hoogte te berekenen, gebruikt u deze functie: = NORM.VERD van de X-waarde, het gemiddelde en de standaarddeviatie, komma false (= NORM.VERD (A10, $ B $ 2, $ B $ 3, FALSE)).
En als je erover nadenkt, deze video gebruikt hier eigenlijk gewoon een reeks nep-X-waarden om een mooie curve te krijgen. En we gaan hier hetzelfde concept gebruiken, maar in plaats van valse X-waarden, zullen we de mensen hier beneden hebben en dan zal de hoogte exact dezelfde formule zijn. Oké.
Dus nu wilde Jimmy een draaitabel maken. Dus we invoegen, draaitabel, plaatsen het hier op dit blad en klikken op OK. Mensen aan de linkerkant en vervolgens hun gemiddelde score. Oké, dus het begint met Som van de score, ik dubbelklik daar en verander dat in een gemiddelde. Super goed. Nu, helemaal onderaan, wil ik geen eindtotaal - klik met de rechtermuisknop en verwijder eindtotaal - en we willen deze mensen van hoog naar laag rangschikken en dit is gemakkelijk te doen in een draaitabel. Gegevens van A tot Z - uitstekend. Oké. Nu gaan we precies hetzelfde doen als in Podcast 1663, en dat is het berekenen van een gemiddelde en een standaarddeviatie. Het gemiddelde is dus een gemiddelde van deze scores en is dan gelijk aan de standaarddeviatie van die scores. Oké. Nu ik dat weet, kan ik mijn y-waarde creëren.
Oké, dus een paar dingen die we hier gaan doen. Ten eerste kun je geen draaitabel - een spreidingsdiagram - maken van een draaitabel. Dus ik ga al deze gegevens kopiëren en ik ga dat doen met = D2. Merk op dat ik voorzichtig ben om de muis of de pijltjestoetsen niet te gebruiken om deze aan te wijzen. En dus hebben we hier onze waarden. Dit worden X-waarden, de Y-waarde wordt = NORM.VERD, hier is de x-waarde, komma, voor het gemiddelde, dat getal, ik zal op F4 drukken om dat vast te zetten; voor de standaarddeviatie is dit dit getal, druk nogmaals op F4 om dat vast te zetten, en cumulatief FALSE. (= NORM.VERD (K2, $ H $ 2, $ H $ 3, FALSE)) En we dubbelklikken om dat naar beneden te kopiëren. Oké. En kies dan niet de labels,Kies gewoon de XY en we zullen een spreidingsdiagram met lijnen invoegen - u kunt ofwel het diagram met gebogen lijnen of een beetje rechte lijnen kiezen. Hier ga ik met gebogen lijnen zoals deze. En we hebben nu al onze mensen op een belcurve geplaatst.
Oké. Nu, sommige dingen - sommige dingen die opmaaktype zijn - gaan we hier doen: Ten eerste, dubbelklik hier langs de schaal en het lijkt erop dat ons laagste getal waarschijnlijk ergens rond de 50 ligt - dus ik zal stel een minimum van 50 in - en ons grootste aantal - ons grootste aantal - is 88 - dus ik stel een maximum van 90 in. Oké. En nu moeten we deze punten benoemen. Als u Excel 2013 of nieuwer gebruikt, is dit eenvoudig te doen; maar als je in een oudere versie van Excel zit, zul je terug moeten gaan en Rob Bovey's Chart Labeler add-in moeten gebruiken om ervoor te zorgen dat deze puntlabels van een plaats komen die niet in de grafiek staat. Oké, dus we beginnen hier. We gaan gegevenslabels toevoegen en het voegt cijfers toe en ze zien er vreselijk uit. Ik kom hier en zeg dat ik meer opties wil, labelopties,en ik wil de waarde uit cellen halen - Waarde uit cellen. Oké? Dus het celbereik is daar, klik op OK. Zeer belangrijk om Waarde uit cellen te gebruiken voordat ik de Y-waarde uitschakel. Het begint er goed uit te zien. Ik kom er vanaf. Nu, de hele sleutel hier - omdat je sommige mensen hebt die elkaar overschrijven - is om te proberen de kaart zo groot mogelijk te maken. We hebben daar geen rubriek nodig. Waarom? Verwijder dat gewoon. En ik zie nog steeds dat Kelly en Lou en Andy en Flo bijna op dezelfde plek zijn; Jared en … Oké. Dus nu gaat dit frustrerend zijn - deze overlappen elkaar. Maar als we op een label klikken, hebben we alle labels geselecteerd en vervolgens opnieuw op een label geklikt, en we selecteren slechts één label. Oké? Dus nu. heel voorzichtig. probeer en klik op Andy, en sleep Andy gewoon naar links.Het lijkt erop dat Jared en Ike samen zijn, dus nu ik in de modus voor het selecteren van één label ben, is het gemakkelijker. En dan Kelly en Lou, sleep ze zo omhoog. Misschien is er een betere plek die Lou niet overrompelt, of zelfs, zoals, hier kan ik het naar beide kanten slepen. Oké, dus, wat hebben we? We zijn begonnen met een heleboel gegevens, hebben een draaitabel gemaakt, de gemiddelde en standaarddeviatie berekend, waardoor we alleen de hoogte kunnen achterhalen - de Y-positie voor elk van die scores, en de hoogte daarvan, hopelijk, we zullen mensen zo in een mooie paraboolvormige klokkromme krijgen.Oké, dus, wat hebben we? We zijn begonnen met een heleboel gegevens, hebben een draaitabel gemaakt, de gemiddelde en standaarddeviatie berekend, waardoor we alleen de hoogte kunnen achterhalen - de Y-positie voor elk van die scores, en de hoogte daarvan, hopelijk, we zullen mensen zo in een mooie paraboolvormige klokkromme krijgen.Oké, dus, wat hebben we? We zijn begonnen met een heleboel gegevens, hebben een draaitabel gemaakt, de gemiddelde en standaarddeviatie berekend, waardoor we alleen de hoogte kunnen achterhalen - de Y-positie voor elk van die scores, en de hoogte daarvan, hopelijk, we zullen mensen zo in een mooie paraboolvormige klokkromme krijgen.
Ik hou van deze vraag van Jimmy, deze vraag staat niet in dit boek, maar hij zal er wel zijn in de volgende keer dat ik dit boek schrijf. Ik moet dit toevoegen - het is een cool verzoek en een coole truc. Bell curves zijn erg populair in Excel.
Maar kijk eens naar mijn boek, LIVe, The 54 Greatest Excel Tips of All Time.
Oké, afsluiting van deze aflevering: Jimmy uit Huntsville, wil mensen regelen op een belcurve. Dus we gebruiken een draaitabel om de gemiddelde score te berekenen, sorteren de draaitabellen op de scores - hoog naar laag gerangschikt - verwijderen het eindtotaal onderaan - dit worden in wezen de X-waarden - en dan aan de zijkant, bereken het gemiddelde en de standaarddeviatie van die scores en gebruik formules om de gegevens van de draaitabel naar een nieuw bereik te kopiëren, omdat je geen XY-diagram kunt hebben dat een draaitabel kruist. Bereken een y-waarde voor elke persoon met = NORM.VERD van hun x-waarde, het gemiddelde, de standaarddeviatie, komma FALSE; maak een XY-spreidingsdiagram met vloeiende lijnen - als u een Excel 2010 of eerder bent, gaat u de invoegtoepassing Chart Labeler van Ron Bovey gebruiken. Ik laat je dat googelen omdat,in het geval dat Rob zijn URL verandert, wil ik hier niet de verkeerde URL. In Excel 2013, had Gegevenslabels, Van cellen, specificeer de namen en vervolgens enkele aanpassingen - verander de schaal langs de onderkant, ik verander ze in en Max en verplaats vervolgens de labels die elkaar overschrijden.
Gebruik de URL in de YouTube-beschrijving om de werkmap van de video van vandaag te downloaden. Ik wil Jimmy bedanken voor deze geweldige vraag in Huntsville, en ik wil je bedanken voor het langskomen. Ik zie je de volgende keer voor nog een netcast van.
Download Excel-bestand
Om het Excel-bestand te downloaden: place-people-on-bell-curve.xlsx
Met dank aan Jimmy in Huntsville voor de vraag van vandaag!
Excel-gedachte van de dag
Ik heb mijn Excel Master-vrienden om advies over Excel gevraagd. De gedachte van vandaag om na te denken:
"Als je Excel de afgelopen maand in de handmatige herberekeningsmodus hebt gezet, is het tijd voor power pivot (je hebt de handmatige modus nooit meer nodig)"
Rob Collie
