Skip to main content

20+ Sampla de VLOOKUP Do Thosaitheoirí Excel & Ardúsáideoirí

Tá an fheidhm VLOOKUP ar cheann de na feidhmeanna is coitianta in Excel. Tabharfaidh an teagasc seo isteach conas feidhm VLOOKUP a úsáid in Excel le mórán samplaí bunúsacha agus ardchéime céim ar chéim.


Íoslódáil comhaid shamplacha VLOOKUP

 Samplaí bunúsacha Vlookup   |    Samplaí Vlookup Casta   |    Vlookup coimeád formáidiú cille


Feidhm VLOOKUP a thabhairt isteach - Comhréir agus Argóintí

In Excel, is feidhm chumhachtach í an fheidhm VLOOKUP don chuid is mó d'úsáideoirí Excel, ligeann sé duit luach a lorg ar an taobh clé den raon sonraí, agus luach meaitseála a thabhairt ar ais sa tsraith chéanna ó cholún a shonraigh tú mar an scáileán seo a leanas a thaispeántar .

Comhréir na feidhme VLOOKUP:

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

Argóintí:

Cuardach_luach (riachtanach): An luach a theastaíonn uait a chuardach. Is féidir é a bheith ina luach (uimhir, dáta nó téacs) nó tagairt cille. Caithfidh sé a bheith sa chéad cholún den raon table_array. 

Tábla_eagar (riachtanach): An raon sonraí nó an tábla ina bhfuil an colún luach cuardaigh agus colún luach an toraidh suite.

Col_innéacs_num (riachtanach): Uimhir an cholúin ina bhfuil na luachanna tuairisceáin. Tosaíonn sé le 1 ón gcolún is faide ar chlé san eagar táblaí.

Raon_lookup (roghnach): Luach loighciúil a chinneann cé acu an dtabharfaidh an fheidhm VLOOKUP seo meaitseáil bheacht nó neasmheaitseáil ar ais.

  • Neas-mheaitseáil – 1 / TRUE / fágtha ar lár (réamhshocraithe): Mura n-aimsítear meaitseáil chruinn, déanann an fhoirmle cuardach don mheaitseáil is gaire - an luach is mó atá níos lú ná an luach cuardaigh.
    Fógra: Sa chás seo, ní mór duit an colún cuardaigh (an colún is clé den raon sonraí) a shórtáil in ord ardaitheach, nó tabharfaidh sé toradh earráide mícheart nó #N/A ar ais.
  • Cluiche cruinn – 0 / BRÉAGACH: Úsáidtear é seo chun luach atá comhionann go díreach leis an luach cuardaigh a chuardach. Mura bhfaightear comhoiriúnú cruinn, tabharfar an luach earráide # N / A ar ais.

Nótaí Feidhm:

  • Ní lorgaíonn an fheidhm Vlookup ach luach ó chlé go deas.
  • Déanann an fheidhm Vlookup cuardach cás-neamhíogair.
  • Má tá illuachanna meaitseála bunaithe ar an luach cuardaigh, ní thabharfar ar ais ach an chéad cheann a mheaitseáil leis an bhfeidhm Vlookup.

Samplaí bunúsacha VLOOKUP

San alt seo, labhairfimid faoi roinnt foirmlí Vlookup a d'úsáid tú go minic.

2.1 Meaitseáil beacht agus neasmheaitseáil VLOOKUP

 2.1.1 Déan meaitseáil chruinn VLOOKUP

De ghnáth, má tá tú ag lorg meaitseáil chruinn leis an bhfeidhm VLOOKUP, ní gá duit ach BRÉAGACH a úsáid mar an argóint dheireanach.

Mar shampla, chun na scóir Math comhfhreagracha a fháil bunaithe ar na huimhreacha aitheantais ar leith, déan mar seo le do thoil:

Déan an fhoirmle thíos a chóipeáil agus a ghreamú isteach i gcill bhán (anseo, roghnaigh mé G2), agus brúigh Iontráil eochair chun an toradh a fháil:

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

Nóta: Sa fhoirmle thuas, tá ceithre argóint:

  • F2 an chill ina bhfuil an luach C1005 is mian leat a chuardach;
  • A2: D7 an t-eagar tábla ina bhfuil tú ag déanamh an chuardaigh;
  • 3 an uimhir cholúin óna gcuirtear do luach comhoiriúnaithe ar ais; (Nuair a aimsíonn an fheidhm an ID - C1005, rachaidh sé go dtí an tríú colún den eagar tábla, agus seolfaidh sé na luachanna sa tsraith chéanna leis an ID - C1005. )
  • FALSE tagraíonn sé don mheaitseáil bheacht.

Conas a oibríonn an fhoirmle VLOOKUP?

Ar dtús, lorgaíonn sé an ID - C1005 sa cholún is faide ar chlé den tábla. Téann sé ó bhun go barr agus aimsíonn sé an luach i gcill A6.

Chomh luath agus a fhaigheann sé an luach, téann sé ar dheis sa tríú colún agus sleachta an luach atá ann.

Mar sin, gheobhaidh tú an toradh mar a thaispeántar thíos scáileáin:

Nóta: Mura bhfuil an luach cuardaigh le fáil sa cholún is mó ar chlé, filleann sé earráid #N/A.
🤖 Kutools AI Aide: anailís sonraí a réabhlóidiú bunaithe ar: Forghníomhú Chliste   |  Gin Cód  |  Cruthaigh Foirmlí Saincheaptha  |  Anailís a dhéanamh ar Sonraí agus Cairteacha a Ghin  |  Feidhmeanna Kutools a agairt...
Gnéithe Coitianta: Faigh, Aibhsigh nó Aithnigh Dúblaigh   |  Scrios Sraitheanna Bána   |  Comhcheangail Colúin nó Cealla gan Sonraí a Chailleadh   |   Babhta gan Foirmle ...
Cuardaigh Super: Ilchritéir VLookup  |   VLookup Illuachanna  |   VLookup Trasna Ilbhileoga   |   Amharc doiléir ...
Liosta anuas Casta: Go tapa Cruthaigh Liosta Anuas   |  Liosta anuas Cleithiúnach   |  Liosta Buail Isteach Ilroghnacha ...
Bainisteoir Colún: Cuir Líon Sonrach Colún leis  |  Colúin Bog   |  Folaigh na Colúin  |  Déan comparáid idir Raonta & Colúin ...
Gnéithe Réadmhaoin: Fócas Eangaí   |  Amharc Dearaidh   |   Barra Mór na Foirmle   |  Leabhar Oibre & Bainisteoir Bileog  |  Leabharlann Acmhainní   |  Piocálaí Dáta  |  Comhcheangail Bileoga Oibre   |  Criptigh/Díchriptigh Cealla    Seol Ríomhphost trí Liosta   |  Scagaire Super   |   Scagaire Speisialta (le cló trom/iodálach...) ...
Barr 15 Uirlisí12 Téacs uirlisí (Cuir Téacs, Bain Carachtair,...)   |   50 + Cairt cineálacha (Cairt Gantt,...)   |   40+ Praiticiúil Foirmlí (Ríomh aois bunaithe ar lá breithe,...)   |   19 Insertion uirlisí (Cuir isteach Cód QR, Ionsáigh Pictiúr ón gCosán,...)   |   12 Tiontú uirlisí (Uimhreacha le Focail, Comhshó Airgeadra,...)   |   7 Cumaisc & Scoilt uirlisí (Sraitheanna Comhcheangail Casta, Cealla Scoilt,...)   |   Go leor eile...

Kutools le haghaidh Excel Bródúil as Níos mó ná 300 Gnéithe, A chinntiú nach bhfuil uait ach cliceáil ar shiúl...

 
 2.1.2 Déan gar-mheaitseáil VLOOKUP

Tá an neasmheaitseáil úsáideach chun luachanna idir raonta sonraí a chuardach. Mura bhfaightear an meaitseáil chruinn, tabharfaidh an neas-VLOOKUP ar ais an luach is mó atá níos lú ná an luach cuardaigh.

Mar shampla, má tá an raon sonraí seo a leanas agat, agus nach bhfuil na horduithe sonraithe sa cholún Orduithe, conas a Lascaine is gaire a fháil i gcolún B?

Céim 1: Cuir an fhoirmle VLOOKUP i bhfeidhm agus é a líonadh chuig cealla eile

Cóipeáil agus greamaigh an fhoirmle seo a leanas isteach i gcill áit ar mhaith leat an toradh a chur, agus ansin tarraing an láimhseáil líonta síos chun an fhoirmle seo a chur i bhfeidhm ar chealla eile.

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

Toradh:

Anois, gheobhaidh tú na neas-mheaitseanna bunaithe ar na luachanna tugtha, féach an seat scáileáin:

Nótaí:

  • San fhoirmle thuas:
    • D2 an luach a theastaíonn uait a fhaisnéis choibhneasta a thabhairt ar ais;
    • A2: B9 an bhfuil an raon sonraí;
    • 2 léiríonn an uimhir colúin go bhfuil do luach comhoiriúnaithe ar ais;
    • TRUE tagraíonn sé don neas-mheaitseáil.
  • Tabharfaidh an neasmheaitseáil ar ais an luach is mó atá níos lú ná do luach cuardaigh sonrach mura bhfaightear meaitseáil chruinn.
  • Chun an fheidhm VLOOKUP a úsáid chun neasluach meaitseála a fháil, ní mór duit an colún is faide ar chlé den raon sonraí a shórtáil in ord ardaitheach, nó tabharfaidh sé toradh mícheart ar ais.

2.2 Déan VLOOKUP cás-íogair in Excel

De réir réamhshocraithe, déanann an fheidhm VLOOKUP cuardach cás-neamhíogair a chiallaíonn go ndéileálann sé le carachtair chás íochtair agus cás uachtair mar an gcéanna. Uaireanta, b'fhéidir go mbeidh ort cuardach cás-íogair a dhéanamh in Excel, b'fhéidir nach réiteoidh gnáthfheidhm VLOOKUP é. Sa chás seo, is féidir leat feidhmeanna malartacha a úsáid mar INDEX agus MATCH leis an bhfeidhm EXACT, nó na feidhmeanna LOOKUP agus EXACT.

Mar shampla, tá an raon sonraí seo a leanas agam sa cholún ID ina bhfuil sreangán téacs le cás uachtair nó cás íochtair, anois, ba mhaith liom an scór Math comhfhreagrach den uimhir aitheantais a tugadh a thabhairt ar ais.

Céim 1: Cuir aon fhoirmle amháin i bhfeidhm agus é a líonadh le cealla eile

Cóipeáil agus greamaigh aon cheann de na foirmlí thíos isteach i gcill bhán áit ar mhaith leat an toradh a fháil. Ansin, roghnaigh an chill fhoirmle, tarraing an láimhseáil líonta síos go dtí na cealla inar mian leat an fhoirmle seo a líonadh.

Foirmle 1: Tar éis an fhoirmle a ghreamú, brúigh le do thoil Ctrl + Shift + Iontráil eochracha.

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

Foirmle 2: Tar éis an fhoirmle a ghreamú, brúigh le do thoil Iontráil eochair.

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

Toradh:

Ansin gheobhaidh tú na torthaí cearta a theastaíonn uait. Féach ar an scáileán:

Nótaí:

  • San fhoirmle thuas:
    • A2: A10 an colún ina bhfuil na luachanna sonracha ar mhaith leat breathnú suas iontu;
    • F2 an bhfuil an luach cuardaigh;
    • C2: C10 an colún as a bhfillfear an toradh.
  • Má aimsítear il-mheaitseanna, tabharfaidh an fhoirmle seo an cluiche deireanach ar ais i gcónaí.

2.3 Luachanna VLOOKUP ó dheas go clé in Excel

Déanann an fheidhm VLOOKUP cuardach i gcónaí ar luach sa cholún is faide ar chlé de raon sonraí agus filleann sí an luach comhfhreagrach ó cholún ar dheis. Más mian leat VLOOKUP droim ar ais a dhéanamh a chiallaíonn luach sonrach a chuardach sa cholún ar dheis agus a luach comhfhreagrach a chur ar ais sa cholún ar chlé mar atá thíos sa screenshot a thaispeántar:

Cliceáil chun na sonraí a fháil céim ar chéim faoin tasc seo ...


2.4 VLOOKUP an dara, an nú luach meaitseála nó deireanach in Excel

De ghnáth, má aimsítear luachanna iolracha meaitseála agus an fheidhm Vlookup in úsáid, ní thabharfar ar ais ach an chéad taifead comhoiriúnaithe. Sa chuid seo, labhróidh mé faoi conas an dara, an nú nó an luach meaitseála deireanach a fháil i raon sonraí.

 2.4.1 VLOOKUP agus seol ar ais an dara nó an nú luach meaitseála

Cuir i gcás go bhfuil liosta ainmneacha agat i gcolún A, an cúrsa oiliúna a cheannaigh siad i gcolún B. Anois, tá tú ag iarraidh teacht ar an 2ú nó an ú cúrsa oiliúna a cheannaigh an custaiméir ar leith. Féach ar an scáileán:

Anseo, ní fhéadfaidh feidhm VLOOKUP an tasc seo a réiteach go díreach. Ach, is féidir leat an fheidhm INDEX a úsáid mar rogha eile.

Céim 1: Cuir an fhoirmle i bhfeidhm agus a líonadh chuig cealla eile

Mar shampla, chun an dara luach meaitseála a fháil bunaithe ar na critéir tugtha, cuir an fhoirmle seo a leanas i bhfeidhm i gcill bhán, agus brúigh Ctrl + Shift + Iontráil eochracha le chéile chun an chéad toradh a fháil. Agus ansin, roghnaigh an chill fhoirmle, tarraing an láimhseáil líonadh síos go dtí na cealla áit ar mhaith leat an fhoirmle seo a líonadh.

=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))

Toradh:

Anois, tá gach dara luach comhoiriúnaithe bunaithe ar na hainmneacha tugtha ar taispeáint ag an am céanna.

Nóta: San fhoirmle thuas:

  • A2: A14 an raon leis na luachanna cuardaigh go léir;
  • B2: B14 an bhfuil raon na luachanna meaitseála a bhfuil tú ag iarraidh filleadh uathu;
  • E2 an bhfuil an luach cuardaigh;
  • 2 léiríonn sé an dara luach meaitseála is mian leat a fháil, chun an tríú luach meaitseála a thabhairt ar ais, ní gá duit ach é a athrú go 3.
 2.4.2 VLOOKUP agus seol ar ais an luach meaitseála deireanach

Más mian leat an luach meaitseála deireanach a fheiceáil agus a chur ar ais mar atá thíos an pictiúr a thaispeántar, seo VLOOKUP Agus Tabhair ar ais An Luach Meaitseála Deiridh d'fhéadfadh go gcuideoidh an rang teagaisc leat an luach meaitseála deireanach a fháil i sonraí.


2.5 VLOOKUP ag meaitseáil luachanna idir dhá luach nó dáta tugtha

Uaireanta, b'fhéidir gur mhaith leat luachanna a chuardach idir dhá luach nó dáta agus na torthaí comhfhreagracha a thabhairt ar ais mar a thaispeántar sa scáileán thíos. I gcás den sórt sin, is féidir leat an fheidhm LOOKUP a úsáid in ionad feidhm VLOOKUP le tábla sórtáilte.

 2.5.1 VLOOKUP ag meaitseáil luachanna idir dhá luach nó dáta tugtha le foirmle

Céim 1: Socraigh na sonraí agus cuir an fhoirmle seo a leanas i bhfeidhm

Ba cheart go mbeadh do bhuntábla ina raon sonraí sórtáilte. Agus ansin, cóipeáil nó cuir isteach an fhoirmle seo a leanas isteach i gcill bhán. Ansin, tarraing an láimhseáil líonta chun an fhoirmle seo a líonadh chuig cealla eile atá uait.

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

Toradh:

Agus anois, gheobhaidh tú na taifid mheaitseála go léir bunaithe ar an luach tugtha, féach an scáileán scáileáin:

Nótaí:

  • San fhoirmle thuas:
    • A2: A6 an bhfuil raon na luachanna níos lú;
    • B2: B6 an bhfuil raon na n-uimhreacha níos mó;
    • E2 an luach cuardaigh a theastaíonn uait a luach comhfhreagrach a fháil;
    • C2: C6 an colún óna dteastaíonn uait luach comhfhreagrach a thabhairt ar ais.
  • Is féidir an fhoirmle seo a úsáid freisin chun luachanna comhoiriúnaithe a bhaint as idir dhá dháta mar a thaispeántar thíos:
 2.5.2 VLOOKUP ag meaitseáil luachanna idir dhá luach nó dáta tugtha le gné áisiúil

Má bhíonn deacracht agat an fhoirmle thuas a mheabhrú agus a thuiscint, anseo, tabharfaidh mé isteach uirlis éasca - Kutools le haghaidh Excel, Lena LOOKUP idir Dhá Luach gné, is féidir leat an mhír chomhfhreagrach a thabhairt ar ais bunaithe ar an luach sonrach nó ar an dáta idir dhá luach nó dáta gan stró.

  1. cliceáil Kutools > Super LOOKUP > LOOKUP idir Dhá Luach chun an ghné seo a chumasú.
  2. Ansin sonraigh na hoibríochtaí ón mbosca dialóige bunaithe ar do shonraí.
nótaí: Chun an ghné seo a chur i bhfeidhm, ba chóir duit a íoslódáil Kutools le haghaidh Excel le triail saor in aisce 30-lá ar dtús.


2.6 Ag baint úsáide as saoróga le haghaidh cluichí páirteacha san fheidhm VLOOKUP

In Excel, is féidir na saorchártaí a úsáid laistigh den fheidhm VLOOKUP, a ligeann duit cluiche páirteach a dhéanamh ar luach cuardaigh. Mar shampla, is féidir leat VLOOKUP a úsáid chun luach comhoiriúnaithe a thabhairt ar ais ó thábla bunaithe ar chuid de luach cuardaigh.

Ag ceapadh, tá raon sonraí agam mar atá thíos an pictiúr a thaispeántar, anois, ba mhaith liom an scór a bhaint bunaithe ar an gcéad ainm (ní ainm iomlán). Conas a d’fhéadfadh an tasc seo a réiteach in Excel?

Céim 1: Cuir an fhoirmle i bhfeidhm agus a líonadh chuig cealla eile

Cóipeáil nó cuir isteach an fhoirmle seo a leanas isteach i gcill bhán le do thoil, agus ansin, tarraing an láimhseáil líonta chun an fhoirmle seo a líonadh chuig cealla eile atá uait:

=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)

Toradh:

Agus tá na scóir chomhoiriúnaithe ar fad tugtha ar ais mar a thaispeántar thíos:

Nóta: San fhoirmle thuas:

  • E2 & ”*” Is iad na critéir le haghaidh na matamaitice páirteach. Ciallaíonn sé seo go bhfuil tú ag lorg aon luach a thosaíonn leis an luach i gcill E2. (An cárta fiáin"*” a léiríonn aon charachtar amháin nó aon charachtair)
  • A2: C11 an raon sonraí inar mian leat an luach comhoiriúnaithe a chuardach;
  • 3 ciallaíonn sé an luach meaitseála a thabhairt ar ais ón 3ú colún den raon sonraí;
  • Bréagach léiríonn an mata cruinn. (Agus tú ag baint úsáide as saoróga, ní mór duit an argóint dheireanach san fheidhm a shocrú mar FALSE nó 0 chun mód meaitseála beacht a chumasú san fheidhm VLOOKUP.)
Leideanna:
  • Chun na luachanna meaitseála a chríochnaíonn le luach ar leith a aimsiú agus a thabhairt ar ais, ba cheart duit an saoróg "*" a chur os comhair an luacha. Cuir an fhoirmle seo i bhfeidhm le do thoil:
  • =VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)

  • Chun an luach comhoiriúnaithe bunaithe ar chuid den teaghrán téacs a chuardach agus a thabhairt ar ais, cibé an bhfuil an téacs sonraithe ag bebinning, ag deireadh nó i lár teaghrán an téacs, ní gá duit ach tagairt na cille nó an téacs a chur faoi iamh le dhá réiltín (*) ar an dá thaobh. Déan leis an bhfoirmle seo le do thoil
  • =VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)


2.7 Luachanna VLOOKUP ó bhileog oibre eile

De ghnáth, b'fhéidir go mbeadh ort oibriú le níos mó ná bileog oibre amháin, is féidir an fheidhm VLOOKUP a úsáid chun sonraí a chuardach ó bhileog eile mar an gcéanna le bileog oibre amháin.

Mar shampla, tá dhá bhileog oibre agat mar atá thíos den ghrianghraf a thaispeántar, chun na sonraí comhfhreagracha ón mbileog oibre a shonraigh tú a chuardach agus a chur ar ais, déan na céimeanna seo a leanas le do thoil:

Céim 1: Cuir an fhoirmle i bhfeidhm agus a líonadh chuig cealla eile

Iontráil nó cóipeáil an fhoirmle thíos isteach i gcill bhán inar mian leat na hearraí comhoiriúnaithe a fháil. Ansin, tarraing an láimhseáil líonta síos go dtí na cealla ar mhaith leat an fhoirmle seo a chur i bhfeidhm.

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

Toradh:

Gheobhaidh tú na torthaí comhfhreagracha de réir mar is gá duit, féach an scáileán scáileáin:

Nóta: San fhoirmle thuas:

  • A2 is ionann é agus an luach amharc;
  • 'Bileog sonraí'!A2:C15 léiríonn sé cuardach a dhéanamh ar na luachanna ón raon A2:C15 ar an mbileog oibre darb ainm Data sheet; (Má tá carachtair spáis nó poncaíochta in ainm na bileoige, ba cheart duit ainm na bileoige a chur isteach i Sleachta singil, nó is féidir leat ainm na bileoige a úsáid go díreach mar =VLOOKUP(A2,Bileog Sonraí!$A$2:$C$15,3,0) ).
  • 3 an uimhir cholúin ina bhfuil sonraí meaitseáilte is mian leat a thabhairt ar ais uathu;
  • 0 ciallaíonn sé cluiche cruinn a dhéanamh.

2.8 luachanna VLOOKUP ó leabhar oibre eile

Labhróidh an chuid seo faoi chuardach agus seolfaidh siad na luachanna meaitseála ó leabhar oibre eile trí úsáid a bhaint as an bhfeidhm VLOOKUP.

Mar shampla, déarfainn go bhfuil dhá leabhar oibre agat. Tá liosta táirgí agus a gcostas faoi seach sa chéad leabhar saothair. Sa dara leabhar oibre, ba mhaith leat an costas comhfhreagrach a bhaint as gach mír táirge mar a thaispeántar thíos scáileáin.

Céim 1: Cuir i bhfeidhm agus líon isteach an fhoirmle

Oscail an dá leabhar oibre is mian leat a úsáid, ansin cuir an fhoirmle seo a leanas i bhfeidhm i gcill inar mian leat an toradh a chur sa dara leabhar oibre. Ansin, tarraing agus cóipeáil an fhoirmle seo chuig cealla eile atá uait

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

Toradh:

Nótaí:

  • San fhoirmle thuas:
    • B2 is ionann é agus an luach amharc;
    • '[liosta táirge.xlsx]Bileog1'!A2:B6 léiríonn sé cuardach a dhéanamh ón raon A2:B6 ar an mbileog darb ainm Bileog 1 ón liosta táirgí leabhar saothair; (Tá an tagairt do leabhar oibre faoi iamh idir lúibíní cearnacha, agus tá an leabhar oibre + bileog iomlán faoi iamh i Sleachta singil.)
    • 2 an uimhir cholúin ina bhfuil sonraí meaitseáilte is mian leat a thabhairt ar ais uathu;
    • 0 léiríonn a thabhairt ar ais ar chluiche cruinn.
  • Má tá an leabhar oibre cuardaigh dúnta, taispeánfar cosán iomlán an chomhaid don leabhar oibre cuardaigh san fhoirmle mar a thaispeántar an scáileán seo a leanas:

2.9 Seol téacs bán nó téacs sonrach ar ais in ionad earráid 0 nó #N/A

De ghnáth, nuair a úsáideann tú an fheidhm VLOOKUP chun luach comhfhreagrach a thabhairt ar ais, má tá an chill meaitseála bán, fillfidh sé 0. Agus mura bhfuarthas an luach meaitseála, gheobhaidh tú luach earráide #N/A mar a thaispeántar sa scáileán thíos. Más mian leat cill bhán nó luach sonrach a thaispeáint in ionad 0 nó #N/B, seo VLOOKUP Chun Luach Bán nó Sonrach a Aischur In ionad 0 Nó N/B is féidir teagaisc a dhéanamh leat i dtús báire.


Samplaí chun cinn VLOOKUP

3.1 Cuardach déthreo (VLOOKUP i ró agus i gcolún)

Uaireanta, b'fhéidir go mbeadh ort cuardach déthoiseach a dhéanamh, rud a chiallaíonn luach a chuardach sa tsraith agus sa cholún ag an am céanna. Mar shampla, má tá an raon sonraí seo a leanas agat, agus b'fhéidir go mbeadh ort luach táirge áirithe a fháil i gceathrú sonraithe. Tabharfaidh an chuid seo isteach foirmle chun déileáil leis an bpost seo in Excel.

In Excel, is féidir leat meascán d’fheidhmeanna VLOOKUP agus MATCH a úsáid chun cuardach déthreo a dhéanamh.

Cuir an fhoirmle seo a leanas i bhfeidhm i gcill bhán le do thoil, agus ansin brúigh Iontráil eochair chun an toradh a fháil.

=VLOOKUP(G2, $A$2:$E$7, MATCH(H1, $A$2:$E$2, 0), FALSE)

Nóta: San fhoirmle thuas:

  • G2 an bhfuil an luach cuardaigh sa cholún ar mian leat an luach comhfhreagrach a fháil bunaithe air;
  • A2: E7 an bhfuil an tábla sonraí a bheidh tú ag breathnú ó;
  • H1 an bhfuil an luach cuardaigh sa tsraith ar mhaith leat an luach comhfhreagrach a fháil bunaithe air;
  • A2: E2 is cealla ceanntásca colúin;
  • FALSE léiríonn a fháil ar chluiche cruinn.

3.2 Luach meaitseála VLOOKUP bunaithe ar dhá chritéar nó níos mó

Tá sé éasca duit an luach meaitseála a chuardach bunaithe ar chritéar amháin, ach má tá dhá chritéar nó níos mó agat, cad is féidir leat a dhéanamh?

 3.2.1 luach meaitseála VLOOKUP bunaithe ar dhá chritéar nó níos mó le foirmlí

Sa chás seo, is féidir leis na feidhmeanna LOOKUP nó MATCH agus INDEX in Excel cabhrú leat an post seo a réiteach go tapa agus go héasca.

Mar shampla, tá an tábla sonraí thíos agam, chun an praghas comhoiriúnaithe a thabhairt ar ais bunaithe ar an táirge agus an méid sonrach, d’fhéadfadh na foirmlí seo a leanas cabhrú leat.

Céim 1: Cuir aon fhoirmle amháin i bhfeidhm

Foirmle 1: Tar éis an fhoirmle a ghreamú, brúigh le do thoil Iontráil eochair.

=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))

Foirmle 2: Tar éis an fhoirmle a ghreamú, brúigh le do thoil Ctrl + Shift + Iontráil eochracha.

=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))

Toradh:

Nótaí:

  • Sna foirmlí thuas:
    • A2: A12 = G1 modhanna chun critéir G1 i raon A2:A12 a chuardach;
    • B2: B12 = G2 modhanna chun critéir G2 i raon B2:B12 a chuardach;
    • D2: D12 is an raon óna dteastaíonn uait an luach comhfhreagrach a thabhairt ar ais.
  • Má tá níos mó ná dhá chritéar agat, níl le déanamh agat ach na critéir eile a chur san fhoirmle, mar shampla:
    =LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))
    =INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))
 3.2.2 luach meaitseála VLOOKUP bunaithe ar dhá chritéar nó níos mó le gné chliste

Féadfaidh sé a bheith dúshlánach cuimhneamh ar na foirmlí casta thuas a chaithfear a chur i bhfeidhm arís agus arís eile, rud a d'fhéadfadh moill a chur ar d'éifeachtúlacht oibre. Ach, Kutools le haghaidh Excel Tairgeann Cuardach Il-riocht gné a ligeann duit an toradh comhfhreagrach a thabhairt ar ais bunaithe ar choinníoll amháin nó níos mó agus gan ach roinnt cad a tharlaíonn.

  1. cliceáil Kutools > Super LOOKUP > Cuardach Il-riocht chun an ghné seo a chumasú.
  2. Ansin sonraigh na hoibríochtaí ón mbosca dialóige bunaithe ar do shonraí.
nótaí: Chun an ghné seo a chur i bhfeidhm, ba chóir duit a íoslódáil Kutools le haghaidh Excel le triail saor in aisce 30-lá ar dtús.


3.3 VLOOKUP chun luachanna iolracha a thabhairt ar ais le critéar amháin nó níos mó

In Excel, déanann an fheidhm VLOOKUP cuardach ar luach agus ní thugann sí an chéad luach meaitseála ar ais ach amháin má aimsítear luachanna iolracha comhfhreagracha. Uaireanta, b'fhéidir gur mhaith leat na luachanna comhfhreagracha go léir a thabhairt ar ais i ndiaidh a chéile, i gcolún nó i gcill amháin. Labhróidh an chuid seo faoi conas na luachanna iolracha meaitseála a thabhairt ar ais le coinníoll amháin nó níos mó i leabhar oibre.

 3.3.1 VLOOKUP gach luach meaitseála bunaithe ar choinníoll amháin nó níos mó go cothrománach

Ag glacadh leis go bhfuil tábla sonraí agat ina bhfuil tír, cathair agus ainmneacha sa raon A1:C14, agus anois, ba mhaith leat na hainmneacha go léir a thabhairt ar ais go cothrománach atá ó "SAM" mar atá thíos sa screenshot a thaispeántar. Chun an tasc seo a réiteach, le do thoil cliceáil anseo chun an toradh a fháil céim ar chéim.

 3.3.2 VLOOKUP gach luach meaitseála bunaithe ar choinníoll amháin nó níos mó go hingearach

Más gá duit Vlookup a dhéanamh agus gach luach meaitseála a chur ar ais go hingearach bunaithe ar chritéir shonracha mar a thaispeántar an scáileán thíos, cliceáil anseo le do thoil chun an réiteach a fháil go mion.

 3.3.3 VLOOKUP gach luach meaitseála bunaithe ar choinníoll amháin nó níos mó ina chill singil

Más mian leat Vlookup agus luachanna iolracha comhoiriúnaithe a chur ar ais isteach i gcill amháin le deighilteoir sonraithe, is féidir le feidhm nua TEXTJOIN cabhrú leat an post seo a réiteach go tapa agus go héasca.

Nótaí:


3.4 VLOOKUP chun an tsraith iomlán de chill mheaitseála a thabhairt ar ais

Sa chuid seo, labhróidh mé faoi conas an tsraith iomlán de luach comhoiriúnaithe a aisghabháil trí úsáid a bhaint as an bhfeidhm VLOOKUP.

Céim 1: Cuir i bhfeidhm agus líon isteach an fhoirmle seo a leanas

Cóipeáil nó clóscríobh an fhoirmle thíos i gcill bhán áit ar mhaith leat an toradh a aschur, agus brúigh Iontráil eochair chun an chéad luach a fháil. Ansin, tarraing an chill fhoirmle ar dheis go dtí go dtaispeánfar sonraí na sraithe ar fad.

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

Toradh:

Anois, is féidir leat a fheiceáil go bhfuil na sonraí sraithe ar fad ar ais. Féach ar an scáileán:
feidhm vlookup doc 50 1

Nóta: san fhoirmle thuas:

  • F2 an luach cuardaigh ar mhaith leat an tsraith iomlán a thabhairt ar ais bunaithe air;
  • A1: D12 an raon sonraí ar mhaith leat an luach cuardaigh a chuardach uaidh;
  • A1 léiríonn an chéad uimhir colún laistigh de do raon sonraí;
  • FALSE léiríonn lorg cruinn.

Leid:

  • Má aimsítear sraitheanna iolracha bunaithe ar an luach comhoiriúnaithe, chun na sraitheanna comhfhreagracha go léir a thabhairt ar ais, cuir an fhoirmle thíos i bhfeidhm, ansin brúigh Ctrl + Shift + Iontráil eochracha le chéile chun an chéad toradh a fháil. Ansin tarraing an láimhseáil líonta ar dheis. Agus ansin, lean ar aghaidh ag tarraingt an láimhseáil líonta síos trasna na gcealla chun na sraitheanna meaitseála go léir a fháil. Féach ar an taispeántas thíos:
    =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),"")
    feidhm vlookup doc 51 2

3.5 Neadaithe VLOOKUP in Excel

Uaireanta, b'fhéidir go mbeadh ort breathnú ar luachanna atá idirnasctha thar táblaí iolracha. Sa chás seo, is féidir leat feidhm VLOOKUP iolrach a neadú le chéile chun an luach deiridh a fháil.

Mar shampla, tá bileog oibre agam ina bhfuil dhá tábla ar leith. Liostaíonn an chéad tábla na hainmneacha táirge go léir mar aon leis an díoltóir comhfhreagrach. Sa dara tábla liostaítear díolacháin iomlána gach díoltóir. Anois, más mian leat díolacháin gach táirge a fháil, mar a thaispeántar sa scáileán seo a leanas, is féidir leat an fheidhm VLOOKUP a neadú chun an tasc seo a chur i gcrích.
feidhm vlookup doc 53 1

Is í an fhoirmle chineálach don fheidhm VLOOKUP neadaithe ná:

=VLOOKUP(VLOOKUP(lookup_value, table_array1, col_index_num1, 0), table_array2, col_index_num2, 0)

Nóta:

  • lookup_value an luach atá á lorg agat;
  • Tábla_eagar1, Tábla_eagar2 na táblaí ina bhfuil an luach cuardaigh agus luach aischuir ann;
  • col_innéacs_num1 léiríonn sé uimhir an cholúin sa chéad tábla chun na sonraí coiteanna idirmheánacha a aimsiú;
  • col_innéacs_num2 léiríonn sé uimhir an cholúin sa dara tábla gur mian leat an luach meaitseála a thabhairt ar ais;
  • 0 a úsáidtear le haghaidh cluiche cruinn.

Céim 1: Cuir i bhfeidhm agus líon isteach an fhoirmle seo a leanas

Cuir an fhoirmle seo a leanas i bhfeidhm i gcill bhán le do thoil, agus ansin tarraing an láimhseáil líonta síos go dtí na cealla ar mhaith leat an fhoirmle seo a chur i bhfeidhm.

=VLOOKUP(VLOOKUP(G3,$A$3:$B$7,2,0),$D$3:$E$7,2,0)

Toradh:

Anois, gheobhaidh tú an toradh mar a thaispeántar sa screenshot seo a leanas:

Nóta: san fhoirmle thuas:

  • G3 ina bhfuil an luach atá á lorg agat;
  • A3: B7, D3: E7 na raonta tábla ina bhfuil an luach cuardaigh agus an luach aischuir;
  • 2 an uimhir cholúin sa raon chun an luach meaitseála a thabhairt ar ais uaidh.
  • 0 léiríonn VLOOKUP matamaitic chruinn.

3.6 Seiceáil an bhfuil luach ann bunaithe ar shonraí liosta i gcolún eile

Is féidir leis an bhfeidhm VLOOKUP cabhrú leat freisin a sheiceáil an bhfuil luachanna bunaithe ar an liosta sonraí i gcolún eile. Mar shampla, más mian leat na hainmneacha i gcolún C a chuardach agus Tá nó Níl má tá an t-ainm le fáil nó nach bhfuil i gcolún A mar a thaispeántar thíos, a chur ar ais.
feidhm vlookup doc 56 1

Céim 1: Cuir i bhfeidhm agus líon isteach an fhoirmle seo a leanas

Cuir an fhoirmle seo a leanas i gcill bhán le do thoil, ansin, tarraing an láimhseáil líonta síos go dtí na cealla ar mhaith leat an fhoirmle seo a líonadh.

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

Toradh:

Agus gheobhaidh tú an toradh de réir mar is gá duit, féach an scáileán scáileáin:

Nóta: san fhoirmle thuas:

  • C2 an luach cuardaigh is mian leat a sheiceáil;
  • A2: A10 an liosta raon ónar féidir seiceáil an bhfaighfear nó nach bhfaighfear na luachanna cuardaigh;
  • FALSE léiríonn a fháil ar chluiche cruinn.

3.7 VLOOKUP agus suim na luachanna comhoiriúnaithe go léir i sraitheanna nó i gcolúin

Agus tú ag obair le sonraí uimhriúla, seans go mbeidh ort luachanna comhoiriúnaithe a bhaint as tábla agus na huimhreacha a shuimiú i roinnt colúin nó sraitheanna. Tabharfaidh an chuid seo roinnt foirmlí isteach a chabhróidh leat an tasc seo a chur i gcrích.

 3.7.1 VLOOKUP agus suimigh gach luach comhoiriúnaithe i ndiaidh a chéile nó i sraitheanna iolracha

Cuir i gcás go bhfuil liosta táirgí agat le díolacháin ar feadh roinnt míonna, mar a thaispeántar sa screenshot seo a leanas. Anois, ní mór duit gach ordú a shuimiú i ngach mí bunaithe ar na táirgí a thugtar.

Céim 1: Cuir i bhfeidhm agus líon isteach an fhoirmle seo a leanas

Cóipeáil nó cuir isteach an fhoirmle seo a leanas isteach i gcill bhán, agus ansin brúigh Ctrl + Shift + Iontráil eochracha le chéile chun an chéad toradh a fháil. Ansin, tarraing an láimhseáil líonta síos chun an fhoirmle seo a chóipeáil chuig cealla eile a theastaíonn uait.

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

Toradh:

Tá na luachanna go léir i ndiaidh a chéile den chéad luach meaitseála curtha le chéile, féach an scáileán:

Nóta: san fhoirmle thuas:

  • H2 an bhfuil an cill ina bhfuil an luach atá á lorg agat;
  • A2: F9 an raon sonraí (gan ceanntásca colúin) lena n-áirítear an luach cuardaigh agus na luachanna comhoiriúnaithe;
  • {} 2,3,4,5,6 is uimhreacha colúin iad a úsáidtear chun iomlán an raoin a ríomh;
  • FALSE léiríonn meaitseáil chruinn.

Leid: Más mian leat na cluichí go léir a shuimiú i sraitheanna iolracha, úsáid an fhoirmle seo a leanas le do thoil:

  • =SUMPRODUCT(($A$2:$A$9=H2)*$B$2:$F$9)
 3.7.2 VLOOKUP agus suim na luachanna comhoiriúnaithe go léir i gcolún nó i gcolúin iolracha

Más mian leat an luach iomlán do na míonna sonracha a shuimiú mar a thaispeántar sa screenshot thíos. Seans nach gcuideoidh an ghnáthfheidhm VLOOKUP leat, anseo, ba cheart duit na feidhmeanna SUM, INDEX agus MATCH a chur i bhfeidhm le chéile chun foirmle a chruthú.

Céim 1: Cuir an fhoirmle seo a leanas i bhfeidhm

Cuir an fhoirmle thíos i gcill bhán, agus ansin tarraing an láimhseáil líonta síos chun an fhoirmle seo a chóipeáil chuig cealla eile.

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

Toradh:

Anois, tá na chéad luachanna comhoiriúnaithe bunaithe ar an mí ar leith i gcolún curtha le chéile, féach an scáileán scáileáin:

Nóta: san fhoirmle thuas:

  • H2 an bhfuil an cill ina bhfuil an luach atá á lorg agat;
  • B1: F1 an bhfuil ceanntásca na gcolún ina bhfuil an luach amharc;
  • B2: F9 an raon sonraí ina bhfuil na luachanna uimhriúla a theastaíonn uait a shuimiú.

Leid: Chun VLOOKUP agus na luachanna comhoiriúnaithe go léir a shuimiú i gcolúin iolracha, ba cheart duit an fhoirmle seo a leanas a úsáid:

  • =SUMPRODUCT($B$2:$F$9*(($B$1:$F$1)=H2))
 3.7.3 VLOOKUP agus suimigh an chéad luach comhoiriúnaithe nó gach luach comhoiriúnaithe le gné chumhachtach

B'fhéidir go bhfuil na foirmlí thuas deacair duit a mheabhrú, sa chás seo, beidh mé a mholadh gné chumhachtach - Cuardach agus Suim of Kutools le haghaidh Excel, leis an ngné seo, is féidir leat Vlookup agus an chéad luach meaitseála nó gach luach meaitseála a shuimiú i sraitheanna nó i gcolúin chomh héasca agus is féidir.

  1. cliceáil Kutools > Super LOOKUP > LOOKUP agus Suim chun an ghné seo a chumasú.
  2. Ansin sonraigh na hoibríochtaí ón mbosca dialóige bunaithe ar do riachtanas.
nótaí: Chun an ghné seo a chur i bhfeidhm, ba chóir duit a íoslódáil Kutools le haghaidh Excel le triail saor in aisce 30-lá ar dtús.
 3.7.4 VLOOKUP agus suim na luachanna comhoiriúnaithe go léir i sraitheanna agus i gcolúin

Más mian leat na luachanna a shuimiú nuair is gá duit an colún agus an tsraith a mheaitseáil, mar shampla, chun luach iomlán an táirge Geansaí a fháil i mí Márta mar atá thíos ar an scáileán a thaispeántar.

Anseo, is féidir leat an fheidhm SUMPRODCT a úsáid chun an tasc seo a chur i gcrích.

Cuir an fhoirmle seo a leanas i bhfeidhm i gcill, agus ansin brúigh Iontráil eochair chun an toradh a fháil, féach an scáileán:

=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))

Nóta: San fhoirmle thuas:

  • B2: F9 an bhfuil an raon sonraí ina bhfuil na luachanna uimhriúla is mian leat a shuimiú;
  • B1: F1 an bhfuil ceanntásca an cholúin ina bhfuil an luach cuardaigh ar mian leat suim a dhéanamh air;
  • I2 an bhfuil an luach cuardaigh laistigh de na ceanntásca colúin atá á lorg agat;
  • A2: A9 an bhfuil ceanntásca an rónna ina bhfuil an luach cuardaigh ar mian leat suim a dhéanamh air bunaithe air;
  • H2 is é an luach cuardaigh laistigh de na ceannteidil rónna atá á lorg agat.

3.8 VLOOKUP chun dhá thábla bunaithe ar eochaircholúin a chumasc

I do chuid oibre laethúil, agus tú ag déanamh anailíse ar shonraí, seans go mbeidh ort an fhaisnéis riachtanach ar fad a bhailiú i dtábla amháin bunaithe ar eochaircholún amháin nó níos mó. Chun an tasc seo a chur i gcrích, is féidir leat na feidhmeanna INDEX agus MATCH a úsáid in ionad na feidhme VLOOKUP.

 3.8.1 VLOOKUP chun dhá thábla a chumasc bunaithe ar eochaircholún amháin

Mar shampla, tá dhá tábla agat, an chéad tábla ina bhfuil na sonraí táirgí agus ainmneacha, agus tá na sonraí táirgí agus orduithe sa dara tábla, anois, ba mhaith leat an dá tábla seo a chomhcheangal tríd an gcolún táirge coitianta a mheaitseáil i dtábla amháin.

Céim 1: Cuir i bhfeidhm agus líon isteach an fhoirmle seo a leanas

Cuir an fhoirmle seo a leanas i bhfeidhm i gcill bhán. Ansin, tarraing an láimhseáil líonta síos go dtí na cealla ar mhaith leat an fhoirmle seo a chur i bhfeidhm

=INDEX($F$2:$F$8, MATCH($A2, $E$2:$E$8, 0))

Toradh:

Anois, gheobhaidh tú tábla cumaisc agus an colún ordaithe ceangailte leis an gcéad tábla bunaithe ar shonraí an cholúin eochair.

Nóta: San fhoirmle thuas:

  • A2 an luach cuardaigh atá á lorg agat;
  • F2: F8 an raon sonraí a theastaíonn uait na luachanna meaitseála a thabhairt ar ais;
  • E2: E8 is raon lookup ina bhfuil an luach lookup.
 3.8.2 VLOOKUP chun dhá thábla a chumasc bunaithe ar ilcholúin eochrach

Má tá eochaircholúin iolracha ag an dá thábla ar mhaith leat a bheith páirteach iontu, chun na táblaí atá bunaithe ar na colúin choitianta seo a chumasc, lean na céimeanna thíos le do thoil.

Is í an fhoirmle chineálach:

=INDEX(lookup_table, MATCH(1, (lookup_value1=lookup_range1) * (lookup_value2=lookup_range2), 0), return_column_number)

Nóta:

  • tábla_lorg an bhfuil na sonraí cuardaigh agus na taifid mheaitseála sa raon sonraí;
  • luach_lorg1 na chéad chritéir atá á lorg agat;
  • raon_lorg1 an bhfuil na chéad chritéir sa liosta sonraí;
  • luach_lorg2 an dara critéir atá á lorg agat;
  • raon_lorg2 an bhfuil an dara critéar sa liosta sonraí;
  • fill_column_uimhir léiríonn sé uimhir an cholúin sa lookup_table gur mian leat an luach meaitseála a thabhairt ar ais.

Céim 1: Cuir an fhoirmle seo a leanas i bhfeidhm

Cuir an fhoirmle thíos i bhfeidhm i gcill bhán áit ar mhaith leat an toradh a chur, agus ansin brúigh Ctrl + Shift + Iontráil eochracha le chéile chun an chéad luach comhoiriúnaithe a fháil, féach an scáileán:

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

Céim 2: Líon an fhoirmle go cealla eile

Ansin, roghnaigh an chéad chill fhoirmle, agus tarraing an láimhseáil líonta chun an fhoirmle seo a chóipeáil chuig cealla eile de réir mar is gá duit:

Leideanna: I Excel 2016 nó leaganacha níos déanaí, is féidir leat úsáid a bhaint freisin ar an Power Query gné chun dhá thábla nó níos mó a chumasc i gceann amháin bunaithe ar eochaircholúin. Cliceáil le do thoil chun na sonraí a fháil céim ar chéim.

3.9 VLOOKUP ag meaitseáil luachanna thar ilbhileoga oibre

An raibh ort riamh VLOOKUP a dhéanamh thar ilbhileoga oibre in Excel? Mar shampla, má tá trí bhileog oibre agat le raonta sonraí, agus más mian leat luachanna sonracha a aisghabháil bunaithe ar chritéir ó na bileoga seo, is féidir leat an teagasc céim ar chéim a leanúint Luachanna VLOOKUP Thar Il Bhileoga Oibre chun an tasc seo a chur i gcrích.


Coinníonn luachanna comhoiriúnaithe VLOOKUP formáidiú cille

Agus luachanna comhoiriúnaithe á lorg, ní choimeádfar an fhormáidiú cille bunaidh ar nós dath an chló, dath cúlra, formáid sonraí, etc. Chun an fhormáidiú cille nó sonraí a choinneáil, tabharfaidh an chuid seo isteach roinnt cleasanna chun na poist a réiteach.

4.1 luach meaitseála VLOOKUP agus coinnigh dath cille, formáidiú cló

Mar is eol dúinn go léir, ní féidir leis an ngnáthfheidhm VLOOKUP ach an luach meaitseála a aisghabháil ó raon sonraí eile. Mar sin féin, d'fhéadfadh go mbeadh cásanna ann inar mhaith leat an luach comhfhreagrach a fháil chomh maith leis an bhformáidiú cille, mar an dath líonadh, dath an chló, agus stíl an chló. Sa chuid seo, déanfaimid plé ar conas luachanna meaitseála a aisghabháil agus formáidiú foinse á chaomhnú in Excel.

Déan na céimeanna seo a leanas, le do thoil, chun a luach comhfhreagrach a chuardach agus a chur ar ais mar aon le formáidiú cille:

Céim 1: Cóipeáil an cód 1 isteach sa Mhodúl Cód Bileog

  1. Sa bhileog oibre tá na sonraí is mian leat a VLOOKUP, cliceáil ar dheis ar an táb bileog agus roghnaigh Féach an cód ón roghchlár comhthéacs. Féach an pictiúr:
  2. San oscailt Microsoft Visual Basic d’Fheidhmchláir fhuinneog, cóipeáil an cód VBA thíos i bhfuinneog an Chóid.
  3. Cód VBA 1: VLOOKUP chun formáidiú cille a fháil mar aon le luach cuardaigh
  4. Sub Worksheet_Change(ByVal Target As Range)
    'Updateby Extendoffice
        Dim I As Long
        Dim xKeys As Long
        Dim xDicStr As String
        On Error Resume Next
        Application.ScreenUpdating = False
        xKeys = UBound(xDic.Keys)
        If xKeys >= 0 Then
            For I = 0 To UBound(xDic.Keys)
                xDicStr = xDic.Items(I)
                If xDicStr <> "" Then
                    Range(xDic.Keys(I)).Interior.Color = _
                    Range(xDic.Items(I)).Interior.Color
                    Range(xDic.Keys(I)).Font.FontStyle = _
                    Range(xDic.Items(I)).Font.FontStyle
                    Range(xDic.Keys(I)).Font.Size = _
                    Range(xDic.Items(I)).Font.Size
                    Range(xDic.Keys(I)).Font.Color = _
                    Range(xDic.Items(I)).Font.Color
                    Range(xDic.Keys(I)).Font.Name = _
                    Range(xDic.Items(I)).Font.Name
                    Range(xDic.Keys(I)).Font.Underline = _
                    Range(xDic.Items(I)).Font.Underline
                Else
                    Range(xDic.Keys(I)).Interior.Color = xlNone
                End If
            Next
            Set xDic = Nothing
        End If
        Application.ScreenUpdating = True
    End Sub
    

Céim 2: Cóipeáil an cód 2 isteach i bhfuinneog an Mhodúil

  1. Fós sa Microsoft Visual Basic d’Fheidhmchláir fuinneog, cliceáil Ionsáigh > Modúil, agus ansin cóipeáil an cód VBA 2 thíos i bhfuinneog an Mhodúil.
  2. Cód VBA 2: VLOOKUP chun formáidiú cille a fháil mar aon le luach cuardaigh
  3. Public xDic As New Dictionary
    Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
        Dim xFindCell As Range
        On Error Resume Next
        Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
        If xFindCell Is Nothing Then
            LookupKeepFormat = ""
            xDic.Add Application.Caller.Address, ""
        Else
            LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
            xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
        End If
    End Function
    

Céim 3: Roghnaigh an rogha le haghaidh VBAproject

  1. Tar éis na cóid thuas a chur isteach, ansin cliceáil uirlisí > tagairtí sa Microsoft Visual Basic d’Fheidhmchláir fuinneog. Ansin seiceáil an Microsoft Scripting Runtime ticbhosca sa Tagairtí - VBAProject bosca dialóige. Féach screenshots:
  2. Ansin, cliceáil OK chun an bosca dialóige a dhúnadh, agus ansin an fhuinneog cód a shábháil agus a dhúnadh.

Céim 4: Clóscríobh an fhoirmle chun an toradh a fháil

  1. Anois, téigh ar ais go dtí an bhileog oibre, cuir an fhoirmle seo a leanas i bhfeidhm. Agus ansin, tarraing an láimhseáil líonta síos chun na torthaí go léir a fháil mar aon lena formáidiú. Féach ar an scáileán:
    =LookupKeepFormat(E2,$A$1:$C$10,3)

Nóta: san fhoirmle thuas:

  • E2 is é an luach a bheidh tú ag breathnú suas;
  • A1: C10 an bhfuil an raon tábla;
  • 3 uimhir cholúin an tábla óna dteastaíonn uait an luach comhoiriúnaithe a fháil.

4.2 Coinnigh an fhormáid dáta ó luach ar ais VLOOKUP

Agus an fheidhm VLOOKUP in úsáid chun luach le formáid dáta a chuardach agus a thabhairt ar ais, féadfar an toradh a tugadh ar ais a thaispeáint mar uimhir. Chun an fhormáid dáta a choinneáil sa toradh ar ais, ba chóir duit an fheidhm VLOOKUP a chur faoi iamh laistigh den fheidhm TEXT.

Céim 1: Cuir i bhfeidhm agus líon isteach an fhoirmle seo a leanas

Cuir an fhoirmle thíos i bhfeidhm i gcill bhán le do thoil. Ansin, tarraing an láimhseáil líonta chun an fhoirmle seo a chóipeáil chuig cealla eile.

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

Toradh:

Tá na dátaí meaitseála go léir tugtha ar ais mar a thaispeántar an scáileán thíos:

Nóta: San fhoirmle thuas:

  • E2 an bhfuil an luach cuardaigh;
  • A2: C9 an bhfuil an raon cuardaigh;
  • 3 an bhfuil uimhir an cholúin ar mhaith leat an luach a thabhairt ar ais;
  • FALSE léiríonn a fháil ar chluiche cruinn;
  • mm / dd / bbbb an formáid dáta is mian leat a choinneáil.

4.3 Nóta tráchta cille ar ais ó VLOOKUP

An raibh ort riamh na sonraí cille meaitseála agus an trácht a bhaineann leis a aisghabháil ag baint úsáide as VLOOKUP in Excel, mar a thaispeántar sa screenshot seo a leanas? Más ea, is féidir leis an Fheidhm Shainithe Úsáideora thíos cabhrú leat an tasc seo a chur i gcrích.

Céim 1: Cóipeáil an cód i Modúl

  1. Coinnigh síos an ALT + F11 eochracha a oscailt Microsoft Visual Basic d’Fheidhmchláir fhuinneog.
  2. cliceáil Ionsáigh > Modúil, ansin cóipeáil agus greamaigh an cód seo a leanas i bhFuinneog an Mhodúil.
    Cód VBA: Luach meaitseála Vlookup agus faigh ar ais le trácht cille:
    Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
    'Updateby Extendoffice
        Application.Volatile
        Dim xRet As Variant 'could be an error
        Dim xCell As Range
        xRet = Application.Match(LookVal, FTable.Columns(1), FType)
        If IsError(xRet) Then
            VlookupComment = "Not Found"
        Else
            Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
            VlookupComment = xCell.Value
            With Application.Caller
                If Not .Comment Is Nothing Then
                    .Comment.Delete
                End If
                If Not xCell.Comment Is Nothing Then
                    .AddComment xCell.Comment.Text
                End If
            End With
        End If
    End Function
  3. Ansin sábháil agus dún an fhuinneog cód.

Céim 2: Clóscríobh an fhoirmle chun an toradh a fháil

  1. Anois, cuir isteach an fhoirmle seo a leanas, agus tarraing an láimhseáil líonta chun an fhoirmle seo a chóipeáil chuig cealla eile. Tabharfaidh sé na luachanna comhoiriúnaithe agus na tuairimí araon ar ais ag an am céanna, féach an scáileán scáileáin:
    =vlookupcomment(D2,$A$2:$B$9,2,FALSE)

Nóta: San fhoirmle thuas:

  • D2 an luach cuardaigh a theastaíonn uait a luach comhfhreagrach a thabhairt ar ais;
  • A2: B9 an bhfuil an tábla sonraí is mian leat a úsáid;
  • 2 an uimhir cholúin ina bhfuil an luach comhoiriúnaithe is mian leat a thabhairt ar ais;
  • FALSE léiríonn a fháil ar chluiche cruinn.

4.4 Uimhreacha VLOOKUP arna stóráil mar théacs

Mar shampla, tá raon sonraí agam ina bhfuil an uimhir aitheantais sa tábla bunaidh i bhformáid uimhreach agus ina bhfuil an uimhir aitheantais sna cealla cuardaigh stóráilte mar théacs, seans go dtiocfaidh tú ar earráid #N/A agus an gnáthfheidhm VLOOKUP á úsáid agat. Sa chás seo, chun an t-eolas ceart a fháil, is féidir leat na feidhmeanna TEXT agus LUACH a fhilleadh laistigh den fheidhm VLOOKUP.Is é seo thíos an fhoirmle chun é seo a bhaint amach:

Céim 1: Cuir i bhfeidhm agus líon isteach an fhoirmle seo a leanas

Cuir an fhoirmle seo a leanas isteach i gcill bhán le do thoil, agus ansin tarraing an láimhseáil líonta anuas chun an fhoirmle seo a chóipeáil.

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

Toradh:

Anois, gheobhaidh tú na torthaí cearta mar a thaispeántar thíos scáileáin:

Nótaí:

  • San fhoirmle thuas:
    • D2 an luach cuardaigh a theastaíonn uait a luach comhfhreagrach a thabhairt ar ais;
    • A2: B8 an bhfuil an tábla sonraí is mian leat a úsáid;
    • 2 an uimhir cholúin ina bhfuil an luach comhoiriúnaithe is mian leat a thabhairt ar ais;
    • 0 léiríonn a fháil ar chluiche cruinn.
  • Oibríonn an fhoirmle seo go maith freisin mura bhfuil tú cinnte cá bhfuil uimhreacha agat agus cá bhfuil téacs agat.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations