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

Standart CF seçenekleri arasında "Duplicate Values" olduğunu biliyoruz. Bununla mükerrer kayıtları(tüm satırı aynı) değil, sadece seçilen kolondaki mükerrer datayı bulmuş oluyoruz. Halbuki veritabanı dilinde Kayıt demek, tüm bir satıra ait data demek. Örneğin aşağıdaki kayıtlar mükerrer iken,

Şu kayıtlar mükerrer değildir. (Sadece Müşteri No datası mükerrerdir.)

Peki mükerrer kayıtları tüm satır şeklinde nasıl buluruz. Tabiki bir formülle. Hemen bakalım.

Countifs'in 1. ve 3. parametresini mutlak başvurulu yaptım, sürekli aynı yerde baksın diye. 2.ve 4.parametresini ise yarı mutlak yarı göreceli yaptım, kayarak ilerlesin diye, ama sadece satırda kaysın, sürunda değil. Böylece örneğin 5.satırı kontrol ederken A2:A17 arasında 14023ten bi tane daha varmı diye bakacak ve aynı anda B2:B17 arasında 14023ün Kredi Kartı bi tane daha varmı diye bakmış olacak.
Tüm satır renklendirmesi istediğim için tüm alanı seçiyorum.

Gördüğünüz gibi en alttaki 18952 nolu müşteri de mükerrer olmasına rağmen iki farklı ürün için datası bulunduğu için kayıt anlamında mükerrer değildir. Sadece 15375 nolu müşteri mükerrer çımıştır.

NOT:C kolonundaki alan hiç değişmediği için bunu formüle dahil etmedim.

Mükerrer kayıtlardan sadece ilkini formatlama

Eğer mükerrer kayıtların tamamıyla değil de sadece ilkiyle ilgileniyorsak, tarama sırasında kontrol bölgesinin de kayarak ilerlemesini sağlamalıyız. Mesela aşağıdaki görüntüde 10.satırın tarama bölgesi A10:A25 olur. Neden? Çünkü A2:A17 olarak başladık, ama satırı göreceli verdik. Dolayısıyla aşağı doğru indikçe satırlar da 1er 1er kayar. Yani ilk kayıt olan 11670 için A2:A17'ye bakarken ikincisi için A3:A18'e bakar ve böylece gider. Dolayısıyla mükerrer kaydı bulduktan sonra bi aşağıya kaydığı için o kayıttan bidaha bulamamış olur. Bu örnekte 11.satırdaki 15375 için tarama bölgesi A11:A26 olduğu için onun aynısına rastlayamamıştır ve işaretlememiştir.

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

Eğer mükerrer kayıtlardan, ilki dışındakilerle ilgilenmiyorsak bundaki tarama mantığı "İlk başı sabitle, sonra hücrenin kendisyle birlikte aşağı doğru kay" şeklinde olmalı. Yani ilk kayıt olan 11670'i A2:A2de arar, ikinci kayıt olan 11693ü A2:A3te, böyle ilerlerken 10.kayda geldiğinde 15375i A2:A10'da aradı ve 1 tane buldu, dolayısıyla işaretlemedi, 11.kayda geldiğinde 15375i A2:A11de aradı ve 2 tane bulduğu için bunu işaretledi. Aşağılarda 3. bi eşleşme olsaydı onu da işaretlerdi.

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

Şimdi diyelim ki şöyle bir listemiz var. Burada bölgenin ortalama HG% oranından düşük HG%'li şubeleri renklendirmek istiyoruz ama şubenin HG%si %100'ün üzerinde kalıyorsa renklenmesin. Mesela Bölge1'in HG% ortalaması %104 olup Şube2'nin renklenmemesi gerekiyor.

Formülümüzü aşağıdaki gibi yazıp ilerleyelim.

Uygulanacak alanı da seçelim.

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