Mai aiki da "IF" a cikin Microsoft Excel: aikace-aikace da misalai

Excel, ba shakka, yana da ayyuka masu yawa. Kuma daga cikin kayan aiki daban-daban, ma'aikacin "IF" yana da wuri na musamman. Yana taimakawa wajen warware ayyuka daban-daban, kuma masu amfani suna jujjuya wannan aikin sau da yawa fiye da sauran.

A cikin wannan labarin, za mu yi magana game da abin da ma'aikacin "IF", da kuma la'akari da ikon yinsa da ka'idojin aiki tare da shi.

Ma'anar aikin "IF" da manufarsa

Ma'aikacin "IF" kayan aiki ne na shirin Excel don duba wani yanayi (kalmomi masu ma'ana) don aiwatarwa.

Wato, ka yi tunanin cewa muna da wani irin yanayi. Ayyukan "IF" shine bincika ko yanayin da aka bayar ya cika kuma ya fitar da ƙima bisa sakamakon rajistan zuwa tantanin halitta tare da aikin.

1. Idan ma'anar ma'ana (sharadi) gaskiya ne, to ƙimar gaskiya ce.
2. Idan magana mai ma'ana (sharadi) ba ta cika ba, ƙimar ƙarya ce.

Tsarin aikin kanta a cikin shirin shine magana mai zuwa:

=IF (sharadi, [darajar idan yanayin ya cika], [darajar idan yanayin bai cika ba])

Yin amfani da aikin "IF" tare da Misali

Wataƙila bayanin da ke sama bazai yi kama da bayyananne ba. Amma, a gaskiya, babu wani abu mai rikitarwa a nan. Kuma don ƙarin fahimtar manufar aikin da aikinsa, yi la'akari da misalin da ke ƙasa.

Muna da tebur tare da sunayen takalman wasanni. Yi tunanin cewa ba da daɗewa ba za mu sami siyarwa, kuma duk takalman mata suna buƙatar ragi da kashi 25%. A cikin ɗaya daga cikin ginshiƙai a cikin tebur, jinsi na kowane abu an rubuta shi kawai.

Ayyukanmu shine nuna darajar "25%" a cikin "Rangwame" shafi na duk layuka tare da sunayen mata. Kuma bisa ga haka, ƙimar ita ce “0”, idan rukunin “Gender” ya ƙunshi ƙimar “namiji”

Cika bayanan da hannu zai ɗauki lokaci mai yawa, kuma akwai yuwuwar yin kuskure a wani wuri, musamman idan jerin suna da tsawo. Zai fi sauƙi a cikin wannan yanayin don sarrafa aikin ta atomatik ta amfani da bayanin "IF".

Don kammala wannan aikin, kuna buƙatar rubuta dabarar da ke ƙasa:

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

• Maganar Boolean: B2 = "mace"
• Darajar a yanayin, yanayin ya cika (gaskiya) - 25%
• Darajar idan yanayin bai cika ba (ƙarya) shine 0.

Muna rubuta wannan dabarar a cikin mafi girman tantanin halitta na ginshiƙin “Rangwame” kuma danna Shigar. Kar a manta da sanya alamar daidai (=) a gaban dabarar.

Bayan haka, don wannan tantanin halitta, za a nuna sakamakon bisa ga yanayin ma'ana (kar a manta da saita tsarin tantanin halitta - kashi). Idan rajistan ya nuna cewa jinsin “mace” ne, za a nuna ƙimar 25%. In ba haka ba, darajar tantanin halitta zai kasance daidai da 0. A matsayin gaskiya, abin da muke bukata.

Yanzu ya rage kawai don kwafi wannan magana zuwa dukkan layi. Don yin wannan, matsar da siginan linzamin kwamfuta zuwa ƙananan gefen dama na tantanin halitta tare da dabara. Mai nuna linzamin kwamfuta ya kamata ya juya ya zama giciye. Riƙe maɓallin linzamin kwamfuta na hagu kuma ja dabarar akan duk layin da ake buƙatar dubawa gwargwadon ƙayyadaddun sharuɗɗan.

Wannan ke nan, yanzu mun yi amfani da yanayin a duk layuka kuma mun sami sakamakon kowannensu.

Aiwatar "IF" tare da yanayi da yawa

Mun kalli misali kawai na amfani da ma'aikacin "IF" tare da furcin boolean guda ɗaya. Amma shirin kuma yana da ikon saita sharadi fiye da ɗaya. A wannan yanayin, za a fara aiwatar da cak a farkon, kuma idan an yi nasara, za a nuna ƙimar da aka saita nan da nan. Kuma idan ba a aiwatar da furci na farko na ma'ana ba, cak na biyu zai fara aiki.

Bari mu kalli tebur guda a matsayin misali. Amma a wannan karon, bari mu ƙara yin wahala. Yanzu kana buƙatar sanya rangwame akan takalman mata, dangane da wasanni.

Sharadi na farko shine duba jinsi. Idan "namiji", ana nuna ƙimar 0 nan da nan. Idan "mace" ce, to ana duba yanayin na biyu. Idan wasanni yana gudana - 20%, idan wasan tennis - 10%.

Bari mu rubuta dabarar waɗannan sharuɗɗan a cikin tantanin halitta da muke buƙata.

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

Mun danna Shigar kuma muna samun sakamakon bisa ga ƙayyadaddun yanayi.

Na gaba, muna shimfiɗa dabarar zuwa duk sauran layuka na tebur.

Cika sharuɗɗa guda biyu lokaci guda

Har ila yau, a cikin Excel akwai damar da za a nuna bayanai game da cikar sharuɗɗa guda biyu a lokaci guda. A wannan yanayin, za a yi la'akari da ƙimar ƙarya idan akalla ɗaya daga cikin sharuɗɗan ba a cika ba. Don wannan aikin, mai aiki "DA".

Bari mu dauki teburinmu a matsayin misali. Yanzu rangwamen 30% za a yi amfani da shi ne kawai idan waɗannan takalman mata ne kuma an tsara su don gudu. Idan waɗannan sharuɗɗan sun cika, ƙimar tantanin halitta zai kasance daidai da 30% a lokaci guda, in ba haka ba zai zama 0.

Don yin wannan, muna amfani da dabara mai zuwa:

=IF (AND(B2="mace";C2="gudu");30%;0)

Danna maɓallin Shigar don nuna sakamako a cikin tantanin halitta.

Kama da misalan da ke sama, muna shimfiɗa dabara zuwa sauran layin.

KO mai aiki

A wannan yanayin, ana ɗaukar ƙimar ma'anar ma'anar gaskiya idan ɗaya daga cikin sharuɗɗan ya cika. Sharadi na biyu bazai gamsu ba a wannan yanayin.

Mu saita matsalar kamar haka. Rangwamen 35% ya shafi takalman wasan tennis na maza kawai. Idan takalman gudu na maza ne ko kowane takalmin mata, rangwamen shine 0.

A wannan yanayin, ana buƙatar tsari mai zuwa:

=IF (OR (B2 = "mace"; C2 = "gudu");0;35%)

Bayan danna Shigar, za mu sami ƙimar da ake buƙata.

Muna shimfiɗa dabarar ƙasa kuma an shirya rangwamen don duk kewayon.

Yadda ake ayyana ayyukan IF ta amfani da Formula Builder

Kuna iya amfani da aikin IF ba kawai ta rubuta shi da hannu a cikin tantanin halitta ko dabara ba, har ma ta hanyar Maginin Formula.

Bari mu ga yadda yake aiki. Bari mu sake, kamar yadda a cikin misali na farko, muna buƙatar sanya rangwame akan duk takalman mata a cikin adadin 25%.

1. Mun sanya siginan kwamfuta a kan tantanin halitta da ake so, je zuwa shafin "Formulas", sannan danna "Saka Aiki".
2. A cikin jerin Formula Builder da ke buɗewa, zaɓi "IF" kuma danna "Saka Aiki".
3. Tagan saitunan ayyuka yana buɗewa. A cikin filin "magana mai ma'ana" muna rubuta yanayin da za a gudanar da rajistan. A cikin yanayinmu shine "B2 ="mace".

   A cikin filin "Gaskiya", rubuta ƙimar da yakamata a nuna a cikin tantanin halitta idan yanayin ya cika.

   A cikin filin "Ƙarya" - ƙimar idan yanayin bai cika ba.

4. Bayan an cika dukkan filayen, danna "Gama" don samun sakamako.

Kammalawa

Ɗaya daga cikin shahararrun kayan aiki masu amfani a cikin Excel shine aikin IF, wanda ke duba bayanan don daidaita yanayin da muka saita kuma ya ba da sakamakon ta atomatik, wanda ya kawar da yiwuwar kurakurai saboda yanayin ɗan adam. Sabili da haka, ilimi da ikon yin amfani da wannan kayan aiki zai adana lokaci ba kawai don yin ayyuka da yawa ba, har ma don neman kurakurai masu yiwuwa saboda yanayin "manual" na aiki.