Kuverengera Chivimbo Interval muExcel

Excel inoshandiswa kuita mabasa akasiyana-siyana enhamba, rimwe rawo kuverengerwa kwenguva yekuvimba, iyo inoshandiswa seyakanyanya kutsiva kutsiva kwenzvimbo yekufungidzira ine saizi diki yemuenzaniso.

Tinoda kucherechedza ipapo ipapo kuti maitiro chaiwo ekuverenga nguva yekuvimbika akanyanya kuoma, zvisinei, muExcel kune akati wandei maturusi akagadzirirwa kufambisa basa iri. Ngatizvitarisei.

Confidence Interval Calculation

Nguva yekuvimba inodiwa kuti upe fungidziro yenguva kune imwe static data. Chinangwa chikuru chekushanda uku kubvisa kusava nechokwadi kwefungidziro yepfungwa.

Pane nzira mbiri dzekuita basa iri muMicrosoft Excel:

• Operator KUVIMBA NORM - inoshandiswa mumamiriro ezvinhu apo kupararira kunozivikanwa;
• Operator TRUST.STUDENTapo musiyano usingazivikanwi.

Pazasi isu ticha nhanho nhanho ongorora nzira mbiri mukuita.

Nzira 1: TRUST.NORM statement

Iri basa rakatanga kuunzwa mune arsenal yechirongwa muExcel 2010 edition (iyi vhezheni isati yatanga, yakatsiviwa nemushandisi "KUVIMBA”) Mushandisi anosanganisirwa muchikamu che "statistical".

Basa Formula KUVIMBA NORM zvinoita sekudaro:

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

Sezvatinoona, basa racho rine nharo nhatu:

• "Alpha" chiratidzo chechiyero chekukosha, chinotorwa sehwaro hwekuverenga. Chiyero chekuvimba chinoverengerwa sezvizvi:
  • 1-"Альфа". Uku kutaura kunoshanda kana kukosha "Alpha" inoratidzwa se coefficient. Semuyenzaniso, 1-0,7 0,3 =, apo 0,7=70%/100%.
  • (100-"Альфа")/100. Uku kutaura kuchashandiswa kana tikafunga danho rekuvimba nehukoshi "Alpha" muzvikamu. Semuyenzaniso, (100-70) / 100 = 0,3.
• "Standard kutsauka" - zvakateerana, kutsauka kwakajairwa kweiyo yakaongororwa data data.
• "Saizi" ndiyo saizi ye data data.

Cherechedza: Pabasa iri, kuvapo kwenharo dzese nhatu chinhu chinodiwa.

Opareta "KUVIMBA”, iyo yakashandiswa muzvinyorwa zvekare zvepurogiramu, ine nharo dzakafanana uye inoita mabasa akafanana.

Basa Formula KUTENDA Sezvinotevera:

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

Iko hakuna misiyano mune fomula pachayo, chete zita remushandisi rakasiyana. MuExcel 2010 uye gare gare editions, mushandisi uyu ari muchikamu cheKuenderana. Mune shanduro dzekare dzechirongwa, inowanikwa muchikamu chebasa rekuita.

Muganho wenguva yekuvimba unotemwa neinotevera fomula:

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

apo Х iavhareji kukosha pane yakatarwa chiyero.

Zvino ngationei kuti tingashandisa sei mafomula aya mukuita. Saka, isu tine tafura ine data rakasiyana kubva pazviyero gumi zvakatorwa. Muchiitiko ichi, kutsauka kwakajairwa kweseti yedata ndeye 10.

Basa redu nderekuwana kukosha kweiyo nguva yekuvimba ne95% yekuvimba level.

1. Chekutanga, sarudza sero kuratidza mhedzisiro. Tinobva tadzvanya bhatani "Isa basa" (kuruboshwe kweformula bar).
2. The Function Wizard hwindo rinovhura. Nekudzvanya pane yazvino chikamu chemabasa, wedzera rondedzero uye tinya pamutsara mairi "Statistical".
3. Mune rondedzero yakarongwa, tinya pane opareta "CONFIDENCE NORM", ipapo shandisa OK.
4. Tichaona hwindo rine zvigadziro zvenharo dzebasa, kuzadza iyo yatinodzvanya bhatani OK.
   • mumunda "Alpha" ratidza mwero wekukosha. Basa redu rinotora 95% yekuvimba level. Kutsiva kukosha uku mufomu yekuverenga, yatafunga pamusoro, tinowana chirevo ichi: (100-95)/100. Isu tinoinyora mundima yekupokana (kana iwe unogona pakarepo kunyora mhedzisiro yekuverenga yakaenzana ne0,05).
   • mumunda "std_off" maererano nemamiriro edu ezvinhu, tinonyora nhamba 8.
   • mundima ye "Size", tsanangura huwandu hwezvinhu zvinofanirwa kuongororwa. Muchiitiko chedu, zviyero gumi zvakatorwa, saka tinonyora nhamba yegumi.
5. Kuti udzivise kuve nekugadzirisa zvakare basa kana data rachinja, unogona kuita otomatiki. Kune izvi tinoshandisa basa "ONA”. Isa chinongedzo munzvimbo yekupinda yeiyo nharo ruzivo "Saizi", wobva wadzvanya pakona yekona iri kuruboshwe rweformula bar wodzvanya chinhu chacho “Mamwe maficha…”.
6. Nekuda kweizvozvo, rimwe hwindo reFundo Wizard richavhura. Nekusarudza chikamu "Statistical", tinya pane "CHECK”, ipapo OK.
7. Iyo skrini icharatidza imwe hwindo ine zvigadziriso zvenharo dzebasa, iro rinoshandiswa kuona huwandu hwemasero mune yakapihwa renji ine data yenhamba.

   Basa Formula Check zvakanyorwa sezvizvi: =СЧЁТ(Значение1;Значение2;...).

   Huwandu hwenharo dziripo dzebasa iri dzinogona kusvika 255. Pano unogona kunyora kana nhamba chaidzo, kana kero dzesero, kana maseru. Tichashandisa sarudzo yekupedzisira. Kuti uite izvi, tinya pane yekupinda ruzivo nzvimbo yenharo yekutanga, wozobata pasi bhatani rekuruboshwe, sarudza maseru ese eimwe makoramu etafura yedu (usingaverenge musoro), wobva wadzvanya bhatani. OK.

8. Somugumisiro wezviito zvakatorwa, mugumisiro wekuverenga kune mushandisi ucharatidzwa musero rakasarudzwa KUVIMBA NORM. Mudambudziko redu, kukosha kwayo kwakave kuenzana 4,9583603.
9. Asi ichi hachisati chiri chigumisiro chekupedzisira mubasa redu. Tevere, iwe unofanirwa kuverenga kukosha kweavhareji pane imwe nguva yakapihwa. Kuti aite izvi, unofanira kushandisa basa ".MOYO”A iyo inoita basa rekuverenga avhareji pane yakatarwa dhata.

   The operator formula yakanyorwa seizvi: =СРЗНАЧ(число1;число2;...).

   Sarudza sero kwatinoronga kuisa basa uye tinya bhatani "Isa basa".

10. Muchikwata "Statistical" sarudza anofinha anoshanda “MOYO” uye baya OK.
11. Mukushanda nharo munharo kukosha “Nhamba” tsanangura huwandu, hunosanganisira maseru ese ane kukosha kwezviyero zvese. Tinobva tabhuya ZVAKANAKA.
12. Somugumisiro wezviito zvakatorwa, chiyero chepakati chichaverengerwa otomatiki uye chinoratidzwa musero nebasa richangobva kuiswa.
13. Iye zvino tinoda kuverenga iyo CI (nguva yekuvimba) miganhu. Ngatitange nekuverenga kukosha kwemuganhu wekurudyi. Isu tinosarudza sero kwatinoda kuratidza mhedzisiro, uye toita wedzero yemhedzisiro yakawanikwa tichishandisa vanobata "MOYO” Uye "ZVINHU ZVEKUVIMBA”. Muchiitiko chedu, fomula inotaridzika seizvi: A14+A16. Mushure mekutaipa, dzvanya pinda.
14. Nekuda kweizvozvo, kuverenga kuchaitwa uye mhedzisiro ichakurumidza kuratidzwa muchitokisi neformula.
15. Zvadaro, nenzira yakafanana, tinoita kuverenga kuti tiwane kukosha kwemuganhu wekuruboshwe weCI. Muchiitiko ichi chete kukosha kwechigumisiro "ZVINHU ZVEKUVIMBA” haufanire kuwedzera, asi bvisa kubva pamhedzisiro yakawanikwa uchishandisa opareta "MOYO”. Muchiitiko chedu, fomula inotaridzika seizvi: =A16-A14.
16. Mushure mekudzvanya Enter, tinowana mhedzisiro musero rakapihwa neformula.

Cherechedza: Mundima dziri pamusoro, takaedza kutsanangura zvakadzama sezvinobvira matanho ese uye basa rega rega rakashandiswa. Nekudaro, ese akatemerwa mafomula anogona kunyorwa pamwechete, sechikamu cheimwe hombe:

• Kuti uone muganho wakakodzera weCI, iyo general formula inotaridzika seizvi:

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

• Saizvozvo, kumuganho wekuruboshwe, chete pachinzvimbo chekuwedzera, iwe unofanirwa kuisa minus:

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

Nzira 2: TRUST.STUDENT mushandisi

Zvino, ngatijairane nemushandisi wechipiri wekuona nguva yekuvimba − TRUST.STUDENT. Iri basa rakapinzwa muchirongwa nguva pfupi yadarika, kutanga kubva mushanduro yeExcel 2010, uye rakanangana nekuona CI yedataset rakasarudzwa uchishandisa kugoverwa kweMudzidzi, nemusiyano usingazivikanwi.

Basa Formula TRUST.STUDENT Sezvinotevera:

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

Ngationgororei mashandisirwo emushandisi uyu pamuenzaniso wetafura imwechete. Chete ikozvino isu hatizive kutsauka kwakajairwa zvinoenderana nemamiriro edambudziko.

1. Kutanga, sarudza sero apo tinoronga kuratidza chigumisiro. Wobva wadzvanya pane icon "Isa basa" (kuruboshwe kweformula bar).
2. Iyo yatove inozivikanwa Function Wizard hwindo ichavhura. Sarudza chikamu "Statistical", ipapo kubva pane yakarongwa runyorwa rwemabasa, tinya pane opareta “MUDZIDZI AKAVIMBA”, zvino - OK.
3. Muhwindo rinotevera, isu tinofanirwa kuseta basa nharo:
   • Mu "Alpha" semunzira yekutanga, tsanangura kukosha kwe0,05 (kana "100-95)/100").
   • Ngatienderere mberi kune nharo. "std_off". Nekuti maererano nemamiriro edambudziko, kukosha kwayo hakuzivikanwe kwatiri, isu tinofanirwa kuita maverengero akakodzera, umo mushandisi "STDEV.B”. Dzvanya pane wedzera basa bhatani uye ipapo pane chinhu “Mamwe maficha…”.
   • Muhwindo rinotevera reFundo Wizard, sarudza mushandisi ".STDEV.B” muchikwata "Statistical" uye baya OK.
   • Isu tinopinda mukati mebasa rekupopotedzana kuseta hwindo, iyo fomula inotaridzika seizvi: =СТАНДОТКЛОН.В(число1;число2;...). Senharo yekutanga, tinotsanangura huwandu hunosanganisira maseru ese mu "Value" column (tisingaverenge musoro).
   • Zvino iwe unofanirwa kudzokera kuhwindo nemabasa nharo "TRUST.STUDENT”. Kuti uite izvi, tinya pane kunyorwa kwezita rimwe chete mundima yekuisa fomula.
   • Zvino ngatienderere mberi kune nharo yekupedzisira "Size". Sezvazviri munzira yekutanga, pano unogona kungotsanangura huwandu hwemasero, kana kuisa mushandisi “CHECK”. Isu tinosarudza sarudzo yekupedzisira.
   • Kana nharo dzese dzazadzwa, tinya bhatani OK.
4. Iyo sero yakasarudzwa icharatidza kukosha kweiyo nguva yekuvimba zvinoenderana nematanho atakatsanangura.
5. Tevere, isu tinofanirwa kuverenga kukosha kweCI miganhu. Uye nokuda kweizvi unoda kuwana muvhareji wehuwandu hwehuwandu hwakasarudzwa. Kuti tiite izvi, tinoshandisa zvakare basa "MOYO”. Iyo algorithm yezviito yakafanana neyakatsanangurwa munzira yekutanga.
6. Ndawana kukosha "MOYO”, unogona kutanga kuverenga miganhu yeCI. Maformula pachawo haana kusiyana neaya anoshandiswa ne”ZVINHU ZVEKUVIMBA”:
   • Muganho wekurudyi CI=AVERAGE+STUDENT CONFIDENCE
   • Left Bound CI=AVERAGE-STUDENT CONFIDENCE

mhedziso

Excel's arsenal yezvishandiso yakakura zvinoshamisa, uye pamwe chete neakajairika mabasa, chirongwa ichi chinopa akasiyana akasiyana emabasa akakosha anoita kuti kushanda nedata kuve nyore. Zvichida matanho anotsanangurwa pamusoro apa anogona kuita seakaoma kune vamwe vashandisi pakutanga kuona. Asi mushure mekuongorora kwakadzama kwenyaya uye kutevedzana kwezviito, zvese zvichava nyore.