Obliczanie przedziału ufności w programie Excel

Program Excel służy do wykonywania różnych zadań statystycznych, z których jednym jest obliczanie przedziału ufności, który jest używany jako najbardziej odpowiedni zamiennik oszacowania punktowego przy małej wielkości próby.

Chcemy od razu zauważyć, że sama procedura obliczania przedziału ufności jest dość skomplikowana, jednak w Excelu istnieje szereg narzędzi zaprojektowanych w celu ułatwienia tego zadania. Przyjrzyjmy się im.

Obliczanie przedziału ufności

Potrzebny jest przedział ufności, aby dać oszacowanie przedziału niektórym danym statycznym. Głównym celem tej operacji jest usunięcie niepewności estymacji punktowej.

Istnieją dwie metody wykonania tego zadania w programie Microsoft Excel:

• Operator NORMA ZAUFANIA – stosowane w przypadkach, gdy dyspersja jest znana;
• Operator ZAUFANIE.STUDENTgdy wariancja jest nieznana.

Poniżej krok po kroku przeanalizujemy obie metody w praktyce.

Metoda 1: Oświadczenie TRUST.NORM

Ta funkcja została po raz pierwszy wprowadzona do arsenału programu w edycji Excel 2010 (wcześniej była zastępowana operatorem „ZAUFANE”). Operator zaliczany jest do kategorii „statystyczne”.

Formuła funkcji NORMA ZAUFANIA na to wygląda:

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

Jak widać, funkcja ma trzy argumenty:

• "Alfa" jest wskaźnikiem poziomu istotności, który stanowi podstawę obliczeń. Poziom ufności oblicza się w następujący sposób:
  • 1-"Альфа". To wyrażenie ma zastosowanie, jeśli wartość "Alfa" przedstawiony jako współczynnik. Na przykład, 1-0,7 0,3 =, gdzie 0,7=70%/100%.
  • (100-"Альфа")/100. To wyrażenie zostanie zastosowane, jeśli weźmiemy pod uwagę poziom ufności z wartością "Alfa" w procentach. Na przykład, (100-70) / 100 = 0,3.
• "Odchylenie standardowe" – odpowiednio odchylenie standardowe analizowanej próbki danych.
• "Rozmiar" to wielkość próbki danych.

Uwaga: W przypadku tej funkcji warunkiem wstępnym jest obecność wszystkich trzech argumentów.

Operator”ZAUFANE”, który był używany we wcześniejszych wersjach programu, zawiera te same argumenty i wykonuje te same funkcje.

Formuła funkcji ZAUFANE w sposób następujący:

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

W samej formule nie ma różnic, tylko nazwa operatora jest inna. W programie Excel 2010 i nowszych wersjach ten operator znajduje się w kategorii Zgodność. W starszych wersjach programu znajduje się w dziale funkcji statycznych.

Granicę przedziału ufności wyznacza następujący wzór:

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

gdzie Х to średnia wartość w określonym zakresie.

Zobaczmy teraz, jak zastosować te formuły w praktyce. Mamy więc tabelę z różnymi danymi z 10 wykonanych pomiarów. W tym przypadku odchylenie standardowe zbioru danych wynosi 8.

Naszym zadaniem jest uzyskanie wartości przedziału ufności przy 95% poziomie ufności.

1. Przede wszystkim wybierz komórkę, aby wyświetlić wynik. Następnie klikamy przycisk „Wstaw funkcję” (na lewo od paska formuły).
2. Otworzy się okno Kreatora funkcji. Klikając na aktualną kategorię funkcji, rozwiń listę i kliknij znajdujący się w niej wiersz "Statystyczny".
3. Na proponowanej liście kliknij operatora „NORMA ZAUFANIA”, A następnie naciśnij przycisk OK.
4. Zobaczymy okno z ustawieniami argumentów funkcji, wypełniając je wciskamy przycisk OK.
   • na polu "Alfa" wskazać poziom istotności. Nasze zadanie zakłada poziom ufności 95%. Podstawiając tę ​​wartość do wzoru obliczeniowego, który rozważaliśmy powyżej, otrzymujemy wyrażenie: (100-95)/100. Piszemy to w polu argumentu (lub możesz od razu wpisać wynik obliczenia równy 0,05).
   • na polu „std_off” zgodnie z naszymi warunkami piszemy liczbę 8.
   • w polu „Rozmiar” podaj liczbę elementów do zbadania. W naszym przypadku wykonano 10 pomiarów, więc wpisujemy liczbę 10.
5. Aby uniknąć konieczności ponownej konfiguracji funkcji po zmianie danych, można ją zautomatyzować. W tym celu używamy funkcji „SPRAWDZAĆ". Umieść wskaźnik w obszarze wejściowym informacji o argumencie "Rozmiar", a następnie kliknij ikonę trójkąta po lewej stronie paska formuły i kliknij element "Więcej funkcji…".
6. W rezultacie otworzy się kolejne okno Kreatora funkcji. Wybierając kategorię "Statystyczny", kliknij funkcję „SPRAWDZAĆ", w takim razie w porządku.
7. Na ekranie pojawi się kolejne okno z ustawieniami argumentów funkcji, które służy do określenia ilości komórek w danym zakresie zawierających dane liczbowe.

   Formuła funkcji SPRAWDZ jest napisane tak: =СЧЁТ(Значение1;Значение2;...).

   Liczba dostępnych argumentów dla tej funkcji może wynosić do 255. Tutaj możesz wpisać określone liczby, adresy komórek lub zakresy komórek. Wykorzystamy ostatnią opcję. W tym celu klikamy w obszar wprowadzania informacji dla pierwszego argumentu, następnie trzymając wciśnięty lewy przycisk myszy zaznaczamy wszystkie komórki jednej z kolumn naszej tabeli (nie licząc nagłówka), a następnie wciskamy przycisk OK.

8. W wyniku podjętych działań wynik obliczeń dla operatora zostanie wyświetlony w wybranej komórce NORMA ZAUFANIA. W naszym problemie jej wartość okazała się równa 4,9583603.
9. Ale to nie jest jeszcze ostateczny rezultat naszego zadania. Następnie musisz obliczyć średnią wartość w danym przedziale. Aby to zrobić, musisz użyć funkcji „SERCE"A, który wykonuje zadanie obliczania średniej z określonego zakresu danych.

   Formuła operatora jest napisana tak: =СРЗНАЧ(число1;число2;...).

   Wybierz komórkę, w której planujemy wstawić funkcję i naciśnij przycisk „Wstaw funkcję”.

10. W kategorii "Statystyczny" wybierz nudnego operatora "SERCE" i kliknij OK.
11. W argumentach funkcji w wartości argumentu "Numer" określić zakres, który obejmuje wszystkie komórki z wartościami wszystkich pomiarów. Następnie klikamy W PORZĄDKU.
12. W wyniku podjętych działań średnia wartość zostanie automatycznie obliczona i wyświetlona w komórce z nowo wstawioną funkcją.
13. Teraz musimy obliczyć granice CI (przedział ufności). Zacznijmy od obliczenia wartości prawej granicy. Wybieramy komórkę, w której chcemy wyświetlić wynik i wykonujemy dodawanie wyników uzyskanych za pomocą operatorów „SERCE" i "NORMY ZAUFANIA”. W naszym przypadku formuła wygląda tak: A14+A16. Po wpisaniu naciśnij Wchodzę.
14. W rezultacie obliczenia zostaną wykonane, a wynik natychmiast wyświetlony w komórce z formułą.
15. Następnie w podobny sposób wykonujemy obliczenia, aby otrzymać wartość lewej granicy CI. Tylko w tym przypadku wartość wyniku „NORMY ZAUFANIA” nie trzeba dodawać, ale odjąć od wyniku uzyskanego za pomocą operatora „SERCE". W naszym przypadku formuła wygląda tak: =A16-A14.
16. Po naciśnięciu Enter otrzymamy wynik w danej komórce z formułą.

Uwaga: W powyższych akapitach staraliśmy się opisać jak najdokładniej wszystkie kroki i każdą używaną funkcję. Jednak wszystkie zalecane formuły można zapisać razem, jako część jednej dużej:

• Aby określić prawą granicę CI, ogólna formuła będzie wyglądać tak:

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

• Podobnie dla lewej krawędzi, tylko zamiast plusa, musisz umieścić minus:

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

Metoda 2: operator TRUST.STUDENT

Poznajmy teraz drugi operator do wyznaczania przedziału ufności − ZAUFANIE.STUDENT. Funkcja ta została wprowadzona do programu stosunkowo niedawno, począwszy od wersji Excel 2010, i ma na celu wyznaczenie CI wybranego zbioru danych z wykorzystaniem rozkładu Studenta o nieznanej wariancji.

Formuła funkcji ZAUFANIE.STUDENT w sposób następujący:

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

Przeanalizujmy zastosowanie tego operatora na przykładzie tej samej tabeli. Dopiero teraz nie znamy odchylenia standardowego zgodnie z warunkami problemu.

1. Najpierw wybierz komórkę, w której planujemy wyświetlić wynik. Następnie kliknij ikonę „Wstaw funkcję” (na lewo od paska formuły).
2. Otworzy się znane już okno Kreatora funkcji. Wybierz kategorię "Statystyczny", następnie z proponowanej listy funkcji kliknij na operator „ZAUFANY STUDENT”, następnie - OK.
3. W kolejnym oknie musimy ustawić argumenty funkcji:
   • W "Alfa" podobnie jak w pierwszej metodzie, podaj wartość 0,05 (lub „100-95)/100”).
   • Przejdźmy do argumentu. „std_off”. Ponieważ zgodnie z warunkami problemu jego wartość jest nam nieznana, musimy wykonać odpowiednie obliczenia, w których operator”ODCH.ST.B”. Kliknij przycisk dodawania funkcji, a następnie element "Więcej funkcji…".
   • W następnym oknie Kreatora funkcji wybierz operator „ODCH.ST.B” w kategorii "Statystyczny" i kliknij OK.
   • Dostajemy się do okna ustawień argumentów funkcji, którego wzór wygląda tak: =СТАНДОТКЛОН.В(число1;число2;...). Jako pierwszy argument podajemy zakres, który obejmuje wszystkie komórki w kolumnie „Wartość” (nie licząc nagłówka).
   • Teraz musisz wrócić do okna z argumentami funkcji „ZAUFANIE.STUDENT”. Aby to zrobić, kliknij napis o tej samej nazwie w polu wprowadzania formuły.
   • Przejdźmy teraz do ostatniego argumentu „Rozmiar”. Podobnie jak w pierwszej metodzie, tutaj możesz po prostu określić zakres komórek lub wstawić operator "SPRAWDZAĆ". Wybieramy ostatnią opcję.
   • Po wypełnieniu wszystkich argumentów kliknij przycisk OK.
4. Wybrana komórka wyświetli wartość przedziału ufności zgodnie z określonymi przez nas parametrami.
5. Następnie musimy obliczyć wartości granic CI. I do tego musisz uzyskać średnią wartość dla wybranego zakresu. Aby to zrobić, ponownie stosujemy funkcję „SERCE". Algorytm działań jest podobny do opisanego w pierwszej metodzie.
6. Otrzymawszy wartość „SERCE", możesz rozpocząć obliczanie granic CI. Same formuły nie różnią się od tych używanych z „NORMY ZAUFANIA”:
   • Ramka prawa CI=ŚREDNIA+ZAUFANIE UCZNIÓW
   • Po lewej CI = ŚREDNIE ZAUFANIE UCZNIÓW

Wnioski

Arsenał narzędzi Excela jest niesamowicie duży, a wraz z typowymi funkcjami program oferuje szeroką gamę funkcji specjalnych, które znacznie ułatwią pracę z danymi. Być może opisane powyżej kroki mogą na pierwszy rzut oka wydawać się skomplikowane dla niektórych użytkowników. Ale po szczegółowym przestudiowaniu problemu i sekwencji działań wszystko stanie się znacznie łatwiejsze.