Note: The other languages of the website are Google-translated. Back to English

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

В 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 дней

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

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

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

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

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

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

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

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

документ возвращает несколько значений, разделенных запятыми, 6 2 документ возвращает несколько значений, разделенных запятыми, 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-2019 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
вкладка kte 201905

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

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Сортировать комментарии по
Комментарии (9)
Номинальный 5 из 5 · рейтинги 1
Этот комментарий был сведен к минимуму модератором на сайте
Пока я собираюсь вставить и сохранить модуль, появляется всплывающее сообщение о значительной потере функциональности средства проверки совместимости.
ГАНГАДХАР
Этот комментарий был сведен к минимуму модератором на сайте
Спасибо за этот пост. Знаете ли вы, как я буду манипулировать двумя отдельными целыми числами, которые это создает. Например, предположим, что функция '=SingleCellExtract' теперь выдает (1, 2). Есть ли способ иметь ячейку рядом с ней, которая делает (1+.5 , 2+.5)?
GUEST
Этот комментарий был сведен к минимуму модератором на сайте
Это работает, но сильно замедляет мой Excel! Любые советы, чтобы помочь скорости?
К Рэй
Этот комментарий был сведен к минимуму модератором на сайте
Это просто не работает. Мне не удалось заставить его работать в моем собственном приложении, поэтому я копировал/вставлял vba и формулу, и каждый раз возвращал ошибку
Опека
Этот комментарий был сведен к минимуму модератором на сайте
спасибо, во-первых, мне удалось заставить это работать без замедления производительности. Я использую значения, а не текст, поэтому мой вопрос в том, что я хочу вернуть всех тех, у кого менее 19 точек в списке. Может ли для этого работать извлечение одной ячейки или это должно быть конкретное значение?
М Маклам
Этот комментарий был сведен к минимуму модератором на сайте
Команда VB прерывается, когда диапазон длиннее 154 строк (например: B154)....
Js
Этот комментарий был сведен к минимуму модератором на сайте
Ошибка выскакивает при увеличении размера массива
абхи
Этот комментарий был сведен к минимуму модератором на сайте
Когда совпадают 2 критерия, возвращайте несколько значений поиска в одной ячейке, разделенной запятыми
A2 = B2, затем результат из диапазона с помощью «SingleCellExtract» - пожалуйста.......
Биплаб Дас
Этот комментарий был сведен к минимуму модератором на сайте
Доброе утро,

код VBA отлично работал с моим рабочим листом, довольно четкий и простой, однако я пытался найти способ указать excel, чтобы он возвращал только уникальные значения. Возможно ли это, используя тот же код?
Кэтлин
Номинальный 5 из 5
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0  Характеристики
Предлагаемые места