Excel formülü: OFFSET ile dinamik adlandırılmış aralık -

Genel formül

=OFFSET(origin,0,0,COUNTA(range),COUNTA(range))

Özet

Bir formülle dinamik adlandırılmış aralık oluşturmanın bir yolu, KAYDIR işlevini COUNTA işleviyle birlikte kullanmaktır. Dinamik aralıklar, genişleyen aralıklar olarak da bilinir - yeni veya silinmiş verileri barındırmak için otomatik olarak genişler ve daralırlar.

Not: OFFSET, geçici bir işlevdir, yani bir çalışma sayfasındaki her değişiklikte yeniden hesaplanır. Modern bir makine ve daha küçük veri kümesiyle, bu bir soruna neden olmamalıdır, ancak büyük veri kümelerinde daha yavaş performans görebilirsiniz. Bu durumda, bunun yerine INDEX işleviyle dinamik adlandırılmış bir aralık oluşturmayı düşünün.

Gösterilen örnekte, dinamik aralık için kullanılan formül şudur:

=OFFSET(B5,0,0,COUNTA($B$5:$B$100),COUNTA($B$4:$Z$4))

Açıklama

Bu formül, boş olmayan hücrelerin sayısına göre yüksekliği ve genişliği ayarlayarak genişleyen ve daralan bir aralık oluşturmak için KAYDIR işlevini kullanır.

KAYDIR'daki ilk bağımsız değişken, verilerdeki ilk hücreyi (kökeni) temsil eder ve bu durumda B5 hücresidir. Sonraki iki argüman, satırlar ve sütunlar için uzaklıklardır ve sıfır olarak sağlanır.

Son iki argüman yüksekliği ve genişliği temsil eder. Yükseklik ve genişlik, sonuçta ortaya çıkan referansı dinamik hale getiren COUNTA kullanılarak anında üretilir.

Yükseklik için, B5: B100 aralığındaki boş olmayan değerleri saymak için COUNTA işlevini kullanırız. Bu, verilerde boş değer olmadığını ve B100 dışında değer olmadığını varsayar. COUNTA 6 değerini döndürür.

Genişlik için, B5: Z5 aralığındaki boş olmayan değerleri saymak için COUNTA işlevini kullanırız. Bu, üstbilgi hücresi olmadığını ve Z5'in ötesinde üstbilgi olmadığını varsayar. COUNTA 6 değerini döndürür.

Bu noktada formül şuna benzer:

=OFFSET(B5,0,0,6,6)

Bu bilgiyle, OFSET, 6 satır yüksekliğinde 6 sütun genişliğine karşılık gelen B5: G10'a bir referans verir.

Not: Yükseklik ve genişlik için kullanılan aralıklar, çalışma sayfası düzenine uyacak şekilde ayarlanmalıdır.

Tam sütun / satır referanslarıyla varyasyon

Yükseklik ve genişlik için tam sütun ve satır referanslarını da şu şekilde kullanabilirsiniz:

=OFFSET($B$5,0,0,COUNTA($B:$B)-2,COUNTA($4:$4))

B4 ve B2 hücrelerindeki başlık ve başlık değerlerini hesaba katmak için yüksekliğin -2 ile ayarlandığını unutmayın. Bu yaklaşımın avantajı, COUNTA içindeki aralıkların basitliğidir. Dezavantaj, büyük boyuttaki tam sütunlardan ve satırlardan gelir - aralık dışındaki hatalı değerleri önlemek için dikkatli olunmalıdır, çünkü sayımı kolayca atabilirler.

Son sıranın belirlenmesi

Çalışma sayfasındaki verilerin yapısına ve içeriğine bağlı olarak, bir veri kümesindeki son satırı (son göreceli konum) belirlemenin birkaç yolu vardır: