Jimmy i Huntsville vil planlægge en klokkekurve, der viser gennemsnittet for flere personer. Da Jimmy stillede spørgsmålet under mit Power Excel-seminar, tænkte jeg tilbage på en af mine mere populære videoer på YouTube.
I Podcast 1665 - Opret en klokkekurve i Excel, forklarer jeg, at for at oprette en klokkekurve skal du beregne middel- og standardafvigelsen. Derefter genererer jeg 30 punkter langs x-aksen, der spænder over en hypotetisk befolkning af mennesker. I den video genererede jeg, der spænder fra -3 standardafvigelser til + 3 standardafvigelser omkring et gennemsnit.
For eksempel, hvis du har et gennemsnit på 50 og en standardafvigelse på 10, ville jeg oprette en x-akse, der løb fra 70 til 130. Højden på hvert punkt beregnes ved hjælp af =NORM.DIST(x,mean,standard deviation,False).
På billedet ovenfor er tallene i A10: A40 i det væsentlige "falske datapunkter". Jeg genererer 31 tal for at skabe en flot glat kurve. Hvis jeg kun ville have brugt 7 datapunkter, ville kurven se sådan ud:
For Jimmys datasæt er de faktiske gennemsnitlige score for hans medarbejdere i det væsentlige punkter langs en x-akse. For at passe dem på en klokkekurve skal du finde ud af højden eller Y-værdien for hver medarbejder.
Følg disse trin:
-
Sorter dataene, så scores vises lavest til højest.
Sorter dataene - Beregn et gennemsnit ved hjælp af funktionen GENNEMSNIT.
- Beregn en standardafvigelse ved hjælp af STDEV-funktionen.
-
Beregn Y-værdien til højre for scores ved hjælp af
=NORM.DIST(L2,$H$2,$H$3,FALSE). Y-værdien genererer en højde af hver persons punkt langs bjælkekurven. NORM.DIST-funktionen tager sig af at plotte folk nær middelværdien på et højere sted end folk nær toppen eller bunden.
Generer en række Y-værdier. - Vælg dine data i L1: M15
-
En ulige fejl begyndte for nylig at vises i Excel, så vælg Alle diagrammer på fanen Indsæt for at sikre succes.
Dialogstarteren fører dig til alle diagramtyper Klik på fanen Alle diagrammer i dialogboksen Indsæt diagram. Klik på XY (Scatter) langs venstre. Vælg det andet ikon øverst. Vælg forhåndsvisning til højre.
Fire klik for at vælge diagrammet Din første klokkekurve vil se sådan ud:
Klokkekurven
Følg disse trin for at rense klokkekurven:
- Klik på titlen, og tryk på Delete-tasten.
- Dobbeltklik på et vilkårligt tal langs Y-aksen i bunden af diagrammet. Panelet Format Axis vises.
-
Indtast nye værdier for minimum og maksimum. Området her skal være lige bredt nok til at vise alle på diagrammet. Jeg brugte 50 til 90.
Skift minimum og maksimum - Gør diagrammet bredere ved at trække kanten af diagrammet.
- Klik på + ikonet til højre for diagrammet, og vælg Data Labels. Bare rolig, at etiketterne ikke giver mening endnu.
- Dobbeltklik på en etiket for at åbne panelet Formatér etiketter.
- Der er fire ikoner øverst på panelet. Vælg ikonet, der viser et søjlediagram.
- Klik på pilen ud for Etiketindstillinger for at udvide den del af panelet.
- Vælg værdi fra celler. En dialogboks vises, der beder om placeringen af etiketterne. Vælg navnene i K2: K15.
-
Fjern stadig Y-værdier i panelet Format Data Label. Det er vigtigt at afslutte trin 15, før du udfører trin 16, ellers fjerner du utilsigtet etiketterne.
Hent etiketterne fra cellerne, der indeholder navne.
Bemærk
Evnen til at hente etiketter fra celler blev tilføjet i Excel 2013. Hvis du bruger Excel 2010 eller tidligere, skal du downloade tilføjelsesprogrammet XY Chart Labeler fra Rob Bovey. (Google for at finde det).
På dette tidspunkt skal du se, om du har nogen diagrametiketter, der kolliderer med hinanden. Følg disse trin for at rette dem.
- Enkeltklik på en kortetiket. Dette markerer alle etiketter.
- Enkeltklik på en af etiketterne oven på en anden etiket for at vælge netop den etiket.
- Hold markøren over forskellige dele af mærkaten, indtil du ser en pil med fire hoveder. Klik og træk etiketten til en ny position.
-
Når du kun har valgt en enkelt etiket, kan du enkeltklikke på en hvilken som helst anden etiket for at vælge den etiket. Gentag for andre etiketter, der skal flyttes.
Det endelige diagram
Se video
Videoudskrift
Lær Excel fra Podcast, afsnit 2217: Placer folk i en bjælkekurve.
Hej, velkommen tilbage til netcast, jeg er Bill Jelen. Dagens spørgsmål fra Jimmy i mit seminar i Huntsville, Alabama. Jimmy har data, han vil opsummere disse data og derefter plotte resultaterne på en klokkekurve.
I orden? Nu er en af mine mest populære videoer på YouTube denne: nummer 1663, Opret en klokkekurve i Excel. Og givet et gennemsnit og en standardafvigelse fandt jeg ud af det lave, hvilket er 3 gange standardafvigelsen mindre end gennemsnittet, og den høje-- 3 gange standardafvigelsen mere end gennemsnittet - hvor afstanden er-- og en række X-værdier her, og for at finde ud af højden, brug denne funktion: = NORM.FORDELING af X-værdien, middelværdien og standardafvigelsen, komma falsk (= NORM.FORDELING (A10, $ B $ 2, $ B $ 3, FALSK)).
Og hvis du tænker over det, bruger denne video egentlig bare en række falske X-værdier her for at få en flot kurve. Og vi skal bruge det samme koncept her, men i stedet for falske X-værdier, vil vi faktisk have folkene hernede, og så vil højden være nøjagtigt den samme formel. I orden.
Så nu ville Jimmy oprette et pivottabel. Så vi indsætter, pivottabel, læg det her på dette ark, klik på OK. Folk på venstre side og derefter deres gennemsnitlige score. Okay, så det starter med Sum of Score, jeg dobbeltklikker der og ændrer det til et gennemsnit. Store. Nu, nederst, vil jeg ikke have et totalbeløb - højreklik og fjern samlet total - og vi vil arrangere disse mennesker højt til lavt, og det er let at gøre i en pivottabel. Data, A til Z-- fremragende. I orden. Nu skal vi gøre nøjagtigt det samme, som vi gjorde tilbage i Podcast 1663, og det beregner et gennemsnit og en standardafvigelse. Så gennemsnittet er et gennemsnit af disse scorer, og svarer derefter til standardafvigelsen for disse scores. I orden. Nu da jeg ved det, kan jeg skabe min y-værdi.
Okay, så et par ting, vi skal gøre her. For det første kan du ikke oprette en pivottabel - et spredningsdiagram - ud fra en pivottabel. Så jeg vil kopiere alle disse data over, og jeg vil bare gøre det med = D2. Bemærk, at jeg er forsigtig med ikke at bruge musen eller piletasterne til at pege på dem. Og så har vi vores værdier her. Disse bliver X-værdier, Y-værdien bliver = NORM.DIST, her er x-værdien, komma, for gennemsnittet, det tal, jeg trykker på F4 for at låse det ned; for standardafvigelsen er det dette nummer. Tryk igen på F4 for at låse det ned og kumulativ FALSE. (= NORM.DIST (K2, $ H $ 2, $ H $ 3, FALSE)) Og vi dobbeltklikker for at kopiere det ned. I orden. Og vælg ikke etiketterne,Vælg bare XY, så indsætter vi et spredningsdiagram med linjer - du kan enten vælge et med buede linjer eller lidt lige linjer. Her går jeg med buede linjer som denne. Og vi har nu alle vores mennesker placeret på en klokkekurve.
I orden. Nu, nogle ting - nogle formateringstyper - vi skal gøre her: Først skal du dobbeltklikke her nede på skalaen, og det ser ud til, at vores laveste antal sandsynligvis er et sted omkring 50-- så jeg sæt et min. på 50-- og vores største antal - vores største antal-- er 88-- så jeg sætter et maksimum på 90. Okay. Og nu skal vi mærke disse punkter. Hvis du er i Excel 2013 eller nyere, er det let at gøre; men hvis du er i en ældre version af Excel, bliver du nødt til at gå tilbage og bruge tilføjelsesprogrammet Rob Bovey's Chart Labeler for at få disse punktetiketter fra et sted, der ikke er i diagrammet. Okay, så vi starter herude. Vi tilføjer dataetiketter, og det tilføjer tal, og de ser forfærdelige ud. Jeg kommer her og siger, at jeg vil have flere muligheder, etiketindstillinger,og jeg vil hente værdien fra celler - værdi fra celler. I orden? Så rækkevidden af celler er lige der, klik på OK. Meget vigtigt at bruge værdi fra celler, før jeg fjerner markeringen af Y-værdien. Det begynder at se godt ud. Jeg slipper af med dette. Nu er hele nøglen her - fordi du har nogle mennesker, der slags overskriver hinanden - at prøve at gøre diagrammet så stort som muligt. Vi har ikke brug for en overskrift deroppe. Hvorfor? Slet det bare. Og jeg kan stadig se, ligesom Kelly og Lou og Andy og Flo er næsten samme sted; Jared og … Okay. Så nu vil dette være frustrerende - disse, der overlapper hinanden. Men når vi klikker på en etiket, valgte vi alle etiketterne og klikker derefter på en etiket igen, og vi vælger kun en enkelt etiket. I orden? Så nu. meget forsigtigt. prøv og klik på Andy, og træk bare Andy op til venstre.Det ser ud til, at Jared og Ike er sammen, så nu hvor jeg er i tilstanden til valg af enkelt etiket, er det lettere. Og så træk Kelly og Lou dem sådan op. Måske er der et bedre sted, der ikke kører Lou, eller endda, her kan jeg trække det på begge sider. Okay, så hvad har vi? Vi er startet med en masse data, oprettet en pivottabel, fundet middelværdien og standardafvigelsen, som bare giver os mulighed for at finde ud af højden - Y-positionen for hver af disse scoringer og højden af disse forhåbentlig vi får folk ind i en dejlig parabelformet klokkekurve, sådan.Okay, så hvad har vi? Vi er startet med en masse data, oprettet en pivottabel, fundet middelværdien og standardafvigelsen, som bare giver os mulighed for at finde ud af højden - Y-positionen for hver af disse scoringer og højden af disse forhåbentlig vi får folk ind i en dejlig parabelformet klokkekurve, sådan.Okay, så hvad har vi? Vi er startet med en masse data, oprettet en pivottabel, fundet middelværdien og standardafvigelsen, som bare giver os mulighed for at finde ud af højden - Y-positionen for hver af disse scoringer og højden af disse forhåbentlig vi får folk ind i en dejlig parabelformet klokkekurve, sådan.
Jeg elsker dette spørgsmål fra Jimmy, dette spørgsmål findes ikke i denne bog, men det vil være næste gang jeg skriver denne bog. Jeg bliver nødt til at tilføje dette - det er en sej anmodning og et sejt lille trick. Bellkurver er meget populære i Excel.
Men tjek min bog, LIVe, The 54 Greatest Excel Tips of All Time.
Okay, afslutning fra denne episode: Jimmy fra Huntsville, ønsker at arrangere folk på en bjergkurve. Så vi bruger en pivottabel til at finde ud af gennemsnittet, sortere pivottabellerne til scoringerne - arrangeret højt til lavt - slippe af den samlede sum i bunden - disse vil i det væsentlige være X-værdierne - og derefter ud til siden, beregne gennemsnittet og standardafvigelsen for disse scoringer og brug formler til at kopiere dataene fra pivottabellen til et nyt interval, fordi du ikke kan have et XY-diagram, der krydser en pivottabel. Beregn en y-værdi for hver person med = NORM.DIST af deres x-værdi, middelværdien, standardafvigelsen, komma FALSK; opret et XY-spredningsdiagram med glatte linjer - hvis du er en Excel 2010 eller tidligere, skal du bruge tilføjelsesprogrammet Ron Bovey's Chart Labeler. Jeg vil få dig til at google det, fordihvis Rob ændrer sin URL, vil jeg ikke have den forkerte URL her. I Excel 2013 havde dataetiketter, fra celler, angiv navnene og derefter nogle justeringer - skift skalaen langs bunden, jeg ændrede dem i og Max og flyttede derefter de etiketter, der overindstillede hinanden.
For at downloade projektmappen fra dagens video skal du bruge URL'en i YouTube-beskrivelsen. Jeg vil takke Jimmy for dette fantastiske spørgsmål i Huntsville, og jeg vil takke dig for at komme forbi. Vi ses næste gang til endnu en netcast fra.
Download Excel-fil
For at downloade excel-filen: place-people-on-bell-curve.xlsx
Tak til Jimmy i Huntsville for dagens spørgsmål!
Excel-tanken om dagen
Jeg har bedt mine Excel Master-venner om deres råd om Excel. Dagens tanke at tænke over:
"Hvis du har sat excel i manuel genberegningstilstand i den sidste måned, er det tid til power-pivot (du har aldrig brug for manuel tilstand igen)"
Rob Collie
