✅ Jak zrobić krzywą dzwonową w Excelu (przewodnik krok po kroku)

Spisie treści

Zrozumienie krzywej dzwonowej
Tworzenie krzywej dzwonowej w Excelu

Krzywa dzwonowa (znana również jako krzywa rozkładu normalnego) to sposób na wykreślanie i analizowanie danych, które wyglądają jak krzywa dzwonowa.

Na krzywej dzwonowej najwyższy punkt to ten, który ma największe prawdopodobieństwo wystąpienia, a prawdopodobieństwo wystąpienia spada po obu stronach krzywej.

Jest często używany podczas ocen pracowniczych lub podczas oceny na egzaminach (kiedykolwiek słyszeliście - „Będziesz oceniany na krzywej?”).

Teraz, zanim wskoczę, jak utworzyć krzywą dzwonową w Excelu, lepiej zrozumiemy tę koncepcję, biorąc przykład.

Zrozumienie krzywej dzwonowej

Załóżmy, że pracujesz w 100-osobowym zespole, a Twój menedżer mówi Ci, że Twoje wyniki będą odnosić się do innych i zostaną ocenione na krzywej dzwonowej.

Oznacza to, że nawet jeśli twoja drużyna jest najlepszą drużyną w historii i wszyscy jesteście superbohaterami, tylko garstka z was otrzyma najwyższą ocenę, większość osób w twoim zespole otrzyma średnią ocenę, a garstka otrzyma najniższą ocena.

Źródło obrazu: EmpxTrack

Ale dlaczego potrzebujemy krzywej dzwonowej?

Uczciwe pytanie!

Załóżmy, że masz klasę 100 uczniów, która pojawia się na egzaminie. Zgodnie z Twoim systemem oceniania, każdy, kto uzyska ponad 80 na 100 punktów, otrzymuje ocenę A. Ale ponieważ ustawiłeś naprawdę łatwy papier, wszyscy zdobyli powyżej 80 punktów i dostali ocenę A.

Teraz nie ma nic złego w tego rodzaju systemie oceniania. Jednak używając go, nie można odróżnić kogoś, kto otrzymał 81 punktów od kogoś, kto uzyskał 95 (ponieważ obaj otrzymaliby ocenę A).

Aby porównanie było uczciwe i utrzymać ducha rywalizacji przy życiu, do oceny wyników często używa się krzywej dzwonowej (przynajmniej tak było, gdy byłem na studiach).

Stosując podejście krzywej dzwonowej, oceny uczniów są przekształcane na percentyle, które są następnie porównywane ze sobą.

Uczniowie, którzy uzyskują wyższe oceny, znajdują się po prawej stronie krzywej, a uczniowie, którzy uzyskują niskie oceny, znajdują się po lewej stronie krzywej (większość uczniów znajduje się pośrodku, wokół średniego wyniku).

Teraz, aby zrozumieć krzywą dzwonową, musisz znać dwie metryki:

Oznaczać - średnia wartość wszystkich punktów danych
Odchylenie standardowe - pokazuje, jak bardzo zbiór danych odbiega od średniej zbioru danych. Załóżmy na przykład, że masz grupę 50 osób i rejestrujesz ich wagę (w kg). W tym zestawie danych średnia waga wynosi 60 kg, a odchylenie standardowe wynosi 4 kg. Oznacza to, że 68% wagi ludzi mieści się w granicach 1 odchylenia standardowego od średniej – czyli 56-64 kg. Podobnie 95% ludzi mieści się w granicach 2 odchylenia standardowego - co wynosi 52-68 kg.

Jeśli masz zestaw danych, który ma rozkład normalny, twoja krzywa dzwonowa będzie zgodna z poniższymi zasadami:

Środek krzywej dzwonowej jest średnią punktu danych (również najwyższego punktu krzywej dzwonowej).
68,2% wszystkich punktów danych mieści się w zakresie (średnia - odchylenie standardowe do średniej + odchylenie standardowe).
95,5% wszystkich punktów danych mieści się w zakresie (średnia - 2*odchylenie standardowe do średniej + 2*odchylenie standardowe)
99,7% wszystkich punktów danych mieści się w zakresie (średnia - 3*odchylenie standardowe do średniej + 3*odchylenie standardowe)

Źródło obrazu: Wiadomości MIT

Zobaczmy teraz, jak utworzyć krzywą dzwonową w programie Excel.

Tworzenie krzywej dzwonowej w Excelu

Weźmy jako przykład klasę uczniów, którzy uzyskali punkty na egzaminie.

Średni wynik klasy to 65 a odchylenie standardowe wynosi 10. (Średnia można obliczyć za pomocą funkcji ŚREDNIA w programie Excel i odchylenia standardowego za pomocą funkcji ODCH.STANDARDOWE.P).

Oto kroki, aby utworzyć krzywą dzwonową dla tego zbioru danych:

W komórce A1 wpisz 35. Tę wartość można obliczyć za pomocą Średnia - 3* Odchylenie standardowe (65-3*10).
W komórce poniżej wpisz 36 i utwórz serię od 35 do 95 (gdzie 95 to średnia + 3* odchylenie standardowe). Możesz to zrobić szybko, używając opcji autouzupełniania lub użyj uchwytu wypełniania i przeciągnij go w dół, aby wypełnić komórki.
W komórce sąsiadującej z 35 wprowadź formułę: =ROZKŁ.NORMALNY(A1,65,10,FAŁSZ)
- Zauważ, że tutaj zakodowałem na sztywno wartość średniej i odchylenia standardowego. Możesz również mieć je w komórkach i używać odwołań do komórek w formule.
Ponownie użyj uchwytu wypełniania, aby szybko skopiować i wkleić formułę dla wszystkich komórek.
Wybierz zestaw danych i przejdź do zakładki Wstaw.
Wstaw wykres „Rozproszony z gładkimi liniami”.

To da ci krzywą dzwonową w Excelu.

Teraz możesz zmienić tytuł wykresu i dostosować oś, jeśli potrzebujesz.

Zwróć uwagę, że gdy masz niskie odchylenie standardowe, otrzymujesz upakowaną szczupłą krzywą dzwonową, a gdy masz wysokie odchylenie standardowe, krzywa dzwonowa jest szeroka i obejmuje większy obszar na wykresie.

Ten rodzaj krzywej dzwonowej można wykorzystać do określenia, gdzie na wykresie znajduje się punkt danych. Na przykład w przypadku, gdy zespół jest pełen graczy o wysokich wynikach, podczas oceny na krzywej, pomimo tego, że jest bardzo skuteczny, ktoś może uzyskać średnią ocenę, ponieważ znajdował się w środku krzywej.

Uwaga: W tym poście na blogu omówiłem koncepcję krzywej dzwonowej i sposób jej tworzenia w programie Excel. Statystyk lepiej by się nadawał do rozmowy o skuteczności krzywej dzwonowej i związanych z nią ograniczeniach. Jestem bardziej facetem od Excela, a moje zaangażowanie w krzywą Bella ograniczało się do obliczeń, które wykonywałem, kiedy pracowałem jako analityk finansowy.

Mam nadzieję, że ten samouczek okazał się przydatny!

Daj mi znać swoje przemyślenia w sekcji komentarzy.

Możesz również polubić następujące samouczki programu Excel: