ten samouczek pokaże, jak utworzyć krzywą dzwonka rozkładu normalnego we wszystkich wersjach programu Excel: 2007, 2010, 2013, 2016 i 2019.

w statystykach krzywa dzwonkowa (znana również jako standardowy rozkład normalny lub krzywa Gaussa) jest symetrycznym wykresem, który ilustruje tendencję danych do skupiania się wokół wartości środkowej lub średniej w danym zbiorze danych.

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

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

ponieważ Excel nie ma żadnych wbudowanych rozwiązań do zaoferowania, będziesz musiał sam je wykreślić. Dlatego opracowaliśmy dodatek Kreator wykresów, narzędzie, które pozwala tworzyć zaawansowane wykresy Excel za pomocą zaledwie kilku kliknięć.

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

aby narysować krzywą Gaussa, musisz wiedzieć dwie rzeczy:

• średnia (znana również jako pomiar standardowy). To określa środek krzywej-co z kolei charakteryzuje położenie krzywej.
• odchylenie standardowe (SD) pomiarów. To określa rozrzut danych w rozkładzie normalnym – lub w prostym języku angielskim, jak szeroka powinna być krzywa. Na przykład, w krzywej bell pokazanej powyżej, jedno odchylenie standardowe średniej reprezentuje zakres między wynikami egzaminu 53 i 85.

im niższa wartość SD, tym wyższa krzywa i tym mniej danych zostanie rozłożonych i odwrotnie.

warto wspomnieć o zasadzie 68-95-99. 7, którą można zastosować do dowolnej krzywej rozkładu normalnego, co oznacza, że około 68% Twoich danych zostanie umieszczone w obrębie jednego SD od średniej, 95% w granicach dwóch SD, a 99,7% w granicach trzech SD.

teraz, gdy znasz podstawy, przejdźmy od teorii do praktyki.

pierwsze kroki

dla celów 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ędnej wydajności w stosunku do reszty klasy:

Krok # 1: Znajdź średnią.

zazwyczaj od początku podawane są wartości średnie i SD, ale jeśli tak nie jest, możesz łatwo obliczyć te wartości w kilku prostych krokach. Najpierw zmierzmy się ze średnią.

ponieważ średnia oznacza średnią wartość próbki lub populacji danych, możesz znaleźć standardowy pomiar za pomocą funkcji średniej.

wpisz następującą formułę w dowolnej pustej komórce (F1 w tym przykładzie) obok rzeczywistych danych (kolumny a i B), aby obliczyć średnią ocen z egzaminu w zbiorze danych:

szybka Uwaga: częściej niż nie, może być konieczne zaokrąglenie wyjścia formuły. Aby to zrobić, po prostu zawiń go w funkcję okrągłą w następujący sposób:

Krok #2: Znajdź odchylenie standardowe.

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

ponownie, formuła wybiera wszystkie wartości z określonego zakresu komórek (B2: B201)i oblicza jego odchylenie standardowe-po prostu nie zapomnij zaokrąglić wyjścia.

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

zasadniczo Wykres stanowi ogromną liczbę interwałów (pomyśl o nich jako o krokach) połączonych ze sobą linią, tworząc gładką krzywą.

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

technicznie można uwzględnić dowolną liczbę interwałów-można bezproblemowo usunąć zbędne dane później, modyfikując poziomą skalę osi. Upewnij się tylko, że wybierzesz zakres, który będzie zawierał trzy odchylenia standardowe.

zacznijmy liczyć od jednego (ponieważ nie ma możliwości, aby student uzyskał negatywny wynik egzaminu) i przejdź do 150—nie ma znaczenia, czy jest to 150, czy 1500—aby ustawić inną tabelę pomocniczą.

1. Wybierz dowolną pustą komórkę poniżej danych wykresu (np. E4) i wpisz „1”, wartość określającą pierwszy interwał.
2. przejdź do zakładki Strona główna.
3. w grupie Edycja wybierz ” wypełnij.”
4. w kolumnie” seria w „Wybierz”.”
5. dla” wartość kroku „wpisz” 1.”Ta wartość określa przyrosty, które będą automatycznie dodawane, dopóki program Excel nie osiągnie ostatniego interwału.
6. w polu „Zatrzymaj wartość” wpisz „150”, wartość oznaczającą ostatni interwał i kliknij ” OK.”

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 osi X.

teraz znajdź wartości rozkładu normalnego-prawdopodobieństwo uzyskania przez ucznia określonego wyniku egzaminu reprezentowanego przez określoną wartość osi x – dla każdego z przedziałów. Na szczęście dla Ciebie, Excel ma konia roboczego, aby wykonać wszystkie te obliczenia za Ciebie: normę.Funkcja DIST.

wpisz następującą formułę w komórce po prawej (F4) pierwszego interwału (E4):

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

blokujesz wartości średnie i SD, aby bez wysiłku wykonać formułę dla pozostałych interwałów (E5: E153).

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

Krok # 5: Utwórz wykres punktowy z gładkich linii.

wreszcie nadszedł czas na budowę krzywej Dzwonkowej:

1. Wybierz dowolną wartość w tabeli pomocniczej zawierającej wartości osi x i y(E4: F153).
2. przejdź do zakładki Wstaw.
3. kliknij przycisk „Wstaw punkt rozproszony (X, Y) lub Wykres Bąbelkowy”.
4. Wybierz ” Scatter z gładkimi liniami.”

Krok #6: Skonfiguruj tabelę etykiet.

Ale trudno byłoby go odczytać, ponieważ brakuje w nim jakichkolwiek danych opisujących go.

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żesz ich użyć do pokazania z-scores).

w tym celu ustaw kolejną tabelę pomocniczą w następujący sposób:

najpierw skopiuj średnią wartość (F1) obok odpowiedniej komórki w kolumnie Wartość X (i5).

następnie Oblicz wartości odchylenia standardowego poniżej średniej, wprowadzając ten prosty wzór do komórki I4:

Mówiąc najprościej, wzór odejmuje sumę poprzednich wartości odchylenia standardowego od średniej. Teraz przeciągnij uchwyt wypełnienia 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 za pomocą wzoru lustrzanego:

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

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

Krok # 7: Wstaw dane etykiety do wykresu.

teraz dodaj wszystkie dane, które przygotowałeś. Kliknij prawym przyciskiem myszy wykres i wybierz ” Wybierz dane.”

w oknie dialogowym, które się pojawi, wybierz „Dodaj.”

zaznacz odpowiednie zakresy komórek z tabeli pomocniczej—I2:I8 dla „wartości serii X” i 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 wyglądały jak kropki. Aby to zrobić, kliknij prawym przyciskiem myszy wykres i wybierz ” Zmień typ wykresu.”

następnie Zaprojektuj wykres kombi:

1. przejdź do zakładki Combo.
2. dla serii „Series2” Zmień „typ wykresu” na ” Scatter.”
   • Uwaga: Upewnij się, że „Series1″ pozostaje ” Scatter z gładkimi liniami.”Czasami Excel zmieni go, gdy tworzysz Kombi, upewnij się również, że „Series1” nie jest przesunięty na oś drugorzędną—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 na osi poziomej i wybierz „Formatuj oś” Z menu.

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

• przejdź do karty Opcje osi.
• ustaw minimalną wartość granic na „15.”
• Ustaw maksymalną wartość granic na ” 125.”

zakres skali osi można dostosować według własnego uznania, ale ponieważ znasz zakresy odchyleń standardowych, ustaw wartości granic nieco od każdego z trzecich odchyleń standardowych, aby pokazać „ogon” krzywej.

krok #10: Wstawianie i pozycjonowanie niestandardowych etykiet danych.

podczas polerowania wykresu należy dodać niestandardowe etykiety danych. Najpierw kliknij prawym przyciskiem myszy dowolną kropkę reprezentującą serię ” Series2 „i wybierz” Dodaj etykiety danych.”

następnie zamień domyślne etykiety na wcześniej skonfigurowane i umieść je nad znacznikami danych.

1. kliknij prawym przyciskiem myszy dowolną etykietę danych serii „Series2”.
2. Wybierz ” Formatuj Etykiety Danych.”
3. w Okienku zadań przejdź do zakładki opcje etykiety.
4. zaznacz pole” Wartość X”.
5. odznacz pole „wartość Y”.
6. pod „Label Position” wybierz ” powyżej.”

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

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

na koniec Zmień kolor kropek, aby dopasować je do stylu wykresu.

1. kliknij prawym przyciskiem myszy dowolną etykietę danych serii „Series2”.
2. kliknij przycisk” Wypełnij”.
3. Wybierz swój kolor z palety, która się pojawi.

również Usuń obramowania wokół punktów:

1. kliknij ponownie prawym przyciskiem myszy ten sam znacznik danych i wybierz ” Kontur.”
2. Wybierz ” Brak Konturu.”

krok #12: Dodaj pionowe linie (opcjonalnie).

jako ostateczne dopasowanie 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 zakładki Wstaw.
• kliknij przycisk” kształty”.
• Wybierz ” Linia.”

przytrzymaj klawisz „SHIFT”, przeciągając myszką, aby narysować idealnie pionowe linie od każdej kropki do miejsca, w którym każda linia styka się z krzywą dzwonkową.

Zmień tytuł wykresu, a poprawiona krzywa Dzwonowa będzie gotowa—Pokaż cenne dane dotyczące dystrybucji.

i tak to się robi. Możesz teraz wybrać dowolny zestaw danych i utworzyć krzywą dzwonkową rozkładu normalnego, wykonując te proste kroki!