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

Як легко об’єднати текст на основі критеріїв у Excel?

Припустимо, у мене є стовпець ідентифікаційних номерів, який містить кілька дублікатів та стовпець імен, і тепер я хочу об'єднати імена на основі унікальних ідентифікаційних номерів, як показано на лівому скріншоті, щоб швидко поєднати текст на основі критеріїв, як ми могли б робити в Excel?

doc поєднує текст на основі критеріїв 1

Об’єднати текст на основі критеріїв за допомогою функції, визначеної користувачем

Об’єднати текст на основі критеріїв за допомогою Kutools для Excel


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

1. Візьмемо такі дані, як приклад, вам потрібно спочатку витягти унікальні ідентифікаційні номери, застосуйте цю формулу масиву: =IFERROR(INDEX($A$2:$A$15, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$15), 0)),""), Введіть цю формулу в порожню комірку, наприклад D2, а потім натисніть Ctrl + Shift + Enter клавіші разом, див. знімок екрана:

doc поєднує текст на основі критеріїв 2

Чайові: У наведеній вище формулі, A2: A15 - діапазон даних списку, з якого ви хочете витягти унікальні значення, D1 - це перша клітинка стовпця, для якої потрібно вивести результат вилучення.

2. А потім перетягніть маркер заповнення вниз, щоб витягти всі унікальні значення, поки не відображатимуться пробіли, див. Знімок екрана:

doc поєднує текст на основі критеріїв 3

3. На цьому кроці вам слід створити файл Визначена користувачем функція щоб поєднати імена на основі унікальних ідентифікаційних номерів, натисніть і утримуйте ALT + F11 і відкриває Microsoft Visual Basic для додатків вікна.

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

Код VBA: об'єднання тексту на основі критеріїв

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

5. Потім збережіть і закрийте цей код, поверніться до робочого аркуша та введіть цю формулу в клітинку E2, = CONCATENATEIF ($ A $ 2: $ A $ 15, D2, $ B $ 2: $ B $ 15, ",") , див. скріншот:

doc поєднує текст на основі критеріїв 4

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

doc поєднує текст на основі критеріїв 5

Порада:

1. У наведеній вище формулі A2: A15 це вихідні дані, на основі яких ви хочете об’єднати, D2 - унікальне значення, яке ви отримали, та B2: B15 - це стовпець імен, який потрібно об’єднати.

2. Як бачите, я об’єднав значення, розділені комами, ви можете використовувати будь-які інші символи, змінюючи кому “,” формули, як вам потрібно.


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

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

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

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

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

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

doc поєднує текст на основі критеріїв 7

4. А потім клацніть Назва стовпець, який потрібно об'єднати, а потім клацніть Поєднувати і виберіть один роздільник для об’єднаних даних, див. знімок екрана:

doc поєднує текст на основі критеріїв 8

5. Після закінчення цих налаштувань натисніть OK для виходу з діалогового вікна, а дані у стовпці B об’єднані разом на основі ключового стовпця A. Дивіться знімок екрана:

doc поєднує текст на основі критеріїв 9

За допомогою цієї функції наступна проблема буде вирішена якомога швидше:

Як об'єднати кілька рядків в один і підсумувати дублікати в 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% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Коментарі (38)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
Це чудове рішення (код VBA), яке за лічені хвилини задовольнило мої вимоги. Я передам ваш сайт іншим, і я відвідаю все, що мені потрібно надалі.
Каладхар
Цей коментар був мінімізований модератором на сайті
Дякую за цей код. Це було саме те, що мені потрібно. Ви заощадили мені багато зусиль, дуже дякую.
Гаррі
Цей коментар був мінімізований модератором на сайті
Дякую за публікацію, це саме те, що я шукаю. Здається, я неправильно зберігаю код vba. Я отримую повідомлення про помилку про знайдене неоднозначне ім’я. Будь-які пропозиції чи крок за кроком щодо етапу VBA цього проекту? Спасибі
ConfusedNBusy
Цей коментар був мінімізований модератором на сайті
Чи зможе цей інструмент обробляти комбінації, чутливі до регістру, такі як jABC 123 abc 345 ABc 678 ABC 912
Manoj
Цей коментар був мінімізований модератором на сайті
Цей код VBA врятував день для мене. Дякую!
Тім Блоссер
Цей коментар був мінімізований модератором на сайті
Я шукаю спосіб використовувати варіант цього коду для створення списку варіантів на основі головного варіанта. Використовуючи ваші приклади даних, мені потрібно об’єднати стовпці A і B в унікальні ідентифікатори, а потім об’єднати ці ідентифікатори до кожного рядка на основі значення в стовпці A, виключаючи значення з об’єднаного для цього рядка, а решту в альфа-сортуванні order: Master id name id variant list CN20150012 Lucy CN20150012-Lucy CN20150012-Andy CN20150012-Monica CN20150012-Phiby US20150011 Tommas US20150011-Tommas US20150011-Rose CN20150012 Monica CN20150012-Monica CN20150012-Andy CN20150012-Lucy CN20150012-Phiby CN20150012 Phiby CN20150012-Phiby CN20150012 -Andy CN20150012-Lucy CN20150012-Monica US20150011 Rose US20150011-Rose US20150011-Tommas UK20150014 Peter UK20150014-Peter UK20150014-Anith UK20150014-Kristi UK20150014-Libin JP20150010 Ramon JP20150010-Ramon JP20150010-Brenda JP20150010-James UK20150014 Libin UK20150014-Libin UK20150014-Anith UK20150014 -Kristi UK20150014-Peter UK20150014 Anith UK20150014-Anith UK20150014-Kristi UK20150014-Libin UK20150014-Peter JP20150010 James JP20150010 20150010-James JP20150010-Brenda JP20150010-James JP20150012-Matus CN20150012 Andy CN20150012-Andy CN20150012-Lucy CN20150012-Monica CN20150014-Phiby UK20150014 Matus UK20150010-Matus JP20150010-Brenda JP20150014-James UK20150014 Kristi UK20150014-Kristi UK20150014-Anith UK20150014-Libin UK20150010- Peter JP20150010 Brenda JP20150010-Brenda JP20150010-James JP1000-Ramon У мене є аркуш із понад 4 рядками, кожен елемент має до XNUMX варіантів. Спробувати зробити це вручну неможливо, але я не можу знайти рішення, яке відповідає моїм потребам.
Renee
Цей коментар був мінімізований модератором на сайті
Витрачає більше часу на оновлення тієї ж формули concatenateif(). у мене 5000 рядків. і вже більше 2 годин він все ще оновлюється :( Чи є рішення, щоб він працював швидко?
лата
Цей коментар був мінімізований модератором на сайті
Пояснено докладно та легко для розуміння, дійсно допомогло, коли я застряг у точно такій же ситуації.
Самрат Говекар
Цей коментар був мінімізований модератором на сайті
Надзвичайно корисно і добре пояснило
Самрат Говекар
Цей коментар був мінімізований модератором на сайті
Чудово, дякую! Я використовував рішення VBA, і воно чудово працювало.
Метт
Цей коментар був мінімізований модератором на сайті
Чудово!!! Дуже дякую!
нікадо
Цей коментар був мінімізований модератором на сайті
Це не працює для великого діапазону даних. Я виявив, що його робочий діапазон становить лише до A2:A362. Ми будемо вдячні, якщо ви поділитеся рішенням для великого діапазону даних, наприклад A2:A200000 .... Дякую
Ram Bahadur Ale
Цей коментар був мінімізований модератором на сайті
Працює чудово, тільки повільно. Я роблю це з 27 тис. рядків тексту в excel, просто вимкніть його, залиште його працювати
Кріс
Цей коментар був мінімізований модератором на сайті
Це не працює для великого діапазону даних. Я виявив, що його робочий діапазон даних становить до A2:A362. Ми будемо вдячні, якщо ви поділитеся рішенням для охоплення ширшого діапазону даних, наприклад A2:A200000 ..... Дякуємо
Ram Bahadur Ale
Цей коментар був мінімізований модератором на сайті
Привіт! concactenateif - це саме те, що я шукав. Але, на жаль, не вдається змусити його працювати. Завжди отримуйте помилку компіляції: синтаксичну помилку. Є ідеї? У минулому, з деякими імпортованими модулями VBA, я помітив, що мені довелося замінити "," на ";" як у моєму ПК, можливо, завдяки моїм регіональним налаштуванням, це єдиний спосіб, яким він працює. Жадібно використовуйте вбудовані суміфи тощо. Але не можу зрозуміти, де я помиляюся в цьому. Ще одна можливість, яка спадає на думку, — це той факт, що в офісі 365 «concat» замінює «concactenate». Чи можете ви допомогти, будь ласка? Наперед дякую, Яш
Яш
Цей коментар був мінімізований модератором на сайті
У коді використовуються деякі нерозривні пробіли для відступів, вони викликають Excel2016. Важко помітити невидиму помилку..
DJDave
Цей коментар був мінімізований модератором на сайті
У мене виникла проблема після вставки цього коду в Excel 2016 – він містить нерегулярні пробіли (можливо, нерозривні пробіли?), які викликають синтаксичні помилки, які не очевидні, як би уважно ви не придивлялися, тому що вони невидимі! Проблема полягає в просторах відступів. Вставте код у Word і ввімкніть приховані символи, щоб побачити їх.
DJDave
Цей коментар був мінімізований модератором на сайті
Ого!! Геній! Спрацював як шарм! Є пробіли, які відображаються як інший персонаж. Велике спасибі, Дейв! Цікаво, як тобі прийшла в голову ідея! Також цікаво, як це працює для деяких інших людей. У всякому разі, ще раз дякую!
Яш
Цей коментар був мінімізований модератором на сайті
Чи є спосіб зробити це на Mac????
Це саме те, що мені потрібно - будь ласка, дайте мені знати (або якщо будь-яке програмне забезпечення Mac зробить це, про яке ви знаєте). Дякую
Мікеле
Цей коментар був мінімізований модератором на сайті
Чи є спосіб застосувати цю функцію CONCATENATEIF на окремому аркуші? Він працює, коли я розміщую його в одному аркуші з вхідними даними, але мені потрібні обидві таблиці на різних аркушах, і він не працює.
krawlis
Цей коментар був мінімізований модератором на сайті
Так, ви хочете додати функцію до модуля. Зайдіть у редактор VBA, клацніть правою кнопкою миші «VBAProject» у провіднику проектів, наведіть курсор миші на пункт меню «Вставка» і виберіть у цьому підменю «Модуль». Будь-які функції, які ви туди введете, можна буде використовувати на будь-якому аркуші вашої робочої книги.
Ел Буллі
Цей коментар був мінімізований модератором на сайті
Привіт, хлопці, я отримав помилку #NAME? коли я застосовую формули CONCATENATEIF у файлі Excel після встановлення коду VBA для цього, чи може хтось допомогти мені вирішити це, дякую
тянь мін
Цей коментар був мінімізований модератором на сайті
Так легко, дякую :)
Ahmed
Цей коментар був мінімізований модератором на сайті
Чи можна замінити роздільник коми на розрив рядка, тобто char(10)? Велике дякую.
Девід
Цей коментар був мінімізований модератором на сайті
Привіт, Девід,

Щоб об’єднати клітинки з розривом рядка, вам може допомогти наступна функція, визначена користувачем.

Функція ConcatenateIf_LineBreak(CriteriaRange як діапазон, умова як варіант, ConcatenateRange як діапазон, необов'язковий роздільник як рядок = ",") як варіант
Dim xResult як рядок
On Error Resume Next
Якщо CriteriaRange.Count <> ConcatenateRange.Count Then
ConcatenateIf = CVERr(xlErrRef)
Функція виходу
End If
Для I = 1 До CriteriaRange.Count
Якщо CriteriaRange.Cells(I).Value = Умова Тоді
xResult = xResult &amp; vbCrLf &amp; ConcatenateRange.Cells(I).Value
End If
Далі я
Якщо xResult <> "" Тоді
xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf_LineBreak = xResult
Функція виходу
End Function

Після вставки цього коду застосуйте цю формулу: =ConcatenateIf_LineBreak(A2:A13,F2,B2:B13,",").

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