Jimmy Huntsvilyje nori suplanuoti varpo kreivę, kurioje būtų rodomi vidutiniai kelių žmonių balai. Kai Jimmy uždavė klausimą mano „Power Excel“ seminaro metu, aš pagalvojau apie vieną iš savo populiariausių vaizdo įrašų „YouTube“.
„Podcast 1665“ - „Sukurkite varpo kreivę programoje„ Excel ““, aš paaiškinu, kad norint sukurti varpo kreivę, reikia apskaičiuoti vidurkį ir standartinį nuokrypį. Tada aš generuoju 30 taškų išilgai ašies, kurie apima hipotetinę žmonių populiaciją. Tame vaizdo įraše sugeneravau nuo -3 standartinių nuokrypių iki + 3 standartinių nuokrypių, esančių aplink vidurkį.
Pvz., Jei jūsų vidurkis yra 50 ir standartinis nuokrypis yra 10, aš sukurčiau x ašį, kuri tęsėsi nuo 70 iki 130. Kiekvieno taško aukštis apskaičiuojamas naudojant =NORM.DIST(x,mean,standard deviation,False).
Aukščiau pateiktame paveikslėlyje A10: A40 skaičiai iš esmės yra „netikri duomenų taškai“. Sugeneruoju 31 skaičių, kad sukurtumėte gražią sklandžią kreivę. Jei būčiau naudojęs tik 7 duomenų taškus, kreivė atrodytų taip:
Kalbant apie Jimmy duomenų rinkinį, faktiniai vidutiniai jo darbuotojų balai iš esmės yra taškai išilgai x ašies. Norėdami juos pritaikyti varpo kreivėje, turite išsiaiškinti kiekvieno darbuotojo ūgį arba Y vertę.
Atlikite šiuos veiksmus:
-
Rūšiuoti duomenis, kad balai būtų rodomi nuo mažiausio iki didžiausio.
Rūšiuoti duomenis - Apskaičiuokite vidurkį naudodamiesi funkcija AVERAGE.
- Apskaičiuokite standartinį nuokrypį naudodami funkciją STDEV.
-
Apskaičiuokite Y vertę balų dešinėje naudodami
=NORM.DIST(L2,$H$2,$H$3,FALSE). Y reikšmė sugeneruos kiekvieno žmogaus taško aukštį išilgai varpo kreivės. NORM.DIST funkcija rūpinsis žmonių planavimu netoli vidutinio aukščiau, nei žmonių, esančių šalia viršaus ar apačios.
Sukurkite Y reikšmių seriją. - Pasirinkite savo duomenis L1: M15
-
Neseniai „Excel“ programoje pradėjo pasirodyti nelyginė klaida, todėl norėdami užtikrinti sėkmę, skirtuke „Įterpti“ pasirinkite „Visos diagramos“.
Dialogo paleidimo priemonė nukreipia jus į visų tipų diagramas Dialogo lange Įterpti diagramą spustelėkite skirtuką Visos diagramos. Kairėje pusėje spustelėkite XY (Scatter). Pasirinkite antrą piktogramą viršuje. Dešinėje pasirinkite peržiūrą.
Keturi paspaudimai, norint pasirinkti diagramą Jūsų vidinė varpo kreivė atrodys taip:
Varpo kreivė
Norėdami išvalyti varpelio kreivę, atlikite šiuos veiksmus:
- Spustelėkite antraštę ir paspauskite klavišą Delete.
- Dukart spustelėkite bet kurį skaičių išilgai Y ašies diagramos apačioje. Pasirodys skydelis Formatuoti ašį.
-
Įveskite naujas reikšmes minimaliai ir maksimaliai. Diapazonas čia turėtų būti pakankamai platus, kad visi būtų rodomi diagramoje. Aš naudojau nuo 50 iki 90.
Pakeiskite mažiausią ir didžiausią - Padarykite diagramą platesnę, vilkdami diagramos kraštą.
- Spustelėkite piktogramą + diagramos dešinėje ir pasirinkite Duomenų etiketės. Nesijaudinkite, kad etiketės dar neturi prasmės.
- Dukart spustelėkite vieną etiketę, kad atidarytumėte skydelį Formatuoti etiketes.
- Skydelio viršuje yra keturios piktogramos. Pasirinkite piktogramą, rodančią stulpelių diagramą.
- Spustelėkite rodyklę šalia Etiketės parinktys, kad išskleistumėte tą skydelio dalį.
- Pasirinkite reikšmę iš langelių. Atsiras dialogo langas, kuriame bus prašoma nurodyti etikečių vietą. Pasirinkite vardus K2: K15.
-
Vis tiek skydelyje „Format Data Label“ panaikinkite Y reikšmių pasirinkimą. Prieš atlikdami 16 veiksmą, svarbu baigti 15 veiksmą, kitaip netyčia pašalinsite etiketes.
Gaukite etiketes iš langelių, kuriuose yra pavadinimai.
Pastaba
Galimybė gauti etiketes iš langelių buvo pridėta programoje „Excel 2013.“. Jei naudojate „Excel 2010“ ar ankstesnę versiją, atsisiųskite „XY Chart Labeler“ priedą iš Robo Bovey. („Google“ tai suras).
Šiuo metu patikrinkite, ar turite diagramų etikečių, kurios griūva viena į kitą. Norėdami juos ištaisyti, atidžiai atlikite šiuos veiksmus.
- Vieną kartą spustelėkite vieną diagramos etiketę. Taip pasirenkamos visos etiketės.
- Vieną kartą spustelėkite vieną iš etikečių, esančių ant kitos etiketės, kad pasirinktumėte tik tą etiketę.
- Užveskite pelės žymeklį virš įvairių etiketės dalių, kol pamatysite keturių galvų rodyklę. Spustelėkite ir vilkite etiketę į naują vietą.
-
Pasirinkę tik vieną etiketę, galite spustelėti bet kurią kitą etiketę, kad pasirinktumėte tą etiketę. Pakartokite visas kitas etiketes, kurias reikia perkelti.
Galutinė diagrama
Žiūrėti video
Vaizdo įrašo nuorašas
Sužinokite „Excel“ iš „Podcast“, 2217 serija: padėkite žmones ant varpo kreivės.
Ei, sveiki sugrįžę į internetą, aš Billas Jelenas. Šiandienos klausimas, kurį pateikė Jimmy mano seminare Hantsvilyje, Alabamos valstijoje. Jimmy turi duomenų, jis nori apibendrinti šiuos duomenis ir tada rezultatus pavaizduoti varpo kreivėje.
Gerai? Dabar vienas iš mano populiariausių „YouTube“ vaizdo įrašų yra šis: numeris 1663, „Excel“ sukurkite varpelio kreivę. Atsižvelgdamas į vidurkį ir standartinį nuokrypį, aš supratau mažą, kuris yra 3 kartus didesnis už standartinį nuokrypį, mažesnį už vidurkį, o aukštas - 3 kartus didesnis už vidutinį nuokrypį - kur yra skirtumas - ir X reikšmių serija čia ir norint išsiaiškinti aukštį, naudokite šią funkciją: = NORM. X vertės, vidurkio ir standartinio nuokrypio NETOLIS, kablelis klaidingas (= NORM.DIST (A10, $ B $ 2, $) B $ 3, NETIESA)).
Ir jei pagalvotumėte, šiame vaizdo įraše iš tikrųjų tik naudojama netikra X reikšmių serija, norint gauti gražiai atrodančią kreivę. Ir mes čia vartosime tą pačią koncepciją, tačiau vietoj padirbtų X reikšmių mes iš tikrųjų turėsime žmones čia, o tada aukštis bus tokia pati formulė. Gerai.
Taigi, dabar Jimmy norėjo sukurti sukamą lentelę. Taigi įdėsime, „PivotTable“, įdėsime jį į šį lapą, spustelėkite Gerai. Žmonės kairėje pusėje ir tada jų vidutinis rezultatas. Gerai, todėl viskas prasideda nuo balų sumos. Aš dukart spustelėsiu ten ir pakeisiu į vidurkį. Puiku. Dabar, pačioje apačioje, nenoriu bendros sumos - dešiniuoju pelės mygtuku spustelėkite ir pašalinkite bendrą sumą, ir mes norime išdėstyti šiuos žmones nuo aukščiausių iki mažiausių, ir tai lengva padaryti suvestinėje lentelėje. Duomenys nuo A iki Z - puikūs. Gerai. Dabar atliksime tą patį, ką darėme dar „Podcast 1663“, ir apskaičiuosime vidurkį ir standartinį nuokrypį. Taigi vidurkis yra šių balų vidurkis, tada lygus tų balų standartiniam nuokrypiui. Gerai. Dabar, kai tai žinau, galiu sukurti savo y vertę.
Gerai, taigi keletą dalykų, kuriuos mes čia padarysime. Pirmiausia, jūs negalite sukurti suvestinės lentelės - sklaidos diagramos - iš suvestinės lentelės. Taigi aš nukopijuosiu visus šiuos duomenis ir tiesiog darysiu tai su = D2. Atkreipkite dėmesį, kad nenaudoju pelės ar rodyklių klavišų, kad nurodytumėte juos. Taigi mes čia turime savo vertybes. Tai taps X reikšmėmis, Y reikšmė taps = NORM.DIST, čia yra x reikšmė, kablelis, jei norite vidurkį, tą skaičių, aš paspausiu F4, kad tai užrakintumėte; standartiniam nuokrypiui tai yra šis skaičius, dar kartą paspauskite F4, kad užblokuotumėte, ir kaupiamą FALSE. (= NORM.DIST (K2, $ H $ 2, $ H $ 3, FALSE)) Ir dukart spustelėkite, kad nukopijuotume žemyn. Gerai. Tada nesirinkite etikečių,tiesiog pasirinkite XY ir mes įterpsime sklaidos diagramą su linijomis - galite pasirinkti tą, kurioje yra kreivos linijos, arba šiek tiek tiesių linijų. Aš eisiu su tokiomis kreivomis linijomis. Ir dabar visi mūsų žmonės yra pastatyti ant varpo kreivės.
Gerai. Kai kuriuos dalykus - kai kuriuos formatavimo tipo dalykus - mes padarysime čia: Pirmiausia dukart spustelėkite čia palei skalę, ir atrodo, kad mūsų mažiausias skaičius tikriausiai yra maždaug 50 - taigi aš nustatykite 50 min., o didžiausias skaičius - 88. Taigi nustatysiu ne daugiau kaip 90. Gerai. Ir dabar turime pažymėti šiuos dalykus. Jei naudojate „Excel 2013“ ar naujesnę versiją, tai lengva padaryti; bet jei naudojate senesnę „Excel“ versiją, turėsite grįžti ir naudoti Robo Bovey „Chart Labeler“ priedą, kad šios taškų etiketės būtų iš kažkurios vietos, kurios nėra diagramoje. Gerai, todėl pradedame čia. Pridėsime duomenų etiketes, o jos prideda skaičius ir jos atrodo siaubingai. Aš ateisiu čia ir pasakysiu, kad noriu daugiau parinkčių, etikečių parinkčių,ir aš noriu gauti vertę iš ląstelių - vertę iš ląstelių. Gerai? Taigi langelių diapazonas yra čia pat, spustelėkite Gerai. Labai svarbu naudoti reikšmę iš langelių, kol aš atžymėsiu Y vertę. Pradeda atrodyti gerai. Atsikratysiu šito. Dabar visas raktas, nes turite keletą žmonių, kurie tarsi perrašo vienas kitą, yra pabandyti padaryti diagramą kuo didesnę. Mums nereikia nukreipimo ten. Kodėl? Tiesiog ištrinkite tai. Aš vis dar matau, kad Kelly ir Lou, Andy ir Flo yra beveik toje pačioje vietoje; Džaredas ir … Gerai. Taigi dabar tai bus nemalonu - šie sutampa. Bet spustelėję etiketę, mes pasirinkome visas etiketes, tada dar kartą spustelėkite etiketę ir mes pasirinkome tik vieną etiketę. Gerai? Taigi dabar. labai atsargiai. pabandykite spustelėti Andy ir tiesiog vilkite Andy į kairę.Panašu, kad Jaredas ir Ike yra kartu, todėl dabar, kai esu vienos etiketės pasirinkimo režime, tai yra lengviau. Ir tada Kelly ir Lou taip juos tempkite aukštyn. Gal yra geresnė vieta, kuri neperlenktų Lou, ar net, pavyzdžiui, čia galiu ją vilkti iš abiejų pusių. Gerai, taigi, ką mes turime? Pradėjome nuo daugybės duomenų, sukūrėme sukamą lentelę, išsiaiškinome vidurkį ir standartinį nuokrypį, kurie mums tik leidžia išsiaiškinti kiekvieno iš šių balų aukštį - Y padėtį ir, tikiuosi, jų aukštį. mes pateksime į tokią gražią parabolės formos varpo kreivę.Gerai, taigi, ką mes turime? Pradėjome nuo daugybės duomenų, sukūrėme sukamą lentelę, išsiaiškinome vidurkį ir standartinį nuokrypį, kurie mums tik leidžia išsiaiškinti kiekvieno iš šių balų aukštį - Y padėtį ir, tikiuosi, jų aukštį. mes pateksime į tokią gražią parabolės formos varpo kreivę.Gerai, taigi, ką mes turime? Pradėjome nuo daugybės duomenų, sukūrėme sukamą lentelę, išsiaiškinome vidurkį ir standartinį nuokrypį, kurie mums tik leidžia išsiaiškinti kiekvieno iš šių balų aukštį - Y padėtį ir, tikiuosi, jų aukštį. mes pateksime į tokią gražią parabolės formos varpo kreivę.
Man patinka šis Jimmy klausimas, šio klausimo nėra šioje knygoje, bet tai bus kitą kartą, kai parašysiu šią knygą. Turėsiu tai pridėti - tai puikus prašymas ir šauni maža gudrybė. Varpo kreivės yra labai populiarios „Excel“ programoje.
Bet peržiūrėkite mano knygą „LIVe“, 54 geriausi visų laikų „Excel“ patarimai.
Gerai, apibendrinimas iš šio epizodo: Jimmy iš Huntsvilio nori sutvarkyti žmones ant varpo kreivės. Taigi mes naudojame suvestinę lentelę, kad išsiaiškintume vidutinį balą, surūšiuosime sukamas lenteles į balus, išdėstytus nuo aukšto iki mažiausio, - atsikratysime viso rezultato apačioje - tai iš esmės bus X reikšmės - tada iš šono apskaičiuokite tų balų vidurkį ir standartinį nuokrypį ir naudokite formules, kad nukopijuotumėte duomenis iš „pivot“ lentelės į naują diapazoną, nes negalite turėti XY diagramos, kertančios su „pivot“ lentele. Apskaičiuokite kiekvieno asmens y reikšmę su = NORM.DIST jų x verte, vidurkis, standartinis nuokrypis, kablelis NETIESA; sukurkite XY sklaidos diagramą su lygiomis linijomis - jei esate „Excel 2010“ ar ankstesnė versija, naudosite Ron Bovey „Chart Labeler“ priedą. Aš paprašysiu, kad jūs tai google, nesjei Robas pakeis savo URL, nenoriu čia neteisingo URL. Programoje „Excel 2013“ duomenų etiketėse, iš langelių nurodykite pavadinimus ir paskui atlikite kai kuriuos koregavimus - pakeiskite skalę apačioje, aš pakeičiu juos į „Max“ ir tada perkeliu etiketes, kurios per daug nustato viena kitą.
Norėdami atsisiųsti darbaknygę iš šiandienos vaizdo įrašo, naudokite „YouTube“ apraše esantį URL. Noriu padėkoti Jimmy už šį nuostabų klausimą Huntsvilyje ir noriu padėkoti už tai, kad užsukote. Pasimatysime kitą kartą kitam internetiniam transliacijai iš.
Atsisiųskite „Excel“ failą
Norėdami atsisiųsti „Excel“ failą: place-people-on-bell-curve.xlsx
Ačiū Jimmy Huntsvilyje už šiandienos klausimą!
„Excel“ dienos mintis
Aš paprašiau savo „Excel Master“ draugų patarimo apie „Excel“. Šiandienos mintis apmąstyti:
"Jei per pastarąjį mėnesį„ Excel “įjungėte rankiniu perskaičiavimo režimu, atėjo laikas sujungti„ power “(daugiau niekada nereikės rankinio režimo)
Robas Kolis
