Jak utworzyć krzywą dzwonkową rozkładu normalnego w programie Excel?

W tym samouczku zademonstrujemy, jak utworzyć krzywą dzwonkową rozkładu normalnego we wszystkich wersjach programu Excel: 2007, 2010, 2013, 2016 i 2022.

W statystyce krzywa dzwonowa (znana również jako standardowy rozkład normalny lub krzywa Gaussa) to symetryczny wykres, który ilustruje tendencję danych do skupiania się wokół wartości środkowej lub średniej w danym zestawie danych.

Oś y reprezentuje względne prawdopodobieństwo wystąpienia danej wartości w zbiorze danych, podczas gdy oś x przedstawia same wartości na wykresie, tworząc krzywą w kształcie dzwonu, stąd nazwa.

Wykres pomaga nam przeanalizować, czy dana wartość jest częścią oczekiwanej zmienności, czy jest statystycznie istotna, a zatem musi być dokładniej zbadana.

Ponieważ Excel nie ma do zaoferowania żadnych wbudowanych rozwiązań, będziesz musiał sam go narysować. Dlatego opracowaliśmy dodatek Chart Creator, narzędzie, które pozwala tworzyć zaawansowane wykresy Excela za pomocą zaledwie kilku kliknięć.

W tym samouczku krok po kroku dowiesz się, jak utworzyć od podstaw krzywą rozkładu normalnego w programie Excel:

Aby wykreślić krzywą Gaussa, musisz wiedzieć dwie rzeczy:

• Średnia (znany również jako pomiar standardowy). To określa środek krzywej, który z kolei charakteryzuje położenie krzywej.
• Odchylenie standardowe (SD) pomiarów. Definiuje to rozłożenie danych w normalnym rozkładzie - lub w prostym języku angielskim, jak szeroka powinna być krzywa. Na przykład na krzywej dzwonowej pokazanej powyżej, jedno odchylenie standardowe średniej reprezentuje zakres między wynikami egzaminów 53 i 85.

Im niższe SD, tym wyższa krzywa i mniej danych będzie rozłożonych i na odwrót.

Warto wspomnieć o regule 68-95-99,7, którą można zastosować do dowolnej krzywej rozkładu normalnego, co oznacza, że ​​około 68% danych zostanie umieszczonych w odległości jednego SD od średniej, 95% w obrębie dwóch SD i 99,7% w granicach trzy SD.

Teraz, gdy znasz już podstawy, przejdźmy od teorii do praktyki.

Pierwsze kroki

W celach ilustracyjnych załóżmy, że masz wyniki testu 200 uczniów i chcesz ocenić ich „na krzywej”, co oznacza, że ​​oceny uczniów będą oparte na ich względnych wynikach w stosunku do reszty klasy:

Krok 1: Znajdź środek.

Zazwyczaj od samego początku otrzymujesz średnią i SD, ale jeśli tak nie jest, możesz łatwo obliczyć te wartości w zaledwie kilku prostych krokach. Zajmijmy się najpierw średnim.

Ponieważ średnia wskazuje średnią wartość próbki lub populacji danych, standardowy pomiar można znaleźć za pomocą funkcji ŚREDNIA.

Wpisz następującą formułę w dowolnej pustej komórce (F1 w tym przykładzie) obok Twoich rzeczywistych danych (kolumny A oraz b), aby obliczyć średnią wyników egzaminów w zbiorze danych:

1	=ŚREDNIA(B2:B201)

Krótka uwaga: częściej niż nie, może być konieczne zaokrąglenie wyników formuły. Aby to zrobić, po prostu zawiń go w funkcję ZAOKR w następujący sposób:

1	=ZAOKR(ŚREDNIA(B2:B201);0)

Krok 2: Znajdź odchylenie standardowe.

Jeden w dół, jeden do wyjścia. Na szczęście Excel ma specjalną funkcję, która wykonuje całą brudną robotę polegającą na znalezieniu odchylenia standardowego za Ciebie:

1	=ODCH.STANDARDOWE.P(B2:B201)

Formuła ponownie wybiera wszystkie wartości z określonego zakresu komórek (B2:B201) i oblicza jego odchylenie standardowe - nie zapomnij też zaokrąglić wyniku.

1	=OKRĄG(ODCH.STANDARD.P(B2:B201);0)

Krok #3: Ustaw wartości osi X dla krzywej.

Zasadniczo wykres składa się z ogromnej liczby interwałów (pomyśl o nich jako o krokach) połączonych linią tworzącą gładką krzywą.

W naszym przypadku wartości na osi x zostaną użyte do zilustrowania konkretnego wyniku egzaminu, podczas gdy wartości na osi y wskażą nam prawdopodobieństwo uzyskania tego wyniku przez ucznia na egzaminie.

Technicznie rzecz biorąc, możesz uwzględnić dowolną liczbę interwałów - możesz później bez wysiłku usunąć zbędne dane, modyfikując skalę osi poziomej. Tylko upewnij się, że wybrałeś zakres, który będzie zawierał trzy odchylenia standardowe.

Zacznijmy liczyć od jednego (ponieważ uczeń nie może uzyskać negatywnego wyniku z egzaminu) i przejdźmy aż do 150 – tak naprawdę nie ma znaczenia, czy będzie to 150, czy 1500 – aby ustawić kolejny stół pomocniczy.

1. Wybierz dowolną pustą komórkę pod danymi wykresu (na przykład E 4) i typ “1,” wartość, która definiuje pierwszy interwał.
2. Przejdź do Dom patka.
3. w Redagowanie grupa, wybierz „Napełnić.”
4. Pod "Seria w," Wybierz "Kolumna.”
5. Do "Wartość kroku," rodzaj “1.” Ta wartość określa przyrosty, które będą automatycznie dodawane, dopóki program Excel nie osiągnie ostatniego interwału.
6. Do "Zatrzymaj wartość," rodzaj "150,” wartość, która oznacza ostatni interwał, i kliknij „OK.”

Cudem 149 komórek w kolumnie E (E5:E153) zostały wypełnione wartościami od 2 do 150.

UWAGA: Nie ukrywaj oryginalnych komórek danych, jak pokazano na zrzutach ekranu. W przeciwnym razie technika nie zadziała.

Krok 4: Oblicz wartości rozkładu normalnego dla każdej wartości na osi x.

Teraz znajdź wartości rozkładu normalnego - prawdopodobieństwo uzyskania przez ucznia określonego wyniku egzaminu reprezentowanego przez konkretną wartość na osi X - dla każdego z przedziałów. Na szczęście dla Ciebie Excel ma konia roboczego, który wykona wszystkie te obliczenia za Ciebie: funkcję ROZKŁ.NORMALNY.

Wpisz następującą formułę w komórce po prawej stronie (F4) pierwszego interwału (E 4):

1	=ROZKŁ.NORMALNY(E4,$F$1,$F$2,FAŁSZ)

Oto zdekodowana wersja, która pomoże Ci odpowiednio dostosować:

1	=ROZKŁ.NORMALNY([pierwszy przedział];[średnia(odniesienie bezwzględne)];[odchylenie standardowe (odniesienie bezwzględne);FAŁSZ)

Blokujesz wartości średniej i SD, dzięki czemu możesz bez wysiłku wykonać wzór dla pozostałych przedziałów (E5:E153).

Teraz kliknij dwukrotnie uchwyt wypełniania, aby skopiować formułę do pozostałych komórek (F5:F153).

Krok 5: Utwórz wykres punktowy z gładkimi liniami.

Wreszcie nadszedł czas na zbudowanie krzywej dzwonowej:

1. Wybierz dowolną wartość z tabeli pomocniczej zawierającej wartości osi X i Y (E4:F153).
2. Przejdź do Wstawić patka.
3. Kliknij "Wstaw punktowy (X, Y) lub wykres bąbelkowy" przycisk.
4. Wybierać "Rozpraszaj gładkimi liniami”.

Krok #6: Skonfiguruj tabelę etykiet.

Technicznie rzecz biorąc, masz swoją krzywą dzwonową. Ale byłoby to trudne do odczytania, ponieważ brakuje w nim opisujących go danych.

Sprawmy, aby rozkład normalny był bardziej informacyjny, dodając etykiety ilustrujące wszystkie wartości odchylenia standardowego poniżej i powyżej średniej (można ich również użyć do pokazania wyników Z).

W tym celu skonfiguruj kolejny stół pomocniczy w następujący sposób:

Najpierw skopiuj wartość średnią (F1) obok odpowiedniej komórki w kolumnie X-Value (I5).

Następnie oblicz wartości odchylenia standardowego poniżej średniej, wprowadzając tę ​​prostą formułę do komórka I4:

1

=I5-$F$2

Mówiąc najprościej, formuła odejmuje sumę poprzednich wartości odchylenia standardowego od średniej. Teraz przeciągnij uchwyt wypełniania w górę, aby skopiować formułę do pozostałych dwóch komórek (I2:I3).

Powtórz ten sam proces dla odchyleń standardowych powyżej średniej, używając wzoru lustrzanego:

1

=I5+$F$2

W ten sam sposób wykonaj formułę dla pozostałych dwóch komórek (I7:I8).

Na koniec wypełnij wartości etykiet na osi Y (J2:J8) z zerami, tak jak chcesz, aby znaczniki danych zostały umieszczone na osi poziomej.

Krok 7: Wstaw dane etykiety do wykresu.

Teraz dodaj wszystkie przygotowane dane. Kliknij prawym przyciskiem myszy na wykresie wykresu i wybierz „Wybierz Dane.”

W wyświetlonym oknie dialogowym wybierz „Dodać.”

Zaznacz odpowiednie zakresy komórek z tabeli pomocniczej-I2:I8 dla "Wartości serii X" oraz J2:J8 dla "Wartości serii Y”-i kliknij „OK.”

Krok 8: Zmień typ wykresu serii etykiet.

Naszym następnym krokiem jest zmiana typu wykresu nowo dodanej serii, aby znaczniki danych były wyświetlane jako kropki. Aby to zrobić, kliknij prawym przyciskiem myszy na wykresie i wybierz „Zmień typ wykresu.”

Następnie zaprojektuj wykres kombi:

1. Przejdź do Kombinacja patka.
2. Do Seria „Seria 2” reszta "Typ wykresu" do "Rozpraszać.”
   • Uwaga: upewnij się, że „Seria1” pozostaje jako „Rozpraszanie z gładkimi liniami”. Czasami Excel zmieni to, gdy zrobisz Kombinacja Upewnij się również, że „Seria1” nie jest przesunięty do osi pomocniczej - pole wyboru obok typu wykresu nie powinno być zaznaczone.
3. Kliknij "ok.”

Krok #9: Zmodyfikuj skalę osi poziomej.

Wyśrodkuj wykres na krzywej dzwonowej, dostosowując skalę osi poziomej. Kliknij prawym przyciskiem myszy oś poziomą i wybierz „Formatuj oś” z menu.

Gdy pojawi się okienko zadań, wykonaj następujące czynności:

• Przejdź do Opcje osi patka.
• Ustaw Minimalne granice wartość do „15.”
• Ustaw Maksymalne granice wartość do „125.”

Możesz dostosować zakres skali osi, jak chcesz, ale ponieważ znasz zakresy odchyleń standardowych, ustaw wartości Bounds nieco z dala od każdego trzeciego odchylenia standardowego, aby pokazać „ogon” krzywej.

Krok 10: Wstaw i umieść niestandardowe etykiety danych.

Dopracowując wykres, pamiętaj o dodaniu niestandardowych etykiet danych. Najpierw kliknij prawym przyciskiem myszy dowolną kropkę reprezentującą Seria „Seria 2” i wybierz „Dodaj etykiety danych.”

Następnie zastąp domyślne etykiety wcześniej skonfigurowanymi i umieść je nad znacznikami danych.

1. Kliknij prawym przyciskiem myszy na dowolnym Seria „Seria 2” etykieta danych.
2. Wybierz "Formatuj etykiety danych.”
3. W okienku zadań przejdź do Opcje etykiet patka.
4. Sprawdź "X Wartość" skrzynka.
5. Odznacz „Wartość Y" skrzynka.
6. Pod "Pozycja etykiety," wybierać "Nad.”

Ponadto możesz teraz usunąć linie siatki (kliknij je prawym przyciskiem myszy> Usuń).

Krok #11: Zmień kolor znaczników danych (opcjonalnie).

Na koniec ponownie pokoloruj kropki, aby dopasować je do stylu wykresu.

1. Kliknij prawym przyciskiem myszy na dowolnym Seria „Seria 2” etykieta danych.
2. Kliknij "Napełnić" przycisk.
3. Wybierz swój kolor z wyświetlonej palety.

Usuń również obramowania wokół kropek:

1. Ponownie kliknij prawym przyciskiem myszy ten sam znacznik danych i wybierz „Zarys.”
2. Wybierać "Brak zarysu.”

Krok #12: Dodaj pionowe linie (opcjonalnie).

Jako ostateczną korektę możesz dodać pionowe linie do wykresu, aby podkreślić wartości SD.

• Wybierz wykres wykresu (w ten sposób linie zostaną wstawione bezpośrednio do wykresu).
• Przejdź do Wstawić patka.
• Kliknij "Kształty" przycisk.
• Wybierać "Linia.”

Przytrzymaj "ZMIANA" podczas przeciągania myszy, aby narysować idealnie pionowe linie od każdej kropki do miejsca, w którym każda linia spotyka się z krzywą dzwonka.

Zmień tytuł wykresu, a poprawiona krzywa dzwonowa będzie gotowa do wyświetlania cennych danych dotyczących dystrybucji.

I tak to robisz. Możesz teraz wybrać dowolny zestaw danych i utworzyć krzywą dzwonową rozkładu normalnego, wykonując te proste czynności!