Excel'de dinamik olarak adlandırılmış aralık nasıl oluşturulur?
Normalde, Adlandırılmış Aralıklar, Excel kullanıcıları için son derece yararlıdır: bir sütundaki değerleri tanımlayabilir, o sütuna bir ad verebilir ve daha sonra bu aralığa hücre başvuruları yerine doğrudan adıyla başvurabilirsiniz. Ancak çoğu zaman, ileride başvurduğunuz aralığın veri değerlerini genişletmek için yeni veriler eklemeniz gerekir. Bu durumda, Formüller > Ad Yöneticisi bölümüne geri dönüp aralığı yeni değeri içerecek şekilde yeniden tanımlamalısınız. Bunu önlemek için her yeni satır veya sütun eklediğinizde hücre başvurularını sürekli güncellemek zorunda kalmazsınız; bunun yerine dinamik bir adlandırılmış aralık oluşturabilirsiniz.
Excel'de tablo oluşturarak dinamik adlandırılmış aralık oluşturma
Excel'de İşlev kullanarak dinamik adlandırılmış aralık oluşturma
Excel'de VBA koduyla dinamik adlandırılmış aralık oluşturma
Excel'de tablo oluşturarak dinamik adlandırılmış aralık oluşturma
Excel 2007 veya sonraki sürümleri kullanıyorsanız, dinamik adlandırılmış 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ı dinamik olarak adlandırılmış bir aralığa dönüştürmeniz gerekiyor.
1. İlk olarak, bu aralık için bir hücre adı tanımlayacağım. A1:A6 aralığını seçin ve Tarih adını Ad Kutusu’na girin, ardından Enter tuşuna basın. Aynı şekilde, B1:B6 aralığı için SatışFiyatı adını tanımlayın. Ardından, boş bir hücreye şu formülü oluşturuyorum: =SUM(SatışFiyatı). Ekran görüntüsüne bakın:
2. Aralığı seçin ve Ekle > Tablo seçeneğine tıklayın; ekran görüntüsüne bakın:
3. Tablo Oluştur iletişim kutusunda, aralık başlık içeriyorsa Tablom başlıklar içeriyor seçeneğini işaretleyin (başlık içermiyorsa işaretini kaldırın), ardından Tamam düğmesine tıklayın; böylece aralık verileri tabloya dönüştürülmüş olur. Ekran görüntülerine bakın:
 |  |  |
4. Verilerin altına yeni değerler girdiğinizde, adlandırılmış aralık otomatik olarak genişleyecek ve oluşturulan formül de buna göre güncellenecektir. Aşağıdaki ekran görüntülerine göz atın:
 | |  |
Notlar:
1. Yeni girdiğiniz veriler, mevcut verilere bitişik olmalı; yani aralarında boş satır veya sütun bulunmamalıdır.
2. Tabloda mevcut değerlerin arasına veri ekleyebilirsiniz.
Excel'de İşlev kullanarak dinamik adlandırılmış aralık oluşturma
Excel 2003 veya önceki sürümlerde ilk yöntem kullanılamaz; bu nedenle size alternatif bir çözüm sunuyoruz. Aşağıdaki OFFSET( ) işlevi bu görevi yerine getirebilir, ancak biraz karmaşık olabilir. Diyelim ki, önceden tanımlanmış hücre adlarını içeren bir veri aralığınız var: Örneğin, A1:A6 aralığının adı Tarih ve B1:B6 aralığının adı SatışFiyatı olsun. Aynı zamanda SatışFiyatı için bir formül oluşturuyorsunuz. Ekran görüntüsüne göz atın:
Hücre adı öğesini aşağıdaki adımları izleyerek dinamik Hücre adı haline getirebilirsiniz:
1. Şuna tıklayın: Formüller > Ad Yöneticisi, ekran görüntüsüne bakın:
2. Ad Yöneticisi iletişim kutusunda, kullanmak istediğiniz öğeyi seçin ve Düzenle düğmesine tıklayın.
3. Açılan Adı Düzenle iletişim kutusuna şu formülü girin: =OFFSET(Sheet1!$A$1, 0, 0, COUNTA($A:$A), 1) Şuna Başvurur metin kutusuna; ekran görüntüsüne bakın:
4. Ardından Tamam düğmesine tıklayın ve ardından 2. ve 3. adımı tekrarlayarak bu formülü =OFFSET(Sheet1!$B$1, 0, 0, COUNTA($B:$B), 1) ifadesini Şuna Başvurur metin kutusuna SatışFiyatı hücre adı için kopyalayın.
5. Böylece dinamik adlandırılmış aralıklar oluşturulmuş oldu! Verilerin altına yeni değerler eklediğinizde, adlandırılmış aralık otomatik olarak genişleyecek ve buna bağlı formüller de anında güncellenecektir. Detaylı görünüm için ekran görüntülerine göz atın:
| | |
Not: Aralığınızın ortasında boş hücreler varsa, formülünüz yanlış sonuç döndürebilir. Bunun nedeni, boş olmayan hücrelerin sayılmamasıdır; bu da aralığınızın olması gerektiğinden daha kısa olmasına ve aralığın sonundaki hücrelerin hesaplamaya dahil edilmemesine yol açar.
İpucu: Bu formülün açıklaması:
- =OFFSET(reference,rows,cols,[height],[width])
- =OFFSET(Sheet1!$A$1, 0, 0, COUNTA($A:$A), 1)
- başvurubaşlangıç hücre konumuna karşılık gelir; bu örnekte Sheet1!$A$1;
- satır, başlangıç hücresine göre aşağıya doğru kaç satır hareket edeceğinizi belirtir (negatif bir değer girerseniz, yukarı hareket edersiniz). Bu örnekte, 0 değeri listenin ilk satırından itibaren aşağıya doğru harekete geçileceğini gösterir.
- sütun, başlangıç hücresine göre sağa doğru kaç sütun hareket edeceğinizi belirtir (negatif bir değer girerseniz sola hareket edersiniz). Yukarıdaki örnek formülde, 0 değeri sağa doğru 0 sütun genişletileceğini gösterir.
- [yükseklik], ayarlanmış konumdan başlayan aralığın yüksekliğini (ya da satır sayısını) belirtir. $A:$A ifadesiyle, A sütununa girilen tüm öğeler sayılıyor.
- [genişlik], ayarlanmış konumdan başlayan aralığın genişliğini (ya da sütun sayısını) belirtir. Yukarıdaki formülde liste, 1 sütun genişliğinde olacaktır.
Bu bağımsız değişkenleri ihtiyacınıza göre özelleştirebilirsiniz.
Excel'de VBA koduyla dinamik adlandırılmış aralık oluşturma
Birden fazla sütununuz varsa, kalan tüm sütunlar için ayrı ayrı formül girmeyi tekrarlayabilirsiniz; ancak bu uzun ve tekrarlı bir süreç olur. İşleri kolaylaştırmak için dinamik adlandırılmış aralığı otomatik olarak oluşturmak üzere bir kod kullanabilirsiniz.
1. Çalışma sayfanızı etkinleştirin.
2. ALT + F11 tuşlarına basılı tutun; bu işlem, Microsoft Visual Basic for Applications penceresini açar.
3. Ekle > Modül seçeneğine tıklayın ve aşağıdaki kodu Modül Penceresi’ne yapıştırın.
VBA kodu: dinamik adlandırılmış aralık oluşturun
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; ilk satırdaki değerlerle adlandırılmış bazı dinamik adlandırılmış aralıklar ve tüm verileri kapsayan VeriAlanım adında bir dinamik aralık oluşturulacaktır.
5. Satırlara veya sütunlara yeni değerler eklediğinizde, aralık otomatik olarak genişleyecektir. Ekran görüntülerine göz atın:
 |
 |
 |
Notlar:
1. Bu kodla, hücre adı Ad Kutusu içinde görünmez hale gelir. Hücre adı öğelerini rahatça görüntülemek ve kullanmak için Kutools for Excel eklentisini yükledim; Gezinme özelliği sayesinde oluşturulan dinamik hücre adları listelenir.
2. Bu kodla verilerin tamamı dikey ya da yatay olarak genişletilebilir; ancak yeni değerler eklerken veriler arasında boş satır veya sütun bırakmamaya dikkat etmelisiniz.
3. Bu kodu kullandığınızda veri aralığı, A1 hücresinden başlamalıdır.
İlgili makale:
Excel'de yeni veri girdikten sonra grafiği otomatik olarak nasıl güncellerim?
En İyi Office Üretkenlik Araçları
Excel becerilerinizi Kutools for Excel ile geliştirin ve verimlilikte hiç yaşamadığınız bir deneyim yaşayın.Kutools for Excel, üretkenliği artırmak ve Zamanı Kaydet için 300’den fazla gelişmiş özelliğe sahiptir.İhtiyacınız olan özelliği hemen edinmek için buraya tıklayın…
Office Tab, Office uygulamalarına sekmeli bir arayüz kazandırarak işlerinizi çok daha kolay hale getirir.
- Word, Excel, PowerPoint, Publisher, Access, Visio ve Project'te sekmeli düzenleme ve okuma özelliğini etkinleştirin.
- Belgeleri yeni pencerelerde değil, aynı pencerenin yeni sekmelerinde açıp oluşturun.
- Günlük üretkenliğinizi %50 artırır ve her gün yüzlerce fare tıklamasından tasarruf etmenizi sağlar!
Tüm Kutools eklentileri — tek bir kurulum dosyasında!
Kutools for Office paketi, Excel, Word, Outlook ve PowerPoint eklentilerini ve ayrıca Office Tab Pro’yu içeren kapsamlı bir çözümdür; Office uygulamalarında çalışan ekipler için idealdir.
- Hepsi bir arada paket— Excel, Word, Outlook ve PowerPoint eklentileri + Office Tab Pro
- Tek kurulum, tek lisans— birkaç dakikada hazır (MSI uyumlu)
- Birlikte daha iyi çalışır— Office uygulamalarında akıcı üretkenlik
- 30 günlük tam özellikli deneme— kayıt gerekmez, kredi kartı gerekmez
- En iyi fiyat performans— ayrı ayrı eklenti satın almaya göre tasarruf sağlar