Tsvaga Multiple Fields Data ne Excel VLOOKUP

Nokubatanidza basa reVLOOKUP reEcelcel neCOLUMN basa tinogona kutora fomu yekukumbira iyo inokubvumira kuti udzoke mitemo yakawanda kubva kune rimwe musara we database kana tafura ye data.

Mumuenzaniso unoratidzwa mumufananidzo uri pamusoro, fomu yekuona inoita kuti zvive nyore kudzorera zvinhu zvose - zvakadai somutengo, nhamba yechitsauko, uye mutengesi - ane chokuita nezvidimbu zvakasiyana-siyana zvehurdware.

01 yegumi

Dzorera Mitemo Yakasiyana-siyana neExcel VLOOKUP

Kutevera matanho ari pasi apa kunogadzira fomu yekuona yakaratidzwa mumufananidzo pamusoro apa ichadzoka mitemo yakawanda kubva pane imwe rekodhi re data.

Izvo zvinodiwa zvinoda kuti basa reCOLUMN rive rakasvibiswa mukati meVLOOKUP.

Kunyorera basa kunosanganisira kupinda basa rechipiri seimwe yezvikonzero zvekutanga.

Muchidzidzo ichi, basa reCOLUMN richapinda sehurukuro yenhamba yenyaya yeVLOOKUP.

Nhanho yekupedzisira mune zvidzidzo inosanganisira kuikora fomu yekutsvaga kune dzimwe mbiru kuitira kuti uwane mamwe maitiro ezvakasarudzwa.

Tutorial Contents

• Kupinda Tutorial Data
• Kugadzira Zita Rakataridzirwa Dheta Dheta
• Kutanga Basa reVLOOKUP
• Kupinda Kukosha Kwekuona Kwekushandisa kushandisa Absolute Cell References
• Kupinda muTereta Ar Arument
• Kupinda Nechipfuva CUMUMN Function
• Kuzadzisa basa reVLOOKUP
• Kukopa Mutemo wekuona neFill Handle
• Kudzokorora Data neLookup Formula

02 yegumi

Pinda Tutorial Data

Nhanho yekutanga mune zvidzidzo ndeyekuisa deta muExpcel worksheet .

Mukutevera kutevera matanho ari mudzidzisi pinda data inoratidzwa mumufananidzo pamusoro pemasero anotevera.

• Ita huwandu hwemashoko makuru mumasero D1 kusvika kuG1
• Pinda muganhu wechipiri mumasero D4 kusvika kuG10

Tsanangudzo yekutsvaga uye chirevo chekutsvaga chakasikwa panguva iyi tutori ichapinda mumutsara wechipiri chebasa.

Dzidzo haisanganisi kuumbwa kunoonekwa mumufananidzo, asi izvi hazvingabatsiri maitiro ekutevedza kwekushandisa.

Mashoko pamusoro pekugadzirisa mafungiro akafanana neaya aonekwa pamusoro apa anowanikwa mune iyi Basic Excel Kuburitsa Tutori .

Tutorial Steps

1. Pinda data sezvinoonekwa mumufananidzo uri pamusoro pemasero D1 kusvika kuG10

03 wegumi

Kugadzira Zita Rakataridzirwa Dheta Dheta

A zita rakarongeka ndiyo nzira iri nyore yekureva ruzivo rwemashoko mune fomu. Panzvimbo pokunyora mune zvinyorwa zvinyorwa zve data, unogona kungonyora zita remutsetse.

Chinhu chechipiri chekubatsira kushandiswa zita rakarongwa ndechokuti masero ezvitokisi zvezera iri haamboshanduki kunyange kana fomu yacho ikakumbirwa kune mamwe masero mupepa rekushanda.

Range mazita, naizvozvo, imwe nzira yekushandisa zvachose masero ezvinyorwa kuti udzivise zvikanganiso apo uchinyorera mafomu.

Cherechedza: Zita remitemo haritauri mazita ekuma musoro kana mashizha emutsetse (rutsva 4) asi chete data pachayo.

Tutorial Steps

1. Simbisai masero D5 kusvika G10 mubasa rekushandisa kuti musarudze
2. Dzvanya paZita reBhokisi riri pamusoro pe column A
3. Tora "Table" (hapana zvinyorwa) muZita reBhokisi
4. Dhinda iri ENTER kiyi pane keyboard
5. Mitsara D5 kusvika kuG10 iye zvino ine zita rezita re "Table". Tichashandisa zita reVLOOKUP tafura array argument gare gare muchidzidzo

04 yegumi

Kuvhura bhokisi reDVD reVLOOKUP

Kunyange zvazvo zvichikwanisika kungongoronga purogiramu yedu yekutsvaga muchengeti mune pepa rekushanda, vanhu vazhinji vanoona zvakaoma kuchengetedza syntax yakananga-kunyanya kwemafomu akaoma seyayo yatinoshandisa mune iyi tadzidzo.

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

1. Dzvanyira pa cell E2 yepafashiti - nzvimbo iyo migumisiro yeiyo miviri yekutsvaga fomu icharatidzwa
2. Dzvanya paMafomu tab yebhoni
3. Dzvanya pakusarudza kweLookup & Referenha mubhubhoni kuti uzarure basa redu rezasi
4. Dzvanya paVLOOKUP mune urongwa kuti uzarure bhokisi rebasa rebasa

05 of 10

Kupinda Kukosha Kwekuona Kwekushandisa kushandisa Absolute Cell References

Kazhinji, kukosha kwekutsvaga kunoenderana nemunda we data munhare yekutanga yedhafura ye data.

Mumuenzaniso wedu, kukosha kwekutsvaga kunoreva kune zita re hardware pamusoro pekuti tinoda kuwana ruzivo.

Mvumo yemvumo yedheta yekukosha kwekutsvaga ndeyi:

• text data
• chinhu chinokosha (TRUE kana FALSE chete)
• nhamba
• sero rinotaura nezvekukosha mune pepa rekushanda

Mumuenzaniso uyu, tichapinda muchitsauko chekuyera pane zita rechinzvimbo richave riri-cell D2.

Absolute Cell References

Mune rimwe danho rekupedzisira mune zvidzidzo, tichakopa fomu yekutsvaga muchitsetse E2 kumasero F2 neG2.

Kazhinji, kana mafomu akanyorwa muExpcel, zvinyorwa zvinoshandurwa zvinoshandura kuratidza nzvimbo yavo itsva.

Kana izvi zvikaitika, D2 - tsanangudzo yesero yekukosha kwekutsvaga - ichashandura sezvo shanduro yacho inokwidziridzwa kusika zvikanganiso mumasero F2 neG2.

Kuti tidzivirire zvikanganiso, isu tichashandura sero reD2 renzvimbo mumutauro wakakwana .

Absolute cell references haishanduki kana mafomu akanyorwa.

Absolute cell references zvinoshandiswa nokufambisa F4 key pane keyboard. Kuita izvi kunowedzera dollar zviratidzo zvakakomberedza sero reference se $ D $ 2

Tutorial Steps

1. Dzvanya pamutsetse wekuteerera wevheji mubhokisi rebhokisi
2. Dzvanya pachitokisi D2 kuti uwedzere kurefera kwekeroyi kumutsara wekutsvaga- vhesi . Iyi ndiyo sero yatinonyora iyo chikamu zita rekuti tiri kutsvaga ruzivo
3. Pasina kutamisa kuisa pfungwa, shandisai F4 key pabhobhoyo kuti uchinje D2 mumutero wakakwana $ D $ 2
4. Siya basa reVLOOKUP rebhokisi rebhokisi rakasununguka kuitira danho rinotevera mune zvidzidzo

06 of 10

Kupinda muTereta Ar Arument

Itafura tafura ndiro tafura ye data iyo fomu yekutsvaga inotsvaga kuwana ruzivo rwatinoda.

Itafura yepafura inofanira kunge ine zvinyorwa zviviri zvemashoko .

• iyo kori yekutanga inosanganisira ikozvino kukoshesa nharo yepamusoro (yekutanga danho mumudzidzi)
• chechipiri, uye mamwe mahwendefa ezvimwe, achatsvakurudzwa nefomu rekutsvaga kuti awane ruzivo rwandinotaura.

Itafura yemhando yakakosha inofanirwa kuiswa sevheji rine zvinyorwa zveseti zvetafura ye data kana sezita remhando .

Nokuda kwemuenzaniso uyu, tichashandisa zita rezita rakagadzirwa muchikamu chechitatu chekudzidzisa.

Tutorial Steps

1. Dzvanya patafura- ruvara rutsva mubhokisi rebhokisi
2. Tora "Table" (hapana zvinyorwa) kuti uise zita rezita rekuda
3. Siya basa reVLOOKUP rebhokisi rebhokisi rakasununguka kuitira danho rinotevera mune zvidzidzo

07 pa10

Kunyanya basa reCOLUMN

Kazhinji VLOOKUP inongodzorera data kubva kune imwe koromo yedhafura ye data uye iyi column inogadziriswa nenhamba yekorodho yenhamba yenyaya.

Mumuenzaniso uyu, zvisinei, tine mitsara mitatu yatinoda kudzorera dheta kubva ipapo tinoda nzira yekushandura nyore nyore nhamba yekorodho yenhamba pasina kuchinja shanduro yedu yekutsvaga.

Iyi ndiyo iyo basa reKOLUMN rinopinda. Nokupinda naro seyo column column index argument, ichashandurwa sezvo fomu yekuteerera inopihwa kubva muchitokisi D2 kusvika kumasero E2 uye F2 gare gare pane zvidzidzo.

Nesting Mabasa

Iko COLUMN inoshanda, saka, inoshanda seVLOOKUP's column column index argument .

Izvi zvinozadzikiswa nekugadzira basa COLUMN mukati meVLOOKUP muCol_index_num line yebhokisi rebhokisi.

Kupinda Basa reCOLUMN NOKUSHANDURA

Apo nesting inoshanda, Excel haitibvumiri kuti tive nemukana wechipiri webhokisi rebasa rekuti tiise mhinduro dzayo.

Iko COLUMN inoshanda, saka, inofanira kuiswa manyore muCol_index_num line.

Basa reCOLUMN rine nharo imwe bedzi - iyo Arbit argument iyo inoshandiswa seyero.

Kusarudza COLUMN Function's Reference Argument

Basa reCOLUMN basa rekudzorera nhamba yekorodhe yakapiwa seChirangaridzo.

Mune mamwe mazwi, iyo inoshandura tsamba yekorineni ichiita nhamba ine chinyorwa A kuva yekutanga korombo, chinyorwa B yechipiri nezvakadaro.

Sezvo munda wekutanga we data watinoda kudzoka ndiyo mutengo wechinhu-chiri muhurukuro mbiri yedhafura yedata - tinogona kusarudza sero rekero chero sero iri muChangi B seShoko Rokutaura kuitira kuti uwane nhamba 2 iyo Col_index_num argument.

Tutorial Steps

1. Mune VLOOKUP basa bhokisi rebhokisi, tora paCol_index_num line
2. Tora iyo basa zita column column yakatevedzwa nebhuka rakapoteredzwa " ( "
3. Dzvanya pane sero B1 mune pepa rekushandisa kuti uise iyo sero reference seChishopano
4. Tora chivharo chakavhara " ) " kupedza COLUMN basa
5. Siya basa reVLOOKUP rebhokisi rebhokisi rakasununguka kuitira danho rinotevera mune zvidzidzo

08 yegumi

Kupinda muVLOOKUP Range Lookup Argument

VLOOKUP's Range_lookup mhirizhonga inokosha (TRUE kana FALSE chete) inoratidza kana iwe uchida VLOOKUP kuwana mhosho chaiyo kana kuti yakatarisana neLookup_value.

• 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 ruzivo rwakananga pamusoro pechinhu chinonzi hardware, tichaisa Range_lookup yakaenzana neNhema.

Tutorial Steps

1. Dzvanya paRange_lookup mutsara mubhokisi rebhokisi
2. Tora shoko rokuti Manyepo munharaunda iyi kuratidzira kuti tinoda VLOOKUP kudzorera mamiriro chaiwo ye data yatinenge tichitsvaka
3. Dzvanya OK kuti upedze chikwata chekutsvaga uye chevha yebhokisi rebhokisi
4. Sezvo isu tisati tapinda maitiro ekutsvaga muchechi D2 an # N / A kukanganisa kuchavapo muchitokisi E2
5. Iko kukanganisa kuchagadziridzwa apo tichawedzera zvido zvekutsvaga mune danho rekupedzisira rekudzidzisa

09 yegumi

Kukopa Mutemo wekuona neFill Handle

Iko yekutsvaga mazano yakagadzirirwa kutora data kubva pazvikamu zvakawanda zvetafura yedhesi panguva imwechete.

Kuti uite izvi, fomu yekutsvaga inofanira kugara muminda yose yatinoda ruzivo.

Muchidzidzo ichi tinochida kuti tiwane dheart kubva pamakamuri 2, 3, uye 4 etafura ye data - iyo ndiyo mutengo, nhamba yechikamu, uye zita remutengesi patinopinda zita remukana seLookup_value.

Sezvo deta iri kuiswa muitiro wenguva dzose mubasa rekushanda , tinokwanisa kukopa fomu yekutsvaga iri muchitokisi E2 kumasero F2 neG2.

Sezvo chirevo chacho chinyorwa, Excel ichavandudza nhengo yechipfumi chechikamu muCOLUMN basa (B1) kuratidza nzvimbo itsva yefomu.

Uyewo, Excel haishanduri nheyo yakakwana $ D $ 2 uye iyo inonzi yaive yeTebhesi sezvo shanduro yacho inopihwa.

Pane nzira inopfuura imwe yekukopa deta muExcel, asi zvichida nzira iri nyore ndeyokushandisa Fill Handle .

Tutorial Steps

1. Dzvanya pachitokisi E2 - apo fomu yekutsvaga iripo - kuiita iyo inoshanda masero
2. Isa iyo pointer yeguru pamusoro pekona mutsara muzasi kwekona yekona. Iyo pointer ichashandura kune kumwe kuwedzera chiratidzo " + " - iyi ndiyo yekuzadza
3. Dzvanya pakusara kwebhondi uye shandisai jira rinogadzikana kusvika kune imwe geri G2
4. Bvisa mutsara ye mouse uye seferi F3 inofanira kuva neyese dimensional lookup formula
5. Kana zvakaitwa zvakanaka, masero F2 neG2 anofanira kunge anewo zvakare # N / A kukanganisa kuripo muchitokisi E2

10 pa10

Kupinda Mazano Okuona

Kana imwe shanduro yakakonzerwa yakanyorwa kune masero anodiwa inogona kushandiswa kutora mashoko kubva patafura ye data.

Kuti uite kudaro, chengetedza zita rechimwe chinhu chaunoda kuti uwane muLookup_value cell (D2) uye shandisa ENTER kiyi pane keyboard.

Pane imwe nguva yaitwa, sero rimwe rine rondedzero yekunyora rinofanira kuva nechero yakasiyana yedhesi pamusoro pezvinhu zvezvinhu zvauri kutsvaga.

Tutorial Steps

1. Dzvanya pane cell D2 mubasa rekushanda
2. Tora Widget muchikamu cheD2 uye chengetedza ENTER key pane keyboard
3. Inotevera inotevera inofanira kuratidzwa mumasero E2 kusvika kuG2:
   • E2 - $ 14.76 - mutengo we widget
   • F2 - PN-98769 - chikamu chikamu che widget
   • G2 - Zvikwata Inc. - zita remutengesi nokuda kwemajenya
4. Edzai VLOOKUP array formula iri mberi nekunyora zita remamwe mativi muchikamu cheD2 nekucherechedza migumisiro mumasero E2 kusvika kuG2

Kana mhinduro yekanganiso yakadai se #REF! rinowanikwa mumasero E2, F2, kana G2, urongwa urwu rweVLOOKUP mhosho dzingakanganisa iwe kuti uone kuti chinetso chiripi.