IF sanarwa a cikin Excel. Duk game da afareta – aikace-aikace, misalai

Saitin ayyuka a cikin shirin Excel, ba shakka, yana da girma da gaske. Musamman ma, yana yiwuwa a tsara tsarin sarrafa bayanai zuwa wani matsayi. Alhakin wannan, a tsakanin sauran abubuwa, aikin IF. Yana ba da damar aiwatar da kusan kowane ɗawainiya. Shi ya sa ake amfani da wannan ma'aikacin sau da yawa fiye da sauran. A yau za mu yi ƙoƙari mu bayyana abin da yake yi da kuma yadda za a yi amfani da shi.

IF aiki - ma'anar da iyaka

Amfani da aikin IF mai amfani zai iya umurtar shirin don bincika idan wani tantanin halitta ya dace da ma'aunin da aka bayar. Idan muna da yanayin da kawai muke buƙatar aiwatar da aikin, to, Excel zai fara dubawa, bayan haka yana nuna sakamakon lissafin a cikin tantanin halitta da aka rubuta wannan aikin. Amma wannan shine kawai idan ana amfani da wannan aikin tare da wani aiki. Mai aiki da kansa IF yana haifar da sakamako guda biyu:

1. GASKIYA. Wannan idan magana ko tantanin halitta ya dace da wani ma'auni.
2. KARYA. Ana nuna wannan afaretan idan babu wasa.

Ma'anar dabarar ita ce kamar haka (a cikin nau'i na duniya): =IF (sharadi; [darajar idan yanayin ya cika]; [darajar idan yanayin bai cika ba]). Ana iya haɗa aikin tare da wasu. A wannan yanayin, dole ne a rubuta wasu masu aiki a cikin mahawara masu dacewa. Misali, zaku iya sanya shi don bincika idan lambar ta tabbata, idan haka ne, nemo ma'anar lissafi. Tabbas, akwai aiki ɗaya da yake yin iri ɗaya, amma wannan misalin yana nuna sarai yadda aikin yake aiki. IF. Amma ga aikace-aikacen da za a iya amfani da aikin IF, to akwai adadi mai yawa daga cikinsu:

1. Climatology.
2. Sales da kasuwanci.
3. Marketing.
4. Ƙididdiga.

Da sauransu. Kowane yanki da kuka suna, kuma za a sami aikace-aikacen wannan aikin.

Yadda ake amfani da aikin IF a cikin Excel - misalai

Bari mu ɗauki wani misali na yadda za mu iya amfani da aikin IF a cikin Excel. A ce muna da tebur dauke da sunayen sneakers. Bari mu ce akwai babban tallace-tallace a kan takalman mata wanda ke buƙatar rangwamen kashi 25 cikin XNUMX akan duk kayayyaki. Don yin wannan cak, akwai wani shafi na musamman da ke nuna jinsin da aka yi nufin sneaker.

Sabili da haka, yanayin wannan aikin zai zama daidaiton jima'i ga mace. Idan, saboda sakamakon rajistan, an gano cewa wannan ma'auni gaskiya ne, to, a wurin da aka nuna wannan tsari, kana buƙatar rubuta adadin rangwame - 25%. Idan karya ne, saka darajar 0, tunda ba a bayar da rangwamen ba a wannan yanayin.

Tabbas, zaku iya cika sel da ake buƙata da hannu. Amma wannan na iya ɗaukar lokaci mai yawa. Bugu da kari, ba a soke abin da dan Adam ke haifarwa ba, wanda sakamakon kuskure da karkatar da bayanai ke iya faruwa. Kwamfuta ba ta yin kuskure. Don haka, idan adadin bayanai ya yi yawa, to yana da kyau a yi amfani da aikin IF.

Don cimma burin da aka saita a matakin farko, dole ne a zaɓi tantanin halitta inda za a nuna ƙimar da aka samu kuma rubuta wannan dabarar: =IF(B2=”mace”,25%,0). Bari mu warware wannan aikin:

1. IDAN ma'aikaci ne kai tsaye.
2. B2=”mace” shine ma’aunin da ya kamata a cika.
3. Bayan haka kuma darajar da za a nuna idan an ƙirƙiri sneakers ga mata da kuma darajar da za a nuna idan aka gano cewa sneakers na maza ne, na yara ko duk wani wanda bai cika sharuddan da aka kayyade a muhawara ta farko ba.

A ina ne ya fi kyau a rubuta wannan dabarar? Gabaɗaya, ana iya zaɓar wurin ba bisa ka'ida ba, amma a cikin yanayinmu, waɗannan su ne sel waɗanda ke ƙarƙashin taken "Ragi".

Yana da mahimmanci kada a manta sanya alamar = a gaban dabarar. In ba haka ba, Excel zai karanta shi a matsayin rubutu a sarari.

Bayan shigar da dabarar, kuna buƙatar danna maɓallin shigar, bayan haka tebur zai cika ta atomatik tare da ƙimar daidai. A cikin teburin da ke ƙasa, zamu iya ganin cewa rajistan farko ya zama daidai. Shirin ta atomatik ya ƙayyade jinsi na waɗannan sneakers kuma ya sanya musu rangwame na kwata na farashin. An samu sakamako.

Yanzu ya rage don cika sauran layukan. Don yin wannan, ba kwa buƙatar kwafin dabarar a cikin kowane tantanin halitta daban-daban. Ya isa nemo murabba'in a cikin ƙananan kusurwar dama, matsar da siginan linzamin kwamfuta akansa, tabbatar da cewa ya zama gunkin giciye kuma ja alamar zuwa layin ƙasan tebur. Sannan Excel zaiyi muku komai.

Amfani da Ayyukan IF tare da Sharuɗɗa da yawa

A baya can, an yi la'akari da yanayin mafi sauƙi na amfani da aikin IF, wanda a cikinsa akwai magana mai ma'ana guda ɗaya kawai. Amma idan kuna buƙatar bincika tantanin halitta akan sharuɗɗa da yawa fa? Hakanan ana iya yin wannan ta amfani da ginanniyar ayyukan Excel.

Ɗaya daga cikin lokuta na musamman na bincika wasu sharuɗɗa da yawa shine bincikar cikawa na farko kuma idan ya zama ƙarya, bincika na biyu, na uku, da sauransu. Ko, idan ƙimar gaskiya ce, duba wani ma'auni. Anan, kamar yadda mai amfani yake so, dabaru na ayyuka zasu kasance kusan iri ɗaya. Idan ka karanta abin da aka rubuta a sama cikin tunani, to mai yiwuwa ka riga ka yi hasashen yadda za ka yi. Amma bari mu ƙara ƙarin gani.

Don yin wannan, bari mu sa aikin ya fi wuya. Yanzu muna buƙatar sanya ragi na musamman ga sneakers na mata, amma dangane da wasan da aka yi niyya, girman ragi ya kamata ya bambanta. Dabarar a kallon farko za ta ɗan ɗan fi rikitarwa, amma gabaɗaya, za ta faɗi cikin dabaru iri ɗaya da na baya: =ЕСЛИ(B2=”мужской”;0; ЕСЛИ(C2=”бег”;20%;10%)).

Na gaba, muna yin ayyuka iri ɗaya kamar a cikin yanayin da ya gabata: danna Shigar kuma cika duk layin da ke gaba. Muna samun irin wannan sakamakon.

Ta yaya wannan dabara ke aiki? Aikin farko na farko IF yana duba ko takalmin namiji ne. Idan ba haka ba, to ana aiwatar da aikin na biyu. IF, wanda ya fara bincika idan an tsara takalma don gudu. Idan eh, to an sanya rangwame 20%. Idan ba haka ba, rangwamen shine 10%. Kamar yadda kake gani, ana iya amfani da wasu ayyuka azaman hujjar aiki, kuma wannan yana ba da ƙarin dama.

Yadda ake amfani da aikin IF don cika sharuɗɗa 2 lokaci ɗaya

Bugu da kari, ta amfani da Excel, zaku iya bincika yarda da sharuɗɗa biyu lokaci guda. Don wannan, ana amfani da wani aiki, wanda ake kira И. Wannan ma'aikacin ma'ana yana haɗa yanayi biyu kuma yana yin shi ba kawai a cikin aiki ba IF. Ana iya amfani da shi a wasu ayyuka da yawa kuma.

Mu koma teburin mu. Yanzu rangwamen ya kamata ya fi girma, amma kawai ya shafi takalman mata masu gudu. Idan, bayan dubawa, ya bayyana cewa duka sharuɗɗan sun cika, to, adadin ragi na 30% za a rubuta a cikin filin "Rangwame". Idan an gano cewa aƙalla ɗaya daga cikin sharuɗɗan ba ya aiki, to rangwamen bai shafi irin wannan samfurin ba. Tsarin tsari a wannan yanayin zai kasance: =IF (AND(B2="mace";C2="gudu");30%;0).

Bugu da ari, duk ayyukan da aka yi sun kara maimaita misalan biyu da suka gabata. Da farko, muna danna maɓallin shigar, sannan mu ja darajar zuwa duk sauran sel waɗanda ke cikin wannan tebur.

Ma'anar aikin AND, kamar yadda muke gani, ya ƙunshi muhawara da yawa. Na farko shi ne sharadi na farko, na biyu kuma shi ne na biyu, da sauransu. Kuna iya amfani da mahawara fiye da biyu kuma bincika yanayi da yawa a lokaci ɗaya. Amma a aikace, irin waɗannan yanayi ba safai suke faruwa ba. Fiye da sharuɗɗa uku a lokaci guda - kusan bai taɓa faruwa ba. Jerin ayyukan da wannan aikin yayi shine kamar haka:

1. Na farko, dabarar tana duba yanayin farko - ko takalma na mata ne.
2. Excel sannan yayi nazarin ma'auni na biyu - ko an tsara takalma don gudu.
3. Idan, sakamakon gwajin, ya bayyana cewa duka ma'auni sun dawo da ƙima GASKIYA, sannan sakamakon aikin IF ya zama gaskiya. Don haka, ana yin aikin da aka tsara a cikin hujjar da ta dace.
4. Idan ya tabbata cewa aƙalla ɗaya daga cikin cak ɗin ya dawo da sakamako KARYA, shi da aiki И zai dawo da wannan sakamakon. Saboda haka, sakamakon da aka rubuta a cikin hujja na uku na aikin zai nuna IF.

Kamar yadda kake gani, dabaru na ayyuka abu ne mai sauqi kuma mai sauƙin fahimta akan matakin fahimta.

OR mai aiki a cikin Excel

Ma'aikacin OR yana aiki ta irin wannan hanya kuma yana da makamancin haka. Amma nau'in tabbatarwa ya ɗan bambanta. Wannan aikin yana dawo da ƙima GASKIYA idan akalla cak daya ya dawo da sakamako GASKIYA. Idan duk binciken ya ba da sakamakon ƙarya, to, bisa ga haka, aikin OR mayar da darajar KARYA.

Saboda haka, idan aikin OR  mayar da sakamakon GASKIYA don aƙalla ɗaya daga cikin ƙimar, sannan aikin IF zai rubuta ƙimar da aka ƙayyade a cikin hujja ta biyu. Kuma kawai idan darajar ba ta cika dukkan ka'idoji ba, ana dawo da rubutu ko lambar da aka ƙayyade a cikin hujja na uku na wannan aikin.

Don nuna wannan ƙa'ida a aikace, bari mu sake yin amfani da misali. Matsalar yanzu ita ce mai zuwa: ana ba da rangwamen ko dai akan takalman maza ko takalman wasan tennis. A wannan yanayin, rangwamen zai zama 35%. Idan takalma na mata ne ko an tsara su don gudu, to, ba za a sami rangwame ga irin wannan batu ba.

Don cimma irin wannan burin, kuna buƙatar rubuta wannan dabarar a cikin tantanin halitta, wanda ke tsaye a ƙarƙashin rubutun "Rangwame": =IF(OR(B2="mace"; C2="gudu");0;35%). Bayan mun danna maɓallin shigar kuma ja wannan dabarar zuwa sauran sel, muna samun sakamako mai zuwa.

Yadda ake ayyana aikin IF ta amfani da Formula Builder

Tabbas, zuwa wani matsayi, rubuta dabara da hannu ya fi dacewa fiye da amfani da wasu kayan aikin. Amma idan kun kasance mafari, to, yanayin yana canzawa sosai. Domin kada a ruɗe wajen shigar da mahawara, da kuma nuna daidaitaccen sunan kowane ɗayan ayyukan, akwai kayan aiki na musamman mai suna Function Entry Wizard ko Formula Builder. Bari mu dubi cikakken tsarin aikin sa. A ce an ba mu aikin ta hanyar gudanarwa don bincika samfuran samfuran da ke akwai kuma sanya ragi na 25% ga duk sneakers mata. Jerin ayyuka a wannan yanayin zai kasance kamar haka:

1. Muna buɗe mayen shigar da aikin ta danna maɓallin da ya dace akan Formulas shafin (an yi alama da ja rectangular a cikin hoton hoton).
2. Bayan haka, ƙaramin panel magini yana buɗewa, wanda a ciki muke zaɓar aikin da muke buƙata. Ana iya zaɓar shi kai tsaye daga lissafin ko bincika ta filin bincike. Mun riga muna da shi a cikin jerin 10 na waɗanda aka yi amfani da su kwanan nan, don haka muna danna shi kuma danna maɓallin "Saka Aiki".
3. Bayan haka, taga don saita muhawarar aikin zai buɗe a gaban idanunmu. A kasan wannan rukunin, zaku iya ganin abin da aikin da aka zaɓa yake yi. Kowace gardamar an sanya hannu, don haka ba dole ba ne ka tuna da jeri. Mun fara shigar da magana mai ma'ana wanda ya haɗa da lamba ko tantanin halitta, da kuma ƙimar da za a bincika don bin ka'ida. Na gaba, ana shigar da ƙima idan gaskiya ne kuma ƙima idan ƙarya.
4. Bayan an kammala duk matakan, danna maɓallin "Gama".

Yanzu mun sami sakamako. Tare da shi, muna yin ayyuka iri ɗaya kamar yadda yake a cikin yanayin da ya gabata, wato, muna nuna linzamin kwamfuta a cikin murabba'i a cikin ƙananan kusurwar dama kuma ja da dabara zuwa duk sauran sel. Don haka aikin IF hakika shine mafi shahara kuma muhimmin ma'aikaci a cikin duk abin da ke akwai. Yana bincika bayanan akan wasu sharuɗɗa kuma yana aiwatar da ayyuka masu dacewa idan cak ɗin ya dawo da sakamako. GASKIYA or KARYA. Wannan yana ba ku damar sauƙaƙe sarrafa manyan bayanai kuma kada ku yi babban adadin ayyuka, ƙaddamar da wannan ƙazantaccen aikin zuwa kwamfutar.