Değerleri, uygun olan kategori rengine göre renklendirmek

[Levent Menteşoğlu]

Bu başlığa ilk soruyuda ben sormak istiyorum.

Aşağıdaki resimde görüldüğü gibi A sütununda malzemeler ve B sütununda da bu malzemelere karşılık gelen sayısal değerler mevcuttur. D sütununda kategorilere ayrılacak malzemelerin alacağı renkler, E sütunundada kategori alt limitleri mevcuttur.

Sorumuz; A ve B sütunlarındaki verileri E sütunundaki alt limit değerleri dikkate alınarak D sütunundaki renklere göre nasıl renklendirebiliriz? Oluşmasını istediğimiz görünüm G ve H sütundaki gibi olacaktır. Soruya hem fonksiyonlar hemde VBA ile üreteceğiniz çözümlerinizi paylaşmanızı rica ederim. Çözümünüze yardımcı olması açısından ayrıca bir dosyada ekliyorum. En sonunda bende kendi üretmiş olduğum çözümü sizlerle paylaşacağım.

 

[Recep İpek]

VBA ile yapılmış bir çözümü aşağıda bulabilirsiniz.
Burada hücrenin limitten büyük olması ve renkli olmaması mantığı kullanılmıştır.

Sub Renklendir()
Range("a2:b1001").Interior.ColorIndex = xlNone  [color=green]'a2:b1001 alanının zemin renklerini sil.[/color]
For i = 2 To 16                                 [color=green]'Limitlerin bulunduğu hücre satırına göre döngü oluştur.[/color]
limit = Cells(i, "e").Value                     [color=green]'Limit tutarını limit değişkenine ata.[/color]
renkkodu = Cells(i, "d").Interior.ColorIndex   [color=green] 'Renk kodunu renkkodu değişkenine ata.[/color]
    For j = 2 To Range("a65536").End(xlUp).Row [color=green] 'B kolonundaki son veriye kadar döngü oluştur.[/color]
    If Cells(j, "b").Value >= limit And Cells(j, "b").Interior.ColorIndex = xlNone Then
    [color=green]'Eğer seçili hücrenin tutarı limit'e eşit veya büyük ise ve seçili hücre renkli değil ise[/color]
    Range(Cells(j, "a"), Cells(j, "b")).Interior.ColorIndex = renkkodu
    [color=green]'Seçili hücre ve solonudaki hücrenin zemin rengini renkkodu yap.[/color]
    End If
    [color=green]'Eğer Bitimi[/color]
    Next j
    [color=green]'2.Döngü Devamı[/color]
Next i
[color=green]'1.Döngü Devamı[/color]
MsgBox "Bitti"
[color=green]'Tüm döngüler bitince uyarı ver.[/color]
End Sub

 

[lsuersoy]

Sayın Leventm Coditional Formating ile Excel 2007 de çok rahat çözüm

Merhabalar , Excel 2007 de Conditional Formating ile bu sorun çok kısa bir sürede çözümlenebilir.

Excel 2007 ile bende yeni tanıştım..Sorunuzu görünce araştırdım ve buldum...2003 de 3 den fazla koşul belirtilemezken Excel 2007 de sınırsız (gibi geldi bana) koşul ve biçim tanımlamak mümkün...

Excel 2007 de Home MEnüsünden (Ribbon olarak anılmakta) , Conditional Formating / Highlight Cells Rules / More Rules / ekranından gerekli koşul ve biçimleri verebilirsiniz..

kolay gelsin , iyi çalışmalar...

 

[Levent Menteşoğlu]

Öncelikle Sn ripek ve Sn Isuersoy’a önerileri nedeniyle teşekkür ederim. Sn Isuersoy’un “Koşullu Biçimlendirme-Conditional Formatting” çözüm öneriside elbette geçerli bir yöntem olabilir. Ancak renk sayısının fazla olması, Excel 2007 kullanımını zorunlu kıldığı gibi (bu bilgiyide bu başlıkta öğrenmiş oldum.) çok sayıda hücrede “Koşullu Biçimlendirme” uygulanması sebebi ile dosya çok hantallaşacak ve büyüklüğüde çok artacaktır. Bu durum dikkate alındığında bu çözümün uygulamada pek pratik olmayacağı söylenebilir.

Bu durumda en ideal çözüm bana göre VBA dan istifade etmektir. VBA ile çözümde de Sn ripek’in önerisi düşünülecek ilk çözümdür.

Başka çözüm yolu gelmediğine göre bende VBA ile hazırladığım kendi çözümümü sizlerle paylaşmak istiyorum.

Daha önce muhtelif başlıklarda VBA ile ilgilenmeden önce Excelin zengin ve pratik fonksiyon altyapısı hakkında yeterli düzeyde tecrübe sahibi olunmasının önemli olduğunu vurgulamıştım. Bu fonksiyonlarla kurulacak mantık VBA’da da aynı şekilde uygulanarak çok hızlı ve pratik çözümler üretmek mümkündür. Bende böyle bir mantıktan hareket ederek, çözümümü Excelin kendi fonksiyonlarından olan “KAÇINCI-MATCH” fonksiyonu üzerine kurdum. Öncelikle bu fonksiyonun özellikleri hakkında kısa bir bilgi vermek istiyorum. (Bu bilgi Excelin kendi yardım menülerinde alınmıştır.)

KAÇINCI-MATCH Fonksiyonu: Bir dizide belirtilen sıradaki, belirtilen değerle eşleştirilen öğenin göreli konumunu verir. Öğenin kendisine değil, ama öğenin aralıktaki konumuna gerek duyduğunuzda ARA fonksiyonlarından birinin yerine KAÇINCI fonksiyonunu kullanın.

KAÇINCI(aranan_değer;aranan_dizi;eşleştir_tür)

Eşleştir_tür -1, 0 ya da 1 sayısıdır. Eşleştir_tür, Microsoft Excel'in aranan_değeri aranan_dizideki değerlerle nasıl eşleştirdiğini belirler.

Eşleştir_tür 1 ise, KAÇINCI fonksiyonu aranan_değerden küçük ya da eşit olan en büyük değeri bulur. Aranan_dizi artan sırada yerleştirilmelidir.

Eşleştir_tür 0 ise, KAÇINCI fonksiyonu aranan_değere tam olarak eşit olan ilk değeri bulur. Aranan_dizi herhangi bir sırada olabilir.

Eşleştir_tür -1 ise, KAÇINCI fonksiyonu aranan_değerden büyük ya da eşit olan en küçük değeri bulur. Aranan_dizi azalan sırada yerleştirilmelidir.

Bu fonksiyon VBA içinde aşağıdaki şekilde kullanılmaktadır.

“worksheetfunction.match(aranan_değer;aranan_dizi;eşleştir_tür)”

Aşağıda verdiğim Kod, alt limit değerlerini bir döngü içinde seçip, her limitin “koşula uyan büyük ya da eşit olan en küçük değerinin” B sütununda bulunması prensibine dayanmaktadır. (örneğin, 1.000.000.000 limiti için bulunacak değer 1.001.938.583 dir) bu kritere uygun değerde MATCH fonksiyonu ile bulunmaktadır. Böylece sadece alt limit değerlerininin taranmasında kullanılan tek bir döngü ile çözüm elde edilmiştir. MATCH fonksiyonu, B sütunundaki tüm değerlerinin bir döngü ile tarama zorunluluğunu ortadan kaldırarak hızlı bir şekilde sonuca ulaşılmasını sağlamıştır. Özellikle çok sayıda veri olan bir veritabanında arama yaparken bu fonksiyonun kullanımı çok ciddi bir avantaj getirecektir.

Yukarıdaki açıklamanın ardından tasarlamış olduğum kodu aşağıda veriyorum.

Sub renklendir()
ilk = 2 [SIZE=2][COLOR=green]'ilk alt limit değerinin bulunduğu satır nosudur.[/COLOR][/SIZE]
For a = 2  To [d65536].End(3).Row [SIZE=2][COLOR=green]'döngünün başlangıç satırı 2 olup bitiş satırı [d65536].end(3).row komutu ile bulunmaktadır.[/COLOR][/SIZE]
son = WorksheetFunction.Match(Cells(a, "e"), [b:b], -1) [SIZE=2][COLOR=green]'alt limite göre uygun olan değerin B sütunundaki sırasını verir.[/COLOR][/SIZE]
Range("a" & ilk & ":b" & son).Interior.ColorIndex = Cells(a, "d").Interior.ColorIndex [SIZE=2][COLOR=green]'renklendirlecek alanı belirler ve kriterdeki renge göre renklendirir.[/COLOR][/SIZE]
ilk = son + 1 [SIZE=2][COLOR=green]'renklendirilecek alanın ilk hücresinin satır değerini belirler.[/COLOR][/SIZE]
Next
End Sub

 

[yanar17]

Çözümlere bakmadan evvel soruda kendimi sınadım.
İlk olarak basit bir düşünme ile koşullu biçimlendirme geldi aklıma fakat 3 koşuldan fazlası olmadı.
Daha sonra vb koduyla bunun çözülebileceğini düşündüm. Çünkü daha önceden "renk işlem" diye bir çalışma vardı onun üzerinde inceleme yapmıştım. Ordaki vb kodları gibi yeni bir düzenleme ile bunu çözülebi,leceğini düşündüm.

Sayın "Recep İpek" ve "Isuersoy" arkadaşlarımın cevaplarını okuyunca düşüncemin doğru olduğunu gördüm.

Ama gerçek olan bişey var. Bendeniz vb kodu yazmada yetersizim. Kodları okuya biliyorum. Kopyalayarak bişeyler yapıyorum kendi çapımda. Daha kırk fırın ekmek yememiz lazım. Ama problem benim için eğiticiydi. Soruyu soran ve çözümünde beyin fırtınasına katılan arkadaşlara teşekkür ederim.

Sağlıcakla dostça kalın. Sebahattin YANAR Çanakkale

 

[PriveT]

Başka çözüm yolu gelmediğine göre bende VBA ile hazırladığım kendi çözümümü sizlerle paylaşmak istiyorum.

Daha önce muhtelif başlıklarda VBA ile ilgilenmeden önce Excelin zengin ve pratik fonksiyon altyapısı hakkında yeterli düzeyde tecrübe sahibi olunmasının önemli olduğunu vurgulamıştım. Bu fonksiyonlarla kurulacak mantık VBA’da da aynı şekilde uygulanarak çok hızlı ve pratik çözümler üretmek mümkündür. Bende böyle bir mantıktan hareket ederek, çözümümü Excelin kendi fonksiyonlarından olan “KAÇINCI-MATCH” fonksiyonu üzerine kurdum. Öncelikle bu fonksiyonun özellikleri hakkında kısa bir bilgi vermek istiyorum. (Bu bilgi Excelin kendi yardım menülerinde alınmıştır.)

KAÇINCI-MATCH Fonksiyonu: Bir dizide belirtilen sıradaki, belirtilen değerle eşleştirilen öğenin göreli konumunu verir. Öğenin kendisine değil, ama öğenin aralıktaki konumuna gerek duyduğunuzda ARA fonksiyonlarından birinin yerine KAÇINCI fonksiyonunu kullanın.

KAÇINCI(aranan_değer;aranan_dizi;eşleştir_tür)

Eşleştir_tür -1, 0 ya da 1 sayısıdır. Eşleştir_tür, Microsoft Excel'in aranan_değeri aranan_dizideki değerlerle nasıl eşleştirdiğini belirler.

Eşleştir_tür 1 ise, KAÇINCI fonksiyonu aranan_değerden küçük ya da eşit olan en büyük değeri bulur. Aranan_dizi artan sırada yerleştirilmelidir.

Eşleştir_tür 0 ise, KAÇINCI fonksiyonu aranan_değere tam olarak eşit olan ilk değeri bulur. Aranan_dizi herhangi bir sırada olabilir.

Eşleştir_tür -1 ise, KAÇINCI fonksiyonu aranan_değerden büyük ya da eşit olan en küçük değeri bulur. Aranan_dizi azalan sırada yerleştirilmelidir.

Bu fonksiyon VBA içinde aşağıdaki şekilde kullanılmaktadır.

“worksheetfunction.match(aranan_değer;aranan_dizi;eşleştir_tür)”

Aşağıda verdiğim Kod, alt limit değerlerini bir döngü içinde seçip, her limitin “koşula uyan büyük ya da eşit olan en küçük değerinin” B sütununda bulunması prensibine dayanmaktadır. (örneğin, 1.000.000.000 limiti için bulunacak değer 1.001.938.583 dir) bu kritere uygun değerde MATCH fonksiyonu ile bulunmaktadır. Böylece sadece alt limit değerlerininin taranmasında kullanılan tek bir döngü ile çözüm elde edilmiştir. MATCH fonksiyonu, B sütunundaki tüm değerlerinin bir döngü ile tarama zorunluluğunu ortadan kaldırarak hızlı bir şekilde sonuca ulaşılmasını sağlamıştır. Özellikle çok sayıda veri olan bir veritabanında arama yaparken bu fonksiyonun kullanımı çok ciddi bir avantaj getirecektir.

Yukarıdaki açıklamanın ardından tasarlamış olduğum kodu aşağıda veriyorum.

Sub renklendir()
ilk = 2 [SIZE=2][COLOR=green]'ilk alt limit değerinin bulunduğu satır nosudur.[/COLOR][/SIZE]
For a = 2  To [d65536].End(3).Row [SIZE=2][COLOR=green]'döngünün başlangıç satırı 2 olup bitiş satırı [d65536].end(3).row komutu ile bulunmaktadır.[/COLOR][/SIZE]
son = WorksheetFunction.Match(Cells(a, "e"), [b:b], -1) [SIZE=2][COLOR=green]'alt limite göre uygun olan değerin B sütunundaki sırasını verir.[/COLOR][/SIZE]
Range("a" & ilk & ":b" & son).Interior.ColorIndex = Cells(a, "d").Interior.ColorIndex [SIZE=2][COLOR=green]'renklendirlecek alanı belirler ve kriterdeki renge göre renklendirir.[/COLOR][/SIZE]
ilk = son + 1 [SIZE=2][COLOR=green]'renklendirilecek alanın ilk hücresinin satır değerini belirler.[/COLOR][/SIZE]
Next
End Sub

Hocam paylasimlariniz harika, cok tesekkurler.
Yalniz bu paylasiminizda ya ben istenilen seyi tam anlamadim ya da bir hata var.
B sutunundaki degerleri buyukten kucuge siralamissiniz. Ama malzeme degerleri boyle sirali olmak zorunda olmamali.

Mesela A1 degerini 1,000,000,000 yaptigimda alttaki satirlarin degeri her ne kadar 1,000,000,000`dan fazla da olsa bir alt kademenin renginialiyor. Ve diger alt kademe renkleri de kendi altindaki renk kodunu aliyor.
A1 degerini 1,000,000,000`in altina cektigimizde ise makro 1004 hatasi veriyor ve WorksheetFunction.Match ozelligi alinamiyor diyor.

Bu probleme de bir coum bulacaginiza eminim.
Paylasimlarinz icin tekrar tesekkurler...