Cara Membandingkan Dua Daftar di Excel dan Menemukan Selisihnya

Daftar karyawan dari sistem HR dan dari penggajian jumlahnya berbeda. Pembayaran di bank tidak sama dengan berita acara. Daftar kontrak di dua departemen berselisih beberapa baris. Mencocokkan dua daftar adalah pekerjaan yang membosankan, tetapi bisa dibuat jauh lebih cepat.

Berikut metode yang bisa dipakai di Excel biasa, dari persiapan data sampai membaca hasilnya.

Langkah 1. Tentukan kunci

Kunci adalah kolom yang menandai satu catatan yang sama di kedua daftar: nomor pegawai, nomor kontrak, NPWP, atau nomor faktur. Tanpa kunci yang andal, perbandingan tidak bisa dipercaya. Nama orang sebaiknya tidak dipakai sebagai kunci tunggal karena ejaan sering berbeda.

Langkah 2. Bersihkan kunci

Sebagian besar «selisih» sebenarnya hanya perbedaan penulisan: spasi di ujung, huruf besar dan kecil, atau spasi ganda. Buat kolom bantu di kedua daftar:

=UPPER(TRIM(A2))

TRIM membuang spasi berlebih, UPPER menyamakan huruf besar dan kecil. Bila data memuat huruf beraksen, ganti dengan SUBSTITUTE. Bila kunci berupa angka yang kadang tersimpan sebagai teks, ubah dengan =VALUE(...) atau =TEXT(...).

Langkah 3. Cari apa yang ada di daftar lain

Di Daftar A, tandai tiap catatan:

=IF(COUNTIF(DaftarB!$F:$F,F2)>0,"Ada di daftar lain","Hanya di sini")

(Bila Excel Anda memakai pemisah titik koma, ganti koma dengan titik koma.) Lakukan hal yang sama di Daftar B terhadap Daftar A. Hasilnya: tiga kelompok yang jelas, cocok, hanya di A, dan hanya di B.

Langkah 4. Bandingkan nilainya

Catatan yang kuncinya ada di kedua daftar belum tentu isinya sama. Ambil nilai dari daftar lain dengan VLOOKUP lalu bandingkan:

=IFERROR(IF(VLOOKUP(F2,DaftarB!$F:$G,2,FALSE)=B2,"Sama","Berbeda"),"")

Contoh sederhana:

Perhatikan INV-002: dua angka yang hanya tertukar urutannya, kesalahan ketik yang tidak akan ketahuan bila hanya memeriksa «ada atau tidak ada».

Langkah 5. Waspadai kunci ganda

Jika satu kunci muncul dua kali, VLOOKUP hanya mengambil kecocokan pertama. Tandai dulu dengan =COUNTIF($F:$F,F2)>1 dan selesaikan pengulangan sebelum menarik kesimpulan. Pengulangan sering menjadi penyebab utama selisih jumlah.

Cara membaca hasil

Mulailah dari kelompok «hanya di sini». Biasanya ada tiga penyebab: catatan memang belum dimasukkan di sisi lain, kunci salah ketik, atau kunci memakai format berbeda (misalnya «INV-001» lawan «INV001»). Perbaiki kunci yang salah dulu, lalu hitung ulang; jumlah «hanya di sini» sering turun drastis. Setelah itu baru periksa kelompok «nilai berbeda», karena di sanalah selisih uang atau jumlah yang sebenarnya berada.

Langkah 6. Ringkas hasilnya

Hitung dengan COUNTIF: total baris, yang cocok, hanya di A, hanya di B, dan selisih nilai. Pastikan angkanya konsisten: total A = cocok + hanya di A.

Kesalahan yang sering terjadi

• Membandingkan sebelum membersihkan. Spasi tak terlihat adalah penyebab terbesar «selisih palsu».
• Menggunakan nama yang mirip sebagai kunci. «Budi S.» dan «Budi Santoso» tidak akan cocok, dan perbandingan samar rawan salah.
• Mengabaikan kunci ganda.
• Mengurutkan satu kolom saja sehingga baris tercerai-berai.

Template Excel pencocokan dua daftar

Template ini gratis. Tempel kedua daftar, dan Excel menunjukkan hasilnya. Isinya:

• Lembar «Daftar A» dan «Daftar B»: kunci dan dua nilai tambahan, hingga 5000 baris di masing-masing.
• Pembersihan kunci otomatis: spasi berlebih, huruf besar atau kecil, dan huruf É diperlakukan sebagai E.
• Hasil untuk setiap baris: «Ada di daftar lain», «Hanya di sini», atau «Ada, tetapi nilainya berbeda», beserta penanda kunci berulang.
• Lembar «Ringkasan» dengan total, cocok, hanya di A, hanya di B, selisih, dan kunci ganda.
• Lembar «Hanya di A», «Hanya di B», dan «Selisih» berisi daftar siap pakai dengan nomor baris.

Kunci dicocokkan secara penuh setelah dibersihkan; tidak ada pencarian samar. Bekerja di Excel 2007 ke atas dan LibreOffice, tanpa makro dan tautan eksternal. Cocok untuk akuntan, staf SDM, dan petugas pengendalian yang selama ini mencocokkan secara manual dengan VLOOKUP dan filter.

FAQ

Apakah bisa untuk lebih dari dua daftar?

Bandingkan berpasangan: A dengan B, lalu hasilnya dengan C. Pastikan kunci tetap sama.

Bagaimana bila tidak ada kunci unik?

Gabungkan beberapa kolom menjadi satu kunci, misalnya tanggal + jumlah + nomor referensi, dengan =A2&"|"&B2&"|"&C2.

Berapa banyak baris yang masih nyaman?

Rumus COUNTIF pada puluhan ribu baris bisa lambat. Untuk data sebesar itu, bersihkan dulu dan batasi rentang rumus.