Formula generik
=COUNTIF(list,A1)=0
Ringkasan
Untuk membandingkan senarai dan menyoroti nilai yang ada dalam satu tetapi tidak yang lain, anda boleh menerapkan pemformatan bersyarat dengan formula berdasarkan fungsi COUNTIF. Sebagai contoh, untuk menonjolkan nilai A1: A10 yang tidak ada C1: C10, pilih A1: A10 dan buat peraturan pemformatan bersyarat berdasarkan formula ini:
=COUNTIF($C$1:$C$10,A1)=0
Catatan: dengan pemformatan bersyarat, penting untuk memasukkan formula yang berkaitan dengan "sel aktif" dalam pemilihan, yang dianggap A1 dalam kes ini.
Penjelasan
Formula ini dinilai untuk setiap 10 sel di A1: D10. A1 akan berubah menjadi alamat sel yang sedang dinilai, sementara C1: C10 dimasukkan sebagai alamat mutlak, sehingga tidak akan berubah sama sekali.
Kunci formula ini adalah = 0 di akhir, yang "membalikkan" logik formula. Untuk setiap nilai dalam A1: A10, COUNTIF mengembalikan kekerapan nilai tersebut muncul dalam C1: C10. Selagi nilainya muncul sekurang-kurangnya sekali dalam C1: C10, COUNTIF akan mengembalikan nombor bukan sifar dan formula akan mengembalikan SALAH.
Tetapi apabila nilai tidak dijumpai di C1: C10, COUNTIF mengembalikan sifar dan, kerana 0 = 0, formula akan mengembalikan BENAR dan pemformatan bersyarat akan diterapkan.
Julat dinamakan untuk sintaks sederhana
Sekiranya anda menamakan senarai yang anda cari (C1: C10 dalam kes ini) dengan julat bernama, rumus lebih mudah dibaca dan difahami:
=COUNTIF(list,A1)=0
Ini berfungsi kerana julat yang dinamakan secara automatik mutlak.
Versi sensitif kes
Sekiranya anda memerlukan jumlah peka huruf besar kecil, anda boleh menggunakan formula seperti ini:
=SUMPRODUCT((--EXACT(A1,list)))=0
Fungsi EXACT melakukan penilaian peka huruf besar dan SUMPRODUCT menilai hasilnya. Seperti COUNTIF, formula ini akan kembali apabila hasilnya adalah sifar. Oleh kerana ujian ini peka huruf besar kecil, "apple" akan kelihatan hilang walaupun "Apple" atau "APPLE" muncul dalam senarai kedua. Lihat halaman ini untuk penjelasan yang lebih terperinci.
