IF mawu mu Excel. Zonse zokhudza wogwiritsa ntchito - kugwiritsa ntchito, zitsanzo

Ntchito mu pulogalamu ya Excel, ndithudi, ndi yaikulu kwambiri. Makamaka, ndizotheka kukonzanso deta pamlingo wina. Udindo wa izi, mwa zina, ntchito IF. Zimapangitsa kukhala kotheka kukhazikitsa pafupifupi ntchito iliyonse. Ndicho chifukwa chake wogwiritsa ntchito uyu amagwiritsidwa ntchito nthawi zambiri kuposa ena. Lero tiyesera kufotokoza zomwe zimachita komanso momwe zingagwiritsidwe ntchito.

IF ntchito - tanthauzo ndi kukula

Kugwiritsa ntchito IF wogwiritsa ntchito akhoza kulangiza pulogalamuyo kuti awone ngati selo linalake likufanana ndi muyeso womwe wapatsidwa. Ngati tili ndi chikhalidwe chomwe timangofunika kuchita ntchitoyi, ndiye kuti Excel imayang'ana kaye, kenako imawonetsa zotsatira zowerengera mu cell yomwe ntchitoyi idalembedwa. Koma izi zimangokhala ngati ntchitoyi ikugwiritsidwa ntchito limodzi ndi ntchito ina. Wothandizira mwiniwake IF imapanga zotsatira ziwiri:

1. ZOONA. Izi ndi ngati mawu kapena selo likufanana ndi mulingo wina.
2. ZABODZA. Wothandizira uyu akuwonetsedwa ngati palibe chofanana.

Kalembedwe ka fomula ndi motere (mu mawonekedwe a chilengedwe chonse): =IF(condition; [mtengo ngati mkhalidwe wakwaniritsidwa]; [mtengo ngati mkhalidwe sunakwaniritsidwe]). Ntchitoyi ikhoza kuphatikizidwa ndi ena. Pachifukwa ichi, ogwira ntchito ena ayenera kulembedwa muzotsutsana zofanana. Mwachitsanzo, mutha kuyipanga kuti iwonetsere ngati nambalayo ili yabwino, ndipo ngati ndi choncho, pezani tanthauzo la masamu. Inde, pali ntchito imodzi yomwe imagwiranso ntchito, koma chitsanzo ichi chikuwonetsa bwino momwe ntchitoyi imagwirira ntchito. IF. Pankhani ya mapulogalamu omwe ntchitoyi ingagwiritsidwe ntchito IF, ndiye pali chiwerengero chachikulu cha iwo:

1. Climatology.
2. Zogulitsa ndi bizinesi.
3. Malonda.
4. Kuwerengera.

Ndi zina zotero. Malo aliwonse omwe mungatchule, ndipo padzakhala ntchito ya ntchitoyi.

Momwe mungagwiritsire ntchito ntchito ya IF mu Excel - zitsanzo

Tiyeni titenge chitsanzo china cha momwe tingagwiritsire ntchito ntchitoyi IF mu Excel. Tiyerekeze kuti tili ndi tebulo lomwe lili ndi mayina a nsapato. Tiyerekeze kuti pali kugulitsa kwakukulu kwa nsapato zazimayi zomwe zimafuna kuchotsera 25 peresenti pazinthu zonse. Kuti mupange cheke ichi, pali gawo lapadera lomwe likuwonetsa jenda lomwe sneaker amapangidwira.

Choncho, chikhalidwe cha ntchitoyi chidzakhala kufanana kwa kugonana kwa mkazi. Ngati, chifukwa cha cheke, chikapezeka kuti muyeso uwu ndi wowona, ndiye kuti pamalo omwe ndondomekoyi ikuwonetsedwa, muyenera kulemba ndalama zochotsera - 25%. Ngati ndi zabodza, tchulani mtengo 0, popeza kuchotsera sikunaperekedwe pankhaniyi.

Inde, mukhoza kudzaza maselo ofunikira pamanja. Koma izi zingatenge nthawi yochuluka. Kuphatikiza apo, chinthu chaumunthu, chifukwa chomwe zolakwika ndi kupotoza kwa chidziwitso zimatha kuchitika, sichinathenso. Kompyuta silakwitsa. Choncho, ngati kuchuluka kwa chidziwitso ndi chachikulu kwambiri, ndiye kuti ndi bwino kugwiritsa ntchito ntchitoyi IF.

Kuti mukwaniritse cholinga chomwe chakhazikitsidwa pagawo loyamba, ndikofunikira kusankha selo lomwe mtengo wake udzawonetsedwa ndikulemba zotsatirazi: =IF(B2="mkazi",25%,0). Taganizirani ntchito iyi:

1. IF ndi wogwiritsa ntchito mwachindunji.
2. B2 = "chachikazi" ndiye muyeso woti ukwaniritse.
3. Izi zikutsatiridwa ndi mtengo womwe udzawonetsedwe ngati sneakers amapangidwa kwa amayi ndi mtengo womwe umasonyezedwa ngati ukupezeka kuti sneakers ndi amuna, ana kapena zina zomwe sizikugwirizana ndi zomwe zafotokozedwa mkangano woyamba.

Malo abwino olembera fomula ili kuti? Kawirikawiri, malowa akhoza kusankhidwa mosasamala, koma kwa ife, awa ndi maselo omwe ali pansi pa mutu wa "Kuchotsera".

Ndikofunika kuti musaiwale kuika = chizindikiro patsogolo pa ndondomekoyi. Apo ayi, Excel idzawerenga ngati malemba osavuta.

Fomuyo ikalowetsedwa, muyenera kukanikiza fungulo lolowera, kenako tebulo lidzadzazidwa ndi mtengo wolondola. Pa tebulo ili m'munsimu, tikhoza kuona kuti cheke choyamba chinali cholondola. Pulogalamuyi idadziwiratu jenda la nsapatozi ndikuwapatsa kuchotsera kotala la mtengo wake. Chotsatira chakwaniritsidwa.

Tsopano yatsala kudzaza mizere yotsala. Kuti muchite izi, simuyenera kukopera fomula mu selo lililonse payekha. Ndikokwanira kupeza sikweya pansi pakona yakumanja, kusuntha cholozera cha mbewa pamwamba pake, onetsetsani kuti chasanduka chithunzi chamtanda ndikukokera chikhomo mpaka pansi pa tebulo. Kenako Excel idzakuchitirani zonse.

Kugwiritsa Ntchito Ntchito ya IF yokhala ndi Mikhalidwe Yambiri

Poyamba, njira yosavuta yogwiritsira ntchito ntchitoyi inkaganiziridwa IF, mmene muli mawu amodzi okha omveka. Koma bwanji ngati muyenera kuyang'ana selo motsutsana ndi zinthu zingapo? Izi zitha kuchitikanso pogwiritsa ntchito magwiridwe antchito a Excel.

Chimodzi mwazinthu zapadera zowunikira zikhalidwe zingapo ndikuwunika kutsata koyamba ndipo ngati zikuwoneka kuti ndi zabodza, fufuzani chachiwiri, chachitatu, ndi zina zotero. Kapena, ngati mtengowo ndi wowona, yang'anani muyeso wina. Apa, monga wogwiritsa ntchito akufuna, malingaliro a zochita adzakhala pafupifupi ofanana. Ngati mwawerenga mozama zomwe zalembedwa pamwambapa, ndiye kuti mwina munaganizapo kale momwe mungachitire. Koma tiyeni tiwonjezere kuwonekera.

Kuti tichite izi, tiyeni tipangitse ntchitoyi kukhala yovuta kwambiri. Tsopano tikuyenera kupereka kuchotsera kwa nsapato zazimayi zokha, koma kutengera masewera omwe amapangidwira, kukula kwa kuchotsera kuyenera kukhala kosiyana. Njirayi poyang'ana koyamba idzakhala yovuta kwambiri, koma kawirikawiri, idzagwera m'lingaliro lofanana ndi lapitalo: =ЕСЛИ(B2=”мужской”;0; ЕСЛИ(C2=”бег”;20%;10%)).

Kenako, timachita zofanana ndi zomwe zidachitika m'mbuyomu: dinani Enter ndikulemba mizere yonseyi. Timapeza zotsatira zotere.

Kodi fomulayi imagwira ntchito bwanji? Ntchito yoyamba poyamba IF amafufuza ngati nsapato ndi yachimuna. Ngati sichoncho, ndiye kuti ntchito yachiwiri ikuchitika. IF, yomwe imayang'ana poyamba ngati nsapato zapangidwira kuthamanga. Ngati inde, ndiye kuchotsera 20% kumaperekedwa. Ngati sichoncho, kuchotsera ndi 10%. Monga mukuwonera, ntchito zina zitha kugwiritsidwa ntchito ngati mikangano yantchito, ndipo izi zimapereka mwayi wowonjezera.

Momwe mungagwiritsire ntchito ntchito ya IF kukwaniritsa zinthu ziwiri nthawi imodzi

Kuphatikiza apo, pogwiritsa ntchito Excel, mutha kuyang'ana ngati mukutsata zikhalidwe ziwiri nthawi imodzi. Kwa ichi, ntchito ina imagwiritsidwa ntchito, yomwe imatchedwa И. Wogwiritsa ntchito bwino uyu amaphatikiza mikhalidwe iwiri ndipo samangogwira ntchito IF. Itha kugwiritsidwanso ntchito pazinthu zina zambiri.

Tiyeni tibwerere ku tebulo lathu. Tsopano kuchotsera kuyenera kukhala kokulirapo, koma kumangogwira ntchito ku nsapato zazimayi zothamanga. Ngati, mutayang'ana, zikuwoneka kuti zonsezi zakwaniritsidwa, ndiye kuti kuchotsera kwa 30% kudzalembedwa m'munda wa "Discount". Zikapezeka kuti chimodzi mwazinthu sizigwira ntchito, ndiye kuti kuchotserako sikukugwira ntchito pamankhwala otere. Fomula mu nkhani iyi ingakhale: =IF(NDI(B2=”mkazi”;C2=“kuthamanga”);30%;0).

Kuphatikiza apo, zochitika zonse zidachitikanso kubwereza zitsanzo ziwiri zam'mbuyomu. Choyamba, timakanikiza batani lolowetsa, ndiyeno timakokera mtengo ku maselo ena onse omwe ali patebulo ili.

Kalembedwe ka AND ntchito, monga tikuwonera, imakhala ndi mikangano ingapo. Choyamba ndi chikhalidwe choyamba, chachiwiri ndi chachiwiri, ndi zina zotero. Mutha kugwiritsa ntchito mikangano yopitilira iwiri ndikuwunika zinthu zingapo nthawi imodzi. Koma m’zochita zoterezi sizichitika kawirikawiri. Zoposa zitatu nthawi imodzi - pafupifupi sizichitika. Mndandanda wa zochita zomwe zimachitidwa ndi ntchitoyi ndi izi:

1. Choyamba, ndondomekoyi imayang'ana chikhalidwe choyamba - kaya nsapato ndi akazi.
2. Excel ndiye amasanthula muyeso wachiwiri - ngati nsapato zidapangidwira kuti zizithamanga.
3. Ngati, chifukwa cha mayesowo, zikuwoneka kuti njira zonse ziwiri zimabwezera mtengo WOONA, ndiye zotsatira za ntchitoyo IF zimakhala zoona. Chifukwa chake, zomwe zakonzedwa muzokambirana zofananira zimachitidwa.
4. Zikapezeka kuti cheke chimodzi chimabweretsa zotsatira KUnama, ndi ntchito И adzabwezera zotsatira izi. Choncho, zotsatira zolembedwa mu mkangano wachitatu wa ntchitoyi zidzawonetsedwa IF.

Monga mukuwonera, malingaliro ochitapo kanthu ndi osavuta komanso osavuta kumvetsetsa pamlingo wanzeru.

OR wogwiritsa ntchito mu Excel

Wogwiritsa ntchito OR amagwira ntchito mofananamo ndipo ali ndi mawu ofanana. Koma mtundu wa chitsimikiziro ndi wosiyana pang'ono. Izi zimabweretsa mtengo WOONA ngati cheke chimodzi chibweretsa zotsatira WOONA. Ngati macheke onse apereka zotsatira zabodza, ndiye, molingana, ntchitoyo OR imabweretsa mtengo KUnama.

Choncho, ngati ntchito OR  zotsatira WOONA chifukwa chimodzi mwazofunikira, ndiye ntchitoyo IF adzalemba mtengo womwe wafotokozedwa mumtsutso wachiwiri. Ndipo pokhapokha ngati mtengowo sukugwirizana ndi zofunikira zonse, malemba kapena nambala yomwe yatchulidwa mumtsutso wachitatu wa ntchitoyi imabwezeretsedwa.

Kuti tiwonetse mfundoyi pochita, tiyeni tigwiritsenso ntchito chitsanzo. Vuto tsopano ndi ili: kuchotsera kumaperekedwa kaya pa nsapato za amuna kapena tennis. Pankhaniyi, kuchotsera kudzakhala 35%. Ngati nsapatozo ndi zazimayi kapena zopangidwira kuthamanga, ndiye kuti sipadzakhala kuchotsera pamutu wotero.

Kuti mukwaniritse cholinga chimenecho, muyenera kulemba fomula ili mu cell, yomwe ili mwachindunji pansi pa mawu akuti "Kuchotsera": = NGATI(OR(B2=“mkazi”; C2=“kuthamanga”);0;35%). Titasindikiza batani lolowera ndikukokera fomulayi kumaselo ena onse, timapeza zotsatirazi.

Momwe mungatanthauzire ntchito ya IF pogwiritsa ntchito Formula Builder

Inde, kumlingo wakutiwakuti, kulemba fomula ndi dzanja ndikosavuta kuposa kugwiritsa ntchito zida zina. Koma ngati ndinu oyamba, ndiye kuti zinthu zikusintha kwambiri. Kuti musasokonezedwe polowetsa mikangano, komanso kuwonetsa dzina lolondola la ntchito iliyonse, pali chida chapadera chotchedwa Function Entry Wizard kapena Formula Builder. Tiyeni tione mwatsatanetsatane limagwirira ntchito yake. Tiyerekeze kuti tapatsidwa ntchito ndi oyang'anira kuti tifufuze kuchuluka kwazinthu zomwe zilipo ndikuchotsera 25% kuchotsera kwa nsapato za amayi onse. Zotsatira zake pankhaniyi zidzakhala motere:

1. Timatsegula wizard yolowera ntchito podina batani lolingana pa tabu ya Fomula (yowonetsedwa ndi rectangle yofiira pazithunzi).
2. Kenako, gulu laling'ono lopanga formula limatsegulidwa, momwe timasankha ntchito yomwe tikufuna. Ikhoza kusankhidwa mwachindunji kuchokera pamndandanda kapena kufufuzidwa m'munda wofufuzira. Tili nazo kale pamndandanda wa 10 omwe agwiritsidwa ntchito posachedwa, ndiye timadina ndikudina batani la "Insert Function".
3. Pambuyo pake, zenera lokhazikitsa mikangano yantchito lidzatsegulidwa pamaso pathu. Pansi pa gululi, mutha kuwonanso zomwe ntchito yosankhidwayo imachita. Mkangano uliwonse wasainidwa, kotero simuyenera kukumbukira kutsatizana kwake. Choyamba timayika mawu omveka bwino omwe ali ndi nambala kapena selo, komanso mtengo woti tiwone ngati akutsatiridwa. Kenako, zikhalidwe zimalowetsedwa ngati zowona ndi mtengo ngati zabodza.
4. Masitepe onse akamaliza, dinani batani "Yambani".

Tsopano ife tikupeza zotsatira. Ndi iyo, timachita zofanana ndi zomwe zidachitika m'mbuyomu, mwachitsanzo, timaloza mbewa pamalo apakati pakona yakumanja ndikukokera chilinganizo kumaselo onse otsala. Choncho ntchito IF ndiye wogwiritsa ntchito wotchuka kwambiri komanso wofunikira pakati pa zonse zomwe zilipo. Imayang'ana deta motsutsana ndi zofunikira zina ndikuchita zoyenera ngati chekeyo ibwezera zotsatira. WOONA or KUnama. Izi zimakuthandizani kuti muchepetse kuwongolera kwa data yayikulu komanso kuti musamachite zinthu zambiri, ndikugawa ntchito yonyansayi pakompyuta.