Jak utworzyć krzywą dzwonową w programie Excel (przewodnik krok po kroku)

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

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

To jest często używany podczas oceny pracowników lub podczas oceny na egzaminach (słyszałeś kiedyś – „Zostaniesz oceniony na krzywej?”).

Zanim przejdę do tworzenia krzywej dzwonowej w Excelu, lepiej zrozummy tę koncepcję, biorąc przykład.

Zrozumienie krzywej dzwonowej

Załóżmy, że pracujesz w zespole liczącym 100 osób, a Twój kierownik mówi, że Twoje wyniki będą w stosunku do innych i będzie oceniany na krzywej dzwonowej.

Oznacza to, że nawet jeśli Twój zespół jest najlepszą drużyną wszechczasów i wszyscy jesteście superbohaterami, tylko garstka z was uzyska najwyższą ocenę, większość z osoby z Twojego zespołu otrzymałyby średnią ocenę, a garstka – najniższą.

Źródło obrazu: EmpxTrack

Ale dlaczego potrzebujemy krzywej dzwonowej?

Dobre pytanie!

Załóżmy, że masz klasę 100 uczniów, która stawić się na egzamin. Zgodnie z Twoim systemem ocen każdy, kto osiągnie wynik powyżej 80 punktów na 100, otrzyma ocenę A. Ale ponieważ wystawiłeś bardzo łatwy papier, każdy uzyskał powyżej 80 punktów i dostał ocenę A.

Teraz nie ma nic złego w tego rodzaju systemie ocen. Jednak używając go, nie możesz rozróżnić między kimś, kto uzyskał 81, a kimś, kto uzyskał 95 (ponieważ obaj dostaliby ocenę A).

Aby porównanie było uczciwe i utrzymało ducha rywalizacji przy życiu, dzwonek krzywa jest często używana do oceny osiągnięć (przynajmniej tak było, gdy byłem na studiach).

Korzystając z metody krzywej dzwonowej, oceny uczniów są przeliczane na percentyle, które są następnie porównywane ze sobą.

Uczniowie otrzymujący wyższe oceny znajdują się po prawej stronie krzywej, a studenci otrzymujący niskie oceny znajdują się po lewej stronie krzywej (większość uczniów znajduje się pośrodku w pobliżu średniej oceny).

Teraz, aby zrozumieć krzywą dzwonową, musisz wiedzieć o dwóch metrykach:

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

Gdy masz zbiór danych, który ma rozkład normalny, krzywa dzwonowa będzie zgodna z poniższymi zasadami :

• Środek krzywej dzwonowej to średnia z punktu danych (również najwyższy punkt na krzywej dzwonowej).
• 68,2% wszystkich punktów danych leży 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 znajduje się w zakresie (średnia – 3 * odchylenie standardowe do średniej + 3 * odchylenie standardowe)

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

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

Tworzenie krzywej dzwonowej w programie Excel

Weźmy przykład klasy uczniów, którzy zostali ocenieni na egzaminie.

Średni wynik z zajęć to 65, a standardowy odchylenie wynosi 10. (Możesz obliczyć średnią za pomocą funkcji ŚREDNIA w programie Excel i Odchylenie standardowe za pomocą funkcji ODCH.STANDARDOWE).

Oto kroki tworzenia krzywej dzwonowej dla tego zestawu danych:

• W komórce A1 wprowadź 35. Tę wartość można obliczyć za pomocą metody Średnia – 3 * odchylenie standardowe (65-3 * 10).
• W komórce poniżej wprowadź 36 i utwórz serie 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ź wzór: = ROZKŁAD.NORMALNY (A1,65,10, FAŁSZ)
  • Zauważ, że tutaj zakodowałem 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 skopiuj i wklej wzór dla wszystkich komórek.
• Wybierz zestaw danych i przejdź do zakładki Wstaw.
• Wstaw „Rozproszenie z wykresem Smooth Lines.

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ą cienką krzywą dzwonkową, a gdy masz wysokie odchylenie standardowe, krzywa dzwonowa jest szeroka i obejmuje większy obszar na wykresie.

Tego rodzaju krzywej dzwonowej można użyć do określenia, gdzie na wykresie znajduje się punkt danych. Na przykład w przypadku, gdy zespół jest pełen najlepszych graczy, podczas oceny na krzywej, mimo że jest dobrym graczem, ktoś może uzyskać średnią ocenę, ponieważ znajdował się na środku krzywej.

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

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

Daj mi znać, co myślisz w sekcji komentarzy.

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

• Jak zrobić histogram w programie Excel.
• Jak obliczyć odsetki złożone w programie Excel + BEZPŁATNY kalkulator.
• Jak utworzyć mapę cieplną w programie Excel.
• Wykres krokowy w programie Excel.
• Jak utworzyć oś czasu / wykres kamieni milowych w programie Excel.
• Tworzenie wykresu Pareto w programie Excel.
• Tworzenie wykresu kołowego w programie Excel
• Zaawansowane wykresy w programie Excel.