Как объединить строки в одну ячейку на основе группы в Excel?

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

Группируйте и объединяйте с помощью формул и функции фильтрации

Группировать и объединять с кодом VBA

Группируйте и объединяйте с помощью Advanced Combine Rows

Группируйте и объединяйте с помощью формул и функции фильтрации

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

Внимание: Вам необходимо отсортировать данные по классу перед выполнением следующих шагов.

1. В пустой ячейке рядом с диапазоном данных, например C13, введите эту формулу. =IF(A13=A12,C12&", "&B13,B13), нажмите Enter и заполните формулой ячейки с перетаскиванием маркера заполнения.

В формуле A13 - это первые данные в столбце «Класс», B13 - это первые данные в столбце «Имя», «,» - это разделитель для разделения содержимого конкатенации.

2. Затем в следующем столбце D13 введите эту формулу =IF(A13<>A14,"Last","") , и перетащите маркер заполнения вниз, чтобы применить формулу к нужным ячейкам.

3. Теперь выберите весь диапазон данных, включая формулы, и нажмите Данные > Filter добавить Filter icons к данным.

4. Нажмите Filter icon в последнем заголовке формулы проверьте Last флажок только из раскрывающегося списка и нажмите OK.

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

Группировать и объединять с кодом VBA

Вот код VBA, который также может справиться с этой задачей.

1. Нажмите Alt + F11 ключи для включения Microsoft Visual Basic for Applications окно.

2. Затем в окне нажмите Tools > References включить References диалог и проверьте Microsoft Scripting Runtime. Смотрите скриншот:

3. Нажмите OKи нажмите Insert > Module в окне VBA, скопируйте и вставьте ниже код VBA в Module сценарий. Смотрите скриншот:

VBA: объединить строки в одну ячейку на основе группы

Sub ConcatenateCellsIfSameValues()
'UpdatebyExtendoffice20180201
    Dim I As Long
    Dim J As Long
    Dim xRg As Range
    Dim xRgKey As Range
    Dim xRgVal As Range
    Dim xStr As String
    Dim xDic As New Dictionary
    On Error Resume Next
    Set xRg = Application.InputBox("Select data range", "KuTools for Excel", Selection.Address, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    Set xRgKey = Application.InputBox("Select key column", "KuTools for Excel", xRg.Columns(1).Address, , , , , 8)
    If xRgKey Is Nothing Then
        MsgBox "Key column cannot be empty", vbInformation, "KuTools for Excel"
    End If
    Set xRgVal = xRg(1).Offset(, 1).Resize(xRg.Rows.Count, xRg.Columns.Count - 1)
    For I = 1 To xRgKey.Count
        If I > xRgKey.Count Then Exit For
        xStr = ""
        For J = 1 To xRgVal.Columns.Count
            xStr = xStr & " " & xRgVal(I, J)
        Next
        If xDic.Exists(xRgKey(I).Text) Then
            xDic(xRgKey(I).Text) = xDic(xRgKey(I).Text) & xStr
            xRgKey(I).EntireRow.Delete
            I = I - 1
       Else
            xDic.Add xRgKey(I).Text, xStr
        End If
    Next
    For I = 1 To xRgVal.Count
        xRgVal(I).Value = xDic(xRgKey(I).Text)
    Next
End Sub

4. Нажмите F5 и выберите диапазон данных, который вы используете в открывшемся диалоговом окне.

5. Нажмите OK , чтобы выбрать ключевой столбец, по которому вы хотите сгруппировать.

6. Нажмите OK, теперь результат показан ниже:

Группируйте и объединяйте с помощью Advanced Combine Rows

Вот утилита в Kutools for Excel, Advanced Combine Ряды, который может объединять строки или выполнять вычисления на основе ключевого столбца в Excel.

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

2. в Advanced Combine Rows в окне выберите столбец, на основе которого вы хотите объединить строки, и нажмите Primary Key чтобы установить его как ключевой столбец.

3. Выберите столбец, который нужно объединить, нажмите Combineи выберите один разделитель, который вы используете для разделения объединенного содержимого.

4. Нажмите Ok. Результат показан так:

Внимание: Перед применением утилиты рекомендуется создать копию исходных данных.

Демонстрация: группировка и объединение с помощью Advanced Combine Rows в Kutools для Excel