Jimmy v Huntsvilleu želi izrisati zvončno krivuljo, ki prikazuje povprečne ocene več ljudi. Ko je Jimmy postavil vprašanje med mojim seminarjem Power Excel, sem se spomnil enega svojih bolj priljubljenih videoposnetkov v YouTubu.
V članku Podcast 1665 - Ustvari krivuljo zvona v Excelu pojasnjujem, da morate za ustvarjanje krivulje zvonca izračunati povprečje in standardni odklon. Nato ustvarim 30 točk vzdolž osi x, ki zajemajo hipotetično populacijo ljudi. V tem videoposnetku sem ustvaril, ki se giblje od -3 standardnih odklonov do + 3 standardnih odklonov okoli povprečja.
Če imate na primer povprečje 50 in standardni odklon 10, bi ustvaril os x, ki je potekala od 70 do 130. Višina vsake točke se izračuna z uporabo =NORM.DIST(x,mean,standard deviation,False).
Na zgornji sliki so številke v A10: A40 v bistvu "ponarejene podatkovne točke". Ustvarim 31 številk, da ustvarim lepo gladko krivuljo. Če bi uporabil samo 7 podatkovnih točk, bi bila krivulja videti tako:
Za Jimmyjev nabor podatkov so dejanske povprečne ocene njegovih zaposlenih v bistvu točke vzdolž osi x. Če jih želite namestiti na zvonasto krivuljo, morate ugotoviti višino ali vrednost Y za vsakega zaposlenega.
Sledite tem korakom:
-
Razvrstite podatke tako, da bodo ocene videti najmanjše do najvišje.
Razvrsti podatke - Izračunajte povprečje s pomočjo funkcije AVERAGE.
- Izračunajte standardni odklon s pomočjo funkcije STDEV.
-
Z uporabo izračunajte vrednost Y na desni strani rezultatov
=NORM.DIST(L2,$H$2,$H$3,FALSE). Vrednost Y bo ustvarila višino točke vsake osebe vzdolž zvončne krivulje. Funkcija NORM.DIST bo poskrbela za risanje ljudi blizu povprečja na višji lokaciji kot za ljudi blizu vrha ali dna.
Ustvari vrsto Y vrednosti. - Izberite podatke v L1: M15
-
V Excelu se je pred kratkim začela pojavljati nenavadna napaka, zato za zagotovitev uspeha izberite Vse karte na zavihku Vstavi.
Zaganjalnik pogovornih oken vas popelje do vseh vrst grafikonov V pogovornem oknu Vstavi grafikon kliknite jeziček Vse karte. Kliknite XY (Scatter) na levi. Izberite drugo ikono na vrhu. Izberite predogled na desni.
Štirje kliki za izbiro grafikona Vaša začetna krivulja zvona bo videti tako:
Zvonova krivulja
Če želite počistiti krivuljo zvonca, sledite tem korakom:
- Kliknite naslov in pritisnite tipko Delete.
- Dvokliknite katero koli številko vzdolž osi Y na dnu grafikona. Pojavi se podokno Format Axis.
-
Vnesite nove vrednosti za Minimum in Maximum. Razpon tukaj mora biti ravno dovolj širok, da se lahko prikažejo vsi na lestvici. Uporabil sem 50 do 90.
Spremenite minimum in maksimum - Graf razširite tako, da povlečete rob grafikona.
- Kliknite ikono + na desni strani grafikona in izberite Data Labels. Ne skrbite, da nalepke še nimajo smisla.
- Dvokliknite eno nalepko, da odprete podokno Oblika nalepk.
- Na vrhu plošče so štiri ikone. Izberite ikono, ki prikazuje stolpčni grafikon.
- Kliknite puščico poleg možnosti oznake, da razširite tisti del plošče.
- Izberite Vrednost med celicami. Pojavilo se bo pogovorno okno z vprašanjem, kje so oznake. Izberite imena v K2: K15.
-
Še vedno na plošči Format Data Label odstranite oznako Y vrednosti. Pomembno je, da dokončate 15. korak pred 16. korakom, sicer boste nenamerno odstranili nalepke.
Pridobite nalepke iz celic, ki vsebujejo imena.
Opomba
Možnost pridobivanja nalepk iz celic je bila dodana v programu Excel 2013. Če uporabljate Excel 2010 ali starejšo različico, prenesite dodatek XY Chart Labeler od Roba Boveyja. (Google, da ga najde).
Na tej točki preverite, ali imate oznake grafikonov, ki se sesujejo. Če jih želite odpraviti, natančno sledite tem korakom.
- Z enim klikom na eno nalepko grafikona. S tem izberete vse nalepke.
- Z enim klikom na eno od nalepk, ki je na vrhu druge nalepke, izberite samo to.
- Premaknite miškin kazalec nad različne dele nalepke, dokler ne vidite puščice s štirimi glavami. Kliknite in povlecite nalepko na novo mesto.
-
Ko izberete samo eno nalepko, lahko z enim klikom poljubno oznako izberete to oznako. Ponovite za vse druge nalepke, ki jih je treba premakniti.
Končni grafikon
Oglejte si video
Video zapis
Naučite se Excel iz podcasta, epizoda 2217: Postavite ljudi na krivuljo zvona.
Hej, dobrodošli nazaj v oddaji, jaz sem Bill Jelen. Današnje vprašanje Jimmyja na mojem seminarju v Huntsvilleu v Alabami. Jimmy ima podatke, te podatke želi strniti in nato rezultate narisati na zvončno krivuljo.
Vredu? Zdaj je eden mojih najbolj priljubljenih videoposnetkov v YouTubu ta: številka 1663, Ustvari krivuljo zvona v Excelu. In glede na povprečje in standardni odklon sem ugotovil nizek, ki je 3-krat standardni odklon manjši od srednjega, in visok - 3-krat standardni odklon večji od srednjega - kjer je razlika - in vrsto vrednosti X tukaj in za ugotovitev višine uporabite to funkcijo: = NORM.DIST vrednosti X, povprečje in standardni odklon, vejica false (= NORM.DIST (A10, $ B $ 2, $ 3 USD, LAŽNO)).
In če malo pomislite, ta video v resnici tukaj uporablja samo vrsto ponarejenih vrednosti X, da bi dobili lepo videti krivuljo. In tu bomo uporabili isti koncept, ampak namesto ponarejenih vrednosti X bomo dejansko imeli ljudi spodaj in potem bo višina popolnoma enaka formula. Vredu.
Torej, zdaj je Jimmy hotel ustvariti vrtilno tabelo. Torej bomo vstavili vrtilno tabelo, jo postavili tukaj na ta list in kliknite V redu. Ljudje po levi strani in nato njihova povprečna ocena. Torej, tako da se začne z vsoto rezultatov, tam dvokliknem in spremenim v povprečje. Super. Zdaj na samem dnu ne želim skupnega seštevka - z desno miškino tipko kliknite in Odstrani skupni seštevek - in te ljudi želimo razporediti visoko na nizko, kar je enostavno narediti v vrtilni tabeli. Podatki, od A do Ž - odlični. Vredu. Zdaj bomo storili popolnoma isto stvar kot v Podcastu 1663, to pa izračuna povprečje in standardni odklon. Torej je povprečje povprečje teh rezultatov in je nato enak standardnemu odklonu teh rezultatov. Vredu. Zdaj, ko to vem, lahko ustvarim svojo vrednost y.
V redu, nekaj stvari bomo naredili tukaj. Najprej iz vrtilne tabele ne morete ustvariti vrtilne tabele - razprševalnega grafikona. Torej bom kopiral vse te podatke in to bom naredil z = D2. Upoštevajte, da sem previden, da z miško ali puščičnimi tipkami ne pokažem nanje. In tu imamo svoje vrednote. Te bodo postale vrednosti X, vrednost Y bo postala = NORM.DIST, tukaj je vrednost x, vejica, za povprečje, to število, pritisnil bom F4, da to zaklenem; za standardni odklon je to število, spet pritisnite F4, da to zaklenete, in kumulativno FALSE. (= NORM.DIST (K2, $ H $ 2, $ H $ 3, FALSE)) In dvokliknili bomo, da bomo to kopirali. Vredu. In potem, ne izbirajte nalepk,samo izberite XY in vstavili bomo razpršeni grafikon s črtami - lahko izberete tistega z ukrivljenimi črtami ali malo ravnih črt. Tukaj bom šel s takimi ukrivljenimi črtami. In zdaj imamo vse svoje ljudi postavljene na zvonasto krivuljo.
Vredu. Zdaj, nekaj stvari - nekaj stvari s formatiranjem - bomo storili tukaj: Najprej dvoklikni tukaj dol po lestvici in zdi se, da je najnižje število verjetno nekje 50 - zato bom nastavite min 50 - in naše največje število - naše največje število - je 88 -, zato bom nastavil največ 90. V redu. In zdaj moramo te točke označiti. Če uporabljate Excel 2013 ali novejšo različico, je to enostavno narediti; če pa uporabljate starejšo različico Excela, se boste morali vrniti in uporabiti dodatek Robe Bovey's Chart Labeler, da bodo te oznake točk prišle od nekega mesta, ki ga ni na grafikonu. V redu, torej začnemo tukaj. Dodali bomo podatkovne nalepke, dodaja pa številke in izgledajo grozno. Prišel bom sem in rekel, da želim več možnosti, možnosti etikete,in želim dobiti vrednost iz celic - vrednost iz celic. Vredu? Torej je Obseg celic tam, kliknite V redu. Zelo pomembno je uporabiti vrednost iz celic, preden počistim vrednost Y. Izgleda dobro. Tega se bom znebil. Zdaj pa je celoten ključ - ker imate nekaj ljudi, ki se nekako prepisujejo - poskusiti karto narediti čim večjo. Tja gor ne rabimo. Zakaj? Samo to izbriši. In še vedno vidim, da so Kelly in Lou ter Andy in Flo skoraj na istem mestu; Jared in-- V redu. Torej, zdaj bo to moteče - tiste, ki se prekrivajo. Ko pa kliknemo nalepko, smo izbrali vse nalepke, nato znova kliknemo na etiketo in izberemo samo eno nalepko. Vredu? In zdaj. zelo previdno. poskusite klikniti Andyja in ga povlecite levo navzgor.Zdi se, da sta Jared in Ike skupaj, zato je zdaj, ko sem v načinu izbire ene nalepke, lažje. In potem Kelly in Lou, jih povlecite gor. Mogoče obstaja boljše mesto, ki ne pretirava z Loujem, ali pa ga lahko povlečem na obe strani. Torej, kaj imamo? Začeli smo s kupom podatkov, ustvarili vrtilno tabelo, ugotovili povprečje in standardni odklon, ki nam omogoča le, da ugotovimo višino - položaj Y za vsako od teh točk in višino teh, upajmo, pripeljali bomo ljudi do lepe zvončaste krivulje v obliki parabole.Torej, kaj imamo? Začeli smo s kupom podatkov, ustvarili vrtilno tabelo, ugotovili povprečje in standardni odklon, ki nam omogoča le, da ugotovimo višino - položaj Y za vsako od teh točk in višino teh, upajmo, pripeljali bomo ljudi do lepe zvončaste krivulje v obliki parabole.Torej, kaj imamo? Začeli smo s kupom podatkov, ustvarili vrtilno tabelo, ugotovili povprečje in standardni odklon, ki nam omogoča le, da ugotovimo višino - položaj Y za vsako od teh točk in višino teh, upajmo, pripeljali bomo ljudi do lepe zvončaste krivulje v obliki parabole.
Obožujem to Jimmyjevo vprašanje, tega vprašanja ni v tej knjigi, bo pa ob naslednjem pisanju te knjige. Moral bom dodati to - to je super prošnja in kul majhen trik. Zvonske krivulje so v Excelu zelo priljubljene.
Oglejte si mojo knjigo LIVe, 54 največjih nasvetov za Excel vseh časov.
V redu, zaključek te epizode: Jimmy iz Huntsvillea želi ljudi urediti na zvonasto krivuljo. Torej uporabljamo vrtilno tabelo, da ugotovimo povprečni rezultat, razvrstimo vrtilne tabele na ocene - razvrščene visoko do nizko - znebimo vsote na dnu - to bodo v bistvu vrednosti X - in nato odmaknite vstran, izračunajte povprečni in standardni odklon teh rezultatov ter z uporabo formul kopirajte podatke iz vrtilne tabele v novo območje, ker ne morete imeti grafikona XY, ki se seka z vrtilno tabelo. Izračunajte vrednost y za vsako osebo z = NORM.DIST njihove vrednosti x, povprečjem, standardnim odklonom, vejico FALSE; ustvarite XY razpršeni grafikon z gladkimi črtami - če ste Excel 2010 ali starejši, boste uporabili dodatek Chart Labeler Rona Boveyja. Poskrbel bom za google, ker,v primeru, da Rob spremeni svoj URL, tukaj ne želim napačnega URL-ja. V Excelu 2013 so imele Data Labels, From Cells, ime, nato pa nekatere prilagoditve - spremenite lestvico na dnu, jaz jih spremenim in Max, nato pa premaknem nalepke, ki se med seboj preveč nastavijo.
Če želite prenesti delovni zvezek iz današnjega videoposnetka, uporabite URL v opisu YouTube. Želim se zahvaliti Jimmyju za to čudovito vprašanje v Huntsvilleu in se vam zahvaljujem, da ste se ustavili. Se vidimo naslednjič za še eno oddajo od.
Prenesite datoteko Excel
Če želite prenesti datoteko excel: place-people-on-bell-curve.xlsx
Hvala Jimmyju v Huntsvilleu za današnje vprašanje!
Excel Misel dneva
Za nasvet glede Excela sem prosil prijatelje Excel Excel. Današnja misel o premišljevanju:
"Če ste Excel v zadnjem mesecu postavili v način ročnega preračunavanja, je čas za vrtenje moči (ročnega načina nikoli več ne boste potrebovali)"
Rob Collie
