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

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

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

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

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

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

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

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

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

Если вы считаете встроенные методы формул сложными или ваша версия Excel не поддерживает продвинутые функции, такие как TEXTJOIN и FILTER, Kutools для Excel предлагает удобное графическое решение. Функция «Один-ко-многим Поиску» в Kutools позволяет вам выполнять поиск и объединять несколько совпадающих результатов всего за несколько шагов, что делает её подходящей как для начинающих, так и для опытных пользователей. С Kutools не нужно писать сложные формулы или код, и это особенно удобно при работе с большими или переменными наборами данных, которые требуют повторяющихся операций поиска и агрегации.

После установки Kutools для Excel выполните следующие шаги:

Нажмите Kutools > Супер ПОИСК > Один-ко-многим поиску (возврат нескольких результатов), чтобы открыть диалог настройки. В этом диалоге вы можете быстро настроить параметры поиска и вывода, выполнив следующие шаги:

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

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

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

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

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

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

2. Нажмите Insert > 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. Сохраните и закройте редактор VBA. Вернитесь к вашей рабочей книге и используйте эту UDF, введя формулу: =ConcatenateMatches(D2, $A$2:$A$16, $B$2:$B$16) в пустую ячейку, где вы хотите видеть результат. Протяните маркер заполнения вниз, чтобы скопировать формулу в другие ячейки по мере необходимости. Все соответствующие значения на основе определенного значения поиска будут возвращены и объединены в одной ячейке, разделенные запятой и пробелом. См. скриншот:

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

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

1. Нажмите Developer Tools > Visual Basic, чтобы открыть редактор VBA.

2. В окне VBA нажмите Insert > Module, затем вставьте этот код в модуль:

Sub VLookupAndConcatenate()
    Dim ws As Worksheet
    Dim dataRange As Range, lookupRange As Range, resultRange As Range
    Dim dict As Object
    Dim i As Long, lastRow As Long
    Dim lookupValue As Variant, result As String
    Dim delimiter As String
    delimiter = ", "
    Set dict = CreateObject("Scripting.Dictionary")
    Set ws = ActiveSheet
    On Error Resume Next
    Set dataRange = Application.InputBox( _
        Prompt:="Please select the data range (contains lookup column and result column)", _
        Title:="Select Data Range", _
        Type:=8)
    On Error GoTo 0
    If dataRange Is Nothing Then Exit Sub
    On Error Resume Next
    Set lookupRange = Application.InputBox( _
        Prompt:="Please select the lookup range (single column)", _
        Title:="Select Lookup Range", _
        Type:=8)
    On Error GoTo 0
    If lookupRange Is Nothing Then Exit Sub
    On Error Resume Next
    Set resultRange = Application.InputBox( _
        Prompt:="Please select the starting cell for results output", _
        Title:="Select Output Location", _
        Type:=8)
    On Error GoTo 0
    If resultRange Is Nothing Then Exit Sub
    resultRange.Resize(lookupRange.Rows.Count, 1).ClearContents
    For i = 1 To dataRange.Rows.Count
        lookupValue = dataRange.Cells(i, 1).Value
        If Not dict.Exists(lookupValue) Then
            dict.Add lookupValue, dataRange.Cells(i, 2).Value
        Else
            dict(lookupValue) = dict(lookupValue) & delimiter & dataRange.Cells(i, 2).Value
        End If
    Next i
    For i = 1 To lookupRange.Rows.Count
        lookupValue = lookupRange.Cells(i, 1).Value
        If dict.Exists(lookupValue) Then
            resultRange.Cells(i, 1).Value = dict(lookupValue)
        Else
            resultRange.Cells(i, 1).Value = "Not Found"
        End If
    Next i
    MsgBox "Operation completed! Processed " & lookupRange.Rows.Count & " lookup values.", vbInformation
End Sub

3. Нажмите кнопку для запуска макроса. Вводные окна предложат вам выбрать диапазон данных, диапазон поиска, диапазон результатов. Результат объединения затем будет отображен напрямую в выбранных выходных ячейках.

Этот подход с макросом особенно полезен, если вы часто выполняете множественные поиски объединения с различными значениями, поскольку он помогает избежать загромождения рабочего листа вызовами UDF.

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

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

Для более продвинутых техник работы с Excel и широкого спектра практических руководств посетите нашу обширную библиотеку учебных материалов.