Kushandisa VLOOKUP basa kutsiva kukosha

Ndiani ane usimbe kana kuti asina nguva yekuverenga - tarisa vhidhiyo. Tsanangudzo uye nuances mune zvinyorwa pazasi.

Kugadziriswa kwedambudziko

Saka, tine matafura maviri - tafura yekuraira и runyorwa rwemutengo:

Iro basa nderekutsiva mitengo kubva pamutengo wemitengo mutafura yeodha otomatiki, uchitarisa pazita rechigadzirwa kuitira kuti gare gare iwe ugone kuverenga mutengo.

mhinduro

MuExcel function set, pasi pechikamu References uye arrays (Kutarisa uye kunongedzera) pane basa VPR (VLOOKUP).Iri basa rinotsvaga kukosha kwakapihwa (mumuenzaniso wedu, iri ishoko rekuti "maapuro") mumutsara wekuruboshwe wetafura yakatsanangurwa (runyorwa rwemitengo) ichifamba kubva kumusoro ichienda pasi uye, kana waiwana, inoratidza zviri mukati mesero riri pedyo. (23 rubles) .Schematically, kushanda kwebasa iri kunogona kumiririrwa Saka:

Kuti ureruke kwekuwedzera kushandiswa kwebasa, ita chinhu chimwe kamwechete - ipa huwandu hwemasero mumutengo rondedzero zita rako pachako. Kuti uite izvi, sarudza maseru ese emutengo rondedzero kunze kwe "musoro" (G3: H19), sarudza kubva mumenyu. Pinza – Zita – Govera (Isa - Zita - Define) kana kudhinda CTRL + F3 uye isa chero zita (hapana nzvimbo) senge mutengo… Zvino, mune ramangwana, unogona kushandisa zita iri kubatanidza kune mutengo runyorwa.

Iye zvino tinoshandisa basa VPR… Sarudza sero painopinzwa (D3) wovhura tab Formulas – Basa Kupinza (Mafomula - Isa Basa)… Muchikamu References uye arrays (Kutsvaga uye Reference) tsvaga basa VPR (VLOOKUP) uye tigoshingairira OK… Hwindo rekuisa nharo dzebasa richaonekwa:

Kushandisa VLOOKUP basa kutsiva kukosha

Isu tinoazadza ivo vachitevera:

  • Ukoshi hunodiwa (Kukosha kwekutsvaga) - zita rechigadzirwa icho basa rinofanira kuwana mumutsara wekuruboshwe wemutengo wemutengo. Muchiitiko chedu, izwi rokuti "maapuro" kubva muchitokisi B3.
  • tafura (Table Array) - tafura kubva kwainoda kukosha uXNUMXbuXNUMXbare yakatorwa, kureva, yedu runyorwa rwemitengo. Nekureva, tinoshandisa zita redu pachedu "Mutengo" wakapihwa kare. Kana iwe usina kupa zita, unogona kungosarudza tafura, asi usakanganwe kudzvanya bhatani F4kubaya chinongedzo nezviratidzo zvemadhora, nekuti zvikasadaro, inotsvedza pasi kana uchikopa fomura yedu pasi kune mamwe ese maseru mukoramu D3: D30.
  • Column_nhamba (Column index number) - serial nhamba (kwete tsamba!) Yekoramu mune mutengo runyorwa kubva kwatinotora mitengo yemitengo. Koramu yekutanga yerondedzero yematengo ine mazita akaverengerwa 1, saka isu tinoda mutengo kubva mukoramu ine nhamba 2.
  • interval_lookup (Range Lookup) - maitiro maviri chete anogona kupinzwa mundima iyi: FALSE kana CHOKWADI:
      • Kana kukosha kwaiswa 0 or KUNYEPA (NHEMA), zvino izvi zvinoreva kuti kutsvaga chete kunobvumirwa kunyatsoenderana, kureva kuti kana basa racho rikasawana chinhu chisingaenzaniswi chinotsanangurwa mutafura yekuraira mumutengo wemutengo (kana "Coconut" yakapinda, somuenzaniso), ichaita #N / ​​A (hapana data) kukanganisa.
      • Kana kukosha kwaiswa 1 or TRUE (CHOKWADI), zvino izvi zvinoreva kuti iwe unobvumira kutsvaga kwete kweiyo chaiyo, asi kuenzanisa, kureva munyaya ye "coconut", basa racho richaedza kutsvaga chigadzirwa chine zita rinenge riri pedyo ne "coconut" uye kudzorera mutengo wezita iri. Muzviitiko zvakawanda, fungidziro yakadaro inogona kuita chikiribidi pamushandisi nekutsiva kukosha kwechigadzirwa chisina kunaka chaivepo! Saka kune mazhinji echokwadi matambudziko ebhizinesi, kufungidzira kutsvaga kuri nani kusatendera. Kusarudzika kuri patiri kutsvaga manhamba uye kwete zvinyorwa - semuenzaniso, kana tichiverenga Danho Redhisheni.

Zvose! Inoramba ichidzvanya OK uye kopira iyo yakapinda basa kune iyo column yese.

# N / A zvikanganiso uye kudzvinyirira kwavo

basa VPR (VLOOKUP) inodzosera #N/A kukanganisa (#N/A) kana a:

  • Kutsvaga chaiko kwakagoneswa (nharo Interval view = 0) uye zita raunoda harimo tafura.
  • Kutsvaga kwakashata kunosanganisira (Interval view = 1), asi tafura, umo kutsvaga kuri kuitika hakuna kurongwa mukukwira kwemazita.
  • Chimiro chesero uko kukosha kunodiwa kwezita kunobva (semuenzaniso, B3 kwatiri) uye mafomati emasero ekoramu yekutanga (F3: F19) yetafura akasiyana (semuenzaniso, nhamba uye zvinyorwa. ) Nyaya iyi inonyanya kushandiswa kana uchishandisa nhamba dzenhamba (nhamba dzeakaundi, zviziviso, mazuva, nezvimwewo) panzvimbo yemazita ezvinyorwa. Muchiitiko chino, unogona kushandisa mabasa Ч и TEXT kushandura mafomu e data. Ichaita seizvi:

    =VLOOKUP(TEXT(B3),mutengo,0)

    Unogona kuverenga zvakawanda pamusoro peizvi pano.

  • Basa racho harigoni kuwana kukosha kunodiwa nokuti kodhi ine nzvimbo kana zvisingaoneki zvisingadhindwe mavara (kuputsa kwemutsara, nezvimwewo). Muchiitiko ichi, unogona kushandisa zvinyorwa zvinyorwa TRIM (TRIM) и PRINDA(CLEAN) kuvabvisa:

    =VLOOKUP(TRIMSPACES(CLEAN(B3)),mutengo,0)

    =VLOOKUP(TRIM(CLEAN(B3));mutengo;0)

Kudzvanya meseji yezvikanganiso # N / A (#N/A) mumamiriro ezvinhu apo basa racho harigoni kuwana mutsara chaiwo, unogona kushandisa basa racho IFERROR (IFERROR)... Saka, semuenzaniso, chivakwa ichi chinobata chero kukanganisa kwakagadzirwa neVLOOKUP uye kuchitsiva nemazero:

= IFERROR (VLOOKUP (B3, mutengo, 2, 0), 0)

= IFERROR (VLOOKUP (B3; mutengo; 2; 0); 0)

PS

Kana iwe uchida kuburitsa kwete imwechete kukosha, asi iyo yese seti kamwechete (kana paine akati wandei akasiyana), ipapo iwe uchafanirwa kushamanize neiyo array formula. kana kushandisa iyo itsva XLOOKUP chimiro kubva kuHofisi 365.

 

  • Shanduro yakavandudzwa yeVLOOKUP basa (VLOOKUP 2).
  • Kukurumidza kuverenga kwedanho (renji) kuderedzwa uchishandisa iyo VLOOKUP basa.
  • Maitiro ekugadzira "kuruboshwe VLOOKUP" uchishandisa INDEX uye MATCH mabasa
  • Maitiro ekushandisa VLOOKUP basa rekuzadza mafomu nedata kubva pane iyo rondedzero
  • Maitiro ekuburitsa kwete yekutanga, asi ese kukosha kubva patafura kamwechete
  • VLOOKUP2 uye VLOOKUP3 inoshanda kubva kune PLEX yekuwedzera

 

Leave a Reply