KAYDIR Formülü (OFFSET): Başlangıç Hücresinden Aralık Oluşturma

KAYDIR'ın mantığı

Excel KAYDIR formülü, verdiğiniz bir başlangıç hücresinden belirli sayıda satır aşağı ve sütun sağa gider, oradan da istediğiniz boyutta bir aralık döndürür. Hücreleri yerinden oynatmaz; yalnızca bir başvuru üretir. Bu başvuruyu genellikle TOPLA, ORTALAMA ya da BAĞ_DEĞ_SAY gibi bir fonksiyona verirsiniz.

=KAYDIR(A1;3;1) A1'den 3 satır aşağı ve 1 sütun sağa giderek B4 hücresine ulaşır. Aylık satış tablosunda bu hücre mart satışıdır.

Beş argüman nasıl okunur?

İlk üç argüman zorunludur. başv başlangıç noktası, satırlar dikey uzaklık, sütunlar yatay uzaklıktır. Pozitif değerler aşağı ve sağa, negatif değerler yukarı ve sola gider; 0 yazmak o yönde yerinde kalmak demektir.

yükseklik ve genişlik döndürülen aralığın boyutunu belirler. =TOPLA(KAYDIR(B1;1;0;3;1)) B1'den bir satır aşağı inip 3 satır yüksekliğinde, 1 sütun genişliğinde bir aralık oluşturur. Ortaya çıkan B2:B4 aralığındaki ocak, şubat ve mart satışları toplanır.

Son üç ayı otomatik toplamak

Her ay yeni satırın eklendiği bir tabloda son üç ayın toplamı sık istenen bir rakamdır. Sabit bir B5:B7 aralığı, temmuz eklendiğinde güncelliğini yitirir. KAYDIR ile başlangıç noktasını dolu hücre sayısına bağlarsınız: =TOPLA(KAYDIR(B1;BAĞ_DEĞ_SAY(B2:B13)-2;0;3;1)).

Altı ay doluyken BAĞ_DEĞ_SAY 6 döndürür. Formül B1'den 4 satır aşağı iner, B5'ten başlayan üç hücrelik aralığı alır ve nisan, mayıs, haziran satışlarını toplar. Temmuz girildiğinde aralık kendiliğinden bir satır kayar.

Büyüyen aralık ve grafikler

Aynı yöntemle bütün dolu satırları kapsayan bir aralık da kurulabilir. =ORTALAMA(KAYDIR(B1;1;0;BAĞ_DEĞ_SAY(B2:B13);1)) yalnız veri girilmiş ayların ortalamasını alır; boş hücreler hesaba katılmaz. Bu formülü bir ad olarak tanımlayıp grafiğin veri kaynağına verirseniz, yeni ay eklendikçe grafik de uzar.

Bu yöntemin çalışması için sütunda arada boş hücre olmaması gerekir. BAĞ_DEĞ_SAY yalnız sayı içeren hücreleri saydığı için ortadaki bir boşluk aralığı bir satır kısa keser.

Sütunda metin değerler de varsa, örneğin ürün adlarından oluşan bir listeyi kapsamak istiyorsanız BAĞ_DEĞ_SAY yerine BAĞ_DEĞ_DOLU_SAY kullanın. Bu fonksiyon boş olmayan bütün hücreleri sayar. Başlık hücresini aralığa dahil etmemeye dikkat edin; aksi halde sayı bir fazla çıkar ve aralık tablonun altındaki boş satıra taşar.

Hatalar ve performans

KAYDIR, oluşturduğu aralık sayfanın dışına taşarsa #BAŞV! verir. A1'den yukarı doğru bir satır kaydırmak bu hatanın tipik örneğidir. yükseklik ya da genişlik için 0 veya negatif sayı yazmak da hataya yol açar.

Fonksiyon her hesaplamada yeniden çalışır. Birkaç düzine formülde sorun olmaz, ama on binlerce satırlık bir dosyada yavaşlama hissedilebilir. Ayrıca formülü okuyan kişi aralığın nereye düştüğünü kafasında hesaplamak zorunda kalır.

Alternatifler

Tek bir hücreyi konumla okumak istiyorsanız İNDİS daha anlaşılır ve her hesaplamada yeniden çalışmaz. İNDİS aralık da döndürebildiği için B2:İNDİS(B2:B13;6) gibi bir yapı KAYDIR'ın yerini tutabilir. Verilerinizi Excel tablosuna çevirmek de büyüyen aralık ihtiyacını çoğu zaman ortadan kaldırır.

Kendi aylık tablonuzda son üç ayın toplamını KAYDIR ile yazın. Sonra yeni bir ay ekleyip toplamın kendiliğinden güncellendiğini kontrol edin.