WikiHow ini mengajar anda bagaimana menggunakan pemformatan bersyarat untuk membandingkan semua sel pada dua senarai berasingan dalam spreadsheet Excel, dan tandakan sel yang muncul di kedua-dua senarai. Ciri ini hanya tersedia pada versi desktop Excel, dan aplikasi mudah alih tidak mendukungnya.
Langkah-langkah
Kaedah 1 dari 2: Menggunakan Formula CountIf
Langkah 1. Buka hamparan Excel yang ingin anda edit
Cari fail hamparan dengan senarai yang ingin anda bandingkan, dan klik dua kali fail untuk membukanya di Microsoft Excel.
Langkah 2. Pilih senarai pertama anda
Klik sel pertama pada senarai pertama anda, dan seret tetikus anda hingga ke sel terakhir senarai untuk memilih keseluruhan julat data.
Langkah 3. Klik tab Formula pada pita bar alat
Anda boleh mendapatkan tab ini di atas bar alat di bahagian atas hamparan anda. Ini akan membuka alat formula anda pada pita bar alat.
Langkah 4. Klik Tentukan Nama pada bar alat
Anda boleh mendapatkan pilihan ini di tengah-tengah reben "Rumus". Ini akan membuka tetingkap pop timbul baru, dan membolehkan anda menamakan senarai anda.
Langkah 5. Taipkan Senarai1 ke dalam medan Nama
Klik medan teks di bahagian atas tetingkap pop timbul, dan masukkan nama senarai di sini.
- Anda kemudian boleh menggunakan nama ini untuk memasukkan senarai anda ke dalam formula perbandingan.
- Sebagai alternatif, anda boleh memberikan senarai nama yang berbeza di sini. Contohnya, jika ini adalah senarai lokasi, anda boleh menamakannya "location1" atau "locationList."
Langkah 6. Klik OK di tetingkap pop timbul
Ini akan mengesahkan tindakan anda, dan menamakan senarai anda.
Langkah 7. Namakan senarai kedua anda sebagai Senarai2
Ikuti langkah yang sama dengan senarai pertama, dan beri nama senarai kedua anda. Ini akan membolehkan anda menggunakan senarai kedua ini dengan cepat dalam formula perbandingan anda kemudian.
Anda boleh memberikan senarai nama yang anda mahukan. Pastikan untuk mengingat atau mencatatkan nama yang anda berikan pada setiap senarai anda di sini
Langkah 8. Pilih senarai pertama anda
Klik sel pertama pada senarai pertama, dan seret ke bawah untuk memilih keseluruhan julat data.
Pastikan senarai pertama anda dipilih sebelum anda mula mengatur format bersyarat anda
Langkah 9. Klik tab Laman Utama pada bar alat
Ini adalah tab pertama di sudut kiri atas pita bar alat. Ini akan membuka alat spreadsheet asas anda pada bar alat.
Langkah 10. Klik Pemformatan Bersyarat pada bar alat
Pilihan ini kelihatan seperti ikon spreadsheet kecil dengan beberapa sel diserlahkan dengan warna merah dan biru. Ini akan membuka menu lungsur dari semua pilihan pemformatan anda.
Langkah 11. Klik Peraturan Baru pada menu lungsur
Ini akan membuka tetingkap pop timbul baru, dan membolehkan anda mengatur peraturan pemformatan baru untuk julat yang dipilih secara manual.
Langkah 12. Pilih pilihan "Gunakan formula untuk menentukan sel yang hendak diformat"
Pilihan ini akan membolehkan anda menaip formula pemformatan secara manual untuk membandingkan dua senarai anda.
- Dihidupkan Tingkap, anda akan menemuinya di bahagian bawah senarai peraturan di kotak "Pilih Jenis Peraturan".
- Dihidupkan Mac, pilih Klasik dalam menu lungsur "Gaya" di bahagian atas pop timbul. Kemudian, cari pilihan ini pada menu lungsur kedua di bawah menu Gaya.
Langkah 13. Klik medan formula di tetingkap pop timbul
Anda boleh memasukkan formula Excel yang sah di sini untuk menetapkan peraturan pemformatan bersyarat.
Langkah 14. Taip = countif (List2, A1) = 1 ke dalam bar formula
Rumus ini akan mengimbas dua senarai anda, dan menandakan semua sel pada senarai pertama anda yang juga muncul di senarai kedua.
- Gantikan A1 dalam formula dengan nombor sel pertama senarai pertama anda.
- Sebagai contoh, jika sel pertama senarai pertama anda adalah sel D5, maka formula anda akan kelihatan seperti = countif (List2, D5) = 1.
- Sekiranya anda memberikan nama yang berbeza untuk senarai kedua anda, pastikan untuk mengganti List2 dalam formula dengan nama sebenar senarai anda sendiri.
- Sebagai alternatif, ubah formula menjadi = countif (List2, A1) = 0 jika anda mahu menandakan sel itu jangan muncul dalam senarai kedua.
Langkah 15. Taip = countif (List1, B1) = 1 ke dalam bar formula (pilihan)
Sekiranya anda ingin mencari dan menandakan sel di kedua senarai yang juga muncul di senarai pertama, gunakan formula ini dan bukannya yang pertama.
Ganti List1 dengan nama senarai pertama anda, dan B1 dengan sel pertama senarai kedua anda
Langkah 16. Pilih format khusus untuk menandakan sel (pilihan)
Anda boleh memilih warna isi latar yang disesuaikan dan gaya fon yang berbeza untuk menandakan sel yang dijumpai formula anda.
- Dihidupkan Tingkap, klik Format butang di kanan bawah tetingkap pop timbul. Anda dapat memilih warna latar pada tab "Isi", dan gaya fon di tab "Font".
- Dihidupkan Mac, pilih pratetap format pada drop-down "Format with" di bahagian bawah. Anda juga boleh memilih format tersuai di sini untuk memilih gaya latar dan fon secara manual.
Langkah 17. Klik OK di tetingkap pop timbul
Ini akan mengesahkan dan menggunakan formula perbandingan anda. Semua sel pada senarai pertama anda yang juga muncul di senarai kedua akan ditandakan dengan warna dan fon yang anda pilih.
- Sebagai contoh, jika anda memilih isi merah terang dengan teks merah gelap, semua sel yang berulang akan bertukar menjadi warna ini pada senarai pertama anda.
- Sekiranya anda menggunakan formula kedua di atas, pemformatan bersyarat akan menandakan sel berulang pada senarai kedua anda dan bukan yang pertama.
Kaedah 2 dari 2: Menggunakan Formula VLookup
Langkah 1. Buka hamparan Excel anda
Cari fail Excel dengan senarai yang ingin anda bandingkan, dan klik dua kali pada nama fail atau ikon untuk membuka hamparan di Microsoft Excel.
Langkah 2. Klik sel kosong di sebelah item pertama dalam senarai kedua anda
Cari senarai kedua anda di hamparan, dan klik sel kosong di sebelah item senarai pertama di bahagian atas.
- Anda boleh memasukkan formula VLookup anda di sini.
- Sebagai alternatif, anda boleh memilih mana-mana sel kosong pada hamparan anda. Sel ini hanya akan memudahkan anda melihat perbandingan anda di sebelah senarai kedua anda.
Langkah 3. Taip = vlookup (ke dalam sel kosong
Rumus VLookup akan membolehkan anda membandingkan semua item dalam dua senarai berasingan, dan melihat apakah nilai itu adalah nilai berulang atau nilai baru.
Jangan tutup kurungan formula sehingga formula anda lengkap
Langkah 4. Pilih item pertama dalam senarai kedua anda
Tanpa menutup kurungan formula, klik item pertama pada senarai kedua anda. Ini akan memasukkan sel pertama senarai kedua anda ke dalam formula.
Langkah 5. Taipkan a, koma dalam formula
Setelah memilih sel pertama dalam senarai kedua anda, taipkan koma dalam formula. Anda akan dapat memilih julat perbandingan anda seterusnya.
Langkah 6. Tahan dan pilih keseluruhan senarai pertama anda
Ini akan memasukkan rangkaian sel senarai pertama anda ke bahagian kedua formula VLookup.
Ini akan membolehkan anda mencari senarai pertama untuk item yang dipilih dari senarai kedua anda (item pertama di bahagian atas senarai kedua), dan kembali jika ia adalah nilai berulang atau baru
Langkah 7. Taipkan a, koma dalam formula
Ini akan mengunci julat perbandingan dalam formula anda.
Langkah 8. Taipkan 1 dalam formula selepas koma
Nombor ini mewakili nombor indeks lajur anda. Ia akan meminta formula VLookup untuk mencari lajur senarai sebenar dan bukannya lajur lain di sebelahnya.
Sekiranya anda mahu formula anda mengembalikan nilai dari lajur tepat di sebelah senarai pertama anda, taip 2 di sini
Langkah 9. Taipkan a, koma dalam formula
Ini akan mengunci nombor indeks lajur anda (1) dalam formula VLookup.
Langkah 10. Taip PALSU dalam formula
Ini akan mencari senarai pencocokan tepat bagi item carian yang dipilih (item pertama di bahagian atas senarai kedua) dan bukannya perkiraan yang sesuai.
- Daripada SALAH anda mungkin menggunakan 0, ia sama.
- Sebagai alternatif, anda boleh menaip BENAR atau 1 jika anda ingin mencari padanan anggaran.
Langkah 11. Taip) di hujung untuk menutup formula
Anda sekarang boleh menjalankan formula anda, dan melihat apakah item carian yang dipilih dalam senarai kedua anda adalah nilai berulang atau baru.
Contohnya, jika senarai kedua anda bermula dari B1, dan senarai pertama anda bermula dari sel A1 hingga A5, formula anda akan kelihatan seperti = vlookup (B1, $ A $ 1: $ A $ 5, 1, palsu).
Langkah 12. Tekan ↵ Enter atau ⏎ Kembali pada papan kekunci anda.
Ini akan menjalankan formula, dan mencari senarai pertama anda untuk item pertama dari senarai kedua anda.
- Sekiranya ini adalah nilai berulang, anda akan melihat nilai yang sama dicetak lagi dalam sel formula.
- Sekiranya ini adalah nilai baru, anda akan melihat " # N / A"dicetak di sini.
- Contohnya, jika anda mencari senarai pertama untuk "John", dan sekarang melihat "John" dalam sel formula, itu adalah nilai berulang yang terdapat pada kedua-dua senarai tersebut. Sekiranya anda melihat "# N / A", itu adalah nilai baru dalam senarai kedua.
Langkah 13. Pilih sel formula anda
Setelah menjalankan formula anda dan melihat hasil anda untuk item senarai pertama, klik pada sel formula untuk memilihnya.
Langkah 14. Klik dan seret titik hijau ke kanan bawah sel
Ini akan mengembangkan sel formula anda di sepanjang senarai, dan menggunakan formula untuk setiap item senarai di senarai kedua anda.
- Dengan cara ini anda dapat membandingkan setiap item dalam senarai kedua anda dengan keseluruhan senarai pertama anda.
- Ini akan mencari senarai pertama anda untuk setiap item dalam senarai kedua anda secara individu, dan menunjukkan hasilnya di sebelah setiap sel secara berasingan.
- Sekiranya anda ingin melihat penanda yang berbeza untuk nilai baru dan bukannya "# N / A", gunakan formula ini: = iferror (vlookup (B1, $ A $ 1: $ A $ 5, 1, false), "Nilai Baru"). Ini akan mencetak "Nilai Baru" untuk nilai baru dan bukannya "# N / A."
