İleri Ders 5: DOLAYLI ve KAYDIR ile Dinamik Aralık

Dinamik aralık neden gerekir?

Dinamik aralık, boyu ya da yeri veriye göre değişen aralıktır. Her ay yeni satış rakamı eklediğiniz bir tabloda "son üç ayın toplamı" formülü sabit B7:B9 yazılırsa eylül verisi girildiğinde yanlış ayları toplar. KAYDIR ve DOLAYLI, aralığı bir hesaplamaya göre kurduğu için bu güncellemeyi sizin yerinize yapar.

Bu dersteki tabloda ocaktan ağustosa kadar sekiz ayın satışı var. Eylül-aralık satırları henüz boş.

KAYDIR'ın argümanları

KAYDIR, bir başlangıç hücresinden belirli sayıda satır ve sütun uzaklaşıp istenen boyutta bir aralık döndürür: =KAYDIR(başv; satırlar; sütunlar; [yükseklik]; [genişlik]). KAYDIR(B1;6;0;3;1) B1'den 6 satır aşağı iner, B7'den başlar ve 3 satır yüksekliğinde, 1 sütun genişliğinde bir aralık verir: B7:B9.

Fonksiyon tek başına bir aralık döndürdüğü için genellikle TOPLA, ORTALAMA gibi bir fonksiyonun içinde kullanılır. Satır argümanı negatif olabilir, bu durumda yukarı doğru kayar.

Son üç ayın toplamı

Son üç ayı bulmak için önce kaç ayın dolu olduğunu bilmeniz gerekir. BAĞ_DEĞ_SAY(B2:B13) dolu satış hücrelerini sayar ve 8 döndürür. Başlık satırı B1 olduğuna göre son ay B9'dadır ve son üç ay B7'den başlar.

Bu hesap tek formüle şöyle sığar: =TOPLA(KAYDIR(B1;BAĞ_DEĞ_SAY(B2:B13)-2;0;3;1)). 8-2 = 6 satır kaydırma B7'ye denk gelir ve haziran, temmuz, ağustos toplanır: 158.000 TL. Eylül rakamını B10'a yazdığınızda sayım 9 olur ve formül temmuz-eylül aralığına kendiliğinden geçer.

Aynı yöntemle tüm dolu ayların ortalaması da alınır. =ORTALAMA(KAYDIR(B2;0;0;BAĞ_DEĞ_SAY(B2:B13);1)) yüksekliği dolu ay sayısına eşitler ve sekiz ayın ortalaması olan 47.812,5 TL'yi verir.

DOLAYLI ile metinden adres

DOLAYLI, metin olarak yazılmış bir adresi gerçek başvuruya çevirir. =DOLAYLI("B5") B5'teki değeri getirir. Gücü, adresi başka hücrelerden birleştirerek kurabilmenizden gelir.

D2'de 2, E2'de 4 yazıyorsa =TOPLA(DOLAYLI("B"&D2&":B"&E2)) önce "B2:B4" metnini oluşturur, sonra bu aralığı toplar. Ocak-mart toplamı 125.700 TL olur. Kullanıcı yalnızca satır numaralarını değiştirerek istediği dönemi seçer. Sayfa adını bir hücreden okuyup DOLAYLI("'"&A1&"'!B2") biçiminde farklı sayfalara bakan özet tablolar da aynı mantıkla kurulur.

Sütun seçmek için kaydırma

KAYDIR yalnızca satırda değil sütunda da kayar. Bölgelerin satırlarda, ayların B1:M1 başlıklarında olduğu bir tabloda seçilen ayın sütununu toplamak için sütunlar argümanını KAÇINCI ile hesaplarsınız. =TOPLA(KAYDIR(A2;0;KAÇINCI("Mart";B1:M1;0);5;1)) A2'den Mart sütununa kadar sağa kayar ve beş bölgenin mart satışını toplar.

Ay adını bir hücreden okursanız kullanıcı açılır listeden ay seçerek toplamı değiştirebilir. Aynı soruyu İNDİS ile =TOPLA(İNDİS(B2:M6;0;KAÇINCI("Mart";B1:M1;0))) biçiminde de sorabilirsiniz; bu yazım değişken değildir.

Dinamik aralığı adlandırmak

KAYDIR formülünü her hücreye ayrı ayrı yazmak yerine bir ada bağlayabilirsiniz. Formüller sekmesinden Ad Yöneticisi'ni açıp Yeni'ye tıklayın. Ad kutusuna SatisVerisi yazın, Başvuru yeri kutusuna =KAYDIR($B$2;0;0;BAĞ_DEĞ_SAY($B$2:$B$13);1) girin ve Tamam'a basın.

Artık =TOPLA(SatisVerisi) ya da =ORTALAMA(SatisVerisi) yazdığınızda Excel o anki dolu aralığı kullanır. Aynı ad bir grafiğin seri değerlerinde ya da Veri Doğrulama listesinin kaynağında da işe yarar. Eylül rakamı girildiğinde grafik yeni ayı kendiliğinden gösterir.

Bu yöntem, formülü tek bir yerde tuttuğu için bakımı da kolaylaştırır. Aralığın başlangıcını değiştirmeniz gerektiğinde yalnızca tanımı güncellersiniz. Adı kullanan bütün hücreler yeni tanıma göre hesaplanır.

Yüksekliği BAĞ_DEĞ_SAY'a bağladığınızda arada boş hücre bırakmamaya dikkat edin. Haziranı boş bırakıp temmuzu girerseniz sayım yedi olur ve aralık temmuzu dışarıda bırakır.

Performans ve bakım uyarısı

KAYDIR ve DOLAYLI değişken (volatile) fonksiyonlardır. Çalışma kitabında herhangi bir hücre değiştiğinde, bağlı olmasalar bile yeniden hesaplanırlar. Birkaç formülde fark edilmez, ama binlerce satıra kopyalandığında dosya belirgin biçimde yavaşlar.

DOLAYLI'nın ikinci bir zayıflığı vardır: adres metin olduğu için satır ya da sütun eklediğinizde güncellenmez. "B2:B4" metni, B sütununun soluna yeni bir sütun eklendiğinde hâlâ B'yi gösterir. Basit "son N satır" ihtiyaçlarında İNDİS ile kurulan B2:İNDİS(B2:B13;BAĞ_DEĞ_SAY(B2:B13)) aralığı değişken değildir ve daha hafiftir.

Sıradaki adım

Alıştırmada farklı N değerleri için son N ayın toplamını KAYDIR ile hesaplayacaksınız. Formülü yazdıktan sonra B10'a bir eylül rakamı girip sonuçların nasıl değiştiğini izleyin. Aynı hesabı İNDİS ile kurulan bir aralıkla yeniden yazmak, iki yöntemi karşılaştırmanız için iyi bir ek çalışmadır.

=TOPLA(KAYDIR(B1;BAĞ_DEĞ_SAY(B2:B13)-2;0;3;1))

=SUM(OFFSET(B1,COUNT(B2:B13)-2,0,3,1))

=TOPLA(DOLAYLI("B"&D2&":B"&E2))

=SUM(INDIRECT("B"&D2&":B"&E2))

=ORTALAMA(KAYDIR(B2;0;0;BAĞ_DEĞ_SAY(B2:B13);1))

=AVERAGE(OFFSET(B2,0,0,COUNT(B2:B13),1))