Formula umum
=COUNTIF(list,A1)=0
Ringkasan
Untuk membandingkan daftar dan menyoroti nilai yang ada di satu tapi tidak yang lain, Anda bisa menerapkan pemformatan bersyarat dengan rumus yang didasarkan pada fungsi COUNTIF. Misalnya, untuk menyorot nilai A1: A10 yang tidak ada C1: C10, pilih A1: A10 dan buat aturan pemformatan bersyarat berdasarkan rumus ini:
=COUNTIF($C$1:$C$10,A1)=0
Catatan: dengan pemformatan bersyarat, penting untuk memasukkan rumus yang berhubungan dengan "sel aktif" dalam pemilihan, yang diasumsikan A1 dalam kasus ini.
Penjelasan
Rumus ini dievaluasi untuk masing-masing 10 sel di A1: D10. A1 akan berubah menjadi alamat sel yang dievaluasi, sedangkan C1: C10 dimasukkan sebagai alamat absolut, jadi tidak akan berubah sama sekali.
Kunci dari rumus ini adalah = 0 di bagian akhir, yang "membalik" logika rumus. Untuk setiap nilai di A1: A10, COUNTIF mengembalikan berapa kali nilai muncul di C1: C10. Selama nilai muncul setidaknya sekali di C1: C10, COUNTIF akan mengembalikan angka bukan nol dan rumus akan mengembalikan FALSE.
Tapi ketika nilai tidak ditemukan di C1: C10, COUNTIF mengembalikan nol dan, karena 0 = 0, rumus akan mengembalikan TRUE dan pemformatan bersyarat akan diterapkan.
Rentang bernama untuk sintaks sederhana
Jika Anda memberi nama daftar yang Anda cari (C1: C10 dalam hal ini) dengan rentang bernama, rumusnya lebih sederhana untuk dibaca dan dipahami:
=COUNTIF(list,A1)=0
Ini berfungsi karena rentang bernama secara otomatis absolut.
Versi peka huruf besar kecil
Jika Anda membutuhkan hitungan peka huruf besar kecil, Anda dapat menggunakan rumus seperti ini:
=SUMPRODUCT((--EXACT(A1,list)))=0
Fungsi EXACT melakukan evaluasi case-sensitive dan SUMPRODUCT menghitung hasilnya. Seperti halnya COUNTIF, rumus ini akan kembali jika hasilnya nol. Karena pengujian peka huruf besar kecil, "apple" akan ditampilkan sebagai hilang meskipun "Apple" atau "APPLE" muncul di daftar kedua. Lihat halaman ini untuk penjelasan lebih rinci.
