Совет. Другие языки - Google-Translated. Вы можете посетить English версия этой ссылки.
Войти
x
or
x
x
Регистрация
x

or

Как легко конкатенировать текст на основе критериев в Excel?

Предположим, у меня есть столбец идентификационных номеров, который содержит некоторые дубликаты и столбец с именами, и теперь я хочу объединить имена на основе уникальных идентификационных номеров, как показано на рисунке слева, чтобы быстро совместить текст на основе критериев, как мы можем делать в Excel?

doc объединить текст на основе критериев 1

Конкатенация текста на основе критериев с пользовательской функцией

Объединить текст, основанный на критериях с Kutools для Excel


Скомпоновать текст, основанный на том же значении в другом столбце:

Kutools for ExcelАвтора Расширенные ряды комбинирования utiltiy, вы можете быстро объединить несколько повторяющихся строк в одну запись на основе ключевых столбцов, а также использовать некоторые вычисления, такие как сумма, средний, счетчик и т. д. для других столбцов.

  • 1. Укажите столбец ключей, который вы хотите объединить на другом столбце;
  • 2. Выберите разделитель для комбинированных данных.

doc объединить текст на основе критериев 10

Kutools for Excel: с более чем 200 удобными надстройками Excel, бесплатно попробовать без ограничений в 60-дни. Скачать и бесплатно пробную версию!



Чтобы объединить текст с уникальными идентификационными номерами, вы можете сначала извлечь уникальные значения, а затем создать определенную пользователем функцию для объединения имен на основе уникального идентификатора.

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

doc объединить текст на основе критериев 2

Чаевые: В приведенной выше формуле, A2: A15 это диапазон данных списка, из которого вы хотите извлечь уникальные значения, D1 это первая ячейка столбца, которую вы хотите вывести из извлеченного результата.

2, Затем перетащите дескриптор заполнения вниз, чтобы извлечь все уникальные значения, пока не отобразятся пробелы, см. Снимок экрана:

doc объединить текст на основе критериев 3

3, На этом этапе вы должны создать Пользовательская функция для объединения имен на основе уникальных идентификационных номеров, пожалуйста, ALT + F11 и открывает Microsoft Visual Basic для приложений окна.

4. Щелчок Вставить > модуль, и вставьте следующий код в модуль Окно.

Код VBA: конкатенация текста на основе критериев

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Update 20150414
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 for Excel, С его Расширенные ряды комбинирования утилита, вы можете быстро и удобно конкатенировать текстовую базу по критериям.

Kutools for Excel : с более чем 300 удобными надстройками Excel, бесплатно попробовать без ограничений в 60-дни.

После установки Kutools for Excel, выполните следующие действия:

1, Выберите диапазон данных, который вы хотите объединить, на основе одного столбца.

2. Щелчок Kutools > содержание > Расширенные ряды комбинирования, см. снимок экрана:

doc объединить текст на основе критериев 6 6

3. В Объединить строки на основе столбца диалогового окна, щелкните столбец идентификатора, а затем щелкните Основной ключ чтобы этот столбец был ключевым столбцом, на котором объединены ваши данные, см. скриншот:

doc объединить текст на основе критериев 7

4, Затем нажмите Имя который вы хотите объединить значения, затем нажмите Сочетать и выберите один разделитель для комбинированных данных, см. снимок экрана:

doc объединить текст на основе критериев 8

5, После завершения этих настроек нажмите OK для выхода из диалогового окна, а данные в столбце B объединены вместе с ключевым столбцом A. См. снимок экрана:

doc объединить текст на основе критериев 9

Узнать больше об этой комбинации строк на основе функции столбца.

С этой функцией, как можно скорее, будет решена следующая проблема:

Как объединить несколько строк в один и суммировать дубликаты в Excel?

Скачать и бесплатно пробную версию Kutools для Excel сейчас!


Kutools for Excel: с более чем 200 удобными надстройками Excel, бесплатно попробовать без ограничений в 60-дни. Скачать и бесплатно пробную версию!


  • Супер Формула Бар (легко редактировать несколько строк текста и формул); Макет чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы и хранение данных; Содержание сплит-клеток; Объедините дублирующиеся строки и сумму / среднее... предотвратить повторяющиеся клетки; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выберите пустые строки (все ячейки пусты); Супер найти и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения формулы ссылки; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Любимые и быстро вставляемые формулы, Диапазоны, графики и рисунки; Шифрование ячеек с паролем; Создать список рассылки и отправлять электронные письма ...
  • Извлечение текстаДобавить текст, Удалить по позиции, Удалить пространство; Создание и печать промежуточных итогов подкачки; Преобразование содержимого ячеек и комментариев...
  • Суперфильтр (сохранить и применить схемы фильтров к другим листам); Расширенный поиск по месяцам / неделям / дням, частоте и более; Специальный фильтр жирным шрифтом, курсивом ...
  • Объединить рабочие тетради и рабочие листы; Объединение таблиц на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Работает с Office 2007-2019 и 365 и поддерживает все языки. Это легко развернуть в вашей компании. Полнофункциональная 60-дневная бесплатная пробная версия.
вкладка kte 201905
  • Включить редактирование и чтение с вкладками в Word, Excel, PowerPoint, Издатель, Доступ, Visio и Проект.
  • Открывайте и создавайте несколько документов в новых вкладках одного и того же окна, а не в новых окнах.
  • Увеличивает вашу производительность на 50% и уменьшает сотни щелчков мышью для вас каждый день!
нижняя часть офиса
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Pete · 5 months ago
    Is there a way to assign this to a button? On large data ranges it takes a while, so ideally I only want it to start the concatenate process once I've finished doing everything else in the sheet. I tried adding a trigger myself but it stopped working completely
  • To post as a guest, your comment is unpublished.
    Merijn · 5 months ago
    BTW i used the VBA solution
  • To post as a guest, your comment is unpublished.
    Merijn · 5 months ago
    Extremely helpfull! After editing it for my sheet i have #VALUE! for some of the unique values.
    I did a countif to see if it could be that there are too many names to concatenate. The two unique values that have the #VALUE! error have 13635 and 19810 results. Is there a way to overcome this?
  • To post as a guest, your comment is unpublished.
    Chantelle · 6 months ago
    How can I ignore blank cells? mine currently displays this:

    ";2503201111@msg.telus.com;;2503202222@msg.telus.com;2508193333@msg.telus.com;2503714444@msg.telus.com;;;;"

    I'd like for the 1st, 3rd and last 3 semi colons not to there/show. TIA
    • To post as a guest, your comment is unpublished.
      skyyang · 6 months ago
      Hello, Chantelle
      When concatenating the cell values ignoring the blank cells, please apply the below User Defined Function:

      Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
      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
      If ConcatenateRange.Cells(i).Value <> "" Then
      xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
      End If
      End If
      Next i
      If xResult <> "" Then
      xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
      End If
      ConcatenateIf = xResult
      Exit Function
      End Function

      Please try it, hope it can help you!
  • To post as a guest, your comment is unpublished.
    victor · 8 months ago
    thank you very much! This was so simple and helped a lot!!
  • To post as a guest, your comment is unpublished.
    David · 1 years ago
    Is it possible to replace the comma splitter with a line break, i.e. char(10)? Many thanks.
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, David,

      To combine the cells with line break, the following User Defined Function may help you.

      Function ConcatenateIf_LineBreak(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
      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 & vbCrLf & ConcatenateRange.Cells(I).Value
      End If
      Next I
      If xResult <> "" Then
      xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
      End If
      ConcatenateIf_LineBreak = xResult
      Exit Function
      End Function

      After pasting this code, then apply this formula: =ConcatenateIf_LineBreak(A2:A13,F2,B2:B13,",").

      After getting the results with this formula, you should click the Wrap Text to get the correct results you need.
  • To post as a guest, your comment is unpublished.
    Ahmed · 1 years ago
    So Easy, thank you :)
  • To post as a guest, your comment is unpublished.
    tien minh · 1 years ago
    Hi guys , I got an error #NAME? when I apply formulas CONCATENATEIF in excel file after set VBA code for this, could anyone help me to solve it , thanks som uch
  • To post as a guest, your comment is unpublished.
    krawlis · 1 years ago
    Is there a way to apply this CONCATENATEIF function in a separate sheet? It works when I put it in the same sheet as input data, but i need both tables in different sheets and it doesn't work.
    • To post as a guest, your comment is unpublished.
      Al Boulley · 1 years ago
      Yes, what you want to do is add the function to a module. Go into the VBA editor, right-click on "VBAProject" in the Project Explorer, mouse over the "Insert" menu item, and in that submenu choose "Module". Any functions you put in there will be useable on any sheet in your workbook.
  • To post as a guest, your comment is unpublished.
    MIchele · 2 years ago
    Is there a way to do this on Mac????
    It's exactly what I need - please let me know (or if any mac software would do it that you know of). Thx
  • To post as a guest, your comment is unpublished.
    DJDave · 2 years ago
    I had a problem after pasting this code into Excel 2016 - it contains non-regular spaces (perhaps non-breaking spaces?) which throw up syntax errors which are not evident no matter how closely you look because they are invisible! It is the indentation spaces that are the problem. Paste the code into Word and turn on hidden characters to see them.
    • To post as a guest, your comment is unpublished.
      Yash · 2 years ago
      Wow!! Genius! Worked like a charm! There ARE come spaces that show as a different character. Thanks a lot Dave! Wonder how you came up with the idea! Also, wonder how it works for some other peeps..Anyway, thanks again!
  • To post as a guest, your comment is unpublished.
    Yash · 2 years ago
    Hi!

    concactenateif is Exactly what I was looking for. But unfortunately can´t get it to work Always get a compile error:syntax error. Any ideas?

    In the past, with some imported VBA modules, I have noticed that I had to replace the "," by ";" as in my PC, maybe owing to my regional settings, that's the only way it works. Avidly use the built in sumifs etc. But can´t understand where am going wrong on this one.

    One more possibility that comes to mind is the fact that in office 365, "concat" replaces "concactenate". Can you help out please?

    Thanks in advance,

    Yash
    • To post as a guest, your comment is unpublished.
      DJDave · 2 years ago
      The code uses some non-breaking spaces for indentation, these trip up Excel2016. Hard to spot an invisible error..
  • To post as a guest, your comment is unpublished.
    Ram Bahadur Ale · 2 years ago
    It does not work for the big data range. I found that its working datarange is up to A2:A362. We would be grateful if you share the solution to cover the wider data range like A2:A200000 .....
    Thank you
  • To post as a guest, your comment is unpublished.
    Ram Bahadur Ale · 2 years ago
    It does not work for the big data range. I found it's working range is only up to A2:A362. We would be grateful if you share the solution for the big data range like A2:A200000 ....

    Thank you
    • To post as a guest, your comment is unpublished.
      Chris · 2 years ago
      Works great just slow. I am doing it with 27k lines of text in excel just set it off go for a brew and leave it to run
  • To post as a guest, your comment is unpublished.
    nickado · 2 years ago
    Great!!! Thank you so much!
  • To post as a guest, your comment is unpublished.
    Matt · 2 years ago
    Awesome, thank you! I used the VBA solution and it worked great.
  • To post as a guest, your comment is unpublished.
    Samrat Govekar · 3 years ago
    Extremely helpful and nicely explained
  • To post as a guest, your comment is unpublished.
    Samrat Govekar · 3 years ago
    Explained in detailed and easy to understand, really helped when i was stuck at exact same situation.
  • To post as a guest, your comment is unpublished.
    latha · 3 years ago
    Taking more time for updating the same concatenateif() formula. i have 5000 rows. and its more than 2 hrs now its still updating :(

    Any resolution to make it work fast?
  • To post as a guest, your comment is unpublished.
    Renee · 3 years ago
    I am looking for a way to use a variation of this code to create a variant list based on master variant. Using your example data, I would need to combine columns A and B into unique identifiers and then concatenate those identifiers to each row based on the value in column A, excluding the value from from the combined for that row, and the rest in alpha sort 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-James JP20150010-Brenda JP20150010-James JP20150010-Matus
    CN20150012 Andy CN20150012-Andy CN20150012-Lucy CN20150012-Monica CN20150012-Phiby
    UK20150014 Matus UK20150014-Matus JP20150010-Brenda JP20150010-James
    UK20150014 Kristi UK20150014-Kristi UK20150014-Anith UK20150014-Libin UK20150014-Peter
    JP20150010 Brenda JP20150010-Brenda JP20150010-James JP20150010-Ramon

    I have a sheet with over 1000 lines, each item comes with up to 4 variants. Trying to do this manually is impossible but I cannot find a solution that fits my needs.
  • To post as a guest, your comment is unpublished.
    Tim Blosser · 3 years ago
    This VBA code saved the day for me. Thank you!
  • To post as a guest, your comment is unpublished.
    Manoj · 3 years ago
    Will this tool be able to handle case sensitive combinations such as

    jABC 123
    abc 345
    ABc 678
    ABC 912
  • To post as a guest, your comment is unpublished.
    Enrique · 3 years ago
    Thanks for this code. It was EXACTLY what I needed. You saved me a lot of effort, thank you so much.
    • To post as a guest, your comment is unpublished.
      ConfusedNBusy · 2 years ago
      Thanks for posting this is exactly what I am looking for. I seem not to be saving the vba code correctly. I am getting an error message about ambiguous name found.

      Any suggestions or step by step on the VBA step of this project?

      Thanks
  • To post as a guest, your comment is unpublished.
    Kaladhar · 3 years ago
    This is an excellent solution (VBA code) and it addressed my requirements in minutes. I will refer your site to others and I will visit for everything that I need going forward.