Ukubalwa kwesikhashana kokuqiniseka ku-Microsoft Excel

Enye yezindlela zokuxazulula izinkinga zezibalo ukubala isikhathi sokuqiniseka. Kusetshenziswa njengenye indlela ekhethwayo yokubonisa ukulinganisa ngosayizi omncane wesampula. Kumele kuqashelwe ukuthi inqubo yokubala isikhathi sokuzithemba iyinkimbinkimbi kunalokho. Kepha amathuluzi e-Excel angawelula kancane. Ake sibheke ukuthi kwenziwa kanjani lokhu.

Funda futhi: Imisebenzi yezibalo ku-Excel

Inqubo yokubala

Le ndlela isetshenziswa ekulinganiselweni kwesikhashana kwamanani ahlukahlukene ezibalo. Umsebenzi oyinhloko walesi sibalo ukususa iziqubu zokungaqiniseki zephuzu.

Ku-Excel kunezindlela ezimbili ezibalulekile zokwenza izibalo usebenzisa le ndlela: lapho ukwahluka kwaziwa khona, nokuthi kungaziwa nini. Esimweni sokuqala, umsebenzi usetshenziselwa ukubala I-TRUST.NORMkanti kwesibili - QHAWULA.

Indlela yoku-1: Umsebenzi we-TRUST.NORM

Umsebenzi I-TRUST.NORM, ongoweqembu lezibalo lemisebenzi, iqale ukuvela ku-Excel 2010. Ezinguqulweni zangaphambili zalolu hlelo, i-analogue yayo iyasetshenziswa IQINISO. Umsebenzi walesi opharetha ukubala isikhathi sokuzethemba ngokusabalalisa okujwayelekile kwesilinganiso sabantu.

I-syntax yayo imi ngolu hlobo:

= TRUST.NORM (alpha; standard_off; usayizi)

U-Alfa -ukuphikisana okubonisa izinga lokubaluleka elisetshenziselwa ukubala izinga lokuzethemba. Izinga lokuzithemba lilingana nale nkulumo elandelayo:

(1- "Alfa") * 100

"Ukuphambuka okujwayelekile" - Lokhu kungukuphikisana, umongo wawo ucacile igama. Lokhu ukuphambuka okujwayelekile kwesampula ehlongozwayo.

"Ubukhulu" - impikiswano enquma usayizi wesampula.

Zonke izimpikiswano ezenziwa kulo opharetha ziyadingeka.

Umsebenzi IQINISO inezimpikiswano ezifanayo kanye namathuba afana nalawo owedlule. I-syntax yayo imi ngolu hlobo:

= TRUST (alpha; standard_off; usayizi)

Njengoba ukwazi ukubona, umehluko usegameni le-opharetha kuphela. Umsebenzi obekiwe washiywa ku-Excel 2010 nakwezinguqulo ezintsha kusigaba esikhethekile ngezinjongo zokuhambisana. "Ukuvumelana". Ezinguqulweni ze-Excel 2007 nangaphambilini, sikhona eqenjini eliyinhloko labasebenza ngezibalo.

Umngcele wesikhathi sokuzethemba unqunywa kusetshenziswa ifomula lefomu elilandelayo:

I-X + (-) TRUST.NORM

Kuphi X inani lesampula eliphakathi nendawo elitholakala maphakathi nebanga elikhethiwe.

Manje ake sibheke ukuthi ungasibala kanjani isikhathi sokuqiniseka usebenzisa isibonelo esithile. Kwenziwa izivivinyo eziyi-12, ngenxa yalokho imiphumela eyahlukahlukene yabhalwa etafuleni. Lokhu kungukuphela kwethu. Ukuphambuka okujwayelekile kungu-8. Sidinga ukubala isikhathi sokuqiniseka ezingeni lama-97%.

1. Khetha iseli lapho kuzoboniswa khona umphumela wokulungiswa kwedatha. Chofoza inkinobho "Faka umsebenzi".



2. Kubonakala Isici Wizard. Iya esigabeni "Izibalo" bese ukhetha igama I-TRUST.NORM. Ngemuva kwalokho chofoza inkinobho "Kulungile".



3. Ibhokisi lokuphikisana liyavula. Amasimu ayo ngokwemvelo ahambelana namagama ezinto eziphikisanayo.
   Setha ikhesa kwinkambu yokuqala - U-Alfa. Lapha kufanele sikhombise izinga lokubaluleka. Njengoba sikhumbula, izinga lethu lokuzethemba lingama-97%. Ngasikhathi sinye, sithe ibalwa ngale ndlela:

   (1- "Alfa") * 100

   Ngakho-ke, ukubala izinga lokubaluleka, okungukuthi, ukuthola inani U-Alfa kufanele ufake ifomula yalolu hlobo:

   (1-level ukuzethemba) / 100

   Okusho ukuthi, ukufaka esikhundleni inani, sithola:

   (1-97)/100

   Ngokubala okulula sithola ukuthi impikiswano U-Alfa ilingana ne 0,03. Faka leli nani endle.

   Njengoba wazi, ngesimo ukuphambuka okujwayelekile 8. Ngakho-ke enkundleni "Ukuphambuka okujwayelekile" bhala le nombolo phansi.

   Ensimini "Ubukhulu" udinga ukufaka inani lezinto zokuhlolwa. Njengoba sibakhumbula 12. Kodwa ukuze uguqule ifomula bese uyihlela njalo lapho kuhlolwa okusha, ake sibeke leli nani hhayi ngenombolo evamile, kepha ngosizo lwe-opharetha I-ACCOUNT. Ngakho-ke, setha ikhesa enkundleni "Ubukhulu", bese uqhafaza kukunxantathu, otholakala ngakwesobunxele komugqa wamafomula.

   Uhlu lwezimpawu ezisanda kusetyenziswa. Uma opharetha I-ACCOUNT isetshenziswe nguwe muva nje, kufanele ibe kulolu hlu. Kulokhu, udinga nje uchofoze igama lakhe. Ngokwesimo esiphambene, uma ungayitholi, bese uya ku "Ezinye izinto ...".



4. Kubukeka ngathi sesikujwayele Isici Wizard. Siphinde sithuthele eqenjini "Izibalo". Sikhetha igama lapho "I-ACCOUNT". Chofoza inkinobho "Kulungile".



5. Iwindi lokuphikisana kwesitatimende esingenhla liyavela. Lo msebenzi wenzelwe ukubala inani lamaseli ebangeni elibekiwe eliqukethe amanani ezinombolo. I-syntax yayo imi ngolu hlobo:

   = COUNT (inani1; inani2; ...)

   Iqembu lokuphikisana "Amanani" isixhumanisi esiya ebangeni lapho udinga khona ukubala inani lamaseli agcwele idatha yamanani. Sekukonke, kungaba nokuphikisana okungaba ngu-255, kepha kithi kudingeka eyodwa kuphela.

   Setha isikhombisi esigangeni "Inani1" futhi, ubambe inkinobho yegundane kwesobunxele, khetha ibanga kushidi eliqukethe inani lethu. Ngemuva kwalokho ikheli lakhe lizokhonjiswa endle. Chofoza inkinobho "Kulungile".



6. Ngemuva kwalokho, uhlelo lokusebenza luzokwenza ukubalwa futhi lubonise umphumela esitokisini esikuyo. Esimweni sethu esithile, ifomula ingeyifomu elilandelayo:

   = TRUST.NORM (0.03; 8; ACCOUNT (B2: B13))

   Umphumela wokubala wonke waba 5,011609.



7. Kepha akupheleli lapho. Njengoba sikhumbula, umngcele wesikhathi sokuqiniseka ubalwa ngokungeza nokukhipha kunani eliphakathi lesampula lemiphumela yokubala I-TRUST.NORM. Ngale ndlela, imingcele yangakwesokunxele nangakwesobunxele yesikhathi sokuzethemba ibalwa ngokufanele. Inani eliphakathi lesampula uqobo lingabalwa kusetshenziswa opharetha. INDAWO.

   Lo opharetha wenzelwe ukubala okushiwo yizibalo ezinobubanzi bezinamba ezikhethiwe. Ine-syntax elandelayo elula elula:

   = ISIVIVINYO (inombolo1; inombolo2; ...)

   Ukuxabana "Inombolo" kungaba yinani elihlukile lezibalo, noma isixhumanisi samaseli noma amabanga wonke aqukethe.

   Ngakho-ke, khetha iseli lapho kuzobukelwa khona isibalo senani eliphakathi, bese uchofoza inkinobho "Faka umsebenzi".



8. Iyavula Isici Wizard. Ukubuyela esigabeni "Izibalo" bese ukhetha igama ohlwini I-SRZNACH. Njengasikhathi sonke, chofoza inkinobho "Kulungile".



9. Iwindi lokuphikisana liyaqala. Setha isikhombisi esigangeni "Inombolo1" futhi inkinobho yegundane kwesobunxele icindezelwe, khetha lonke uhla lwamanani. Ngemuva kokuthi izixhumanisi zikhonjiswe endle, chofoza inkinobho "Kulungile".



10. Emva kwalokho INDAWO ibonisa umphumela wokubala entweni eshidi.



11. Sibala umngcele ofanele wesikhathi sokuzethemba. Ukuze wenze lokhu, khetha iseli elihlukile, faka uphawu "=" futhi wengeze okuqukethwe yizicucu zeshidi lapho kutholakala khona imiphumela yokubala komsebenzi INDAWO futhi I-TRUST.NORM. Ukuze wenze ukubala, chofoza inkinobho Ngena. Kithi, ifomula elandelayo yatholakala:

    = F2 + A16

    Umphumela wokubala: 6,953276



12. Ngendlela efanayo, sibala umngcele wesokunxele wesikhathi sokuzethemba, kuphela kulokhu kusuka kumphumela wokubala INDAWO khipha umphumela wokubala opharetha I-TRUST.NORM. Kuyavela ifomula yesibonelo sethu sohlobo olulandelayo:

    = F2-A16

    Umphumela wokubala: -3,06994



13. Sizame ukuchaza ngokuningiliziwe zonke izinyathelo zokubala isikhathi sokuzethemba, ngakho-ke sachaza imininingwane ngayinye. Kepha ungahlanganisa zonke izenzo kwifomula eyodwa. Ukubalwa komngcele ofanele wesikhathi sokuzithemba kungabhalwa kanjena:

    = ISIVIVINYO (B2: B13) + TRUST.NORM (0.03; 8; ACCOUNT (B2: B13))



14. Ukubalwa okufanayo komngcele wangakwesokunxele kuzobukeka kanjena:

    = ISIVIVINYO (B2: B13) - TRUST.NORM (0.03; 8; ACCOUNT (B2: B13))

Indlela yesi-2: Umsebenzi WOKUFUNDA KWESITOLO

Ngaphezu kwalokho, ku-Excel kukhona omunye umsebenzi ohambisana nokubalwa kwesikhathi sokuzethemba - QHAWULA. Kuvele kuphela kusukela ku-Excel 2010. Lo opharetha ubala isikhathi sokuzethemba sabantu esebenzisa ukusatshalaliswa kwabafundi. Kuyinto elula kakhulu ukuyisebenzisa lapho umehluko futhi, ngokufanelekile, ukuphambuka okujwayelekile kungaziwa. I-syntax yokusebenza kanjengale elandelayo:

= IQHAWU LOKUFUNDA (alpha; standard_off; size)

Njengoba ubona, amagama abasebenza kule ndaba ahlala engashintshiwe.

Ake sibheke ukuthi singabalwa kanjani imingcele yesikhathi sokuzethemba ngokuphambuka okujwayelekile okungaziwa kusetshenziswa isibonelo seqoqo elifanayo esilicabangele indlela yangaphambilini. Izinga lokuzethemba, njengesikhathi sokugcina, lingama-97%.

1. Khetha iseli okuzokwenziwa sibalo kulo. Chofoza inkinobho "Faka umsebenzi".



2. Kuvuliwe Umsebenzi wizadi iya esigabeni "Izibalo". Khetha igama DOVERIT.STUDENT. Chofoza inkinobho "Kulungile".



3. Iwindi lokuphikisana lo opharetha ochaziwe liqalisiwe.

   Ensimini U-Alfa, ngokubheka ukuthi izinga lokuqiniseka lingama-97%, sibhala inombolo 0,03. Okwesibili ngeke sigxile emigomeni yokubala le pharamitha.

   Ngemuva kwalokho, setha ikhesa enkundleni "Ukuphambuka okujwayelekile". Okwamanje lesi sikhombisi asaziwa kithi futhi sidinga ukubalwa. Lokhu kwenziwa kusetshenziswa umsebenzi okhethekile - STANDOTLON.V. Ukuze uvule iwindi lalesi opharetha, chofoza unxantathu ongakwesobunxele webha lefomula. Uma uhlu lungatholi igama olifunayo, bese uya kulo "Ezinye izinto ...".



4. Kuqala Isici Wizard. Siqhubekela esigabeni "Izibalo" bese umaka igama kuyo STANDOTKLON.V. Bese uchofoza inkinobho "Kulungile".



5. Iwindi lokuphikisana liyavula. Umsebenzi we-opharetha STANDOTLON.V ukucaciswa kokuphambuka okujwayelekile kwesampula. I-syntax yayo ibukeka kanjena:

   = STD. B (inombolo1; inombolo2; ...)

   Kulula ukuqagela ukuthi impikiswano "Inombolo" ikheli lezinto ezikhethiwe. Uma ukukhethwa kubekwe kuluhlu olulodwa, khona-ke unga, usebenzisa ingxabano eyodwa kuphela, unikeze isixhumanisi kuleli banga.

   Setha isikhombisi esigangeni "Inombolo1" futhi, njengokuhlala kuphethe inkinobho yegundane kwesobunxele, khetha inani labantu. Ngemuva kokuthi izixhumanisi zisensimini, musa ukushesha ukucindezela inkinobho "Kulungile", njengoba umphumela ungalungile. Okokuqala sidinga ukubuyela kuwindi yengxabano yomqhubi QHAWULAukwenza impikiswano yokugcina. Ukuze wenze lokhu, chofoza igama elifanele kubha yefomula.



6. Iwindi lokuphikisana lomsebenzi osuvele laziwa livulwa futhi. Setha isikhombisi esigangeni "Ubukhulu". Futhi, chofoza unxantathu osuvele ujwayelekile kithi ukuya ekukhetheni kwabasebenzayo. Njengoba uqonda, sidinga igama "I-ACCOUNT". Njengoba sisebenzise lo msebenzi ekubaleni ngendlela yangaphambilini, sikhona kulolu hlu, ngakho-ke mane uchofoze kuso. Uma ungayitholi, bese ulandela i-algorithm echazwe ngendlela yokuqala.



7. Wake waba ewindini lokuphikisana I-ACCOUNTfaka isikhombisi esigangeni "Inombolo1" futhi inkinobho yegundane ibanjelwe phansi, sikhetha isethi. Bese uchofoza inkinobho "Kulungile".



8. Ngemuva kwalokho, uhlelo lubala futhi lubonise inani lesikhathi sokuzethemba.



9. Ukunquma imingcele, futhi sidinga ukubala inani eliphakathi lesampula. Kodwa, uma unikezwe ukuthi i-algorithm yokubala isebenzisa ifomula INDAWO okufanayo nangendlela eyedlule, futhi noma umphumela awushintshile, ngeke sigcine kulokhu okwesibili.



10. Ukungeza imiphumela yokubala INDAWO futhi QHAWULA, sithola umngcele ofanele wesikhathi sokuzethemba.



11. Ukukhipha emiphumeleni yokubala ye-opharetha INDAWO umphumela wokubala QHAWULA, sinomngcele wesokunxele wesikhathi sokuzethemba.



12. Uma ukubalwa kubhaliwe kwifomula eyodwa, khona-ke ukubalwa komngcele okuyiwo kithi kuzobukeka kanjena:

    = ISIQINISEKISO (B2: B13) + ISITOLO. ISIFUNDI (0.03; STD CLIP. B (B2: B13); ACCOUNT (B2: B13))



13. Ngokuvumelana nalokho, ifomula yokubala umngcele wesobunxele izobukeka kanjena:

    = ISIVIVINYO (B2: B13) - ISITOLO. ISIFUNDI (0.03; STD CLIP. B (B2: B13); ACCOUNT (B2: B13))

Njengoba ukwazi ukubona, amathuluzi e-Excel angasiza kakhulu ukubalwa kwesikhathi sokuzethemba nemingcele yaso. Ngalezi zinhloso, ama-opharetha ahlukile asetshenziselwa amasampuli okwaziwa ngawo nokungafani kwawo.