Contents
Domin nemo kimar tantanin halitta da ke tsakiyar mahadar ginshiƙi da jeri a cikin jeri na tebur a cikin Microsoft Office Excel, dole ne ku yi amfani da aikin “INDEX”, da kuma “SEARCH” ƙarin taimako. Ana buƙatar neman ƙima a cikin tsararru lokacin da mai amfani ke aiki tare da babban tebur, kuma yana buƙatar "jawo" jerin bayanai. Wannan labarin zai dubi cikakken algorithm don amfani da aikin "INDEX" don nemo ƙima a cikin tsararru.
Yin rikodin aikin "INDEX".
Ana rubuta irin wannan ma'aikacin tsararru kamar haka: =INDEX(array; lambar layi; lambar shafi). Maimakon kalmomi a cikin baka, ana nuna madaidaitan lambobi na sel a cikin tebur na asali.
Yin rikodin aikin "MATCH".
Wannan ma'aikacin taimako ne don aikin farko, wanda kuma za'a yi amfani dashi lokacin neman ƙima a cikin tsararru. Rikodinsa a cikin Excel yayi kama da haka: = MATCH (darajar samun; tebur array; nau'in wasa).
Kula! Lokacin rubuta hujja don aikin INDEX, lambar shafi na zaɓi ne.
Yadda ake samun ƙima a cikin tsararru
Don fahimtar batun, dole ne a yi la'akari da algorithm don yin aikin ta amfani da takamaiman misali. Bari mu yi tebur na oda a cikin Excel na rana ɗaya, inda za a sami ginshiƙai: “lambar odar”, “Abokin ciniki”, “Kayayyakin”, “Yawan”, “Farashin Unit”, “Ƙiddiga”. Kuna buƙatar nemo ƙima a cikin tsararru, watau ƙirƙira katin odar abokin ciniki ɗaya ɗaya don ku sami bayanai a cikin nau'i mai matsi daga sel na asali na tebur.
Don yin wannan, kuna buƙatar aiwatar da jerin ayyuka bisa ga algorithm:
- Ƙirƙiri katin odar abokin ciniki.
- Don layin farko na katin, kuna buƙatar ƙirƙirar jerin zaɓuka wanda za a rubuta sunayen abokan ciniki daga babban tsarin. Daga baya, ta zaɓar wani suna, mai amfani zai ga taƙaitaccen bayani akansa, waɗanda za a nuna su a cikin wasu layin katin oda.
- Sanya siginan linzamin kwamfuta a layin farko na katin kuma shigar da sashin "Data" a saman babban menu na shirin.
- Danna maɓallin "Tabbatar Data".
- A cikin taga da ya bayyana, a cikin filin "Nau'in Bayanai", zaɓi zaɓin "List", sannan zaɓi kewayon sel na ainihin tsararrun a matsayin tushen, wanda jerin duk abokan ciniki ke rajista.
- Kibiya zata bayyana a gefen dama na tantanin halitta a layin farko na katin. Idan ka danna shi, zaka iya ganin jerin duk abokan ciniki. Anan kuna buƙatar zaɓar kowane abokin ciniki.
- A cikin layin "lambar oda" rubuta aikin «=INDEX(», sannan danna alamar "fx" kusa da mashaya dabarar Excel.
- A cikin menu na Wizard na Aiki da ke buɗewa, zaɓi tsarin tsararru don aikin “INDEX” daga lissafin kuma danna “Ok”.
- Za a buɗe taga "Hujjar Aiki", wanda a ciki kake buƙatar cika dukkan layi, yana nuna ma'auni na sel.
- Da farko kana buƙatar danna gunkin da ke gaban filin "Array" kuma zaɓi gaba ɗaya farantin asali tare da rubutun kai.
- A cikin filin "Layin layi" kuna buƙatar cika aikin "MATCH". A farkon wuri a cikin mahaifa, a matsayin hujja, muna nuna sunan abokin ciniki wanda aka zaɓa a cikin katin tsari. A matsayin hujja na biyu na aikin "MATCH", kuna buƙatar ƙayyade duk kewayon abokan ciniki a cikin tsararrun tebur na asali. A maimakon hujja na uku, dole ne ka rubuta lamba 0, saboda zai nemi daidai daidai.
Muhimmin! Bayan cika kowane kashi don aikin "MATCH", kuna buƙatar danna maɓallin "F4" don rataya alamun dala a gaban kowane hali a cikin gardama. Wannan zai ba da damar dabarar kada ta “fita” a cikin aiwatar da aiwatarwa.
- A cikin layin "Lambar shafi" sake rubuta aikin taimako "MATCH" tare da hujjoji masu dacewa.
- A matsayin hujja ta farko don aikin, dole ne ka saka tantanin halitta mara komai a cikin layin "samfurin" a cikin katin tsari. A lokaci guda kuma, ba lallai ba ne don rataya alamun dala a kan muhawara, saboda hujjar da ake so ya kamata ya zama "mai iyo".
- Cika hujja ta biyu na aikin "MATCH", kuna buƙatar zaɓar taken tsararrun tushen, sannan danna maɓallin "F4" don gyara haruffa.
- A matsayin hujja ta ƙarshe, dole ne ka rubuta 0, rufe sashin kuma danna "Ok" a kasan akwatin "Hujjar Ayyuka". A wannan yanayin, lambar 0 daidai ne.
- Duba sakamakon. Bayan yin irin waɗannan ayyuka masu tsayi, lambar da ta dace da abokin ciniki da aka zaɓa ya kamata a nuna shi a cikin layin "lambar oda".
- A mataki na ƙarshe, tsarin zai buƙaci a shimfiɗa shi zuwa duk sel na katin oda har zuwa ƙarshe don cika sauran layin.
Ƙarin Bayani! Lokacin da aka zaɓi abokin ciniki daga jerin zaɓuka na katin oda, duk bayanan wannan mutumin za a nuna su a cikin sauran layuka na tsararru.
Kammalawa
Don haka, don nemo ƙimar da ake so a cikin tsararru a cikin Microsoft Office Excel, mai amfani zai yi ayyuka da yawa. A sakamakon haka, ya kamata a sami ƙaramin farantin bayanai, wanda ke nuna bayanan da aka matse don kowane ma'auni daga tsararrun asali. Hanyar neman dabi'u tare da hotuna masu dacewa an tattauna su dalla-dalla a sama.