Salah satu dari banyak kemampuan Microsoft Excel adalah kemampuan untuk membandingkan dua daftar data, mengidentifikasi kecocokan antara daftar dan mengidentifikasi item mana yang ditemukan hanya dalam satu daftar. Ini berguna ketika membandingkan catatan keuangan atau memeriksa untuk melihat apakah nama tertentu ada dalam database. Anda dapat menggunakan fungsi MATCH untuk mengidentifikasi dan menandai catatan yang cocok atau tidak cocok, atau Anda dapat menggunakan pemformatan pengkondisian dengan fungsi COUNTIF. Langkah-langkah berikut memberi tahu Anda cara menggunakan masing-masing untuk mencocokkan data Anda.
Langkah
Metode 1 dari 2: Mengidentifikasi Catatan Dengan Fungsi MATCH
Langkah 1. Salin daftar data ke satu lembar kerja
Excel bisa bekerja dengan beberapa lembar kerja dalam satu buku kerja, atau dengan beberapa buku kerja, tetapi Anda akan menemukan membandingkan daftar lebih mudah jika Anda menyalin informasinya ke satu lembar kerja.
Langkah 2. Berikan setiap item daftar pengenal unik
Jika dua daftar Anda tidak memiliki cara yang sama untuk mengidentifikasinya, Anda mungkin perlu menambahkan kolom tambahan ke setiap daftar data yang mengidentifikasi item tersebut ke Excel sehingga dapat melihat apakah item dalam daftar tertentu terkait dengan item dalam daftar lainnya. Sifat pengenal ini akan bergantung pada jenis data yang Anda coba cocokkan. Anda akan memerlukan pengenal untuk setiap daftar kolom.
- Untuk data keuangan yang terkait dengan periode tertentu, seperti catatan pajak, ini bisa berupa deskripsi aset, tanggal perolehan aset, atau keduanya. Dalam beberapa kasus, entri dapat diidentifikasi dengan nomor kode; namun, jika sistem yang sama tidak digunakan untuk kedua daftar, pengenal ini dapat membuat kecocokan di mana tidak ada atau mengabaikan kecocokan yang harus dibuat.
- Dalam beberapa kasus, Anda dapat mengambil item dari satu daftar dan menggabungkannya dengan item dari daftar lain untuk membuat pengidentifikasi, seperti deskripsi aset fisik dan tahun pembeliannya. Untuk membuat pengidentifikasi seperti itu, Anda menggabungkan (menambahkan, menggabungkan) data dari dua sel atau lebih menggunakan ampersand (&). Untuk menggabungkan deskripsi item di sel F3 dengan tanggal di sel G3, dipisahkan oleh spasi, Anda akan memasukkan rumus '=F3&" "&G3' di sel lain di baris itu, seperti E3. Jika Anda ingin memasukkan hanya tahun dalam pengidentifikasi (karena satu daftar menggunakan tanggal penuh dan yang lainnya hanya menggunakan tahun), Anda akan menyertakan fungsi YEAR dengan memasukkan '=F3&" "&YEAR(G3)' di sel E3 sebagai gantinya. (Jangan sertakan tanda kutip tunggal; tanda kutip hanya ada untuk menunjukkan contoh.)
- Setelah Anda membuat rumus, Anda bisa menyalinnya ke semua sel lain dari kolom pengenal dengan memilih sel dengan rumus dan menyeret gagang isian ke sel lain dari kolom tempat Anda ingin menyalin rumus. Saat Anda melepaskan tombol mouse, setiap sel yang Anda seret akan diisi dengan rumus, dengan referensi sel yang disesuaikan dengan sel yang sesuai di baris yang sama.
Langkah 3. Standarisasi data jika memungkinkan
Sementara pikiran mengenali bahwa "Inc." dan "Incorporated" memiliki arti yang sama, Excel tidak kecuali jika Anda memformat ulang satu kata atau yang lain. Demikian juga, Anda dapat mempertimbangkan nilai seperti $11, 950 dan $11, 999,95 sebagai cukup dekat untuk dicocokkan, tetapi Excel tidak akan melakukannya kecuali Anda menyuruhnya.
- Anda dapat menangani beberapa singkatan, seperti "Co" untuk "Perusahaan" dan "Inc" untuk "Dimasukkan dengan menggunakan fungsi string KIRI untuk memotong karakter tambahan. Singkatan lain, seperti "Assn" untuk "Asosiasi," mungkin lebih baik. ditangani dengan membuat panduan gaya entri data dan kemudian menulis program untuk mencari dan memperbaiki format yang tidak tepat.
- Untuk string angka, seperti kode ZIP di mana beberapa entri menyertakan akhiran ZIP+4 dan yang lainnya tidak, Anda dapat menggunakan lagi fungsi string LEFT untuk mengenali dan mencocokkan hanya kode ZIP utama. Agar Excel mengenali nilai numerik yang mendekati tetapi tidak sama, Anda bisa menggunakan fungsi ROUND untuk membulatkan nilai tutup ke angka yang sama dan mencocokkannya.
- Spasi ekstra, seperti mengetik dua spasi di antara kata, bukan satu, dapat dihapus dengan menggunakan fungsi TRIM.
Langkah 4. Buat kolom untuk rumus perbandingan
Sama seperti Anda harus membuat kolom untuk pengidentifikasi daftar, Anda harus membuat kolom untuk rumus yang melakukan perbandingan untuk Anda. Anda memerlukan satu kolom untuk setiap daftar.
Anda ingin memberi label pada kolom ini dengan sesuatu seperti "Hilang?"
Langkah 5. Masukkan rumus perbandingan di setiap sel
Untuk rumus perbandingan, Anda akan menggunakan fungsi MATCH yang bersarang di dalam fungsi Excel lain, ISNA.
- Rumusnya berbentuk "=ISNA(MATCH(G3, $L$3:$L$14, FALSE)))", di mana sel kolom pengenal dari daftar pertama dibandingkan dengan masing-masing pengenal di daftar kedua untuk lihat apakah itu cocok dengan salah satunya. Jika tidak cocok, catatan hilang, dan kata "TRUE" akan ditampilkan di sel itu. Jika cocok, catatan ada, dan kata "FALSE" akan ditampilkan. (Saat memasukkan rumus, jangan sertakan tanda kutip.)
- Anda dapat menyalin rumus ke dalam sel yang tersisa dari kolom dengan cara yang sama seperti Anda menyalin rumus pengidentifikasi sel. Dalam hal ini, hanya referensi sel untuk sel pengidentifikasi yang berubah, karena menempatkan tanda dolar di depan referensi baris dan kolom untuk sel pertama dan terakhir dalam daftar pengidentifikasi sel kedua menjadikannya referensi mutlak.
- Anda dapat menyalin rumus perbandingan untuk daftar pertama ke dalam sel pertama kolom untuk daftar kedua. Anda kemudian harus mengedit referensi sel sehingga "G3" diganti dengan referensi untuk sel pengenal pertama dari daftar kedua dan "$L$3:$L$14" diganti dengan sel pengenal pertama dan terakhir dari daftar kedua. (Biarkan tanda dolar dan titik dua saja.) Anda kemudian dapat menyalin rumus yang diedit ini ke sel yang tersisa di baris perbandingan daftar kedua.
Langkah 6. Urutkan daftar untuk melihat nilai yang tidak cocok dengan lebih mudah, jika perlu
Jika daftar Anda besar, Anda mungkin perlu mengurutkannya untuk menyatukan semua nilai yang tidak cocok. Petunjuk di sublangkah di bawah ini akan mengonversi rumus menjadi nilai untuk menghindari kesalahan penghitungan ulang, dan jika daftar Anda besar, akan menghindari waktu penghitungan ulang yang lama.
- Seret mouse Anda ke semua sel dalam daftar untuk memilihnya.
- Pilih Salin dari menu Edit di Excel 2003 atau dari grup Clipboard di pita Beranda di Excel 2007 atau 2010.
- Pilih Tempel Spesial dari menu Edit di Excel 2003 atau dari tombol tarik-turun Tempel di grup Papan Klip pada pita Excel 2007 atau Beranda 2010-an.
- Pilih "Nilai" dari daftar Tempel Sebagai di kotak dialog Tempel Spesial. Klik OK untuk menutup dialog.
- Pilih Urutkan dari menu Data di Excel 2003 atau grup Urutkan dan Filter dari pita Data di Excel 2007 atau 2010.
- Pilih "Baris tajuk" dari daftar "Rentang data saya" di dialog Urutkan Menurut, pilih "Tidak ada?" (atau nama yang sebenarnya Anda berikan pada tajuk kolom perbandingan) dan klik OK.
- Ulangi langkah ini untuk daftar lainnya.
Langkah 7. Bandingkan item yang tidak cocok secara visual untuk melihat mengapa mereka tidak cocok
Seperti disebutkan sebelumnya, Excel dirancang untuk mencari kecocokan data yang tepat kecuali Anda mengaturnya untuk mencari perkiraan. Ketidakcocokan Anda bisa sesederhana pemindahan huruf atau angka yang tidak disengaja. Bisa juga sesuatu yang memerlukan verifikasi independen, seperti memeriksa untuk melihat apakah aset yang terdaftar perlu dilaporkan terlebih dahulu.
Metode 2 dari 2: Pemformatan Bersyarat Dengan COUNTIF
Langkah 1. Salin daftar data ke satu lembar kerja
Langkah 2. Tentukan daftar mana yang ingin Anda soroti catatan yang cocok atau tidak cocok
Jika Anda ingin menyorot rekaman hanya dalam satu daftar, Anda mungkin ingin menyorot rekaman unik untuk daftar itu; yaitu, catatan yang tidak cocok dengan catatan di daftar lain. Jika Anda ingin menyorot rekaman di kedua daftar, Anda ingin menyorot rekaman yang cocok satu sama lain. Untuk tujuan contoh ini, kita akan menganggap daftar pertama mengambil sel G3 sampai G14 dan daftar kedua mengambil sel L3 sampai L14.
Langkah 3. Pilih item dalam daftar yang ingin Anda soroti item unik atau cocok
Jika Anda ingin menyorot item yang cocok di kedua daftar, Anda harus memilih daftar satu per satu dan menerapkan rumus perbandingan (dijelaskan di langkah berikutnya) ke setiap daftar.
Langkah 4. Terapkan rumus perbandingan yang sesuai
Untuk melakukannya, Anda harus mengakses dialog Pemformatan Bersyarat di versi Excel Anda. Di Excel 2003, Anda melakukannya dengan memilih Pemformatan Bersyarat dari menu Format, sedangkan di Excel 2007 dan 2010, Anda mengklik tombol Pemformatan Bersyarat di grup Gaya pada pita Beranda. Pilih jenis aturan sebagai "Rumus" dan masukkan rumus Anda di bidang Edit Deskripsi Aturan.
- Jika Anda ingin menyorot rekaman unik untuk daftar pertama, rumusnya adalah "=COUNTIF($L$3:$L$14, G3=0)", dengan rentang sel dari daftar kedua yang dirender sebagai nilai absolut dan referensi ke sel pertama dari daftar pertama sebagai nilai relatif. (Jangan masukkan tanda kutip tutup.)
- Jika Anda ingin menyorot catatan unik untuk daftar kedua, rumusnya adalah "=COUNTIF($G$3:$G$14, L3=0)", dengan rentang sel dari daftar pertama yang dirender sebagai nilai absolut dan referensi ke sel pertama dari daftar kedua sebagai nilai relatif. (Jangan masukkan tanda kutip tutup.)
- Jika Anda ingin menyorot rekaman di setiap daftar yang ditemukan di daftar lain, Anda memerlukan dua rumus, satu untuk daftar pertama dan satu untuk yang kedua. Rumus untuk daftar pertama adalah "=COUNTIF($L$3:$L$14, G3>0)", sedangkan rumus untuk daftar kedua adalah COUNTIF($G$3:$G$14, L3>0)". dicatat sebelumnya, Anda memilih daftar pertama untuk menerapkan rumusnya dan kemudian memilih daftar kedua untuk menerapkan rumusnya.
- Terapkan pemformatan apa pun yang Anda inginkan untuk menyorot catatan yang ditandai. Klik OK untuk menutup dialog.
Video - Dengan menggunakan layanan ini, beberapa informasi dapat dibagikan dengan YouTube
Tips
- Alih-alih menggunakan referensi sel dengan metode pemformatan bersyarat COUNTIF, Anda dapat memasukkan nilai yang akan dicari dan menandai satu atau beberapa daftar untuk contoh nilai tersebut.
- Untuk menyederhanakan formulir perbandingan, Anda dapat membuat nama untuk daftar Anda, seperti "Daftar1" dan "Daftar2." Kemudian, saat menulis rumus, nama daftar ini dapat menggantikan rentang sel absolut yang digunakan dalam contoh di atas.
