KAYDIR (OFFSET) FONKSİYONU VE BAĞ_DEĞ_DOLU_SAY (COUNTA) İLE KULLANILARAK DİNAMİK ALAN OLUŞTURMAK

KAYDIR (OFFSET) FONKSİYONU VE BAĞ_DEĞ_DOLU_SAY İLE KULLANILARAK DİNAMİK ALAN OLUŞTURMAK

Öncelikle KAYDIR( OFFSET) fonksiyonun işlevinden bahsedelim. Bu fonksiyon Basit olarak referans olarak verdiğimiz hücreden yine bizim belirlediğimiz satır ve sütun uzaktaki

hücrenin değerini döndürür.

=KAYDIR(başv; satırlar; sütunlar; [yükseklik]; [genişlik])

Başv : referans (başlangıç) hücremiz

Satırlar: referans hücreden kaç satır uzağa gideceğimiz

Sütunlar: referans hücreden kaç sütun uzağa gideceğimiz

[yükseklik]: dönecek sonuçtaki satır sayısı (yazılmaz ise excel default değer olarak 1 atar)

[genişlik]:  dönecek sonuçtaki sütun sayısı (yazılmaz ise excel default değer olarak 1 atar)

Burada satırlar ve sütunlar eksi ve ya artı değerler olabilir. Eksiler sola ve yukarıya, artılar sağa ve aşağıya anlamına gelir.

Aşağıdaki örnekle pekiştirmeye çalışalım:

Referans hücre olarak "C4" ü kullanacağız.

	A	B	C	D	E
1			2
2			3
3			4
4	13	12	5	9	10
5			6
6			7
7			8
8
9	7	=KAYDIR(C4;2;0)
10	3	=KAYDIR(C4;-2;0)
11	10	=KAYDIR(C4;0;2)
12	12	=KAYDIR(C4;0;-1)
13	#BAŞV!	=KAYDIR(C4;2;-3)
14

Son yazdığımız formül #BAŞV! Hatası verdi çünkü C sütunundan sonra en fazla 2 sütun geriye gidebilir max A sütununa gelebiliriz.

Şimdi 2. Konumuza geçelim KAYDIR (OFFSET) fonksiyonu ile dinamik alan tanımlama:

Burada BAĞ_DEĞ_DOLU_SAY yani seçilen aralıktaki dolu hücrelerin sayısını döndüren fonksiyonumuzdan yaralanacağız.

 

Kaydır fonksiyonu ile yukarıda açıkladığımız gibi tek bir hücrenin sonucunu döndürebileceğimiz gibi [yükseklik] ve [genişlik] girdilerini değiştirerek bir hücre aralığından da bahsedebiliriz.

Ancak hücre aralığının sonucunu tek bir sonuç olarak döndüremeyeceğimiz için bu kısmı KAYDIR ile beraber TOPLAM,MAX,MİN,ORTALAMA gibi fonksiyonlarla tamamlayacağız.

	A	B	C	D	E
1		aylar	satış adetleri	satışlar
2		ocak	26	1452
3		şubat	57	1287
4		mart	23	868
5		nisan	35	980
6		mayıs	66	1493
7		haziran	32	1071
8		temmuz	70	1415
9		ağustos	49	856
10		eylül	35	899
11
12		26	=KAYDIR(B2;0;1;1;1)
13		980	=KAYDIR(B2;3;2;1;1)
14		393	=TOPLA(KAYDIR(B2;0;1;BAĞ_DEĞ_DOLU_SAY(B2:B10);1))
15		10714	=TOPLA(KAYDIR(B2;0;1;BAĞ_DEĞ_DOLU_SAY(B2:B10);2))
16

 

393	=TOPLA(KAYDIR(B2;0;1;BAĞ_DEĞ_DOLU_SAY(B2:B10);1))

 

393 = C2:C10  tüm satış adetlerinin toplamı,

 

Formülü açıklayalım:

 

B2: B2 hücresini referans hücre olarak al.

 

0: satır atlama

 

1: 1 sütun sağa kay  (buraya kadarki işlem sonucu bize C2 hücresini yani 26 sonucunu verecek)

 

BAĞ_DEĞ_DOLU_SAY(B2:B10) : şimdi aynı formülü tekrar uygula referans hücre olarak sırayla B2 den B10 a kadar hepsini al.

 

*Daha da açıklayıcı olması için arka planda yapılan işlem sırasını gösterelim:

 

	A	B	C	D	E	F
1		aylar	satış adetleri	satışlar
2		ocak	26	1452
3		şubat	57	1287
4		mart	23	868
5		nisan	35	980
6		mayıs	66	1493
7		haziran	32	1071
8		temmuz	70	1415
9		ağustos	49	856
10		eylül	35	899
11
12		26	=KAYDIR(B2;0;1)
13		57	=KAYDIR(B3;0;1)
14		23	=KAYDIR(B4;0;1)
15		35	=KAYDIR(B5;0;1)
16		66	=KAYDIR(B6;0;1)
17		32	=KAYDIR(B7;0;1)
18		70	=KAYDIR(B8;0;1)
19		49	=KAYDIR(B9;0;1)
20		35	=KAYDIR(B10;0;1)
21		393	=TOPLA(B12:B20)
22

10714

=TOPLA(KAYDIR(B2;0;1;BAĞ_DEĞ_DOLU_SAY(B2:B10);2))

 

10.794= C2:D10 toplamı,

Formülü açıklayalım:

 

B2: B2 hücresini referans hücre olarak al.

 

0: satır atlama

 

1: 1 sütun sağa kay  (buraya kadarki işlem sonucu bize C2 hücresini yani 26 sonucunu verecek)

 

BAĞ_DEĞ_DOLU_SAY(B2:B10) : şimdi aynı formülü tekrar uygula referans hücre olarak sırayla B2 den B10 a kadar hepsini al. Sütun u bir kaydır.

 

2: Aynı işlemleri tekrar yap bu kez sütunu 2 kaydır.

 

Bu formülünde arka planda nasıl çalıştığını görelim:

 

	A	B	C	D	E	F	G
1		aylar	satış adetleri	satışlar
2		ocak	26	1452
3		şubat	57	1287
4		mart	23	868
5		nisan	35	980
6		mayıs	66	1493
7		haziran	32	1071
8		temmuz	70	1415
9		ağustos	49	856
10		eylül	35	899
11
12		26	=KAYDIR(B2;0;1)	1.452	=KAYDIR(B2;0;2)
13		57	=KAYDIR(B3;0;1)	1.287	=KAYDIR(B3;0;2)
14		23	=KAYDIR(B4;0;1)	868	=KAYDIR(B4;0;2)
15		35	=KAYDIR(B5;0;1)	980	=KAYDIR(B5;0;2)
16		66	=KAYDIR(B6;0;1)	1.493	=KAYDIR(B6;0;2)
17		32	=KAYDIR(B7;0;1)	1.071	=KAYDIR(B7;0;2)
18		70	=KAYDIR(B8;0;1)	1.415	=KAYDIR(B8;0;2)
19		49	=KAYDIR(B9;0;1)	856	=KAYDIR(B9;0;2)
20		35	=KAYDIR(B10;0;1)	899	=KAYDIR(B10;0;2)
21		393	=TOPLA(B12:B20)	10.321	=TOPLA(D12:D20)	10.714	=B21+D21
22

Neden DİNAMİK hücre aralığı diyoruz?

Burada BAĞ_DEĞ_DOLU_SAY kullanarak dinamik bir formül elde etmemizin nedeni tablomuza bir satır eklendiğinde formül veri aralığını otomatik olarak genişletecektir.

XXX satırını ekledikten sonra formül otomatik olarak veri aralığını genişletti.

	A	B	C	D	E
1		aylar	satış adetleri	satışlar
2		ocak	26	1452
3		şubat	57	1287
4		mart	23	868
5		nisan	35	980
6		XXX	50	750
7		mayıs	66	1493
8		haziran	32	1071
9		temmuz	70	1415
10		ağustos	49	856
11		eylül	35	899
12
13		443	=TOPLA(KAYDIR(B2;0;1;BAĞ_DEĞ_DOLU_SAY(B2:B11);1))
14