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

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

Иногда вам может понадобиться vlookup и сразу вернуть несколько совпадающих значений в одну ячейку. Но если в возвращенные ячейки внесены повторяющиеся значения, как можно игнорировать дубликаты и сохранять только уникальные значения при возврате всех совпадающих значений, как показано на следующем снимке экрана в Excel?

документ возвращает несколько уникальных значений 1

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


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

Следующий код VBA может помочь вам вернуть несколько совпадающих значений без дубликатов, сделайте следующее:

1. Удерживайте Alt + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

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

Код VBA: Vlookup и возврат нескольких уникальных совпадающих значений:

Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
    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 вариант в Доступные ссылки список, см. снимок экрана:

документ возвращает несколько уникальных значений 2

4. Затем нажмите OK чтобы закрыть диалоговое окно, сохраните и закройте окно кода, вернитесь на рабочий лист и введите эту формулу: =MultipleLookupNoRept(E2,A2:C17,3) в пустую ячейку, в которую вы хотите вывести результат, нажмите Enter ключ, чтобы получить правильный результат, как вам нужно. Смотрите скриншот:

документ возвращает несколько уникальных значений 3

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


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

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

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

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

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

=LOOKUP(2, 1/((COUNTIF($E$1:E1, $B$2:$B$12)=0)*($D$2=$A$2:$A$12)), $B$2:$B$12)

Пожалуйста, попробуйте.
Этот комментарий был сведен к минимуму модератором на сайте
Привет Скайанг,

Большое спасибо за эту формулу.
Это работает для меня. Однако обработка большого набора данных занимает много времени.
Можем ли мы изменить эту формулу, чтобы она работала немного быстрее?
еще раз спасибо
Расике
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Skyyang, что, если вы хотите получить результат в виде столбца?
Этот комментарий был сведен к минимуму модератором на сайте
Есть ли способ добавить пробел между несколькими значениями, полученными в результатах, без добавления запятой в конце списка? Например, приведенный выше результат будет выглядеть так: «Эмили, Джеймс, Дейзи, Гэри», а не так: «Эмили, Джеймс, Дейзи, Гэри».

Я попытался отредактировать эту часть кода VBA: xStr = xStr & xDic.Keys(I) & "," так: xStr = xStr & xDic.Keys(I) & ","

Это добавило пробел между значениями, но также добавило запятую после последнего значения. «Эмили, Джеймс, Дейзи, Гэри».

Есть ли способ заставить его работать с пробелом, но без дополнительной запятой после последнего значения?
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Деметра,
Используйте пробел для разделения значений, вам просто нужно изменить код vba:
из xStr = xStr & xDic.Keys(i) & "," будет следующим: xStr = xStr & xDic.Keys(i) & " "

Пожалуйста, попробуйте.
Этот комментарий был сведен к минимуму модератором на сайте
xStr = xStr & xDic.Keys(I) & "," будет таким: xStr = xStr & xDic.Keys(I) & ", "

Есть ли способ заменить «,» на ALT + ENTER внутри ячейки, чтобы результаты были в той же ячейке, но в разных строках? Нужно ли для этого вводить дополнительный модуль VBA и комбинировать их?

Кроме того, этот код довольно медленный при переборе огромных таблиц. Кто-нибудь знает более быстрые решения?
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Имре,
Чтобы разделить значения результатов с помощью клавиш Alt + Enter, примените следующую определяемую пользователем функцию:

Функция MultipleLookupNoRept (значение поиска в виде строки, диапазон поиска в виде диапазона, номер столбца в виде целого числа)
Dim xDic как новый словарь
Dim xRows As Long
Dim xStr как строка
Dim i As Long
On Error Resume Next
xRows = LookupRange.Rows.Count
Для i = 1 в xRows
Если LookupRange.Columns(1).Cells(i).Value = Lookupvalue Тогда
xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
End If
Далее
xстр = ""
MultipleLookupNoRept = xStr
Если xDic.Count > 0 Тогда
Для i = 0 To xDic.Count - 1
xStr = xStr и xDic.Keys(i) & Chr(10) + Chr(13)
Далее
MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
End If
Отладка. Печать xStr
End Function

А затем выполните описанные выше шаги в этой статье, наконец, после ввода формулы вы должны нажать «Перенос текста» на вкладке «Главная».
Этот комментарий был сведен к минимуму модератором на сайте
Hi

Я хотел создать список в таблице из этого, а не все результаты в одной ячейке. Поэтому я использовал формулу, аналогичную приведенной ниже (то, что вы предложили)

=LOOKUP(2, 1/((COUNTIF($E$1:E1, $B$2:$B$12)=0)*($D$2=$A$2:$A$12)), $B$2:$B$12)

Однако для обработки большого набора данных требуется много времени.
Есть ли альтернативный способ ускорить это?
еще раз спасибо
Расике
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуй,
в то время как время многократных просмотров стоимости лота мой рабочий лист завис. Есть ли другие способы многократных просмотров без повторения????

а также я использовал на новом рабочем столе, и он только зависает ...

мое значение данных составляет около 10,000 XNUMX строк
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте, я сделал, как вы сказали, и это здорово, но это все еще не решило одну из моих проблем, что происходит, когда у вас есть уникальное значение в каждом месяце? =MultipleLookupNoRept(E2,A2:C17,3), я пытаюсь использовать E2&1 для января, но это не работает
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Джейм,
Не могли бы вы привести свою проблему в виде скриншота здесь, чтобы я мог понять ваши требования?
Этот комментарий был сведен к минимуму модератором на сайте
Это круто! Как мне адаптировать это, чтобы не добавлять нулевые значения в словарь? Я попытался добавить жирный шрифт ниже, но окончательная строка все еще возвращается с экземплярами ,"".


xRows = LookupRange.Rows.Count
Для i = 1 в xRows
Если LookupRange.Columns(1).Cells(i).Value = Lookupvalue And Not IsEmpty(LookupRange.Columns(1).Cells(i).Value), то
xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
End If
Далее

Благодаря,
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места