Как использовать функцию ВПР для возврата нескольких значений в одну ячейку в Excel?

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

Вернуть несколько значений в одну ячейку с помощью функции СЦЕПИТЬТЕКСТ (Excel 2019 и Office 365)

• Вернуть все совпадающие значения в одну ячейку
• Вернуть все совпадающие значения без дубликатов в одну ячейку

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

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

• Вернуть все совпадающие значения в одну ячейку
• Вернуть все совпадающие значения без дубликатов в одну ячейку

Вернуть несколько значений в одну ячейку с помощью функции СЦЕПИТЬТЕКСТ (Excel 2019 и Office 365)

Если у вас есть более новая версия Excel, например Excel 2019 или Office 365, существует новая функция — TEXTJOIN. С помощью этой мощной функции вы можете быстро выполнить поиск и вернуть все совпадающие значения в одну ячейку.

  Вернуть все совпадающие значения в одну ячейку

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

  Вернуть все совпадающие значения без дубликатов в одну ячейку

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

Скопируйте и вставьте следующую формулу в пустую ячейку, затем нажмите комбинацию клавиш Ctrl + Shift + Enter, чтобы получить первый результат. После этого скопируйте эту формулу в другие ячейки, и вы получите все соответствующие значения без дубликатов, как показано на скриншоте ниже:

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

С помощью функции «Расширенное объединение строк» в Kutools для Excel вы можете легко получить несколько совпадающих значений в одну ячейку — никаких сложных формул не требуется! Забудьте о ручных обходных путях и откройте для себя более эффективный способ работы с задачами поиска в Excel. Давайте рассмотрим, как Kutools для Excel делает всё возможным!

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

1. Выберите диапазон данных, который вы хотите объединить на основе другого столбца.

2. Нажмите «Kutools» > «Объединить и разделить» > «Расширенное объединение строк», см. скриншот:

3. В открывшемся диалоговом окне «Расширенное объединение строк»:

• Нажмите на имя ключевого столбца, который нужно объединить, и затем нажмите «Основной ключ».
• Затем нажмите на другой столбец, данные которого вы хотите объединить на основе ключевого столбца, и выберите разделитель для объединенных данных из выпадающего списка в поле «Вычисление», выбрав один из вариантов в разделе «Объединить».
• Затем нажмите кнопку OK.

Все соответствующие значения из другого столбца, основанные на одном и том же значении, объединены в одну ячейку. См. скриншоты:

Советы: Если вы хотите удалить повторяющийся контент при объединении ячеек, просто отметьте опцию «Удалить повторяющиеся значения» в диалоговом окне. Это гарантирует, что только уникальные записи будут объединены в одну ячейку, делая ваши данные чище и организованнее без дополнительных усилий. См. скриншоты:

Скачайте и попробуйте Kutools для Excel прямо сейчас!

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

Функция СЦЕПИТЬТЕКСТ доступна только для Excel 2019 и Office 365. Если у вас есть более старые версии Excel, вы должны использовать код для выполнения этой задачи.

  Вернуть все совпадающие значения в одну ячейку

1. Удерживайте клавиши «ALT + F11», и откроется окно «Microsoft Visual Basic for Applications».

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

VBA-код: ВПР для возврата нескольких значений в одну ячейку

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Updateby Extendoffice
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
    ConcatenateIf = CVErr(xlErrRef)
    Exit Function
End If
For i = 1 To CriteriaRange.Count
    If CriteriaRange.Cells(i).Value = Condition Then
        xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
    End If
Next i
If xResult <> "" Then
    xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

3. Затем сохраните и закройте этот код, вернитесь на лист и введите эту формулу: =CONCATENATEIF($A$2:$A$11; E2; $C$2:$C$11; ", ") в конкретную пустую ячейку, где вы хотите разместить результат, затем перетащите маркер заполнения вниз, чтобы получить все соответствующие значения в одной ячейке, которые вам нужны, см. скриншот:

  Вернуть все совпадающие значения без дубликатов в одну ячейку

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

1. Удерживайте клавиши «Alt + F11», чтобы открыть окно «Microsoft Visual Basic for Applications».

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

VBA-код: ВПР и возврат нескольких уникальных совпадающих значений в одну ячейку

Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
'Updateby Extendoffice
    Dim xDic As New Dictionary
    Dim xRows As Long
    Dim xStr As String
    Dim i As Long
    On Error Resume Next
    xRows = LookupRange.Rows.Count
    For i = 1 To xRows
        If LookupRange.Columns(1).Cells(i).Value = Lookupvalue Then
            xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
        End If
    Next
    xStr = ""
    MultipleLookupNoRept = xStr
    If xDic.Count > 0 Then
        For i = 0 To xDic.Count - 1
            xStr = xStr & xDic.Keys(i) & ","
        Next
        MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
    End If
End Function

3. После вставки кода нажмите «Инструменты» > «Ссылки» в открытом окне «Microsoft Visual Basic for Applications», а затем в появившемся диалоговом окне «Ссылки – VBAProject» отметьте опцию «Microsoft Scripting Runtime» в списке доступных ссылок, см. скриншоты:

4. Затем нажмите OK, чтобы закрыть диалоговое окно, сохраните и закройте окно кода, вернитесь на лист и введите эту формулу: =MultipleLookupNoRept(E2;$A$2:$C$11;3) в пустую ячейку, где вы хотите вывести результат, и затем перетащите маркер заполнения вниз, чтобы получить все совпадающие значения, см. скриншот:

Будь то использование формул, таких как СЦЕПИТЬТЕКСТ в сочетании с массивными функциями, использование инструментов, таких как Kutools для Excel или пользовательских функций, все подходы помогают упростить сложные задачи поиска. Выберите метод, который лучше всего подходит для ваших потребностей. Если вы заинтересованы в изучении дополнительных советов и приемов Excel, наш сайт предлагает тысячи учебных материалов.

Другие связанные статьи: