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

01 ka-06

Ukuqala ukusebenza kwe-MATCH Function

Buyela kuNgxenye 1

Ukufaka umsebenzi we-MATCH njengeNombolo Yekholomu Yenkomba Yezinombolo

Ngokuvamile i-VLOOKUP ibuyisela kuphela idatha kusuka kukholomu eyodwa yedatha yedatha futhi le kholomu isethwe ngxabano yenombolo yenkomba yenkomba .

Noma kunjalo, kulesi sibonelo sinamakholomu amathathu esifisa ukuthola idatha ngakho sidinga indlela yokushintsha kalula inombolo yenkomba yekholomu ngaphandle kokuhlela ifomula yethu yokubuka.

Yilapho umsebenzi we-MATCH uqala khona. Izosenza sikwazi ukufanisa inombolo yekholomu egameni lesimo - noma ngoJanuwari, Februwari, noma ngoMashi - ukuthi siyifake esitokisini se-E2 sephepha lokusebenzela.

ImiSebenzi Yokuzijabulisa

Ngakho-ke umsebenzi we-MATCH, usebenza njenge-VLOOKUP's index index number argument .

Lokhu kufezwa ngokukhipha umsebenzi we-MATCH ngaphakathi kwe-VLOOKUP emgqeni we- Col_index_num ibhokisi lengxoxo.

Ukufaka i-MATCH Function ngesandla

Uma imisebenzi yokuhlala, i-Excel ayivumeli ukuvula ibhokisi lengxoxo lomsebenzi wesibili ukufaka izingxabano zalo.

Umsebenzi we-MATCH, ngakho-ke, kufanele ungene ngesandla ku- Col_index_num line.

Uma ufaka imisebenzi ngokuzenzekelayo, izingxabano zomsebenzi ngamunye kufanele zihlukaniswe umbala "," .

Izinyathelo Zokufundisa

Ukufaka ukungena kwe-MATCH Function Lookup_value

Isinyathelo sokuqala ekungeneni umsebenzi we-MATCH ocebile ukuthi ufake ukungqubuzana kwe- Lookup_value .

I- Lookup_value izoba yindawo noma ireferensi yeseli yegama lokusesha esifuna ukulifanisa ku-database.

1. Kulobhokisi lebhokisi lokusebenza le-VLOOKUP, chofoza ku- Col_index_num line.
2. Thayipha umdlalo wegama lomsebenzi olandelwe ibakaki evulekile " ( "
3. Chofoza ku-cell E2 ukuze ufake leyo reference yocingo ebhokisini lengxoxo.
4. Thayipha i-comma "," ngemuva kokubhekisela kwe-cell E3 ukuqedela ukungena kwempikiswano yomsebenzi we-MATCH Lookup_value .
5. Shiya ibhokisi lebhokisi lengxoxo yeVLOOKUP evulekile isinyathelo esilandelayo ekufundiseni.

Esikhathini sokugcina sokufundisa i-Lookup_values ​​izofakwa kuma-cell D2 no-E2 kwiphepha lokusebenzela .

02 ka 06

Ukungeza i-Lookup_array yomsebenzi we-MATCH

Ukungeza i-Lookup_array yomsebenzi we-MATCH

Lesi sinyathelo sihlanganisa ukungeza ukuphikisana kwe- Lookup_array komsebenzi we-MATCH owakhiwe .

I- Lookup_array ibanga lamaseli ukuthi umsebenzi we-MATCH uzosesha ukuze uthole ukungqubuzana kwe- Lookup_value kufakwe kwisinyathelo sangaphambilini sesifundo .

Kulesi sibonelo, sifuna umsebenzi we-MATCH ukucinga amaseli D5 kuya ku-G5 ngomdlalo egameni lenyanga ezofakwa efelini le-E2.

Izinyathelo Zokufundisa

Lezi zinyathelo kufanele zifakwe ngemuva kokuba i-comma ingene esiteji esedlule ku- Col_index_num line ebhokisini lengxoxo ye-VLOOKUP.

1. Uma kunesidingo, chofoza ku- Col_index_num line ngemuva kombala ukubeka iphuzu lokufaka ekupheleni kokungena kwamanje.
2. Gqamisa amaseli D5 kuya ku-G5 ekhasini lokusebenzela ukuze ufake lezi zikhombisi zeseli njengoba ububanzi umsebenzi ozowuhlola.
3. Cindezela ukhiye we- F4 ekhibhodi ukuze ushintshe leli banga libe yizingqikithi zeseli eziphelele . Ukwenza kanjalo kuzokwenza ukuthi ukwazi ukukopisha ifomula yokugcwalisa egcwalisiwe kwezinye izindawo kushidi lokusebenzela esiteji sokugcina soqeqesho
4. Thayipha i-comma "," ngemuva kokubhekisela kweseli e- E3 ukuqedela ukungena kwempikiswano yomsebenzi we-MATCH Lookup_array .

03 ka 06

Ukwengeza uhlobo lomdlalo nokuqedela ukusebenza kwe-MATCH

Ukwengeza uhlobo lomdlalo nokuqedela ukusebenza kwe-MATCH

Iphikisana lesithathu nelokugcina lomsebenzi we-MATCH yi- argument Match_type.

Le mpikiswano iyatshela i-Excel ukuthi ifana kanjani ne-Lookup_value ngamanani ku-Lookup_array. Izinqumo ziyi: -1, 0, noma 1.

Le mpikiswano iyakhethwa. Uma ingavumelekile umsebenzi usebenzise inani elizenzakalelayo le-1.

• uma i-Match_type = 1 noma ishiyiwe: MATCH ithola inani elikhulu kunazo zonke elingaphansi noma elingana ne-Lookup_value. Uma leli xabiso likhethiwe, idatha ye-Lookup_array kufanele ihlungwe ngokunyuka kwe-oda.
• uma i-Match_type = 0: MATCH ithola inani lokuqala elilingana ncamashi ne-Lookup_value. Idatha ye-Lookup_array ingahle ihlelwe kunoma iyiphi i-oda.
• uma i-Match_type = 1: MATCH ithola inani elincane kunazo zonke noma lilingana ne-Lookup_value. Uma ngabe leli khetho likhethiwe, idatha ye-Lookup_array kufanele ihlungwe ngokuhlehlisa.

Izinyathelo Zokufundisa

Lezi zinyathelo kufanele zifakwe ngemuva kokuba i-comma ingene esiteji sangaphambilini ku- Row_num line ebhokisini lengxoxo ye-VLOOKUP.

1. Ukulandela ikomidi yesibili ku- Col_index_num line, thayipha i-zero " 0 " ngoba sifuna umsebenzi osikiwe ukubuyisela umdlalo oqondile ngenyanga ukufaka esitokisini E2.
2. Thayipha ibhakha lokuvala " ) " ukuqedela umsebenzi we-MATCH.
3. Shiya ibhokisi lebhokisi lengxoxo yeVLOOKUP evulekile isinyathelo esilandelayo ekufundiseni.

04 ka 06

Ukufaka i-VLOOKUP Range Lookup Argument

I-Range Lookup Argument

I-Range_lookup ingxabano kaVLOOKUP yinani elilinganiselwe (TRUE noma FALSE kuphela) elikhombisa ukuthi ufuna i-VLOOKUP ukuthola umlinganiso oqondile noma olinganayo ku-Lookup_value.

• 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 sifuna izibalo zokuthengisa kwenyanga ethile, sizobeka i-Range_lookup efana neyamanga.

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 lokuxoxa lefomula le-close and zoom close
4. Njengoba singakaze sithole ukungena kwe-lookup kumaseli e-D2 no-E2 iphutha elingu- # N / A lizoba khona kuseli F2
5. Leli phutha lizolungiswa esiteji esilandelayo ekufundiseni lapho sizofaka imodeli yokulandelela kwisinyathelo esilandelayo sesifundo.

05 ka 06

Ukuvivinya i-Formula YokuLula I-Two Way

Ukuvivinya i-Formula YokuLula I-Two Way

Ukuze usebenzise ifomula yokuthola indlela emibili ukuthola idatha yokuthengisa nyangazonke yama-cookies ahlukene efakwe ohlwini lwetafula, thayipha igama lekhukhi kwi-cell D2, inyanga ibe yi-cell E2 bese ucindezela inkinobho ENTER ekhibhodi.

Idatha yokuthengisa izoboniswa kuseli F2.

Izinyathelo Zokufundisa

1. Chofoza ku-cell D2 kushidi lakho lokusebenzela
2. Thayipha Oatmeal esitokisini D2 bese ucindezela inkinobho ENTER ekhibhodi
3. Chofoza kumakhalekhukhwini E2
4. Thayipha uFebhuwari ungene esitokisini E2 bese ucindezela inkinobho ENTER ekhibhodi
5. Inani elibiza u-$ 1,345 - inani lokuthengisa lama-coat oatmeal ngenyanga kaFebruwari - kumele liboniswe kuseli F2
6. Kuleli phuzu, ishidi lakho lokusebenzela kufanele lifanane nesibonelo ekhasini 1 lale tutorial
7. Hlola ifomu lokubheka ngokuqhubekayo ngokuthayipha noma yikuphi ukuhlanganiswa kwezinhlobo ze-cookie nezinyanga ezikhona ku-Table_array futhi izibalo zokuthengisa kufanele ziboniswe kuseli F2
8. Isinyathelo sokugcina esifundweni sihlanganisa ukukopisha ifomula yokubheka usebenzisa i- Handle Fill .

Uma umlayezo wephutha onjengo- #REF! ivela kuseli F2, lolu hlu lwemiyalezo yephutha le-VLOOKUP lingakusiza ukuthi ubone ukuthi iyiphi inkinga ekhona.

06 ka-06

Ukukopisha i-Formula Lookup Formula Yesibili nge-Handle Fill

Ukukopisha i-Formula Lookup Formula Yesibili nge-Handle Fill

Ukuze kube lula ukuqhathanisa idatha yezinyanga ezahlukahlukene noma amakhukhi ahlukene, ifomula ye-lookup ingakopishwa kwamanye amaseli ukuze imali eminingi ingaboniswa ngesikhathi esifanayo.

Njengoba idatha ibekwe iphethini evamile kuphepha lokusebenzela, singakopisha ifomula yokungena ku-cell F2 kuya kuseli F3.

Njengoba ifomula likopishiwe, i-Excel izobuyekeza izikhombisi zeseli ezihlobene ukuze kuboniswe indawo entsha yefomula. Kulokhu D2 iba D3 no-E2 iba E3,

Futhi, i-Excel igcina ireferensi yeseli ephelele ngokufanayo ngakho uhla oluphelele $ D $ 5: $ G $ 5 lihlala lifanayo uma ifomula likopishwa.

Kunendlela engaphezu kweyodwa yokukopisha idatha ku-Excel, kodwa mhlawumbe indlela elula kakhulu ngokusebenzisa i-Handle Fill.

Izinyathelo Zokufundisa

1. Chofoza kuseli D3 kushidi lakho lokusebenzela
2. Thayipha i- Oatmeal esitokisini D3 bese ucindezela inkinobho ENTER ekhibhodi
3. Chofoza kuseli E3
4. Thayipha u- Mashi esitokisini E3 bese ucindezela inkinobho ENTER ekhibhodi
5. Chofoza kuseli F2 ukuze wenze iseli esebenzayo
6. Beka isikhombisi segundane phezu kwesikwele esimnyama ngakwesokudla kwesokudla. I-pointer izoshintsha ibe nesibonakaliso esengeziwe "+" - yi-Handle Fill
7. Chofoza inkinobho yegundane lesokunxele bese uhudula isibambo sokugcwalisa phansi kweseli F3
8. Khipha inkinobho yegundane kanye neseli F3 kufanele uqukathe ifomula yokubheka kokubili
9. Inani elibiza u-$ 1,287 - inani lokuthengisa lama-coat oatmeal ngenyanga ka-Mashi- kufanele liboniswe kuseli le-F3