Note: The other languages of the website are Google-translated. Back to English

Как vlookup вернуть несколько значений в одной ячейке в Excel?

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

Vlookup возвращает несколько значений в одну ячейку с помощью функции TEXTJOIN (Excel 2019 и Office 365)

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

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


Vlookup возвращает несколько значений в одну ячейку с помощью функции TEXTJOIN (Excel 2019 и Office 365)

Если у вас более поздняя версия Excel, такая как Excel 2019 и Office 365, есть новая функция - ТЕКСТ ПРИСОЕДИНИТЬСЯ, с помощью этой мощной функции вы можете быстро найти и вернуть все совпадающие значения в одну ячейку.

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

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

=TEXTJOIN(",",TRUE,IF($A$2:$A$11=E2,$C$2:$C$11,""))

Примечание: В приведенной выше формуле A2: A11 содержит ли диапазон поиска данные поиска, E2 это значение поиска, C2: C11 это диапазон данных, из которого вы хотите вернуть совпадающие значения, ","- это разделитель для разделения нескольких записей.

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

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

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

=TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$11, IF(E2=$A$2:$A$11, $C$2:$C$11, ""), 0),"")=MATCH(ROW($C$2:$C$11), ROW($C$2:$C$11)), $C$2:$C$11, ""))

Примечание: В приведенной выше формуле A2: A11 содержит ли диапазон поиска данные поиска, E2 это значение поиска, C2: C11 это диапазон данных, из которого вы хотите вернуть совпадающие значения, ","- это разделитель для разделения нескольких записей.

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, ", ") в конкретную пустую ячейку, в которую вы хотите поместить результат, затем перетащите дескриптор заполнения вниз, чтобы получить все соответствующие значения в одной ячейке, которую вы хотите, см. снимок экрана:

Примечание: В приведенной выше формуле A2: A11 содержит ли диапазон поиска данные поиска, E2 это значение поиска, C2: C11 это диапазон данных, из которого вы хотите вернуть совпадающие значения, ","- это разделитель для разделения нескольких записей.

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:

Примечание: В приведенной выше формуле A2: C11 диапазон данных, который вы хотите использовать, E2 это значение поиска, число 3 - номер столбца, который содержит возвращенные значения.

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

 Если у вас есть наши Kutools for Excel, С его Расширенные ряды комбинирования функцию, вы можете быстро объединить или объединить строки на основе одного и того же значения и выполнить некоторые вычисления по мере необходимости.

Примечание:Чтобы применить это Расширенные ряды комбинирования, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.

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

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

2. Нажмите Кутулс > Слияние и разделение > Расширенные ряды комбинирования, см. снимок экрана:

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

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

4. Затем нажмите OK кнопку, и вы получите следующие результаты:

Загрузите бесплатную пробную версию Kutools for Excel прямо сейчас!


Больше относительных статей:

  • Функция ВПР с некоторыми базовыми и расширенными примерами
  • В Excel функция ВПР - мощная функция для большинства пользователей Excel, которая используется для поиска значения в крайнем левом углу диапазона данных и возврата соответствующего значения в той же строке из указанного вами столбца. В этом руководстве рассказывается о том, как использовать функцию ВПР с некоторыми базовыми и расширенными примерами в Excel.
  • Vlookup и возврат нескольких значений по вертикали
  • Обычно вы можете использовать функцию Vlookup для получения первого соответствующего значения, но иногда вы хотите вернуть все совпадающие записи на основе определенного критерия. В этой статье я расскажу о том, как использовать vlookup и возвращать все совпадающие значения по вертикали, горизонтали или в одну ячейку.
  • Vlookup и возврат нескольких значений из раскрывающегося списка
  • В Excel, как вы могли vlookup и возвращать несколько соответствующих значений из раскрывающегося списка, что означает, что когда вы выбираете один элемент из раскрывающегося списка, все его относительные значения отображаются одновременно. В этой статье я расскажу о решении шаг за шагом.

Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

  • Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
  • Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы... Предотвращение дублирования ячеек; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
  • Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
  • Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
вкладка kte 201905

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Сортировать комментарии по
Комментарии (43)
Оценок пока нет. Оцените первым!
Этот комментарий был сведен к минимуму модератором на сайте
Как мне настроить эту формулу, чтобы отделить каждое возвращаемое значение, кроме «,», а также возвращать только уникальные значения?
Этот комментарий был сведен к минимуму модератором на сайте
Спасибо за код!!

Что касается подстановочных знаков, то можно использовать INSTR.

Вы можете заменить [ If rng = pValue Then ] на [ InStr (1, rng.Value, pValue) Then ], и если вы не хотите, чтобы регистр был чувствительным, используйте [ InStr (1, rng.Value, pValue, vbTextCompare) Затем]
Этот комментарий был сведен к минимуму модератором на сайте
Спасибо за код VBA выше. Не подскажете, как сделать, чтобы результаты выводились на новую строку в ячейке, т.е. как Alt-Enter 300 400 1000 1300
Этот комментарий был сведен к минимуму модератором на сайте
Спасибо, что поделились приведенным выше кодом. Я использую это в течение нескольких месяцев, но сегодня это, кажется, не работает. Я получаю пустые ячейки вместо обычной ошибки, когда есть данные, которые нужно вернуть. есть идеи?
Этот комментарий был сведен к минимуму модератором на сайте
Потрясающая работа.. Получил именно то, что хотел!!! Любить это !!
Этот комментарий был сведен к минимуму модератором на сайте
Привет, я действительно впечатлен работой, и ее так легко создать, чтобы использовать эту функцию. однако мне нужна дополнительная поддержка. Мой ? это то, как я могу выбрать число из ячейки с несколькими ячейками в моем массиве vlookup. т.е. если ячейка A1 = 100, A2 = 350, A3 = 69, C1 = 100; 1222; 12133 С2 = 69; 222 D1 = яблоко D2 = банан Итак, как выбрать 100 из столбца массива таблицы C, чтобы получить соответствующее значение D1 = яблоко. Обратите внимание, что у меня есть 7-значные числа в моем поисковом значении и массиве таблицы, которые разделены знаком «;». Я был бы очень признателен, если бы вы могли решить эту проблему и помочь мне сэкономить много времени.
Этот комментарий был сведен к минимуму модератором на сайте
Спасибо за VBA-код. Я получил именно то, что хочу! Я изменил только код "rng.Offset(0, pIndex - 1)" на "rng.Offset(0, pIndex - 2)" . Таким образом, MYVLOOKUP может выполнять поиск справа налево.
Этот комментарий был сведен к минимуму модератором на сайте
Это именно то, что я искал и не думал просто сделать свою UDF. Однако он не будет работать точно так же, как функция ВПР. Если строка, которую вы ищете, находится не только в первом столбце, она может дать вам данные за пределами исходного переданного диапазона. Имя Номер Другое имя Столбец, не входящий в пройденный диапазон Джей 1 Джей 1 Джей 2 Джей 2 Крис 3 Крис 3 Хорхе 4 Хорхе 4 Джей 5 Джей 5 Хорхе 6 Хорхе 6 Функция «MYVLOOKUP» возвращает 1 7 1 7 1 1, когда вы ожидаете, что она вернет 2 2 5. Изменения, приведенные ниже, устраняют проблему: /5/1 Jay Coltrain 'Dim rng As Range Dim xResult As String xResult = "" Dim Rows As Long, i As Long Rows = pWorkRng.Rows.Count For i = 2 To Rows If pWorkRng.Cells(i, 5). Value = pValue Then xResult = xResult & " " & pWorkRng.Cells(i, 20150310).Offset(6, pIndex - 9) End If Next i 'Для каждого rng ​​In pWorkRng ' If rng = pValue Then ' xResult = xResult & " " & rng.Offset(16, pIndex - 1) ' End If 'Next MYVLOOKUP = xResult End Function
Этот комментарий был сведен к минимуму модератором на сайте
Это прекрасно работает, но мне нужна помощь с командой для удаления дубликатов из результатов. А если серьезно, отличная работа.
Этот комментарий был сведен к минимуму модератором на сайте
Это прекрасно работает, но мне все еще нужна помощь с командной функцией для удаления дубликатов из результатов.
Этот комментарий был сведен к минимуму модератором на сайте
Сообщите мне или следите за комментариями
Этот комментарий был сведен к минимуму модератором на сайте
Ничего не вернуть! после применения MYLOOKUP не дает результата, кроме пустого.
Этот комментарий был сведен к минимуму модератором на сайте
Привет, это работает хорошо. Что я хотел бы сделать, так это адаптировать код для разделения результатов значений с помощью «///» или любого другого маркера (по техническим причинам мне не нужен только один разделитель символов). Кроме того, я заметил, что эта формула не работает с подстановочным знаком. Я знаю, что прошу слишком многого, но это не так, поскольку vlookup может работать, когда я ищу =myvlookup("*"&E6&"*",$A$2:$C$15,2), что он сделал бы/мог бы сделать. Любая помощь?
Этот комментарий был сведен к минимуму модератором на сайте
Берегись. Я понял, как получить любой разделитель в этом выводе. Это элементарно. Но я понял это. xResult = xResult & "///" & rng.Offset(0, pIndex - 1) Последняя и наиболее желанная вещь - это возможность работать с подстановочными знаками в критериях поиска. Еще раз спасибо за это красивое и блестящее решение. Чрезвычайно полезно. Теперь просто хочу, чтобы макрос запускался и постоянно устанавливался в моем excel, независимо от того, что я делаю, чтобы я мог использовать его, когда мне нужно. И подстановочные знаки! Большое спасибо. Подстановочные знаки - это все, что осталось сделать.
Этот комментарий был сведен к минимуму модератором на сайте
Чтобы получить уникальную запись, вы можете использовать ниже: (изменено ссылкой на другой код пользователя) Функция MYVLOOKUP (pValue As String, pWorkRng As Range, pIndex As Long) 'Update 20150310 'Обновлено 6 Jay Coltrain 'Dim rng As Range Dim xResult As String xResult = "" Затемнение строк As Long, i As Long Rows = pWorkRng.Rows.Count For i = 9 To Rows If pWorkRng.Cells(i, 16).Value = pValue Then xResult = xResult & "," & pWorkRng.Cells(i, 1).Offset(1, pIndex - 1) End If Next i Dim varSection As Variant Dim sTemp As String Dim sDelimiter As String sDelimiter = "," Для каждого varSection In Split(xResult, sDelimiter) If InStr(0, sDelimiter & sTemp & sDelimiter, sDelimiter & varSection & sDelimiter, vbTextCompare) = 1 Then sTemp = sTemp & sDelimiter & varSection End If Next varSection MYVLOOKUP = Mid(sTemp, Len(sDelimiter) + 1) End Function
Этот комментарий был сведен к минимуму модератором на сайте
Это сработало отлично, но мне потребовалось некоторое время, чтобы функция работала должным образом в моей электронной таблице с 20 вкладками и 50 тысячами строк. Теперь БОЛЬШОЙ вопрос заключается в том, как взять эту строку с разделителями, а затем использовать каждую запись в качестве значения поиска индекса/соответствия (не связанного с индексом/соответствием, но кажется быстрее) в другом наборе данных, возвращая значение СУММА всех результатов в одну ячейку. . Мой сценарий заключается в том, что у меня есть один заказ с несколькими счетами-фактурами. Ваша функция MYVLOOKUP отлично работает, чтобы отобразить все счета-фактуры в одной ячейке. Что я хочу сделать сейчас, так это взять каждый конкатенированный возврат с отчетной ячейкой, прокрутить этот массив и суммировать суммы оплаты каждого счета обратно в ячейку формулы. Я ценю любую помощь, которую вы можете предложить, и спасибо за функцию MYVLOOKUP!
Этот комментарий был сведен к минимуму модератором на сайте
Что бы я ни делал, я всегда получаю #value! возвращается вместо результата. vlookup работает нормально, поэтому данные работают. Уже следил за процессом включения макросов. Я даже объединила все в один лист. Любые идеи??
Этот комментарий был сведен к минимуму модератором на сайте
Отличный макрос, полезный. Но нужно знать, можно ли его изменить, чтобы проверить 2 критерия, и нашел ли кто-нибудь, чтобы он работал с подстановочными знаками. Любая помощь?
Этот комментарий был сведен к минимуму модератором на сайте
Есть ли способ изменить результат, чтобы вместо 1000 1000 -1000 отображалось, например, 1,000/1,000/(1,000)?
Этот комментарий был сведен к минимуму модератором на сайте
Отличная функция, однако разбивка по 100,000 XNUMX записей слишком много для моего бедного ноутбука, нужно оставить его работать на ночь!
Этот комментарий был сведен к минимуму модератором на сайте
Это потрясающе, спасибо!
Здесь еще нет комментариев
Загрузить ещё
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места

Подписывайтесь на Нас

Copyright © 2009 - www.extendoffice.ком. | Все права защищены. Питаться от ExtendOffice, | Карта сайта
Microsoft и логотип Office являются товарными знаками или зарегистрированными товарными знаками Microsoft Corporation в США и / или других странах.
Защищено Sectigo SSL