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
|
