Fumana idatha kwi-Google Spreadsheets ngeVLOOKUP

01 ka 03

Fumana izaphulelo zexabiso ngeVLOOKUP

Indlela umsebenzi weVLOOKUP usebenza ngayo

I-Google Spreadsheets 'I-VLOOKUP umsebenzi , emele ukujonga ngokubonakalayo , ingasetyenziselwa ukujonga ulwazi oluthile olusetafile yedata okanye isiseko sedatha.

• Ulwazi malunga nemiyalezo yesiphoso seVLOOKUP lunokufumaneka kwiphepha 3

I-VLOOKUP iqhele ngokutsha insimu enye yedatha njengento yayo. Indlela okwenza ngayo oku:

1. Unika igama okanye i- search_key echazela i-VLOOKUP apho umgca okanye irekhodi yetafile yedata ukujonga idatha efunwayo
2. Unika inombolo yekholomu - eyaziwa njenge- index -yedatha oyifunayo
3. Umsebenzi ujonge ukukhangela_kukhangela kwikholamu yokuqala yedatha yedatha
4. I-VLOOKUP ibeka kwakhona kwaye ibuyisele ulwazi olufunayo kwenye insimu yerekhodi efanayo usebenzisa inombolo yenkcazo enikezelweyo

Ukufumana iMilinganiselo ehambelana neVLOOKUP

Ngokuqhelekileyo, iVLOOKUP izama ukufumana umgca ochanekileyo we- search_key ebonisiweyo . Ukuba umdlalo ofanelekileyo awufumaneki, iVLOOKUP ingafumana umdlalo olinganayo.

• Umdlalo osondeleyo ngumdlalo osondeleyo kwi- search_key ngobukhulu obuncinci okanye ubuncinane kwixabiso.
• Imilinganiselo ehambelana nayo ifumaneka ngokusekwa komsebenzi we-i- argument argument ye- TRUE.

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.

Umsebenzi weVLOOKUP Umzekelo

Umzekelo kumfanekiso ongentla usebenzise le fomula elandelayo equkethe umsebenzi weVLOOKUP ukufumana isaphulelo semali yeempahla ezithengiweyo.

= I-VLOOKUP (A2, A5: B8,2, YEQINISO)

Nangona ifom elapha ngentla ingafakwa nje kwifayile yeshidi, enye inketho, njengoko isetyenziswe ngamanyathelo alulwe apha ngezantsi, kukusebenzisa i-Google Spreadsheets ibonisa ibhokisi ukufaka ifom.

Ukufaka umsebenzi weVLOOKUP

Amanyathelo okungena kumsebenzi weVLOOKUP aboniswe kumfanekiso ongentla kwiseli B2 yile:

1. Cofa kwifowuni B2 ukwenza i- cell esebenzayo- yilapho iziphumo ze-VLOOKUP ziza kuboniswa
2. Thayipha uphawu olulinganayo (=) olulandelwe igama lomsebenzi vlookup
3. Njengoko uthayipha, ibhokisi ye -auto-suggestion ibonakala ngamagama kunye ne-syntax yemisebenzi eqala ngeleta V
4. Xa igama VLOOKUP livela kwibhokisi, nqakraza egameni kunye nekhompi ye mouse ukuze ufake igama lomsebenzi kwaye uvule ibhoksi ejikelezayo kwi-cell B2

Ukungena kwiSigqibo seMisebenzi

• Ulwazi olunzulu malunga ne- syntax yeVLOOKUP kunye neengxabano zifumaneka kwiphepha 2
• Njengoko ku-Excel, i-comma ifakwa phakathi kweengxoxo zomsebenzi ukwenza njengomhluleli.

Iingxoxo ze-VLOOKUP zifakwe emva kwebhodi evulekileyo kwi-cell B2.

1. Cofa kwiseli A2 kwiphepha lokusebenzela ukufaka le ferensi yeselenki njengengxabano yokusesha_kukhangela
2. Emva kwereferensi yeseli, thayipha i-comma ( , ) ukuba usebenze njengomhluli phakathi kweengxoxo
3. Gqamisa iiseli ezi-A5 ukuya kwi-B8 kwiphepha lokusebenzela ukuze ufake ezi ngqamaniso zeeseli njengeengxabano zoluhlu - izihloko zetafile azifakiwe kwibala
4. Emva kwereferensi yeseli, thayipha enye i-comma
5. Thayipha i- 2 emva kokuba i-comma ingenise ingxabano yenkcazo kuba ixabiso lokunciphisa lifumaneka kwikholam yesi-2 yeengxabano zamanqanaba
6. Emva kwenombolo 2, thayipha enye i-comma
7. Khanyisa iiseli B3 kunye ne-B4 kwiphepha lokusebenzela ukuze ufake ezi ngqamaniso zeeseli njengeengxoxo zeeholide
8. Thayipha igama eliyinyaniso emva kokuba i-comma ibe yi-argument engqiniswanga
9. Cindezela ukhiye wokungena kwibhodibhodi ukufaka ibhondi yokuvala ejikelezayo " ) " emva kwengxabano yokugqibela yomsebenzi nokugqiba umsebenzi
10. Impendulo 2.5% - izinga lokunciphisa inani elithengiweyo - kufuneka livele kwiseli B2 kwiphepha lokusebenzela
11. Xa ucofa kwiseli B2, umsebenzi opheleleyo = VLOOKUP (A2, A4: B8, 2, Eyinyaniso) ibonakala kwifom yefomula ngaphezu kwephepha lokusebenzela

Kutheni iVLOOKUP ibuyise i-2.5% njengesiphumo

• Kulo mzekelo, ikholomu ye- Quantity ayinalo umdlalo ochanekileyo wexabiso lokusesha_key of 23.
• Ekubeni i-argument engqinelanayo isetyenziswe kuQINISO, i-VLOOKUP iya kufumana umlinganiselo olingana nexabiso lokusesha_lokhu .
• Ixabiso elisondeleyo ngobukhulu elincinci kunenani le- search_key ye-23 ngu-21.
• I-VLOOKUP, ngoko, ikhangele ipesenti yokuphuhlisa kwimiqolo equle i-21, kwaye, ngenxa yoko, ibuyisela isaphulelo se-2.5%.

02 ka 03

I-Google Spreadsheets I-Syntax ne-Arguments ye-VLOOKUP

I-Syntax kunye neArguments ye-VLOOKUP

I- syntax yomsebenzi ibhekisela kwisakhiwo somsebenzi kwaye iquka igama lomsebenzi, amabakethi kunye neengxabano .

I-syntax yomsebenzi weVLOOKUP yile:

= I-VLOOKUP (uphando_kukhangela, uluhlu, isalathisi, lu_lusetyenziswe)

ukucinga_kukhethi - (efunekayo) ixabiso lokukhangela - njengexabiso elithengiswa kumfanekiso ongentla

uluhlu - (olufunekayo) inani lamakholomu kunye nemigca ekufuneka iVLOOKUP iphenye
- ikholomu yokuqala kwibanga ngokuqhelekileyo iqulethe ukukhangela_kukhangela

inkcazo - (efunekayo) inombolo yekholomu yexabiso oyifunayo
- inani lokuqala liqala ngekholamu yocingo_kukhangela njengekholomu 1
- ukuba isalathisi sisetyenziswe kwinani elikhulu kunani lamakholomu akhethwe kwingxabano ye-#REF! Iphutha libuyiselwa ngumsebenzi

i_iyo-efanelekileyo-(ekhethiweyo) ibonisa ukuba okanye akukho uluhlu luhlelwe ngokunyuka komyalelo ngokusebenzisa ikholam yokuqala yohlu lwelolu hlobo
- ixabiso leBoolean - IQINISO okanye i-FALSE yimiqobo kuphela eyamkelekileyo
- ukuba isetyenziswe kwi-TRUE okanye ingashiywanga kwaye ikholomu yokuqala yeloluhlu aluhlanjulwanga kunyuka umyalelo, isiphumo esingalunganga senzeke
- ukuba uyashiywe, ixabiso lihlelwe kwi-TRUE ngokuzenzakalelayo
- ukuba isetyenziswe kwi-TRUE okanye ingashiywanga kwaye ifanelana ngqo ne-search_key ayifumanekanga, umdlalo osondeleyo osincinane kwisayizi okanye ixabiso lusetyenziselwa njenge-search_key.
- ukuba isethelwe kwi-FALSE, i-VLOOKUP iyakwamukela kuphela umdlalo ochanekileyo we-search_key. Ukuba kukho amaxabiso amaninzi afanayo, ixabiso lokuqala elihambelana nalo libuyiselwa
- ukuba isethelwe kwi-FALSE, kwaye akukho xabiso ehambelanayo le-search_key lifunyenwe i-# N / A iphutha libuyiselwa ngumsebenzi

03 ka 03

Imiyalezo yeZiphutha zeVLOOKUP

Imiyalezo yeZiphutha zeVLOOKUP

Imilayezo elandelayo impazamo idibene neVLOOKUP.

I-# N / A ("ixabiso alifumaneki") iphoso iboniswa ukuba:

• Ukhange_kukhangela alufumaneki kwikholamu yokuqala yeengxabano zamanqanaba
• Ingxabano yamanqanaba ayilunganga. Ngokomzekelo, ingxabano ingafaka iikholomu ezingenanto kwinqanaba lekhohlo loluhlu
• I-i-argument engqiniswanga isethelwe kwi-FALSE kwaye umdlalo ochanekileyo kwi-argument ye- search_key ayifumaneki kwikholamu yokuqala yohlu
• I-i- argument argument isethelwe kwi-TRUE kunye nazo zonke ixabiso kwikholam yokuqala yeloluhlu zikhulu kunokwenza_kukhangela

#REF! ("khankanya kwibala") ibonakaliswe ukuba:

• Inkcazo yenkcazo inkulu kunani leemakholomu kwingxabano yoluhlu