vynikať

Výpočet intervalu dôvery v programe Microsoft Excel

Jednou z metód na riešenie štatistických problémov je výpočet intervalu spoľahlivosti. Používa sa ako vhodnejšia alternatíva k odhadu bodov s malou veľkosťou vzorky. Treba poznamenať, že proces výpočtu samotného intervalu spoľahlivosti je dosť komplikovaný. Ale nástroje programu Excel nám umožňujú trochu zjednodušiť. Dozvieme sa, ako sa to v praxi vykonáva.

Prečítajte si tiež: Štatistické funkcie v programe Excel

Postup výpočtu

Táto metóda sa používa na odhad intervalov rôznych štatistických premenných. Hlavnou úlohou tohto výpočtu je zbaviť sa neistôt bodového odhadu.

V programe Excel existujú dve hlavné možnosti výpočtov pomocou tejto metódy: keď je známa odchýlka a keď nie je známa. V prvom prípade sa na výpočty používa funkcia BELIEVE.NORM av druhom prípade TRUST .

Metóda 1: Funkcia TRUST NORM

Operátor BELIEVE.NORM , ktorý sa týka štatistickej skupiny funkcií, sa prvýkrát objavil v programe Excel 2010. V starších verziách tohto programu je jeho analóg BELIEVED . Úlohou tohto operátora je vypočítať interval spoľahlivosti s normálnou distribúciou pre priemernú populáciu.

Jeho syntax je nasledujúci:

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

"Alpha" je argument, ktorý označuje úroveň významnosti, ktorá sa používa na výpočet úrovne spoľahlivosti. Úroveň spoľahlivosti sa rovná nasledujúcemu výrazu:

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

"Štandardná odchýlka" je argument, ktorého podstata je jasná z názvu. Toto je štandardná odchýlka navrhovanej vzorky.

"Veľkosť" je argument, ktorý určuje veľkosť vzorky.

Všetky argumenty tohto operátora sú povinné.

Funkcia TRUST má presne tie isté argumenty a možnosti ako predchádzajúca. Jeho syntax je toto:

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

Ako vidíte, rozdiely sú len v mene operátora. Zadaná funkcia je ponechaná na účely kompatibility v programe Excel 2010 av novších verziách v špeciálnej kategórii "Kompatibilita" . Vo verziách programu Excel 2007 a starších sa nachádza v hlavnej skupine štatistických operátorov.

Interval spoľahlivosti sa určuje pomocou tohto vzorca:

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

Kde X je priemerná hodnota vzorky, ktorá sa nachádza v strede zvoleného rozsahu.

Teraz sa pozrime na to, ako vypočítať interval spoľahlivosti pre konkrétny príklad. Boli vykonané dvanásť testov, v dôsledku ktorých boli v tabuľke zaznamenané rôzne výsledky. Toto je naša celistvosť. Štandardná odchýlka je 8. Musíme vypočítať interval spoľahlivosti s úrovňou spoľahlivosti 97%.

  1. Vyberte bunku, na ktorej sa zobrazí výsledok spracovania údajov. Klikneme na tlačidlo "Vložiť funkciu" .

    2. Prejdite na sprievodcu funkciami v programe Microsoft Excel

  2. Zobrazí sa Sprievodca funkciami. Prejdite do kategórie "Štatistické" a vyberte meno "Trust" . Potom kliknite na tlačidlo "OK" .

    3. Prejdite do okna argumentov funkcie BIND.NORM v programe Microsoft Excel

  3. Otvorí okno s argumentmi. Jeho polia prirodzene zodpovedajú menám argumentov.
    Nastavte kurzor na prvé pole - "Alpha" . Tu by sme mali uviesť úroveň dôležitosti. Ako si pamätáme, úroveň dôvery v nás je 97%. Zároveň sme povedali, že je vypočítaný takto:

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

    Takže, aby ste vypočítali úroveň významu, teda určiť hodnotu "Alpha", mali by ste použiť tento vzorec:

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

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

    (1-97)/100

    Jednoduchými výpočtami sa dozvieme, že argument "Alpha" je 0,03 . Zadajte túto hodnotu do poľa.

    Ako je známe, podľa predpokladu je štandardná odchýlka 8 . Preto v poli "Štandardná odchýlka" stačí napísať toto číslo.

    V poli "Veľkosť" je potrebné zadať počet prvkov testov. Ako si spomíname, je ich 12 . Ale s cieľom automatizovať vzorec a neupravovať ho pri každom spustení nového testu, nastavte túto hodnotu nie obyčajným číslom, ale pomocou operátora COUNT . Nastavíme kurzor do poľa Veľkosť a potom klikneme na trojuholník, ktorý je umiestnený naľavo od riadku vzorca.

    Zobrazí sa zoznam nedávno použitých funkcií. Ak bol nedávno použitý výkaz COUNT , mal by byť v tomto zozname. V tomto prípade stačí kliknúť na jeho meno. V opačnom prípade, ak ho nenájdete, prejdite na položku "Ďalšie funkcie ..." .

    4. Okno argumentu funkcie TRUST.NORM v programe Microsoft Excel

  4. Zobrazí sa už známy sprievodca funkciami. Opäť prejdeme do skupiny "Štatistické" . Priradíme tam meno "ACCOUNT" . Kliknite na tlačidlo "OK" .

    5. Prejdite do okna argumentov funkcie ACCOUNT v programe Microsoft Excel

  5. Zobrazí sa okno s argumentmi pre vyššie uvedené vyhlásenie. Táto funkcia je určená na výpočet počtu buniek v určenom rozsahu, ktoré obsahujú číselné hodnoty. Syntax je:

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

    Skupina argumentov "Hodnoty" je odkaz na rozsah, v ktorom chcete vypočítať počet buniek vyplnených číselnými údajmi. Celkovo je možné spočítať až 255 takýchto argumentov, avšak v našom prípade bude potrebná iba jedna.

    Nastavte kurzor do poľa "Value1" a podržaním ľavého tlačidla myši vyberte v hárku rozsah, ktorý obsahuje našu zbierku. Potom sa jeho adresa zobrazí v poli. Kliknite na tlačidlo "OK" .

    6. Okno argumentov funkcie COUNT v programe Microsoft Excel

  6. Potom aplikácia vykoná výpočet a vykáže výsledok do bunky, kde sa nachádza. V našom konkrétnom prípade je nasledujúci vzorec:

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

    Celkový výsledok výpočtov bol 5,011609 .

    7. Výsledok výpočtu funkcie BIND.NORM v programe Microsoft Excel

  7. Ale to nie je všetko. Ako sme si spomenuli, hranica intervalu spoľahlivosti sa vypočítava pripočítaním a odpočítaním výsledku výpočtu BIND.NORM od priemernej hodnoty vzorky. Týmto spôsobom sa vypočíta pravá a ľavá hranica intervalu spoľahlivosti. Priemerná hodnota odberu vzoriek sa môže vypočítať pomocou operátora AVERAGE .

    Tento operátor je určený na výpočet aritmetického priemeru vybraného rozsahu čísel. Má nasledujúcu jednoduchú syntax:

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

    Argument "Číslo" môže mať buď jednu číselnú hodnotu alebo odkaz na bunky alebo dokonca celé rozsahy, ktoré ich obsahujú.

    Vyberte bunku, v ktorej sa zobrazí priemerná hodnota, a kliknite na tlačidlo "Vložiť funkciu" .

    8. Prejdite na sprievodcu funkciami v programe Microsoft Excel

  8. Otvorí sa Sprievodca funkciami. Opäť prejdite do kategórie "Štatistické" a zvoľte zo zoznamu názov "AVERAGE" . Ako vždy, kliknite na tlačidlo "OK" .

    9. Prejdite do okna argumentov funkcie AVERAGE v programe Microsoft Excel

  9. Otvorí sa okno s argumentmi. Nastavte kurzor do poľa "Číslo 1" a stlačením ľavého tlačidla myši zvoľte celý rozsah hodnôt. Po zobrazení súradníc v poli kliknite na tlačidlo "OK" .

    10. Okno s argumentom funkcie AVERAGE v programe Microsoft Excel

  10. Potom sa zobrazí priemer výsledku výpočtu v prvku listu.

    11. Výsledok výpočtu funkcie AVERAGE v programe Microsoft Excel

  11. Vypočítame správnu hranicu intervalu spoľahlivosti. Ak to chcete urobiť, vyberte samostatnú bunku, vložte značku "=" a pridajte obsah prvkov listu, v ktorých sú výsledky výpočtu funkcií AVERAGE a TRUST . Na vykonanie výpočtu stlačte kláves Enter . V našom prípade bol získaný nasledujúci vzorec:

    =F2+A16

    Výpočet výsledku: 6,953276

    12. Pravý okraj intervalu dôvery v programe Microsoft Excel

  12. Rovnakým spôsobom vypočítame ľavú hranicu intervalu spoľahlivosti, len od tohto času od výpočtu priemerného výsledku odčítame výsledok výpočtu operátora TRUE . Vzorec sa získa pre náš príklad nasledujúceho typu:

    =F2-A16

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

    13. Ľavý okraj intervalu dôvery v programe Microsoft Excel

  13. Pokúsili sme sa podrobne popísať všetky akcie na výpočet intervalu spoľahlivosti, takže sme každú formulu detailne vymalovali. Ale môžete kombinovať všetky akcie do jedného vzorca. Výpočet správnej hranice intervalu spoľahlivosti sa môže vypisovať takto:

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

    14. Pravý okraj intervalu dôvery pomocou jedného vzorca v programe Microsoft Excel

  14. Podobný výpočet ľavého okraja bude vyzerať takto:

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

Ľavý okraj intervalu dôvery pomocou jedného vzorca v programe Microsoft Excel

Metóda 2: Funkcia TRUST BUDGET

Okrem toho existuje v programe Excel jedna funkcia, ktorá súvisí s výpočtom intervalu spoľahlivosti - DOVERIT.STUDENT . Zdá sa, že sa začína až od programu Excel 2010. Tento operátor vykonáva výpočet intervalu dôvery obyvateľov pomocou distribúcie študentov. Je veľmi výhodné použiť v prípade, keď nie je známa odchýlka a teda štandardná odchýlka. Syntax operátora je:

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

Ako vidíte, mená prevádzkovateľov zostali v tomto prípade nezmenené.

Pozrime sa, ako vypočítať hranice intervalu spoľahlivosti s neznámou štandardnou odchýlkou ​​na základe príkladu toho istého agregátu, ktorý sme považovali za predchádzajúcu metódu. Úroveň dôvery, rovnako ako naposledy, budeme mať 97%.

  1. Vyberte bunku, v ktorej sa vykoná výpočet. Klikneme na tlačidlo "Vložiť funkciu" .

    2. Vložiť funkciu v programe Microsoft Excel

  2. V otvorenom Master of functions prechádzame do kategórie "Statistical" . Vyberieme meno "Trust". Kliknite na tlačidlo "OK" .

    3. Prejdite do okna argumentov funkcie BEFORE.STUDENT v programe Microsoft Excel

  3. Objaví sa okno s argumentom daného operátora.

    V poli "Alpha" , vzhľadom na to, že úroveň spoľahlivosti je 97%, napíšte číslo 0,03 . Podruhé na princípe výpočtu tohto parametra nebudeme.

    Potom nastavte kurzor do poľa "Štandardná odchýlka" . Tentokrát tento ukazovateľ nie je pre nás známy a je potrebné ho vypočítať. To sa vykonáva pomocou špeciálnej funkcie - STD . Ak chcete zavolať okno tohto operátora, kliknite na trojuholník vľavo od riadku vzorca. Ak v zozname, ktorý sa otvorí, nenájdeme požadované meno, prejdeme na položku "Ďalšie funkcie ..." .

    4. Prepnutie na iné funkcie v programe Microsoft Excel

  4. Spustí sa Sprievodca funkciami . Presťahujeme sa do kategórie "Štatistické" a označíme v ňom názov "STANDOTKLON.V" . Potom kliknite na tlačidlo "OK" .

    5. Prejdite do okna argumentov funkcie STDEV v programe Microsoft Excel.

  5. Otvorí okno s argumentmi. Úlohou operátora STDEV je stanoviť štandardnú odchýlku vo vzorke. Jeho syntax vyzerá takto:

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

    Nie je ťažké odhadnúť, že argument "Číslo" je adresa prvku výberu. Ak je vzorka umiestnená do jedného poľa, môžete použiť jeden argument, ktorý odkazuje na tento rozsah.

    Nastavte kurzor do poľa "Číslo 1" a ako vždy držte ľavé tlačidlo myši, vyberte kolekciu. Po umiestnení súradníc do poľa nezostávajte pospomínať stlačením tlačidla "OK" , pretože výsledok bude nesprávny. Najprv sa musíme vrátiť do okna argumentov operátora TRUST.STUDENT, aby sme urobili posledný argument. Ak to chcete urobiť, kliknite na príslušný názov na lište vzorcov.

    6. Okno s argumentom funkcie STDEV v programe Microsoft Excel

  6. Okno argumentov už známej funkcie sa znovu otvorí. Nastavte kurzor do poľa "Veľkosť" . Opäť kliknite na už známy trojuholník a prejdite na výber operátorov. Ako chápete, potrebujeme meno "ACCOUNT" . Vzhľadom k tomu, že sme túto funkciu použili pre výpočty v predchádzajúcej metóde, je v tomto zozname prítomný, takže ju jednoducho kliknite. Ak ju nenájdete, postupujte podľa algoritmu popísaného v prvej metóde.

    7. Okno s argumentom funkcie TRUST-STUDENT v programe Microsoft Excel

  7. Raz v okne argumentov ACCOUNT vložte kurzor do poľa "Číslo 1" a stlačte tlačidlo myši. Potom kliknite na tlačidlo "OK" .

    8. Okno argumentov funkcie COUNT v programe Microsoft Excel

  8. Potom program vypočíta a zobrazí hodnotu intervalu spoľahlivosti.

    9. Výsledok výpočtu funkcie BIND.STITUTE v programe Microsoft Excel

  9. Na stanovenie hraníc je potrebné opäť vypočítať priemernú hodnotu vzorky. Avšak vzhľadom na to, že algoritmus výpočtu s použitím vzorca AVERAGE je rovnaký ako v predchádzajúcej metóde a dokonca aj výsledok sa nezmenil, nebudeme sa na ňu podrobnejšie zaoberať druhýkrát.

    10. Výsledok výpočtu funkcie AVERAGE v programe Microsoft Excel

  10. Kombinácia výsledkov výpočtu AVERAGE a TRUST získa správnu hranicu intervalu spoľahlivosti.

    11. Pravý okraj intervalu dôvery v programe Microsoft Excel

  11. Po odpočítaní výsledkov výpočtu TRUSTENTu od výsledkov výpočtu prevádzkovateľa AVERAGE máme ľavú hranicu intervalu spoľahlivosti.

    12. Ľavý okraj intervalu dôvery v programe Microsoft Excel

  12. Ak je výpočet napísaný pomocou jedného vzorca, potom výpočet správnej hranice v našom prípade bude vyzerať takto:

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

    13. Pravý okraj intervalu dôvery pomocou jedného vzorca v programe Microsoft Excel

  13. Preto vzorec na výpočet ľavého okraja bude vyzerať takto:

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

Ľavý okraj intervalu dôvery pomocou jedného vzorca v programe Microsoft Excel

Ako môžete vidieť, nástroje programu Excel uľahčujú výpočet intervalu spoľahlivosti a jeho hraníc. Na tieto účely sa používajú oddelené operátory pre vzorky, ktorých rozptyl je známy a neznámy.