Jimmy v Huntsville chce vykreslit křivku ukazující průměrné skóre několika lidí. Když mi Jimmy položil otázku během semináře Power Excel, vzpomněl jsem si na jedno z mých populárnějších videí na YouTube.
V Podcastu 1665 - Vytvoření zvonové křivky v aplikaci Excel vysvětluji, že pro vytvoření zvonové křivky je třeba vypočítat střední a standardní odchylku. Poté vygeneruji 30 bodů podél osy x, které pokrývají hypotetickou populaci lidí. V tomto videu jsem vygeneroval rozsah od -3 standardní odchylky do + 3 standardní odchylky kolem průměru.
Například pokud máte průměr 50 a směrodatnou odchylku 10, vytvořil bych osu x, která běžela od 70 do 130. Výška každého bodu se vypočítá pomocí =NORM.DIST(x,mean,standard deviation,False).
Na obrázku výše jsou čísla v A10: A40 v podstatě „falešné datové body“. Generuji 31 čísel, abych vytvořil pěknou hladkou křivku. Pokud bych použil pouze 7 datových bodů, křivka by vypadala takto:
U Jimmyho datového souboru jsou skutečné průměrné skóre jeho zaměstnanců v podstatě body podél osy x. Aby se vešly na křivku zvonu, musíte zjistit výšku nebo hodnotu Y pro každého zaměstnance.
Následuj tyto kroky:
-
Seřaďte data tak, aby se skóre zobrazovala od nejnižší k nejvyšší.
Seřadit data - Vypočítejte průměr pomocí funkce PRŮMĚR.
- Vypočítejte směrodatnou odchylku pomocí funkce STDEV.
-
Vypočítejte hodnotu Y napravo od skóre pomocí
=NORM.DIST(L2,$H$2,$H$3,FALSE). Hodnota Y vygeneruje výšku bodu každé osoby podél křivky zvonu. Funkce NORM.DIST se postará o vykreslování lidí v blízkosti střední hodnoty na vyšším místě než lidí v horní nebo dolní části.
Vygenerujte řadu hodnot Y. - Vyberte svá data v L1: M15
-
V aplikaci Excel se nedávno začala objevovat zvláštní chyba, abyste si zajistili úspěch a na kartě Vložit vyberte Všechny grafy.
Spouštěč dialogů vás přenese na všechny typy grafů V dialogovém okně Vložit graf klikněte na kartu Všechny grafy. Klikněte vlevo na XY (Scatter). Vyberte druhou ikonu v horní části. Vyberte náhled vpravo.
Čtyři kliknutí k výběru grafu Vaše počáteční křivka bude vypadat takto:
Zvonová křivka
Chcete-li vyčistit křivku zvonu, postupujte takto:
- Klikněte na název a stiskněte klávesu Delete.
- Poklepejte na libovolné číslo podél osy Y ve spodní části grafu. Zobrazí se panel Formátovat osu.
-
Zadejte nové hodnoty pro Minimum a Maximum. Rozsah zde by měl být dostatečně široký, aby se zobrazil všem v grafu. Použil jsem 50 až 90.
Změňte minimum a maximum - Zvětšete graf přetažením za okraj grafu.
- Klikněte na ikonu + napravo od grafu a vyberte Datové štítky. Nebojte se, že štítky ještě nedávají smysl.
- Poklepáním na jeden štítek otevřete panel Formátovat štítky.
- V horní části panelu jsou čtyři ikony. Vyberte ikonu, která zobrazuje sloupcový graf.
- Kliknutím na šipku vedle možnosti Možnosti štítku rozbalíte tuto část panelu.
- Vyberte hodnotu z buněk. Zobrazí se dialogové okno s dotazem na umístění štítků. Vyberte jména v K2: K15.
-
Na panelu Formát datového štítku zrušte výběr hodnot Y. Je důležité dokončit krok 15 před provedením kroku 16, jinak štítky nechtěně odstraníte.
Získejte štítky z buněk obsahujících názvy.
Poznámka
Možnost získat štítky z buněk byla přidána v aplikaci Excel 2013. Pokud používáte Excel 2010 nebo starší, stáhněte si doplněk XY Chart Labeler od Roba Boveyho. (Google to najde).
V tomto okamžiku zkontrolujte, zda nemáte nějaké štítky grafů, které se navzájem shazují. Chcete-li je opravit, pečlivě postupujte podle těchto kroků.
- Jedním kliknutím na jeden štítek grafu. Tím se vyberou všechny štítky.
- Jedním kliknutím na jeden ze štítků nad jiným štítkem vyberte pouze tento štítek.
- Umístěte ukazatel myši na různé části štítku, dokud neuvidíte čtyřhlavou šipku. Klikněte a přetáhněte štítek na nové místo.
-
Jakmile vyberete pouze jeden štítek, můžete jej kliknutím vybrat kliknutím na kterýkoli jiný štítek. Opakujte pro všechny další štítky, které je třeba přesunout.
Konečný graf
Sledovat video
Přepis videa
Naučte se Excel z Podcastu, epizoda 2217: Umístěte lidi na zvonovou křivku.
Hej, vítej zpět na netcastu, jsem Bill Jelen. Dnešní otázka od Jimmyho na mém semináři v Huntsville v Alabamě. Jimmy má data, chce tato data shrnout a výsledky pak vykreslit na křivku zvonu.
V pořádku? Jedním z mých nejoblíbenějších videí na YouTube je toto: číslo 1663, Vytvořte křivku zvonu v aplikaci Excel. A vzhledem k střední hodnotě a směrodatné odchylce jsem zjistil nízkou hodnotu, která je 3násobkem standardní odchylky menší než průměr, a vysokou - 3násobek standardní odchylky více než průměr - kde je mezera - a řada X hodnot zde a pro zjištění výšky použijte tuto funkci: = NORM.DIST hodnoty X, průměr a směrodatná odchylka, čárka false (= NORM.DIST (A10, $ B $ 2, $ B $ 3, FALSE)).
A pokud o tom přemýšlíte, toto video opravdu používá pouze řadu falešných hodnot X, aby získalo pěkně vypadající křivku. Použijeme zde stejný koncept, ale místo falešných hodnot X budeme mít lidi dole a potom bude výška přesně stejný vzorec. V pořádku.
Takže teď Jimmy chtěl vytvořit kontingenční tabulku. Takže vložíme kontingenční tabulku, umístíme ji sem na tento list a klikneme na OK. Lidé na levé straně a poté jejich průměrné skóre. Dobře, takže to začíná Sum of Score, tam na to dvakrát kliknu a změním to na průměr. Skvělý. Nyní úplně dole nechci celkový součet - klikněte pravým tlačítkem a Odebrat celkový součet - a chceme tyto lidi uspořádat od nejvyšší k nejnižší, což je v kontingenční tabulce snadné. Data, od A do Z - vynikající. V pořádku. Nyní uděláme přesně to samé, co jsme udělali zpět v Podcastu 1663, a to je výpočet střední hodnoty a standardní odchylky. Průměr je tedy průměrem těchto skóre a pak se rovná standardní odchylce těchto skóre. V pořádku. Teď, když to vím, jsem schopen vytvořit svou hodnotu y.
Dobře, takže pár věcí, které tu uděláme. Nejprve nemůžete vytvořit kontingenční tabulku - bodový graf - z kontingenční tabulky. Takže zkopíruji všechna tato data a udělám to s = D2. Všimněte si, že dávám pozor, abych na ně neukazoval myší ani klávesami se šipkami. A tak zde máme své hodnoty. Ty se stanou hodnotami X, hodnota Y se stane = NORM.DIST, tady je hodnota x, čárka, pro průměr, to číslo, stisknu F4, abych to uzamkl; pro směrodatnou odchylku je to toto číslo, znovu stiskněte F4, abyste to uzamkli, a kumulativní FALSE. (= NORM.DIST (K2, $ H $ 2, $ H $ 3, FALSE)) A dvakrát klikneme a zkopírujeme to dolů. V pořádku. A pak si nevybírejte štítky,stačí vybrat XY a vložíme bodový graf s čarami - můžete si vybrat ten se zakřivenými čarami nebo s malými přímkami. Tady půjdu s takovými zakřivenými čarami. A teď máme všechny naše lidi umístěné na zvonové křivce.
V pořádku. Nyní, některé věci - některé věci typu formátování - uděláme zde: Nejprve poklepejte sem dolů po stupnici a vypadá to, že náš nejnižší počet je pravděpodobně někde kolem 50 - takže budu nastavit min. 50 - a naše největší číslo - naše největší číslo - je 88 - takže nastavím maximálně 90. Dobře. A teď musíme tyto body označit. Pokud používáte Excel 2013 nebo novější, je to snadné; ale pokud používáte starší verzi aplikace Excel, budete se muset vrátit zpět a použít doplněk Graf Labeler od Rob Bovey, aby tyto popisky bodů pocházely z místa, které v grafu není. Dobře, tak začneme tady. Přidáme datové štítky a přidá čísla a vypadají hrozně. Pojedu sem a řeknu, že chci Další možnosti, Možnosti štítku,a chci získat hodnotu z buněk - hodnotu z buněk. V pořádku? Takže rozsah buněk je právě tam, klikněte na OK. Před zrušením zaškrtnutí hodnoty Y je velmi důležité použít hodnotu z buněk. Začíná to vypadat dobře. Zbavím se toho. Celý klíč zde - protože máte některé lidi, kteří se navzájem přepisují - je pokusit se vytvořit graf co největší. Nepotřebujeme tam nahoře. Proč? Stačí to smazat. A stále vidím, že Kelly a Lou a Andy a Flo jsou téměř na stejném místě; Jared a-- Dobře. Takže teď to bude frustrující - ty, které se překrývají. Když však klikneme na štítek, vybrali jsme všechny štítky a poté znovu klikli na štítek a vybrali jsme jen jeden štítek. V pořádku? Tak teď. velmi opatrně. zkuste kliknout na Andyho a přetáhněte Andyho nahoru doleva.Vypadá to, že Jared a Ike jsou spolu, takže teď, když jsem v režimu výběru jednoho štítku, je to jednodušší. A pak Kelly a Lou je přetáhněte takhle nahoru. Možná existuje lepší místo, které nepřetěžuje Lou, nebo dokonce, jako bych ho mohl přetáhnout na obě strany. Dobře, tak co máme? Začali jsme s hromadou dat, vytvořili jsme kontingenční tabulku, zjistili jsme střední a standardní odchylku, což nám umožňuje zjistit výšku - pozici Y pro každé z těchto skóre a jejich výšku, doufejme, přivedeme lidi do pěkné zvonové křivky ve tvaru paraboly.Dobře, tak co máme? Začali jsme s hromadou dat, vytvořili jsme kontingenční tabulku, zjistili jsme střední a standardní odchylku, což nám umožňuje zjistit výšku - pozici Y pro každé z těchto skóre a jejich výšku, doufejme, přivedeme lidi do pěkné zvonové křivky ve tvaru paraboly.Dobře, tak co máme? Začali jsme s hromadou dat, vytvořili jsme kontingenční tabulku, zjistili jsme střední a standardní odchylku, což nám umožňuje zjistit výšku - pozici Y pro každé z těchto skóre a jejich výšku, doufejme, přivedeme lidi do pěkné zvonové křivky ve tvaru paraboly.
Miluji tuto otázku od Jimmyho, tato otázka není v této knize, ale bude v příštím psaní této knihy. Budu muset přidat toto - je to skvělý požadavek a skvělý malý trik. Zvukové křivky jsou v aplikaci Excel velmi oblíbené.
Ale podívejte se na moji knihu, LIVe, 54 největších tipů na Excel všech dob.
Dobře, shrnutí z této epizody: Jimmy z Huntsville chce lidi uspořádat na zvonu. Takže k určení průměrného skóre použijeme kontingenční tabulku, roztřídíme kontingenční tabulky podle skóre - uspořádané od nejvyšší k nejnižší - zbavte se celkového součtu dole - to budou v zásadě hodnoty X - a potom do strany, vypočítejte průměrnou a směrodatnou odchylku těchto skóre a pomocí vzorců zkopírujte data z kontingenční tabulky do nového rozsahu, protože nemůžete mít graf XY, který se protíná s kontingenční tabulkou. Vypočítejte hodnotu y pro každou osobu s = NORM.DIST jejich hodnoty x, průměr, standardní odchylka, čárka FALSE; vytvořte bodový graf XY s hladkými čarami - pokud používáte Excel 2010 nebo starší, budete používat doplněk Label Label od Rona Boveyho. Budu vás chtít vygooglit, protože,v případě, že Rob změní svou URL, nechci zde špatnou URL. V aplikaci Excel 2013 jsem měl datové štítky, z buněk, zadejte názvy a pak některé úpravy - změňte měřítko podél spodní části, změním je a Max a poté přesunu štítky, které se navzájem nadměrně nastavují.
Chcete-li sešit stáhnout z dnešního videa, použijte adresu URL v popisu YouTube. Chci poděkovat Jimmymu za tuto úžasnou otázku v Huntsville a chci vám poděkovat za to, že jste se zastavili. Uvidíme se příště na dalším netcastu z.
Stáhněte si soubor Excel
Chcete-li stáhnout soubor aplikace Excel: place-people-on-bell-curve.xlsx
Díky Jimmymu v Huntsville za dnešní otázku!
Excel myšlenka dne
Požádal jsem své přátele Excel Master o radu ohledně Excelu. Dnešní myšlenka k zamyšlení:
„Pokud jste v minulém měsíci nastavili aplikaci Excel do režimu manuálního přepočítávání, je čas na pivot (již nikdy nebudete potřebovat manuální režim)
Rob kolie
