Совет. Другие языки - Google-Translated. Вы можете посетить English версия этой ссылки.
Войти
x
or
x
x
Регистрация
x

or

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

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

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

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

Vlookup и объединить несколько совпадающих значений в ячейке с помощью Kutools for Excel


Vlookup и объединить несколько совпадающих значений в ячейке:

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

  • 1. Укажите столбец ключей, который вы хотите объединить на другом столбце;
  • 2. Выберите разделитель для комбинированных данных.

doc объединить текст на основе критериев 10

Kutools for Excel: с более чем 200 удобными надстройками Excel, бесплатно попробовать без ограничений в 60-дни. Скачать и бесплатно пробную версию!



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

doc vlookup concatenate 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 concatenate 2

Внимание: В приведенной выше формуле:

A1: A16 - диапазон столбцов, который содержит конкретное значение, которое вы хотите найти;

D2 указывает конкретное значение, которое вы хотите vlookup;

B1: B16 - диапазон столбцов, из которого вы хотите вернуть соответствующие данные;

$ 1: $ 16 указывает ссылку на строки в пределах диапазона.

2, Затем выберите ячейку E2 и перетащите дескриптор заполнения в ячейки до тех пор, пока не получите пустые ячейки, и все соответствующие значения будут перечислены в столбце следующим образом:

doc vlookup concatenate 3


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

1. Удерживая нажатой ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окна.

2. Щелчок Вставить > модуль, и вставьте следующий код в модуль Окно.

Код VBA: Vlookup и объединить несколько совпадающих значений в ячейке

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
        result = result & " " & x.Offset(0, indexcol - 1)
    End If
Next x
CusVlookup = result
End Function

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

doc vlookup concatenate 4

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


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

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

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

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

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

doc vlookup concatenate 5

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

doc vlookup concatenate 6

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

doc vlookup concatenate 7

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

doc vlookup concatenate 8  2 doc vlookup concatenate 9

Скачать и бесплатно пробную версию Kutools для Excel сейчас!


Kutools for Excel: с более чем 200 удобными надстройками Excel, бесплатно попробовать без ограничений в 60-дни. Скачать и бесплатно пробную версию!


Kutools для Excel - лучший инструмент для повышения производительности в офисе Повысьте производительность на 80%

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

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

  • Включить редактирование и чтение с вкладками в Word, Excel, PowerPoint, Издатель, Доступ, Visio и Проект.
  • Открывайте и создавайте несколько документов в новых вкладках одного и того же окна, а не в новых окнах.
  • Увеличивает вашу производительность на 50% и уменьшает сотни щелчков мышью для вас каждый день!
нижняя часть офиса
Say something here...
symbols left.
You are guest ( Sign Up? )
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 days ago
    Is there any way to get the unique "name" for "class1"
  • To post as a guest, your comment is unpublished.
    Russell · 4 months 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 · 5 days 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 · 5 months 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 · 5 months ago
      Hi, jeff,
      Thanks for your sharing, you must be a warmhearted man.
  • To post as a guest, your comment is unpublished.
    Dana Rohde · 1 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 · 1 years ago
    Is there a way to delete the duplicate values in the concatenate?
  • To post as a guest, your comment is unpublished.
    Sree Kanth · 1 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 · 2 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 · 1 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 · 2 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 · 3 years ago
    How to get the result. Please help.

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