I-Excel Left Lookup Formula usebenzisa i-VLOOKUP

01 ka 03

Fumana iDatha kwiNxele

I-Excel Left Lookup Uqwalaselo Lwasezantsi

Umsebenzi we-VLOOKUP we-Excel isetyenziselwa ukufumana nokubuyisela ulwazi kwitafile yedata ngokusekelwe kwinqanaba lokukhetha elikhethiweyo.

Ngokuqhelekileyo, iVLOOKUP idinga ukuba ixabiso lookuphambili libe kwicala lekhohlo le-data yedatha, kwaye umsebenzi ubuyisela enye insimu yedatha elinelayini elifanayo ukuya ngasekunene kweli xabiso.

Ngokudibanisa iVLOOKUP kunye nomsebenzi we-CHOOSE; nangona kunjalo, ifomula yokuloba ekhohlo ingadalwa eya kuthi:

• ivumela ixabiso lookuphambili elikhethiweyo ukuba likhethwe kuyo nayiphi na ikholomu kwitheyibhile yedatha
• ukubuyisela ulwazi olukhoyo kunoma iyiphi ikholomu ngakwesobunxele bexabiso lokungena

Umzekelo: Ukusebenzisa iVLOOKUP kwaye CHOOSE Imisebenzi kwi-Formula Lookup Formula

Amanyathelo anezantsi apha adala ifom ye-lookup ifom ebonwe kumfanekiso ongentla.

Ifom

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

yenza kube lula ukufumana inxalenye enikwe iinkampani ezahlukeneyo ezidweliswe kwikholam yesi-3 yedatha yedatha.

Umsebenzi we-CHOOSE umsebenzi kule fom ukukhohlisa i-VLOOKUP ukuba ikholelwe ukuba ikholam yesi-3 iyinxalenye yekholamu 1. Ngenxa yoko, igama leNkampani linokusetyenziswa njengelona xabiso lookupasa ukufumana igama lenxalenye enikezelwa yinkampani nganye.

Iimfundiso zeTutorial - Ukufaka i-Data Tutorial Data

1. Faka izihloko ezilandelayo kwiiseli ezibonisiweyo: D1 - Umthengisi E1-Icandelo
2. Faka itafile yedata ebonwa kumfanekiso ongentla kwiiseli D4 ukuya kwi-F9
3. Imigangatho yesi-2 neye-3 ishiywe ingenanto ukuze kulungelelaniswe imigaqo yokukhangela kunye nefom ye-lookup ifomda eyenziwe ngexesha loqeqesho

Ukuqala iFomula yokuLohlo lokuBhohlo - Ukuvula i-VLOOKUP yoLwazi lwebhokisi

Nangona kunokwenzeka ukuba uthayiphe ifomula apha ngentla ngqo kwiseli F1 kwiphepha lokusebenzela, abantu abaninzi banenkinga nge syntax yale fomyula.

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 yokuloba ekhohlo ziza kuboniswa
2. Cofa kwiThebhu yefomula yerbhoni
3. Cofa kwikhetho yeLowokup & Reference kwi-ribbon ukuvula uluhlu lwehla phantsi
4. Cofa kwiVLOOKUP kuloluhlu ukukhupha ibhokisi yencoko yencoko yomsebenzi

02 ka 03

Ukufaka iiArguments kwi-VLOOKUP yebhokisi yemixholo - Cofa ukuze Ubonise umfanekiso omkhulu

IiArguments zeVLOOKUP

Iingxoxo zomsebenzi zizithethe ezisetyenziswe ngumsebenzi ukubala iziphumo.

Kwibhokisi yencoko yomsebenzi, igama lempikiswano nganye lifumaneka kumgca ohlukileyo olandelwa yintsimi ekungeniseni ixabiso.

Faka ezi zilandelayo zithethe kwiingxoxo zeVLOOKUP kumgca ochanekileyo kwibhokisi yencoko njengoko kuboniswe kumfanekiso ongentla.

Ixabiso loLookup

Ixabiso lokulunga lithenda yolwazi esetyenziselwa ukukhangela uluhlu lwetafile. I-VLOOKUP ibuyisela enye insimu yedata ukusuka kumqolo ofanayo kunye nexabiso lokulunga.

Lo mzekelo usebenzisa iselferensi yeseli kwindawo apho igama lenkampani liza kufakwa kwiphepha lokusebenzela. Inzuzo yale nto kukuba yenza kube lula ukutshintsha igama lenkampani ngaphandle kokuhlela ifom.

Iimfundiso zeTutorial

1. Cofa kumgca we- lookup_value kwibhokisi yencoko
2. Cofa kwiseli D2 ukuze ungeze le referensi yeseli kumgca we- lookup_value
3. Cinezela iqhosha le- F4 kwibhodibhodi ukwenzela ukuba isalathisi yesalathisi ibe ngu- $ D $ 2

Qaphela: Iingqamaniso zeseli ezingapheliyo zisetyenziselwa ixabiso lookuphambili kunye neetafile eziphambili zeetable zokukhusela iimpazamo ukuba ifom ye-lookup ikopiwe kwezinye iiseli kwiphepha lokusebenzela.

Uluhlu lwetafile: Ukufaka i-CHOOSE Function

I-array argument argument ibhanki yedatha edibeneyo apho ulwazi oluthe ngqo lufunyenwe khona.

Ngokuqhelekileyo, i-VLOOKUP ibheka kuphela ekunene kwi-argument yexabiso lookup ukufumana idatha kwi-table table. Ukuze ufumane ukujonga ngakwesobunxele, i-VLOOKUP imele ikhohliswe ngokuhlaziywa kwamakholomu kwitheyibhile ngokusetyenziswa kwe-CHOOSE.

Kule ndlela, umsebenzi we-CHOOSE ufezekisa imisebenzi emibini:

1. idala uluhlu lwetafile elineembalo zombini kuphela - iikholomu D no-F
2. utshintshe ilungelo ukuya kwinqanaba lekhohlo kwimiqolo kwi-table array ukuze ikholomu F ize kuqala kwaye ikholomu D yesibini

Iinkcukacha zendlela umsebenzi we-CHOOSE owenza ngayo le misebenzi inokufumaneka kwiphepha 3 le-tutorial .

Iimfundiso zeTutorial

Qaphela: Xa ufaka imisebenzi ngokuzenzekelayo, imivumelwano nganye yomsebenzi kufuneka ihlukaniswe ngumbumba "," .

1. Kwibhokisi yencoko yebhokisi yeVLOOKUP, nqakraza kwi- Table_array line
2. Faka lo msebenzi olandelayo
3. KHETHA ({1,2}, $ F: $ F, i $ D: $ D)

Inombolo ye Index Index

Ngokuqhelekileyo, inamba yenkcazo yekholam ibonisa ukuba yeyiphi ikholomu yoluhlu lwetafile equle idatha olandelayo. Kule ndlela; nangona kunjalo, ibhekisela kwimiyalelo yamakholomu atyunjwe ngumsebenzi we-CHOOSE.

UMSEBENZI WOKUSETSHENZA udala uluhlu lwetafile ezinamacala amakhulu amabini kunye nekholam F yokuqala elandelwa yikholomu D. Njengoko ulwazi olufunyenweyo-igama legama - likhompyutheni D, ixabiso lempikiswano yenkalo yekholomu mayibekwe kwi-2.

Iimfundiso zeTutorial

1. Cofa kwi Col_index_num umgca kwibhokisi yencoko
2. Thayipha i- 2 kulo mgca

Range Lookup

I-VLOOKP ye-Range_lookup ingxabano iyinqobo enengqiqo (INYANISO okanye I-FALSE kuphela) ebonisa ukuba ufuna iVLOOKUP ukufumana umlinganiselo ochanekileyo okanye umlinganiselo olinganayo kwixabiso lokungena.

• 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 sibheke igama legama elithile, i-Range_lookup iya kusekwa kuNkohliso ukuze kuphela imifuno efana nayo ibuyiselwa yifom.

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 u-Kulungile ukuqedela ifom ye-lookup yefowuni kunye nebhokisi yencoko yokuvala
4. Ekubeni singakangena igama lenkampani kwi-D2 yeseli, impazamo ye-# N / A ifanele ibe khona kwi-cell E2

03 ka 03

Ukuvavanya i-Formula Lookup Formula

Idata yokubuyisela kunye neFomula yokuLobo lokuBhohlo

Ukufumana ukuba yeyiphi iinkampani ezinikezelayo iindawo, zifake igama lenkampani ibe yiseli D2 kwaye cinezela ENTER kwikhibhodi.

Igama legama liya kuboniswa kwi-cell E2.

Iimfundiso zeTutorial

1. Cofa kwiseli D2 kwiphepha lakho lokusebenzela
2. Thayipha iGadi kwi-cell D2 uze ucinezela ENTER kwikhibhodi
3. Iibhaliweyo zeGadi - inxalenye enikezelwa yinkampani ye- Gadgets Plus - kufuneka iboniswe kwiseli E2
4. Ukuvavanya ifom ye-lookup ngokuqhubekayo ngokuthayipha amanye amagama eenkampani kwiseli D2 kunye negama elifanelekileyo legama kufuneka livele kwiseli E2

Imiyalezo yeZiphutha zeVLOOKUP

Ukuba umyalezo wephutha ofana no-# N / A uvela kwiseli ye-E2, kuqala uhlolisise iimpazamo zepelingi kwiseli D2.

Ukuba isipelingi ayilona ingxaki, olu luhlu lwemiyalezo yeemposiso zeVLOOKUP lunokukunceda ubone ukuba ingxaki ikhona.

Ukudiliza I-CHOOSE Umsebenzi weJob

Njengoko kubonisiwe, kule ndlela, umsebenzi we-CHOOSE unemisebenzi emibini:

• idala uluhlu lwetafile elineembalo zombini kuphela - iikholomu D no-F
• utshintshe ilungelo ukuya kwinqanaba lekhohlo kwimiqolo kwi-table array ukuze ikholomu F ize kuqala kwaye ikholomu D yesibini

Ukudala i-Array Two Table Colray

I- syntax ye-CHOOSE umsebenzi yile:

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

UMSEBENZI WOKUSEBENZA ngokuqhelekileyo ubuyisela ixabiso elilodwa ukusuka kuluhlu lwexabiso (Value1 kuya ku-Value254) ngokusekelwe kwinombolo yerefayile efakwe.

Ukuba inani lesalathisi li-1, umsebenzi ubuyisela ixabiso1 ukusuka kuhlu; ukuba inani lesalathisi li-2, umsebenzi ubuyisela i-Value2 ukusuka kuhlu kwaye njalo njalo.

Ngokufaka amanani amanqaku amaninzi; nangona kunjalo, umsebenzi uya kubuya kwiimpawu ezininzi kunoma yimuphi umyalelo oyifunayo. UKUKHETHA ukubuyisela amanani amaninzi kwenziwa ngokudala uluhlu .

Ukwenza uluhlu lufezekiswe ngokujikeleza amanani angeniswe ngamabhonki okanye iibakaki. Amanani amabini afakwe kwinombolo yerejista: {1,2} .

Kufuneka kuqatshelwe ukuba ukhetho alukhawulelwanga ekwakheni itafile ezimbini zekholomu. Ngokubandakanya nenani elongezelelweyo kwi-array - njenge- {1,2,3} - kunye nolunye olungakumbi kwixabiso lexabiso, kukho itafile emithathu yekholomu.

Iikholomu ezongezelelweyo ziza kukuvumela ukuba ubuyele ulwazi oluhlukileyo ngefomula yokulandelwa kwesobunxele nje ngokutshintsha i-VLOOKUP yekholomu yenombolo yenkcazo yenombolo kwinani lekholomu eneenkcukacha ezifunayo.

Ukutshintsha umyalelo weeKholamu nge-CHOOSE Function

KWENKQUBO YOKUSETYENZISWA kusetyenziswa kule fomyula: CHOOSE ({1,2}, $ F: $ F, $ D: $ D) , uluhlu lwekholam F ludweliswe ngaphambi kwekholomu D.

Ekubeni umsebenzi we-CHOOSE ubeka uluhlu lwetafula yeVLOOKUP - umthombo wedatha yaloo msebenzi - ukuguqula umyalelo weekholamu kwi-CHOOSE umsebenzi uhanjiswa kuVLOOKUP.

Ngoku, ngokubhekiselele kwiVLOOKUP, i-table array yimiqolo emibini kuphela ngobubanzi bekholomu F ngakwesobunxele kunye nekholomu D ngakwesokudla. Ekubeni umgca F uqulethe igama lenkampani sifuna ukuyifuna, kwaye ekubeni ikholomu D iqulethe amagama ayingxenye, i-VLOOKUP iza kukwazi ukwenza imisebenzi yayo yokujonga ngokuqhelekileyo ekufumaneni idatha ekhoyo ngakwesobunxele bexabiso lokungena.

Ngenxa yoko, i-VLOOKUP inako ukusebenzisa igama lekampani ukufumana inxalenye abayinike yona.