KAYDIR (İngilizce OFFSET) formülü =KAYDIR(A1;3;2), A1'den 3 satır aşağıdaki ve 2 sütun sağdaki hücreyi, yani C4'ü döndürür. Ona bir yükseklik ve genişlik de verin, bütün bir aralık döndürür; KAYDIR çoğunlukla bunun için kullanılır: kayan veya büyüyen bir aralık üzerinde toplamlar ve ortalamalar. Tablodaki formülleri de bu şekilde, Türkçe adlarla ve noktalı virgülle yazabilirsiniz.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Rows | Cols | Result | |
| 2 | Apple | Fruit | $1.20 | 3 | 2 | $0.80 | |
| 3 | Pear | Fruit | $1.50 | ||||
| 4 | Carrot | Vegetable | $0.80 | ||||
| 5 | Bread | Bakery | $2.40 | ||||
| 6 | Milk | Dairy | $1.10 |
=KAYDIR(A1;E2;F2)A1'den 3 satır aşağı ve 2 sütun sağa gitmek, Carrot'ın fiyatı olan C4'e denk gelir: $0.80. Carrot adı için Cols'u 0, Milk'in satırı için Rows'u 5 yapın. Satırlar ve sütunlar yukarı veya geri gitmek için negatif olabilir; sayfanın üstünden veya kenarından taşan bir hareket #BAŞV! (İngilizce #REF!) verir. Tablo hata adlarını İngilizce gösterir.
KAYDIR söz dizimi
=OFFSET(reference, rows, cols, [height], [width])
reference: başlangıç hücresi (veya aralığı).rows,cols: ne kadar hareket edileceği. 0 yerinde kal demektir.height,width: varılan hücreden itibaren sayılan, döndürülecek aralığın boyutu. Yazılmazsareferenceboyutunda olurlar.
Bir hücrede tek başına duran ve birkaç hücre döndüren bir KAYDIR, Excel 365'te taşar; eski sürümler genellikle #DEĞER! (İngilizce #VALUE!) gösterir. TOPLA, ORTALAMA, BAĞ_DEĞ_SAY veya MAK içinde bir aralık olarak çalışır.
Son N satırı toplama
KAYDIR'ın klasik işi: kaç satır eklenmiş olursa olsun her zaman en son satırları kapsayan bir toplam. BAĞ_DEĞ_SAY (İngilizce COUNT) kaç değer olduğunu bulur, KAYDIR son N değerin ilkine kadar aşağı iner ve yükseklik N satır alır.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | Last N | Total | ||
| 2 | Jan | 4,200 | 3 | 14,900 | ||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 | ||||
| 8 | Jul | 5,000 |
=TOPLA(KAYDIR(B1;BAĞ_DEĞ_SAY(B2:B13)-E2+1;0;E2;1))7 değer var, bu yüzden KAYDIR 7-3+1, yani B1'in 5 satır altından, B6'dan başlar ve 3 satır alır: May'den Jul'a, 14,900. B9'a (Ağustos) 4900 yazın, toplam Jun, Jul ve Aug'a kayar, çünkü BAĞ_DEĞ_SAY artık 8 bulur. B2:B13 aralığı yılın geri kalanı için yer bırakır. Sütunun arasında boş hücre olmamalıdır, aksi hâlde BAĞ_DEĞ_SAY eksik sayar ve pencere yanlış yere düşer.
Hareketli ortalama
Bir sütun boyunca aşağı doldurulan, negatif satır kaydırmalı KAYDIR, her satıra üstündeki satırlardan oluşan bir pencere verir: burada içinde bulunulan ay ile önceki iki ayın ortalaması.
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | 3-month average |
| 2 | Jan | 4,200 | |
| 3 | Feb | 3,900 | |
| 4 | Mar | 4,800 | 4,300 |
| 5 | Apr | 5,100 | 4,600 |
| 6 | May | 4,600 | 4,833 |
| 7 | Jun | 5,300 | 5,000 |
| 8 | Jul | 5,000 | 4,967 |
=ORTALAMA(KAYDIR(B4;-2;0;3;1))C4, B2:B4'ün (Jan'dan Mar'a) ortalamasını alır, 4,300. Aşağıdaki her satır pencereyi bir satır aşağı kaydırır. Altı aylık ortalama için 3'ü 6, -2'yi -5 yapın (bu durumda formüle 7. satırda başlayın). Bu özel durum hiç KAYDIR gerektirmez: C4'ten aşağı doldurulan =ORTALAMA(B2:B4) aynısını yapar, çünkü göreli başvurular zaten kayar. KAYDIR, pencerenin boyutu bir hücreden geldiğinde işe yarar.
İNDİS neden çoğu zaman daha iyi bir seçimdir
KAYDIR geçicidir: Excel, önceden hangi hücreleri göstereceğini bilemediği için çalışma kitabının herhangi bir yerindeki her düzenlemeden sonra her KAYDIR'ı yeniden hesaplar. Binlercesini içeren bir sayfa yavaşlar. İNDİS de bir başvuru döndürür ve başlangıç:İNDİS(...) şeklinde yazılan bir aralık geçici olmadan aynı şekilde büyür:
=SUM(OFFSET(B2, 0, 0, E2, 1)) first E2 rows, volatile
=SUM(B2:INDEX(B2:B13, E2)) same rows, not volatile
Türkçe Excel'de: =TOPLA(KAYDIR(B2;0;0;E2;1)) ve =TOPLA(B2:İNDİS(B2:B13;E2)). İkisi de sütunun ilk E2 satırını okur. KAYDIR'ı denetlemek de daha zordur: Etkileyenleri İzle ve formülü düzenlerken Excel'in çizdiği renkli çerçeveler, KAYDIR'ın sonunda döndürdüğü aralığı değil, başlangıç hücresini ve bağımsız değişkenleri gösterir. Hızlı bir model veya bir grafik aralığı için KAYDIR'ı kullanın; büyük çalışma kitaplarında İNDİS'i tercih edin. Aralık döndürme hakkında daha fazlası İNDİS sayfasında; diğer geçici başvuru fonksiyonu ise DOLAYLI.
Alıştırma: ilk N ayın toplamı
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | First N | Total | ||
| 2 | Jan | 4,200 | 4 | |||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 | ||||
| 8 | Jul | 5,000 |
Sıra sende: F2 hücresinde TOPLA içinde KAYDIR kullanarak, N değeri E2'de olan ilk N ayı toplayın.
Sıkça Sorulan Sorular
Excel'de KAYDIR ne işe yarar?
Bir başlangıç hücresinden belirli sayıda satır ve sütun uzaktaki, isteğe bağlı olarak yeniden boyutlandırılmış bir başvuru döndürür. =KAYDIR(A1;3;2) A1'in 3 satır aşağısındaki ve 2 sütun sağındaki hücredir, yani C4.
Excel'de son N satır nasıl toplanır?
Başlıktan başlayın ve son N değerin ilkine kadar aşağı inin: =TOPLA(KAYDIR(B1;BAĞ_DEĞ_SAY(B2:B100)-N+1;0;N;1)). BAĞ_DEĞ_SAY kaç değer olduğunu bulur, N yüksekliği de o kadar satır alır. Yalnızca sütunda boşluk yoksa çalışır.
KAYDIR neden geçici bir fonksiyon?
Excel, gösterdiği hücreler ancak çalıştıktan sonra bilindiği için çalışma kitabındaki her değişiklikten sonra her KAYDIR'ı yeniden hesaplar. Büyük çalışma kitaplarında bu işleri yavaşlatır. B2:İNDİS(B2:B100;N) gibi İNDİS ile oluşturulan bir aralık aynı işi geçici olmadan yapar.
KAYDIR'ın bağımsız değişkenleri nelerdir?
OFFSET(reference, rows, cols, [height], [width]): başlangıç hücresi, kaç satır aşağı (negatif yukarı demektir), kaç sütun sağa (negatif geri gider) ve isteğe bağlı olarak döndürülecek aralığın boyutu.
