Excel Forum
ALTIN ÜYELİK Hakkında Bilgi


Geri Git   Excel Forum > EXCEL-Soruları > Beyin Fırtınası
Atatürk
Şifremi Unuttum

DUYURU SİSTEMİ / REKLAM PANOSU

Beyin Fırtınası Bu başlıkta, ilginç olduğunu düşündüğünüz sorularınızı, bir problem şeklinde sorabilir, alternatif olduğunu düşündüğünüz çözümlerinizi paylaşabilirsiniz. (Bu başlıkta yeni konu açılması onaya bağlıdır.)
Dosya ekleyebilirsiniz

Özel Arama


Yanıtla
 
Paylaş Konu Araçları Görünüm Modları
Eski 07-04-2007, 00:29   #1
Levent Menteşoğlu
Administrator
 
Levent Menteşoğlu kullanıcısının avatarı
 
Giriş: 13/10/2004
Şehir: Çorlu
Mesaj: 15,896
Excel Vers. ve Dili:
Excel 2010-Türkçe
Varsayılan Değerleri, uygun olan kategori rengine göre renklendirmek

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.

Eklenmiş Dosyalar
Dosya Türü: zip kategori-soru.zip (37.9 KB, 110 Görüntülenme)
__________________
FORUM KURALLARI



"Biz burada hep beraber, sevginin,saygının, alınterinin, mutluluğun makrosunu yazıyoruz. " Kaylan

Bu mesaj en son " 08-11-2008 " tarihinde saat 21:59 itibariyle Levent Menteşoğlu tarafından düzenlenmiştir.... Neden: dosyanın tekrar eklenmesi
Levent Menteşoğlu Çevrimdışı   Alıntı Yaparak Cevapla
Eski 07-04-2007, 01:25   #2
Recep İpek
Uzman
 
Recep İpek kullanıcısının avatarı
 
Giriş: 14/02/2006
Şehir: İstanbul
Mesaj: 3,439
Excel Vers. ve Dili:
( Excel 2010 - İngilizce )
Varsayılan

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.


Kod: (Rahat kopyalayabilmeniz için tüm kodu seçmenizi sağlar)
Sub Renklendir()
Range("a2:b1001").Interior.ColorIndex = xlNone  'a2:b1001 alanının zemin renklerini sil.
For i = 2 To 16                                 'Limitlerin bulunduğu hücre satırına göre döngü oluştur.
limit = Cells(i, "e").Value                     'Limit tutarını limit değişkenine ata.
renkkodu = Cells(i, "d").Interior.ColorIndex    'Renk kodunu renkkodu değişkenine ata.
    For j = 2 To Range("a65536").End(xlUp).Row  'B kolonundaki son veriye kadar döngü oluştur.
    If Cells(j, "b").Value >= limit And Cells(j, "b").Interior.ColorIndex = xlNone Then
    '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
    Range(Cells(j, "a"), Cells(j, "b")).Interior.ColorIndex = renkkodu
    'Seçili hücre ve solonudaki hücrenin zemin rengini renkkodu yap.
    End If
    'Eğer Bitimi
    Next j
    '2.Döngü Devamı
Next i
'1.Döngü Devamı
MsgBox "Bitti"
'Tüm döngüler bitince uyarı ver.
End Sub
Eklenmiş Dosyalar
Dosya Türü: zip kategori-soru01.zip (42.3 KB, 85 Görüntülenme)
__________________
www.excel.web.tr
Recep İpek Çevrimdışı   Alıntı Yaparak Cevapla
Eski 09-04-2007, 16:49   #3
lsuersoy
 
lsuersoy kullanıcısının avatarı
 
Giriş: 29/06/2005
Şehir: İstanbul
Mesaj: 158
Excel Vers. ve Dili:
MS Office Professional Plus 2013 64 Bit -Eng MSSQL 2012 R2 64 Bit
Varsayılan 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...
Eklenmiş Resimler
Dosya Türü: jpg Excel_2007_Conditional_Formating_02.JPG (29.2 KB, 105 Görüntülenme)
Dosya Türü: jpg Excel_2007_Conditional_Formating_03.JPG (38.2 KB, 38 Görüntülenme)
__________________
[SIZE=1][B][COLOR=Red]MS Office Professional Plus 2013 ENGLISH , MS SQL Server 2012 R2 , Visual Studio 2010 , Logo Tiger Plus 2.29
[/COLOR][/B][/SIZE]

Bu mesaj en son " 09-04-2007 " tarihinde saat 17:16 itibariyle lsuersoy tarafından düzenlenmiştir.... Neden: Açıklamaların yetersiz olması
lsuersoy Çevrimdışı   Alıntı Yaparak Cevapla
Eski 09-04-2007, 18:47   #4
Levent Menteşoğlu
Administrator
 
Levent Menteşoğlu kullanıcısının avatarı
 
Giriş: 13/10/2004
Şehir: Çorlu
Mesaj: 15,896
Excel Vers. ve Dili:
Excel 2010-Türkçe
Varsayılan

Ö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.

Kod: (Rahat kopyalayabilmeniz için tüm kodu seçmenizi sağlar)
Sub renklendir()
ilk = 2 'ilk alt limit değerinin bulunduğu satır nosudur.
For a = 2  To [d65536].End(3).Row 'döngünün başlangıç satırı 2 olup bitiş satırı [d65536].end(3).row komutu ile bulunmaktadır.
son = WorksheetFunction.Match(Cells(a, "e"), [b:b], -1) 'alt limite göre uygun olan değerin B sütunundaki sırasını verir.
Range("a" & ilk & ":b" & son).Interior.ColorIndex = Cells(a, "d").Interior.ColorIndex 'renklendirlecek alanı belirler ve kriterdeki renge göre renklendirir.
ilk = son + 1 'renklendirilecek alanın ilk hücresinin satır değerini belirler.
Next
End Sub
Eklenmiş Dosyalar
Dosya Türü: zip kategori-çözüm.zip (48.4 KB, 128 Görüntülenme)
__________________
FORUM KURALLARI



"Biz burada hep beraber, sevginin,saygının, alınterinin, mutluluğun makrosunu yazıyoruz. " Kaylan

Bu mesaj en son " 08-11-2008 " tarihinde saat 22:01 itibariyle Levent Menteşoğlu tarafından düzenlenmiştir.... Neden: dosyanın tekrar eklenmesi
Levent Menteşoğlu Çevrimdışı   Alıntı Yaparak Cevapla
Eski 09-07-2008, 11:37   #5
yanar17
 
yanar17 kullanıcısının avatarı
 
Giriş: 20/11/2005
Mesaj: 27
Smile

Çö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
__________________
>::<~123

excel 2003 tr
yanar17 Çevrimdışı   Alıntı Yaparak Cevapla
Eski 02-02-2011, 14:31   #6
PriveT
 
Giriş: 08/10/2009
Şehir: Moskova
Mesaj: 31
Excel Vers. ve Dili:
2010
Exclamation

Alıntı:
Levent Menteşoğlu tarafından gönderildi Mesajı Görüntüle
[font=Times New Roman][font=Verdana]

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.

Kod: (Rahat kopyalayabilmeniz için tüm kodu seçmenizi sağlar)
Sub renklendir()
ilk = 2 'ilk alt limit değerinin bulunduğu satır nosudur.
For a = 2  To [d65536].End(3).Row 'döngünün başlangıç satırı 2 olup bitiş satırı [d65536].end(3).row komutu ile bulunmaktadır.
son = WorksheetFunction.Match(Cells(a, "e"), [b:b], -1) 'alt limite göre uygun olan değerin B sütunundaki sırasını verir.
Range("a" & ilk & ":b" & son).Interior.ColorIndex = Cells(a, "d").Interior.ColorIndex 'renklendirlecek alanı belirler ve kriterdeki renge göre renklendirir.
ilk = son + 1 'renklendirilecek alanın ilk hücresinin satır değerini belirler.
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...
PriveT Çevrimdışı   Alıntı Yaparak Cevapla
Yanıtla


Konu Araçları
Görünüm Modları

Gönderme Kuralları
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is Açık
SimgelerAçık
[IMG] kodu Açık
HTML kodu Kapalı


Saat 12:16


Bu forum Elit NET - www.elitnet.com.tr tarafından sunulmaktadır.

Excel Eğitimi - Mobil Uygulama - Çorlu - Çorlu Web Tasarım - Tarot Falı - invest in turkey - Lingerie - Dyeing Machine - Karton Bardak- Çorlu Özel Eğitim- Site Yönetimi- Led Aydınlatma- Pronet Tekirdağ- Çorlu Kamera- Pronet Edirne- Pronet Kırklareli- Pronet Çerkezköy- Pronet Çorlu- Pronet Lüleburgaz- Pronet Keşan- Çorlu Araç Takip- Çorlu Su Arıtma- Boru Profil- Gebze Emlak- Beylikdüzü Temizlik- İstanbul Burun Estetiği- Bakır Sülfat- Rampa- Rotary- Çorlu İnternet Sitesi- youngblood- Çorlu Palet- Çerkezköy Palet- Çorlu Prefabrik- Çorlu Sürücü Kursu- Çorlu Sandviç Panel- Şişli Avukat- Korona Test Kalemi- Çorlu Vinç- Çorlu Pimapen Tamiri-
Powered by vBulletin Version 3.7.2
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Advertisement System V2.6 By   Branden