Как выполнить ВПР и получить несколько уникальных значений без дубликатов в Excel?
При работе с данными в Excel нередко возникает необходимость получить несколько совпадающих значений по заданному критерию поиска. Однако стандартная функция ВПР возвращает лишь первое найденное значение. Если совпадений несколько, а вы хотите отобразить их все в одной ячейке без дубликатов, воспользуйтесь альтернативными методами.
ВПР с возвратом нескольких совпадающих значений без дубликатов в Excel
Возврат нескольких совпадающих значений без дубликатов с помощью функций TEXTJOIN и FILTER
Если вы используете Excel 365 или Excel 2021, легко справьтесь с этой задачей благодаря функциям TEXTJOIN и FILTER — они динамически фильтруют данные и объединяют результаты в одной ячейке.
Введите приведённую ниже формулу в пустую ячейку, нажмите клавишу «Enter» — и получите все совпадающие значения без дубликатов. См. скриншот:
=TEXTJOIN(", ", TRUE, UNIQUE(FILTER(C2:C17, A2:A17=E2)))
- Формула FILTER(C2:C17; A2:A17=E2) извлекает все имена из столбца C, для которых продукт в столбце A совпадает со значением, указанным в ячейке E2.
- Функция UNIQUE удаляет все дублирующиеся значения.
- Функция TEXTJOIN(", "; ИСТИНА; …) объединяет уникальные значения в одну ячейку, разделяя их запятыми.
Возврат нескольких совпадающих значений без дубликатов с помощью мощной функции
Если вам нужно выполнить ВПР и получить несколько совпадающих значений без дубликатов в Excel, но ручные формулы или VBA кажутся слишком сложными, «Kutools для Excel» предлагает простое и эффективное решение. Благодаря функции «Поиск один-ко-многим» вы сможете всего за несколько кликов быстро извлечь и объединить все уникальные совпадающие значения в одной ячейке.
Выберите «Kutools» > «Супер ПОИСК» > «Один-ко-многим поиску (возвращает несколько результатов)», чтобы открыть диалоговое окно «Один-ко-многим поиску», затем укажите необходимые параметры:
- Выберите «Область размещения списка» и «Диапазон значений поиска» в соответствующих полях;
- Выберите диапазон таблицы, который хотите использовать;
- Укажите Ключевой столбец и Столбец для возврата из раскрывающихся списков «Ключевой столбец» и «Столбец для возврата» соответственно;
- Наконец, нажмите кнопку «OK».
Результат:
Теперь вы видите все совпадающие значения, извлечённые без дубликатов. См. скриншот:
Если вы хотите использовать другой разделитель для данных, нажмите «Параметры» и выберите нужный. Кроме того, вы можете выполнять с результатами дополнительные операции — например, суммирование, вычисление среднего и другие.
Возврат нескольких совпадающих значений без дубликатов с помощью пользовательской функции
Если у вас нет Excel 365 или Excel 2021, вы можете воспользоваться приведённой ниже пользовательской функцией в качестве альтернативы. Этот метод обеспечивает аналогичные результаты, такие какВозврат нескольких совпадающих значений без дубликатов — даже в более старых версиях Excel.
- Удерживая клавиши «Alt» + «F11», откройте окно «Microsoft Visual Basic для приложений».
- Выберите «Вставка» > «Модуль» и вставьте следующий код в окно модуля.
Код VBA: ВПР с возвратом нескольких уникальных совпадающих значений:
Function VlookupUnique(lookupValue As String, lookupRange As Range, resultRange As Range, delim As String) As String Dim cell As Range Dim result As String Dim dict As Object Set dict = CreateObject("Scripting.Dictionary") For Each cell In lookupRange If cell.Value = lookupValue Then If Not dict.exists(resultRange.Cells(cell.Row - lookupRange.Row + 1, 1).Value) Then dict.Add resultRange.Cells(cell.Row - lookupRange.Row + 1, 1).Value, True result = result & delim & resultRange.Cells(cell.Row - lookupRange.Row + 1, 1).Value End If End If Next cell If Len(result) > 0 Then VlookupUnique = Mid(result, Len(delim) + 1) Else VlookupUnique = "" End If End Function - Закройте окно кода, вернитесь на лист, введите указанную формулу и нажмите клавишу «Enter», чтобы получить нужный результат. См. скриншот:
=VlookupUnique(E2, A2:A17, C2:C17, ", ")
Таким образом, в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек