Thola amasimu amaningi weDatha nge-Excel VLOOKUP

Ngokuhlanganisa umsebenzi we-VLOOKUP we-Excel nomsebenzi we- COLUMN singakha ifomula yokukuvumela evumela ukuthi ubuyeke amanani amaningi kusuka kumugqa owodwa wedatha noma ithebula lwedatha.

Esikhathini esiboniswe esithombeni esingenhla, ifomula ye-lookup yenza kube lula ukubuyisela wonke amanani - njengamanani, inombolo yenxenye, nomthengisi - ezihlobene nezingcezu ezihlukahlukene ze-hardware.

01 kwezingu-10

Buyisela izimiso eziningi nge-Excel VLOOKUP

Ukulandela izinyathelo ezibalwe ngezansi kudala ifomula yokubheka ebonwe esithombeni ngenhla ezobuyisela amanani amaningi kusuka kwirekhodi elilodwa lwedatha.

Ifomula ye-lookup idinga ukuthi umsebenzi we-COLUMN ubekwe ngaphakathi ngaphakathi kwe-VLOOKUP.

Ukwenza umsebenzi kuhilela ukufaka umsebenzi wesibili njengenye yezingxabano zomsebenzi wokuqala.

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

Isinyathelo sokugcina ekufundiseni kuhilela ukukopisha ifomula yokungena kumakholomu engeziwe ukuze uthole amanani angeziwe engxenyeni ekhethiwe.

Okuqukethwe kokufundisa

• Ukufaka idatha yokufundisa
• Ukwakha Uhlobo Olubizwa NgeThebula Ledatha
• Ukuqala umsebenzi we-VLOOKUP
• Ukufaka ukuxabana kokubaluleka kokusebenzisa i-Absolute Cell References
• Ukufaka i-Table Array Argument
• Ukufaka umsebenzi we-COLUMN okhonjiwe
• Ukuqedela umsebenzi we-VLOOKUP
• Ukukopisha i-Formula Lookup ne-Fill Handle
• Ukubuyiswa kwedatha nge-Formula Lookup

02 kwezingu-10

Faka Idatha Yokufundisa

Isinyathelo sokuqala esifundweni ukufaka idatha kwi-Excel sheet sheet .

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

• Faka uchungechunge oluphezulu lwedatha kumaseli D1 kuya ku-G1
• Faka ibanga lesibili kumaseli D4 kuya ku-G10

I-criteria yokusesha kanye nefomula ye-lookup edalwe phakathi nalesi sifundo izofakwa kumugqa wesibili wephepha lokusebenzela.

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 kuboniswa esithombeni ngenhla kumaseli D1 kuya ku-G10

03 ka-10

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.

Amagama wamangcele ke, okunye okunye okusetshenziselwa ukusebenzisa izikhombisi zezingqimba eziphelele ukuvimbela amaphutha uma ukopisha amafomula.

Qaphela: Igama lebanga alifaki amagama noma amagama emasimu wedatha (umugqa 4) kodwa kuphela idatha ngokwayo.

Izinyathelo Zokufundisa

1. Gqamisa amaseli D5 kuya ku-G10 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-G10 manje anegama lokubala "Ithebula". Sizosebenzisa igama lempikiswano ye-VLOOKUP etafuleni kamuva esikhathini sokufundisa

04 kwezingu-10

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 E2 kwiphepha 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 uvule ibhokisi lengxoxo lomsebenzi

05 ka-10

Ukufaka ukuxabana kokubaluleka kokusebenzisa i-Absolute Cell References

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

Esikhathini sethu, inani lokubheka libhekisela egameni le-hardware ingxenye mayelana nokuthi sifuna ukuthola ulwazi.

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 legama elizobe selikhona khona - iseli D2.

Ama-Absolute Cell References

Esikhathini esilandelayo esilandelayo ekufundiseni, sizokopisha ifomula yokubheka ku-E2 kumaseli F2 no-G2.

Ngokuvamile, uma amafomula abhalwa ku-Excel, izinkomba zezingcingo zishintsha ukuze zibonise indawo yazo entsha.

Uma lokhu kwenzeka, i-D2 - ireferensi yeseli ye- value lookup - izoshintsha njengoba ifomula ikopishwa ukudala amaphutha kumaseli F2 no-G2.

Ukuvimbela amaphutha, sizoguqula isithenjwa se-cell D2 sibe yinkomba yeselula ngokuphelele .

Izingqikithi zeseli ezingaguquki aziguquki uma amafomula ekopishwa.

Izinkomba ezingenalutho zeseli zidalwa ngokucindezela ukhiye we- F4 ekhibhodi. Ukwenza kanjalo kunezela izibonakaliso ze-dollar ezungeze ireferensi yeseli njenge $ D $ 2

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 sizokuthayipha igama legama mayelana nokuthi sifuna ulwazi
3. Ngaphandle kokuhambisa iphuzu lokufaka, cindezela ukhiye we- F4 ekhibhodi ukuze uguqule i-D2 ibe yereferensi yeseli ephelele $ D $ 2
4. Shiya ibhokisi lebhokisi lengxoxo yeVLOOKUP evulekile isinyathelo esilandelayo ekufundiseni

06 kwangu-10

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 sesifundo.

Izinyathelo Zokufundisa

1. Chofoza kutafula_layini lomugqa ebhokisini lengxoxo
2. Thayipha "Ithebula" (akukho izingcaphuno) ukungena igama lebanga lale mpikiswano
3. Shiya ibhokisi lebhokisi lengxoxo yeVLOOKUP evulekile isinyathelo esilandelayo ekufundiseni

07 kwangu-10

Yenza umsebenzi we-COLUMN

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

Kulesi sibonelo, noma kunjalo, sinamakholomu amathathu esifisa ukubuyisela idatha kusukela ngakho sidinga indlela yokushintsha kalula inombolo yenkomba yekholomu ngaphandle kokuhlela ifomula yethu yokubuka.

Yilapho umsebenzi we-COLUMN ungena khona. Ngokungena njengengxabano yenombolo yenkomba yekholomu , kuzoshintsha njengoba ifomula ye-lookup ikopiwe kusuka kuseli D2 kuya kwamaseli E2 no-F2 kamuva ekufundiseni.

ImiSebenzi Yokuzijabulisa

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

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

Ukufaka umsebenzi we-COLUMN Ngokuzenzakalelayo

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

Ngakho-ke, umsebenzi we-COLUMN kufanele ungene ngesandla ku- col_index_num line.

Umsebenzi we-COLUMN unempikiswano eyodwa kuphela - impikiswano yokubhekisela okubhekiselwe kweseli.

Ukukhetha i-Argumument ye-Function Reference Reference

Umsebenzi we-COLUMN umsebenzi wokubuyisela inombolo yekholomu enikezwe njenge-argument Reference .

Ngamanye amagama, iguqula incwadi yekholomu ibe yinombolo enekholomu A yokuba ikholomu yokuqala, ikholomu B kwesibili nokunye.

Njengoba insimu yokuqala yedatha esifuna ukubuyelwa yintengo yento - okukukholomu kwetafula lemininingwane yedatha - singakhetha ukubhekisela kweseli nganoma yiliphi iseli kukholomu B njenge-Argumument Reference ukuze uthole inombolo engu-2 ingxabano ye- Col_index_num .

Izinyathelo Zokufundisa

1. Kulobhokisi lebhokisi lokusebenza le-VLOOKUP, chofoza ku- Col_index_num line
2. Thayipha ikholomu igama lomsebenzi elilandelwe ibakaki evulekile " ( "
3. Chofoza kuseli B1 kusikripthi sokusebenzela ukuze ufake leyo reference reference njengengxabano yokubhekisela
4. Thayipha ibhakha lokuvala " ) " ukuqedela umsebenzi we-COLUMN
5. Shiya ibhokisi lebhokisi lengxoxo yeVLOOKUP evulekile isinyathelo esilandelayo ekufundiseni

08 kwezingu-10

Ukufaka i-VLOOKUP 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 ulwazi oluthile mayelana nento ethile yehadiwe, 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 lokungena lefomula yokuvala nokuvala
4. Njengoba singakayifaki isicelo sokungena ngemvume esitokisini se-D2 iphutha le- # N / A lizobe selikhona kuseli le-E2
5. Leli phutha lizolungiswa uma sizofaka izinkomba zokubheka ekugcineni kwesifundo

09 kwezingu-10

Ukukopisha i-Formula Lookup ne-Fill Handle

Ifomula ye-lookup ihloselwe ukubuyisa idatha kusuka kumakholomu amaningi wetafula yedatha ngesikhathi esisodwa.

Ukuze wenze lokhu, ifomula yokungena kufanele ihlale kuwo wonke amasimu esifuna ulwazi.

Kulesi sifundo sifuna ukuthi ithole idatha kusuka kumakholomu 2, 3, no-4 etafuleni yedatha - leyo yintengo, inombolo yenxenye, negama lomphakeli lapho sifaka igama legama njengeLookup_value.

Njengoba idatha ibekwe iphethini evamile kuphepha lokusebenzela , singakwazi ukukopisha ifomula ye-lookup kuseli E2 kumaseli F2 no-G2.

Njengoba ifomula likopishiwe, i-Excel izobuyekeza isithenjwa sefoni esilandelayo emsebenzini we-COLUMN (B1) ukubonisa indawo entsha yefomula.

Ngokufanayo, i-Excel ayishintshi ireferensi yeseli ephelele $ D $ 2 kanye neThebula elibanzi elibizwa ngokuthi ifomula likopishwe.

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

Izinyathelo Zokufundisa

1. Chofoza ku-cell E2 - lapho ifomula ye-lookup itholakala khona - ukwenza kube iseli elisebenzayo
2. Beka isikhombisi segundane phezu kwesikwele esimnyama ngakwesokudla kwesokudla. I-pointer izoshintsha ibe nesibonakaliso esengeziwe " + " - lena isibambo sokugcwalisa
3. Chofoza inkinobho yegundane lesokunxele bese uhudula isibambo sokugcwalisa ngaphesheya kweseli G2
4. Khipha inkinobho yegundane kanye neseli F3 kufanele uqukathe ifomula yokubheka kokubili
5. Uma kwenziwe kahle, amaseli F2 no-G2 kufanele manje aqukethe iphutha le- # N / A elikhona kuseli E2

10 kwangu-10

Ukufaka izinketho zokuLondoloza

Uma ifomula ye-lookup ikopishwe kumaseli adingekayo ingasetshenziselwa ukubuyisa ulwazi kusuka kuthebula lwedatha.

Ukwenza kanjalo, thayipha igama lento ofuna ukuyifaka kwiselula ye-Lookup_value (D2) bese ucindezela inkinobho ENTER ekhibhodi.

Uma usuqedile, iseli ngalinye eliqukethe ifomula ye-lookup kufanele libe nengxenye ehlukile yedatha mayelana nento yehadiwe oyifunayo.

Izinyathelo Zokufundisa

1. Chofoza kuseli D2 kushidi lokusebenzela
2. Thayipha i- Widget esitokisini D2 bese ucindezela inkinobho ENTER ekhibhodi
3. Ulwazi olulandelayo kufanele luboniswe kumaseli E2 kuya ku-G2:
   • E2 - $ 14.76 - intengo yewijethi
   • F2 - PN-98769 - inombolo yenxenye yewijethi
   • I-G2 - Amawijethi Inc. - igama lomphakeli wamawijethi
4. Hlola i-formula ye-VLOOKUP eqhubekayo ngokuthayipha igama lezinye izingxenye zibe yiseli D2 futhi ugcine imiphumela kumaseli E2 kuya ku-G2

Uma umlayezo wephutha onjengo- #REF! livela kumaseli e-E2, F2, noma i-G2, lolu hlu lwemiyalezo yephutha le-VLOOKUP lingakusiza ukuthi ubone ukuthi iyiphi inkinga ekhona.