Excel'de açılır liste nasıl otomatik olarak güncellenir?
Açılır listeler, özellikle günlük raporlama, envanter seçimi ve veri sınıflandırma görevleri için veri girişini daha standartlaştırılmış ve verimli hale getirmek amacıyla Excel'de sıkça kullanılır. Ancak, birçok kullanıcı ortak bir sınırlama ile karşı karşıya kalır: Orijinal kaynak aralığının hemen altına yeni öğeler eklediğinizde, açılır liste bu eklemeleri otomatik olarak içermeyecektir. Excel varsayılan olarak yalnızca başlangıçta belirtilen aralığı tanır, bu nedenle bu aralığın dışındaki yeni girişler varsayılan olarak açılır listede görünmez. Bu sorunu çözmek için, Excel, yeni veri eklediğinizde otomatik olarak genişleyen dinamik açılır liste oluşturmak için çeşitli yöntemler sunar.
Bu kılavuz, Excel'de otomatik olarak güncellenen açılır liste uygulamak için pratik yöntemler tanıtmaktadır. Özellikle düzenli olarak büyüyen tablolar ve listelerde bakım çabasını ve potansiyel giriş hatalarını azaltmaya yardımcı olur.
Açılır listeyi formülle otomatik olarak güncelleme
Açılır listenin otomatik olarak güncellenmesini gerektiren birkaç senaryo vardır — örneğin, bir ürün listesini sürdürmek, kayıt formundaki üyeleri yönetmek veya düzenli olarak değiştirilen proje görevlerini takip etmek gibi. Bu yöntem, OFFSET fonksiyonunu kullanarak dinamik bir aralık oluşturur, böylece sütuna yeni girişler ekledikçe açılır listeniz tüm öğeleri otomatik olarak içerir.
1. Açılır liste eklemek istediğiniz hücreyi seçin, ardından Veri > Veri Doğrulaması > Veri Doğrulaması bölümüne gidin. Ekran görüntüsüne bakın:
2. Açılan Veri Doğrulaması diyalog kutusunda Ayarlar sekmesine gidin, ardından Liste seçeneğini İzin Ver seçeneklerinden seçin, ardından aşağıdaki dinamik aralık formülünü Kaynak kutusuna girin:
=OFFSET($A$2,0,0,COUNTA(A:A)-1)
Parametre açıklaması ve pratik ipuçları:
- A2, amaçlanan veri aralığınızın ilk hücresidir. Gerçek listenizin başlangıç hücresine uyacak şekilde buna göre ayarlayın.
- A:A, listenizin verilerini içeren tüm sütunu ifade eder. Bu kurulum, sütuna daha fazla öğe ekledikçe işlevin aralık boyutunu dinamik olarak yeniden hesaplamasını sağlar.
- Sütunun içinde boş hücreler varsa veya alt başlıklar kullanıyorsanız, açılır listenizde boş öğelerden kaçınmak için formülü ayarlamanız veya veri yerleşiminde tutarlılığı sağlamalısınız.
- Büyük veri setleri için, OFFSET gibi uçucu işlevlerin her değişiklikte yeniden hesaplanması nedeniyle performans üzerinde hafif bir etkisi olabileceğini unutmayın.
3. Tamam'a tıklayın. Artık orijinal sütuna yeni veri girdiğinizde otomatik olarak güncellenen bir açılır listeniz var. Beklenen aralığa daha fazla öğe eklediğinizde, bunlar anında açılır listede seçilebilir değerler olarak görünecektir.
Sorun giderme ve ipuçları:
- Açılır liste beklenmedik boş girişler gösteriyorsa, kaynak sütununuzda fazladan boşluklar veya gizli satırlar olup olmadığını kontrol edin.
- Formül bir hata döndürüyorsa, verilerinizde bitişik olmayan aralıklar veya tamamen boş sütunlar bulunmadığını doğrulayın.
- Listeniz 2. satırdan başka bir yerden başlıyorsa, kaynak formülünüzü genişletmeyi unutmayın, hem hücre referansını hem de COUNTA(A:A)’yı uygun şekilde değiştirin.
Tabloyu açılır liste kaynağı olarak kullan (yeni öğelerle otomatik genişler)
Açılır listeniz için bir Excel Tablosu'nu kaynak aralık olarak kullanmak, verimli ve acemi dostu bir yaklaşımdır. Excel Tabloları, yeni öğeler eklendikçe otomatik olarak genişler, bu nedenle açılır liste manuel olarak aralık referanslarını veya formülleri ayarlamadan güncel kalır.
Bu yöntem, çalışan listeleri, envanteri veya etkinlik kayıt formlarını yöneten kullanıcılar için özellikle uygundur. Ana avantajı, güncel listeleri sürdürmede sadelik ve güvenilirdir, ancak Tabloların veri doğrulamasında çapraz çalışma kitabı referanslarını desteklemediği için bu yaklaşım kaynak veriler aynı sayfada veya çalışma kitabında olduğunda en iyi şekilde çalışır.
1. Kaynak veri aralığınızı vurgulayın (örneğin, A2:A6).
2. Ekle sekmesine gidin ve Tablo seçeneğini seçin. Listeniz başlıklar içeriyorsa “Tablomun başlıkları var” kutusunun işaretli olduğundan emin olun.
3. Excel, aralığınızı bir Tablo olarak biçimlendirir. Varsayılan olarak Table1 adı verilebilir (Tablo Tasarımı sekmesinden Tablo Adı kutusunu kullanarak Tablo adını kontrol edebilir veya değiştirebilirsiniz).
4. Açılır listeye ihtiyacınız olan hücreye tıklayın, ardından Veri > Veri Doğrulaması bölümüne gidin.
5. İzin Ver açılır listesinden Liste seçeneğini seçin ve ardından Kaynak kutusuna Tablonuzun sütununa bir referans girin, örneğin:
=INDIRECT("Table1[Column1]")6. Tamam'a tıklayın. Artık, Tablo'nun altına yeni veri eklediğinizde, sütun ve açılır liste yeni girişleri otomatik olarak güncelleyerek içerecektir.
Notlar ve ipuçları:
- Excel Tabloları, veriler değiştiğinde genişleyen ve daralan yapılandırılmış bir aralık sağlar, bu da sık sık değişmesi beklenen listeler için idealdir.
- Açılır listenizi başka bir sayfada referans göstermeniz gerekiyorsa
=INDIRECT("Table1[Column1]")kullanın, çünkü bazı Excel sürümlerinde doğrudan Tablo referansları veri doğrulamasında mevcut sayfaya sınırlı olabilir. - Bu yaklaşım, listeniz yalnızca boş olmayan girişler içeriyorsa açılır listede boş değerlerden kaçınır.
Açılır kaynak aralığını otomatik olarak güncellemek için VBA kullanın
Özellikle uzun listelerle çalışırken veya çalışma kitabı bakım görevlerini otomatikleştirirken gelişmiş ve otomatik senaryolar için, yeni veri eklendiğinde açılır listenizde kullanılan aralığı otomatik olarak güncellemek için VBA kodu kullanabilirsiniz. Bu, birden fazla açılır listenin gelişen kaynak listelerini yansıtması gereken karmaşık çözümlerde veya birden fazla kullanıcı için açılır listeleri yönetirken yararlıdır.
1. Alt+F11 tuşlarına basarak VBA düzenleyiciyi açın, VBAProjesi'nde veri doğrulamanızın bulunduğu sayfaya çift tıklayın.
2. Aşağıdaki kodu modüle kopyalayıp yapıştırın.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sourceColumn As Range
Dim validationCell As Range
Dim lastRow As Long
Set sourceColumn = Me.Range("A:A") ' Change to your source column
If Not Intersect(Target, sourceColumn) Is Nothing Then
Application.EnableEvents = False
lastRow = Me.Cells(Me.Rows.Count, sourceColumn.Column).End(xlUp).Row
Set validationCell = Me.Range("D1:D100") ' Change to your validation cell
With validationCell.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
Formula1:="=$A$1:$A$" & lastRow
End With
Application.EnableEvents = True
End If
End Sub
3. Ardından kod penceresini kapatın. Kaynak aralığınıza her veri eklediğinizde, açılır liste otomatik olarak güncellenecektir.
- Kaynak sütun ("A:A", verilerin eklendiği yer)
- Doğrulama hücresi/aralığı ("D1:D100", açılır listenin bulunduğu yer)
- Sayfada değişiklik yapıldığında kod otomatik olarak çalışır
- Son veri içeren satırı bulur ve buna göre doğrulama aralığını günceller
- Bunun çalışması için makroları etkinleştirdiğinizden emin olun
Kutools AI ile Excel Sihirini Keşfedin
- Akıllı Yürütme: Hücre işlemleri gerçekleştirin, verileri analiz edin ve grafikler oluşturun—tümü basit komutlarla sürülür.
- Özel Formüller: İş akışlarınızı hızlandırmak için özel formüller oluşturun.
- VBA Kodlama: VBA kodunu kolayca yazın ve uygulayın.
- Formül Yorumlama: Karmaşık formülleri kolayca anlayın.
- Metin Çevirisi: Elektronik tablolarınız içindeki dil engellerini aşın.
İlgili Makaleler:
En İyi Ofis Verimlilik Araçları
Kutools for Excel ile Excel becerilerinizi güçlendirin ve benzersiz bir verimlilik deneyimi yaşayın. Kutools for Excel, üretkenliği artırmak ve zamandan tasarruf etmek için300'den fazla Gelişmiş Özellik sunuyor. İhtiyacınız olan özelliği almak için buraya tıklayın...
Office Tab, Ofis uygulamalarına sekmeli arayüz kazandırır ve işinizi çok daha kolaylaştırır.
- Word, Excel, PowerPoint'te sekmeli düzenleme ve okuma işlevini etkinleştirin.
- Yeni pencereler yerine aynı pencerede yeni sekmelerde birden fazla belge açıp oluşturun.
- Verimliliğinizi %50 artırır ve her gün yüzlerce mouse tıklaması azaltır!
Tüm Kutools eklentileri. Tek kurulum
Kutools for Office paketi, Excel, Word, Outlook & PowerPoint için eklentileri ve Office Tab Pro'yu bir araya getirir; Office uygulamalarında çalışan ekipler için ideal bir çözümdür.
- Hepsi bir arada paket — Excel, Word, Outlook & PowerPoint eklentileri + Office Tab Pro
- Tek kurulum, tek lisans — dakikalar içinde kurulun (MSI hazır)
- Birlikte daha verimli — Ofis uygulamalarında hızlı üretkenlik
- 30 günlük tam özellikli deneme — kayıt yok, kredi kartı yok
- En iyi değer — tek tek eklenti almak yerine tasarruf edin