Bagaimana membandingkan nilai yang dipisahkan koma dalam dua sel dan mengembalikan nilai duplikat atau unik di Excel?
Seperti yang ditunjukkan pada tangkapan layar di bawah, ada dua kolom - Kolom1 dan Kolom2, setiap sel dalam kolom berisi angka yang dipisahkan koma. Untuk membandingkan angka yang dipisahkan koma di Kolom1 dengan konten sel di baris yang sama di Kolom2 dan mengembalikan semua nilai duplikat atau unik, apa yang dapat Anda lakukan?
Tutorial ini menyediakan dua metode untuk membantu Anda menyelesaikan tugas ini.
Bandingkan nilai yang dipisahkan koma dalam dua sel dan kembalikan nilai duplikat atau unik dengan rumus
Bagian ini menyediakan dua rumus untuk membantu membandingkan nilai yang dipisahkan koma dalam dua sel dan mengembalikan nilai duplikat atau unik di antara keduanya.
Note: Rumus berikut hanya bekerja di Unggul untuk 365. Jika Anda menggunakan versi Excel lainnya, coba gunakan metode VBA di bawah ini.
Ambil dua kolom di atas sebagai contoh, untuk membandingkan angka yang dipisahkan koma di Kolom1 dengan angka yang dipisahkan koma di baris yang sama di Kolom2 dan mengembalikan nilai duplikat atau unik, lakukan hal berikut.
Kembalikan nilai duplikat
1. Pilih sel untuk mengeluarkan angka duplikat antara dua sel yang ditentukan dengan angka yang dipisahkan koma, dalam hal ini, saya memilih sel D2, lalu masukkan rumus di bawah ini dan tekan tombol Enter kunci. Pilih sel formula dan seret Tuas IsiOtomatis turun untuk mendapatkan nomor duplikat antar sel di baris lainnya.
=LET(x, TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ",TRUE,A2:B2), ", ")),y,UNIQUE(x),z,UNIQUE(x,,1), TEXTJOIN(", ",TRUE,IF(ISERROR(MATCH(y,z,0)),y, "")))
Kembalikan nilai unik
Untuk mengembalikan angka unik antara dua sel yang ditentukan dengan angka yang dipisahkan koma di baris yang sama, rumus berikut dapat membantu.
1. Pilih sel untuk mengeluarkan angka unik, dalam hal ini saya pilih sel E2, lalu masukkan rumus di bawah ini dan tekan tombol Enter kunci. Pilih sel formula dan seret Tuas IsiOtomatis turun untuk mendapatkan nomor unik antar sel di baris lainnya.
=TEXTJOIN(", ",TRUE,UNIQUE(TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ",TRUE,A2:B2), ", ")),,1))
Catatan:
Bandingkan dua kolom dengan nilai yang dipisahkan koma dan kembalikan nilai duplikat atau unik dengan VBA
Fungsi yang ditentukan pengguna yang disediakan di bagian ini membantu membandingkan nilai yang dipisahkan koma dalam dua sel tertentu dan mengembalikan nilai duplikat atau nilai unik di antara keduanya. Silakan lakukan sebagai berikut.
Ambil contoh yang sama seperti di atas, untuk membandingkan angka yang dipisahkan koma di Kolom1 dengan angka yang dipisahkan koma di baris yang sama di Kolom2 dan mengembalikan nilai duplikat atau unik, silakan coba fungsi yang ditentukan pengguna di bagian ini.
1. Di buku kerja pembuka, tekan lain + F11 kunci untuk membuka Microsoft Visual Basic untuk Aplikasi jendela.
2. Dalam Microsoft Visual Basic untuk Aplikasi window, klik Menyisipkan > Modul, dan salin kode VBA berikut di Modul (Kode) jendela.
Kode VBA: Bandingkan nilai yang dipisahkan koma dalam dua sel dan kembalikan nilai duplikat/unik
Private Function COMPARE(Rng1, Rng2 As Range, Op As Boolean)
'Updated by Extendoffice 20221019
Dim R1Arr As Variant
Dim R2Arr As Variant
Dim Ans1 As String
Dim Ans2 As String
Dim Separator As String
Dim d1 As New Dictionary
Dim d2 As New Dictionary
Dim d3 As New Dictionary
Application.Volatile
Separator = ", "
R1Arr = Split(Rng1.Value, Separator)
R2Arr = Split(Rng2.Value, Separator)
Ans1 = ""
Ans2 = ""
For Each ch In R2Arr
If Not d2.Exists(ch) Then
d2.Add ch, "1"
End If
Next
If Op Then
For Each ch In R1Arr
If d2.Exists(ch) Then
If Not d3.Exists(ch) Then
d3.Add ch, "1"
Ans1 = Ans1 & ch & Separator
End If
End If
Next
If Ans1 <> "" Then
Ans1 = Mid(Ans1, 1, Len(Ans1) - Len(Separator))
End If
COMPARE = Ans1
Else
For Each ch In R1Arr
If Not d1.Exists(ch) Then
d1.Add ch, "1"
End If
Next
For Each ch In R1Arr
If Not d2.Exists(ch) Then
If Not d3.Exists(ch) Then
d3.Add ch, "1"
Ans2 = Ans2 & ch & Separator
End If
End If
Next
For Each ch In R2Arr
If Not d1.Exists(ch) Then
If Not d3.Exists(ch) Then
d3.Add ch, "1"
Ans2 = Ans2 & ch & Separator
End If
End If
Next
If Ans2 <> "" Then
Ans2 = Mid(Ans2, 1, Len(Ans2) - Len(Separator))
End If
COMPARE = Ans2
End If
End Function3. Setelah menempelkan kode di Modul (Kode) jendela, klik Tools > Referensi untuk membuka Referensi - VBAProject jendela, periksa Runtime Microsoft Scripting kotak dan kemudian klik OK .
4. tekan lain + Q kunci untuk menutup Microsoft Visual Basic untuk Aplikasi jendela.
5. Sekarang Anda perlu menerapkan dua fungsi secara terpisah untuk mengembalikan nilai duplikat dan unik dari dua sel nilai yang dipisahkan koma.
Kembalikan nilai duplikat
Pilih sel untuk mengeluarkan angka duplikat, dalam contoh ini saya memilih sel D2, lalu masukkan rumus di bawah ini dan tekan tombol Enter kunci untuk mendapatkan nomor duplikat antara sel A2 dan B2.
Pilih sel formula dan seret Pegangan IsiOtomatis ke bawah untuk mendapatkan nomor duplikat di antara sel di baris lain.
=COMPARE(A2,B2,TRUE)
Kembalikan nilai unik
Pilih sel untuk menampilkan angka unik, dalam contoh ini saya memilih sel E2, lalu masukkan rumus di bawah ini dan tekan tombol Enter kunci untuk mendapatkan nomor unik antara sel A2 dan B2.
Pilih sel rumus dan seret Pegangan IsiOtomatis ke bawah untuk mendapatkan nomor unik di antara sel di baris lainnya.
=COMPARE(A2,B2,FALSE)
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!
