Jimmy Huntsvillessa haluaa piirtää kellokäyrän, joka näyttää useiden ihmisten keskimääräiset pisteet. Kun Jimmy esitti kysymyksen Power Excel -seminaarini aikana, ajattelin yhtä suosituimmista videoistani YouTubessa.
Podcast 1665: ssä - Luo soittokäyrä Excelissä, selitän, että kellokäyrän luomiseksi sinun on laskettava keskiarvo ja keskihajonta. Sitten tuotan x-akselilla 30 pistettä, jotka kattavat hypoteettisen ihmisjoukon. Tuossa videossa tuotin sen, joka ulottui -3 vakiopoikkeamasta + 3 vakiopoikkeamaan keskiarvon ympärille.
Esimerkiksi, jos keskiarvo on 50 ja keskihajonta 10, luon x-akselin, joka juoksi välillä 70-130. Kunkin pisteen korkeus lasketaan käyttämällä =NORM.DIST(x,mean,standard deviation,False).
Yllä olevassa kuvassa luvut A10: A40 ovat olennaisesti "väärennettyjä datapisteitä". Luon 31 numeroa luomaan mukavan tasaisen käyrän. Jos olisin käyttänyt vain 7 datapistettä, käyrä näyttäisi tältä:
Jimmyn tietojoukon työntekijöiden todelliset keskimääräiset pisteet ovat olennaisesti pisteitä x-akselilla. Jotta ne sopisivat kellokäyrään, sinun on selvitettävä jokaisen työntekijän korkeus tai Y-arvo.
Toimi seuraavasti:
-
Lajittele tiedot niin, että pisteet näyttävät alimmasta korkeimpaan.
Lajittele tiedot - Laske keskiarvo funktion AVERAGE avulla.
- Laske keskihajonta käyttämällä STDEV-toimintoa.
-
Laske Y-arvo pisteiden oikealla puolella käyttäen
=NORM.DIST(L2,$H$2,$H$3,FALSE). Y-arvo tuottaa kunkin henkilön pisteen korkeuden kellokäyrää pitkin. NORM.DIST-toiminto huolehtii ihmisten piirtämisestä keskiarvon lähellä korkeammalle paikalle kuin ihmiset ylä- tai alaosaan.
Luo sarja Y-arvoja. - Valitse tietosi kohdassa L1: M15
-
Pariton vika alkoi äskettäin näkyä Excelissä, joten menestyksen varmistamiseksi valitse Lisää-välilehdestä Kaikki kaaviot.
Valintaikkunan käynnistysohjelma vie sinut kaikkiin kaaviotyyppeihin Napsauta Lisää kaavio -valintaikkunassa Kaikki kaaviot -välilehteä. Napsauta vasemmalla XY (hajonta). Valitse toinen kuvake yläreunasta. Valitse esikatselu oikealta.
Neljä napsautusta valitaksesi kaavion Sisäinen kellokäyräsi näyttää tältä:
Kellokäyrä
Puhdista soittokäyrä seuraavasti:
- Napsauta otsikkoa ja paina Poista-näppäintä.
- Kaksoisnapsauta mitä tahansa Y-akselin numeroa kaavion alareunassa. Muotoile akseli -paneeli tulee näkyviin.
-
Kirjoita uudet arvot pienimmälle ja suurimmalle. Alueen tulisi olla juuri niin leveä, että kaikki näkyvät kaaviossa. Käytin 50-90.
Muuta minimi- ja maksimiarvoja - Tee kaaviosta leveämpi vetämällä kaavion reunaa.
- Napsauta kaavion oikealla puolella olevaa + -kuvaketta ja valitse Data Labels. Älä huoli siitä, että tarroilla ei ole vielä järkeä.
- Kaksoisnapsauta yhtä tarraa avataksesi Format Labels -paneelin.
- Paneelin yläosassa on neljä kuvaketta. Valitse kuvake, joka näyttää sarakekaavion.
- Laajenna paneelin osa napsauttamalla Nimiominaisuudet-kohdan vieressä olevaa nuolta.
- Valitse arvo soluista. Esiin tulee valintaikkuna, jossa kysytään tarrojen sijaintia. Valitse nimet sarakkeesta K2: K15.
-
Poista Format Data Label -paneelista Y-arvojen valinta. On tärkeää, että vaihe 15 on suoritettu ennen vaiheen 16 suorittamista, tai muuten tahattomasti poistat tarrat.
Hanki tarrat soluja, jotka sisältävät nimiä.
Huomautus
Mahdollisuus hankkia tarroja soluista lisättiin Excel 2013. Jos käytät Excel 2010: tä tai vanhempaa versiota, lataa XY Chart Labeler -apuohjelma Rob Boveylta. (Google löytää sen).
Tarkista tässä vaiheessa, onko sinulla kaaviotunnisteita, jotka törmäävät toisiinsa. Korjaa ne noudattamalla näitä ohjeita huolellisesti.
- Kaksoisnapsauta yhtä kaavion tarraa. Tämä valitsee kaikki tarrat.
- Napsauta yhtä tarraa, joka on toisen tarran päällä, napsauttamalla sitä.
- Vie hiiri tarran eri osien päälle, kunnes näet nelipäisen nuolen. Napsauta ja vedä tarra uuteen paikkaan.
-
Kun olet valinnut vain yhden tunnisteen, voit valita kyseisen tunnisteen napsauttamalla mitä tahansa muuta tarraa. Toista kaikki muut siirrettävät tarrat.
Lopullinen kaavio
Katso video
Videon transkriptio
Opi Excel Podcastista, jaksosta 2217: Aseta ihmiset kellokäyrälle.
Hei, tervetuloa takaisin netcastiin, olen Bill Jelen. Tämän päivän kysymys, Jimmyltä seminaarissani Huntsvillessä Alabamassa. Jimmyllä on tietoja, hän haluaa tiivistää nämä tiedot ja piirtää tulokset kellokäyrälle.
Hyvä on? Nyt yksi suosituimmista videoistani YouTubessa on tämä: numero 1663, Luo kellokäyrä Excelissä. Ja kun otetaan huomioon keskiarvo ja keskihajonta, tajusin matalan, joka on 3 kertaa keskihajonta pienempi kuin keskiarvo, ja korkean - 3 kertaa keskihajonnan enemmän kuin keskiarvo - missä aukko on - ja sarjassa X-arvoja täällä, ja selvittääksesi korkeuden, käytä tätä toimintoa: = NORM.DIST X-arvosta, keskiarvosta ja keskihajonnasta, pilkulla false (= NORM.DIST (A10, $ B $ 2, $ B $ 3, FALSE)).
Ja jos ajattelet sitä, tämä video käyttää oikeastaan vain sarjaa väärennettyjä X-arvoja täällä saadakseen mukavan näköisen käyrän. Ja käytämme samaa käsitettä täällä, mutta väärennettyjen X-arvojen sijaan meillä on tosiasiassa ihmiset täällä ja sitten korkeus on täsmälleen sama kaava. Hyvä on.
Joten nyt Jimmy halusi luoda pivot-taulukon. Joten lisäämme, PivotTable, laitamme sen tähän tälle sivulle, napsauta OK. Ihmiset vasemmalla puolella ja sitten niiden keskiarvo. Selvä, joten se alkaa Sum of Score -toiminnosta, kaksoisnapsautan sitä ja vaihdan sen keskiarvoksi. Loistava. Nyt, aivan alareunassa, en halua loppusummaa - napsauta hiiren kakkospainikkeella ja Poista loppusumma - ja haluamme järjestää nämä ihmiset korkeista mataliin, ja tämä on helppo tehdä pivot-taulukossa. Tiedot, A - Z - erinomainen. Hyvä on. Nyt aiomme tehdä täsmälleen saman asian kuin aikaisemminkin Podcast 1663: ssa, ja se laskee keskiarvon ja keskihajonnan. Joten keskiarvo on näiden pisteiden keskiarvo ja yhtä suuri kuin näiden pisteiden keskihajonta. Hyvä on. Nyt kun tiedän sen, pystyn luomaan y-arvon.
Selvä, joten muutama asia, jonka teemme täällä. Ensinnäkin, et voi luoda pivot-taulukkoa - sirontataulukkoa - pivot-taulukosta. Joten aion kopioida kaikki nämä tiedot ja teen vain sen = D2. Huomaa, että en käytä hiirtä tai nuolinäppäimiä osoittamaan niihin. Ja siis meillä on arvomme täällä. Näistä tulee X-arvoja, Y-arvosta tulee = NORM.DIST, tässä on x-arvo, pilkku, keskiarvolle, että luku, lukitsen sen painamalla F4; standardipoikkeama on tämä luku, lukitse se painamalla F4 ja kumulatiivinen FALSE. (= NORM.DIST (K2, $ H $ 2, $ H $ 3, FALSE)) Ja kopioimme sen kaksoisnapsauttamalla sitä. Hyvä on. Älä sitten valitse tarroja,Valitse vain XY ja lisäämme sirontakaavion - voit joko valita kaarevan viivan tai hieman suoran. Täällä menen tällaisten kaarevien viivojen kanssa. Ja nyt kaikki ihmiset on asetettu kellokäyrälle.
Hyvä on. Jotkut asiat - jotkut muotoilutyyppiset asiat - aiomme tehdä täällä: Ensinnäkin, kaksoisnapsauta tätä alas asteikolla, ja näyttää siltä, että pienin lukumme on todennäköisesti noin 50 - joten aion aseta minimi 50 - ja suurin luku - suurin luku - on 88 - joten asetan maksimi 90. Hyvä on. Ja nyt meidän on merkittävä nämä kohdat. Jos olet Excel 2013: ssa tai uudemmassa, tämä on helppo tehdä; mutta jos olet vanhemmassa Excel-versiossa, sinun on palattava takaisin ja käytettävä Rob Boveyn Chart Labeler -apuohjelmaa, jotta nämä pistetarrat tulevat paikasta, jota ei ole kaaviossa. Selvä, joten aloitamme täältä. Lisäämme tietotarrat, ja se lisää numeroita, ja ne näyttävät kauheilta. Tulen tänne ja sanon, että haluan lisää vaihtoehtoja, etikettiasetuksia,ja haluan saada arvon soluista - arvon soluista. Hyvä on? Joten solualue on oikeassa, napsauta OK. Erittäin tärkeää käyttää solujen arvoa, ennen kuin poistan Y-arvon valinnan. Se alkaa näyttää hyvältä. Pääsen eroon tästä. Nyt koko avain täällä - koska sinulla on joitain ihmisiä, jotka korvaavat toisiaan - on yrittää tehdä kaavio mahdollisimman suureksi. Emme tarvitse otsikkoa sinne. Miksi? Poista vain se. Ja näen edelleen, että Kelly ja Lou ja Andy ja Flo ovat melkein samassa paikassa; Jared ja - Okei. Joten nyt tämä tulee olemaan turhauttavaa - nämä ovat päällekkäisiä. Mutta kun napsautamme tarraa, valitsimme kaikki tarrat ja napsautamme sitten tarraa uudelleen, ja valitsemme vain yhden tarran. Hyvä on? Joten nyt. hyvin huolellisesti. yritä napsauttaa Andy ja vedä Andy vain vasemmalle.Näyttää siltä, että Jared ja Ike ovat yhdessä, joten nyt kun olen yhden tarran valintatilassa, se on helpompaa. Ja sitten Kelly ja Lou, vedä heidät ylöspäin niin. Ehkä on parempi paikka, joka ei ylitä Loua, tai jopa, kuten täällä, voin vetää sen kummallekin puolelle. Selvä, niin mitä meillä on? Olemme aloittaneet joukon tietoja, luoneet pivot-taulukon, selvittäneet keskiarvon ja keskihajonnan, jonka avulla voimme vain selvittää korkeuden - Y-sija kullekin näistä pisteistä ja näiden korkeuden, toivottavasti, saamme ihmiset sellaiseen mukavaan parabolin muotoiseen kellokäyrään.Selvä, niin mitä meillä on? Olemme aloittaneet joukon tietoja, luoneet pivot-taulukon, selvittäneet keskiarvon ja keskihajonnan, jonka avulla voimme vain selvittää korkeuden - Y-sija kullekin näistä pisteistä ja näiden korkeuden, toivottavasti, saamme ihmiset sellaiseen mukavaan parabolin muotoiseen kellokäyrään.Selvä, niin mitä meillä on? Olemme aloittaneet joukon tietoja, luoneet pivot-taulukon, selvittäneet keskiarvon ja keskihajonnan, jonka avulla voimme vain selvittää korkeuden - Y-sija kullekin näistä pisteistä ja näiden korkeuden, toivottavasti, saamme ihmiset sellaiseen mukavaan parabolin muotoiseen kellokäyrään.
Rakastan tätä Jimmyn kysymystä, tätä kysymystä ei ole tässä kirjassa, mutta se on seuraavan kerran, kun kirjoitan tämän kirjan. Minun on lisättävä tämä - se on hieno pyyntö ja hieno pieni temppu. Kellokäyrät ovat erittäin suosittuja Excelissä.
Katso kuitenkin kirjani LIVe, 54 kaikkien aikojen suurinta Excel-vinkkiä.
Selvä, yhteenveto tästä jaksosta: Jimmy Huntsvillesta haluaa järjestää ihmiset kellokäyrälle. Joten käytämme pivot-taulukkoa keskimääräisen pistemäärän selvittämiseen, lajittelemme pivot-taulukot pisteisiin - järjestetty korkeimmasta pienempään - päästä eroon alareunan loppusummasta - nämä ovat pohjimmiltaan X-arvot - ja sitten sivulle, laske näiden pisteiden keskiarvo ja keskihajonta ja kopioi kaavojen avulla tiedot pivot-taulukosta uudelle alueelle kaavojen avulla, koska sinulla ei voi olla XY-kaaviota, joka leikkaa pivot-taulukon. Laske y-arvo jokaiselle henkilölle, jolla = NORM.DIST heidän x-arvostaan, keskiarvosta, keskihajonnasta, pilkusta EPÄTOSI; luo XY-sirontakaavio, jossa on sileät viivat - jos olet Excel 2010 tai vanhempi, aiot käyttää Ron Boveyn Chart Labeler -lisäosaa. Pyydän teitä googeldamaan sitä, koskaJos Rob muuttaa URL-osoitettaan, en halua väärää URL-osoitetta. Excel 2013: ssa oli Data Labels, Soluista määritettävä nimet ja sitten joitain säätöjä - muutettava asteikkoa pohjassa, muutan ne sisään ja Maxiin ja siirrän sitten tarroja, jotka asettavat liikaa toisiaan.
Voit ladata työkirjan tämän päivän videosta käyttämällä YouTube-kuvauksen URL-osoitetta. Haluan kiittää Jimmyä tästä mahtavasta kysymyksestä Huntsvillessä ja haluan kiittää sinua pysähtymisestä. Nähdään ensi kerralla toisesta netcastista.
Lataa Excel-tiedosto
Excel-tiedoston lataaminen: place-people-on-bell-curve.xlsx
Kiitos Huntsvillen Jimmylle tämänpäiväisestä kysymyksestä!
Päivän Excel-ajatus
Olen pyytänyt Excel Master -kaveriltani neuvoja Excelistä. Tämän päivän ajatus miettiä:
"Jos olet asettanut Excelin manuaaliseen uudelleenlaskentatilaan viimeisen kuukauden aikana, on aika virran kääntämiseen (et enää koskaan tarvitse manuaalista tilaa)"
Rob Collie
