Mae adroddiadau Swyddogaeth Excel VLOOKUP yn declyn pwerus sy'n eich helpu i chwilio am werth penodedig trwy baru ar golofn gyntaf tabl neu ystod yn fertigol ac yna dychwelyd gwerth cyfatebol o golofn arall yn yr un rhes. Er bod VLOOKUP yn hynod ddefnyddiol, weithiau gall fod yn heriol i ddechreuwyr ei ddeall. Nod y tiwtorial hwn yw eich helpu i feistroli VLOOKUP trwy ddarparu esboniad cam wrth gam o'r dadleuon, enghreifftiau defnyddiol ac atebion i wallau cyffredin efallai y byddwch yn dod ar eu traws wrth ddefnyddio'r swyddogaeth VLOOKUP.

• Esboniad cam wrth gam o'r dadleuon
• Cystrawen a dadleuon
• Enghreifftiau VLOOKUP
  Cyfateb union vs cyfateb yn fras   |   Vlookup gydag aml-amodau
• VLOOKUP Gwallau a datrysiadau cyffredin
  # Amherthnasol Gwall   |   #VALUE gwall   |   Gwall #REF   |   Gwerth anghywir

Enghreifftiau VLOOKUP .xlsx Gwallau VLOOKUP .xlsx

Fideos perthnasol

Esboniad cam wrth gam o'r dadleuon

Fel y dangosir yn y llun uchod, defnyddir y swyddogaeth VLOOKUP i ddod o hyd i e-bost yn seiliedig ar rif ID penodol. Byddaf yn awr yn rhoi esboniad manwl o sut i ddefnyddio VLOOKUP yn yr enghraifft hon trwy dorri i lawr bob dadl gam wrth gam.

Cam 1: Dechreuwch y swyddogaeth VLOOKUP

Dewiswch gell (H6 yn yr achos hwn) i allbynnu'r canlyniad, yna dechreuwch y swyddogaeth VLOOKUP trwy deipio'r cynnwys canlynol yn y Bar Fformiwla.

=VLOOKUP(

Cam 2: Nodwch y gwerth chwilio

Yn gyntaf, nodwch y gwerth chwilio (sef yr hyn rydych chi'n edrych amdano) yn y swyddogaeth VLOOKUP. Yma, cyfeiriaf at gell G6 sy'n cynnwys rhif ID penodol 1005.

=VLOOKUP(G6

Cam 3: Nodwch yr arae tabl

Nesaf, nodwch ystod o gelloedd sy'n cynnwys y gwerth rydych chi'n edrych amdano a'r gwerth rydych chi am ei ddychwelyd. Yn yr achos hwn, rwy'n dewis yr ystod B6:E12. Mae'r fformiwla nawr yn ymddangos fel a ganlyn:

=VLOOKUP(G6,B6:E12

=VLOOKUP(G6,$B$6:$E$12

Cam 4: Nodwch y golofn yr ydych am ddychwelyd gwerth ohoni

Yna nodwch y golofn rydych chi am ddychwelyd gwerth ohoni.

Yn yr enghraifft hon, gan fod angen i mi ddychwelyd yr e-bost yn seiliedig ar rif ID, yma rwy'n nodi rhif 4 i ddweud wrth VLOOKUP am ddychwelyd gwerth o bedwaredd golofn yr ystod ddata.

=VLOOKUP(G6,B6:E12,4

Cam 5: Dewch o hyd i gyfatebiaeth fras neu union

Yn olaf, penderfynwch a ydych chi'n chwilio am ornest fras neu union gyfateb.

• I ddod o hyd i union gyfatebiaeth, mae angen i chi ddefnyddio Anghywir fel y ddadl olaf.
• I ddod o hyd i cyfateb yn fras, Defnyddiwch TRUE fel y ddadl olaf, neu gadewch hi yn wag.

Yn yr enghraifft hon, rwy'n defnyddio FALSE ar gyfer cyfateb yn union. Mae'r fformiwla nawr yn edrych fel hyn:

=VLOOKUP(G6,B6:E12,4,FALSE

Pwyswch yr allwedd Enter i gael y canlyniad

Trwy egluro pob dadl fesul un yn yr enghraifft uchod, mae cystrawen a dadleuon swyddogaeth VLOOKUP bellach yn llawer haws i'w deall.

Cystrawen a dadleuon

=VLOOKUP (lookup_value, table_array, col_index, [range_lookup])

• Edrych_gwerth (gofynnol): Y gwerth (gwerth gwirioneddol neu gyfeirnod cell) yr ydych yn chwilio amdano. Cofiwch fod yn rhaid i'r gwerth hwn fod yng ngholofn gyntaf y tabl_arae.
• Tabl_array (gofynnol): Mae ystod o gelloedd yn cynnwys colofn y gwerth am-edrych a cholofn y gwerth dychwelyd.
• col_mynegai (gofynnol): Mae cyfanrif yn cynrychioli rhif y golofn sy'n cynnwys y gwerth dychwelyd. Mae'n dechrau gyda rhif 1 ar gyfer y golofn fwyaf chwith o'r table_array.
• Ystod_lookup (dewisol): Gwerth rhesymegol sy'n penderfynu a ydych am i VLOOKUP ddod o hyd i gyfatebiaeth fras neu union gyfatebiaeth.
  • Gêm fras — Gosodwch y ddadl hon i TRUE, 1 neu ei adael gwag.
    pwysig: I ddarganfod cyfatebiad bras, rhaid trefnu'r gwerthoedd yng ngholofn gyntaf y table_array mewn trefn esgynnol rhag ofn i VLOOKUP ddychwelyd y canlyniad anghywir.
  • Cydweddiad union — Gosodwch y ddadl hon i Anghywir or 0.

Enghreifftiau

Mae'r adran hon yn dangos rhai enghreifftiau i'ch helpu i gael dealltwriaeth fwy cynhwysfawr o swyddogaeth VLOOKUP.

Enghraifft 1: Cydweddiad union yn erbyn cyfatebiaeth fras yn VLOOKUP

Os ydych wedi drysu ynghylch union baru a pharu’n fras wrth ddefnyddio VLOOKUP, gall yr adran hon eich helpu i glirio’r dryswch hwnnw.

Cydweddiad Union yn VLOOKUP

Yn yr enghraifft hon, rydw i'n mynd i ddod o hyd i'r enwau cyfatebol yn seiliedig ar y sgoriau a restrir yn yr ystod E6: E8, felly rydw i'n nodi'r fformiwla ganlynol yng nghell F6 a llusgo'r handlen AutoFill i lawr i F8. Yn y fformiwla hon, nodir y ddadl olaf fel Anghywir i berfformio chwiliad cyfatebol union.

=VLOOKUP(E6,$B$6:$C$12,2,FALSE)

Fodd bynnag, gan nad yw'r sgôr 98 yn bodoli yng ngholofn gyntaf yr ystod ddata, mae VLOOKUP yn dychwelyd canlyniad gwall #N/A.

Paru bras yn VLOOKUP

Dal i ddefnyddio'r enghraifft uchod, os byddwch yn newid y ddadl olaf i TRUE, Bydd VLOOKUP yn perfformio chwiliad cyfatebol bras. Os na chanfyddir cyfatebiaeth, bydd yn dod o hyd i'r gwerth mwyaf nesaf sy'n llai na'r gwerth chwilio ac yn dychwelyd y canlyniad cyfatebol.

=VLOOKUP(E6,$B$6:$C$12,2,TRUE)

Gan nad yw'r sgôr 98 yn bodoli, mae VLOOKUP yn canfod y gwerth mwyaf nesaf sy'n llai na 98, sef 95, ac yn dychwelyd enw'r sgôr 95 fel y canlyniad agosaf.

Enghraifft 2: Defnyddiwch VLOOKUP gyda meini prawf lluosog

Mae'r adran hon yn dangos sut i ddefnyddio VLOOKUP gydag amodau lluosog yn Excel. Fel y dangosir yn y llun isod, os ydych chi'n ceisio dod o hyd i gyflog yn seiliedig ar enw a ddarperir (yng nghell H5) ac adran (yng nghell H6), dilynwch y camau isod i'w wneud.

Cam 1: Ychwanegu colofn cynorthwyydd i gydgatenu'r gwerthoedd o'r colofnau chwilio

Yn yr achos hwn, mae angen i ni greu colofn helpwr i gydgatenu'r gwerthoedd o'r Enw colofn a'r Adran colofn.

1. Ychwanegwch golofn helpwr i'r chwith o'ch ystod data a rhowch bennawd i'r golofn hon. Gweler y sgrinlun:
   
   
2. Yn y golofn helpwr hon, dewiswch y gell gyntaf o dan y pennawd, rhowch y fformiwla ganlynol yn y Bar fformiwla, a'r wasg Rhowch.
   

   =C6&" "&D6

   
   Nodiadau: Yn y fformiwla hon, rydym yn defnyddio ampersand (&) i uno'r testun mewn dwy golofn i gynhyrchu un darn o destun.

   • C6 yw enw cyntaf y Enw colofn i ymuno, D6 yw adran gyntaf y Adran colofn i ymuno.
   • Mae gwerthoedd y ddwy gell hyn wedi'u cydgadwyno â gofod rhyngddynt.
3. Dewiswch y gell canlyniad hon, yna llusgwch y Trin AutoFill i lawr i gymhwyso'r fformiwla hon i gelloedd eraill yn yr un golofn.
   
   

Cam 2: Cymhwyso'r swyddogaeth VLOOKUP gyda'r meini prawf a roddir

Dewiswch gell lle rydych chi am allbynnu'r canlyniad (yma dwi'n dewis I7), nodwch y fformiwla ganlynol yn y Bar fformiwla, ac yna'r wasg Rhowch.

=VLOOKUP(I5& " "&I6,B6:F12,5,FALSE)

Canlyniad

Gwallau ac atebion VLOOKUP cyffredin

Mae'r adran hon yn rhestru'r gwallau cyffredin y gallech ddod ar eu traws wrth ddefnyddio VLOOKUP ac yn darparu'r atebion i'w trwsio.

# N/A gwall yn cael ei ddychwelyd

Y gwall mwyaf cyffredin gyda VLOOKUP yw'r gwall # N/A, sy'n golygu na allai Excel ddod o hyd i'r gwerth yr oeddech yn chwilio amdano. Dyma rai rhesymau pam y gall VLOOKUP ddychwelyd # gwall Amh.

Rheswm 1: Nid yw'r gwerth chwilio yng ngholofn gyntaf y table_array

Un o gyfyngiadau Excel VLOOKUP yw ei fod ond yn caniatáu ichi edrych o'r chwith i'r dde. Felly, rhaid i'r gwerthoedd chwilio fod yng ngholofn gyntaf y table_array.

Fel y dangosir yn y sgrin isod, rwyf am ddychwelyd enw yn seiliedig ar deitl y swydd a roddwyd. Yma mae'r gwerth chwilio (rheolwr gwerthiannau) yn ail golofn y table_array ac mae'r gwerth dychwelyd i'r chwith o'r golofn chwilio, felly mae VLOOKUP yn dychwelyd gwall #N/A.

Solutions

Gallwch gymhwyso unrhyw un o'r atebion canlynol i drwsio'r gwall hwn.

• Aildrefnwch y colofnau
  
  Gallwch aildrefnu'r colofnau i osod y golofn chwilio yng ngholofn gyntaf y table_array.
• Defnyddiwch y ffwythiannau MYNEGAI a MATCH gyda'i gilydd
  
  Yma rydym yn defnyddio'r swyddogaethau MYNEGAI a MATCH gyda'i gilydd fel dewis amgen i VLOOKUP i ddatrys y broblem hon.

  =INDEX(B6:B12,MATCH(F6,C6:C12,0))

  
• Defnyddiwch y swyddogaeth XLOOKUP (ar gael yn Excel 365, Excel 2021 a fersiynau diweddarach)
  

  =XLOOKUP(F6,C6:C12,B6:B12)

Rheswm 2: Nid yw'r gwerth chwilio i'w gael yn y golofn chwilio (union gyfatebiaeth)

Un o'r rhesymau mwyaf cyffredin pam mae VLOOKUP yn dychwelyd gwall #N/A yw oherwydd nad yw'r gwerth rydych chi'n edrych amdano wedi'i ganfod.

Fel y dangosir yn yr enghraifft isod, rydym yn mynd i ddod o hyd i'r enw yn seiliedig ar y sgôr a roddwyd o 98 yn E6. Fodd bynnag, nid yw'r sgôr hwn yn bodoli yng ngholofn gyntaf yr ystod ddata, felly mae VLOOKUP yn dychwelyd canlyniad gwall # N/A.

Solutions

I drwsio'r gwall hwn, gallwch chi roi cynnig ar un o'r atebion canlynol.

• Os ydych chi eisiau chwiliadau VLOOKUP am y gwerth mwyaf nesaf sy'n llai na'r gwerth chwilio, newidiwch y ddadl olaf Anghywir (union gyfatebiaeth) i TRUE (cyfateb yn fras). Am fwy o wybodaeth, gweler Enghraifft 1: Cydweddiad union yn erbyn cyfatebiad bras gan ddefnyddio VLOOKUP.
• Er mwyn osgoi newid y ddadl olaf a chael nodyn atgoffa rhag ofn na chanfyddir y gwerth chwilio, gallwch amgáu'r swyddogaeth VLOOKUP o fewn y ffwythiant IFERROR:
  

  =IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")

Rheswm 3: Mae'r gwerth chwilio yn llai na'r gwerth lleiaf yn y golofn chwilio (cyfatebiaeth fras)

Fel y dangosir yn y sgrin isod, rydych chi'n perfformio chwiliad cyfatebol bras. Mae'r gwerth yr ydych yn chwilio amdano (y rhif adnabod 1001 yn yr achos hwn) yn llai na'r gwerth lleiaf 1002 yn y golofn chwilio, felly, mae VLOOKUP yn dychwelyd gwall #N/A.

Solutions

Dyma ddau ateb i chi.

• Sicrhewch fod y gwerth chwilio yn fwy neu'n hafal i'r gwerth lleiaf yn y golofn chwilio.
• Os ydych chi am i Excel eich atgoffa na chanfuwyd y gwerth chwilio, nythu'r swyddogaeth VLOOKUP yn y swyddogaeth IFERROR fel a ganlyn:
  

  =IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")

Rheswm 4: Mae rhifau yn cael eu fformatio fel testun

Fel y gwelwch yn y sgrin isod, mae canlyniad gwall # N/A yn yr enghraifft hon oherwydd diffyg cyfatebiaeth data rhwng y gell chwilio (G6) a cholofn edrych (B6: B12) y tabl gwreiddiol. Yma mae'r gwerth yn G6 yn rhif, a'r gwerthoedd yn yr ystod B6:B12 yw rhifau wedi'u fformatio fel testun.

Solutions

I ddatrys y broblem hon, mae angen i chi drosi'r gwerth chwilio yn ôl i rif. Dyma ddau ddull i chi.

• Cymhwyswch y nodwedd Trosi i Rif
  
  Cliciwch ar y gell rydych chi am drosi'r testun i rif, dewiswch y botwm hwn  wrth ymyl y gell ac yna dewiswch Trosi i Rif.
  
• Defnyddiwch offeryn defnyddiol i drosi swp rhwng testun a rhif
  
  Mae adroddiadau Trosi rhwng Testun a Rhif nodwedd o Kutools ar gyfer Excel yn eich helpu i drosi ystod o gelloedd yn hawdd o destun i rif ac i'r gwrthwyneb. Sicrhewch dreial 30 diwrnod llawn sylw am ddim nawr!
  

Rheswm 5: Nid yw'r table_array yn gyson wrth lusgo'r fformiwla VLOOKUP i gelloedd eraill

Fel y dangosir yn y sgrin isod, mae dau werth chwilio yn E6 ac E7. Ar ôl cael y canlyniad cyntaf yn F6, llusgwch y fformiwla VLOOKUP o gell F6 i F7, dychwelodd canlyniad gwall # N/A. Mae hynny oherwydd bod y cyfeiriadau cell (B6: C12) yn gymharol yn ddiofyn, ac wedi'u haddasu wrth i chi symud i lawr trwy'r rhesi. Mae'r arae tablau wedi'i symud i lawr i B7:C13, nad yw bellach yn cynnwys y sgôr chwilio 73.

Ateb

Mae angen i chi gloi'r arae bwrdd i'w gadw'n gyson trwy ychwanegu a $ llofnodwch cyn y rhesi a'r colofnau yn y cyfeiriadau cell. I wybod mwy am gyfeirio absoliwt yn Excel, edrychwch ar y tiwtorial hwn: Cyfeiriad absoliwt Excel (sut i wneud a defnyddio).

#VALUE gwall yn cael ei ddychwelyd

Gall yr amodau canlynol achosi i VLOOKUP ddychwelyd canlyniad gwall #VALUE.

Rheswm 1: Mae'r gwerth chwilio yn fwy na 255 nod

Fel y dangosir yn y llun isod, mae'r gwerth chwilio yng nghell H4 yn fwy na 255 nod, felly mae VLOOKUP yn dychwelyd canlyniad gwall #VALUE.

Solutions

I weithio o amgylch y cyfyngiad hwn, gallwch gymhwyso swyddogaeth chwilio wahanol a all drin llinynnau hirach. Rhowch gynnig ar un o'r fformiwlâu canlynol.

• MYNEGAI a MATCH:
  

  =INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=H4, 0), 0))

  
• Swyddogaeth XLOOKUP (ar gael yn Excel 365, Excel 2021 a fersiynau diweddarach):
  

  =XLOOKUP(H4,B5:B11,E5:E11)

Rheswm 2: Mae dadl col_index yn llai nag 1

Mae mynegai'r golofn yn pennu rhif y golofn yn yr arae tabl sy'n cynnwys y gwerth rydych am ei ddychwelyd. Rhaid i'r ddadl hon fod yn rhif positif sy'n cyfateb i golofn ddilys yn yr arae tabl.

Os rhowch fynegai colofn sy'n llai nag 1 (hy, sero neu negyddol), ni fydd VLOOKUP yn gallu lleoli'r golofn yn yr arae tabl.

Ateb

I ddatrys y mater hwn, gwnewch yn siŵr bod y ddadl mynegai colofn yn eich fformiwla VLOOKUP yn rhif positif sy'n cyfateb i golofn ddilys yn yr arae tabl.

Gwall #REF yn cael ei ddychwelyd

Mae'r adran hon yn rhestru un rheswm pam mae VLOOKUP yn dychwelyd gwall #REF ac yn darparu atebion i'r broblem hon.

Rheswm: Mae'r arg col_index yn fwy na nifer y colofnau

Fel y gwelwch yn y llun isod, dim ond 4 colofn sydd gan yr arae tabl. Fodd bynnag, y mynegai colofn a nodwyd gennych yn y fformiwla VLOOKUP yw 5, sy'n fwy na nifer y colofnau yn yr arae tabl. O ganlyniad, ni fydd VLOOKUP yn gallu dod o hyd i'r golofn a bydd yn dychwelyd gwall #REF yn y pen draw.

Solutions

• Nodwch rif colofn cywir
  Sicrhewch fod yr arg mynegai colofn yn eich fformiwla VLOOKUP yn rhif sy'n cyfateb i golofn ddilys yn yr arae tabl.
• Sicrhewch rif y golofn yn awtomatig yn seiliedig ar bennawd y golofn penodedig
  Os yw'r tabl yn cynnwys llawer o golofnau, efallai y byddwch yn cael trafferth pennu rhif mynegai cywir y golofn. Yma, gallwch chi nythu'r swyddogaeth MATCH yn y swyddogaeth VLOOKUP i ddod o hyd i leoliad y golofn yn seiliedig ar bennawd colofn certian.

  =VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)

  Nodyn: Yn y fformiwla uchod, mae'r MATCH ("E-bost", B5:E5, 0) defnyddir ffwythiant i gael rhif colofn y "E-bost" " colofn yn yr ystod dyddiad B6:E12. Yma y canlyniad yw 4, sy'n cael ei ddefnyddio fel col_index yn y ffwythiant VLOOKUP.

Gwerth anghywir yn cael ei ddychwelyd

Os gwelwch nad yw VLOOKUP yn dychwelyd y canlyniad cywir, efallai mai'r rhesymau canlynol sy'n ei achosi

Rheswm 1: Nid yw'r golofn chwilio wedi'i didoli mewn trefn esgynnol

Os ydych wedi gosod y ddadl olaf i TRUE (neu ei adael yn wag) ar gyfer cyfatebiad bras, ac nid yw'r golofn chwilio wedi'i didoli mewn trefn esgynnol, gall y gwerth canlyniadol fod yn anghywir.

Ateb

Gall trefnwch y golofn chwilio mewn trefn esgynnol eich helpu i ddatrys y broblem hon. I wneud hyn, dilynwch y camau isod:

1. Dewiswch y celloedd data yn y golofn chwilio, ewch i'r Dyddiad tab, cliciwch Trefnu Lleiaf i'r Mwyaf yn y Trefnu a Hidlo grŵp.
2. Yn y Rhybudd Trefnu blwch deialog, dewiswch y Ehangu'r dewis opsiwn, a chlicio OK.

Rheswm 2: Mae colofn yn cael ei fewnosod neu ei ddileu

Fel y dangosir yn y sgrin isod, mae'r gwerth yr oeddwn am ei ddychwelyd yn wreiddiol ym mhedwaredd golofn yr arae tabl, felly rwy'n nodi'r rhif col_index fel 4. Wrth i golofn newydd gael ei mewnosod, mae'r golofn canlyniad yn dod yn bumed colofn y tabl arae, gan achosi VLOOKUP i ddychwelyd y canlyniad o golofn anghywir.

Solutions

Dyma ddau ateb i chi.

• Gallwch newid rhif mynegai'r golofn â llaw i gyd-fynd â lleoliad y golofn dychwelyd. Dylid newid y fformiwla yma i:

  =VLOOKUP(H6,B6:F12,5,FALSE)

• Os ydych chi bob amser eisiau dychwelyd y canlyniad o golofn certian, fel y golofn E-bost yn yr enghraifft hon. Gall y fformiwla ganlynol helpu i gyd-fynd yn awtomatig â mynegai'r golofn yn seiliedig ar bennawd y golofn a roddir, ni waeth a yw colofnau'n cael eu mewnosod neu eu tynnu o'r arae tabl.

  =VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)

Nodiadau swyddogaeth eraill

• Mae VLOOKUP yn chwilio am werth o'r chwith i'r dde yn unig.
  Mae'r gwerth chwilio yn y golofn fwyaf chwith, a dylai gwerth y canlyniad fod mewn unrhyw golofn i'r dde o'r golofn edrych i fyny.
• Os byddwch yn gadael yr arg olaf yn wag, mae VLOOKUP yn defnyddio cyfatebiaeth fras yn ddiofyn.
• Mae VLOOKUP yn perfformio chwiliad achos ansensitif.
• Ar gyfer gemau lluosog, mae VLOOKUP yn dychwelyd y gêm gyntaf y mae'n ei darganfod yn yr arae tablau yn unig, yn seiliedig ar drefn y rhesi yn yr arae tabl.

Erthyglau Perthnasol