Koşullu Biçimlendirme: Veriyi Renklerle Konuşturmak

Bu derste ne yapacağız?

Bu derste Excel'in Koşullu Biçimlendirme özelliğini öğreneceksiniz. Amaç, yüzlerce satırlık bir tabloya bakıp önemli değerleri saniyeler içinde fark edebilmek. Hücre vurgulama kurallarını, en yüksek/en düşük değer kurallarını, veri çubuklarını ve renk ölçeklerini kullanacaksınız. Ardından formül tabanlı bir kuralla koşulu sağlayan satırın tamamını renklendirecek, son olarak kuralları yönetmeyi öğreneceksiniz.

Ön koşul: Hücre seçmeyi, sütun başlıklarını tanımayı ve basit bir karşılaştırma formülü (örneğin bir hücrenin başka bir değerden büyük olup olmadığı) yazmayı bilmeniz yeterli. Önceki derslerde özet tablo ve arama formülleriyle veriyi topladınız ve birleştirdiniz. Bu ders, o veriyi gözle okunur hâle getiriyor. Bir sonraki derste (8/8) tekrarlayan işleri otomatikleştiren makrolara giriş yapacağız.

Not: Menü adları ve yerleşimi Excel sürümüne ve dil ayarına göre küçük farklılıklar gösterebilir. Aşağıdaki adlar Türkçe arayüze göredir.

Kavram

Trafik lambasını düşünün. Sürücü kırmızıyı, sarıyı ya da yeşili görünce hiçbir şey okumadan karar verir. Koşullu biçimlendirme de tablonuza böyle bir lamba ekler. Siz bir koşul tanımlarsınız (örneğin satış 10.000'den büyükse). Excel bu koşulu sağlayan hücreleri sizin seçtiğiniz renk, yazı biçimi ya da simgeyle otomatik boyar.

En önemli fark şudur: Hücreyi elle boyamazsınız. Kural hücrenin değerine bakar. Değer değişirse renk de kendiliğinden değişir. Bu yüzden veri güncellendikçe tablonuz canlı kalır.

Bu özelliğin dört ana ailesi vardır:

• Vurgulama kuralları: Belirli bir değerden büyük, küçük, arasında, metin içeren, yinelenen gibi koşullar.
• Üst/Alt kurallar: En yüksek 10, en düşük 10 ya da ortalamanın üstü gibi sıralamaya dayalı koşullar.
• Veri çubukları ve renk ölçekleri: Değerin büyüklüğünü hücre içinde çubuk uzunluğu ya da renk tonuyla gösterir.
• Formül tabanlı kurallar: Kendi mantığınızı yazarsınız. Tüm satırı boyamak gibi esnek işler için kullanılır.

Adım adım uygulama

Örnek olarak A sütununda Tarih, B sütununda Bölge, C sütununda Temsilci, D sütununda Satış Tutarı, E sütununda Durum (Ödendi, Bekliyor, Gecikti) bulunan bir satış tablosu kullanacağız. Veriniz 2. satırdan 100. satıra kadar olsun. Kendi tablonuzda aynı yapıyı kurabilirsiniz.

A) Hücre vurgulama kuralı

• 1. D2'den D100'e kadar Satış Tutarı sütunundaki hücreleri fareyle sürükleyerek seçin.
• 2. Üstteki Giriş sekmesine tıklayın. Şeridin orta kısmında Koşullu Biçimlendirme düğmesini bulun ve tıklayın.
• 3. Açılan listede Hücre Vurgulama Kuralları üzerine gelin, sağda açılan menüden Büyüktür seçeneğine tıklayın.
• 4. Açılan küçük pencerede sol kutuya 10000 yazın. Sağdaki açılır listeden Koyu Yeşil Metinle Yeşil Dolgu seçeneğini seçin.
• 5. Tamam düğmesine tıklayın. 10.000'den büyük satışlar yeşile boyanır.

B) En yüksek ve en düşük değerler

• 1. D sütunu seçiliyken yine Koşullu Biçimlendirme düğmesine tıklayın.
• 2. Üst/Alt Kurallar üzerine gelin ve İlk 10 Öğe seçeneğine tıklayın.
• 3. Soldaki kutudaki 10 sayısını 5 yapın. Sağdan bir renk seçip Tamam'a basın. Artık en yüksek beş satış işaretlidir.
• 4. Aynı yolla Son 10 Öğe kuralını ekleyerek en düşük beş değeri başka bir renkle gösterebilirsiniz. Ortalamanın Üstünde seçeneği de aynı menüdedir.

C) Veri çubukları

• 1. D2:D100 aralığını yeniden seçin.
• 2. Koşullu Biçimlendirme > Veri Çubukları yolunu izleyin. Dereceli Dolgu ya da Düz Dolgu başlığı altından bir renk seçin.
• 3. Her hücrede değerle orantılı bir çubuk oluşur. Uzun çubuk büyük değer demektir. Böylece tabloyu okumadan karşılaştırırsınız.

İpucu: Aynı aralıkta hem vurgulama hem çubuk kullanabilirsiniz ama görüntü kalabalıklaşır. Bir aralık için tek bir görsel dil seçmek daha okunaklıdır.

D) Renk ölçekleri

• 1. Bu kez Satış Tutarı yerine farklı bir sayısal sütun, örneğin bir hedef gerçekleşme oranı sütunu seçin.
• 2. Koşullu Biçimlendirme > Renk Ölçekleri yolunu izleyin ve üç renkli bir ölçek seçin. Kırmızı en düşüğü, yeşil en yükseği gösterecek biçimde olanı tercih edin.
• 3. Hücreler değerlerine göre kırmızıdan yeşile geçen tonlarla boyanır. Sıcaklık haritası görünümü elde edersiniz.

E) Formül tabanlı kural: tüm satırı boyamak

Şimdi Durum sütunu Gecikti olan satırların tamamını kırmızıya boyayalım. Bu, yerleşik kurallarla yapılamaz.

• 1. Tablonun tamamını, A2'den E100'e kadar seçin. Başlık satırını dahil etmeyin.
• 2. Koşullu Biçimlendirme > Yeni Kural seçeneğine tıklayın.
• 3. Pencerenin üst kısmındaki kural türü listesinden Biçimlendirilecek hücreleri belirlemek için formül kullan satırını seçin.
• 4. Alttaki formül kutusuna şunu yazın: =$E2="Gecikti"
• 5. Biçim düğmesine tıklayın. Dolgu sekmesinden açık kırmızı bir renk seçin, isterseniz Yazı Tipi sekmesinden kalın yapın. İki kez Tamam'a basın.

Formülü şöyle okuyun: E sütunundaki değer Gecikti'ye eşitse boya. Buradaki dolar işareti hayati önem taşır. $E yazdığınızda sütun sabit kalır, yani her hücre kendi satırının E hücresine bakar. Satır numarası (2) ise dolarsızdır, böylece kural aşağı doğru her satıra uyarlanır. Dolar işaretini unutursanız renkler dağınık ve anlamsız görünür. Bir formülde birden fazla koşul gerekirse =VE($E2="Gecikti";$D2>10000) biçiminde yazabilirsiniz. Türkçe Excel'de bağımsız değişkenler noktalı virgülle ayrılır.

F) Kuralları yönetmek

• 1. Tablodaki herhangi bir hücreye tıklayın. Koşullu Biçimlendirme > Kuralları Yönet seçeneğine tıklayın.
• 2. Üstteki Biçimlendirme kurallarını göster listesinden Bu Çalışma Sayfası seçeneğini seçin. Sayfadaki tüm kurallar listelenir.
• 3. Bir kuralı seçip Kuralı Düzenle ile değiştirebilir, Kuralı Sil ile kaldırabilirsiniz. Yukarı ve aşağı oklarla öncelik sırasını değiştirebilirsiniz. Listenin üstündeki kural önceliklidir.
• 4. Uygulama Alanı sütunundan her kuralın hangi hücrelere işlediğini görür ve düzeltebilirsiniz.
• 5. Hepsini kaldırmak için Koşullu Biçimlendirme > Kuralları Temizle yolunu izleyip Tüm Sayfadan Kuralları Temizle seçeneğini kullanın.

Sık karşılaşılan sorunlar

Mini görev

Kendi küçük tablonuzu kurun: en az 20 satırlık bir harcama listesi hazırlayın. Sütunlar Tarih, Kategori, Tutar ve Durum (Ödendi ya da Bekliyor) olsun. Aşağıdakileri yapın:

• Tutar sütununda 1.000'den büyük değerleri yeşile boyayan bir vurgulama kuralı ekleyin.
• Aynı sütuna ilk 3 öğe kuralı ekleyin ve farklı bir renk seçin.
• Ayrı bir sayısal sütuna veri çubukları ya da renk ölçeği uygulayın.
• Durumu Bekliyor olan satırların tamamını sarıya boyayan formül tabanlı bir kural yazın.
• Kuralları Yönet penceresini açıp kural sırasını değiştirin ve sonucu gözlemleyin.

Kontrol listesi:

• Kural değerleri değiştirince renkler otomatik güncelleniyor mu?
• Formül tabanlı kuralda sütun adının önünde dolar işareti var mı?
• Uygulama Alanı tablonun tüm satırlarını kapsıyor mu?
• Kuralları Yönet penceresinde her kuralı adlandırabilecek kadar anlıyor musunuz?

Kendini sına

• 1. Koşullu biçimlendirme ile elle hücre boyamak arasındaki en önemli fark nedir?
• 2. Beş en düşük değeri işaretlemek için hangi menü yolunu izlersiniz?
• 3. Veri çubuğu bir hücrede neyi gösterir?
• 4. Formül tabanlı kuralda =$E2="Gecikti" yazarken neden E'nin önüne dolar işareti koyarız?
• 5. İki kural çakıştığında Excel hangisine öncelik verir ve bunu nereden değiştirirsiniz?

Cevaplar

• 1. Koşullu biçimlendirme değere bakar. Değer değişince renk kendiliğinden değişir. Elle boyama ise sabit kalır.
• 2. Giriş sekmesi, Koşullu Biçimlendirme, Üst/Alt Kurallar, Son 10 Öğe. Sonra sayıyı 5 yapın.
• 3. Hücredeki değerin aralıktaki diğer değerlere göre büyüklüğünü, çubuk uzunluğuyla gösterir.
• 4. Sütunu sabitlemek için. Böylece kural her hücrede aynı satırın E hücresine bakar ve tüm satır aynı koşula göre boyanır.
• 5. Listede üstte olan kural önceliklidir. Sırayı Koşullu Biçimlendirme, Kuralları Yönet penceresindeki yukarı ve aşağı oklarla değiştirirsiniz.

Bir sonraki derste, bu tür tekrarlayan biçimlendirme ve düzenleme işlerini tek tıkla yapmanızı sağlayan makrolara ilk adımı atacağız.