Bandingkan dua kolom untuk kecocokan dan perbedaan Excel
Tutorial ini membahas tentang cara membandingkan dua kolom Excel, itu normal Excel pekerjaan dalam pekerjaan kita sehari-hari. Membandingkan dua kolom dapat dilakukan dengan cara berbeda Excel, seperti membandingkan dua kolom baris demi baris atau sel demi sel, membandingkan dua kolom untuk menyorot kecocokan atau perbedaan, dll. Di sini artikel ini mencakup sebagian besar skenario yang mungkin terjadi dalam membandingkan dua kolom yang mungkin Anda temui, dan semoga dapat membantu Anda.
Note
Dalam tutorial ini, ini memberikan beberapa contoh data untuk lebih menjelaskan metode dalam membandingkan dua kolom dalam berbagai kasus. Berdasarkan kumpulan data Anda, Anda mungkin perlu mengubah atau menyesuaikan beberapa konten (referensi), namun prinsip dasarnya akan tetap sama. Atau langsung unduh sampel dari setiap kasus jika Anda hanya ingin memverifikasi apakah metode tersebut berfungsi atau tidak.
Di bawah ini adalah kumpulan data di mana saya perlu memeriksa di baris yang sama apakah nama di kolom A sama dengan yang ada di kolom B atau tidak.
Klik untuk mendownload file contoh
1.1 Bandingkan sel di baris yang sama untuk pencocokan persis
Secara umum, jika Anda ingin membandingkan dua kolom baris demi baris untuk pencocokan persis, Anda dapat menggunakan rumus di bawah ini:
Tekan Enter dan seret gagang isian ke sel D8. Jika rumus mengembalikan TRUE, nilai dua kolom sama sekali, jika mengembalikan FALSE, keduanya berbeda.
Jika Anda ingin membandingkan dua kolom baris demi baris untuk case insensitive, atau mendapatkan lebih banyak deskripsi seperti Match, Mismatch, Anda bisa menggunakan fungsi IF.
Membandingkan sel di baris yang sama agar sama persis
Jika Anda ingin menggunakan teks "Match" dan "Mismatch" untuk menjelaskan hasil perbandingan, gunakan rumus di bawah ini:
Tekan Enter untuk mendapatkan hasil pertama, lalu seret gagang pengisian otomatis ke sel D8.
Membandingkan sel di baris yang sama untuk pencocokan tidak peka huruf besar / kecil
Jika Anda ingin membandingkan sel untuk case insensitive, Anda dapat menggunakan rumus di bawah ini:
Tekan Enter untuk mendapatkan hasil pertama, lalu seret gagang pengisian otomatis ke sel E8.
Ucapan
Dalam rumus di atas, Anda dapat mengubah teks "Cocok" dan "Tidak Cocok" ke deskripsi Anda sendiri.
Jika Anda ingin menyorot nilai yang cocok atau berbeda, file Format Bersyarat fitur dapat membantu Anda.
1. Pilih dua kolom yang akan dibandingkan dengan (B2: C8, tidak termasuk tajuk kolom), lalu klik Beranda > Format Bersyarat > Aturan baru.
2. Dalam bermunculan Aturan Pemformatan Baru dialog, klik untuk memilih Gunakan rumus untuk menentukan sel mana yang akan diformat dalam Pilih Jenis Aturan bagian, lalu ketik = $ B2 = $ C2 ke dalam kotak teks Memformat nilai yang rumus ini benar.
3. Sekarang klik dibentuk untuk menampilkan Format Cells dialog, lalu di bawah Mengisi tab, pilih satu warna yang Anda butuhkan untuk menyorot pertandingan.
Atau Anda bisa mengubah ukuran font, ukuran font, batas sel, atau format angka agar lebih menonjol dari kecocokan seperti yang Anda perlukan di tab lain.
4. klik OK > OK untuk menutup dialog, maka sel di baris yang sama akan disorot jika sama.
Jika Anda ingin menyorot nilai ketidakcocokan, Anda dapat menggunakan ini di = $ B2 <> $ C2 ke dalam Memformat nilai yang rumus ini benar kotak teks di Edit Aturan Pemformatan dialog.
Kemudian perbedaan dua kolom pada baris yang sama akan disorot dengan warna tertentu.
1.4 Bandingkan dua kolom baris demi baris dan sorot nilai ketidakcocokan (menggunakan VBA)
Jika Anda ingin membandingkan dua kolom baris demi baris dengan kode VBA, tutorial ini memuaskan Anda.
1. Aktifkan lembar kerja yang berisi dua kolom yang digunakan untuk membandingkan, tekan Alt + F11 kunci untuk mengaktifkan Microsoft Visual Basic untuk Aplikasi jendela.
2. Pada dialog popping, klik Menyisipkan > Modul.
3. Kemudian salin dan tempel makro di bawah ini dalam skrip modul baru.
VBA: Bandingkan dua kolom baris demi baris dan sorot perbedaannya
Sub ExtendOffice_HighlightColumnDifferences()
'UpdatebyKutools20201016
Dim xRg As Range
Dim xWs As Worksheet
Dim xFI As Integer
On Error Resume Next
SRg:
Set xRg = Application.InputBox("Select two columns:", "Kutools for Excel", , , , , , 8)
If xRg Is Nothing Then Exit Sub
If xRg.Columns.Count <> 2 Then
MsgBox "Please select two columns"
GoTo SRg
End If
Set xWs = xRg.Worksheet
For xFI = 1 To xRg.Rows.Count
If Not StrComp(xRg.Cells(xFI, 1), xRg.Cells(xFI, 2), vbBinaryCompare) = 0 Then
xWs.Range(xRg.Cells(xFI, 1), xRg.Cells(xFI, 2)).Interior.ColorIndex = 7 'you can change the color index as you need.
End If
Next xFI
End Sub
4. tekan F5 kunci untuk menjalankan kode, lalu sebuah dialog muncul untuk memilih dua kolom.
5. klik OK. Kemudian perbedaan dua kolom telah disorot dengan warna latar belakang.
Ucapan
Anda dapat mengubah warna sorotan berdasarkan kebutuhan Anda sendiri dengan mengubah indeks warna dalam kode, referensi indeks Warna:
Di bagian ini, kumpulan data diperlihatkan seperti di bawah ini, dan Anda ingin menemukan semua nilai yang ada di kolom B dan kolom C secara bersamaan, atau, temukan nilai hanya di kolom B.
Klik untuk mendownload file contoh
Di sini Anda dapat menggunakan rumus yang digabungkan dengan fungsi IF dan COUNTIF untuk membandingkan dua kolom dan menemukan nilai yang ada di kolom B tetapi tidak di kolom C.
Tekan Enter kunci dan seret tuas IsiOtomatis ke sel D8.
Ucapan
1. Formula ini membandingkan dua kolom tanpa case sensitive.
2. Anda dapat mengubah deskripsi "Tidak di C" dan "Ya di C" ke orang lain.
3. Jika Anda ingin membandingkan dua kolom secara keseluruhan, ubah kisaran tetap $ C $ 2: $ C $ 8 menjadi $ C: $ C.
2.2 Membandingkan dan menyorot data duplikat atau unik (menggunakan Pemformatan Bersyarat)
Grafik Format Bersyarat fitur di Excel sangat kuat, di sini Anda dapat menggunakannya untuk membandingkan dua kolom sel demi sel dan kemudian menyorot perbedaan atau kecocokan sesuai kebutuhan.
Sorot semua duplikat atau nilai unik dalam dua kolom
1. Pilih dua kolom yang akan Anda bandingkan, lalu klik Beranda > Format Bersyarat > Sorot Aturan Sel > Nilai duplikat.
2. Dalam bermunculan Nilai duplikat dialog, pilih format penyorotan yang Anda perlukan dari daftar drop-down nilai dengan.
3. klik OK. Kemudian duplikat dalam dua kolom telah disorot.
Ucapan
Jika Anda ingin menyoroti nilai unik (perbedaan) dalam dua kolom, klik juga Beranda > Format Bersyarat > Sorot Aturan Sel > Nilai duplikat untuk menampilkan Nilai duplikat dialog, ubah Duplikat untuk Khas Papua di daftar drop-down kiri, lalu pilih format lain dari nilai dengan daftar drop-down, klik OK.
Nilai unik akan disorot.
Temukan dan sorot nilai di kolom B jika juga ada di kolom C.
Jika Anda ingin menyoroti nilai di kolom B yang juga ada di kolom C, CPemformatan onditional fitur juga dapat membantu Anda.
1. Pilih kolom B2: B8, klik Beranda > Format Bersyarat > Aturan baru.
2. Dalam Aturan Pemformatan Baru dialog, pilih Gunakan rumus untuk menentukan sel mana yang akan diformat dari Pilih Jenis Aturan bagian, lalu ketik = COUNTIF ($ C $ 2: $ C $ 8, $ B2)> 0 ke dalam kotak teks Memformat nilai yang rumus ini benar.
3. klik dibentuk untuk pergi ke Format Cells dialog, di bawah Mengisi tab, pilih satu warna untuk menyorot pertandingan.
Anda dapat menggunakan format lain untuk menyorot nilai di tab Font, Number, Border.
4. klik OK > OK. Kemudian nilai di kolom B yang juga ada di kolom C telah disorot dengan warna yang ditentukan.
Jika Anda ingin menyorot nilai yang hanya ada di kolom B tapi tidak di kolom C, ulangi langkah di atas, tapi ubah rumus di langkah 2 menjadi = COUNTIF ($ C $ 2: $ C $ 8, $ B2) = 0 dalam Aturan Pemformatan Baru dialog.
Kemudian pilih warna lain untuk memformat nilainya.
Ucapan
Di sini Conditional Formatting membandingkan dua kolom tanpa case insensitive.
2.3 Membandingkan dan memilih atau menyorot data duplikat atau unik (menggunakan alat praktis)
Terkadang, setelah membandingkan dua kolom, Anda dapat mengambil tindakan lain pada kecocokan atau perbedaan, seperti pemilihan, penghapusan, salin, dan sebagainya. Dalam hal ini, alat praktis - Pilih Sel yang Sama & Berbeda of Kutools for Excel dapat langsung memilih pertandingan atau perbedaan untuk melakukan operasi selanjutnya dengan lebih baik, juga dapat langsung menyorot nilai.
Setelah pemasangan gratis Kutools for Excel, lakukan seperti di bawah ini:
1. klik Kutools > Pilih > Pilih Sel yang Sama & Berbeda.
2. Kemudian di Pilih Sel yang Sama & Berbeda dialog, lakukan seperti di bawah ini:
1) Di Temukan nilai di dan Menurut bagian, pilih dua kolom yang ingin Anda bandingkan;
2) Pilih Setiap baris pilihan;
3) Pilih Nilai yang sama or Nilai yang Berbeda seperti yang Anda butuhkan;
4) Jika Anda ingin menyorot nilainya, pilih warna yang Anda butuhkan.
3. klik Ok, sebuah dialog muncul untuk mengingatkan Anda jumlah nilai yang telah ditemukan, klik OK untuk menutup dialog. Dan pada saat yang sama, nilai telah dipilih, sekarang Anda dapat menghapus atau menyalin atau melakukan operasi lain.
Jika Anda memeriksa Isi warna latar dan Isi warna font kotak centang, hasilnya ditampilkan seperti ini:
Ucapan
Jika Anda ingin membandingkan dengan case sensitive, centang Hal - hal sensitif .
Alat ini mendukung untuk membandingkan dua kolom di lembar kerja yang berbeda.
2.4 Bandingkan dua kolom dan buat daftar duplikat persis di kolom lain (menggunakan kode VBA)
Jika Anda ingin mencantumkan nilai yang cocok di kolom lain setelah membandingkan dua kolom sel demi sel, di sini kode makro di bawah ini dapat membantu Anda.
1. Aktifkan lembar yang ingin Anda bandingkan dua kolom, lalu tekan Alt + F11 tombol untuk menampilkan Microsoft Visual Basic untuk Aplikasi jendela.
2. klik Menyisipkan > Modul dalam Microsoft Visual Basic untuk Aplikasi jendela.
3. Kemudian salin dan tempel kode di bawah ini ke skrip modul kosong baru.
VBA: Buat daftar duplikat di kolom samping setelah membandingkan dua kolom
Sub ExtendOffice_FindMatches()
'UpdatebyKutools20201019
Dim xRg, xRgC1, xRgC2, xRgF1, xRgF2 As Range
Dim xIntSR, xIntER, xIntSC, xIntEC As Integer
On Error Resume Next
SRg:
Set xRgC1 = Application.InputBox("Select first column:", "Kutools for Excel", , , , , , 8)
If xRgC1 Is Nothing Then Exit Sub
If xRgC1.Columns.Count <> 1 Then
MsgBox "Please select single column"
GoTo SRg
End If
SsRg:
Set xRgC2 = Application.InputBox("Select the second column:", "Kutools for Excel", , , , , , 8)
If xRgC2 Is Nothing Then Exit Sub
If xRgC2.Columns.Count <> 1 Then
MsgBox "Please select single column"
GoTo SsRg
End If
Set xWs = xRg.Worksheet
For Each xRgF1 In xRgC1
For Each xRgF2 In xRgC2
If xRgF1.Value = xRgF2.Value Then xRgF2.Offset(0, 1) = xRgF1.Value
Next xRgF2
Next xRgF1
End Sub
4. tekan F5 kunci untuk menjalankan kode, ada dua dialog yang muncul satu per satu bagi Anda untuk memilih dua kolom secara terpisah.
Note: Silakan pilih kolom kiri terlebih dahulu, lalu pilih kolom kanan di dialog kedua, atau duplikat akan menggantikan data asli di kolom kedua.
5. klik OK > OK, maka pertandingan telah otomatis terdaftar di kolom kanan dari dua kolom.
Ucapan
Kode VBA membandingkan dua kolom dengan case sensitive.
2.5 Bandingkan dua kolom dan sorot duplikat (menggunakan kode VBA)
Jika Anda ingin membandingkan dua kolom sel demi sel dan kemudian menyorot kecocokan, Anda dapat mencoba kode di bawah ini.
1. Aktifkan lembar yang ingin Anda bandingkan dua kolom, lalu tekan Alt + F11 tombol untuk menampilkan Microsoft Visual Basic untuk Aplikasi jendela.
2. klik Menyisipkan > Modul dalam Microsoft Visual Basic untuk Aplikasi jendela.
3. Salin kode makro di bawah ini dan tempel di tempat kosong baru Modul Script.
VBA: Bandingkan dua kolom dan sorot duplikat
Sub ExtendOffice_CompareTwoRanges()
'UpdatebyKutools20201019
Dim xRg, xRgC1, xRgC2, xRgF1, xRgF2 As Range
SRg:
Set xRgC1 = Application.InputBox("Select the column you want compare according to", "Kutools for Excel", , , , , , 8)
If xRgC1 Is Nothing Then Exit Sub
If xRgC1.Columns.Count <> 1 Then
MsgBox "Please select a single column"
GoTo SRg
End If
SsRg:
Set xRgC2 = Application.InputBox("Select the column you want to highlight duplicates in:", "Kutools for Excel", , , , , , 8)
If xRgC2 Is Nothing Then Exit Sub
If xRgC2.Columns.Count <> 1 Then
MsgBox "Please select a single column"
GoTo SsRg
End If
For Each xRgF1 In xRgC1
For Each xRgF2 In xRgC2
If xRgF1.Value = xRgF2.Value Then
xRgF2.Interior.ColorIndex = 38 '(you can change the color index as you need)
End If
Next
Next
End Sub
4. tekan F5 kunci untuk menjalankan kode. Pada dialog pertama yang muncul, pilih kolom yang ingin Anda bandingkan nilai duplikatnya.
5. klik OK. Pada dialog kedua, pilih kolom tempat Anda ingin menyorot nilai duplikat.
6. klik OK. Sekarang duplikat di kolom kedua telah disorot dengan warna latar belakang setelah dibandingkan dengan kolom pertama.
Ucapan
1. Kode membandingkan kolom dengan case sensitive.
2. Anda dapat mengubah warna sorotan berdasarkan kebutuhan Anda sendiri dengan mengubah indeks warna dalam kode, referensi indeks Warna:
Terkadang, Anda mungkin ingin membandingkan lebih dari dua kolom di baris yang sama, seperti kumpulan data seperti gambar di bawah ini. Di sini, di bagian ini, ini mencantumkan metode berbeda untuk membandingkan beberapa kolom.
Klik untuk mendownload file contoh
3.1 Menemukan kecocokan penuh di semua sel di baris yang sama (menggunakan rumus IF)
Untuk menemukan kecocokan penuh di seluruh kolom dalam baris yang sama, rumus IF di bawah ini dapat membantu Anda.
Jika sel di baris yang sama cocok satu sama lain, "Pertandingan penuh" ditampilkan, atau "Tidak" ditampilkan.
Tekan Enter kunci untuk mendapatkan hasil perbandingan pertama, lalu seret pegangan isi otomatis ke sel E7.
Ucapan
1. Rumus membandingkan kolom tanpa case sensitive.
2. Jika Anda ingin membandingkan lebih atau sama dengan tiga kolom, Anda dapat menggunakan rumus di bawah ini:
Dalam rumusnya, 3 adalah jumlah kolom, Anda dapat mengubahnya untuk memenuhi kebutuhan Anda.
3.2 Temukan yang cocok dalam dua sel dalam baris yang sama (menggunakan rumus IF)
Terkadang, Anda ingin mengetahui apakah ada dua kolom di baris yang sama yang cocok, Anda bisa menggunakan rumus IF di bawah ini.
Dalam rumus ini, Anda perlu membandingkan sepasang sel di baris yang sama. "Cocok" menunjukkan bahwa ada dua sel yang cocok, jika tidak ada sel yang cocok, rumus menampilkan "Tidak", Anda dapat mengubah teks sesuai kebutuhan.
Tekan Enter kunci, dan seret gagang isian ke sel E7.
Ucapan
1. Formula ini tidak mendukung case insensitive.
2. Jika ada banyak kolom yang perlu dibandingkan, untuk membandingkan salah satu pasangan sel dalam rumus mungkin terlalu panjang. Dalam kasus ini, Anda dapat mempertimbangkan untuk menggunakan rumus di bawah ini yang menggabungkan fungsi IF dan COUNTIF.
Jika Anda ingin menyorot baris yang dicocokkan oleh semua sel, Anda dapat menggunakan Format Bersyarat fitur di Excel.
1. Pilih rentang yang Anda gunakan, lalu klik Beranda > Format Bersyarat > Aturan baru.
2. Dalam Aturan Pemformatan Baru dialog, pilih Gunakan rumus untuk menentukan sel mana yang akan diformat dari Pilih Jenis Aturan bagian, maka Anda dapat menggunakan salah satu rumus di bawah ini di Memformat nilai yang rumus ini benar kolom tulisan.
Or
Note: Jika jumlah kolom lebih dari tiga, misalkan 5, rumus harus diubah menjadi:
Or
3. klik dibentuk tombol untuk masuk ke dialog Format Sel, lalu pilih satu warna isian atau pemformatan sel lainnya agar lebih menonjol dari baris.
4. klik OK > OK, sekarang hanya baris yang semua selnya cocok yang akan disorot.
Ucapan
Rumus di atas tidak mendukung case sensitive.
3.4 Membandingkan beberapa kolom dan menyoroti perbedaan baris
Jika Anda ingin menyorot perbedaan baris, yang berarti membandingkan sel kolom satu per satu, dan menemukan sel yang berbeda sesuai dengan kolom pertama, Anda dapat menggunakan Excel fitur bawaan-Pergi ke khusus.
1. Pilih rentang yang ingin Anda sorot perbedaan barisnya, dan klik Beranda > Temukan & Pilih > Pergi ke khusus.
2. Dalam bermunculan Pergi ke khusus dialog, periksa Perbedaan baris .
3. klik OK. Sekarang perbedaan baris telah dipilih.
4. Sekarang biarkan sel tetap dipilih, klik Beranda > Isi Warna untuk memilih satu warna dari menu tarik-turun.
Ucapan
Metode ini membandingkan sel tanpa case sensitive.
Misalkan ada dua kolom, kolom B lebih panjang, dan kolom C lebih pendek seperti gambar di bawah ini. Dibandingkan dengan kolom B, bagaimana cara mengetahui data yang hilang di kolom C?
Klik untuk mendownload file contoh
4.1 Bandingkan dan temukan titik data yang hilang (menggunakan rumus VLOOKUP atau MATCH)
Jika Anda hanya ingin mengidentifikasi data mana yang hilang setelah membandingkan dua kolom, Anda dapat menggunakan salah satu rumus di bawah ini:
Or
Tekan Enter kunci, lalu seret gagang pengisian otomatis ke sel D10. Sekarang jika data berada di kolom B dan kolom C, rumus mengembalikan FALSE, jika data hanya di kolom B tetapi meleset di kolom C, rumus mengembalikan TRUE.
Ucapan
Di atas dua rumus membandingkan data tanpa peka huruf besar / kecil.
4.2 Bandingkan dua kolom dan daftar data yang hilang di bawah ini (menggunakan rumus INDEX)
Jika Anda ingin mencantumkan data yang hilang di bawah kolom yang lebih pendek setelah membandingkan dua kolom, rumus array INDEX dapat membantu Anda.
Di sel di bawah dari kolom yang lebih pendek, misalkan sel C7, ketikkan rumus di bawah ini:
Tekan Shift + Ctrl + Masuk untuk mendapatkan data pertama yang hilang, lalu seret gagang pengisian otomatis ke bawah hingga mengembalikan nilai kesalahan # N / A.
Kemudian Anda dapat menghapus nilai kesalahan, dan semua data yang hilang telah dicantumkan di bawah kolom yang lebih pendek.
Ucapan
Rumus ini membandingkan sel tanpa peka huruf besar / kecil.
4.3 Bandingkan dan temukan poin data yang hilang dan daftar di kolom lain (menggunakan alat praktis)
Jika Anda ingin melakukan operasi tindak lanjut pada data yang hilang setelah membandingkan dua kolom, seperti membuat daftar data yang hilang di kolom lain atau melengkapi data yang hilang di bawah kolom yang lebih pendek, Anda dapat mencoba alat praktis-Pilih Sel yang Sama & Berbeda of Kutools for Excel.
1. klik Kutools > Pilih > Pilih Sel yang Sama & Berbeda.
2. Dalam Pilih Sel yang Sama & Berbeda dialog, lakukan seperti di bawah ini:
1) Masuk Temukan nilai dalam bagian, pilih kolom yang lebih panjang yang berisi daftar lengkap.
In Menurut bagian, pilih kolom yang lebih pendek yang melewatkan beberapa data.
2) Pilih Setiap baris .
3) Pilih Nilai yang Berbeda .
3. klik Ok, sebuah dialog muncul untuk mengingatkan Anda jumlah data yang hilang, klik OK untuk menutupnya. Kemudian data yang hilang telah dipilih.
Sekarang Anda bisa menekan Ctrl + C untuk menyalin data hilang yang dipilih, dan menempelkannya dengan menekan Ctrl + V kunci di bawah kolom yang lebih pendek atau kolom baru lainnya yang Anda butuhkan.
Ucapan
Mencentang Peka huruf besar / kecil Opsi dalam dialog Select Same & Different Cells akan membandingkan dua kolom dengan case sensitive.
4.4 Bandingkan dua kolom dan buat daftar data yang hilang di bawah ini (menggunakan VBA)
Berikut adalah kode makro yang dapat mengisi data yang hilang di bawah kedua kolom tersebut.
1. Buka lembar yang Anda gunakan, tekan Alt + F11 kunci untuk membuka Microsoft Visual Basic untuk Aplikasi jendela.
2. klik Menyisipkan > Modul untuk membuat modul baru untuk ditempatkan di bawah kode VBA.
VBA: Bandingkan dua kolom dan isi data yang hilang
Sub ExtendOffice_PullUniques()
'UpdatebyKutools20201021
Dim xRg, xRgC1, xRgC2, xFRg1, xFRg2 As Range
Dim xIntR, xIntSR, xIntER, xIntSC, xIntEC As Integer
Dim xWs As Worksheet
On Error Resume Next
SRg:
Set xRg = Application.InputBox("Select two columns:", "Kutools for Excel", , , , , , 8)
If xRg Is Nothing Then Exit Sub
If xRg.Columns.Count <> 2 Then
MsgBox "Please select two columns as a range"
GoTo SRg
End If
Set xWs = xRg.Worksheet
xIntSC = xRg.Column
xIntEC = xRg.Columns.Count + xIntSC - 1
xIntSR = xRg.Row
xIntER = xRg.Rows.Count + xIntSR - 1
Set xRg = xRg.Columns
Set xRgC1 = xWs.Range(xWs.Cells(xIntSR, xIntSC), xWs.Cells(xIntER, xIntSC))
Set xRgC2 = xWs.Range(xWs.Cells(xIntSR, xIntEC), xWs.Cells(xIntER, xIntEC))
xIntR = 1
For Each xFRg In xRgC1
If WorksheetFunction.CountIf(xRgC2, xFRg.Value) = 0 Then
xWs.Cells(xIntER, xIntEC).Offset(xIntR) = xFRg
xIntR = xIntR + 1
End If
Next
xIntR = 1
For Each xFRg In xRgC2
If WorksheetFunction.CountIf(xRgC1, xFRg) = 0 Then
xWs.Cells(xIntER, xIntSC).Offset(xIntR) = xFRg
xIntR = xIntR + 1
End If
Next
End Sub
3. Kemudian tekan F5 kunci untuk menjalankan kode, dialog muncul untuk memilih dua kolom pembanding.
4. klik OK. Sekarang data yang hilang telah terdaftar di bawah dua kolom.
Ucapan
Kode membandingkan sel tanpa peka huruf besar / kecil.
Jika ada dua kolom tanggal seperti gambar di bawah ini, Anda mungkin ingin membandingkan tanggal mana yang kemudian di baris yang sama.
Klik untuk mendownload file contoh
5.1 Membandingkan dua kolom jika lebih besar dari atau kurang dari (menggunakan rumus)
Anda dapat menggunakan rumus sederhana untuk menemukan dengan cepat apakah tanggal 1 lebih lama dari tanggal 2 di setiap baris.
Tekan Enter kunci untuk mendapatkan hasil perbandingan pertama, lalu seret gagang isian otomatis ke sel C6 untuk mendapatkan semua hasil.
Ucapan
1. di Excel, tanggal disimpan sebagai rangkaian angka, sebenarnya itu adalah angka. Oleh karena itu, Anda menerapkan rumus untuk membandingkan tanggal secara langsung.
2. Jika Anda ingin membandingkan jika tanggal 1 lebih awal dari tanggal 2 di setiap baris, ubah simbolnya > untuk < dalam rumus.
Jika Anda ingin menyorot sel di kolom Tanggal 1 jika lebih besar dari Tanggal 2, Anda dapat menggunakan Format Bersyarat fitur di Excel.
1. Pilih tanggal di kolom B (Tanggal1), lalu klik Beranda > Format Bersyarat > Aturan baru.
2. Dalam Aturan Pemformatan Baru dialog, pilih Gunakan rumus untuk menentukan sel mana yang akan diformat dalam Pilih Jenis Aturan bagian, lalu ketik rumus = $ B2> $ C2 ke dalam kotak teks Memformat nilai yang rumus ini benar.
Jika Anda ingin menyorot sel di kolom B yang lebih kecil dari yang ada di kolom C, gunakan rumus = $ B2 <$ C2.
3. klik dibentuk tombol untuk membuka Format Cells dialog, lalu pilih jenis format yang Anda butuhkan.
4. klik OK > OK. Kemudian sel di kolom Date1 yang lebih besar dari yang ada di kolom Date2 telah disorot.
Misalnya, ada dua tabel, sekarang Anda ingin membandingkan kolom B dan kolom E, lalu cari harga relatif dari kolom C dan kembalikan di kolom F.
Klik untuk mendownload file contoh
6.1 Tarik data yang cocok persis (menggunakan rumus atau alat praktis)
Di sini ia memperkenalkan beberapa rumus dan alat yang berguna untuk menyelesaikan pekerjaan ini.
Metode rumus
Di sel F2 (sel tempat Anda ingin meletakkan nilai yang dikembalikan), gunakan salah satu rumus di bawah ini:
Or
Tekan Enter kunci, dan nilai pertama telah ditemukan. Kemudian seret pegangan pengisian otomatis ke sel F6, semua nilai telah diekstraksi.
Ucapan
1. Rumus tidak mendukung case sensitive.
2. Angka 2 dalam rumus menunjukkan bahwa Anda menemukan nilai yang cocok di kolom kedua dari larik tabel.
3. Jika rumus tidak dapat menemukan nilai relatif, ia mengembalikan nilai kesalahan # N / A.
Metode alat praktis
Jika Anda bingung dengan rumus, Anda dapat mencoba alat praktis - Pembantu Formula of Kutools for Excel, yang berisi beberapa rumus untuk menyelesaikan sebagian besar masalah Excel. Dengannya, Anda hanya perlu memilih rentang tetapi tidak perlu mengingat cara penggunaan rumus.
Setelah pemasangan gratis Kutools for Excel, lakukan seperti di bawah ini:
1. Pilih sel F2 (sel yang ingin Anda tempatkan nilai pengembaliannya), dan klik Kutools > Pembantu Formula > Pencarian & Referensi > Cari nilai dalam daftar.
2. Dalam Rumus Pembantu dialog, di kanan Masukan argumen bagian, pilih rentang larik tabel, nilai pencarian dan tentukan kolom mana yang ingin Anda cari nilai yang dikembalikan.
jenis: Ingatlah untuk mengubah referensi absolut di Nilai lookup kotak ke relatif, atau Anda hanya dapat menemukan satu nilai pertama.
3. klik Ok, nilai pertama telah ditemukan, lalu seret gagang pengisian otomatis ke sel F6.
6.2 Tarik data pencocokan parsial (menggunakan rumus)
Jika ada beberapa perbedaan kecil antara dua kolom yang dibandingkan seperti gambar di bawah ini, metode di atas tidak dapat berfungsi.
Pilih salah satu rumus di bawah ini untuk menyelesaikan pekerjaan ini:
Or
Tekan Enter kunci, lalu seret pegangan pengisian otomatis ke sel F5, semua nilai telah ditemukan.
Ucapan
1. Rumus tidak mendukung case sensitive.
2. Angka 2 dalam rumus menunjukkan bahwa Anda menemukan nilai yang dikembalikan di kolom kedua dari larik tabel.
3. Jika rumus tidak dapat menemukan nilai relatif, ia mengembalikan nilai kesalahan # N / A.
4. * dalam rumus adalah wildcard yang digunakan untuk menunjukkan karakter atau string apa pun.
Kumpulan data di bawah ini adalah contoh untuk membandingkan dan menghitung kecocokan atau perbedaan.
Klik untuk mendownload file contoh
7.1 Bandingkan dua kolom dan hitung yang cocok (menggunakan rumus SUMPRODUCT)
Rumus SUMPRODUCT bisa dengan cepat menghitung kecocokan dalam dua kolom.
Tekan Enter kunci untuk mendapatkan hasil.
Ucapan
Rumus menghitung sel tanpa peka huruf besar / kecil.
7.2 Membandingkan dua kolom dan menghitung kecocokan atau perbedaan (menggunakan alat praktis)
Jika Anda ingin menghitung kecocokan atau perbedaan antara dua kolom, alat praktis - Pilih Sel yang Sama & Berbeda.
Setelah pemasangan gratis Kutools for Excel, lakukan seperti di bawah ini:
1. klik Kutools > Pilih > Pilih Sel yang Sama & Berbeda.
2. Dalam Pilih Sel yang Sama & Perbedaan dialog, pilih dua rentang kolom di Temukan nilai dalam dan Menurut bagian secara terpisah, lalu pilih Setiap baris, dan pilih Nilai yang sama or Nilai yang Berbeda pilihan yang Anda butuhkan.
3. klik Ok. Sebuah dialog muncul untuk memberi tahu Anda berapa banyak sel yang cocok atau berbeda yang dipilih.
Sel yang cocok
Sel yang berbeda
Misalkan di sini adalah daftar data di kolom B, dan Anda ingin menghitung sel yang berisi "Apple" atau "Candy" di kolom D seperti gambar di bawah ini yang ditampilkan:
Klik untuk mendownload file contoh
Untuk menghitung apakah sel berisi satu atau beberapa nilai, Anda bisa menggunakan rumus dengan wildcard untuk memecahkan masalah ini.
Tekan Shift + Ctrl + Masuk kunci untuk mendapatkan pemeriksaan pertama, lalu seret pegangan isi otomatis ke sel F8.
Sekarang jika sel terkait berisi satu atau beberapa nilai di kolom D, hasilnya menampilkan angka yang lebih besar dari 0, jika tidak berisi nilai apa pun di kolom D, hasilnya 0.
Jika Anda ingin menghitung jumlah total sel yang berisi nilai di kolom D, gunakan rumus di bawah sel F8:
Ucapan
1. Anda juga bisa menggunakan rumus untuk menghitung jika sel berisi nilai di kolom lain
Rumus ini hanya perlu ditekan Enter kunci dan kemudian seret gagang pengisian otomatis.
2. Dalam rumus, * adalah karakter pengganti yang menunjukkan karakter atau string apa pun.
Jika Anda ingin menghapus perbedaan atau kecocokan setelah membandingkan dua kolom seperti gambar di bawah ini yang ditampilkan:
Klik untuk mendownload file contoh
Anda dapat menggunakan rumus untuk menemukan perbedaan atau kecocokan terlebih dahulu:
Tekan Enter kunci, dan seret gagang pengisian otomatis ke sel D8.
Kemudian terapkan Filter fitur di Excel untuk menyaring perbedaan atau kecocokan nilai.
Pilih kolom rumus, lalu klik Data > Filter.
Kemudian tombol filter muncul di kolom C, klik di tombol filter untuk memperluas menu drop-down, pilih Perbedaan or Cocok sesuai kebutuhan. Lalu klik OK menyelesaikan.
Sekarang hanya perbedaan atau kecocokan yang telah disaring. Anda dapat memilihnya dan menekan Delete kunci untuk menghapusnya.
Sekarang klik Data > Filter sekali lagi untuk menghapus filter.
Hapus kolom rumus jika Anda tidak membutuhkannya lagi.
Ada dua kolom, satu berisi harga asli, dan kolom lainnya berisi harga jual. Sekarang bagian ini memperkenalkan rumus untuk membandingkan dua kolom ini dan kemudian menghitung persentase perubahan antara dua kolom tersebut.
Klik untuk mendownload file contoh
Anda dapat menggunakan rumus di bawah ini untuk mengetahui persentase perubahan antara dua harga di baris yang sama.
Tekan Enter untuk mendapatkan nomor, lalu seret gagang pengisian otomatis ke sel D7.
Kemudian format hasil rumus sebagai persentase. Pilih hasilnya, klik Beranda tab dan pergi ke Jumlah grup untuk diklik Gaya Persen.
Hasil rumus telah diformat sebagai persentase.
Sekarang Anda tahu cara membandingkan dua kolom setelah membaca metode di atas. Namun, dalam beberapa kasus, Anda mungkin ingin membandingkan dua rentang (dua seri dengan beberapa kolom) Anda dapat menggunakan metode di atas (rumus atau pemformatan bersyarat) untuk membandingkannya kolom demi kolom, tetapi di sini memperkenalkan alat praktis - Kutools for Excel dapat menyelesaikan pekerjaan ini dalam berbagai kasus dengan cepat menggunakan formula gratis.
Klik untuk mendownload file contoh
11.1 Bandingkan dua rentang dengan sel
Berikut adalah dua rentang yang perlu dibandingkan dengan sel, Anda dapat menggunakan Pilih Sel yang Sama & Berbeda kegunaan Kutools for Excel untuk menanganinya.
Setelah pemasangan gratis Kutools for Excel, lakukan seperti di bawah ini:
1. klik Kutools > Pilih > Pilih Sel yang Sama & Berbeda.
2. Dalam bermunculan Pilih Sel yang Sama & Berbeda dialog, lakukan seperti di bawah ini:
1) Di Temukan nilai dalam bagian, pilih rentang yang ingin Anda temukan kecocokan atau perbedaannya setelah membandingkan dua rentang.
2) Di Menurut bagian, pilih rentang lain yang digunakan untuk membandingkan rentang.
3) Masuk Berdasarkan bagian, pilih Sel tunggal.
4) Kemudian di Menemukan bagian, pilih tipe sel yang ingin Anda pilih atau sorot.
5) Di Memproses hasil bagian, Anda dapat menyorot sel dengan mengisi warna latar belakang atau warna font, jika Anda tidak perlu menyorot, jangan centang kotak.
3. klik Ok. Sebuah dialog muncul dan mengingatkan berapa banyak sel / baris yang telah dipilih, klik OK untuk menutupnya. Sekarang sel yang berbeda dengan yang ada di rentang lainnya telah dipilih dan disorot.
Menyoroti nilai yang sama
Ucapan
Jika Anda ingin membandingkan dua rentang dengan baris, Anda juga dapat menerapkan Pilih Sel yang Sama & Berbeda fitur, tetapi dalam kasus ini, pilih Setiap baris .
11.2 Bandingkan dua rentang jika data dalam urutan yang sama
Jika Anda ingin membandingkan dua rentang dengan baris, Bandingkan fitur Sel of Kutools for Excel bisa membantumu.
Setelah pemasangan gratis Kutools for Excel, lakukan seperti di bawah ini:
Misalkan, rentang F2: H7 adalah model, sekarang Anda ingin mengetahui apakah data dalam rentang B2: D7 dalam urutan yang benar sesuai dengan rentang F2: H7.
1. klik Kutools > Bandingkan Sel.
2. Dalam Bandingkan Sel dialog, atur seperti di bawah ini:
1) Pilih dua rentang ke dalam Temukan nilai dalam dan Menurut kotak secara terpisah.
2) Pilih jenis sel yang ingin Anda sorot di Menemukan bagian.
3) Pilih jenis penyorotan di Memproses hasil bagian.
3. klik Ok. Sebuah dialog muncul dan mengingatkan berapa banyak sel yang telah dipilih, klik OK untuk menutupnya. Sekarang sel yang berbeda dengan yang ada di rentang lainnya telah dipilih dan disorot.
Anda mungkin juga tertarik |
|
Bandingkan Tanggal Jika Lebih Besar Dari Tanggal Lain Di Excel Bandingkan Dua Lembar Berdampingan Vlookup Untuk Membandingkan Dua Daftar Di Lembar Kerja Terpisah Temukan, Sorot, Filter, Hitung, Hapus Duplikat Dalam Excel |
Alat Produktivitas Kantor Terbaik
Kutools for Excel Memecahkan Sebagian Besar Masalah Anda, dan Meningkatkan Produktivitas Anda hingga 80%
- Bilah Formula Super (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 dan Menyimpan Data; Pisahkan Konten Sel; Gabungkan Baris Duplikat dan Jumlah / Rata-rata... 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 ...
- Favorit dan Sisipkan Rumus dengan Cepat, Rentang, Grafik dan Gambar; Enkripsi Sel dengan izinword; Buat Milis dan mengirim email ...
- 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...
- Pengelompokan Tabel Pivot menurut nomor minggu, hari dalam seminggu dan banyak lagi ... Tampilkan Sel yang Tidak Terkunci dan Terkunci dengan warna berbeda; Sorot Sel Yang Memiliki Rumus / Nama...
- Aktifkan pengeditan tab dan pembacaan Word, Excel, Power Point, 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!
