I-VLOOKUP Ayisebenzi. Izimbangela Nezixazululo

Nakuba idume kakhulu ngokubala kwayo nemisebenzi ye-graphing, Excel inikeza ezinye izinsiza eziningi kubasebenzisi bayo. Omunye wabo umsebenzi VLOOKUP, okungenza umsebenzi wakho usheshe futhi ube lula. Kodwa kungase kwenzeke ukuthi unezinkinga ngayo futhi uqaphele lokho I-VLOOKUP ayisebenzi.

Lapha sizochaza ukuthi kungani I-VLOOKUP ayisebenzi, nokuthi yini ongayenza uma kwenzeka kuwe. Kuyinto elula ukuyiqonda, engadingi ulwazi oluhle kumhleli. Microsoft.

Uyini Umsebenzi we-VLOOKUP?

Umsebenzi wokusesha obizwa ngokuthi «VLOOKUP»imane nje iyifomula echazwe ngaphambilini engakusiza uthole ulwazi ohlwini noma kumathebula e-Excel. Kungenza imisebenzi yakho ibe lula kakhulu, ikakhulukazi uma kunokuqukethwe okuningi futhi ukusesha okwenziwa ngesandla kungase kudle isikhathi.

Ngokuvamile, lona umsebenzi ozokuvumela ukuthi useshe, ngolwazi olubalulekile, ukuze uthole olunye ulwazi oluhambisanayo ngaphakathi kwedatha yakho.

Kodwa-ke, leli ithuluzi elikuvumela ukuthi useshe ngokuqondile, okungukuthi, eduze kwekholomu.

Usebenza kanjani Umsebenzi we-VLOOKUP

Lo msebenzi wakhiwe ngamapharamitha okusesha amane:

=LOOKUP(inani_lokubheka, ithebula lomzali, inombolo_yenkomba_yekholomu, [sesha_ububanzi])

• Inani lokusesha: Lolu ulwazi ofuna ukulucinga ngakho-ke luyadingeka. Kufanele njalo kube ikholomu “inombolo 1” yohlu noma ithebula lakho.
• Itafula lomzali: Imele ububanzi bamaseli lapho ulwazi onalo lukhona, ngakho futhi luyisibopho.
• Inombolo yenkomba yekholomu: Lapha inombolo yekholomu ofuna ukuthola kuyo "inani lokusesha" izosetshenziswa. Lolu lwazi luyisibopho.
• Ibanga lokusesha: Lolu wulwazi kuphela ongalukhetha, njengoba kufanele lusetshenziswe kuphela uma ufuna ukucacisa ukufana ncamashi noma cishe, lapho kusetshenziswa okuthi "FALSE" lapho kuseshwa okufana ncamashí kanye nokuthi "TRUE" uma kungalinganiselwa.

Umsebenzi VLOOKUP ayicabangi izihloko zekholomu ezinjengokuthi "A, B, C...". Kufanele njalo ubale amakholomu bese ufaka inombolo ebhekisela kuyo, njalo ubala ukusuka kwesokunxele uye kwesokudla, kusukela kukholamu yokuqala ngolwazi.

Lolu ucezu olunembile lolwazi okungafanele ulukhohlwe, njengoba luholela ekudidekeni okuningi, futhi lubangele ukuthi ifomula ingasebenzi kahle uma isetshenziswa.

Ungahle ube nentshisekelo Indlela Yokwenza Ibhodi Yabaphambili ku-Excel

Ungawusebenzisa Kanjani Umsebenzi we-VLOOKUP

Manje njengoba usuwazi ukuthi uzowusebenzisa nini umsebenzi nokuthi wakheke kanjani, ake sibone ukuthi ungawusebenzisa kanjani ngesibonelo. Ake sithi unebhizinisi. Njengoba imikhiqizo oyinikezayo iyahlukahluka, amanani namanani nawo ayahluka.

Ake siqonde indlela yokusebenzisa VLOOKUP ukuthola ulwazi mayelana nezenzo zakho kalula futhi ngokushesha.

• Faka ulwazi kuspredishithi ukuze ikholomu yokuqala enokuqukethwe ihlale iyisisekelo sakho sokusesha. Kulokhu, kusetshenziswa ikholomu “Umkhiqizo” ukuthi sizothola okufanayo kokuthi “Inani"Noma"Intengo".

• Ngaphandle kohlu, bhala usesho lwakho kumakholomu eceleni. Amaseli kumele aqukathe isihloko salokho okuzoseshwa nesihloko salokho okuzotholakala.
• Manje ake sifake ifomula.
• Zimise phakathi iseli G4, bese uchofoza “ithebhuAmafomula".
• Bese uchofoza “Faka umsebenzi".
• Ebhokisini lokuqukethwe, uzothola isigaba “Thola umsebenzi”. Bhala lapha "VLOOKUP".
• Bese, chofoza "ukwamukela".
• Izigaba ezine okufanele uzigcwalise ukuze ugcwalise ifomula zizovela.
  • Kuvelu eliseshiwe, kufanele ufake okufunayo, kithina emelelwe iseli G3.
  • Sesha i-matrix, yithebula ledatha eliphelele. Esimweni sethu kusuka ku-B4 kuya ku-D14.
  • Inkomba yamakholomu, inombolo yekholomu ofuna kuyo umphumela. Kufanele ubale wonke amakholomu kuthebula. Esimweni sethu, yikholomu "Intengo”, okungaba inombolo yesi-3.
  • Ngokuhlelekile, kufanele ubeke "FAKE” uma udinga umphumela oqondile ovela etafuleni, noma “NGEMPELA” uma nje ufuna inani elilinganiselwe. Esimeni sethu, sibeka "FAKE".
• Bese uchofoza “ukwamukela".
• Uma ufaka umsebenzi, uzobona ukuthi kubizwe iphutha “#N/A”. Ungakhathazeki, lokhu kwenzeka kuphela ngoba iseli “G3” ayigcwalisiwe futhi lena ipharamitha edingekayo.
• Umsebenzi wakho usulungile. Ukuze useshe, faka iseli “G3"noma yikuphi"Imikhiqizo"kulabo abafuna ukumazi"Intengo".

Esibonelweni sethu, sifuna ukwazi intengo “Ilayisi", esinika umphumela olindelekile"I-8,00 euro".

Manje njengoba usukwazi ukusesha usebenzisa VLOOKUP, ungashintsha amakholomu ngokwezidingo zakho. Ngaleli thuluzi, uzosindisa isikhathi esiningi emisebenzini yakho yansuku zonke.

I-VLOOKUP Ayisebenzi

VLOOKUP ngumyalo ku Excel okungenzeka kungasebenzi kahle ngoba udinga ukuhlanganisa izimpikiswano eziningi. Umsebenzi uwusizo ekusesheni amanani kuthebula ngaphandle kokusesha mathupha.

Ngayo, kungenzeka, isibonelo, ukudala inkinobho yokubonisana nentengo yomkhiqizo obhaliswe eshidini lokusungula, njengoba sikubonise esibonelweni esingenhla.

Nokho, umsebenzi kufanele uqedele ngempumelelo ukuze ungabuyisi ukwehluleka. Kuya ngokuthi uthayipha kanjani kuseli, ungathola amakhodi amaphutha afana “#N/A”, "#REF!" y "#KUFANELE!".

Ngezansi sichaza indlela yokuxazulula izinkinga eziyinhloko ngomsebenzi VLOOKUP ku-Excel.

1. Iphutha elithi “#N/A” nempikiswano ethi “TRUE”

Iphutha "#N/A" Ngokuvamile kwenzeka lapho ububanzi bubhalwe ngokungalungile emyalweni, bungafani nevelu etholiwe. Ukuze ugweme inkinga, kuyadingeka ukwengeza efanayo equkethe inani liseshiwe njengekholomu yokuqala ye-matrix.

Uma usesha intengo yomkhiqizo kusipredishithi se-inventory, njengakusibonelo sangaphambilini, umyalo kufanele ubuyisele ikholomu yokuqala yobubanzi obuqukethe amagama emikhiqizo.

Kubalulekile ukucabangela ikholomu yokuqala hhayi leyo yeseli yenkinobho yombuzo wentengo.

2. Iphutha elithi “#N/A” nempikiswano ethi “FALSE”

Lapho umsebenzisi esebenzisa ukuphikisana "Amanga"phakathi VLOOKUP, iphutha “#N/A” Empeleni akulona iphutha. Umlayezo usho nje ukuthi ukusesha akutholanga inani eliqondile obulifuna.

Ukuze ucabangele amanani alinganiselwe, kufanelekile ukukhumbula ukuthi kufanele usebenzise impikiswano «Yiqiniso«. I-agumenti ethi “FALSE” ingeyokufana ngqo kuphela, ngakho-ke, kufanele ubeke idatha oyifunayo ngendlela efanele ukuze inikeze umphumela olindelekile.

3. Iphutha “#REF!”

Iphutha "#REF!" kulula ukuxazulula njengoba kukhombisa iphutha kuphela lapho ufaka inombolo yereferensi yekholomu. Le nombolo iwukungqubuzana kokugcina komyalo futhi ikhombisa ukuhleleka kwekholomu phakathi kwamalungu afanayo okufanele kucatshangelwe lapho kuseshwa.

Ukucabangela kuphela ikholomu yesithathu ku-matrix yamakholomu amathathu esiyibone ngenhla, ungabhala «3» engxabanweni. Uma, ngephutha, umsebenzisi efaka inani lekholomu elehlukile, i-Excel ibuya nephutha "#REF!".

Ukuze ulungise lokhu, kwanele, ngakho-ke, ukushintsha inombolo ibe yinani elifanele, ngaphakathi kwenani lamakholomu amakwe ku-matrix.

4. Iphutha “#VALUE!”

Iphutha "#KUFANELE!" Ingavela ezimeni ezimbili. Enye yazo yenzeka lapho iseli eliseshiwe, elibhalwe kuqala kumpikiswano, linezinhlamvu ezingaphezu kuka-255. Njengoba imbangela ngokuvamile kuwukuthayipha, vele uye kuseli futhi ulungise inani.

Esinye isimo futhi sihilela inombolo yereferensi yekholomu. Uma uthayipha umyalo VLOOKUP, inombolo ebonisa ukuhleleka kwekholomu ku-matrix kufanele ngokusobala ibe nkulu kunoziro. Lapho umsebenzisi engena ngephutha "0", iseli libonisa umlayezo "# IQINISO!".

5. Iphutha “#NAME?”

Excel ukhipha iphutha "#I-YAM?" umyalo wokumisa VLOOKUP lapho umsebenzisi esebenzisa igama njengesithenjwa sosesho kodwa akhohlwe ukulifaka kumacaphuno.

Inkinga ingaxazululwa kalula ngokungeza izingcaphuno ngaphambi nangemva kwegama. Ukwengeza, kuyenzeka ukhethe iseli lapho igama litholakala khona ukuze ugweme amaphutha okubhala angenzeka.

6. Iphutha “#DUMP!”

Excel ibonisa ikhodi yephutha “#DUMP!” lapho umsebenzisi esebenzisa ikholomu yonke njengereferensi yengxabano. VLOOKUP ikuvumela ukuthi wenze lokhu, kodwa kudinga ukusebenzisa ukuhola kuphawu ukuze ugweme izinkinga, njengakufomethi: =VLOOKUP (@A:A; A:C; 2; 0)

7. Iphutha lenani elibi

VLOOKUP Ingase futhi ibuyisele inani elingalungile ngokuphelele. Lena akuyona ikhodi yephutha, kodwa ukuziphatha okungajwayelekile ngenxa yephutha kufomethi yamalungu afanayo.

Lokhu kwenzeka uma umsebenzisi esebenzisa umyalo othi “NGEMPELA” ukuze uthole amanani alinganiselwe, kodwa ukhohlwe ukuhlela izinto kukholamu yokuqala. Ukuze ukusesha okungaqondakali kusebenze, ikholomu yokuqala kufanele ibe ngokulandelana kwezinhlamvu noma izinombolo.

Kungenzeka futhi ukuthi inikeze amanani angalungile, uma umyalo "FAKE” kodwa idatha yosesho ayihlelwanga ngokulandelana okunyukayo.

Ukuvala

Ungahle ube nentshisekelo Indlela Yokuxhumanisa Uhlu Olwehlayo ku-Excel

Ngokucaciswa komsebenzi kanye nokusetshenziswa kwawo, ngaphezu kwamaphutha okungenzeka uhlangane nawo, sicabanga ukuthi usukulungele ukusetshenziswa. umsebenzi we-VLOOKUP. Akunzima kakhulu ukuyisebenzisa, ukucaca mayelana nemibono nokuhleleka kwefomula lapho uyidala.