Kako napraviti zvonastu krivulju u Excelu (korak-po-korak vodič)

Zvonasta krivulja (poznata i kao normalna distribucijska krivulja) je način za iscrtavanje i analizu podataka koji izgledaju poput zvonaste krivulje.

U zvonastoj krivulji najviša točka je ona koja ima najveću vjerojatnost da se dogodi, a vjerojatnost pojavljivanja se spušta s obje strane krivulje.

Često se koristi tijekom ocjenjivanja zaposlenika ili tijekom ocjenjivanja na ispitima (Jeste li ikada čuli - "Bit ćete ocijenjeni na krivini?").

Prije nego što uskočim u to kako stvoriti zvonastu krivulju u Excelu, uzmimo primjer kako bismo bolje razumjeli koncept.

Razumijevanje zvonaste krivulje

Pretpostavimo da radite u timu od 100 članova, a vaš menadžer vam kaže da će vaša izvedba biti relativna u odnosu na druge i da će se ocijeniti na krivulji zvona.

To znači da čak i ako je vaš tim najbolji tim ikada, a vi ste svi superjunaci, samo će vas nekolicina dobiti najbolju ocjenu, većina ljudi u vašem timu dobit će prosječnu ocjenu, a šačica će dobiti najnižu ocjena.

Izvor slike: EmpxTrack

Ali zašto nam je potrebna zvonasta krivulja?

Pošteno pitanje!

Pretpostavimo da imate razred od 100 učenika koji se pojavljuju na ispitu. Prema vašem sustavu ocjenjivanja, svatko tko dobije više od 80 od 100 dobiva ocjenu A. No, budući da ste postavili jako lak rad, svi su postigli rezultat iznad 80 i dobili ocjenu A.

Nema ništa loše u ovakvom sustavu ocjenjivanja. Međutim, koristeći ga, ne možete razlikovati nekoga tko je dobio 81 i nekoga tko je dobio 95 (jer bi oboje dobili ocjenu A).

Kako bi usporedba bila poštena i natjecateljski duh ostao živ, za ocjenjivanje performansi često se koristi zvonasta krivulja (barem je tako bilo dok sam bio na fakultetu).

Pristupom zvonaste krivulje ocjene učenika pretvaraju se u percentile koji se zatim međusobno uspoređuju.

Studenti koji dobivaju više ocjene nalaze se na desnoj strani krivulje, a studenti koji imaju niske ocjene su lijevo od krivulje (s većinom učenika u sredini oko srednjeg rezultata).

Sada da biste razumjeli zvonastu krivulju, morate znati o dvije metrike:

• Srednje - prosječna vrijednost svih podatkovnih točaka
• Standardna devijacija - pokazuje koliko skup podataka odstupa od srednje vrijednosti skupa podataka. Na primjer, pretpostavimo da imate grupu od 50 ljudi i bilježite njihovu težinu (u kg). U ovom skupu podataka prosječna težina je 60 kg, a standardna devijacija je 4 kg. To znači da je 68% tjelesne težine unutar 1 standardne devijacije od prosjeka - što bi bilo 56-64 kg. Slično, 95% ljudi je unutar 2 standardne devijacije - što bi bilo 52-68 kg.

Kad imate skup podataka koji je normalno distribuiran, vaša će zvonasta krivulja slijediti sljedeća pravila:

• Središte zvonaste krivulje je srednja vrijednost podatkovne točke (ujedno i najviša točka u zvonastoj krivulji).
• 68,2% ukupnih točaka podataka nalazi se u rasponu (srednja vrijednost - standardna devijacija do srednje vrijednosti + standardna devijacija).
• 95,5% ukupnih točaka podataka nalazi se u rasponu (srednja vrijednost - 2*standardna devijacija do srednje vrijednosti + 2*standardna devijacija)
• 99,7% ukupnih točaka podataka nalazi se u rasponu (srednja vrijednost - 3*standardna devijacija do srednje vrijednosti + 3*standardna devijacija)

Izvor slike: Vijesti MIT -a

Pogledajmo sada kako stvoriti krivulju zvona u Excelu.

Stvaranje zvonaste krivulje u Excelu

Uzmimo primjer razreda učenika koji je dobio bodove na ispitu.

Prosječna ocjena razreda je 65 a standardna devijacija je 10. (Možete izračunati srednju vrijednost pomoću funkcije PROSJEK u Excelu i Standardnog odstupanja pomoću funkcije STDEV.P).

Evo koraka za stvaranje zvonaste krivulje za ovaj skup podataka:

• U ćeliju A1 upišite 35. Ta se vrijednost može izračunati pomoću Srednja vrijednost - 3* Standardna devijacija (65-3*10).
• U ćeliju ispod nje unesite 36 i stvorite niz od 35 do 95 (gdje je 95 srednja vrijednost + 3* standardna devijacija). To možete učiniti brzo pomoću opcije automatskog popunjavanja ili upotrijebite ručicu ispune i povucite je prema dolje da biste ispunili ćelije.
• U ćeliju pored 35 unesite formulu: = NORM.DIST (A1,65,10, FALSE)
  • Imajte na umu da sam ovdje teško kodirao vrijednost srednje vrijednosti i standardne devijacije. Možete ih imati i u ćelijama te koristiti reference ćelija u formuli.
• Opet upotrijebite ručicu za popunjavanje da biste brzo kopirali i zalijepili formulu za sve ćelije.
• Odaberite skup podataka i idite na karticu Umetanje.
• Umetnite grafikon "Raspršivanje s glatkim linijama".

To će vam dati zvonastu krivulju u Excelu.

Sada možete promijeniti naslov grafikona i prilagoditi os ako je potrebno.

Imajte na umu da kada imate nisku standardnu ​​devijaciju, dobivate pakiranu tanku krivulju zvona, a kada imate visoku standardnu ​​devijaciju, krivulja zvona je široka i pokriva više područja na grafikonu.

Ova vrsta zvonaste krivulje može se upotrijebiti za identifikaciju mjesta podataka na grafikonu. Na primjer, u slučaju da je tim pun visokih performansi, kada se ocjenjuje na krivulji, unatoč tome što ima visoke performanse, netko može dobiti prosječnu ocjenu jer je bio na sredini krivulje.

Napomena: U ovom postu na blogu raspravljao sam o konceptu zvonaste krivulje i o tome kako je stvoriti u Excelu. Statističar bi bio prikladniji za razgovor o učinkovitosti zvonaste krivulje i ograničenjima povezanim s njom. Više sam tip iz Excela i moje sudjelovanje u Bell krivulji ograničeno je na izračune koje sam napravio dok sam radio kao financijski analitičar.

Nadam se da vam je ovaj vodič bio koristan!

Recite mi svoja razmišljanja u odjeljku za komentare.

Možda će vam se svidjeti i sljedeći vodiči za Excel:

• Kako napraviti histogram u Excelu.
• Kako izračunati složene kamate u Excelu + BESPLATNI kalkulator.
• Kako stvoriti toplinsku kartu u Excelu.
• Koračni dijagram u Excelu.
• Kako stvoriti vremensku traku / prekretnicu u Excelu.
• Stvaranje Paretovog grafikona u Excelu.
• Stvaranje tortnog grafikona u Excelu
• Napredni grafikoni programa Excel
• Kako pronaći nagib u Excelu? Korištenje formule i grafikona