Hanyoyi guda biyu na Excel ta hanyar yin amfani da VLOOKUP Sashe na 1

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

Hanyar Hanya Biyu ta Excel Yin amfani da VLOOKUP. © Ted Faransanci

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

02 na 06

Shigar da Bayanan Tutorial

Hanyar Hanya Biyu ta Excel Yin amfani da VLOOKUP. © Ted Faransanci

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 .

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

  1. 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

Samar da Hanya da aka sanya a cikin Excel. © Ted Faransanci

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

  1. Sanya sassa D5 zuwa G8 a cikin takardun aiki don zaɓar su
  2. Danna kan Akwatin Akwatin da ke saman shafi na A
  3. Rubuta "tebur" (babu maƙalafi) a cikin akwatin akwatin
  4. Danna maballin ENTER akan keyboard
  5. 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

Ana buɗe akwatin maganganu na VLOOKUP. © Ted Faransanci

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

  1. Danna kan tantanin halitta F2 na takarda-aiki - wurin da za a nuna ma'anar tsarin binciken biyu
  2. Danna kan Rubutun shafin shafin rubutun
  3. Danna kan Zaɓuɓɓukan Binciken & Zaɓin zaɓi a cikin rubutun don buɗe jerin abubuwan da aka sauke aikin
  4. Danna kan VLOOKUP a cikin jerin don kawo akwatin maganganun aikin

05 na 06

Shigar da Magana mai Mahimmanci

Hanyar Hanya Biyu ta Excel Yin amfani da VLOOKUP. © Ted Faransanci

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:

A cikin wannan misalin za mu shigar da tantanin salula a inda za'a sanya sunan kuki - cell D2.

Tutorial Steps

  1. Danna kan layin lookup_value a cikin akwatin maganganu
  2. 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

Hanyar Hanya Biyu ta Excel Yin amfani da VLOOKUP. © Ted Faransanci

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 .

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

  1. Danna kan line_array line a cikin maganganun maganganu
  2. Rubuta "tebur" (babu tsinkaye) don shigar da sunan layi don wannan hujja
  3. Ka bar akwatin maganganun VLOOKUP don buɗewa na gaba na tutorial
Ci gaba da Sashe na 2 >>