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

Як vlookup повернути кілька значень в одній комірці в Excel?

Як правило, в Excel, коли ви використовуєте функцію VLOOKUP, якщо є кілька значень, що відповідають критеріям, ви просто можете отримати перше. Але, іноді, ви хочете повернути всі відповідні значення, які відповідають критеріям, в одну комірку, як показано на наступному скріншоті, як ви могли це вирішити?

Vlookup для повернення кількох значень в одну комірку з функцією TEXTJOIN (Excel 2019 та Office 365)

Vlookup для повернення кількох значень в одну клітинку з користувацькою функцією

Vlookup для повернення кількох значень в одну комірку з корисною функцією


Vlookup для повернення кількох значень в одну комірку з функцією TEXTJOIN (Excel 2019 та Office 365)

Якщо у вас вища версія Excel, така як Excel 2019 та Office 365, є нова функція - ТЕКСТЬ, за допомогою цієї потужної функції ви можете швидко переглядати та повертати всі відповідні значення в одну комірку.

Vlookup для повернення всіх відповідних значень в одну комірку

Будь ласка, застосуйте наведену нижче формулу до порожньої комірки, куди ви хочете ввести результат, а потім натисніть Ctrl + Shift + Enter клавіші разом, щоб отримати перший результат, а потім перетягніть маркер заповнення вниз до комірки, в якій ви хочете використовувати цю формулу, і ви отримаєте всі відповідні значення, як показано на знімку екрана нижче:

=TEXTJOIN(",",TRUE,IF($A$2:$A$11=E2,$C$2:$C$11,""))

Примітка: У наведеній вище формулі A2: A11 - діапазон пошуку містить дані пошуку, E2 - значення пошуку, C2: C11 - діапазон даних, з якого потрібно повернути відповідні значення, ","є розділювачем для розділення декількох записів.

Vlookup для повернення всіх відповідних значень без дублікатів в одну комірку

Якщо ви хочете повернути всі відповідні значення на основі даних пошуку без дублікатів, нижченаведена формула може вам допомогти.

Скопіюйте та вставте наступну формулу в порожню комірку, а потім натисніть Ctrl + Shift + Enter клавіші разом, щоб отримати перший результат, а потім скопіюйте цю формулу, щоб заповнити інші клітинки, і ви отримаєте всі відповідні значення без дублікатів, як показано нижче:

=TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$11, IF(E2=$A$2:$A$11, $C$2:$C$11, ""), 0),"")=MATCH(ROW($C$2:$C$11), ROW($C$2:$C$11)), $C$2:$C$11, ""))

Примітка: У наведеній вище формулі A2: A11 - діапазон пошуку містить дані пошуку, E2 - значення пошуку, C2: C11 - діапазон даних, з якого потрібно повернути відповідні значення, ","є розділювачем для розділення декількох записів.

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, ", ") у певну порожню комірку, де ви хочете помістити результат, а потім перетягніть маркер заповнення вниз, щоб отримати всі відповідні значення в одній комірці, яку ви хочете, див. знімок екрана:

Примітка: У наведеній вище формулі A2: A11 - діапазон пошуку містить дані пошуку, E2 - значення пошуку, C2: C11 - діапазон даних, з якого потрібно повернути відповідні значення, ","є розділювачем для розділення декількох записів.

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. Після вставки коду натисніть інструменти > посилання у відкритому 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:

Примітка: У наведеній вище формулі A2: C11 діапазон даних, який ви хочете використовувати, E2 - значення пошуку, число 3 - номер стовпця, який містить повернені значення.

Vlookup для повернення кількох значень в одну комірку з корисною функцією

 Якщо у вас є наш Kutools для Excel, З його Розширені комбіновані ряди функцію, ви можете швидко об'єднати або об'єднати рядки, виходячи з того самого значення, і зробити деякі обчислення, як вам потрібно.

Примітка:Щоб застосувати це Розширені комбіновані ряди, по-перше, вам слід завантажити Kutools для Excel, а потім швидко та легко застосувати функцію.

після установки Kutools для Excel, будь ласка, виконайте наступне:

1. Виберіть діапазон даних, для якого потрібно об’єднати дані одного стовпця на основі іншого стовпця.

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

3. В вискочив Розширені комбіновані ряди діалогове вікно:

  • Клацніть назву ключового стовпця, яке потрібно об’єднати, а потім клацніть Первинний ключ
  • Потім клацніть інший стовпець, для якого потрібно об’єднати його дані на основі ключового стовпця, і клацніть Поєднувати вибрати один роздільник для розділення об’єднаних даних.

4. Потім натисніть OK , і ви отримаєте такі результати:

Завантажте та безкоштовну пробну версію Kutools для Excel зараз!


Більше відносних статей:

  • Функція VLOOKUP з деякими основними та вдосконаленими прикладами
  • У Excel функція VLOOKUP - це потужна функція для більшості користувачів Excel, яка використовується для пошуку значення в крайньому лівому куті діапазону даних і повернення відповідного значення в тому ж рядку із вказаного вами стовпця. Цей підручник розповідає про те, як використовувати функцію VLOOKUP з деякими основними та вдосконаленими прикладами в Excel.
  • Повернути кілька значень відповідності на основі одного або декількох критеріїв
  • Зазвичай пошук певного значення та повернення відповідного елемента є простим для більшості з нас за допомогою функції VLOOKUP. Але, ви коли-небудь намагалися повернути кілька відповідних значень на основі одного або кількох критеріїв? У цій статті я познайомлюсь із деякими формулами вирішення цього складного завдання в Excel.
  • Перегляд і повернення декількох значень по вертикалі
  • Зазвичай ви можете використовувати функцію Vlookup для отримання першого відповідного значення, але, іноді, ви хочете повернути всі відповідні записи на основі певного критерію. У цій статті я розповім про те, як шукати і повертати всі відповідні значення вертикально, горизонтально або в одну клітинку.
  • Перегляд і повернення декількох значень зі спадного списку
  • Як в Excel можна шукати та повертати кілька відповідних значень зі спадного списку, що означає, що коли ви вибираєте один елемент зі спадного списку, усі його відносні значення відображаються одночасно. У цій статті я буду представляти рішення поетапно.

Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

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

Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці