Isitatimende se-IF ku-Excel. Konke mayelana nomsebenzisi - isicelo, izibonelo

Isethi yemisebenzi kuhlelo lwe-Excel, yebo, inkulu ngempela. Ikakhulukazi, kungenzeka ukuhlela ukucubungula idatha ngezinga elithile. Obhekene nalokhu, phakathi kwezinye izinto, umsebenzi IF. Kwenza kube nokwenzeka ukusebenzisa cishe noma yimuphi umsebenzi. Yingakho lo opharetha esetshenziswa kaningi kunabanye. Namuhla sizozama ukuchaza ukuthi yenzani nokuthi ingasetshenziswa kanjani.

Umsebenzi we-IF - incazelo kanye nobubanzi

Ukusebenzisa umsebenzi IF umsebenzisi angakwazi ukuyala uhlelo ukuthi luhlole ukuthi iseli elithile lifana nombandela othile. Uma sinesimo lapho sidinga kuphela ukwenza umsebenzi ngaphansi kwaso, khona-ke i-Excel ihlola kuqala, ngemuva kwalokho ikhombisa umphumela wokubala kuseli lapho lo msebenzi ubhalwe khona. Kodwa lokhu kuphela uma lo msebenzi usetshenziswa kanye nomunye umsebenzi. Umsebenzisi ngokwakhe IF ikhiqiza imiphumela emibili:

1. IQINISO. Lokhu kuwukuthi isisho noma iseli ifana nesimiso esithile.
2. AMANGA. Lo opharetha uyaboniswa uma kungekho okufanayo.

I-syntax yefomula imi kanje (ngendlela yendawo yonke): =IF(isimo; [inani uma isimo sihlangatshezwana nayo]; [inani uma isimo singahlangatshezwanga]). Umsebenzi ungahlanganiswa nabanye. Kulokhu, abanye opharetha kufanele babhalwe ezingxoxweni ezihambisanayo. Isibonelo, ungayenza ukuze ihlole ukuthi inombolo ithi phozithivu yini, futhi uma kunjalo, thola incazelo ye-arithmetic. Yebo, kunomsebenzi owodwa owenza okufanayo, kodwa lesi sibonelo sibonisa ngokucacile ukuthi umsebenzi usebenza kanjani. IF. Ngokuqondene nezinhlelo zokusebenza lapho umsebenzi ungasetshenziswa khona IF, bese kuba nenani elikhulu lazo:

1. I-Climatology.
2. Ukuthengisa kanye nebhizinisi.
3. Ukuthengisa.
4. Ukubika.

Njalo njalo. Noma ngabe iyiphi indawo oyishoyo, futhi kuzoba nesicelo salo msebenzi.

Ungawusebenzisa kanjani umsebenzi we-IF ku-Excel - izibonelo

Ake sithathe esinye isibonelo sokuthi singawusebenzisa kanjani umsebenzi IF ku-Excel. Ake sithi sinetafula elinamagama amateku. Ake sithi kunendali enkulu yezicathulo zabesifazane ebiza isaphulelo samaphesenti angu-25 kuzo zonke izinto. Ukuze wenze lokhu kuhlola, kukhona ikholomu ekhethekile ebonisa ubulili lapho i-sneaker ihloselwe khona.

Ngakho-ke, isimo salo msebenzi sizoba ukulingana kobulili kowesifazane. Uma, ngenxa yesheke, kutholakala ukuthi lesi simiso siyiqiniso, khona-ke endaweni lapho le fomula iboniswa khona, udinga ukubhala inani lesaphulelo - 25%. Uma kungamanga, cacisa inani elingu-0, njengoba isaphulelo asinikezwanga kuleli cala.

Kunjalo, ungakwazi ukugcwalisa amaseli adingekayo mathupha. Kodwa lokhu kungathatha isikhathi esikhulu. Ngaphezu kwalokho, isici somuntu, ngenxa yokuthi ukuphrinta okungalungile nokuhlanekezelwa kolwazi kungenzeka, nakho akuzange kukhanselwe. Ikhompyutha ayiwenzi amaphutha. Ngakho-ke, uma inani lolwazi likhulu kakhulu, kungcono ukusebenzisa umsebenzi IF.

Ukuze ufinyelele umgomo obekwe esigabeni sokuqala, kubalulekile ukukhetha iseli lapho inani eliwumphumela lizoboniswa khona bese ubhala ifomula elandelayo: =IF(B2="owesifazane",25%,0). Ake sihlukanise lo msebenzi:

1. I-IF ingu-opharetha ngokuqondile.
2. B2=“owesifazane” umbandela okufanele kuhlangatshezwane nawo.
3. Lokhu kulandelwa inani elizoboniswa uma amateki enziwa abesifazane kanye nenani elikhonjisiwe uma kutholakala ukuthi amateki awabesilisa, awezingane noma amanye angahlangabezani nemibandela eshiwo kungxabano yokuqala.

Ikuphi indawo engcono kakhulu yokubhala le fomula? Ngokuvamile, indawo ingakhethwa ngokunganaki, kodwa kithi, lawa amaseli angaphansi kwesihloko sekholomu ethi “Isaphulelo”.

Kubalulekile ukuthi ungakhohlwa ukubeka = uphawu phambi kwefomula. Uma kungenjalo, i-Excel izoyifunda njengombhalo ongenalutho.

Ngemuva kokuthi ifomula ifakiwe, udinga ukucindezela ukhiye wokufaka, emva kwalokho itafula lizogcwaliswa ngokuzenzakalelayo ngenani elifanele. Kuthebula elingezansi, singabona ukuthi isheke lokuqala lilungile. Uhlelo lwanquma ngokuzenzakalelayo ubulili balawa mateki futhi lwawanikeza isaphulelo sekota yenani. Umphumela ufinyelelwe.

Manje kusasele ukugcwalisa imigqa esele. Ukuze wenze lokhu, awudingi ukukopisha ifomula kuseli ngayinye ngayinye. Kwanele ukuthola isikwele ekhoneni elingezansi kwesokudla, hambisa ikhesa yegundane phezu kwaso, uqiniseke ukuthi isiphenduke isithonjana esiphambene bese uhudulela umaka kumugqa ongezansi kakhulu wetafula. Khona-ke i-Excel izokwenzela yonke into.

Ukusebenzisa Umsebenzi we-IF onemibandela eminingi

Ngaphambilini, icala elilula lokusebenzisa umsebenzi lalicatshangelwa IF, lapho kunenkulumo eyodwa kuphela enengqondo. Kodwa kuthiwani uma udinga ukuhlola iseli ngokumelene nezimo eziningana? Lokhu kungenziwa futhi kusetshenziswa ukusebenza okwakhelwe ngaphakathi kwe-Excel.

Esinye sezimo ezikhethekile zokuhlola izimo eziningana ukuhlola ukuhambisana nesokuqala futhi uma kuvela ukuthi kungamanga, hlola okwesibili, okwesithathu, njalonjalo. Noma, uma inani liyiqiniso, hlola omunye umbandela. Lapha, njengoba umsebenzisi efuna, i-logic yezenzo izocishe ifane. Uma ufunda ngokucophelela okubhalwe ngenhla, kungenzeka ukuthi usuvele uqagele ukuthi ukwenze kanjani. Kodwa ake sengeze ukubonakala okwengeziwe.

Ukwenza lokhu, masenze umsebenzi ube nzima kakhulu. Manje sidinga ukunika isaphulelo ngokukhethekile amateki abesifazane, kodwa kuye ngomdlalo ahloselwe wona, ubukhulu besaphulelo kufanele buhluke. Ifomula ekuboneni kokuqala izoba yinkimbinkimbi kakhulu, kodwa ngokuvamile izowela emcabangweni ofanayo nowangaphambilini: =ЕСЛИ(B2=”мужской”;0; ЕСЛИ(C2=”бег”;20%;10%)).

Okulandelayo, senza izenzo ezifanayo njengasekuqaleni: cindezela u-Enter bese ugcwalisa yonke imigqa elandelayo. Sithola umphumela onjalo.

Isebenza kanjani le fomula? Umsebenzi wokuqala kuqala IF ibheka ukuthi izicathulo zingowesilisa yini. Uma kungenjalo, umsebenzi wesibili wenziwe. IF, okuhlola kuqala ukuthi izicathulo zenzelwe ukugijima. Uma kunjalo, isaphulelo esingu-20% sinikezwa. Uma kungenjalo, isaphulelo singu-10%. Njengoba ubona, eminye imisebenzi ingasetshenziswa njengezimpikiswano zomsebenzi, futhi lokhu kunikeza amathuba engeziwe.

Ungawusebenzisa kanjani umsebenzi we-IF ukuze ugcwalise izimo ezi-2 ngesikhathi esisodwa

Ngaphezu kwalokho, usebenzisa i-Excel, ungabheka ukuhambisana nemibandela emibili ngesikhathi esisodwa. Kulokhu, kusetshenziswa omunye umsebenzi, obizwa ngokuthi И. Lo opharetha onengqondo uhlanganisa izimo ezimbili futhi akakwenzi kumsebenzi kuphela IF. Ingasetshenziswa nakweminye imisebenzi eminingi.

Asibuyele etafuleni lethu. Manje isaphulelo kufanele sibe sikhulu, kodwa sisebenza kuphela ezicathulweni zokugijima zabesifazane. Uma, ngemva kokuhlola, kuvela ukuthi zombili izimo zifinyelelwe, khona-ke inani lesaphulelo lika-30% lizorekhodwa ensimini ethi "Isaphulelo". Uma kutholakala ukuthi okungenani eyodwa yemibandela ayisebenzi, khona-ke isaphulelo asisebenzi kumkhiqizo onjalo. Ifomula kuleli cala izoba: =IF(FUTHI(B2=”owesifazane”;C2=“egijima”);30%;0).

Ngaphezu kwalokho, zonke izenzo zenziwe ziphinda izibonelo ezimbili zangaphambilini. Okokuqala, sicindezela ukhiye wokungena, bese sihudula inani kuwo wonke amanye amaseli akuleli thebula.

I-syntax yomsebenzi we-AND, njengoba sibona, iqukethe izimpikiswano ezimbalwa. Esokuqala isimo sokuqala, esesibili singesesibili, njalo njalo. Ungasebenzisa izimpikiswano ezingaphezu kwezimbili futhi uhlole izimo eziningi ngesikhathi esisodwa. Kodwa empeleni, izimo ezinjalo azivamile ukwenzeka. Izimo ezingaphezu kwezintathu ngesikhathi esisodwa - cishe akwenzeki. Ukulandelana kwezenzo ezenziwa yilo msebenzi limi kanje:

1. Okokuqala, ifomula ihlola isimo sokuqala - ukuthi izicathulo ezabesifazane yini.
2. I-Excel ibese ihlaziya umbandela wesibili - ukuthi izicathulo zenzelwe ukugijima yini.
3. Uma, njengomphumela wokuhlolwa, kuvela ukuthi zombili imibandela zibuyisela inani TRUE, bese kuba umphumela womsebenzi IF kuvele kuyiqiniso. Ngakho-ke, isenzo esihlelwe ku-agumenti ehambisanayo siyenziwa.
4. Uma kuvela ukuthi okungenani isheke elilodwa libuyisela umphumela AMANGA, yona kanye nomsebenzi И izobuyisela lo mphumela. Ngakho-ke, umphumela obhalwe ku-agumenti yesithathu yomsebenzi uzoboniswa IF.

Njengoba ubona, i-logic yezenzo ilula kakhulu futhi kulula ukuyiqonda ezingeni elinembile.

NOMA u-opharetha ku-Excel

Umsebenzisi we-OR usebenza ngendlela efanayo futhi une-syntax efanayo. Kodwa uhlobo lokuqinisekisa luhluke kancane. Lo msebenzi ubuyisela inani TRUE uma okungenani isheke elilodwa libuyisela umphumela TRUE. Uma wonke amasheke enikeza umphumela ongamanga, khona-ke, ngokufanele, umsebenzi OR ibuyisela inani AMANGA.

Ngokuvumelana nalokho, uma umsebenzi OR  ibuyisela umphumela TRUE okungenani okukodwa kwamanani, bese kuba umsebenzi IF izobhala inani elishiwo ku-agumenti yesibili. Futhi kuphela uma inani lingahlangabezani nazo zonke imibandela, umbhalo noma inombolo eshiwo ku-agumenti yesithathu yalo msebenzi iyabuyiselwa.

Ukukhombisa lesi simiso ngokusebenza, ake sisebenzise isibonelo futhi. Inkinga manje yilokhu okulandelayo: isaphulelo sinikezwa izicathulo zamadoda noma izicathulo zethenisi. Kulokhu, isaphulelo sizoba ngu-35%. Uma izicathulo zingezabesifazane noma zenzelwe ukugijima, khona-ke ngeke kube khona isaphulelo sesihloko esinjalo.

Ukuze ufinyelele umgomo onjalo, udinga ukubhala ifomula elandelayo esitokisini, etholakala ngokuqondile ngaphansi kombhalo othi "Isaphulelo": =IF(NOMA(B2="owesifazane"; C2="egijima");0;35%). Ngemuva kokuthi sicindezele ukhiye wokufaka bese sihudulela le fomula kuwo wonke amaseli, sithola umphumela olandelayo.

Ungawuchaza kanjani umsebenzi we-IF usebenzisa I-Formula Builder

Yiqiniso, ngokwezinga elithile, ukubhala ifomula ngesandla kulula nakakhulu kunokusebenzisa amanye amathuluzi. Kodwa uma usaqala, khona-ke isimo sishintsha kakhulu. Ukuze ungadideki ekufakeni izimpikiswano, kanye nokubonisa igama elilungile lomsebenzi ngamunye, kunethuluzi elikhethekile elibizwa ngokuthi i-Function Entry Wizard noma i-Formula Builder. Ake sibheke indlela enemininingwane yomsebenzi wayo. Ake sithi sinikezwe umsebenzi ngabaphathi wokuhlaziya uhla olutholakalayo lwemikhiqizo futhi sinikeze isaphulelo sika-25% kuwo wonke amateku abesifazane. Ukulandelana kwezenzo kuleli cala kuzoba kanje:

1. Sivula iwizadi yokufaka umsebenzi ngokuchofoza inkinobho ehambisanayo kuthebhu Amafomula (igqanyiswe ngonxande obomvu kusithombe-skrini).
2. Okulandelayo, iphaneli yomakhi wefomula encane iyavula, lapho sikhetha khona umsebenzi esiwudingayo. Ingakhethwa ngokuqondile ohlwini noma iseshwe endaweni yokusesha. Sesivele sinayo ohlwini lwezi-10 zalezo ezisanda kusetshenziswa, ngakho-ke siyachofoza bese sichofoza inkinobho ethi "Faka Umsebenzi".
3. Ngemuva kwalokho, iwindi lokusetha izimpikiswano zomsebenzi lizovuleka phambi kwamehlo ethu. Phansi kwaleli phaneli, ungabona futhi ukuthi umsebenzi okhethiwe wenzani. Impikiswano ngayinye isayiniwe, ngakho-ke akudingeki ukhumbule ukulandelana. Siqala ngokufaka isisho esinengqondo esihlanganisa inombolo noma iseli, kanye nenani okufanele silihlole ukuthi liyahambisana yini nalo. Okulandelayo, amanani afakwa uma kuyiqiniso futhi inani uma kungamanga.
4. Ngemuva kokuthi zonke izinyathelo seziqediwe, chofoza inkinobho ethi "Qeda".

Manje sithola umphumela. Ngayo, senza izenzo ezifanayo njengasesikhathini sangaphambilini, okungukuthi, sikhomba igundane esigcawini ekhoneni elingezansi kwesokudla bese sihudula ifomula kuwo wonke amaseli asele. Ngakho umsebenzi IF iyi-opharetha ethandwa kakhulu futhi ebaluleke kakhulu phakathi kwazo zonke ezikhona. Ihlola idatha iqhathaniswa nemibandela ethile futhi yenze izenzo ezifanele uma isheke libuyisela umphumela. TRUE or AMANGA. Lokhu kukuvumela ukuthi wenze lula kakhulu ukucutshungulwa kwedatha enkulu futhi ungenzi inani elikhulu lezenzo, unikeze lo msebenzi ongcolile kukhompyutha.