Excel Koşullu Biçimlendirme Formülleri

Excel'de koşullu biçimlendirme eklemek, ayarladığınız belirli koşullara uyan bir hücreye veya hücre aralığına farklı biçimlendirme seçenekleri uygulamanıza olanak tanır.

Biçimlendirme seçenekleri yalnızca seçilen hücreler bu ayar koşullarını karşıladığında uygulanır.

Uygulanabilecek biçimlendirme seçenekleri, yazı tipi ve arka plan rengi değişiklikleri, yazı tipi stilleri, hücre sınırları ve veriye numara biçimlendirme eklemeyi içerir.

Excel 2007'den beri, Excel'in, belirli bir değerden daha büyük veya daha küçük sayıların bulunması veya ortalama değerin üstünde veya altında olan sayıların bulunması gibi yaygın olarak kullanılan koşullar için bir dizi yerleşik seçenek vardır.

Bu önceden ayarlanmış seçeneklere ek olarak, kullanıcı tarafından belirlenen koşulları test etmek için Excel formüllerini kullanarak özel koşullu biçimlendirme kuralları oluşturmak da mümkündür.

Birden Çok Kural Uygulanması

Farklı koşullar için test etmek için aynı verilere birden fazla kural uygulanabilir. Örneğin, bütçe verilerinin, toplam bütçenin belirli düzeyleri (% 50,% 75 ve% 100 gibi) harcanması durumunda biçimlendirme değişiklikleri uygulayan koşullar olabilir.

Bu gibi durumlarda, Excel önce çeşitli kuralların çakışıp çakışmadığını belirler ve eğer varsa, program, hangi koşullu biçimlendirme kuralının verilere uygulanacağını belirlemek için önceden belirlenmiş bir sıralama sırasını izler.

Örnek: Koşullu Biçimlendirme ile% 25 ve% 50'yi Aşan Verileri Artırma

Aşağıdaki örnekte, B2 ile B5 arasındaki hücrelerin aralığına iki özel koşullu biçimlendirme kuralları uygulanacaktır.

• Birinci kural, A2 ila A5 hücrelerindeki verilerin B2'den B5'e karşılık gelen değerden% 25'ten fazla olup olmadığını kontrol eder.
• ikinci kural, A2: A5'teki aynı verilerin B2: B5'te karşılık gelen değeri% 50'den fazla aşıp aşmadığını kontrol eder.

Yukarıdaki resimde görüldüğü gibi, yukarıdaki koşullardan herhangi biri doğruysa, hücrenin arka plan rengi veya B1: B4 aralığındaki hücreler değişecektir.

• Farkın% 25'ten büyük olduğu veriler için, hücre arka plan rengi maviye dönüşecektir.
• fark% 50'den büyükse, hücre arka plan rengi kırmızıya dönüşür.

Bu görevi yerine getirmek için kullanılan kurallar,

= (A2-B2) / A2>% 25 = (A2-B2) / A2>% 50

Koşullu biçimlendirme Yeni Biçimlendirme Kuralı iletişim kutusu kullanılarak girilecektir.

Öğretici Verilerini Girme

1. Yukarıdaki resimde görüldüğü gibi verileri A1 ila C5 hücrelerine girin

Not: Öğreticinin 3. adımı, koşullu formatlama kurallarının doğruluğunu kontrol etmek için A2: A5 ve B2: B5 hücrelerindeki değerler arasındaki tam yüzde farkını gösteren C2: C4 hücrelerine formül ekleyecektir.

Condtional Formatlama Kurallarını Ayarlama

Belirtildiği gibi, iki koşulu kontrol eden koşullu biçimlendirme kuralları Koşullu biçimlendirme Yeni Biçimlendirme Kuralı iletişim kutusu kullanılarak girilecektir.

% 25'ten daha fazla artış elde etmek için koşullu biçimlendirmeyi ayarlama

1. Çalışma sayfasında B2 - B5 hücrelerini vurgulayın.
2. Şeritteki Ana Sayfa sekmesine tıklayın.
3. Açılır menüyü açmak için şeridin Koşullu Biçimlendirme simgesine tıklayın.
4. Yukarıdaki resimde görüldüğü gibi Yeni Biçimlendirme Kuralı iletişim kutusunu açmak için Yeni Kural'ı seçin.
5. İletişim kutusunun üst yarısında son seçeneğe tıklayın: Hangi hücrelerin biçimlendirileceğini belirlemek için bir formül kullanın.
6. İletişim kutusunun alt yarısında, bu formülün doğru olduğu Biçim değerlerini tıklatın : satır.
7. Sağlanan alana aşağıdaki formülü yazınız : = (A2-B2) / A2>% 25
8. Biçim Hücreleri iletişim kutusunu açmak için Biçim düğmesine tıklayın.
9. Bu iletişim kutusunda, Dolgu sekmesine tıklayın ve mavi bir dolgu rengi seçin.
10. İletişim kutularını kapatmak ve çalışma sayfasına geri dönmek için iki kez Tamam'ı tıklatın .
11. Bu noktada, B3 ve B5 hücrelerinin arka plan rengi mavi olmalıdır.

% 50'den fazla artışa ulaşmak için koşullu biçimlendirmeyi ayarlama

1. Halen B2'den B5'e kadar olan hücrelerle, yukarıdaki 1'den 6'ya kadar olan adımları tekrarlayın.
2. Sağlanan alana aşağıdaki formülü yazınız: = (A2-B2) / A2>% 50.
3. Biçim Hücreleri iletişim kutusunu açmak için Biçim düğmesine tıklayın.
4. Dolgu sekmesine tıklayın ve kırmızı bir dolgu rengi seçin.
5. İletişim kutularını kapatmak ve çalışma sayfasına geri dönmek için iki kez Tamam'ı tıklatın .
6. B3 hücresinin arka plan rengi hala mavi olmalıdır, bu da, A3 ve B3 hücrelerindeki sayılar arasındaki yüzde farkın% 25'ten büyük, ancak% 50'den küçük veya eşit olduğunu gösterir.
7. B5 hücresinin arka plan rengi, A5 ve B5 hücrelerindeki sayılar arasındaki yüzde farkın% 50'den büyük olduğunu gösteren kırmızıya dönüşmelidir.

Koşullu Biçimlendirme Kurallarını Denetleme

% Fark hesaplanıyor

Girilen koşullu biçimlendirme kurallarının doğru olup olmadığını kontrol etmek için, formülleri A2: A5 ve B2: B5 aralığındaki sayılar arasındaki tam yüzde farkını hesaplayacak C2: C5 hücrelerine girebiliriz.

1. Aktif hücre yapmak için C2 hücresini tıklayın.
2. = (A2-B2) / A2 formülünü girin ve klavyede Enter tuşuna basın.
3. % 10 yanıtı, hücre A2'deki sayının, B2 hücresindeki sayıdan% 10 daha büyük olduğunu belirten C2 hücresinde görünmelidir.
4. Yanıtı yüzde olarak görüntülemek için C2 hücresindeki formatlamayı değiştirmek gerekebilir.
5. Formülü C2 hücresinden C3 - C5 hücrelerine kopyalamak için dolgu tutamacını kullanın.
6. C3 ila C5 hücreleri için yanıtlar:% 30,% 25 ve% 60 olmalıdır.
7. Bu hücrelerdeki yanıtlar, koşullu biçimlendirme kurallarının doğru olduğunu gösterir çünkü A3 ve B3 hücreleri arasındaki fark% 25'ten büyüktür ve A5 ve B5 hücreleri arasındaki fark% 50'den büyüktür.
8. Hücre B4, renk değiştirmez çünkü A4 ve B4 hücreleri arasındaki fark% 25'e eşittir ve koşullu biçimlendirme kuralımız, arka plan renginin maviye dönüşmesi için% 25'ten büyük bir yüzdeye gerek olduğunu belirtmiştir.

Koşullu Biçimlendirme Kuralları İçin Öncelik Sırası

Çakışan Koşullu Biçimlendirme Kurallarını Uygulama

Aynı veri aralığına birden fazla kural uygulandığında, Excel ilk olarak kuralların çakışıp çakışmadığını belirler.

Çelişen kurallar, her kural için seçilen biçimlendirme seçeneklerinin her ikisinin de aynı verilere uygulanamadığı kurallardır.

Bu öğreticide kullanılan örnekte, kurallar her iki kural da aynı biçimlendirme seçeneğini kullanır - arka plan hücre rengini değiştirir.

İkinci kuralın doğru olduğu durumda (değerdeki fark iki hücre arasında% 50'den fazladır), o zaman ilk kural (değerdeki fark% 25'ten büyüktür) da doğrudur.

Excel'in Öncelik Sırası

Bir hücre aynı anda hem kırmızı hem de mavi bir arka plana sahip olamayacağından, Excel'in hangi koşullu biçimlendirme kuralının uygulanacağını bilmesi gerekir.

Hangi kuralın uygulanacağı, Excel'in öncelik sırasına göre belirlenir; bu, Koşullu Biçimlendirme Kuralları Yöneticisi iletişim kutusundaki listede daha yüksek olan kuralın öncelikli olduğunu belirtir.

Yukarıdaki resimde gösterildiği gibi, bu öğreticide kullanılan ikinci kural (= (A2-B2) / A2>% 50) listede daha yüksektir ve bu nedenle, birinci kural üzerinde önceliğe sahiptir.

Sonuç olarak, B5 hücresinin arka plan rengi kırmızıya dönüşür.

Varsayılan olarak, listenin en üstüne yeni kurallar eklenir ve bu nedenle daha yüksek bir önceliğe sahiptir.

Öncelik sırasını değiştirmek için yukarıdaki görüntüde tanımlanan iletişim kutusundaki Yukarı ve Aşağı ok düğmelerini kullanın.

Çakışan Olmayan Kuralların Uygulanması

İki veya daha fazla koşullu biçimlendirme kuralı çakışmazsa, her kuralın sınandığı koşul doğru olduğunda her ikisi de uygulanır.

Örneğimizdeki ilk koşullu biçimlendirme kuralı (= (A2-B2) / A2>% 25), mavi arka plan rengi yerine mavi kenarlı B2: B5 hücre aralığını biçimlendirdiyse, iki koşullu biçimlendirme kuralı Her iki format diğerine müdahale etmeden uygulanabilir.

Sonuç olarak, B5 hücresi hem mavi bir kenarlığa hem de kırmızı bir arka plan rengine sahip olacaktır, çünkü A5 ve B5 hücrelerindeki sayılar arasındaki fark yüzde 25 ve yüzde 50'den daha büyüktür.

Koşullu Biçimlendirme ve Düzenli Biçimlendirme

Koşullu biçimlendirme kuralları ve el ile uygulanan biçimlendirme seçenekleri arasında çakışma olması durumunda, koşullu biçimlendirme kuralı her zaman önceliğe sahiptir ve el ile eklenmiş biçimlendirme seçenekleri yerine uygulanır.

Örneğimizde, başlangıçta B2-B5 hücrelerine sarı bir arka plan rengi uygulandıysa, koşullu biçimlendirme kuralları eklendikten sonra, yalnızca B2 ve B4 hücreleri sarı kalacaktı.

Girilen koşullu biçimlendirme kuralları B3 ve B5 hücrelerine uygulandığından, arka plan renkleri sırasıyla sarıdan maviye ve kırmızıya değişir.