Kana iwe wakambonzwa kuremerwa kutsvaga chaiyo data muExcel sheet ine mazana kana zviuru zvemitsara, ungangofara kuziva kuti pane basa rakagadzirirwa kuti basa iro rive nyore uye nekukuchengetera nguva: iro rine mukurumbira basa. Excel VLOOKUPChishandiso ichi chakafanana negirazi rekukudza otomatiki mukati memaspredishiti ako, richikubvumidza kuti uwane ruzivo uye udzore mhedzisiro ine chekuita mumasekonzi.
Kunyangwe vanhu vazhinji vachishandisa Excel zuva nezuva kune bhizinesi ravo, Haasi munhu wese anonyatsoziva kugona kweVLOOKUP.Muchikamu chino, tichatsanangura zvakajeka uye zvakadzama kuti zvinoshanda sei, rini zvinogona kuponesa hupenyu hwako, uye ndedzipi dzimwe nzira dzingave dzakaenzana kana kutonyanya kubatsira mune mamwe mamiriro.
Chii chinonzi VLOOKUP uye nei ichibatsira muExcel?
Basa racho VLOOKUP inotibvumira kutsvaga kukosha kwakatarwa mukoramu yekutanga yetafura kana data renji uye kudzoreredza ruzivo rwakabatana rwunoonekwa mumutsara wakafanana, asi mune imwe koramu.Izvi zvinoshandura mukukwanisa kukurumidza, otomatiki, uye nemazvo kuwana data rakabatana kune kukosha kwaunoda, pasina kuongorora mutsara nemutsara kana kuita zvikanganiso zvevanhu.
VLOOKUP iri kunyanya kubatsira kana uine matafura makuru muExcel uye iwe unofanirwa kutora ruzivo rwakabatana nekiyi kana chiziviso. Iyi ficha inoitawo kuti maspredishiti awedzere kusimba uye anochinja-chinja, achikubvumidza kuti ubatanidze uye utaure mavhoriyamu makuru edata angadai asingabatike.
Basa reVLOOKUP rakagadzirwa sei? Parameters uye syntax
Usati watanga kushandisa VLOOKUP, Chinokosha kunzwisisa syntax yayoMuExcel, iyo fomula inomiririrwa seizvi:
=VLOOKUP(kutarisisa_kukosha; tafura_yakarongwa; mbiru_chiratidzo;)
Imwe neimwe yeiyi parameter inoshanda basa rakakosha. Ngatidziburitse zvakadzama:
- lookup_value: Ndiyo data yaunoda kuwana muchikamu chekutanga cherenji yako. Inogona kuva kukosha kwaunonyora zvakananga pakati pemakotesheni, kana chirevo chesero rine kukosha.
- table_array: Zvinoreva huwandu hwemasero achaitwa kutsvaga. Izvo zvakakosha kuti koramu yekutanga yerenji ine kukosha kunotsvakwa.
- indicator_columns: Isa iyo nhamba (kutanga kubva pa1) yekoramu mukati meiyo yaunoda kubvisa iyo yakakosha kukosha. Kana huwandu hwako huri A2:D10 uye uchitsvaga data muchikamu C (inove iyo koramu yechitatu muchikamu), iwe unoisa nhamba 3 pano.
- interval_search: Iyi inharo yesarudzo inotsanangura kuti kutsvaga kunofanirwa kuve chaiko here (FALSE) kana kufungidzira (CHOKWADI). Kana ukasazvitsanangura, Excel inofunga kuti iwe unoda fungidziro yemutambo (TRUE).
Iyo syntax inogona kuita seyakaoma pakutanga, asi kana iwe uchinge waiisa mukuita iwe uchaona kuti iri nyore pane zvazvinoita. Kana iwe ukakanganisa mune chero parameter, Excel ichakuyambira nemhosho kuitira kuti ugone kugadzirisa fomula..
Zvakanakira uye Zvinogumira zveVLOOKUP: Makiyi Ruzivo
VLOOKUP inobatsira zvinoshamisa, asi ine zvimwe zvinogumira zvakafanira kuchengetwa mupfungwa. Chikuru ndechekuti inogona chete kutsvaga data kubva kuruboshwe kuenda kurudyiIzvi zvinoreva kuti kukosha kwauri kutsvaga kunofanirwa kugara kuri muchikamu chekutanga chechikamu chakasarudzwa. Kana iwe uchida kutsvaga data kune yakapesana, iwe unozoda kudzidza nezve mamwe mabasa akaita seINDEX uye MATCH.
Chimwe chidziviso chakakosha ndechekuti VLOOKUP inodzorera chete mhinduro yekutanga yainowanaKana paine zvakapetwa kaviri mukoramu yekutarisa, iwe unongowana yekutanga, saka iwe unozofanirwa kuchenesa tafura yako kana kufunga mamwe mafomula emamwe mhedzisiro yakaoma.
Uyewo cherechedza kuti kana Nhamba yakatsanangurwa yakakura kudarika nhamba yese yemakoramu muchikamu, Excel ichakuratidza kukanganisa #REF!. Zvakare, kana iwe ukashandisa iyo chaiyo match sarudzo uye kukosha hakusipo, iwe uchaona a # N / A.
Mazano ekutsvaga nekukurumidza uye nemazvo
Paunenge uchishanda nemabhodhi makuru, kurongeka kwakanaka ndiyo shamwari yako yakanaka. Heano mamwe tricks izvo zvinoita mutsauko:
- Shandisa madiki madiki: Deredza huwandu hwekutsvaga kune izvo zvinonyanya kudikanwa kuita kuti kutsvaga kuve nyore.
- Shandura matafura ako kuExcel Tables: Izvi zvinokutendera kuti uwedzere mareferensi akarongeka uye inovandudza zvakanyanya manejimendi nekugadzirisa data.
- Usashandise zvakanyanya kufanana chaiko kunze kwekunge zvichidikanwa: Kufananidza kunoenderana kunokurumidza uye kunokwana mune dzakawanda zviitiko.
Zvikanganiso zvakajairika uye nzira yekuzvigadzirisa
Kunyangwe vashandisi vane ruzivo vanogona kugumburwa pane zvidiki glitches. Heano mamwe ezvikanganiso zvakajairika uye maitiro ekuzvidzivirira kubva pakukanganisa zuva rako:
- #REF!: Nhamba yekoramu yakasarudzwa inodarika huwandu hwataurwa.
- #WORTH!: Iyo column inoratidzira haina kuiswa zvisizvo ku0.
- #N/A: Hapana chakanyatsoenderana chawanikwa padanho rakasarudzwa pachishandiswa "FALSE".
Kuti udzivise zvikanganiso izvi, tarisa chimiro chetafura, ita shuwa kuti data iri muhurongwa chaihwo, uye kaviri-tarisa yega yega yega basa.
Mazano epamusoro evashandisi vane ruzivo
Kana watobhowekana VLOOKUP uye uchida kutora maspredishiti ako eExcel kune imwe nhanho, ita shuwa yekuyedza aya matipi:
- Sanganisa VLOOKUP neIFERROR: Nenzira iyi unogona kuratidza meseji yetsika kana iyo data yauri kutsvaga isipo, pane kukanganisa kwakashata.
- Batanidza VLOOKUP nematafura epivot: Iyo inokutendera kuti ugovane data, kuongorora mavhoriyamu makuru, uye kubvisa ruzivo rwakakodzera nekungotarisa.
- Shandisa VLOOKUP nemamwe mafomula: Semuenzaniso, inosanganiswa neINDEX uye MATCH, kana neCHOOSE kana uchida kudzosera kumashure kurongeka kwemakoramu mukutsvaga kwako.
Zviwanikwa izvi zvinogadzira VLOOKUP mira kunze kwete sechishandiso chekutsvaga chete, asiwo semubatsiri wepamusoro wekutonga kwedata.
Mushure mezvose zvakaonekwa, iyo VLOOKUP basa Inoratidzwa sembiru yakakosha kune avo vanoshanda neExcel. Kuziva mashandisirwo ayo zvakanaka kuchachengetedza nguva, kuderedza zvikanganiso uye kushanda zvakanyanya..
