Výpočet intervalu spolehlivosti v Excelu

Excel se používá k provádění různých statistických úloh, jednou z nich je výpočet intervalu spolehlivosti, který se používá jako nejvhodnější náhrada bodového odhadu s malou velikostí vzorku.

Chceme hned poznamenat, že samotný postup výpočtu intervalu spolehlivosti je poměrně komplikovaný, nicméně v Excelu existuje řada nástrojů navržených pro usnadnění tohoto úkolu. Pojďme se na ně podívat.

Výpočet intervalu spolehlivosti

Interval spolehlivosti je nutný k tomu, aby byl některým statickým datům poskytnut odhad intervalu. Hlavním účelem této operace je odstranit nejistoty bodového odhadu.

V aplikaci Microsoft Excel lze tento úkol provést dvěma způsoby:

• Operátor SEBEVĚDOMÁ NORMA – používá se v případech, kdy je známa disperze;
• Operátor DŮVĚŘOVAT.STUDENTkdyž je rozptyl neznámý.

Níže si obě metody krok za krokem rozebereme v praxi.

Metoda 1: Příkaz TRUST.NORM

Tato funkce byla poprvé zavedena do arzenálu programu ve verzi Excel 2010 (před touto verzí byla nahrazena operátorem „DŮVĚRYHODNÉ”). Operátor je zařazen do kategorie „statistické“.

Funkční vzorec SEBEVĚDOMÁ NORMA vypadá takto:

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

Jak vidíme, funkce má tři argumenty:

• "Alpha" je ukazatel hladiny významnosti, který je brán jako základ pro výpočet. Úroveň spolehlivosti se vypočítá takto:
  • 1-"Альфа". Tento výraz je použitelný, pokud je hodnota "Alpha" prezentovány jako koeficient. Například, 1-0,7 =kde 0,7 = 70 %/100 %.
  • (100-"Альфа")/100. Tento výraz bude použit, pokud vezmeme v úvahu úroveň spolehlivosti s hodnotou "Alpha" v procentech. Například, (100-70) / 100 = 0,3.
• "Standardní odchylka" – směrodatná odchylka analyzovaného vzorku dat.
• "Velikost" je velikost vzorku dat.

Poznámka: Pro tuto funkci je předpokladem přítomnost všech tří argumentů.

Operátor"DŮVĚRYHODNÉ”, který byl používán v dřívějších verzích programu, obsahuje stejné argumenty a provádí stejné funkce.

Funkční vzorec DŮVĚRYHODNÉ následujícím způsobem:

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

V samotném vzorci nejsou žádné rozdíly, liší se pouze jméno operátora. V Excelu 2010 a novějších verzích je tento operátor v kategorii Kompatibilita. Ve starších verzích programu se nachází v sekci statických funkcí.

Hranice intervalu spolehlivosti je určena následujícím vzorcem:

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

kde Х je průměrná hodnota ve specifikovaném rozsahu.

Nyní se podívejme, jak tyto vzorce aplikovat v praxi. Máme tedy tabulku s různými údaji z 10 provedených měření. V tomto případě je standardní odchylka souboru dat 8.

Naším úkolem je získat hodnotu intervalu spolehlivosti s 95% hladinou spolehlivosti.

1. Nejprve vyberte buňku pro zobrazení výsledku. Poté klikneme na tlačítko "Funkce vložení" (vlevo od řádku vzorců).
2. Otevře se okno Průvodce funkcí. Kliknutím na aktuální kategorii funkcí rozbalte seznam a klikněte na řádek v něm "Statistický".
3. V navrhovaném seznamu klikněte na operátora “NORMÁ DŮVĚRY”, Poté stiskněte tlačítko OK.
4. Zobrazí se nám okno s nastavením argumentů funkce, které vyplníme stisknutím tlačítka OK.
   • v oboru "Alpha" uveďte úroveň významnosti. Náš úkol předpokládá 95% úroveň spolehlivosti. Dosazením této hodnoty do výpočtového vzorce, který jsme uvažovali výše, získáme výraz: (100-95)/100. Zapíšeme jej do pole argument (nebo můžete rovnou napsat výsledek výpočtu rovný 0,05).
   • v oboru "std_off" podle našich podmínek napíšeme číslo 8.
   • v poli „Velikost“ zadejte počet prvků, které mají být zkoumány. V našem případě bylo provedeno 10 měření, takže zapíšeme číslo 10.
5. Abyste nemuseli znovu konfigurovat funkci při změně dat, můžete ji automatizovat. K tomu použijeme funkci „ŠEK". Umístěte ukazatel do vstupní oblasti informací o argumentu "Velikost", poté klikněte na ikonu trojúhelníku na levé straně řádku vzorců a klikněte na položku “Další funkce…”.
6. V důsledku toho se otevře další okno Průvodce funkcí. Výběrem kategorie "Statistický", klikněte na funkci “ŠEK", pak je to v pořádku.
7. Na obrazovce se zobrazí další okno s nastavením argumentů funkce, která slouží k určení počtu buněk v daném rozsahu, které obsahují číselná data.

   Funkční vzorec KONTROLA píše se to takto: =СЧЁТ(Значение1;Значение2;...).

   Počet dostupných argumentů pro tuto funkci může být až 255. Zde můžete psát buď konkrétní čísla, nebo adresy buněk nebo rozsahy buněk. Využijeme poslední možnost. Chcete-li to provést, klikněte na oblast pro zadávání informací pro první argument, poté podržte levé tlačítko myši, vyberte všechny buňky jednoho ze sloupců naší tabulky (bez záhlaví) a poté stiskněte tlačítko OK.

8. V důsledku provedených akcí se ve vybrané buňce zobrazí výsledek výpočtů pro operátora SEBEVĚDOMÁ NORMA. V našem problému se jeho hodnota ukázala jako rovna 4,9583603.
9. Ale to ještě není konečný výsledek našeho úkolu. Dále je třeba vypočítat průměrnou hodnotu za daný interval. Chcete-li to provést, musíte použít funkci „SRDCE"A, které plní úlohu výpočtu průměru ze zadaného rozsahu dat.

   Operátorový vzorec je napsán takto: =СРЗНАЧ(число1;число2;...).

   Vyberte buňku, kam plánujeme funkci vložit a stiskněte tlačítko "Funkce vložení".

10. V kategorii "Statistický" vyberte si nudného operátora "SRDCE" a klepněte na tlačítko OK.
11. Ve funkci argumenty v hodnotě argumentu "Číslo" zadejte rozsah, který zahrnuje všechny buňky s hodnotami všech měření. Pak klikneme DOBŘE.
12. V důsledku provedených akcí se automaticky vypočítá průměrná hodnota a zobrazí se v buňce s nově vloženou funkcí.
13. Nyní musíme vypočítat hranice CI (interval spolehlivosti). Začněme výpočtem hodnoty pravé hranice. Vybereme buňku, kde chceme výsledek zobrazit, a provedeme přidání výsledků získaných pomocí operátorů “SRDCE" a "NORMY DŮVĚRY“. V našem případě vzorec vypadá takto: A14+A16. Po napsání stiskněte vstoupit.
14. Tím se provede výpočet a výsledek se ihned zobrazí v buňce se vzorcem.
15. Poté obdobným způsobem provedeme výpočet pro získání hodnoty levé hranice CI. Pouze v tomto případě hodnota výsledku „NORMY DŮVĚRY“ nemusíte sčítat, ale odečítat od výsledku získaného pomocí operátoru “SRDCE". V našem případě vzorec vypadá takto: =A16-A14.
16. Po stisknutí Enter dostaneme výsledek v dané buňce se vzorcem.

Poznámka: V odstavcích výše jsme se snažili popsat co nejpodrobněji všechny kroky a každou použitou funkci. Všechny předepsané vzorce však mohou být zapsány společně, jako součást jednoho velkého:

• Pro určení pravé hranice CI bude obecný vzorec vypadat takto:

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

• Podobně pro levý okraj, pouze místo plus, musíte dát mínus:

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

Metoda 2: Operátor TRUST.STUDENT

Nyní se seznámíme s druhým operátorem pro určení intervalu spolehlivosti − DŮVĚŘOVAT.STUDENT. Tato funkce byla do programu zavedena relativně nedávno, počínaje verzí Excel 2010, a je zaměřena na určení CI vybrané datové sady pomocí Studentova rozdělení s neznámým rozptylem.

Funkční vzorec DŮVĚŘOVAT.STUDENT následujícím způsobem:

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

Pojďme analyzovat aplikaci tohoto operátoru na příkladu stejné tabulky. Jen nyní neznáme směrodatnou odchylku podle podmínek problému.

1. Nejprve vybereme buňku, kde plánujeme zobrazit výsledek. Poté klikněte na ikonu "Funkce vložení" (vlevo od řádku vzorců).
2. Otevře se již známé okno Průvodce funkcí. Vyberte kategorii "Statistický", poté z navrženého seznamu funkcí klikněte na operátor "DŮVĚRYhodný STUDENT", pak - OK.
3. V dalším okně musíme nastavit argumenty funkce:
   • v "Alpha" stejně jako v první metodě zadejte hodnotu 0,05 (nebo „100-95)/100“).
   • Přejděme k argumentaci. "std_off". Protože podle podmínek problému je nám jeho hodnota neznámá, musíme provést příslušné výpočty, ve kterých operátor „STDEV.B”. Klikněte na tlačítko přidat funkci a poté na položku “Další funkce…”.
   • V dalším okně Průvodce funkcí vyberte operátora “STDEV.B” v kategorii "Statistický" a klepněte na tlačítko OK.
   • Dostaneme se do okna nastavení argumentů funkce, jehož vzorec vypadá takto: =СТАНДОТКЛОН.В(число1;число2;...). Jako první argument zadáme rozsah, který zahrnuje všechny buňky ve sloupci „Hodnota“ (bez záhlaví).
   • Nyní se musíte vrátit do okna s argumenty funkce “DŮVĚRA.STUDENT”. Chcete-li to provést, klikněte na stejnojmenný nápis ve vstupním poli vzorce.
   • Nyní přejdeme k poslednímu argumentu „Velikost“. Stejně jako v první metodě můžete i zde jednoduše zadat rozsah buněk nebo vložit operátor "ŠEK". Volíme poslední možnost.
   • Jakmile jsou všechny argumenty vyplněny, klikněte na tlačítko OK.
4. Vybraná buňka zobrazí hodnotu intervalu spolehlivosti podle námi zadaných parametrů.
5. Dále musíme vypočítat hodnoty hranic CI. A k tomu musíte získat průměrnou hodnotu pro vybraný rozsah. K tomu znovu použijeme funkci „SRDCE". Algoritmus akcí je podobný tomu popsanému v první metodě.
6. Po obdržení hodnoty „SRDCE", můžete začít počítat hranice CI. Samotné vzorce se neliší od vzorců používaných s „NORMY DŮVĚRY“:
   • Pravý okraj CI=PRŮMĚR+SEVĚDOMÍ STUDENTŮ
   • Levá vazba CI=PRŮMĚRNÁ SEBEVĚDOMÍ STUDENTŮ

Proč investovat do čističky vzduchu?

Arzenál nástrojů Excelu je neuvěřitelně velký a spolu s běžnými funkcemi program nabízí širokou škálu speciálních funkcí, které práci s daty výrazně usnadní. Možná se výše popsané kroky mohou některým uživatelům na první pohled zdát složité. Ale po podrobném prostudování problému a sledu akcí bude vše mnohem jednodušší.