Izračun intervala zaupanja v Excelu

Excel se uporablja za izvajanje različnih statističnih nalog, eno izmed njih je izračun intervala zaupanja, ki se uporablja kot najprimernejša zamenjava za točkovno oceno pri majhnem vzorcu.

Takoj želimo opozoriti, da je sam postopek za izračun intervala zaupanja precej zapleten, vendar v Excelu obstajajo številna orodja, namenjena olajšanju te naloge. Oglejmo si jih.

Izračun intervala zaupanja

Interval zaupanja je potreben, da se nekaterim statičnim podatkom poda intervalna ocena. Glavni namen te operacije je odpraviti negotovosti točkovne ocene.

Obstajata dva načina za izvedbo te naloge v Microsoft Excelu:

• Operater NORMA ZAUPANJA – uporablja se v primerih, ko je disperzija znana;
• Operater ZAUPANJE.ŠTUDENTko je varianca neznana.

Spodaj bomo korak za korakom analizirali obe metodi v praksi.

1. način: izjava TRUST.NORM

Ta funkcija je bila prvič uvedena v arzenal programa v izdaji Excel 2010 (pred to različico jo je nadomestil operater "ZAUPANJA”). Operater je vključen v kategorijo »statistični«.

Formula funkcije NORMA ZAUPANJA izgleda takole:

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

Kot lahko vidimo, ima funkcija tri argumente:

• "Alpha" je indikator stopnje pomembnosti, ki je osnova za izračun. Stopnja zaupanja se izračuna na naslednji način:
  • 1-"Альфа". Ta izraz je uporaben, če je vrednost "Alpha" predstavljen kot koeficient. na primer 1-0,7 0,3 =, kjer je 0,7=70%/100%.
  • (100-"Альфа")/100. Ta izraz bo uporabljen, če z vrednostjo upoštevamo stopnjo zaupanja "Alpha" v odstotkih. na primer (100-70) / 100 = 0,3.
• "Standardni odklon" – standardni odklon analiziranega vzorca podatkov.
• "Velikost" je velikost vzorca podatkov.

Opomba: Za to funkcijo je predpogoj prisotnost vseh treh argumentov.

Operater “ZAUPANJA”, ki je bil uporabljen v prejšnjih različicah programa, vsebuje enake argumente in izvaja enake funkcije.

Formula funkcije ZAUPAL kot sledi:

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

V sami formuli ni razlik, drugače je le ime operaterja. V Excelu 2010 in novejših izdajah je ta operater v kategoriji Združljivost. V starejših različicah programa se nahaja v razdelku s statičnimi funkcijami.

Meja intervala zaupanja je določena z naslednjo formulo:

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

Kje Х je povprečna vrednost v določenem območju.

Zdaj pa poglejmo, kako te formule uporabiti v praksi. Tako imamo tabelo z različnimi podatki iz 10 opravljenih meritev. V tem primeru je standardna deviacija nabora podatkov 8.

Naša naloga je pridobiti vrednost intervala zaupanja s 95% stopnjo zaupanja.

1. Najprej izberite celico za prikaz rezultata. Nato kliknemo na gumb "Vstavi funkcijo" (levo od vrstice formule).
2. Odpre se okno čarovnika za funkcije. S klikom na trenutno kategorijo funkcij razširite seznam in kliknite na vrstico v njem "Statistični".
3. Na predlaganem seznamu kliknite operaterja “NORMA ZAUPANJA”, Nato pritisnite OK.
4. Videli bomo okno z nastavitvami argumentov funkcije, ki jih zapolnimo s pritiskom na gumb OK.
   • na področju "Alpha" navedite stopnjo pomembnosti. Naša naloga predvideva 95-odstotno stopnjo zaupanja. Če to vrednost zamenjamo v formulo za izračun, ki smo jo obravnavali zgoraj, dobimo izraz: (100-95)/100. Zapišemo ga v polje argumenta (lahko pa takoj zapišemo rezultat izračuna, ki je enak 0,05).
   • na področju “std_off” po naših pogojih pišemo številko 8.
   • v polju "Velikost" določite število elementov, ki jih želite pregledati. V našem primeru je bilo opravljenih 10 meritev, zato zapišemo številko 10.
5. Da se izognete ponovni konfiguraciji funkcije, ko se podatki spremenijo, jo lahko avtomatizirate. Za to uporabimo funkcijo "PREVERI”. Postavite kazalec v vnosno območje podatkov o argumentu "Velikost", nato kliknite ikono trikotnika na levi strani vrstice s formulami in kliknite element "Več funkcij ...".
6. Posledično se odpre drugo okno čarovnika za funkcije. Z izbiro kategorije "Statistični", kliknite na funkcijo "PREVERI", potem OK.
7. Na zaslonu se prikaže drugo okno z nastavitvami argumentov funkcije, ki se uporablja za določanje števila celic v danem obsegu, ki vsebujejo številske podatke.

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

   Število razpoložljivih argumentov za to funkcijo je lahko do 255. Tukaj lahko napišete določene številke, naslove celic ali obsege celic. Uporabili bomo zadnjo možnost. Če želite to narediti, kliknite območje za vnos podatkov za prvi argument, nato pa z levim gumbom miške izberite vse celice enega od stolpcev naše tabele (brez štetja glave) in nato pritisnite gumb OK.

8. Kot rezultat izvedenih dejanj bo rezultat izračunov za operaterja prikazan v izbrani celici NORMA ZAUPANJA. V našem problemu se je izkazalo, da je njegova vrednost enaka 4,9583603.
9. A to še ni končni rezultat naše naloge. Nato morate izračunati povprečno vrednost v določenem intervalu. Če želite to narediti, morate uporabiti funkcijo "SRCE”A, ki opravlja nalogo izračuna povprečja v določenem obsegu podatkov.

   Formula operatorja je zapisana takole: =СРЗНАЧ(число1;число2;...).

   Izberemo celico, kamor nameravamo vstaviti funkcijo in pritisnemo gumb "Vstavi funkcijo".

10. V kategoriji "Statistični" izberite dolgočasnega operaterja “SRCE” in kliknite OK.
11. V argumentih funkcije v vrednosti argumenta "Številka" določite obseg, ki vključuje vse celice z vrednostmi vseh meritev. Nato kliknemo V REDU.
12. Kot rezultat izvedenih dejanj bo povprečna vrednost samodejno izračunana in prikazana v celici z novo vstavljeno funkcijo.
13. Zdaj moramo izračunati meje CI (intervala zaupanja). Začnimo z izračunom vrednosti desne obrobe. Izberemo celico, v kateri želimo prikazati rezultat, in izvedemo seštevanje rezultatov, dobljenih z operatorji “SRCE” in "NORME ZAUPANJA”. V našem primeru je formula videti takole: A14+A16. Ko ga vtipkate, pritisnite Vnesite.
14. Posledično bo izveden izračun in rezultat bo takoj prikazan v celici s formulo.
15. Nato na podoben način izvedemo izračun, da dobimo vrednost leve meje CI. Samo v tem primeru je vrednost rezultata “NORME ZAUPANJA” vam ni treba dodati, ampak odšteti od rezultata, dobljenega z uporabo operatorja "SRCE”. V našem primeru je formula videti takole: =A16-A14.
16. Po pritisku na Enter bomo dobili rezultat v dani celici s formulo.

Opomba: V zgornjih odstavkih smo poskušali čim bolj podrobno opisati vse korake in vsako uporabljeno funkcijo. Vse predpisane formule pa lahko zapišemo skupaj, kot del ene velike:

• Za določitev desne meje CI bo splošna formula videti takole:

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

• Podobno za levo obrobo, samo namesto plusa, morate dati minus:

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

2. način: operator TRUST.STUDENT

Zdaj pa se seznanimo z drugim operatorjem za določanje intervala zaupanja − ZAUPANJE.ŠTUDENT. Ta funkcija je bila v program uvedena relativno nedavno, začenši z različico Excela 2010, in je namenjena določanju CI izbranega nabora podatkov z uporabo Studentove porazdelitve z neznano varianco.

Formula funkcije ZAUPANJE.ŠTUDENT kot sledi:

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

Analizirajmo uporabo tega operaterja na primeru iste tabele. Samo zdaj ne poznamo standardnega odklona glede na pogoje problema.

1. Najprej izberite celico, v kateri nameravamo prikazati rezultat. Nato kliknite na ikono "Vstavi funkcijo" (levo od vrstice formule).
2. Odpre se že dobro poznano okno čarovnika za funkcije. Izberite kategorijo "Statistični", nato na predlaganem seznamu funkcij kliknite operater “ZAUPANJA ŠTUDENT”, nato – OK.
3. V naslednjem oknu moramo nastaviti argumente funkcije:
   • v "Alpha" kot pri prvi metodi določite vrednost 0,05 (ali "100-95)/100").
   • Pojdimo k argumentu. “std_off”. Ker nam glede na pogoje problema njegova vrednost ni znana, moramo narediti ustrezne izračune, v katerih je operator “STDEV.B”. Kliknite na gumb za dodajanje funkcije in nato na element "Več funkcij ...".
   • V naslednjem oknu čarovnika za funkcije izberite operator “STDEV.B” v kategoriji "Statistični" in kliknite OK.
   • Pridemo v okno z nastavitvami argumentov funkcije, katerega formula je videti takole: =СТАНДОТКЛОН.В(число1;число2;...). Kot prvi argument določimo obseg, ki vključuje vse celice v stolpcu »Vrednost« (brez štetja glave).
   • Zdaj se morate vrniti v okno z argumenti funkcije "ZAUPANJE.ŠTUDENT”. Če želite to narediti, kliknite istoimenski napis v polju za vnos formule.
   • Zdaj pa preidimo na zadnji argument »Velikost«. Tako kot pri prvi metodi lahko tudi tukaj preprosto določite obseg celic ali vstavite operator "PREVERI". Izberemo zadnjo možnost.
   • Ko so vsi argumenti izpolnjeni, kliknite gumb OK.
4. V izbrani celici bo prikazana vrednost intervala zaupanja glede na parametre, ki smo jih določili.
5. Nato moramo izračunati vrednosti meja CI. In za to morate dobiti povprečno vrednost za izbrani obseg. Če želite to narediti, znova uporabimo funkcijo "SRCE”. Algoritem dejanj je podoben tistemu, opisanemu v prvi metodi.
6. Ob prejemu vrednosti "SRCE”, lahko začnete izračunavati meje CI. Same formule se ne razlikujejo od tistih, ki se uporabljajo z "NORME ZAUPANJA”:
   • Desna obroba CI=POVPREČNO+ZAUPANJE UČENCEV
   • Left Bound CI=POVPREČNO ZAUPANJE ŠTUDENTOV

zaključek

Excelov arzenal orodij je neverjetno velik in poleg običajnih funkcij program ponuja širok nabor posebnih funkcij, ki bodo zelo olajšale delo s podatki. Morda se nekaterim uporabnikom zgoraj opisani koraki na prvi pogled zdijo zapleteni. Toda po podrobni študiji vprašanja in zaporedju dejanj bo vse postalo veliko lažje.