Как вернуть несколько диапазонов искомых значений, разделённых запятыми, в одной ячейке?
При работе с данными в Excel нередко возникает задача найти несколько совпадающих значений и объединить их в одной ячейке через запятую, тире или другой разделитель. Хотя встроенные функции, такие как «ВПР» (VLOOKUP) и комбинация «ИНДЕКС+ПОИСКПОЗ» (INDEX-MATCH), отлично справляются с поиском одного совпадения, для объединения нескольких результатов в одну ячейку требуется более продвинутый подход. В этой статье вы найдёте эффективные способы решения этой задачи в 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(", "; ИСТИНА; …): объединяет полученные значения в одну строку, разделяя их запятыми.
Возврат нескольких Диапазон значений поиска в одной ячейке, разделённых запятыми, с помощью Kutools для Excel
«Kutools для Excel» — это надстройка, разработанная для упрощения сложных задач в Excel. Её расширенная функция «Один-ко-многим поиску» позволяет легко собрать все совпадающие значения в одну ячейку с заданным разделителем — без необходимости использовать громоздкие формулы или код VBA.
После установки Kutools для Excel выполните следующие действия:
Нажмите «Kutools» > «Супер ПОИСК» > «Один-ко-многим (возвращает несколько результатов)», чтобы открыть диалоговое окно. В нём выполните операции, описанные ниже:
- Выберите «Область размещения списка» и «Диапазон значений поиска» в соответствующих полях ввода;
- Выберите диапазон таблицы, который вы хотите использовать;
- Укажите Ключевой столбец и Столбец для возврата в раскрывающихся списках «Ключевой столбец» и «Столбец для возврата» соответственно;
- Наконец, нажмите кнопку «ОК».
Результат:
Все соответствующие ячейки с одинаковыми значениями объединены в одну, а их содержимое разделено запятыми (см. снимок экрана):
Возврат нескольких Диапазон значений поиска в одной ячейке, разделённых запятыми, с помощью пользовательской функции
Если у вас нет Excel 365 или Excel 2021, в этом разделе пошагово описано, как создать и использовать пользовательскую функцию (UDF), чтобы вернуть несколько значений из диапазона поиска в одной ячейке, разделённых запятыми.
1. Нажмите и удерживайте клавиши «ALT» + «F11», чтобы открыть окно «Microsoft Visual Basic для приложений».
2. Нажмите «Insert» («Вставка») → «Module» («Модуль») и вставьте приведённый ниже код в окно модуля.
Код 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? На нашем сайте доступны тысячи обучающих материалов!
Связанные статьи:
- VLOOKUP с возвратом нескольких значений без дубликатов
- При работе с данными в Excel нередко возникает необходимость получить несколько совпадающих значений по заданному критерию поиска. Однако стандартная функция ВПР (VLOOKUP) возвращает лишь первое найденное значение. Если совпадений несколько и вы хотите отобразить их все в одной ячейке без дублирования, воспользуйтесь альтернативными методами для решения этой задачи.
- Возврат нескольких совпадающих значений по одному или нескольким критериям
- Обычно поиск конкретного значения и возврат соответствующего элемента не вызывает затруднений у большинства пользователей благодаря функции ВПР (VLOOKUP). Но пробовали ли вы когда-нибудь возвращать сразу несколько совпадающих значений по одному или нескольким критериям — как на снимке экрана ниже? В этой статье я покажу несколько формул, которые помогут справиться с этой непростой задачей в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек