Բաց թողնել հիմնական բովանդակությունը

Ինչպե՞ս դիտել և միավորել բազմաթիվ համապատասխան արժեքներ Excel- ում:

Ինչպես բոլորս գիտեինք Vlookup- ը Excel- ում գործառույթը կարող է օգնել մեզ որոնել մի արժեք և վերադարձնել համապատասխան տվյալները մեկ այլ սյունակում, բայց ընդհանուր առմամբ, այն կարող է ստանալ միայն առաջին հարաբերական արժեքը, եթե առկա են բազմաթիվ համընկնող տվյալներ: Այս հոդվածում ես կխոսեմ այն ​​մասին, թե ինչպես կարելի է դիտել և միացնել բազմաթիվ համապատասխան արժեքներ միայն մեկ խցում կամ ուղղահայաց ցուցակում:

Vlookup- ը և ուղղաձիգորեն վերադարձնի համապատասխանեցման բազմաթիվ արժեքներ բանաձևի հետ

Vlookup- ը և միմյանց միացրեք բազմաթիվ համապատասխանող արժեքներ օգտագործողի կողմից սահմանված գործառույթ ունեցող բջիջում

Vlookup- ը և Excel- ի համար Kutools- ի հետ միացրեք բազմաթիվ համապատասխանող արժեքներ բջիջում


Ենթադրելով, որ ես ունեմ տվյալների հետևյալ շարքը, ուղղահայաց որոշակի արժեքի հիման վրա բոլոր համապատասխան արժեքները ստանալու համար, ինչպես ցույց է տրված հետևյալ նկարը, կարող եք կիրառել զանգվածի բանաձև:

doc vlookup համակցված 1

1, Մուտքագրեք այս բանաձևը. =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))),"") դատարկ վանդակի մեջ, որտեղ ցանկանում եք տեղադրել արդյունքը, օրինակ ՝ E2, և այնուհետև սեղմել Ctrl + Shift + Մուտք ստեղները միասին `որոշակի չափանիշի վրա հարաբերական արժեքի հիմքը ստանալու համար, տես նկարի նկարը.

doc vlookup համակցված 2

ՆշումՎերոհիշյալ բանաձևում.

A1: A16 սյունակի տիրույթն է, որը պարունակում է այն հատուկ արժեքը, որը ցանկանում եք փնտրել.

D2 նշում է այն հատուկ արժեքը, որը ցանկանում եք դիտել;

B1: B16 սյունակի տիրույթն է, որից ցանկանում եք վերադարձնել համապատասխան տվյալները.

$ 1- ը, $ 16 նշում է տողերի հղումը տիրույթում:

2, Դրանից հետո ընտրեք E2 բջիջը և լրացնելու բռնիչը ներքև քաշեք դեպի բջիջները, մինչև դատարկ բջիջներ ստանաք, և բոլոր համապատասխան արժեքները շարված են սյունակում, ինչպես ցույց է տրված հետևյալ նկարը.

doc vlookup համակցված 3


Հարաբերական արժեքները ուղղահայաց ստանալու փոխարեն, երբեմն, դուք ցանկանում եք համապատասխանող արժեքները մեկ բջիջի մեջ դնել և դրանք միացնել հատուկ տարանջատիչով: Այս դեպքում օգտագործողի կողմից սահմանված հետևյալ գործառույթը կարող է ձեզ լավություն բերել:

1. Անջատեք ALT + F11 բացել ստեղները Microsoft Visual Basic հավելվածների համար պատուհան.

2: Սեղմեք Տեղադրել > Մոդուլներ, և տեղադրեք հետևյալ կոդը Մոդուլներ Պատուհանը:

VBA կոդ. Բջջում Vlookup- ը և միմյանց հետ համատեղելու բազմաթիվ համապատասխանող արժեքներ

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, Դրանից հետո պահեք և փակեք այս կոդը, վերադարձեք աշխատանքային թերթ և մուտքագրեք այս բանաձևը. = cusvlookup (D2, A1: B16,2) դատարկ խցիկի մեջ, որտեղ ցանկանում եք տեղադրել արդյունքը և սեղմել Մտնել բանալին, հատուկ տվյալների հիման վրա բոլոր համապատասխան արժեքները վերադարձվել են տարածության բաժանարարով մեկ խցում, տես նկարի նկարը.

doc vlookup համակցված 4

ՆշումՎերոհիշյալ բանաձևում. D2 ցույց է տալիս այն բջիջների արժեքները, որոնք ցանկանում եք փնտրել, A1: B16 տվյալների տիրույթն է, որը ցանկանում եք բերել տվյալները, համարը 2 սյունակի համարն է, որից պետք է վերադարձվի համապատասխան արժեքը, կարող եք փոխել թեզերի հղումները ձեր կարիքի վրա:


Եթե ​​դուք ունեք Excel- ի համար նախատեսված գործիքներԻր Ընդլայնված կոմբինատ տողեր առանձնահատկությունը, դուք կարող եք արագ ավարտել այս գործը հեշտությամբ: Այս հատկությունը կարող է օգնել ձեզ միավորել բոլոր համապատասխանող արժեքները հատուկ սահմանազատիչների հետ, որոնք հիմնված են մեկ այլ սյունակի նույն տվյալների վրա:

Excel- ի համար նախատեսված գործիքներ : ավելի քան 300 հարմար Excel հավելվածներով, 30 օրվա ընթացքում առանց սահմանափակումների փորձեք անվճար.

Տեղադրելուց հետո Excel- ի համար նախատեսված գործիքներխնդրում եմ արեք հետևյալը.

1. Ընտրեք տվյալների տիրույթը, որը ցանկանում եք ստանալ համապատասխան արժեքներ ՝ հիմնվելով հատուկ տվյալների վրա:

2. Այնուհետեւ կտտացրեք Կուտոլս > Միաձուլում և պառակտում > Ընդլայնված կոմբինատ տողեր, տես նկարի նկարը.

3, Մեջ Ընդլայնված կոմբինատ տողեր երկխոսության տուփ, կտտացրեք սյունակի անունը, որի վրա ցանկանում եք համատեղել, ապա կտտացրեք Առաջնային բանալին կոճակը, տես նկարի նկարը.

doc vlookup համակցված 6

4, Դրանից հետո կտտացրեք մեկ այլ սյունակի անուն, որը ցանկանում եք վերադարձնել համապատասխան արժեքները և կտտացնել Միավորել ընտրել մեկ տարանջատիչ `համակցված արժեքներն առանձնացնելու համար, տես նկարի նկարը.

doc vlookup համակցված 7

5. Եվ այնուհետեւ կտտացրեք Ok կոճակը, նույն արժեքների վրա հիմնված բոլոր համապատասխան արժեքները զուգորդվել են հատուկ բաժանարարի հետ միասին, տես նկարի նկարներ.

doc vlookup համակցված 8 2 doc vlookup համակցված 9

 Ներբեռնեք և անվճար փորձեք Kutools- ը Excel- ի համար:


Excel- ի համար նախատեսված գործիքներ. Excel- ի ավելի քան 300 հարմարավետ հավելումներով, 30 օրվա ընթացքում անվճար փորձեք առանց սահմանափակումների: Ներբեռնեք և անվճար փորձեք հիմա:

Գրասենյակի արտադրողականության լավագույն գործիքները

Հանրաճանաչ հատկություններ: Գտեք, ընդգծեք կամ նույնականացրեք կրկնօրինակները   |  Deleteնջել դատարկ շարքերը   |  Միավորել սյունակները կամ բջիջները՝ առանց տվյալների կորստի   |   Կլոր առանց բանաձևի ...
Super Փնտրել: Բազմաթիվ չափանիշների VLookup    Բազմակի արժեք VLookup  |   VLookup բազմաթիվ թերթերում   |   Fuzzy Փնտրել ....
Ընդլայնված բացվող ցուցակ: Արագ ստեղծեք բացվող ցուցակը   |  Կախված բացվող ցուցակ   |  Բազմակի ընտրություն Drop Down ցուցակ ....
Սյունակի կառավարիչ: Ավելացրեք որոշակի քանակությամբ սյունակներ  |  Տեղափոխել սյունակները  |  Փոխարկել թաքնված սյունակների տեսանելիության կարգավիճակը  |  Համեմատեք միջակայքերը և սյունակները ...
Առանձնահատկություններ: Ցանցի կենտրոնացում   |  Դիզայնի տեսք   |   Մեծ Formula Bar    Աշխատանքային գրքույկի և թերթիկների կառավարիչ   |  Ռեսուրսների գրադարան (Ավտոմատ տեքստ)   |  Ամսաթիվ ընտրող   |  Միավորել աշխատանքային թերթերը   |  Գաղտնագրել/գաղտնազերծել բջիջները    Ուղարկեք նամակներ ըստ ցանկի   |  Սուպեր զտիչ   |   Հատուկ զտիչ (զտել թավ/շեղ/շեղված...) ...
Լավագույն 15 գործիքների հավաքածու12 Տեքստ Գործիքներ (Ավելացրեք տեքստ, Հեռացնել նիշերը, ...)   |   50 + Աղյուսակ Տեսակներ (Գանտի աղյուսակը, ...)   |   40+ Գործնական Բանաձեւեր (Հաշվարկել տարիքը ՝ ելնելով ծննդյան տարեդարձից, ...)   |   19 միացում Գործիքներ (Տեղադրեք QR կոդ, Տեղադրեք նկար ուղուց, ...)   |   12 Փոխարկում Գործիքներ (Բառեր համարներ, Արտարժույթի փոխակերպումը, ...)   |   7 Միաձուլում և պառակտում Գործիքներ (Ընդլայնված կոմբինատ տողեր, Պառակտված բջիջներ, ...)   |   ... եւ ավելին

Լրացրեք ձեր Excel-ի հմտությունները Kutools-ի հետ Excel-ի համար և փորձեք արդյունավետությունը, ինչպես երբեք: Kutools-ը Excel-ի համար առաջարկում է ավելի քան 300 առաջադեմ առանձնահատկություններ՝ արտադրողականությունը բարձրացնելու և ժամանակ խնայելու համար:  Սեղմեք այստեղ՝ Ձեզ ամենաշատ անհրաժեշտ հատկանիշը ստանալու համար...

kte էջանիշը 201905


Office Tab- ը Tabbed ինտերֆեյսը բերում է Office, և ձեր աշխատանքը շատ ավելի դյուրին դարձրեք

  • Միացնել ներդիրներով խմբագրումը և ընթերցումը Word, Excel, PowerPoint- ով, Հրատարակիչ, Access, Visio և Project:
  • Բացեք և ստեղծեք բազմաթիվ փաստաթղթեր նույն պատուհանի նոր ներդիրներում, այլ ոչ թե նոր պատուհաններում:
  • Բարձրացնում է ձեր արտադրողականությունը 50%-ով և նվազեցնում մկնիկի հարյուրավոր սեղմումները ձեզ համար ամեն օր:
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