VBA Döngülerle Koşullu Biçimlendirme

Bu yazıda vba ile minimum ve maximum değerlerin renklendirilmesi

Yapacağımız örnekte ay bazında çeşitli ürünlerin satış adetleri mevcut ve bizden istenilen her ay en çok ve en az satan ürünlerin belirtilmesi.

En çok satan ürünü sarı, en az satan ürünü ise kırmızı ile renklendireceğiz.

	A	B	C	D	E	F	G
1	aylar	ürün-1	ürün-2	ürün-3	ürün-4	ürün-5
2	ocak	62	56	65	42	52
3	şubat	32	76	62	54	74
4	mart	59	39	29	70	73
5	nisan	60	36	69	58	27
6	mayıs	69	40	54	38	32
7	haziran	80	56	72	52	39
8	temmuz	52	39	37	34	51
9	ağustos	32	20	68	41	33
10	eylül	47	28	67	20	75
11	ekim	50	53	71	72	32
12	kasım	30	23	66	26	25
13	aralık	26	49	42	36	21
14
15

 

Burada her satırı bağımsız bir yapı olarak ele alıp her satırın içindeki hücrelerde dolaşarak maximum ve minimum değerlerini bulmamız gerekiyor.

Sub renklendir()

 

Dim rng As Range

 

Dim i As Integer

 

For i = 2 To 13 'i değeri satır numaramızı verecek A2 ile A13 arasındaki tüm satırlarda sırayla dolaşacağız.

 

For Each rng In Range("A" & i & ":" & "F" & i)

 

If rng.Value = Application.WorksheetFunction.Max(Range("A" & i & ":" & "F" & i)) Then

'üstünde bulunduğun hücre bu aralıklatki en büyük hücre ise

rng.Interior.Color = vbYellow 'rengini sarıya boya

 

ElseIf rng.Value = Application.WorksheetFunction.Min(Range("A" & i & ":" & "F" & i)) Then

'üstünde bulunduğun hücre bu aralıklatki en küçük hücre ise

rng.Interior.Color = vbRed 'rengini kırmızıya boya

 

End If

 

Next rng

 

Next i 'bir sonraki satıra geç

 

End Sub



vba max min

VBA-FOR EACH Döngüsü

For each bir nesnenin tüm elemanlarına ulaşmak ve o elemanların içinde dönmemizi sağlayan bir döngüdür.

Belli bir hücre aralığındaki tüm hücrelere istedğimiz bir metni yazdıralım:

 

Sub isimyazdır()

Dim rng As Range

For Each rng In Range("a1:a20")

rng.Value = "excelyardimcim@blogspot.com"

Next rng

End Sub

vba for each

 

Şimdi biraz daha farklı bir örnek üzerinden gidelim:

For each döngüsüyle 1'den 20 ye kadar sayıları sıralamak

Sub arttır()

 

Dim rng As Range

 

Dim sayac As Integer

 

sayac = 1

 

For Each rng In Range("a1:a20")

 

rng.Value = sayac      'ilk hücreme sayacın ilk değeri olan "1" i yazdırdık

 

sayac = sayac + 1       'her bir hücreden sonra sayacın değerini 1 arttırıyoruz. 

 

Next rng

 

End Sub

 

vba for aech

Not: Formül içine yorum yazmak isterseniz yukarıda yaptığım gibi yorumun başına tek tırnak koyarsanız formül bozulmaz.

Koşullu min,mak,ortalama, toplama fonksiyonları - DİZİLER

Burada belli bir koşul vererek ve dizilerden yararlanarak MIN, MAK, TOPLA, ORTALAMA fonksiyonlarının kullanımını öğreneceğiz.

İlk olarak bu fonksiyonların basit kullanımlarını hatırlayalım:

Belli bir grubun min değerini bulmak için: =MİN(aralık)

Belli bir grubun mak değerini bulmak için: =MAK(aralık)

Belli bir grubun topla değerini bulmak için: =TOPLA(aralık)

Belli bir grubun ortalama değerini bulmak için: =ORTALAMA(aralık)

	A	B	C	D	E	F
1
2
3	isim	yaş	istenen	sonuç	formül
4	Ali	10	grup içindeki en küçük yaş :	7	=MİN(B4:B7)
5	Ahmet	13	grup içindeki en büyük yaş :	14	=MAK(B4:B7)
6	Duygu	7	grup içindeki yaşların toplamı :	44	=TOPLA(B4:B7)
7	Ceren	14	grup içindeki yaşların ortalaması :	11	=ORTALAMA(B4:B7)
8
9

Şimdi aşağıdaki gelişmiş örneği açıklayarak ilerleyelim

 

Bir grup meyve ve meyvelerin değişik manavlardaki kg fiyatları mevcut.

	A	B	C	D
1	manav	meyve	manav kg/fiyat
2	A	çilek	10,00 TL
3	B	çilek	13,00 TL
4	C	çilek	7,00 TL
5	D	çilek	14,00 TL
6	A	elma	4,00 TL
7	B	elma	8,00 TL
8	A	karpuz	3,00 TL
9	B	karpuz	4,00 TL
10	C	karpuz	5,00 TL
11	A	vişne	5,00 TL
12	B	vişne	9,00 TL
13	C	vişne	8,00 TL
14	D	vişne	6,00 TL
15	E	vişne	5,00 TL
16	F	vişne	4,00 TL
17	G	vişne	5,00 TL
18

1- Meyve bazında ortalama fiyatları bulalım:

{=ORTALAMA(EĞER(E2=B$2$:B$17$;C$2:C$17))} 'eğer fonksiyonunun içerisinde meyve isimlerinin aynı olduğu satırları bulduk, Sonra C sütununa gidip bu satırların ortalamasını aldık.

Burada önemli nokta bu biz dizi fonksiyonudur ve formülü yazdıktan sonra Ctrl+Shift+Enter ile formülü dizi fonksiyonu haline getirmeliyiz.

2- Meyve Bazında MİN fiyatı bulalım:

{=MİN(EĞER(E9=B$2$:B$17$;C$2:C$17))}

3- Meyve Bazında MAK fiyatı bulalım:

{=MAK(EĞER(E16=B$2$:B$17$;C$2:C$17))}

4- Meyve Bazında fiyatları toplayalım:

{=TOPLA(EĞER(E23=B$2$:B$17$;C$2:C$17))}

5- İşi biraz daha karmaşık hale getirelim meyve bazında ortalama fiyatın üzerindeki min fiyatları bulalım yani çileğin ortalam fiyatı 11tl, 11tl nin üzerindeki min satış fiyatı çilek için 13 tl bu rakama ulaşalım:

yani {=Min(Eğer(E30=B$2$:B$17$;EĞER($C$2:$C$17>ORTALAMA(EĞER(E30=B$2$:B$17$;C$2:C$17));C$2:C$17)))}

burada çift koşul girdik yani meyve isimleri aynı olan satırları al, bu satırlarda o meyvenin ortalaması üzerindeki satırları al ve ortalamnın üzerindeki satırların min.unu bul.

örneğin çilek fiyatları 10,13,7,14, çileğin ortalama fiyatı 11tl, 11tl nin üzerindeki fiyatlar 13tl ve 14tl bunların min. değeri 13tl. şeklinde.