A Huntsville-i Jimmy egy haranggörbét akar megrajzolni, amely több ember átlagos pontszámát mutatja. Amikor Jimmy feltette a kérdést a Power Excel szemináriumom során, visszagondoltam az egyik legnépszerűbb videómra a YouTube-on.
A Podcast 1665 - Haranggörbe létrehozása az Excel programban elmagyarázom, hogy haranggörbe létrehozásához ki kell számolni az átlagot és a szórást. Ezután az x tengely mentén 30 pontot generálok, amelyek az emberek hipotetikus populációjára terjednek ki. Ebben a videóban azt generáltam, amely -3 szórástól + 3 standard eltérésig terjedt az átlag körül.
Például, ha az átlaga 50 és a szórása 10, akkor létrehoznék egy x tengelyt, amely 70 és 130 között futott. Az egyes pontok magasságát a segítségével számoljuk =NORM.DIST(x,mean,standard deviation,False).
A fenti képen az A10: A40 számok lényegében "hamis adatpontok". 31 számot generálok, hogy szép sima görbét hozzak létre. Ha csak 7 adatpontot használtam volna, a görbe a következőképpen nézne ki:
Jimmy adatsora szempontjából alkalmazottai tényleges átlagos pontszámai lényegében egy x tengely mentén lévő pontok. A haranggörbén való elhelyezéshez ki kell derítenie az egyes munkavállalók magasságát vagy Y-értékét.
Kovesd ezeket a lepeseket:
-
Rendezze az adatokat úgy, hogy a pontszámok a legalacsonyabbtól a legmagasabbig jelennek meg.
Rendezze az adatokat - Számítson ki egy átlagot az ÁTLAG funkció segítségével.
- Számítson ki egy szórást az STDEV függvény segítségével.
-
Számítsa ki a pontszámoktól jobbra lévő Y értéket a segítségével
=NORM.DIST(L2,$H$2,$H$3,FALSE). Az Y-érték minden ember pontjának magasságát generálja a haranggörbe mentén. A NORM.DIST funkció gondoskodik arról, hogy az átlag közelében lévő embereket magasabb helyen ábrázolja, mint a teteje vagy alja közelében.
Generáljon Y értékek sorozatát. - Válassza ki adatait az L1: M15 mezőben
-
Az Excelben nemrégiben egy furcsa hiba kezdett megjelenni, így a siker érdekében a Beszúrás fülön válassza az Összes diagram lehetőséget.
A párbeszédpanel elindítja az összes diagramtípust A Diagram beszúrása párbeszédpanelen kattintson az Összes diagram fülre. Kattintson a bal oldalon az XY (Szórás) elemre. Válassza a tetején a második ikont. Válassza ki a jobb oldali előnézetet.
Négy kattintás a diagram kiválasztásához A belső csengőgörbéje így néz ki:
A harang görbe
A haranggörbe tisztításához kövesse az alábbi lépéseket:
- Kattintson a címre, és nyomja meg a Törlés gombot.
- Kattintson duplán bármelyik számra a diagram alján az Y tengely mentén. Megjelenik a Tengely formázása panel.
-
Írjon be új értékeket a Minimum és Maximum értékekre. A tartománynak itt elég szélesnek kell lennie ahhoz, hogy mindenkit megjelenítsen a diagramon. 50-90-et használtam.
Módosítsa a minimumot és a maximumot - A diagram szélét húzva tegye szélesebbé a diagramot.
- Kattintson a diagram jobb oldalán található + ikonra, és válassza az Adatcímkék lehetőséget. Ne aggódjon, hogy a címkéknek még nincs értelme.
- Kattintson duplán egy címkére a Címkék formázása panel megnyitásához.
- A panel tetején négy ikon található. Válassza ki az oszlopdiagramot megjelenítő ikont.
- Kattintson a Címkebeállítások melletti nyílra a panel ezen részének kibontásához.
- Válassza az Érték elemet a cellák közül. Megjelenik egy párbeszédpanel, amely megkérdezi a címkék helyét. Válassza ki a neveket a K2-ben: K15.
-
Az Adatcímke formázása panelen továbbra is törölje az Y értékek kijelölését. Fontos, hogy a 16. lépést megelőzően befejezze a 15. lépést, különben véletlenül eltávolítja a címkéket.
Szerezze meg a címkéket a neveket tartalmazó cellákból.
jegyzet
A cellákból címkék megszerzésének lehetősége az Excel 2013-ban került hozzáadásra. Ha az Excel 2010 vagy korábbi verziót használja, töltse le az XY Chart Labeler bővítményt Rob Bovey-tól. (A Google megtalálja).
Ezen a ponton ellenőrizze, vannak-e diagramcímkéi, amelyek egymásba ütköznek. Javításukhoz gondosan kövesse ezeket a lépéseket.
- Egyetlen kattintás egy diagram címkéjére. Ez kijelöli az összes címkét.
- Kattintson egyetlen kattintással az egyik címkére, amely egy másik címke tetején található, hogy csak azt a címkét válassza ki.
- Vigye az egérmutatót a címke különböző részeire, amíg meg nem jelenik egy négyfejű nyíl. Kattintson és húzza a címkét egy új pozícióba.
-
Ha csak egyetlen címke van kiválasztva, akkor egyetlen kattintással bármely más címkére kattintva kiválaszthatja azt. Ismételje meg az esetleges más áthelyezni kívánt címkéket.
A végső diagram
Videót nézni
Videó átirat
Ismerje meg az Excel programot a Podcast 2217-es epizódjából: Helyezze az embereket egy haranggörbére.
Hé, üdvözlöm újra a neten, Bill Jelen vagyok. A mai kérdés Jimmytől az alabamai Huntsville-i szemináriumon. Jimmynek vannak adatai, ezeket az adatokat szeretné összefoglalni, majd az eredményeket egy haranggörbén ábrázolni.
Rendben? Az egyik legnépszerűbb videó a YouTube-on ez: 1663-as szám, Hozzon létre egy haranggörbét az Excel-ben. Átlagot és szórást figyelembe véve rájöttem az alacsonyra, amely a szórás háromszorosa kisebb, mint az átlag, és a magas - háromszorosa a szórásnak az átlagnál nagyobb - ahol a rés van - és X értékek sorozata itt, és a magasság kiszámításához használja ezt a függvényt: = NORM. Az X érték, az átlag és a szórás távolsága, vessző hamis (= NORM.DIST (A10, $ B $ 2, $) B $ 3, HAMIS)).
És ha belegondolunk, ez a videó valóban csak hamis X értékek sorozatát használja itt annak érdekében, hogy szép megjelenésű görbét kapjon. És ugyanazt a fogalmat fogjuk itt használni, de a hamis X értékek helyett valójában itt lennünk az emberekkel, és akkor a magasság pontosan ugyanaz a képlet lesz. Rendben.
Tehát most Jimmy egy forgóasztalt akart létrehozni. Tehát beillesztjük a PivotTable-t, ide tesszük ezt a lapot, és kattintsunk az OK gombra Az emberek a bal oldalon, majd az átlagos pontszámuk. Rendben, tehát a Pontszám összegével kezdődik, duplán kattintok oda, és ezt átlagra változtatom. Nagy. Most, a legvégén, nem akarok végösszeget - kattintson a jobb gombbal és távolítsa el a teljes összeget -, és ezeket az embereket magasról alacsonyra akarjuk rendezni, és ezt könnyű megtenni egy forgatótáblában. Adatok, A-tól Z-ig kiváló. Rendben. Most pontosan ugyanazt fogjuk csinálni, mint amit még a Podcast 1663-ban tettünk, és ez kiszámítja az átlagot és a szórást. Tehát az átlag ezen értékek átlaga, majd megegyezik ezeknek a pontszámoknak a szórásával. Rendben. Most, hogy ezt tudom, képes vagyok létrehozni az y-értékemet.
Rendben, szóval pár dolgot itt fogunk csinálni. Először is, nem hozhat létre pivot táblázatot - szóródiagramot - egy pivot táblából. Tehát átmásolom ezeket az adatokat, és csak a = D2-vel fogom megtenni. Figyelem, vigyázok, hogy ne az egérrel vagy a nyílbillentyűkkel mutassak ezekre. Tehát itt megvannak az értékeink. Ezek X értékekké válnak, az Y érték = NORM.DIST lesz, itt van az x érték, vessző, az átlag, ez a szám, az F4 billentyűt lenyomva zárom; a szóráshoz ez a szám, nyomja meg ismét az F4 billentyűt a lezáráshoz, és a kumulatív FALSE értéket. (= NORM.DIST (K2, $ H $ 2, $ H $ 3, FALSE)) És duplán kattintunk a lemásoláshoz. Rendben. És akkor ne válassza ki a címkéket,csak válassza az XY-t, és beszúrunk egy szórásdiagramot vonalakkal - választhatja a görbült vonalakat vagy egyenes vonalakat. Itt ilyen görbe vonalakkal megyek. És most minden emberünket haranggörbére helyezzük.
Rendben. Néhány dolgot - néhány formázási típusú dolgot - itt fogunk megtenni: Először kattintson duplán ide a skála mentén, és úgy tűnik, hogy a legalacsonyabb számunk valószínűleg valahol 50 körül van - ezért állítson be min 50-et, és a legnagyobb számunk - a legnagyobb számunk - 88-, ezért max. 90-et állítok. Rendben. És most meg kell jelölnünk ezeket a pontokat. Ha az Excel 2013-as vagy újabb verzióját használja, ezt könnyen megteheti; de ha az Excel régebbi verzióját használja, akkor vissza kell térnie, és Rob Bovey Chart Labeler bővítményét kell használnia annak érdekében, hogy ezek a pontcímkék olyan helyről származzanak, amely nem szerepel a diagramban. Rendben, tehát itt indulunk. Adatcímkéket fogunk hozzáadni, és számokat ad hozzá, és szörnyen néznek ki. Ide jövök, és azt mondom, hogy több opciót, címke opciót,és szeretném megszerezni az Értéket a cellákból - Az értéket a cellákból. Rendben? Tehát a cellák tartománya itt van, kattintson az OK gombra. Nagyon fontos, hogy a cellákból származó értéket használjuk, mielőtt törlöm az Y érték jelölését. Kezd jól nézni. Megszabadulok ettől. Most itt az egész kulcs - mert van néhány ember, aki valahogy felülírja egymást - az az, hogy megpróbálja minél nagyobbá tenni a diagramot. Nincs szükségünk odafelé tartó irányra. Miért? Csak törölje. És még mindig látom, hogy Kelly és Lou, Andy és Flo majdnem ugyanazon a helyen vannak; Jared és … Rendben. Tehát ez most frusztráló lesz - ezek átfedik egymást. De amikor rákattintunk egy címkére, kijelöltük az összes címkét, majd ismét egy címkére kattintva csak egyetlen címkét választunk. Rendben? Tehát most. nagyon óvatosan. kattintson az Andy-ra, és húzza fel balra Andyt.Úgy tűnik, hogy Jared és Ike együtt vannak, így most, hogy egyetlen kiadó kiválasztási módban vagyok, könnyebb. És akkor Kelly és Lou húzza őket így fel. Talán van egy jobb hely, amely nem futja túl Lou-t, vagy akár itt is, mindkét oldalra húzhatom. Rendben, akkor mi van? Elkezdtünk egy csomó adattal, létrehoztunk egy forgatótáblát, kitaláltuk az átlagot és a szórást, ami csak lehetővé teszi számunkra, hogy kitaláljuk a magasságot - az egyes Y pontszámot és remélhetőleg ilyen szép parabola alakú haranggörbére juttatjuk az embereket.Rendben, akkor mi van? Elkezdtünk egy csomó adattal, létrehoztunk egy forgatótáblát, kitaláltuk az átlagot és a szórást, ami csak lehetővé teszi számunkra, hogy kitaláljuk a magasságot - az egyes Y pontszámot és remélhetőleg ilyen szép parabola alakú haranggörbére juttatjuk az embereket.Rendben, akkor mi van? Elkezdtünk egy csomó adattal, létrehoztunk egy forgatótáblát, kitaláltuk az átlagot és a szórást, ami csak lehetővé teszi számunkra, hogy kitaláljuk a magasságot - az egyes Y pontszámot és remélhetőleg ilyen szép parabola alakú haranggörbére juttatjuk az embereket.
Szeretem ezt a kérdést Jimmytől, ez a kérdés nem szerepel ebben a könyvben, de a következő alkalommal megírom. Hozzá kell tennem ezt - ez nagyon jó kérés és jó kis trükk. A haranggörbék nagyon népszerűek az Excelben.
De nézze meg a LIVe című könyvemet, minden idők 54 legnagyobb Excel-tippjét.
Rendben, összefoglalva ebből az epizódból: Jimmy Huntsville-ből csengőgörbén akarja elrendezni az embereket. Tehát egy pivot táblázattal kiszámoljuk az átlagos pontszámot, a pivot táblázatokat a pontszámok szerint rendezzük - magasról alacsonyra elrendezve - megszabadulunk az alsótól - ezek lényegében az X értékek lesznek - majd oldalra számolva számolja ki ezeknek a pontszámoknak az átlagát és szórását, és képletek segítségével másolja át az adatokat a pivot táblából egy új tartományba, mert nem lehet XY diagram, amely metszi a pivot táblázatot. Számítson ki egy személy y-értékét = = NORM.DIST értékével, az átlagával, a szórással, a HATÓ vesszővel hozzon létre egy XY szórásdiagramot sima vonalakkal - ha Excel 2010 vagy korábbi, akkor Ron Bovey Chart Labeler bővítményét fogja használni. Meg fogom kérni, hogy ezt guglizza, mertha Rob megváltoztatja az URL-jét, akkor nem akarok rossz URL-t itt. Az Excel 2013 programban az Adatcímkék, a celláktól megadta a neveket, majd néhány kiigazítást - megváltoztatta a skálát az alján, megváltoztatom őket és Max, majd áthelyezem azokat a címkéket, amelyek túlságosan beállítják egymást.
A munkafüzet letöltéséhez a mai videóból használja a YouTube leírásában található URL-t. Szeretném megköszönni Jimmynek ezt a fantasztikus kérdést Huntsville-ben, és szeretném megköszönni, hogy betért. Találkozunk legközelebb egy másik netcast-re.
Töltse le az Excel fájlt
Az excel fájl letöltése: place-people-on-bell-curve.xlsx
Köszönet Jimmynek Huntsville-ben a mai kérdésért!
Excel gondolata a napnak
Az Excel Master barátaimtól kértem tanácsokat az Excel-mel kapcsolatban. Mai gondolat, hogy elgondolkodjak:
"Ha az elmúlt hónapban az excelt manuális újraszámítási módba helyezte, akkor itt az ideje a tápellátásnak (soha többé nem lesz szüksége kézi módra)"
Rob Collie
