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

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

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

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

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

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

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

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

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

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

Function SingleCellExtract(LookupValue As String, LookupRange As Range, ColumnNumber As Integer, Char As String)
'Updateby Extendoffice
    Dim I As Long
    Dim xRet As String
    For I = 1 To LookupRange.Columns(1).Cells.Count
        If LookupRange.Cells(I, 1) = LookupValue Then
            If xRet = "" Then
                xRet = LookupRange.Cells(I, ColumnNumber) & Char
            Else
                xRet = xRet & "" & LookupRange.Cells(I, ColumnNumber) & Char
            End If
        End If
    Next
    SingleCellExtract = Left(xRet, Len(xRet) - 1)
End Function

3. Затем сохраните этот код и закройте окно модуля, вернитесь на свой рабочий лист и введите эту формулу: = SingleCellExtract (D2; A2: B15,2; ",") в пустую ячейку, в которую вы хотите вернуть результат. А затем нажмите Enter ключ для получения результата, см. снимок экрана:

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

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

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

А2: В15: это диапазон данных, из которого вы хотите получить данные;

2: число 2 - это номер столбца, в котором должно быть возвращено совпадающее значение;

,: запятая - это разделитель, которым вы хотите разделить несколько значений.

Вы можете изменить их по своему усмотрению.

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

Если у вас есть Kutools for Excel, эта задача больше не будет проблемой. В Расширенные ряды комбинирования Утилита может помочь вам объединить все относительные значения на основе столбца.

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

Перейти к загрузке
Бесплатная пробная версия 30 дней покупка
PayPal / Моя коммерция

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

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

2. Нажмите Кутулс > Слияние и разделение > Расширенные ряды комбинирования, см. снимок экрана:

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

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

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

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

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

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

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

Нажмите, чтобы узнать больше об этой утилите Advanced Combine Rows…

Загрузите бесплатную пробную версию Kutools for Excel прямо сейчас!

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

Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!

Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

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

Больше информации... Скачать бесплатно... Покупка ...

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

Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Больше информации... Скачать бесплатно... Покупка ...

Комментарии (16)

Номинальный 5 из 5 · рейтинги 2

ГАНГАДХАР

о 5 лет назад

#19810

Этот комментарий был сведен к минимуму модератором на сайте

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

Ответить

GUEST

о 5 лет назад

#20594

Этот комментарий был сведен к минимуму модератором на сайте

Спасибо за этот пост. Знаете ли вы, как я буду манипулировать двумя отдельными целыми числами, которые это создает. Например, предположим, что функция '=SingleCellExtract' теперь выдает (1, 2). Есть ли способ иметь ячейку рядом с ней, которая делает (1+.5 , 2+.5)?

Ответить

К Рэй

о 5 лет назад

#21678

Этот комментарий был сведен к минимуму модератором на сайте

Это работает, но сильно замедляет мой Excel! Любые советы, чтобы помочь скорости?

Ответить

Опека

о 5 лет назад

#21834

Этот комментарий был сведен к минимуму модератором на сайте

Это просто не работает. Мне не удалось заставить его работать в моем собственном приложении, поэтому я копировал/вставлял vba и формулу, и каждый раз возвращал ошибку

Ответить

М Маклам

о 5 лет назад

#23661

Этот комментарий был сведен к минимуму модератором на сайте

спасибо, во-первых, мне удалось заставить это работать без замедления производительности. Я использую значения, а не текст, поэтому мой вопрос в том, что я хочу вернуть всех тех, у кого менее 19 точек в списке. Может ли для этого работать извлечение одной ячейки или это должно быть конкретное значение?

Ответить

о 4 лет назад

#24897

Этот комментарий был сведен к минимуму модератором на сайте

Команда VB прерывается, когда диапазон длиннее 154 строк (например: B154)....

Ответить

абхи

о 2 лет назад

#32700

Этот комментарий был сведен к минимуму модератором на сайте

Ошибка выскакивает при увеличении размера массива

Ответить

Биплаб Дас

о 2 лет назад

#32802

Этот комментарий был сведен к минимуму модератором на сайте

Когда совпадают 2 критерия, возвращайте несколько значений поиска в одной ячейке, разделенной запятыми
A2 = B2, затем результат из диапазона с помощью «SingleCellExtract» - пожалуйста.......

Ответить

Кэтлин

О 10 месяцев назад

#37877

Этот комментарий был сведен к минимуму модератором на сайте

Доброе утро,

код VBA отлично работал с моим рабочим листом, довольно четкий и простой, однако я пытался найти способ указать excel, чтобы он возвращал только уникальные значения. Возможно ли это, используя тот же код?

Ответить

Report

Номинальный 5 из 5

Маниканта

О 7 месяцев назад

#39460

Этот комментарий был сведен к минимуму модератором на сайте

Здравствуйте,

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

Пример:
Люси, Том, Никол, Акаша, Эппл

Пожалуйста, повторите, если у вас есть какие-либо предложения.

Ответить

Номинальный 4.5 из 5

скайян Маниканта

О 7 месяцев назад

#39549

Этот комментарий был сведен к минимуму модератором на сайте

Привет, Маниканта
Чтобы разделить несколько значений запятой и пробелом, вам просто нужно добавить пробел после запятой, изменить формулу следующим образом: =SingleCellExtract(D2,A2:B15,2,", ").
Пожалуйста, попробуйте, надеюсь, это поможет вам!

Ответить

Report

Маниканта скайян

О 7 месяцев назад

#39559

Этот комментарий был сведен к минимуму модератором на сайте

Привет Скайанг,

Спасибо за повтор!

Я уже пытался таким же образом, но в значении ячейки последняя дополнительная запятая (,) ниже приведена в качестве примера.

Люси, Том, Никол, Акаша, Эппл,

Это не будет работать для файла Json, поэтому я хочу, чтобы значения были разделены запятой и пробелом, как показано ниже.

Люси, Том, Никол, Акаша, Эппл

Спасибо!

Ответить

скайян Маниканта

О 7 месяцев назад

#39567

Этот комментарий был сведен к минимуму модератором на сайте

Привет, Маниканта
В этом случае вы можете применить следующую определяемую пользователем функцию:

Function MultipleValues(work_range As Range, criteria As Variant, merge_range As Range, Optional Separator As String = ",") As Variant
Dim outcome As String
On Error Resume Next
If work_range.Count <> merge_range.Count Then
MultipleValues = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To work_range.Count
If work_range.Cells(i).Value = criteria Then
outcome = outcome & Separator & merge_range.Cells(i).Value
End If
Next i
If outcome <> "" Then
outcome = VBA.Mid(outcome, VBA.Len(Separator) + 1)
End If
MultipleValues = outcome
Exit Function
End Function

После вставки кода используйте следующую формулу: =MultipleValues($A$2:$A$15,D2,$B$2:$B$15,", ")

Пожалуйста, попробуйте, надеюсь, это поможет вам!
Если у вас все еще есть какие-либо другие проблемы, пожалуйста, прокомментируйте здесь.

https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-return-multiple-matching-1.png

Ответить

Report

Маниканта скайян

О 7 месяцев назад

#39580

Этот комментарий был сведен к минимуму модератором на сайте

Привет Скайанг,

Это работает сейчас, спасибо за ваш быстрый ответ.

Это очень полезно для меня еще раз Спасибо за вашу помощь.

С Уважением,
Маниканта.

Ответить

Номинальный 5 из 5

Yery

О 3 месяцев назад

#41721

Этот комментарий был сведен к минимуму модератором на сайте

No soy programador pero necesito ayuda para que la función de abajo en lugar de devolverme esto

S-01-08-0017->Micro Post 1R, черный, нержавеющая сталь -конец,->4;S-01-08-0057->Micro Post 2R, черный, нержавеющая сталь -конец,->2

меня devuelva лос valores en lineas diferentes.
S-01-08-0017->Micro Post 1R, черный, нержавеющая сталь - конец,->4
S-01-08-0057->Micro Post 2R, черный, нержавеющая сталь - конец,->2

Функции:
Функция SingleCellExtract (LookupValue как строка, LookupRange как диапазон, ColumnNumber как целое число, Char как строка)
'Обновить Extendoffice
Дим я пока
Dim xRet как строка
Для I = 1 для LookupRange.Columns(1).Cells.Count
Если LookupRange.Cells(I, 1) = LookupValue Тогда
Если xRet = "" Тогда
xRet = LookupRange.Cells(I, ColumnNumber) & Char
Еще
xRet = xRet & "" & LookupRange.Cells(I, ColumnNumber) & Char
End If
End If
Далее
SingleCellExtract = Left(xRet, Len(xRet) - 1)
End Function

Ответить

скайян Yery

О 3 месяцев назад

#41787

Этот комментарий был сведен к минимуму модератором на сайте

Привет, Йери,
Вы имели в виду разбить ячейку на несколько строк на основе символа точки с запятой?
Если это так, следующий код VBA может вам помочь:

Sub SplitAll()
    Dim xRg As Range
    Dim xRg1 As Range
    Dim xCell As Range
    Dim I As Long
    Dim xAddress As String
    Dim xUpdate As Boolean
    Dim xRet As Variant
    On Error Resume Next
    xAddress = Application.ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Please select a range", "Kutools for Excel", xAddress, , , , , 8)
    Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
    If xRg Is Nothing Then Exit Sub
        If xRg.Columns.Count > 1 Then
            MsgBox "You can't select multiple columns", , "Kutools for Excel"
            Exit Sub
            End If
            Set xRg1 = Application.InputBox("Split to (single cell):", "Kutools for Excel", , , , , , 8)
            Set xRg1 = xRg1.Range("A1")
            If xRg1 Is Nothing Then Exit Sub
                xUpdate = Application.ScreenUpdating
                Application.ScreenUpdating = False
                For Each xCell In xRg
                    xRet = Split(xCell.Value, ";")
                    xRg1.Worksheet.Range(xRg1.Offset(I, 0), xRg1.Offset(I + UBound(xRet, 1), 0)) = Application.WorksheetFunction.Transpose(xRet)
                    I = I + UBound(xRet, 1) + 1
                Next
                Application.ScreenUpdating = xUpdate
            End Sub

Пожалуйста, попробуйте, надеюсь, это поможет вам!

Ответить

Report

Здесь еще нет комментариев