Pagkalkula ng Confidence Interval sa Excel

Ginagamit ang Excel upang magsagawa ng iba't ibang mga istatistikal na gawain, isa sa mga ito ay ang pagkalkula ng agwat ng kumpiyansa, na ginagamit bilang pinakaangkop na kapalit para sa pagtatantya ng punto na may maliit na sukat ng sample.

Nais naming tandaan kaagad na ang mismong pamamaraan para sa pagkalkula ng agwat ng kumpiyansa ay medyo kumplikado, gayunpaman, sa Excel mayroong isang bilang ng mga tool na idinisenyo upang mapadali ang gawaing ito. Tingnan natin ang mga ito.

Pagkalkula ng Interval ng Kumpiyansa

Kailangan ng confidence interval para makapagbigay ng interval estimate sa ilang static na data. Ang pangunahing layunin ng operasyong ito ay alisin ang mga kawalan ng katiyakan ng pagtatantya ng punto.

Mayroong dalawang mga pamamaraan para sa pagsasagawa ng gawaing ito sa Microsoft Excel:

• Opereytor NORM NG PAGTIWALA – ginagamit sa mga kaso kung saan alam ang dispersion;
• Opereytor MAGTIWALA.ESTUDYANTEkapag hindi alam ang pagkakaiba.

Sa ibaba ay susuriin namin nang sunud-sunod ang parehong mga pamamaraan sa pagsasanay.

Paraan 1: TRUST.NORM na pahayag

Ang function na ito ay unang ipinakilala sa arsenal ng programa sa Excel 2010 edition (bago ang bersyon na ito, pinalitan ito ng operator "PINAGKAKATIWALAAN"). Ang operator ay kasama sa kategoryang "statistical".

Formula ng Function NORM NG PAGTIWALA parang ganyan:

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

Tulad ng nakikita natin, ang function ay may tatlong argumento:

• "Alpha" ay isang tagapagpahiwatig ng antas ng kahalagahan, na kinuha bilang batayan para sa pagkalkula. Ang antas ng kumpiyansa ay kinakalkula tulad ng sumusunod:
  • 1-"Альфа". Ang expression na ito ay naaangkop kung ang halaga "Alpha" ipinakita bilang isang koepisyent. Halimbawa, 1-0,7 0,3 =, kung saan 0,7=70%/100%.
  • (100-"Альфа")/100. Ilalapat ang expression na ito kung isasaalang-alang namin ang antas ng kumpiyansa sa halaga "Alpha" sa porsyento. Halimbawa, (100-70) / 100 = 0,3.
• "Karaniwang lihis" – ayon sa pagkakabanggit, ang karaniwang paglihis ng nasuri na sample ng data.
• "Laki" ay ang laki ng sample ng data.

tandaan: Para sa function na ito, ang pagkakaroon ng lahat ng tatlong argumento ay isang paunang kinakailangan.

Operator "PINAGKAKATIWALAAN", na ginamit sa mga naunang bersyon ng programa, ay naglalaman ng parehong mga argumento at gumaganap ng parehong mga function.

Formula ng Function PINAGKAKATIWALAAN tulad ng sumusunod:

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

Walang mga pagkakaiba sa mismong formula, tanging ang pangalan ng operator ay naiiba. Sa Excel 2010 at mas bagong mga edisyon, ang operator na ito ay nasa kategoryang Compatibility. Sa mas lumang mga bersyon ng programa, ito ay matatagpuan sa seksyon ng mga static na function.

Ang hangganan ng agwat ng kumpiyansa ay tinutukoy ng sumusunod na formula:

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

saan Х ay ang average na halaga sa tinukoy na hanay.

Ngayon tingnan natin kung paano ilapat ang mga formula na ito sa pagsasanay. Kaya, mayroon kaming isang talahanayan na may iba't ibang data mula sa 10 mga sukat na kinuha. Sa kasong ito, ang karaniwang paglihis ng set ng data ay 8.

Ang aming gawain ay kunin ang halaga ng agwat ng kumpiyansa na may 95% na antas ng kumpiyansa.

1. Una sa lahat, pumili ng isang cell upang ipakita ang resulta. Pagkatapos ay nag-click kami sa pindutan "Insert function" (sa kaliwa ng formula bar).
2. Bubukas ang window ng Function Wizard. Sa pamamagitan ng pag-click sa kasalukuyang kategorya ng mga function, palawakin ang listahan at mag-click sa linya sa loob nito "Istatistika".
3. Sa iminungkahing listahan, mag-click sa operator “NORMA NG KUMPIYANSA”, pagkatapos ay pindutin ang OK.
4. Makakakita kami ng isang window na may mga setting ng mga argumento ng pag-andar, na pinupunan kung saan pinindot namin ang pindutan OK.
   • sa bukid "Alpha" ipahiwatig ang antas ng kahalagahan. Ipinapalagay ng aming gawain ang isang 95% na antas ng kumpiyansa. Ang pagpapalit ng halagang ito sa formula ng pagkalkula, na isinasaalang-alang namin sa itaas, nakuha namin ang expression: (100-95)/100. Isinulat namin ito sa patlang ng argumento (o maaari mong agad na isulat ang resulta ng pagkalkula na katumbas ng 0,05).
   • sa bukid "std_off" ayon sa aming mga kondisyon, isinusulat namin ang numero 8.
   • sa field na "Laki", tukuyin ang bilang ng mga elementong susuriin. Sa aming kaso, 10 mga sukat ang kinuha, kaya isinulat namin ang numero 10.
5. Upang maiwasang muling i-configure ang function kapag nagbago ang data, maaari mo itong i-automate. Para dito ginagamit namin ang function na "SURIIN”. Ilagay ang pointer sa lugar ng pag-input ng impormasyon ng argumento "Laki", pagkatapos ay mag-click sa icon na tatsulok sa kaliwang bahagi ng formula bar at mag-click sa item “Higit pang mga tampok…”.
6. Bilang resulta, magbubukas ang isa pang window ng Function Wizard. Sa pamamagitan ng pagpili ng isang kategorya "Istatistika", i-click ang function na “SURIIN", tapos OK.
7. Ang screen ay magpapakita ng isa pang window na may mga setting ng mga argumento ng function, na ginagamit upang matukoy ang bilang ng mga cell sa isang ibinigay na hanay na naglalaman ng numeric data.

   Formula ng Function CHECK ito ay nakasulat na ganito: =СЧЁТ(Значение1;Значение2;...).

   Ang bilang ng mga magagamit na argumento para sa function na ito ay maaaring hanggang sa 255. Dito maaari mong isulat ang alinman sa mga partikular na numero, o mga cell address, o mga hanay ng cell. Gagamitin namin ang huling opsyon. Upang gawin ito, mag-click sa lugar ng pag-input ng impormasyon para sa unang argumento, pagkatapos, pagpindot sa kaliwang pindutan ng mouse, piliin ang lahat ng mga cell ng isa sa mga haligi ng aming talahanayan (hindi binibilang ang header), at pagkatapos ay pindutin ang pindutan OK.

8. Bilang resulta ng mga aksyon na ginawa, ang resulta ng mga kalkulasyon para sa operator ay ipapakita sa napiling cell NORM NG PAGTIWALA. Sa aming problema, ang halaga nito ay naging katumbas ng 4,9583603.
9. Ngunit hindi pa ito ang huling resulta sa aming gawain. Susunod, kailangan mong kalkulahin ang average na halaga sa isang naibigay na agwat. Upang gawin ito, kailangan mong gamitin ang function na "PUSO”A na gumaganap ng gawain ng pagkalkula ng average sa isang tinukoy na hanay ng data.

   Ang formula ng operator ay nakasulat tulad nito: =СРЗНАЧ(число1;число2;...).

   Piliin ang cell kung saan plano naming ipasok ang function at pindutin ang pindutan "Insert function".

10. Sa kategorya "Istatistika" pumili ng nakakainip na operator “PUSO” at i-click ang OK.
11. Sa mga argumento ng function sa halaga ng argumento “Numero” tukuyin ang hanay, na kinabibilangan ng lahat ng mga cell na may mga halaga ng lahat ng mga sukat. Pagkatapos ay nag-click kami SIGE.
12. Bilang resulta ng mga pagkilos na ginawa, ang average na halaga ay awtomatikong kakalkulahin at ipapakita sa cell na may bagong ipinasok na function.
13. Ngayon kailangan nating kalkulahin ang mga hangganan ng CI (confidence interval). Magsimula tayo sa pamamagitan ng pagkalkula ng halaga ng kanang hangganan. Pinipili namin ang cell kung saan nais naming ipakita ang resulta, at isagawa ang pagdaragdag ng mga resulta na nakuha gamit ang mga operator "PUSO” at "NORMS NG tiwala sa sarili”. Sa aming kaso, ang formula ay ganito: A14+A16. Pagkatapos i-type ito, pindutin Magpasok.
14. Bilang resulta, isasagawa ang pagkalkula at agad na ipapakita ang resulta sa cell na may formula.
15. Pagkatapos, sa katulad na paraan, ginagawa namin ang pagkalkula upang makuha ang halaga ng kaliwang hangganan ng CI. Sa kasong ito lamang ang halaga ng resulta "NORMS NG tiwala sa sarili” hindi mo kailangang magdagdag, ngunit ibawas mula sa resulta na nakuha gamit ang operator "PUSO”. Sa aming kaso, ang formula ay ganito: =A16-A14.
16. Pagkatapos pindutin ang Enter, makukuha natin ang resulta sa ibinigay na cell na may formula.

tandaan: Sa mga talata sa itaas, sinubukan naming ilarawan nang detalyado hangga't maaari ang lahat ng mga hakbang at bawat function na ginamit. Gayunpaman, ang lahat ng mga iniresetang formula ay maaaring isulat nang magkasama, bilang bahagi ng isang malaking formula:

• Upang matukoy ang tamang hangganan ng CI, ang pangkalahatang formula ay magiging ganito:

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

• Katulad nito, para sa kaliwang hangganan, sa halip na isang plus, kailangan mong maglagay ng minus:

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

Paraan 2: TIWALA.STUDENT operator

Ngayon, maging pamilyar tayo sa pangalawang operator para sa pagtukoy ng agwat ng kumpiyansa − MAGTIWALA.ESTUDYANTE. Ang function na ito ay ipinakilala sa programa medyo kamakailan lamang, simula sa bersyon ng Excel 2010, at naglalayong tukuyin ang CI ng napiling dataset gamit ang pamamahagi ng Mag-aaral, na may hindi alam na pagkakaiba.

Formula ng Function MAGTIWALA.ESTUDYANTE tulad ng sumusunod:

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

Suriin natin ang aplikasyon ng operator na ito sa halimbawa ng parehong talahanayan. Ngayon lang hindi natin alam ang standard deviation ayon sa kondisyon ng problema.

1. Una, piliin ang cell kung saan plano naming ipakita ang resulta. Pagkatapos ay mag-click sa icon "Insert function" (sa kaliwa ng formula bar).
2. Magbubukas ang kilalang Function Wizard window. Pumili ng isang kategorya "Istatistika", pagkatapos ay mula sa iminungkahing listahan ng mga function, mag-click sa operator "TRUSTED STUDENT", pagkatapos - OK.
3. Sa susunod na window, kailangan nating i-set up ang mga argumento ng function:
   • Sa "Alpha" tulad ng sa unang paraan, tukuyin ang halaga 0,05 (o "100-95)/100").
   • Lumipat tayo sa argumento. "std_off". Dahil ayon sa mga kondisyon ng problema, ang halaga nito ay hindi alam sa amin, kailangan naming gumawa ng naaangkop na mga kalkulasyon, kung saan ang operator "STDEV.B”. Mag-click sa button na magdagdag ng function at pagkatapos ay sa item “Higit pang mga tampok…”.
   • Sa susunod na window ng Function Wizard, piliin ang operator "STDEV.B” nasa kategorya "Istatistika" at i-click ang OK.
   • Pumasok kami sa window ng mga setting ng mga argumento ng function, ang formula na ganito ang hitsura: =СТАНДОТКЛОН.В(число1;число2;...). Bilang unang argumento, tumukoy kami ng hanay na kinabibilangan ng lahat ng mga cell sa column na "Halaga" (hindi binibilang ang header).
   • Ngayon ay kailangan mong bumalik sa window na may mga argumento ng function na "MAGTIWALA.ESTUDYANTE”. Upang gawin ito, mag-click sa inskripsyon ng parehong pangalan sa field ng input ng formula.
   • Ngayon ay lumipat tayo sa huling argumento na "Size". Tulad ng sa unang paraan, dito maaari mong tukuyin ang isang hanay ng mga cell, o ipasok ang operator "Suriin". Pinipili namin ang huling pagpipilian.
   • Kapag napunan na ang lahat ng argumento, i-click ang button OK.
4. Ipapakita ng napiling cell ang halaga ng agwat ng kumpiyansa ayon sa mga parameter na aming tinukoy.
5. Susunod, kailangan nating kalkulahin ang mga halaga ng mga hangganan ng CI. At para dito kailangan mong makuha ang average na halaga para sa napiling hanay. Upang gawin ito, muli naming inilalapat ang function na "PUSO”. Ang algorithm ng mga aksyon ay katulad ng inilarawan sa unang paraan.
6. Natanggap ang halaga "PUSO”, maaari mong simulan ang pagkalkula ng mga hangganan ng CI. Ang mga formula mismo ay hindi naiiba sa mga ginamit sa "NORMS NG tiwala sa sarili”:
   • Kanang hangganan CI=AVERAGE+CONFIDENCE NG MAG-AARAL
   • Left Bound CI=AVERAGE-STUDENT CONFIDENCE

Konklusyon

Ang arsenal ng mga tool ng Excel ay hindi kapani-paniwalang malaki, at kasama ng mga karaniwang function, nag-aalok ang program ng malawak na iba't ibang mga espesyal na function na magpapadali sa pagtatrabaho sa data. Marahil ang mga hakbang na inilarawan sa itaas ay maaaring mukhang kumplikado sa ilang mga gumagamit sa unang tingin. Ngunit pagkatapos ng isang detalyadong pag-aaral ng isyu at ang pagkakasunud-sunod ng mga aksyon, ang lahat ay magiging mas madali.