Výpočet intervalu spoľahlivosti v Exceli

Excel sa používa na vykonávanie rôznych štatistických úloh, jednou z nich je výpočet intervalu spoľahlivosti, ktorý sa používa ako najvhodnejšia náhrada bodového odhadu s malou veľkosťou vzorky.

Okamžite by sme chceli poznamenať, že samotný postup výpočtu intervalu spoľahlivosti je dosť komplikovaný, avšak v Exceli existuje množstvo nástrojov určených na uľahčenie tejto úlohy. Poďme sa na ne pozrieť.

Výpočet intervalu spoľahlivosti

Interval spoľahlivosti je potrebný na poskytnutie odhadu intervalu pre niektoré statické údaje. Hlavným účelom tejto operácie je odstrániť neistoty bodového odhadu.

Existujú dva spôsoby vykonania tejto úlohy v programe Microsoft Excel:

• operátor NORMÁLNA DÔVERA – používa sa v prípadoch, keď je známa disperzia;
• operátor DÔVERUJTE.ŠTUDENTkeď je rozptyl neznámy.

Nižšie si postupne obe metódy rozoberieme v praxi.

Metóda 1: Vyhlásenie TRUST.NORM

Táto funkcia bola prvýkrát zavedená do arzenálu programu vo vydaní Excel 2010 (pred touto verziou ju nahradil operátor „DÔVERYHODNÉ“). Operátor je zaradený do kategórie „štatistický“.

Funkčný vzorec NORMÁLNA DÔVERA vyzerá takto:

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

Ako vidíme, funkcia má tri argumenty:

• "alfa" je ukazovateľ hladiny významnosti, ktorý sa berie ako základ pre výpočet. Úroveň spoľahlivosti sa vypočíta takto:
  • 1-"Альфа". Tento výraz je použiteľný, ak je hodnota "alfa" prezentované ako koeficient. Napríklad, 1-0,7 =kde 0,7 = 70 %/100 %.
  • (100-"Альфа")/100. Tento výraz sa použije, ak vezmeme do úvahy úroveň spoľahlivosti s hodnotou "alfa" v percentách. Napríklad, (100-70) / 100 = 0,3.
• "Štandardná odchýlka" – štandardná odchýlka analyzovanej vzorky údajov.
• "veľkosť" je veľkosť vzorky údajov.

Poznámka: Pre túto funkciu je predpokladom prítomnosť všetkých troch argumentov.

Operátor “DÔVERYHODNÉ“, ktorý sa používal v starších verziách programu, obsahuje rovnaké argumenty a vykonáva rovnaké funkcie.

Funkčný vzorec DÔVERYHODNÉ nasledujúcim spôsobom:

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

V samotnom vzorci nie sú žiadne rozdiely, líši sa len názov operátora. V Exceli 2010 a novších vydaniach je tento operátor v kategórii Kompatibilita. V starších verziách programu sa nachádza v sekcii statických funkcií.

Hranica intervalu spoľahlivosti je určená nasledujúcim vzorcom:

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

kde Х je priemerná hodnota v špecifikovanom rozsahu.

Teraz sa pozrime, ako tieto vzorce aplikovať v praxi. Máme teda tabuľku s rôznymi údajmi z 10 vykonaných meraní. V tomto prípade je štandardná odchýlka súboru údajov 8.

Našou úlohou je získať hodnotu intervalu spoľahlivosti s 95% hladinou spoľahlivosti.

1. Najprv vyberte bunku na zobrazenie výsledku. Potom klikneme na tlačidlo "Vložiť funkciu" (naľavo od riadka vzorcov).
2. Otvorí sa okno Sprievodca funkciou. Kliknutím na aktuálnu kategóriu funkcií rozbaľte zoznam a kliknite na riadok v ňom "štatistické".
3. V navrhovanom zozname kliknite na operátora “Norma DÔVERY”, Potom stlačte OK.
4. Zobrazí sa nám okno s nastaveniami argumentov funkcie, ktoré vyplníme stlačením tlačidla OK.
   • v teréne "alfa" uveďte úroveň významnosti. Naša úloha predpokladá 95% úroveň spoľahlivosti. Nahradením tejto hodnoty do výpočtového vzorca, ktorý sme uvažovali vyššie, dostaneme výraz: (100-95)/100. Zapíšeme ho do poľa argument (alebo môžete rovno zapísať výsledok výpočtu rovný 0,05).
   • v teréne "std_off" podľa našich podmienok napíšeme číslo 8.
   • v poli „Veľkosť“ zadajte počet prvkov, ktoré sa majú preskúmať. V našom prípade bolo vykonaných 10 meraní, takže zapíšeme číslo 10.
5. Aby ste nemuseli prekonfigurovať funkciu pri zmene údajov, môžete ju automatizovať. Na tento účel používame funkciu „KONTROLA”. Umiestnite ukazovateľ do vstupnej oblasti informácií o argumente "veľkosť", potom kliknite na ikonu trojuholníka na ľavej strane riadka vzorcov a kliknite na položku “Viac funkcií...”.
6. V dôsledku toho sa otvorí ďalšie okno Sprievodcu funkciami. Výberom kategórie "štatistické", kliknite na funkciu “KONTROLA”, potom OK.
7. Na obrazovke sa zobrazí ďalšie okno s nastavením argumentov funkcie, ktorá slúži na určenie počtu buniek v danom rozsahu, ktoré obsahujú číselné údaje.

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

   Počet dostupných argumentov pre túto funkciu môže byť až 255. Tu môžete písať buď konkrétne čísla, alebo adresy buniek alebo rozsahy buniek. Využijeme poslednú možnosť. Ak to chcete urobiť, kliknite na oblasť zadávania informácií pre prvý argument, potom podržte ľavé tlačidlo myši, vyberte všetky bunky jedného zo stĺpcov našej tabuľky (nepočítajúc hlavičku) a potom stlačte tlačidlo OK.

8. V dôsledku vykonaných akcií sa vo vybranej bunke zobrazí výsledok výpočtov pre operátora NORMÁLNA DÔVERA. V našom probléme sa jeho hodnota rovnala 4,9583603.
9. Ale to ešte nie je konečný výsledok našej úlohy. Ďalej je potrebné vypočítať priemernú hodnotu za daný interval. Ak to chcete urobiť, musíte použiť funkciu „SRDCE"A, ktorá vykonáva úlohu výpočtu priemeru pre špecifikovaný rozsah údajov.

   Vzorec operátora je napísaný takto: =СРЗНАЧ(число1;число2;...).

   Vyberte bunku, do ktorej plánujeme vložiť funkciu a stlačte tlačidlo "Vložiť funkciu".

10. V kategórii "štatistické" vyberte si nudného operátora "SRDCE" a kliknite na tlačidlo OK.
11. Vo funkcii argumenty v hodnote argumentu "číslo" zadajte rozsah, ktorý zahŕňa všetky bunky s hodnotami všetkých meraní. Potom klikneme OK
12. V dôsledku vykonaných akcií sa automaticky vypočíta priemerná hodnota a zobrazí sa v bunke s novo vloženou funkciou.
13. Teraz musíme vypočítať hranice CI (interval spoľahlivosti). Začnime výpočtom hodnoty pravého okraja. Vyberieme bunku, v ktorej chceme zobraziť výsledok, a vykonáme sčítanie výsledkov získaných pomocou operátorov “SRDCE" a "NORMY DÔVERY“. V našom prípade vzorec vyzerá takto: A14+A16. Po napísaní stlačte vstúpiť.
14. V dôsledku toho sa vykoná výpočet a výsledok sa okamžite zobrazí v bunke so vzorcom.
15. Potom podobným spôsobom vykonáme výpočet na získanie hodnoty ľavého okraja CI. Iba v tomto prípade hodnota výsledku „NORMY DÔVERY“ nemusíte pridávať, ale odčítať od výsledku získaného pomocou operátora “SRDCE". V našom prípade vzorec vyzerá takto: =A16-A14.
16. Po stlačení Enter dostaneme výsledok v danej bunke so vzorcom.

Poznámka: V odsekoch vyššie sme sa snažili čo najpodrobnejšie popísať všetky kroky a každú použitú funkciu. Všetky predpísané vzorce však možno napísať spolu, ako súčasť jedného veľkého:

• Na určenie správnej hranice CI bude všeobecný vzorec vyzerať takto:

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

• Podobne pre ľavý okraj, iba namiesto plus, musíte dať mínus:

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

Metóda 2: Operátor TRUST.STUDENT

Teraz sa zoznámime s druhým operátorom na určenie intervalu spoľahlivosti − DÔVERUJTE.ŠTUDENT. Táto funkcia bola do programu zavedená pomerne nedávno, počnúc verziou Excelu 2010, a je zameraná na určenie CI vybraného súboru údajov pomocou študentského rozdelenia s neznámym rozptylom.

Funkčný vzorec DÔVERUJTE.ŠTUDENT nasledujúcim spôsobom:

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

Analyzujme aplikáciu tohto operátora na príklade tej istej tabuľky. Len teraz nepoznáme smerodajnú odchýlku podľa podmienok problému.

1. Najprv vyberieme bunku, kde plánujeme zobraziť výsledok. Potom kliknite na ikonu "Vložiť funkciu" (naľavo od riadka vzorcov).
2. Otvorí sa nám už dobre známe okno Sprievodca funkciou. Vyberte kategóriu "štatistické", potom z navrhovaného zoznamu funkcií kliknite na operátora “DÔVERYHODNÝ ŠTUDENT”, potom - OK.
3. V ďalšom okne musíme nastaviť argumenty funkcie:
   • V "alfa" ako v prvej metóde zadajte hodnotu 0,05 (alebo „100-95)/100“).
   • Prejdime k argumentácii. "std_off". Pretože podľa podmienok problému nám jeho hodnota nie je známa, musíme urobiť príslušné výpočty, v ktorých operátor „STDEV.B”. Kliknite na tlačidlo pridať funkciu a potom na položku “Viac funkcií...”.
   • V ďalšom okne Sprievodcu funkciou vyberte operátora “STDEV.B” v kategórii "štatistické" a kliknite na tlačidlo OK.
   • Dostaneme sa do okna nastavení argumentov funkcie, ktorého vzorec vyzerá takto: =СТАНДОТКЛОН.В(число1;число2;...). Ako prvý argument uvádzame rozsah, ktorý zahŕňa všetky bunky v stĺpci „Hodnota“ (okrem hlavičky).
   • Teraz sa musíte vrátiť do okna s argumentmi funkcie “DÔVERA.ŠTUDENT”. Ak to chcete urobiť, kliknite na nápis s rovnakým názvom vo vstupnom poli vzorca.
   • Teraz prejdime k poslednému argumentu „Veľkosť“. Rovnako ako v prvej metóde, aj tu môžete jednoducho zadať rozsah buniek alebo vložiť operátor “KONTROLA”. Vyberáme poslednú možnosť.
   • Po vyplnení všetkých argumentov kliknite na tlačidlo OK.
4. Vo vybranej bunke sa zobrazí hodnota intervalu spoľahlivosti podľa nami zadaných parametrov.
5. Ďalej musíme vypočítať hodnoty hraníc CI. A na to musíte získať priemernú hodnotu pre vybraný rozsah. Aby sme to dosiahli, znova použijeme funkciu „SRDCE". Algoritmus akcií je podobný tomu, ktorý je opísaný v prvej metóde.
6. Po získaní hodnoty „SRDCE", môžete začať počítať hranice CI. Samotné vzorce sa nelíšia od vzorcov používaných s „NORMY DÔVERY“:
   • Pravé ohraničenie CI=PREMER+SEBAVEDOMIE ŠTUDENTOV
   • Ľavá väzba CI = PRIEMERNÁ DÔVERA ŠTUDENTOV

záver

Arzenál nástrojov Excelu je neuveriteľne veľký a spolu s bežnými funkciami ponúka program širokú škálu špeciálnych funkcií, ktoré výrazne uľahčia prácu s údajmi. Možno sa vyššie popísané kroky môžu niektorým používateľom na prvý pohľad zdať komplikované. Ale po podrobnom preštudovaní problému a postupnosti akcií bude všetko oveľa jednoduchšie.