Ta hanyar hada aikin VLOOKUP na Excel tare da aikin MATCH , za mu iya ƙirƙirar abin da aka sani da hanyar hanya guda biyu ko nau'i-nau'i na biyu wanda ya ba ka damar saukewa da bangarori biyu na bayanai a cikin bayanan bayanai ko tebur na bayanai.
Wata hanya mai amfani da hanyoyi guda biyu yana da amfani lokacin da kake so ka samo ko kwatanta sakamakon saboda yanayi daban-daban.
A cikin misalin da aka nuna a cikin hoton da ke sama, tsarin bincike yana mai sauƙi don dawo da tallan tallace-tallace don daban-daban kukis a cikin watanni daban-daban ta hanyar canza sunan cookie da wata a cikin sassan daidai.
01 na 06
Nemi Bayanai a Tsarin Tsarin Hanya da Tsarin
Wannan tutorial ya rushe zuwa sassa biyu. Biye da matakan da aka jera a kowane ɓangaren ya haifar da hanyar da aka gano a cikin hoto a sama.
Wannan koyo ya haɗa da aikin MATCH a ciki na VLOOKUP.
Nesting wani aiki ya shafi shigar da na biyu aiki a matsayin daya daga cikin muhawara na farko aiki.
A cikin wannan koyo, aikin MATCH za a shigar a matsayin mahaɗin ƙididdiga na index don VLOOKUP.
Tutorial abubuwan
- Shigar da Bayanan Tutorial
- Ƙirƙirar Rangi mai suna don Data Data
- Fara aikin WANNAN BUGA
- Shigar da Magana mai Mahimmanci
- Shigar da Magana Tsarin Gida
- Fara Ayyukan MATCH Nested
- Ƙara Rukunin Ranar bayanai don aikin MATCH
- Ƙara nau'in matsala da kuma kammala aikin MATCH
- Cikakken aikin VLOOKUP
- Ƙara Shafin Bincike don Gwada Samun Ƙarshe
- Yin kwaskwarima tsarin ƙira guda biyu tare da cika cika
02 na 06
Shigar da Bayanan Tutorial
Mataki na farko a cikin koyawa shine shigar da bayanai a cikin takardar aikin Excel.
Don bi matakai a cikin koyawa shigar da bayanai da aka nuna a hoton da ke sama zuwa cikin wadannan kwayoyin .
- Shigar da kewayon bayanai a cikin sel D1 zuwa F1
- Shigar da jeri na biyu a cikin sel D4 zuwa G8
Rukunai na 2 da 3 an bar blank domin yada tsarin bincike da tsarin da aka samo a lokacin wannan koyawa.
Koyarwar ba ta haɗa da tsarin da aka gani a cikin hoton ba, amma wannan ba zai tasiri yadda tsarin binciken yake aiki ba.
Zaɓuɓɓukan bayani game da tsarin tsarawa kamar waɗanda aka gani a sama suna samuwa a wannan Basic Tutel Tutorial .
Tutorial Steps
- Shigar da bayanai kamar yadda aka gani a cikin hoton da ke cikin sel D1 zuwa G8
03 na 06
Ƙirƙirar Rangi mai suna don Data Data
Hanyar mai suna shine hanya mai sauƙi don komawa zuwa kewayon bayanai a cikin wata hanya. Maimakon bugawa a cikin tantanin salula don bayanai, za ka iya danna sunan filin kawai.
Amfani na biyu don yin amfani da lakabi mai suna shine cewa tantancewar salula don wannan kewayon bata canzawa ko da lokacin da aka kofe wannan tsari zuwa wasu kwayoyin a cikin takardun aiki.
Tutorial Steps
- Sanya sassa D5 zuwa G8 a cikin takardun aiki don zaɓar su
- Danna kan Akwatin Akwatin da ke saman shafi na A
- Rubuta "tebur" (babu maƙalafi) a cikin akwatin akwatin
- Danna maballin ENTER akan keyboard
- Sel D5 zuwa G8 yanzu suna da sunan layin "launi". Za mu yi amfani da sunan don layin jigilar VLOOKUP a baya a cikin koyawa
04 na 06
Ana buɗe akwatin maganganu na VLOOKUP
Kodayake yana yiwuwa a rubuta hanyar da muke nema kai tsaye a cikin tantanin halitta a cikin takardun aiki, mutane da yawa suna da wuya a ci gaba da daidaitawa ta hanyar sadarwa - musamman ga wani tsari mai mahimmanci kamar wanda muke amfani dashi a cikin wannan koyo.
Wani madadin, a wannan yanayin, shine a yi amfani da akwatin maganganun VLOOKUP. Kusan dukkan ayyuka na Excel yana da akwatin maganganu wanda ke ba ka damar shigar da kowannen muhawarar aikin a kan layi.
Tutorial Steps
- Danna kan tantanin halitta F2 na takarda-aiki - wurin da za a nuna ma'anar tsarin binciken biyu
- Danna kan Rubutun shafin shafin rubutun
- Danna kan Zaɓuɓɓukan Binciken & Zaɓin zaɓi a cikin rubutun don buɗe jerin abubuwan da aka sauke aikin
- Danna kan VLOOKUP a cikin jerin don kawo akwatin maganganun aikin
05 na 06
Shigar da Magana mai Mahimmanci
Yawancin lokaci, darajar binciken ya dace da filin bayanan bayanai a cikin shafin farko na layin bayanai.
A cikin misalinmu, darajar binciken tana nufin irin kuki muna so mu sami bayani game da.
Abubuwan da aka ba da izinin don darajar binciken sune:
- bayanan rubutu
- wani ƙimar ma'ana (TRUE ko FALSE kawai)
- lamba
- tantancewar salula akan darajar a cikin takarda
A cikin wannan misalin za mu shigar da tantanin salula a inda za'a sanya sunan kuki - cell D2.
Tutorial Steps
- Danna kan layin lookup_value a cikin akwatin maganganu
- Danna kan tantanin halitta D2 don ƙara wannan tantanin halitta zuwa layin lookup_value . Wannan shi ne tantanin halitta inda za mu rubuta sunan kuki game da abin da muke nemo bayani
06 na 06
Shigar da Magana Tsarin Gida
Teburin tashar shi ne tebur na bayanan da binciken da ake nema don neman bayanin da muke so.
Dogayen tebur ya ƙunshi akalla ginshiƙai guda biyu na bayanai .
- shafi na farko ya ƙunshi ƙimar ƙimar binciken (mataki na baya a cikin koyawa)
- na biyu, da kowane ginshiƙan ƙarin, zabin bincike zai nema don gano bayanin da muka ƙayyade.
Dole ne a shigar da hujjar layin jigon layi kamar yadda kewayon da ke dauke da tantancewar salula don teburin bayanai ko a matsayin sunan mahaɗin .
Don wannan misali, zamu yi amfani da sunan mahadin da aka tsara a mataki na 3 na wannan koyawa.
Tutorial Steps
- Danna kan line_array line a cikin maganganun maganganu
- Rubuta "tebur" (babu tsinkaye) don shigar da sunan layi don wannan hujja
- Ka bar akwatin maganganun VLOOKUP don buɗewa na gaba na tutorial