Yintoni umsebenzi we-XLOOKUP kwaye wenzelwe ntoni kwi-Excel

  • I-XLOOKUP ngumsebenzi wokukhangela kwi-Excel, uhambele phambili ngakumbi kune-VLOOKUP.
  • Ikuvumela ukuba ukhangele amaxabiso athile kulo lonke uxwebhu ngokukhawuleza nangokuchanekileyo.
  • I-syntax yayo ibandakanya iiparameters ezifunekayo kunye nezikhethiweyo zokuguquguquka okukhulu.
  • Ungabuyisela iziphumo ezininzi kwaye wenze imiyalezo ngokusesikweni ukuba ixabiso alifumaneki.

searchx excel

Ukusukela oko iMicrosoft isungule iofisi yayo ngo-1990 ukuza kuthi ga ngoku, uluhlu lophuculo olwaziswayo lude kakhulu. Enye yakutshanje, eya kuphucula amava omsebenzisi kwiprogram yayo yeentombi zokubala, yayikungeniswa komsebenzi wokukhangela. Kule post siza kubona yintoni kanye kanye i-XLOOKUP kwaye yeyantoni kwi-Excel.

Eli nqaku laqala ukudityaniswa njengesigaba se-beta ngo-Agasti 2019. Ngoku, lifumaneka kuphela kubasebenzisi be Microsoft 365. Sisixhobo esiluncedo kakhulu kwabo basebenza kwi-Excel kunye nomthamo omkhulu wedatha, njengoko sikubonisa ngezantsi.

Yintoni i-XLOOKUP?

Ngaphakathi kwe-Excel, umsebenzi SEARCHX yeyeqela lophendlo kunye nemisebenzi yereferensi. Ukusukela oko yaphunyezwa, ibe sesona sixhobo sisetyenziswa kakhulu ngabasebenzisi ngenxa yokusebenza kakuhle kunye nokusebenziseka ngokulula.

I-XLOOKUP iyasinceda ukuba khangela amangeniso athile kwiseti yeeseli. Umsebenzi ofanayo obizwa ngokuba yi-VLOOKUP wawukho ngaphambili, nangona i-XLOOKUP isebenza ngcono kakhulu, kuba isivumela ukuba sikhangele amangeno amaninzi (ngomsebenzi wangaphambili oku kwakungenzeki) kunye nokukhangela amaxabiso ngokuthe nkqo nangokuthe tye.

Ezi mpawu zivula amathuba amatsha kwintsimi esebenzayo. Umzekelo, ukusebenzisa umsebenzi we-XLOOKUP kwitheyibhile enezintlu ezininzi kunye neekholamu kuya kusibonisa into esiyikhangelayo, nokuba ulwazi lubekwe phi. Uphendlo, ngaphezu koko, alupheleli kwiphepha elinye kodwa kulo lonke uxwebhu. Kwaye yonke loo nto ihamba nayo isantya kunye nokuchaneka. Yintoni enye onokuyifuna?

khangela kwi-excel
Inqaku elidibeneyo:
Khangela kwi-Excel: onke amaqhinga

I-syntax yomsebenzi we-XLOOKUP

Njengoko wonke umsebenzisi olungileyo we-Excel sele esazi, umsebenzi ngamnye une-syntax ethile ekufuneka iqulathe uthotho lweeparamitha ezithile. Kufuneka uchaneke kakhulu kuba nakuphi na ukungachaneki okuncinci xa uqulunqa i-syntax kunokukhokelela kwimpazamo. I-syntax ekufuneka isetyenziswe kumsebenzi we-XLOOKUP yile:

=XLOOKUP(ixabiso_lokukhangela, ujongo_uluhlu, ukubuyisela_uluhlu, [ukuba_a_akufunyanwanga], [imowudi_yomdlalo], [imo_yokukhangela])

Iiparamitha ezihambelana nexabiso ngalinye lale fomula zezi zilandelayo:

  • khangela_ixabiso: igalelo esilikhangelayo, elinokuthi lenziwe ngokubhaliweyo, amanani, okanye nezibambi-ndawo.
  • Uluhlu_lokukhangela: ibhekisa kuluhlu okanye uluhlu apho i-Excel kufuneka ikhangele ulwazi olufunekayo.
  • ubuyise_uluhlu: ibhekisa kuluhlu okanye imatrix apho sifuna i-Excel ibuyisele ulwazi olufunekayo.

Ezi zintathu ziiparamitha ezisisiseko. Kukho nezinye iiparameters esinokuthi sizisebenzise ngokuxhomekeke kwimeko nganye:

  • ukuba_ayifunyanwanga: Xa i-Excel ingafumani kungqamana okusebenzayo, inokubuyisela okubhaliweyo okanye ixabiso ebesilinikezile ngaphambili (ukuba akwenziwanga oku, i-Excel iyakubonisa ngokulula umyalezo "#N/A").
  • Imowudi_yokulinganisa: Uhlobo lomdlalo lunokuchazwa kusetyenziswa indlela yothelekiso.
  • imowudi_yokukhangela: Ungaqalisa uphendlo lokubini okanye ugqibe apho uphendlo kufuneka luqale kwaye luphele, usebenzisa ixabiso "1" kwinto yokuqala kunye nexabiso "-1" okokugqibela.

Imizekelo yokusebenzisa umsebenzi weXLOOKUP

Kungenzeka ukuba lonke ulwazi olufunde ukuza kuthi ga apha lushiye ubhidekile. Njengamaxesha onke, kungcono ukubhenela kwimizekelo ebonakalayo esiya kuthi ngayo siqinisekise ngcono ukuba luncedo komsebenzi we-XLOOKUP kwi-Excel:

Ukusebenzisa iiparamitha ezifunekayo kuphela

searchx

Okokuqala, eyona meko ilula apho kufuneka kusetyenziswa kuphela imigaqo emithathu efunekayo "look_value", "lokup_array" kunye ne "return_array".

Kumzekelo wethu sibonisa itafile ngamagama eqela labadlali bebhasikithi kunye nokuphakama kwabo. Iziphumo zokukhangela kufuneka ziboniswe kwiseli G6, ekufuneka siyikhethe. Ifomula ekufuneka siyisebenzise yile ilandelayo:

=BUSCARX(F6;B6:B17;D6:D17)

Ixabiso elikhangelweyo ngu "Galván" (cell F6). Uluhlu olukhangelweyo luluhlu lwamagama (B6: B17), ngelixa uluhlu olubuyiselweyo luyikholomu ehambelana nokuphakama kwendawo nganye (D6: D17). Cinezela nje iqhosha elithi "Enter" ukuzisa iziphumo esizifunayo.

Ukongeza amaxabiso amabini okubuyisela

searchx kwi-excel

Sisebenzisa umzekelo ofanayo njengakwimeko yangaphambili, ngeli xesha kuphela, ngaphezu kobude bomdlali, sifuna ukwazi ukuba yintoni inombolo yakhe yejezi. Yintoni ekufuneka yenziwe ngoko kukwandisa uluhlu olubuyisiweyo ukuze i-Excel ikwazi ukufumana iziphumo zombini. Ifomula ingajongeka ngolu hlobo:

=BUSCARX(F6;B6:B17;C6:D17)

Ukongeza iparameter "ukuba_a_ayifunyanwanga"

khangela kwi-excel

Ngoku makhe sicinge ukuba, kumzekelo ofanayo njengakwimeko ezimbini ezidlulileyo, sifaka kukhangelo lwethu igama lomdlali ongaveliyo kuluhlu. Ilapha xa impendulo ebhaliweyo inokuchazwa ngeparamitha "if_it_is_not_found". Okubhaliweyo kwemveliso ekhethiweyo ngu "Iinkcukacha ezingasebenziyo", nangona nawuphi na omnye umyalezo obonakala ufanelekile kuthi nawo uya kusebenza. I-syntax ehambelanayo iya kuba yile:

=XLOOKUP(F6,B6:B17,C6:D17, "Iinkcukacha ezingasebenziyo")

Le yimizekelo nje emithathu elula nesebenzayo yendlela yokusebenzisa umsebenzi we-XLOOKUP kwi-Excel. Inyaniso kukuba lo msebenzi unokusetyenziswa kumaxwebhu amakhulu kakhulu kwaye anzima kakhulu, ngaloo ndlela ufumana umphumo ofunekayo ngokukhawuleza.


Shiya uluvo lwakho

Idilesi yakho ye email aziyi kupapashwa. ezidingekayo ziphawulwe *

*

*

  1. Uxanduva lwedatha: UMiguel Ángel Gatón
  2. Injongo yedatha: Ulawulo lwe-SPAM, ulawulo lwezimvo.
  3. Umthetho: Imvume yakho
  4. Unxibelelwano lwedatha: Idatha ayizukuhanjiswa kubantu besithathu ngaphandle koxanduva lomthetho.
  5. Ukugcinwa kweenkcukacha
  6. Amalungelo: Ngalo naliphi na ixesha unganciphisa, uphinde uphinde ucime ulwazi lwakho.