Jimmy v Huntsville chce nakresliť krivku ukazujúcu priemerné skóre niekoľkých ľudí. Keď mi Jimmy položil otázku počas môjho seminára Power Excel, spomenul som si na jedno z mojich najobľúbenejších videí na YouTube.
V Podcastu 1665 - Vytvorenie zvonovej krivky v programe Excel vysvetľujem, že na vytvorenie zvonovej krivky je potrebné vypočítať strednú a štandardnú odchýlku. Potom generujem 30 bodov pozdĺž osi x, ktoré pokrývajú hypotetickú populáciu ľudí. V tomto videu som vygeneroval rozsah od -3 štandardných odchýlok do +3 štandardných odchýlok okolo priemeru.
Napríklad, ak máte priemer 50 a štandardnú odchýlku 10, vytvoril by som os x, ktorá prebiehala od 70 do 130. Výška každého bodu sa počíta pomocou =NORM.DIST(x,mean,standard deviation,False).
Na obrázku vyššie sú čísla v A10: A40 v podstate „falošné údajové body“. Generujem 31 čísel, aby som vytvoril peknú hladkú krivku. Ak by som použil iba 7 dátových bodov, krivka by vyzerala takto:
Pre Jimmyho dátový súbor sú skutočné priemerné skóre jeho zamestnancov v podstate body pozdĺž osi x. Aby ste ich umiestnili na zvonovú krivku, musíte zistiť výšku alebo hodnotu Y pre každého zamestnanca.
Nasleduj tieto kroky:
-
Zoraďte údaje tak, aby sa skóre javilo od najnižšieho po najvyššie.
Zoraďte údaje - Vypočítajte priemer pomocou funkcie PRIEMER.
- Vypočítajte štandardnú odchýlku pomocou funkcie STDEV.
-
Vypočítajte hodnotu Y napravo od skóre pomocou
=NORM.DIST(L2,$H$2,$H$3,FALSE). Hodnota Y vygeneruje výšku bodu každej osoby pozdĺž zvonovej krivky. Funkcia NORM.DIST sa postará o vykreslenie ľudí v blízkosti strednej hodnoty na vyššom mieste ako ľudí v hornej alebo dolnej časti.
Vytvorte sériu hodnôt Y. - Vyberte svoje údaje v stĺpci L1: M15
-
Nedávno sa v programe Excel začala objavovať zvláštna chyba, aby ste zabezpečili úspech, vyberte na karte Vložiť všetky grafy.
Spustenie dialógového okna vás prevedie všetkými typmi grafov V dialógovom okne Vložiť graf kliknite na kartu Všetky grafy. Kliknite na XY (Scatter) vľavo. Vyberte druhú ikonu v hornej časti. Vyberte ukážku vpravo.
Graf si vyberiete štyrmi kliknutiami Vaša úvodná krivka zvončeka bude vyzerať takto:
Zvonová krivka
Ak chcete vyčistiť zvonovú krivku, postupujte takto:
- Kliknite na názov a stlačte kláves Delete.
- Dvakrát kliknite na ľubovoľné číslo pozdĺž osi Y v dolnej časti grafu. Zobrazí sa panel Formátovať os.
-
Zadajte nové hodnoty pre Minimum a Maximum. Rozsah tu by mal byť dostatočne široký, aby sa zobrazili všetkým v grafe. Použil som 50 až 90.
Zmeňte minimum a maximum - Tabuľku môžete rozšíriť pretiahnutím za okraj tabuľky.
- Kliknite na ikonu + napravo od grafu a vyberte možnosť Dátové štítky. Nebojte sa, že štítky zatiaľ nedávajú zmysel.
- Dvojitým kliknutím na jeden štítok otvoríte panel Formátovať štítky.
- V hornej časti panela sú štyri ikony. Vyberte ikonu, ktorá zobrazuje stĺpcový graf.
- Kliknutím na šípku vedľa položky Možnosti menovky rozbaľte danú časť panela.
- Vyberte hodnotu z buniek. Zobrazí sa dialógové okno s požiadavkou na umiestnenie štítkov. Vyberte mená v K2: K15.
-
Stále na paneli Format Data Label zrušte výber hodnôt Y. Pred vykonaním kroku 16 je dôležité dokončiť krok 15, inak štítky odstránite nechtiac.
Získajte štítky z buniek obsahujúcich mená.
Poznámka
Možnosť získať štítky z buniek bola pridaná do programu Excel 2013. Ak používate program Excel 2010 alebo starší, stiahnite si doplnok XY Chart Labeler od Roba Boveyho. (Google to nájde).
V tomto okamihu skontrolujte, či nemáte nejaké štítky grafov, ktoré sa navzájom zrazia. Ak ich chcete opraviť, postupujte opatrne podľa týchto pokynov.
- Jedným kliknutím kliknite na jeden štítok grafu. Týmto sa vyberú všetky štítky.
- Jedným kliknutím na jeden zo štítkov, ktorý je na vrchu iného štítka, vyberte iba tento štítok.
- Umiestnite kurzor myši na rôzne časti štítka, kým neuvidíte štvorhlavú šípku. Kliknite a presuňte štítok na nové miesto.
-
Keď už máte vybraný iba jeden štítok, môžete ho zvoliť kliknutím na ktorýkoľvek iný štítok. Tento postup opakujte pre všetky ďalšie štítky, ktoré je potrebné presunúť.
Výsledný graf
Pozeraj video
Prepis videa
Naučte sa Excel z Podcastu, epizóda 2217: Umiestnite ľudí na zvonovú krivku.
Ahoj, vitaj späť na netcastu, som Bill Jelen. Dnešná otázka od Jimmyho na mojom seminári v Huntsville v Alabame. Jimmy má údaje, chce tieto údaje zosumarizovať a potom výsledky vyniesť do krivky.
Dobre? Jedno z mojich najobľúbenejších videí na YouTube je toto: číslo 1663, Vytvorte zvonovú krivku v programe Excel. A vzhľadom na strednú hodnotu a štandardnú odchýlku som zistil nízku hodnotu, ktorá je trojnásobkom štandardnej odchýlky menšou ako priemer, a vysokú trojnásobok štandardnej odchýlky viac ako priemernú hodnotu - kde je medzera - séria hodnôt X a na zistenie výšky použite túto funkciu: = NORM.DIST hodnoty X, priemer a štandardná odchýlka, čiarka false (= NORM.DIST (A10, $ B $ 2, $ B $ 3, FALSE)).
A ak sa nad tým zamyslíte, toto video v skutočnosti iba využíva sériu falošných hodnôt X, aby získala pekne vyzerajúcu krivku. Použijeme tu rovnaký koncept, ale namiesto falošných hodnôt X budeme mať ľudí tu dole a výška bude presne tento istý vzorec. Dobre.
Takže teraz chcel Jimmy vytvoriť kontingenčnú tabuľku. Takže vložíme kontingenčnú tabuľku, umiestnime ju sem na tento hárok a klikneme na tlačidlo OK. Ľudia dole na ľavej strane a potom ich priemerné skóre. Dobre, takže to začína Sumom skóre, dvakrát na ne kliknem a zmením to na priemer. Skvelé. Teraz úplne dole nechcem celkový súčet - kliknite pravým tlačidlom myši a Odstrániť celkový súčet - a chceme týchto ľudí usporiadať od najvyšších po najnižšie, čo je v kontingenčnej tabuľke ľahké. Údaje, od A po Z - vynikajúce. Dobre. Teraz urobíme presne to isté, čo sme urobili späť v Podcastu 1663, a to je výpočet priemeru a štandardnej odchýlky. Priemer je teda priemer týchto skóre a potom sa rovná štandardnej odchýlke týchto skóre. Dobre. Teraz, keď to viem, som schopný vytvoriť svoju y-hodnotu.
Dobre, takže pár vecí, ktoré tu urobíme. Najprv nemôžete vytvoriť kontingenčnú tabuľku - bodový graf - z kontingenčnej tabuľky. Takže skopírujem všetky tieto údaje a urobím to s = D2. Všimnite si, že nedávam pozor, aby som na ne ukazoval pomocou myši alebo klávesov so šípkami. A tak tu máme svoje hodnoty. Stanú sa z nich hodnoty X, z hodnoty Y sa stane = NORM.DIST, tu je hodnota x, čiarka, čo znamená, že číslo, stlačím kláves F4 a uzamknem to; pre smerodajnú odchýlku je to toto číslo, znova stlačte F4, aby ste to uzamkli, a kumulatívna NEPRAVDA. (= NORM.DIST (K2, $ H $ 2, $ H $ 3, FALSE)) A dvakrát to skopírujeme. Dobre. A potom nevyberajte štítky,Stačí zvoliť XY a vložíme bodový graf s čiarami - môžete zvoliť ten, ktorý má zakrivené čiary alebo malé rovné čiary. Tu pôjdem s takýmito zakrivenými čiarami. A teraz máme všetkých našich ľudí umiestnených na zvonovej krivke.
Dobre. Teraz, niektoré veci - niektoré veci týkajúce sa formátovania - urobíme tu: Najprv dvakrát kliknite tu dole na mierke a vyzerá to, že náš najnižší počet je pravdepodobne niekde okolo 50 - takže nastavíme min. 50 - a naše najväčšie číslo - naše najväčšie číslo - je 88 - takže nastavím max. 90. Dobre. A teraz musíme tieto body označiť. Ak používate program Excel 2013 alebo novší, je to ľahké; ale ak ste v staršej verzii Excelu, budete sa musieť vrátiť späť a použiť doplnok Rob Bovey's Chart Labeler, aby tieto štítky bodov pochádzali z nejakého miesta, ktoré v grafe nie je. Dobre, tak začneme tu. Budeme pridávať štítky s údajmi a budú pridané čísla a budú vyzerať hrozne. Prídem sem a poviem, že chcem viac možností, možnosti štítkov,a chcem získať hodnotu z buniek - hodnotu z buniek. Dobre? Takže rozsah buniek je priamo tam, kliknite na OK. Pred zrušením začiarknutia hodnoty Y je veľmi dôležité použiť hodnotu z buniek. Začína to vyzerať dobre. Zbavím sa toho. Celý tento kľúč - pretože máte niekoľko ľudí, ktorí sa navzájom prepíšu - je pokúsiť sa vytvoriť čo najväčší graf. Nepotrebujeme smerovať hore. Prečo? Stačí to vymazať. A stále vidím, že Kelly a Lou a Andy a Flo sú takmer na rovnakom mieste; Jared a-- Dobre. Takže teraz to bude frustrujúce - tie, ktoré sa prekrývajú. Ale keď klikneme na štítok, vybrali sme všetky štítky a potom znova klikneme na štítok a vyberieme iba jeden štítok. Dobre? Tak teraz. veľmi opatrne. skúste kliknúť na Andyho a jednoducho potiahnite Andyho hore doľava.Vyzerá to, že Jared a Ike sú spolu, takže teraz, keď som v režime výberu jediného štítka, je to jednoduchšie. A potom Kelly a Lou ich takto pretiahnite. Možno existuje lepšie miesto, ktoré nepreháňa Lou, alebo dokonca, akoby som ho mohol ťahať na obe strany. Dobre, tak čo máme? Začali sme s hromadou údajov, vytvorili sme kontingenčnú tabuľku, zistili sme strednú a štandardnú odchýlku, ktorá nám umožňuje zistiť výšku - pozíciu Y pre každé z týchto skóre a výšku týchto, dúfajme, dostaneme ľudí do peknej zvonovej krivky v tvare paraboly.Dobre, tak čo máme? Začali sme s hromadou údajov, vytvorili sme kontingenčnú tabuľku, zistili sme strednú a štandardnú odchýlku, ktorá nám umožňuje zistiť výšku - pozíciu Y pre každé z týchto skóre a výšku týchto, dúfajme, dostaneme ľudí do peknej zvonovej krivky v tvare paraboly.Dobre, tak čo máme? Začali sme s hromadou údajov, vytvorili sme kontingenčnú tabuľku, zistili sme strednú a štandardnú odchýlku, ktorá nám umožňuje zistiť výšku - pozíciu Y pre každé z týchto skóre a výšku týchto, dúfajme, dostaneme ľudí do peknej zvonovej krivky v tvare paraboly.
Milujem túto otázku od Jimmyho, táto otázka sa nenachádza v tejto knihe, ale bude to v nasledujúcom písaní tejto knihy. Budem musieť pridať toto - je to super požiadavka a super malý trik. Zvukové krivky sú v programe Excel veľmi populárne.
Ale pozrite sa na moju knihu LIVe, 54 najlepších tipov na aplikáciu Excel všetkých čias.
Dobre, zhrnutie z tejto epizódy: Jimmy z Huntsville chce usporiadať ľudí na zvončeku. Takže na zistenie priemerného skóre použijeme kontingenčnú tabuľku, zoraďme kontingenčné tabuľky podľa skóre - usporiadané od najvyššieho po najnižšie - zbavte sa celkového súčtu dole - v podstate to budú hodnoty X - a potom do strany, vypočítajte priemernú a štandardnú odchýlku týchto skóre a pomocou vzorcov skopírujte údaje z kontingenčnej tabuľky do nového rozsahu, pretože nemôžete mať graf XY, ktorý sa pretne s kontingenčnou tabuľkou. Vypočítajte hodnotu y pre každú osobu s hodnotou = NORM.DIST ich hodnoty x, priemeru, štandardnej odchýlky, čiarky FALSE; vytvorte bodový graf XY s hladkými čiarami - ak máte program Excel 2010 alebo starší, budete používať doplnok Label Labeler od Rona Boveyho. Budem si ťa nechať vygoogliť, pretože,v prípade, že Rob zmení svoju URL, nechcem tu nesprávnu URL. V programe Excel 2013 som mal štítky s údajmi, z buniek, zadajte názvy a potom niektoré úpravy - zmeňte mierku v dolnej časti, zmeňte ich dovnútra a Max a potom presuňte štítky, ktoré sa navzájom príliš nastavujú.
Ak si chcete zošit stiahnuť z dnešného videa, použite adresu URL v popise služby YouTube. Chcem poďakovať Jimmymu za túto úžasnú otázku v Huntsville a chcem sa vám poďakovať za návštevu. Uvidíme sa nabudúce na ďalšom netcaste od.
Stiahnite si súbor Excel
Stiahnutie súboru programu Excel: place-people-on-bell-curve.xlsx
Ďakujeme Jimmymu v Huntsville za dnešnú otázku!
Excel myslel dňa
Požiadal som svojich priateľov programu Excel Master o radu o programe Excel. Dnešná myšlienka na zamyslenie:
„Ak ste v minulom mesiaci vložili program Excel do režimu manuálneho prepočtu, je čas na otočenie výkonu (manuálny režim už nikdy nebudete potrebovať)“ “
Rob kólia
