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ütunda bir dizi değer tanımlayabilir, bu sütuna bir ad verebilir ve ardından bu aralığa hücre referansları yerine adıyla başvurabilirsiniz. Ancak çoğu zaman, ileride başvurulan aralığınızın veri değerlerini genişletmek için yeni veriler eklemeniz gerekir. Bu durumda, geri dönmelisiniz. Formüller > İsim Yöneticisi ve aralığı yeni değeri içerecek şekilde yeniden tanımlayın. Bunu önlemek için, dinamik adlandırılmış bir aralık oluşturabilirsiniz; bu, listeye her yeni satır veya sütun eklediğinizde hücre referanslarını ayarlamanıza gerek olmadığı anlamına gelir.
Excel'de bir tablo oluşturarak dinamik adlandırılmış aralık oluşturun
İşlev ile Excel'de dinamik adlandırılmış aralık oluşturun
VBA kodu ile Excel'de dinamik adlandırılmış aralık oluşturun
Excel'de bir tablo oluşturarak dinamik adlandırılmış aralık oluşturun
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, dinamik adlandırılmış aralık haline gelmesi gereken bir dizi aşağıdaki veriye sahipsiniz.
1. İlk olarak, bu aralık için aralık adlarını tanımlayacağım. A1: A6 aralığını seçin ve adı girin Tarih içine isim KutusuTuşuna basın ve ardından tuşuna basın. Keşfet anahtar. Aynı şekilde Saleprice olarak B1: B6 aralığı için bir isim tanımlamak. Aynı zamanda bir formül oluşturuyorum = toplam (Saleprice) boş bir hücrede ekran görüntüsüne bakın:
2. Aralığı seçin ve tıklayın Ekle > tablo, ekran görüntüsüne bakın:
3. In Tablo Oluştur komut kutusu, işaretleyin Masamın başlıkları var (aralıkta başlık yoksa, işaretini kaldırın), tıklayın OK düğmesi ve aralık verileri tabloya dönüştürüldü. Ekran görüntülerine bakın:
 |
 |
 |
4. Verilerden sonra 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 giriş verileriniz yukarıdaki verilere bitişik olmalıdır, bu, yeni veriler ile mevcut veriler arasında boş satır veya sütun olmadığı anlamına gelir.
2. Tabloda, mevcut değerler arasına veri ekleyebilirsiniz.
İşlev ile Excel'de dinamik adlandırılmış aralık oluşturun
Excel 2003 veya önceki sürümlerde, ilk yöntem kullanılamayacaktır, bu nedenle işte sizin için başka bir yol. Aşağıdaki OFSET () işlevi bu iyiliği sizin için yapabilir, ancak biraz zahmetlidir. Tanımladığım aralık adlarını içeren bir veri aralığım olduğunu varsayarsak, örneğin, A1: A6 aralık adı Tarih, ve B1: B6 aralık adı Satış ücretiaynı zamanda bir formül oluşturuyorum Satış ücreti. Ekran görüntüsüne bakın:
Aralık adlarını aşağıdaki adımlarla dinamik aralık adlarıyla değiştirebilirsiniz:
1. Tıklamaya git Formüller > İsim Yöneticisi, ekran görüntüsüne bakın:
2. In İsim Yöneticisi iletişim kutusunda kullanmak istediğiniz öğeyi seçin ve Düzenle düğmesine basın.
3. Dışarı fırladı Adı Düzenle iletişim kutusu, bu formülü girin = OFSET (Sayfa1! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1) içine Anlamına gelir metin kutusu, ekran görüntüsüne bakın:
4. Sonra tıklayın OKve ardından bu formülü kopyalamak için 2. ve 3. adımları tekrarlayın = OFSET (Sayfa1! $ B $ 1, 0, 0, COUNTA ($ B: $ B), 1) içine Anlamına gelir metin kutusu Satış ücreti aralık adı.
5. Ve dinamik adlandırılmış aralıklar oluşturuldu. Verilerden sonra yeni değerler girdiğinizde, adlandırılan aralık otomatik olarak ayarlanacak ve oluşturulan formül de değiştirilecektir. Ekran görüntülerine bakın:
|
|
|
Not: Aralığınızın ortasında boş hücreler varsa formülünüzün sonucu yanlış olacaktır. Bunun nedeni, boş olmayan hücrelerin sayılmaması, dolayısıyla aralığınızın olması gerekenden daha kısa olması ve aralıktaki son hücrelerin kalmamasıdır.
İpucu: Bu formülün açıklaması:
- = KAYDIR (başvuru, satırlar, sütunlar, [yükseklik], [genişlik])
- = OFSET (Sayfa1! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1)
- referans bu örnekte başlangıç hücre konumuna karşılık gelir Sayfa1! $ A $ 1;
- sıra başlangıç hücresine göre aşağıya doğru hareket edeceğiniz satırların sayısını ifade eder (veya negatif bir değer kullanıyorsanız yukarı doğru), bu örnekte 0, listenin ilk satırdan aşağı başlayacağını belirtir
- sütun , başlangıç hücresine (veya negatif bir değer kullanarak sola) göre sağa taşıyacağınız sütunların sayısına karşılık gelir, yukarıdaki örnek formülde, 0, 0 sütununu sağa genişletmeyi belirtir.
- [yükseklik] ayarlanan konumdan başlayan aralığın yüksekliğine (veya sıra sayısına) karşılık gelir. $ A: $ A, A sütununa girilen tüm öğeleri sayacaktır.
- [Genişlik] ayarlanan konumda başlayan aralığın genişliğine (veya sütun sayısına) karşılık gelir. Yukarıdaki formülde liste 1 sütun genişliğinde olacaktır.
Bu argümanları ihtiyacınıza göre değiştirebilirsiniz.
VBA kodu ile Excel'de dinamik adlandırılmış aralık oluşturun
Birden fazla sütununuz varsa, kalan tüm sütunlar için tek tek formülü tekrarlayabilir ve girebilirsiniz, ancak bu uzun ve tekrarlayan 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. Basılı tutun ALT + F11 anahtarlar ve açılır Uygulamalar için Microsoft Visual Basic penceresi.
3. tıklayın Ekle > modülve aşağıdaki kodu Modül Penceresi.
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. Daha sonra tuşuna basın. F5 kodu çalıştırmak için anahtar ve ilk satır değerleriyle adlandırılan bazı dinamik adlandırılmış aralıklar oluşturulur ve ayrıca adında dinamik bir aralık oluşturur Benim verim tüm verileri kapsar.
5. Satırlardan veya sütunlardan sonra yeni değerler girdiğinizde, aralık da genişleyecektir. Ekran görüntülerine bakın:
 |
|
 |
Notlar:
1. Bu kodla, aralık isimleri ekranda görüntülenmez. isim Kutusu, aralık adlarını rahatça görüntülemek ve kullanmak için, Kutools for Excel, Onun ile Gezinti Bölmesioluşturulan dinamik aralık adları listelenir.
2. Bu kodla, tüm veri aralığı dikey veya yatay olarak genişletilebilir, ancak yeni değerler girdiğinizde veriler arasında boş satırlar veya sütunlar olmamalıdır.
3. Bu kodu kullandığınızda, veri aralığınız A1 hücresinden başlamalıdır.
İlgili makale:
Excel'e yeni veriler girdikten sonra bir grafik nasıl otomatik olarak güncellenir?
En İyi Ofis Üretkenlik Araçları
Kutools for Excel ile Excel Becerilerinizi Güçlendirin ve Daha Önce Hiç Olmadığı Gibi Verimliliği Deneyimleyin. Kutools for Excel, Üretkenliği Artırmak ve Zamandan Tasarruf Etmek için 300'den Fazla Gelişmiş Özellik Sunar. En Çok İhtiyacınız Olan Özelliği Almak İçin Buraya Tıklayın...
Office Tab, Office'e Sekmeli Arayüz Getirir ve İşinizi Çok Daha Kolay Hale Getirir
- Word, Excel, PowerPoint'te sekmeli düzenlemeyi ve okumayı etkinleştirin, Publisher, Access, Visio ve Project.
- Yeni pencereler yerine aynı pencerenin yeni sekmelerinde birden çok belge açın ve oluşturun.
- Üretkenliğinizi% 50 artırır ve her gün sizin için yüzlerce fare tıklamasını azaltır!
