Lissafin Tazarar Amincewa a cikin Excel

Ana amfani da Excel don yin ayyuka daban-daban na ƙididdiga, ɗaya daga cikinsu shine lissafin tazarar amincewa, wanda aka yi amfani da shi azaman mafi dacewa don maye gurbin ma'auni tare da ƙananan samfurin.

Muna so mu lura nan da nan cewa ainihin hanyar ƙididdige tazarar amincewa yana da wahala sosai, duk da haka, a cikin Excel akwai kayan aikin da yawa waɗanda aka tsara don sauƙaƙe wannan aikin. Mu duba su.

Ƙididdigar Tazarar Amincewa

Ana buƙatar tazarar amincewa don ba da kiyasin tazara ga wasu tsayayyen bayanai. Babban manufar wannan aiki shine don kawar da rashin tabbas na ƙimar ƙima.

Akwai hanyoyi guda biyu don yin wannan aikin a cikin Microsoft Excel:

• Operator AL'ADA AMINCI - ana amfani da su a cikin lokuta inda aka san tarwatsawa;
• Operator AMINCI.DALIBIlokacin da ba a san bambancin ba.

A ƙasa za mu yi nazarin hanyoyin biyu a aikace.

Hanyar 1: Bayanin TRUST.NORM

An fara gabatar da wannan aikin a cikin arsenal na shirin a cikin fitowar Excel 2010 (kafin wannan sigar, mai aiki ya maye gurbinsa "AMINCI"). An haɗa mai aiki a cikin nau'in "ƙididdiga".

Tsarin Aiki AL'ADA AMINCI yayi kama da haka:

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

Kamar yadda muke iya gani, aikin yana da dalilai guda uku:

• "Alfa" alama ce ta matakin mahimmanci, wanda aka ɗauka azaman tushen lissafin. An ƙididdige matakin amincewa kamar haka:
  • 1-"Альфа". Wannan magana yana aiki idan ƙimar "Alfa" gabatar a matsayin coefficient. Misali, 1-0,7 0,3 =, inda 0,7=70%/100%.
  • (100-"Альфа")/100. Za a yi amfani da wannan magana idan muka yi la'akari da matakin amincewa tare da ƙimar "Alfa" cikin kashi dari. Misali, (100-70) / 100 = 0,3.
• "Standard sabawa" - bi da bi, daidaitattun ƙetare samfurin bayanan da aka bincika.
• "Size" shine girman samfurin bayanan.

lura: Don wannan aikin, kasancewar dukkanin hujjoji guda uku sharadi ne.

Mai aiki"AMINCI", wanda aka yi amfani da shi a farkon juzu'in shirin, ya ƙunshi muhawara iri ɗaya kuma yana yin ayyuka iri ɗaya.

Tsarin Aiki AMANA mai bi:

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

Babu bambance-bambance a cikin dabarar kanta, kawai sunan mai aiki ya bambanta. A cikin Excel 2010 da daga baya bugu, wannan ma'aikacin yana cikin nau'in Compatibility. A cikin tsofaffin juzu'in shirin, yana cikin sashin ayyuka na tsaye.

An ƙayyade iyakar tazarar amincewa ta hanyar dabara mai zuwa:

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

inda Х shine matsakaicin ƙimar akan kewayon da aka ƙayyade.

Yanzu bari mu ga yadda ake amfani da waɗannan dabarun a aikace. Don haka, muna da tebur tare da bayanai daban-daban daga ma'auni 10 da aka ɗauka. A wannan yanayin, daidaitaccen karkatar da saitin bayanai shine 8.

Ayyukanmu shine samun ƙimar tazarar amincewa tare da matakin amincewa 95%.

1. Da farko, zaɓi tantanin halitta don nuna sakamakon. Sai mu danna maballin "Saka aikin" (zuwa hagu na mashayin dabara).
2. Tagan Wizard Aiki yana buɗewa. Ta danna kan nau'in ayyuka na yanzu, fadada lissafin kuma danna layin da ke cikinsa "Kididdiga".
3. A cikin lissafin da aka tsara, danna kan afareta "KA'IDAR TSARKI", sannan latsa OK.
4. Za mu ga taga tare da saituna na muhawarar aikin, cika abin da muke danna maɓallin OK.
   • a cikin filin "Alfa" nuna matakin mahimmanci. Ayyukanmu yana ɗaukar matakin amincewa 95%. Sauya wannan ƙima a cikin tsarin lissafin, wanda muka yi la'akari a sama, muna samun furci: (100-95)/100. Mun rubuta shi a cikin filin gardama (ko za ku iya rubuta sakamakon lissafin nan da nan daidai da 0,05).
   • a cikin filin "std_off" bisa ga sharuddan mu, muna rubuta lamba 8.
   • a cikin filin "Size", saka adadin abubuwan da za a bincika. A cikin yanayinmu, an ɗauki ma'auni 10, don haka muka rubuta lamba 10.
5. Don guje wa sake saita aikin lokacin da bayanai suka canza, zaku iya sarrafa shi ta atomatik. Don wannan muna amfani da aikin "DUBA". Saka mai nuni a cikin wurin shigar da bayanan gardama "Size", sannan danna gunkin triangle a gefen hagu na mashayin dabara kuma danna abun "Ƙarin fasali...".
6. A sakamakon haka, wani taga na Aiki Wizard zai bude. Ta zabar rukuni "Kididdiga", danna kan aikin"DUBA", sai OK.
7. Allon zai nuna wata taga tare da saitin muhawarar aikin, wanda ake amfani da shi don ƙayyade adadin sel a cikin kewayon da aka ba da wanda ya ƙunshi bayanan lambobi.

   Tsarin Aiki duba an rubuta kamar haka: =СЧЁТ(Значение1;Значение2;...).

   Adadin da ke akwai don wannan aikin zai iya zuwa 255. Anan zaka iya rubuta takamaiman lambobi, ko adiresoshin cell, ko jeri na salula. Za mu yi amfani da zaɓi na ƙarshe. Don yin wannan, danna wurin shigar da bayanai don hujja ta farko, sannan, riƙe maɓallin linzamin kwamfuta na hagu, zaɓi duk sel ɗaya daga cikin ginshiƙan tebur ɗinmu (ba ƙidaya taken ba), sannan danna maɓallin. OK.

8. Sakamakon ayyukan da aka yi, za a nuna sakamakon lissafin ma'aikacin a cikin tantanin halitta da aka zaɓa AL'ADA AMINCI. A cikin matsalarmu, darajarta ta zama daidai 4,9583603.
9. Amma har yanzu wannan ba shine sakamako na ƙarshe a cikin aikinmu ba. Na gaba, kuna buƙatar ƙididdige matsakaicin ƙimar sama da tazara da aka bayar. Don yin wannan, kuna buƙatar amfani da aikin "ZUCIYA"A wanda ke yin aikin ƙididdige matsakaita akan kewayon kewayon bayanai.

   An rubuta dabarar ma'aikaci kamar haka: =СРЗНАЧ(число1;число2;...).

   Zaɓi tantanin halitta inda muke shirin saka aikin kuma danna maɓallin "Saka aikin".

10. A cikin rukuni "Kididdiga" zabi mai aiki mai ban sha'awa "ZUCI" kuma danna OK.
11. A cikin muhawarar aiki a cikin ƙimar hujja "Lambar" Ƙayyade kewayon, wanda ya haɗa da duk sel tare da ƙimar duk ma'auni. Sai mu danna OKAYA.
12. Sakamakon ayyukan da aka yi, matsakaicin ƙimar za a ƙididdige shi ta atomatik kuma a nuna shi a cikin tantanin halitta tare da sabon aikin da aka saka.
13. Yanzu muna buƙatar ƙididdige iyakokin CI (tazarar amincewa). Bari mu fara da ƙididdige ƙimar iyakar daidai. Muna zaɓar tantanin halitta inda muke son nuna sakamakon, kuma muna yin ƙarin sakamakon da aka samu ta amfani da masu aiki "ZUCIYA" Kuma "TSORON ARZIKI”. A wajenmu, dabarar ta yi kama da haka: A14+A16. Bayan buga shi, danna Shigar.
14. A sakamakon haka, za a yi lissafin kuma za a nuna sakamakon nan da nan a cikin tantanin halitta tare da dabara.
15. Sa'an nan kuma, a irin wannan hanya, muna yin lissafin don samun darajar iyakar hagu na CI. Kawai a wannan yanayin darajar sakamakon "TSORON ARZIKI” ba kwa buƙatar ƙarawa, amma cire daga sakamakon da aka samu ta amfani da afareta "ZUCIYA". A wajenmu, dabarar ta yi kama da haka: =A16-A14.
16. Bayan danna Shigar, za mu sami sakamakon a cikin tantanin halitta da aka ba da tsari.

lura: A cikin sakin layi na sama, mun yi ƙoƙarin bayyana dalla-dalla yadda zai yiwu duk matakai da kowane aikin da aka yi amfani da su. Koyaya, ana iya rubuta duk ƙa'idodin da aka tsara tare, a matsayin ɓangare na ɗaya babba:

• Don ƙayyade iyakar dama na CI, tsarin gaba ɗaya zai yi kama da haka:

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

• Hakazalika, don iyakar hagu, kawai maimakon ƙari, kuna buƙatar sanya ragi:

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

Hanyar 2: AMANA.DALIBAN mai aiki

Yanzu, bari mu saba da mai aiki na biyu don tantance tazarar amincewa - AMINCI.DALIBI. An gabatar da wannan aikin a cikin shirin kwanan nan, wanda ya fara daga sigar Excel 2010, kuma ana nufin tantance CI na bayanan da aka zaɓa ta amfani da rarrabawar ɗalibi, tare da bambancin da ba a sani ba.

Tsarin Aiki AMINCI.DALIBI mai bi:

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

Bari mu bincika aikace-aikacen wannan ma'aikaci akan misalin tebur guda. Sai kawai yanzu ba mu san ma'auni daidai ba bisa ga yanayin matsalar.

1. Da farko, zaɓi tantanin halitta inda muke shirin nuna sakamakon. Sannan danna gunkin "Saka aikin" (zuwa hagu na mashayin dabara).
2. Tagar Wizard Aiki sanannen zai buɗe. Zaɓi nau'i "Kididdiga", sannan daga jerin ayyuka da aka tsara, danna kan afareta "AMINCI DALIBAN", sannan - OK.
3. A cikin taga na gaba, muna buƙatar saita dalilan aikin:
   • a cikin "Alfa" kamar yadda a cikin hanyar farko, ƙayyade ƙimar 0,05 (ko "100-95) / 100").
   • Mu ci gaba da hujja. "std_off". Domin bisa ga yanayin matsalar, darajarta ba a san mu ba, muna buƙatar yin lissafin da ya dace, wanda mai aiki "STDEV.B”. Danna maɓallin ƙara ayyuka sannan a kan abu "Ƙarin fasali...".
   • A cikin taga na gaba na Wizard Aiki, zaɓi afaretan "STDEV.B” a cikin category "Kididdiga" kuma danna OK.
   • Mun shiga cikin taga saitunan mahallin aiki, wanda tsarinsa yayi kama da haka: =СТАНДОТКЛОН.В(число1;число2;...). A matsayin hujja ta farko, mun ƙididdige kewayon da ya haɗa da duk sel a cikin ginshiƙi "Ƙimar" (ba ƙidaya kan taken ba).
   • Yanzu kuna buƙatar komawa taga tare da gardamar aikin "AMINCI.DALIBAN”. Don yin wannan, danna kan rubutun sunan guda a cikin filin shigar da dabara.
   • Yanzu bari mu matsa zuwa hujja ta ƙarshe "Size". Kamar yadda yake a hanya ta farko, anan zaka iya ko dai kawai saka kewayon sel, ko saka mai aiki “Duba”. Mun zaɓi zaɓi na ƙarshe.
   • Da zarar an cika duk gardama, danna maɓallin OK.
4. Tantanin da aka zaɓa zai nuna ƙimar tazarar amincewa bisa ga sigogin da muka ayyana.
5. Na gaba, muna buƙatar lissafin ƙimar iyakokin CI. Kuma don wannan kuna buƙatar samun matsakaicin ƙima don kewayon da aka zaɓa. Don yin wannan, muna sake amfani da aikin "ZUCIYA". Algorithm na ayyuka yayi kama da wanda aka bayyana a hanya ta farko.
6. Bayan sun sami darajar"ZUCIYA", zaku iya fara lissafin iyakokin CI. Hanyoyin da kansu ba su bambanta da waɗanda aka yi amfani da su tare da "TSORON ARZIKI”:
   • Iyakar dama CI=MAGASKIYA+AMINTAR DALIBAN
   • Hagu Bound CI=AMINCI-DALIBAN GASKIYA

Kammalawa

Kayan aikin kayan aiki na Excel yana da girman gaske, kuma tare da ayyuka na yau da kullun, shirin yana ba da ayyuka na musamman iri-iri waɗanda zasu sa aiki tare da bayanai ya fi sauƙi. Wataƙila matakan da aka kwatanta a sama na iya zama kamar rikitarwa ga wasu masu amfani da kallon farko. Amma bayan cikakken nazarin batun da jerin ayyuka, komai zai zama mafi sauƙi.