Selokhu iMicrosoft yethula ihhovisi layo emuva ngo-1990 kuze kube namuhla, uhlu lwentuthuko eyethulwe selude kakhulu. Okunye kwakamuva, okuze ukuthuthukisa ulwazi lomsebenzisi wohlelo lwamadodakazi okubala, kwaba ukwethulwa komsebenzi wokusesha. Kule post sizobona iyini ngempela i-XLOOKUP futhi ingeyani ku-Excel.
Lesi sici sahlanganiswa okokuqala njengesigaba se-beta ngo-Agasti 2019. Okwamanje, sitholakala kuphela kubasebenzisi be- I-Microsoft 365. Kuyithuluzi eliwusizo kakhulu kulabo abasebenza ku-Excel ngenani elikhulu ledatha, njengoba sikukhombisa ngezansi.
Yini i-XLOOKUP?
Ngaphakathi kwe-Excel, umsebenzi SEARCHX ingeyeqembu lokusesha kanye nemisebenzi yereferensi. Selokhu yaqalwa, isibe ngelinye lamathuluzi asetshenziswa kakhulu ngabasebenzisi ngenxa yokusebenza kahle kwayo kanye nokusebenziseka kalula.
I-XLOOKUP isisiza ukuba senze kanjalo sesha okufakiwe okuthile kusethi yamaseli. Umsebenzi ofanayo obizwa ngokuthi i-VLOOKUP wawukhona ngaphambili, nakuba i-XLOOKUP isebenza kangcono kakhulu, njengoba isivumela ukuthi sifune okufakiwe okuningi (ngomsebenzi wangaphambilini lokhu bekungenzeki) kanye nokusesha amanani ngokuqondile nangokuvundlile.
Lezi zimfanelo zivula amathuba amasha emkhakheni ongokoqobo. Isibonelo, ukusebenzisa umsebenzi we-XLOOKUP kuthebula elinemigqa namakholomu amaningi kuzosibonisa esikufunayo, kungakhathaliseki ukuthi ulwazi lukuphi. Ukusesha, ngaphezu kwalokho, akugcini ekhasini elilodwa kodwa kuyo yonke idokhumenti. Futhi konke lokho kuhambisana isivinini nokunemba. Yini enye ongayifuna?
I-syntax yomsebenzi we-XLOOKUP
Njengoba wonke umsebenzisi omuhle we-Excel eseyazi, umsebenzi ngamunye une-syntax ethile okufanele iqukathe uchungechunge lwamapharamitha athile. Kufanele unembe kakhulu ngoba noma yikuphi ukunganembi okuncane lapho uhlanganisa i-syntax kungaholela ephutheni. I-syntax okufanele isetshenziswe kumsebenzi we-XLOOKUP yilena:
=I-XLOOKUP(inani_lokubheka, uhlu_lokubheka, uhlu_lokubuyisela, [uma_lungatholakali], [imodi_yokufanisa], [imodi_yokusesha])
Amapharamitha ahambisana nenani ngalinye lale fomula yilawa alandelayo:
- lookup_value: okokufaka esikufunayo, okungakhiwa umbhalo, izinombolo, noma izibambindawo.
- I-Array_search: ibhekisela ebangeni noma amalungu afanayo lapho i-Excel kufanele iseshe khona ulwazi olufunayo.
- kubuyiswe_amalungu afanayo: ibhekisela ebangeni noma i-matrix lapho sifuna i-Excel ibuyisele ulwazi olufunekayo.
Lezi zintathu ziyimingcele eyisisekelo. Kukhona namanye amapharamitha ongawakhetha esingawasebenzisa kuye ngesimo ngasinye:
- uma_ingatholakali: Uma i-Excel ingakutholi okufanayo okuvumelekile, ingabuyisela umbhalo noma inani esisinikeze lona ngaphambilini (uma lokhu kungenziwanga, i-Excel izovele ibonise umlayezo othi "#N/A").
- fanisa_imodi: Uhlobo lokufanisa lungacaciswa kusetshenziswa imodi yokuqhathanisa.
- search_mode: Ungaqalisa ukusesha okumbambili noma unqume ukuthi ukusesha kufanele kuqale futhi kuphele kuphi, usebenzisa inani elithi "1" lesici sokuqala kanye nenani elithi "-1" okokugcina.
Izibonelo zokusebenzisa umsebenzi we-XLOOKUP
Kungenzeka ukuthi yonke imininingwane oyifunde kuze kube lapha ikushiye udidekile. Njengenjwayelo, kungcono ukuphendukela ezibonelweni ezisebenzayo esizokwazi ngazo ukuthi siqinisekise kangcono ukusebenziseka komsebenzi we-XLOOKUP ku-Excel:
Ukusebenzisa amapharamitha adingekayo kuphela
Okokuqala, icala elilula lapho kufanele kusetshenziswa imininingwane emithathu kuphela eyimpoqo "i-lookup_value", "lookup_array" kanye "return_array".
Esibonelweni sethu sethula itafula elinamagama eqembu labadlali be-basketball nobude babo. Umphumela wosesho kufanele uboniswe kuseli G6, okufanele siyikhethe. Ifomula okufanele siyisebenzise yile elandelayo:
=BUSCARX(F6;B6:B17;D6:D17)
Inani liseshiwe lithi “Galván” (cell F6). Uhlu oluseshwayo luyikholomu yamagama (B6:B17), kuyilapho amalungu afanayo abuyiswayo kuyikholomu ehambisana nobude belinye ngalinye (D6:D17). Vele ucindezele inkinobho ethi "Enter" ukuze uveze umphumela esiwufunayo.
Ukwengeza amanani amabili okubuyisela
Sisebenzisa isibonelo esifanayo nasesimweni esedlule, ngalesi sikhathi kuphela, ngaphezu kobude bomdlali, sifuna ukwazi ukuthi iyiphi inombolo yejezi lakhe. Okufanele kwenziwe lapho-ke ukwandisa uhlu olubuyisiwe ukuze i-Excel ikwazi ukuthola yomibili imiphumela. Ifomula izobukeka kanje:
=BUSCARX(F6;B6:B17;C6:D17)
Ukwengeza ipharamitha "uma_it_is_not_found"
Manje ake sicabange ukuthi, esibonelweni esifanayo njengasezimweni ezimbili ezedlule, sifaka ekusesheni kwethu igama lomdlali ongaveli ohlwini. Kulapha lapho umbhalo wempendulo ungacaciswa ngepharamitha ethi "if_it_is_not_found". Umbhalo ophumayo okhethiwe uthi "Izicaciso ezingavumelekile", nakuba noma imuphi omunye umlayezo obonakala ufanelekile kithi nawo ungasebenza. I-syntax ehambisanayo ingaba yilokhu:
=XLOOKUP(F6,B6:B17,C6:D17, "Ukucaciswa okungavumelekile")
Lezi yizibonelo ezintathu nje ezilula nezisebenzayo zokuthi usetshenziswa kanjani umsebenzi we-XLOOKUP ku-Excel. Iqiniso liwukuthi lo msebenzi ungasetshenziswa kumadokhumenti amakhulu kakhulu futhi ayinkimbinkimbi, ngaleyo ndlela uthole umphumela oyifunayo ngokushesha.