Artikel wikiHow ini akan mengajarkan kepada Anda cara menggunakan pemformatan bersyarat untuk membandingkan semua sel pada dua daftar terpisah dalam lembar bentang Excel, dan menandai sel yang muncul di kedua daftar tersebut. Fitur ini hanya tersedia di versi desktop Excel, dan aplikasi seluler tidak mendukungnya.
Langkah
Metode 1 dari 2: Menggunakan Rumus CountIf
Langkah 1. Buka spreadsheet Excel yang ingin Anda edit
Temukan file spreadsheet dengan daftar yang ingin Anda bandingkan, dan klik dua kali file tersebut untuk membukanya di Microsoft Excel.
Langkah 2. Pilih daftar pertama Anda
Klik sel pertama pada daftar pertama Anda, dan seret mouse Anda ke bawah ke sel terakhir daftar untuk memilih seluruh rentang data.
Langkah 3. Klik tab Rumus pada pita toolbar
Anda dapat menemukan tab ini di atas bilah alat di bagian atas spreadsheet Anda. Ini akan membuka alat rumus Anda di pita bilah alat.
Langkah 4. Klik Tentukan Nama pada bilah alat
Anda dapat menemukan opsi ini di tengah pita "Rumus". Ini akan membuka jendela pop-up baru, dan memungkinkan Anda memberi nama daftar Anda.
Langkah 5. Ketik List1 ke dalam bidang Nama
Klik bidang teks di bagian atas jendela pop-up, dan masukkan nama daftar di sini.
- Anda nanti dapat menggunakan nama ini untuk memasukkan daftar Anda ke dalam rumus perbandingan.
- Atau, Anda dapat memberi daftar Anda nama yang berbeda di sini. Misalnya, jika ini adalah daftar lokasi, Anda dapat menamainya "locations1" atau "locationList."
Langkah 6. Klik OK di jendela pop-up
Ini akan mengkonfirmasi tindakan Anda, dan memberi nama daftar Anda.
Langkah 7. Beri nama daftar kedua Anda sebagai List2
Ikuti langkah yang sama seperti daftar pertama, dan beri nama daftar kedua Anda. Ini akan memungkinkan Anda dengan cepat menggunakan daftar kedua ini dalam rumus perbandingan Anda nanti.
Anda dapat memberikan daftar nama apapun yang Anda inginkan. Pastikan untuk mengingat atau mencatat nama yang Anda berikan untuk setiap daftar Anda di sini
Langkah 8. Pilih daftar pertama Anda
Klik sel pertama pada daftar pertama, dan seret ke bawah untuk memilih seluruh rentang data.
Pastikan daftar pertama Anda dipilih sebelum Anda mulai mengatur pemformatan bersyarat Anda
Langkah 9. Klik tab Beranda pada bilah alat
Ini adalah tab pertama di sudut kiri atas pita toolbar. Ini akan membuka alat spreadsheet dasar Anda di bilah alat.
Langkah 10. Klik Pemformatan Bersyarat pada bilah alat
Opsi ini terlihat seperti ikon spreadsheet kecil dengan beberapa sel disorot dengan warna merah dan biru. Ini akan membuka menu tarik-turun dari semua opsi pemformatan Anda.
Langkah 11. Klik Aturan Baru pada menu tarik-turun
Ini akan membuka jendela pop-up baru, dan memungkinkan Anda mengatur aturan pemformatan baru secara manual untuk rentang yang dipilih.
Langkah 12. Pilih opsi "Gunakan rumus untuk menentukan sel mana yang akan diformat"
Opsi ini akan memungkinkan Anda mengetikkan rumus pemformatan secara manual untuk membandingkan dua daftar Anda.
- Pada jendela, Anda akan menemukannya di bagian bawah daftar aturan di kotak "Select a Rule Type".
- Pada Mac, Pilih Klasik di menu tarik-turun "Gaya" di bagian atas jendela munculan. Kemudian, temukan opsi ini di drop-down kedua di bawah menu Style.
Langkah 13. Klik bidang rumus di jendela pop-up
Anda dapat memasukkan rumus Excel apa pun yang valid di sini untuk menyiapkan aturan pemformatan bersyarat.
Langkah 14. Ketik =countif(List2, A1)=1 ke dalam bilah rumus
Rumus ini akan memindai dua daftar Anda, dan menandai semua sel di daftar pertama Anda yang juga muncul di daftar kedua.
- Ganti A1 dalam rumus dengan nomor sel pertama dari daftar pertama Anda.
- Misalnya, jika sel pertama dari daftar pertama Anda adalah sel D5, maka rumus Anda akan terlihat seperti =countif(List2, D5)=1.
- Jika Anda memberikan nama yang berbeda untuk daftar kedua Anda, pastikan untuk mengganti List2 dalam rumus dengan nama sebenarnya dari daftar Anda sendiri.
- Atau, ubah rumus menjadi =countif(List2, A1)=0 jika Anda ingin menandai sel yang tidak muncul di daftar kedua.
Langkah 15. Ketik =countif(List1, B1)=1 ke dalam formula bar (opsional)
Jika Anda ingin menemukan dan menandai sel di kedua list yang juga muncul di daftar pertama, gunakan rumus ini alih-alih yang pertama.
Ganti List1 dengan nama daftar pertama Anda, dan B1 dengan sel pertama dari daftar kedua Anda
Langkah 16. Pilih format khusus untuk menandai sel (opsional)
Anda dapat memilih warna isian latar belakang kustom dan gaya font yang berbeda untuk menandai sel yang ditemukan rumus Anda.
- Pada jendela, klik Format tombol di kanan bawah jendela pop-up. Anda dapat memilih warna latar belakang di tab "Isi", dan gaya font di tab "Font".
- Pada Mac, pilih prasetel format pada tarik-turun "Format dengan" di bagian bawah. Anda juga dapat memilih format khusus di sini untuk memilih isian latar belakang dan gaya font secara manual.
Langkah 17. Klik OK di jendela pop-up
Ini akan mengkonfirmasi dan menerapkan rumus perbandingan Anda. Semua sel di daftar pertama Anda yang juga muncul di daftar kedua akan ditandai dengan warna dan font yang Anda pilih.
- Misalnya, jika Anda memilih isian merah muda dengan teks merah tua, semua sel berulang akan berubah menjadi warna ini pada daftar pertama Anda.
- Jika Anda menggunakan rumus kedua di atas, pemformatan bersyarat akan menandai sel berulang pada daftar kedua Anda, bukan yang pertama.
Metode 2 dari 2: Menggunakan Rumus VLookup
Langkah 1. Buka spreadsheet Excel Anda
Temukan file Excel dengan daftar yang ingin Anda bandingkan, dan klik dua kali pada nama file atau ikon untuk membuka spreadsheet di Microsoft Excel.
Langkah 2. Klik sel kosong di sebelah item pertama pada daftar kedua Anda
Temukan daftar kedua Anda di spreadsheet, dan klik sel kosong di sebelah item daftar pertama di bagian atas.
- Anda dapat memasukkan rumus VLookup Anda di sini.
- Atau, Anda dapat memilih sel kosong di spreadsheet Anda. Sel ini hanya akan membuat Anda lebih nyaman untuk melihat perbandingan Anda di sebelah daftar kedua Anda.
Langkah 3. Ketik =vlookup(ke dalam sel kosong
Rumus VLookup akan memungkinkan Anda untuk membandingkan semua item pada dua daftar terpisah, dan melihat apakah suatu nilai adalah nilai berulang atau baru.
Jangan tutup kurung rumus sampai rumus Anda lengkap
Langkah 4. Pilih item pertama pada daftar kedua Anda
Tanpa menutup kurung rumus, klik item pertama pada daftar kedua Anda. Ini akan memasukkan sel pertama daftar kedua Anda ke dalam rumus.
Langkah 5. Ketik a, koma dalam rumus
Setelah memilih sel pertama dari daftar kedua Anda, ketik koma dalam rumus. Anda akan dapat memilih rentang perbandingan Anda berikutnya.
Langkah 6. Tahan dan pilih seluruh daftar pertama Anda
Ini akan memasukkan rentang sel dari daftar pertama Anda ke bagian kedua dari rumus VLookup.
Ini akan memungkinkan Anda untuk mencari daftar pertama untuk item yang dipilih dari daftar kedua Anda (item pertama di bagian atas daftar kedua), dan kembali jika itu adalah pengulangan atau nilai baru
Langkah 7. Ketik a, koma dalam rumus
Ini akan mengunci rentang perbandingan dalam rumus Anda.
Langkah 8. Ketik 1 dalam rumus setelah koma
Angka ini mewakili nomor indeks kolom Anda. Ini akan meminta rumus VLookup untuk mencari kolom daftar yang sebenarnya, bukan kolom yang berbeda di sebelahnya.
Jika Anda ingin rumus Anda mengembalikan nilai dari kolom tepat di sebelah daftar pertama Anda, ketik 2 di sini
Langkah 9. Ketik a, koma dalam rumus
Ini akan mengunci nomor indeks kolom Anda (1) dalam rumus VLookup.
Langkah 10. Ketik FALSE dalam rumus
Ini akan mencari daftar untuk kecocokan yang tepat dari item pencarian yang dipilih (item pertama di bagian atas daftar kedua) alih-alih perkiraan kecocokan.
- Alih-alih FALSE Anda dapat menggunakan 0, itu persis sama.
- Atau, Anda dapat mengetik TRUE atau 1 jika Anda ingin mencari perkiraan kecocokan.
Langkah 11. Ketik) di akhir untuk menutup rumus
Anda sekarang dapat menjalankan rumus Anda, dan melihat apakah item pencarian yang dipilih pada daftar kedua Anda adalah pengulangan atau nilai baru.
Misalnya, jika daftar kedua Anda dimulai dari B1, dan daftar pertama Anda beralih dari sel A1 ke A5, rumus Anda akan terlihat seperti =vlookup(B1, $A$1:$A$5, 1, salah).
Langkah 12. Tekan Enter atau Kembali di keyboard Anda.
Ini akan menjalankan rumus, dan mencari daftar pertama Anda untuk item pertama dari daftar kedua Anda.
- Jika ini adalah nilai berulang, Anda akan melihat nilai yang sama dicetak lagi di sel rumus.
- Jika ini adalah nilai baru, Anda akan melihat " #T/A" dicetak di sini.
- Misalnya, jika Anda mencari daftar pertama untuk "John", dan sekarang melihat "John" di sel rumus, itu adalah nilai berulang yang muncul di kedua daftar. Jika Anda melihat "#N/A", itu adalah nilai baru di daftar kedua.
Langkah 13. Pilih sel rumus Anda
Setelah menjalankan rumus Anda dan melihat hasil Anda untuk item daftar pertama, klik sel rumus untuk memilihnya.
Langkah 14. Klik dan seret ke bawah titik hijau di kanan bawah sel
Ini akan memperluas sel rumus Anda di sepanjang daftar, dan menerapkan rumus ke setiap item daftar di daftar kedua Anda.
- Dengan cara ini Anda dapat membandingkan setiap item di daftar kedua Anda dengan seluruh daftar pertama Anda.
- Ini akan mencari daftar pertama Anda untuk setiap item di daftar kedua Anda satu per satu, dan menampilkan hasilnya di sebelah setiap sel secara terpisah.
- Jika Anda ingin melihat penanda yang berbeda untuk nilai baru daripada "#N/A", gunakan rumus ini: =iferror(vlookup(B1, $A$1:$A$5, 1, false), "Nilai Baru"). Ini akan mencetak "Nilai Baru" untuk nilai baru, bukan "#N/A."
