Jimmy i Huntsville ønsker å tegne en bjellekurve som viser gjennomsnittspoengene til flere personer. Da Jimmy stilte spørsmålet under Power Excel-seminaret mitt, tenkte jeg tilbake på en av mine mer populære videoer på YouTube.
I Podcast 1665 - Opprett en Bell Curve i Excel, forklarer jeg at for å lage en bellkurve, må du beregne middel- og standardavvik. Deretter genererer jeg 30 poeng langs x-aksen som strekker seg over en hypotetisk befolkning av mennesker. I den videoen genererte jeg det som spenner fra -3 standardavvik til + 3 standardavvik rundt et gjennomsnitt.
Hvis du for eksempel har et gjennomsnitt på 50 og et standardavvik på 10, vil jeg lage en x-akse som gikk fra 70 til 130. Høyden på hvert punkt beregnes ved hjelp av =NORM.DIST(x,mean,standard deviation,False).
På bildet over er tallene i A10: A40 i det vesentlige "falske datapunkter". Jeg genererer 31 tall for å lage en fin jevn kurve. Hvis jeg bare hadde brukt 7 datapunkter, ville kurven se slik ut:
For Jimmys datasett er de faktiske gjennomsnittlige poengene til hans ansatte i hovedsak poeng langs en x-akse. For å passe dem på en bjellekurve, må du finne ut høyden eller Y-verdien for hver ansatt.
Følg disse trinnene:
-
Sorter dataene slik at poengene vises lavest til høyest.
Sorter dataene - Beregn et gjennomsnitt ved hjelp av AVERAGE-funksjonen.
- Beregn et standardavvik ved hjelp av STDEV-funksjonen.
-
Beregn Y-verdien til høyre for poengene ved hjelp av
=NORM.DIST(L2,$H$2,$H$3,FALSE). Y-verdien vil generere en høyde på hver persons punkt langs bjelkekurven. NORM.DIST-funksjonen tar seg av å plotte mennesker nær gjennomsnittet på et høyere sted enn folk nær toppen eller bunnen.
Generer en serie Y-verdier. - Velg dataene dine i L1: M15
-
En merkelig feil begynte nylig å vises i Excel, så for å sikre suksess, velg Alle diagrammer i Sett inn-fanen.
Dialogboksen starter deg til alle diagramtyper I dialogboksen Sett inn diagram klikker du kategorien Alle diagrammer. Klikk på XY (Scatter) til venstre. Velg det andre ikonet øverst. Velg forhåndsvisning til høyre.
Fire klikk for å velge diagrammet Den første bjellekurven din vil se slik ut:
Klokkekurven
Følg disse trinnene for å rydde opp bjellekurven:
- Klikk på tittelen og trykk på Slett-tasten.
- Dobbeltklikk på et hvilket som helst tall langs Y-aksen nederst i diagrammet. Panelet Format Axis vises.
-
Skriv inn nye verdier for Minimum og Maximum. Området her skal være akkurat bredt nok til å vise alle på diagrammet. Jeg brukte 50 til 90.
Endre minimum og maksimum - Gjør diagrammet bredere ved å dra kanten av diagrammet.
- Klikk på + ikonet til høyre for diagrammet, og velg Data Labels. Ikke bekymre deg for at etikettene ikke gir mening ennå.
- Dobbeltklikk på en etikett for å åpne Format Labels-panelet.
- Det er fire ikoner øverst på panelet. Velg ikonet som viser et kolonnediagram.
- Klikk på pilen ved siden av Etikettalternativer for å utvide den delen av panelet.
- Velg verdi fra celler. En dialogboks vises der du blir bedt om plassering av etikettene. Velg navnene i K2: K15.
-
Fortsett å velge Y-verdier i Format Data Label-panelet. Det er viktig å fullføre trinn 15 før du utfører trinn 16, ellers fjerner du etikettene utilsiktet.
Få etikettene fra cellene som inneholder navn.
Merk
Muligheten til å få etiketter fra celler ble lagt til i Excel 2013. Hvis du bruker Excel 2010 eller tidligere, last ned XY Chart Labeler-tillegget fra Rob Bovey. (Google for å finne den).
På dette punktet, se om du har noen diagrammer som krasjer inn i hverandre. Følg disse trinnene nøye for å fikse dem.
- Enkeltklikk på en kartetikett. Dette velger alle etiketter.
- Enkeltklikk på en av etikettene som er oppå en annen etikett for å velge akkurat den etiketten.
- Hold markøren over forskjellige deler av etiketten til du ser en pil med fire hoder. Klikk og dra etiketten til en ny posisjon.
-
Når du bare har valgt en enkelt etikett, kan du enkeltklikke på en hvilken som helst annen etikett for å velge den etiketten. Gjenta for andre etiketter som må flyttes.
Det endelige diagrammet
Se på video
Videoutskrift
Lær Excel fra Podcast, Episode 2217: Place People on a Bell Curve.
Hei, velkommen tilbake til netcast, jeg er Bill Jelen. Dagens spørsmål fra Jimmy i seminaret mitt i Huntsville, Alabama. Jimmy har data, han vil oppsummere disse dataene og deretter plotte resultatene på en bjellekurve.
Ok? Nå er en av mine mest populære videoer på YouTube denne: nummer 1663, Create a Bell Curve in Excel. Og gitt et gjennomsnitt og et standardavvik, fant jeg ut det lave, som er 3 ganger standardavviket mindre enn gjennomsnittet, og det høye - 3 ganger standardavviket mer enn gjennomsnittet - der gapet er-- og en serie X-verdier her, og for å finne ut høyden, bruk denne funksjonen: = NORM.FORDELING av X-verdien, gjennomsnittet og standardavviket, komma falsk (= NORM.FORDELING (A10, $ B $ 2, $ B $ 3, FALSE)).
Og hvis du tenker på det, bruker denne videoen egentlig bare en serie falske X-verdier her for å få en fin kurve. Og vi skal bruke det samme konseptet her, men i stedet for falske X-verdier, skal vi faktisk ha folket her nede, og da vil høyden være akkurat den samme formelen. Ok.
Så nå ville Jimmy lage et pivottabell. Så vi setter inn, pivottabell, legger det her på dette arket, klikker OK. Folk på venstre side og deretter deres gjennomsnittlige poengsum. Greit, så det begynner med Sum of Score, jeg dobbeltklikker der og endrer det til et gjennomsnitt. Flott. Nå, helt nederst, vil jeg ikke ha et totalbeløp - høyreklikk og fjern total totalt - og vi vil ordne disse menneskene høyt til lavt, og dette er enkelt å gjøre i en pivottabell. Data, A til Z-- utmerket. Ok. Nå skal vi gjøre nøyaktig det samme som vi gjorde i Podcast 1663, og det beregner et gjennomsnitt og et standardavvik. Så gjennomsnittet er et gjennomsnitt av disse poengene, og tilsvarer deretter standardavviket til disse poengene. Ok. Nå som jeg vet det, kan jeg skape y-verdien min.
Greit, så et par ting vi skal gjøre her. For det første kan du ikke lage en pivottabell - et spredetabell - fra en pivottabell. Så jeg skal kopiere alle disse dataene over, og jeg skal bare gjøre det med = D2. Legg merke til at jeg er forsiktig så jeg ikke bruker musen eller piltastene for å peke på dem. Og så har vi våre verdier her. Disse blir X-verdier, Y-verdien blir = NORM.DIST, her er x-verdien, komma, for gjennomsnittet, det tallet, jeg trykker på F4 for å låse det ned; For standardavviket er det dette tallet, igjen, trykk F4 for å låse det ned og kumulativ FALSE. (= NORM.DIST (K2, $ H $ 2, $ H $ 3, FALSE)) Og vi dobbeltklikker for å kopiere det ned. Ok. Og velg ikke etikettene,Bare velg XY, så setter vi inn et spredetabell med linjer - du kan enten velge en med buede linjer eller litt rette linjer. Her skal jeg gå med buede linjer som dette. Og vi har nå alle våre mennesker plassert på en bjellekurve.
Ok. Nå, noen ting - noen formateringstype ting - vi skal gjøre her: Først dobbeltklikker du her nede på skalaen, og det ser ut til at vårt laveste tall sannsynligvis er et sted rundt 50 - så jeg skal angi min på 50-- og vårt største nummer-- vårt største nummer-- er 88-- så jeg setter maks 90. Ok. Og nå må vi merke disse punktene. Hvis du er i Excel 2013 eller nyere, er dette enkelt å gjøre; men hvis du er i en eldre versjon av Excel, må du gå tilbake og bruke Rob Bovey's Chart Labeler-tillegg for å få disse punktetikettene til å komme fra et sted som ikke er i diagrammet. Ok, så vi begynner her ute. Vi skal legge til dataetiketter, og det legger til tall og de ser forferdelig ut. Jeg kommer hit og sier at jeg vil ha flere alternativer, etikettalternativer,og jeg vil hente verdien fra celler - verdi fra celler. Ok? Så rekkeområdet er der, klikk OK. Veldig viktig å bruke verdi fra celler før jeg fjerner merket for Y-verdien. Det begynner å se bra ut. Jeg blir kvitt dette. Nå er hele nøkkelen her - fordi dere har noen mennesker som slags overskriver hverandre - å prøve å gjøre diagrammet så stort som mulig. Vi trenger ikke en kurs der oppe. Hvorfor? Bare slett det. Og jeg ser fremdeles som Kelly og Lou og Andy og Flo er nesten på samme sted; Jared og … Greit. Så nå, dette kommer til å bli frustrerende - de som overlapper hverandre. Men når vi klikker på en etikett, valgte vi alle etikettene, og klikk deretter på en etikett igjen, og vi velger bare en enkelt etikett. Ok? Så nå. veldig forsiktig. prøv å klikke på Andy, og bare dra Andy opp til venstre.Det ser ut til at Jared og Ike er sammen, så nå som jeg er i valg av enkelt etikettmodus, er det lettere. Og så drar Kelly og Lou dem opp sånn. Kanskje det er et bedre sted som ikke kjører Lou, eller til og med, her, kan jeg dra det på hver side. Ok, så hva har vi? Vi har startet med en haug med data, laget en pivottabell, funnet ut gjennomsnittet og standardavviket, som bare lar oss finne ut høyden - Y-posisjonen for hvert av disse poengene, og høyden på disse, forhåpentligvis, vi får folk inn i en fin parabelformet bjellekurve, sånn.Ok, så hva har vi? Vi har startet med en haug med data, laget en pivottabell, funnet ut gjennomsnittet og standardavviket, som bare lar oss finne ut høyden - Y-posisjonen for hver av disse poengene, og høyden på disse, forhåpentligvis, vi får folk inn i en fin parabelformet bjellekurve, sånn.Ok, så hva har vi? Vi har startet med en haug med data, laget en pivottabell, funnet ut gjennomsnittet og standardavviket, som bare lar oss finne ut høyden - Y-posisjonen for hver av disse poengene, og høyden på disse, forhåpentligvis, vi får folk inn i en fin parabelformet bjellekurve, sånn.
Jeg elsker dette spørsmålet fra Jimmy, dette spørsmålet er ikke i denne boka, men det vil være neste gang jeg skriver denne boka. Jeg må legge til dette - det er en kul forespørsel og et kult lite triks. Bellkurver er veldig populære i Excel.
Men sjekk ut boka mi, LIVe, The 54 Greatest Excel Tips of All Time.
Greit, avslutning fra denne episoden: Jimmy fra Huntsville, ønsker å ordne folk på en bjellekurve. Så vi bruker en pivottabell for å finne ut gjennomsnittspoengene, sortere pivottabellene til poengene - ordnet høyt til lavt - kvitt oss totalbeløpet nederst - dette blir egentlig X-verdiene - og deretter til siden, beregne gjennomsnittet og standardavviket for disse poengene og bruk formler for å kopiere dataene fra pivottabellen til et nytt område, fordi du ikke kan ha et XY-diagram som krysser en pivottabell. Beregn en y-verdi for hver person med = NORM.DIST av deres x-verdi, gjennomsnittet, standardavviket, komma FALSK; lage et XY-spredningsdiagram med jevne linjer - hvis du er en Excel 2010 eller tidligere, skal du bruke Ron Bovey's Chart Labeler-tillegg. Jeg skal få deg til å google det fordi,i tilfelle Rob endrer URL, vil jeg ikke ha feil URL her. I Excel 2013 hadde Data Labels, From Cells, spesifiser navnene, og deretter noen justeringer - endre skalaen nederst, jeg endrer dem i og Max og flytter deretter etikettene som overstiller hverandre.
For å laste ned arbeidsboken fra dagens video, bruk URL-en i YouTube-beskrivelsen. Jeg vil takke Jimmy for dette fantastiske spørsmålet i Huntsville, og jeg vil takke deg for at du var innom. Vi sees neste gang for en ny netcast fra.
Last ned Excel-fil
For å laste ned excel-filen: place-people-on-bell-curve.xlsx
Takk til Jimmy i Huntsville for dagens spørsmål!
Excel-tanken om dagen
Jeg har bedt mine Excel Master-venner om deres råd om Excel. Dagens tanke å tenke på:
"Hvis du har satt excel i manuell beregningsmodus i løpet av den siste måneden, er det på tide for kraftpivot (du trenger aldri manuell modus igjen)"
Rob Collie
