I-Excel ezimbini Indlela yokuSebenzisa usebenzisa iVLOOKUP Ingxenye 2

01 ngo-06

Ukuqala ukusebenza kweMATCH Function

Buyela kwiNgxenye 1

Ukufaka i-MATCH Function njengeNombolo ye-Index Index

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

Nangona kunjalo, kulo mzekelo sinamakholomu amathathu enqwenela ukufumana idatha ngokunjalo sifuna indlela yokutshintsha idijithali yenombolo yekholomu ngaphandle kokuhlela ifom ye-lookup yethu.

Le yilapho umsebenzi WOKUHLOLA usebenza. Kuya kusivumela ukuba sifanise nombolo yekholomu egameni lendawo- mhlawumbi ngoJanuwari, ngoFebruwari, okanye ngo-Matshi-ukuba siyifake kwi-cell E2 kwiphepha lokusebenzela.

ImiSebenzi yokuHlola

Umsebenzi weMATCH, ngoko, usebenza njenge-VLOOKUP yekholomu yenombolo yenkcazo yenombolo .

Oku kufezekiswa ngumbono weMATCH ngaphakathi ngaphakathi kweVLOOKUP kwi Col_index_num umgca webhokisi yencoko.

Ukufaka UMSEBENZI WOMSEBENZI Ngokuzenzekelayo

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

Umsebenzi weMATCH, ngoko, kufuneka ungene ngesandla kwiCol_index_num line.

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

Iimfundiso zeTutorial

Ukungena kwi-MATCH Function Lookup_value Argument

Isinyathelo sokuqala ekungeneni umsebenzi we-MATCH odibeneyo kukufaka ingxabano yeLookup_value .

I- Lookup_value iya kuba yindawo okanye iselfowuni yegama leseshishini lokukhangela esifuna ukulifanisa kwisiseko sedatha.

1. Kwibhokisi yencoko yebhokisi yeVLOOKUP, nqakraza kwiCol_index_num line.
2. Thayipha umdlalo wegama lomsebenzi olandelwe ibakaki evulekile " ( "
3. Cofa kwiseli E2 ukufaka loo ntetho yeselenki kwibhokisi yencoko.
4. Thayipha i-comma "," emva kokubhekisele kweseli E3 ukugqiba ukungena kwempikiswano yomsebenzi we- MATCH Lookup_value .
5. Shiya ibhokisi yencoko yebhokisi yeVLOOKUP evuliwe kwisinyathelo esilandelayo kwisifundo.

Kwinqanaba lokugqibela le-tutorial i-Lookup_values ​​iya kufakwa kwiiseli D2 no-E2 kwiphepha lokusebenzela .

02 we-06

Ukongeza i-Lookup_array ye-Function Function

Ukongeza i-Lookup_array ye-Function Function

Eli nyathelo liquka ukufaka ingxabano yeLookup_array yomsebenzi we-MATCH odibeneyo.

I- Lookup_array uluhlu lwamaseli apho umsebenzi weMATCH uzakufuna ukufumana ingxabano yeLookup_value yongezwe kwisinyathelo esedlule sesifundo .

Kulo mzekelo, sifuna umsebenzi weMATCH ukukhangela amaseli D5 ukuya kwi-G5 kumdlalo kumgama oza kungena kwi-cell E2.

Iimfundiso zeTutorial

Ezi nyathelo kufuneka zingeniswe emva kokuba i-comma ingene kwisinyathelo esedlule kwi- Col_index_num line kwibhokisi yencoko yebhokisi ye-VLOOKUP.

1. Ukuba kuyimfuneko, nqakraza kwiCol_index_num line emva komxube ukuba ufake indawo yokufaka ekupheleni kokungena kwangoku.
2. Phakamisa iiseli ze-D5 ukuya kwi-G5 kwiphepha lokusebenzela ukuze ufake ezi ngqamaniso zeseli njengoko uluhlu lomsebenzi ukukhangela.
3. Cinezela iqhosha le- F4 kwikhibhodi ukuze utshintshe eli lihla zibe ziingqamaniso zeeseli ezipheleleyo . Ukwenza njalo kuya kwenza ukuba ukwazi ukukopisha ifom ye-lookup egcwalisiweyo kwenye indawo kwiphepha lokusebenzela kwisinyathelo sokugqibela soqeqesho
4. Thayipha i-comma "," emva kokubhekisele kweseli ye- E3 ukugqiba ukungena kwempikiswano yomsebenzi we- MATCH Lookup_array .

03 we-06

Ukongeza uhlobo lomdlalo kunye nokugqiba umsebenzi we-MATCH

Ukongeza uhlobo lomdlalo kunye nokugqiba umsebenzi we-MATCH

Ingxabano yesithathu kunye yokugqibela yomsebenzi we-MATCH yi- Match_type argument.

Le ngxabano ixelela i-Excel indlela yokufanisa i-Lookup_value kunye namaxabiso kwiLookup_array. Iinqununu zezi: -1, 0, okanye 1.

Le ngxabano ikhethiweyo. Ukuba akushiywanga umsebenzi usebenzisa ixabiso elingagqibekanga le-1.

• ukuba i-Match_type = 1 okanye ingashiywanga: UMATCH ufumana ixabiso elikhulu kunawo wonke okanye lilingana neLookup_value. Ukuba eli khetho likhethiweyo, idatha yeLookup_array kufuneka ihlelwe ngokunyuka komyalelo.
• ukuba iMatch_type = 0: MATCH ifumana ixabiso lokuqala elilingana neLookup_value. Idatha yeLookup_array ingahlulwa kunoma yimuphi umyalelo.
• ukuba iMatch_type = 1: IMATCH ifumana ixabiso elincinane kunaleyo okanye lilingana neLookup_value. Ukuba eli khetho likhethiweyo, idata yeLookup_array kufuneka ihlulwe ngokuhlehla.

Iimfundiso zeTutorial

Ezi nyathelo kufuneka zingeniswe emva kokuba i-comma ingene kwisinyathelo sangaphambili kwi- Row_num line kwibhokisi yencoko yebhokisi ye-VLOOKUP.

1. Ukulandela i-comma yesibini kwi- col_index_num line, thayipha i-" 0 " kuba sifuna umsebenzi odibeneyo ukubuyisela umdlalo ohambelana nenyanga ukufaka kwiseli E2.
2. Thayipha ibhondi yokuvala ejikelezayo " ) " ukugqiba umsebenzi weMATCH.
3. Shiya ibhokisi yencoko yebhokisi yeVLOOKUP evuliwe kwisinyathelo esilandelayo kwisifundo.

04 we-06

Ukufaka i-VLOOKUP Range Lookup Argument

I-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 sibheke amanani athengisayo kwinyanga ethile, siza kuseka iRange_lookup elingana 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 u-OK ugcwalise ifom yefowuni yokujonga kunye nebhokisi yencoko yokuvala
4. Ekubeni asikazanga ukungena kwiimpawu ze-lookup kwiiseli ze-D2 kunye ne-E2 i-# N / A iphoso iya kubakho kwi-cell F2
5. Le mpazamo iya kulungiswa kwisinyathelo esilandelayo kwisifundo xa siya kudibanisa imigaqo yokulandelelana kwisinyathelo esilandelayo soqeqesho.

05 ka 06

Ukuvavanya i-Formula yeLowula yokuLungisa

Ukuvavanya i-Formula yeLowula yokuLungisa

Ukusebenzisa ifom yeendlela ezimbini zokuthengisa ukufumana i-cookie eyahlukileyo kwi-cookie igama kwifayile ye-D2, inyanga ibe yiseli ye-E2 kwaye cinezela ENTER kwikhibhodi.

Idatha yokuthengisa iya kuboniswa kwiseli F2.

Iimfundiso zeTutorial

1. Cofa kwiseli D2 kwiphepha lakho lokusebenzela
2. Thayipha i- Oatmeal kwiseli D2 uze ucinezela ENTER kwikhibhodi
3. Cofa kwi cell E2
4. Thayipha uFebruwari kwiseli ye-E2 uze ucinezela ENTER kwikhibhodi
5. Ixabiso le -1,345 ye-$ - imali yokuthengisa ye-Oatmeal cookies ngenyanga kaFebhuwari - kufuneka iboniswe kwi-cell F2
6. Kule ngongoma, iphepha lokusebenzela kufuneka lifanise umzekelo kwiphepha 1 yale khokelo
7. Ukuvavanya ifom ye-lookup ngokuqhubekayo ngokuthayipha nayiphi na inhlanganisela yeenkukhu zee-cookie kunye neenyanga ezikhoyo kwi-Table_array kunye namanani okuthengisa kufuneka aboniswe kwi-cell F2
8. Inyathelo lokugqibela kwi-tutorial lihlanganisa ukukopisha ifom ye-lookup usebenzisa i- Handill Fill .

Ukuba umyalezo wephutha ufana ne #REF! ibonakala kwiseli F2, olu luhlu lwemiyalezo yesiphoso seVLOOKUP lunokukunceda ukuba ufumane apho ingxaki ikhona khona.

06 we-06

Ukukopisha iFomula yokuLungiswa kweMibini yokuPhakamisa kunye neSigxina sokuzalisa

Ukukopisha iFomula yokuLungiswa kweMibini yokuPhakamisa kunye neSigxina sokuzalisa

Ukuze kube lula ukuthelekisa idatha kwiinyanga ezahlukileyo okanye ii-cookies ezahlukeneyo, ifom ye-lookup ingakopishwa kwezinye iiseli ukwenzela ukuba izixa ezininzi ziboniswe ngexesha elifanayo.

Ekubeni idatha isetyenziswe kwiphepha eliqhelekileyo kwiphepha lokusebenzela, sinokukukopisha ifom ye-lookup kwiselfayile F2 kwisisele F3.

Njengoko ifom ekhutshiwe, i-Excel iya kuhlaziya iiferensi zesisele ezihambelanayo ukubonisa indawo entsha yefomyula. Kule meko i-D2 iba ngu-D3 kunye no-E2 iba ngu-E3,

Kwakhona, i-Excel igcina isalathisi epheleleyo yesalathisi ngokufanayo udidi lwama-$ D $ 5: i-$ G ye-$ 5 ihlala yinto efanayo xa ifomula ikopishwa.

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

Iimfundiso zeTutorial

1. Cofa kwiseli D3 kwiphepha lakho lokusebenzela
2. Thayipha i- Oatmeal kwiseli D3 uze ucinezela ENTER kwikhibhodi
3. Cofa kwi cell E3
4. Thayipha u- Matshi kwi-cell E3 uze ucinezela ENTER kwikhibhodi
5. Cofa kwifayile F2 ukwenzela ukuba yenze iseli esebenzayo
6. Beka isikhombisi semouse phezu kwebala elimnyama kwicala eliphantsi lasekunene. I-pointer iya kutshintshwa kwisibonakaliso esongezelelweyo "+" - yilo Mgca wokugcwalisa
7. Cofa iqhosha lesinxele lemouse uze udibanise isibambo sokugcwalisa phantsi kwiseli F3
8. Ukukhulula inkinobho yemouse kunye nefowuni F3 kufuneka iqulethe ifom yefowuni yokujonga
9. Ixabiso le-1,287 ye-$ - imali yokuthengisa ye-Oatmeal cookies ngenyanga ka-Matshi kufuneka iboniswe kwiseli F3