Nzira Yokuwana Dhiyabhorosi neVLOOKUP mu Excel

01 of 03

Tsvaga Matanho Akaenzana kuDhina neVelokUP yaEexcel

Izvo Basa reVLOOKUP Rinoshanda Sei

Basa reVLOOKUP reEexcel, iro rinomiririra kuona , rinogona kushandiswa kutarisa mashoko chaiwo ari mune tafura ye data kana database.

VLOOKUP inowanzodzorera munda umwe we data sezvo yakabuda. Iyo inoita sei izvi ndeyikuti:

1. Iwe unopa zita kana lookup_value inotaurira VLOOKUP mumutsara wei kana kuti rekodha retafura yedeta kuti utsvake data yaunoda
2. Iwe unopa iyo nhamba yekorodhe - inozivikanwa seye col_index_num - ye data yaunotsvaka
3. Basa racho rinotarisa lookup_value mukomba yekutanga yedhafura ye data
4. VLOOKUP inotora uye inodzorera ruzivo rwauri kutsvaga kubva mune imwe munda wenhoroondo imwechete uchishandisa iyo yakapiwa column column

Kuronga Dhiyabhorosi Kutanga

Kunyange zvazvo zvisingawanzodiwa nguva dzose, zvinowanzova zvakanaka kuti utange kutarisa huwandu hwemashoko ayo VLOOKUP ari kutsvaga mukukwira urongwa achishandisa korona yokutanga yemuchina wekodzero yemhando.

Kana iyo data isati yakarongwa, VLOOKUP inogona kudzorera chikonzero chisina kururama.

Vhangeri yeVLOOKUP's Syntax uye Arguments

Mutsara webasa unoreva kurongeka kwebasa uye rinosanganisira zita rebasa, mabheji, uye nharo .

Mutsara webasa reVLOOKUP ndewokuti:

= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)

lookup _value - (inoda) kukosha kwekutsvaga - yakadai sehuwandu hwakatengeswa mumufananidzo kumusoro

tafura_array - (inoda) iyi ndiro tafura ye data iyo VLOOKUP inotsvaga kuwana ruzivo rwaunotevera.

• Itafura_array inofanira kunge ine zvinyorwa zviviri zvemashoko
• Irosi yekutanga inowanzo ine lookup_value

col_index_num - (inodikanwa) nhamba yekorodhe yenhamba yaunoda inowanikwa.

• Kuverenga kunotanga nekatsva_kutsvaga_key column sekholomu 1
• Kana col_index_num yakagadzirirwa kune nhamba huru kudarika nhamba yemakorani akasarudzwa mutafura_array argument #REF! kukanganisa kunodzoserwa nebasa racho

range_lookup - (kusarudza) inoratidza kuti kana kuti kwete urongwa hwakarongerwa mukukwira urongwa.

• Dhiyabhorosi iri mukorofa yekutanga inoshandiswa seyiyi yerudzi
• Kukosha kweBoolean - TRUE kana FALSE ndiyo chete inogamuchirwa
• Kana kusina kusimbiswa, kukosha kwakagadzirirwa ku TRUE kuburikidza
• Kana yakagadzirirwa ku TRUE kana kusakanganiswa uye korona yekutanga yezera haisi kurongwa mukukwira kurongeka, mhinduro isina kururama inogona kuitika
• Kana yakagadzirirwa ku TRUE kana kusakanganiswa uye kunyatsokwikwidzana kwekutsvaga _value hakuwanikwi, muto uri pedyo uri muduku muhukuru kana kukosha inoshandiswa se- search_key
• Kana yakagadzirirwa kuFALSE, VLOOKUP inogamuchira mamiriro chaiyo ezvo kuwanikwa _value . Kana pane mitemo yakaenzana yakaenzana, kukosha kwekutanga kufanana kunodzorerwa
• Kana yakagadzirirwa kuFALSE uye hapana chiyano chakafanana chekutsvakurudza_key kunowanikwa, # N / A kukanganisa kunodzorerwa nemabasa

Muenzaniso: Tsvaga Mari Yokutengesa Yezvinhu Zvakawanda Zvakatengwa

Muenzaniso uri mumufananidzo uri pamusoro apa unoshandisa basa reVLOOKUP kuwana huwandu hwekutengesa hunosiyana zvichienderana nehuwandu hwezvinhu zvakatengwa.

Muenzaniso unoratidza kuti kutengesa kwekutenga zvinhu 19 ndiko 2%. Ichi ndechokuti Mahombekombe yakawanda ine zvikamu zvehutano. Somugumisiro, VLOOKUP haigoni kuwana mamiriro chaiwo. Pane kudaro, mutsara unofanirwa unofanira kuwanika kuitira kuti udzorere yakarurama kutengesa muyero.

Kuti uwane zvakafanana:

• sarudza iyo data iri mutafura_array yakakwira kuronga;
• sarudza mharidzo_kukurukurirana ku TRUE

Mumuenzaniso, inotevera inoshandiswa ine basa reVLOOKUP inoshandiswa kuwana tsvina yehuwandu hwezvinhu zvakatengwa.

= VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE)

Kunyange zvazvo chirevo ichi chinogona kungoiswa muchengeti rekushanda, rimwe sarudzo, sezvinoshandiswa nematanho akarongwa pasi apa, nderokushandisa ibhokisi rebasa rekushanda kuti uise mazano aro.

• Kushandisa bhokisi rinowanzoita kuti zvive nyore kupinda mhinduro dzebasa zvakanaka.

Kuvhura bhokisi reDVD reVLOOKUP

Matanho anoshandiswa kupinda basa reVLOOKUP rinoratidzwa mumufananidzo pamusoro apa muchikamu B2 nde:

1. Dzvanya pachitokisi B2 kuti uite sero rinoshanda - nzvimbo iyo migumisiro yebasa reVLOOKUP inoratidzwa
2. Dzvanya paMafomu eTom.
3. Sarudza Kuona & Tsvaga kubva kubhoni kuti uzarure basa rinoderera pasi
4. Dzvanya paVLOOKUP mune urongwa kuti uwedzere basa rebhokisi rebhokisi

02 of 03

Kupinda VLOOKUP Function Arguments

Kunongedzera kumagwaro ekare

Mhedziso yebasa reVLOOKUP inopinda mumiganhu yakasiyana yebhokisi rebhokisi sezvinoratidzwa mumufananidzo uri pamusoro.

Zvinyorwa zveseti zvinoshandiswa semafungiro zvinogona kuiswa mumugwagwa wakarurama, kana, sezvakaitwa mumatanho ari pasi apa, kuratidza, izvo zvinobatanidza kujekesa huwandu hwemasero anenge aine pointer, hunogona kushandiswa kuvaisa mubhokisi yebhokisi .

Zvakanakira kushandiswa kunyorera zvinosanganisira:

• Inokurumidza kupfuura kuverenga;
• Pane zvikanganiso zvishomanana zvinoshandiswa kupinda muchirongwa chema cell references.

Kushandisa Relative uye Absolute Cell References neArguments

Hazvishamisi kushandisa makopi akawanda eVLOOKUP kudzoka mashoko akasiyana-siyana kubva patafura imwechete ye data. Kuita kuti zvive nyore kuita izvi, kazhinji VLOOKUP inogona kunyorwa kubva pane imwe sero kuenda kune imwe. Kana mabasa akanyorerwa kune mamwe masero, kutarisirwa kunofanira kutorwa kuti ivimbise kuti iyo inokonzerwa nesero inotaurwa yakarurama yakapiwa nzvimbo itsva yebasa.

Mumufananidzo wepamusoro, dhidha zviratidzo ( $ ) zvakakomberedza zvinyorwa zvemasero zvetafura- mhirizhonga inoratidza kuti ndeyeiyo yesekeri zvinyorwa , izvo zvinoreva kuti hazvizoshanduki kana basa racho rakanyorerwa kune rimwe sero. Izvi zvinodikanwa semakopi akawanda eVLOOKUP zvese zvinongoratidzira tafura imwechete ye data sechinhu chekuziva.

Tsanangudzo yesero inoshandiswa ye lookup_value, kune rumwe rutivi , haisati yakakomberedzwa nemadhora emadhora, izvo zvinoita kuti ive nhengo yechipatara. Relative cell references inoshandura pavanonyorwa kuti varatidze nzvimbo yavo itsva maererano nenzvimbo ye data yavanoreva.

Kupinda Maitiro Akashanda

1. Dzvanya paLookup _value line muVLOOKUP dialog box
2. Dzvanya pane seki C2 mune pepa rekushanda kuti uite ino rekero sechirongwa semutsva_kutsvaga
3. Dzvanya paTable_array line yebhokisi rebhokisi
4. Simbisai masero eC5 kusvika kuD8 mubasa rekushandisa kuti uise hutabhenakeri iyi seTable_array argument - misoro yetafura haisi
5. Dhinda F4 key pabhobhoyo kuti uchinje mararamiro kune zvachose zvinyorwa zvinyorwa
6. Dzvanya paCol_index_num mutsara webhokisi rebhokisi
7. Tora 2 pamusoro pemutsara uyu seCol_index_num argument, sezvo kubhadharwa kwemazinga ari mu column 2 yeThe Table_array argument
8. Dzvanya paRange_lookup mutsara webhokisi rebhokisi
9. Tora shoko rokuti True seRange_lookup argument
10. Dhinda pikicha yePinda mubhobhoyo kuti uvhare bhokisi rebhokisi uye udzoke kushizha
11. Mhinduro 2% (iyo inotambisa chiyero chehuwandu hwakatengwa) inofanira kuonekwa muchitokisi D2 chebasa rekushanda
12. Paunenge uchinyora pane cell D2, basa rakakwana = VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE) inowanikwa mubhairi yepamusoro pane pepa rekushanda

Nei VLOOKUP Yakadzoka 2% seGumisiro

• Mumuenzaniso, yeWorld column haina mutsara chaiwo wekutsvaga_key value ye19.
• Sezvo i_kukakavadzana kwakagadzirirwa ku TRUE, VLOOKUP ichawana mutsara wakatarisana nehutsva hwekutsvaga_key .
• Iko kukoshesa kwepedyo kuburikidza nehukuru hwakanga huri hudiki kudarika ku_kutsvaga_keyeni kwe19 ndiko 11.
• VLOOKUP, saka, anotarisa chikamu chekutsvaga mumutsara waiva ne11, uye, somugumisiro, inodzorera kuderedzwa kwema 2%.

03 of 03

Excel VLOOKUP Isingashandisi: # N / A uye #REF Zvikanganiso

VLOOKUP Error Messages

Izvo zvinotevera mhosho zvinyorwa zvinosanganiswa neVLOOKUP.

A # N / A ("kukosha hakuwanikwi") Mhosva inoratidzwa Kana:

• Izvo zvinoonekwa -vhura hazviwanikwi muhombe yekutanga yemakakatanwa akasiyana
• Iyo Table_array argument haina kururama. Semuenzaniso, nharo dzinogona kusanganisira mbiru dzisina chinhu kune rumwe rutivi rworuboshwe
• Nharo yeRange_lookup yakagadzirirwa kuFALSE uye mutsanana chaiwo wekutsvaga_kutsvaga kwekutsvaga hakugoni kuwanikwa muhombe yekutanga yezera
• Mhinduro yeRange_lookup yakagadzirirwa ku TRUE uye yose yehutsika mukorori yekutanga yezera yakakura kudarika search_key

A #REF! ("rondedzera kure") Mhosva inoratidzwa Kana:

• Iko Col_index_num kukaka yakawanda kudarika nhamba yemakamuri muTable_array