Как использовать функцию ВПР для возврата нескольких значений в одну ячейку в 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, наш сайт предлагает тысячи учебных материалов.
Другие связанные статьи:
- Функция ВПР с некоторыми базовыми и продвинутыми примерами
- В 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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек