Rumus excel mencari persamaan kata

Tutorial ini membahas tentang cara membandingkan dua kolom di Excel, yang merupakan pekerjaan Excel normal dalam pekerjaan kita sehari-hari. Membandingkan dua kolom dapat dilakukan dengan cara yang berbeda di 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 skenario yang paling mungkin dari membandingkan dua kolom Anda mungkin bertemu, dan berharap dapat membantu Anda.

Tutorial Ini Meliputi

1. Bandingkan dua kolom baris demi baris 1.1 Bandingkan sel di baris yang sama untuk pencocokan persis 1.2 Membandingkan sel dalam baris yang sama untuk pencocokan tepat atau tidak peka huruf besar / kecil (menggunakan rumus IF) 1.3 Bandingkan sel di baris yang sama dan sorot data yang cocok atau tidak cocok (menggunakan Format Bersyarat) 1.4 Bandingkan dua kolom baris demi baris dan sorot nilai ketidakcocokan (menggunakan VBA)

2．Bandingkan dua kolom dalam sel dan pilih atau sorot data duplikat atau unik 2.1 Membandingkan dua kolom sel dengan sel dan menampilkan membandingkan hasil di kolom lain (menggunakan rumus) 2.2 Membandingkan dan menyorot data duplikat atau unik (menggunakan Pemformatan Bersyarat) 2.3 Membandingkan dan memilih atau menyorot data duplikat atau unik (menggunakan alat praktis) 2.4 Bandingkan dua kolom dan buat daftar duplikat persis di kolom lain (menggunakan kode VBA) 2.5 Bandingkan dua kolom dan sorot duplikat (menggunakan kode VBA)

3．Bandingkan beberapa kolom dalam baris yang sama 3.1 Menemukan kecocokan penuh di semua sel di baris yang sama (menggunakan rumus IF) 3.2 Temukan yang cocok dalam dua sel dalam baris yang sama (menggunakan rumus IF) 3.3 Temukan kecocokan penuh di semua sel di baris yang sama dan sorot (menggunakan Conditional Formatting) 3.4 Membandingkan beberapa kolom dan menyoroti perbedaan baris

4. Bandingkan dua kolom dan temukan titik data yang hilang 4.1 Bandingkan dan temukan titik data yang hilang (menggunakan rumus VLOOKUP atau MATCH) 4.2 Bandingkan dua kolom dan daftar data yang hilang di bawah ini (menggunakan rumus INDEX) 4.3 Bandingkan dan temukan poin data yang hilang dan daftar di kolom lain (menggunakan alat praktis) 4.4 Bandingkan dua kolom dan buat daftar data yang hilang di bawah ini (menggunakan VBA)

5． Bandingkan dua kolom (tanggal) jika lebih dari atau kurang dari 5.1 Membandingkan dua kolom jika lebih besar dari atau kurang dari (menggunakan rumus) 5.2 Membandingkan dua kolom jika lebih besar dari atau kurang dari maka format (menggunakan Conditional Formatting)

6． Bandingkan dua kolom dan kembalikan nilai di kolom lain 6.1 Tarik data yang cocok persis (menggunakan rumus atau alat praktis) 6.2 Tarik data pencocokan parsial (menggunakan rumus)

7. Bandingkan dua kolom dan hitung kecocokan atau perbedaan 7.1 Bandingkan dua kolom dan hitung yang cocok (menggunakan rumus SUMPRODUCT) 7.2 Membandingkan dua kolom dan menghitung kecocokan atau perbedaan (menggunakan alat praktis)

8. Bandingkan dua kolom dengan wildcard

9. Hapus perbedaan atau kecocokan

10. Bandingkan dua kolom dan hitung persentase perubahan di antaranya

11. Bandingkan dua rentang 11.1 Bandingkan dua rentang dengan sel 11.2 Bandingkan dua rentang jika data dalam urutan yang sama

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.

1. Bandingkan dua kolom baris demi baris

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:

= B2 = C2

Tekan Enter dan seret gagang isian ke sel D8. Jika rumus mengembalikan TRUE, nilai dua kolom sama sekali, jika mengembalikan FALSE, keduanya berbeda.

1.2 Membandingkan sel dalam baris yang sama untuk pencocokan tepat atau tidak peka huruf besar / kecil (menggunakan rumus IF)

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:

=IF(EXACT(B2,C2),"Match","Mismatch")

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:

=IF(B2=C2,"Match","Mismatch")

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.

1.3 Bandingkan sel di baris yang sama dan sorot data yang cocok atau tidak cocok (menggunakan Format Bersyarat)

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:

2． Bandingkan dua kolom dalam sel dan pilih atau sorot data duplikat atau unik

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

2.1 Membandingkan dua kolom sel dengan sel dan menampilkan membandingkan hasil di kolom lain (menggunakan rumus)

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.

=IF(COUNTIF($C$2:$C$8, $B2)=0, "No in C", "Yes in 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)

The Format Bersyarat Fitur di Excel sangat kuat, di sini Anda bisa menggunakannya untuk membandingkan dua kolom sel dengan sel dan kemudian menyoroti perbedaan atau kecocokan yang Anda butuhkan.

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 untuk 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 serta 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:

3． Bandingkan beberapa kolom di baris yang sama

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.

=IF(AND(B2=C2, B2=D2), "Full match", "Not")

 

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:

=IF(COUNTIF($B2:$D2, $B2)=3, "Full match", "Not")

 

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.

=IF(OR(B2=C2, C2=D2, B2=D2), "Match", "Not")

 

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.

=IF(COUNTIF(C11:E11,B11)+COUNTIF(D11:E11,C11)+(D11=E11)=0,"Not","Match")

 

3.3 Temukan kecocokan penuh di semua sel di baris yang sama dan sorot (menggunakan Conditional Formatting)

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.

=AND($B2=$C2, $B2=$D2)

Or

=COUNTIF($B2:$D2, $B2)=3

 

Note: Jika jumlah kolom lebih dari tiga, misalkan 5, rumus harus diubah menjadi:

=AND($B2=$C2, $B2=$D2, $B2=$E2, $B2=$F2)

Or

=COUNTIF($B2:$F2, $B2)=5

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 menyoroti perbedaan baris, yang berarti membandingkan sel kolom satu per satu, dan menemukan sel yang berbeda sesuai dengan kolom pertama, Anda dapat menggunakan fitur bawaan Excel-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.

4. Bandingkan dua kolom dan temukan titik data yang hilang

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:

=ISERROR(VLOOKUP(B2,$C$2:$C$10,1,0))

Or

=NOT(ISNUMBER(MATCH(B2,$C$2:$C$10,0)))

 

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:

=INDEX($B$2:$B$10,MATCH(TRUE,ISNA(MATCH($B$2:$B$10,$C$2:C6,0)),0))

 

Tekan Shift + Ctrl + Enter 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 untuk 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.

5． Bandingkan dua kolom (tanggal) jika lebih dari atau kurang dari

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.

=IF(B2>C2,"Yes","No")

 

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 deret angka, sebenarnya 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.

5.2 Membandingkan dua kolom jika lebih besar dari atau kurang dari maka format (menggunakan Conditional Formatting)

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.

6． Bandingkan dua kolom dan kembalikan nilai di kolom lain

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:

=VLOOKUP(E2,$B$2:$C$8,2,0)

Or

=INDEX($B$2:$C$8,MATCH(E2,$B$2:$B$8,0),2)

 

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 untuk Excel, yang berisi beberapa rumus untuk menyelesaikan sebagian besar masalah di Excel. Dengannya, Anda hanya perlu memilih rentang tetapi tidak perlu mengingat cara rumus digunakan.

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:

=VLOOKUP("*"&E2&"*",$B$2:$C$8,2,0)

Or

=INDEX($B$2:$C$8,MATCH("*"&E2&"*",$B$2:$B$8,0),2)

 

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.

7． Bandingkan dua kolom dan hitung kecocokan atau perbedaan

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.

=SUMPRODUCT(--(ISNUMBER(MATCH(B2:B8,C2:C8,0))))

 

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 serta 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

8． Bandingkan dua kolom dengan karakter pengganti

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.

=SUM(COUNTIF(B2,"*" & $D$2:$D$3 & "*"))

 

Tekan Shift + Ctrl + Enter 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

=SUMPRODUCT(COUNTIF(B2,"*" &$D$2:$D$3& "*"))

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.

9. Hapus perbedaan atau kecocokan

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:

=IF(ISERROR(MATCH(B2,$D$2:$D$8,0)),"Difference","Match")

Tekan Enter kunci, dan seret gagang pengisian otomatis ke sel D8.

Kemudian terapkan SARING fitur di Excel untuk menyaring perbedaan atau kecocokan nilai.

Pilih kolom rumus, lalu klik Data > SARING.

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 > SARING sekali lagi untuk menghapus filter.

Hapus kolom rumus jika Anda tidak membutuhkannya lagi.

10. Bandingkan dua kolom dan hitung persentase perubahan di antaranya

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.

=(C2-B2)/B2

 

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.

11. Bandingkan dua rentang

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 untuk 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 dari Kutools for Excel dapat membantu Anda.

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 serta 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.

Alat Produktivitas Kantor Terbaik

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

• 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 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 kata sandi; 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...

Office Tab - menghadirkan 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!

Rumus match Excel untuk apa?

Rumus VLOOKUP dan hlookup untuk apa?

Bagaimana cara mencari data yang sama di Excel?

Rumus Hlookup untuk apa?