Uzticības intervāla aprēķināšana programmā Excel

Excel tiek izmantots dažādu statistikas uzdevumu veikšanai, viens no tiem ir ticamības intervāla aprēķins, kas tiek izmantots kā vispiemērotākais punktu novērtējuma aizstājējs ar nelielu izlases lielumu.

Mēs vēlamies uzreiz atzīmēt, ka pati ticamības intervāla aprēķināšanas procedūra ir diezgan sarežģīta, tomēr programmā Excel ir vairāki rīki, kas paredzēti šī uzdevuma atvieglošanai. Apskatīsim tos.

Pārliecības intervāla aprēķins

Uzticamības intervāls ir nepieciešams, lai sniegtu intervāla novērtējumu dažiem statiskiem datiem. Šīs darbības galvenais mērķis ir novērst punktu aplēses neskaidrības.

Ir divas metodes šī uzdevuma veikšanai programmā Microsoft Excel:

• operators PĀRLIECĪBAS NORMA – izmanto gadījumos, kad dispersija ir zināma;
• operators UZTICIES.STUDENTSkad dispersija nav zināma.

Tālāk mēs soli pa solim analizēsim abas metodes praksē.

1. metode: paziņojums TRUST.NORM

Šī funkcija pirmo reizi tika ieviesta programmas arsenālā Excel 2010 izdevumā (pirms šīs versijas to aizstāja operators “UZTICAMS”). Operators ir iekļauts "statistikas" kategorijā.

Funkciju formula PĀRLIECĪBAS NORMA izskatās šādi:

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

Kā redzam, funkcijai ir trīs argumenti:

• "Alfa" ir nozīmīguma līmeņa rādītājs, kas tiek ņemts par pamatu aprēķinam. Uzticamības līmeni aprēķina šādi:
  • 1-"Альфа". Šī izteiksme ir piemērojama, ja vērtība "Alfa" uzrādīts kā koeficients. Piemēram, 1-0,7 0,3 =, kur 0,7=70%/100%.
  • (100-"Альфа")/100. Šī izteiksme tiks lietota, ja ņemsim vērā ticamības līmeni ar vērtību "Alfa" procentos. Piemēram, (100-70) / 100 = 0,3.
• "Standarta novirze" – attiecīgi analizētā datu parauga standartnovirze.
• "Izmērs" ir datu izlases lielums.

Piezīme: Šīs funkcijas priekšnoteikums ir visu trīs argumentu klātbūtne.

operators "UZTICAMS”, kas tika izmantots iepriekšējās programmas versijās, satur tos pašus argumentus un veic tās pašas funkcijas.

Funkciju formula UZTICAMI šādi:

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

Pašā formulā nav atšķirību, atšķiras tikai operatora nosaukums. Programmā Excel 2010 un jaunākos izdevumos šis operators ir saderības kategorijā. Vecākajās programmas versijās tā atrodas statisko funkciju sadaļā.

Uzticamības intervāla robežu nosaka pēc šādas formulas:

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

kur Х ir vidējā vērtība norādītajā diapazonā.

Tagad redzēsim, kā šīs formulas pielietot praksē. Tātad, mums ir tabula ar dažādiem datiem no 10 veiktajiem mērījumiem. Šajā gadījumā datu kopas standarta novirze ir 8.

Mūsu uzdevums ir iegūt ticamības intervāla vērtību ar 95% ticamības līmeni.

1. Vispirms atlasiet šūnu, lai parādītu rezultātu. Pēc tam mēs noklikšķinām uz pogas "Ievietošanas funkcija" (pa kreisi no formulas joslas).
2. Tiek atvērts funkciju vedņa logs. Noklikšķinot uz pašreizējās funkciju kategorijas, izvērsiet sarakstu un noklikšķiniet uz tajā esošās rindas "Statistika".
3. Piedāvātajā sarakstā noklikšķiniet uz operatora “PĀRLIECĪBAS NORMA”, Tad nospiediet OK.
4. Mēs redzēsim logu ar funkcijas argumentu iestatījumiem, kuru aizpildot nospiežam pogu OK.
   • laukā "Alfa" norāda nozīmīguma līmeni. Mūsu uzdevums paredz 95% ticamības līmeni. Aizvietojot šo vērtību aprēķina formulā, kuru mēs aplūkojām iepriekš, mēs iegūstam izteiksmi: (100-95)/100. Mēs to ierakstām argumentu laukā (vai arī varat uzreiz ierakstīt aprēķina rezultātu, kas vienāds ar 0,05).
   • laukā “std_off” saskaņā ar mūsu nosacījumiem mēs rakstām skaitli 8.
   • laukā “Izmērs” norādiet pārbaudāmo elementu skaitu. Mūsu gadījumā tika veikti 10 mērījumi, tāpēc mēs rakstām skaitli 10.
5. Lai, mainoties datiem, nebūtu jāpārkonfigurē funkcija, varat to automatizēt. Šim nolūkam mēs izmantojam funkciju "PĀRBAUDE”. Novietojiet rādītāju argumentu informācijas ievades apgabalā "Izmērs", pēc tam noklikšķiniet uz trīsstūra ikonas formulas joslas kreisajā pusē un noklikšķiniet uz vienuma “Citas funkcijas…”.
6. Rezultātā tiks atvērts cits funkciju vedņa logs. Izvēloties kategoriju "Statistika", noklikšķiniet uz funkcijas "PĀRBAUDE”, tad OK.
7. Ekrānā tiks parādīts cits logs ar funkcijas argumentu iestatījumiem, kas tiek izmantots, lai noteiktu šūnu skaitu dotajā diapazonā, kas satur skaitliskus datus.

   Funkciju formula CHECK tas ir rakstīts šādi: =СЧЁТ(Значение1;Значение2;...).

   Šai funkcijai pieejamo argumentu skaits var būt līdz 255. Šeit var ierakstīt vai nu konkrētus skaitļus, vai šūnu adreses, vai šūnu diapazonus. Mēs izmantosim pēdējo iespēju. Lai to izdarītu, noklikšķiniet uz informācijas ievades apgabala pirmajam argumentam, pēc tam, turot nospiestu peles kreiso pogu, atlasiet visas mūsu tabulas vienas kolonnas šūnas (neskaitot galveni), un pēc tam nospiediet pogu OK.

8. Veikto darbību rezultātā atlasītajā šūnā tiks parādīts operatora aprēķinu rezultāts PĀRLIECĪBAS NORMA. Mūsu uzdevumā tā vērtība izrādījās vienāda ar 4,9583603.
9. Bet tas vēl nav mūsu uzdevuma gala rezultāts. Tālāk jums jāaprēķina vidējā vērtība noteiktā intervālā. Lai to izdarītu, jums jāizmanto funkcija "SIRDS”A, kas veic uzdevumu aprēķināt vidējo vērtību noteiktā datu diapazonā.

   Operatora formula ir uzrakstīta šādi: =СРЗНАЧ(число1;число2;...).

   Atlasiet šūnu, kurā plānojam ievietot funkciju, un nospiediet pogu "Ievietošanas funkcija".

10. Kategorijā "Statistika" izvēlieties garlaicīgu operatoru "SIRDS" un noklikšķiniet OK.
11. Funkcijas argumentos argumentu vērtībā "Numurs" norādiet diapazonu, kurā ietilpst visas šūnas ar visu mērījumu vērtībām. Tad mēs noklikšķinām LABI.
12. Veikto darbību rezultātā vidējā vērtība tiks automātiski aprēķināta un parādīta šūnā ar tikko ievietoto funkciju.
13. Tagad mums jāaprēķina CI (uzticamības intervāla) robežas. Sāksim ar labās apmales vērtības aprēķināšanu. Mēs atlasām šūnu, kurā vēlamies parādīt rezultātu, un veicam iegūto rezultātu pievienošanu, izmantojot operatorus "SIRDS” un "PĀRLIECĪBAS NORMAS”. Mūsu gadījumā formula izskatās šādi: A14+A16. Pēc tā ievadīšanas nospiediet ienākt.
14. Rezultātā aprēķins tiks veikts, un rezultāts nekavējoties tiks parādīts šūnā ar formulu.
15. Pēc tam līdzīgā veidā veicam aprēķinu, lai iegūtu CI kreisās robežas vērtību. Tikai šajā gadījumā rezultāta vērtība "PĀRLIECĪBAS NORMAS” jums nav jāpievieno, bet jāatņem no rezultāta, kas iegūts, izmantojot operatoru "SIRDS”. Mūsu gadījumā formula izskatās šādi: =A16-A14.
16. Pēc Enter nospiešanas mēs iegūsim rezultātu dotajā šūnā ar formulu.

Piezīme: Iepriekš minētajos punktos mēs centāmies pēc iespējas detalizētāk aprakstīt visas darbības un katru izmantoto funkciju. Tomēr visas noteiktās formulas var rakstīt kopā kā daļu no vienas lielas:

• Lai noteiktu CI labo robežu, vispārējā formula izskatīsies šādi:

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

• Tāpat kreisajai apmalei, tikai plusa vietā, jāievieto mīnuss:

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

2. metode: TRUST.STUDENT operators

Tagad iepazīsimies ar otro operatoru ticamības intervāla noteikšanai − UZTICIES.STUDENTS. Šī funkcija programmā tika ieviesta salīdzinoši nesen, sākot no Excel 2010 versijas, un tās mērķis ir noteikt atlasītās datu kopas CI, izmantojot Studenta sadalījumu ar nezināmu dispersiju.

Funkciju formula UZTICIES.STUDENTS šādi:

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

Analizēsim šī operatora pielietojumu tās pašas tabulas piemērā. Tikai tagad mēs nezinām standarta novirzi atbilstoši problēmas apstākļiem.

1. Vispirms atlasiet šūnu, kurā plānojam parādīt rezultātu. Pēc tam noklikšķiniet uz ikonas "Ievietošanas funkcija" (pa kreisi no formulas joslas).
2. Tiks atvērts jau labi zināmais funkciju vedņa logs. Izvēlieties kategoriju "Statistika", pēc tam piedāvātajā funkciju sarakstā noklikšķiniet uz operatora "Uzticams STUDENTS", tad - OK.
3. Nākamajā logā mums jāiestata funkcijas argumenti:
   • Iekš "Alfa" tāpat kā pirmajā metodē, norādiet vērtību 0,05 (vai “100-95)/100”).
   • Pāriesim pie argumentācijas. “std_off”. Tā kā atbilstoši problēmas apstākļiem tās vērtība mums nav zināma, ir jāveic attiecīgi aprēķini, kuros operators “STDEV.B”. Noklikšķiniet uz pogas Pievienot funkciju un pēc tam uz vienuma “Citas funkcijas…”.
   • Nākamajā funkciju vedņa logā atlasiet operatoru “STDEV.B” kategorijā "Statistika" un noklikšķiniet OK.
   • Mēs nokļūstam funkciju argumentu iestatījumu logā, kura formula izskatās šādi: =СТАНДОТКЛОН.В(число1;число2;...). Kā pirmo argumentu mēs norādām diapazonu, kas ietver visas šūnas slejā “Vērtība” (neņemot vērā galveni).
   • Tagad jums ir jāatgriežas logā ar funkcijas argumentiem "UZTICĪBAS.STUDENTS”. Lai to izdarītu, formulas ievades laukā noklikšķiniet uz tāda paša nosaukuma uzraksta.
   • Tagad pāriesim pie pēdējā argumenta “Izmērs”. Tāpat kā pirmajā metodē, šeit jūs varat vienkārši norādīt šūnu diapazonu vai ievietot operatoru “PĀRBAUDĪT”. Mēs izvēlamies pēdējo iespēju.
   • Kad visi argumenti ir aizpildīti, noklikšķiniet uz pogas OK.
4. Atlasītajā šūnā tiks parādīta ticamības intervāla vērtība atbilstoši mūsu norādītajiem parametriem.
5. Tālāk mums jāaprēķina CI robežu vērtības. Un šim nolūkam jums jāiegūst vidējā vērtība atlasītajam diapazonam. Lai to izdarītu, mēs atkal izmantojam funkciju "SIRDS”. Darbību algoritms ir līdzīgs pirmajā metodē aprakstītajam.
6. Saņemot vērtību "SIRDS”, varat sākt aprēķināt CI robežas. Pašas formulas neatšķiras no tām, kuras tiek izmantotas ar "PĀRLIECĪBAS NORMAS”:
   • Labā robeža CI=VIDĒJĀ+STUDENTA PĀRLIECĪBA
   • Left Bound CI=VIDĒJĀ STUDENTA PĀRLIECĪBA

Secinājumi

Programmas Excel rīku arsenāls ir neticami liels, un līdztekus parastajām funkcijām programma piedāvā plašu speciālo funkciju klāstu, kas ievērojami atvieglos darbu ar datiem. Varbūt dažiem lietotājiem no pirmā acu uzmetiena iepriekš aprakstītās darbības var šķist sarežģītas. Bet pēc detalizētas jautājuma izpētes un darbību secības viss kļūs daudz vieglāk.