Як об’єднати рядки в одну комірку на основі групи в Excel?

Ось діапазон із двох стовпців, один — це список класів, а інший — список імен студентів. Як бачите, деякі учні навчаються в одному класі, а деякі – ні. Я хочу об’єднати учнів одного класу в одну клітинку, як показано на знімку екрана нижче. Як я можу швидко впоратися з цим у Excel?

Групуйте та об’єднуйте за допомогою формул та функції фільтра

Групуйте та об’єднуйте за допомогою коду VBA

Групуйте та об’єднуйте за допомогою розширених комбінованих рядків

Групуйте та об’єднуйте за допомогою формул та функції фільтра

В 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, тепер результат показано нижче:

Групуйте та об’єднуйте за допомогою розширених комбінованих рядків

Ось утиліта в Kutools для Excel, Advanced Combine ряди, який може поєднувати рядки або робити обчислення на основі стовпця ключа в Excel.

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

2 В Advanced Combine Rows вікно, виберіть стовпець, на основі якого потрібно об’єднати рядки, та клацніть Primary Key щоб встановити його як ключовий стовпець.

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

4. клацання Ok. Результат відображається так:

Зауважте: Перед застосуванням утиліти рекомендується створити копію вихідних даних.

Демонстрація: групуйте та об’єднуйте за допомогою Advanced Combine Rows Kutools for Excel