Как использовать ВПР для поиска и объединения нескольких соответствующих значений в Excel?
При использовании функции ВПР (VLOOKUP) в Excel она по умолчанию возвращает только первое найденное совпадение по заданному критерию. Однако на практике часто требуется извлечь и объединить все соответствующие значения, связанные с одним ключом — например, составить список всех студентов одной группы или всех товаров определённой категории. Поскольку стандартная функция ВПР не поддерживает такой сценарий, возникает закономерный вопрос: как одновременно выполнить поиск и объединить несколько подходящих результатов в одной ячейке? Ниже мы рассмотрим несколько практичных и эффективных способов решения этой задачи, подходящих для разных версий Excel и предпочтений пользователей.
Поиск значений с помощью ВПР и объединение нескольких соответствующих значений в Excel
Поиск с помощью ВПР и объединение нескольких соответствующих значений с использованием функций TEXTJOIN и FILTER
Если вы используете Excel 365 или Excel 2021, сочетание функций TEXTJOIN и FILTER предлагает эффективное формульное решение для поиска и объединения всех соответствующих значений. Такой подход идеально подходит для динамичных и обновляемых наборов данных — результат автоматически обновляется при изменении исходных данных. Наилучший эффект достигается, когда ваша версия Excel поддерживает функцию FILTER, доступную только в последних версиях Office.
Введите следующую формулу в целевую ячейку, а затем протяните её вниз, если необходимо применить к другим строкам. Все соответствующие совпадающие значения будут извлечены и объединены в одной ячейке. См. скриншот:
=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$16, $A$2:$A$16=D2, ""))
- FILTER($B$2:$B$16, $A$2:$A$16=D2, "")Эта часть формулы проверяет каждое значение в диапазоне $A$2:$A$16: если оно совпадает со значением в ячейке D2, соответствующее значение из диапазона $B$2:$B$16 включается в результирующий массив.
- $B$2:$B$16Диапазон, из которого будут извлекаться соответствующие значения.
- $A$2:$A$16=D2Условие отбора значений: обрабатываются только строки, в которых диапазон $A$2:$A$16 совпадает с содержимым ячейки D2.
- TEXTJOIN(", ", TRUE, …): Эта функция принимает результат работы функции FILTER (массив совпадений) и объединяет его в одну текстовую строку с указанным разделителем (запятая и пробел), автоматически пропуская пустые элементы.
- ",": задаёт запятую и пробел в качестве разделителя. При необходимости вы можете заменить этот символ, например, на точку с запятой или разрыв строки.
- TRUE: Гарантирует игнорирование пустых ячеек при объединении, обеспечивая аккуратное форматирование результата.
Особое примечание: Этот метод работает только в Excel 365 или Excel 2021 и не поддерживается в более ранних версиях (например, Excel 2019, 2016 и старше). Обязательно проверяйте версию Excel перед использованием.
Совет: если в диапазон данных вносятся изменения или добавляются новые соответствующие элементы, результат обновляется автоматически — без дополнительных действий.
Возможные ограничения: При работе с очень большими наборами данных время вычисления формулы может увеличиться. Кроме того, убедитесь, что в диапазонах поиска и результатов отсутствуют объединённые ячейки, так как они могут вызвать ошибки в формуле.
Поиск с помощью ВПР и объединение нескольких соответствующих значений с помощью Kutools для Excel
Если встроенные методы на основе формул кажутся вам сложными или ваша версия Excel не поддерживает такие продвинутые функции, как TEXTJOIN и FILTER, Kutools для Excel предлагает удобное графическое решение. Функция «Один-ко-многим» в Kutools позволяет быстро находить и объединять все соответствующие результаты всего за несколько шагов, делая её идеальной как для новичков, так и для опытных пользователей. С Kutools вам не придётся писать сложные формулы или коды — особенно при работе с большими или динамически изменяющимися наборами данных, где требуется многократный поиск и агрегация.
После установки Kutools для Excel выполните следующие действия:
Нажмите Kutools > Супер ПОИСК > Один-ко-многим поиск (возврат нескольких результатов), чтобы открыть диалоговое окно настройки. В этом окне вы сможете быстро задать параметры поиска и вывода, выполнив следующие шаги:
- Выберите целевые ячейки для вывода объединённых результатов и ячейки, содержащие значения, которые необходимо найти;
- Укажите диапазон таблицы, содержащей как столбцы ключей поиска, так и столбцы результатов;
- Укажите, какой столбец содержит ключи поиска (Ключевой столбец), а какой — значения для объединения (Столбец для возврата);
- Нажмите кнопку OK, чтобы подтвердить настройки и запустить обработку данных.
Результат: Kutools отобразит все найденные и объединённые значения в выбранной вами ячейке. См. скриншот:
Этот метод настоятельно рекомендуется тем, кто предпочитает работать в интерфейсе Excel без сложных формул или кода. Он не только снижает вероятность ошибок в формулах, но и значительно повышает продуктивность при выполнении повторяющихся задач поиска и объединения данных.
Поиск с помощью ВПР и объединение нескольких соответствующих значений с помощью пользовательской функции
Пользователям, хорошо знакомым с VBA (Visual Basic for Applications), или тем, кто работает со старыми версиями Excel, не поддерживающими динамические массивы и функцию FILTER, можно создать собственную пользовательскую функцию (UDF) для гибкого объединения нескольких результатов. Такой подход совместим со всеми версиями Excel и легко адаптируется под нужные разделители или условия.
1. Удерживая клавиши ALT + F11, откройте окно Microsoft Visual Basic for Applications.
2. Нажмите Вставка > Модуль и вставьте следующий код в окно модуля.
Код VBA: Поиск с помощью ВПР и объединение нескольких совпадающих значений в ячейке
Function ConcatenateMatches(LookupValue As String, LookupRange As Range, ReturnRange As Range, Optional Delimiter As String = ", ") As String
'Updateby Extendoffice
Dim Cell As Range
Dim Result As String
Result = ""
For Each Cell In LookupRange
If Cell.Value = LookupValue Then
Result = Result & Cell.Offset(0, ReturnRange.Column - LookupRange.Column).Value & Delimiter
End If
Next Cell
If Result <> "" Then
Result = Left(Result, Len(Result) - Len(Delimiter))
End If
ConcatenateMatches = Result
End Function
3. Сохраните и закройте редактор VBA. Вернитесь на лист и используйте эту пользовательскую функцию, введя формулу: =ConcatenateMatches(D2, $A$2:$A$16, $B$2:$B$16) в пустую ячейку, где должен отображаться результат. Протяните маркер заполнения вниз, чтобы скопировать формулу в другие ячейки при необходимости. Все соответствующие значения для указанного искомого значения будут объединены в одной ячейке через запятую и пробел. См. скриншот:
- D2: искомое значение, которое нужно найти в наборе данных (LookupValue).
- A2:A16: диапазон, в котором функция ищет искомое значение (LookupRange).
- B2:B16: диапазон, содержащий значения для объединения при совпадении с искомым значением (ReturnRange).
Поиск с помощью ВПР и объединение нескольких соответствующих значений с помощью кода VBA
Для сценариев, предполагающих многократное использование, или для тех, кто предпочитает не размещать пользовательские функции непосредственно в ячейках листа, можно воспользоваться готовым макросом VBA для прямого объединения результатов. Такой подход идеально подходит для совместной работы, особенно когда участники используют разные версии Excel или не имеют одинаковых надстроек.
1. Нажмите Инструменты разработчика > Visual Basic, чтобы открыть редактор VBA.
2. В окне VBA выберите Вставка > Модуль, затем вставьте этот код в открывшийся модуль:
Sub VLookupAndConcatenate()
Dim ws As Worksheet
Dim dataRange As Range, lookupRange As Range, resultRange As Range
Dim dict As Object
Dim i As Long, lastRow As Long
Dim lookupValue As Variant, result As String
Dim delimiter As String
delimiter = ", "
Set dict = CreateObject("Scripting.Dictionary")
Set ws = ActiveSheet
On Error Resume Next
Set dataRange = Application.InputBox( _
Prompt:="Please select the data range (contains lookup column and result column)", _
Title:="Select Data Range", _
Type:=8)
On Error GoTo 0
If dataRange Is Nothing Then Exit Sub
On Error Resume Next
Set lookupRange = Application.InputBox( _
Prompt:="Please select the lookup range (single column)", _
Title:="Select Lookup Range", _
Type:=8)
On Error GoTo 0
If lookupRange Is Nothing Then Exit Sub
On Error Resume Next
Set resultRange = Application.InputBox( _
Prompt:="Please select the starting cell for results output", _
Title:="Select Output Location", _
Type:=8)
On Error GoTo 0
If resultRange Is Nothing Then Exit Sub
resultRange.Resize(lookupRange.Rows.Count, 1).ClearContents
For i = 1 To dataRange.Rows.Count
lookupValue = dataRange.Cells(i, 1).Value
If Not dict.Exists(lookupValue) Then
dict.Add lookupValue, dataRange.Cells(i, 2).Value
Else
dict(lookupValue) = dict(lookupValue) & delimiter & dataRange.Cells(i, 2).Value
End If
Next i
For i = 1 To lookupRange.Rows.Count
lookupValue = lookupRange.Cells(i, 1).Value
If dict.Exists(lookupValue) Then
resultRange.Cells(i, 1).Value = dict(lookupValue)
Else
resultRange.Cells(i, 1).Value = "Not Found"
End If
Next i
MsgBox "Operation completed! Processed " & lookupRange.Rows.Count & " lookup values.", vbInformation
End Sub
3. Нажмите кнопку 
, чтобы запустить макрос. Появятся диалоговые окна, в которых вы сможете указать диапазон данных, диапазон для поиска и диапазон для результатов. Объединённые данные сразу отобразятся в выбранных ячейках вывода.
Такой подход с использованием макроса особенно полезен, если вы часто выполняете множественные поиски с объединением по признаку «Разное значение», поскольку он помогает избежать загромождения листа вызовами пользовательских функций.
При необходимости вы легко можете изменить разделитель в коде и адаптировать макрос под ваш рабочий процесс — например, для вывода результатов прямо в ячейку или в файл.
Объединить несколько соответствующих значений в Excel можно разными способами — каждый из них имеет свои преимущества в зависимости от ситуации. Независимо от того, выберете ли вы формулы с динамическими массивами, надстройки вроде Kutools для Excel или методы на основе VBA, вы значительно усилите свою способность эффективно анализировать и визуализировать сгруппированные данные. Учитывая объём и сложность вашего набора данных, заранее определите, какой подход обеспечит наилучшую производительность и простоту поддержки для вас или вашей команды. В повседневной работе следите за согласованностью данных, избегайте объединённых ячеек и тщательно проверяйте корректность диапазонов ссылок, чтобы добиться оптимальных результатов. Если при вычислении формул возникают ошибки, дважды убедитесь в правильности сопоставления диапазонов с данными и используйте подходящий метод ввода формулы для вашей версии 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек