Note: The other languages of the website are Google-translated. Back to English

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?

doc menggabungkan teks berdasarkan kriteria 1

Teks gabungan berdasarkan kriteria dengan User Defined Function

Teks gabungan berdasarkan kriteria dengan Kutools for Excel


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:

doc menggabungkan teks berdasarkan kriteria 2

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:

doc menggabungkan teks berdasarkan kriteria 3

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:

doc menggabungkan teks berdasarkan kriteria 4

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:

doc menggabungkan teks berdasarkan kriteria 5

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.


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:

doc menggabungkan teks berdasarkan kriteria 7

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:

doc menggabungkan teks berdasarkan kriteria 8

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:

doc menggabungkan teks berdasarkan kriteria 9

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!


Kutools untuk Excel: dengan lebih dari 300 add-in Excel yang praktis, gratis untuk dicoba tanpa batasan dalam 30 hari. Unduh dan uji coba gratis Sekarang!

Alat Produktivitas Kantor Terbaik

Kutools for Excel Memecahkan Sebagian Besar Masalah Anda, dan Meningkatkan Produktivitas Anda hingga 80%

  • Reuse: Masukkan dengan cepat rumus, bagan yang kompleks dan apa pun yang pernah Anda gunakan sebelumnya; Enkripsi Sel dengan kata sandi; Buat Milis dan mengirim email ...
  • Super Formula Bar (dengan mudah mengedit beberapa baris teks dan rumus); Membaca Tata Letak (membaca dan mengedit sel dalam jumlah besar dengan mudah); Tempel ke Rentang yang Difilter...
  • Gabungkan Sel / Baris / Kolom tanpa kehilangan Data; Pisahkan Konten Sel; Gabungkan Baris / Kolom Duplikat... Mencegah Sel Duplikat; Bandingkan Rentang...
  • Pilih Duplikat atau Unik Baris; Pilih Baris Kosong (semua sel kosong); Temukan Super dan Temukan Fuzzy di Banyak Buku Kerja; Pilih Acak ...
  • Salinan Tepat Beberapa Sel tanpa mengubah referensi rumus; Buat Referensi Otomatis ke Beberapa Lembar; Sisipkan Poin, Kotak Centang, dan lainnya ...
  • Ekstrak Teks, Tambahkan Teks, Hapus berdasarkan Posisi, Hapus Space; Membuat dan Mencetak Subtotal Paging; Konversi Konten Antar Sel dan Komentar...
  • Filter Super (simpan dan terapkan skema filter ke sheet lain); Penyortiran Lanjutan menurut bulan / minggu / hari, frekuensi dan lainnya; Filter Khusus dengan huruf tebal, miring ...
  • Gabungkan Workbooks dan WorkSheets; Gabungkan Tabel berdasarkan kolom kunci; Pisahkan Data menjadi Beberapa Lembar; Konversi Batch xls, xlsx dan PDF...
  • Lebih dari 300 fitur canggih. Mendukung Office / Excel 2007-2019 dan 365. Mendukung semua bahasa. Penerapan yang mudah di perusahaan atau organisasi Anda. Fitur lengkap uji coba gratis 30 hari. Jaminan uang kembali 60 hari.
tab kte 201905

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.
  • Tingkatkan produktivitas Anda hingga 50%, dan kurangi ratusan klik mouse untuk Anda setiap hari!
officetab bawah
Urutkan komentar berdasarkan
komentar (38)
Belum ada peringkat. Jadilah yang pertama memberi peringkat!
Komentar ini diminimalkan oleh moderator di situs
Ini adalah solusi yang sangat baik (kode VBA) dan memenuhi kebutuhan saya dalam hitungan menit. Saya akan merujuk situs Anda kepada orang lain dan saya akan mengunjungi semua yang saya perlukan ke depannya.
Komentar ini diminimalkan oleh moderator di situs
Terima kasih untuk kode ini. Itu PERSIS apa yang saya butuhkan. Anda menyelamatkan saya banyak usaha, terima kasih banyak.
Komentar ini diminimalkan oleh moderator di situs
Terima kasih untuk posting ini adalah apa yang saya cari. Sepertinya saya tidak menyimpan kode vba dengan benar. Saya mendapatkan pesan kesalahan tentang nama ambigu yang ditemukan. Adakah saran atau langkah demi langkah tentang langkah VBA dari proyek ini? terima kasih
Komentar ini diminimalkan oleh moderator di situs
Apakah alat ini dapat menangani kombinasi case sensitive seperti jABC 123 abc 345 ABc 678 ABC 912
Komentar ini diminimalkan oleh moderator di situs
Kode VBA ini menyelamatkan saya. Terima kasih!
Komentar ini diminimalkan oleh moderator di situs
Saya mencari cara untuk menggunakan variasi kode ini untuk membuat daftar varian berdasarkan varian master. Menggunakan data contoh Anda, saya perlu menggabungkan kolom A dan B menjadi pengidentifikasi unik dan kemudian menggabungkan pengidentifikasi tersebut ke setiap baris berdasarkan nilai di kolom A, mengecualikan nilai dari dari gabungan untuk baris itu, dan sisanya dalam pengurutan alfa pesanan: Master id nama id daftar varian CN20150012 Lucy CN20150012-Lucy CN20150012-Andy CN20150012-Monica CN20150012-Phiby US20150011 Tommas US20150011-Tommas US20150011-Rose CN20150012 Monica CN20150012-Monica CN20150012 oleh CN20150012-Andy CN20150012 -Andy CN20150012-Lucy CN20150012-Monica US20150012 Rose US20150012-Rose US20150012-Tommas UK20150011 Peter UK20150011-Peter UK20150011-Anith UK20150014-Kristi UK20150014-Libin JP20150014 Ramon UKJP20150014-Ramon JP20150014-Brendath-Lines20150010 UK20150010-Brendabin JP20150010-Brenda -Kristi UK20150010-Peter UK20150014 Anith UK20150014-Anith UK20150014-Kristi UK20150014-Libin UK20150014-Peter JP20150014 James JP20150014 20150014-James JP20150014-Brenda JP20150014-James JP20150010-Matus CN20150010 Andy CN20150010-Andy CN20150010-Lucy CN20150010-Monica CN20150012-Phiby UK20150012 Matus UK20150012-Matus JP20150012-Brenda JP20150012-James20150014B Peter JP20150014 Brenda JP20150010-Brenda JP20150010-James JP20150014-Ramon Saya memiliki lembar dengan lebih dari 20150014 baris, setiap item hadir dengan hingga 20150014 varian. Mencoba melakukan ini secara manual tidak mungkin tetapi saya tidak dapat menemukan solusi yang sesuai dengan kebutuhan saya.
Komentar ini diminimalkan oleh moderator di situs
Mengambil lebih banyak waktu untuk memperbarui rumus concatenateif() yang sama. saya punya 5000 baris. dan lebih dari 2 jam sekarang masih memperbarui :( Adakah resolusi untuk membuatnya bekerja cepat?
Komentar ini diminimalkan oleh moderator di situs
Dijelaskan secara rinci dan mudah dimengerti, sangat membantu ketika saya terjebak pada situasi yang sama persis.
Komentar ini diminimalkan oleh moderator di situs
Sangat membantu dan dijelaskan dengan baik
Komentar ini diminimalkan oleh moderator di situs
Luarbiasa, terimakasih! Saya menggunakan solusi VBA dan itu bekerja dengan baik.
Komentar ini diminimalkan oleh moderator di situs
Besar!!! Terima kasih banyak!
Komentar ini diminimalkan oleh moderator di situs
Ini tidak berfungsi untuk rentang data besar. Saya menemukan rentang kerjanya hanya hingga A2:A362. Kami akan berterima kasih jika Anda membagikan solusi untuk rentang data besar seperti A2:A200000 .... Terima kasih
Komentar ini diminimalkan oleh moderator di situs
Bekerja dengan baik hanya lambat. Saya melakukannya dengan 27k baris teks di excel, matikan saja, buat minuman dan biarkan berjalan
Komentar ini diminimalkan oleh moderator di situs
Ini tidak berfungsi untuk rentang data besar. Saya menemukan bahwa rentang data kerjanya hingga A2:A362. Kami akan berterima kasih jika Anda membagikan solusi untuk mencakup rentang data yang lebih luas seperti A2:A200000 ..... Terima kasih
Komentar ini diminimalkan oleh moderator di situs
Hai! concactenateif adalah Persis apa yang saya cari. Tapi sayangnya tidak bisa bekerja Selalu mendapatkan kesalahan kompilasi: kesalahan sintaks. Ada ide? Di masa lalu, dengan beberapa modul VBA yang diimpor, saya perhatikan bahwa saya harus mengganti "," dengan ";" seperti di PC saya, mungkin karena pengaturan regional saya, itulah satu-satunya cara kerjanya. Rajin menggunakan sumif bawaan dll. Tapi tidak bisa mengerti di mana saya salah dalam hal ini. Satu lagi kemungkinan yang terlintas dalam pikiran adalah kenyataan bahwa di office 365, "concat" menggantikan "concactenate". Bisakah Anda membantu? Terima kasih sebelumnya, Yash
Komentar ini diminimalkan oleh moderator di situs
Kode menggunakan beberapa ruang non-breaking untuk indentasi, ini membuat Excel2016 tersandung. Sulit untuk menemukan kesalahan yang tidak terlihat..
Komentar ini diminimalkan oleh moderator di situs
Saya memiliki masalah setelah menempelkan kode ini ke Excel 2016 - kode ini berisi spasi yang tidak teratur (mungkin spasi yang tidak terputus?) yang memunculkan kesalahan sintaks yang tidak jelas tidak peduli seberapa dekat Anda melihat karena tidak terlihat! Ini adalah ruang lekukan yang menjadi masalah. Rekatkan kode ke Word dan aktifkan karakter tersembunyi untuk melihatnya.
Komentar ini diminimalkan oleh moderator di situs
Wow!! Jenius! Bekerja seperti pesona! Ada ruang datang yang ditampilkan sebagai karakter yang berbeda. Terima kasih banyak Dave! Bertanya-tanya bagaimana Anda datang dengan ide itu! Juga, bertanya-tanya bagaimana cara kerjanya untuk beberapa peeps lainnya.. Bagaimanapun, terima kasih lagi!
Komentar ini diminimalkan oleh moderator di situs
Apakah ada cara untuk melakukan ini di Mac????
Itu persis yang saya butuhkan - beri tahu saya (atau jika ada perangkat lunak mac yang akan melakukannya yang Anda ketahui). Terima kasih
Komentar ini diminimalkan oleh moderator di situs
Apakah ada cara untuk menerapkan fungsi CONCATENATEIF ini di lembar terpisah? Ini berfungsi ketika saya meletakkannya di lembar yang sama dengan data input, tetapi saya membutuhkan kedua tabel di lembar yang berbeda dan tidak berfungsi.
Komentar ini diminimalkan oleh moderator di situs
Ya, yang ingin Anda lakukan adalah menambahkan fungsi ke modul. Masuk ke editor VBA, klik kanan pada "VBAProject" di Project Explorer, arahkan mouse ke item menu "Insert", dan di submenu itu pilih "Modul". Fungsi apa pun yang Anda masukkan di sana akan dapat digunakan di lembar mana pun di buku kerja Anda.
Komentar ini diminimalkan oleh moderator di situs
Hai teman-teman , saya mendapatkan kesalahan #NAME? ketika saya menerapkan rumus CONCATENATEIF dalam file excel setelah mengatur kode VBA untuk ini, adakah yang bisa membantu saya untuk menyelesaikannya, terima kasih banyak
Komentar ini diminimalkan oleh moderator di situs
Sangat Mudah, terima kasih :)
Komentar ini diminimalkan oleh moderator di situs
Apakah mungkin untuk mengganti pemisah koma dengan jeda baris, yaitu char(10)? Terimakasih banyak.
Komentar ini diminimalkan oleh moderator di situs
Halo, Daud,

Untuk menggabungkan sel dengan jeda baris, Fungsi Buatan Pengguna berikut dapat membantu Anda.

Fungsi ConcatenateIf_LineBreak(CriteriaRange Sebagai Rentang, Kondisi Sebagai Varian, ConcatenateRange Sebagai Rentang, Pemisah Opsional Sebagai String = ",") Sebagai Varian
Redupkan xResult Sebagai String
On Error Resume Next
Jika CriteriaRange.Count <> ConcatenateRange.Count Kemudian
GabungkanJika = CVErr(xlErrRef)
Keluar Fungsi
End If
Untuk I = 1 Untuk CriteriaRange.Count
Jika CriteriaRange.Cells(I).Nilai = Kondisi Maka
xResult = xResult & vbCrLf & ConcatenateRange.Cells(I).Nilai
End If
Selanjutnya saya
Jika xResult <> "" Maka
xResult = VBA.Mid(xResult, VBA.Len(Pemisah) + 1)
End If
ConcatenateIf_LineBreak = xResult
Keluar Fungsi
End Function

Setelah menempelkan kode ini, lalu terapkan rumus ini: =ConcatenateIf_LineBreak(A2:A13,F2,B2:B13,",").

Setelah mendapatkan hasil dengan rumus ini, Anda harus mengklik Teks Bungkus untuk mendapatkan hasil yang benar yang Anda butuhkan.
Komentar ini diminimalkan oleh moderator di situs
terima kasih banyak! Ini sangat sederhana dan sangat membantu!!
Belum ada komentar yang diposting di sini
Muat Lebih
Tinggalkan komentar anda
Posting sebagai Tamu
×
Beri peringkat pos ini:
0   Karakter
Lokasi yang Disarankan