Ukuba ukhe waziva uxinezelekile ukujonga idatha ethile kwiphepha le-Excel elinamakhulu okanye amawaka emigca, uya kuvuya ukwazi ukuba kukho umsebenzi oyilelwe ngokukodwa ukwenza loo msebenzi ube lula kwaye wonge ixesha: umsebenzi odumileyo. Excel VLOOKUPEsi sixhobo sifana neglasi yokukhulisa ngokuzenzekelayo ngaphakathi kwispredishithi zakho, ekuvumela ukuba ufumane ulwazi kwaye ubuyisele iziphumo ezihambelanayo kwimizuzwana nje.
Nangona abantu abaninzi basebenzisa i-Excel yonke imihla kwishishini labo, Ayinguye wonke umntu owazi ngokupheleleyo ukubanakho kweVLOOKUP.Kweli nqaku, siza kuchaza ngokucacileyo nangeenkcukacha ukuba isebenza njani, xa inokusindisa ubomi bakho, kwaye zeziphi ezinye iindlela ezinokuba luncedo ngokulinganayo okanye ngakumbi kwiimeko ezithile.
Yintoni i-VLOOKUP kwaye kutheni iluncedo kakhulu kwi-Excel?
Umsebenzi I-VLOOKUP isivumela ukuba sikhangele ixabiso elithile kuluhlu lokuqala lwetafile okanye uluhlu lwedatha kwaye sibuyisele ulwazi oluhambelanayo oluvela kumqolo omnye, kodwa kwenye ikholamu.Oku kuguqulela kwikhono lokukhawuleza, ngokuzenzekelayo, kwaye ngokuchanekileyo ufumane idatha exhunywe kwixabiso elifunekayo, ngaphandle kokuphonononga umqolo ngomqolo okanye ukwenza iimpazamo zomntu.
I-VLOOKUP i luncedo ngakumbi xa uneetafile ezinkulu kwi-Excel kwaye kufuneka ukhuphe ulwazi olunxulumene nesitshixo okanye isichongi. Eli nqaku lenza ukuba i-spreadsheets zibe namandla ngakumbi kwaye zisebenze ngeendlela ezininzi, zikuvumela ukuba uqhagamshele kwaye unxulumanise imiqulu emikhulu yedatha ebinokungalawuleki.
Ingaba umsebenzi weVLOOKUP wakhiwe njani? Iiparamitha kunye nesintaksi
Ngaphambi kokuba usebenzise VLOOKUP, Kubalulekile ukuqonda i-syntax yayoKwi-Excel, ifomula imelwe ngolu hlobo:
=I-VLOOKUP(ixabiso_lokukhangela; uluhlu_ lwetheyibhile; isalathisi_ sekholamu; )
Nganye kwezi parameters isebenza umsebenzi obalulekileyo. Masizichaze ngokweenkcukacha:
- khangela_xabiso: Yidatha ofuna ukuyifumana kuluhlu lokuqala loluhlu lwakho. Inokuba lixabiso ochwetheza ngokuthe ngqo phakathi kocatshulo, okanye ireferensi kwiseli equlathe ixabiso.
- uluhlu_ lwetafile: Ibhekisa kuluhlu lweeseli apho uphendlo luyakwenziwa. Kubalulekile ukuba umqolo wokuqala woluhlu uqulathe amaxabiso aza kukhangelwa.
- isalathisi_imihlathi: Ngenisa inani (ukuqala ku-1) kwikholamu ngaphakathi koluhlu ofuna ukukhupha kulo ixabiso elinxulumeneyo. Ukuba uluhlu lwakho luyi-A2:D10 kwaye ukhangela idata kuluhlu C (eya kuba ngumqolo wesithathu kuluhlu), ungangenisa inani lesi-3 apha.
- interval_search: Le yingxoxo ekhethwayo echaza ukuba ukhangelo kufuneka lube ngqo (FALSE) okanye uqikelelo (TRUE). Ukuba awuyikhankanyi, i-Excel ithatha ukuba ufuna umdlalo oqikelelweyo (TRUE).
I-syntax isenokubonakala intsonkothile ekuqaleni, kodwa wakube uyisebenzisa uya kubona ukuba ilula kunokuba ibonakala. Ukuba wenza impazamo kuyo nayiphi na ipharamitha, i-Excel iya kukulumkisa ngempazamo ukuze ukwazi ukulungisa ifomula..
Izinto eziluncedo kunye neMida ye-VLOOKUP: Iinkcukacha ezingundoqo
I-VLOOKUP iluncedo kakhulu, kodwa inemida ethile ekufuneka uyikhumbule. Eyona nto iphambili yileyo inokukhangela kuphela idatha ukusuka ekhohlo ukuya ekuneneOku kuthetha ukuba ixabiso olikhangelayo kufuneka lisoloko likuluhlu lokuqala loluhlu olukhethiweyo. Ukuba ufuna ukukhangela idatha kwelinye icala, uya kufuna ukufunda malunga neminye imisebenzi efana ne-INDEX kunye neMATCH.
Omnye umda obalulekileyo kukuba iVLOOKUP ibuyisela kuphela isiphumo sokuqala esisifumeneyoUkuba kukho amaxabiso aphindwe kabini kuluhlu lokukhangela, uya kufumana kuphela eyokuqala, ke kuya kufuneka ucoce itafile yakho okanye uqwalasele ezinye iifomyula zeziphumo ezinzima ngakumbi.
Kwakhona qaphela ukuba i Inani lekholam elikhankanyiweyo likhulu kunetotali yenani leekholamu kuluhlu, I-Excel iya kukubonisa impazamo #REF!. Kwakhona, ukuba usebenzisa eyona ndlela yomdlalo kunye nexabiso alikho, uya kubona i # N / A.
Iingcebiso zophendlo olukhawulezayo nolusebenzayo
Xa usebenza neebhodi ezinkulu, ulungelelwaniso olulungileyo lolona hlakani lwakho lulungileyo. Nazi ezinye amaqhinga eyenza umahluko:
- Sebenzisa uluhlu oluncinci: Nciphisa uluhlu lokukhangela kwinto eyimfuneko ngokungqongqo ukwenza ukhangelo lube lula ngakumbi.
- Guqula iitafile zakho zibe yi-Excel Tables: Oku kukuvumela ukuba usebenzise iireferensi ezicwangcisiweyo kwaye uphucule kakhulu ulawulo lwedatha kunye nokuhlaziya.
- Musa ukusebenzisa ngokugqithisileyo ungqamaniso oluchanekileyo ngaphandle kokuba kuyimfuneko: Uthelekiso oluqikelelweyo lukhawuleza kakhulu kwaye lwanele kwiimeko ezininzi.
Iimpazamo eziqhelekileyo kunye nendlela yokuzilungisa
Nabona basebenzisi banamava banokukhubeka kwiimpazamo ezincinci. Nazi ezinye zezona mpazamo zixhaphakileyo kunye nendlela yokuzikhusela ekonakaliseni usuku lwakho:
- #REF!: Inani lekholam ekhethiweyo ligqithise kuluhlu oluxeliweyo.
- #IFANELE!: Isalathisi sekholomu asisetwanga ngendlela engeyiyo ukuya ku-0.
- #N / A: Akukho ngqamaniso ngqo efunyenweyo kuluhlu olukhethiweyo usebenzisa "FALSE".
Ukuze ugweme ezi mpazamo, khangela isakhiwo setafile, qinisekisa ukuba idatha ikwindlela echanekileyo, kwaye jonga kabini ingxabano nganye yomsebenzi.
Iingcebiso eziphezulu kubasebenzisi abanamava
Ukuba sele umoshile VLOOKUP kwaye ufuna ukuthatha i-spreadsheets zakho ze-Excel ukuya kwinqanaba elilandelayo, qiniseka ukuba uzame ezi ngcebiso:
- Dibanisa i-VLOOKUP ne-IFERROR: Ngale ndlela unokubonisa umyalezo wesiko ukuba idatha oyikhangelayo ayikho, endaweni yempazamo embi.
- Dibanisa iVLOOKUP kunye neetafile zepivot: Ikuvumela ukuba uhlukanise idatha, uhlalutye umthamo omkhulu, kwaye ukhuphe ulwazi olufanelekileyo ngokujonga nje.
- Sebenzisa i-VLOOKUP kunye nezinye iifomyula: Umzekelo, idibanisa ne-INDEX kunye neMATCH, okanye nge-CHOOSE ukuba ufuna ukubuyisela umva ulandelelwano lwemihlathi kukhangelo lwakho.
Ezi zixhobo zenza VLOOKUP kugqame kungekuphela nje njengesixhobo sokukhangela, kodwa kwakhona njengomhlobo ophambili wolawulo lwedatha.
Emva kokubona yonke into, umsebenzi weVLOOKUP Ityhilwa njengentsika esisiseko kwabo basebenza ne-Excel. Ukwazi indlela yokuyisebenzisa kakuhle kuya konga ixesha, kunciphise iimpazamo nokusebenza ngokufanelekileyo..
