Bagaimana cara menggabungkan baris menjadi satu sel berdasarkan grup di Excel?
Berikut adalah rentang dua kolom, satu daftar kelas, dan yang lainnya adalah daftar nama siswa. Seperti yang Anda lihat, beberapa siswa berada di kelas yang sama, beberapa tidak. Sekarang saya ingin menggabungkan siswa di kelas yang sama ke dalam satu sel seperti gambar di bawah ini, bagaimana saya bisa cepat menanganinya di Excel?
Kelompokkan dan gabungkan dengan rumus dan fungsi Filter
Kelompokkan dan gabungkan dengan kode VBA
Kelompokkan dan gabungkan dengan Advanced Combine Rows
Kelompokkan dan gabungkan dengan rumus dan fungsi Filter
Di Excel, Anda bisa menerapkan rumus ke baris gabungan berdasarkan satu kolom, lalu gunakan fungsi Filter untuk menampilkan hasilnya saja.
Note: Anda perlu menyortir data Anda berdasarkan kelas sebelum mengikuti langkah-langkah.
1. Dalam sel kosong di sebelah rentang data, misalnya, C13, ketikkan rumus ini =IF(A13=A12,C12&", "&B13,B13), tekan Enter kunci dan isi rumus ke sel dengan menyeret gagang isian.
Dalam rumusnya, A13 adalah data pertama di kolom "Kelas", B13 adalah data pertama di kolom "Nama", "," adalah pemisah untuk membatasi konten gabungan.
2. Kemudian di kolom berikutnya, D13, ketikkan rumus ini =IF(A13<>A14,"Last","") , dan seret gagang isian ke bawah untuk menerapkan rumus ke sel yang Anda butuhkan.
3. Sekarang pilih semua rentang data termasuk rumus dan klik Data > Filter untuk menambahkan Filter icons ke data.
4. Klik Filter icon di header rumus terakhir, centang Last checkbox hanya dari daftar drop-down, dan klik OK.
Sekarang hasilnya seperti di bawah ini, Anda dapat menghapus kolom helper terakhir jika Anda tidak membutuhkannya.
Kelompokkan dan gabungkan dengan kode VBA
Berikut adalah kode VBA yang juga bisa menangani pekerjaan ini.
1. tekan Alt + F11 kunci untuk mengaktifkan Microsoft Visual Basic for Applications jendela.
2. Kemudian di jendela, klik Tools > References untuk mengaktifkan References dialog, dan periksa Microsoft Scripting Runtime. Lihat tangkapan layar:
3. klik OK, Dan klik Insert > Module di jendela VBA, dan salin dan tempel kode VBA di bawah ini ke file Module naskah. Lihat tangkapan layar:
VBA: Menggabungkan baris menjadi satu sel berdasarkan grup
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. tekan F5 kunci, dan pilih rentang data yang Anda gunakan dalam dialog yang muncul.
5. klik OK untuk memilih kolom kunci yang ingin Anda kelompokkan berdasarkan.
6. klik OK, sekarang hasilnya seperti di bawah ini:
Kelompokkan dan gabungkan dengan Advanced Combine Rows
Ini adalah utilitas di dalamnya Kutools untuk Excel, Advanced Combine Baris, yang bisa menggabungkan baris atau melakukan perhitungan berdasarkan kolom kunci di Excel.
Kutools untuk Excel, dengan lebih dari 300 fungsi praktis, membuat pekerjaan Anda lebih mudah. |
Setelah menginstal Kutools for Excel, lakukan seperti di bawah ini:锛圲nduh Gratis Kutools untuk Excel Sekarang!)
1. Pilih rentang data yang Anda gunakan dan klik Kutools > Gabungkan & Pisahkan > Lanjutan Gabungkan Baris.
2. Dalam Advanced Combine Rows jendela, pilih kolom yang ingin Anda gabungkan berdasarkan baris, dan klik Primary Key untuk mengaturnya sebagai kolom kunci.
3. Pilih kolom yang ingin Anda gabungkan, klik Combine, dan pilih satu pembatas yang Anda gunakan untuk memisahkan konten gabungan.
4. klik Ok. Hasilnya ditampilkan seperti ini:
Note: Sebelum menerapkan utilitas, Anda sebaiknya memiliki salinan data asli.
Demo
Alat Produktivitas Kantor Terbaik
Tingkatkan Keterampilan Excel Anda dengan Kutools for Excel, dan Rasakan Efisiensi yang Belum Pernah Ada Sebelumnya. Kutools for Excel Menawarkan Lebih dari 300 Fitur Lanjutan untuk Meningkatkan Produktivitas dan Menghemat Waktu. Klik Di Sini untuk Mendapatkan Fitur yang Paling Anda Butuhkan...
Tab Office Membawa antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Jauh Lebih Mudah
- Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
- Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
- Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!