Как объединить уникальные значения в Excel?

Объединение уникальных значений в Excel — это распространенная задача, которая может быть выполнена различными методами. В этой статье мы рассмотрим разные техники для объединения уникальных значений в столбце и перечисления уникальных значений с одновременным объединением соответствующих им значений.

Объединить только уникальные значения из столбца

• Используя функции TEXTJOIN и UNIQUE
• Используя Kutools AI Aide
• Используя пользовательскую функцию

Перечислить уникальные значения и объединить соответствующие значения

• Используя функции TEXTJOIN и UNIQUE
• Используя Kutools for Excel
• Используя код VBA

Объединить только уникальные значения из столбца

При анализе данных иногда возникает необходимость объединить уникальные значения из столбца Excel в одну ячейку. Эта задача может быть выполнена с помощью формул, VBA или Kutools в зависимости от ваших предпочтений и сложности набора данных. Ниже мы рассмотрим оптимизированные методы для эффективного решения этой задачи.

Метод 1: Использование функций TEXTJOIN и UNIQUE

Если у вас есть Excel 365 или Excel 2021, новые функции TEXTJOIN и UNIQUE предоставляют простой и эффективный способ объединения уникальных значений из столбца.

Используйте следующую формулу в ячейке, где вы хотите получить объединенный результат:

=TEXTJOIN(", ", TRUE, UNIQUE(A2:A18))

Метод 2: Использование Kutools AI Aide

Для более быстрого и полностью автоматизированного решения Kutools для Excel предоставляет инновационный инструмент — «Kutools AI Aide». Просто опишите свою задачу, и искусственный интеллект мгновенно сгенерирует желаемый результат без каких-либо ручных настроек. Это устраняет необходимость в сложных формулах или трудоемких конфигурациях, обеспечивая точные результаты за считанные секунды.

После установки Kutools для Excel нажмите «Kutools» > «AI Aide», чтобы открыть панель «Kutools AI Aide».

1. Выберите ячейки, которые вы хотите объединить, используя только уникальные значения;
2. Введите требование в окне чата, например:
   Объединить уникальные значения через запятую из выбранного диапазона и поместить объединенный результат в ячейку C2
3. Затем нажмите клавишу Enter или кнопку «Отправить». Kutools AI проанализирует вопрос, после завершения нажмите кнопку «Выполнить», чтобы получить результат.
   

Метод 3: Использование пользовательской функции

Для продвинутых пользователей, которым требуется большая гибкость или автоматизация процесса, можно создать пользовательскую функцию VBA (UDF) для объединения уникальных значений. Этот метод идеально подходит для пользователей, знакомых с программированием, или для тех, кому нужен многоразовый инструмент.

1. Удерживайте клавиши «ALT + F11», чтобы открыть окно «Microsoft Visual Basic for Applications».

2. Нажмите «Вставка» > «Модуль» и вставьте следующий код в окно модуля.

Код VBA: Объединение уникальных значений в одну ячейку:

Function ConcatUniq(xRg As Range, xChar As String) As String
'updateby Extendoffice
    Dim xCell As Range
    Dim xDic As Object
    Set xDic = CreateObject("Scripting.Dictionary")
    For Each xCell In xRg
        xDic(xCell.Value) = Empty
    Next
    ConcatUniq = Join$(xDic.Keys, xChar)
    Set xDic = Nothing
End Function

3. Затем вернитесь к вашей таблице и введите эту формулу: =ConcatUniq (A2:A18,",") в пустую ячейку, куда вы хотите вывести объединенный результат, и нажмите клавишу Enter, чтобы получить уникальный объединенный результат, см. скриншот:

Перечислить уникальные значения и объединить соответствующие значения

В Excel перечисление уникальных значений и объединение соответствующих им значений является распространенной задачей, особенно при работе с наборами данных, требующими сводки или консолидации. Этот раздел предоставляет подробное руководство по эффективному выполнению этой задачи.

Метод 1: Использование функций TEXTJOIN и UNIQUE

Современные функции Excel, такие как UNIQUE, FILTER и TEXTJOIN, позволяют легко перечислять уникальные значения и объединять соответствующие им значения без необходимости использования сложных формул.

1. Используйте функцию UNIQUE для перечисления всех уникальных значений из столбца A:

=UNIQUE(A2:A17)

2. Примените функции TEXTJOIN и FILTER для объединения соответствующих значений для уникального значения, протяните формулу вниз, чтобы заполнить другие ячейки, см. скриншот:

=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$17, $A$2:$A$17 =D2))

Метод 2: Использование Kutools для Excel

Если у вас установлен Kutools для Excel, его мощная функция «Расширенное объединение строк» позволяет легко перечислять уникальные значения и объединять соответствующие им значения — быстро и просто, без необходимости кодирования или использования сложных формул. Просто выберите свои данные, настройте параметры, и Kutools сделает всю работу, предоставляя профессиональные результаты за считанные секунды.

1. Скопируйте и вставьте исходные данные в другую ячейку для резервного копирования данных. Выберите диапазон данных, затем нажмите «Kutools» > «Объединить и разделить» > «Расширенное объединение строк», см. скриншот:

2. В диалоговом окне «Расширенное объединение строк» настройте параметры:

• Нажмите на имя столбца, который вы хотите использовать для объединения дубликатов, а затем выберите «Основной ключ» из выпадающего списка в столбце «Операция»;
• Затем выберите имя столбца, значения которого вы хотите объединить, и выберите разделитель для разделения значений из выпадающего списка в столбце «Операция»;
• Наконец, нажмите кнопку OK.
  

Результат:

Kutools автоматизирует процесс извлечения уникальных значений и объединения соответствующих им значений с указанным разделителем.

Метод 3: Использование кода VBA

Хотя встроенные функции Excel, такие как UNIQUE и TEXTJOIN, могут справиться с этой задачей в некоторой степени, они могут оказаться недостаточными при работе со сложными сценариями или старыми версиями Excel. Независимо от того, являетесь ли вы новичком или продвинутым пользователем, это руководство проведет вас через шаги по перечислению уникальных значений и объединению соответствующих им значений с использованием кода VBA.

1. Удерживайте клавиши «ALT + F11», чтобы открыть окно «Microsoft Visual Basic for Applications».

2. Нажмите «Вставка» > «Модуль» и вставьте следующий код в окно модуля.

Код VBA: Перечислить уникальные значения и объединить соответствующие данные

Sub test()
    'updateby Extendoffice
    Dim xRg As Range
    Dim xArr As Variant
    Dim xCell As Range
    Dim xTxt As String
    Dim I As Long
    Dim xDic As Object
    Dim xOutputRg As Range
    On Error Resume Next
    xTxt = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Please select the data range", "Kutools for Excel", xTxt, , , , , 8)
    Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
    If xRg Is Nothing Then Exit Sub
    If xRg.Areas.Count > 1 Then
        MsgBox "Does not support multiple selections", , "Kutools for Excel"
        Exit Sub
    End If
    If xRg.Columns.Count <> 2 Then
        MsgBox "There must be only two columns in the selected range", , "Kutools for Excel"
        Exit Sub
    End If
    Set xOutputRg = Application.InputBox("Please select the output cell", "Kutools for Excel", Type:=8)
    If xOutputRg Is Nothing Then Exit Sub
    xArr = xRg
    Set xDic = CreateObject("Scripting.Dictionary")
    xDic.CompareMode = 1
    For I = 1 To UBound(xArr)
        If Not xDic.Exists(xArr(I, 1)) Then
            xDic.Item(xArr(I, 1)) = xDic.Count + 1
            xArr(xDic.Count, 1) = xArr(I, 1)
            xArr(xDic.Count, 2) = xArr(I, 2)
        Else
            xArr(xDic.Item(xArr(I, 1)), 2) = xArr(xDic.Item(xArr(I, 1)), 2) & "," & xArr(I, 2)
        End If
    Next
    xOutputRg.Resize(xDic.Count, 2).Value = xArr
End Sub

3. Затем нажмите клавишу F5 для запуска этого кода, и появится всплывающее окно, напоминающее вам выбрать диапазон данных, который вы хотите объединить на основе уникальных значений, см. скриншот:

4. Нажмите OK, появится еще одно всплывающее окно, напоминающее вам выбрать ячейки для размещения результата:

5. Нажмите OK, уникальные значения будут извлечены, а соответствующие им значения в другом столбце будут объединены вместе. См. скриншот:

Подводя итог, объединение уникальных значений в Excel может быть эффективно достигнуто несколькими методами, адаптированными для разных уровней знаний и потребностей. Выбрав метод, наиболее подходящий для ваших требований, вы сможете упростить обработку данных и повысить эффективность управления уникальными значениями в Excel. Если вас интересуют дополнительные советы и приемы работы с Excel, наш сайт предлагает тысячи учебных материалов.