Cara Membandingkan Dua File Excel untuk Mencari Data yang Berbeda

Membandingkan dua laporan Excel sebaiknya dimulai dari ID transaksi, bukan posisi baris. Baris ketiga di file baru bisa berisi transaksi yang berbeda karena datanya diurutkan ulang.

Tujuan latihan ini adalah menemukan transaksi baru, transaksi yang hilang, dan perubahan nominal. Unduh laporan lama serta laporan baru. Seluruh datanya fiktif.

Siapkan kunci pencocokan

Kedua file punya sheet Data dengan kolom ID, Tanggal, Cabang, dan Total. Masing-masing berisi enam transaksi, tetapi urutan file baru sengaja berbeda.

Pastikan ID memiliki arti yang sama dan unik pada masing-masing sumber. Jika nomor pesanan hanya unik di setiap cabang, gunakan kunci gabungan cabang dan nomor. Jangan menghapus duplikat sebelum mengetahui apakah itu kiriman ulang atau dua transaksi berbeda.

Untuk mengikuti rumus berikut, salin data tanpa mengubah urutannya ke satu workbook dengan sheet Lama dan Baru, masing-masing pada A1:D7. Ini salinan kerja; kedua file unduhan tetap menjadi sumber pembanding.

Cari ID yang hilang dan baru

Pada Lama!E1, tulis Keberadaan di Baru. Di E2, masukkan lalu salin sampai E7:

=IF(COUNTIFS(Baru!$A$2:$A$7,A2)=0,"Hilang","Ada")

Pada Baru!E1, tulis Keberadaan di Lama. Di E2:

=IF(COUNTIFS(Lama!$A$2:$A$7,A2)=0,"Baru","Ada")

Rumus dipisahkan dengan koma. Jika pengaturan Excel memakai titik koma, sesuaikan pemisah argumennya. Rentang pada contoh hanya untuk enam transaksi; perluas ketika memakai data sendiri.

Bandingkan nominal untuk ID yang sama

Di Baru!F1, tulis Total lama. Untuk Excel yang mendukung XLOOKUP, masukkan di F2 dan salin ke bawah:

=XLOOKUP(A2,Lama!$A$2:$A$7,Lama!$D$2:$D$7,"Tidak ada",0)

Dokumentasi XLOOKUP Microsoft menjelaskan pencarian dan nilai jika tidak ditemukan. Fungsi ini tidak tersedia secara native di Excel 2016 dan 2019; untuk versi itu, gunakan pencarian exact match dengan INDEX/MATCH atau Power Query sesuai kebutuhan.

Tambahkan Perubahan di G1. Di G2:

=IF(E2="Baru","Transaksi baru",IF(D2=F2,"Sama","Nominal berubah"))

Pemeriksaan ini mengasumsikan ID unik. XLOOKUP yang mengambil satu kecocokan tidak cukup untuk sumber dengan beberapa baris per ID. Untuk kasus itu, tentukan dulu apakah harus dijumlahkan atau dibandingkan pada level item.

Cocokkan dengan hasil latihan

Rekonsiliasinya: Rp1.400.000 + Rp20.000 − Rp300.000 + Rp180.000 = Rp1.300.000. Jumlah baris sama-sama enam tidak berarti isi laporannya sama.

Periksa kolom lain secara terpisah

Latihan ini membandingkan keberadaan dan Total. Jika Cabang, Status, atau Tanggal juga penting, buat perbandingan per kolom. Jangan menamai hasilnya “semua data sama” ketika yang diperiksa baru nominal.

Untuk laporan dari banyak cabang, baca juga cara menggabungkan file Excel dengan aturan duplikat. Kalau perbandingan ini harus dilakukan setiap hari, kita bisa memetakan alur rekonsiliasi yang dapat diulang.

Foto utama: Anthony Choren di Unsplash, digunakan sesuai Unsplash License. Foto merupakan ilustrasi, bukan dokumentasi insiden, produk yang dibahas, atau hasil pengujian.