Podsumowanie
Cele tego artykułu są następujące:
- Aby opisać funkcję ROZKŁAD.NORMALNY.S.ODW w programie Microsoft Office Excel 2003 i nowszych wersjach programu Excel
- Aby zilustrować sposób użycia tej funkcji
- Aby porównać wyniki funkcji dla programu Excel 2003 i nowszych wersji programu Excel z wynikami funkcji używanej we wcześniejszych wersjach programu Excel
Więcej informacji
Funkcja ROZKŁAD.NORMALNY.S.ODW(p) zwraca taką wartość z, że przy prawdopodobieństwie p standardowa normalna zmienna losowa przyjmuje wartość mniejszą lub równą z. Standardowa normalna zmienna losowa ma średnią 0 i odchylenie standardowe 1 (a także wariancję 1, ponieważ wariancja = odchylenie standardowe podniesione do kwadratu).
Składnia
NORMSINV(p)
gdzie p jest wartością liczbową. Ponieważ p odpowiada prawdopodobieństwu, musi być większe od 0 i mniejsze od 1.
Przykład użycia
Funkcje ROZKŁAD.NORMALNY.S.ODW i ROZKŁAD.NORMALNY.S są funkcjami pokrewnymi. Jeśli funkcja ROZKŁAD.NORMALNY.S(z) zwraca p, to funkcja ROZKŁAD.NORMALNY.S.ODW(p) zwraca z.
Utwórz pusty arkusz programu Excel, skopiuj poniższą tabelę, zaznacz komórkę A1 w pustym arkuszu programu Excel, a następnie wklej wpisy, aby tabela wypełniła komórki z zakresu A1:C24 w arkuszu.
| z | ROZKŁAD.NORMALNY.S(z) | ROZKŁAD.NORMALNY.S.ODW(ROZKŁAD.NORMALNY.S(Z)) |
|---|---|---|
| 0 | =ROZKŁAD.NORMALNY.S(A3) | =ROZKŁAD.NORMALNY.S.ODW(B3) |
| 0,2 | =ROZKŁAD.NORMALNY.S(A4) | =ROZKŁAD.NORMALNY.S.ODW(B4) |
| 0,4 | =ROZKŁAD.NORMALNY.S(A5) | =ROZKŁAD.NORMALNY.S.ODW(B5) |
| 0,6 | =ROZKŁAD.NORMALNY.S(A6) | =ROZKŁAD.NORMALNY.S.ODW(B6) |
| 0,8 | =ROZKŁAD.NORMALNY.S(A7) | =ROZKŁAD.NORMALNY.S.ODW(B7) |
| 1 | =ROZKŁAD.NORMALNY.S(A8) | =ROZKŁAD.NORMALNY.S.ODW(B8) |
| 1,5 | =ROZKŁAD.NORMALNY.S(A9) | =ROZKŁAD.NORMALNY.S.ODW(B9) |
| 2 | =ROZKŁAD.NORMALNY.S(A10) | =ROZKŁAD.NORMALNY.S.ODW(B10) |
| 2,5 | =ROZKŁAD.NORMALNY.S(A11) | =ROZKŁAD.NORMALNY.S.ODW(B11) |
| p | ROZKŁAD.NORMALNY.S.ODW(p) | |
| 0,5 | =ROZKŁAD.NORMALNY.S.ODW(A14) | |
| 0,6 | =ROZKŁAD.NORMALNY.S.ODW(A15) | |
| 0,9 | =ROZKŁAD.NORMALNY.S.ODW(A16) | |
| 0.95 | =ROZKŁAD.NORMALNY.S.ODW(A17) | |
| 0.975 | =ROZKŁAD.NORMALNY.S.ODW(A18) | poprawna funkcja ROZKŁAD.NORMALNY.S.ODW(p) |
| 0.001 | =ROZKŁAD.NORMALNY.S.ODW(A19) | -3.09023 |
| 0.0001 | =ROZKŁAD.NORMALNY.S.ODW(A20) | -3.71902 |
| 0.00001 | =ROZKŁAD.NORMALNY.S.ODW(A21) | -4.26489 |
| 0.000001 | =ROZKŁAD.NORMALNY.S.ODW(A22) | -4.75342 |
| 0.0000003 | =ROZKŁAD.NORMALNY.S.ODW(A23) | -4.99122 |
| 0.0000002 | =ROZKŁAD.NORMALNY.S.ODW(A24) | -5.06896 |
Uwaga Po wklejeniu tej tabeli do nowego arkusza programu Excel kliknij przycisk Opcje wklejania , a następnie kliknij pozycję Uwzględnij formatowanie docelowe. Nie usuwając zaznaczenia wklejonego zakresu, zastosuj jedną z następujących procedur, odpowiednią dla używanej wersji programu Excel:
- W programie Microsoft Office Excel 2007 kliknij kartę Narzędzia główne, w grupie Komórki kliknij pozycję Format, a następnie kliknij pozycję Autodopasowanie szerokości kolumn.
- W programie Excel 2003 wskaż polecenie
w menu Format , a następnie kliknij polecenie
Autodopasowanie zaznaczenia.
Kolumny B i C można sformatować w celu zachowania spójnej czytelności (na przykład liczby z pięcioma miejscami dziesiętnymi).
Komórki A1:B11 zawierają "mini-normalną tabelę" podobną do tej, którą można zobaczyć w tekście statystycznym, z tą różnicą, że takie tabele zawierają wiersze dla wielu wartości z między wartościami w zakresie A2:A11 i wyższymi niż wartość 2,5 w komórce A11.
Komórki A13:B24 ilustrują sposób użycia funkcji ROZKŁAD.NORMALNY.S.ODW. Ponieważ w komórce A14 w komórce B3 znajduje się wartość 0,5, odpowiednia wartość z dająca wynik funkcji ROZKŁAD.NORMALNY.S = 0,5 wynosi 0, a funkcja ROZKŁAD.NORMALNY.S.ODW(0,5) zwraca wartość 0. W komórce B15 należy umieścić wartość z, gdzie ROZKŁAD.NORMALNY.S(z) = 0,6. Wpisy w komórkach A4:B5 wskazują, że odpowiednia wartość z musi należeć do przedziału od 0,2 do 0,4. Musi być większa niż 0,2, ponieważ wartość funkcji ROZKŁAD.NORMALNY.S(0,2) jest mniejsza niż 0,6, i musi być mniejsza niż 0,4, ponieważ wartość funkcji ROZKŁAD.NORMALNY.S(0,4) jest większa niż 0,6. Obliczenie funkcji ROZKŁAD.NORMALNY.S.ODW w komórce B15 daje wartość 0,25335, która jest rzeczywiście większa niż 0,2 i mniejsza niż 0,4. Analogicznie wartość funkcji ROZKŁAD.NORMALNY.S.ODW(0,9) w komórce B16 musi być większa niż 1 i mniejsza niż 1,5, jak wynika z wpisów w komórkach A8:B9. Odpowiedź, 1,28155, rzeczywiście mieści się w tym przedziale. Ponadto wartość funkcji ROZKŁAD.NORMALNY.S.ODW(0,95) w komórce B17 musi być większa niż 1,5 i mniejsza niż 2,0, co wynika z wpisów w komórkach A9:B10. Odpowiedź 1,644485 mieści się w tym przedziale. Funkcja ROZKŁAD.NORMALNY.S.ODW(0,975) również musi należeć do przedziału od 1 do 1,5 zgodnie z zakresem A10:B11. Ponieważ 0,975 jest znacznie bliższe 0,977 niż 0,933, należy oczekiwać, że funkcja ROZKŁAD.NORMALNY.S.ODW(0,975) będzie znacznie bliższa 2 niż 1,5; i jest na poziomie 1,965996.
Nawiasem mówiąc, dawni użytkownicy tabel statystycznych służących do testowania hipotez statystycznych i obliczania przedziałów ufności mogli rozpoznać wartości w komórkach A17:B18. Prawdopodobieństwo 0,05 leży w prawym ogonie powyżej 1,644485, ponieważ funkcja ROZKŁAD.NORMALNY(1,644485) = 0,95, a prawdopodobieństwo 0,025 leży w prawym ogonie powyżej 1,965996, ponieważ ROZKŁAD.NORMALNY(1,965996) = 0,975. Te wartości odcięcia są często używane odpowiednio do jednostronnych i dwustronnych testów hipotez, gdy prawdopodobieństwo odrzucenia hipotezy zerowej, jeśli jest prawdziwe, jest ustawione na 0,05.
Wartości z zakresu C3:C11 weryfikują odwrotną zależność między funkcją a jej odwrotnością, w tym przypadku między funkcjami ROZKŁAD.NORMALNY.S i ROZKŁAD.NORMALNY.S.ODW . Powinno być tak, że z = ROZKŁAD.NORMALNY.S.ODW(ROZKŁAD.NORMALNY.S(z)). Po zmianie formatu tych wpisów w celu wyświetlenia większej liczby miejsc dziesiętnych można stwierdzić, że wynik nie jest prawidłowy z powodu niedokładności funkcji ROZKŁAD.NORMALNY.S, ROZKŁAD.NORMALNY.S.ODW lub obu tych funkcji. Błędy pojawiają się jednak tylko po umieszczeniu tylu miejsc dziesiętnych, że jest mało prawdopodobne, aby zainteresowały użytkownika. Wyniki w programie Excel 2003 i nowszych wersjach programu Excel zostaną ulepszone w stosunku do wyników w programie Microsoft Excel 2002. Wyniki w programie Excel 2002 zostaną ulepszone w stosunku do wcześniejszych wersji.
A19:C24 Pokaż wartości funkcji ROZKŁAD.NORMALNY.S.ODW(P) dla bieżącej wersji programu Excel dla coraz mniejszych wartości p. Wpisy w kolumnie C pochodzą z tabeli 5 w Knusel, L. On the accuracy of statistical distributions in Microsoft Excel 97, Computational Statistics and Data Analysis, 26, 375-377, 1998.
Wyniki we wcześniejszych wersjach programu Excel
Dokładność funkcji ROZKŁAD.NORMALNY.S.ODW zależy od dwóch czynników. Ponieważ do obliczeń funkcji ROZKŁAD.NORMALNY.S.ODW stosowane jest systematyczne przeszukiwanie wartości zwracanych przez funkcję ROZKŁAD.NORMALNY.S, dokładność funkcji ROZKŁAD.NORMALNY.S ma krytyczne znaczenie.
Ponadto wyszukiwanie musi być na tyle precyzyjne, aby "naprowadzało się" na odpowiednią odpowiedź. Aby można było użyć jako analogii podręcznikowej tabeli rozkładu normalnego prawdopodobieństwa, wpisy w tabeli muszą być dokładne. Ponadto tabela musi zawierać tyle wpisów, aby można było znaleźć odpowiedni wiersz tabeli dający poprawność prawdopodobieństwa z dokładnością do określonej liczby miejsc dziesiętnych.
Oczywiście korzystając z programu komputerowego, nie musisz budować i przechowywać tak dużej tabeli. Zamiast tego poszczególne wpisy są obliczane na żądanie w miarę postępu przeszukiwania "tabeli". Tabela musi być jednak dokładna, a wyszukiwanie musi być na tyle prowadzone, aby nie zatrzymało się przedwcześnie na odpowiedzi, dla której prawdopodobieństwo (lub wiersz tabeli) jest zbyt dalekie od wartości
p używaną w wywołaniu funkcji ROZKŁAD.NORMALNY.S.ODW(p). W związku z tym funkcja ROZKŁAD.NORMALNY.S.ODW została ulepszona w następujący sposób:
- Ulepszono dokładność funkcji ROZKŁAD.NORMALNY.S.
- Proces wyszukiwania został ulepszony w celu zwiększenia możliwości uściślenia.
W programie Excel 2003 i nowszych wersjach programu Excel ulepszono funkcję ROZKŁAD.NORMALNY.S. W programie Excel 2002 wprowadzono ulepszone uściślenia procesu wyszukiwania. W artykule autorstwa Knusela (patrz przypis 2) omówiono braki numeryczne w funkcji ROZKŁAD.NORMALNY.S.ODW w programie Microsoft Excel 97. Braki te utrzymywały się, jak udokumentował Knusel do czasu, gdy ulepszenia w procesie wyszukiwania w programie Excel 2002 poprawiły wyniki, ale nadal nie były w pełni zgodne z ustaleniami Knusela.
Uwaga 2 Knusel, L. On the accuracy of statistical distributions in Microsoft Excel 97, Computational Statistics and Data Analysis, 26, 375-377, 1998.
Wyniki w programie Excel 2003 i nowszych wersjach programu Excel
W procedurze obliczania funkcji ROZKŁAD.NORMALNY.S.ODW w programie Excel 2003 i nowszych wersjach tego programu są wykorzystywane ulepszenia funkcji ROZKŁAD.NORMALNY.S, która została wprowadzona w programie Excel 2003 i nowszych wersjach tego programu.
Aby uzyskać więcej informacji, kliknij następujący numer artykułu w celu wyświetlenia tego artykułu z bazy wiedzy Baza wiedzy Microsoft Knowledge Base:
827369 Funkcje statystyczne programu Excel: ROZKŁAD.NORMALNY.S
Wyniki powinny być zawsze zgodne z wynikami Knusela co do liczby pokazanych miejsc dziesiętnych.
Wnioski
Zazwyczaj w przypadku skrajnie małych lub bardzo dużych wartości p w funkcji ROZKŁAD.NORMALNY.S.ODW(P) występują niedokładności we wcześniejszych wersjach programu Excel. Wartości w programie Excel 2003 i nowszych wersjach programu Excel są znacznie dokładniejsze.
W artykule dotyczącym funkcji ROZKŁAD.NORMALNY.S wspomniano, że nieścisłości funkcji ROZKŁAD.NORMALNY.S występujące we wcześniejszych wersjach programu Excel prawdopodobnie nie będą miały wpływu na większość użytkowników. Z tego względu nieścisłości w funkcji ROZKŁAD.NORMALNY.S.ODW nie będą miały wpływu na użytkowników programu Excel 2002, ponieważ do programu Excel 2002 dodano uściślenia procesu wyszukiwania. Użytkownicy wcześniejszych wersji programu Excel (wcześniejszych od programu Excel 2002) mają jednak większe obawy co do niedokładności funkcji ROZKŁAD.NORMALNY.S.ODW, ponieważ zarówno funkcja ROZKŁAD.NORMALNY.S, jak i proces wyszukiwania wymagały ulepszeń w tych wcześniejszych wersjach.