Smjestite ljude na krivulju zvona - Excel savjeti

Sadržaj

Jimmy u Huntsvilleu želi zacrtati krivulju zvona koja prikazuje prosječne rezultate nekoliko ljudi. Kad je Jimmy postavio pitanje tijekom mog Power Excel seminara, sjetio sam se jednog od svojih popularnijih videozapisa na YouTubeu.

U Podcastu 1665 - Stvaranje krivulje zvona u Excelu, objašnjavam da za izradu krivulje zvona morate izračunati srednju i standardnu ​​devijaciju. Zatim generiram 30 točaka duž osi x koje obuhvaćaju hipotetičku populaciju ljudi. U tom sam videozapisu generirao raspon od -3 standardnih odstupanja do + 3 standardna odstupanja oko srednje vrijednosti

Na primjer, ako imate srednju vrijednost 50 i standardno odstupanje 10, stvorio bih x-os koja se kretala od 70 do 130. Visina svake točke izračunava se pomoću =NORM.DIST(x,mean,standard deviation,False).

Generirajte krivulju zvona

Na gornjoj slici brojevi u A10: A40 u osnovi su "lažne podatkovne točke". Generiram 31 broj kako bih stvorio lijepu glatku krivulju. Da sam koristio samo 7 točaka podataka, krivulja bi izgledala ovako:

Koristeći manje podataka, krivulja zvona i dalje radi

Za Jimmyjev niz podataka stvarni prosječni rezultati njegovih zaposlenika u osnovi su točke duž x osi. Da biste ih smjestili na krivulju zvona, morate shvatiti visinu ili Y vrijednost svakog zaposlenika.

Prati ove korake:

  1. Razvrstajte podatke tako da se ocjene prikazuju najniže do najviše.

    Poredaj podatke
  2. Izračunajte srednju vrijednost pomoću funkcije PROSJEČNO.
  3. Izračunajte standardno odstupanje pomoću funkcije STDEV.

  4. Izračunajte vrijednost Y desno od rezultata pomoću =NORM.DIST(L2,$H$2,$H$3,FALSE). Vrijednost Y generirat će visinu točke svake osobe duž krivulje zvona. Funkcija NORM.DIST pobrinut će se za crtanje ljudi u blizini srednje vrijednosti na višem mjestu od ljudi blizu vrha ili dna.

    Generirajte niz Y vrijednosti.
  5. Odaberite svoje podatke u L1: M15

  6. Čudna se pogreška nedavno počela pojavljivati ​​u Excelu pa kako biste osigurali uspjeh, odaberite Sve karte na kartici Umetanje.

    Pokretač dijaloškog okvira vodi vas do svih vrsta grafikona

    U dijaloškom okviru Umetanje grafikona kliknite karticu Sve karte. Pritisnite XY (Scatter) lijevo. Odaberite drugu ikonu na vrhu. Odaberite pregled s desne strane.

    Četiri klika za odabir grafikona

    Vaša početna krivulja zvona izgledat će ovako:

    Krivulja zvona

Da biste očistili krivulju zvona, slijedite ove korake:

  1. Kliknite naslov i pritisnite tipku Delete.
  2. Dvaput kliknite bilo koji broj duž Y osi na dnu grafikona. Pojavit će se ploča Format Axis.

  3. Upišite nove vrijednosti za Minimum i Maximum. Raspon ovdje trebao bi biti dovoljno širok da prikaže sve na karti. Koristio sam 50 do 90.

    Promijenite minimum i maksimum
  4. Proširite grafikon povlačenjem ruba grafikona.
  5. Kliknite ikonu + s desne strane grafikona i odaberite Data Labels (Oznake podataka). Ne brinite da etikete još nemaju smisla.
  6. Dvaput kliknite jednu naljepnicu da biste otvorili ploču Oblikovanje naljepnica.
  7. Na vrhu ploče nalaze se četiri ikone. Odaberite ikonu koja prikazuje grafikon stupaca.
  8. Kliknite strelicu pored Opcije oznake da biste proširili taj dio ploče.
  9. Odaberite vrijednost iz ćelija. Pojavit će se dijaloški okvir u kojem se traži mjesto oznaka. Odaberite imena u K2: K15.

  10. I dalje na ploči Oblikovanje oznake podataka poništite odabir vrijednosti Y. Važno je dovršiti 15. korak prije nego što napravite 16. ili ćete nehotice ukloniti naljepnice.

    Dohvatite naljepnice iz ćelija koje sadrže imena.

Bilješka

Mogućnost dobivanja naljepnica iz ćelija dodana je u programu Excel 2013. Ako koristite Excel 2010 ili stariji, preuzmite dodatak XY Chart Labeler od Roba Boveyja. (Google da ga pronađe).

U ovom trenutku pogledajte imate li oznake grafikona koje se međusobno ruše. Da biste ih popravili, pažljivo slijedite ove korake.

  1. Jednokratni klik na jednu naljepnicu grafikona. Ovim se odabiru sve oznake.
  2. Jednim klikom na jednu od naljepnica koja se nalazi na vrhu druge naljepnice odaberite samo tu naljepnicu.
  3. Zadržite pokazivač iznad raznih dijelova naljepnice dok ne vidite četveroglavu strelicu. Kliknite i povucite naljepnicu na novo mjesto.

  4. Nakon što odaberete samo jednu naljepnicu, možete kliknuti bilo koju drugu naljepnicu da biste je odabrali. Ponovite za sve ostale naljepnice koje treba premjestiti.

    Konačni grafikon

Gledaj video

Prijepis videozapisa

Naučite Excel iz Podcasta, epizoda 2217: Postavite ljude na krivulju zvona.

Hej, dobrodošao natrag u prijenos uživo, ja sam Bill Jelen. Današnje pitanje, Jimmyja na mom seminaru u Huntsvilleu, Alabama. Jimmy ima podatke, želi ih sažeti, a zatim rezultate ucrtati u krivulju zvona.

U redu? E sad, jedan od mojih najpopularnijih videozapisa na YouTubeu je ovaj: broj 1663, Stvori krivulju zvona u Excelu. A s obzirom na srednju i standardnu ​​devijaciju, skužio sam nisku, koja je 3 puta standardna devijacija manja od srednje, a visoka - 3 puta standardna devijacija veća od srednje vrijednosti - gdje je jaz - i ovdje se nalazi niz X vrijednosti, a da biste izračunali visinu, upotrijebite ovu funkciju: = NORM.DIST vrijednosti X, srednja vrijednost i standardno odstupanje, zarez false (= NORM.DIST (A10, $ B $ 2, $ 3 USD, LAŽNO)).

I ako razmislite, ovaj video ovdje zapravo koristi samo niz lažnih X vrijednosti kako bi se dobila lijepa krivulja. I mi ćemo ovdje upotrijebiti isti koncept, ali umjesto lažnih vrijednosti X, zapravo ćemo imati ljude ovdje dolje i tada će visina biti potpuno ista formula. U redu.

Dakle, sada je Jimmy želio stvoriti stožernu tablicu. Pa ćemo umetnuti zaokretnu tablicu, staviti je ovdje na ovaj list, kliknuti U redu. Ljudi dolje s lijeve strane, a zatim njihov Prosječni rezultat. U redu, dakle, počinje sa zbrojem bodova, dvaput ću tamo kliknuti i to promijeniti u prosjek. Sjajno. Sada, na samom dnu, ne želim ukupan zbroj - kliknite desnim gumbom miša i ukloni ukupan zbroj - i želimo te ljude rasporediti visoko na nisko, a to je lako učiniti u zaokretnoj tablici. Podaci, od A do Ž - izvrsni. U redu. Sada ćemo napraviti potpuno istu stvar kao što smo to radili još u Podcastu 1663, i to je izračunavanje srednje vrijednosti i standardne devijacije. Dakle, srednja vrijednost je prosjek ovih rezultata, a zatim je jednaka standardnoj devijaciji tih rezultata. U redu. Sad kad to znam, u stanju sam stvoriti svoju y vrijednost.

U redu, ovdje ćemo napraviti nekoliko stvari. Prvo, ne možete stvoriti zaokretnu tablicu - dijagram raspršenja - od zaokretne tablice. Dakle, kopirat ću sve ove podatke i to ću učiniti s = D2. Primijetite da pazim da ne upotrijebim miš ili tipke sa strelicama da pokažem na njih. I ovdje imamo svoje vrijednosti. To će postati X vrijednosti, Y vrijednost će postati = NORM.DIST, ovdje je x vrijednost, zarez, za srednju vrijednost tog broja, pritisnut ću F4 da to zaključam; za standardno odstupanje to je ovaj broj, opet pritisnite F4 da to zaključate i kumulativni FALSE. (= NORM.DIST (K2, $ H $ 2, $ H $ 3, FALSE)) I dvostruko ćemo kliknuti da to kopiramo. U redu. I onda, nemojte birati naljepnice,samo odaberite XY, a mi ćemo umetnuti raspršeni grafikon s linijama - možete odabrati onaj s zakrivljenim crtama ili malo ravnih crta. Evo, ići ću s ovakvim zakrivljenim crtama. I sada smo sve svoje ljude postavili na krivulju zvona.

U redu. Sad ćemo neke stvari - neke stvari s formatiranjem - ovdje ćemo napraviti: Prvo dvaput pritisnite ovdje duž skale i čini se da je naš najmanji broj vjerojatno negdje oko 50 - pa ću postavite min 50 - a naš najveći broj - naš najveći broj - je 88 - pa ću postaviti maksimum 90. U redu. A sada, ove točke moramo označiti. Ako imate Excel 2013 ili noviju verziju, to je jednostavno učiniti; ali ako ste u starijoj verziji Excela, morat ćete se vratiti i koristiti dodatak Chabe Labeler Roba Boveyja kako bi oznake točaka došle s mjesta koje nije na grafikonu. U redu, pa počinjemo ovdje. Dodati ćemo oznake podataka, a one dodaju brojeve i izgledaju užasno. Doći ću ovdje i reći da želim više opcija, opcije oznaka,i želim dobiti vrijednost iz stanica - vrijednost iz stanica. U redu? Dakle, raspon ćelija je upravo tamo, kliknite U redu. Vrlo je važno koristiti vrijednost iz ćelija prije nego što poništim vrijednost Y. Počinje izgledati dobro. Riješit ću se ovoga. Sada je cijeli ključ - jer imate neke ljude koji se nekako prepisuju - pokušati napraviti grafikon što je moguće većim. Ne treba nam put gore. Zašto? Samo to izbriši. I dalje vidim da su Kelly, Lou i Andy i Flo gotovo na istom mjestu; Jared i-- U redu. Dakle, ovo će biti frustrirajuće - one koje se preklapaju. Ali kad kliknemo na naljepnicu, odabrali smo sve naljepnice, a zatim ponovno kliknuli na naljepnicu i odabrali smo samo jednu naljepnicu. U redu? Pa sad. vrlo oprezno. pokušajte kliknuti Andyja i povucite Andyja gore ulijevo.Izgleda da su Jared i Ike zajedno, pa je sada, kad sam u načinu odabira jedne naljepnice, lakše. A onda ih Kelly i Lou povucite gore. Možda postoji neko bolje mjesto koje ne pretjeruje s Louom, ili ga čak mogu povući s obje strane. Pa, što imamo? Počeli smo s hrpom podataka, kreirali zaokretnu tablicu, shvatili srednju i standardnu ​​devijaciju, koja nam samo omogućuju da utvrdimo visinu - položaj Y za svaki od tih rezultata i visinu onih, nadamo se, odvest ćemo ljude u lijepu krivulju zvona u obliku parabole, poput te.Pa, što imamo? Počeli smo s hrpom podataka, kreirali zaokretnu tablicu, shvatili srednju i standardnu ​​devijaciju, koja nam samo omogućuju da utvrdimo visinu - položaj Y za svaki od tih rezultata i visinu onih, nadamo se, odvest ćemo ljude u lijepu krivulju zvona u obliku parabole, poput te.Pa, što imamo? Počeli smo s hrpom podataka, kreirali zaokretnu tablicu, shvatili srednju i standardnu ​​devijaciju, koja nam samo omogućuju da utvrdimo visinu - položaj Y za svaki od tih rezultata i visinu onih, nadamo se, odvest ćemo ljude u lijepu krivulju zvona u obliku parabole, poput te.

Sviđa mi se Jimmyjevo pitanje, ovo pitanje nije u ovoj knjizi, ali bit će to sljedeći put kad napišem ovu knjigu. Morat ću dodati ovo - to je super zahtjev i cool mali trik. Zvone su vrlo popularne u programu Excel.

Ali pogledajte moju knjigu LIVe, 54 najveća Excel savjeta svih vremena.

U redu, završetak ove epizode: Jimmy iz Huntsvillea želi rasporediti ljude na krivulji zvona. Dakle, koristimo zaokretnu tablicu za utvrđivanje prosječnog rezultata, razvrstavanje zaokretnih tablica prema rezultatima - poredani od visokih do najnižih - riješimo se ukupnog zbroja na dnu - to će u osnovi biti X vrijednosti - a zatim odmaknite u stranu, izračunajte prosječnu i standardnu ​​devijaciju tih rezultata i upotrijebite formule za kopiranje podataka iz zaokretne tablice u novi raspon, jer ne možete imati XY grafikon koji se siječe sa zaokretnom tablicom. Izračunajte vrijednost y za svaku osobu s = NORM.DIST njihove x-vrijednosti, sredinom, standardnim odstupanjem, zarezom FALSE; stvorite XY raspršeni grafikon s glatkim linijama - ako ste Excel 2010 ili stariji, upotrebljavat ćete dodatak Chart Labeler Rona Boveyja. Tražit ću da to poguglate jer,u slučaju da Rob promijeni svoj URL, ovdje ne želim pogrešan URL. U programu Excel 2013, oznake podataka, iz ćelija, odredite imena, a zatim neke prilagodbe - promijenite ljestvicu na dnu, ja ih promijenim u i Max, a zatim premjestite naljepnice koje međusobno prekomponiraju.

Da biste preuzeli radnu knjigu iz današnjeg videozapisa, upotrijebite URL u opisu YouTubea. Želim zahvaliti Jimmyju na ovom sjajnom pitanju u Huntsvilleu i želim vam zahvaliti što ste navratili. Vidimo se sljedeći put za još jedan prijenos od.

Preuzmite datoteku Excel

Da biste preuzeli excel datoteku: place-people-on-bell-curve.xlsx

Hvala Jimmyju u Huntsvilleu na današnjem pitanju!

Excel misao dana

Pitao sam svoje prijatelje Excel Master za savjet o Excelu. Današnja misao za razmišljanje:

"Ako ste u prošli mjesec stavili Excel u način ručnog preračunavanja, vrijeme je za okretanje napajanja (nikad više neće trebati ručni način rada)

Rob Collie

Zanimljivi članci...