Note: The other languages of the website are Google-translated. Back to English
Logáil isteach  \/ 
x
or
x
Cláraigh  \/ 
x

or

Feidhm VLOOKUP le roinnt samplaí bunúsacha agus ardleibhéil in Excel

In Excel, is feidhm chumhachtach í an fheidhm VLOOKUP d’fhormhór na n-úsáideoirí Excel, a úsáidtear chun luach a lorg sa chuid is faide ar chlé den raon sonraí, agus luach meaitseála a thabhairt ar ais sa tsraith chéanna ó cholún a shonraigh tú mar atá thíos den ghrianghraf a thaispeántar . Tá an rang teagaisc seo ag caint ar conas feidhm VLOOKUP a úsáid le roinnt samplaí bunúsacha agus ardleibhéil in Excel.

Clár ábhair:

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

2. Samplaí bunúsacha VLOOKUP

3. Ard-shamplaí VLOOKUP

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

5. Íoslódáil comhaid shamplacha VLOOKUP


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

Comhréir na feidhme VLOOKUP:

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

Argóintí:

Lookup_value: An luach atá tú ag iarraidh a chuardach. Caithfidh sé a bheith sa chéad cholún den raon table_array.

Table_array: An raon sonraí nó an tábla ina bhfuil an colún luacha cuardaigh agus an colún luacha toraidh suite.

Col_index_num: Líon na gcolún óna dtabharfar an luach comhoiriúnaithe ar ais. Tosaíonn sé le 1 ón gcolún is faide ar chlé sa tsraith tábla.

Raon_lookup: Luach loighciúil a chinneann an bhfillfidh an fheidhm VLOOKUP seo comhoiriúnú cruinn nó neas-mheaitseáil.

  • Meaitseáil thart - 1 / TRUE: Mura bhfaightear comhoiriúnú cruinn, déanann an fhoirmle cuardach ar an mheaitseáil is gaire - an luach is mó atá níos lú ná an luach cuardaigh. Sa chás seo, ba cheart duit an colún lookup a shórtáil in ord ardaitheach.
    = VLOOKUP (lookup_value, table_array, col_index, TRUE)
    = VLOOKUP (lookup_value, table_array, col_index, 1)
  • Cluiche díreach - 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.
    = VLOOKUP (lookup_value, table_array, col_index, FALSE)
    = VLOOKUP (lookup_value, table_array, col_index, 0)

Nótaí:

  • 1. Ní lorgaíonn feidhm Vlookup ach luach ó chlé go deas.
  • 2. Má tá luachanna meaitseála iolracha ann bunaithe ar an luach cuardaigh, ní thabharfar ar ais ach an chéad mheaitseáil trí úsáid a bhaint as an bhfeidhm Vlookup.
  • 3. Fillfidh sé an luach earráide # N / A mura féidir an luach cuardaigh a fháil.

Samplaí bunúsacha VLOOKUP

1. Déan Vlookup comhoiriúnach cruinn agus thart ar Vlookup

Déan Vlookup comhoiriúnach cruinn in Excel

De ghnáth, má tá tú ag lorg comhoiriúnú cruinn leis an bhfeidhm Vlookup, ní gá duit ach FALSE a úsáid san 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:

1. Cuir an fhoirmle thíos i bhfeidhm i gcill bhán inar mian leat an toradh a fháil:

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

2. Agus ansin, tarraing an láimhseáil líonta síos go dtí na cealla a theastaíonn uait an fhoirmle seo a líonadh, agus gheobhaidh tú na torthaí de réir mar is gá duit. Féach an pictiúr:

Nótaí:

  • 1. San fhoirmle thuas, F2 an luach atá tú ag iarraidh a luach meaitseála a thabhairt ar ais, A2: D7 is é an tsraith tábla, an uimhir 3 is í uimhir an cholúin a bhfilltear do luach comhoiriúnaithe agus an FALSE tagraíonn sé don mheaitseáil bheacht.
  • 2. Mura bhfaightear luach do chritéir sa raon sonraí, taispeánfar luach earráide # N / A.

Déan Vlookup meaitseála in Excel

Tá an mheaitseáil thart úsáideach chun luachanna a chuardach idir raonta sonraí. Mura bhfaightear an comhoiriúnú cruinn, fillfidh an neas-amharc Vlookup an luach is mó atá níos lú ná an luach cuardaigh.

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

1. Iontráil an fhoirmle seo a leanas i gcill inar mian leat an toradh a chur:

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

2. Ansin, tarraing an láimhseáil líonta síos go dtí na cealla chun an fhoirmle seo a chur i bhfeidhm, agus gheobhaidh tú na neasmheaitseálacha bunaithe ar na luachanna tugtha, féach an scáileán:

Nótaí:

  • 1. San fhoirmle thuas, D2 an luach atá tú ag iarraidh a fhaisnéis choibhneasta a thabhairt ar ais, A2: B9 is é an raon sonraí, an uimhir 2 léiríonn uimhir an cholúin go gcuirtear do luach comhoiriúnaithe ar ais agus an TRUE tagraíonn sé don neas-mheaitseáil.
  • 2. Fillfidh an neas-mheaitseáil an luach is mó atá níos lú ná do luach cuardaigh sonrach.
  • 3. Chun feidhm 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, ar shlí eile tabharfaidh sé toradh mícheart ar ais.

2. Déan Vlookup cás-íogair in Excel

De réir réamhshocraithe, feidhmíonn feidhm Vlookup amharc-chás neamhíogair a chiallaíonn go ndéileálann sé le carachtair litreacha beaga agus uachtair mar an gcéanna. Am éigin, b’fhéidir go mbeidh ort cuardach cás-íogair a dhéanamh in Excel, is féidir leis na feidhmeanna Innéacs, Meaitseáil agus Beacht nó feidhmeanna Cuardaigh agus Díreacha fabhar a dhéanamh duit.

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.

Foirmle 1: Ag baint úsáide as feidhmeanna EXACT, INDEX, MATCH

1. Iontráil nó cóipeáil an fhoirmle eagar thíos i gcill bhán inar mian leat an toradh a fháil:

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

2. Ansin, brúigh Ctrl + Shift + Iontráil eochracha ag an am céanna chun an chéad toradh a fháil, agus ansin an chill fhoirmle a roghnú, tarraing an láimhseáil líonta síos go dtí na cealla a theastaíonn uait an fhoirmle seo a líonadh, ansin gheobhaidh tú na torthaí cearta a theastaíonn uait. Féach an pictiúr:

Nótaí:

  • 1. San fhoirmle thuas, A2: A10 an colún ina bhfuil na luachanna sonracha ar mhaith leat breathnú orthu, F2 is é an luach lookup, C2: C10 an colún as a bhfillfear an toradh.
  • 2. Má aimsítear iliomad cluichí, fillfidh an fhoirmle seo an chéad mheaitseáil i gcónaí.

Foirmle 2: Feidhmeanna Cuardaigh agus Díreach a úsáid

1. Cuir an fhoirmle thíos i bhfeidhm i gcill bhán más mian leat an toradh a fháil:

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

2. Ansin, tarraing an láimhseáil líonta síos go dtí na cealla ar mhaith leat an fhoirmle seo a chóipeáil, agus gheobhaidh tú na luachanna comhoiriúnaithe le cás-íogair mar atá thíos an pictiúr a thaispeántar:

Nótaí:

  • 1. San fhoirmle thuas, A2: A10 an colún ina bhfuil na luachanna sonracha ar mhaith leat breathnú orthu, F2 is é an luach lookup, C2: C10 an colún as a bhfillfear an toradh.
  • 2. Má aimsítear iliomad cluichí, fillfidh an fhoirmle seo an cluiche deireanach i gcónaí.

3. Luachanna Vlookup ó dheis go clé in Excel

Breathnaíonn feidhm Vlookup luach i gcónaí sa cholún is faide ar chlé de raon sonraí agus tugann sí an luach comhfhreagrach ar ais ó cholún ar dheis. Más mian leat Vlookup droim ar ais a dhéanamh a chiallaíonn luach sonrach ar dheis a bhreathnú agus a luach comhfhreagrach a chur ar ais sa cholún ar chlé mar atá thíos an pictiúr a thaispeántar:

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


4. Vlookup an dara, an naoú nó an luach meaitseála deireanach in Excel

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

Vlookup agus seol an dara nó an naoú luach meaitseála ar ais

Má tá liosta ainmneacha agat i gcolún A, an cúrsa oiliúna a cheannaigh siad i gcolún B, agus anois, tá tú ag iarraidh an 2ú nó an naoú cúrsa oiliúna a cheannaigh an custaiméir ar leith a fháil. Féach an pictiúr:

1. Chun an dara nó an naoú luach meaitseála a fháil bunaithe ar na critéir a thugtar, cuir an fhoirmle eagar seo a leanas i gcill bhán:

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

2. Ansin brúigh Ctrl + Shift + Iontráil eochracha le chéile chun an chéad toradh a fháil, agus ansin an chill fhoirmle a roghnú, tarraing an láimhseáil líonta síos go dtí na cealla is mian leat an fhoirmle seo a líonadh, agus taispeánadh an dara luach comhoiriúnaithe go léir bunaithe ar na hainmneacha tugtha ag an am céanna, féach an scáileán:

Nóta:

  • San fhoirmle seo, A2: A14 Is é an raon leis na luachanna go léir le haghaidh lookup, B2: B14 is é raon na luachanna meaitseála ar mhaith leat filleadh uathu, E2 is é an luach lookup, agus an uimhir dheireanach 2 léiríonn sé an dara luach comhoiriúnaithe is mian leat a fháil, más mian leat an tríú luach meaitseála a thabhairt ar ais, ní gá duit ach é a athrú go 3 de réir mar is gá duit.

Vlookup agus faigh an luach meaitseála deireanach ar ais

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 Cuir ar ais an Luach Meaitseála Deireanach féadfaidh rang teagaisc cabhrú leat an luach meaitseála deireanach a fháil i mionsonraí.


5. Luachanna meaitseála Vlookup idir dhá luach nó dáta ar leith

Uaireanta, b’fhéidir gur mhaith leat luachanna a bhreathnú idir dhá luach nó dháta agus na torthaí comhfhreagracha a thabhairt ar ais mar atá thíos an pictiúr a thaispeántar, sa chás seo, is féidir leat an fheidhm LOOKUP agus tábla sórtáilte a úsáid.

Luachanna meaitseála Vlookup idir dhá luach nó dátaí tugtha leis an bhfoirmle

1. Ar dtús, ba chóir go mbeadh do thábla bunaidh ina raon sonraí sórtáilte. Agus ansin, cóipeáil nó cuir isteach an fhoirmle seo a leanas i gcill bhán:

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

2. Ansin, tarraing an láimhseáil líonta chun an fhoirmle seo a líonadh chuig cealla eile a theastaíonn uait, agus anois, gheobhaidh tú gach taifead comhoiriúnaithe bunaithe ar an luach tugtha, féach an scáileán:

Nótaí:

  • 1. San fhoirmle thuas, A2: A6 is é an raon luachanna níos lú agus B2: B6 is é an raon uimhreacha níos mó i do raon sonraí, an E2 an luach tugtha a theastaíonn uait a luach comhfhreagrach a fháil, C2: C6 na sonraí colúin ar mhaith leat a bhaint astu.
  • 2. Is féidir an fhoirmle seo a úsáid freisin chun luachanna comhoiriúnaithe a bhaint idir dhá dháta mar a thaispeántar thíos an scáileán:

Luachanna meaitseála Vlookup idir dhá luach nó dáta ar leith le gné úsáideach

Má tá tú pianmhar leis an bhfoirmle thuas, anseo, tabharfaidh mé uirlis éasca isteach - 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 nó an dáta sonrach idir dhá luach nó dáta gan cuimhneamh ar aon fhoirmle.   Cliceáil chun Kutools le haghaidh Excel a íoslódáil anois!


6. Cártaí fiáine a úsáid le haghaidh páirt-mheaitseálacha i bhfeidhm Vlookup

In Excel, is féidir na cártaí fiáine a úsáid laistigh den fheidhm Vlookup, a dhéanann cluiche páirteach a mheaitseáil ar luach amharc. 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?

1. Ní oibríonn gnáthfheidhm Vlookup i gceart, ní mór duit an téacs nó an tagairt cille a cheangal le cárta fiáin, cóipeáil nó cuir isteach an fhoirmle seo a leanas i gcill bhán:

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

2. Ansin, tarraing an láimhseáil líonta chun an fhoirmle seo a líonadh chuig cealla eile atá uait, agus tá na scóir comhoiriúnaithe uile curtha ar ais mar atá thíos an pictiúr a thaispeántar:

Nótaí:

  • 1. San fhoirmle thuas, E2 & ”*” is é an luach lookup, an luach i E2 agus an * cárta fiáin (léiríonn “*” carachtar amháin nó carachtair ar bith), A2: C11 is é an raon lookup, an uimhir 3 an colún ina bhfuil an luach le filleadh.
  • 2. Vlookup agus cártaí fiáine á n-úsáid agat, ní mór duit an modh meaitseála cruinn a shocrú le FALSE nó 0 don argóint dheireanach i bhfeidhm Vlookup.

Leid:

1. Faigh agus seol ar ais na luachanna meaitseála ag críochnú le luach sonrach, cuir an fhoirmle seo i bhfeidhm le do thoil: =VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)

2. Chun an luach comhoiriúnaithe a chuardach agus a thabhairt ar ais bunaithe ar chuid den tsreang téacs, cibé an bhfuil an téacs sonraithe os comhair, taobh thiar nó i lár an tsreangáin téacs, ní gá duit ach dhá * charachtar a cheangal timpeall na tagartha cille nó an téacs. Déan an fhoirmle seo le do thoil: =VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)


7. Luachanna amhairc ó bhileog oibre eile

De ghnáth, b’fhéidir go mbeidh ort oibriú le níos mó ná bileog oibre amháin, is féidir an fheidhm Vlookup a úsáid chun sonraí ó bhileog eile a chuardach 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:

1. Iontráil nó cóipeáil an fhoirmle thíos i gcill bhán inar mian leat na míreanna comhoiriúnaithe a fháil:

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

2. Ansin, tarraing an láimhseáil líonta síos go dtí na cealla a theastaíonn uait an fhoirmle seo a chur i bhfeidhm, agus gheobhaidh tú na torthaí comhfhreagracha de réir mar a theastaíonn uait, féach an pictiúr:

Nóta: San fhoirmle thuas:

  • A2 is ionann é agus an luach amharc;
  • Bileog sonraí is ainm don bhileog oibre ar mhaith leat sonraí a chuardach uaidh, (Má tá spás nó carachtair phoncaíochta in ainm na bileoige, ba cheart duit luachana aonair a iamh timpeall ainm na bileoige, ar shlí eile, is féidir leat ainm na bileoige mar = VLOOKUP (A2, a úsáid go díreach). Bileog Sonraí! $ A $ 2: $ C $ 15,3,0));
  • A2: C15 an bhfuil an raon sonraí sa Bhileog Sonraí ina bhfuilimid ag cuardach sonraí;
  • an líon 3 is í uimhir an cholúin ina bhfuil sonraí comhoiriúnaithe ar mhaith leat filleadh orthu.

8. Luachanna amhairc ó leabhar oibre eile

Labhróidh an chuid seo faoi chuardach agus seolfaidh sé na luachanna meaitseála ar ais ó leabhar oibre difriúil tríd an bhfeidhm Vlookup a úsáid.

Mar shampla, tá na liostaí táirgí agus costais sa chéad leabhar oibre, anois, ba mhaith leat an costas comhfhreagrach a bhaint as an dara leabhar oibre bunaithe ar mhír an táirge mar atá thíos an pictiúr a thaispeántar.

1. Chun an costas coibhneasta a aisghabháil ó leabhar oibre eile, ar dtús, oscail na leabhair oibre is mian leat a úsáid, ansin cuir an fhoirmle seo a leanas i gcill inar mian leat an toradh a chur:

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

2. Ansin, tarraing agus cóipeáil an fhoirmle seo chuig cealla eile atá uait, féach an pictiúr:

Nótaí:

  • 1. San fhoirmle thuas:
    B2 is ionann é agus an luach amharc;
    [Liosta táirgí.xlsx] Bileog1 is ainm don leabhar oibre agus don bhileog oibre ar mhaith leat sonraí a chuardach uaidh, (Tá an tagairt don leabhar oibre faoi iamh idir lúibíní cearnacha, agus tá an bileog iomlán leabhar oibre + faoi iamh i luachana aonair);
    A2: B6 an bhfuil an raon sonraí i mbileog oibre leabhar oibre eile ina bhfuilimid ag cuardach sonraí;
    an líon 2 is í uimhir an cholúin ina bhfuil sonraí comhoiriúnaithe ar mhaith leat filleadh orthu.
  • 2. Má dhúnfar an leabhar oibre lookup, taispeánfar an cosán comhaid iomlán don leabhar oibre lookup san fhoirmle mar a leanas an pictiúr a thaispeántar:

9. Vlookup agus seol téacs bán nó sonrach ar ais in ionad luach earráide 0 nó # N / A.

De ghnáth, nuair a chuireann tú an fheidhm vlookup i bhfeidhm chun an luach comhfhreagrach a thabhairt ar ais, má tá do chill mheaitseála bán, fillfidh sé 0 ar ais, agus mura bhfaighfear do luach meaitseála, gheobhaidh tú luach earráide # N / A mar atá thíos an pictiúr a thaispeántar. In áit an luach 0 nó # N / A a thaispeáint le cill bhán nó luach eile is mian leat, seo Vlookup Chun Luach Bán nó Luach Sonrach a Fhilleadh In ionad 0 Nó N / B. féadfaidh an rang teagaisc fabhar a dhéanamh céim ar chéim.


Samplaí chun cinn VLOOKUP

1. Amharc dhá bhealach le feidhm Vlookup (Vlookup i ndiaidh a chéile agus colún)

Uaireanta, b’fhéidir go mbeidh ort amharclann déthoiseach a dhéanamh, rud a chiallaíonn go Vlookup sa tsraith agus sa cholún araon ag an am céanna. Ligean le rá, má tá an raon sonraí seo a leanas agat, agus anois, b’fhéidir go mbeidh ort an luach do tháirge áirithe a fháil i gceathrú sonraithe. Tabharfaidh an chuid seo roinnt foirmle isteach chun déileáil leis an bpost seo in Excel.

Foirmle 1: Feidhmeanna VLOOKUP agus MATCH a úsáid

In Excel, is féidir leat teaglaim d’fheidhmeanna VLOOKUP agus MATCH a úsáid chun amharc dhá bhealach a dhéanamh, cuir an fhoirmle seo a leanas i gcill bhán le do thoil, agus ansin brúigh Iontráil eochair chun an toradh a fháil.

=VLOOKUP(H1, $A$2:$E$6, MATCH(H2, $A$1:$E$1, 0), FALSE)

Nóta: San fhoirmle thuas:

  • H1: an luach cuardaigh sa cholún ar mhaith leat an luach comhfhreagrach a fháil bunaithe air;
  • A2: E6: an raon sonraí lena n-áirítear ceanntásca as a chéile;
  • H2: an luach amharc as a chéile ar mhaith leat an luach comhfhreagrach a fháil bunaithe air;
  • A1: E1: cealla ceanntásca na gcolún.

Foirmle 2: Feidhmeanna INDEX agus MATCH a úsáid

Seo foirmle eile a chabhróidh leat cuardach déthoiseach a dhéanamh, cuir an fhoirmle thíos i bhfeidhm, agus ansin brúigh Iontráil eochair chun an toradh a theastaíonn uait a fháil.

=INDEX($B$2:$E$6, MATCH(H1, $A$2:$A$6, 0), MATCH(H2, $B$1:$E$1, 0))

Nóta: San fhoirmle thuas:

  • B2: E6: an raon sonraí chun an mhír mheaitseáilte a chur ar ais ó;
  • H1: an luach cuardaigh sa cholún ar mhaith leat an luach comhfhreagrach a fháil bunaithe air;
  • A2: A6: tá an táirge a theastaíonn uait a lorg sna ceanntásca as a chéile.
  • H2: an luach amharc as a chéile ar mhaith leat an luach comhfhreagrach a fháil bunaithe air;
  • B1: E1: tá an ráithe a theastaíonn uait a lorg i gceanntásca na gcolún.

2. Luach meaitseála Vlookup bunaithe ar dhá chritéar nó níos mó

Is furasta 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? 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.

Foirmle 1: Feidhm LOOKUP a úsáid

Cuir an fhoirmle thíos i bhfeidhm i gcill inar mian leat an toradh a fháil, agus ansin brúigh Iontráil eochair, féach an scáileán:

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

Nótaí:

  • 1. San fhoirmle thuas:
    A2: A12 = G1: modhanna chun critéir G1 a chuardach i raon A2: A12;
    B2: B12 = G2: modhanna chun critéir G2 a chuardach i raon B2: B12;
    D2: D12: an raon ar mhaith leat an luach comhfhreagrach a thabhairt ar ais.
  • 2. Má tá níos mó ná dhá chritéar agat, níl le déanamh agat ach na critéir eile a cheangal leis an bhfoirmle, mar shampla: =LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))

Foirmle 2: Feidhmeanna INDEXT agus MATCH a úsáid

Is féidir an teaglaim d’fheidhm Innéacs agus Meaitseála a úsáid freisin chun an luach comhoiriúnaithe a thabhairt ar ais bunaithe ar ilchritéir. Déan an fhoirmle seo a leanas a chóipeáil nó a iontráil:

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

Ansin, brúigh Ctrl + Shift + Iontráil eochracha le chéile chun an luach coibhneasta a theastaíonn uait a fháil. Féach an pictiúr:

Nótaí:

  • 1. San fhoirmle thuas:
    A2: A12 = G1: modhanna chun critéir G1 a chuardach i raon A2: A12;
    B2: B12 = G2: modhanna chun critéir G2 a chuardach i raon B2: B12;
    D2: D12: an raon ar mhaith leat an luach comhfhreagrach a thabhairt ar ais.
  • 2. Má tá níos mó ná dhá chritéar agat, níl le déanamh agat ach na critéir nua a cheangal leis an bhfoirmle, mar shampla: =INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))

3. Vlookup chun luachanna meaitseála iolracha a thabhairt ar ais le coinníoll amháin nó níos mó

In Excel, déanann feidhm Vlookup cuardach ar luach agus gan an chéad luach meaitseála a thabhairt ar ais ach má aimsítear iliomad luachanna comhfhreagracha. Uaireanta, b’fhéidir gur mhaith leat na luachanna comhfhreagracha uile a thabhairt ar ais i ndiaidh a chéile, i gcolún nó i gcill aonair. Labhróidh an chuid seo faoi conas na luachanna meaitseála iolracha a thabhairt ar ais le coinníoll amháin nó níos mó i leabhar oibre.

Féach ar na luachanna meaitseála go léir bunaithe ar choinníoll amháin nó níos mó go cothrománach

Féach ar na luachanna meaitseála go léir bunaithe ar choinníoll amháin go cothrománach:

Chun gach luach meaitseála a Vlookup agus a thabhairt ar ais bunaithe ar luach sonrach amháin go cothrománach, is í an fhoirmle chineálach:

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range) - m, ""), COLUMN() - n)), "")
nótaí: m is í an tsraithuimhir den chéad chill sa raon fillte lúide 1.
      n is é uimhir cholún na chéad chill fhoirmle lúide 1.

1. Cuir an fhoirmle thíos i bhfeidhm i gcill bhán, 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:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF($F1=$A$2:$A$20, ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2. Agus ansin, roghnaigh an chéad chill fhoirmle, agus tarraing an láimhseáil líonta chuig na cealla cearta go dtí go dtaispeántar cill bhán, agus go mbainfear gach earra comhfhreagrach, féach an scáileán:

Leid:

Má tá luachanna comhoiriúnaithe dúblacha ar an liosta a cuireadh ar ais, chun neamhaird a dhéanamh de na dúblacha, bain úsáid as na foirmlí seo, ansin brúigh Iontráil chun an chéad toradh a fháil: =IFERROR(INDEX($C$2:$C$20,MATCH($F1,$A$2:$A$20,0)),"")

Téigh ar aghaidh leis an bhfoirmle seo a iontráil: =IFERROR(INDEX($C$2:$C$20,MATCH(1,($F1=$A$2:$A$20)*ISNA(MATCH($C$2:$C$20,$F2:F2,0)),0)),"") isteach i gcill in aice leis an gcéad toradh, agus ansin brúigh Ctrl + Shift + Iontráil eochracha le chéile chun an dara toradh a fháil, ansin tarraing an fhoirmle seo chuig cealla cearta chun na luachanna comhoiriúnaithe eile go léir a fháil go dtí go dtaispeántar cealla bán, féach an scáileán:


Féach ar na luachanna meaitseála go léir bunaithe ar dhá choinníoll nó níos mó go cothrománach:

Chun gach luach meaitseála a Vlookup agus a thabhairt ar ais bunaithe ar luachanna níos sainiúla go cothrománach, is í an fhoirmle chineálach:

=IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2))), ROW(return_range) - m, ""), COLUMN() - n)),"")
nótaí: m is í an tsraithuimhir den chéad chill sa raon fillte lúide 1.
      n is é uimhir cholún na chéad chill fhoirmle lúide 1.

1. Cuir an fhoirmle seo a leanas i gcill bhán inar mian leat an toradh a aschur:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($F1=$A$2:$A$20)) * (--($F2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2. Ansin, roghnaigh an chill fhoirmle agus tarraing an láimhseáil líonta chuig na cealla cearta go dtí go mbeidh taispeántais chealla bána, agus na luachanna comhoiriúnaithe uile bunaithe ar na critéir shonracha curtha ar ais, féach an scáileán:

nótaí: Le haghaidh tuilleadh critéar, níl le déanamh agat ach an lookup_value agus lookup_range a cheangal leis an bhfoirmle, mar shampla: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), COLUMN() - n)),"").


Féach ar na luachanna meaitseála go léir bunaithe ar choinníoll amháin nó níos mó go hingearach

Féach ar na luachanna meaitseála go léir bunaithe ar choinníoll amháin go hingearach:

Chun gach luach meaitseála a Vlookup agus a thabhairt ar ais bunaithe ar luach sonrach amháin go hingearach, is í an fhoirmle chineálach:

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range )- m ,""), ROW() - n )),"")
nótaí: m is í an tsraithuimhir den chéad chill sa raon fillte lúide 1.
      n is í an tsraithuimhir den chéad chill fhoirmle lúide 1.

1. Cóipeáil nó clóscríobh an fhoirmle seo a leanas i gcill inar mian leat an toradh a fháil, 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:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(E$2=$A$2:$A$20, ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2. Ansin, roghnaigh an chéad chill fhoirmle, agus tarraing an láimhseáil líonta síos go cealla eile go dtí go dtaispeántar cill bhán, agus go mbeidh na míreanna comhfhreagracha uile liostaithe i gcolún, féach an scáileán:

Leid:

Chun neamhaird a dhéanamh ar na dúblacha sna luachanna meaitseála ar ais, bain úsáid as na foirmlí seo: =IFERROR(INDEX($C$2:$C$20,MATCH(0,COUNTIF($F$1:F1,$C$2:$C$20)+($A$2:$A$20<>$E$2),0)),"")

Ansin brúigh Ctrl + Shift + Iontráil eochracha le chéile chun an chéad luach comhoiriúnaithe a fháil, agus ansin an cill fhoirmle seo a tharraingt anuas go cealla eile go dtí go dtaispeánann cealla bán, agus gheobhaidh tú an toradh de réir mar a theastaíonn uait:


Féach ar na luachanna meaitseála go léir bunaithe ar dhá choinníoll nó níos mó go hingearach:

Chun gach luach meaitseála a Vlookup agus a thabhairt ar ais bunaithe ar luachanna níos sainiúla go hingearach, is í an fhoirmle chineálach:

=IFERROR(INDEX(return_range, SMALL(IF(1=((--(lookup_value1=lookup_range1)) * ( --(lookup_value2=lookup_range2))), ROW(return_range)-m,""), ROW()-n)),"")
nótaí: m is í an tsraithuimhir den chéad chill sa raon fillte lúide 1.
      n is í an tsraithuimhir den chéad chill fhoirmle lúide 1.

1. Cóipeáil an fhoirmle thíos i gcill bhán, agus ansin brúigh Ctrl + Shift + Iontráil eochracha le chéile chun an chéad earra comhoiriúnaithe a fháil.

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($E$2=$A$2:$A$20)) * (--($F$2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2. Ansin tarraing an chill fhoirmle síos go cealla eile go dtí go dtaispeántar cill bhán, féach an scáileán:

nótaí: Le haghaidh tuilleadh critéar, níl le déanamh agat ach an lookup_value agus lookup_range a cheangal leis an bhfoirmle, mar shampla: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), ROW() - n)),"").


Vlookup na luachanna meaitseála bunaithe ar dhá choinníoll nó níos mó i gcill aonair

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

Vlookup na luachanna meaitseála bunaithe ar riocht amháin i gcill aonair:

Cuir an fhoirmle thíos thíos i bhfeidhm i gcill bhán, agus ansin brúigh Ctrl + Shift + Iontráil eochracha le chéile chun an toradh a fháil:

=TEXTJOIN(",",TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,""))

Leid:

Chun neamhaird a dhéanamh ar na dúblacha sna luachanna meaitseála ar ais, bain úsáid as na foirmlí seo: =TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$20, IF(F1=$A$2:$A$20, $C$2:$C$20, ""), 0),"")=MATCH(ROW($C$2:$C$20), ROW($C$2:$C$20)), $C$2:$C$20, ""))


Vlookup na luachanna meaitseála bunaithe ar dhá choinníoll nó níos mó i gcill aonair:

Chun déileáil le ilchoinníollacha agus na luachanna meaitseála uile á gcur ar ais i gcill aonair, cuir an fhoirmle thíos i bhfeidhm, agus ansin brúigh Ctrl + Shift + Iontráil eochracha le chéile chun an toradh a fháil:

=TEXTJOIN(",",TRUE,IF(($A$2:$A$20=F1)*($B$2:$B$20=F2),$C$2:$C$20,""))

Nótaí:

1. Níl feidhm TEXTJOIN ar fáil ach in Excel 2019 agus in Office 365.

2. Má úsáideann tú Excel 2016 agus leaganacha níos luaithe, bain úsáid as Feidhm Sainmhínithe Úsáideora na n-alt thíos:


4. Vlookup chun sraith iomlán nó iomlán de chill mheaitseáilte a thabhairt ar ais

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

1. Cóipeáil nó clóscríobh an fhoirmle thíos i gcill bhán inar mian leat an toradh a aschur, agus brúigh Iontráil eochair chun an chéad luach a fháil.

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

2. Ansin, tarraing an chill fhoirmle ar an taobh dheis go dtí go dtaispeántar sonraí an tsraith iomláin, féach an scáileán:

nótaí: San fhoirmle thuas, F2 an luach cuardaigh atá tú ag iarraidh an tsraith iomlán a thabhairt ar ais bunaithe ar, A1: D12 an raon sonraí is mian leat a úsáid, A1 léiríonn sé an chéad uimhir cholúin laistigh de do raon sonraí.

Leid:

Má aimsítear sraitheanna iolracha bunaithe ar an luach comhoiriúnaithe, chun na sraitheanna comhfhreagracha uile a chur ar ais, cuir an fhoirmle seo i bhfeidhm le do thoil: =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),""), 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 ceart go dtí na cealla, féach an scáileán:

Agus ansin tarraing an láimhseáil líonta síos ar fud na gcealla chun na sraitheanna meaitseála go léir a fháil mar a thaispeántar thíos an scáileán:


5. Déan feidhm iolrach Vlookup (Vlookup neadaithe) in Excel

Uaireanta, b’fhéidir gur mhaith leat luachanna a chuardach i dtáblaí iolracha, má tá an luach breathnóireachta a thugtar in aon cheann de na táblaí mar atá faoi bhun an scáileáin a thaispeántar, sa chás seo, is féidir leat feidhm Vlookup amháin nó níos mó a chomhcheangal le feidhm IFERROR chun il-amharc a dhéanamh.

Is í an fhoirmle chineálach d'fheidhm Vlookup neadaithe:

=IFERROR(VLOOKUP(lookup_value,table1,col,0),IFERROR(VLOOKUP(lookup_value,table2,col,0),VLOOKUP(lookup_value,table3,col,0)))

Nóta:

  • lookup_value: an luach atá á lorg agat;
  • Table1, Table2, Table3, ...: na táblaí ina bhfuil an luach cuardaigh agus an luach toraidh;
  • col: uimhir an cholúin sa tábla óna dteastaíonn uait an luach meaitseála a thabhairt ar ais.
  • 0: Úsáidtear é seo le haghaidh cluiche cruinn.

1. Cuir an fhoirmle seo a leanas i gcill bhán le do thoil inar mian leat an toradh a chur:

=IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),VLOOKUP(J3,$G$3:$H$7,2,0)))

2. Ansin, tarraing an láimhseáil líonta síos go dtí na cealla ar mhaith leat an fhoirmle seo a chur i bhfeidhm, agus tá na luachanna comhoiriúnaithe uile curtha ar ais mar atá thíos an pictiúr a thaispeántar:

Nótaí:

  • 1. San fhoirmle thuas, J3 an luach atá á lorg agat; A3: B7, D3: E7, G3: H7 an bhfuil na raonta tábla ina bhfuil an luach cuardaigh agus an luach toraidh; An uimhir 2 is í uimhir an cholúin sa raon chun an luach meaitseála a thabhairt ar ais ó.
  • 2. Mura féidir an luach cuardaigh a fháil, taispeántar luach earráide, chun téacs inléite a chur in ionad na hearráide, bain úsáid as an bhfoirmle seo le do thoil: =IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),IFERROR(VLOOKUP(J3,$G$3:$H$7,2,0),"can't find")))

6. Vlookup le seiceáil an bhfuil luach ann bunaithe ar shonraí liosta i gcolún eile

Is féidir leis an bhfeidhm Vlookup cabhrú leat a sheiceáil an bhfuil luachanna ann bunaithe ar liosta eile, mar shampla, más mian leat na hainmneacha i gcolún C a lorg agus díreach Sea nó Níl a fháil ar ais má aimsítear an t-ainm nó nach bhfuil i gcolún A mar atá faoi bhun an scáileáin a thaispeántar.

1. Cuir an fhoirmle seo a leanas i gcill bhán le do thoil:

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

2. Ansin, tarraing an láimhseáil líonta síos go dtí na cealla a theastaíonn uait an fhoirmle seo a líonadh, agus gheobhaidh tú an toradh de réir mar a theastaíonn uait, féach an pictiúr:

nótaí: San fhoirmle thuas, C2 an luach cuardaigh is mian leat a sheiceáil; A2: A10 an bhfuil liosta na raon ón áit a bhfaighfear luachanna amharc; an uimhir 1 an uimhir cholúin ón áit ar mhaith leat luach a fháil i do raon.


7. Vlookup agus suim na luachanna comhoiriúnaithe i sraitheanna nó i gcolúin

Má oibríonn tú le sonraí uimhriúla, uaireanta, agus na luachanna comhoiriúnaithe á mbaint as an tábla, b’fhéidir go mbeidh ort na huimhreacha a shuimiú i roinnt colúin nó sraitheanna. Tabharfaidh an chuid seo roinnt foirmlí isteach chun an post seo a chríochnú in Excel.

Vlookup agus suim na luachanna comhoiriúnaithe i ndiaidh a chéile nó i sraitheanna iolracha

Cuir i gcás, tá liosta táirgí agat le díolacháin ar feadh roinnt míonna mar atá thíos faoin ngrianghraf a thaispeántar, anois, ní mór duit gach ordú a shuimiú i ngach mí bunaithe ar na táirgí ar leith.

Vlookup agus suim na chéad luachanna comhoiriúnaithe i ndiaidh a chéile:

1. Déan an fhoirmle seo a leanas a chóipeáil nó a iontráil i gcill bhán, agus ansin brúigh Ctrl + Shift + Iontráil eochracha le chéile chun an chéad toradh a fháil.

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

2. Ansin, tarraing an láimhseáil líonta síos chun an fhoirmle seo a chóipeáil chuig cealla eile atá uait, agus tá na luachanna go léir i ndiaidh a chéile den chéad luach meaitseála achoimrithe 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 is é an raon sonraí (gan ceanntásca colún) a chuimsíonn an luach cuardaigh agus na luachanna comhoiriúnaithe; An uimhir {2,3,4,5,6} is uimhreacha colún iad a úsáidtear chun iomlán an raoin a ríomh.


Vlookup agus suim na luachanna comhoiriúnaithe i sraitheanna iolracha:

Ní féidir leis an bhfoirmle thuas luachanna a lua ach i ndiaidh a chéile don chéad luach comhoiriúnaithe. Más mian leat na cluichí go léir a achoimriú i sraitheanna iolracha, bain úsáid as an bhfoirmle seo a leanas 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, agus gheobhaidh tú an toradh inmhianaithe atá uait, féach an scáileán:

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

nótaí: San fhoirmle thuas: H2 an luach cuardaigh atá á lorg agat; A2: A9 an bhfuil na ceanntásca as a chéile ina bhfuil an luach amharc; B2: F9 raon sonraí na luachanna uimhriúla ar mhaith leat a achoimriú.


Vlookup agus suim na luachanna comhoiriúnaithe i gcolún nó i gcolúin iolracha

Vlookup agus suim na chéad luachanna comhoiriúnaithe i gcolún:

Más mian leat luach iomlán na míonna ar leith a achoimriú mar a thaispeántar sa phictiúr thíos.

Cuir an fhoirmle thíos i bhfeidhm 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, anois, tá na chéad luachanna comhoiriúnaithe bunaithe ar an mí shonrach i gcolún achoimrithe le chéile, féach an scáileán:

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

nótaí: San fhoirmle thuas: H2 an luach cuardaigh atá á lorg agat; B1: F1 an bhfuil ceanntásca na gcolún ina bhfuil an luach amharc; B2: F9 raon sonraí na luachanna uimhriúla ar mhaith leat a achoimriú.


Vlookup agus suim na luachanna comhoiriúnaithe i gcolúin iolracha:

Ba cheart duit an fhoirmle seo a leanas a úsáid chun Vlookup a dhéanamh agus gach luach comhoiriúnaithe a shuimiú in iliomad colúin

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

nótaí: San fhoirmle thuas: H2 an luach cuardaigh atá á lorg agat; B1: F1 an bhfuil ceanntásca na gcolún ina bhfuil an luach amharc; B2: F9 raon sonraí na luachanna uimhriúla ar mhaith leat a achoimriú.


Vlookup agus suim na chéad luachanna comhoiriúnaithe nó gach luach comhoiriúnaithe le gné chumhachtach

B’fhéidir go bhfuil sé deacair duit na foirmlí thuas a mheabhrú, sa chás seo, molfaidh mé gné áisiúil - Cuardach agus Suim of Kutools le haghaidh Excel, leis an ngné seo, is féidir leat an toradh a fháil chomh furasta agus is féidir.    Cliceáil chun Kutools le haghaidh Excel a íoslódáil anois!


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.

Cuir an fhoirmle seo a leanas i gcill le do thoil, 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 raon sonraí na luachanna uimhriúla ar mhaith leat a achoimriú; B1: F1 is tá luach an chuardaigh ar mhaith leat suim a fháil i gceanntásca na gcolún bunaithe ar; I2 an bhfuil an luach cuardaigh laistigh de na ceanntásca colúin atá á lorg agat; A2: A9 an bhfuil an luach cuardaigh ar mhaith leat suim a fháil sna ceanntásca as a chéile bunaithe ar; H2 an bhfuil an luach cuardaigh laistigh de na ceanntásca as a chéile atá á lorg agat.


8. Vlookup chun dhá thábla a chumasc bunaithe ar eochaircholún amháin nó níos mó

I d’obair laethúil, agus tú ag déanamh anailíse ar na sonraí, b’fhéidir go mbeidh ort an fhaisnéis uile is gá a bhailiú i dtábla amháin bunaithe ar eochaircholún amháin nó níos mó. Chun an post seo a réiteach, is féidir le feidhm Vlookup fabhar a thabhairt duit freisin.

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

Mar shampla, tá dhá thábla agat, tá sonraí na dtáirgí agus na n-ainmneacha sa chéad tábla, agus tá na táirgí agus na horduithe sa dara tábla, anois, ba mhaith leat an dá thábla seo a chur le chéile tríd an gcolún táirgí comónta a mheaitseáil in aon tábla amháin.

Foirmle 1: Feidhm VLOOKUP a úsáid

Chun an dá thábla a chumasc i gceann amháin bunaithe ar eochaircholún, cuir an fhoirmle seo a leanas i gcill bhán le do thoil áit ar mhaith leat an toradh a fháil, 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, déanfaidh tú faigh tábla cumaiscthe leis an gcolún ordaithe a cheangal leis na chéad sonraí tábla bunaithe ar phríomhshonraí an cholúin.

=VLOOKUP($A2,$E$2:$F$8,2,FALSE)

nótaí: San fhoirmle thuas, A2 an luach atá á lorg agat, E2: F8 an tábla atá le cuardach, an uimhir 2 is í uimhir an cholúin sa tábla chun an luach a aisghabháil.

Foirmle 2: Feidhmeanna INDEX agus MATCH a úsáid

Má tá do chuid sonraí coitianta ar an taobh dheis agus na sonraí a cuireadh ar ais sa cholún ar chlé laistigh den dara tábla, chun an colún ordaithe a chumasc, níl an fheidhm Vlookup in ann an post a dhéanamh. Chun breathnú suas ó dheis go clé, is féidir leat na feidhmeanna INDEX agus MATCH a úsáid chun an fheidhm Vlookup a athsholáthar.

Déan an fhoirmle thíos a chóipeáil nó a iontráil i gcill bhán, ansin cóipeáil an fhoirmle síos an colún, agus tá an colún ordaithe ceangailte leis an gcéad tábla, féach an scáileán:

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

nótaí: San fhoirmle thuas, A2 an luach cuardaigh atá á lorg agat, E2: E8 an raon sonraí a theastaíonn uait a thabhairt ar ais, F2: F8 is raon lookup ina bhfuil an luach lookup.


Vlookup chun dhá thábla a chumasc bunaithe ar ilcholúin

Má tá ilcholúin lárnacha sa dá thábla ar mhaith leat a bheith páirteach, chun na táblaí a chumasc bunaithe ar na colúin choiteanna seo, is féidir leis na feidhmeanna INDEX agus MATCH cabhrú leat.

Is í an fhoirmle chineálach chun dhá thábla a chumasc bunaithe ar ilcholúin:

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

1. Cuir an fhoirmle thíos i bhfeidhm i gcill bhán más mian 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)

nótaí: San fhoirmle thuas, léirítear na tagairtí cille mar atá thíos an pictiúr:

2.Sin, 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 a theastaíonn uait:

Leideanna: I Excel 2016 agus leaganacha níos déanaí, is féidir leat an Ceist Chumhachta 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.

9. Luachanna meaitseála Vlookup thar iliomad bileoga oibre

An ndearna tú iarracht riamh luachanna Vlookup a fháil ar fud iliomad bileog oibre? Ag glacadh leis go bhfuil na trí bhileog oibre seo a leanas agam le raon sonraí, agus anois, ba mhaith liom cuid de na luachanna comhfhreagracha a fháil bunaithe ar na critéir ó na trí bhileog oibre seo chun an toradh a fháil mar a thaispeántar thíos. Sa chás seo, beidh an Luachanna Vlookup ar fud Ilbhileoga Oibre féadfaidh an rang teagaisc fabhar a dhéanamh céim ar chéim.


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

1. Vlookup chun formáidiú cille (dath cille, dath cló) a fháil chomh maith le luach lookup

Mar is eol dúinn uile, ní féidir leis an ngnáthfheidhm Vlookup ach cabhrú linn an luach comhoiriúnaithe a thabhairt ar ais ó raon sonraí eile, ach uaireanta, b’fhéidir gur mhaith leat an luach comhfhreagrach a thabhairt ar ais in éineacht leis an bhformáidiú cille, mar shampla an dath líonta, dath cló, stíl chló mar a thaispeántar thíos an pictiúr. Labhróidh an chuid seo faoi conas formáidiú na gceall a fháil leis an luach ar ais 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:

1. Sa bhileog oibre tá na sonraí a theastaíonn uait 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 fuinneog, cóipeáil thíos an cód VBA isteach i bhfuinneog an Chóid.

Cód VBA 1: Vlookup chun formáidiú cille a fháil in éineacht le luach lookup

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

3. 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.

Cód VBA 2: Vlookup chun formáidiú cille a fháil in éineacht le luach lookup

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

4. 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 Runtime Script Microsoft ticbhosca sa Tagairtí - VBAProject bosca dialóige. Féach screenshots:

5. 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, anois, téigh ar ais ar an mbileog oibre, agus ansin an fhoirmle seo a chur i bhfeidhm: =LookupKeepFormat(E2,$A$1:$C$10,3) isteach i gcill bhán inar mian leat an toradh a aschur, agus ansin brúigh an eochair Iontráil. Féach an pictiúr:

nótaí: San fhoirmle thuas, E2 an luach a fhéachfaidh tú suas, A1: C10 is é an raon tábla, agus uimhir 3 is é uimhir cholún an tábla a theastaíonn uait an luach comhoiriúnaithe a fháil ar ais.

6. Ansin, roghnaigh an chéad chill toradh, agus tarraing an láimhseáil líonta síos chun na torthaí go léir a fháil in éineacht lena bhformáidiú. Féach an pictiúr.


2. Coinnigh formáid an dáta ó luach ar ais Vlookup

De ghnáth, agus an fheidhm Vloook á úsáid agat chun luach formáid an dáta comhoiriúnaithe a chuardach agus a thabhairt ar ais, taispeánfar formáid uimhreach áirithe mar atá thíos an pictiúr a thaispeántar. Chun formáid an dáta a choinneáil ó thoradh ar ais, ba cheart duit an fheidhm TEXT a iamh leis an bhfeidhm Vlookup.

Cuir an fhoirmle thíos i bhfeidhm i gcill bhán, agus ansin tarraing an láimhseáil líonta chun an fhoirmle seo a chóipeáil chuig cealla eile, agus tá na dátaí comhoiriúnaithe uile curtha ar ais mar atá thíos den phictiúr a thaispeántar:

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

nótaí: San fhoirmle thuas, E2 is é an luach cuma, A2: C9 is é an raon lookup, an uimhir 3 an uimhir cholúin atá uait an luach a fuarthas ar ais, mm / dd / bbbb an formáid dáta is mian leat a choinneáil.


3. Luach meaitseála Vlookup agus filleadh le trácht cille

An ndearna tú iarracht riamh ar Vlookup chun ní amháin na sonraí cille comhoiriúnaithe a thabhairt ar ais, ach an trácht cille chomh maith in Excel agus an pictiúr a leanas a thaispeántar? Chun an tasc seo a réiteach, is féidir leis an bhFeidhm Sainmhínithe Úsáideora thíos fabhar a thabhairt duit.

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, iontráil an fhoirmle seo: =vlookupcomment(D2,$A$2:$B$9,2,FALSE) isteach i gcill bhán chun an toradh a aimsiú, agus ansin an láimhseáil líonta a tharraingt chun an fhoirmle seo a chóipeáil chuig cealla eile, anois, cuirtear na luachanna comhoiriúnaithe chomh maith leis na tráchtanna ar ais ag an am céanna, féach an scáileán:

nótaí: San fhoirmle thuas, D2 an luach cuardaigh is mian leat a luach comhfhreagrach a thabhairt ar ais, A2: B9 an tábla sonraí a theastaíonn uait a úsáid, an uimhir 2 is í uimhir an cholúin ina bhfuil an luach comhoiriúnaithe a theastaíonn uait a thabhairt ar ais.


4. Déileáil leis an téacs agus na fíoruimhreacha i Vlookup

Mar shampla, tá raon sonraí agam, is í an uimhir aitheantais sa tábla bunaidh formáid na huimhreach, sa chill amharc a stóráiltear mar théacs, agus an ghnáthfheidhm Vlookup á cur i bhfeidhm, taispeántar toradh earráide # N / A mar atá faoi bhun an scáileáin a thaispeántar. Sa chás seo, conas a d’fhéadfá an fhaisnéis cheart a fháil má tá an fhormáid dhifriúil sonraí ag an uimhir amharc agus an uimhir bhunaidh sa tábla?

Chun déileáil leis an téacs agus na fíoruimhreacha i bhfeidhm Vlookup, cuir an fhoirmle seo a leanas i gcill bhán, agus ansin tarraing an láimhseáil líonta síos chun an fhoirmle seo a chóipeáil, agus gheobhaidh tú na torthaí cearta mar atá thíos ar an scáileán:

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

Nótaí:

  • 1. San fhoirmle thuas, D2 an luach cuardaigh is mian leat a luach comhfhreagrach a thabhairt ar ais, A2: B8 an tábla sonraí a theastaíonn uait a úsáid, an uimhir 2 is í uimhir an cholúin ina bhfuil an luach comhoiriúnaithe a theastaíonn uait a thabhairt ar ais.
  • 2. Oibríonn an fhoirmle seo go maith freisin mura bhfuil tú cinnte cá bhfuil uimhreacha agat agus cá bhfuil téacs agat.

Íoslódáil comhaid shamplacha VLOOKUP

Vlookup_basic_examples.xlsx

Advanced_Vlookup_examples.xlsx

Vlookup_keep_cell_formatting.zip



  • Barra Foirmle Super (cuir línte iolracha téacs agus foirmle in eagar go héasca); Leagan Amach Léitheoireachta (líon mór cealla a léamh agus a chur in eagar go héasca); Greamaigh go dtí an Raon Scagtha...
  • Cumaisc Cealla / Sraitheanna / Colúin agus Sonraí a Choinneáil; Ábhar Cealla Scoilt; Comhcheangail Sraitheanna Dúblacha agus Suim / Meán... Cill Dúblach a Chosc; Déan comparáid idir Ranganna...
  • Roghnaigh Dúblach nó Uathúil Sraitheanna; Roghnaigh Blank Rows (tá na cealla uile folamh); Aimsigh Super agus Fuzzy Aimsigh i go leor Leabhar Oibre; Roghnaigh go randamach ...
  • Cóip Díreach Cealla Il gan tagairt fhoirmle a athrú; Tagairtí Cruthaigh Auto chuig Bileoga Il; Cuir Urchair isteach, Boscaí Seiceála agus go leor eile ...
  • Foirmlí is Fearr agus Cuir isteach go tapa, Ranganna, Cairteacha agus Pictiúir; Cealla a Chriptiú le pasfhocal; Cruthaigh Liosta Ríomhphoist agus seol ríomhphoist ...
  • Sliocht Téacs, Cuir Téacs leis, Bain de réir Poist, Bain Spás; Subtotals Paging a chruthú agus a phriontáil; Tiontaigh Idir Ábhar Cealla agus Tráchtanna...
  • Scagaire Super (scéimeanna scagaire a shábháil agus a chur i bhfeidhm ar bhileoga eile); Ard-Sórtáil de réir míosa / seachtaine / lae, minicíocht agus níos mó; Scagaire Speisialta le cló trom, iodálach ...
  • Comhcheangail Leabhair Oibre agus Bileoga Oibre; Cumaisc Táblaí bunaithe ar eochaircholúin; Roinn Sonraí i Ilbhileoga; Baisc Tiontaigh xls, xlsx agus PDF...
  • Grúpáil Tábla Pivot de réir uimhir na seachtaine, lá na seachtaine agus níos mó ... Taispeáin Cealla Díghlasáilte, Faoi Ghlas de réir dathanna éagsúla; Aibhsigh Cealla a bhfuil Foirmle / Ainm orthu...
cluaisín kte 201905
  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
bun officetab
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Anton · 1 months ago
    Здравствуйте, возможно ли сравнить любое значение из столбцов в таблице. Суть в том, что значения сделаны неправильно и есть слова "CO" "BE" как сделанные на английском так и на русском, чтобы каждый раз не делать замену их неудобно, т.к. файл обновляется постоянно. Необходимо чтобы Впр искал любой вариант из первого и второго столбца. 1 столбец русские символы, 2-ой английские.