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

При использовании VLOOKUP в Excel он обычно возвращает первое совпадающее значение из набора данных. Однако существуют сценарии, в которых вам может потребоваться вернуть и объединить несколько соответствующих значений (например, все имена, связанные с определенным классом). Поскольку VLOOKUP сам по себе не может этого сделать, мы рассмотрим расширенные методы для достижения желаемого результата.

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

• Использование функций TEXTJOIN и FILTER (Excel 365/2021)
• Использование Kutools для Excel
• Использование пользовательской функции

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

Если вы используете Excel 365 или Excel 2021, сочетание функций TEXTJOIN и FILTER обеспечивает мощный и эффективный способ объединения нескольких соответствующих значений.

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

=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$16, $A$2:$A$16=D2, ""))

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

Устали от ограничений функции VLOOKUP в Excel при работе с несколькими соответствующими значениями? Но с функцией поиска «один ко многим» в Kutools for Excel вы можете без труда находить и объединять несколько соответствующих значений всего за несколько щелчков мыши!

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

Нажмите "Kutools" > "Super Lookup" > "One-to-many Lookup (возвращает несколько результатов)", чтобы открыть диалоговое окно. В диалоговом окне укажите операции, как показано ниже:

1. Выберите в текстовых полях «Выходной диапазон» и «Значения поиска» по отдельности;
2. Выберите диапазон таблиц, который вы хотите использовать;
3. Укажите ключевой столбец и возвращаемый столбец отдельно в раскрывающихся списках «Ключевой столбец» и «Возвращаемый столбец»;
4. Наконец, нажмите кнопку ОК.
   

Результат: Теперь все соответствующие значения объединены в одну ячейку. Смотрите скриншот:

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

Если вы знакомы с VBA (Visual Basic for Applications), вы можете создать определяемую пользователем функцию (UDF) для объединения нескольких соответствующих значений. Этот метод работает во всех версиях Excel.

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

2. Нажмите «Вставить» > «Модуль» и вставьте следующий код в окно модуля.

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

Function ConcatenateMatches(LookupValue As String, LookupRange As Range, ReturnRange As Range, Optional Delimiter As String = ", ") As String
'Updateby Extendoffice
    Dim Cell As Range
    Dim Result As String
    Result = ""
    For Each Cell In LookupRange
        If Cell.Value = LookupValue Then
            Result = Result & Cell.Offset(0, ReturnRange.Column - LookupRange.Column).Value & Delimiter
        End If
    Next Cell
    If Result <> "" Then
        Result = Left(Result, Len(Result) - Len(Delimiter))
    End If
    ConcatenateMatches = Result
End Function

3. Затем сохраните и закройте этот код, вернитесь на рабочий лист и введите следующую формулу: =ConcatenateMatches(D2, $A$2:$A$16, $B$2:$B$16) в пустую ячейку, куда вы хотите поместить результат, затем перетащите маркер заполнения вниз, чтобы заполнить формулой другие ячейки. Все соответствующие значения, основанные на определенных данных, были возвращены в одну ячейку с разделителями запятыми и пробелами, см. снимок экрана:

Объединение нескольких соответствующих значений в Excel может быть достигнуто различными методами, каждый из которых имеет свои преимущества. Независимо от того, предпочитаете ли вы использовать встроенные функции, такие как TEXTJOIN и FILTER, сторонние инструменты, такие как Kutools, или пользовательские решения VBA, ключ в том, чтобы выбрать метод, который лучше всего подходит для вашей версии Excel, уровня навыков и конкретных требований. Если вы заинтересованы в изучении дополнительных советов и приемов Excel, наш сайт предлагает тысячи обучающих программ.