01 ka 03
Fumana amanani afanelekileyo kwiDatha nge-Excel yeVLOOKUP
Indlela umsebenzi weVLOOKUP usebenza ngayo
Umsebenzi we-VLOOKUP we-Excel, omelela ukujonga ngokubonakalayo , unokusetyenziswa ukujonga ulwazi oluthile olusetafile yedatha okanye isiseko sedatha.
I-VLOOKUP iqhele ngokutsha insimu enye yedatha njengento yayo. Indlela okwenza ngayo oku:
- Unika igama okanye i- lookup_value exelela i-VLOOKUP apho umgca okanye irekodi yedatha yedata ukujonga idatha efunwayo.
- Unika inombolo yekholomu - eyaziwayo njenge- col_index_num - yedatha oyifunayo
- Umsebenzi ujonga i- lookup_value kwikholamu yokuqala yedatha yedatha
- I-VLOOKUP ibeka kwakhona kwaye ibuyisele ulwazi olufunayo kwenye insimu yerekhodi efanayo usebenzisa inombolo yekholomu enikeziwe
Ukuhlelwa kweDatha kuqala
Nangona kungasoloko kuyimfuneko, kudla ngokuqhelekileyo ukuba uqale uhlalutye uluhlu lweenkcukacha ezenziwa nguVLOOKUP ekwenyuka umyalelo usebenzisa ikholam yokuqala yoluhlu lwesicatshulwa.
Ukuba idatha ayilungiswanga, iVLOOKUP inokubuyisela isiphumo esingalunganga.
I-Syntax kunye neArguments ye-VLOOKUP
I- syntax yomsebenzi ibhekisela kwisakhiwo somsebenzi kwaye iquka igama lomsebenzi, amabakethi kunye neengxabano .
I-syntax yomsebenzi weVLOOKUP yile:
= VLOOKUP (lookup_value, itafile_array, col_index_num, i-range_lookup)
ukukhangela _value - (efunekayo) ixabiso lokukhangela - njengexabiso elithengiswa kumfanekiso ongentla
itafile_array - (efunekayo) le yitheyibhile yedatha efunwa nguVLOOKUP ukufumana ulwazi olulandelayo.
- Itafile_array kufuneka iqule ubuncinane iikholomu ezimbini zeedatha
- Ikholamu yokuqala iqulethe i-lookup_value
col_index_num - (efunekayo) inombolo yekholomu yexabiso oyifunayo.
- Ukubalwa kwenani kuqala ngekholomu ye - search_key njengekholamu 1
- Ukuba i- col_index_num isetyenziswe kwinani elingaphaya kuneenombolo zamakholomu akhethwe etafileni_engxabano ye- # REF! Iphutha libuyiselwa ngumsebenzi
uluhlu lwe_lookup - (olukhethiweyo) lubonisa ukuba ngaba uluhlu aluhlulwayo kwinqanaba.
- Idatha kwikholamu yokuqala isetyenziswa njengendlela yokhiye
- Ixabiso leBoolean - IQINISO okanye i-FALSE yimiqobo kuphela eyamkelekileyo
- Ukuba akhiyiweyo, ixabiso lihlelwe kwi-TRUE ngokuzenzakalelayo
- Ukuba isetyenziswe kwi-TRUE okanye ingashiywanga kwaye ikholomu yokuqala yeloluhlu aluhlanjulwanga kwenyuka umyalelo, isiphumo esingalunganga senzeke
- Ukuba isetyenziswe kwi-TRUE okanye ingashiywanga kwaye ifanelana ngqo ne- lookup _value ayifumanekanga, umdlalo osondeleyo oncinci kwisayizi okanye xabiso isetyenziswe njenge- search_key
- Ukuba isethelwe kwi-FALSE, i-VLOOKUP iyakwamukela kuphela umdlalo ochanekileyo we- lookup _value . Ukuba kukho amaxabiso amaninzi afanayo, ixabiso lokuqala elihambelana nalo libuyiselwa
- Ukuba isethelwe kwi-FALSE kwaye akukho xabiso ehambelana ne- search_key ifumaneka, i-# N / A iphutha libuyiselwa ngumsebenzi
Umzekelo: Fumana ixabiso lokuThengiswa kweeMpahla ezithengiweyo
Umzekelo kumfanekiso ongentla usebenzisa umsebenzi weVLOOKUP ukufumana izinga lokunciphisa elihluka kuye kuxhomekeke kubukhulu bezinto ezithengiweyo.
Umzekelo ubonisa ukuba isaphulelo sokuthenga izinto ezi-19 ngu-2%. Oku kungenxa yokuba ikholomu yobuninzi iqulethe uluhlu lwexabiso. Ngenxa yoko, iVLOOKUP ayinakufumana umdlalo ochanekileyo. Kunoko, umdlalo olinganayo kufuneka ufumaneke ukuze ubuyisele izinga elichanekileyo lokunciphisa.
Ukufumana imilinganiselo esondeleyo:
- ukuhlela idatha kwitheyibhile_kuye kunyuka umyalelo;
- misela i- array_lookup ingxabano kwi-TRUE
Ngokomzekelo, le fomula elandelayo equkethe umsebenzi weVLOOKUP isetyenziswe ukufumana isaphulelo semali yezinto ezithengwe.
= VLOOKUP (C2, $ C $ 5: i $ D $ 8,2, INYANISO)
Nangona le fomyula ingafakwa nje kwifayile yeshidi, enye inketho, njengoko isetyenziswe ngamanyathelo alulwe apha ngezantsi, kukusebenzisa ibhokisi yencoko yencoko yomsebenzi ukufaka iingxabano zayo.
- Ukusebenzisa ibhokisi yencoko ibinceda kube lula ukufaka iingxoxo zomsebenzi ngokuchanekileyo.
Ukuvula i-VLOOKUP Dialog Box
Amanyathelo asetyenziswa ukufaka umsebenzi weVLOOKUP aboniswe kumfanekiso ongentla kwiseli B2 yile:
- Cofa kwifowuni B2 ukwenzela ukuba yenze iseli esebenzayo - indawo apho iziphumo ze-VLOOKUP ziboniswa khona
- Cofa kwiThebhu yefomula.
- Khetha iLookup & I-Reference kwi- Ribbon ukuvula uluhlu lwehla lokuhla
- Cofa kwiVLOOKUP kuloluhlu ukukhupha ibhokisi yencoko yencoko yomsebenzi
02 ka 03
Ukufaka ii-Arguments ze-VLOOKUP ze-Excel
Ukubhekisela kwiiNgxelo zeeSeli
Iingxoxo zomsebenzi weVLOOKUP zifakwe kwimida eyahlukileyo kwibhokisi yencoko njengoko kuboniswe kumfanekiso ongentla.
Iingxelo zeseli ezisetyenziswe njengeengxabano zingasetyenzwa kumgca ochanekileyo, okanye, njengoko zenziwe kumanyathelo angezantsi, ukukhomba, okubandakanya ukugqamisa uluhlu olufunwayo lweeseli kunye nekhompi ye mouse, lingasetyenziselwa ukungena kwibhokisi yencoko yababini. .
Iinzuzo zokusebenzisa ukukhomba ziquka:
- Kukhawuleza kunokuthayipha;
- Iiphosiso ezimbalwa zenziwe zingeniso ezichanekileyo zesalathisi.
Ukusebenzisa iiReferensi zeeNxeba eziHlangeneyo neziNgcaciso kunye neeArguments
Akuqhelekanga ukusebenzisa iikopi ezininzi zeVLOOKUP ukubuyisela ulwazi oluhlukileyo kwitafile enye yedatha. Ukwenza kube lula ukwenza oku, rhoqo iVLOOKUP ingakopishwa kwisinye iseli enye. Xa imisebenzi ikopishwa kwezinye iiseli, kufuneka kuthathelwe ingqalelo ukuze kuqinisekiswe ukuba izikhokelo zeseli eziphumo zichanekile zinikezelwa kwindawo entsha yomsebenzi.
Kulo mfanekiso ngentla, iimpawu zeedola ( iirekhodi ) ezungeze iireferensi zeeseli zetafile_engxabano ezibonisa ukuba ziiinkcukacha ezipheleleyo zeeseli , oku kuthetha ukuba abayi kutshintsha ukuba umsebenzi ukopishwa kwenye iseli. Oku kuyinqweneleka njengekopi ezininzi ze-VLOOKUP bonke baya kubhekisela kwitafile efanayo yedata njengomthombo wolwazi.
Iselferensi yeselula esisetyenziselwa ukufumana i- lookup_value, ngakolunye uhlangothi, ayizungezwe ngeempawu zeedola, ezenza ukuba isalathisi eseluncedo . Iingqinisiso zeeseli ezinxulumene zitshintsha xa zikopishwa ukubonisa indawo yazo entsha ngokumalunga nesimo seenkcukacha ababhekise kuyo.
Ukungena kwiSigqibo seMisebenzi
- Cofa kwi-Lookup _value line kwibhokisi yebhokisi yeVLOOKUP
- Cofa kwiseli C2 kwiphepha lokusebenzela ukufaka le ferensi yeselenki njengengxabano yokusesha_kukhangela
- Cofa kwiTable_array umgca webhokisi yencoko
- Phakamisa iiseli C5 ukuya kwi-D8 kwiphepha lokusebenzela ukuze ufake olu luhlu njenge- Table_array argument - izihloko zetafile azifakiwe
- Cinezela iqhosha le- F4 kwikhibhodi ukutshintsha uluhlu kwiinkcukacha ezikhoyo zeeseli
- Cofa kwi Col_index_num umgca webhokisi yencoko
- Thayipha i- 2 kulo mgca njengengxabano yeCol_index_num , kuba ixabiso lokunciphisa lifumaneka kwikholam yesi-2 se- Table_array argument
- Cofa kumgca weRange_lookup kwibhokisi yencoko
- Thayipha igama eliyinyaniso njengengxoxo yeRange_lookup
- Cinezela ukhiye wokungena kwibhodibhodi ukuvala ibhokisi yencoko kwaye ubuyele kwiphepha lokusebenzela
- Impendulo ye-2% (izinga lokunciphisa inani elithengiweyo) kufuneka livele kwiseli D2 kwiphepha lokusebenzela
- Xa ucofa kwi-cell D2, umsebenzi opheleleyo = VLOOKUP (C2, $ C $ 5: $ D $ 8,2, EYENYANISO) ibonakala kwibha yefomula ngaphezulu kwiphepha lokusebenzela
Kutheni iVLOOKUP ibuyise i-2% njengeSiphumo
- Kulo mzekelo, ikholomu ye- Quantity ayinayo umdlalo ochanekileyo wexabiso lokusesha_key of 19.
- Ekubeni i-argument engqinelanayo isetyenziswe kuQINISO, i-VLOOKUP iya kufumana umlinganiselo olingana nexabiso lokusesha_lokhu .
- Ixabiso elikufuphi kwisayizi esincinci kunexabiso lokusesha_i -19 le-11.
- I-VLOOKUP, ngoko, ikhangele ipesenti yokuphuhlisa kumqolo oqulethe i-11, kwaye, ngenxa yoko, ibuyisela izinga lokunciphisa i-2%.
03 ka 03
I-Excel VLOOKUP Ingasebenzi: # I-N / A kunye ne-#REF Iimpazamo
Imiyalezo yeZiphutha zeVLOOKUP
Imilayezo elandelayo impazamo idibene neVLOOKUP.
I-# N / A ("ixabiso alifumaneki") Iphutha liboniswa Ukuba:
- I- lookup _value ayitholakali kwikholamu yokuqala yeengxabano zamanqanaba
- I- Table_array ingxabano ayilungile. Ngokomzekelo, ingxabano ingafaka iikholomu ezingenanto kwinqanaba lekhohlo loluhlu
- Ingxabano ye- Range_lookup isethelwe kwi-FALSE kunye nomdlalo ochanekileyo kwingxabano yokusesha_kukhethi ayifumaneki kwikholamu yokuqala yohlu
- I-argument ye- Range_lookup isethelwe kwi-TRUE kunye nazo zonke ixabiso kwikholam yokuqala yeloluhlu zikhulu kunokwenza_kukhangela
#REF! ("khankanya kwibala") Iphutha liboniswa Ukuba:
- I- Col_index_num argument mkhulu kuneenombolo zamakholomu kwi- Table_array