Conditional Formatting

CF'da en sık karıştırılan konulardan biri, belirli yüzdesel eşiklerin altını/üstünü formatlı göstermektir. Malesef bana göre bu konuda Microsoft hatalı yönlendirmede bulunmakta, daha doğrusu gerekli yönlendimeyi yapmamaktadır. İşin doğrusunu öğrenene kadar bayağı bir araştırma yapmanız gerekmektedir.

Örneğin aşağıdaki tabloda D kolonuna CF uygulayacacağız. Diyeceğiz ki %100 ve üzerini en iyi olarak(yeşil ikon), %90-100 arasını orta(sarı ikon), 90 altını da kötü(kırmızı ikon) olarak göster.

Aşağıdaki ikon stini uyguladım

CF, otomatik olarak 33  ve 67 şeklinde ayarladı. Ben gittim bunu 90 ve 100 olarak değiştirdim.

Sonuç şöyle:

Gördüğünüz gibi %100 ve üzerinde 6 bölge olmasına rağmen sadece birini yeşil yaptı, 90-100 arası 1 bölge olmasına rağmen hiç sarı yapmadı. Neden böyle oldu?

Çünkü Excel, buradaki verilerin en düşüğünü %0, en yükseğini %100 varsayarak yeni bir değer hesaplıyor ve onun üzerinden CF uyguluyor. Zaten ilk başta %33 ve %67 diye otomatik ayırmasının sebebi de bu. Değerleri %0 ve %100 arasında olacak şekilde diziyor, 3 dilim belirliyor, ve rakamları bu 3 dilim içine yerleştiriyor. Uygulanan formül şöyle:

=(D2-MIN(D:D))/(MAX(D:D)-MIN(D:D))

Buna göre hesaplanmış HG%ler E kolonundaki gibi oluyor ve bu değerlere göre CF uygulandığında da sonuç gayet normal(!). %100 ve üzerinde sadece 1 bölge var, 90-100 arasında ise hiç yok.

Bizim istediğimiz ise böyle birşey değildi. O yüzden Percent ifadesinin bizi yanıltmasına izin vermeyelim ve Number seçeneğini aşağıdaki gibi ayarlayalım.

Sonuç:

Sıklıkla, formatlama işlemi komşu veya başka hücrelere göre yapılmaktadır. Mesela format uygulayacağımız  hücre bu yılın artış oranını, bir yan hücre ise geçen yılın artış oranını veriyordur. Bu  yılın artış oranı geçen yılın artış oranından büyükse/küçükse şöyle şöyle formatla diyebiliriz. Ancak burda bazı nüanslar var. Şimdi bunlara bakalım.

CF'da karıştırılan, anlaşılması zor olan bir konu da mutlak ve göreceli başvurulardır, ki bu konu formüle dayalı CF'in can damarını oluşturmaktadır. Eğer bu konu tam anlaşılamazsa CF de verimli ve etkin kullanılamaz. Mutlak/Göreceli başvuru tipleri için buraya bakabilirsiniz. Özetle CF dilinde "=$A$1", "=$A1", "=A$1" ve "=A1" tamamen farklı sonuçlar üretir. Örnek üzerinden durumu açıklayalım.

Şimdi aşağıdaki tabloda Highlight Cell Rules>Greater Than dedik ve değer olarak da E2 hücresini seçtik. Default olarak mutlak başvuru yazıldı. Buna göre seçilen alandaki tüm hücrelerin E1'in değerinden yani %81den büyük olmasına baktı. Mutlak başvuruda, CF uygulanan hücrelerin karşılaştırma hücresine göre konumu önemsizdir ve sabittir. Yani bu örnek için tüm hücreler için E1 hücresiyle karşılaştırma yapılır.

Mutlak başvuru yerine göreceli başvuru yaparsak durumun nasıl değiştiğini görelim. Bu sefer D3 hücresini E3 ile, D4'ü E4 v.s ile karşılaştırır.

Burda yapılacak bir hata da şu olabilir. İlk CF'ı yaptınız ve mutlak başvuru kullandığınızı farkettiniz, bunu düzeltmek yerine mevcudun üzerine ikinci bir CF uygulayıp orada göreceli yaparsanız etkisi olmaz, çünkü ilk CF ikincisini ezmiş olur. Zaten Rule Managera girince 2 tane CF olduğunu da görebilirsiniz.

Burada ve burada, birden fazla CF'ın öncelik işleyişi hakkında detaylı bilgi bulunmaktadır.

Dikkat:Göreceli başvuruları, Icon setleri ve Color Scale formatingle kullanamazsınız. Bunlarda nasıl biz çözüm uygulanacağını bir sonraki örnekte bulabilirsiniz.

Aşağıdaki gibi bir tablomuz olsun. Bu, bir bankada çeşitli kalemlerde X bölge müdürlüğü ile bankanın toplam performansını karşılaştıran bir tablodur.

Bu tabloda Bölge Büyüme kolonu üzerinde, Bankadan büyükse yeşil bir tick işareti olsun, sarı ve kırmızı ikon olmasın istiyoruz diyelim. Çünkü yöneticimiz "çok renkli olunca kafam karışıyor, sadece iyileri görmek yeterli" demiş olsun. Bunun için Conditional Formatting>Icon Sets>Indicators'ten ilk seçenek tıklandığında karşımıza aşağıdaki ekran gelir.

Bu ekranda aşağıdaki gibi sadece ilk kutu için ilgili hücreyi gireriz ve Type kutusuna da Number gireriz. Diğer iki kutuyu "No Cell Icon" yaparız.

OK dedikten sonra da "Applies to" kutusunu da hangi hücrelere uygulayacaksak onları seçeriz.

Ancak iki üstteki resimde farkettiyseniz yeşil tick işareti koyduğumuz alan için Value kutusuna $I$6 şeklinde mutlak başvuru girişi yaptık, zira göreceli başvuryu(I6) Icon Setlerde girmeye izin vermiyor. Bu nedenle, Conditional kutusunu kapattıktan sonra ilgili hücrelere tek tek girip bu Value alanını değiştirmemiz lazım. Ör:H7 hücresine gidip bunun için Value alanını $I$7 yapmak gibi. Ancak değiştireceğiniz hücre sayısı çoksa ve bunları tek tek değiştirmek istemiyorsanız, aşağıdaki VBA kodu ile de bunu başarabilirsiniz(Makro bilmenin avantajları...)

Sub conditional_toplu()
'bu makroyu recorder ile kaydettim, sadece döngüyü ve Value kısmını elle değiştirdim
For Each c In Selection
    c.Select
    c.FormatConditions.Delete 'mevcuttaki conditionı silelim

    c.FormatConditions.AddIconSetCondition
    c.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .ReverseOrder = False
        .ShowIconOnly = False
        .IconSet = ActiveWorkbook.IconSets(xl3TrafficLights1)
    End With
    c.FormatConditions(1).IconCriteria(1).Icon = xlIconGreenCheckSymbol
    With c.FormatConditions(1).IconCriteria(2)
        .Type = xlConditionValueFormula
        .Value = "=" & c.Offset(0, -2).Address 'bu kısmı kendim değiştirdim
        .Operator = 7
        .Icon = xlIconNoCellIcon
    End With
    With c.FormatConditions(1).IconCriteria(3)
        .Type = xlConditionValueNumber
        .Value = 1000000000000#
        .Operator = 7
        .Icon = xlIconNoCellIcon
    End With
Next c

End Sub

Sonuç aşağıdaki gibi olacaktır.

İkinci örneğimiz ilk örneğin hemen hemen aynısı, ancak bu sefer bir de pazar büyümesi ile karşılaştırma kolonu var. Bundan sonraki kısım teknik bilgiden ziyade, çözüm bulma beceresi ve biraz pratiklikle alakalı. Giriş sayfamda belirttiğim gibi bu sitede bu tür çözüm önerilerini de buluyor olacaksınız. Şimdi diyelim ki yöneticiniz, bölgeyi banka ve pazarla karşılaştırmanızı istemiş olsun. Bir hücrede aynı anda hem bölgeye hem pazara göre aynı türde conditional formatting uygulayamıyoruz. Yani aynı anda hem bankaya hem pazara göre icon set formatı uygulayamayız. Bu durumda önünüzde iki seçenek(benim aklıma gelen) var,

1. seçenek: Bölge kolonunda, bankaya göre karşılaştırma için icon set uygularken, pazara göre karşılaştırma için farklı bir formating uygulanabilir(arkaplan rengini değiştirmek gibi)

2.seçenek: Conditional formattingi bölge kolonuna değil, banka ve pazar kolonlarına uygulamak. Bölge, bankadan iyiyise banka kolonuna yeşil tick, bölge pazardan iyiyse pazar kolonuna yeşil tick konur.

Ben örnek olsun diye ikisini birden uyguladım, tercih size kalmış. Tabiki ikisini aynı anda kullanmak anlamsız olur, ya birini ya diğerini seçmeniz gerekecektir.

CF'i, girdiğimiz bir formülün doğru olması durumunda da uygulayabiliriz. Yani girdiğimiz formül TRUE/FALSE döndürmelidir. Mesela aşağıdaki formül ile Sayı olan tüm hücreler formatlanacaktır.

Aşağıda diğer formüllü örnekleri bulabilirsiniz. Ancak yukarda belirttiğimiz gibi mutlak ve göreceli başvuru tiplerinin ne olduklarını iyice içselleştirdikten sonra devam etmenizi öneririm. Zira bu örneklerin hepsinde mutlak ve göreceli başvuruların farklı kombinasyonları kullanılacak olup formüller biraz karmaşıklaşacaktır.

Mükerrer kayıtları bulma

Mükerrer kayıtlardan sadece ilkini formatlama

Mükerrer kayıtlardan ilki dışındakileri formatlama

Belli bir kategorideki rakamların toplamı negatif/poziti mi kontrolü

Şimdi diyelim ki şöyle bir listemiz var.

Bu listede İndirim+İlave toplamı negatif mi diye kontrol edelim, toplamı negatif olan kayıtları işaretleyelim. Tabi bunun yapmanın bir yolu da PivotTable yapıp, küçükten büyüğe sıralayıp en üstte negatif var mı diye bakmak olabilir, veya başka yöntemler de kullanılabilir. Her zaman dediğimiz gibi, o an ihtiyacınızı hangi yöntem çözüyorsa onu kullanın. Biz şimdi CF yöntemini kullanacağız.

Bunun için formüle dayalı CF uygular ve şu formülü yazarız:

ve A2:C17 alanına uygularız:

Sonuç:

Bölge ortalamasının altında kalan şubeleri formatlama