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

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

A statisztikákban a haranggörbe (más néven szokásos normális eloszlás vagy Gauss -görbe) egy szimmetrikus grafikon, amely illusztrálja az adatok azon tendenciáját, hogy egy adott adatkészlet középpontja vagy átlaga körül csoportosulnak.

Az y tengely egy adott érték relatív valószínűségét jelzi az adathalmazban, míg az x tengely maga az értékeket ábrázolja a diagramon, hogy harang alakú görbét hozzon létre, innen a név.

A grafikon segít elemezni, hogy egy adott érték része -e a várt eltérésnek, vagy statisztikailag szignifikáns, és ezért alaposabban meg kell vizsgálni.

Mivel az Excel nem kínál beépített megoldásokat, ezt saját maga kell elkészítenie. Ezért fejlesztettük ki a Chart Creator bővítményt, egy eszközt, amely lehetővé teszi speciális Excel diagramok létrehozását mindössze néhány kattintással.

Ebben a lépésenkénti oktatóanyagban megtanulhatja, hogyan hozhat létre normál eloszlású haranggörbét az Excelben az alapoktól kezdve:

A Gauss -görbe ábrázolásához 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 szórás (SD) a mérésekből. Ez határozza meg az adatok elterjedését a normál eloszlás szerint-vagy egyszerű angol nyelven, hogy milyen széles legyen a görbe. Például a fent látható haranggörbében az átlag egy szórása az 53 és 85 közötti vizsgaeredmények közötti tartományt jelenti.

Minél alacsonyabb az SD, annál magasabb a görbe, és annál kevésbé kerülnek szét az adatok, é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 az átlagostól egy SD-n belül, két SD-n belül 95% -on belül és 99,7% -on belül helyezik el három SD.

Most, hogy ismeri a lényeges dolgokat, térjünk át az elméletről a gyakorlatra.

Elkezdeni

Szemléltetés céljából tegyük fel, hogy 200 tanuló teszteredménye van, és „görbére” szeretné osztályozni őket, vagyis a diákok osztályzatai az osztály többi részéhez viszonyított teljesítményükön alapulnak:

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

Általában kezdettől fogva megadja az átlagot és az SD értékeket, de ha ez nem így van, akkor ezeket az értékeket néhány egyszerű lépésben könnyen kiszámíthatja. Először foglalkozzunk az átlaggal.

Mivel az átlag egy minta vagy adatpopuláció átlagos értékét jelzi, a standard mérést az ÁTLAG funkció segítségével találhatja meg.

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

1	= ÁTLAG (B2: B201)

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

1	= KÖR (ÁTLAG (B2: B201), 0)

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:

1	= STDEV.P (B2: B201)

Ismét a képlet kiválaszt minden értéket a megadott cellatartományból (B2: B201) és kiszámítja a szórását-csak ne felejtse el felkerekíteni a kimenetet sem.

1	= KÖR (STDEV.P (B2: B201), 0)

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

Alapvetően a diagram hatalmas számú intervallumot tartalmaz (gondoljon rájuk, mint lépésekre), amelyek egy vonallal vannak összekapcsolva, hogy sima görbét hozzanak létre.

Esetünkben az x tengely értékeit egy adott vizsgapontszám illusztrálására használjuk, míg az y tengely értékei azt a valószínűséget jelzik, hogy egy tanuló megkapja ezt a pontszámot a vizsgán.

Technikailag annyi intervallumot tartalmazhat, amennyit csak akar-a redundáns adatokat később könnyedén törölheti a vízszintes tengely skála módosításával. Csak győződjön meg arról, hogy olyan tartományt választ, amely tartalmazza a három szórást.

Kezdjük a számolást egynél (mivel a diák semmilyen módon nem kaphat negatív vizsgapontszámot), és menjünk egészen 150-ig-teljesen mindegy, hogy 150 vagy 1500-egy másik segítő asztal felállítása.

1. Válasszon ki egy üres cellát a diagramadatok alatt (pl E4) és típusát “1,” az első intervallumot meghatározó érték.
2. Navigáljon a itthon fülre.
3. Ban,-ben Szerkesztés csoport, válassza a „Tölt.”
4. A „Sorozatok,"Válassza a"Oszlop.”
5. A „Lépés értéke," típus “1.” Ez az érték határozza meg a növekményeket, amelyek automatikusan összeadódnak, amíg az Excel el nem éri az utolsó intervallumot.
6. A „Stop érték," típus "150,” az utolsó intervallum értékét, majd kattintson a "RENDBEN.”

Csodálatos módon 149 sejt az E oszlopban (E5: E153) 2 és 150 közötti értékekkel vannak feltöltve.

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 a normál eloszlás értékeit minden x-tengely értékhez.

Most keresse meg a normál eloszlási értékeket-annak a valószínűségét, hogy a tanuló minden egyes intervallumban megkap egy bizonyos vizsgapontszámot, amelyet egy adott x-tengely értéke képvisel. Szerencsére az Excel rendelkezik a munkahellyel, amely elvégzi ezeket a számításokat: NORM.DIST függvény.

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

1	= NORM.DIST (E4, $ F $ 1, $ F $ 2, FALSE)

Itt van a dekódolt verzió, amely segít a megfelelő beállításban:

1	= NORM.DIST ([az első intervallum], [az átlag (abszolút referencia)], [a szórás (abszolút referencia), HAMIS)

Lezárja az átlag és az SD értékeket, hogy könnyedén végrehajthassa a képletet a fennmaradó intervallumokra (E5: E153).

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

5. lépés: Hozzon létre szórási rajzot sima vonalakkal.

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

1. Válasszon ki bármilyen értéket a segédtáblázatban, amely tartalmazza az x és y tengely értékeit (E4: F153).
2. Menj a Beszúrás fülre.
3. Kattints a "Beszúrás (X, Y) vagy buborékdiagram”Gombot.
4. Választ "Szórás sima vonalakkal. ”

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

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

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

Ehhez állítson fel egy másik segédasztalt az alábbiak szerint:

Először másolja le az átlagértéket (F1) a megfelelő cella mellett az X-érték oszlopban (I5).

Ezután számítsa ki az átlag alatti szórási értékeket, ha beírja ezt az egyszerű képletet cella I4:

1	= I5- $ F $ 2

Egyszerűen fogalmazva, a képlet kivonja az előző szórás értékeinek ö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 ugyanezt a folyamatot az átlag feletti szórásoknál a tükörképlet használatával:

1	= I5+$ F $ 2

Hasonló módon hajtsa végre a képletet a másik két cellához (I7: I8).

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

7. lépés: Illessze be a címkeadatokat a diagramba.

Most adja hozzá az összes előkészített adatot. Kattintson a jobb gombbal a diagramra, és válassza a „Válassza az Adatok lehetőséget.”

A megjelenő párbeszédpanelen válassza a „Hozzáadás.”

Jelölje ki a megfelelő cellákat a segítő táblázatból-I2: I8 a „X sorozat értékei”És J2: J8 a „Y sorozat értékei ”-és kattintson a "RENDBEN.”

8. lépés: Módosítsa a címke sorozat diagramtípusát.

Következő lépésünk az újonnan hozzáadott sorozat diagramtípusának megváltoztatása, hogy az adatjelzők pontokban jelenjenek meg. Ehhez kattintson a jobb gombbal a grafikonra, és válassza a „Diagramtípus módosítása.”

Ezután készítsen kombinált diagramot:

1. Navigáljon a Kombináció fülre.
2. For „Sorozat 2” sorozat, változás "Diagram típusa" nak nek "Szétszór.”
   • Megjegyzés: Győződjön meg arról, hogy „Sorozat1"Marad mint"Szórás sima vonalakkal. ” Néha az Excel megváltoztatja, amikor a Kombináció Győződjön meg arról is, hogy "Sorozat1”Nincs a másodlagos tengelyre tolva-a diagramtípus melletti jelölőnégyzetet nem szabad megjelölni.
3. Kattintson a „rendben.”

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

Középre állítsa a diagramot a haranggörbére a vízszintes tengely skála beállításával. Kattintson a jobb gombbal a vízszintes tengelyre, és válassza a „A tengely formázása”A menüből.

Miután megjelenik a munkaablak, tegye a következőket:

• Menj a Tengely opciók fülre.
• Állítsa be a Minimális határok érték "15.”
• Állítsa be a Maximum korlátok érték "125.”

A tengely skálatartományát tetszés szerint módosíthatja, de mivel ismeri a szórási tartományokat, állítsa a Határértékeket egy kicsit távolabb minden harmadik szórástól, hogy megjelenjen a görbe „farka”.

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

A diagram csiszolásakor feltétlenül adja hozzá az egyéni adatcímkéket. Először kattintson a jobb gombbal bármelyik pontra „Sorozat 2” sorozat és válassza a "Adatcímkék hozzáadása.”

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

1. Kattintson a jobb gombbal bármelyikre „Sorozat 2” sorozat adatcímke.
2. Válassza a „Adatcímkék formázása.”
3. A munkaablakban váltson a Címke beállításai fülre.
4. Ellenőrizd a "X érték”Doboz.
5. Törölje a jelölést a "Y érték”Doboz.
6. A „Címke pozíciója," választ "Felett.”

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

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

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

1. Kattintson a jobb gombbal bármelyikre „Sorozat 2” sorozat adatcímke.
2. Kattints a "Tölt”Gombot.
3. Válassza ki a színét a megjelenő palettáról.

Ezenkívül távolítsa el a pontok körüli szegélyeket:

1. Kattintson jobb gombbal ismét ugyanazon adatjelzőre, és válassza a „Vázlat.”
2. Választ "Nincs vázlat.”

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

Végső kiigazításként függőleges vonalakat adhat hozzá a diagramhoz, hogy kiemelje az SD -értékeket.

• Válassza ki a diagram diagramot (így a sorok közvetlenül a diagramba kerülnek beillesztésre).
• Menj a Beszúrás fülre.
• Kattints a "Alakzatok”Gombot.
• Választ "Vonal.”

Tartsa lenyomva a "VÁLTÁS" gombot, miközben az egeret húzza, hogy tökéletesen függőleges vonalakat rajzoljon minden pontból oda, ahol minden vonal találkozik a haranggörbével.

Módosítsa a diagram címét, és a továbbfejlesztett haranggörbe készen áll az értékes disztribúciós adatok megjelenítésére.

És így csinálod. Most kiválaszthat bármilyen adatkészletet, és létrehozhat normál eloszlású haranggörbét az alábbi egyszerű lépések végrehajtásával!