Skip to main content

Conas luachanna iolracha comhfhreagracha in Excel a bhreathnú agus a chomhchuibhiú?

Mar is eol dúinn go léir, rinne an Vlookup is féidir le feidhm in Excel cabhrú linn luach a chuardach agus na sonraí comhfhreagracha a chur ar ais i gcolún eile, ach go ginearálta, ní fhéadfaidh sé an chéad luach coibhneasta a fháil ach má tá sonraí meaitseála iolracha ann. San Airteagal seo, labhróidh mé faoi conas luachanna comhfhreagracha iolracha a bhreathnú agus a chomhchruinniú i gcill amháin nó i liosta ceartingearach.

Vlookup agus seol luachanna iolracha meaitseála go hingearach leis an bhfoirmle

Vlookup agus concatenate luachanna meaitseála iolracha i gcill le Feidhm Sainithe ag Úsáideoir

Vlookup agus concatenate luachanna meaitseála iolracha i gcill le Kutools le haghaidh Excel


Ag ceapadh, tá an raon sonraí seo a leanas agam, chun na luachanna comhfhreagracha uile a fháil bunaithe ar luach sonrach go hingearach mar a thaispeántar an pictiúr a leanas, is féidir leat foirmle eagar a chur i bhfeidhm.

doc vlookup comhcheangail 1

1. Iontráil an fhoirmle seo: =IF(COUNTIF($A$1:$A$16,$D$2)>=ROWS($1:1),INDEX($B$1:$B$16,SMALL(IF($A$1:$A$16=$D$2,ROW($1:$16)),ROW(1:1))),"") isteach i gcill bhán inar mian leat an toradh a chur, mar shampla, E2, agus ansin brúigh Ctrl + Shift + Iontráil eochracha le chéile chun an bonn luacha coibhneasta a fháil ar chritéar ar leith, féach an scáileán:

doc vlookup comhcheangail 2

nótaí: San fhoirmle thuas:

A1: A16 an raon colún é ina bhfuil an luach sonrach a theastaíonn uait a lorg;

D2 léiríonn sé an luach sonrach a theastaíonn uait a amharc;

B1: B16 an bhfuil an raon colún ar mhaith leat na sonraí comhfhreagracha a thabhairt ar ais uaidh;

$ 1: $ 16 léiríonn sé tagairt na sraitheanna laistigh den raon.

2. Ansin roghnaigh cill E2, agus tarraing an láimhseáil líonta síos go dtí na cealla go dtí go bhfaighidh tú cealla bána, agus liostaítear na luachanna meaitseála uile sa cholún mar a thaispeántar ar an scáileán a leanas:

doc vlookup comhcheangail 3


In áit na luachanna coibhneasta a fháil go hingearach, uaireanta, ba mhaith leat na luachanna meaitseála a chur isteach i gcill amháin agus iad a chur i bpáirt le deighilteoir ar leith. Sa chás seo, féadfaidh an Fheidhm Sainithe Úsáideora seo a leanas 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, agus greamaigh an cód seo a leanas sa Modúil Fuinneog.

Cód VBA: Vlookup agus concatenate luachanna meaitseála iolracha i gcill

Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice
Dim x As Range
Dim result As String
result = ""
For Each x In lookuprange
    If x = lookupval Then
        result = result & " " & x.Offset(0, indexcol - 1)
    End If
Next x
CusVlookup = result
End Function

3. Ansin sábháil agus dún an cód seo, téigh ar ais chuig an mbileog oibre, agus iontráil an fhoirmle seo: = cusvlookup (D2, A1: B16,2) isteach i gcill bhán inar mian leat an toradh a chur, agus brúigh Iontráil eochair, tá na luachanna comhfhreagracha uile bunaithe ar shonraí ar leith curtha ar ais i gcill amháin le deighilteoir spáis, féach an scáileán:

doc vlookup comhcheangail 4

nótaí: San fhoirmle thuas: D2 léiríonn sé na luachanna cille ar mhaith leat breathnú suas, A1: B16 an raon sonraí a theastaíonn uait na sonraí a fháil, an uimhir 2 an uimhir cholúin a bhfuil an luach meaitseála le filleadh uaidh, is féidir leat tagairtí tráchtais do do riachtanas a athrú.


Má tá tú Kutools le haghaidh Excel, Lena Sraitheanna Comhcheangail Casta gné, is féidir leat an post seo a chríochnú go tapa gan stró. Is féidir leis an ngné seo cabhrú leat na luachanna meaitseála go léir a chomhcheangal le teorantóirí ar leith bunaithe ar na sonraí céanna i gcolún eile.

Kutools le haghaidh Excel : le níos mó ná 300 breiseán áisiúil Excel, saor in aisce le triail gan aon teorannú i 30 lá.

Tar éis a shuiteáil Kutools le haghaidh Excel, déan mar a leanas le do thoil:

1. Roghnaigh an raon sonraí a theastaíonn uait na luachanna comhfhreagracha a fháil bunaithe ar na sonraí ar leith.

2. Ansin cliceáil Kutools > Cumaisc & Scoilt > Sraitheanna Comhcheangail Casta, féach ar an scáileán:

3. Sa an Sraitheanna Comhcheangail Casta bosca dialóige, cliceáil ainm an cholúin ar mhaith leat a chur le chéile bunaithe air, ansin cliceáil Eochair Bhunscoile cnaipe, féach ar an scáileán:

doc vlookup comhcheangail 6

4. Ansin cliceáil ainm colúin eile ar mhaith leat na luachanna comhoiriúnaithe a thabhairt ar ais, agus cliceáil Chomhcheangail chun deighilteoir amháin a roghnú chun na luachanna comhcheangailte a scaradh, féach an scáileán:

doc vlookup comhcheangail 7

5. Agus ansin cliceáil Ok cnaipe, tá na luachanna comhfhreagracha uile bunaithe ar luachanna céanna comhcheangailte le deighilteoir ar leith, féach screenshots:

doc vlookup comhcheangail 8 2 doc vlookup comhcheangail 9

 Íoslódáil agus triail saor in aisce Kutools le haghaidh Excel Now!


Kutools le haghaidh Excel: le níos mó ná 300 breiseán áisiúil Excel, saor in aisce le triail gan aon teorannú i 30 lá. Íoslódáil agus triail saor in aisce Anois!

Uirlisí Táirgiúlachta Oifige is Fearr

🤖 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  |  Scoránaigh Stádas Infheictheachta na gColún Ceilte  |  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í (Uaththéacs)   |  Piocálaí Dáta   |  Comhcheangail Bileoga Oibre   |  Criptigh/Díchriptigh Cealla    Seol Ríomhphost trí Liosta   |  Scagaire Super   |   Scagaire Speisialta (scagaire trom/iodálach/stailc tríd...) ...
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,...)   |   ... agus eile

Supercharge Do Scileanna Excel le Kutools le haghaidh Excel, agus Éifeachtúlacht Taithí Cosúil Ná Roimhe. Kutools le haghaidh Excel Tairiscintí Níos mó ná 300 Ardghnéithe chun Táirgiúlacht a Treisiú agus Sábháil Am.  Cliceáil anseo chun an ghné is mó a theastaíonn uait a fháil ...

Tuairisc


Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • 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á!
Comments (16)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Is there any way to get the unique "name" for "class1"
This comment was minimized by the moderator on the site
Hello, sym-john,
Maybe the below article can solve your problem, please view it:
https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html
This comment was minimized by the moderator on the site
This is working great for me - is there anyway to change it that it checks if the cell contains rather than a complete match? Basically I have a list of tasks where:
Column A: Dependencies (eg 10003 10004 10008)
Column B: Task Reference (eg 10001)
Column C: Dependent Tasks (the column for the formula result) - where it would lookup the task reference to see which rows contain it in Column A, and then list the Task Reference of those tasks.

E.g:

Row | Column A | Column B | Column C
1 | | 10001 | 10002 10003
2 | 10001 | 10002 | 10003
3 | 10001 10002 | 10003 |
This comment was minimized by the moderator on the site
you would want to use the Instr() function which will check for something in a string of text in a cell. You can also use Left() and Right() if you are looking for the starting or ending details.
This comment was minimized by the moderator on the site
The cusVlookup worked great for me. Another way to have a different separator is to wrap in two substitute functions. The first (from inside to out) replaces the first space with no space, the second replaces all other spaces with a " / " in mine. Could use "," if you want commas.
=SUBSTITUTE(SUBSTITUTE(cusVlookup(D2,Table1,2)," ","",1)," "," / ")

Also, if your lookup value isn't the first column, you can use 0 or negative numbers to go to column to the left.
=SUBSTITUTE(SUBSTITUTE(cusVlookup(D2,Table1,-1)," ","",1)," "," / ")
This comment was minimized by the moderator on the site
Hi, jeff,
Thanks for your sharing, you must be a warmhearted man.
This comment was minimized by the moderator on the site
I have to say, I have been trying to get a formula for combining multiple values and returning them to a single cell for 2 days now. This "How To" has saved me!! Thank you SO much! I would never have gotten it without your Module!
I do have 2 questions though. I have the deliminator as a comma instead of a space and because of that it starts out with a comma. Is there a way to prevent the start comma but keep the rest?
My second question is; When I use the fill handle it changes the range values as well as the cell value I want to look up. I want it to continue to change the cell number I want to look up but keep the same range values. How can I make this happen?

Thank you so much for your help!!
This comment was minimized by the moderator on the site
Is there a way to delete the duplicate values in the concatenate?
This comment was minimized by the moderator on the site
Hello, Jacob,
May be the following article can help you to solve your problem.
https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html

Please try, hope it can help you!
This comment was minimized by the moderator on the site
Is there a way to list the duplicate values only once, using the vba code and formula above? I am not sure where to put the countif>1 statement in the formula bar, or in the vba itself. Please help
This comment was minimized by the moderator on the site
you can add two extra condition to skip blank cells and to skip duplicates:For i = 1 To CriteriaRange.Count
If CriteriaRange.Cells(i).Value = Condition Then
If ConcatenateRange.Cells(i).Value <> "" Then 'SKIP BANKS
If InStr(xResult, ConcatenateRange.Cells(i).Value) = 0 Then 'SKIP IF FOUND DUPLICATE
xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
End If
End If
End If
Next i
This comment was minimized by the moderator on the site
This is amazing but i am looking for something else, i have a table with RollNo StudentName sub1, sub2, sub3 ... Total Result, When I enter Rollnumber it should give a result like "SName Sub1 64, sub2 78,... Total 389, Result pass", is it possible
This comment was minimized by the moderator on the site
Loved the function for Excel 2013 but amended it slightly to change the separating character to ";" instead of " " and then remove the prefixed ";" from the concantenated values Results matching values in my example would have ;result01 or ;result01;result02 . Added the extra If Left(xResult, 1) = ";" to remove any extra ";" at the beginning of the string if it is the 1st character. I'm sure there is a neater way of doing it but it worked for me. :) Function CusVlookup(pValue As String, pWorkRng As Range, pIndex As Long) Dim rng As Range Dim xResult As String xResult = "" For Each rng In pWorkRng If rng = pValue Then xResult = xResult & ";" & rng.Offset(0, pIndex - 1) If Left(xResult, 1) = ";" Then xResult = MID(xResult,2,255) End If End If Next CusVlookup = xResult End Function
This comment was minimized by the moderator on the site
Make if condition for result if empty.

Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice 20151118
Dim x As Range
Dim result As String
result = ""
For Each x In lookuprange
If x = lookupval Then
If Not result = "" Then
result = result & " " & x.Offset(0, indexcol - 1)
Else
result = x.Offset(0, indexcol - 1)
End If
Next x
CusVlookup = result
End Function
This comment was minimized by the moderator on the site
When using the cusvlookup is there a way to add the last name as well with a comma in between that might appear in Column C
This comment was minimized by the moderator on the site
How to get the result. Please help. data data1 result a 1 a1 b 2 a2 c b1 b2 c1 c2
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations