Excel'de dinamik adlandırılmış aralık nasıl oluşturulur?

Normalde, Adlandırılmış Aralıklar Excel kullanıcıları için çok kullanışlıdır. Bir sütundaki bir dizi değeri tanımlayabilir, bu sütuna bir isim verebilir ve daha sonra bu aralığı hücre referansları yerine ismiyle ifade edebilirsiniz. Ancak çoğu zaman, gelecekte başvurduğunuz aralığın veri değerlerini genişletmek için yeni veriler eklemeniz gerekir. Bu durumda, Formüller > İsim Yöneticisi'ne geri dönüp aralığı yeniden tanımlamanız gerekir. Bunu önlemek için, her yeni satır veya sütun eklediğinizde hücre referanslarını ayarlamanıza gerek kalmadan otomatik olarak genişleyen dinamik adlandırılmış bir aralık oluşturabilirsiniz.

Excel'de tablo oluşturarak dinamik adlandırılmış aralık oluşturun

Excel'de Fonksiyon ile dinamik adlandırılmış aralık oluşturun

Excel'de VBA kodu ile dinamik adlandırılmış aralık oluşturun

Excel'de tablo oluşturarak dinamik adlandırılmış aralık oluşturun

Excel 2007 veya daha yeni sürümlerini kullanıyorsanız, dinamik adlandırılmış bir aralık oluşturmanın en kolay yolu, adlandırılmış bir Excel tablosu oluşturmaktır.

Diyelim ki, aşağıdaki veri aralığınız var ve bu aralığı dinamik adlandırılmış aralık haline getirmeniz gerekiyor.

1. İlk olarak, bu aralık için aralık adları tanımlayacağım. A1:A6 aralığını seçin ve Ad Kutusu'na Tarih adını girin, ardından Enter tuşuna basın. Aynı şekilde, B1:B6 aralığı için SatışFiyatı adını tanımlayın. Aynı zamanda, boş bir hücreye =topla(SatışFiyatı) formülünü oluşturuyorum, aşağıdaki ekran görüntüsüne bakın:

2. Aralığı seçin ve Ekle > Tablo'ya tıklayın, aşağıdaki ekran görüntüsüne bakın:

3. Tablo Oluştur istem kutusunda, tablomun başlıkları var (My table has headers) seçeneğini işaretleyin (eğer aralıkta başlıklar yoksa, bu seçeneği kaldırın), Tamam düğmesine tıklayın ve aralık verileri tabloya dönüştürülmüş olacaktır. Ekran görüntüleri:

4. Ve verilerin altına yeni değerler girdiğinizde, adlandırılmış aralık otomatik olarak ayarlanacak ve oluşturulan formül de değişecektir. Aşağıdaki ekran görüntülerine bakın:

Notlar:

1. Yeni girdiğiniz veriler mevcut verilere bitişik olmalıdır, yani yeni verilerle mevcut veriler arasında boş satır veya sütun bulunmamalıdır.

2. Tabloda, mevcut değerler arasına veri ekleyebilirsiniz.

Excel'de Fonksiyon ile dinamik adlandırılmış aralık oluşturun

Excel 2003 veya önceki sürümlerde, ilk yöntem kullanılamaz, bu nedenle burada başka bir yol sunuyoruz. Aşağıdaki OFFSET( ) fonksiyonu bu işi sizin için yapabilir, ancak biraz karmaşıktır. Diyelim ki, tanımladığım aralık adlarını içeren bir veri aralığım var, örneğin, A1:A6 aralık adı Tarih, ve B1:B6 aralık adı SatışFiyatı, aynı zamanda SatışFiyatı için bir formül oluşturuyorum. Ekran görüntüsüne bakın:

Aşağıdaki adımları izleyerek aralık adlarını dinamik aralık adlarına değiştirebilirsiniz:

1. Formüller > İsim Yöneticisi'ne tıklayın, aşağıdaki ekran görüntüsüne bakın:

2. İsim Yöneticisi iletişim kutusunda, kullanmak istediğiniz öğeyi seçin ve Düzenle düğmesine tıklayın.

3. Açılan Düzenleme İsmi iletişim kutusunda, şu formülü =OFFSET(Sayfa1!$A$1, 0, 0, SAYI($A:$A), 1) Refers To metin kutusuna girin, aşağıdaki ekran görüntüsüne bakın:

4. Ardından Tamam'a tıklayın ve adım 2 ve adım 3'ü tekrarlayarak bu formülü =OFFSET(Sayfa1!$B$1, 0, 0, SAYI($B:$B), 1) Refers To metin kutusuna SatışFiyatı aralık adı için kopyalayın.

5. Ve dinamik adlandırılmış aralıklar oluşturulmuştur. Verilerin altına yeni değerler girdiğinizde, adlandırılmış aralık otomatik olarak ayarlanacak ve oluşturulan formül de değişecektir. Aşağıdaki ekran görüntülerine bakın:

Not: Eğer aralığınızın ortasında boş hücreler varsa, formülünüzün sonucu yanlış olacaktır. Çünkü boş olmayan hücreler sayılmaz, bu yüzden aralığınız olması gerekenden daha kısa olacaktır ve aralıktaki son hücreler dışarıda kalacaktır.

İpucu: Bu formül için açıklama:

• =OFFSET(referans,satırlar,sütunlar,[yükseklik],[genişlik])
• 
• =OFFSET(Sayfa1!$A$1, 0, 0, SAYI($A:$A), 1)

• referans, başlangıç hücre konumuna karşılık gelir, bu örnekte Sayfa1!$A$1;
• satır, başlangıç hücresine göre aşağıya doğru ne kadar hareket edeceğinizi belirtir (veya negatif bir değer kullanırsanız yukarı doğru), bu örnekte, 0 listenin ilk satırdan başlayacağını gösterir.
• sütun, başlangıç hücresine göre sağa doğru kaç sütun hareket edeceğinizi belirtir (veya negatif bir değer kullanırsanız sola doğru), yukarıdaki örnek formülde, 0 sağa doğru 0 sütun genişletileceğini gösterir.
• [yükseklik], ayarlanmış pozisyondan başlayan aralığın yüksekliği (veya satır sayısı). $A:$A, sütun A'da girilen tüm öğeleri sayar.
• [genişlik], ayarlanmış pozisyondan başlayan aralığın genişliği (veya sütun sayısı). Yukarıdaki formülde, liste 1 sütun genişliğinde olacaktır.

Bu argümanları ihtiyaçlarınıza göre değiştirebilirsiniz.

Excel'de VBA kodu ile dinamik adlandırılmış aralık oluşturun

Birden fazla sütununuz varsa, kalan tüm sütunlar için bireysel formülleri tekrarlayıp girebilirsiniz, ancak bu uzun ve tekrarlayıcı bir süreç olacaktır. İşleri kolaylaştırmak için, dinamik adlandırılmış aralığı otomatik olarak oluşturmak için bir kod kullanabilirsiniz.

1. Çalışma sayfanızı etkinleştirin.

2. ALT + F11 tuşlarını basılı tutun ve Microsoft Visual Basic for Applications penceresi açılacaktır.

3. Ekle Insert > Module'e tıklayın ve aşağıdaki kodu Modül Penceresine yapıştırın.

Vba kodu: dinamik adlandırılmış aralık oluştur

Sub CreateNamesxx()
'Update 20131128
Dim wb As Workbook, ws As Worksheet
Dim lrow As Long, lcol As Long, i As Long
Dim myName As String, Start As String
Const Rowno = 1
Const Colno = 1
Const Offset = 1
On Error Resume Next
Set wb = ActiveWorkbook
Set ws = ActiveSheet
lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row
Start = Cells(Rowno, Colno).Address
wb.Names.Add Name:="lcol", RefersTo:="=COUNTA($" & Rowno & ":$" & Rowno & ")"
wb.Names.Add Name:="lrow", RefersToR1C1:="=COUNTA(C" & Colno & ")"
wb.Names.Add Name:="myData", RefersTo:="=" & Start & ":INDEX($1:$65536," & "lrow," & "Lcol)"
For i = Colno To lcol
    myName = Replace(Cells(Rowno, i).Value, " ", "_")
    If myName <> "" Then
        wb.Names.Add Name:=myName, RefersToR1C1:="=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & ",lrow)"
    End If
Next
End Sub

4. Ardından kodu çalıştırmak için F5 tuşuna basın ve ilk satır değerleriyle adlandırılan bazı dinamik adlandırılmış aralıklar oluşturulacaktır. Ayrıca, tüm verileri kapsayan MyData adında bir dinamik aralık da oluşturacaktır.

5. Satırların veya sütunların altına yeni değerler girdiğinizde, aralık da genişleyecektir. Ekran görüntülerine bakın:

Notlar:

1. Bu kodla, aralık adları Ad Kutusu'nda görüntülenmez. Aralık adlarını rahatça görüntülemek ve kullanmak için Kutools for Excel'i yükledim ve Gezinme Bölmesi ile oluşturulan dinamik aralık adları listelenmiştir.

2. Bu kodla, verilerin tüm aralığı dikey veya yatay olarak genişletilebilir, ancak yeni değerler girdiğinizde veriler arasında boş satır veya sütun olmaması gerektiğini unutmayın.

3. Bu kodu kullandığınızda, veri aralığınız A1 hücresinden başlamalıdır.

İlgili makale:

Excel'de yeni veri girdikten sonra grafiği otomatik olarak nasıl güncelleyebilirim?