01 ka-03
Thola izaphulelo zezintengo nge-VLOOKUP
Indlela umsebenzi we-VLOOKUP osebenza ngayo
Umsebenzi we-Google Spreadsheets 'VLOOKUP, omelela ukubonakala ngokuqondile, ungasetshenziswa ukubuka ulwazi oluthile olusetafuleni lwedata noma database.
- Imininingwane emilayezo yephutha ye-VLOOKUP ingatholakala ekhasini 3
I-VLOOKUP ivame ukubuyisela insimu eyodwa yedatha njengokukhipha kwayo. Kwenza kanjani lokhu:
- Uhlinzeka igama noma ukusesha_kuthi okutshela i-VLOOKUP emgqeni noma irekhodi letafula lemininingwane ukuze ubheke idatha oyifunayo
- Wena unikeza inombolo yikholomu - eyaziwa njengenkomba - yedatha oyifunayo
- Umsebenzi ubheka ukusesha_kusesho kukholomu yokuqala kwedatha yedatha
- U-VLOOKUP uthola futhi abuyisele ulwazi olufunayo kwenye insimu yerekhodi elifanayo usebenzisa inamba yenkomba ehlinzekiwe
Ukuthola Ukulingana Okulinganiselwe ne-VLOOKUP
Ngokuvamile, i-VLOOKUP izama ukuthola umdlalo oqondile we- search_key obonisiwe . Uma umdlalo oqondile ungatholakali, u-VLOOKUP angathola umdlalo olinganayo.
- Umdlalo olinganiselwe ngumdlalo oseduze we- search_key usayizi obuncane noma obuncane kakhulu.
- Ukufaniswa okulinganiselwe kutholakala ngokubeka i- argument ehambisanayo ne-TRUE.
Ukuhlunga idatha kuqala
Nakuba kungadingeki njalo, kungcono kakhulu ukuhlela kuqala uhla lwemininingwane i- VLOOKUP iseshayo ekhuphukayo usebenzisa ikholomu yokuqala yebanga lokukhiya uhlobo.
Uma idatha ingahle ihlelwe, i-VLOOKUP ingabuyisa umphumela ongafanele.
Isibonelo somsebenzi we-VLOOKUP
Isibonelo esithombeni ngenhla sisebenzisa ifomula elandelayo equkethe umsebenzi we-VLOOKUP ukuthola isaphulelo semali yezimpahla ezithengiwe.
= VLOOKUP (A2, A5: B8,2, TRUE)
Ngisho noma ifomula elingenhla lingafakwa nje kwisitoreji sekhasi lokusebenzela, enye inketho, njengoba isetshenziswe ngezinyathelo ezibalulwe ngezansi, ukusebenzisa i-Google Spreadsheets ibhokisi eliphakamisa ngokuzenzakalelayo ukufaka ifomula.
Ukufaka umsebenzi we-VLOOKUP
Izinyathelo zokungena emsebenzini we-VLOOKUP oboniswe esithombeni ngaphezulu ngenqamuzana B2 yizi:
- Chofoza kuseli B2 ukuze wenze iseli elisebenzayo - yilapho imiphumela ye-VLOOKUP izoboniswa khona
- Thayipha uphawu olulinganayo (=) olulandelwe igama le- vlookup yomsebenzi
- Njengoba uthayipha, ibhokisi eliphakamisa ngokuzenzakalelayo livela ngamagama kanye ne-syntax yemisebenzi eqala nge-V V
- Uma igama elithi VLOOKUP livela ebhokisini, chofoza igama nge-pointer yegundane ukufaka igama lomsebenzi bese uvula ibakaki nxazonke esitokisini B2
Ukufaka iziphakamiso zomsebenzi
- Ukwaziswa okuningiliziwe kwi- syntax kaVLOOKUP nokuphikisana kungatholakala ekhasini 2
- Njengase-Excel, i-comma ifakwa phakathi kwezingqinamba zomsebenzi wokusebenza njengesahlukanisi.
Iziphakamiso ze-VLOOKUP umsebenzi zifakwe emva kokufaka okuvulekile nxazonke esitokisini B2.
- Chofoza ku-cell A2 kusikripthi sokusebenza ukuze ufake leli referensi yeseli njengengxabano yokusesha_key
- Ngemuva kokubhekisela kweseli, thayipha i-comma ( , ) ukuze wenze njengesahlukanisi phakathi kwezimpikiswano
- Gqamisa amaseli A5 kuya ku-B8 kushidi lokufaka ukufaka lezi zikhombo zeseli njengengxabano yobubanzi - izihloko zetafula azifakiwe ebangeni
- Ngemuva kokubhekisela kweseli, thayipha enye i-comma
- Thayipha i- 2 emva komhlahlandlela ukungena impikiswano yenkomba kusukela amazinga esaphulelo atholakala kukholomu 2 yezingxabano zamabanga
- Ngemuva kwenombolo 2, thayipha enye i-comma
- Qinisekisa amaseli B3 no-B4 kushidi lokusebenzela ukuze ufake lezi zikhombo zeseli njengengxabano yeholidi
- Thayipha igama Liyiqiniso ngemva kokuhlaziya njenge-i-argument ehambisanayo
- Cindezela ukhiye wokungena kwikhibhodi ukungena ibhakha lokuvala " ) " emva kokuphikisana kokugcina komsebenzi futhi ukuqedela umsebenzi
- Impendulo engu-2.5% - izinga lokunciphisa inani elithengiwe - kufanele livele esitokisini B2 kwiphepha lokusebenzela
- Uma uchofoza ku-cell B2, umsebenzi ophelele = VLOOKUP (A2, A4: B8, 2, True) ibonakala kwibha yefomula ngaphezulu kwephepha lokusebenzela
Kungani i-VLOOKUP ibuyiselwe i-2.5% njengesiphumo
- Kulesi sibonelo, ikholomu ye- Quantity ayiqukethe umdlalo oqondile we- search_key value ye-23.
- Njengoba i-argument ekhonjisiwe isethwe ku-TRUE, i-VLOOKUP izothola umlinganiso olinganiselwe nenani lokusesha_lokucinga .
- Inani eliseduze ngosayizi eliseyingcosana kunenani lokusesha_khiye le-23 lingu-21.
- Ngakho-ke, i-VLOOKUP ifuna amaphesenti ayisaphulelo emgqeni onamakhasi angu-21, futhi ngenxa yalokho, ubuyisa isaphulelo se-2.5%.
02 ka-03
I-Google Spreadsheets i-Syntax ye-Function ne-Arguments
I-Syntax ye-Function ne-Arguments ye-VLOOKUP
I- syntax yomsebenzi ibhekisela kokuhlelwa komsebenzi futhi ihlanganisa igama lomsebenzi, amabakaki, nokuphikisana .
I-syntax yomsebenzi we-VLOOKUP yilezi:
= VLOOKUP (ukusesha_kuseshi, ububanzi, inkomba, i-ehlelwe)
search_key - (edingekayo) inani lokusesha - njengenani elithengiswa esithombeni ngenhla
ububanzi - (edingekayo) inani lamakholomu nemigqa i-VLOOKUP okufanele ifune
- ikholomu yokuqala ebangeni ngokuvamile iqukethe ukusesha_kusesho
inkomba - (edingekayo) inombolo yekholomu yenani elifunayo elifunayo
- ukubalwa kwezinombolo kuqala ngekholomu_sesho_khiye njengekholomu 1
- uma inkomba isethelwe kwinombolo enkulu kunezinombolo zamakholomu akhethiwe ku-argument yebanga #REF! iphutha libuyiselwa umsebenzi
i-ekhonjiswayo - (okukhethwa kukho) ibonisa ukuthi ngabe uhla luhlelwe yini noma lukhuphuka ngokuhleleka usebenzisa ikholomu yokuqala yebanga lokhi lokuhlunga
- inani le-Boolean - TRUE noma FALSE yizona zindinganiso ezamukelekayo kuphela
- uma ihlelwe ku-TRUE noma ilahlekile futhi ikholomu yokuqala ebangeni ayihlehlwanga ngokunyuka, umphumela ongafanele ungase uvele
- uma ishiyiwe, inani lihlelwe ku-TRUE ngokuzenzakalelayo
- uma ihlelwe ku-TRUE noma ishiyiwe futhi ihambisana ngokuqondile ne-search_key ayitholakali, umeshi oseduzane osayizi noma usayizi osetshenziselwa ukusetshenziselwa njenge-search_key.
- uma isethwe ku-FALSE, i-VLOOKUP yamukela kuphela umdlalo oqondile we-search_key. Uma kunezindinganiso eziningi ezihambelana, inani lokulinganisa lokuqala libuyiselwa
- uma isethwe ku-FALSE, futhi akukho nhlobo ehambelanayo ye-search_key ithola iphutha le- # N / A libuyiselwa umsebenzi
03 ka 03
Imilayezo Yephutha le-VLOOKUP
Imilayezo Yephutha le-VLOOKUP
Imilayezo yephutha elandelayo ihlotshaniswa ne-VLOOKUP.
I-# N / A ("inani alitholakali") liyaboniswa uma:
- I- search_key ayitholakali kukholomu yokuqala yezingxabano zebanga
- Ingxabano yebanga ayilungile. Isibonelo, ingxabano ingafaka amakholomu angenalutho ngakwesokunxele ebangeni
- I-i-argument ekhonjisiwe isethelwe ku-FALSE futhi umdlalo oqondile wempikiswano yokusesha_key ingatholakali kukholomu yokuqala ebangeni
- I-i-argument ehambisanayo isethelwe ku-TRUE futhi zonke izindinganiso kukholomu yokuqala ebangeni zikhulu kune- search_key
I-#REF! ("ukubhekisela ebangeni") iphutha iboniswa uma:
- Ingxabano yenkomba ingaphezulu kwenani lamakholomu ku-argument yobubanzi