Jimmy i Huntsville vill plotta en klockkurva som visar genomsnittet för flera personer. När Jimmy ställde frågan under mitt Power Excel-seminarium tänkte jag tillbaka på en av mina mer populära videor på YouTube.
I Podcast 1665 - Skapa en Bell Curve i Excel förklarar jag att för att skapa en bellkurva måste du beräkna medelvärdet och standardavvikelsen. Sedan genererar jag 30 punkter längs x-axeln som spänner över en hypotetisk befolkning. I den videon genererade jag det som spänner från -3 standardavvikelser till + 3 standardavvikelser runt ett medelvärde.
Till exempel, om du har ett medelvärde på 50 och en standardavvikelse på 10, skulle jag skapa en x-axel som gick från 70 till 130. Höjden på varje punkt beräknas med =NORM.DIST(x,mean,standard deviation,False).
I bilden ovan är siffrorna i A10: A40 i huvudsak "falska datapunkter". Jag genererar 31 nummer för att skapa en fin jämn kurva. Om jag bara skulle ha använt 7 datapunkter skulle kurvan se ut så här:
För Jimmys datamängd är de faktiska genomsnittliga poängen för hans anställda i huvudsak punkter längs en x-axel. För att passa dem på en klockkurva måste du räkna ut höjden eller Y-värdet för varje anställd.
Följ dessa steg:
-
Sortera data så att poängen verkar lägst till högst.
Sortera data - Beräkna ett medelvärde med AVERAGE-funktionen.
- Beräkna en standardavvikelse med STDEV-funktionen.
-
Beräkna Y-värdet till höger om poängen med
=NORM.DIST(L2,$H$2,$H$3,FALSE). Y-värdet genererar en persons höjd längs klockan. NORM.DIST-funktionen tar hand om att plotta människor nära medelvärdet på en högre plats än personer nära toppen eller botten.
Generera en serie Y-värden. - Välj dina data i L1: M15
-
Ett udda fel uppstod nyligen i Excel, så för att säkerställa framgång, välj Alla diagram på fliken Infoga.
Dialogrutan startar dig till alla diagramtyper I dialogrutan Infoga diagram klickar du på fliken Alla diagram. Klicka på XY (Scatter) till vänster. Välj den andra ikonen längst upp. Välj förhandsgranskningen till höger.
Fyra klick för att välja diagrammet Din första klockkurva ser ut så här:
Klockan kurva
Följ dessa steg för att städa upp klockan:
- Klicka på titeln och tryck på Delete-tangenten.
- Dubbelklicka på valfritt tal längs Y-axeln längst ner i diagrammet. Panelen Format Axis visas.
-
Skriv nya värden för lägsta och högsta. Området här bör vara tillräckligt stort för att visa alla i diagrammet. Jag brukade 50 till 90.
Ändra lägsta och högsta - Gör sjökortet bredare genom att dra i sjökanten.
- Klicka på + -ikonen till höger om diagrammet och välj Datatiketter. Oroa dig inte för att etiketterna inte är vettiga än.
- Dubbelklicka på en etikett för att öppna panelen Formatera etiketter.
- Det finns fyra ikoner längst upp på panelen. Välj ikonen som visar ett kolumndiagram.
- Klicka på pilen bredvid etikettalternativ för att expandera den delen av panelen.
- Välj värde från celler. En dialogruta visas där etiketterna befinner sig. Välj namnen i K2: K15.
-
Avmarkera fortfarande Y-värden i panelen Format Data Label. Det är viktigt att avsluta steg 15 innan du gör steg 16, annars tar du av misstag bort etiketterna.
Hämta etiketterna från cellerna som innehåller namn.
Notera
Möjligheten att få etiketter från celler har lagts till i Excel 2013. Om du använder Excel 2010 eller tidigare, ladda ner tillägget XY Chart Labeler från Rob Bovey. (Google för att hitta det).
Vid denna punkt, se om du har några diagrametiketter som kraschar in i varandra. För att fixa dem, följ dessa steg noggrant.
- Dubbelklicka på en diagrametikett. Detta markerar alla etiketter.
- Dubbelklicka på en av etiketterna ovanpå en annan etikett för att välja just den etiketten.
- Håll muspekaren över olika delar av etiketten tills du ser en fyrhårig pil. Klicka och dra etiketten till en ny position.
-
När du bara har valt en enda etikett kan du enkelt klicka på vilken annan etikett som helst för att välja den etiketten. Upprepa för andra etiketter som behöver flyttas.
Det sista diagrammet
Kolla på video
Videoutskrift
Lär dig Excel från Podcast, avsnitt 2217: Placera människor i en klockkurva.
Hej, välkommen tillbaka till netcast, jag heter Bill Jelen. Dagens fråga från Jimmy i mitt seminarium i Huntsville, Alabama. Jimmy har data, han vill sammanfatta dessa data och sedan rita resultaten på en klockkurva.
OK? Nu är en av mina mest populära videor på YouTube den här: nummer 1663, Create a Bell Curve in Excel. Och med tanke på ett medelvärde och en standardavvikelse räknade jag ut det låga, vilket är 3 gånger standardavvikelsen mindre än medelvärdet, och den höga - 3 gånger standardavvikelsen mer än genomsnittet - där klyftan är-- och en serie X-värden här, och för att räkna ut höjden, använd den här funktionen: = NORM.DIST av X-värdet, medelvärdet och standardavvikelsen, komma falsk (= NORM.DIST (A10, $ B $ 2, $ B $ 3, FALSE)).
Och om du tänker på det använder den här videon egentligen bara en serie falska X-värden här för att få en snygg kurva. Och vi kommer att använda samma koncept här, men istället för falska X-värden kommer vi faktiskt att ha folket här nere och då kommer höjden att vara exakt samma formel. OK.
Så nu ville Jimmy skapa ett pivottabell. Så vi ska infoga, pivottabell, placera det här på det här arket, klicka på OK. Människor på vänster sida och sedan deras genomsnittliga poäng. Okej, så det börjar med Sum of Score, jag dubbelklickar där och ändrar det till ett genomsnitt. Bra. Nu längst ner vill jag inte ha en totalsumma - högerklicka och ta bort totalsumma - och vi vill ordna dessa människor högt till lågt och det är lätt att göra i en pivottabell. Data, A till Z-- utmärkt. OK. Nu ska vi göra exakt samma sak som vi gjorde i Podcast 1663, och det beräknar ett medelvärde och en standardavvikelse. Så medelvärdet är ett genomsnitt av dessa poäng och motsvarar sedan standardavvikelsen för dessa poäng. OK. Nu när jag vet det kan jag skapa mitt y-värde.
Okej, så ett par saker vi ska göra här. För det första kan du inte skapa en pivottabell - ett spridningsdiagram - från en pivottabell. Så jag ska kopiera all denna information över och jag ska bara göra det med = D2. Observera att jag är försiktig så att jag inte använder musen eller piltangenterna för att peka på dem. Och så har vi våra värderingar här. Dessa blir X-värden, Y-värdet kommer att bli = NORM.DIST, här är x-värdet, komma, för medelvärdet, det talet, jag trycker på F4 för att låsa det; för standardavvikelsen är det detta nummer, tryck igen F4 för att låsa det och kumulativ FALSE. (= NORM.DIST (K2, $ H $ 2, $ H $ 3, FALSE)) Och vi dubbelklickar för att kopiera det. OK. Och välj inte etiketterna,välj bara XY så infogar vi ett spridningsdiagram med linjer - du kan antingen välja det med böjda linjer eller lite raka linjer. Här går jag med böjda linjer som denna. Och vi har nu alla våra människor placerade på en klockkurva.
OK. Några saker - några formateringssaker - vi ska göra här: Först och främst, dubbelklicka här längs skalan, och det ser ut som att vårt lägsta antal troligen ligger någonstans runt 50 - så jag ska ställa in min på 50-- och vårt största antal-- vårt största antal-- är 88-- så jag ställer in max på 90. Okej. Och nu måste vi märka dessa punkter. Om du är i Excel 2013 eller senare är det enkelt att göra det; men om du är i en äldre version av Excel måste du gå tillbaka och använda tillägget Rob Boveys Chart Labeler för att få dessa punktetiketter från någon plats som inte finns i diagrammet. Okej, så vi börjar här. Vi ska lägga till dataetiketter, och det lägger till siffror och de ser hemskt ut. Jag kommer hit och säger att jag vill ha fler alternativ, etikettalternativ,och jag vill hämta värde från celler - värde från celler. OK? Så cellintervallet är precis där, klicka på OK. Mycket viktigt att använda värde från celler innan jag avmarkerar Y-värdet. Det börjar se bra ut. Jag ska bli av med det här. Nu är hela nyckeln här - för att du har några människor som typ skriver över varandra - att försöka göra diagrammet så stort som möjligt. Vi behöver inte en rubrik där uppe. Varför? Ta bara bort det. Och jag ser fortfarande att Kelly och Lou och Andy och Flo nästan är på samma plats; Jared och … Okej. Så nu kommer det att bli frustrerande - de som överlappar varandra. Men när vi klickar på en etikett valde vi alla etiketter och klickade sedan på en etikett igen och vi väljer bara en enda etikett. OK? Så nu. väldigt försiktigt. försök och klicka på Andy och dra bara Andy upp till vänster.Det ser ut som att Jared och Ike är tillsammans, så nu när jag är i val av enstaka etikett är det lättare. Och sedan drar Kelly och Lou upp dem så. Kanske finns det en bättre plats som inte överskrider Lou, eller till och med, här kan jag dra den på vardera sidan. Okej, så vad har vi? Vi har börjat med en massa data, skapat en pivottabell, räknat ut medelvärdet och standardavvikelsen, som bara gör det möjligt för oss att räkna ut höjden - Y-positionen för var och en av dessa poäng och höjden på dessa, förhoppningsvis, vi får människor in i en fin parabelformad klockkurva.Okej, så vad har vi? Vi har börjat med en massa data, skapat en pivottabell, räknat ut medelvärdet och standardavvikelsen, som bara gör det möjligt för oss att räkna ut höjden - Y-positionen för var och en av dessa poäng och höjden på dessa, förhoppningsvis, vi får människor in i en fin parabelformad klockkurva.Okej, så vad har vi? Vi har börjat med en massa data, skapat en pivottabell, räknat ut medelvärdet och standardavvikelsen, som bara gör det möjligt för oss att räkna ut höjden - Y-positionen för var och en av dessa poäng och höjden på dessa, förhoppningsvis, vi får människor in i en fin parabelformad klockkurva.
Jag älskar den här frågan från Jimmy, den här frågan finns inte i den här boken, men det kommer att ske nästa gång jag skriver den här boken. Jag måste lägga till detta - det är en cool begäran och ett coolt litet trick. Klockkurvor är mycket populära i Excel.
Men kolla in min bok, LIVe, The 54 Greatest Excel Tips of All Time.
Okej, avslutning från det här avsnittet: Jimmy från Huntsville, vill ordna människor på en klockkurva. Så vi använder en pivottabell för att räkna ut medelvärdet, sortera pivottabellerna till poängen - ordnade högt till lågt - bli av med totalsumman längst ner - dessa kommer i princip att vara X-värdena - och sedan åt sidan, beräkna medelvärdet och standardavvikelsen för dessa poäng och använd formler för att kopiera data från pivottabellen till ett nytt intervall, eftersom du inte kan ha ett XY-diagram som korsar en pivottabell. Beräkna ett y-värde för varje person med = NORM.DIST av deras x-värde, medelvärdet, standardavvikelsen, komma FALSKT; skapa ett XY-spridningsdiagram med släta linjer - om du är en Excel 2010 eller tidigare ska du använda tillägget Ron Boveys Chart Labeler. Jag ska låta dig google det för,om Rob ändrar sin URL vill jag inte ha fel URL här. I Excel 2013 hade dataetiketter, från celler, ange namnen och sedan några justeringar - ändra skalan längst ner, jag ändrar dem och Max och flyttar sedan etiketterna som överställer varandra.
För att ladda ner arbetsboken från dagens video använder du webbadressen i YouTube-beskrivningen. Jag vill tacka Jimmy för den här fantastiska frågan i Huntsville, och jag vill tacka dig för att du kom förbi. Vi ses nästa gång för en ny netcast från.
Ladda ner Excel-fil
För att ladda ner excel-filen: place-people-on-bell-curve.xlsx
Tack till Jimmy i Huntsville för dagens fråga!
Excel-tanke på dagen
Jag har frågat mina Excel Master-vänner om deras råd om Excel. Dagens tanke att fundera på:
"Om du har satt Excel i manuellt omberäkningsläge under den senaste månaden är det dags för power-pivot (du behöver aldrig mer manuellt läge igen)"
Rob Collie
