Как vlookup вернуть несколько значений в одной ячейке в Excel?
Обычно в Excel, когда вы используете функцию ВПР, если есть несколько значений, соответствующих критериям, вы можете просто получить первое. Но иногда вы хотите вернуть все соответствующие значения, которые соответствуют критериям, в одну ячейку, как показано на следующем снимке экрана, как вы могли бы решить эту проблему?
- Vlookup возвращает все совпадающие значения в одну ячейку
- Vlookup возвращает все совпадающие значения без дубликатов в одну ячейку
Vlookup возвращает несколько значений в одну ячейку с помощью функции, определяемой пользователем
- Vlookup возвращает все совпадающие значения в одну ячейку
- Vlookup возвращает все совпадающие значения без дубликатов в одну ячейку
Vlookup возвращает несколько значений в одну ячейку с полезной функцией
Vlookup возвращает несколько значений в одну ячейку с помощью функции TEXTJOIN (Excel 2019 и Office 365)
Если у вас более поздняя версия Excel, такая как Excel 2019 и Office 365, есть новая функция - ТЕКСТ ПРИСОЕДИНИТЬСЯ, с помощью этой мощной функции вы можете быстро найти и вернуть все совпадающие значения в одну ячейку.
Vlookup возвращает все совпадающие значения в одну ячейку
Примените приведенную ниже формулу в пустую ячейку, в которую вы хотите поместить результат, затем нажмите Shift + Ctrl + Enter вместе, чтобы получить первый результат, а затем перетащите дескриптор заполнения в ячейку, в которой вы хотите использовать эту формулу, и вы получите все соответствующие значения, как показано ниже:
Vlookup возвращает все совпадающие значения без дубликатов в одну ячейку
Если вы хотите вернуть все совпадающие значения на основе данных поиска без дубликатов, вам может помочь приведенная ниже формула.
Скопируйте и вставьте следующую формулу в пустую ячейку, затем нажмите Shift + Ctrl + Enter вместе, чтобы получить первый результат, а затем скопируйте эту формулу, чтобы заполнить другие ячейки, и вы получите все соответствующие значения без двойных, как показано ниже:
Vlookup возвращает несколько значений в одну ячейку с помощью функции, определяемой пользователем
Вышеупомянутая функция TEXTJOIN доступна только для Excel 2019 и Office 365, если у вас есть другие более ранние версии Excel, вы должны использовать некоторые коды для завершения этой задачи.
Vlookup возвращает все совпадающие значения в одну ячейку
1. Удерживайте ALT + F11 ключи, и он открывает Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии вставьте следующий код в Окно модуля.
Код VBA: Vlookup для возврата нескольких значений в одну ячейку
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, ", ") в конкретную пустую ячейку, в которую вы хотите поместить результат, затем перетащите дескриптор заполнения вниз, чтобы получить все соответствующие значения в одной ячейке, которую вы хотите, см. снимок экрана:
Vlookup возвращает все совпадающие значения без дубликатов в одну ячейку
Чтобы игнорировать дубликаты в возвращаемых совпадающих значениях, выполните приведенный ниже код.
1. Удерживайте Alt + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии вставьте следующий код в Окно модуля.
Код VBA: Vlookup и возврат нескольких уникальных совпадающих значений в одну ячейку
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 вариант в Доступные ссылки список, см. скриншоты:
 |
 |
 |
4. Затем нажмите OK чтобы закрыть диалоговое окно, сохраните и закройте окно кода, вернитесь на рабочий лист и введите эту формулу: =MultipleLookupNoRept(E2,$A$2:$C$11,3) into a blank cell where you want to output the result, and then drag the fill hanlde down to get all matching values, see screenshot:
Vlookup возвращает несколько значений в одну ячейку с полезной функцией
Если у вас есть наши Kutools for Excel, С его Расширенные ряды комбинирования функцию, вы можете быстро объединить или объединить строки на основе одного и того же значения и выполнить некоторые вычисления по мере необходимости.
После установки Kutools for Excel, пожалуйста, сделайте следующее:
1. Выберите диапазон данных, в котором вы хотите объединить данные одного столбца на основе другого столбца.
2. Нажмите Кутулс > Слияние и разделение > Расширенные ряды комбинирования, см. снимок экрана:
3. В выскочившем Расширенные ряды комбинирования диалоговое окно:
- Щелкните имя ключевого столбца, на основе которого нужно объединить, а затем щелкните Основной ключ
- Затем щелкните другой столбец, данные которого вы хотите объединить на основе ключевого столбца, и щелкните Сочетать выбрать один разделитель для разделения объединенных данных.
4. Затем нажмите OK кнопку, и вы получите следующие результаты:
 |
|
 |
Загрузите бесплатную пробную версию Kutools for Excel прямо сейчас!
Больше относительных статей:
- Функция ВПР с некоторыми базовыми и расширенными примерами
- В Excel функция ВПР - мощная функция для большинства пользователей Excel, которая используется для поиска значения в крайнем левом углу диапазона данных и возврата соответствующего значения в той же строке из указанного вами столбца. В этом руководстве рассказывается о том, как использовать функцию ВПР с некоторыми базовыми и расширенными примерами в Excel.
- Возвращает несколько совпадающих значений на основе одного или нескольких критериев
- Обычно найти конкретное значение и вернуть соответствующий элемент для большинства из нас несложно с помощью функции ВПР. Но пробовали ли вы когда-нибудь вернуть несколько совпадающих значений на основе одного или нескольких критериев? В этой статье я представлю несколько формул для решения этой сложной задачи в Excel.
- Vlookup и возврат нескольких значений по вертикали
- Обычно вы можете использовать функцию Vlookup для получения первого соответствующего значения, но иногда вы хотите вернуть все совпадающие записи на основе определенного критерия. В этой статье я расскажу о том, как использовать vlookup и возвращать все совпадающие значения по вертикали, горизонтали или в одну ячейку.
- Vlookup и возврат нескольких значений из раскрывающегося списка
- В Excel, как вы могли vlookup и возвращать несколько соответствующих значений из раскрывающегося списка, что означает, что когда вы выбираете один элемент из раскрывающегося списка, все его относительные значения отображаются одновременно. В этой статье я расскажу о решении шаг за шагом.
Лучшие инструменты для офисной работы
Улучшите свои навыки работы с Excel с помощью Kutools for Excel и почувствуйте эффективность, как никогда раньше. Kutools for Excel предлагает более 300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего...
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
