An Feidhm Excel VLOOKUP Is uirlis chumhachtach é a chuidíonn leat luach sonraithe a chuardach trí mheaitseáil ar an gcéad cholún tábla nó raon go hingearach agus ansin luach comhfhreagrach a thabhairt ar ais ó cholún eile sa tsraith chéanna. Cé go bhfuil VLOOKUP thar a bheith úsáideach, uaireanta bíonn sé dúshlánach é a thuiscint do thosaitheoirí. Tá sé mar aidhm ag an teagasc seo cabhrú leat VLOOKUP a mháistir trí sholáthar míniú céim ar chéim ar na hargóintí, samplaí úsáideacha agus réitigh ar earráidí coitianta seans go dtiocfaidh tú trasna agus an fheidhm VLOOKUP á úsáid agat.

• Míniú céim ar chéim ar argóintí
• Comhréir agus argóintí
• Samplaí VLOOKUP
  Match beacht vs. neasmheaitseáil   |   Vlookup le il-coinníollacha
• VLOOKUP Earráidí & réitigh choitianta
  # N / A earráid   |   Earráid #VALUE   |   Earráid #REF   |   Luach mícheart

Samplaí VLOOKUP .xlsx Earráidí VLOOKUP .xlsx

Related Videos

Míniú céim ar chéim ar argóintí

Mar a thaispeántar sa screenshot thuas, úsáidtear an fheidhm VLOOKUP chun ríomhphost a aimsiú bunaithe ar uimhir aitheantais tugtha. Tabharfaidh mé míniú mionsonraithe anois ar conas VLOOKUP a úsáid sa sampla seo trí gach argóint a bhriseadh síos céim ar chéim.

Céim 1: Tosaigh an fheidhm VLOOKUP

Roghnaigh cill (H6 sa chás seo) chun an toradh a aschur, ansin cuir tús leis an bhfeidhm VLOOKUP tríd an ábhar seo a leanas a chlóscríobh sa Bar Foirmle.

=VLOOKUP(

Céim 2: Sonraigh an luach cuardaigh

Ar dtús, sonraigh an luach cuardaigh (is é sin an méid atá á lorg agat) san fheidhm VLOOKUP. Anseo, déanaim tagairt do chill G6 ina bhfuil uimhir aitheantais áirithe 1005.

=VLOOKUP(G6

Céim 3: Sonraigh an t-eagar tábla

Ansin, sonraigh raon cealla ina bhfuil an luach atá á lorg agat agus an luach a theastaíonn uait a thabhairt ar ais. Sa chás seo, roghnaigh mé an raon B6:E12. Tá an fhoirmle le feiceáil anois mar seo a leanas:

=VLOOKUP(G6,B6:E12

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

Céim 4: Sonraigh an colún óna dteastaíonn uait luach a thabhairt ar ais

Ansin sonraigh an colún ar mhaith leat luach a thabhairt ar ais uaidh.

Sa sampla seo, mar is gá dom an ríomhphost a chur ar ais bunaithe ar uimhir aitheantais, anseo cuirim uimhir 4 isteach chun a insint do VLOOKUP luach a thabhairt ar ais ón gceathrú colún den raon sonraí.

=VLOOKUP(G6,B6:E12,4

Céim 5: Faigh neas nó meaitseáil chruinn

Ar deireadh, cinntigh an bhfuil tú ag lorg neasmheaitseála nó meaitseála beacht.

• Chun teacht ar comhoiriúnú cruinn, ní mór duit a úsáid FALSE mar an argóint dheireanach.
• Chun teacht ar comhoiriúnú thart, Úsáid TRUE mar an argóint dheireanach, nó fág bán é.

Sa sampla seo, úsáidim BRÉAGACH le haghaidh meaitseáil chruinn. Breathnaíonn an fhoirmle mar seo anois:

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

Brúigh an eochair Enter chun an toradh a fháil

Trí gach argóint a mhíniú ceann ar cheann sa sampla thuas, tá sé i bhfad níos éasca anois comhréir agus argóintí na feidhme VLOOKUP a thuiscint.

Comhréir agus argóintí

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

• Cuardach_luach (riachtanach): An luach (fíorluach nó tagairt cille) atá á lorg agat. Cuimhnigh nach mór an luach seo a bheith sa chéad cholún den table_array.
• Tábla_eagar (riachtanach): Tá colún an luach cuardaigh agus colún an luach tuairisceáin i raon cealla.
• Col_innéacs (riachtanach): Seasann slánuimhir uimhir an cholúin ina bhfuil an luach tuairisceáin. Tosaíonn sé le huimhir 1 don cholún is mó ar chlé den table_array.
• Raon_lookup (roghnach): Luach loighciúil a chinneann an dteastaíonn uait go bhfaighidh VLOOKUP neasmheaitseáil nó meaitseáil beacht.
  • Meaitseáil thart - Socraigh an argóint seo TRUE, 1 nó é a fhágáil bán.
    tábhachtach: Chun neasmheaitseáil a aimsiú, ní mór na luachanna sa chéad cholún den table_array a shórtáil in ord ardaitheach ar eagla go dtabharfaidh VLOOKUP an toradh mícheart ar ais.
  • Cluiche díreach - Socraigh an argóint seo FALSE or 0.

Samplaí

Léiríonn an rannán seo roinnt samplaí a chuideoidh leat tuiscint níos cuimsithí a bheith agat ar fheidhm VLOOKUP.

Sampla 1: Meaitseáil beacht vs. neasmheaitseáil in VLOOKUP

Má tá mearbhall ort maidir le meaitseáil bheacht agus neasmheaitseáil agus VLOOKUP in úsáid agat, is féidir leis an rannán seo cabhrú leat an mearbhall sin a ghlanadh.

Meaitseáil Beacht i VLOOKUP

Sa sampla seo, táim chun na hainmneacha comhfhreagracha a fháil bunaithe ar na scóir atá liostaithe sa raon E6: E8, mar sin cuirim an fhoirmle seo a leanas isteach i gcill F6 agus tarraing an láimhseáil AutoFill síos go F8. Sa fhoirmle seo, sonraítear an argóint dheireanach mar FALSE chun cuardach cruinn a dhéanamh.

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

Mar sin féin, toisc nach bhfuil an scór 98 sa chéad cholún den raon sonraí, cuireann VLOOKUP toradh earráide #N/A ar ais.

Meaitseáil neas i VLOOKUP

Fós ag baint úsáide as an sampla thuas, má athraíonn tú an argóint dheireanach go TRUE, déanfaidh VLOOKUP neas-lorg meaitseála. Mura bhfuarthas meaitseáil, gheobhaidh sé an chéad luach eile is lú ná an luach cuardaigh agus seolfaidh sé an toradh comhfhreagrach.

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

Ós rud é nach bhfuil an scór 98 ann, aimsíonn VLOOKUP an chéad luach eile is mó atá níos lú ná 98, is é sin 95, agus cuireann sé ainm an scór 95 ar ais mar an toradh is gaire.

Sampla 2: Bain úsáid as VLOOKUP le critéir iolracha

Léiríonn an chuid seo conas VLOOKUP a úsáid le coinníollacha iolracha in Excel. Mar a thaispeántar sa screenshot thíos, má tá tú ag iarraidh tuarastal a aimsiú bunaithe ar ainm a thugtar (i gcill H5) agus roinn (i gcill H6), lean na céimeanna thíos chun é a dhéanamh.

Céim 1: Cuir colún cúntóir leis chun na luachanna ó na colúin cuardaigh a chomhcheangal

Sa chás seo, ní mór dúinn colún cúntóir a chruthú chun na luachanna ó na Ainm colún agus an Roinn colún.

1. Cuir colún cúntóir leis ar thaobh na láimhe clé de do raon sonraí agus tabhair ceanntásc don cholún seo. Féach ar an scáileán:
   
   
2. Sa cholún cúntóir seo, roghnaigh an chéad chill faoin gceanntásc, cuir isteach an fhoirmle seo a leanas sa Barra foirmle, agus brúigh Iontráil.
   

   =C6&" "&D6

   
   nótaí: Sa fhoirmle seo, úsáidimid ampersand (&) chun an téacs a cheangal in dhá cholún chun píosa amháin téacs a tháirgeadh.

   • C6 is an chéad ainm ar an Ainm colún le páirt a ghlacadh, D6 Is é an chéad roinn den Roinn colún le páirt a ghlacadh.
   • Tá luachanna an dá chill seo comhtháite le spás eatarthu.
3. Roghnaigh an chill toradh seo, ansin tarraing an Láimhseáil AutoFill síos chun an fhoirmle seo a chur i bhfeidhm ar chealla eile sa cholún céanna.
   
   

Céim 2: Cuir an fheidhm VLOOKUP i bhfeidhm leis na critéir a thugtar

Roghnaigh cill inar mian leat an toradh a aschur (anseo roghnaigh mé I7), cuir isteach an fhoirmle seo a leanas sa Barra foirmle, agus ansin brúigh Iontráil.

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

Toradh

Earráidí agus réitigh coitianta VLOOKUP

Liostaíonn an chuid seo na hearráidí coitianta a d’fhéadfadh teacht ort agus tú ag úsáid VLOOKUP agus soláthraíonn sé na réitigh chun iad a cheartú.

#N/B earráid á cur ar ais

Is é an earráid is coitianta le VLOOKUP ná an earráid #N/A, rud a chiallaíonn nach bhféadfadh Excel an luach a bhí á lorg agat a fháil. Seo roinnt cúiseanna a bhféadfadh VLOOKUP earráid #N/A a thabhairt ar ais.

Cúis 1: Níl an luach cuardaigh sa chéad cholún den table_array

Ceann de na teorainneacha atá ag Excel VLOOKUP ná nach ligeann sé duit ach breathnú ó chlé go deas. Mar sin, caithfidh na luachanna cuardaigh a bheith sa chéad cholún den table_array.

Mar a thaispeántar sa scáileán thíos, ba mhaith liom ainm a thabhairt ar ais bunaithe ar theideal an phoist a tugadh. Seo an luach cuardaigh (díolacháin bainisteoir) sa dara colún den table_array agus tá an luach tuairisceáin ar thaobh na láimhe clé den cholún cuardaigh, mar sin filleann VLOOKUP earráid #N/A.

Solutions

Is féidir leat aon cheann de na réitigh seo a leanas a chur i bhfeidhm chun an earráid seo a réiteach.

• Na colúin a athshocrú
  
  Is féidir leat na colúin a athshocrú chun an colún cuardaigh a chur sa chéad cholún den table_array.
• Úsáid na feidhmeanna INDEX agus MATCH le chéile
  
  Anseo úsáidimid na feidhmeanna INDEX agus MATCH le chéile mar mhalairt ar VLOOKUP chun an fhadhb seo a réiteach.

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

  
• Úsáid an fheidhm XLOOKUP (ar fáil in Excel 365, Excel 2021 agus leaganacha níos déanaí)
  

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

Cúis 2: Níl an luach cuardaigh le fáil sa cholún cuardaigh (comhoiriúnach beacht)

Ceann de na cúiseanna is coitianta a dtugann VLOOKUP earráid #N/A ar ais ná toisc nach bhfuarthas an luach atá á lorg agat.

Mar a léirítear sa sampla thíos, táimid chun an t-ainm a fháil bunaithe ar an scór a thugtar de 98 in E6. Mar sin féin, níl an scór seo sa chéad cholún den raon sonraí, mar sin seolann VLOOKUP toradh earráide #N/A.

Solutions

Chun an earráid seo a réiteach, is féidir leat ceann de na réitigh seo a leanas a thriail.

• Más mian leat cuardaigh VLOOKUP don chéad luach eile is lú ná an luach cuardaigh, athraigh an argóint dheireanach FALSE (meaitseáil cruinn) go TRUE (meastachán). Le haghaidh tuilleadh eolais, féach le do thoil Sampla 1: Meaitseáil beacht vs. neasmheaitseáil ag úsáid VLOOKUP.
• Chun an argóint dheireanach a sheachaint agus meabhrúchán a fháil ar eagla nach bhfaightear an luach cuardaigh, is féidir leat an fheidhm VLOOKUP a chur isteach san fheidhm IFERROR:
  

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

Cúis 3: Tá an luach cuardaigh níos lú ná an luach is lú sa cholún cuardaigh (neasmheaitseáil)

Mar a thaispeántar sa screenshot thíos, tá tú ag déanamh neas-lorg meaitseála. Tá an luach atá á lorg agat (an uimhir aitheantais 1001 sa chás seo) níos lú ná an luach is lú 1002 sa cholún cuardaigh, mar sin, filleann VLOOKUP earráid #N/A.

Solutions

Seo dhá réiteach duit.

• Cinntigh go bhfuil an luach cuardaigh níos mó ná nó cothrom leis an luach is lú sa cholún cuardaigh.
• Más mian leat go gcuirfeadh Excel i gcuimhne duit nár aimsíodh an luach cuardaigh, neadaigh an fheidhm VLOOKUP san fheidhm IFERROR mar seo a leanas:
  

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

Cúis 4: Déantar uimhreacha a fhormáidiú mar théacs

Mar a fheiceann tú sa screenshot thíos, tá toradh na hearráide #N/A sa sampla seo mar gheall ar mhímheaitseáil sonraí idir an chill cuardaigh (G6) agus an colún cuardaigh (B6:B12) den tábla bunaidh. Anseo is uimhir é an luach i G6, agus is iad na luachanna sa raon B6:B12 ná uimhreacha atá formáidithe mar théacs.

Solutions

Chun an fhadhb seo a réiteach, ní mór duit an luach cuardaigh a thiontú ar ais go huimhir. Seo dhá mhodh duit.

• Cuir an ghné Tiontaigh go Uimhir i bhfeidhm
  
  Cliceáil ar an gcill ar mhaith leat an téacs a thiontú ina uimhir, roghnaigh an cnaipe seo  in aice leis an gcill agus ansin roghnaigh Tiontaigh go Uimhir.
  
• Cuir uirlis áisiúil i bhfeidhm chun baisc a thiontú idir téacs agus uimhir
  
  An Tiontaigh idir Téacs agus Uimhir gné de Kutools le haghaidh Excel cabhraíonn sé leat raon cealla a thiontú go héasca ó théacs go uimhir agus vice versa. Faigh triail saor in aisce 30-lá iomlán anois!
  

Cúis 5: Níl an table_array seasmhach agus an fhoirmle VLOOKUP á tharraingt chuig cealla eile

Mar a thaispeántar sa screenshot thíos, tá dhá luach cuardaigh in E6 agus E7. Tar éis duit an chéad toradh a fháil i F6, tarraing an fhoirmle VLOOKUP ó chill F6 go F7, tháinig toradh earráide #N/A ar ais. Sin toisc go bhfuil na tagairtí cille (B6:C12) coibhneasta de réir réamhshocraithe, agus coigeartaithe de réir mar a bhogann tú síos trí na sraitheanna. Bogadh an t-eagar tábla síos go B7:C13, nach bhfuil an scór cuardaigh 73 ann a thuilleadh.

réiteach

Ní mór duit an t-eagar tábla a ghlasáil chun é a choinneáil tairiseach trí a $ sínigh roimh na sraitheanna agus na colúin sna tagairtí cille. Chun tuilleadh eolais a fháil faoi thagairt iomlán in Excel, féach ar an rang teagaisc seo: Tagairt iomlán Excel (conas a dhéanamh agus a úsáid).

Earráid #VALUE á seoladh ar ais

Seans go dtabharfaidh VLOOKUP toradh earráide #VALUE ar ais de bharr na gcoinníollacha seo a leanas.

Cúis 1: Sáraíonn an luach cuardaigh 255 carachtar

Mar a thaispeántar sa screenshot thíos, sáraíonn an luach cuardaigh i gcill H4 255 carachtar, mar sin cuireann VLOOKUP toradh earráide #VALUE ar ais.

Solutions

Chun oibriú timpeall ar an teorannú seo, is féidir leat feidhm chuardaigh eile a chur i bhfeidhm a fhéadfaidh teaghráin níos faide a láimhseáil. Bain triail as ceann amháin de na foirmlí seo a leanas.

• INDEX agus MATCH:
  

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

  
• Feidhm XLOOKUP (ar fáil in Excel 365, Excel 2021 agus leaganacha níos déanaí):
  

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

Cúis 2: Tá an argóint col_index níos lú ná 1

Sonraíonn an t-innéacs colún uimhir an cholúin san eagar táblaí ina bhfuil an luach is mian leat a thabhairt ar ais. Caithfidh an argóint seo a bheith ina uimhir dheimhneach a fhreagraíonn do cholún bailí san eagar táblaí.

Má chuireann tú isteach innéacs colún atá níos lú ná 1 (.i. nialas nó diúltach), ní bheidh VLOOKUP in ann an colún a aimsiú san eagar tábla.

réiteach

Chun an cheist seo a réiteach, cinntigh gur uimhir dheimhneach í an argóint innéacs colúin i d’fhoirmle VLOOKUP a fhreagraíonn do cholún bailí san eagar táblaí.

Earráid #REF á seoladh ar ais

Liostaíonn an chuid seo fáth amháin a dtugann VLOOKUP earráid #REF ar ais agus soláthraíonn sé réitigh ar an bhfadhb seo.

Cúis: Is mó an argóint col_index ná líon na gcolún

Mar a fheiceann tú sa screenshot thíos, níl ach 4 cholún san eagar tábla. Mar sin féin, is é 5 an t-innéacs colúin a shonraigh tú san fhoirmle VLOOKUP, atá níos mó ná líon na gcolún san eagar táblaí. Mar thoradh air sin, ní bheidh VLOOKUP in ann an colún a aimsiú agus cuirfidh sé earráid #REF ar ais ar deireadh.

Solutions

• Sonraigh uimhir cholúin cheart
  Cinntigh gur uimhir í an argóint innéacs colúin i d’fhoirmle VLOOKUP a fhreagraíonn do cholún bailí san eagar táblaí.
• Faigh uimhir an cholúin go huathoibríoch bunaithe ar cheanntásc an cholúin sonraithe
  Má tá go leor colúin sa tábla, seans go mbeidh deacracht agat an uimhir innéacs colún ceart a chinneadh. Anseo, is féidir leat an fheidhm MATCH a neadú san fheidhm VLOOKUP chun suíomh an cholúin a fháil bunaithe ar cheanntásc colún certian.

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

  nótaí: Sa fhoirmle thuas, tá an MATCH("Ríomhphost",B5:E5, 0) úsáidtear an fheidhm chun uimhir cholúin an "Ríomhphost" colún sa raon dátaí B6:E12. Seo é an toradh 4, a úsáidtear mar an col_index san fheidhm VLOOKUP.

Luach mícheart á thabhairt ar ais

Má fhaigheann tú amach nach bhfuil VLOOKUP ag tabhairt an toradh ceart ar ais, d'fhéadfadh sé a bheith mar gheall ar na cúiseanna seo a leanas

Cúis 1: Níl an colún cuardaigh curtha in ord ardaitheach

Má tá an argóint dheireanach socraithe agat go TRUE (nó d’fhág sé folamh é) le haghaidh neasmheaitseála, agus nach bhfuil an colún cuardaigh curtha in ord ardaitheach, féadfaidh an luach a bheidh mar thoradh air a bheith mícheart.

réiteach

Sórtáil an colún cuardaigh in ord ardaitheach is féidir cabhrú leat an fhadhb seo a réiteach. Chun seo a dhéanamh, lean na céimeanna thíos le do thoil:

1. Roghnaigh na cealla sonraí sa cholún cuardaigh, téigh go dtí an Dáta tab, cliceáil Sórtáil is lú go dtí an ceann is mó sa Sórtáil & Scagaire ghrúpa.
2. sa Rabhadh Sórtáil bosca dialóige, roghnaigh an Leathnaigh an roghnú rogha, agus cliceáil OK.

Cúis 2: Cuirtear colún isteach nó as oifig

Mar a thaispeántar sa screenshot thíos, tá an luach a bhí mé ag iarraidh a thabhairt ar ais ar dtús sa cheathrú colún den eagar tábla, mar sin sonraigh mé an uimhir col_index mar 4. De réir mar a chuirtear colún nua isteach, déantar an colún toraidh mar an cúigiú colún den tábla eagar, rud a fhágann go dtabharfaidh VLOOKUP an toradh ar ais ó cholún mícheart.

Solutions

Seo dhá réiteach duit.

• Is féidir leat uimhir innéacs an cholúin a athrú de láimh chun suíomh an cholúin tuairisceáin a mheaitseáil. Ba cheart an fhoirmle anseo a athrú go:

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

• Más mian leat an toradh a thabhairt ar ais ó cholún deimhnithe i gcónaí, mar shampla an colún Ríomhphost sa sampla seo. Is féidir leis an bhfoirmle seo a leanas cabhrú le hinnéacs na gcolún a mheaitseáil go huathoibríoch bunaithe ar cheanntásc an cholúin a thugtar, is cuma an gcuirtear colúin isteach nó nach mbaintear den eagar tábla iad.

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

Nótaí feidhm eile

• Ní lorgaíonn VLOOKUP ach luach ó chlé go deas.
  Tá an luach cuardaigh sa cholún is mó ar chlé, agus ba cheart go mbeadh luach an toraidh in aon cholún ar thaobh na láimhe deise den cholún breathnú suas.
• Má fhágann tú an argóint dheireanach folamh, úsáideann VLOOKUP neasmheaitseáil de réir réamhshocraithe.
• Déanann VLOOKUP cuardach cás-neamhíogair.
• I gcás cluichí iolracha, ní fhilleann VLOOKUP ach an chéad mheaitseáil a aimsíonn sé san eagar táblaí, bunaithe ar ord na sraitheanna san eagar táblaí.

Airteagail gaolmhara