Izračunavanje intervala pouzdanosti u Excelu

Excel se koristi za obavljanje različitih statističkih zadataka, od kojih je jedan izračunavanje intervala pouzdanosti, koji se koristi kao najprikladnija zamjena za procjenu bodova s ​​malom veličinom uzorka.

Odmah želimo napomenuti da je sama procedura za izračunavanje intervala povjerenja prilično složena, međutim, u Excelu postoji niz alata dizajniranih da olakšaju ovaj zadatak. Hajde da ih pogledamo.

Izračun intervala pouzdanosti

Interval pouzdanosti je potreban da bi se dala procjena intervala nekim statičkim podacima. Glavna svrha ove operacije je otklanjanje nesigurnosti procjene bodova.

Postoje dva načina za obavljanje ovog zadatka u programu Microsoft Excel:

• operator NORMA POVJERENJE – koristi se u slučajevima kada je disperzija poznata;
• operator TRUST.STUDENTkada je varijansa nepoznata.

U nastavku ćemo korak po korak analizirati obje metode u praksi.

Metoda 1: TRUST.NORM izjava

Ova funkcija je prvi put uvedena u arsenal programa u izdanju Excel 2010 (prije ove verzije zamijenjena je operatorom "POUZDANO”). Operater je uključen u kategoriju „statistički“.

Funkcija Formula NORMA POVJERENJE izgleda ovako:

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

Kao što vidimo, funkcija ima tri argumenta:

• "Alpha" je indikator nivoa značajnosti, koji se uzima kao osnova za proračun. Nivo pouzdanosti se izračunava na sljedeći način:
  • 1-"Альфа". Ovaj izraz je primjenjiv ako je vrijednost "Alpha" predstavljen kao koeficijent. Na primjer, 1-0,7 0,3 =, gdje je 0,7=70%/100%.
  • (100-"Альфа")/100. Ovaj izraz će se primijeniti ako uzmemo u obzir razinu povjerenja sa vrijednošću "Alpha" u procentima. Na primjer, (100-70) / 100 = 0,3.
• "Standardna devijacija" – standardna devijacija analiziranog uzorka podataka.
• "Veličina" je veličina uzorka podataka.

Bilješka: Za ovu funkciju, prisustvo sva tri argumenta je preduvjet.

Operater “POUZDANO”, koji je korišten u ranijim verzijama programa, sadrži iste argumente i obavlja iste funkcije.

Funkcija Formula Pouzdano kao što slijedi:

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

Nema razlike u samoj formuli, samo se ime operatora razlikuje. U Excelu 2010 i kasnijim izdanjima, ovaj operator je u kategoriji Kompatibilnost. U starijim verzijama programa, nalazi se u odjeljku statičkih funkcija.

Granica intervala pouzdanosti određena je sljedećom formulom:

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

gdje Х je prosječna vrijednost u navedenom rasponu.

Sada da vidimo kako primijeniti ove formule u praksi. Dakle, imamo tabelu sa raznim podacima iz 10 merenja. U ovom slučaju, standardna devijacija skupa podataka je 8.

Naš zadatak je da dobijemo vrednost intervala poverenja sa nivoom pouzdanosti od 95%.

1. Prije svega, odaberite ćeliju za prikaz rezultata. Zatim kliknemo na dugme “Insert function” (lijevo od trake formule).
2. Otvara se prozor čarobnjaka za funkcije. Klikom na trenutnu kategoriju funkcija proširite listu i kliknite na liniju u njoj “Statistički”.
3. Na predloženoj listi kliknite na operatora “NORMA POVJERENJA”, zatim pritisnite OK.
4. Vidjet ćemo prozor s postavkama argumenata funkcije, popunivši koje pritisnemo dugme OK.
   • na terenu "Alpha" ukazuju na nivo značaja. Naš zadatak pretpostavlja nivo pouzdanosti od 95%. Zamjenom ove vrijednosti u formulu za izračunavanje, koju smo razmotrili gore, dobijamo izraz: (100-95)/100. Zapisujemo ga u polje za argument (ili možete odmah napisati rezultat izračuna jednak 0,05).
   • na terenu “std_off” prema našim uslovima pišemo broj 8.
   • u polju „Veličina“ navedite broj elemenata koji će se ispitati. U našem slučaju uzeto je 10 mjerenja, pa pišemo broj 10.
5. Da biste izbjegli ponovno konfiguriranje funkcije kada se podaci promijene, možete je automatizirati. Za to koristimo funkciju “PROVJERI”. Stavite pokazivač u polje za unos informacija o argumentu "Veličina", zatim kliknite na ikonu trougla na lijevoj strani trake formule i kliknite na stavku “Više funkcija…”.
6. Kao rezultat, otvorit će se još jedan prozor čarobnjaka za funkcije. Odabirom kategorije “Statistički”, kliknite na funkciju “PROVJERI“, onda OK.
7. Na ekranu će se prikazati još jedan prozor sa postavkama argumenata funkcije, koji se koristi za određivanje broja ćelija u datom opsegu koje sadrže numeričke podatke.

   Funkcija Formula CHECK napisano je ovako: =СЧЁТ(Значение1;Значение2;...).

   Broj dostupnih argumenata za ovu funkciju može biti do 255. Ovdje možete napisati ili određene brojeve, ili adrese ćelija, ili opsege ćelija. Koristićemo poslednju opciju. Da biste to učinili, kliknite na područje za unos informacija za prvi argument, zatim, držeći lijevu tipku miša, odaberite sve ćelije jedne od kolona naše tablice (ne računajući zaglavlje), a zatim pritisnite dugme OK.

8. Kao rezultat poduzetih radnji, rezultat proračuna za operatera će biti prikazan u odabranoj ćeliji NORMA POVJERENJE. U našem zadatku ispostavilo se da je njegova vrijednost jednaka 4,9583603.
9. Ali to još nije konačan rezultat u našem zadatku. Zatim morate izračunati prosječnu vrijednost u datom intervalu. Da biste to učinili, trebate koristiti funkciju “SRCE”A koji obavlja zadatak izračunavanja prosjeka za određeni raspon podataka.

   Formula operatora je napisana ovako: =СРЗНАЧ(число1;число2;...).

   Odaberite ćeliju u koju planiramo umetnuti funkciju i pritisnite dugme “Insert function”.

10. U kategoriji “Statistički” izaberite dosadnog operatera “SRCE” i kliknite OK.
11. U funkciji argumenti u vrijednosti argumenta "Broj" navedite raspon, koji uključuje sve ćelije sa vrijednostima svih mjerenja. Zatim kliknemo U REDU.
12. Kao rezultat poduzetih radnji, prosječna vrijednost će se automatski izračunati i prikazati u ćeliji s novoubačenom funkcijom.
13. Sada moramo izračunati granice CI (interval pouzdanosti). Počnimo s izračunavanjem vrijednosti desne granice. Odabiremo ćeliju u kojoj želimo prikazati rezultat i vršimo sabiranje rezultata dobivenih pomoću operatora “SRCE” I "NORME POVJERENJA”. U našem slučaju formula izgleda ovako: A14+A16. Nakon što ga unesete, pritisnite ući.
14. Kao rezultat toga, izračunat će se i rezultat će se odmah prikazati u ćeliji s formulom.
15. Zatim, na sličan način, vršimo proračun kako bismo dobili vrijednost lijevog ruba CI. Samo u ovom slučaju vrijednost rezultata “NORME POVJERENJA” ne morate zbrajati, već oduzimati od rezultata dobivenog pomoću operatora “SRCE”. U našem slučaju formula izgleda ovako: =A16-A14.
16. Nakon što pritisnemo Enter, dobićemo rezultat u datoj ćeliji sa formulom.

Bilješka: U gornjim paragrafima pokušali smo da što detaljnije opišemo sve korake i svaku korišćenu funkciju. Međutim, sve propisane formule mogu se napisati zajedno, kao dio jedne velike:

• Za određivanje desne granice CI-a, opća formula će izgledati ovako:

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

• Slično, za lijevu ivicu, samo umjesto plusa, trebate staviti minus:

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

Metoda 2: TRUST.STUDENT operator

Sada, hajde da se upoznamo sa drugim operatorom za određivanje intervala pouzdanosti − TRUST.STUDENT. Ova funkcija je u program uvedena relativno nedavno, počevši od verzije Excel 2010, i ima za cilj određivanje CI odabranog skupa podataka korištenjem Studentove distribucije, sa nepoznatom varijansom.

Funkcija Formula TRUST.STUDENT kao što slijedi:

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

Analizirajmo primjenu ovog operatora na primjeru iste tabele. Samo sada ne znamo standardnu ​​devijaciju prema uslovima problema.

1. Prvo odaberite ćeliju u kojoj planiramo prikazati rezultat. Zatim kliknite na ikonu “Insert function” (lijevo od trake formule).
2. Otvara se već dobro poznati prozor čarobnjaka za funkcije. Odaberite kategoriju “Statistički”, zatim sa predložene liste funkcija kliknite na operator “STUDENT OD POUZDANJA”, zatim - OK.
3. U sljedećem prozoru moramo postaviti argumente funkcije:
   • U "Alpha" kao u prvom metodu, navedite vrijednost 0,05 (ili “100-95)/100”).
   • Pređimo na argument. “std_off”. Budući da nam je prema uslovima problema njegova vrijednost nepoznata, potrebno je napraviti odgovarajuće proračune, u kojima operator “STDEV.B”. Kliknite na dugme za dodavanje funkcije, a zatim na stavku “Više funkcija…”.
   • U sljedećem prozoru čarobnjaka za funkcije odaberite operatora “STDEV.B” u kategoriji “Statistički” i kliknite OK.
   • Ulazimo u prozor postavki argumenata funkcije, čija formula izgleda ovako: =СТАНДОТКЛОН.В(число1;число2;...). Kao prvi argument navodimo raspon koji uključuje sve ćelije u stupcu “Vrijednost” (ne računajući zaglavlje).
   • Sada se morate vratiti u prozor s argumentima funkcije “VERUJ.STUDENT”. Da biste to učinili, kliknite na istoimeni natpis u polju za unos formule.
   • Sada pređimo na posljednji argument “Veličina”. Kao iu prvoj metodi, ovdje možete jednostavno odrediti raspon ćelija ili umetnuti operator “PROVERI”. Biramo posljednju opciju.
   • Kada su svi argumenti popunjeni, kliknite na dugme OK.
4. Odabrana ćelija će prikazati vrijednost intervala pouzdanosti prema parametrima koje smo naveli.
5. Zatim moramo izračunati vrijednosti granica CI. A za ovo morate dobiti prosječnu vrijednost za odabrani raspon. Da bismo to učinili, ponovo primjenjujemo funkciju "SRCE”. Algoritam akcija sličan je onom opisanom u prvoj metodi.
6. Dobivši vrijednost “SRCE”, možete početi računati granice CI. Same formule se ne razlikuju od onih koje se koriste sa “NORME POVJERENJA”:
   • Desna granica CI=PROSJEČNO+POVJERENJE STUDENATA
   • Lijeva granica CI=PROSJEČNO-POVJERENJENJE UČENIKA

zaključak

Excelov arsenal alata je nevjerovatno velik, a uz uobičajene funkcije, program nudi široku lepezu posebnih funkcija koje će rad s podacima učiniti mnogo lakšim. Možda će se gore opisani koraci nekim korisnicima na prvi pogled činiti komplikovanim. Ali nakon detaljnog proučavanja problema i redoslijeda radnji, sve će postati mnogo lakše.