I-opharetha ethi “IF” ku-Microsoft Excel: uhlelo lokusebenza nezibonelo

I-Excel, vele, inomsebenzi ocebile kakhulu. Futhi phakathi kwamathuluzi amaningi ahlukene, opharetha "IF" unendawo ekhethekile. Kuyasiza ekuxazululeni imisebenzi ehluke ngokuphelele, futhi abasebenzisi baphendukela kulo msebenzi kaningi kunabanye.

Kulesi sihloko, sizokhuluma ngokuthi u-opharetha "IF", futhi sicabangele ububanzi nemigomo yokusebenza nayo.

Incazelo yomsebenzi othi “IF” kanye nenjongo yawo

I-opharetha ethi “IF” iyithuluzi lohlelo lwe-Excel lokuhlola isimo esithile (inkulumo enengqondo) ukuze isetshenziswe.

Okungukuthi, zicabange sinohlobo oluthile lwesimo. Umsebenzi othi “IF” uwukuhlola ukuthi umbandela onikeziwe uyafinyelelwa yini futhi ukhiphe inani elisuselwe kumphumela wesheke eliya kuseli elinomsebenzi.

1. Uma inkulumo enengqondo (isimo) iyiqiniso, kusho ukuthi inani liyiqiniso.
2. Uma inkulumo enengqondo (isimo) ingahlangatshezwana nayo, inani lingamanga.

Ifomula yomsebenzi ngokwayo kuhlelo yilesi sisho esilandelayo:

=IF(isimo, [inani uma isimo sifinyelelwa], [inani uma isimo singahlangatshezwana])

Ukusebenzisa umsebenzi othi “IF” onesibonelo

Mhlawumbe ukwaziswa okungenhla kungase kungabonakali kucace kangako. Kodwa, eqinisweni, akukho lutho oluyinkimbinkimbi lapha. Futhi ukuze uqonde kangcono inhloso yomsebenzi kanye nokusebenza kwawo, cabanga ngesibonelo esingezansi.

Sinetafula elinamagama ezicathulo zezemidlalo. Cabanga ukuthi sizodayiswa maduze, futhi zonke izicathulo zabesifazane zidinga ukwehliswa ngo-25%. Kwenye yamakholomu kuthebula, ubulili bento ngayinye buvele bubhalwe.

Umsebenzi wethu uwukubonisa inani elithi “25%” kukholamu ethi “Isaphulelo” kuyo yonke imigqa enamagama abesifazane. Futhi ngokufanelekile, inani lithi “0”, uma ikholomu ethi “Ubulili” iqukethe inani elithi “owesilisa”

Ukugcwalisa idatha mathupha kuzothatha isikhathi esiningi, futhi kunamathuba amaningi okwenza iphutha endaweni ethile, ikakhulukazi uma uhlu lude. Kulula kakhulu kulokhu ukwenza inqubo ngokuzenzakalelayo usebenzisa isitatimende esithi “IF”.

Ukuze uqedele lo msebenzi, uzodinga ukubhala ifomula elandelayo ngezansi:

=IF(B2="owesifazane",25%,0)

• Isisho esiphundukile: B2 = "owesifazane"
• Inani uma kwenzeka, isimo sihlangatshezwana nayo (iqiniso) - 25%
• Inani uma umbandela ungafinyelelwanga (amanga) ngu-0.

Sibhala le fomula kuseli eliphezulu kakhulu lekholomu “Yesaphulelo” bese ucindezela u-Enter. Ungakhohlwa ukubeka uphawu olulinganayo (=) phambi kwefomula.

Ngemva kwalokho, kule seli, umphumela uzoboniswa ngokuvumelana nesimo sethu esinengqondo (ungakhohlwa ukusetha ifomethi yeseli - iphesenti). Uma isheke liveza ukuthi ubulili "bowesifazane", inani elingu-25% lizoboniswa. Uma kungenjalo, inani leseli lizolingana no-0. Empeleni, ebesikudinga.

Manje kusele kuphela ukukopisha lesi sisho kuyo yonke imigqa. Ukuze wenze lokhu, hambisa ikhesa yegundane emaphethelweni angezansi kwesokudla weseli ngefomula. Isikhombi segundane kufanele siphenduke sibe isiphambano. Cindezela inkinobho yegundane kwesokunxele bese uhudula ifomula phezu kwayo yonke imigqa edinga ukuhlolwa ngokuya ngemibandela eshiwo.

Yilokho kuphela, manje sesisebenzise isimo kuyo yonke imigqa futhi sathola umphumela wayo ngamunye.

Ukusebenzisa "IF" enemibandela eminingi

Sisanda kubheka isibonelo sokusebenzisa u-opharetha othi “IF” onenkulumo eyodwa ye-boolean. Kodwa uhlelo futhi lunamandla okusetha izimo ezingaphezu kwesisodwa. Kulokhu, isheke lizokwenziwa okokuqala ngqa, futhi uma liphumelela, inani elimisiwe lizoboniswa ngokushesha. Futhi kuphela uma inkulumo yokuqala enengqondo ingenziwanga, isheke kweyesibili lizosebenza.

Ake sibheke ithebula elifanayo njengesibonelo. Kodwa kulokhu, ake sikwenze kube nzima. Manje udinga ukubeka isaphulelo ezicathulweni zabesifazane, kuye ngomdlalo.

Isimo sokuqala ukuhlola ubulili. Uma “owesilisa”, inani elingu-0 liboniswa ngokushesha. Uma "kungowesifazane", khona-ke isimo sesibili siyahlolwa. Uma umdlalo ugijima - 20%, uma tennis - 10%.

Masibhale ifomula yalezi zimo kuseli esilidingayo.

=ЕСЛИ(B2=”мужской”;0; ЕСЛИ(C2=”бег”;20%;10%))

Sichofoza u-Enter futhi sithola umphumela ngokuvumelana nemibandela ethile.

Okulandelayo, selula ifomula kuyo yonke imigqa esele yetafula.

Ukugcwaliseka ngesikhathi esisodwa kwezimo ezimbili

Futhi ku-Excel kukhona ithuba lokubonisa idatha ekugcwalisekeni ngesikhathi esisodwa kwezimo ezimbili. Kulokhu, inani lizobhekwa njengamanga uma okungenani imibandela eyodwa ingafinyelelwa. Ukuze lo msebenzi, opharetha “FUTHI”.

Ake sithathe itafula lethu njengesibonelo. Manje isaphulelo sika-30% sizosetshenziswa kuphela uma lezi kuyizicathulo zabesifazane futhi zenzelwe ukugijima. Uma lezi zimo zifinyelelwa, inani leseli lizolingana no-30% ngesikhathi esifanayo, ngaphandle kwalokho lizoba ngu-0.

Ukuze senze lokhu, sisebenzisa ifomula elandelayo:

=IF(FUTHI(B2=”owesifazane”;C2=“egijima”);30%;0)

Cindezela inkinobho ethi Enter ukuveza umphumela kuseli.

Ngokufanayo nezibonelo ezingenhla, sidlulisela ifomula kuyo yonke imigqa.

NOMA opharetha

Kulokhu, inani lenkulumo enengqondo libhekwa njengeqiniso uma kuhlangatshezwana nemibandela eyodwa. Umbandela wesibili ungase unganeliseki kuleli cala.

Ake siyibeke kanje inkinga. Isaphulelo esingu-35% sisebenza ezicathulweni zethenisi zabesilisa kuphela. Uma kuyisicathulo sabesilisa noma esinye isicathulo sabesifazane, isaphulelo singu-0.

Kulokhu, ifomula elandelayo iyadingeka:

=UMA(NOMA(B2="owesifazane"; C2="egijima");0;35%)

Ngemva kokucindezela u-Enter, sizothola inani elidingekayo.

Selula ifomula phansi futhi izaphulelo zalo lonke uhla sezilungile.

Uyichaza kanjani imisebenzi ye-IF usebenzisa I-Formula Builder

Ungasebenzisa umsebenzi we-IF hhayi ngokuwubhala mathupha kuseli noma ibha yefomula, kodwa futhi nangoMakhi Wefomula.

Ake sibone ukuthi kusebenza kanjani. Ake sithi futhi, njengesibonelo sokuqala, sidinga ukubeka isaphulelo kuzo zonke izicathulo zabesifazane ngenani lama-25%.

1. Sibeka ikhesa kuseli oyifunayo, iya kuthebhu ethi "Amafomula", bese uchofoza "Faka Umsebenzi".
2. Ohlwini lwe-Formula Builder oluvulekayo, khetha okuthi “IF” bese uchofoza okuthi “Faka Umsebenzi”.
3. Iwindi lezilungiselelo zomsebenzi liyavuleka. Emkhakheni othi "inkulumo enengqondo" sibhala isimo lapho isheke lizokwenziwa khona. Esimeni sethu "B2="female".

   Enkambini ethi "Iqiniso", bhala inani okufanele liboniswe kuseli uma umbandela ufinyelelwa.

   Esigabeni esithi "Amanga" - inani uma isimo singahlangatshezwana nayo.

4. Ngemuva kokuthi zonke izinkambu sezigcwalisiwe, chofoza okuthi "Qeda" ukuze uthole umphumela.

Isiphetho

Elinye lamathuluzi athandwa kakhulu futhi awusizo ku-Excel umsebenzi IF, ehlola idatha ukuze ifane nezimo esizibekayo futhi inikeze umphumela ngokuzenzakalelayo, okususa amathuba okuba namaphutha ngenxa yesici somuntu. Ngakho-ke, ulwazi nekhono lokusebenzisa leli thuluzi kuzosindisa isikhathi hhayi nje sokwenza imisebenzi eminingi, kodwa futhi nasekufuneni amaphutha okungenzeka ngenxa yemodi yokusebenza "yemanuwali".