01 ka-03
Thola Ukufaniswa Okulinganiselwe kuDatha nge-VLOOKUP ye-Excel
Indlela umsebenzi we-VLOOKUP osebenza ngayo
Umsebenzi we-VLOOKUP we-Excel, okumele uhlolwe ngokuqondile , ungasetshenziswa ukubuka ulwazi oluthile olusetafuleni lwedata noma database.
I-VLOOKUP ivame ukubuyisela insimu eyodwa yedatha njengokukhipha kwayo. Kwenza kanjani lokhu:
- Uhlinzeka igama noma i- lookup_value etshela i-VLOOKUP emgqeni noma irekhodi letafula ledatha ukuze libheke idatha efunayo
- Uhlinzeka inombolo yekholomu - eyaziwa ngokuthi col_index_num - yedatha oyifunayo
- Umsebenzi uhlola i - lookup_value kukholomu yokuqala yedatha yedatha
- U-VLOOKUP uthola futhi abuyisele ulwazi olufunayo kusuka kwenye insimu yerekhodi elifanayo usebenzisa inombolo yenkomba ehlinzekwayo
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.
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 (lookup_value, table_array, col_index_num, range_lookup)
ukufundwa _value - (edingekayo) inani lokusesha - njengenani elithengiswa esithombeni ngenhla
ithebula_array - (edingekayo) lena yithebula yedatha ukuthi iVLOOKUP ifuna ukuthola ulwazi olulandelayo.
- Ithebula_i-table kufanele ibe namakholomu amabili wedatha
- Ikholomu yokuqala ngokuvamile iqukethe i-lookup_value
col_index_num - (edingekayo) inombolo yekholomu yenani elifunayo elifunayo .
- Ukubalwa kwenombolo kuqala ngekholomu_kusesho_kusesha njengekholomu 1
- Uma i- col_index_num isethelwe kwinombolo enkulu kunani lamakholomu akhethiwe etafuleni_ngxabano yama-#REF! iphutha libuyiselwa umsebenzi
i-range_lookup - (ngokuzikhethela) ibonisa ukuthi ngabe uhla luhlelwa kanjani noma luhlehlisiwe.
- Idatha ekholomu yokuqala isetshenziselwa ukhiye wohlobo
- Inani le-Boolean - TRUE noma FALSE yizona zindinganiso ezamukelekayo kuphela
- Uma kukhishiwe, inani lihlelwe ku-TRUE ngokuzenzakalelayo
- Uma ihlelwe ku-TRUE noma ishiyiwe futhi ikholomu yokuqala ebangeni ayihlehlwanga ukunyuka, umphumela ongafanele ungase uvele
- Uma kusetshenziswe ku-TRUE noma ukushiywe futhi kufanelana ngokuqondile ne- lookup _value ayitholakali, umdlalo oseduzane osayizi noma ubukhulu obusetshenzisiwe usetshenziswa njengekhiye lokusesha
- Uma isethwe ku-FALSE, i-VLOOKUP yamukela kuphela umdlalo oqondile we- lookup _value . Uma kunezindinganiso eziningi ezihambelana, inani lokulinganisa lokuqala libuyiselwa
- Uma isethwe ku-FALSE futhi akukho nhlobo efanako ye- search_key itholakele, iphutha le- # N / A libuyiselwa umsebenzi
Isibonelo: Thola Isaphulelo Sokuthengiswa Kwama-Purchased Quantity
Isibonelo esithombeni esingenhla sisebenzisa umsebenzi we-VLOOKUP ukuthola isilinganiso sesaphulelo esivumelana kuye ngokulingana kwezinto ezithengiwe.
Isibonelo sibonisa ukuthi isaphulelo sokuthenga izinto ezingu-19 ngu-2%. Lokhu kungenxa yokuthi ikholomu ye- Quantity iqukethe ububanzi bamanani. Ngenxa yalokho, i-VLOOKUP ayikwazi ukuthola umdlalo oqondile. Esikhundleni salokho, umdlalo olinganiselwe kufanele utholakale ukuze ubuyekeze isilinganiso esifanele sokusaphulelo.
Ukuze uthole okufanayo okufanayo:
- hlola idatha etafuleni_ukuhle ukwenyuka;
- setha i-argument ye- range_lookup ku-TRUE
Esikhathini isibonelo, ifomula elandelayo equkethe umsebenzi we-VLOOKUP isetshenziselwa ukuthola isaphulelo semali yezimpahla ezithengiwe.
= VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE)
Ngisho noma le fomula ingafakwa nje kwisitoreji sekhasi lokusebenzela, enye inketho, njengoba isetshenziswe ngezinyathelo ezibalulwe ngezansi, ukusebenzisa ibhokisi lengxoxo lomsebenzi ukufaka izingxabano zalo.
- Ukusebenzisa ibhokisi lengxoxo ngokuvamile kwenza kube lula ukufaka izingxabano zomsebenzi ngendlela efanele.
Vula i-VLOOKUP Dialog Box
Izinyathelo ezisetshenziselwa ukungena umsebenzi we-VLOOKUP oboniswe esithombeni ngaphezulu ngenqamuzana B2 yizi:
- Chofoza kuseli B2 ukwenza kube iseli elisebenzayo - indawo lapho imiphumela ye-VLOOKUP iboniswa khona
- Chofoza kuthebhu yefomula .
- Khetha Ukulanda & Izikhombisi ezivela kubhebhoni ukuvula uhlu lwehla lokusebenza
- Chofoza ku- VLOOKUP ohlwini ukuze ulethe ibhokisi lengxoxo lomsebenzi
02 ka-03
Ukufaka ama-Arguments e-VLOOKUP we-Excel
Ukukhomba okubhekiselwe kumakhalekhukhwini
Imibono ye-VLOOKUP isebenza emigqeni ehlukene yebhokisi lebhokisi njengoba kuboniswe esithombeni ngenhla.
Izinkomba zesitokisi okufanele zisetshenziswe njengezingxabano zingafakwa emgqeni oqondile, noma, njengoba zenziwe ezinyathelweni ezingezansi, ezikhomba, okufaka phakathi ukugqamisa ububanzi obufunayo bamaseli ane-pointer yegundane, kungasetshenziswa ukuwafaka ebhokisini lengxoxo .
Izinzuzo zokusebenzisa ukukhomba zihlanganisa:
- Kuyashesha kunokuthayipha;
- Amaphutha ambalwa akwenziwa ekufakeni izikhombisi eziqondile zamaseli.
Ukusebenzisa Ukubhekisela Kwezingxenye Ezihlobene Namaqiniso Nezimpikiswano
Kuyinto engavamile ukusebenzisa amakhophi amaningi we-VLOOKUP ukubuyisa ulwazi oluhlukile kusuka etafuleni elifanayo lemininingwane. Ukwenza kube lula ukwenza lokhu, ngokuvamile i-VLOOKUP ingakopishwa kusuka kwesinye iseli kuya kwesinye. Uma imisebenzi ikopishwa kwamanye amaseli, kufanele kuthathwe ukunakekelwa ukuze kuqinisekiswe ukuthi izikhombisi-ndlela eziholela ekugcineni zinikezwa indawo entsha yomsebenzi.
Esikhathini esingenhla, izibonakaliso ze dollar ( $ ) zizungezile izinkomba zeseli zethebula_ukuphikisana okubonisa ukuthi ziyizingqikithi zeseli , okusho ukuthi ngeke zishintshe uma umsebenzi ukopishwa kwesinye iseli. Lokhu kuyadingeka njengamakhophi amaningi we-VLOOKUP bonke bazobhekisela etafuleni elifanayo lemininingwane njengomthombo wolwazi.
Ukubhekisela kweseli esetshenziselwa i- lookup_value, ngakolunye uhlangothi , akuzungezwe izimpawu zama dollar, okwenza kube ireferensi yeselula. Izingqikithi zeseli ezihlobene uma zikopishwa ukuze zibonise indawo yazo entsha ngokuphathelene nendawo yedatha abayibhekisela kuyo.
Ukufaka iziphakamiso zomsebenzi
- Chofoza kulayini we-Lookup _value ebhokisini lengxoxo yeVLOOKUP
- Chofoza kuseli C2 kushidi lokusebenzela ukuze ufake le nkomba yesikhala njengengxabano yokusesha_key
- Chofoza ku- Table_array line yebhokisi lengxoxo
- Qinisekisa amaseli C5 kuya ku-D8 kusikripthi sokusebenzela ukuze ufake leli hlu njengobe i- Table_array ingxabano - izihloko zetafula azifakiwe
- Cindezela ukhiye we- F4 ekhibhodi ukuze ushintshe ububanzi ekubhekisweni kwezingqayizivele zeseli
- Chofoza ku- Col_index_num line yebhokisi lengxoxo
- Thayipha oku- 2 kulolu gqa njengengxabano ye- Col_index_num , ngoba amazinga esaphulelo asekholomu 2 we-argument ye- Table_array
- Chofoza kumugqa we- Range_lookup webhokisi lebhokisi
- Thayipha igama Liyiqiniso njengengxabano ye- Range_lookup
- Cindezela ukhiye wokungena kwikhibhodi ukuvala ibhokisi lengxoxo bese ubuyela ekhasini lokusebenzela
- Impendulo engu-2% (izinga lokunciphisa inani elithengiwe) kufanele livele esitokisini D2 sephepha lokusebenzela
- Uma uchofoza ku-cell D2, umsebenzi ophelele = i-VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE) ibonakala kwibha yefomula ngaphezulu kwephepha lokusebenzela
Kungani i-VLOOKUP ibuyiselwe i-2% njengesiphumo
- Kulesi sibonelo, ikholomu yokulinganisa ayinakho umdlalo oqondile we- search_key value ye-19.
- Njengoba i-argument ekhonjisiwe isethwe ku-TRUE, i-VLOOKUP izothola umlinganiso olinganiselwe nenani lokusesha_lokucinga .
- Inani eliseduze ngosayizi eliseyingcosana kunenani lokusesha_khiye lokungu -19 lineminyaka engu-11.
- Ngakho-ke, i-VLOOKUP ifuna amaphesenti ayisaphulelo emgqeni oqukethe u-11, futhi ngenxa yalokho, ubuyisa isilinganiso sesaphulelo se-2%.
03 ka 03
I-Excel VLOOKUP Ayisebenzi: # I-N / A ne-#REF Amaphutha
Imilayezo Yephutha le-VLOOKUP
Imilayezo yephutha elandelayo ihlotshaniswa ne-VLOOKUP.
A # N / A ("inani alitholakali") Iphutha likhonjiswa Uma:
- I- lookup _value ayitholakali kukholomu yokuqala yezingxabano zebanga
- I-argument ye- Table_array ayilungile. Isibonelo, ingxabano ingafaka amakholomu angenalutho ngakwesokunxele ebangeni
- I-argument ye- Range_lookup isethelwe ku-FALSE futhi umdlalo oqondile wempikiswano yokusesha_key ingatholakali kukholomu yokuqala ebangeni
- Ingxabano ye- Range_lookup isethelwe ku-TRUE futhi zonke izindinganiso kukholomu yokuqala ebangeni zikhulu kunokusesha_kusesho
I-#REF! ("khipha ebangeni") Iphutha likhonjiswa Uma:
- Umgudu we- Col_index_num mkhulu kunezinombolo zamakholomu ku- Table_array