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?
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
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
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"
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.