Note: The other languages of the website are Google-translated. Back to English
Увійти  \/ 
x
or
x
Реєстрація  \/ 
x

or

Як здійснити пошук та об’єднання кількох відповідних значень у Excel?

Як ми всі знали, Vlookup Функція в Excel може допомогти нам шукати значення і повертати відповідні дані в іншому стовпці, але загалом вона може отримати перше відносне значення лише за наявності декількох відповідних даних. У цій статті я розповім про те, як переглядати та об’єднувати кілька відповідних значень лише в одній комірці або вертикальному списку.

Перегляд і повернення декількох відповідних значень по вертикалі за допомогою формули

Перегляд і об'єднання кількох відповідних значень у комірці з користувацькою функцією

Перегляньте та об’єднайте кілька значень, що збігаються, у комірці за допомогою Kutools для Excel


Припустимо, у мене є такий діапазон даних, щоб отримати всі відповідні значення на основі конкретного значення по вертикалі, як показано на наступному скріншоті, ви можете застосувати формулу масиву.

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 + Enter клавіші разом, щоб отримати відносну базу значень за певним критерієм, див. знімок екрана:

doc vlookup об'єднує 2

примітки: У наведеній вище формулі:

A1: A16 - діапазон стовпців, який містить конкретне значення, яке потрібно шукати;

D2 вказує конкретне значення, яке потрібно переглянути;

B1: B16 - діапазон стовпців, з якого потрібно повернути відповідні дані;

$ 1: $ 16 вказує посилання на рядки в межах діапазону.

2. Потім виділіть комірку E2 і перетягніть маркер заповнення вниз до комірок, поки не отримаєте порожні комірки, і всі відповідні значення будуть перераховані в стовпці, як показано на наступному знімку екрана:

doc vlookup об'єднує 3


Замість того, щоб отримувати відносні значення по вертикалі, іноді потрібно зіставити значення в одну клітинку та об’єднати їх за допомогою певного роздільника. У цьому випадку наступна визначена користувачем функція може зробити вам послугу.

1. Утримуйте клавішу ALT + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.

2. Клацання Insert > Модуліта вставте наступний код у Модулі Вікно

Код VBA: пошук і об'єднання кількох відповідних значень у комірці

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 вказує значення комірок, які потрібно шукати, А1: В16 - діапазон даних, який потрібно отримати, номер 2 - це номер стовпця, з якого слід повернути відповідне значення, ви можете змінити посилання на тези до своїх потреб.


Якщо у вас є Kutools для Excel, З його Розширені комбіновані ряди функцію, ви можете швидко закінчити цю роботу з легкістю. Ця функція може допомогти вам поєднати всі співпадаючі значення з певними роздільниками на основі тих самих даних в іншому стовпці.

Kutools для Excel : з більш ніж 300 зручними надбудовами Excel, які можна спробувати без обмежень протягом 30 днів.

після установки Kutools для Excel, будь ласка, виконайте наступне:

1. Виберіть діапазон даних, для якого потрібно отримати відповідні значення на основі конкретних даних.

2. Потім натисніть Кутулс > Злиття та розділення > Розширені комбіновані ряди, див. скріншот:

3, в Розширені комбіновані ряди у діалоговому вікні клацніть ім’я стовпця, на основі якого потрібно об’єднати, а потім натисніть Первинний ключ , див. знімок екрана:

doc vlookup об'єднує 6

4. Потім клацніть інше ім’я стовпця, для якого потрібно повернути відповідні значення, і натисніть Поєднувати щоб вибрати один роздільник для розділення комбінованих значень, див. скріншот

doc vlookup об'єднує 7

5. А потім клацніть Ok кнопку, всі відповідні значення на основі тих самих значень були об'єднані разом із певним роздільником, див. скріншоти:

doc vlookup об'єднує 8 2 doc vlookup об'єднує 9

 Завантажте та безкоштовну пробну версію Kutools для Excel зараз!


Kutools для Excel: з більш ніж 300 зручними надбудовами Excel, спробуйте безкоштовно без обмежень протягом 30 днів. Завантажте та безкоштовно пробну версію зараз!

Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Понад 300 потужних функцій. Підтримує Office / Excel 2007-2019 та 365. Підтримує всі мови. Простота розгортання на вашому підприємстві чи в організації. Повна функція 30-денної безкоштовної пробної версії. 60-денна гарантія повернення грошей.
вкладка kte 201905

Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
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.
    sym-john · 2 years ago
    Is there any way to get the unique "name" for "class1"
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      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
  • To post as a guest, your comment is unpublished.
    Russell · 2 years ago
    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 |
    • To post as a guest, your comment is unpublished.
      Jeff F · 2 years ago
      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.
  • To post as a guest, your comment is unpublished.
    jeff · 2 years ago
    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)," "," / ")
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      Hi, jeff,
      Thanks for your sharing, you must be a warmhearted man.
  • To post as a guest, your comment is unpublished.
    Dana Rohde · 3 years ago
    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!!
  • To post as a guest, your comment is unpublished.
    Jacob Nelson · 3 years ago
    Is there a way to delete the duplicate values in the concatenate?
    • To post as a guest, your comment is unpublished.
      skyyang · 3 years ago
      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!
      • To post as a guest, your comment is unpublished.
        carmela · 2 years ago
        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
        • To post as a guest, your comment is unpublished.
          Jerry · 8 months ago
          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
  • To post as a guest, your comment is unpublished.
    sreetechnical@gmail.com · 3 years ago
    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
  • To post as a guest, your comment is unpublished.
    Brett Bieker · 4 years ago
    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
    • To post as a guest, your comment is unpublished.
      Anand · 3 years ago
      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
  • To post as a guest, your comment is unpublished.
    slohman · 5 years ago
    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
  • To post as a guest, your comment is unpublished.
    Deepan Saha · 5 years ago
    How to get the result. Please help.

    data data1 result
    a 1 a1
    b 2 a2
    c b1
    b2
    c1
    c2