I-Excel ezimbini Indlela yokuSebenzisa usebenzisa iVLOOKUP Ingxenye 1

Ngokudibanisa umsebenzi we-VLOOKUP kunye ne- MATCH function , singenza into eyaziwa njengendlela elandelwayo yeendlela ezimbini okanye ububanzi obuvumela ukuba ulandelelanise kalula iinkalo ezimbini zolwazi kwiziko ledatha okanye kwitafile yedata.

Ifom ye-lookup ifowuni iyakunceda xa ufuna ukufumana okanye uqhathanisa iziphumo zeemeko ezahlukeneyo.

Kumzekelo oboniswe kumfanekiso ongentla, ifom ye-lookup yenza kube lula ukufumana amanani okuthengisa kwiikhukhi ezahlukeneyo kwiinyanga ezahlukileyo ngokuguqula igama lekhukhi kunye nenyanga kwiiseli ezichanekileyo.

01 ngo-06

Fumana iData kwiPixse Point Point yeRow kunye neKholamu

Olu qeqesho luye lwaphuka zibe zimbini. Ukulandela amanyathelo adweliswe kwicandelo ngalinye kudala ifom ye-lookup ifom ebonakalayo kumfanekiso ongentla.

I-tutorial ibandakanya ukukhupha umsebenzi we-MATCH ngaphakathi kwe-VLOOKUP.

Ukuhlawula umsebenzi kubandakanya ukufaka umsebenzi wesibini njengenye yeengxoxo zokuqala umsebenzi.

Kule ngqeqesho, umsebenzi we-MATCH uza kufakwa njengengxabano yenombolo yenkalo ye-VLOOKUP.

Iziqulatho zeTutorial

• Ukufaka i-Tutorial Data
• Ukudala umgama obizwa ngokuba yiTable Table
• Ukuqala umsebenzi weVLOOKUP
• Ukungena kwexabiso lokuLungisa
• Ukungena kwiTable Array Argument
• Ukuqala ukusebenza kweMATCH Function
• Ukongeza iiRanges zeDatha zeMATCH Function
• Ukongeza uhlobo lomdlalo kunye nokugqiba umsebenzi we-MATCH
• Ukugqiba umsebenzi weVLOOKUP
• Ukongeza iCriter Criteria ukuVavanya iFomula epheleleyo
• Ukukopisha iFomula yokuLungiswa kweMibini yokuPhakamisa kunye neSigxina sokuzalisa

02 we-06

Ukufaka i-Tutorial Data

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-F1
• Faka udidi lwesibini kwiiseli zeD4 ukuya kwi-G8

Imigangatho yesi-2 ne-3 ishiywe ingenanto ukuze kulungelelaniswe imigaqo yokukhangela kunye nefom ye-lookup eyenziwe ngeli thuba.

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-G8

03 we-06

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.

Iimfundiso zeTutorial

1. Khanyisa iiseli D5 ukuya kwi-G8 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-G8 ngoku zinegama loluhlu "lwetafile". Siza kusebenzisa igama kwi-VLOOKUP itheyibhile yenkcazo elandelayo emva kwesikhokelo

04 we-06

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 kwifayile F2 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 ukukhupha ibhokisi yencoko yencoko yomsebenzi

05 ka 06

Ukungena kwexabiso lokuLungisa

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

Ngokomzekelo wethu, ixabiso lokungena libhekisela kuhlobo lwecookie sifuna ukufumana ulwazi malunga.

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 le-cookie liza kubakho khona - iseli D2.

Iimfundiso zeTutorial

1. Cofa kumgca we- lookup_value kwibhokisi yencoko
2. Cofa kwiseli D2 ukuze ungeze le referensi yeseli kumgca we- lookup_value . Le neseli apho siza kufaka igama lekhukhi malunga nathi sifuna ulwazi

06 we-06

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, siya kusebenzisa igama legama elidalwe kwisinyathelo 3 sale khokelo.

Iimfundiso zeTutorial

1. Cofa kwitafile_layini lomgca kwibhokisi yencoko
2. Thayipha "itafile" (akukho zicatshulwa) ukungena kwigama legama kule ngxabano
3. Shiya ibhokisi yencoko yebhodi yencoko yeVLOOKUP evuliwe kwinqanaba elilandelayo le-tutorial

Qhubela kwiNgxenye 2 >>