Как объединить строки в одну ячейку на основе группы в 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.

После установки Kutools for Excel, сделайте следующее:（Бесплатная загрузка Kutools for Excel прямо сейчас!)

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

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

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

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

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

  Демо

Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

• Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
• Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
• Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы... Предотвращение дублирования ячеек; Сравнить диапазоны...
• Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
• Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
• Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
• Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
• Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
• Более 300 мощных функций. Поддерживает Office / Excel 2007-2019 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

Больше информации... Скачать бесплатно... Покупка ... 

---

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

• Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
• Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
• Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Больше информации... Скачать бесплатно... Покупка ...