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

Как vlookup и объединить несколько соответствующих значений в Excel?

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

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

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

Vlookup и объединение нескольких совпадающих значений в ячейке с помощью Kutools for Excel


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

doc vlookup объединить 1

1. Введите эту формулу: =IF(COUNTIF($A$1:$A$16,$D$2)>=ROWS($1:1),INDEX($B$1:$B$16,SMALL(IF($A$1:$A$16=$D$2,ROW($1:$16)),ROW(1:1))),"") в пустую ячейку, в которую вы хотите поместить результат, например E2, а затем нажмите Shift + Ctrl + Enter вместе, чтобы получить относительную базу значений по определенному критерию, см. снимок экрана:

doc vlookup объединить 2

Внимание: В приведенной выше формуле:

A1: A16 это диапазон столбцов, который содержит конкретное значение, которое вы хотите найти;

D2 указывает конкретное значение, которое вы хотите просмотреть;

B1: B16 это диапазон столбцов, из которого вы хотите вернуть соответствующие данные;

$ 1: $ 16 указывает ссылку на строки в пределах диапазона.

2. Затем выберите ячейку E2 и перетащите дескриптор заполнения вниз к ячейкам, пока не получите пустые ячейки, а все совпадающие значения будут перечислены в столбце, как показано на следующем снимке экрана:

doc vlookup объединить 3


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

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

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

Код VBA: Vlookup и объединение нескольких совпадающих значений в ячейке

Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice
Dim x As Range
Dim result As String
result = ""
For Each x In lookuprange
    If x = lookupval Then
        result = result & " " & x.Offset(0, indexcol - 1)
    End If
Next x
CusVlookup = result
End Function

3. Затем сохраните и закройте этот код, вернитесь на рабочий лист и введите следующую формулу: = cusvlookup (D2; A1: B16,2) в пустую ячейку, куда вы хотите поместить результат, и нажмите Enter key, все соответствующие значения, основанные на определенных данных, были возвращены в одну ячейку с разделителем пробела, см. снимок экрана:

doc vlookup объединить 4

Внимание: В приведенной выше формуле: D2 указывает значения ячеек, которые вы хотите найти, А1: В16 это диапазон данных, из которого вы хотите получить данные, число 2 это номер столбца, из которого должно быть возвращено совпадающее значение, вы можете изменить эти ссылки по своему усмотрению.


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

Kutools for Excel : с более чем 300 удобными надстройками Excel, бесплатно и без ограничений в течение 30 дней.

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

1. Выберите диапазон данных, для которого вы хотите получить соответствующие значения на основе конкретных данных.

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

3. В Расширенные ряды комбинирования В диалоговом окне щелкните имя столбца, на основе которого вы хотите объединить, затем щелкните Основной ключ кнопку, см. снимок экрана:

doc vlookup объединить 6

4. Затем щелкните имя другого столбца, в котором вы хотите вернуть совпадающие значения, и щелкните Сочетать чтобы выбрать один разделитель для разделения комбинированных значений, см. снимок экрана:

doc vlookup объединить 7

5, Затем нажмите Ok кнопки, все соответствующие значения, основанные на одинаковых значениях, были объединены вместе с определенным разделителем, см. скриншоты:

doc vlookup объединить 8 2 doc vlookup объединить 9

 Скачать и бесплатную пробную версию Kutools for Excel Сейчас !


Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!

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

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

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

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

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Сортировать комментарии по
Комментарии (16)
Оценок пока нет. Оцените первым!
Этот комментарий был сведен к минимуму модератором на сайте
Как получить результат. Пожалуйста помоги. данные данные1 результат a 1 a1 b 2 a2 c b1 b2 c1 c2
Этот комментарий был сведен к минимуму модератором на сайте
При использовании cusvlookup есть ли способ добавить фамилию с запятой между ними, которая может появиться в столбце C
Этот комментарий был сведен к минимуму модератором на сайте
Мне понравилась функция для Excel 2013, но она немного изменена, чтобы изменить разделительный символ на «;». вместо " ", а затем удалите префикс ";" из объединенных значений Результаты, соответствующие значениям в моем примере, будут иметь ;result01 или ;result01;result02 . Добавлен дополнительный параметр If Left(xResult, 1) = ";" чтобы удалить все лишние ";" в начале строки, если это первый символ. Я уверен, что есть более аккуратный способ сделать это, но это сработало для меня. :) Функция CusVlookup(pValue As String, pWorkRng As Range, pIndex As Long) Dim rng As Range Dim xResult As String xResult = "" Для каждого rng ​​In pWorkRng If rng = pValue Then xResult = xResult & ";" & rng.Offset(1, pIndex - 0) If Left(xResult, 1) = ";" Затем xResult = MID(xResult,1) End If End If Next CusVlookup = xResult End Function
Этот комментарий был сведен к минимуму модератором на сайте
Сделайте условие if для результата, если оно пустое.

Функция CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice 20151118
Dim x As Диапазон
Затемнить результат как строку
результат = ""
Для каждого x в диапазоне поиска
Если x = lookupval Тогда
Если Не результат = "" Тогда
результат = результат & " " & x.Offset (0, indexcol - 1)
Еще
результат = x.Offset (0, indexcol - 1)
End If
Следующий х
CusVlookup = результат
End Function
Этот комментарий был сведен к минимуму модератором на сайте
Это удивительно, но я ищу что-то еще, у меня есть таблица с RollNo StudentName sub1, sub2, sub3 ... Общий результат, когда я ввожу Rollnumber, он должен дать результат вроде «SName Sub1 64, sub2 78,... Итого 389, Результат прошел", возможно ли
Этот комментарий был сведен к минимуму модератором на сайте
Есть ли способ удалить повторяющиеся значения в конкатенации?
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуй, Джейкоб,
Возможно, следующая статья поможет вам решить вашу проблему.
https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html

Пожалуйста, попробуйте, надеюсь, это поможет вам!
Этот комментарий был сведен к минимуму модератором на сайте
Есть ли способ перечислить повторяющиеся значения только один раз, используя приведенный выше код и формулу vba? Я не уверен, куда поместить оператор countif> 1 в строке формул или в самом vba. Пожалуйста помоги
Этот комментарий был сведен к минимуму модератором на сайте
вы можете добавить два дополнительных условия, чтобы пропустить пустые ячейки и пропустить дубликаты: For i = 1 To CriteriaRange.Count
Если CriteriaRange.Cells(i).Value = Условие Тогда
Если ConcatenateRange.Cells(i).Value <> "" Then 'ПРОПУСТИТЬ БАНКИ
Если InStr(xResult, ConcatenateRange.Cells(i).Value) = 0, то 'ПРОПУСТИТЬ, ЕСЛИ НАЙДЕНЫ ДУБЛИКАЦИИ
xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
End If
End If
End If
Затем я
Этот комментарий был сведен к минимуму модератором на сайте
Должен сказать, я уже 2 дня пытаюсь получить формулу для объединения нескольких значений и возврата их в одну ячейку. Это "Как" спасло меня!! Большое спасибо! Я бы никогда не получил его без вашего модуля!
Хотя у меня есть 2 вопроса. У меня есть разделитель в виде запятой вместо пробела, и из-за этого он начинается с запятой. Есть ли способ предотвратить начальную запятую, но сохранить остальное?
Мой второй вопрос; Когда я использую дескриптор заполнения, он изменяет значения диапазона, а также значение ячейки, которое я хочу найти. Я хочу, чтобы он продолжал изменять номер ячейки, которую я хочу найти, но сохранял те же значения диапазона. Как я могу это сделать?

Большое вам спасибо за вашу помощь!!
Этот комментарий был сведен к минимуму модератором на сайте
cusVlookup отлично сработал для меня. Другой способ иметь другой разделитель — это обернуть две заменяющие функции. Первый (изнутри наружу) заменяет первый пробел без пробела, второй заменяет все остальные пробелы знаком «/» в моем случае. Можно использовать ",", если вам нужны запятые.
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(cusVlookup(D2,Table1,2)," ","",1)," "," / ")

Кроме того, если искомое значение не является первым столбцом, вы можете использовать 0 или отрицательные числа, чтобы перейти к столбцу слева.
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(cusVlookup(D2,Table1,-1)," ","",1)," "," / ")
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Джефф,
Спасибо, что поделились, вы, должно быть, сердечный человек.
Этот комментарий был сведен к минимуму модератором на сайте
Это отлично работает для меня - можно ли как-то изменить его, чтобы он проверял, содержит ли ячейка, а не полное совпадение? В основном у меня есть список задач, где:
Столбец A: Зависимости (например, 10003 10004 10008)
Столбец B: Справочник по задаче (например, 10001)
Столбец C: Зависимые задачи (столбец для результата формулы) — где он будет искать ссылку на задачу, чтобы увидеть, какие строки содержат ее в столбце A, а затем перечислить ссылку на задачу этих задач.

Например:

Ряд | Колонка А | Колонка Б | Колонка С
1 | | 10001 | 10002 10003
2 | 10001 | 10002 | 10003
3 | 10001 10002 | 10003 |
Этот комментарий был сведен к минимуму модератором на сайте
вы хотели бы использовать функцию Instr(), которая будет проверять что-то в строке текста в ячейке. Вы также можете использовать Left() и Right(), если вы ищете начальные или конечные детали.
Этот комментарий был сведен к минимуму модератором на сайте
Есть ли способ получить уникальное «имя» для «класса 1»?
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Сим-Джон,
Возможно, приведенная ниже статья может решить вашу проблему, пожалуйста, просмотрите ее:
https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html
Здесь еще нет комментариев

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

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