Как выполнить ВПР и вернуть несколько значений в одну ячейку Excel?
ВПР — мощная функция Excel, но по умолчанию она возвращает лишь первое совпадающее значение. А как быть, если нужно получить все совпадения и объединить их в одной ячейке? Такая задача нередко возникает при анализе данных или составлении сводок. В этом руководстве мы подробно расскажем, как вернуть несколько значений в одну ячейку — с помощью формул и полезных функций.
Возврат нескольких значений в одну ячейку с помощью функции TEXTJOIN (Excel 2019 и Office 365)
- Возврат всех совпадающих значений в одну ячейку
- Возврат всех совпадающих значений без дубликатов в одну ячейку
Возврат нескольких значений в одну ячейку с помощью Kutools
Возврат нескольких значений в одну ячейку с помощью пользовательской функции
- Возврат всех совпадающих значений в одну ячейку
- Возврат всех совпадающих значений без дубликатов в одну ячейку
Возврат нескольких значений в одну ячейку с помощью функции TEXTJOIN (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. В появившемся диалоговом окне «Расширенное объединение строк»:
- Щёлкните имя ключевого столбца, на основе которого будет выполняться объединение, затем нажмите «Первичный ключ».
- Затем щёлкните другой столбец, данные из которого вы хотите объединить на основе ключевого столбца, и в разделе «Объединить» выберите нужный разделитель для объединённых данных из раскрывающегося списка в поле «Операция».
- Затем нажмите кнопку «ОК».
Все соответствующие значения из другого столбца, совпадающие по значению, объединяются в одну ячейку. См. скриншоты:
 |  |  |
Совет: Чтобы удалить повторяющиеся значения при объединении ячеек, просто установите флажок «Удалить повторяющиеся значения» в диалоговом окне. Так в одну ячейку попадут только уникальные записи — ваши данные станут чище и аккуратнее без лишних усилий. См. скриншоты:
 | |  |
Скачайте Kutools для Excel и начните бесплатную пробную версию прямо сейчас!
Возврат нескольких значений в одну ячейку с помощью пользовательской функции
Функция TEXTJOIN доступна только в Excel 2019 и Office 365. Если у вас установлена более ранняя версия Excel, для выполнения этой задачи необходимо использовать код.
Возврат всех совпадающих значений в одну ячейку
1. Удерживая клавиши «ALT + F11», откройте окно Microsoft Visual Basic для приложений.
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 для приложений.
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 для приложений» выберите «Сервис» → «Ссылки». В появившемся диалоговом окне «Ссылки – VBAProject» установите флажок напротив пункта «Microsoft Scripting Runtime» в списке «Доступные ссылки» (см. скриншоты).
 | |  |
4. Затем нажмите «ОК», чтобы закрыть диалоговое окно, сохраните и закройте окно редактора кода, вернитесь на лист и введите следующую формулу: =MultipleLookupNoRept(E2,$A$2:$C$11,3) в пустую ячейку, куда вы хотите вывести результат. После этого протяните маркер заполнения вниз, чтобы получить все совпадающие значения (см. скриншот):
Независимо от того, используете ли вы формулы вроде TEXTJOIN в сочетании с массивными функциями, инструменты, такие как 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек