Iyo "IF" mushandisi muMicrosoft Excel: application uye mienzaniso

Excel, hongu, ine basa rakapfuma kwazvo. Uye pakati pezvishandiso zvakawanda zvakasiyana, mushandisi we "IF" ane nzvimbo yakakosha. Inobatsira mukugadzirisa mabasa akasiyana zvachose, uye vashandisi vanotendeukira kune iri basa kakawanda kupfuura vamwe.

Muchikamu chino, tichataura pamusoro pekuti chii chinonzi "IF", uyewo funga nezvehuwandu uye nheyo dzekushanda nayo.

Tsanangudzo ye "IF" basa uye chinangwa chayo

Iyo "IF" mushandisi iExcel chirongwa chekutarisa imwe mamiriro (ane musoro kutaura) kuti aite.

Ndiko, fungidzira kuti tine imwe mhando yemamiriro. Basa re "IF" nderekutarisa kana mamiriro akapihwa asangana uye kuburitsa kukosha kunoenderana nemhedzisiro yecheki kune sero rine basa.

1. Kana chirevo chine musoro (mamiriro) chiri chechokwadi, saka kukosha kwacho ndekwechokwadi.
2. Kana chirevo chine musoro (mamiriro) chikasasangana, kukosha kwenhema.

Iyo basa formula pachayo muchirongwa ndiyo inotevera kutaura:

=IF(mamiriro, [kukosha kana mamiriro asangana], [kukosha kana mamiriro asina kusangana])

Kushandisa "IF" Basa nemuenzaniso

Zvichida mashoko ari pamusoro apa angasava akajeka zvikuru. Asi, chaizvoizvo, hapana chakaoma apa. Uye kuti unzwisise zviri nani chinangwa chebasa uye kushanda kwayo, funga muenzaniso uri pasi apa.

Tine tafura ine mazita eshangu dzemitambo. Fungidzira kuti tichakurumidza kutengesa, uye shangu dzose dzevakadzi dzinoda kuderedzwa ne25%. Mune imwe yemakoramu ari patafura, gender yechinhu chimwe nechimwe inongopereterwa.

Basa redu ndere kuratidza kukosha "25%" mu "Discount" column yemitsara yese ine mazita echikadzi. Uye maererano, kukosha ndeye "0", kana "Gender" column ine kukosha "murume"

Kuzadza data nemaoko kunotora nguva yakawanda, uye kune mukana mukuru wekuita chikanganiso pane imwe nzvimbo, kunyanya kana runyoro rwakareba. Zviri nyore mune iyi kesi kuita otomatiki maitiro uchishandisa "IF" chirevo.

Kuti upedze basa iri, iwe uchafanirwa kunyora inotevera fomula pazasi:

=IF(B2=”mukadzi”,25%,0)

• Boolean kutaura: B2 = "mukadzi"
• Kukosha kwechiitiko, mamiriro acho anosangana (chokwadi) - 25%
• Kukosha kana mamiriro asina kusangana (manyepo) ndeye 0.

Isu tinonyora iyi fomula mune yepamusoro sero ye "Discount" column uye tinya Enter. Usakanganwa kuisa chiratidzo chakaenzana (=) pamberi pefomula.

Mushure meizvi, nokuda kwesero iri, chigumisiro chicharatidzwa maererano nemamiriro edu anonzwisisika (usakanganwa kuisa sero format - muzana). Kana cheki ichiratidza kuti murume "mukadzi", kukosha kwe25% kucharatidzwa. Zvikasadaro, kukosha kwesero kuchaenzana na 0. Sezvineiwo, zvataida.

Zvino chasara chete kukopa kutaura uku kumitsara yese. Kuti uite izvi, fambisa mutsara wegonzo kumucheto wekurudyi wesero neformula. Iyo mouse pointer inofanira kushanduka kuita muchinjikwa. Bata pasi bhatani rekuruboshwe uye dhonza fomula pamusoro pemitsara yese inoda kutariswa zvinoenderana nemamiriro akataurwa.

Ndizvo chete, ikozvino takaisa mamiriro acho kumitsara yese uye tawana mhedzisiro yemumwe nemumwe wavo.

Kushandisa "IF" nemamiriro akawanda

Isu takangotarisa muenzaniso wekushandisa iyo "IF" mushandisi ane imwechete boolean kutaura. Asi chirongwa zvakare chine kugona kuseta zvinopfuura imwe mamiriro. Muchiitiko ichi, cheki ichaitwa kutanga pane yekutanga, uye kana ikabudirira, kukosha kwakatarwa kucharatidzwa pakarepo. Uye chete kana chirevo chekutanga chine musoro chisina kuitwa, cheki pane yechipiri ichaita.

Ngatitarisei patafura imwechete semuenzaniso. Asi panguva ino, ngatiomese. Iye zvino iwe unofanirwa kuisa pasi kuderedzwa pashangu dzevakadzi, zvichienderana nemutambo.

Mamiriro ekutanga ndeyekuongororwa kwevakadzi. Kana "murume", kukosha kwe0 kunoratidzwa pakarepo. Kana iri "mukadzi", ipapo chimiro chechipiri chinotariswa. Kana mutambo uchimhanya - 20%, kana tennis - 10%.

Ngatinyorei nzira yemamiriro aya musero yatinoda.

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

Isu tinodzvanya Enter uye tinowana mhedzisiro zvinoenderana neyakatsanangurwa mamiriro.

Zvadaro, tinotambanudza fomula kune yose yakasara mitsara yetafura.

Kuzadzikiswa kwemamiriro maviri panguva imwe chete

Uyewo muExcel pane mukana wekuratidza data pamusoro pekuzadzika kumwe chete kwemamiriro maviri. Muchiitiko ichi, kukosha kuchaonekwa senhema kana imwe yemamiriro ezvinhu isati yasangana. Nokuda kwebasa iri, mushandisi "UYE".

Ngatitorei tafura yedu semuenzaniso. Iye zvino iyo 30% mutero ichashandiswa chete kana idzi dziri shangu dzevakadzi uye dzakagadzirirwa kumhanya. Kana mamiriro aya akazadzikiswa, kukosha kwesero kunenge kwakaenzana ne30% panguva imwe chete, kana zvisina kudaro ichava 0.

Kuti tiite izvi, tinoshandisa formula inotevera:

=KANA(AND(B2=”mukadzi”;C2=”kumhanya”);30%;0)

Dzvanya kiyi Enter kuratidza mhedzisiro musero.

Zvakafanana nemienzaniso iri pamusoro, isu tinotambanudza fomula kune yasara mitsetse.

OR mushandisi

Muchiitiko ichi, kukosha kwekutaura kunonzwisisika kunoonekwa sechokwadi kana imwe yemamiriro ezvinhu inosangana. Chimiro chechipiri chinogona kusagutsikana munyaya iyi.

Ngatiisei dambudziko sezvinotevera. 35% kuderedzwa kunoshanda kune varume tennis shangu chete. Kana iri bhutsu yekumhanya yevarume kana chero shangu yevakadzi, mutero i0.

Muchiitiko ichi, formula inotevera inodiwa:

= KANA(OR(B2=”mukadzi”; C2=”kumhanya”);0;35%)

Mushure mekudzvanya Enter, tichawana kukosha kunodiwa.

Isu tinotambanudza fomula pasi uye zvidzikiso zvehuwandu hwese zvakagadzirira.

Maitiro ekutsanangura IF mabasa uchishandisa iyo Formula Builder

Iwe unogona kushandisa iyo IF basa kwete chete nekuinyora nemaoko musero kana fomula bar, asiwo kuburikidza neFormula Builder.

Ngationei kuti zvinoshanda sei. Ngatitii isu zvakare, semumuenzaniso wekutanga, tinoda kuisa dhigirii pashangu dzose dzevakadzi muhuwandu hwe25%.

1. Isu tinoisa cursor pane yaunoda sero, enda kune "Formulas" tab, uye tinya "Isa Basa".
2. Mune iyo Formula Builder rondedzero inovhura, sarudza "IF" uye tinya "Isa Basa".
3. Iwindo rezvirongwa zvebasa rinovhura. Mundima "chirevo chinonzwisisika" tinonyora mamiriro ayo cheki ichaitwa. Muchiitiko chedu "B2 =" mukadzi ".

   Mundima ye "Chokwadi", nyora kukosha kunofanira kuratidzwa muchitokisi kana mamiriro acho asangana.

   Mundima ye "Nhema" - kukosha kana mamiriro acho asina kusangana.

4. Mushure mekunge minda yese yazadzwa, tinya "Pedzisa" kuti uwane mhedzisiro.

mhedziso

Imwe yeanonyanya kufarirwa uye anobatsira maturusi muExcel ndiro basa IF, iyo inotarisa data yekufananidza mamiriro atinoisa uye inopa mhedzisiro otomatiki, izvo zvinobvisa mukana wekukanganisa nekuda kwechinhu chemunhu. Naizvozvo, ruzivo uye kugona kushandisa chishandiso ichi kuchachengetedza nguva kwete chete yekuita mabasa mazhinji, asiwo nekutsvaga zvikanganiso zvinogona kuitika nekuda kwe "manual" maitiro ekushanda.