Nemo Sakamakon Bayanin Bayanai tare da Excel VLOOKUP

Ta hanyar hada aikin VLOOKUP na Excel tare da aiki na COLUMN za mu iya ƙirƙirar wata hanyar da za ta iya ba ka damar dawo da lambobi masu yawa daga jere guda ɗaya daga cikin bayanai ko labarun bayanai.

A cikin misalin da aka nuna a cikin hoton da ke sama, tsarin bincike yana mai sauƙi don dawo da duk dabi'u - irin su farashin, lambar ɓangaren, da kuma mai sayarwa - alaka da kayan aiki daban-daban.

01 na 10

Koma Ƙididdiga Masu yawa tare da Excel VLOOKUP

Biye da matakai da aka lissafa a ƙasa ya haifar da samfurin binciken da aka gani a cikin hoton da ke sama da zai dawo dabi'u mai yawa daga rikodin bayanai guda.

Tsarin binciken yana buƙatar aikin aikin COLUMN a cikin VLOOKUP.

Nesting wani aiki ya shafi shigar da na biyu aiki a matsayin daya daga cikin muhawara don farko aiki.

A cikin wannan koyo, aikin COLUMN za a shigar a matsayin mahaɗin ƙididdiga na index na VLOOKUP.

Mataki na ƙarshe a cikin koyo shine kayar da tsarin binciken zuwa ƙarin ginshiƙai domin ya sami ƙarin dabi'un da aka zaɓa.

Tutorial abubuwan

• Shigar da Bayanan Tutorial
• Ƙirƙirar Rangi mai suna don Data Data
• Fara aikin WANNAN BUGA
• Shigar da Jayayya mai Mahimmanci tare da amfani da Sakamakon Sakamakon Ƙarshe
• Shigar da Magana Tsarin Gida
• Shigar da Ayyukan COLUMN Nested
• Cikakken aikin VLOOKUP
• Kashe Formula Duba tare da Cika Gyara
• Ana dawo da bayanan tare da tsarin bincike

02 na 10

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 G1
• Shigar da jeri na biyu a cikin sel D4 zuwa G10

Sha'idodin bincike da tsarin binciken da aka yi a wannan darasi zai shiga cikin jere na 2 na takarda.

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 G10

03 na 10

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

Saboda haka sunayen sunaye sun zama madadin yin amfani da cikakkun labaran labaran don hana kurakurai yayin yin kwafi dabara.

Lura: Sunan mahaɗin ba ya haɗa da rubutun ko sunayen filin don bayanai (jere 4) amma kawai bayanan kanta.

Tutorial Steps

1. Sanya sassa D5 zuwa G10 a cikin takardun aiki don zaɓar su
2. Danna kan Akwatin Akwatin da ke saman shafi na A
3. Rubuta "Launin" (ba a faɗi) a cikin Akwatin Sunan ba
4. Danna maballin ENTER akan keyboard
5. Sel D5 zuwa G10 yanzu suna da sunan layin "Table". Za mu yi amfani da sunan don layin jigilar VLOOKUP a baya a cikin koyawa

04 na 10

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

1. Danna kan tantanin halitta E2 na takardun 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 cikin jerin don bude akwatin maganganun

05 na 10

Shigar da Jayayya mai Mahimmanci tare da amfani da Sakamakon Sakamakon Ƙarshe

Yawancin lokaci, darajar binciken ya dace da filin bayanan bayanai a cikin shafin farko na layin bayanai.

A cikin misalinmu, darajan binciken yana nufin sunan ɓangaren hardware game da abin da muke son samun bayani.

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 misali, zamu shigar da tantanin salula a inda za'a sanya sunan sashi - cell D2.

Sakamakon Sakamakon Ƙarƙashin Ƙari

A wani mataki na gaba a cikin koyawa, za mu kwafi tsarin binciken a cikin cell E2 zuwa sassan F2 da G2.

Yawanci, lokacin da aka kofe takardun a Excel, tantancewar labaran sun canza don yin la'akari da sabon wuri.

Idan wannan ya faru, D2 - mahimmin tantancewar tantanin halitta don darajar binciken - zai canza kamar yadda aka tsara takarda don ƙirƙirar kurakurai a cikin sel F2 da G2.

Don hana kurakurai, za mu sake canza tsarin tantanin halitta D2 a cikin cikakkiyar ƙididdigar salula .

Nassoshi masu mahimmanci ba su canza ba lokacin da aka kwafi su.

Ana danganta cikakkun labaran ƙwayoyin salula ta danna maballin F4 akan keyboard. Yin haka yana ƙara alamun dollar a kusa da tantanin salula kamar $ D $ 2

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 sashi game da abin da muke nemo bayani
3. Ba tare da motsa wurin sakawa ba, danna maɓalli F4 a kan keyboard don maida D2 a cikin cikakkiyar ƙididdigar ƙirar $ D $ 2
4. Ka bar akwatin maganganun VLOOKUP don bude mataki na gaba a cikin koyawa

06 na 10

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 mahaɗan da aka halitta a mataki na 3 na koyawa.

Tutorial Steps

1. Danna kan line_array line a cikin maganganun maganganu
2. Rubuta "Launin" (ba a faɗi) don shigar da sunan layi don wannan hujja ba
3. Ka bar akwatin maganganun VLOOKUP don bude mataki na gaba a cikin koyawa

07 na 10

Nesting aikin COLUMN

Bisa ga al'ada VLOOKUP kawai ya dawo da bayanai daga wani shafi na lissafin bayanai kuma wannan shafi an saita shi ta hanyar ƙididdiga ƙididdiga na index .

A cikin wannan misali, duk da haka, muna da ginshiƙai guda uku da muke so mu dawo da bayanai daga haka muna buƙatar hanyar da za mu sauya sauƙin rubutun harafi ba tare da gyara madadin bincikenmu ba.

Wannan shi ne inda aikin COLUMN ya shigo. Ta shigar da shi a matsayin shafi na ƙididdigar index , zai canza a matsayin tsarin da aka bincika daga tantanin halitta D2 zuwa sassan E2 da F2 daga bisani a cikin koyawa.

Ayyukan Nesting

Sabili da haka, aikin COLUMN, yana aiki kamar yadda ake magana da lambar ƙididdiga na Index na VLOOKUP.

An cika wannan ta hanyar aikin aikin COLUMN a ciki na VLOOKUP a cikin layin Col_index_num na akwatin maganganu.

Shigar da aikin COLUMN da hannu

A lokacin da ayyuka na nesting, Excel ba ya ƙyale mu mu buɗe akwatin maganganun na biyu don shigar da muhawararsu.

Saboda haka, aikin COLUMN dole ne a shigar da hannu a cikin layin Col_index_num .

Ayyukan COLUMN yana da gardama guda ɗaya - abin da aka nuna game da ƙididdigewa wanda shine ƙididdigar salula.

Zaɓin Shirin Magana na Kungiyar COLUMN

Ayyukan aikin COLUMN shine don mayar da lambar da aka ba a matsayin Magana .

A wasu kalmomi, ya canza harafin shafi a cikin lamba tare da shafi na A kasancewa na farko shafi, shafi na B na biyu da sauransu.

Tun da farko filin bayanan da muke son dawowa shi ne farashin abu - wanda yake a cikin shafi biyu daga cikin jerin bayanai - za mu iya zaɓar tantancewar tantanin halitta don kowane tantanin halitta a shafi na B a matsayin Magana na Magana don samun lamba 2 don Shaidar Col_index_num .

Tutorial Steps

1. A cikin akwatin maganganun VLOOKUP, danna kan Col_index_num line
2. Rubuta rubutun aikin aiki wanda aka biyo baya da takalmin budewa " ( "
3. Danna sel B1 a cikin takardun aiki don shigar da wannan tantanin halitta a matsayin shaida na Magana
4. Rubuta takalmin rufewa " ) " don kammala aikin COLUMN
5. Ka bar akwatin maganganun VLOOKUP don bude mataki na gaba a cikin koyawa

08 na 10

Shigar da Bincike na VLOOKUP a Kalli Shawara

Magana ta VLOOKUP ta Range_lookup tana da ma'ana daidai (TRUE ko FALSE kawai) wanda ya nuna ko kuna so VLOOKUP don neman ainihin ko kusa da wasan zuwa Lookup_value.

• 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 bayani game da wani abu na musamman, za mu saita Range_lookup daidai da Ƙarya .

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 da rufe akwatin maganganu
4. Tun da ba a riga mun shiga cikin binciken binciken a cikin tantanin halitta D2 ba, kuskuren N / A zai kasance a cell E2
5. Wannan kuskure za a gyara yayin da za mu kara halayen binciken a mataki na karshe na koyawa

09 na 10

Kashe Formula Duba tare da Cika Gyara

Ana tsara tsarin da ake nema don dawo da bayanai daga ginshiƙai na layin bayanan data a lokaci daya.

Don yin wannan, hanyar da ake nema za ta kasance a cikin dukkan fannoni daga abin da muke so bayani.

A cikin wannan koyaswar muna so ne don dawo da bayanan daga ginshiƙai 2, 3, da 4 na kwamfutar bayanai - wannan shine farashin, lambar ɓangaren, da kuma mai amfani da sunan lokacin da muka shigar da sunan suna kamar Lookup_value.

Tun lokacin da aka ƙaddamar da bayanan a cikin takardun aiki , zamu iya kwafi tsarin binciken a cikin E2 E2 zuwa sassan F2 da G2.

Kamar yadda aka kwashe ma'anar, Excel zai sake sabunta tantancewar salula a cikin aikin COLUMN (B1) don yin la'akari da sabon wuri.

Bugu da ƙari, Excel ba ta canja cikakkiyar tantancewar kwayar halitta ta $ D $ 2 da kuma Launin layi mai suna kamar yadda aka kofe shi ba.

Akwai hanya fiye da ɗaya don kwafe bayanai a Excel, amma mai yiwuwa hanya mafi sauki ita ce ta amfani da Fill Handle .

Tutorial Steps

1. Danna kan tantanin halitta E2 - inda aka samo hanyar bincike - don sanya shi tantanin halitta mai aiki
2. Sanya ma'anar linzamin kwamfuta a kan kusurwar baki a cikin kusurwar dama. Macijin zai canza zuwa alamar da aka sanya " + " - wannan shine mai cikawa
3. Danna maɓallin linzamin hagu sannan ja jawo mai cikawa zuwa ga sel G2
4. Saki da maballin linzamin kwamfuta kuma tantanin halitta F3 ya kamata ya ƙunshi nauyin binciken abu biyu
5. Idan an yi daidai, sassan F2 da G2 ya kamata a yanzu sun ƙunshi kuskuren N / A wanda ba a cikin tantanin halitta E2

10 na 10

Shigar da Jagoran Bincike

Da zarar an kayyade nauyin dubawa zuwa sassan da ake buƙata za'a iya amfani dasu don dawo da bayanan daga labarun bayanai.

Don yin haka, rubuta sunan abin da kake son dawowa cikin cellular Lookup_value (D2) kuma danna maballin ENTER akan keyboard.

Da zarar an yi, kowane tantanin halitta wanda ke dauke da tsarin binciken zai ƙunshi wani bangare na daban game da kayan aikin da kake nema.

Tutorial Steps

1. Danna kan tantanin D2 a cikin takardun aiki
2. Rubuta Widget zuwa cikin cell D2 kuma danna maballin ENTER akan keyboard
3. Ya kamata a nuna bayanan nan a cikin kwayoyin E2 zuwa G2:
   • E2 - $ 14.76 - farashin widget din
   • F2 - PN-98769 - sashin lambar don widget din
   • G2 - Widgets Inc. - sunan mai sayarwa don widget din
4. Gwada gwadawa ta hanyar DLOOKUP ta hanyar buga sunan wasu sassa zuwa cikin cell D2 kuma lura da sakamakon a cikin kwayoyin halitta E2 zuwa G2

Idan sakon kuskure kamar #REF! ya bayyana a cikin sassan E2, F2, ko G2, wannan jerin jerin saƙonnin kuskure na VLOOKUP zai taimake ka ka gane inda matsalar ta kasance.