Bagaimana dengan mudah menggabungkan teks berdasarkan kriteria di Excel?
Misalkan saya memiliki kolom nomor ID yang berisi beberapa duplikat dan kolom nama, dan sekarang, saya ingin menggabungkan nama-nama berdasarkan nomor ID unik seperti yang ditunjukkan screenshot kiri, untuk dengan cepat menggabungkan teks berdasarkan kriteria, bagaimana kita bisa lakukan di Excel?
Teks gabungan berdasarkan kriteria dengan User Defined Function
Teks gabungan berdasarkan kriteria dengan Kutools for Excel
Teks gabungan berdasarkan kriteria dengan User Defined Function
Untuk menggabungkan teks dengan nomor ID unik, Anda dapat mengekstrak nilai unik terlebih dahulu, lalu membuat Fungsi Buatan Pengguna untuk menggabungkan nama berdasarkan ID unik.
1. Ambil data berikut sebagai contoh, Anda perlu mengekstrak nomor ID unik terlebih dahulu, silakan gunakan rumus array ini: =IFERROR(INDEX($A$2:$A$15, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$15), 0)),"")， Masukkan rumus ini ke dalam sel kosong, D2 misalnya, lalu tekan Ctrl + Shift + Enter kunci bersama, lihat tangkapan layar:
jenis: Dalam rumus di atas, A2: A15 adalah rentang data daftar yang ingin Anda ekstrak nilai uniknya, D1 adalah sel pertama dari kolom yang ingin Anda masukkan hasil ekstraksi.
2. Dan kemudian seret gagang isian ke bawah untuk mengekstrak semua nilai unik sampai kosong ditampilkan, lihat tangkapan layar:
3. Pada langkah ini, Anda harus membuat file Fungsi Buatan Pengguna untuk menggabungkan nama berdasarkan nomor ID unik, harap tahan ALT + F11 kunci, dan itu membuka Microsoft Visual Basic untuk Aplikasi jendela.
4. Klik Menyisipkan > Modul, dan tempel kode berikut di Modul Jendela.
Kode VBA: menggabungkan teks berdasarkan kriteria
Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Updateby Extendoffice
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
ConcatenateIf = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To CriteriaRange.Count
If CriteriaRange.Cells(i).Value = Condition Then
xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
End If
Next i
If xResult <> "" Then
xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function
5. Kemudian simpan dan tutup kode ini, kembali ke lembar kerja Anda, dan masukkan rumus ini ke dalam sel E2, = CONCATENATEIF ($ A $ 2: $ A $ 15, D2, $ B $ 2: $ B $ 15, ",") , lihat tangkapan layar:
6. Kemudian seret gagang isian ke sel yang ingin Anda terapkan rumus ini, dan semua nama yang sesuai telah digabungkan berdasarkan nomor ID, lihat tangkapan layar:
Tip:
1. Dalam rumus di atas, A2: A15 adalah data asli yang ingin Anda gabungkan berdasarkan, D2 adalah nilai unik yang telah Anda ekstrak, dan B2: B15 adalah kolom nama yang ingin Anda gabungkan.
2. Seperti yang Anda lihat, saya menggabungkan nilai yang dipisahkan dengan koma, Anda dapat menggunakan karakter lain dengan mengubah koma "," dari rumus yang Anda butuhkan.
Teks gabungan berdasarkan kriteria dengan Kutools for Excel
Jika Anda memiliki Kutools untuk Excel, Dengan yang Lanjutan Gabungkan Baris utilitas, Anda dapat dengan cepat dan mudah menggabungkan teks berdasarkan kriteria.
| Kutools untuk Excel : dengan lebih dari 300 add-in Excel yang praktis, gratis untuk dicoba tanpa batasan dalam 30 hari. |
Setelah menginstal Kutools untuk Excel, lakukan sebagai langkah berikut:
1. Pilih rentang data yang ingin Anda gabungkan berdasarkan satu kolom.
2. Klik Kutools > Gabungkan & Pisahkan > Lanjutan Gabungkan Baris, lihat tangkapan layar:
3. di Gabungkan Baris Berdasarkan Kolom kotak dialog, klik kolom ID dan kemudian klik Kunci utama untuk menjadikan kolom ini sebagai kolom kunci yang menjadi dasar gabungan data Anda, lihat tangkapan layar:
4. Dan kemudian klik Nama kolom yang ingin Anda gabungkan nilainya, lalu klik Menggabungkan opsi, dan pilih satu pemisah untuk data gabungan, lihat tangkapan layar:
5. Setelah menyelesaikan pengaturan ini, klik OK untuk keluar dari dialog, dan data di kolom B telah digabungkan bersama berdasarkan kolom kunci A. Lihat tangkapan layar:
Dengan fitur ini, masalah berikut akan diselesaikan secepatnya:
Bagaimana cara menggabungkan beberapa baris menjadi satu dan menjumlahkan duplikat di Excel?
Unduh dan uji coba gratis Kutools untuk Excel Sekarang!
Demo: Gabungkan teks berdasarkan sel yang sama di kolom lain dengan Kutools for Excel
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!
