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

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

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

doc повертає кілька значень, розділених комами 1

Повертає кілька значень пошуку в одній комірці, розділеній комами, за допомогою функції, визначеної користувачем

Повертає кілька значень пошуку в одній комірці, розділеній комами, за допомогою Kutools для Excel


Повертає кілька значень пошуку в одній комірці, розділеній комами, за допомогою функції, визначеної користувачем

Зазвичай для нас не існує прямого способу витягти та повернути декілька значень відповідності та розділити їх комою в одну клітинку. Тут ви можете створити визначену користувачем функцію для вирішення цієї роботи, будь ласка, виконайте наступне:

1. Утримуйте клавішу ALT + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.

2. Клацання Insert > Модуліта вставте наступний код у Модулі Вікно

Код VBA: повертає кілька значень пошуку в одну комірку, відокремлену комами

Function SingleCellExtract(LookupValue As String, LookupRange As Range, ColumnNumber As Integer, Char As String)
'Updateby Extendoffice
    Dim I As Long
    Dim xRet As String
    For I = 1 To LookupRange.Columns(1).Cells.Count
        If LookupRange.Cells(I, 1) = LookupValue Then
            If xRet = "" Then
                xRet = LookupRange.Cells(I, ColumnNumber) & Char
            Else
                xRet = xRet & "" & LookupRange.Cells(I, ColumnNumber) & Char
            End If
        End If
    Next
    SingleCellExtract = Left(xRet, Len(xRet) - 1)
End Function

3. Потім збережіть цей код і закрийте вікно Модуль, поверніться до робочого аркуша та введіть цю формулу: = SingleCellExtract (D2, A2: B15,2, ",") у порожню комірку, якій потрібно повернути результат. А потім натисніть вводити ключ, щоб отримати результат, див. знімок екрана:

doc повертає кілька значень, розділених комами 2

примітки: У наведеній вище формулі:

D2: вказує значення комірок, які потрібно шукати;

А2: В15: - діапазон даних, для якого потрібно отримати дані;

2: число 2 - це номер стовпця, яке має повернути відповідне значення;

,: кома - це роздільник, який потрібно розділити на кілька значень.

Ви можете змінити їх відповідно до своїх потреб.


Повертає кілька значень пошуку в одній комірці, розділеній комами, за допомогою Kutools для Excel

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

Kutools для Excel : з більш ніж 300 зручними надбудовами Excel, які можна спробувати без обмежень протягом 30 днів

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

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

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

3, в Об'єднати рядки на основі стовпця у діалоговому вікні клацніть ім'я стовпця, на основі якого потрібно об'єднати, а потім клацніть Первинний ключ , див. знімок екрана:

doc повертає кілька значень, розділених комами 4

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

doc повертає кілька значень, розділених комами 5

5. Потім натисніть OK , всі відповідні комірки з однаковим значенням об'єднані в одну комірку, яка відокремлена комою, див. скріншоти:

doc повертає кілька значень, розділених комами 6 2 doc повертає кілька значень, розділених комами 7

Клацніть, щоб дізнатися більше про цю утиліту Advanced Combine Rows ...

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


Демонстрація: повертає кілька значень пошуку в одній комірці, розділеній комами, за допомогою Kutools для Excel

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

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

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% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Коментарі (14)
Оцінено 5 з 5 · рейтинги 2
Цей коментар був мінімізований модератором на сайті
Поки я збираюся вставити та зберегти модуль, з’являється спливаюче повідомлення про значну втрату функції перевірки сумісності
Цей коментар був мінімізований модератором на сайті
Дякую за цей пост. Знаєте, як би я маніпулював двома окремими цілими числами, які це створює. Наприклад, скажімо, що функція '=SingleCellExtract' тепер створює (1 , 2). Чи є спосіб мати поруч із нею клітинку, яка відповідає (1+.5 , 2+.5)?
Цей коментар був мінімізований модератором на сайті
Це працює, але значно сповільнює мій Excel! Будь-які поради, які допоможуть прискорити?
Цей коментар був мінімізований модератором на сайті
Це просто не працює. Я не зміг змусити його працювати в моїй власній програмі, тому я скопіював/вставив vba і формулу, і він кожного разу повертав помилку
Цей коментар був мінімізований модератором на сайті
дякую, по-перше, мені вдалося змусити це працювати без уповільнення продуктивності. Я використовую значення, а не текст, тому моє запитання полягає в тому, що я хочу повернути всіх тих, хто має менше 19 пунктів у списку. Чи може для цього працювати витяг однієї клітинки чи це має бути конкретне значення?
Цей коментар був мінімізований модератором на сайті
Команда VB розривається, коли діапазон перевищує 154 рядки (тобто :B154)....
Цей коментар був мінімізований модератором на сайті
Якщо збільшити розмір масиву, з’являється помилка
Цей коментар був мінімізований модератором на сайті
Коли 2 критерії збігаються, то повертати кілька значень пошуку в одній комірці, розділеній комами
A2=B2 Потім результат з діапазону "SingleCellExtract" - будь ласка......
Цей коментар був мінімізований модератором на сайті
Доброго ранку,

код VBA ідеально працював з моїм робочим аркушем, досить зрозумілим і простим, однак я намагався знайти спосіб сказати excel повертати лише унікальні значення. Чи можливо це за допомогою того самого коду?
Цей коментар був мінімізований модератором на сайті
привіт,

Мені потрібен точний код перевірки для кількох значень, розділених комою та пробілом для кожного значення.

приклад:
Люсі, Том, Ніколь, Акаш, Епл

Повторіть, якщо у вас є пропозиції.
Оцінено 4.5 з 5
Цей коментар був мінімізований модератором на сайті
Привіт, Маніканта
Щоб відокремити кілька значень комою та пробілом, вам просто потрібно додати пробіл після коми, змініть формулу так: =SingleCellExtract(D2,A2:B15,2,", ").
Будь ласка, спробуйте, сподіваюся, це допоможе вам!
Цей коментар був мінімізований модератором на сайті
Привіт, Скайян,

Дякую за повтор!

Я вже пробував так само, але в значенні клітинки остання додаткова кома (,) є прикладом.

Люсі, Том, Ніколь, Акаш, Яблуко,

Це не працюватиме для файлу Json, тому я хочу, щоб значення розділялися комою та пробілом, як показано нижче.

Люсі, Том, Ніколь, Акаш, Епл

Дякую!
Цей коментар був мінімізований модератором на сайті
Привіт, Маніканта
У цьому випадку ви можете застосувати наведену нижче функцію, визначену користувачем:

Function MultipleValues(work_range As Range, criteria As Variant, merge_range As Range, Optional Separator As String = ",") As Variant
Dim outcome As String
On Error Resume Next
If work_range.Count <> merge_range.Count Then
MultipleValues = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To work_range.Count
If work_range.Cells(i).Value = criteria Then
outcome = outcome & Separator & merge_range.Cells(i).Value
End If
Next i
If outcome <> "" Then
outcome = VBA.Mid(outcome, VBA.Len(Separator) + 1)
End If
MultipleValues = outcome
Exit Function
End Function


Після вставлення коду використовуйте цю формулу: =MultipleValues($A$2:$A$15,D2,$B$2:$B$15,", ")

Спробуйте, сподіваюся, це допоможе вам!
Якщо у вас все ще є якісь інші проблеми, прокоментуйте тут.
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-return-multiple-matching-1.png
Цей коментар був мінімізований модератором на сайті
Привіт, Скайян,

Зараз це працює. Дякуємо за швидку відповідь.

Це дуже корисно для мене ще раз. Дякую за вашу допомогу.

З повагою,
Маніканта.
Оцінено 5 з 5
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця