Excel

Formüllerle koşullu biçimlendirme (10 örnek)

Conditional Formatting With Formulas

Hızlı Başlangıç ​​| Örnekler | Sorun giderme | Eğitim

Koşullu biçimlendirme, bir elektronik tablodaki verileri hızla görselleştirmenin harika bir yoludur. Koşullu biçimlendirme ile, önümüzdeki 30 gün içinde öne çıkan tarihler, veri girişi sorunlarını işaretleme, en iyi müşterileri içeren satırları vurgulama, yinelenenleri gösterme ve daha fazlası gibi şeyler yapabilirsiniz.





Excel, formüller olmadan yeni kurallar oluşturmayı kolaylaştıran çok sayıda 'ön ayar' ile birlikte gelir. Ancak, kendi özel formüllerinizle de kurallar oluşturabilirsiniz. Kendi formülünüzü kullanarak, bir kuralı tetikleyen koşulu devralırsınız ve tam olarak ihtiyacınız olan mantığı uygulayabilirsiniz. Formüller size maksimum güç ve esneklik sağlar.

Örneğin, 'Eşit' ön ayarını kullanarak 'elma'ya eşit olan hücreleri vurgulamak kolaydır.





Peki ya 'elma', 'kivi' veya 'kireç' ile eşit olan hücreleri vurgulamak isterseniz? Elbette, her değer için bir kural oluşturabilirsiniz, ancak bu çok zahmetlidir. Bunun yerine, aşağıdaki formüle dayalı bir kuralı kullanabilirsiniz: VEYA işlevi :

x, y veya z'yi vurgulama kuralı



İşte bu e-tablodaki B4:F8 aralığına uygulanan kuralın sonucu:

VEYA işleviyle koşullu biçimlendirme

İşte kullanılan tam formül:

 
= OR (B4='apple',B4='kiwi',B4='lime')

Hızlı başlangıç

Dört kolay adımda formüle dayalı bir koşullu biçimlendirme kuralı oluşturabilirsiniz:

1. Biçimlendirmek istediğiniz hücreleri seçin.

Biçimlendirilecek hücreleri seçin

2. Bir koşullu biçimlendirme kuralı oluşturun ve Formül seçeneğini seçin.

Formül seçeneğini seçin

3. DOĞRU veya YANLIŞ döndüren bir formül girin.

Etkin hücreye göre formülü girin

4. Biçimlendirme seçeneklerini ayarlayın ve kuralı kaydedin.

Biçimlendirme seçeneklerini ayarla

NS ISODD işlevi kuralı tetikleyerek yalnızca tek sayılar için TRUE değerini döndürür:

excel hücresindeki boşluk nasıl kaldırılır

ISODD işlevi, kuralı tetikleyen tek sayılar için TRUE değerini döndürür.

Video: Formülle koşullu biçimlendirme nasıl uygulanır

Biz de sunuyoruz bu konuyla ilgili video eğitimi .

formül mantığı

Koşullu biçimlendirme uygulayan formüller DOĞRU veya YANLIŞ ya da sayısal eşdeğerlerini döndürmelidir. İşte bazı örnekler:

excel'de ad ve soyadları birleştirmek
 
= ISODD (A1) = ISNUMBER (A1) =A1>100 = AND (A1>100,B1<50) = OR (F1='MN',F1='WI')

Yukarıdaki formüllerin tümü DOĞRU veya YANLIŞ döndürür, bu nedenle koşullu biçimlendirme için mükemmel bir tetikleyici olarak çalışırlar.

Bir hücre aralığına koşullu biçimlendirme uygulandığında, seçimdeki ilk satıra ve sütuna (yani sol üst hücreye) göre hücre referanslarını girin. Koşullu biçimlendirme formüllerinin nasıl çalıştığını anlamanın püf noktası, aynı formülün seçimdeki her hücre , hücre referansları her zamanki gibi güncellendi. Formülü seçimin sol üst hücresine girdiğinizi ve ardından formülü tüm seçim boyunca kopyaladığınızı hayal edin. Bununla mücadele ediyorsanız, bölümüne bakın. kukla formüller aşağıda.

Formül Örnekleri

Aşağıda, koşullu biçimlendirme uygulamak için kullanabileceğiniz özel formül örnekleri verilmiştir. Bu örneklerden bazıları, hücreleri vurgulamak için Excel'in yerleşik hazır ayarları kullanılarak oluşturulabilir, ancak aşağıda görebileceğiniz gibi özel formüller hazır ayarların çok ötesine geçebilir.

Ayrıca bakınız: 30'dan Fazla Koşullu Biçimlendirme Formülü

Teksas'tan siparişleri vurgulayın

Teksas'tan gelen siparişleri (kısaltılmış TX) temsil eden satırları vurgulamak için F sütununa referansı kilitleyen bir formül kullanın:

 
=$F5='TX'

Durumun = olduğu satırları vurgulamak için bir formül kullanın

Daha fazla ayrıntı için şu makaleye bakın: Koşullu biçimlendirme ile satırları vurgulayın .

Video: Koşullu biçimlendirme ile satırlar nasıl vurgulanır

Önümüzdeki 30 gün içindeki tarihleri ​​vurgulayın

Önümüzdeki 30 gün içinde meydana gelen tarihleri ​​vurgulamak için (1) tarihlerin gelecekte olmasını ve (2) tarihlerin bugünden 30 gün veya daha az olmasını sağlayan bir formüle ihtiyacımız var. Bunu yapmanın bir yolu, VE işlevi ile birlikte ŞİMDİ işlevi bunun gibi:

 
= AND (B4> NOW (),B4<=( NOW ()+30))

Geçerli tarihi 18 Ağustos 2016 olan koşullu biçimlendirme, tarihleri ​​aşağıdaki gibi vurgular:

Sonraki 30 gün içindeki tarihleri ​​vurgulamak için koşullu biçimlendirme

NS ŞİMDİ işlevi geçerli tarih ve saati döndürür. Bu formülün nasıl çalıştığıyla ilgili ayrıntılar için şu makaleye bakın: Tarihleri ​​sonraki N gün içinde vurgulayın .

Sütun farklılıklarını vurgulayın

Benzer bilgiler içeren iki sütun verildiğinde, küçük farklılıkları tespit etmek için koşullu biçimlendirmeyi kullanabilirsiniz. Aşağıdaki biçimlendirmeyi tetiklemek için kullanılan formül:

 
=$B4$C4

Sütunları karşılaştırmak için koşullu biçimlendirme

Ayrıca bakınız: Bu formülün, büyük/küçük harfe duyarlı bir karşılaştırma yapmak için EXACT işlevini kullanan bir sürümü .

Eksik değerleri vurgulayın

Bir listedeki diğerinde eksik olan değerleri vurgulamak için, aşağıdakilere dayalı bir formül kullanabilirsiniz. EĞERSAY işlevi :

 
= COUNTIF (list,B5)=0

Koşullu biçimlendirme ile eksik değerleri vurgulayın

Bu formül, içindeki her bir değeri kontrol eder. A Listesi adlandırılmış 'liste' aralığındaki değerlere karşı (D5:D10). Sayı sıfır olduğunda formül DOĞRU değerini döndürür ve içindeki değerleri vurgulayan kuralı tetikler. A Listesi eksik olan B Listesi .

Video: COUNTIF ile eksik değerler nasıl bulunur

3+ yatak odası 350 bin doların altında olan mülkleri vurgulayın

Bu listede en az 3 yatak odası olan ancak 300.000 ABD Dolarından az olan mülkleri bulmak için VE işlevine dayalı bir formül kullanabilirsiniz:

 
= AND ($C5<350000,$D5>=3)

Dolar işaretleri ($) C ve D sütunlarına referansı kilitler ve VE işlevi her iki koşulun da DOĞRU olduğundan emin olmak için kullanılır. AND işlevinin TRUE değerini döndürdüğü satırlarda koşullu biçimlendirme uygulanır:

Mülk listelerini vurgulamak için koşullu biçimlendirme

En iyi değerleri vurgulayın (dinamik örnek)

Excel'in 'en yüksek değerler' için ön ayarları olmasına rağmen, bu örnek aynı şeyin bir formülle nasıl yapılacağını ve formüllerin nasıl daha esnek olabileceğini gösterir. Bir formül kullanarak çalışma sayfasını etkileşimli hale getirebiliriz — F2'deki değer güncellendiğinde, kural anında yanıt verir ve yeni değerleri vurgular.

Üst değerler için dinamik koşullu biçimlendirme

Bu kural için kullanılan formül:

klasördeki excel vba listesi dosyaları
 
=B4>= LARGE (data,input)

Burada 'veri', B4:G11 olarak adlandırılan aralıktır ve 'girdi', F2 olarak adlandırılan aralıktır. Bu sayfada ayrıntılar ve tam bir açıklama .

Gantt çizelgeleri

İster inanın ister inanmayın, aşağıdaki gibi koşullu biçimlendirme ile basit Gantt çizelgeleri oluşturmak için formülleri bile kullanabilirsiniz:

Gantt grafiği oluşturmak için koşullu biçimlendirmeyi kullanma

Bu çalışma sayfası, biri çubuklar için, diğeri hafta sonu gölgelendirme için olmak üzere iki kural kullanır:

 
= AND (D>=$B5,D<=$C5) // bars = WEEKDAY (D,2)>5 // weekends

Bu makale çubukların formülünü açıklar , ve bu makale hafta sonu gölgelendirme formülünü açıklıyor .

Basit arama kutusu

Koşullu biçimlendirme ile yapabileceğiniz harika bir numara, basit bir arama kutusu oluşturmaktır. Bu örnekte, bir kural, F2 hücresine yazılan metni içeren B sütunundaki hücreleri vurgular:

Koşullu biçimlendirme arama kutusu

Kullanılan formül:

 
= ISNUMBER ( SEARCH ($F,B2))

Daha fazla ayrıntı ve tam bir açıklama için bakınız:

Sorun giderme

Koşullu biçimlendirme kurallarınızı doğru şekilde çalıştıramıyorsanız, büyük olasılıkla formülünüzde bir sorun vardır. İlk olarak, formüle eşittir işareti (=) ile başladığınızdan emin olun. Bu adımı unutursanız, Excel tüm formülünüzü sessizce metne dönüştürerek onu işe yaramaz hale getirir. Düzeltmek için, Excel'in her iki tarafına eklenen çift tırnak işaretlerini kaldırın ve formülün eşittir (=) ile başladığından emin olun.

Formülünüz doğru girilmişse ancak kuralı tetiklemiyorsa, biraz daha derine inmiş olabilirsiniz. Normalde, bir formüldeki sonuçları kontrol etmek için F9 tuşunu veya bir formülde ilerlemek için Değerlendir özelliğini kullanabilirsiniz. Ne yazık ki, bu araçları koşullu biçimlendirme formülleriyle kullanamazsınız, ancak 'kukla formüller' adı verilen bir teknik kullanabilirsiniz.

kukla formüller

Sahte formüller, koşullu biçimlendirme formüllerinizi doğrudan çalışma sayfasında test etmenin bir yoludur, böylece gerçekte ne yaptıklarını görebilirsiniz. Bu, hücre referanslarının doğru şekilde çalışmasını sağlamak için mücadele ederken büyük bir zaman kazandırabilir.

Özetle, aynı formülü, verilerinizin şekliyle eşleşen bir dizi hücreye girersiniz. Bu, her formülün döndürdüğü değerleri görmenizi sağlar ve formüle dayalı koşullu biçimlendirmenin nasıl çalıştığını görselleştirmenin ve anlamanın harika bir yoludur. Ayrıntılı açıklama için, bu makaleye bakın .

Koşullu biçimlendirme formüllerini kontrol etmek için sahte formüller kullanın

Video: Sahte formüllerle koşullu biçimlendirmeyi test edin

sınırlamalar

Formüle dayalı koşullu biçimlendirmeyle gelen bazı sınırlamalar vardır:

  1. Özel bir formülle simgeler, renk ölçekleri veya veri çubukları uygulayamazsınız. Sayı biçimleri, yazı tipi, dolgu rengi ve kenarlık seçenekleri dahil olmak üzere standart hücre biçimlendirmesiyle sınırlısınız.
  2. Koşullu biçimlendirme ölçütleri için birleşimler, kesişimler veya dizi sabitleri gibi belirli formül yapılarını kullanamazsınız.
  3. Koşullu biçimlendirme formülünde diğer çalışma kitaplarına başvuramazsınız.

Bazen #2 ve #3 civarında çalışabilirsiniz. Formülün mantığını çalışma sayfasındaki bir hücreye taşıyabilir ve bunun yerine formülde o hücreye başvurabilirsiniz. Bir dizi sabiti kullanmaya çalışıyorsanız, bunun yerine adlandırılmış bir aralık oluşturmayı deneyin.

Daha fazla CF formül kaynağı

  • 30'dan fazla koşullu biçimlendirme formülü örneği
  • Pratik çalışma sayfalarıyla video eğitimi
Yazar Dave Bruns


^