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% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Коментарі (43)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
Як я можу налаштувати цю формулу, щоб розділяти кожне повернуто значення, крім «,», а також повертати лише унікальні значення?
Цей коментар був мінімізований модератором на сайті
Дякую за код!!

Що стосується символів підстановки, то іншим способом є використання INSTR

Ви можете замінити [ If rng = pValue Then ] на [ InStr(1, rng.Value, pValue) Then ] і якщо ви не хочете, щоб він був чутливим до регістру, використовуйте [ InStr(1, rng.Value, pValue, vbTextCompare) Тоді ]
Цей коментар був мінімізований модератором на сайті
Дякую за код VBA вище. Чи можете ви сказати мені, як зробити так, щоб результати вводилися в новий рядок у клітинці, наприклад, Alt-Enter 300 400 1000 1300
Цей коментар був мінімізований модератором на сайті
Дякуємо, що поділилися кодом вище. Я використовую це вже кілька місяців, але сьогодні він, здається, не працює. Я отримую порожні клітинки замість звичайної помилки, коли потрібно повернути дані. Якісь думки?
Цей коментар був мінімізований модератором на сайті
Чудова робота.. Отримав саме те, що я хотів !!! Люблю це!!
Цей коментар був мінімізований модератором на сайті
Привіт, я справді вражений роботою, і її так легко створити, щоб використовувати цю функцію. проте мені потрібна додаткова підтримка. Мій ? полягає в тому, як я можу вибрати число з комірки з кількома клітинками в моєму масиві vlookup. тобто Якщо клітинка A1 = 100, A2 = 350, A3 = 69 C1 = 100; 1222; 12133 С2 = 69; 222 D1 = Apple D2 = банан Отже, як я можу вибрати 100 з мого стовпця C масиву таблиці, щоб отримати відповідний D1 = apple. Будь ласка, зверніть увагу, що я маю 7-значні числа в моєму значенні пошуку та масиві таблиці, який розділяється символом «;». Буду дуже вдячний, якщо ви зможете вирішити цю проблему та допомогти мені заощадити багато часу.
Цей коментар був мінімізований модератором на сайті
Дякую за VBA-код. Я отримав саме те, що хочу! Я змінив лише код " rng.Offset(0, pIndex - 1) " на " rng.Offset(0, pIndex - 2) " . Так само MYVLOOKUP може здійснювати пошук справа наліво.
Цей коментар був мінімізований модератором на сайті
Це саме те, що я шукав і не думав просто створити власну UDF. Однак він працюватиме не так, як VLOOKUP. Якщо рядок, який ви шукаєте, знаходиться не тільки в першому стовпці, він може надати вам дані за межами вихідного діапазону. Ім'я Номер Інше ім'я Стовпець не в діапазоні пройдено Джей 1 Джей 1 Джей 2 Джей 2 Кріс 3 Кріс 3 Хорхе 4 Хорхе 4 Джей 5 Джей 5 Хорхе 6 Хорхе 6 Якщо в таблиці вище були клітинки A1:D7, якщо ви пройшли лише A1:C7, ваш Функція "MYVLOOKUP" повертає 1 1 2 2 5 5, коли ви очікуєте, що вона поверне 1 2 5. Наведені нижче зміни вирішують проблему: Функція MYVLOOKUP(pValue як рядок, pWorkRng як діапазон, pIndex як довго) "Оновлення 20150310 6" /9/16 Jay Coltrain 'Dim rng As Range Dim xResult As String xResult = "" Dim Rows As Long, i As Long Rows = pWorkRng.Rows.Count For i = 1 To Rows If pWorkRng.Cells(i, 1). Значення = pValue Тоді xResult = xResult & " " & pWorkRng.Cells(i, 1).Offset(0, pIndex - 1) End If Next i 'Для кожного rng ​​In pWorkRng ' Якщо rng ​​= pValue Тоді ' xResult = xResult & " " & rng.Offset(0, pIndex - 1) ' End If 'Next MYVLOOKUP = xResult End Function
Цей коментар був мінімізований модератором на сайті
Це чудово працює, але мені потрібна допомога з командою видалення дублікатів із результатів. А якщо серйозно, чудова робота.
Цей коментар був мінімізований модератором на сайті
Це чудово працює, але мені все одно потрібна допомога з функцією команди, щоб видалити дублікати з результатів.
Цей коментар був мінімізований модератором на сайті
Повідомте мене про наступні коментарі
Цей коментар був мінімізований модератором на сайті
Нічого не повертати! після застосування MYLOOKUP не дає результату, але порожнє.
Цей коментар був мінімізований модератором на сайті
Привіт, це добре працює. Що я хотів би зробити, так це адаптувати код, щоб розділяти результати значень за допомогою «///» або будь-якого іншого маркера (з технічних причин мені не потрібен лише один символ). Крім того, я помітив, що ця формула не працює з підстановкою. Я знаю, що прошу забагато, але це не так, оскільки vlookup може працювати, коли я шукаю =myvlookup("*"&E6&"*",$A$2:$C$15,2), що він зробив би/може зробити. Будь-яка допомога?
Цей коментар був мінімізований модератором на сайті
Попередження. Я зрозумів, як отримати будь-який роздільник у цьому виводі. Його рудиментарний. Але я це зрозумів. xResult = xResult & "///" & rng.Offset(0, pIndex - 1) Останнє і найбільш бажане - це дозволити йому працювати з підстановочними знаками в критеріях пошуку. Ще раз дякую за це прекрасне та блискуче рішення. Надзвичайно корисно. Тепер я просто хочу, щоб макрос запускався та був постійно встановлений у моєму Excel, незалежно від того, що я роблю, щоб я міг використовувати його, коли мені це потрібно. І підстановки! Дуже дякую. Залишилося зробити лише символи підстановки.
Цей коментар був мінімізований модератором на сайті
Щоб отримати унікальний запис, ви можете використовувати нижче: (змінено за допомогою іншого коду користувача) Функція MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long) 'Update 20150310 'Updated 6/9/16 Jay Coltrain 'Dim rng As Range Dim xResult As String xResult = "" Dim Rows As Long, i As Long Rows = pWorkRng.Rows.Count For i = 1 To Rows Якщо pWorkRng.Cells(i, 1).Value = pValue Тоді xResult = xResult & "," & pWorkRng.Cells(i, 1).Offset(0, pIndex - 1) End If Next i Dim varSection As Variant Dim sTemp As String Dim sDelimiter As String sDelimiter = "," For Every varSection In Split(xResult, sDelimiter) Якщо InStr(1, sDelimiter & sTemp & sDelimiter, sDelimiter & varSection & sDelimiter, vbTextCompare) = 0 Тоді sTemp = sTemp & sDelimiter & varSection End Якщо наступний varSection MYVLOOKUP = Mid(sTemp, Len(s1) End Function) +
Цей коментар був мінімізований модератором на сайті
Це спрацювало ідеально, але мені знадобився деякий час, щоб налаштувати функцію належним чином у моїй електронній таблиці з 20 вкладками та понад 50 тисяч рядків. Тепер ВЕЛИКЕ питання полягає в тому, як взяти цей рядок із роздільниками, а потім використовувати кожен запис як значення для пошуку індексу/відповідності (не одружено з індексом/відповідністю, але здається швидше) в іншому наборі даних, повертаючи значення SUM всіх повернених в одну клітинку . Мій сценарій полягає в тому, що в мене є одне замовлення, яке має кілька рахунків-фактур. Ваша функція MYVLOOKUP чудово працює, щоб звітувати про всі рахунки-фактури в одній клітинці. Тепер я хочу взяти кожне об’єднане повернення зі звітною клітинкою, прокрутити цей масив і підсумувати суми платежу кожного рахунку-фактури назад у клітинку формули. Я ціную будь-яку допомогу, яку ви можете запропонувати з цього приводу, і дякую за функцію MYVLOOKUP!
Цей коментар був мінімізований модератором на сайті
Що б я не робив, я завжди отримую #value! повертається замість результату. vlookup працює чудово, тому дані працюють. Вже слідкував за процесом увімкнення макросів. Я навіть об’єднав все в один аркуш. Якісь ідеї??
Цей коментар був мінімізований модератором на сайті
Чудовий макрос, корисний. Але потрібно знати, чи можна змінити його, щоб перевірити 2 критерії, і чи знайдеться хтось, щоб підстановки працювали для нього. Будь-яка допомога?
Цей коментар був мінімізований модератором на сайті
Чи є спосіб змінити результат, щоб замість показу 1000 1000 -1000 він показував, наприклад, 1,000/1,000/(1,000)?
Цей коментар був мінімізований модератором на сайті
Чудова функція, проте перебір 100,000 XNUMX записів є чимось значущим для мого бідного ноутбука, потрібно дати йому працювати протягом ночі!
Цей коментар був мінімізований модератором на сайті
Це дивовижно, дякую!
There are no comments posted here yet
Load More
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця