NTHAWI ZOSADALIRA ZA EXCEL

Njira imodzi yothetsera mavuto a chiwerengero ndi kuwerengera kwa chidaliro. Amagwiritsidwa ntchito monga chiwerengero chosankhidwa chokhazikika ndi kukula kwazing'ono. Tiyenera kukumbukira kuti njira yowerengera nthawi yokhala ndi chidaliro ndi yovuta kwambiri. Koma zida za pulogalamu ya Excel zimakhala zosavuta. Tiyeni tiwone momwe izi zakhalira mwa kuchita.

Onaninso: Zomwe zimagwira ntchito mu Excel

Njira yowerengetsera

Njira imeneyi imagwiritsidwa ntchito poyerekeza nthawi zosiyanasiyana. Ntchito yaikulu ya chiwerengero ichi ndi kuchotsa kusatsimikizika kwa chiwerengerochi.

Mu Excel, pali njira zazikulu ziwiri zomwe mungachite kuti muwerenge pogwiritsa ntchito njirayi: pamene kusiyana kwake kumadziwika ndi pamene sikudziwika. Pachiyambi choyamba, ntchitoyi imagwiritsidwa ntchito kuwerengera. TRUST.NORM, ndipo chachiwiri - TRUST.STUDENT.

Mchitidwe 1: KUKHULUPIRIRA.NORM ntchito

Woyendetsa TRUST.NORMzokhudzana ndi chiwerengero cha gulu la ntchito, zinayambira koyamba ku Excel 2010. Mu mapulogalamu oyambirira a pulojekitiyi, zimagwiritsidwa ntchito mofanana KUKHULUPIRIRA. Ntchito ya wogwiritsira ntchitoyi ndi kuwerengera nthawi yokhala ndi chidaliro ndi kufalitsa kwabwino kwa anthu ambiri.

Mawu ake omasulira ndi awa:

= KUKHULUPIRIRA. NORM (alpha; standard_off; kukula)

"Alpha" - mtsutso wonena za msinkhu umene umagwiritsidwa ntchito kuwerengera chikhulupiliro. Mbali yodalirika ndi mawu otsatirawa:

(1- "Alpha") * 100

"Kusiyana kwakukulu" - Iyi ndi mtsutsano, zomwe zimayambira pa dzina. Izi ndizoyendetsedwe kazitsulo zazomwe zimayesedwa.

"Kukula" - Kukangana kumene kumatsimikizira kukula kwake.

Zotsutsana zonse za woyendetsa izi zikufunika.

Ntchito KUKHULUPIRIRA Zili ndi ndondomeko zomwezo ndi zofanana ndi zomwe zapitazo. Chizindikiro chake ndi:

= KUKHULUPIRIRA (alpha; standard_off; kukula)

Monga mukuonera, kusiyana kuli kokha pa dzina la woyendetsa. Ntchito yeniyeniyo imasiyidwa mogwirizana ndi Excel 2010 ndi mawonekedwe atsopano m'gulu lapadera. "Kugwirizana". M'masulidwe a Excel 2007 ndi kale, ilipo mu gulu lalikulu la owerengetsera.

Malire a nthawi ya chidaliro amatsimikiziridwa pogwiritsa ntchito njirayi:

X + (-) TRUST. NORM

Kumeneko X - ndiyeso yamtengo wapatali, yomwe ili pakati pa osankhidwawo.

Tsopano tiyeni tiyang'ane momwe tingawerengere nthawi yodalira pa chitsanzo chapadera. Mayesero 12 anachitidwa, chifukwa cha zotsatira zosiyanasiyana zomwe zinalembedwa patebulo. Uku ndiko kwathunthu. Kusiyana kwapadera ndi 8. Timayenera kuwerengera chikhulupiliro pa chikhulupiliro cha 97%.

Sankhani selo komwe zotsatira za kusinthidwa kwa deta ziwonetsedwe. Dinani pa batani "Ikani ntchito".

Zikuwonekera Mlaliki Wachipangizo. Pitani ku gawo "Zotsatira" ndipo sankhani dzina DOVERT.NORM. Pambuyo pake timatsegula batani. "Chabwino".

Fesholo yotsutsana ikutsegula. Minda yake mwachibadwa imafanana ndi mayina a zifukwa.
Ikani malonda mu munda woyamba - "Alpha". Pano ife tiyenera kufotokoza mlingo wamtengo wapatali. Pamene tikukumbukira, mlingo wathu wa chikhulupiriro ndi 97%. Pa nthawi yomweyi, tinanena kuti iwerengedwa motere:
(1- "Alpha") * 100
Choncho, kuti muwerenge mlingo wamtengo wapatali, ndiko kuti, kudziwa mtengo "Alpha" Njira yotsatirayi iyenera kugwiritsidwa ntchito:
(1 level of trust) / 100
Ndikutanthauza kuti, m'malo mmalo mwa mtengo, timapeza:
(1-97)/100
Ndi ziwerengero zophweka, ife tikupeza kuti kutsutsana "Alpha" zofanana 0,03. Lowani mtengo uwu m'munda.
Monga mukudziwira, chikhalidwe cha kusokonezeka ndizo 8. Kotero, mmunda "Kusiyana kwakukulu" ingolemba nambala iyi.
Kumunda "Kukula" muyenera kulowa chiwerengero cha zinthu za mayesero. Pamene tikuwakumbukira 12. Koma kuti tipeze mayendedwe ake osasintha nthawi iliyonse kuyesedwa kwatsopano, tiyeni tiyike mtengowu osati ndi nambala wamba, koma mothandizidwa ndi wogwiritsira ntchito ACCOUNT. Choncho, ikani chotsekeramo kumunda "Kukula"ndiyeno dinani pang'onopang'ono yomwe ili kumanzere kwa bar.
Mndandanda wa ntchito zatsopano zomwe zikugwiritsidwa ntchito zikuwonekera. Ngati woyendetsa ACCOUNT yogwiritsidwa ntchito posachedwa, ziyenera kukhala pazinthu izi. Pankhaniyi, muyenera kungolemba pa dzina lake. Mulimonsemo, ngati simukupeza, ndiye kuti muyambe kudutsa "Zina ...".

Tikuwonekera kale Mlaliki Wachipangizo. Pitani ku gulu kachiwiri "Zotsatira". Sankhanipo dzina "NKHANI". Timasankha pa batani "Chabwino".

Festile yotsutsana ya mawu apamwambawa ikuwonekera. Ntchitoyi inalinganizidwa kuti awerengere chiwerengero cha maselo muzinthu zomwe zilipo zomwe zili ndi chiwerengero. Mawu ake omasulira ndi awa:
= COUNT (value1; value2; ...)
Gulu lamakangano "Makhalidwe" ndikutanthauza mtundu umene mukufunikira kuwerengera chiwerengero cha maselo odzazidwa ndi deta. Zonsezi zingakhale ndi zokambirana zokwana 255, koma kwa ife ndi chimodzi chofunika.
Ikani cholozera mmunda Chofunika1 " ndipo, mutagwiritsa ntchito batani lamanzere, sankhani mtundu umene uli nawo pa pepala. Ndiye adiresi yake iwonetsedwa mmunda. Timasankha pa batani "Chabwino".

Pambuyo pake, ntchitoyo idzachita kuwerengera ndikuwonetsa zotsatira mu selo yomwe ilipo. Momwe ife timakhalira, njirayi inakhazikitsidwa mwa mawonekedwe otsatirawa:
= KUKHULUPIRIRA. NORM (0.03; 8; ACCOUNT (B2: B13))
Zotsatira zonse za ziwerengerozo zinali 5,011609.

Koma sizo zonse. Pamene tikukumbukira, malire a nthawi ya chidaliro amawerengedwa powonjezerapo ndikuchotsa kuchoka ku zitsanzo zogulira zotsatira za chiwerengerocho TRUST.NORM. Mwa njira iyi, malire abwino ndi a kumanzere a nthawi yodalirika amawerengedwa motsatira. Chitsanzo chamtengo wapatali chikhoza kuwerengedwa pogwiritsira ntchito wogwiritsira ntchito MALANGIZO.
Wogwiritsira ntchitoyi akukonzekera kuti awerengere chiwerengero cha masamu pamasamba omwe anasankhidwa. Lili ndi mawu ofotokoza mwachidule:
= GAWO (nambala1; nambala2; ...)
Kutsutsana "Nambala" Zingakhale zosiyana ndi mtengo, kapena kutanthauza maselo kapena mzere wonse umene uli nawo.
Choncho, sankhani selo limene mawerengedwe a mtengo wapatali adzawonetsedwa, ndipo dinani pa batani "Ikani ntchito".

Kutsegulidwa Mlaliki Wachipangizo. Bwererani ku gululo "Zotsatira" ndipo sankhani kuchokera mndandanda wa dzina "SRZNACH". Monga nthawi zonse, timakani pa batani "Chabwino".

Fesholo yotsutsana ikuyamba. Ikani cholozera mmunda "Number1" ndipo ndi batani lamanzere lachinsinsi, mutasankhiratu zamtundu uliwonse. Pambuyo pazigawozo zikuwonetsedwa m'munda, dinani pa batani "Chabwino".

Pambuyo pake MALANGIZO imasonyeza zotsatira za kuwerengera muzomwe zimapangidwa.

Timawerengera malire oyenera a nthawi yodalirika. Kuti muchite izi, sankhani selo losiyana, ikani chizindikiro "=" ndipo onjezerani zomwe zili m'mapangidwe a pepala, zomwe zotsatira za mawerengedwe a ntchito zikupezeka MALANGIZO ndi TRUST.NORM. Kuti muyese kuwerengera, pindani makiyiwo Lowani. Kwa ife, ife tiri ndi njira yotsatirayi:
= F2 + A16
Zotsatira za kuwerengera: 6,953276

Mofananamo, ife tikuwerengera malire a kumanzere a nthawi yodalirika, kokha nthawi ino kuchokera ku zotsatira za kuwerengera MALANGIZO Chotsani zotsatira za chiwerengero cha wogwiritsira ntchito TRUST.NORM. Zimatengera chitsanzo cha chitsanzo chathu cha mtundu wotsatira:
= F2-A16
Zotsatira za kuwerengera: -3,06994

Tinayesetsa kufotokozera mwatsatanetsatane njira zonse zowerengera nthawi ya chidaliro, choncho tinalongosola mwatsatanetsatane ndondomeko iliyonse. Koma zochitika zonse zingathe kuphatikizidwa mu njira imodzi. Kuwerengera kwa malire oyenera a nthawi yodalirika kungalembedwe monga:
= GAWO (B2: B13) + TRUST.NORM (0.03; 8; COUNT (B2: B13))

Kuwerengera komweko kwa malire a kumanzere kudzawoneka ngati:
= GAWO (B2: B13) - TRUST. NORM (0.03; 8; COUNT (B2: B13))

Njira 2: Ntchito TRUST FESTUDENT

Kuonjezera apo, mu Excel palinso ntchito ina yomwe ikukhudzana ndi kuwerengera kwa chidaliro - TRUST.STUDENT. Izo zinangowoneka kokha kuchokera ku Excel 2010. Wogwira ntchitoyi amachita chiŵerengero cha kuchepetsa chidaliro cha chiwerengero cha anthu pogwiritsa ntchito kufalitsa kwa Ophunzira. Ndizovuta kuzigwiritsira ntchito pazomwe zimakhala zosiyana komanso, motero, kusokonekera kwabwino sikudziwika. Mawu ogwiritsira ntchito ndi:

= TEST TREST (alpha; standard_off; kukula)

Monga momwe mukuonera, mayina a ogwira ntchitoyi sanasinthe.

Tiyeni tiwone momwe tingawerengere malire a nthawi yokhala ndi chidaliro ndi zosadziwika zosadziwika kupyolera mu chitsanzo cha chiwerengero chomwecho chomwe tachiganizira mu njira yapitayi. Mlingo wokhulupirira, monga nthawi yotsiriza, umatenga 97%.

Sankhani selo limene mawerengedwewo adzapangidwe. Timasankha pa batani "Ikani ntchito".

Mudatseguka Wizard ntchito pitani ku gulu "Zotsatira". Sankhani dzina "DOVERT.STUUDENT". Timasankha pa batani "Chabwino".

Zenera la zotsutsana za otchulidwayo zimayambika.
Kumunda "Alpha", poganizira kuti mlingo wa chikhulupiriro ndi 97%, timalemba nambalayi 0,03. Nthawi yachiwiri pazifukwa zowerengera izi sizingatheke.
Pambuyo pake ikani cholozera kumunda "Kusiyana kwakukulu". Panthawi ino, chiwerengero ichi sichidziwika kwa ife ndipo chiyenera kuwerengera. Izi zachitika pogwiritsa ntchito ntchito yapadera - STANDOWCLON.V. Kuti muyitane zenera la woyendetsa ndegeyi, dinani pa katatu kumanzere kwa bar. Ngati mndandanda watsegulidwa sitinapeze dzina lofunidwa, ndiye pitirani mu chinthucho "Zina ...".

Iyamba Mlaliki Wachipangizo. Pitani ku gawo "Zotsatira" ndipo onani dzina mmenemo "STANDOTKLON.V". Kenaka dinani pa batani. "Chabwino".

Fesholo yotsutsana ikutsegula. Ntchito yogwira ntchito STANDOWCLON.V ndi kutsimikiza kwa kusokonezeka kwapadera pamene sampuli. Chizindikiro chake ndi:
= STDEV.V (nambala1; nambala2; ...)
Sikovuta kuganiza kuti kukangana "Nambala" ndi adiresi ya chinthu chosankhidwa. Ngati chitsanzocho chiyikidwa mumtundu umodzi, ndiye kuti mungagwiritse ntchito ndemanga imodzi yokha, ndikuwongolera mbaliyi.
Ikani cholozera mmunda "Number1" ndipo, monga nthawi zonse, mutagwira batani lamanzere, khetha mndandanda. Pambuyo pazigawozo zogwira ntchitoyi, musafulumize kukanikiza batani "Chabwino", chifukwa zotsatira zake sizolondola. Choyamba tifunika kubwerera kuwindo lamatsutsano TRUST.STUDENTkuti apange mtsutso womaliza. Kuti muchite izi, dinani pa dzina loyenera pa bar.

Wowonjezera mawonekedwe a ntchito yodziwika imatsegulidwanso. Ikani cholozera mmunda "Kukula". Apanso, dinani katatu kakudziwika kuti mupite ku chisankho cha ogwira ntchito. Monga mudamvetsetsa, tikufunikira dzina. "NKHANI". Popeza tinagwiritsira ntchito ntchitoyi pakuwerengera njira yapitayi, ilipo mndandandawu, kotero dinani pomwepo. Ngati simukuzipeza, pitirizani kutsatira ndondomeko yowonongeka.

Kumenya zenera ACCOUNTikani malonda mmunda "Number1" ndipo pogwiritsa ntchito batani, pangani zosankhidwa. Kenaka dinani pa batani. "Chabwino".

Pambuyo pake, pulogalamuyi ikuwerengetsa ndi kuwonetsera kufunika kwa nthawi yodalirika.

Kuti tipeze malire, tidzafunikiranso kuwerengera mtengo wamtengo wapatali. Koma, kupatsidwa kuti chiwerengero cha algorithm chogwiritsa ntchito njirayi MALANGIZO chimodzimodzi ndi njira yapitayi, ndipo ngakhale zotsatira sizinasinthe, sitidzangoganizira izi mobwerezabwereza.

Mwa kuwonjezera zotsatira za chiwerengero MALANGIZO ndi TRUST.STUDENT, timapeza malire oyenera a nthawi yodalirika.

Kuchokera ku zotsatira za chiwerengero cha woyendetsa MALANGIZO chiwerengero chowerengera TRUST.STUDENT, tili ndi malire a kumanzere a chitetezo.

Ngati chiwerengerocho chalembedwa mu njira imodzi, ndiye kuti kuwerengera kwa malire abwino kumbali yathu kudzawoneka ngati:
= GAWO (B2: B13) + KUYENERA KUYERA (0.03; STANDARD CLON B (B2: B13); ACCOUNT (B2: B13))

Potero, ndondomeko yowerengera malire a kumanzere idzawoneka ngati iyi:
= GAWO (B2: B13) -DVERIT.TUDENT (0.03; STANDARD CLON B (B2: B13); ACCOUNT (B2: B13))

Monga mukuonera, zida za Excel zimakulolani kuti muzitha kuchepetsa kuwerengera kwa nthawi ya chidaliro ndi malire ake. Kwa zolinga izi, opaleshoni zosiyana zimagwiritsidwa ntchito pazitsanzo zomwe kusiyana kwake kumadziwika ndi kosadziwika.