Jimmy din Huntsville vrea să traseze o curbă de clopot care să arate scorurile medii ale mai multor persoane. Când Jimmy a pus întrebarea în timpul seminarului meu Power Excel, m-am gândit la unul dintre videoclipurile mele cele mai populare de pe YouTube.
În Podcast 1665 - Creați o curbă de clopot în Excel, vă explic că pentru a crea o curbă de clopot, trebuie să calculați media și abaterea standard. Apoi generez 30 de puncte de-a lungul axei x care acoperă o populație ipotetică de oameni. În acel videoclip, am generat un interval cuprins între -3 deviații standard și + 3 deviații standard în jurul unei medii.
De exemplu, dacă aveți o medie de 50 și o abatere standard de 10, aș crea o axă x care ar varia de la 70 la 130. Înălțimea fiecărui punct este calculată folosind =NORM.DIST(x,mean,standard deviation,False).
În imaginea de mai sus, numerele din A10: A40 sunt în esență „puncte de date false”. Generez 31 de numere pentru a crea o curbă netedă. Dacă aș fi folosit doar 7 puncte de date, curba ar arăta astfel:
Pentru setul de date al lui Jimmy, scorurile medii reale ale angajaților săi sunt în esență puncte de-a lungul unei axe x. Pentru a le potrivi pe o curbă de clopot, trebuie să vă dați seama de înălțimea sau valoarea Y pentru fiecare angajat.
Urmați acești pași:
-
Sortați datele astfel încât scorurile să pară de la cel mai mic la cel mai mare.
Sortați datele - Calculați o medie utilizând funcția MEDIE.
- Calculați o abatere standard utilizând funcția STDEV.
-
Calculați valoarea Y din dreapta scorurilor folosind
=NORM.DIST(L2,$H$2,$H$3,FALSE). Valoarea Y va genera o înălțime a punctului fiecărei persoane de-a lungul curbei clopotului. Funcția NORM.DIST se va ocupa de reprezentarea grafică a persoanelor în apropierea mediei într-o locație mai înaltă decât a persoanelor din partea de sus sau de jos.
Generați o serie de valori Y. - Selectați datele în L1: M15
-
O eroare ciudată a început să apară recent în Excel, astfel încât pentru a asigura succesul, alegeți Toate diagramele din fila Inserare.
Lansatorul de dialog vă duce la toate tipurile de diagrame În dialogul Inserare diagramă, faceți clic pe fila Toate diagramele. Faceți clic pe XY (Scatter) de-a lungul stânga. Alegeți a doua pictogramă din partea de sus. Alegeți previzualizarea din dreapta.
Patru clicuri pentru a alege graficul Curba inițială a clopotului va arăta astfel:
Curba clopotului
Pentru a curăța curba clopotului, urmați acești pași:
- Faceți clic pe titlu și apăsați tasta Ștergere.
- Faceți dublu clic pe orice număr de-a lungul axei Y din partea de jos a diagramei. Va apărea panoul Format Axis.
-
Tastați valori noi pentru minim și maxim. Gama de aici ar trebui să fie suficient de largă pentru a le arăta tuturor pe grafic. Am folosit 50-90.
Modificați minimul și maximul - Faceți diagrama mai largă trăgând marginea diagramei.
- Faceți clic pe pictograma + din dreapta graficului și selectați Etichete de date. Nu vă faceți griji că etichetele nu au încă sens.
- Faceți dublu clic pe o etichetă pentru a deschide panoul Format etichete.
- Există patru pictograme în partea de sus a panoului. Alegeți pictograma care arată o diagramă pe coloane.
- Faceți clic pe săgeata de lângă Opțiuni etichetă pentru a extinde acea parte a panoului.
- Alegeți Valoare din celule. Va apărea o casetă de dialog care va solicita locația etichetelor. Alegeți numele din K2: K15.
-
Încă în panoul Format Data Label, deselectați valorile Y. Este important să finalizați Pasul 15 înainte de a face Pasul 16 sau veți elimina din greșeală etichetele.
Obțineți etichetele din celulele care conțin nume.
Notă
Capacitatea de a obține etichete din celule a fost adăugată în Excel 2013. Dacă utilizați Excel 2010 sau o versiune anterioară, descărcați programul de completare XY Chart Labeler de la Rob Bovey. (Google să o găsească).
În acest moment, vedeți dacă aveți etichete ale graficelor care se prăbușesc între ele. Pentru a le remedia, urmați cu atenție acești pași.
- Faceți un singur clic pe o etichetă de grafic. Aceasta selectează toate etichetele.
- Faceți un singur clic pe una dintre etichetele aflate deasupra altei etichete pentru a selecta doar acea etichetă.
- Plasați cursorul peste diferite părți ale etichetei până când vedeți o săgeată cu patru capete. Faceți clic și trageți eticheta într-o poziție nouă.
-
După ce ați selectat doar o singură etichetă, puteți face un singur clic pe orice altă etichetă pentru a selecta acea etichetă. Repetați pentru orice alte etichete care trebuie mutate.
Graficul final
Urmăriți videoclipul
Transcriere video
Aflați Excel din Podcast, Episodul 2217: Plasați oamenii pe o curbă de clopot.
Hei, bine ai venit din nou pe netcast, eu sunt Bill Jelen. Întrebarea de astăzi, de la Jimmy la seminarul meu din Huntsville, Alabama. Jimmy are date, vrea să rezume aceste date și apoi să traseze rezultatele pe o curbă de clopot.
Bine? Acum, unul dintre cele mai populare videoclipuri de pe YouTube este acesta: numărul 1663, Creați o curbă de clopot în Excel. Și având în vedere o medie și o abatere standard, mi-am dat seama de valoarea mică, care este de 3 ori abaterea standard mai mică decât media și de cea mai mare - de 3 ori abaterea standard mai mare decât media - unde este decalajul - și o serie de valori X aici și pentru a afla înălțimea, utilizați această funcție: = NORM.DIST a valorii X, media și deviația standard, virgulă falsă (= NORM.DIST (A10, $ B $ 2, $ B $ 3, FALS)).
Și dacă vă gândiți la asta, acest videoclip utilizează într-adevăr o serie de valori X false aici pentru a obține o curbă frumoasă. Și vom folosi același concept aici, dar în loc de valori X false, vom avea de fapt oamenii aici și atunci înălțimea va fi exact aceeași formulă. Bine.
Deci, acum, Jimmy a vrut să creeze un tabel pivot. Deci vom Insera, PivotTable, îl vom pune aici pe această foaie, vom face clic pe OK. Oamenii din partea stângă și apoi Scorul mediu. În regulă, așa că începe cu Suma scorului, voi face dublu clic acolo și o voi schimba într-o medie. Grozav. Acum, în partea de jos, nu vreau un total mare - faceți clic dreapta și eliminați totalul total - și dorim să aranjăm aceste persoane de la mare la mic și acest lucru este ușor de făcut într-un tabel pivot. Date, de la A la Z-- excelent. Bine. Acum, vom face exact același lucru pe care l-am făcut înapoi în Podcast 1663, iar asta calculează o medie și o abatere standard. Deci, media este o medie a acestor scoruri și apoi este egală cu deviația standard a acestor scoruri. Bine. Acum, că știu asta, sunt capabil să îmi creez valoarea y.
Bine, deci vom face câteva lucruri aici. În primul rând, nu puteți crea un tabel pivot - o diagramă scatter - dintr-un tabel pivot. Deci, voi copia toate aceste date și voi face asta doar cu = D2. Observați că sunt atent să nu folosesc mouse-ul sau tastele săgeată pentru a le indica. Și astfel avem valorile noastre aici. Acestea vor deveni valori X, valoarea Y va deveni = NORM.DIST, iată valoarea x, virgulă, pentru media, acel număr, voi apăsa F4 pentru a bloca acest lucru; pentru deviația standard este acest număr, din nou, apăsați F4 pentru a bloca acest lucru și FALS cumulativ. (= NORM.DIST (K2, $ H $ 2, $ H $ 3, FALSE)) Și vom face dublu clic pentru a copia asta. Bine. Și apoi, nu alegeți etichetele,trebuie doar să alegeți XY și vom introduce o diagramă de împrăștiere cu linii - o puteți alege fie pe cea cu linii curbe, fie cu câteva linii drepte. Aici, voi merge cu linii curbate ca aceasta. Și acum avem toți oamenii noștri așezați pe o curbă de clopot.
Bine. Acum, unele lucruri - unele lucruri de tip formatare - le vom face aici: În primul rând, faceți dublu clic aici de-a lungul scalei și se pare că numărul nostru cel mai mic este probabil undeva la 50 - așa că setați un min de 50 - și cel mai mare număr al nostru - cel mai mare număr al nostru - este 88 - așa că voi seta un maxim de 90. Bine. Și acum, trebuie să etichetăm aceste puncte. Dacă sunteți în Excel 2013 sau mai nou, acest lucru este ușor de făcut; dar dacă vă aflați într-o versiune mai veche de Excel, va trebui să vă întoarceți și să utilizați programul de completare Rob Bovey Chart Labeler pentru ca aceste etichete punctuale să provină dintr-un loc care nu se află în diagramă. Bine, așa că începem aici. Vom adăuga etichete de date, iar acestea adaugă numere și arată oribil. Voi veni aici și voi spune că vreau mai multe opțiuni, opțiuni de etichetă,și vreau să obțin Valoarea din celule - Valoarea din celule. Bine? Deci, gama de celule este chiar acolo, faceți clic pe OK. Foarte important să utilizați Valoare din celule înainte de a debifa valoarea Y. Începe să arate bine. Voi scăpa de asta. Acum, întreaga cheie aici - pentru că aveți unii oameni care se suprascriu unii pe alții - este să încercați să faceți graficul cât mai mare posibil. Nu avem nevoie de un titlu acolo sus. De ce? Șterge doar asta. Și încă văd, de genul, Kelly, Lou și Andy și Flo sunt aproape în același loc; Jared și … Bine. Deci, acum, acest lucru va fi frustrant - acestea se suprapun. Dar când facem clic pe o etichetă, am selectat toate etichetele, apoi facem din nou clic pe o etichetă și selectăm doar o singură etichetă. Bine? Asa ca acum. cu mare grija. încercați să faceți clic pe Andy și trageți-l pe Andy în sus în stânga.Se pare că Jared și Ike sunt împreună, așa că acum că sunt în modul de selectare a unei singure etichete, este mai ușor. Și apoi Kelly și Lou, trage-i în sus așa. Poate că există un loc mai bun care să nu exagereze cu Lou, sau chiar, așa, aici îl pot trage de ambele părți. Bine, deci, ce avem? Am început cu o grămadă de date, am creat un tabel pivot, am stabilit media și deviația standard, ceea ce ne permite doar să ne dăm seama de înălțimea - poziția Y pentru fiecare dintre aceste scoruri și înălțimea celor, sperăm, vom aduce oamenii într-o frumoasă curbă de clopot în formă de parabolă, așa.Bine, deci, ce avem? Am început cu o grămadă de date, am creat un tabel pivot, am stabilit media și deviația standard, ceea ce ne permite doar să ne dăm seama de înălțimea - poziția Y pentru fiecare dintre aceste scoruri și înălțimea celor, sperăm, vom aduce oamenii într-o frumoasă curbă de clopot în formă de parabolă, așa.Bine, deci, ce avem? Am început cu o grămadă de date, am creat un tabel pivot, am stabilit media și deviația standard, ceea ce ne permite doar să ne dăm seama de înălțimea - poziția Y pentru fiecare dintre aceste scoruri și înălțimea celor, sperăm, vom aduce oamenii într-o frumoasă curbă de clopot în formă de parabolă, așa.
Îmi place această întrebare de la Jimmy, această întrebare nu se află în această carte, dar va fi în următoarea dată când voi scrie această carte. Va trebui să adaug acest lucru - este o cerere interesantă și un mic truc minunat. Curbele clopotului sunt foarte populare în Excel.
Dar uitați-vă la cartea mea, LIVe, Cele mai mari 54 de sfaturi Excel din toate timpurile.
Bine, încheie acest episod: Jimmy din Huntsville, vrea să aranjeze oamenii pe o curbă de clopot. Deci, folosim un tabel pivot pentru a afla scorul mediu, sortăm tabelele pivot la scoruri - aranjate de la mare la mic - scăpați de marele total din partea de jos - acestea vor fi în esență valorile X - și apoi în lateral, calculați abaterea medie și standard a acelor scoruri și folosiți formule pentru a copia datele din tabelul pivot într-un interval nou, deoarece nu puteți avea o diagramă XY care se intersectează cu un tabel pivot. Calculați o valoare y pentru fiecare persoană cu = NORM.DIST a valorii lor x, media, abaterea standard, virgulă FALS; creați o diagramă de împrăștiere XY cu linii netede - dacă sunteți un Excel 2010 sau o versiune anterioară, veți utiliza programul de completare pentru eticheta de diagrame a lui Ron Bovey. O să vă pun pe Google asta pentru că,în cazul în care Rob își schimbă adresa URL, nu vreau adresa URL greșită aici. În Excel 2013, a avut etichete de date, din celule, specificați numele și apoi unele ajustări - schimbați scala de jos, le schimb și Max și apoi mutați etichetele care se suprapun reciproc.
Pentru a descărca registrul de lucru din videoclipul de astăzi, utilizați adresa URL din descrierea YouTube. Vreau să-i mulțumesc lui Jimmy pentru această minunată întrebare din Huntsville și vreau să-ți mulțumesc că ai trecut pe aici. Ne vedem data viitoare pentru un alt netcast de la.
Descărcați fișierul Excel
Pentru a descărca fișierul Excel: place-people-on-bell-curve.xlsx
Mulțumesc lui Jimmy din Huntsville pentru întrebarea de astăzi!
Gândul Excel al zilei
Le-am cerut prietenilor mei Excel Master sfatul lor despre Excel. Gândul de astăzi să medităm:
„Dacă ați pus Excel în modul de recalculare manuală în ultima lună, este timpul pentru pivotarea electrică (nu veți mai avea nevoie niciodată de modul manual)”
Rob Collie
