Izračunavanje intervala pouzdanosti u Excelu

Excel se koristi za obavljanje različitih statističkih zadataka, a jedan od njih je izračun intervala pouzdanosti, koji se koristi kao najprikladnija zamjena za točkastu procjenu s malim uzorkom.

Želimo odmah napomenuti da je sam postupak za izračunavanje intervala povjerenja prilično kompliciran, međutim, u Excelu postoji niz alata dizajniranih da olakšaju ovaj zadatak. Pogledajmo ih.

Izračun intervala povjerenja

Interval pouzdanosti je potreban da bi se dala intervalna procjena za neke statičke podatke. Glavna svrha ove operacije je ukloniti nesigurnosti točke procjene.

Postoje dvije metode za obavljanje ovog zadatka u programu Microsoft Excel:

• Operator NORMA POVJERENJA – koristi se u slučajevima kada je poznata disperzija;
• Operator POVJERENJE.STUDENTkada je varijanca nepoznata.

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

Metoda 1: izjava TRUST.NORM

Ova je funkcija prvi put uvedena u arsenal programa u izdanju Excela 2010 (prije ove verzije zamijenjena je operatorom “POUZDANO”). Operator je uključen u kategoriju "statistika".

Formula funkcije NORMA POVJERENJA izgleda ovako:

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

Kao što vidimo, funkcija ima tri argumenta:

• "Alfa" je pokazatelj razine značajnosti koji se uzima kao osnova za izračun. Razina pouzdanosti izračunava se na sljedeći način:
  • 1-"Альфа". Ovaj izraz je primjenjiv ako je vrijednost "Alfa" prikazano kao koeficijent. Na primjer, 1-0,7 0,3 =, gdje je 0,7=70%/100%.
  • (100-"Альфа")/100. Ovaj izraz će se primijeniti ako uz vrijednost uzmemo u obzir razinu pouzdanosti "Alfa" u postocima. Na primjer, (100-70) / 100 = 0,3.
• "Standardna devijacija" – odnosno standardna devijacija analiziranog uzorka podataka.
• "Veličina" je veličina uzorka podataka.

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

Operater “POUZDANO”, koji se koristio u ranijim verzijama programa, sadrži iste argumente i obavlja iste funkcije.

Formula funkcije POVJERENO kako slijedi:

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

U samoj formuli nema razlike, samo je naziv operatora drugačiji. U programu Excel 2010 i novijim izdanjima ovaj se operator nalazi u kategoriji kompatibilnosti. 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.

Pogledajmo sada kako primijeniti ove formule u praksi. Dakle, imamo tablicu s različitim podacima iz 10 obavljenih mjerenja. U ovom slučaju, standardna devijacija skupa podataka je 8.

Naš zadatak je dobiti vrijednost intervala pouzdanosti s razinom pouzdanosti od 95%.

1. Prije svega odaberite ćeliju za prikaz rezultata. Zatim kliknemo na gumb "Umetni funkciju" (lijevo od trake formule).
2. Otvara se prozor čarobnjaka za funkcije. Klikom na trenutnu kategoriju funkcija proširite popis i kliknite na redak u njemu "Statistički".
3. Na predloženom popisu kliknite na operatora “NORMA POVJERENJA”, A zatim pritisnite OK.
4. Vidjet ćemo prozor s postavkama argumenata funkcije, ispunjavajući koje pritisnemo gumb OK.
   • u području "Alfa" ukazuju na razinu značaja. Naš zadatak pretpostavlja razinu pouzdanosti od 95%. Zamjenom ove vrijednosti u formulu za izračun, koju smo gore razmotrili, dobivamo izraz: (100-95)/100. Zapisujemo ga u polje argumenta (ili možete odmah napisati rezultat izračuna jednak 0,05).
   • u području “std_off” prema našim uvjetima, pišemo broj 8.
   • u polju "Veličina" odredite broj elemenata koji će se ispitati. U našem slučaju obavljeno je 10 mjerenja, pa pišemo broj 10.
5. Kako biste izbjegli ponovno konfiguriranje funkcije kada se podaci promijene, možete je automatizirati. Za to koristimo funkciju "ČEK". Postavite pokazivač u područje unosa informacija o argumentu "Veličina", zatim kliknite na ikonu trokuta na lijevoj strani trake formule i kliknite na stavku "Više značajki...".
6. Kao rezultat, otvorit će se drugi prozor čarobnjaka za funkcije. Odabirom kategorije "Statistički", kliknite na funkciju "ČEK", Onda ok.
7. Na ekranu će se prikazati drugi prozor s postavkama argumenata funkcije, koji se koristi za određivanje broja ćelija u zadanom rasponu koje sadrže numeričke podatke.

   Formula funkcije PROVJERITI piše ovako: =СЧЁТ(Значение1;Значение2;...).

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

8. Kao rezultat poduzetih radnji, rezultat izračuna za operatora bit će prikazan u odabranoj ćeliji NORMA POVJERENJA. U našem problemu pokazalo se da je njegova vrijednost jednaka 4,9583603.
9. Ali to još nije konačni rezultat našeg zadatka. Zatim morate izračunati prosječnu vrijednost u određenom intervalu. Da biste to učinili, morate koristiti funkciju "SRCE"A koji obavlja zadatak izračunavanja prosjeka u određenom rasponu podataka.

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

   Odaberite ćeliju u koju planiramo umetnuti funkciju i pritisnite gumb "Umetni funkciju".

10. U kategoriji "Statistički" odaberite dosadnog operatera "SRCE" i kliknite OK.
11. U argumentima funkcije u vrijednosti argumenta "Broj" odredite raspon koji uključuje sve ćelije s 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 novoumetnutom funkcijom.
13. Sada moramo izračunati granice CI (interval pouzdanosti). Počnimo s izračunavanjem vrijednosti desne granice. Odaberemo ćeliju u kojoj želimo prikazati rezultat, te izvršimo zbrajanje dobivenih rezultata pomoću operatora “SRCE" i "NORME POVJERENJA”. U našem slučaju formula izgleda ovako: A14+A16. Nakon što ga upišete, pritisnite ući.
14. Kao rezultat toga, izračun će se izvršiti i rezultat će se odmah prikazati u ćeliji s formulom.
15. Zatim na sličan način izvodimo izračun kako bismo dobili vrijednost lijeve granice CI. Samo u ovom slučaju vrijednost rezultata “NORME POVJERENJA” ne trebate zbrajati, već oduzimati rezultat dobiven pomoću operatora “SRCE". U našem slučaju formula izgleda ovako: =A16-A14.
16. Nakon što pritisnemo Enter, dobit ćemo rezultat u zadanoj ćeliji s formulom.

Bilješka: U gornjim odlomcima pokušali smo što detaljnije opisati sve korake i svaku korištenu funkciju. Međutim, sve propisane formule mogu se napisati zajedno, kao dio jedne velike:

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

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

• Slično, za lijevu granicu, samo umjesto plusa, morate staviti minus:

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

Metoda 2: operator TRUST.STUDENT

Sada se upoznajmo s drugim operatorom za određivanje intervala pouzdanosti − POVJERENJE.STUDENT. Ova je funkcija uvedena u program relativno nedavno, počevši od verzije Excela 2010, a usmjerena je na određivanje CI odabranog skupa podataka pomoću Studentove distribucije, s nepoznatom varijancom.

Formula funkcije POVJERENJE.STUDENT kako slijedi:

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

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

1. Prvo odaberite ćeliju u kojoj planiramo prikazati rezultat. Zatim kliknite na ikonu "Umetni funkciju" (lijevo od trake formule).
2. Otvorit će se već dobro poznati prozor Function Wizard. Odaberite kategoriju "Statistički", zatim s predloženog popisa funkcija kliknite na operator “STUDENT OD POVJERANJA”, zatim – OK.
3. U sljedećem prozoru moramo postaviti argumente funkcije:
   • u "Alfa" kao u prvoj metodi, navedite vrijednost 0,05 (ili “100-95)/100”).
   • Prijeđimo na argumentaciju. “std_off”. Budući da nam je prema uvjetima problema njegova vrijednost nepoznata, moramo napraviti odgovarajuće izračune, u kojima operator “STDEV.B”. Kliknite na gumb za dodavanje funkcije, a zatim na stavku "Više značajki...".
   • U sljedećem prozoru čarobnjaka za funkcije odaberite operator "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 “POVJERENJE.STUDENT”. Da biste to učinili, kliknite na istoimeni natpis u polju za unos formule.
   • Sada prijeđimo na zadnji argument “Veličina”. Kao iu prvoj metodi, ovdje možete jednostavno odrediti raspon ćelija ili umetnuti operator "ČEK". Biramo posljednju opciju.
   • Nakon što su svi argumenti ispunjeni, kliknite gumb OK.
4. Odabrana ćelija će prikazati vrijednost intervala pouzdanosti prema parametrima koje smo naveli.
5. Zatim moramo izračunati vrijednosti CI granica. A za ovo morate dobiti prosječnu vrijednost za odabrani raspon. Da bismo to učinili, ponovno primjenjujemo funkciju "SRCE". Algoritam radnji sličan je onom opisanom u prvoj metodi.
6. Primivši vrijednost “SRCE", možete početi izračunavati granice CI. Same formule se ne razlikuju od onih koje se koriste s "NORME POVJERENJA”:
   • Desna granica CI=PROSJEK+POVJERENJE UČENIKA
   • Lijeva granica CI=PROSJEČNO POVJERENJE STUDENATA

Zaključak

Excelov arsenal alata je nevjerojatno velik, a uz uobičajene funkcije, program nudi širok izbor posebnih funkcija koje će rad s podacima učiniti mnogo lakšim. Možda se gore opisani koraci nekim korisnicima na prvi pogled čine kompliciranima. Ali nakon detaljnog proučavanja pitanja i slijeda radnji, sve će postati puno lakše.