Obliczanie przedziału ufności w programie Microsoft Excel

Jedną z metod rozwiązywania problemów statystycznych jest obliczanie przedziału ufności. Jest on stosowany jako bardziej preferowana alternatywa do estymacji punktowej z niewielkim rozmiarem próbki. Należy zauważyć, że sam proces obliczania przedziału ufności jest dość skomplikowany. Ale narzędzia programu Excel pozwalają nam nieco go uprościć. Zobaczmy, jak to się dzieje w praktyce.

Czytaj także: Funkcje statystyczne w Excelu

Procedura obliczania

Ta metoda służy do estymacji przedziałowej różnych zmiennych statystycznych. Głównym zadaniem tego obliczenia jest pozbycie się niepewności oszacowania punktowego.

W programie Excel istnieją dwie główne opcje wykonywania obliczeń przy użyciu tej metody: kiedy wariancja jest znana, a kiedy nieznana. W pierwszym przypadku do obliczeń wykorzystywana jest funkcja BELIEVE.NORM , aw drugim - ZAUFANIE .

Metoda 1: Funkcja TRUST NORM

Operator BELIEVE.NORM , odnoszący się do grupy statystycznej funkcji, po raz pierwszy pojawił się w programie Excel 2010. We wcześniejszych wersjach tego programu jego analogiem jest WIERZYNA . Zadaniem tego operatora jest obliczenie przedziału ufności z rozkładem normalnym dla średniej populacji.

Jego składnia jest następująca:

=ДОВЕРИТ.НОРМ(альфа;стандартное_откл;размер)

"Alfa" to argument, który wskazuje poziom istotności, który jest używany do obliczenia poziomu ufności. Poziom ufności jest równy następującemu wyrażeniu:

(1-«Альфа»)*100

"Odchylenie standardowe" jest argumentem, którego istota wynika jasno z nazwy. Jest to standardowe odchylenie proponowanej próbki.

"Rozmiar" to argument określający rozmiar próbki.

Wszystkie argumenty tego operatora są obowiązkowe.

Funkcja TRUST ma dokładnie te same argumenty i możliwości, co poprzednia. Jego składnia jest następująca:

=ДОВЕРИТ(альфа;стандартное_откл;размер)

Jak widać, różnice występują tylko w nazwie operatora. Podana funkcja została pozostawiona dla celów kompatybilności w programie Excel 2010 oraz w nowszych wersjach w kategorii specjalnej "Zgodność" . W wersjach Excel 2007 i wcześniejszych jest obecna w głównej grupie operatorów statystycznych.

Przedział ufności określa się za pomocą następującej formuły:

X+(-)ДОВЕРИТ.НОРМ

Gdzie X jest średnią wartością próbki, która znajduje się w środku wybranego zakresu.

Teraz przyjrzyjmy się, jak obliczyć przedział ufności dla konkretnego przykładu. Przeprowadzono 12 testów, w wyniku których w tabeli odnotowano różne wyniki. To jest nasza totalność. Odchylenie standardowe wynosi 8. Musimy obliczyć przedział ufności z poziomem ufności 97%.

1. Wybierz komórkę, w której będzie wyświetlany wynik przetwarzania danych. Klikamy przycisk "Wstaw funkcję" .
2. Pojawi się Kreator funkcji . Przejdź do kategorii "Statystyczne" i wybierz nazwę "Zaufanie" . Następnie kliknij przycisk "OK" .
3. Otwiera okno argumentów. Jego pola w naturalny sposób odpowiadają nazwom argumentów.
   Ustaw kursor na pierwszym polu - "Alfa" . Tutaj powinniśmy wskazać poziom istotności. Jak pamiętamy, poziom zaufania do nas wynosi 97%. Jednocześnie powiedzieliśmy, że jest on obliczany w następujący sposób:

   (1-«Альфа»)*100

   Tak więc, aby obliczyć poziom istotności, czyli określić wartość "Alfa", należy zastosować formułę tego rodzaju:

   (1-уровень доверия)/100

   To znaczy, zastępując wartość, otrzymujemy:

   (1-97)/100

   Przez proste obliczenia dowiadujemy się, że argument "Alfa" wynosi 0,03 . Wprowadź tę wartość w polu.

   Jak wiadomo, z założenia odchylenie standardowe wynosi 8 . Dlatego w polu "Odchylenie standardowe" po prostu wpisz ten numer.

   W polu "Rozmiar" należy wprowadzić liczbę elementów testów. Jak pamiętamy, jest ich 12 . Ale aby zautomatyzować formułę i nie edytować jej za każdym razem, gdy uruchamiany jest nowy test, ustawmy tę wartość nie według zwykłego numeru, ale za pomocą operatora COUNT . Ustawiamy kursor w polu "Rozmiar" , a następnie klikamy trójkąt, który znajduje się po lewej stronie linii formuły.

   Pojawi się lista ostatnio używanych funkcji. Jeśli ostatnio zastosowano instrukcję COUNT , powinna ona znajdować się na tej liście. W takim przypadku wystarczy kliknąć jego nazwę. W przeciwnym przypadku, jeśli go nie znajdziesz, przejdź do pozycji "Inne funkcje ..." .

4. Pojawi się już znany kreator funkcji . Ponownie przechodzimy do grupy "Statystyczne" . Przydzielamy tam nazwę "KONTA" . Kliknij przycisk "OK" .
5. Pojawi się okno argumentów dla powyższej instrukcji. Ta funkcja służy do obliczania liczby komórek w określonym zakresie, które zawierają wartości liczbowe. Składnia jest następująca:

   =СЧЁТ(значение1;значение2;…)

   Grupa argumentów "Wartości" jest odniesieniem do zakresu, w którym ma zostać obliczona liczba komórek wypełnionych danymi liczbowymi. Łącznie można zliczyć do 255 takich argumentów, ale w naszym przypadku będzie potrzebna tylko jedna.

   Ustaw kursor w polu "Wartość 1" i przytrzymując lewy przycisk myszy, zaznacz na arkuszu zakres, który zawiera naszą całość. Wtedy jego adres zostanie wyświetlony w polu. Kliknij przycisk "OK" .

6. Następnie aplikacja wykona obliczenia i wyświetli wynik w komórce, w której się znajduje. W naszym konkretnym przypadku formuła jest następująca:

   =ДОВЕРИТ.НОРМ(0,03;8;СЧЁТ(B2:B13))

   Ogólny wynik obliczeń wyniósł 5.011609 .

7. Ale to nie wszystko. Jak pamiętamy, granica przedziału ufności jest obliczana przez dodanie i odjęcie od średniej wartości próbki wyniku obliczenia zaufania . W ten sposób obliczane są odpowiednio prawa i lewa granica przedziału ufności. Średnią wartość próbkowania można obliczyć za pomocą operatora ŚREDNIA .

   Ten operator jest zaprojektowany do obliczania średniej arytmetycznej z wybranego zakresu liczb. Ma następującą, dość prostą składnię:

   =СРЗНАЧ(число1;число2;…)

   Argument "Numer" może być pojedynczą wartością numeryczną lub odniesieniem do komórek lub nawet całymi zakresami, które je zawierają.

   Wybierz komórkę, w której będzie wyświetlana wartość średnia, i kliknij przycisk "Wstaw funkcję" .

8. Kreator funkcji otworzy się. Ponownie przejdź do kategorii "Statystyczne" i wybierz z listy nazwę "ŚREDNIA" . Jak zwykle kliknij przycisk "OK" .
9. Otworzy się okno argumentów. Ustaw kursor w polu "Numer 1" i wybierz cały zakres wartości, naciskając lewy przycisk myszy. Po wyświetleniu współrzędnych w polu kliknij przycisk "OK" .
10. Następnie AVERAGE wyświetla wynik obliczeń w elemencie arkusza.
11. Obliczamy prawą granicę przedziału ufności. Aby to zrobić, wybierz oddzielną komórkę, umieść znak "=" i dodaj zawartość elementów arkusza, w których wyniki obliczeń funkcji ŚREDNIA i ZAUFAJ . Aby wykonać obliczenia, naciśnij klawisz Enter . W naszym przypadku otrzymano następującą formułę:

    =F2+A16

    Wynik obliczenia: 6,953276

12. W ten sam sposób obliczamy lewą granicę przedziału ufności, tyle tylko, że od wyniku obliczenia ŚREDNIA odejmujemy wynik obliczenia operatora PRAWDA . Otrzymujemy formułę dla naszego przykładu następującego typu:

    =F2-A16

    Wynik obliczeń wynosi -3,06994

13. Staraliśmy się szczegółowo opisać wszystkie działania w celu obliczenia przedziału ufności, więc pomalowaliśmy każdą formułę szczegółowo. Ale możesz łączyć wszystkie działania w jedną formułę. Obliczenie prawej granicy przedziału ufności można zapisać w następujący sposób:

    =СРЗНАЧ(B2:B13)+ДОВЕРИТ.НОРМ(0,03;8;СЧЁТ(B2:B13))

14. Podobne obliczenie lewej krawędzi będzie wyglądać tak:

    =СРЗНАЧ(B2:B13)-ДОВЕРИТ.НОРМ(0,03;8;СЧЁТ(B2:B13))

Metoda 2: TRUST BUDGET function

Ponadto w programie Excel istnieje jeszcze jedna funkcja związana z obliczaniem przedziału ufności - DOVERIT.STUDENT . Wydawało się, że zaczyna się od Excela 2010. Ten operator wykonuje obliczenia przedziału ufności populacji za pomocą rozkładu Studenta. Jest to bardzo wygodne w przypadku, gdy wariancja i, odpowiednio, odchylenie standardowe są nieznane. Składnia operatora to:

=ДОВЕРИТ.СТЬЮДЕНТ(альфа;стандартное_откл;размер)

Jak widać, nazwy operatorów pozostały niezmienione w tym przypadku.

Zobaczmy, jak obliczyć granice przedziału ufności z nieznanym odchyleniem standardowym na podstawie tego samego agregatu, który rozważaliśmy w poprzedniej metodzie. Poziom zaufania, a także po raz ostatni, weźmiemy 97%.

1. Wybierz komórkę, w której będą dokonywane obliczenia. Klikamy przycisk "Wstaw funkcję" .
2. W otwartym Master of functions przechodzimy do kategorii "Statistical" . Wybieramy nazwę "Zaufanie". Kliknij przycisk "OK" .
3. Wyświetlane jest okno argumentu określonego operatora.

   W polu "Alpha" , biorąc pod uwagę, że poziom ufności wynosi 97%, zapisz liczbę 0,03 . Po raz drugi na zasadach obliczania tego parametru, nie będziemy się mieszkać.

   Następnie ustaw kursor w polu "Odchylenie standardowe" . Tym razem ten wskaźnik nie jest nam znany i należy go obliczyć. Odbywa się to za pomocą specjalnej funkcji - STD . Aby wywołać okno tego operatora, kliknij trójkąt po lewej stronie linii formuły. Jeśli nie znajdziemy pożądanej nazwy na liście, która się otworzy, przechodzimy do pozycji "Inne funkcje ..." .

4. Uruchomi się Kreator funkcji . Przechodzimy do kategorii "Statystyczne" i zaznaczamy w niej nazwę "STANDOTKLON.V" . Następnie kliknij przycisk "OK" .
5. Otwiera okno argumentów. Zadaniem operatora STDEV jest określenie odchylenia standardowego w próbce. Jego składnia wygląda następująco:

   =СТАНДОТКЛОН.В(число1;число2;…)

   Nietrudno zgadnąć, że argument "Number" jest adresem elementu wyboru. Jeśli próbka jest umieszczona w pojedynczej tablicy, możesz użyć pojedynczego argumentu do odniesienia się do tego zakresu.

   Ustaw kursor w polu "Numer 1" i, jak zawsze, przytrzymując lewy przycisk myszy, wybierz kolekcję. Po ustawieniu współrzędnych, nie spiesz się, aby nacisnąć przycisk "OK" , ponieważ wynik okaże się niepoprawny. Najpierw musimy powrócić do okna argumentu operatora TRUST.STUDENT , aby wprowadzić ostatni argument. Aby to zrobić, kliknij odpowiednią nazwę na pasku formuły.

6. Okno argumentów znanej już funkcji jest ponownie otwierane. Ustaw kursor w polu "Rozmiar" . Ponownie kliknij już znany trójkąt, aby przejść do wyboru operatorów. Jak rozumiesz, potrzebujemy nazwy "KONTO" . Ponieważ użyliśmy tej funkcji do obliczeń w poprzedniej metodzie, jest ona obecna na tej liście, więc po prostu kliknij na nią. Jeśli go nie znajdziesz, postępuj zgodnie z algorytmem opisanym w pierwszej metodzie.
7. Po przejściu do okna argumentów KONTA , ustaw kursor w polu "Numer 1" i wybierz zestaw za pomocą wciśniętego przycisku myszy. Następnie kliknij przycisk "OK" .
8. Następnie program oblicza i wyświetla wartość przedziału ufności.
9. Aby określić granice, musimy ponownie obliczyć średnią wartość próbki. Ale biorąc pod uwagę, że algorytm obliczania za pomocą wzoru ŚREDNIA jest taki sam jak w poprzedniej metodzie, a nawet wynik nie zmienił się, nie będziemy się nad nim szczegółowo zastanawiać po raz drugi.
10. Łącząc wyniki obliczania AVERAGE i TRUST , otrzymujemy właściwą granicę przedziału ufności.
11. Po odjęciu od wyników obliczeń operatora AVERAGE wyniku obliczenia TRUSTENT , mamy lewą granicę przedziału ufności.
12. Jeśli obliczenia są zapisywane z jedną formułą, wówczas obliczenie prawej granicy w naszym przypadku będzie wyglądać tak:

    =СРЗНАЧ(B2:B13)+ДОВЕРИТ.СТЬЮДЕНТ(0,03;СТАНДОТКЛОН.В(B2:B13);СЧЁТ(B2:B13))

13. Odpowiednio, wzór obliczania lewej krawędzi będzie wyglądał następująco:

    =СРЗНАЧ(B2:B13)-ДОВЕРИТ.СТЬЮДЕНТ(0,03;СТАНДОТКЛОН.В(B2:B13);СЧЁТ(B2:B13))

Jak widać, narzędzia programu Excel znacznie ułatwiają obliczanie przedziału ufności i jego granic. Do tych celów stosuje się oddzielne operatory dla próbek, w których wariancja jest znana i nieznana.

• ← Scalaj wiersze w Microsoft Excel
• Konwertuj HTML na Microsoft Excel →