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

Ten samouczek pokazuje, jak utworzyć krzywą dzwonową rozkładu normalnego we wszystkich wersjach programu Excel: 2007, 2010, 2013 , 2016 i 2019.

W statystyce krzywa dzwonowa (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 same wartości na wykresie, tworząc dzwonek krzywa, stąd nazwa.

Wykres pomaga nam przeanalizować, czy dana wartość jest częścią oczekiwanej zmienności lub jest statystycznie istotna, a zatem należy ją dokładniej zbadać.

Ponieważ program Excel nie ma żadnych wbudowanych rozwiązań do zaoferowania, będziesz musiał sam je wykreślić. Dlatego opracowaliśmy dodatek Chart Creator, narzędzie, które umożliwia tworzenie zaawansowanych wykresów programu Excel za pomocą zaledwie kilku kliknięć.

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

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

• Średnia (znana 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 rozrzut danych w rozkładzie normalnym – 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 od 53 do 85.

Im niższe odchylenie standardowe, tym wyższa krzywa i mniej Twoje dane zostaną rozproszone i odwrotnie.

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 granicach jednego odchylenia standardowego od średniej, 95% między dwoma odchyleniami standardowymi i 99,7% w ramach trzech odchyleń od standardowej.

Teraz, gdy poznaj podstawy, przejdźmy od teorii do praktyki.

Pierwsze kroki

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

Krok # 1: Znajdź średnią.

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

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

Wpisz następujący wzór do dowolnej pustej komórki (w tym przykładzie F1) obok rzeczywistych danych (kolumny A i B), aby obliczyć średnią wyników egzaminów w zbiorze danych:

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ę ROUND w następujący sposób:

Krok 2: znajdź odchylenie standardowe.

Jeden w dół, jeden do przejścia. Na szczęście program Excel ma specjalną funkcję, która wykonuje całą brudną robotę znajdowania odchylenia standardowego za Ciebie:

Ponownie formuła wybiera wszystkie wartości z określonego zakresu komórek (B2: B201) i oblicza odchylenie standardowe – po prostu nie zapomnij zaokrąglić w górę również wynik.

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 ze sobą linią w celu utworzenia gładkiej krzywej.

W naszym przypadku wartości na osi X zostaną użyte do zilustrowania konkretnego wyniku egzaminu, podczas gdy wartości na osi Y my określamy prawdopodobieństwo, że uczeń uzyska ten wynik na egzaminie.

Z technicznego punktu widzenia możesz uwzględnić dowolną liczbę interwałów – możesz później bez wysiłku usunąć zbędne dane, modyfikując skalę osi poziomej. Po prostu upewnij się, że wybrałeś zakres, który będzie obejmował trzy odchylenia standardowe.

Zacznijmy liczyć od jednego (ponieważ nie ma możliwości, aby uczeń uzyskał negatywny wynik egzaminu) i przejdźmy aż do 150 – nie ma znaczenia, czy jest to 150 czy 1500 – aby skonfigurować kolejną tabelę pomocniczą.

1. Wybierz dowolną pustą komórkę poniżej danych wykresu (na przykład E4) i wpisz „1”, wartość, która definiuje pierwszy interwał.
2. Przejdź do karty Strona główna.
3. W grupie Edycja wybierz „Wypełnij”.
4. W sekcji „Seria w”, ”Wybierz„ Kolumna ”.
5. W polu„ Wartość kroku ”wpisz„ 1 ”. Ta wartość określa przyrosty, które będą automatycznie dodawane, aż program Excel osiągnie ostatni interwał.
6. W polu „Wartość zatrzymania” wpisz „150”, wartość odpowiadającą ostatniemu interwałowi i kliknij przycisk „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, że uczeń uzyska określony wynik egzaminu reprezentowany przez określoną wartość na osi x —Dla każdego z przedziałów. Na szczęście dla Ciebie program Excel ma narzędzie do wykonywania wszystkich tych obliczeń za Ciebie: funkcja ROZKŁAD.NORMALNY.

Wpisz następującą formułę w komórce po prawej (F4) Twój pierwszy interwał (E4):

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

Zablokujesz średnią i odchylenie standardowe, aby móc 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 dzwonka:

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

Krok 6: Skonfiguruj tabelę etykiet.

Z technicznego punktu widzenia mieć krzywą dzwonkową. Ale byłoby to trudne do odczytania, ponieważ brakuje w nim danych opisujących ją.

Uczyńmy rozkład normalny bardziej pouczającym, dodając etykiety ilustrujące wszystkie wartości odchylenia standardowego poniżej i powyżej średniej ( możesz ich również użyć do wyświetlania wyników z).

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

Najpierw skopiuj średnią wartość (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órki I4:

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:

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) zerami, tak jak chcesz znaczniki danych umieszczone na osi poziomej.

Krok # 7: Wstaw dane etykiety do wykresu.

Teraz dodaj wszystkie przygotowane dane. Kliknij prawym przyciskiem myszy wykres i wybierz „Wybierz dane”.

W wyświetlonym oknie dialogowym wybierz opcję „ Dodaj. ”

Podświetl 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.

Następnym krokiem jest zmiana typu wykresu nowo dodanej serii, tak aby znaczniki danych były wyświetlane jako kropki. Aby to zrobić, kliknij prawym przyciskiem myszy wykres i wybierz opcję „Zmień Typ wykresu. ”

Następnie zaprojektuj wykres kombi:

1. Przejdź do Karta Combo.
2. W przypadku serii „Seria2” zmień „Typ wykresu” na „Rozproszenie”.
   • Uwaga: upewnij się, że „Seria 1” pozostaje „Rozpraszaj gładkimi liniami”. Czasami Excel zmienia to podczas tworzenia Combo. Upewnij się również, że „Seria 1” nie jest wypychana do osi pomocniczej – pole wyboru obok typu wykresu nie powinno być zaznaczone.
3. Kliknij „OK”.

Krok 9: Modyfikuj 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 zakładki Opcje osi.
• Ustaw wartość minimalnych granic na „15”.
• Ustaw wartość maksymalnych granic na „125”.

Możesz dostosować zakres skali osi według własnego uznania, ale ponieważ znasz zakresy odchyleń standardowych, ustaw wartości graniczne nieco z dala od każdego z trzech odchyleń standardowych, aby pokazać „koniec” krzywej .

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

Dopracowując wykres, upewnij się, że aby dodać niestandardowe etykiety danych. Najpierw kliknij prawym przyciskiem myszy dowolną kropkę reprezentującą serię „Seria 2” i wybierz „Dodaj etykiety danych”.

Następnie zamień domyślne etykiety z tymi, które wcześniej skonfigurowałeś, i umieść je nad znacznikami danych.

1. Kliknij prawym przyciskiem myszy dowolną etykietę danych serii „Series2”.
2. Wybierz opcję „Formatuj Etykiety danych ”.
3. W okienku zadań przejdź do karty Opcje etykiet.
4. Zaznacz pole„ Wartość X ”.
5. Usuń zaznaczenie pola„ Wartość Y ” ”.
6. W sekcji„ Pozycja etykiety ”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 „Seria 2”.
2. Kliknij przycisk „Wypełnij”.
3. Wybierz kolor z wyświetlonej palety.

Również r usuń obramowanie wokół kropek:

1. Ponownie kliknij prawym przyciskiem myszy ten sam znacznik danych i wybierz „Kontur”.
2. Wybierz „Bez konturu”.

Krok # 12: Dodaj pionowe linie (opcjonalnie).

Na koniec możesz dodaj pionowe linie do wykresu, aby podkreślić wartości SD.

• Wybierz wykres (w ten sposób linie zostaną wstawione bezpośrednio do wykresu).
• Idź do karty Wstaw.
• Kliknij przycisk „Kształty”.
• Wybierz „Linia”.

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

Zmień tytuł wykresu i Twoja ulepszona krzywa dzwonkowa jest gotowa – pokazując cenne dane dotyczące dystrybucji.

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