Normál elosztási haranggörbe létrehozása az Excelben

Ez az oktatóanyag bemutatja, hogyan lehet normál elosztó haranggörbét létrehozni az Excel összes verziójában: 2007, 2010, 2013 , 2016 és 2019.

A statisztikákban a haranggörbe (más néven normál eloszlás vagy Gauss-görbe) egy szimmetrikus grafikon, amely szemlélteti az adatok klaszterre való hajlamát egy adott adatkészlet középértéke vagy átlaga körül.

Az y tengely az adott érték relatív valószínűségét jelzi az adatkészletben, míg az x tengely magukat az értékeket ábrázolja a diagramon, hogy harang alakú görbe, innen a név.

A grafikon segít elemezni, hogy egy adott érték része-e a várt variációnak, vagy statisztikailag szignifikáns-e, ezért szorosabban kell megvizsgálni.

Mivel az Excel nem kínál beépített megoldásokat, magának kell megrajzolnia. Ezért fejlesztettük ki a Chart Creator bővítményt, egy eszközt, amely lehetővé teszi fejlett Excel diagramok elkészítését néhány kattintással.

Ebben a lépésben- lépésről lépésre megtanulhatja, hogyan lehet az alapértelmezetten létrehozni egy normál elosztó haranggörbét az Excelben:

ábrázol egy Gauss-görbét, két dolgot kell tudnia:

• Az átlag (más néven standard mérés). Ez határozza meg a görbe középpontját – ami viszont jellemzi a görbe helyzetét.
• A mérések szórása (SD). Ez meghatározza az adatok terjedését a normál eloszlásban – vagy egyszerű angol nyelven, hogy milyen széles legyen a görbe. Például a fent bemutatott haranggörbén az átlag egy szórása az 53 és 85 vizsgaértékek közötti tartományt képviseli.

Minél alacsonyabb az SD, annál magasabb a görbe és annál kevesebb az adatok szét fognak oszlani, és fordítva.

Érdemes megemlíteni a 68-95-99.7 szabályt, amely bármely normál eloszlási görbére alkalmazható, vagyis az adatok nagyjából 68% -át fogják elhelyezni egy SD-n belül az átlagtól, 95% két SD-n belül és 99,7% három SD-n belül.

Most, hogy ismerje a lényegeket, térjünk át az elméletről a gyakorlatra.

Kezdő lépések

Szemléltetés céljából tegyük fel, hogy 200 hallgató teszt pontszáma van, és szeretné “görbén” osztályozni őket. “, Vagyis a diákok osztályzata az osztály többi tagjához viszonyított relatív teljesítményükön fog alapulni:

1. lépés: Keresse meg az átlagot.

Általában az átlagot kapja és SD értékeket a kezdetektől fogva, de ha nem ez a helyzet, akkor ezeket az értékeket egyszerűen kiszámíthatja néhány egyszerű lépésben. Először kezeljük az átlagot.

Mivel az átlag egy minta vagy adatcsoport átlagos értékét jelzi, a standard mérést az ÁTLAG függvény segítségével találhatja meg.

Írja be a következő képletet bármely üres cellába (ebben a példában az F1) a tényleges adatok mellett (A és B oszlop) az adatkészlet vizsgaeredményeinek átlagának kiszámításához:

Gyors megjegyzés : gyakran előfordulhat, hogy fel kell kerekítenie a képlet kimenetét. Ehhez egyszerűen csomagolja be a ROUND függvénybe az alábbiak szerint:

2. lépés: Keresse meg a szórást.

Egy le, egy menni. Szerencsére az Excel speciális funkcióval rendelkezik, hogy elvégezze az összes piszkos munkát, hogy megtalálja az Ön számára a szórást:

A képlet megint felveszi az összes értéket a megadott cellatartományból (B2: B201), és kiszámítja annak szórását – csak ne felejtsen el kerekíteni a kimenetet is.

3. lépés: Állítsa be a görbe x tengely értékeit.

Alapvetően a diagram hatalmas számú intervallumot jelent ( lépésekként gondolj rájuk), amelyeket egy vonallal összekötve sima görbét kapunk.

Esetünkben az x tengely értékei egy adott vizsga pontszámának illusztrálására szolgálnak, míg az y tengely értékei megmondják annak valószínűsége, hogy egy hallgató megszerzi ezt a pontszámot a vizsgán.

Technikailag annyi intervallumot vehet fel, amennyit csak akar – később könnyedén törölheti a felesleges adatokat a vízszintes tengely skálájának módosításával. Csak győződjön meg arról, hogy választott egy tartományt, amely magában foglalja a három szórást.

Kezdjük a számlálást egynél (mivel a hallgató semmilyen módon nem kaphat negatív vizsgaértéket), és haladjunk egészen 150 – teljesen mindegy, hogy 150 vagy 1500 – egy másik segítő táblázat beállítása.

1. Válasszon ki egy üres cellát a diagram adatai (például E4) alá, és írja be az “1” szót. az első intervallumot meghatározó érték.
2. Navigáljon a Főoldal fülre.
3. A Szerkesztés csoportban válassza a “Kitöltés” lehetőséget.
4. A “Sorozat itt: ”Válassza az„ Oszlop ”lehetőséget.
5. A„ Lépésérték ”mezőbe írja be az„ 1. ”lehetőséget. Ez az érték határozza meg azokat a növekményeket, amelyek automatikusan összeadódnak, amíg az Excel el nem éri az utolsó intervallumot.
6. A “Stop value” mezőbe írja be a “150” értéket, amely az utolsó intervallumot jelenti, majd kattintson az “OK” gombra. ”

Csodálatos módon 149 cella az E oszlopban (E5: E153) kitöltötték a 2-től 150-ig terjedő értékeket.

MEGJEGYZÉS: Ne rejtse el az eredeti adatcellákat a képernyőképeken látható módon. Ellenkező esetben a technika nem fog működni.

4. lépés: Számítsa ki az összes x tengely értékének normál eloszlási értékeit.

Most keresse meg a normális eloszlás értékeit – annak valószínűségét, hogy a hallgató egy bizonyos vizsga pontszámot kap, amelyet egy adott x tengely érték képvisel – az egyes intervallumokhoz. Szerencsére az Excelnek van egy munkalova, hogy elvégezze ezeket a számításokat az Ön számára: a NORM.DIST függvény.

Írja be a következő képletet a jobb oldali cellába (F4): az első intervallum (E4):

A dekódolt verzió segít a megfelelő beállításban:

Zárolja az átlag és az SD értékeket, hogy könnyedén végrehajthassa a képletet a fennmaradó időközönként (E5: E153).

Most kattintson duplán a kitöltő fogantyúra, hogy a képletet a többi cellába másolja (F5: F153).

5. lépés: Hozzon létre egy sima görbét sima vonalakkal.

Végül elérkezett a haranggörbe felépítésének ideje:

1. Válasszon ki bármelyik értéket a segítő táblából, amely tartalmazza az x- és y-tengely értékeit (E4: F153).
2. Lépjen a Beszúrás fülre.
3. Kattintson a “Szórás (X, Y) vagy Buborékdiagram beszúrása” gombra.
4. Válassza a “Szórás sima vonalakkal” lehetőséget.

6. lépés: Állítsa be a címketáblát.

Technikailag Ön megvan a haranggörbéje. De nehéz lenne elolvasni, mivel hiányoznak az ezt leíró adatok.

Tegyük informatívabbá a normál eloszlást azáltal, hogy hozzáadjuk az átlag alatti és feletti összes szórásértéket szemléltető címkéket ( használhatja őket a z-pontszámok megjelenítésére is).

Ehhez állítson be még egy segítőtáblát az alábbiak szerint:

Először másolja az átlagértéket (F1) az X-Value (I5) oszlop megfelelő cellája mellé.

Ezután számítsa ki az átlag alatti szórásértékeket úgy, hogy ezt az egyszerű képletet beírja az I4 cellába:

Egyszerűen fogalmazva: a képlet kivonja az előző szórásértékek összegét az átlagból. Most húzza felfelé a kitöltő fogantyút, hogy a képletet a maradék két cellába másolja (I2: I3).

Ismételje meg ugyanazt a folyamatot az átlag feletti szórásoknál a tükör képlet használatával:

Ugyanígy hajtsa végre a képletet a másik két cellához is (I7 : I8).

Végül töltse be az y tengely címkeértékeit (J2: J8) nullákkal, ahogy szeretné adatjelölők a vízszintes tengelyen.

7. lépés: Helyezze be a címke adatait a diagramba.

Most adja hozzá az összes előkészített adatot. Kattintson a jobb gombbal a diagramdiagramon, és válassza az “Adatok kiválasztása” lehetőséget.

A felbukkanó párbeszédpanelen válassza a ” Hozzáadás. ”

Jelölje ki a megfelelő cellatartományokat a segédtáblázatból – I2: I8 a” X sorozatú értékek “és a J2 : J8 az “Y sorozat értékeihez” – és kattintson az “OK” gombra.

8. lépés: A diagram típusának módosítása a címkesorozat.

A következő lépés az újonnan hozzáadott sorozat diagramtípusának megváltoztatása, hogy az adatjelzők pontokként jelenjenek meg. Ehhez kattintson a jobb gombbal a diagram diagramra, és válassza a “Módosítás Diagram típusa. ”

Ezután tervezzen egy kombinált diagramot:

1. navigáljon a Combo fül.
2. A “Series2” sorozatnál változtassa meg a “Chart Type” értéket “Scatter” -re.
   • Megjegyzés: Győződjön meg arról, hogy a “Series1” továbbra is “Scatter with Smooth Lines” marad. Néha az Excel megváltoztatja, amikor kombinációt készít. Győződjön meg arról is, hogy a “Series1” nincs a másodlagos tengelyre tolva – a diagramtípus melletti jelölőnégyzetet nem szabad megjelölni.
3. Kattintson az “OK” gombra.

9. lépés: Módosítsa a vízszintes tengely skáláját.

Középre helyezze a diagramot a haranggörbén a vízszintes tengely skálájának beállításával. Kattintson a jobb gombbal a vízszintes tengelyre, és válassza ki “Formázza a tengelyt” a menüből.

Amint megjelenik a tálca, tegye a következőket:

• Lépjen a Tengelybeállítások fülre.
• Állítsa a Minimális határérték értéket “15” értékre.
• Állítsa a Maximális határérték értéket “125” értékre.

A tengelyskála-tartományt tetszés szerint módosíthatja, de mivel ismeri a szórás tartományait, állítsa a Bounds értékeket kissé távolabb minden harmadik standard eltéréstől, hogy a görbe “farka” megjelenjen .

10. lépés: Helyezze be és helyezze el az egyéni adatcímkéket.

Amint csiszolja a diagramját, ügyeljen arra, hogy az egyéni adatcímkék hozzáadásához. Először kattintson jobb gombbal a “Series2” sorozatot jelölő bármely pontra, és válassza az “Adattáblák hozzáadása” lehetőséget.

Ezután cserélje ki az alapértelmezett címkék a korábban beállítottakkal, és helyezze őket az adatjelölők fölé.

1. Kattintson a jobb gombbal bármelyik „Series2” sorozat adattáblájára.
2. Válassza a „Formátum” lehetőséget Adatcímkék. ”
3. A tálcán váltson a Címkebeállítások fülre.
4. Jelölje be az„ X érték ”négyzetet.
5. Törölje az„ Y érték ”jelölését. ”Mezőbe.
6. A„ Címke helye ”részben válassza a„ Fent ”lehetőséget.

Ezenkívül eltávolíthatja a rácsvonalakat (jobb gombbal kattintson rájuk > Törlés).

11. lépés: Színezze át az adatjelölőket (opcionális).

Végül színezze át a pontokat, hogy jobban illeszkedjenek a diagram stílusához.

1. Kattintson a jobb gombbal bármelyik „Series2” sorozat adattáblájára.
2. Kattintson a “Kitöltés” gombot.
3. Válassza ki a színét a megjelenő palettáról.

Továbbá, r emelje ki a határokat a pontok körül:

1. Kattintson a jobb gombbal ismét ugyanazon az adatjelölőn, és válassza a „Vázlat” lehetőséget.
2. Válassza a „Nincs körvonal” lehetőséget.

12. lépés: függőleges vonalak hozzáadása (opcionális).

Végső kiigazításként adjon hozzá függőleges vonalakat a diagramhoz, hogy elősegítse az SD-értékek hangsúlyozását.

• Válassza ki a diagramdiagramot (így a vonalak közvetlenül beilleszkednek a diagramba).
• Menjen a Beszúrás fülre.
• Kattintson az “Alakzatok” gombra.
• Válassza a “Vonal” lehetőséget.

Tartsa lenyomva a “SHIFT” billentyűt, miközben az egér húzásával tökéletesen függőleges vonalakat rajzolhat az egyes pontoktól oda, ahol minden vonal megfelel a haranggörbének.

A diagram címének módosítása , és a továbbfejlesztett haranggörbe készen áll – megmutatja értékes terjesztési adatait.

És ezt így csinálja. Mostantól bármelyik adatkészletet kiválaszthatja, és normál elosztó haranggörbét hozhat létre az alábbi egyszerű lépéseket követve!