Как вернуть несколько значений поиска, разделенных запятыми, в одной ячейке?

При работе с данными в Excel вы можете столкнуться с ситуацией, когда необходимо вернуть несколько совпадающих значений для поиска и отобразить их в одной ячейке, разделенных запятыми, тире и т.д. В то время как встроенные функции поиска Excel, такие как "ВПР" и "ИНДЕКС-ПОИСКПОЗ", отлично подходят для нахождения одного совпадения, объединение нескольких результатов в одну ячейку требует более сложного подхода. В этой статье будут представлены некоторые полезные способы решения этой задачи в Excel.

Вернуть несколько значений поиска в ячейке, разделенных запятыми

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

Вернуть несколько значений поиска в ячейке, разделенных запятыми, с помощью функций TEXTJOIN и FILTER

Если у вас есть доступ к Excel 365 или Excel 2021 и более поздним версиям, использование динамической формулы массива является самым простым методом.

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

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

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

Вернуть несколько значений поиска в ячейке, разделенных запятыми, с помощью Kutools для Excel

"Kutools для Excel" — это надстройка, предназначенная для упрощения сложных задач в Excel. Её продвинутая функция "Поиск один-ко-многим" позволяет вам легко извлекать все совпадающие значения в одну ячейку с указанным разделителем — без необходимости использования сложных формул или кодирования на VBA.

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

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

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

Результат:

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

Советы: Если вы хотите использовать другой разделитель для разделения данных, вы можете нажать "Опции" и выбрать желаемый разделитель. Кроме того, вы можете выполнять другие операции с результатами, такие как суммирование, усреднение и многое другое.

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

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

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

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

VBA код: Вернуть несколько значений поиска в одну ячейку, разделенную запятыми

Function JoinLookupValues(lookupValue As String, lookupRange As Range, returnRange As Range, delimiter As String) As String
    'Updateby Extendoffice
    Dim result As String
    Dim i As Long
    result = ""
    For i = 1 To lookupRange.Rows.Count
        If lookupRange.Cells(i, 1).Value = lookupValue Then
            result = result & returnRange.Cells(i, 1).Value & delimiter
        End If
    Next i
    If Len(result) > 0 Then
        JoinLookupValues = Left(result, Len(result) - Len(delimiter))
    Else
        JoinLookupValues = ""
    End If
End Function

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

=JoinLookupValues(D2, $A$2:$A$15, $B$2:$B$15, ", ")

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

Связанные статьи: