Orta Ders 8: Koşullu Biçimlendirme ile Veriyi Vurgulama

Koşullu biçimlendirme ne yapar?

Koşullu biçimlendirme, bir hücrenin rengini, yazı biçimini ya da simgesini içindeki değere göre otomatik olarak değiştirir. Değer değiştikçe biçim de güncellenir. Yüzlerce satırlık bir stok listesinde azalan ürünleri gözle aramak yerine kırmızıya boyanmış satırlara bakarsınız.

Bu araç veriyi değiştirmez, yalnız görünümünü etkiler. Bu yüzden biçimin arkasındaki koşulu sayıyla da sorgulayabilmek gerekir. Ders boyunca her kuralın formül karşılığını da göreceksiniz.

Hazır kurallarla başlamak

En hızlı yol, Giriş sekmesindeki Koşullu Biçimlendirme menüsünün hazır kurallarıdır. Hücre Vurgulama Kuralları altında Büyüktür, Küçüktür, Arasında ve Yinelenen Değerler gibi seçenekler bulunur. Stok sütununu seçip Küçüktür'ü işaretler, kutuya 20 yazarsanız 20'nin altındaki stoklar renklenir.

Üst/Alt Kuralları en yüksek ya da en düşük değerleri, Veri Çubukları hücre içinde değerle orantılı bir çubuk, Renk Ölçekleri ise küçükten büyüğe bir renk geçişi gösterir. Satış tablosunda Renk Ölçekleri, hangi ayın zayıf kaldığını tek bakışta gösterir.

Formülle kendi kuralınızı yazmak

Karşılaştırılacak eşik her satırda farklıysa hazır kurallar yetmez. Örnek tabloda her ürünün kendi kritik seviyesi var: kalem için 50, zımba için 15. Burada Yeni Kural penceresindeki "Biçimlendirilecek hücreleri belirlemek için formül kullanın" seçeneğine geçersiniz.

Kural formülü DOĞRU ya da YANLIŞ döndürmelidir. DOĞRU dönen hücreler biçimlenir. Önce aynı mantığı bir hücrede deneyin.

=B3<C3

=B3<C3

Defter'in stoku 35, kritik seviyesi 40 olduğu için karşılaştırma DOĞRU döner. Kuralı A2:C7 aralığına uygularken formülü =$B2<$C2 olarak yazarsınız. Sütun harflerinin önündeki $, her hücrenin B ve C sütunlarına bakmasını sağlar. Satır numarası göreli kaldığı için de her satır kendi stokunu kendi eşiğiyle karşılaştırır, böylece bütün satır renklenir.

Kaç satır vurgulanacak?

Biçimlenen satır sayısını bilmek, raporda "kritik ürün sayısı" gibi bir göstergeye ihtiyacınız olduğunda işe yarar. Koşullu biçimlendirme bu sayıyı vermez, ama aynı koşulu bir formülle saydırabilirsiniz.

=TOPLA.ÇARPIM(--(B2:B7<C2:C7))

=SUMPRODUCT(--(B2:B7<C2:C7))

Defter, silgi ve zımba eşiğin altında olduğu için sonuç 3'tür. TOPLA.ÇARPIM her satırdaki karşılaştırmayı tek tek yapar. Başındaki iki eksi işareti DOĞRU ve YANLIŞ değerlerini 1 ve 0'a çevirir, toplam da kritik satır sayısını verir.

Kuralları yönetmek

Aynı aralığa birden fazla kural koyduğunuzda Koşullu Biçimlendirme menüsündeki Kuralları Yönet penceresi sıralamayı gösterir. Üstteki kural önce uygulanır. Hem "kritik" hem "dikkat" kuralınız varsa kritik olanı üste taşıyın.

Bu pencerede her kuralın "Uygulandığı yer" alanı da görünür. Satır kopyalayıp yapıştırdıkça Excel aynı kuralı parçalı aralıklara bölebilir ve liste uzar. Böyle bir durumda fazlalıkları silip tek bir kuralı bütün aralığa uygulamak dosyayı hem hızlandırır hem okunur kılar.

Sık yapılan hatalar

En yaygın hata, formüldeki başvuruların yanlış sabitlenmesidir. =$B$2<$C$2 yazarsanız bütün satırlar yalnız ilk satırın sonucuna göre boyanır. =B2<C2 yazarsanız da sütun kaydığı için satırın yalnız bir kısmı doğru renklenir.

İkinci hata, formülün seçili aralığın ilk satırına göre yazılmamasıdır. Aralığı A2'den başlatıp formülde 3. satıra başvurursanız biçim bir satır kayık görünür. Kuralı kurmadan önce aralığın sol üst hücresini kontrol edin.

Vadesi geçen faturaları boyamak

Tahsilat takibinde en çok işe yarayan kurallardan biri, vadesi geçen ve hâlâ ödenmemiş faturaları boyamaktır. Vade tarihi D sütununda, ödeme durumu E sütunundaysa A2:E50 aralığına şu formüllü kuralı uygularsınız: =VE($D2<BUGÜN();$E2="Ödenmedi").

İki koşul birlikte sağlandığında satır kırmızıya boyanır. Ödeme geldiğinde E sütununu "Ödendi" yaparsınız ve renk kendiliğinden kalkar. BUGÜN her gün değiştiği için dosyayı ertesi gün açtığınızda vadesi yeni dolan faturalar da renklenmiş olur.

Vadesine üç günden az kalan faturalar için ikinci bir sarı kural ekleyebilirsiniz: =VE($D2>=BUGÜN();$D2-BUGÜN()<3;$E2="Ödenmedi"). Kuralları Yönet penceresinde kırmızı kuralı üstte tutun.

Bu kurallardaki BUGÜN, dosya her açıldığında yeniden hesaplanır. Raporu belirli bir güne göre dondurmak istiyorsanız BUGÜN yerine rapor tarihini yazdığınız bir hücreye başvurun, örneğin $H$1. Böylece geçmiş bir tarihin durumunu da aynı kurallarla görebilir, rapor tarihini değiştirerek renklerin nasıl değiştiğini izleyebilirsiniz.

Sıradaki adım

Alıştırmada aynı stok tablosuna, kuralın koşulunu metne çeviren bir Durum sütunu ekleyeceksiniz. Ardından kendi dosyanızda bu sütunu kaynak alan bir kural kurup "Sipariş ver" yazan satırları renklendirin.