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

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