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

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

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

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

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

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

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

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

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

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

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

Нажмите «Kutools» > «Супер ПОИСК» > «One-to-many Lookup(returns multiple results)», чтобы открыть диалоговое окно. В диалоговом окне укажите операции следующим образом:

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

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

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

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

1. Нажмите и удерживайте клавиши «ALT + F11», чтобы открыть окно «Microsoft Visual Basic for Applications».

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

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

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, наш сайт предлагает тысячи учебных материалов.