Fumana iindidi ezininzi zeDatha nge-Excel VLOOKUP

Ngokudibanisa umsebenzi we-VLOOKUP nge-COLUMN umsebenzi we-Excel singayenza ifom ye-lookup evumela ukuba ubuyeke ixabiso lamanani ukusuka kumqolo omnye wedatha okanye itafile yedatha.

Kulo mzekelo oboniswe kumfanekiso ongentla, ifom ye-lookup yenza kube lula ukubuyisela zonke ixabiso-njengowamaxabiso, inombolo yenxenye, kunye nomthengisi-ezinxulumene neziqhekeza ezahlukeneyo ze-hardware.

01 ngo-10

Buyisela iimilinganiselo ezininzi nge-Excel VLOOKUP

Ukulandela amanyathelo alulwe apha ngezantsi kudala ifom ye-lookup ebonwe kumfanekiso ongentla oya kubuyela amanqaku amaninzi kwiirekodi enye yedatha.

Imfomula yokufunwa idinga ukuba umsebenzi we-COLUMN ube nendlu ngaphakathi kweVLOOKUP.

Ukuhlawula umsebenzi kubandakanya ukufaka umsebenzi wesibini njengenye yeengxoxo malunga nomsebenzi wokuqala.

Kule khokelo, umsebenzi we-COLUMN uya kufakwa njengengxabano yenombolo yekholomu ye-VLOOKUP.

Isinyathelo sokugqibela kwisifundo sifaka ukukopisha ifom ye-lookup kwiikholomu ezongezelelweyo ukuze ufumane amanani angaphezulu kwinqanaba elikhethiweyo.

Iziqulatho zeTutorial

• Ukufaka i-Tutorial Data
• Ukudala umgama obizwa ngokuba yiTable Table
• Ukuqala umsebenzi weVLOOKUP
• Ukungena kwexabiso lokuLungiswa kweMali yokuSebenzisa usebenzisa iiReferensi zeeNgcaciso zeSeli
• Ukungena kwiTable Array Argument
• Ukufaka umsebenzi weCOLUMN weNested
• Ukugqiba umsebenzi weVLOOKUP
• Ukukopisha i-Formula yeLowokuPhepha ngeSandla sokuzalisa
• Ukubuyisela iDatha ngeFomula Lookup

02 ngo 10

Faka idatha yeTutorial

Isinyathelo sokuqala kwisifundo kukufaka idatha kwiphepha lokusebenzela lase Excel.

Ukuze ulandele amanyathelo ekufundiseni faka idatha eboniswe kumfanekiso ongentla apha kwiiseli ezilandelayo.

• Faka uluhlu oluphezulu lweedatha kwiiseli D1 ukuya kwi-G1
• Faka udidi lwesibini kwiiseli D4 ukuya kwi-G10

I-criteria yokukhangela kunye nefom ye-lookup eyenziwe ngeli thuba liya kufakwa kumgca 2 wephepha lokusebenzela.

I-tutorial ayifaki ukufomatha ebonwe kumfanekiso, kodwa oku akukuchaphazela indlela ifom ye-lookup isebenza ngayo.

Ulwazi malunga nokufometha okukhethiweyo kufana nalabo abonwe ngentla lufumaneka kule Siseko yoQuquzelelo lwe-Excel .

Iimfundiso zeTutorial

1. Faka idatha njengoko iboniswe kumfanekiso ongentla kwiiseli D1 ukuya kwi-G10

03 ngo 10

Ukudala umgama obizwa ngokuba yiTable Table

Uluhlu olukhankanyiweyo luyindlela elula yokubhekisela kwimiba yedatha kwifom. Esikhundleni sokuthayibha kwiinkcukacha zeeseli zedata, unokwenza nje uthayiphe igama loluhlu.

Inzuzo yesibini yokusebenzisa uluhlu olukhankanyiweyo kukuba iiferensi zesellulululuhlu aluzange zitshintshe nangona ifom ekopishwa kwezinye iiseli kwiphepha lokusebenzela.

Amagama amaninzi, ngoko ke, enye indlela yokusebenzisa iiferensi zeseli ngokupheleleyo ukukhusela iimpazamo xa ukopisha amafomula.

Qaphela: Igama legama alibandakanyi amagama okubhaliweyo okanye kwintsimi yedatha (umgca 4) kodwa kuphela idatha ngokwayo.

Iimfundiso zeTutorial

1. Gqamisa iiseli D5 ukuya kwi-G10 kwiphepha lokusebenzela ukuze ukhethe
2. Cofa kwiBhokisi legama elisezantsi kwekholam A
3. Thayipha "Itafile" (akukho zicatshulwa) kwiBhokisi legama
4. Cinezela i- ENTER iqhosha kwikhibhodi
5. Iiseli ze-D5 ukuya kwi-G10 ngoku zinegama loluhlu "lwetafile". Siza kusebenzisa igama kwi-VLOOKUP itheyibhile yenkcazo elandelayo emva kwesikhokelo

04 we-10

Ukuvula i-VLOOKUP Dialog Box

Nangona kunokwenzeka ukuba uthayiphe ifom ye-lookup ngokuthe ngqo kwiseli kwiphepha lokusebenzela, abaninzi abantu bakuthola kunzima ukugcina iqhosha le- syntax ngqo-ngokukodwa kwifomula eyinkimbinkimbi njengaleyo esisisebenzisa kule khokelo.

Ngenye indlela, kulo mzekelo, kukusebenzisa ibhokisi yencoko yeVLOOKUP. Phantse yonke imisebenzi ye-Excel ibhokisi yencoko yababini evumela ukuba ungene kwiingxoxo zomsebenzi ngamnye kumgca ohlukile.

Iimfundiso zeTutorial

1. Cofa kwifowuni E2 kwiphepha lokusebenzela - indawo apho iziphumo zefomula yokudibanisa ezimbini ziza kuboniswa
2. Cofa kwiThebhu yefomula yerbhoni
3. Cofa kwikhetho yeLowokup & Reference kwi-ribbon ukuvula uluhlu lwehla phantsi
4. Cofa kwiVLOOKUP kuloluhlu ukuvula ibhokisi yencoko yencoko yomsebenzi

05 we-10

Ukungena kwexabiso lokuLungiswa kweMali yokuSebenzisa usebenzisa iiReferensi zeeNgcaciso zeSeli

Ngokuqhelekileyo, ixabiso lokungena lifana nensimu yedata kwikholamu yokuqala yedatha yedatha.

Ngokomzekelo wethu, ixabiso lokulunga libhekisela egameni le-hardware inxalenye malunga nathi sifuna ukufumana ulwazi.

Iindidi ezivumelekileyo zedatha yexabiso lookup :

• idatha yombhalo
• xabiso elungileyo (INYANISO okanye FALSE kuphela)
• inombolo
• iselferensi yeseli kwixabiso kwiphepha lokusebenzela

Kulo mzekelo, siya kufaka iselferensi yeseli apho igama legama liza kuba khona-iseli D2.

Iifomferensi zeeNombolo ezingekho

Kwixesha elizayo kwinqanaba lokufundisa, siya kukopa ifom ye-lookup kwiseli E2 kwiiseli F2 kunye ne-G2.

Ngokuqhelekileyo, xa iifomula zikopishwa kwi-Excel, iiferensi zeeselitshi zitshintsha ukubonisa indawo yazo entsha.

Ukuba oko kwenzeka, i-D2 - iselferensi yeseli yexabiso lookupakisha - liya kutshintsha njengoko ifomu ikopishwa ukudala iiphene kwiiF2 kunye ne-G2.

Ukuthintela iimpazamo, siza kuguqula isalathisi yesibini D2 kwisithensi eselungileyo .

Izikhombisi zeeseli ezingaphendukiyo azitshintshi xa iifomula zikopishwa.

Izikhombisi zeeseli ezingapheliyo zenziwa ngokucinezela iqhosha leF4 kwibhodibhodi. Ukwenza oko kufaka iidola iimpawu ezijikelezayo kwi-cell reference njengama $ D $ 2

Iimfundiso zeTutorial

1. Cofa kumgca we- lookup_value kwibhokisi yencoko
2. Cofa kwiseli D2 ukuze ungeze le referensi yeseli kumgca we- lookup_value . Le yiseli apho siza kufaka igama legama malunga nathi sifuna ulwazi
3. Ngaphandle kokuhambisa inqaku lokufaka, cinezela iqhosha le- F4 kwikhibhodi ukuguqula i-D2 kwisithensi se-D $ 2.
4. Shiya ibhokisi yencoko yebhokisi yeVLOOKUP evuliwe kwisinyathelo esilandelayo kwisifundo

06 ngo 10

Ukungena kwiTable Array Argument

Itafile yetafile yitheyibhile yedatha apho ifom ye-lookup ifuna ukufumana ulwazi esiyifunayo.

Uludwe lwetafile kufuneka luqule ubuncinane iikholomu ezimbini zeedatha .

• Ikholam yokuqala iqulethe ingxabano yexabiso lookup (isinyathelo sangaphambili kwisifundo)
• okwesibini, kunye nayiphi na iikholomu ezongezelelweyo, ziya kuphandwa ngefowuni yokungena ukuze ufumane ulwazi esilucacisayo.

I-array array argument kufuneka ingeniswe njengoluhlu oluqulethe iinkcazo zeeseli kwitheyibhile yedata okanye njengegama legama .

Kulo mzekelo, siza kusebenzisa igama legama elidalwe kwisigaba sesi-3 se-tutorial.

Iimfundiso zeTutorial

1. Cofa kwitafile_layini lomgca kwibhokisi yencoko
2. Thayipha "Itafile" (akukho zicatshulwa) ukungena kwigama legama kule ngxabano
3. Shiya ibhokisi yencoko yebhokisi yeVLOOKUP evuliwe kwisinyathelo esilandelayo kwisifundo

07 ngo 10

Ukuhlawula umsebenzi weCOLUMN

Ngokuqhelekileyo iVLOOKUP ibuyisela kuphela idatha ukusuka kwikholamu enye yedatha yedatha kwaye eli kholomu libekwe ngxabano yenombolo yenkcazo yekholomu .

Kulo mzekelo, ke, sinamakholomu amathathu enqwenela ukubuyisela idatha ukususela ngoko sifuna indlela yokutshintsha idijithali yenombolo yekholomu ngaphandle kokuhlela ifom ye-lookup.

Yilapho umsebenzi we-COLUMN ungena khona. Ngokungena njengombambano wenombolo yenkalo yenkomfa, kuya kutshintsha njengoko ifom ye-lookup ikopiwe kwiseli D2 kwiiseli E2 kunye noF2 kamva kwinqanaba lokufundisa.

ImiSebenzi yokuHlola

Umsebenzi weCOLUMN, ngoko, usebenza njenge-VLOOKUP's column index number argument .

Oku kufezekiswa ngokukhupha umsebenzi we-COLUMN ngaphakathi kweVLOOKUP kwi- Col_index_num umgca webhokisi yencoko.

Ukungena Umsebenzi weCOLUMN Ngokuzimeleyo

Xa imisebenzi yesidima, i-Excel ayivumeli ukuba sivule ibhokisi yencoko yomsebenzi we sibini ukufaka iingxabano zayo.

Umsebenzi we-COLUMN, ngoko, kufuneka ungenwe ngesandla kwiCol_index_num line.

Umsebenzi we-COLUMN unempikiswano eyodwa kuphela - impikiswano yokuBhekisana neneterensi yeseli.

Ukukhethwa kweNkcazo yeMisebenzi yeCOLUMN

Umsebenzi we-COLUMN umsebenzi wokubuyisela inani lekholomu enikezwe njengengxabano yokubhekisa .

Ngamanye amagama, iguqula ikholomu yekholomu kwinombolo enekholomu A ibe yikholamu yokuqala, ikholamu B yesibini nokunye.

Ekubeni intsimi yokuqala yedatha esifuna ukuyibuyisela yintengo yento - ekhompyutheni yetafile yedatha - sinokukhetha inkcazo yeseluncedo nayiphi na iseli kwikholamu B njengeNgcaciso yeNtetho ukuze ufumane inombolo yesi-2 i- Col_index_num ingxabano.

Iimfundiso zeTutorial

1. Kwibhokisi yencoko yebhokisi yeVLOOKUP, nqakraza kwiCol_index_num line
2. Thayipha igama lekholomu yegama elilandelwa libhokisi elivulekileyo " ( "
3. Cofa kwifowuni B1 kwiphepha lokusebenzela ukuze ufake eso senzo yeselenki njengengxabano yokubhekisa
4. Thayipha ibhondi yokuvala " ) " ukugqiba umsebenzi weCOLUMN
5. Shiya ibhokisi yencoko yebhokisi yeVLOOKUP evuliwe kwisinyathelo esilandelayo kwisifundo

08 ngo 10

Ukufaka i-VLOOKUP Range Lookup Argument

I-RLO_lookup ingxabano yinto enengqiqo (INYANISO okanye FALSE kuphela) ebonisa ukuba ufuna iVLOOKUP ukufumana umdlalo okanye umlinganiselo olinganayo kwiLookup_value.

• Ukuba i-TRUE okanye ukuba le ngxabano ingashiywanga, i-VLOOKUP ibuyisela umdlalo ochanekileyo kwiLookup_value, okanye, ukuba umdlalo ochanekileyo awutholakali, iVLOOKUP ibuyisela ixabiso elilandelayo likhulu. Ngendlela yokwenza oku, idatha kwikholamu yokuqala yeTable_array kufuneka ihlelwe ngokunyuka komyalelo .
• Ukuba uFALSE, i-VLOOKUP izakusebenzisa kuphela umdlalo ochanekileyo kwiLookup_value. Ukuba kukho iimbali ezimbini okanye ngaphezulu kwikholam yokuqala yeTable_array efanisa ixabiso lookup, ixabiso lokuqala elifunyenweyo lisetyenzisiweyo. Ukuba umdlalo ofanelekileyo awufumananga, ikopi ye-# N / A ibuyiselwe.

Kule khokelo, ekubeni sifuna ulwazi oluthe ngqo malunga neprojekti ethile yeehadi, siza kuseka iRange_lookup elilingana nobuxoki.

Iimfundiso zeTutorial

1. Cofa kumgca weRange_lookup kwibhokisi yencoko
2. Thayipha igama lobuxoki kulo mgca ukubonisa ukuba sifuna i-VLOOKUP ukubuyisela umdlalo ochanekileyo wedatha esiyifunayo
3. Cofa Kulungile ukuqedela ifom yefowuni kunye nebhokisi yencoko yokuvala
4. Ekubeni singakangena kwi-criteria ye-lookup kwisisele se-D2 i- # N / A iphoso iya kubakho kwi-cell E2
5. Le mpazamo iya kulungiswa xa siza kudibanisa iikhrayithi zokungena kwi-step of step of the tutorial

09 we-10

Ukukopisha i-Formula yeLowokuPhepha ngeSandla sokuzalisa

Ifom ye-lookup ihlose ukufumana idatha esuka kwiikholamu ezininzi zeetafile yedatha ngexesha elinye.

Ukwenza oku, ifom ye-lookup kufuneka ihlale kuyo yonke indawo esifuna kuyo ulwazi.

Kule tutorial sifuna ukuba ibuyise idatha ukusuka kwiikholamu 2, 3, kunye ne-4 yedatha yedatha-leyo yintengo, inamba yecandelo, kunye negama lomthengisi xa sifaka igama legama njengeLookup_value.

Ekubeni idatha isetyenziswe kwiphepha eliqhelekileyo kwiphepha lokusebenzela , sinokukukopisha ifom ye-lookup kwiseli E2 kwiiseli F2 kunye ne-G2.

Njengoko ifom ekhutshiwe, i-Excel iya kuhlaziya isalathisi eseluncedo kwi-COLUMN umsebenzi (B1) ukubonisa indawo entsha yefomu.

Kwakhona, i-Excel ayitshintshi ireferensi yeseli ye $ D $ 2 kunye neTable Table ebizwa ngokuba ifom ifayiliwe.

Kukho iindlela ezingaphezulu kweyodwa zokukopa idatha kwi-Excel, kodwa mhlawumbi indlela elula kakhulu ngokusebenzisa iSondlo sokuPhatha .

Iimfundiso zeTutorial

1. Cofa kwifowuni E2 - apho ifom ye-lookup ifumaneka khona - ukwenza iseli esebenzayo
2. Beka isikhombisi semouse phezu kwebala elimnyama kwicala eliphantsi lasekunene. I-pointer iya kutshintshwa kwisibonakaliso esongezelelweyo " + " - oku kukuphatha
3. Cofa iqhosha lesinxele le mouse uze udonse isibambo sokuzalisa kwi-cell G2
4. Ukukhulula inkinobho yemouse kunye nefowuni F3 kufuneka iqulethe ifom yefowuni yokujonga
5. Ukuba kwenziwe ngokuchanekileyo, iifelesi F2 kunye ne-G2 kufuneka ngoku ngoku ziqulethe i-# N / A iphutha elikhoyo kwiseli E2

10 kwi-10

Ukungena kwiiNkqubo zoLookup

Emva kokuba ifom ye-lookup ikopishwe kwiiseli ezifunekayo ingasetyenziselwa ukubuyisa ulwazi kwitafile yedatha.

Ukwenza njalo, thayipha igama lento ofuna ukuyifumana kwiseli yeLookup_value (D2) kwaye cinezela ENTER kwikhibhodi.

Xa sele senziwe, ifayile nganye equkethe ifom ye-lookup ifanele iqulathe idilesi eyahlukileyo malunga nento ye-hardware oyifunayo.

Iimfundiso zeTutorial

1. Cofa kwiseli D2 kwiphepha lokusebenzela
2. Thayipha Umtya kwi-cell D2 uze ucinezela ENTER kwikhibhodi
3. Ulwazi olulandelayo lufanele luboniswe kwiiseli E2 ukuya kwi-G2:
   • E2 - $ 14.76 - intengo yewijethi
   • F2 - PN-98769 - inamba yecala widget
   • G2 - Widget Inc. - igama lomthengisi weejethi
4. Hlola i-formula yeVLOOKUP elandelelweyo ngokufakela igama lezinye iindawo kwi-D2 yeseli kwaye ugcine iziphumo kwiiseli E2 ukuya kwi-G2

Ukuba umyalezo wephutha ufana ne #REF! ivela kwiiseli E2, F2, okanye i-G2, olu luhlu lwemiyalezo yesiphoso seVLOOKUP lunokukunceda ukuba ufumane apho ingxaki ikhona khona.