Výpočet intervalu spolehlivosti v aplikaci Microsoft Excel

Jednou z metod řešení statistických problémů je výpočet intervalu spolehlivosti. Používá se jako výhodnější alternativa k odhadu bodu s malou velikostí vzorku. Je třeba poznamenat, že proces výpočtu intervalu spolehlivosti je poměrně komplikovaný. Ale nástroje programu Excel nám umožňují trochu zjednodušit. Zjistíme, jak se to v praxi děje.

Čtěte také: Statistické funkce v aplikaci Excel

Postup výpočtu

Tato metoda se používá pro odhad intervalů různých statistických proměnných. Hlavním úkolem tohoto výpočtu je zbavit se nejistot bodového odhadu.

V aplikaci Excel existují dvě hlavní možnosti pro výpočty pomocí této metody: pokud je rozpoznání známo a není-li známo. V prvním případě se pro výpočty používá funkce BELIEVE.NORM a ve druhé - TRUST .

Metoda 1: Funkce TRUST NORM

Operátor BELIEVE.NORM , který se týká statistické skupiny funkcí, se poprvé objevil v aplikaci Excel 2010. V dřívějších verzích tohoto programu se jeho analog je POTVRZEN . Úkolem tohoto operátora je vypočítat interval spolehlivosti s normálním rozdělením průměrné populace.

Jeho syntaxe je následující:

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

"Alfa" je argument, který označuje úroveň významnosti, která se používá k výpočtu úrovně spolehlivosti. Úroveň spolehlivosti se rovná následujícímu výrazu:

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

"Standardní odchylka" je argument, jehož podstata je z názvu jasná. Jedná se o standardní odchylku navrhovaného vzorku.

"Velikost" je argument, který určuje velikost vzorku.

Všechny argumenty tohoto operátora jsou povinné.

Funkce TRUST má přesně stejné argumenty a možnosti jako předchozí. Jeho syntax je následující:

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

Jak vidíte, rozdíly jsou pouze v názvu provozovatele. Zadaná funkce je ponechána pro účely kompatibility v aplikaci Excel 2010 av novějších verzích v zvláštní kategorii "Kompatibilita" . Ve verzích aplikace Excel 2007 a dřívějších, je přítomen v hlavní skupině statistických operátorů.

Interval spolehlivosti se stanoví podle následujícího vzorce:

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

Kde X je průměrná hodnota vzorku, která se nachází uprostřed vybraného rozsahu.

Nyní se podívejme na to, jak vypočítat interval spolehlivosti pro konkrétní příklad. Bylo provedeno dvanáct testů, v důsledku kterých byly v tabulce zaznamenány různé výsledky. To je naše úplnost. Standardní odchylka je 8. Musíme vypočítat interval spolehlivosti s úrovní spolehlivosti 97%.

1. Vyberte buňku, ve které bude zobrazen výsledek zpracování dat. Klepněte na tlačítko "Vložit funkci" .
2. Zobrazí se Průvodce funkcí . Přejděte do kategorie "Statistické" a vyberte název "Trust . " . Poté klikněte na tlačítko "OK" .
3. Otevře okno argumentů. Jeho pole přirozeně odpovídají názvům argumentů.
   Nastavte kurzor na první pole - "Alpha" . Zde bychom měli uvést míru významnosti. Jak si pamatujeme, úroveň důvěry v nás je 97%. Současně jsme říkali, že se vypočítá tímto způsobem:

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

   Takže pro výpočet úrovně významnosti, tedy pro určení hodnoty "Alpha", byste měli použít tento vzorec:

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

   To znamená, že nahradíme hodnotu, dostaneme:

   (1-97)/100

   Jednoduchými výpočty zjistíme, že argument "Alpha" je 0,03 . Zadejte tuto hodnotu do pole.

   Jak je známo, podle předpokladů je směrodatná odchylka 8 . Proto v poli "Standardní odchylka" stačí napsat toto číslo.

   V poli "Velikost" je třeba zadat počet prvků testů. Jak si pamatujeme, je jich tam 12 . Avšak za účelem automatizace vzorec a jeho neupravování při každém spuštění nového testu, nastavte tuto hodnotu nikoliv běžným číslem, ale s pomocí operátoru COUNT . Takže v poli "Velikost" nastavíme kurzor a pak klikneme na trojúhelník, který je umístěn nalevo od čáry vzorce.

   Zobrazí se seznam posledních funkcí. Pokud byl v poslední době aplikován příkaz COUNT , měl by být v tomto seznamu uveden. V tomto případě stačí kliknout na jeho název. V opačném případě, pokud ji nenajdete, přejděte na položku "Další funkce ..." .

4. Zobrazí se již známý Průvodce funkcí . Opět se přesuneme do skupiny "Statistické" . Zde přidělujeme jméno "ACCOUNT" . Klikněte na tlačítko "OK" .
5. Zobrazí se okno s argumenty výše uvedeného příkazu. Tato funkce je určena k výpočtu počtu buněk ve specifikovaném rozsahu, které obsahují číselné hodnoty. Syntaxe je:

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

   Skupina argumentů "Hodnoty" je odkaz na rozsah, ve kterém chcete vypočítat počet buněk vyplněných číselnými daty. Celkově je možné počítat až 255 takových argumentů, ale v našem případě bude zapotřebí pouze jedna.

   Nastavte kurzor v poli "Hodnota1" a podržením levého tlačítka myši vyberte na listu rozsah, který obsahuje naši sbírku. Potom se jeho adresa zobrazí v poli. Klikněte na tlačítko "OK" .

6. Poté aplikace provede výpočet a vyvede výsledek do buňky, ve které se nachází. V našem konkrétním případě je vzorec následující:

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

   Celkový výsledek výpočtů byl 5,011609 .

7. Ale to není všechno. Jak připomínáme, hranice intervalu spolehlivosti se vypočte přidáním a odečtením výsledku výpočtu důvěry od průměrné hodnoty vzorku. Tímto způsobem se vypočítají pravá i levá hranice intervalu spolehlivosti. Průměrná hodnota vzorkování lze vypočítat pomocí operátora AVERAGE .

   Tento operátor je určen k výpočtu aritmetického průměru vybraného rozsahu čísel. Má následující poměrně jednoduchou syntaxi:

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

   Argument "Číslo" může mít buď jednu číselnou hodnotu, nebo odkaz na buňky, nebo dokonce na celé rozsahy, které je obsahují.

   Vyberte buňku, ve které se zobrazí průměrná hodnota, a klikněte na tlačítko "Vložit funkci" .

8. Otevře se Průvodce funkcí . Opět přejděte do kategorie "Statistické" a vyberte ze seznamu název "AVERAGE" . Jako vždy klikněte na tlačítko "OK" .
9. Otevře se okno s argumenty. Nastavte kurzor do pole "Číslo 1" a stisknutím levého tlačítka myši vyberte celý rozsah hodnot. Po zobrazení souřadnic v poli klepněte na tlačítko "OK" .
10. Poté se zobrazí průměr výsledku výpočtu v elementu listu.
11. Vypočítáme správnou hranici intervalu spolehlivosti. Chcete-li to provést, vyberte samostatnou buňku, vložte znak "=" a přidejte obsah prvků listu, ve kterém jsou výsledky výpočtu funkcí AVERAGE a TRUST . Pro provedení výpočtu stiskněte klávesu Enter . V našem případě byl získán následující vzorec:

    =F2+A16

    Výsledek výpočtu: 6,953276

12. Stejným způsobem vypočítáme levou hranici intervalu spolehlivosti, pouze tentokrát od výsledku výpočtu AVERAGE odečteme výsledek výpočtu operátora TRUE . Vzorec je získán pro náš příklad následujícího typu:

    =F2-A16

    Výsledek výpočtu je -3,06994

13. Snažili jsme se podrobně popsat všechny kroky pro výpočet intervalu důvěryhodnosti, takže jsme každou formulaci detailně vymalovali. Ale můžete všechny akce kombinovat do jednoho vzorce. Výpočet správné hranice intervalu spolehlivosti lze vypsat následovně:

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

14. Podobný výpočet levého okraje bude vypadat takto:

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

Metoda 2: Funkce TRUST BUDGET

Navíc je v aplikaci Excel jedna funkce, která se týká výpočtu intervalu spolehlivosti - DOVERIT.STUDENT . To se objevilo teprve od programu Excel 2010. Tento operátor provádí výpočet intervalu důvěry populace pomocí distribuce Student. Je velmi výhodné použít v případě, kdy rozptyl a tedy i směrodatná odchylka nejsou známy. Syntaxe operátora je:

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

Jak vidíte, jména operátorů se v tomto případě nezměnily.

Podívejme se, jak vypočítat hranice intervalu spolehlivosti s neznámou směrodatnou odchylkou založenou na příkladu stejného agregátu, který jsme uvažovali v předchozí metodě. Úroveň důvěry, stejně jako naposledy, budeme mít 97%.

1. Vyberte buňku, ve které bude výpočet proveden. Klepněte na tlačítko "Vložit funkci" .
2. V otevřeném magistrátu přecházíme do kategorie "Statistické" . Vybíráme název "Trust". Klikněte na tlačítko "OK" .
3. Zobrazí se okno s argumenty daného operátora.

   V poli "Alpha" , vzhledem k tomu, že úroveň spolehlivosti je 97%, zadejte číslo 0,03 . Podruhé se na principu výpočtu tohoto parametru nebudeme zabývat.

   Poté nastavte kurzor do pole "Standardní odchylka" . Tentokrát je tento indikátor pro nás neznámý a musí být vypočítán. To se provádí pomocí speciální funkce - STD . Chcete-li zavolat okno tohoto operátora, klikněte na trojúhelník vlevo od řádku vzorce. Pokud v otevřeném seznamu nenajdeme požadované jméno, přejdeme k položce "Další funkce ..." .

4. Spustí se Průvodce funkcí . Přesuneme se do kategorie "Statistické" a označíme v něm název "STANDOTKLON.V" . Poté klikněte na tlačítko "OK" .
5. Otevře okno s argumenty. Úkolem operátora STDEV je stanovení směrodatné odchylky ve vzorku. Jeho syntaxe vypadá takto:

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

   Není těžké odhadnout, že argument "Číslo" je adresa prvku výběru. Pokud je vzorek umístěn do jediného pole, můžete použít jeden argument pro tento rozsah.

   Nastavte kurzor v poli "Číslo 1" a jako vždy držte levé tlačítko myši, vyberte kolekci. Po uložení souřadnic do pole nezapomeňte stisknout tlačítko "OK" , protože výsledek se ukáže jako nesprávný. Nejprve se musíme vrátit do okna argumentu operátoru TRUST.STUDENT , abychom provedli poslední argument. Chcete-li to provést, klepněte na příslušné jméno ve vzorci bar.

6. Okno argumentů již známé funkce se znovu otevře. Nastavte kurzor do pole "Velikost" . Opět klikněte na již známý trojúhelník a přejděte na výběr operátorů. Jak chápete, potřebujeme jméno "ACCOUNT" . Vzhledem k tomu, že jsme tuto funkci použili pro výpočty v předchozí metodě, jsou v tomto seznamu přítomny, stačí kliknout na ni. Pokud ji nenajdete, postupujte podle algoritmu popsaného v první metodě.
7. Jakmile v okně argumentů ÚČET vložte kurzor do pole "Číslo 1" a stisknutím tlačítka myši vyberte sadu. Poté klikněte na tlačítko "OK" .
8. Poté program vypočítá a zobrazí hodnotu intervalu spolehlivosti.
9. K určení hranic musíme opět vypočítat průměrnou hodnotu vzorku. Ale vzhledem k tomu, že algoritmus výpočtu pomocí vzorce AVERAGE je stejný jako v předchozí metodě, a dokonce ani výsledek se nezměnil, nebudeme o tom podrobněji pojednávat druhýkrát.
10. Kombinací výsledků výpočtu AVERAGE a TRUST získáme správnou hranici intervalu spolehlivosti.
11. Po odečtení výsledku výpočtu TRUSTENTu z výsledků výpočtu provozovatele AVERAGE máme levou hranici intervalu spolehlivosti.
12. Pokud je výpočet zapsán s jedním vzorem, potom výpočet správné hranice v našem případě vypadá takto:

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

13. Vzorec pro výpočet levého okraje bude vypadat následovně:

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

Jak můžete vidět, nástroje aplikace Excel usnadňují výpočet intervalu spolehlivosti a jejích hranic. Pro tyto účely jsou pro vzorky, u kterých je rozdíl znám a neznámé, použity oddělené operátory.

• ← Sloučit řádky do aplikace Microsoft Excel
• Převést HTML do aplikace Microsoft Excel →