IF ntchito mu Excel. Zitsanzo (zambiri)

Chimodzi mwazabwino zazikulu zamaspredishithi a Excel ndikutha kukonza magwiridwe antchito a chikalata china. Monga momwe anthu ambiri amadziwira kuchokera kumaphunziro a sayansi yamakompyuta kusukulu, chimodzi mwazinthu zazikulu zomwe zimakulolani kuti mugwiritse ntchito izi ndi ogwiritsa ntchito mwanzeru. Mmodzi wa iwo ndi woyendetsa IF, yemwe amapereka kuchitidwa kwa zinthu zina pamene zinthu zina zakwaniritsidwa. 

Mwachitsanzo, ngati mtengo ukufanana ndi wina, ndiye kuti chizindikiro chimodzi chikuwonetsedwa mu selo. Ngati sichoncho, ndizosiyana. Tiyeni tiwone chida ichi chothandiza mwatsatanetsatane muzochita.

IF ntchito mu Excel (zambiri)

Pulogalamu iliyonse, ngakhale itakhala yaying'ono, imakhala ndi machitidwe angapo, omwe amatchedwa algorithm. Zitha kuwoneka motere:

1. Onani ndime yonse A kuti mupeze manambala ofanana.
2. Ngati nambala yofanana ipezeka, onjezerani izi ndi zina.
3. Ngati nambala yofananira sinapezeke, sonyezani mawu akuti "sanapezeke".
4. Onani ngati nambala yotsatiridwayo ndi yofanana. 
5. Ngati inde, onjezani ku manambala onse omwe asankhidwa mundime 1.

Ndipo ngakhale izi ndizochitika zongopeka, zomwe sizingakhale zofunikira m'moyo weniweni, kuchitidwa kwa ntchito iliyonse kumatanthauza kukhalapo kwa algorithm yofanana. Musanagwiritse ntchito ntchitoyi NGATI, muyenera kukhala ndi lingaliro lomveka bwino m'mutu mwanu za zotsatira zomwe mukufuna kukwaniritsa. 

Syntax ya ntchito ya IF yokhala ndi chikhalidwe chimodzi

Ntchito iliyonse mu Excel imachitika pogwiritsa ntchito fomula. Njira yomwe deta iyenera kutumizidwa ku ntchito imatchedwa syntax. Pankhani ya woyendetsa IF, fomulayo ikhala mwanjira iyi.

=IF (mawu_womveka, mtengo_ngati_zoona, mtengo_ngati_zabodza)

Tiyeni tiwone ma syntax mwatsatanetsatane:

1. Mawu a Boolean. Izi ndiye momwe zimakhalira, kutsata kapena kusatsata komwe Excel imayang'ana. Zonse zamawerengero ndi zolemba zimatha kufufuzidwa.
2. Mtengo_ngati_zoona. Chotsatira chomwe chidzawonetsedwa mu selo ngati deta yomwe ikufufuzidwa ikugwirizana ndi zomwe zatchulidwa.
3. mtengo_ngati_zabodza. Chotsatira chomwe chikuwonetsedwa mu selo ngati deta yomwe ikufufuzidwa sichikugwirizana ndi chikhalidwecho.

Nachi chitsanzo chomveka bwino.

Apa ntchitoyi ikufanizira selo A1 ndi nambala 20. Iyi ndi ndime yoyamba ya syntax. Ngati zomwe zili ndi zazikulu kuposa mtengowu, mtengo "wamkulu kuposa 20" umawonetsedwa mu selo momwe fomula idalembedwera. Ngati zinthu sizikugwirizana ndi chikhalidwe ichi - "zocheperapo kapena zofanana ndi 20".

Ngati mukufuna kuwonetsa mtengo wa mawu mu cell, muyenera kuyika mu ma quotation marks.

Nawu mkhalidwe wina. Kuti akhale oyenerera kutenga gawo la mayeso, ophunzira ayenera kukhoza mayeso. Ophunzira adatha kupambana m'maphunziro onse, ndipo tsopano yomaliza yatsala, yomwe idakhala yotsimikizika. Ntchito yathu ndikuwona kuti ndi ndani mwa ophunzira omwe adavomerezedwa ku mayesowo komanso omwe saloledwa.

Popeza tikufuna kuyang'ana malemba osati nambala, mkangano woyamba ndi B2 = "zoipa".

IF Function Syntax yokhala ndi Mikhalidwe Yambiri

Nthawi zambiri, muyezo umodzi sikokwanira kuti muwone mtengo wotsutsana nawo. Ngati mukuyenera kuganizira njira zingapo, mutha kupanga chisa IF chimodzi mwa chimzake. Padzakhala ntchito zingapo zisa.

Kuti zimveke bwino, nayi mawu omasulira.

=IF(mawu_womveka, mtengo_ngati_zoona, IF(mawu_womveka, mtengo_ngati_zoona, mtengo_ngati_zabodza))

Pankhaniyi, ntchitoyi idzayang'ana njira ziwiri nthawi imodzi. Ngati chikhalidwe choyamba chiri chowona, mtengo wopezedwa chifukwa cha ntchito mu mkangano woyamba umabwereranso. Ngati sichoncho, muyeso wachiwiri umayang'aniridwa kuti ukutsatiridwa.

Nazi chitsanzo.

Ndipo mothandizidwa ndi fomula yotere (yowonetsedwa pazithunzi pansipa), mutha kusanthula momwe wophunzira aliyense amachitira.

Monga mukuonera, chikhalidwe china chinawonjezedwa apa, koma mfundoyi sinasinthe. Kotero mukhoza kuyang'ana njira zingapo nthawi imodzi.

Momwe mungakulitsire magwiridwe antchito a IF pogwiritsa ntchito NDI ndi OR operekera

Nthawi ndi nthawi pamakhala zochitika zoyang'ana nthawi yomweyo kuti zitsatidwe ndi njira zingapo, osagwiritsa ntchito ogwiritsira ntchito zomveka, monga momwe tawonera kale. Kuti muchite izi, gwiritsani ntchito chilichonse И kapena ntchito OR kutengera ngati muyenera kukwaniritsa zingapo nthawi imodzi kapena chimodzi mwa izo. Tiyeni tione bwinobwino mfundo zimenezi.

IF ntchito ndi NDI chikhalidwe

Nthawi zina muyenera kuyang'ana mawu azinthu zingapo nthawi imodzi. Pachifukwa ichi, ntchito ya AND imagwiritsidwa ntchito, yolembedwa mkangano woyamba wa ntchitoyi IF. Zimagwira ntchito motere: ngati a ali wofanana ndi chimodzi ndipo a ndi wofanana ndi 2, mtengo udzakhala c.

IF ntchito ndi "OR" chikhalidwe

Ntchito ya OR imagwira ntchito mofananamo, koma pamenepa, chimodzi chokha mwazinthu ndichowona. Momwe ndingathere, mpaka 30 zinthu zitha kufufuzidwa motere. 

Nazi njira zina zogwiritsira ntchito И и OR monga mkangano wa ntchito IF.

Kuyerekeza deta mu matebulo awiri

Nthawi ndi nthawi n'zotheka kufananitsa matebulo awiri ofanana. Mwachitsanzo, munthu amagwira ntchito yowerengera ndalama ndipo amafunika kufananiza malipoti awiri. Palinso ntchito zina zofanana, monga kuyerekezera mtengo wa katundu wamagulu osiyanasiyana, ndiye, kuwunika kwa ophunzira kwa nthawi zosiyanasiyana, ndi zina zotero.

Kuti mufananize ma tebulo awiri, gwiritsani ntchito ntchitoyi COUNTIF. Tiyeni tione mwatsatanetsatane.

Tiyerekeze kuti tili ndi matebulo awiri omwe ali ndi mfundo za mapurosesa awiri a chakudya. Ndipo tiyenera kufananiza iwo, ndi kuunikila kusiyana ndi mtundu. Izi zitha kuchitika pogwiritsa ntchito mawonekedwe okhazikika komanso ntchito COUNTIF. 

Gome lathu likuwoneka chonchi.

Timasankha mitundu yogwirizana ndi luso la purosesa ya chakudya choyamba.

Pambuyo pake, dinani pazotsatira zotsatirazi: Mapangidwe ovomerezeka - pangani lamulo - gwiritsani ntchito fomula kuti mudziwe ma cell opangidwa.

Mu mawonekedwe a fomula yopangira masanjidwe, timalemba ntchitoyo =COUNTIF (kuyerekeza; selo loyamba la tebulo loyamba)=0. Gome lomwe lili ndi mawonekedwe a pulogalamu yachiwiri ya chakudya limagwiritsidwa ntchito ngati kufananiza.

Muyenera kuwonetsetsa kuti maadiresi ali mtheradi (ndi chizindikiro cha dola kutsogolo kwa mzere ndi mayina). Onjezani = 0 pambuyo pa fomula kuti Excel iyang'ane zenizeni zenizeni.

Pambuyo pake, muyenera kukhazikitsa masanjidwe a ma cell. Kuti muchite izi, pafupi ndi chitsanzo, muyenera dinani batani la "Format". Kwa ife, timagwiritsa ntchito kudzaza, chifukwa ndi yabwino kwambiri pazifukwa izi. Koma mutha kusankha masanjidwe aliwonse omwe mukufuna.

Tapereka dzina lazambiri ngati gawo. Izi ndizosavuta kuposa kulowa munjira pamanja.

Ntchito ya SUMIF mu Excel

Tsopano tiyeni tipitirire ku ntchito IF, zomwe zingathandize kusintha mfundo ziwiri za aligorivimu nthawi imodzi. Choyamba ndi SUMMESLEY, zomwe zimawonjezera manambala awiri omwe amakwaniritsa chikhalidwe china. Mwachitsanzo, timayang'anizana ndi ntchito yosankha ndalama zomwe ziyenera kulipidwa pamwezi kwa onse ogulitsa. Kwa ichi ndikofunikira.

1. Onjezani mzere ndi ndalama zonse zomwe ogulitsa onse amapeza ndikudina pa selo lomwe lingakhale ndi zotsatira mutalowa fomula. 
2. Timapeza batani la fx, lomwe lili pafupi ndi mzere wa mafomu. Kenako, zenera adzaoneka kumene mungapeze ntchito zofunika kudzera kufufuza. Pambuyo kusankha woyendetsa, muyenera dinani "Chabwino" batani. Koma kulowetsa pamanja kumakhala kotheka nthawi zonse.
   

   

3. Kenako, zenera lolowetsamo mikangano yantchito lidzawonekera. Makhalidwe onse amatha kufotokozedwa m'magawo ofanana, ndipo mndandanda ukhoza kulowetsedwa kudzera pa batani pafupi nawo.
   

   

4. Mtsutso woyamba ndi osiyanasiyana. Apa mukulowetsa ma cell omwe mukufuna kuwona kuti akutsatiridwa ndi zomwe mukufuna. Ngati tilankhula za ife, awa ndi maudindo a antchito. Lowetsani mtundu wa D4:D18. Kapena ingosankhani ma cell omwe mukufuna.
5. M'munda wa "Criteria", lowetsani malowo. Kwa ife - "wogulitsa". Monga momwe zimakhalira, timawonetsa maselo omwe malipiro a antchito amalembedwa (izi zimachitika pamanja ndikuzisankha ndi mbewa). Dinani "Chabwino", ndipo timapeza malipiro owerengeka a antchito onse omwe akugulitsa.

Gwirizanani kuti ndi yabwino kwambiri. Sichoncho?

SUMIFS imagwira ntchito mu Excel

Ntchitoyi imakupatsani mwayi wodziwa kuchuluka kwazinthu zomwe zimakwaniritsa zingapo. Mwachitsanzo, tinapatsidwa ntchito yosankha malipiro a mamenejala onse ogwira ntchito kunthambi ya kum’mwera kwa kampaniyo.

Onjezani mzere pomwe zotsatira zomaliza zidzakhala, ndikuyika fomula mu cell yomwe mukufuna. Kuti muchite izi, dinani chizindikiro cha ntchito. A zenera adzaoneka imene muyenera kupeza ntchito SUMMESLIMN. Kenako, sankhani pamndandanda ndipo zenera lodziwika bwino lomwe lili ndi mikangano limatsegulidwa. Koma chiwerengero cha mikangano imeneyi tsopano ndi yosiyana. Njirayi imapangitsa kuti zitheke kugwiritsa ntchito chiwerengero chosawerengeka, koma chiwerengero chochepa cha mikangano ndi zisanu. 

Zisanu zokha zomwe zitha kufotokozedwa kudzera muzokambirana zotsutsana. Ngati mukufuna zina zambiri, ndiye kuti ziyenera kulowetsedwa pamanja molingana ndi malingaliro awiri oyambawo.

Tiyeni tiwone mikangano yayikulu mwatsatanetsatane:

1. Chidule cha mitundu. Maselo kuti afotokozedwe mwachidule.
2. Chikhalidwe 1 - mtundu womwe udzawunikiridwa kuti utsatire muyeso wina. 
3. Condition 1 ndiye mkhalidwe womwewo.
4. Mulingo wa 2 ndi mzere wachiwiri womwe ungawunikidwe molingana ndi muyezo.
5. Condition 2 ndi chikhalidwe chachiwiri.

Mfundo zinanso n'zofanana. Chifukwa cha zimenezi, tinasankha malipiro a mamenejala onse a Nthambi ya Kumwera.

COUNTIF ntchito mu Excel

Ngati mukufuna kudziwa kuti ndi maselo angati omwe amagwera pansi pa muyeso wina, gwiritsani ntchito ntchitoyi COUNTIF. Tiyerekeze kuti tikuyenera kumvetsetsa kuti ndi angati ogulitsa amagwira ntchito m'bungweli:

1. Choyamba, onjezani mzere wokhala ndi chiwerengero cha ogulitsa. Pambuyo pake, muyenera dinani pa selo kumene zotsatira zidzawonetsedwa.
2. Pambuyo pake, muyenera dinani batani la "Insert Function", lomwe limapezeka mu tabu "Mapangidwe". A zenera adzaoneka ndi mndandanda wa magulu. Tiyenera kusankha chinthucho "Mndandanda wa zilembo zonse". Pamndandanda, tili ndi chidwi ndi chilinganizo COUNTIF. Pambuyo kusankha izo, tiyenera dinani "Chabwino" batani.
   

   

3. Pambuyo pake, tili ndi chiwerengero cha ogulitsa omwe adalembedwa ntchito m'bungweli. Anapezedwa mwa kuwerengera chiwerengero cha maselo omwe mawu oti "wogulitsa" amalembedwa. Zonse ndi zophweka. 

COUNTSLIM ntchito mu Excel

Zofanana ndi formula SUMMESLIMN, fomula iyi imawerengera kuchuluka kwa maselo omwe amafanana ndi mikhalidwe ingapo. Kalembedwe kake ndi kofanana koma kosiyana pang'ono ndi kalembedwe SUMMESLIMN:

1. Mkhalidwe 1. Uwu ndiye mulingo womwe uyesedwa ndi muyeso woyamba.
2. Chomwe 1. Mwachindunji muyeso woyamba.
3. Condition Range 2. Uwu ndiwo mtundu womwe uyesedwa ndi muyeso wachiwiri. 
4. Chikhalidwe 2.
5. Makhalidwe osiyanasiyana 3.

Ndi zina zotero.

Choncho ntchito IF mu Excel - osati imodzi yokha, pali mitundu yake ingapo yomwe imangochita zinthu zodziwika bwino, zomwe zimathandizira moyo wamunthu kukhala wosalira zambiri. 

Zikomo kwambiri chifukwa cha ntchito IF Ma spreadsheets a Excel amaonedwa kuti ndi okonzeka. Ndizoposa chowerengera chosavuta. Ngati mukuganiza za izo, ndiye ntchito IF ndi mwala wapangodya mumtundu uliwonse wa mapulogalamu.

Chifukwa chake ngati muphunzira momwe mungagwiritsire ntchito ndi data yambiri mu Excel, zidzakhala zosavuta kuphunzira mapulogalamu. Chifukwa cha ogwiritsa ntchito mwanzeru, maderawa ali ndi zofanana kwambiri, ngakhale kuti Excel imagwiritsidwa ntchito nthawi zambiri ndi akauntanti. Koma makina ogwiritsira ntchito deta ndi ofanana kwambiri. 

Ntchito m'manja mwamanja IF ndipo kusiyanasiyana kwake kumakupatsani mwayi wosinthira pepala la Excel kukhala pulogalamu yokwanira yomwe imatha kuchitapo kanthu pama algorithms ovuta. Kumvetsetsa momwe ntchitoyi imagwirira ntchito IF ndi sitepe yoyamba yophunzirira ma macros - sitepe yotsatira mu ntchito yosinthika kwambiri ndi ma spreadsheets. Koma uwu kale kwambiri akatswiri mlingo.