Как vlookup и объединить несколько соответствующих значений в Excel?
Как все мы знаем, ВПР Функция в Excel может помочь нам найти значение и вернуть соответствующие данные в другом столбце, но в целом она может получить только первое относительное значение, если есть несколько совпадающих данных. В этой статье я расскажу о том, как выполнить vlookup и объединить несколько соответствующих значений только в одной ячейке или вертикальном списке.
Vlookup и возврат нескольких совпадающих значений по вертикали с формулой
Vlookup и объединение нескольких совпадающих значений в ячейке с помощью Kutools for Excel
Vlookup и возврат нескольких совпадающих значений по вертикали с формулой
Предположим, у меня есть следующий диапазон данных, чтобы получить все соответствующие значения на основе определенного значения по вертикали, как показано на следующем снимке экрана, вы можете применить формулу массива.
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 вместе, чтобы получить относительную базу значений по определенному критерию, см. снимок экрана:
Внимание: В приведенной выше формуле:
A1: A16 это диапазон столбцов, который содержит конкретное значение, которое вы хотите найти;
D2 указывает конкретное значение, которое вы хотите просмотреть;
B1: B16 это диапазон столбцов, из которого вы хотите вернуть соответствующие данные;
$ 1: $ 16 указывает ссылку на строки в пределах диапазона.
2. Затем выберите ячейку E2 и перетащите дескриптор заполнения вниз к ячейкам, пока не получите пустые ячейки, а все совпадающие значения будут перечислены в столбце, как показано на следующем снимке экрана:
Vlookup и объединение нескольких совпадающих значений в ячейке с помощью функции, определяемой пользователем
Вместо получения относительных значений по вертикали иногда требуется объединить соответствующие значения в одну ячейку и объединить их с помощью определенного разделителя. В этом случае вам может помочь следующая функция, определяемая пользователем.
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, все соответствующие значения, основанные на определенных данных, были возвращены в одну ячейку с разделителем пробела, см. снимок экрана:
Внимание: В приведенной выше формуле: D2 указывает значения ячеек, которые вы хотите найти, А1: В16 это диапазон данных, из которого вы хотите получить данные, число 2 это номер столбца, из которого должно быть возвращено совпадающее значение, вы можете изменить эти ссылки по своему усмотрению.
Vlookup и объединение нескольких совпадающих значений в ячейке с помощью Kutools for Excel
Если у вас есть Kutools for Excel, С его Расширенные ряды комбинирования вы можете быстро и легко завершить эту работу. Эта функция может помочь вам объединить все совпадающие значения с определенными разделителями на основе тех же данных в другом столбце.
| Kutools for Excel : с более чем 300 удобными надстройками Excel, бесплатно и без ограничений в течение 30 дней. |
После установки Kutools for Excel, пожалуйста, сделайте следующее:
1. Выберите диапазон данных, для которого вы хотите получить соответствующие значения на основе конкретных данных.
2. Затем нажмите Кутулс > Слияние и разделение > Расширенные ряды комбинирования, см. снимок экрана:
3. В Расширенные ряды комбинирования В диалоговом окне щелкните имя столбца, на основе которого вы хотите объединить, затем щелкните Основной ключ кнопку, см. снимок экрана:
4. Затем щелкните имя другого столбца, в котором вы хотите вернуть совпадающие значения, и щелкните Сочетать чтобы выбрать один разделитель для разделения комбинированных значений, см. снимок экрана:
5, Затем нажмите Ok кнопки, все соответствующие значения, основанные на одинаковых значениях, были объединены вместе с определенным разделителем, см. скриншоты:
 |
 |
 |
Скачать и бесплатную пробную версию Kutools for Excel Сейчас !
Демонстрация: Vlookup и объединение нескольких совпадающих значений в ячейке с помощью Kutools for Excel
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы... Предотвращение дублирования ячеек; Сравнить диапазоны...
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
