I-Excel Two Way Lookup Usebenzisa i-VLOOKUP Ingxenye 1

Ngokuhlanganisa umsebenzi we-VLOOKUP we-Excel nomsebenzi we- MATCH , singadala ukuthi yini eyaziwa ngokuthi ifomu le - two-dimensional lookup ifomula elikuvumela ukuba uhlole kalula amasimu amabili wolwazi ku- database noma ithebula lemininingwane.

Ifomula yokubheka indlela emibili iyasiza uma ufuna ukuthola noma uqhathanisa imiphumela yezimo ezihlukahlukene.

Esibonisweni esiboniswe esithombeni esingenhla, ifomula ye-lookup yenza kube lula ukuthola izibalo zokuthengisa zamakhukhi ahlukene ezinyangeni ezahlukahlukene ngokumane uguqule igama lekhukhi nenyanga kumaseli afanele.

01 ka-06

Thola idatha ku-Intersection Point yeRow and Column

Le tutorial ihlehliwe yaba izingxenye ezimbili. Ukulandela izinyathelo ezibalulwe esigabeni ngasinye kudala ifomula yokubheka indlela emibili ebonwe esithombeni ngenhla.

Ukufundisa kuhilela ukwakha umsebenzi we-MATCH ngaphakathi kwe-VLOOKUP.

Ukwenza umsebenzi kuhilela ukufaka umsebenzi wesibili njengenye yezingxabano zomsebenzi wokuqala.

Kulesi sifundo, umsebenzi we-MATCH uzofakwa njengengxabano yenombolo yekholomu ye-VLOOKUP.

Okuqukethwe kokufundisa

• Ukufaka idatha yokufundisa
• Ukwakha Uhlobo Olubizwa NgeThebula Ledatha
• Ukuqala umsebenzi we-VLOOKUP
• Ukufaka ukuxabana kwe-Lookup
• Ukufaka i-Table Array Argument
• Ukuqala ukusebenza kwe-MATCH Function
• Ukungeza ama-Data Ranges we-MATCH Function
• Ukwengeza uhlobo lomdlalo nokuqedela ukusebenza kwe-MATCH
• Ukuqedela umsebenzi we-VLOOKUP
• Ukungeza I-Criteria Yokusesha Ukuhlola Ifomula Eqediwe
• Ukukopisha i-Formula Lookup Formula Yesibili nge-Handle Fill

02 ka 06

Ukufaka idatha yokufundisa

Isinyathelo sokuqala esifundweni ukufaka idatha kwi-Excel sheet sheet .

Ukuze ulandele izinyathelo ekufundiseni faka idatha eboniswe esithombeni ngenhla kula maseli alandelayo.

• Faka uhla oluphezulu lwedatha kuma-cell D1 kuya ku-F1
• Faka ibanga lesibili kumaseli D4 kuya ku-G8

Imigqa engu- 2 no-3 ishiywe ingenalutho ukuze ihambisane nemigomo yokusesha kanye nefomula ye-lookup eyadalwa phakathi nalesi sifundo.

Ukufundisa akubandakanyi ukufometha okubonwe esithombeni, kepha lokhu ngeke kuthinte indlela ifomula yokufundwa isebenza ngayo.

Ulwazi olukhethwa kukho lokufometha olufana nalabo abonwe ngenhla luyatholakala kule Tifundvo Eyisisekelo Yokufometha I-Excel .

Izinyathelo Zokufundisa

1. Faka idatha njengoba iboniswe esithombeni ngenhla kumaseli D1 kuya ku-G8

03 ka 06

Ukwakha Uhlobo Olubizwa NgeThebula Ledatha

Ububanzi obuqanjwe yiyona ndlela elula yokubhekisela kuluhlu lwemininingwane kwifomula. Esikhundleni sokuthayipha ezinkomba zesitokisi zedatha, ungakwazi nje ukuthayipha igama lobubanzi.

Inzuzo yesibili yokusebenzisa ububanzi obuqanjwe yilokho okukhulunywa ngakho kwamangqamuzana kulolu hlu akuguquki ngisho nalapho ifomula likopishwa kwamanye amaseli kuphepha lokusebenzela.

Izinyathelo Zokufundisa

1. Gqamisa amaseli D5 kuya ku-G8 ekhasini lokusebenzela ukuze ukhethe
2. Chofoza ebhokisini legama elisenhla kwekholomu A
3. Thayipha "ithebula" (ayikho izingcaphuno) ebhokisini legama
4. Cindezela inkinobho ENTER ekhibhodi
5. Amaseli D5 kuya ku-G8 manje analo igama lokubala "kwetafula". Sizosebenzisa igama lempikiswano ye-VLOOKUP etafuleni kamuva esikhathini sokufundisa

04 ka 06

Vula i-VLOOKUP Dialog Box

Yize kungenzeka ukuthi uthayiphe ifomu lethu lookuphenya ngqo esitokisini ekhasini lokusebenzela, abantu abaningi bakuthola kunzima ukugcina i- syntax iqonde - ikakhulukazi ifomula eyinkimbinkimbi njengaleyo esiyisebenzisayo kule tutorial.

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 ku-cell F2 ephepheni lokusebenzela - indawo lapho imiphumela yefomula yokubuka kokubili ebonakalayo izoboniswa
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

05 ka 06

Ukufaka ukuxabana kwe-Lookup

Ngokuvamile, inani lokubheka lifanelana nensimu yedatha kukholomu yokuqala yedatha yedatha.

Esikhathini sethu, inani lokubheka libhekisela kohlobo lwekhukhi esifuna ukuthola ulwazi mayelana nalo.

Izinhlobo ezivunyelwe zedatha ye- value lookup yilezi:

• idatha yombhalo
• inani elinengqondo (TRUE noma FALSE kuphela)
• inombolo
• ukubhekisela kweseli kunani elisekhasini lokusebenzela

Kulesi sibonelo sizofaka ireferensi yeseli lapho igama lamakhukhi lizotholakala khona - iseli D2.

Izinyathelo Zokufundisa

1. Chofoza kulayini lookup_value ebhokisini lengxoxo
2. Chofoza kuseli D2 ukuze ungeze le nkomba yeselula kumugqa we- lookup_value . Lona iseli lapho sizoyibhala khona igama lekhukhi mayelana nokuthi sifuna ulwazi

06 ka-06

Ukufaka i-Table Array Argument

Ithebula letafula yithebula yedatha ukuthi ifomula ye-lookup isesha ukuthola ulwazi esikufunayo.

Ithebula letafula kufanele libe nezinhlamvu ezimbili zedatha okungenani.

• ikholomu yokuqala iqukethe ukuphikisana kwenani lokubheka (isinyathelo sangaphambilini esifundweni)
• okwesibini, nanoma yimaphi amakholomu angeziwe, azoseshwa ngefomula yokubheka ukuze athole ulwazi esiluchazayo.

I-array array argument kufanele ifakwe njengobe ibanga eliqukethe izingqikithi zeseli yedatha yedatha noma njengegama lebanga .

Kulesi sibonelo, sizosebenzisa igama lebanga elidalwe kwisinyathelo sesi-3 salesi sifundo.

Izinyathelo Zokufundisa

1. Chofoza kutafula_layini lomugqa ebhokisini lengxoxo
2. Thayipha "ithebula" (ayikho izingcaphuno) ukungena igama lebanga lale mpikiswano
3. Shiya ibhokisi lengxoxo yebhizinisi le-VLOOKUP evulekile ingxenye elandelayo yokufundisa

Qhubekela kuNgxenye 2 >>