Krzywa dzwonowa, znana również jako rozkład normalny, jest najczęstszym typem rozkładu zmiennej. Jest akceptowany jako dystrybucja, która występuje naturalnie w wielu sytuacjach. Na krzywej dzwonowej najwyższy punkt łuku definiuje średnią, czyli największą liczbę wystąpień elementu. Prawdopodobieństwo wystąpienia zdarzeń maleje w każdą stronę krzywej. Możesz użyć krzywej dzwonowej, aby porównać wyniki, wykluczając skrajności, lub zdefiniować swoje oczekiwania na podstawie możliwości, że wynik będzie mieścił się w zakresie na lewo lub na prawo od środka. W tym przewodniku pokażemy, jak utworzyć krzywą dzwonkową w programie Excel na przykładzie rzeczywistego scenariusza użycia.
Pobierz skoroszyt
Podstawy
Wszystko, czego potrzebujesz, to średnia (średnia) i wartości odchylenia standardowego zbioru danych. Oba te wskaźniki można obliczyć w programie Excel przy użyciu poniższych wzorów.
Biorąc pod uwagę te dwie wartości, wartości o rozkładzie normalnym są zgodne z następującymi regułami:
- Całkowita powierzchnia pod krzywa jest równa 1 (100%)
- Środek krzywej dzwonowej jest średnią z punktu danych
- (1-σ) Około 68,2% powierzchni pod krzywą mieści się w zakresie jednego odchylenia standardowego (średnia ± odchylenie standardowe)
- (2-σ) Około 95,5% obszaru pod krzywą mieści się w zakresie dwóch odchyleń standardowych (średnia ± 2 * odchylenie standardowe)
- (3-σ) Około 99,7% obszaru pod krzywą mieści się w zakresie trzech odchyleń standardowych (średnia ± 3 * odchylenie standardowe)
Obraz z University of Virginia
Tworzenie krzywej dzwonowej w programie Excel
Weźmy typowy przykład i powiedzmy, że analizujemy wyniki egzaminów To dla klasy uczniów. Będziemy używać krzywej dzwonowej do pomiaru wyników egzaminu w celu lepszego porównania.
Zaczynamy od obliczenia metryk, aby wygenerować dane o rozkładzie normalnym, które wygenerują naszą krzywą. Musimy obliczyć:
- Średnia (średnia) wartości.
- Odchylenie standardowe wartości.
- Granice 3-standardowego odchylenia dla wcześniejszego i po średniej.
- Wartość przedziału dla punktów danych o rozkładzie normalnym. Wymaga to również określenia punktów interwału. Możesz wybrać dowolną liczbę, ale pamiętaj, że im więcej interwałów, tym większa precyzja.
Metryki
Rozpocznij od obliczenia średniej i odchylenia standardowego danych. Możesz użyć funkcji ŚREDNIA i ODCH.STANDARDOWE, aby obliczyć odpowiednio średnią i odchylenie standardowe.
Następnym krokiem jest obliczenie 3 wartości odchylenia standardowego, aby ustawić wartości minimalne i maksymalne dla 99,7% danych.
Maksimum = 83,23 – 3 * 5,54 = 99,86
Po ustawieniu minimalnej i maksymalnej wartości dla naszej krzywej musimy wygenerować interwały . Wartości przedziałów będą podstawą dla wartości o rozkładzie normalnym. Aby obliczyć odstępy, wystarczy podzielić obszar między wartościami minimalnymi i maksymalnymi przez liczbę interwałów. W tym przykładzie ustawiliśmy to na 20, ale możesz użyć większej liczby, aby zwiększyć liczbę punktów danych.
Po obliczeniu wartości interwału można wygenerować punkty danych. Aby to zrobić, wprowadź minimalną wartość w komórce. Następnie tuż pod wartością minimalną wprowadź formułę, aby dodać wartość interwału do minimum. W tym przypadku użyliśmy odwołań do komórek (takich jak J4), które pomagają łatwo wypełnić punkty danych do maksymalnej wartości.
Następnym krokiem jest obliczenie wartości rozkładu normalnego z wygenerowanych punktów danych. Aby wygenerować te wartości, możesz użyć funkcji NORM.DIST programu Excel.
Użyj wypełnionych punktów danych jako pierwszego argumentu funkcji. Kolejne argumenty to średnia i odchylenie standardowe. Zakończ formułę wartością logiczną FALSE, aby użyć niekumulatywnego typu tej funkcji.
Wykres
Prawie skończyliśmy! Wybierz punkty danych i wartości rozkładu normalnego, a następnie wstaw wykres punktowy X-Y. Skorzystaj z wersji Scattered with Smooth Lines, aby utworzyć krzywą dzwonkową w programie Excel.
Wykres może wydawać się najpierw nieco dziwny.Zobaczmy, jak możesz poprawić jego wygląd.
Aby zmienić tytuł wykresu, kliknij dwukrotnie tytuł i zaktualizuj nazwę.
Następnie kliknij dwukrotnie Oś X i zdefiniuj wartości minimalne i maksymalne z panelu Opcje osi, aby wyeliminować odstępy po obu stronach. Dzięki temu twój wykres będzie miał lepszy kształt dzwonka. Ustawiamy wartości, które są nieco poza naszym zestawem danych. Na przykład 66 – 100 dla wartości 66,30 – 99,86.
Możesz dodatkowo ulepszyć swój wykres, dodając wartości odchylenia standardowego .
