Как вернуть несколько значений поиска, разделенных запятыми, в одной ячейке?
При работе с данными в Excel вы можете столкнуться с ситуацией, когда необходимо вернуть несколько совпадающих значений для поиска и отобразить их в одной ячейке, разделенных запятыми, тире и т.д. В то время как встроенные функции поиска Excel, такие как "ВПР" и "ИНДЕКС-ПОИСКПОЗ", отлично подходят для нахождения одного совпадения, объединение нескольких результатов в одну ячейку требует более сложного подхода. В этой статье будут представлены некоторые полезные способы решения этой задачи в Excel.
Вернуть несколько значений поиска в ячейке, разделенных запятыми
Вернуть несколько значений поиска в ячейке, разделенных запятыми, с помощью функций TEXTJOIN и FILTER
Если у вас есть доступ к Excel 365 или Excel 2021 и более поздним версиям, использование динамической формулы массива является самым простым методом.
Пожалуйста, примените следующую формулу в пустой ячейке для вывода результата, а затем нажмите клавишу "Enter", чтобы получить результат.
=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$15, $A$2:$A$15=D2))Затем перетащите маркер заполнения вниз, чтобы заполнить формулу в другие ячейки, см. скриншот:
- FILTER(B2:B15, A2:A15=D2): Извлекает все значения в столбце B, где столбец A равен ячейке D2.
- TEXTJOIN(", ", TRUE, ...): Объединяет полученные значения в одну строку, разделенную запятыми.
Вернуть несколько значений поиска в ячейке, разделенных запятыми, с помощью Kutools для Excel
"Kutools для Excel" — это надстройка, предназначенная для упрощения сложных задач в Excel. Её продвинутая функция "Поиск один-ко-многим" позволяет вам легко извлекать все совпадающие значения в одну ячейку с указанным разделителем — без необходимости использования сложных формул или кодирования на VBA.
После установки Kutools для Excel выполните следующие действия:
Нажмите "Kutools" > "Супер ПОИСК" > "Поиск один-ко-многим (возвращает несколько результатов)", чтобы открыть диалоговое окно. В диалоговом окне укажите операции следующим образом:
- Выберите "Область размещения списка" и "Диапазон значений для поиска" в текстовых полях отдельно;
- Выберите диапазон таблицы, который вы хотите использовать;
- Укажите ключевой столбец и столбец возврата из выпадающих списков "Ключевой столбец" и "Столбец возврата" отдельно;
- Наконец, нажмите кнопку "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, наш сайт предлагает тысячи учебных материалов.
Связанные статьи:
- Поиск и возврат нескольких значений без дубликатов
- При работе с данными в Excel иногда может потребоваться вернуть несколько совпадающих значений для конкретного критерия поиска. Однако стандартная функция ВПР возвращает только одно значение. В ситуациях, когда существует несколько совпадений, и вы хотите отобразить их в одной ячейке без дубликатов, можно использовать альтернативные методы для достижения этого.
- Вернуть несколько совпадающих значений на основе одного или нескольких критериев
- Обычно поиск определенного значения и возврат совпадающего элемента не составляет труда для большинства из нас с использованием функции ВПР. Но пробовали ли вы вернуть несколько совпадающих значений на основе одного или нескольких критериев, как показано на следующем скриншоте? В этой статье я представлю несколько формул для решения этой сложной задачи в Excel.
- Поиск и возврат последнего совпадающего значения
- Если у вас есть список элементов, которые повторяются много раз, и вы хотите узнать последнее совпадающее значение с вашими указанными данными. Как это сделать?
- Поиск и возврат совпадающих данных между двумя значениями
- В Excel мы можем применить обычную функцию ВПР, чтобы получить соответствующее значение на основе заданных данных. Но иногда мы хотим выполнить поиск и вернуть совпадающее значение между двумя значениями, как показано на следующем скриншоте, как вы могли бы справиться с этой задачей в Excel?
Лучшие инструменты для повышения продуктивности в Office
Повысьте свои навыки работы в Excel с помощью Kutools для Excel и ощутите эффективность на новом уровне. Kutools для Excel предлагает более300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы выбрать функцию, которая вам нужнее всего...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите режим вкладок для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна вместо новых отдельных окон.
- Увеличьте свою продуктивность на50% и уменьшите количество щелчков мышью на сотни ежедневно!
Все надстройки Kutools. Один установщик
Пакет Kutools for Office включает надстройки для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro — идеально для команд, работающих в разных приложениях Office.
- Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
- Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
- Совместная работа — максимальная эффективность между приложениями Office
- 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек