01 of 03
Tsvaga Dhiyabhorosi kuruboshwe
Excel Kuruboshwe Kwekutsvaga Mufomu Overview
Basa reVLOOKUP reAexcel rinoshandiswa kuwana uye kudzorera mashoko kubva patafura ye data zvichienderana nehuwandu hunokosha hwaunosarudza.
Kazhinji, VLOOKUP inoda kukoshesa kukosha kuve kuruboshwe rwese-kuruboshwe rwetafura ye data, uye basa rinodzokera kune imwe nzvimbo ye data iri mumutsara mumwechete kusvika kurudyi rwekukosha uku.
Nokubatanidza VLOOKUP neCOOSE basa ; zvisinei, ruboshwe rworuboshwe runogona kusikwa runoti:
- inobvumira kukosha kwekutsvaga kunosarudzwa kubva kune imwe koromo mumatafura e data
- dzoka ruzivo rwakanowanikwa mune imwe korombo kuruboshwe rwekukosha kwekuona
Muenzaniso: Kushandisa VLOOKUP uye CHOOSE Mabasa muRoft Pokura
Matanho akazara pasi apa aita shanduro yekupedzisira yakasara inoonekwa mumufananidzo uri pamusoro.
The formula
= VLOOKUP ($ D $ 2, CHOOSE ({1,2}, $ F: $ F, $ D: $ D), 2, FALSE)inoita kuti zvikwanise kuwana chikamu chinopiwa nemakambani akasiyana akanyorwa munhare 3 yedhafura ye data.
Basa reCOOSE rinoshandiswa mumusangano ndekunyengedza VLOOKUP kuti utende kuti chinyorwa chechitatu ndechechokwadi chinyorwa 1. Somugumisiro, zita rekambani rinogona kushandiswa sekukosha kwekutsvaga kuwana zita rechikamu chinopiwa nekambani imwe neimwe.
Zvidzidzo zveDzidziso - Kupinda muTutorial Data
- Isa zvinotevera pamasero akaratidzwa: D1 - Mutengesi E1 - Chikamu
- Pinda tafura ye data yakaratidzwa mumufananidzo pamusoro apa mumasero D4 kusvika F9
- Mitsara 2 ne3 yasara isina chinhu kuitira kuti agone kutsvaga nzira yekutsvakurudza uye kuruboshwe rwekutsvaga yakagadzirwa panguva ino
Kutanga Kuruboshwe Kwekutsvaga - Kuvhura VLOOKUP Dialog Box
Kunyange zvazvo zvichibvira kungonyora fomu iri pamusoro apa zvakananga muchitokisi F1 mubasa rekushanda, vanhu vazhinji vane dambudziko neshoko rekushandisa.
Imwe nzira, munyaya iyi, ndeyokushandisa VLOOKUP dialog box . Munenge mese mabasa eExcel ane bhokisi rekubhokisi rinokubvumira kupinda mune imwe nharo dzemabasa pane imwe mutsara.
Tutorial Steps
- Dzvanyira pane cell E2 yepaji rekushanda - nzvimbo iyo migumisiro yefomu yekutsvaga yakasara icharatidzwa
- Dzvanya paMafomu tab yebhoni
- Dzvanya pakusarudza kweLookup & Referenha mubhubhoni kuti uzarure basa redu rezasi
- Dzvanya paVLOOKUP mune urongwa kuti uwedzere basa rebhokisi rebhokisi
02 of 03
Kupinda Mhedziso muBhokisi reDhialogo yeVLOOKUP - Dzvanya kuti Uone Dhiyabhorosi Mufananidzo
VLOOKUP's Arguments
Mhinduro dzebasa ndiwo maitiro anoshandiswa nehutano kuverenga chigumisiro.
Mubhokisi rebhokisi rebasa, zita remashoko ose rinowanikwa pane imwe mutsara unoteverwa nemunda umo ungapinda kukosha.
Pinda mazano anotevera pane imwe neimwe yeVLOOKUP's arguments pamusoro pechirevo chakarurama chebhokisi rebhokisi sezvinoratidzwa mumufananidzo uri pamusoro.
Kukosha kweLookup
Kukosha kwekutsvaga ndiyo munda weruzivo runoshandiswa kutsvaga tafura yakazara. VLOOKUP inodzorera imwe munda we data kubva mumuganhu mumwechete sekukosha kwekuona.
Muenzaniso uyu unoshandisa seferensi yekero kune nzvimbo iyo zita rekambani richapindirwa mubheji rekushanda. Kubudirira kweizvi ndechokuti zvinoita kuti zvive nyore kuchinja zita rekambani pasina kushandura fomu.
Tutorial Steps
- Dzvanya pamutsetse wekuteerera wevheji mubhokisi rebhokisi
- Dzvanya pachitokisi D2 kuti uwedzere kurefera kwekeroyi kumutsara wekutsvaga- vhesi
- Dhinda f4 key pane keyboard kuitira kuti seferensi yeseferensi ive yakakwana - $ D $ 2
Cherechedza: Absolute cell references inoshandiswa pakukosha kwekutsvaga uye patafura nharo dzekudzivirira zvikanganiso kana shanduro yekunyora iko kunyoreswa kune mamwe masero mupepa rekushanda.
Tafura Array: Kupinda CHOOSE Function
Itafura yenyaya yakakonzera ndiyo ibwe remashoko akawandisa kubva kune mamwe mashoko anowanikwa.
Kazhinji, VLOOKUP inongotarisa kurudyi rwekuona maonero ekukosha kuti uwane deta iri patafura. Kuti uite kuti utarise kuruboshwe, VLOOKUP inofanira kunyengedzwa nokugadzirisa zvakare mapuranga mutafura yepafura uchishandisa CHOOSE basa.
Muchirevo ichi, CHOOSE basa rinoita mabasa maviri:
- iyo inogadzira tafura yakazara iyo inongori miviri miviri yakafara-zvinyorwa D uye F
- inoshandura kodzero yekuenda kuruboshwe rwema column mutafura yakazara kuitira kuti chinyorwa F chiuye pakutanga uye chinyorwa D chechipiri
Tsanangudzo yekuti CHOOSE basa rinoita sei mabasa aya inogona kuwanikwa pane peji 3 yemudzidzi .
Tutorial Steps
Cherechedza: Paunopinda maitiro nemaune, nharo dzese dzebasa dzinofanira kuparadzaniswa ne " comma ", " .
- Mune VLOOKUP basa rebhokisi rebhokisi, tora paTable_array line
- Pinda zvinotevera CHOOSE basa
- CHOOSE ({1,2}, $ F: $ F, $ D: $ D)
Nhamba Yenzorwa Indaneti
Kazhinji, nhamba yekorodho yenhamba inoratidza kuti ndeypi imwe yetafura yakazara ine data iwe uri shure. Muchirevo ichi; zvisinei, zvinoreva kurongeka kwemakoroni akaiswa neCOOSE basa.
ICHOOSE basa rinogadzira tafura yakazara iyo miviri miviri yakareba neyendori F yekutanga inoteverwa nekombodhi D. Sezvo mashoko aitsvaga - chikamu zita - iri muchitenderera D, kukosha kwekorodhi index argument inofanira kuiswa kune 2.
Tutorial Steps
- Dzvanya paCol_index_num line mubhokisi rebhokisi
- Tora a 2 mumutsara uyu
The Range Lookup
VLOOKUP's Range_lookup nharo inokosha inokosha (TRUE kana FALSE chete) inoratidza kana iwe uchida VLOOKUP kuwana mhosho chaiyo kana kuti inofananidzwa nehuwandu hunokosha.
- Kana CHOKWADI kana kana mhirizhonga iyi isingabvumirwi, VLOOKUP inodzorera kana yakanyatsoenderana neLookup_value, kana, kana mutsara chaiwo usingawanikwi, VLOOKUP inodzorera iyo inotevera kukosha kukuru. Nokuda kwemuumbiro wekuita izvi, data iri muhombe yekutanga yeTable_array inofanira kuenzanisirwa mukukwira .
- Kana FALSE, VLOOKUP achangoshandisa mhando chaiyo yeLookup_value. Kana pane zviyero zviviri kana kupfuura muhombe yekutanga yeTable_array iyo inofanidza kukosha kwekuona, kukosha kokutanga kunowanikwa kunoshandiswa. Kana mutsara chaiwo usina kuwanikwa, # N / A kukanganisa kunodzorerwa.
Muchidzidzo ichi, sezvo isu tichitsvaga chikamu chezita, Range_lookup ichaiswa kuNhema kuitira kuti mamiriro chete chaiwo adzorerwa nemurairo.
Tutorial Steps
- Dzvanya paRange_lookup mutsara mubhokisi rebhokisi
- Tora shoko rokuti Manyepo munharaunda iyi kuratidzira kuti tinoda VLOOKUP kudzorera mamiriro chaiwo ye data yatinenge tichitsvaka
- Dzvanya OK kuti upedze rondedzero yekutsvaga fomu uye yevha yebhokisi yebhokisi
- Sezvo isu tisati tava muzita rekambani muchitokisi D2, n # N / A kukanganisa kunofanira kunge iri muchitokisi E2
03 of 03
Kuedza Kuruboshwe Kwemafomu
Dzoka Kudzoka neRoft Lookup Formula
Kuti uwane kuti makambani anopa zvikamu zvipi, chengetedza zita rekambani muchikamu cheD2 uye shandisa ENTER kiyi pane keyboard.
Zita iro rinenge richiratidzwa muchitokisi E2.
Tutorial Steps
- Dzvanya pane cell D2 mubasa rako rekushanda
- Gadzira Gadgets Kuwedzera pane cell D2 uye shandisa ENTER key pane keyboard
- Zvinyorwa Gadhi - chikamu chinopiwa nekambani Gadgets Plus - inofanira kuiswa muchitokisi E2
- Edzai chikwata chekutsvaga chinowedzera nekunyora mamwe mazita emakambani muchitokisi D2 uye chikamu chinowirirana zita chinofanira kuonekwa muchitokisi E2
VLOOKUP Error Messages
Kana shoko rekukanganisa rakadai se # N / A rinowanikwa muchitokisi E2, kutanga chengeta mapepa ekupelera muchitokisi D2.
Kana kupora hakusi dambudziko, urongwa urwu hweVLOOKUP mhosho dzedambudziko hunogona kukubatsira kuziva kuti chinetso chiripi.
Kuparadzanisa CHOOSE Basa reJobho
Sezvakataurwa, mumutsetse uyu, CHOOSE basa rine mabasa maviri:
- iyo inogadzira tafura yakazara iyo inongori miviri miviri yakafara-zvinyorwa D uye F
- inoshandura kodzero yekuenda kuruboshwe rwema column mutafura yakazara kuitira kuti chinyorwa F chiuye pakutanga uye chinyorwa D chechipiri
Kugadzira Kabiri Tahfu Tafura Array
Mutsara weCOOSE basa ndeyu:
= CHOOSE (Index_number, Value1, Value2, ... Value254)
ICHOOSE basa rinowanzodzorera kukosha kweumwe kubva pane urongwa hwehutsika (Value1 kusvika Value254) zvichibva pane nhamba ye index inopinda.
Kana iyo nhamba ye nhamba iine 1, basa rinodzoka Value1 kubva pakurongwa; kana nhamba yenhamba yacho iri 2, basa rinodzoka Value2 kubva pakurongwa nezvimwewo.
Nokupinda mazita akawanda nenhamba; zvisinei, basa racho richadzoka mitemo yakawanda chero ipi zvayo yaidiwa. Kuwana CHOOSE kudzoka mitemo yakawanda kunoitwa nekugadzira zvinhu .
Kupinda maitiro kunoitwa nekukomberedza nhamba dzakapinda nemakumbo emakumbo kana mabheji. Nhamba mbiri dzakapinda nhamba ye index: {1,2} .
Iko kunofanira kuonekwa kuti CHOOSE haisi yekugadzirisa pakuumba mapafura maviri emutsara. Nokubatanidza nhamba inowanikwa mu-array - yakafanana ne "{1,2,3}" uye imwe yeruzhinji mumagariro ekukosha, katatu tafura yepeyumu ingasikwa.
Zvimwe zvinyorwa zvingakubvumira kuti udzoke ruzivo rwakasiyana neyo kuruboshwe rwekutsvaga mazano nenzira yekushandura VLOOKUP's column column index nharo kusvika kuhamba ye column iri ine mashoko anoda.
Kuchinja Mutemo weColumns neCOOSE Function
MuCHOOSE basa rinoshandiswa mumutsetse uyu: CHOOSE ({1,2}, $ F: $ F, $ D: $ D) , rutivi rwekorodhi F rwakanyorwa pamberi pehurukuro D.
Sezvo CHOOSE basa inogadzirisa tafura yeVLOOKUP - iyo inotora dhiyabhorosi yeiyo basa - kushandura kurongeka kwemasango muCHOOSE basa inopfuudzwa kuenda kuVLOOKUP.
Iye zvino, kusvika paVLOOKUP inofunganya, tafura yefurafura ndeyemakorom maviri chete akareba nekamuripu F kuruboshwe uye chinyorwa D kurudyi. Sezvo chikamu F chine zita rekambani yatinoda kutsvaga, uye sezvo chikamu D chine mazita okuti, VLOOKUP ichakwanisa kuita zvayo zvekuita mabasa ekutsvaga dhesi iri kune kuruboshwe rwekukosha kwekuona.
Somugumisiro, VLOOKUP inokwanisa kushandisa zita rekambani kuwana chikamu chavanopa.