ez az oktatóanyag bemutatja, hogyan hozhat létre normál eloszlási haranggörbét az Excel összes verziójában: 2007, 2010, 2013, 2016 és 2019.

ban ben statisztika, a haranggörbe (más néven a standard normál eloszlás vagy Gauss-görbe) egy szimmetrikus gráf, amely szemlélteti az adatok azon tendenciáját, hogy egy adott adatkészletben egy középérték vagy átlag körül csoportosuljanak.

az y tengely egy adott érték relatív valószínűségét képviseli az adatkészletben, míg az x tengely magukat 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árható változásnak, vagy statisztikailag szignifikáns, ezért alaposabban meg kell vizsgálni.

mivel az Excel nem rendelkezik beépített megoldásokkal, magának kell megterveznie. Ezért fejlesztettük ki a Chart Creator bővítményt, egy olyan eszközt, amely lehetővé teszi, hogy néhány kattintással fejlett Excel diagramokat készítsen.

ebben a lépésről lépésre szóló oktatóanyagban megtudhatja, hogyan hozhat létre normál eloszlási 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 mérések szórása (SD). Ez határozza meg az adatok terjedését a normál eloszlásban—vagy egyszerű angol nyelven, milyen szélesnek kell lennie a görbének. Például a fent bemutatott haranggörbében az átlag egy szórása az 53 és 85 vizsga pontszámok közötti tartományt jelenti.

minél alacsonyabb az SD, annál magasabb a görbe, és annál kisebb lesz az adatok eloszlása, és fordítva.

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

most, hogy ismeri a lényeget, térjünk át az elméletről a gyakorlatra.

első lépések

illusztrációként tegyük fel, hogy 200 diák teszteredményei vannak, és “görbén” szeretné osztályozni őket, ami azt jelenti, hogy 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 az átlagot és az SD értékeket a kezdetektől kapja meg, 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 foglalkozzunk az átlaggal.

mivel az átlag egy minta vagy adatpopuláció átlagértékét jelzi, a szokásos mérést az átlag függvény segítségével találhatja meg.

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

gyors megjegyzés: gyakran előfordulhat, hogy fel kell kerekítenie a képlet kimenetét. Ehhez egyszerűen tekerje be a kerek funkcióba az alábbiak szerint:

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

egy megvan, még egy van hátra. 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:

ismét a képlet felveszi az összes értéket a megadott cellatartományból (B2:B201), és kiszámítja a szórását—csak ne felejtse 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 alkot (gondoljon rájuk lépésként), amelyek egy vonallal vannak összekötve, hogy sima görbét hozzanak létre.

esetünkben az x tengely értékeit használjuk egy adott vizsga pontszámának illusztrálására, míg az y tengely értékei megmondják nekünk annak valószínűségét, hogy egy hallgató megkapja 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 redundáns adatokat a vízszintes tengely skálájának módosításával. Csak győződjön meg róla, hogy olyan tartományt választ, amely magában foglalja a három szórást.

kezdjük a számlálást egynél (mivel nincs mód arra, hogy a hallgató negatív vizsga pontszámot kapjon), és egészen 150—ig—nem igazán számít, hogy 150 vagy 1500-hoz állítsunk be egy másik segítő táblát.

1. válasszon ki egy üres cellát a diagramadatok alatt (például E4), és írja be az “1” értéket, amely meghatározza az első intervallumot.
2. keresse meg a KEZDŐLAP lapot.
3. a szerkesztési csoportban válassza a “kitöltés” lehetőséget.”
4. a “Series in”, “select” oszlopban.”
5. a “Lépésérték” mezőbe írja be az “1.”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 Érték” mezőbe írja be a “150” értéket, amely az utolsó intervallumot jelenti, majd kattintson az “OK” gombra.”

csodával határos módon az E oszlop 149 celláját (E5:E153) kitöltötték az 2-től 150-ig terjedő értékekkel.

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ási értékeket minden x-tengely értékéhez.

most keresse meg a normál eloszlási értékeket—annak a valószínűségét, hogy a hallgató egy adott x tengelyérték által képviselt bizonyos vizsgaértéket kap-az egyes intervallumokra. Szerencsédre az Excel rendelkezik a munkalóval, hogy elvégezze ezeket a számításokat az Ön számára: a norma.DIST funkció.

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

itt van a dekódolt verzió, amely segít ennek megfelelően beállítani:

zárolja az átlagos és SD értékeket, így könnyedén végrehajthatja a képletet a fennmaradó intervallumokra (E5:E153).

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

Lépés #5: hozzon létre egy scatter telek sima vonalak.

végül eljött az idő a haranggörbe felépítéséhez:

1. Jelöljön ki bármely értéket a segítő táblában, amely tartalmazza az x-és y-tengely értékeit (E4:F153).
2. lépjen a Beszúrás fülre.
3. kattintson a” Scatter (X, Y) vagy buborékdiagram beszúrása ” gombra.
4. válassza a “szórás sima vonalakkal.”

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

technikailag megvan a haranggörbe. De nehéz lenne olvasni, mivel hiányzik minden leíró adat.

tegyük informatívabbá a normál eloszlást azáltal, hogy hozzáadjuk az összes szórási értéket az átlag alatt és felett (ezeket felhasználhatjuk a z-pontszámok megjelenítésére is).

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

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

ezután számítsa ki az átlag alatti szórási é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ási értékek összegét az átlagból. Most húzza felfelé a kitöltő fogantyút, hogy a képletet a fennmaradó két cellába másolja (I2:I3).

ismételje meg ugyanazt a folyamatot az átlag feletti szórásokra a tükörképlet segítségével:

ugyanígy hajtsa végre a másik két sejt képletét (I7:I8).

végül töltse ki az y tengely címkeértékeit (J2:J8) nullákkal, amennyit csak akar, a vízszintes tengelyre helyezett adatjelölőket.

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

most adja hozzá az összes elkészített adatot. Kattintson a jobb gombbal a diagramra, majd válassza az “Adatok kiválasztása” 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 az “X sorozat értékeihez” és J2:J8 az “Y sorozat értékeihez”—, majd kattintson az “OK” gombra.”

8.lépés: módosítsa a Címkesorozat diagramtípusát.

következő lépésünk 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 diagramra, majd válassza a “change Chart Type” lehetőséget.”

ezután tervezzen egy kombinált diagramot:

1. keresse meg a kombinált lapot.
2. a “Series2” sorozatnál változtassa meg a “diagram típusát” “Scatter” – re.”
   • Megjegyzés: Győződjön meg arról, hogy a “Series1” továbbra is “sima vonalakkal szóródik.”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özpontosítsa a diagramot a haranggörbén a vízszintes tengely skála beállításával. Kattintson a jobb gombbal a vízszintes tengelyre, majd válassza a menüből a “tengely formázása” lehetőséget.

amint megjelenik a Munkaablak, tegye a következőket:

• lépjen a Tengelybeállítások fülre.
• állítsa a minimális határértéket “15-re.”
• állítsa a maximális határértéket “125-re.”

a tengely skálatartományát tetszés szerint módosíthatja, de mivel ismeri a szórási tartományokat, állítsa be a határérték-értékeket kissé távol a harmadik szórásoktól, hogy megmutassa a görbe “farkát”.

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

a diagram csiszolása közben feltétlenül adja hozzá az egyéni adatcímkéket. Először kattintson a jobb gombbal bármelyik pontra, amely a “Series2” sorozatot képviseli, majd válassza az “Adatcímkék hozzáadása” lehetőséget.”

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

1. kattintson a jobb gombbal bármelyik sorozat “Series2” adatcímkéjére.
2. Válassza Az “Adatcímkék Formázása” Lehetőséget.”
3. a munkaablakban 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” négyzet jelölését.
6. a “címke pozíciója” alatt válassza ki a “lehetőséget.”

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

11. lépés: az adatjelzők újraszínezése (opcionális).

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

1. kattintson a jobb gombbal bármelyik sorozat “Series2” adatcímkéjére.
2. kattintson a “kitöltés” gombra.
3. válassza ki a színt a megjelenő palettáról.

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

1. kattintson a jobb gombbal ugyanarra az adatjelzőre, majd válassza a ” vázlat.”
2. Válassza A “Nincs Vázlat” Lehetőséget.”

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

végső beállításként függőleges vonalakat adhat a diagramhoz az SD-értékek hangsúlyozásához.

• válassza ki a diagramot (így a vonalak közvetlenül a diagramba kerülnek).
• lépjen a Beszúrás fülre.
• kattintson az “alakzatok” gombra.
• Válassza A “Sor” Lehetőséget.”

tartsa lenyomva a” SHIFT ” gombot, miközben az egeret húzza, hogy tökéletesen függőleges vonalakat rajzoljon minden pontról oda, ahol minden vonal megfelel a haranggörbének.

változtassa meg a diagram címét, és a továbbfejlesztett haranggörbe készen áll—bemutatva az értékes terjesztési adatokat.

és így csinálod. Most választhat bármilyen adatkészletet, és létrehozhat egy normál eloszlási haranggörbét az alábbi egyszerű lépések követésével!