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

or

Как vlookup и объединить несколько соответствующих значений в Excel?

Как все мы знаем, ВПР Функция в Excel может помочь нам найти значение и вернуть соответствующие данные в другом столбце, но в целом она может получить только первое относительное значение, если есть несколько совпадающих данных. В этой статье я расскажу о том, как выполнить vlookup и объединить несколько соответствующих значений только в одной ячейке или вертикальном списке.

Vlookup и возврат нескольких совпадающих значений по вертикали с формулой

Vlookup и объединение нескольких совпадающих значений в ячейке с помощью функции, определяемой пользователем

Vlookup и объединение нескольких совпадающих значений в ячейке с помощью Kutools for 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, а затем нажмите Shift + Ctrl + Enter вместе, чтобы получить относительную базу значений по определенному критерию, см. снимок экрана:

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) в пустую ячейку, куда вы хотите поместить результат, и нажмите Enter key, все соответствующие значения, основанные на определенных данных, были возвращены в одну ячейку с разделителем пробела, см. снимок экрана:

doc vlookup объединить 4

Внимание: В приведенной выше формуле: D2 указывает значения ячеек, которые вы хотите найти, A1: B16 это диапазон данных, из которого вы хотите получить данные, число 2 это номер столбца, из которого должно быть возвращено совпадающее значение, вы можете изменить эти ссылки по своему усмотрению.


Если у вас есть Kutools for Excel, С его Расширенные ряды комбинирования вы можете быстро и легко завершить эту работу. Эта функция может помочь вам объединить все совпадающие значения с определенными разделителями на основе тех же данных в другом столбце.

Kutools for Excel : с более чем 300 удобными надстройками Excel, бесплатно и без ограничений в течение 30 дней.

После установки Kutools for Excel, пожалуйста, сделайте следующее:

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

2. Затем нажмите Kutools > Слияние и разделение > Расширенные ряды комбинирования, см. снимок экрана:

3. В Расширенные ряды комбинирования В диалоговом окне щелкните имя столбца, на основе которого вы хотите объединить, затем щелкните Основной ключ кнопку, см. снимок экрана:

doc vlookup объединить 6

4. Затем щелкните имя другого столбца, в котором вы хотите вернуть совпадающие значения, и щелкните Сочетать чтобы выбрать один разделитель для разделения комбинированных значений, см. снимок экрана:

doc vlookup объединить 7

5, Затем нажмите Ok кнопки, все соответствующие значения, основанные на одинаковых значениях, были объединены вместе с определенным разделителем, см. скриншоты:

doc vlookup объединить 8 2 doc vlookup объединить 9

 Загрузите бесплатную пробную версию Kutools for Excel прямо сейчас!


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

Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

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

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 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 · 1 years ago
    Is there any way to get the unique "name" for "class1"
    • To post as a guest, your comment is unpublished.
      skyyang · 1 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 · 1 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 · 5 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 · 4 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