IF umsebenzi ku-Excel. Izibonelo (nezimo eziningi)

Enye yezinzuzo eziyinhloko zamaspredishithi e-Excel yikhono lokuhlela ukusebenza kombhalo othile. Njengoba abantu abaningi bazi ezifundweni zesayensi yekhompiyutha yesikole, enye yezinto ezibalulekile ezikuvumela ukuthi usebenzise lokhu ngama-opharetha anengqondo. Omunye wabo u-opharetha we-IF, ohlinzeka ngokwenziwa kwezenzo ezithile lapho izimo ezithile zifinyelelwa. 

Isibonelo, uma inani lifana nelinye, ilebula elilodwa liyavezwa kuseli. Uma kungenjalo, kuhlukile. Ake sibheke leli thuluzi elisebenzayo ngokuningiliziwe ekusebenzeni.

Umsebenzi we-IF ku-Excel (ulwazi olujwayelekile)

Noma yiluphi uhlelo, noma luncane, luqukethe ukulandelana kwezenzo, okubizwa ngokuthi i-algorithm. Kungase kubukeke kanje:

1. Hlola ikholomu A yonke ukuze uthole izinombolo ezilinganayo.
2. Uma kutholwa inombolo elinganayo, engeza amanani afana nalawa.
3. Uma inombolo elinganayo ingatholakali, bonisa umbhalo othi "awutholakali".
4. Hlola ukuthi inombolo ewumphumela ilingana yini. 
5. Uma uthi yebo, yengeze kuzo zonke izinombolo ezilinganayo ezikhethwe esigabeni soku-1.

Futhi ngisho noma lokhu kuyisimo sokucatshangelwa kuphela, okungenakwenzeka ukuthi sidingeke empilweni yangempela, ukwenziwa kwanoma yimuphi umsebenzi kusho ukuthi kukhona i-algorithm efanayo. Ngaphambi kokusebenzisa uhlelo IF, udinga ukuba nombono ocacile ekhanda lakho mayelana nokuthi yimuphi umphumela ofuna ukuwufinyelela. 

I-syntax yomsebenzi we-IF onesimo esisodwa

Noma yimuphi umsebenzi ku-Excel wenziwa kusetshenziswa ifomula. Iphethini idatha okufanele idluliselwe ngayo emsebenzini ibizwa ngokuthi i-syntax. Endabeni yomqhubi IF, ifomula izoba ngale fomethi.

=IF (inkulumo_enengqondo, inani_uma_iqiniso, inani_uma_amanga)

Ake sibheke i-syntax ngokuningiliziwe:

1. Isisho esiphuphile. Lesi yisimo ngokwaso, ukuthobela noma ukungathobeli okuhlolwa yi-Excel. Kokubili imininingwane yezinombolo neyombhalo ingabhekwa.
2. Inani_uma_iqiniso. Umphumela ozoboniswa kuseli uma idatha ehlolwayo ihlangabezana nemibandela eshiwo.
3. inani_uma_lingamanga. Umphumela oboniswa kuseli uma idatha ehlolwayo ingahambisani nesimo.

Nasi isibonelo sokucaca.

Lapha umsebenzi uqhathanisa iseli A1 nenombolo 20. Lesi isigaba sokuqala se-syntax. Uma okuqukethwe kukukhulu kunaleli nani, inani “likhulu kuno-20” liboniswa kuseli lapho ifomula ibhalwe khona. Uma isimo singahambisani nalesi simo - "ngaphansi noma ukulingana no-20".

Uma ufuna ukuveza inani lombhalo kuseli, kufanele ulivale ezimpawu zokucaphuna.

Nasi esinye isimo. Ukuze bafanelekele ukuthatha iseshini yokuhlolwa, abafundi kufanele baphumelele iseshini yokuhlola. Abafundi bakwazile ukuwina amakhredithi kuzo zonke izifundo, futhi manje sekusele esokugcina, okugcine sekuba yimpumelelo. Umsebenzi wethu wukunquma ukuthi yibaphi abafundi abangeniswa ezivivinyweni nokuthi yibaphi abangamukelwa.

Njengoba sifuna ukuhlola umbhalo hhayi inombolo, impikiswano yokuqala ithi B2=""cons".

I-IF Function Syntax eneZimo Eziningi

Ngokuvamile, umbandela owodwa ayanele ukuhlola inani eliphikisana nawo. Uma udinga ukucabangela inketho engaphezu kweyodwa, ungafaka imisebenzi IF omunye komunye. Kuzoba nemisebenzi embalwa esidlekeni.

Ukwenza kucace kakhudlwana, nansi i-syntax.

=IF(inkulumo_enengqondo, inani_uma_iqiniso, IF(inkulumo_enengqondo, inani_uma_iqiniso, inani_uma_amanga))

Kulokhu, umsebenzi uzohlola imibandela emibili ngesikhathi esisodwa. Uma isimo sokuqala siyiqiniso, inani elitholwe ngenxa yokusebenza ku-agumenti yokuqala liyabuyiswa. Uma kungenjalo, umbandela wesibili uhlolelwa ukuhambisana nawo.

Nasi isibonelo.

Futhi ngosizo lwefomula enjalo (eboniswe kusithombe-skrini esingezansi), ungakwazi ukuhlaziya ukusebenza komfundi ngamunye.

Njengoba ubona, isimo esisodwa sengeziwe lapha, kodwa isimiso asishintshile. Ngakho ungabheka imibandela embalwa ngesikhathi esisodwa.

Unganweba kanjani ukusebenza kwe-IF usebenzisa KANYE kanye NOMA opharetha

Ngezikhathi ezithile kunesimo sokuhlola ngokushesha ukuhambisana nemibandela embalwa, futhi ungasebenzisi ama-opharetha akhiwe esidlekeni anengqondo, njengasesibonelweni sangaphambilini. Ukuze wenze lokhu, sebenzisa noma yimuphi umsebenzi И noma umsebenzi OR kuya ngokuthi udinga ukuhlangabezana nemibandela embalwa ngesikhathi esisodwa noma okungenani eyodwa yazo. Ake sibhekisise le mibandela.

IF isebenza FUTHI nesimo

Kwesinye isikhathi udinga ukuhlola isisho ngezimo eziningi ngesikhathi esisodwa. Kulokhu, i-AND function isetshenziswa, ebhalwe kumpikiswano yokuqala yomsebenzi IF. Isebenza kanje: uma u-a elingana noyedwa futhi u-a elingana no-2, inani liyoba c.

IF umsebenzi onesimo esithi "NOMA".

Umsebenzi othi NOMA usebenza ngendlela efanayo, kodwa kulesi simo, isimo esisodwa kuphela esiyiqiniso. Ngangokunokwenzeka, izimo ezingafika kwezingama-30 zingahlolwa ngale ndlela. 

Nazi ezinye izindlela zokusebenzisa imisebenzi И и OR njengokuphikisana komsebenzi IF.

Ukuqhathanisa idatha kumathebula amabili

Ngezikhathi ezithile kungenzeka ukuqhathanisa amatafula amabili afanayo. Isibonelo, umuntu usebenza njenge-accountant futhi udinga ukuqhathanisa imibiko emibili. Kuneminye imisebenzi efanayo, njengokuqhathanisa izindleko zezimpahla zamaqoqo ahlukene, khona-ke, ukuhlolwa kwabafundi ngezikhathi ezahlukene, njalonjalo.

Ukuze uqhathanise amathebula amabili, sebenzisa umsebenzi COUNTIF. Ake sikubheke kabanzi.

Ake sithi sinamathebula amabili aqukethe imininingwane yamaphrosesa amabili okudla. Futhi sidinga ukuwaqhathanisa, futhi sigqamise umehluko ngombala. Lokhu kungenziwa ngokusebenzisa ukufometha okunemibandela kanye nomsebenzi COUNTIF. 

Itafula lethu libukeka kanje.

Sikhetha ububanzi obuhambisana nezici zobuchwepheshe zephrosesa yokuqala yokudla.

Ngemva kwalokho, chofoza amamenyu alandelayo: Ukufometha okunemibandela - dala umthetho - sebenzisa ifomula ukuze unqume amaseli afomethiwe.

Ngendlela yefomula yokufometha, sibhala umsebenzi =COUNTIF (ububanzi obuzoqhathaniswa; iseli lokuqala lethebula lokuqala)=0. Ithebula elinezici zephrosesa yokudla yesibili lisetshenziswa njengebanga lokuqhathanisa.

Udinga ukwenza isiqiniseko sokuthi amakheli aphelele (nophawu lwedola phambi komugqa namagama ekholomu). Engeza =0 ngemva kwefomula ukuze i-Excel ibheke amanani aqondile.

Ngemuva kwalokho, udinga ukusetha ukufometha kwamaseli. Ukuze wenze lokhu, eduze kwesampula, udinga uchofoze inkinobho ethi "Fometha". Esimweni sethu, sisebenzisa ukugcwaliswa, ngoba kungcono kakhulu kule njongo. Kodwa ungakhetha noma yikuphi ukufometha okufunayo.

Sinikeze igama lekholomu njengobubanzi. Lokhu kulula kakhulu kunokungena ebangeni mathupha.

Umsebenzi we-SUMIF ku-Excel

Manje ake siqhubekele emisebenzini IF, okuzosiza ukushintsha amaphuzu amabili we-algorithm ngesikhathi esisodwa. Esokuqala sithi SUMMESLEY, okwengeza izinombolo ezimbili ezihlangabezana nesimo esithile. Isibonelo, sibhekene nomsebenzi wokunquma ukuthi yimalini okufanele ikhokhwe ngenyanga kubo bonke abadayisi. Kulokhu kuyadingeka.

1. Engeza umugqa nesamba semali engenayo yabo bonke abathengisi bese uchofoza iseli elizoqukatha umphumela ngemva kokufaka ifomula. 
2. Sithola inkinobho ye-fx, etholakala eduze komugqa wamafomula. Okulandelayo, iwindi lizovela lapho ungathola khona umsebenzi odingekayo ngokusesha. Ngemva kokukhetha opharetha, udinga uchofoze inkinobho "OK". Kodwa okokufaka okwenziwa ngesandla kuhlala kwenzeka.
   

   

3. Okulandelayo, iwindi lokufaka ama-agumenti omsebenzi lizovela. Wonke amanani angacaciswa ezinkambini ezihambisanayo, futhi ububanzi bungafakwa ngenkinobho eseduze kwawo.
   

   

4. Ukuphikisana kokuqala ububanzi. Lapha ufaka amaseli ofuna ukuwahlola ukuthi ayayithobela yini imigomo. Uma sikhuluma ngathi lezi yizikhundla zabasebenzi. Faka ububanzi D4:D18. Noma vele ukhethe amaseli owathandayo.
5. Kunkambu ethi "Imibandela", faka indawo. Endabeni yethu - "umdayisi". Njengohlu lokuhlanganisa, sibonisa lawo maseli lapho amaholo ezisebenzi abhalwe khona (lokhu kwenziwa kokubili mathupha bese siwakhetha ngegundane). Chofoza okuthi “KULUNGILE”, futhi sithola amaholo aqediwe abaliwe abo bonke abasebenzi abangabathengisi.

Vumelana ukuthi kulula kakhulu. Akuyona?

Umsebenzi we-SUMIFS ku-Excel

Lo msebenzi ikuvumela ukuthi unqume isamba samanani ahlangabezana nemibandela eminingi. Ngokwesibonelo, sanikezwa umsebenzi wokunquma ingqikithi yeholo labo bonke abaphathi abasebenza egatsheni eliseningizimu yenkampani.

Engeza umugqa lapho umphumela wokugcina uzoba khona, bese ufaka ifomula kuseli oyifunayo. Ukuze wenze lokhu, chofoza isithonjana somsebenzi. Iwindi lizovela lapho udinga ukuthola khona umsebenzi I-SUMMESLIMN. Okulandelayo, ikhethe ohlwini futhi iwindi elijwayelekile elinama-agumenti liyavuleka. Kodwa inani lalezi zingxabano manje selihlukile. Le fomula yenza kube nokwenzeka ukusebenzisa inombolo engapheli yemibandela, kodwa inani elincane lama-agumenti liyisihlanu. 

Okuhlanu kuphela okungacaciswa ngengxoxo yokufaka ye-agumenti. Uma udinga imibandela eyengeziwe, kuzodingeka ukuthi ifakwe ngesandla ngokuvumelana nengqondo efanayo neyesibili yokuqala.

Ake sibheke izimpikiswano eziyinhloko ngokuningiliziwe:

1. Ibanga lesifinyezo. Amaseli azofingqwa.
2. Ububanzi besimo 1 - ububanzi obuzohlolelwa ukuthobela umbandela othile. 
3. Isimo 1 yisona simo ngokwaso.
4. Ibanga lombandela 2 ububanzi besibili obuzohlolwa ngokuqhathaniswa nombandela.
5. Isimo 2 yisimo sesibili.

Ukuqonda okwengeziwe kuyafana. Ngenxa yalokho, sanquma amaholo abo bonke abaphathi beGatsha eliseNingizimu.

COUNTIF umsebenzi ku-Excel

Uma udinga ukunquma ukuthi mangaki amaseli awela ngaphansi kwesimo esithile, sebenzisa umsebenzi COUNTIF. Ake sithi sidinga ukuqonda ukuthi bangaki abathengisi abasebenza kule nhlangano:

1. Okokuqala, engeza umugqa oqukethe inombolo yabathengisi. Ngemuva kwalokho, udinga ukuchofoza kuseli lapho umphumela uzoboniswa khona.
2. Ngemuva kwalokho, udinga ukuchofoza inkinobho ethi "Faka Umsebenzi", engatholakala kuthebhu "Amafomula". Kuzovela iwindi elinohlu lwezigaba. Sidinga ukukhetha into ethi "Uhlu olugcwele lwama-alfabhethi". Ohlwini, sinentshisekelo kufomula COUNTIF. Ngemuva kokuyikhetha, sidinga ukuchofoza inkinobho ethi "OK".
   

   

3. Ngemuva kwalokho, sinenani labathengisi abaqashwe kule nhlangano. Yatholwa ngokubala inani lamaseli lapho kubhalwe khona igama elithi “umdayisi”. Konke kulula. 

COUNTSLIM umsebenzi ku-Excel

Kufana nefomula I-SUMMESLIMN, le fomula ibala inani lamaseli afana nezimo eziningi. I-syntax iyafana kodwa ihluke kancane kufomula I-SUMMESLIMN:

1. Ububanzi besimo 1. Lobu ububanzi obuzohlolwa ngombandela wokuqala.
2. Umbandela 1. Ngokuqondile umbandela wokuqala.
3. Ububanzi Besimo 2. Lobu ububanzi obuzohlolwa ngombandela wesibili. 
4. Umbandela 2.
5. Izimo zobubanzi 3.

Njalo njalo.

Ngakho umsebenzi IF ku-Excel – akusiyo yodwa, kunezinhlobonhlobo zayo eziningi ezenza ngokuzenzakalelayo izenzo ezivame kakhulu, okwenza impilo yomuntu ibe lula. 

Ikakhulukazi ngenxa yomsebenzi IF Amaspredishithi e-Excel athathwa njengokuhleleka. Kungaphezu nje kokubala okulula. Uma ucabanga ngakho, ke umsebenzi IF iyisisekelo kunoma yiluphi uhlobo lwezinhlelo.

Ngakho-ke uma ufunda ukusebenza ngenani elikhulu ledatha ku-Excel, kuzoba lula kakhulu ukufunda ukuhlela. Ngenxa yama-opharetha anengqondo, lezi zindawo zinokuningi okufanayo, nakuba i-Excel ivame ukusetshenziswa ngababalimali. Kodwa indlela yokusebenza ngedatha iyafana kakhulu. 

Umsebenzi ezandleni ezifanele IF futhi ukuhlukahluka kwalo kukuvumela ukuthi uguqule ishidi le-Excel libe uhlelo olugcwele olungakwazi ukusebenza kuma-algorithms ayinkimbinkimbi. Ukuqonda ukuthi umsebenzi usebenza kanjani IF isinyathelo sokuqala sokufunda amamakhro - isinyathelo esilandelayo emsebenzini ovumelana nezimo namaspredishithi. Kodwa lokhu sekuvele kuyizinga lochwepheshe.