krzywa dzwonkowa, znana również jako rozkład normalny, jest najczęstszym rodzajem rozkładu zmiennej. Jest on akceptowany jako rozkład, który występuje naturalnie w wielu sytuacjach. W krzywej Dzwonkowej najwyższy punkt łuku określa średnią, która jest największą liczbą wystąpień elementu. Prawdopodobieństwo wystąpienia zdarzeń zmniejsza się w kierunku obu stron krzywej. Możesz użyć krzywej dzwonka, aby porównać wyniki, wykluczając skrajności, lub zdefiniować swoje oczekiwania przez możliwość, że wynik będzie leżał w zakresie po lewej lub prawej stronie środka. W tym przewodniku pokażemy, jak utworzyć krzywą dzwonkową w programie Excel z rzeczywistym scenariuszem przypadków użycia jako przykładem.
Pobierz skoroszyt
podstawy
wszystko, czego potrzebujesz, to średnia (średnia) i wartości odchylenia standardowego zestawu danych. Obie te metryki można obliczyć w programie Excel za pomocą poniższych formuł.
biorąc pod uwagę te dwie wartości, normalnie rozmieszczone wartości są zgodne z tymi regułami:
- całkowita powierzchnia pod krzywą jest równa 1 (100%)
- środek krzywej Dzwonkowej jest średnią punktu danych
- (1-σ) około 68,2% powierzchni pod krzywą mieści się w granicach jednego odchylenia standardowego (Średnia ± odchylenie standardowe)
- (2-σ) około 95.5% powierzchni pod krzywą mieści się w dwóch odchyleniach standardowych (Średnia ± 2 * odchylenie standardowe)
- (3-σ) około 99,7% powierzchni pod krzywą mieści się w trzech odchyleniach standardowych (Średnia ± 3 * odchylenie standardowe)
obraz z University of Virginia
Tworzenie krzywej dzwonka w Excelu
weźmy wspólny przykład i powiedzmy, że analizujemy wyniki egzaminu dla klasy STUDENTÓW. Będziemy używać krzywej dzwonowej do pomiaru wyników egzaminu w celu lepszego porównania.
zaczynamy od obliczenia metryki, aby wygenerować normalne dane rozproszone, które wygenerują naszą krzywą. Musimy obliczyć:
- średnia (średnia) wartości.
- odchylenie standardowe wartości.
- 3-Granice odchylenia standardowego dla średniej przed i po.
- wartość interwału dla normalnie rozmieszczonych punktów danych. Wymaga to również określenia punktów interwałowych. Możesz wybrać dowolną liczbę, ale pamiętaj, że więcej interwałów oznacza większą precyzję.
metryki
zacznij od obliczenia średniej i odchylenia standardowego danych. Możesz użyć average i STDEV.Funkcje P do obliczania wartości średniej i odchylenia standardowego odpowiednio.
następnym krokiem jest obliczenie 3 wartości odchylenia standardowego, aby ustawić minimalne i maksymalne wartości dla 99,7% danych.
maksimum = 83.23 – 3 * 5.54 = 99.86
po ustawieniu wartości minimalnych i maksymalnych dla naszej krzywej, musimy wygenerować interwały. Wartości interwałów będą podstawą dla wartości normalnie rozłożonych. Aby obliczyć interwały, 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 minimalną wartością, wprowadź formułę, aby dodać wartość interwału do minimum. Tutaj użyliśmy odniesień do komórek (takich jak J4), które pomagają łatwo zapełnić punkty danych do maksymalnej wartości.
następnym krokiem jest obliczenie normalnie rozłożonych wartości z wygenerowanych punktów danych. Możesz użyć normy Excela.Funkcja dist do generowania tych wartości.
użyj wypełnionych punktów danych jako pierwszego argumentu funkcji. Wartości średniej i odchylenia standardowego są kolejnymi argumentami. Zakończ formułę fałszywą wartością logiczną, aby użyć nie kumulatywnego typu tej funkcji.
Chart
prawie skończyliśmy! Zaznacz punkty danych i wartości rozkładu normalnego, a następnie wstaw Wykres punktowy XY. Użyj wersji Scattered z gładkimi liniami, aby utworzyć krzywą dzwonkową w programie Excel.
wykres może najpierw wydawać się nieco off. Zobaczmy, jak możesz sprawić, że będzie wyglądać lepiej.
aby zmienić tytuł wykresu, kliknij dwukrotnie tytuł i zaktualizuj nazwę.
następnie kliknij dwukrotnie oś X i określ minimalne i maksymalne wartości z panelu Opcje osi, aby wyeliminować białe spacje po obu stronach. To da Wykres 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 jeszcze bardziej ulepszyć swój wykres, dodając wartości odchylenia standardowego.
