Fom na Neman Hanya Ta Yi amfani da VLOOKUP

01 na 03

Nemo bayanai zuwa Hagu

Maɓallin Lissafin Lissafi na Tsibi na Excel

Ayyukan VLOOKUP na Excel yana amfani da su don ganowa da kuma mayar da bayanai daga tebur na bayanan da aka dogara akan darajar binciken da ka zaɓa.

Yawanci, VLOOKUP na buƙatar darajar binciken su zama a cikin hagu-mafi yawan ginshiƙan bayanai, kuma aikin ya sake dawo da wani filin bayanan dake cikin jere guda zuwa dama na wannan darajar.

Ta hanyar hada VLOOKUP tare da aikin KASHI; duk da haka, ana iya kirkirar daftarin hanyar hagu wanda zai:

• ya ba da izinin darajar neman zaɓa daga kowane shafi a cikin layin bayanai
• Bayar da bayanin da ke cikin kowane shafi zuwa hagu na darajar binciken

Alal misali: Yin amfani da kayan aiki da kayan aiki a tsarin haɓaka na hagu

Matakan da aka samo a ƙasa ya kirkiro hanyar da aka gani a cikin hoton da ke sama.

Dabarar

= KASHI ($ D $ 2, KASHI ({1,2}, $ F: $ F, $ D: $ D), 2, FALSE)

ya sa ya yiwu a sami ɓangaren da aka ba ta kamfanoni daban-daban da aka jera a shafi na 3 na tarin bayanai.

Ayyukan aikin da aka zaɓa a cikin wannan tsari shi ne yaudarar VLOOKUP a cikin gaskantawa cewa shafi na 3 shi ne ainihin shafi na 1. A sakamakon haka, sunan kamfanin zai iya amfani dashi a matsayin darajar binciken don gano sunan ɓangaren da kamfanonin ke bayar.

Matakan Tutorial - Shigar da Bayanan Tutorial

1. Shigar da rubutun da ke biyowa a cikin kwayoyin da aka nuna: D1 - Mai samar da E1 - Sashe
2. Shigar da tebur na bayanan da aka gani a cikin hoton da ke cikin sel D4 zuwa F9
3. Rukunai na 2 da 3 an bar blank don yada tsarin bincike da kuma tsarin hagu na haɓaka da aka tsara a wannan tutorial

Farawa da Maɓallin Bincike na Hagu - Gyara Akwatin Gida ta VLOOKUP

Ko da yake yana yiwuwa a rubuta nau'ikan da ke sama kai tsaye cikin cell F1 a cikin takardun aiki, mutane da yawa suna da matsala tare da haɗin ma'anar.

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 e2 E2 na takardun aiki - wurin da za a nuna sakamakon da aka samu na hagu
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

02 na 03

Shigar da muhawara a cikin akwatin maganganu na VLOOKUP - Danna don Duba Ƙari Mai Girma

Tambayoyi na VLOOKUP

Ƙididdigar aiki shine dabi'u da aka yi amfani da su don lissafta sakamakon.

A cikin maganganun maganganu, sunan kowace gardama yana samuwa a kan rabaccen layin sannan kuma filin da za a shigar da darajar.

Shigar da dabi'u masu biyowa akan kowane maganganun VLOOKUP akan layin daidai na akwatin maganganu kamar yadda aka nuna a cikin hoto a sama.

Darajar Bincike

Tallafin bincike shine filin bayanin da aka yi amfani da shi don bincika mahaɗin tebur. VLOOKUP ya sake dawo da wani bayanan bayanai daga jere guda ɗaya azaman darajar binciken.

Wannan misali yana amfani da tantancewar salula zuwa wurin da sunan kamfanin zai shiga cikin takardun aiki. Amfani da wannan shi ne cewa yana da sauƙi don canza sunan kamfanin ba tare da gyara wannan tsari ba.

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
3. Latsa maɓallin F4 a kan keyboard don yin tantanin salula dangane da - $ D $ 2

Lura: An yi amfani da nassoshin tantancewar salula don darajar binciken da allon tsararraki don hana kurakurai idan an kayyade nauyin binciken zuwa wasu kwayoyin a cikin takardun aiki.

Rigaren Arba: Shigar da Sakamakon Sakamakon

Tabbatar da tsararran launi na gaba shine asalin bayanan da ke tattare da shi daga abin da aka samo asali na musamman.

A yadda aka saba, VLOOKUP kawai ya dubi dacewa game da ƙwaƙwalwar ƙwaƙwalwar binciken don gano bayanai a cikin tashar tebur. Don samun shi ya dubi hagu, dole ne a yaudare VLOOKUP ta hanyar raya ginshiƙai a cikin tashar tebur ta amfani da aikin da aka zaɓa.

A cikin wannan tsari, da zaɓaɓɓen aikin ya cika ayyuka biyu:

1. shi ya haifar da tsararren tebur wanda kawai ginshiƙai biyu ne - ginshiƙai D da F
2. yana canja wurin hagu na hagu na ginshiƙai a cikin mahallin mahaɗin domin shafi na F ya zo da farko kuma shafi na D shine na biyu

Ƙididdiga game da yadda zaɓaɓɓun aikin da aka yi wa waɗannan ayyuka za a iya samu a shafi na 3 na koyawa .

Tutorial Steps

Lura: Lokacin shigar da ayyuka tare da hannu, dole ne a raba raƙuman muhawarar ta hanyar takaddama "," .

1. A cikin akwatin maganganun VLOOKUP, danna kan Line_array line
2. Shigar da wadannan zaɓaɓɓen aikin
3. Nemi ({1,2}, $ F: $ F, $ D: $ D)

Lambar Shafin Taɓa

Yawanci, lambar mahaɗin shafi na nuna wane ɓangaren ginshiƙin tsararren ya ƙunshi bayanan da kake bayan. A wannan tsari; duk da haka, yana nufin tsarin ginshiƙai da aka zaɓa ta hanyar aikin da aka zaɓa.

Ayyukan da aka zaɓa ya haifar da tsararren launi wanda ke da ginshiƙai guda biyu tare da shafi na F wanda ya biyo baya a shafi na D. Tun da bayanin da ake nema - sunan ɓangaren - yana a cikin shafi na D, ana ƙayyade darajar ƙididdigin shafi na 2.

Tutorial Steps

1. Danna kan layin Col_index_num a cikin akwatin maganganu
2. Rubuta 2 a wannan layi

Binciken Range

Maganar VLOOKUP ta Range_lookup tana da mahimmanci na gaske (TRUE ko FALSE kawai) wanda ya nuna ko kana so VLOOKUP don samo ainihin ko kimanin dacewa zuwa darajar binciken.

• Idan TRUE ko kuma idan an cire wannan hujja , VLOOKUP ya dawo ko dai daidai daidai da Lookup_value, ko, idan ba'a samo daidai ba, VLOOKUP ya sake dawowa mafi girma. Domin dabarun da za a yi haka, dole ne a ware jigilar bayanai a cikin shafi na farko na Table_array a tsari mai girma .
• Idan FALSE, VLOOKUP kawai zai yi amfani da ainihin daidai zuwa Lookup_value. Idan akwai dabi'u biyu ko fiye a cikin shafin farko na Table_array wanda yayi daidai da darajar binciken, ana amfani da darajar farko. Idan ba a samo daidai ba, ana mayar da kuskuren N / A.

A cikin wannan koyo, tun da muna neman sunan wani ɓangare, Range_lookup za a saita zuwa Ƙarya domin kawai matakan da aka mayar da su ta hanyar dabarar.

Tutorial Steps

1. Danna kan Range_lookup line a cikin akwatin maganganu
2. Rubuta kalma ƙarya a cikin wannan layi don nuna cewa muna so VLOOKUP ya dawo daidai daidai don bayanan da muke nema
3. Danna Ya yi don kammala tsarin bincike na hagu kuma rufe akwatin maganganu
4. Tun da ba mu riga mun shiga sunan kamfanin ba cikin cell D2, kuskuren N / A ya kasance a cell E2

03 na 03

Gwada tsarin Kayan Hagu na Hagu

Bayanin da aka dawo tare da Formup Lookup Formula

Don gano wace kamfanonin suna samar wacce sassa, rubuta sunan kamfanin a cell D2 kuma danna maballin ENTER akan keyboard.

Za a nuna sunan sashi a cikin wayar E2.

Tutorial Steps

1. Danna kan tantanin halitta D2 a cikin takardar aikinku
2. Rubuta Gadgets a cikin D2 kuma danna maballin ENTER akan keyboard
3. Rubutun Gadget - rabon da kamfanin Gadgets Plus ya bayar - ya kamata a nuna a cikin tantanin halitta E2
4. Gwada hanyar da aka gano ta hanyar buga wasu kamfanoni a cikin tantanin halitta D2 kuma sunan sashi na daidai ya kamata ya bayyana a cell E2

Saƙonnin kuskuren VLOOKUP

Idan sakon kuskure irin su # N / A ya bayyana cikin tantanin halitta E2, duba farko don kurakuran rubutu a cikin tantanin halitta D2.

Idan rubutun ba shine matsala ba, wannan rukunin saƙonnin kuskure na VLOOKUP zai taimake ka ka gane inda matsalar ta kasance.

Kaddamar da Ƙaƙƙashin Aikin Ayyuka

Kamar yadda aka ambata, a cikin wannan tsari, aikin da aka zaɓa yana da ayyuka biyu:

• shi ya haifar da tsararren tebur wanda kawai ginshiƙai biyu ne - ginshiƙai D da F
• yana canja wurin hagu na hagu na ginshiƙai a cikin mahallin mahaɗin domin shafi na F ya zo da farko kuma shafi na D shine na biyu

Ƙirƙirar Ƙungiyoyi na Biyu

Hadawa don aikin da aka zaɓa shine:

= CHOOSE (Index_number, Value1, Value2, ... Value254)

Ayyukan Sakamakon yana dawowa ɗaya daga darajar lambobi (Value1 zuwa Value254) bisa ga lambar index ɗin da aka shigo.

Idan lambar index ita ce 1, aikin ya dawo Value1 daga jerin; idan lambar index yana da 2, aikin zai dawo Value2 daga lissafi da sauransu.

Ta shigar da lambobi masu yawa; duk da haka, aikin zai dawo da dabi'u mai yawa a kowane umurni da ake bukata. Samun KASHE don dawo da dabi'u mai yawa anyi ta hanyar ƙirƙirar tsararru .

Shigar da tsararren yana cika ta kewaye da lambobin da aka shigar tare da takalmin gyare-gyare ko ƙuƙwalwar. An shigar da lambobi biyu don lambar index: {1,2} .

Ya kamata a lura cewa CHOOSE ba'a iyakance ga ƙirƙirar tebur guda biyu ba. Ta hada da ƙarin lambar a cikin tsararraki - irin su {1,2,3} - da ƙarin ƙarin bayani a cikin ƙwararrayar darajar, za'a iya ƙirƙirar tebur uku.

Ƙarin ƙarin ginshiƙai zai ba ka damar dawo da bayanai daban-daban tare da maɓallin binciken hagu ta hanyar canza hanyar VLOOKUP ta mahaɗin ƙididdiga zuwa lambar mahaɗin da ke dauke da bayanin da ake so.

Canza Canjin ginshiƙai tare da Sakamakon Sakamakon

A cikin aikin da aka yi amfani da shi a cikin wannan tsari: Zaɓi ({1,2}, $ F: $ F, $ D: $ D) , an saita ɗakin don shafi na F a gaban rukunin D.

Tun da aikin da aka yi amfani da shi ya tsara zane-zane na VLOOKUP - asalin bayanan don wannan aikin - sauya tsari na ginshiƙai a cikin aikin CHOOSE ya wuce zuwa VLOOKUP.

Yanzu, kamar yadda VLOOKUP ke damuwa, tashar tebur tana da ginshiƙai biyu ne kawai tare da shafi F a gefen hagu da shafi D a dama. Tunda shafi na F ya ƙunshi sunan kamfanin da muke so mu bincika, kuma tun lokacin da shafi na D ya ƙunshi sunayen yanki, VLOOKUP zai iya yin aikinsa na al'ada na neman gano bayanan da aka samo zuwa hagu na darajar binciken.

A sakamakon haka, VLOOKUP zai iya amfani da sunan kamfanin don neman sashin da suke samarwa.