Koşullu Biçimlendirmede Formül Nasıl Yazılır?

Formüllü kural ne zaman gerekir?

Hazır kurallar tek bir hücrenin kendi değerine bakar. Bir hücreyi başka bir hücreye, birden fazla koşula ya da bir hesaplamaya göre boyamak istediğinizde formüllü kurala geçersiniz. Örneğin tutarı 1000 TL'yi geçen ve durumu Bekliyor olan siparişleri işaretlemek iki ayrı sütuna bakmayı gerektirir.

Formüllü kuralın mantığı basittir: yazdığınız formül her hücre için hesaplanır. Sonuç DOĞRU ise biçim uygulanır, YANLIŞ ise hücre olduğu gibi kalır.

Formül kuralını oluşturmak

Biçimlendirilecek aralığı seçin, örneğin B2:B5. Giriş sekmesinde Koşullu Biçimlendirme'ye tıklayıp Yeni Kural'ı seçin. Kural Türü Seçin listesinde en alttaki Biçimlendirilecek hücreleri belirlemek için formül kullan seçeneğini işaretleyin.

Formül kutusuna aralığın ilk hücresi için geçerli olan formülü yazın. Biçim düğmesiyle dolgu rengini seçip Tamam'a basın. Excel aynı formülü aralığın diğer hücrelerine, tıpkı aşağı kopyalanan bir formül gibi göreli olarak uygular.

EĞER yazmanız gerekmez

Sık yapılan bir hata formülü =EĞER(B2>1000;DOĞRU;YANLIŞ) gibi yazmaktır. Bu formül çalışır, ama gereksizdir. B2>1000 karşılaştırması zaten DOĞRU ya da YANLIŞ döndürür ve kural bu sonucu doğrudan kullanır.

=EĞER($B3>1000;DOĞRU;YANLIŞ)

=IF($B3>1000,TRUE,FALSE)

EĞER ile metin döndürmek ise kuralı bozar. =EĞER(B2>1000;"Yüksek";"") gibi bir formül DOĞRU ya da YANLIŞ yerine metin döndürdüğü için hiçbir hücre boyanmaz. Kural kutusundaki formülün sonucu her zaman mantıksal bir değer olmalıdır.

Birden fazla koşulu VE ile bağlamak

İki koşulun birlikte sağlanması gerektiğinde VE fonksiyonunu kullanın. Tutarı 1000'den büyük ve durumu Bekliyor olan siparişler için formül =VE($B2>1000;$C2="Bekliyor") olur. Koşullardan biri yetiyorsa VE yerine YADA yazarsınız.

Formülü kurala yazmadan önce sayfadaki boş bir hücrede denemek, hatayı erken yakalamanın en kolay yoludur. Aynı formülü E2'ye yazdığınızda DOĞRU görüyorsanız kural da o satırı boyar.

=VE($B2>1000;$C2="Bekliyor")

=AND($B2>1000,$C2="Bekliyor")

S-101 siparişi 1450 TL ve beklemede olduğu için DOĞRU döner. S-102 beklemede, ama tutarı 800 TL olduğu için YANLIŞ döner ve boyanmaz.

$ işareti neyi değiştirir?

Formüllü kuralda en çok hataya yol açan konu başvuru türüdür. $B2 yazdığınızda sütun sabitlenir, satır her satırda değişir. Bu yüzden kural aralığın her satırında kendi satırının B ve C hücrelerine bakar.

$B$2 yazarsanız bütün hücreler aynı hücreye bakar ve ya hepsi boyanır ya hiçbiri. Formül kutusuna fareyle hücre seçtiğinizde Excel varsayılan olarak $B$2 gibi tam mutlak başvuru yazar. Satır numarasının önündeki $ işaretini sizin silmeniz gerekir.

Bazen tam mutlak başvuru tam da istediğiniz şeydir. Eşik değerini sayfanın üstündeki bir hücreye, örneğin F1'e yazıp formülde =$B2>$F$1 kullandığınızda, eşiği değiştirmek için kurala dokunmanız gerekmez. F1'deki 1000'i 1500 yaptığınız anda renkler yeni sınıra göre güncellenir.

Formül her zaman seçili aralığın sol üst hücresine göre yazılır. Aralığı A2'den başlattıysanız formül de 2. satıra göre olmalıdır; 1. satıra göre yazılmış bir formül bir satır kayık sonuç verir.

Kuralı kurduktan sonra bir hücrenin neden boyandığını merak ederseniz o hücreyi seçip Kuralları Yönet'i açın. Pencere yalnız seçili hücreye uygulanan kuralları gösterir ve formülün o hücre için hangi başvuruyla yazıldığını görmenizi sağlar.

Başka bir listeye göre biçimlendirmek

Formüllü kural başka bir aralığı da kontrol edebilir. Kara listedeki müşteri kodlarını işaretlemek için =EĞERSAY($H$2:$H$20;A2)>0 gibi bir formül yazarsınız. Kod listede bulunduğunda sayı 0'dan büyük olur ve hücre boyanır. Liste aralığı her hücre için aynı kalması gerektiği için burada tam mutlak başvuru doğru seçimdir.

Formülde sık kullanılan kalıplar

Birkaç kalıp çoğu ihtiyacı karşılar. Boş olmayan hücreleri işaretlemek için =$A2<>"" yazılır. Bir metnin içinde belirli bir sözcüğü aramak için =ESAYIYSA(MBUL("acil";$B2)) kullanılır; MBUL bulduğunda sayı, bulamadığında hata döndürdüğü için ESAYIYSA bunu DOĞRU ya da YANLIŞ'a çevirir.

Ortalamanın üstündeki değerler için =B2>ORTALAMA($B$2:$B$50) yazılır. Burada ortalama aralığı her hücre için aynı kalmalıdır, bu yüzden tam mutlak başvuru kullanılır. Her kalıbı önce bir hücrede deneyip sonra kurala taşımak en güvenli yoldur.

Şimdi deneyin

Kendi tablonuzda iki koşullu bir formülü önce boş bir hücrede test edin, DOĞRU ve YANLIŞ sonuçlarını kontrol edin. Ardından aynı formülü Yeni Kural penceresine yazıp sonucu sayfada karşılaştırın.