Imisebenzi ye-Boolean ku-Excel. Konke mayelana nokusebenzisa imisebenzi enengqondo ku-Excel

I-Excel wuhlelo olusebenza ngendlela emangalisayo olukuvumela ukuthi ungagcini nje ngokuqopha idatha ngendlela yethebula, kodwa futhi wenze ngokuzenzakalelayo ukucutshungulwa kwayo. Imisebenzi enengqondo iyisici esiyinhloko esikuvumela ukuthi wenze noma yimiphi imisebenzi yalolu hlobo. Asetshenziswa kumafomula nakweminye imisebenzi ukuze enze yonke imisebenzi ibe lula.

Zenzelwe ukuhlola ukuthi amanani ayahlangabezana yini nemibandela eshiwo. Uma kukhona okufanayo, kuseli lapho kubhalwe khona, kufakwa inani elithi “TRUE”, uma kwenzeka kuba nomehluko – “FALSE”. Namuhla sizocabangela ngokuningiliziwe izindaba ezifana nesakhiwo semisebenzi enengqondo, ububanzi bokusetshenziswa kwabo.

Uhlu Lwemisebenzi ye-Boolean ku-Excel

Kunenombolo enkulu yemisebenzi enengqondo, kodwa evame ukusetshenziswa yilena elandelayo:

1. TRUE
2. AMANGA
3. IF
4. IFERROR
5. OR
6. И
7. HHAYI
8. EOSHIBKA
9. ISBLANK

Zonke zingasetshenziswa ukwakha izakhiwo eziyinkimbinkimbi futhi ucacise imibandela yanoma iyiphi i-oda. Cishe yonke le misebenzi ihlanganisa ukudlulisa amapharamitha athile kuyo. Okuhlukile kuphela IQINISO kanye NAMANGA, ezibuya ngokwazo. Izinombolo, umbhalo, izinkomba zamaseli, ububanzi, nokunye kuvame ukusetshenziswa njengamapharamitha. Ake sibheke bonke opharetha abangenhla.

Abasebenzisi IQINISO futhi AMANGA

Okufanayo yomibili le misebenzi ukuthi ibuyisela inani elilodwa kuphela. Ububanzi bokusetshenziswa kwabo ukusetshenziswa njengengxenye yeminye imisebenzi. Njengoba kungaqondwa egameni labaqhubi, imisebenzi TRUE и AMANGA buyisela amanani TRUE и AMANGA ngokulandelana.

HHAYI u-opharetha

Lo msebenzi usetshenziswa ne-agumenti eyodwa futhi ubhala inani eliphambene neseli. Uma udlula lo opharetha TRUE, khona-ke izobuya AMANGA futhi, ngokufanelekile, ukugomela okuphambene kuyiqiniso. Ngakho-ke, umphumela wokucubungula idatha yilo opharetha uncike ngokuphelele ekutheni yiziphi imingcele okufanele zidlule kuyo.

I-syntax yalo opharetha imi kanje: =HHAYI(iqiniso noma amanga).

Ama-opharetha KANYE kanye NOMA

Laba opharetha ababili bayadingeka ukuze kudluliselwe ukuhlobana kwemibandela yesisho komunye nomunye. Umsebenzi И isetshenziselwa ukukhombisa ukuthi imibandela emibili kufanele ifane nenombolo efanayo noma umbhalo ngesikhathi esisodwa. Lo msebenzi ubuyisela inani TRUE kuphela ngaphansi kombandela wokuthi zonke izimo zikhiqiza leli nani ngesikhathi esisodwa. Uma okungenani umbandela owodwa wehluleka, ukulandelana konke kubuyisela inani AMANGA.

Indlela i-AND opharetha eyakhelwe ngayo ilula kakhulu: =Futhi(ingxabano1; ingxabano2; …). Inombolo enkulu yama-agumenti engasetshenziswa yilo msebenzi ngu-255. I-syntax yesisebenzisi OR ziyefana, kodwa indlela yokusebenza ihluke kancane. Uma eyodwa yohlu lwemisebenzi ikhiqiza umphumela TRUE, khona-ke le nombolo izobuyiselwa njengokulandelana okuphelele okunengqondo.

IF kanye nezitatimende ze-ISERROR

Le misebenzi emibili inenjongo ebaluleke kakhulu - ibeka ngokuqondile umbandela wokuhambisana okufanele isisho esithile sihlolwe ngaso. Ukuze uthole ukuqonda okujulile kokuthi u-opharetha usebenza kanjani IFERROR, kufanele uqale uchaze umsebenzi IF. Ukwakheka kwayo okujwayelekile kuyinkimbinkimbi kakhulu kunangaphambili: =IF(inkulumo_enengqondo, inani_uma_iqiniso, inani_uma_amanga).

Umsebenzi walo opharetha ukudala izakhiwo eziyinkimbinkimbi kakhulu. Ihlola ukuthi imibandela iyafinyelelwa yini. Uma kunjalo, u-opharetha uzobuya TRUE, uma kungenjalo - AMANGA. Kodwa opharetha ngokuvamile isetshenziswa ngokubambisana nabanye. Isibonelo, uma isetshenziswa njengokuphikisana komsebenzi HHAYI, bese, ngokufanelekile, inani lizothathelwa indawo ngokuphambene. Okusho ukuthi, uma kukhona okufanayo kumbandela, inani lizobuyiselwa AMANGA. Lena inzuzo eyinhloko yemisebenzi ye-logic: ingahlanganiswa ngezindlela ezingavamile kakhulu.

Ngaphezu kwalokho, uhlelo luba nzima nakakhulu. Uma ngalo mbandela sithola umphumela othi “TRUE”, ungacacisa umbhalo, inombolo ezoboniswa noma umsebenzi ozobalwa. Ngokufanayo, ungasetha umphumela ozoboniswa uma umphumela ubuyiswe ngemva kokucubungula idatha. AMANGA.

Isakhiwo se-opharetha IFERROR zifana kakhulu, kodwa zihluke kancane. Iqukethe izimpikiswano ezimbili ezidingekayo:

1. Incazelo. Yinkulumo ngokwayo evivinywayo. Uma kuvela ukuthi kuyiqiniso, khona-ke lelo nani liyabuyiselwa.
2. Inani uma iphutha. Lona umbhalo, inombolo, noma umsebenzi ozovezwa noma usetshenziswe uma umphumela wokuhlola impikiswano yokuqala uthi AMANGA.

I-Syntax: =IFERROR(inani;inani_uma_iphutha).

ISERROW kanye nabaqhubi be-ISEMPLAND

Umsebenzi wokuqala walokhu okungenhla uqukethe inani elilodwa kuphela futhi une-syntax elandelayo: =ISERROR(inani). Umsebenzi walo opharetha ukuhlola ukuthi amaseli agcwaliswe kahle kangakanani (eyodwa noma kulo lonke uhla). Uma kuvela ukuthi i-padding ibingalungile, ibuyisela umphumela weqiniso. Uma konke kuhle - amanga. Ingasetshenziswa ngokuqondile njengombandela womunye umsebenzi.

I-Excel ingabheka izixhumanisi ngezinhlobo zamaphutha ezilandelayo:

• #IGAMA?;
• #N/A;
• #DEL/0!;
• #INOMBOLO!;
• #SO;
• #AKUNALUTHO!;
• #XHUMANA!.

Umsebenzi ISBLANK Sekukonke, ilula ngendlela emangalisayo. Iqukethe ipharamitha eyodwa kuphela, okuyiseli/ububanzi okufanele buhlolwe. Uma kukhona iseli elingenawo umbhalo, noma izinombolo, noma izinhlamvu ezingaphrintiwe, umphumela uyabuyiswa TRUE. Ngokufanelekile, uma kukhona idatha kuwo wonke amaseli obubanzi, umsebenzisi uthola umphumela AMANGA.

Ithebula lememo “Imisebenzi enengqondo ku-Excel”

Ukufingqa yonke into echazwe ngenhla, ake sinikeze ithebula elincane eliqukethe ulwazi mayelana nayo yonke imisebenzi enengqondo evame ukusetshenziswa.

Imisebenzi enengqondo nezibonelo zokuxazulula izinkinga

Imisebenzi enengqondo yenza kube nokwenzeka ukuxazulula imisebenzi ehlukahlukene, kuhlanganise eyinkimbinkimbi. Ake sinikeze izibonelo zendlela asebenza ngayo.

Umsebenzi 1. Ake sithi sinengxenye yezimpahla ezisele ngemva kwesikhathi esithile sokuthengisa. Kumele ihlolwe kabusha ngokuvumelana nemithetho elandelayo: uma kwakungenakwenzeka ukuyithengisa ezinyangeni ezingu-8, hlukanisa intengo yayo izikhathi ezingu-2. Okokuqala, masidale ububanzi obuchaza idatha yokuqala. Kubukeka kanje.

Ukuze umsebenzi ochaziwe uxazululwe ngempumelelo, udinga ukusebenzisa umsebenzi olandelayo.

Ungayibona kubha yefomula kusithombe-skrini. Manje ake senze izincazelo ezithile. Inkulumo enengqondo eboniswe kusithombe-skrini (okungukuthi, C2>=8) isho ukuthi umkhiqizo kufanele ube sesitokweni isikhathi esingafika ezinyangeni eziyi-8 sihlanganisiwe. Sisebenzisa ama-opharetha we->= arithmetic, sichaza okukhulu noma okulingana nomthetho. Ngemva kokubhala lo mbandela, umsebenzi uzobuyisela inani elilodwa kwamabili: “TRUE” noma “FALSE”. Uma ifomula ihlangabezana nombandela, khona-ke inani ngemva kokubuyekezwa libhalelwa iseli (kahle, noma lidluliselwe njengengxabano komunye umsebenzi, konke kuncike kumingcele ebekwe ngumsebenzisi), ihlukaniswe kabili (kulokhu, sihlukanise intengo ngesikhathi sokuthola esitolo ngamabili) . Uma ngemva kwalokho kutholakala ukuthi umkhiqizo ubusesitokweni isikhathi esingaphansi kwezinyanga ezingu-8, kubuyiselwa inani elifanayo eliqukethwe kuseli.

Manje masenze umsebenzi ube nzima kakhulu. Sisebenzisa umbandela: isikali sezaphulelo kufanele siqhubeke. Kalula nje, uma izimpahla zilele isikhathi esingaphezu kwezinyanga ezingu-5, kodwa ngaphansi kuka-8, intengo kufanele ihlukaniswe izikhathi ezine nengxenye. Uma zingaphezu kuka-8, zimbili. Ukuze le fomula ihambisane nenani, kufanele ibe kanje. Bheka isithombe-skrini kubha yefomula ukuze usibone.

Kubalulekile! Njengama-agumenti, kuvunyelwe ukusebenzisa izinombolo kuphela, kodwa namanani ombhalo. Ngakho-ke kuvunyelwe ukusetha imibandela yokuhleleka okuhluke kakhulu. Isibonelo, ukwenza isaphulelo ezimpahleni ezitholwe ngoJanuwari futhi ungakwenzi uma zifika ngo-Ephreli.

Umsebenzi 2. Masisebenzise lo mbandela emkhiqizweni osesitokweni. Ake sithi, uma ngemuva kokumakishwa okwenziwe ngenhla, inani layo selingaphansi kwama-ruble angama-300, noma uma selithengiswa ngaphandle kwezinyanga ezingaphezu kweziyi-10, livele lisuswe ekuthengisweni. Ifomula yile elandelayo.

Ake siyihlaziye. Sisebenzise umsebenzi njengombandela OR. Kudingeka ukuhlinzeka ngemfoloko enjalo. Uma iseli D2 liqukethe inombolo engu-10, khona-ke inani "elicishiwe" lizovezwa ngokuzenzakalelayo emgqeni ohambisanayo wekholomu E. Okufanayo kusebenza kwesinye isimo. Uma kungekho okuhlangatshezwana nayo, iseli elingenalutho livele libuyiswe.

Umsebenzi 3. Ake sithi sinesampula yabafundi abazama ukungena esikoleni esiphakeme. Ukuze benze lokhu, badinga ukuphumelela izivivinyo ezifundweni ezimbalwa, eziboniswe kusithombe-skrini esingezansi. Ukuze bathathwe njengabakufanelekele ukwamukelwa kulesi sikhungo semfundo, kumele bathole amaphuzu ayi-12 esewonke. Ngesikhathi esifanayo, umbandela obalulekile ukuthi amaphuzu kuzibalo akufanele abe ngaphansi kwamaphoyinti ama-4. Umsebenzi uwukwenza ngokuzenzakalelayo ukucutshungulwa kwale datha, kanye nokuhlanganisa umbiko wokuthi yibaphi abafundi abangenile nabangazange. Ukuze senze lokhu, sizokwenza itafula elinjalo.

Ngakho-ke, umsebenzi wethu ukwenza uhlelo lubale ukuthi mangaki amaphuzu azoba khona esewonke, bheka umphumela wokudlula bese wenza isiqhathaniso. Ngemuva kwalokhu kusebenza, umsebenzi kufanele ubeke umphumela kuseli lapho ulingana khona. Kunezindlela ezimbili ongakhetha kuzo: "kwamukelwe" noma "cha". Ukuze wenze lo msebenzi, faka ifomula efanayo (vele uxhume amanani akho): =ЕСЛИ(И(B3>=4;СУММ(B3:D3)>=$B$1);»принят»;»нет»).

Ngomsebenzi we-boolean И singaqinisekisa ukuthi izimo ezimbili ziyahlangatshezwa ngesikhathi esisodwa. Kulokhu, sisebenzise umsebenzi ISUM ukubala amaphuzu aphelele. Njengombandela wokuqala (empikiswano yokuqala yomsebenzi othi AND), sicacise ifomula ethi B3>=4. Le kholomu iqukethe amaphuzu kuzibalo, okungafanele abe ngaphansi kwamaphoyinti angu-4.

Sibona ukusetshenziswa okubanzi komsebenzi IF lapho usebenza namaspredishithi. Kungakho kuwumsebenzi we-logic othandwa kakhulu okudingeka wazi kuqala.

Kunconywa kakhulu ukuzijwayeza eshadini lokuhlola ngaphambi kokusebenzisa lawa makhono emsebenzini wangempela. Lokhu kuzosiza ukonga isikhathi esiningi.

Umsebenzi 4. Sibhekene nomsebenzi wokunquma inani eliphelele lezindleko zezimpahla ngemva kokwehliswa. Isidingo - izindleko zomkhiqizo kumele zibe phezulu noma isilinganiso. Uma lesi simo singahlangatshezwana nayo, izimpahla kufanele zisulwe. Kulesi sibonelo, sizobona ukuthi inqwaba yemisebenzi ye-arithmetic nezibalo isebenza kanjani.

Masisebenzise ithebula esesilidwetshiwe. Ukuze kuxazululwe le nkinga, kuyadingeka ukusetha umthetho njengesimo sokuthi iseli D2 kufanele libe ngaphansi kwencazelo ye-arithmetic yalo lonke uhla lwezimpahla. Uma umthetho uqinisekisiwe, khona-ke kuseli lapho kubhalwe khona le fomula, inani "elicishiwe" liyasethwa. Uma umbandela ungafinyelelwanga, kusho ukuthi inani elingenalutho liyasethwa. Ukuze ubuyisele incazelo ye-arithmetic, kunomsebenzi ISAVERAGE.

Umsebenzi 5. Ake sithi sidinga ukubala isilinganiso sokuthengiswa kwemikhiqizo ehlukene ezitolo ezihlukene zomkhiqizo ofanayo. Masenze itafula elinjalo.

Umsebenzi wethu ukunquma isilinganiso sawo wonke amanani, ahambisana nezici ezithile. Ukuze senze lokhu, sisebenzisa umsebenzi okhethekile owawungekho ohlwini olungenhla. Ikuvumela ukuthi uhlanganise imisebenzi emibili ISAVERAGE и IF. Futhi wafona NGENHLIZIYO. Iqukethe ama-agumenti amathathu:

1. Ububanzi okufanele buhlolwe.
2. Isimo okufanele sibhekwe.
3. Ibanga elimaphakathi.

Ngenxa yalokho, kutholwa ifomula elandelayo (kusithombe-skrini).

Siyabona ukuthi ububanzi bokusetshenziswa kwemisebenzi enengqondo bukhulu kakhulu. Futhi uhlu lwabo empeleni lukhulu kakhulu kunalokho okuchazwe ngenhla. Sisanda kubhala okuthandwa kakhulu kubo, kodwa futhi sachaza isibonelo somunye umsebenzi, okuyinhlanganisela yezibalo nokunengqondo. Kukhona namanye ama-hybrids afanayo afanelwe ukucatshangelwa ngokwehlukana.