Як vlookup повернути кілька значень в одній комірці в Excel?
Як правило, в Excel, коли ви використовуєте функцію VLOOKUP, якщо є кілька значень, що відповідають критеріям, ви просто можете отримати перше. Але, іноді, ви хочете повернути всі відповідні значення, які відповідають критеріям, в одну комірку, як показано на наступному скріншоті, як ви могли це вирішити?
Vlookup для повернення кількох значень в одну комірку з функцією TEXTJOIN (Excel 2019 та Office 365)
- Vlookup для повернення всіх відповідних значень в одну комірку
- Vlookup для повернення всіх відповідних значень без дублікатів в одну комірку
Vlookup для повернення кількох значень в одну клітинку з користувацькою функцією
- Vlookup для повернення всіх відповідних значень в одну комірку
- Vlookup для повернення всіх відповідних значень без дублікатів в одну комірку
Vlookup для повернення кількох значень в одну комірку з корисною функцією
Vlookup для повернення кількох значень в одну комірку з функцією TEXTJOIN (Excel 2019 та Office 365)
Якщо у вас вища версія Excel, така як Excel 2019 та Office 365, є нова функція - ТЕКСТЬ, за допомогою цієї потужної функції ви можете швидко переглядати та повертати всі відповідні значення в одну комірку.
Vlookup для повернення всіх відповідних значень в одну комірку
Будь ласка, застосуйте наведену нижче формулу до порожньої комірки, куди ви хочете ввести результат, а потім натисніть Ctrl + Shift + Enter клавіші разом, щоб отримати перший результат, а потім перетягніть маркер заповнення вниз до комірки, в якій ви хочете використовувати цю формулу, і ви отримаєте всі відповідні значення, як показано на знімку екрана нижче:
Vlookup для повернення всіх відповідних значень без дублікатів в одну комірку
Якщо ви хочете повернути всі відповідні значення на основі даних пошуку без дублікатів, нижченаведена формула може вам допомогти.
Скопіюйте та вставте наступну формулу в порожню комірку, а потім натисніть Ctrl + Shift + Enter клавіші разом, щоб отримати перший результат, а потім скопіюйте цю формулу, щоб заповнити інші клітинки, і ви отримаєте всі відповідні значення без дублікатів, як показано нижче:
Vlookup для повернення кількох значень в одну клітинку з користувацькою функцією
Вищевказана функція TEXTJOIN доступна лише для Excel 2019 та Office 365, якщо у вас інші версії Excel нижчого рівня, для завершення цього завдання слід використовувати деякі коди.
Vlookup для повернення всіх відповідних значень в одну комірку
1. Утримуйте клавішу ALT + F11 і відкриває Microsoft Visual Basic для додатків вікна.
2. Клацання Insert > Модуліта вставте наступний код у Вікно модуля.
Код 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, ", ") у певну порожню комірку, де ви хочете помістити результат, а потім перетягніть маркер заповнення вниз, щоб отримати всі відповідні значення в одній комірці, яку ви хочете, див. знімок екрана:
Vlookup для повернення всіх відповідних значень без дублікатів в одну комірку
Щоб ігнорувати дублікати у повернутих відповідних значеннях, виконайте наведені нижче коди.
1. Утримуйте клавішу Alt + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.
2. Клацання Insert > Модуліта вставте наступний код у Вікно модуля.
Код 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. Після вставки коду натисніть Tools > посилання у відкритому 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:
Vlookup для повернення кількох значень в одну комірку з корисною функцією
Якщо у вас є наш Kutools для Excel, З його Розширені комбіновані ряди функцію, ви можете швидко об'єднати або об'єднати рядки, виходячи з того самого значення, і зробити деякі обчислення, як вам потрібно.
після установки Kutools для Excel, будь ласка, виконайте наступне:
1. Виберіть діапазон даних, для якого потрібно об’єднати дані одного стовпця на основі іншого стовпця.
2. Клацання Кутулс > Злиття та розділення > Розширені комбіновані ряди, див. скріншот:
3. В вискочив Розширені комбіновані ряди діалогове вікно:
- Клацніть назву ключового стовпця, яке потрібно об’єднати, а потім клацніть Первинний ключ
- Потім клацніть інший стовпець, для якого потрібно об’єднати його дані на основі ключового стовпця, і клацніть Поєднувати вибрати один роздільник для розділення об’єднаних даних.
4. Потім натисніть OK , і ви отримаєте такі результати:
 |
|
 |
Завантажте та безкоштовну пробну версію Kutools для Excel зараз!
Більше відносних статей:
- Функція VLOOKUP з деякими основними та вдосконаленими прикладами
- У Excel функція VLOOKUP - це потужна функція для більшості користувачів Excel, яка використовується для пошуку значення в крайньому лівому куті діапазону даних і повернення відповідного значення в тому ж рядку із вказаного вами стовпця. Цей підручник розповідає про те, як використовувати функцію VLOOKUP з деякими основними та вдосконаленими прикладами в Excel.
- Повернути кілька значень відповідності на основі одного або декількох критеріїв
- Зазвичай пошук певного значення та повернення відповідного елемента є простим для більшості з нас за допомогою функції VLOOKUP. Але, ви коли-небудь намагалися повернути кілька відповідних значень на основі одного або кількох критеріїв? У цій статті я познайомлюсь із деякими формулами вирішення цього складного завдання в Excel.
- Перегляд і повернення декількох значень по вертикалі
- Зазвичай ви можете використовувати функцію Vlookup для отримання першого відповідного значення, але, іноді, ви хочете повернути всі відповідні записи на основі певного критерію. У цій статті я розповім про те, як шукати і повертати всі відповідні значення вертикально, горизонтально або в одну клітинку.
- Перегляд і повернення декількох значень зі спадного списку
- Як в Excel можна шукати та повертати кілька відповідних значень зі спадного списку, що означає, що коли ви вибираєте один елемент зі спадного списку, усі його відносні значення відображаються одночасно. У цій статті я буду представляти рішення поетапно.
Найкращі інструменти продуктивності офісу
Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу. Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...
Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу
- Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
- Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
- Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
