Як об’єднати рядки в одну комірку на основі групи в 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.
| Kutools для Excel, з більш ніж 300 зручні функції, полегшує вам роботу. |
після установки Kutools для Excel, виконайте наведені нижче дії.（Безкоштовно завантажте Kutools для Excel зараз!)
1. Виберіть діапазон даних, який ви використовуєте, і натисніть Kutools > Злиття та розділення > Розширені комбіновані ряди.
2 В Advanced Combine Rows вікно, виберіть стовпець, на основі якого потрібно об’єднати рядки, та клацніть Primary Key щоб встановити його як ключовий стовпець.
3. Виберіть стовпець, який потрібно об’єднати, натисніть Combine, і виберіть один роздільник, який ви використовуєте для розділення поєднаного вмісту.
 |
 |
 |
4. клацання Ok. Результат відображається так:
примітки: Перш ніж застосовувати утиліту, вам краще скопіювати копію вихідних даних.
Демонстрація
Найкращі інструменти продуктивності офісу
Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу. Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...
Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу
- Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
- Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
- Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
