I-Excel I-Forward Lookup Formula Usebenzisa i-VLOOKUP

01 ka-03

Thola Idatha ngakwesobunxele

I-Excel Left Forward Formula Overview

Umsebenzi we-VLOOKUP we-Excel usetshenziselwa ukuthola nokubuyisela ulwazi kusukela etafuleni lwedatha ngokususelwa kunani lokubheka olikhethayo.

Ngokuvamile, i-VLOOKUP idinga ukubaluleka kokubheka ukuthi kube ngakwesokunxele kakhulu kwekholomu yedatha, futhi umsebenzi ubuyisela enye insimu yedatha esesigabeni esifanayo ngakwesokudla sale nani.

Ngokuhlanganisa i-VLOOKUP nomsebenzi we- CHOOSE ; Nokho, ifomula yokubheka kwesokunxele ingadalwa ukuthi:

• ivumela inani lokubheka ukuthi likhethwe kunoma iyiphi ikholomu kudatha yedatha
• ulwazi lokubuyela olutholakala kunoma iyiphi ikholomu ngakwesokunxele kwenani lokubheka

Isibonelo: Ukusebenzisa i-VLOOKUP bese ukhethe Imisebenzi ku-Formula Lookup Formula

Izinyathelo eziningiliziwe ngezansi zakha ifomula yokubheka kwesokunxele ibonwe esithombeni ngenhla.

Ifomula

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

yenza kube lula ukuthola ingxenye ehlinzekwe yizinkampani ezahlukene ezibalwe kukholomu 3 yedatha yedatha.

Umsebenzi we-CHOOSE umsebenzi kulefomula ukukhohlisa i-VLOOKUP ekukholweni ukuthi ikholomu 3 empeleni ikholomu 1. Ngenxa yalokho, igama leNkampani lingasetshenziswa njengenani le-lookup ukuthola igama lesigaba esinikezwa yinkampani ngayinye.

Izinyathelo Zokufundisa - Ukufaka Idatha Yokufundisa

1. Faka izihloko ezilandelayo kumaseli abonisiwe: D1 - Umphakeli E1 - Ingxenye
2. Faka itafula lemininingwane elibonwe esithombeni ngenhla kumaseli D4 kuya ku-F9
3. Imigqa engu- 2 no-3 ishiywe ingenalutho ukuze ihambisane nemigomo yokusesha kanye nefomula yokufundwa kwesokunxele idalwe phakathi nalesi sifundo

Ukuqala ifomu lokuLowulwa Kwesobunxele - Ukuvula i-VLOOKUP Dialog Box

Yize kungenzeka ukuthi uthayiphe ifomula ngenhla ngqo ku-cell F1 ekhasini lokusebenzela, abantu abaningi banenkinga nge- syntax yefomula.

Okunye, kulokhu, ukusebenzisa i-VLOOKUP ibhokisi lengxoxo . Cishe yonke imisebenzi ye-Excel inebhokisi lengxoxo evumela ukuthi ufake izingxabano zomsebenzi ngamunye kumugqa ohlukile.

Izinyathelo Zokufundisa

1. Chofoza kumakhalekhukhwini E2 wephepha lokusebenzela - indawo lapho imiphumela yefomula yokungena kwesokunxele izoboniswa khona
2. Chofoza kuthebhu yefomathi yebhande
3. Chofoza ku-Option Lookup & Reference kwi-ribbon ukuvula uhlu lwehla lokusebenza
4. Chofoza ku- VLOOKUP ohlwini ukuze ulethe ibhokisi lengxoxo lomsebenzi

02 ka-03

Ukufaka izimpikiswano ebhokisini le-VLOOKUP le-Dialog - Chofoza ukuze Ubuke Isithombe Esikhulu

Amaphuzu kaVLOOKUP

Iziphakamiso zomsebenzi yizilinganiso ezisetshenziswe umsebenzi ukubala umphumela.

Ebhokisini lengxoxo yomsebenzi, igama lempikiswano ngayinye likhona kumzila ohlukile olandelwa yinkambu lapho ungangena khona inani.

Faka amanani alandelayo kuzo zonke iziphakamiso ze-VLOOKUP emgqeni ofanele webhokisi lebhokisi njengoba kuboniswe esithombeni ngenhla.

Inani lokuLookup

Inani lookuphambili yinkambu yolwazi elisetshenziselwa ukucinga uhlu lwetafula. I-VLOOKUP ibuyisela enye insimu yedatha kusuka kumugqa owodwa njengenani lokubheka.

Lesi sibonelo sisebenzisa ireferensi yeseli endaweni lapho igama lenkampani lizofakwa khona ekhasini lokusebenzela. Inzuzo yalokhu ukuthi yenza kube lula ukushintsha igama lenkampani ngaphandle kokuhlela ifomula.

Izinyathelo Zokufundisa

1. Chofoza kulayini lookup_value ebhokisini lengxoxo
2. Chofoza kuseli D2 ukuze ungeze le nkomba yeselula kumugqa we- lookup_value
3. Cindezela ukhiye we- F4 ekhibhodi ukuze wenze ireferensi yeseli ngokuphelele - $ D $ 2

Qaphela: Izingqikithi zeseli ezingapheli zisetshenziselwa ukubaluleka kokubaluleka kanye neziphakamiso zethebula zokuvimbela amaphutha uma ifomula ye-lookup ikopishwa kwamanye amaseli kushidi lokusebenzela.

I-Table Array: Ukufaka umsebenzi we-CHOOSE

I-array argument array iyinhloko yedatha eqondile lapho ulwazi oluthile lubuyiswa khona.

Ngokuvamile, i-VLOOKUP ibheka kuphela ngakwesokudla ukuphikisana kwenani lokubheka ukuze uthole idatha ku-table table. Ukuze uthole ukuthi ubukeke ngakwesobunxele, i-VLOOKUP kufanele ikhohliswe ngokuhlelwa kabusha kwamakholomu ku-table table usebenzisa umsebenzi we-CHOOSE.

Kule ndlela, umsebenzi we-CHOOSE ufeza imisebenzi emibili:

1. idala uhlu lwetafula oluphakathi kwamakholomu amabili kuphela - amakholomu D no-F
2. lishintsha ilungelo lokuya kwesokunxele kwekholomu ku-table table ukuze ikholomu F ifike kuqala nekholomu D eyesibili

Imininingwane yokuthi umsebenzi we-CHOOSE owenza kanjani le misebenzi ungatholakala ekhasini 3 lokufundisa .

Izinyathelo Zokufundisa

Qaphela: Uma ufaka imisebenzi ngesandla, izingxabano zomsebenzi ngamunye kufanele zihlukaniswe ngomahlaya "," .

1. Kulobhokisi lebhizinisi le-VLOOKUP, chofoza kulayini lwe- Table_array
2. Faka umsebenzi olandelayo we-CHOOSE
3. Khetha ({1,2}, $ F: $ F, $ D: $ D)

Inombolo Yenkomba Yekholomu

Ngokuvamile, inombolo yenkomba yekholomu ibonisa ukuthi iyiphi ikholomu yohlu lwethebula equkethe idatha oyilandelayo. Kule fomula; Nokho, libhekisela ekuhleleni kwamakholomu asethwe umsebenzi wokukhetha.

Umsebenzi we-CHOOSE udala uhlu lwetafula olulodwa lamakholomu amabili ububanzi nekholomu F yokuqala elandelwa ikholomu D. Njengoba ulwazi olufunwa - igama lesigcawu - likukholomu D, ukubaluleka kwempikiswano yenkomba yekholomu kufanele kubekwe ku-2.

Izinyathelo Zokufundisa

1. Chofoza ku- Col_index_num line ebhokisini lengxoxo
2. Thayipha u- 2 kulolu gqa

I-Range Lookup

I-Range_lookup ingxabano kaVLOOKUP yinani elilinganiselwe (TRUE noma FALSE kuphela) elikhombisa ukuthi ufuna i-VLOOKUP ukuthola umlinganiso oqondile noma olingana nenani lookuphenya.

• Uma i-TRUE noma uma le mpikiswano ingavaliwe, i-VLOOKUP ibuyisela ngokufana ngqo ne-Lookup_value, noma, uma umdlalo oqondile ungatholakali, i-VLOOKUP ibuyisela inani elilandelayo elikhulu. Ukuze ifomula ukwenza lokhu, idatha ekholomu yokuqala yeThebula_array kufanele ihlungwe ngokunyuka kwe-oda .
• Uma i-FALSE, i-VLOOKUP izosebenzisa kuphela umdlalo oqondile ku-Lookup_value. Uma kunamagugu amabili noma ngaphezulu kukholomu yokuqala yeThebula_array efanisa inani lookup, inani lokuqala elifunyenwe lisetshenzisiwe. Uma umdlalo oqondile ungatholakali, iphutha le- # N / A libuyiselwe.

Kulesi sifundo, njengoba sibheke igama elithile legama, i-Range_lookup izobekwa ku- Amanga ukuze kuphela ukufanelana okuqondile kubuyiselwa ifomula.

Izinyathelo Zokufundisa

1. Chofoza kumugqa we- Range_lookup ebhokisini lengxoxo
2. Thayipha igama Amanga kulo mkhakha ukuze ukhombise ukuthi sifuna i-VLOOKUP ukubuyisa umdlalo oqondile we-data esiyifunayo
3. Chofoza u-Kulungile ukuqedela ibhokisi lokungena lefomula lokuvala kwesokunxele nokuvala
4. Njengoba sesingenanga igama lenkampani ibe yiseli D2, iphutha le- # N / A kufanele libe khona esitokisini se-E2

03 ka 03

Ukuvivinya iFomula YokuLondoloza Kwesobunxele

Idatha yokubuyisa ngefomula yokuLondoloza kwesokunxele

Ukuze uthole ukuthi yiziphi izinkampani ezinikezela yiziphi izingxenye, thayipha igama lenkampani ibe yiseli D2 bese ucindezela inkinobho ENTER ekhibhodi.

Igama legama lizoboniswa kuseli le-E2.

Izinyathelo Zokufundisa

1. Chofoza ku-cell D2 kushidi lakho lokusebenzela
2. Thayipha Gadget Plus esitokisini D2 bese ucindezela ENTER ikhibhodi
3. Umbhalo Igajethi - inxenye ehlinzekwa yinkampani Gadgets Plus - kufanele iboniswe kuseli E2
4. Hlola ifomula yokubheka ngokuqhubekayo ngokuthayipha amanye amagama enkampani ibe yiseli D2 futhi igama eliyingxenye ehambelanayo kufanele livele kuseli E2

Imilayezo Yephutha le-VLOOKUP

Uma umlayezo wephutha onjengo- # N / A uvela esitokisini se-E2, hlola kuqala amaphutha okupela isipelingi kuseli D2.

Uma isipelingi akuyona inkinga, lolu hlu lwemiyalezo yephutha le-VLOOKUP lingakusiza ukuthi ubone ukuthi inkinga ikhona kuphi.

Ukuqeda I-CHOOSE Umsebenzi Wezisebenzi

Njengoba kushiwo, kule fomu, umsebenzi we-CHOOSE unemisebenzi emibili:

• idala uhlu lwetafula oluphakathi kwamakholomu amabili kuphela - amakholomu D no-F
• lishintsha ilungelo lokuya kwesokunxele kwekholomu ku-table table ukuze ikholomu F ifike kuqala nekholomu D eyesibili

Ukwakha Ithebula Lethebula Elibili

I- syntax yomsebenzi we-CHOOSE yilezi:

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

Umsebenzi we-CHOOSE uvame ukubuyisa inani elilodwa ohlwini lwamanani (Value1 kuya Value254) ngokususelwa kwinombolo yenkomba efakiwe.

Uma inombolo yenombolo ingu-1, umsebenzi ubuyisela i-Value1 ohlwini; uma inombolo yenombolo ingu-2, umsebenzi ubuyisela i-Value2 ohlwini futhi njalo.

Ngokufaka izinombolo zocingo eziningi; Kodwa-ke, umsebenzi uzobuyisela amanani amaningi kunoma yikuphi okufunayo. Ukukhetha ukubuyisela amanani amaningi kwenziwa ngokudala uhlu .

Ukufaka uhlu lufezwa ngokuzungezile izinombolo ezifakwe ngamabhande noma amabakaki. Izinombolo ezimbili zifakwe kwinombolo yenkomba: {1,2} .

Kumele kuqashelwe ukuthi ukukhethwa akukhawulelwe ekudaleni ithebula lekholomu emibili. Ngokufaka inombolo eyengeziwe ohlwini - njenge- {1,2,3} - nolunye olwengeziwe ku-argument value, itafula lekholomu ezintathu lingadalwa.

Amakholomu angeziwe angakuvumela ukuba ubuyele ulwazi oluhlukile ngefomula yokufundwa kwesobunxele ngokumane uguqule ingxabano yenombolo yenkomba ye-VLOOKUP yocingo enombhalo oqukethe ulwazi olufunayo.

Ukushintsha i-oda yama Columns nge-CHOOSE Umsebenzi

Kumsebenzi we-CHOOSE osetshenziswe kule fomula: CHOOSE ({1,2}, $ F: $ F, $ D: $ D) , ububanzi bekholomu F bubhalwe ngaphambi kwekholomu D.

Njengoba umsebenzi we-CHOOSE ubeka uhlu lwetafula le-VLOOKUP - umthombo wedatha yalowo msebenzi - ukushintsha umyalo wekholomu ku-CHOOSE umsebenzi udluliselwa ku-VLOOKUP.

Manje, ngokuqondene ne-VLOOKUP ethintekayo, uhlu lwetafula luphela ngamakholomu amabili ububanzi nekholomu F ngakwesobunxele nekholomu D ngakwesokudla. Njengoba ikholomu F liqukethe igama lenkampani esifuna ukuyifuna, futhi kusukela ikholomu D iqukethe amagama okuyingxenye, i-VLOOKUP izokwazi ukwenza imisebenzi yayo yokujwayelekile yokuthola idatha ekhona ngakwesobunxele bexabiso lokubheka.

Ngenxa yalokho, i-VLOOKUP ikwazi ukusebenzisa igama lenkampani ukuthola ingxenye abayinike yona.