Excel elektronik tabloları genellikle veri girişini basitleştirmek ve / veya standart hale getirmek için hücre açılır pencereleri içerir. Bu açılır menüler izin verilen girişlerin bir listesini belirlemek için veri doğrulama özelliği kullanılarak oluşturulur.
Basit bir açılır liste oluşturmak için verinin girileceği hücreyi seçin, ardından Veri Doğrulama'yı tıklayın ( Veri sekmesinde), Veri Doğrulama'yı seçin, Liste'yi seçin (İzin Ver altında) ve sonra liste öğelerini girin (virgülle ayrılmış olarak). ) Kaynak : alanında (bkz. Şekil 1).
Bu tür bir temel açılır menüde, izin verilen girişlerin listesi veri doğrulamasının kendisinde belirtilir; bu nedenle, listede değişiklik yapmak için, kullanıcının veri doğrulamasını açması ve düzenlemesi gerekir. Bununla birlikte, deneyimsiz kullanıcılar veya tercih listesinin uzun olduğu durumlarda bu zor olabilir.
Başka bir seçenek de listeyi elektronik tablo içinde adlandırılmış bir aralığa yerleştirmek ve ardından veri aralığı doğrulamasının (Şekil 2'de gösterildiği gibi) Kaynak : alanında bu aralık adını (eşit işaretli bir ön yüze sahip) belirtmektir.
Bu ikinci yöntem listedeki seçimlerin düzenlenmesini kolaylaştırır, ancak öğe eklemek veya çıkarmak problemli olabilir. Adlandırılmış aralık (örneğimizde FruitChoices) sabit bir hücre aralığına atıfta bulunduğundan (gösterildiği gibi $ H $ 3: $ H $ 10), H11 ya da altındaki hücrelere daha fazla seçenek eklenirse, açılır pencerede görünmezler. (çünkü bu hücreler FruitChoices aralığının bir parçası değildir).
Aynı şekilde, örneğin, Armut ve Çilek girişleri silinirse, bunlar açılır menüde artık görünmeyeceklerdir; ancak, açılır listenin açılmasında, H9 ve H10.
Bu nedenlerden dolayı, bir açılır listenin liste kaynağı olarak normal bir adlandırılmış aralık kullanılırken, girişler listeden eklenir veya silinirse, adlandırılmış aralığın kendisinin daha fazla veya daha az hücre içerecek şekilde düzenlenmesi gerekir.
Bu soruna bir çözüm, açılan seçimler için kaynak olarak dinamik bir aralık adı kullanmaktır. Dinamik aralık adı, girdiler eklendikçe veya kaldırılırken veri bloğunun boyutuyla tam olarak eşleşecek şekilde otomatik olarak genişleyen (veya daralan) bir addır. Bunu yapmak için, adlandırılmış aralığı tanımlamak için sabit bir hücre adresi aralığı yerine bir formül kullanırsınız.
Excel'de Dinamik Aralık Ayarlama
Normal (statik) bir aralık adı, belirtilen hücre aralığına atıfta bulunur (örneğimizde $ H $ 3: $ H $ 10, aşağıya bakınız):
Ancak, bir formül kullanılarak dinamik bir aralık tanımlanır (aşağıya bakın, dinamik aralık adlarını kullanan ayrı bir elektronik tablodan alınmış):
Başlamadan önce, Excel örnek dosyamızı indirdiğinizden emin olun (sıralama makroları devre dışı bırakıldı).
Bu formülü ayrıntılı olarak inceleyelim. Meyvelerin seçenekleri, bir başlığın ( MEYVELER ) hemen altındaki bir hücre bloğundadır. Bu başlığa ayrıca bir ad verilir: FruitsHeading :
Meyve seçenekleri için dinamik aralığı tanımlamak için kullanılan formülün tamamı:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (GERÇEK INDEX (ISBLANK ())) 1, 0, 20, 1, (0, 0 FruitsHeading OFFSET, 0) -1, 20), 1)
FruitsHeading, listedeki ilk girişin bir satır üstündeki başlığa işaret eder. 20 sayısı (formülde iki kez kullanılır), listedeki maksimum boyuttur (satır sayısı) (bu istediğiniz şekilde ayarlanabilir).
Bu örnekte, listede yalnızca 8 giriş bulunduğunu, ancak bunların altında ilave girişlerin eklenebileceği boş hücreler bulunduğunu unutmayın. 20 sayısı, gerçek kayıt sayısına değil, girişlerin yapılabileceği bloğun tamamına işaret eder.
Şimdi nasıl çalıştığını anlamak için formülü parçalara ayıralım (her parçanın rengini kodlayan):
= OFFSET (Meyve Başı, 1, 0, IFERROR (MAÇ (DOĞRU, İNDEKS (ISBLANK ( OFSET (Meyve Baş, 1, 0, 20, 1) )), 0, 0), 0) -1, 20), 1)
“En içteki” parça OFSET'tir (FruitsHeading, 1, 0, 20, 1) . Bu, seçimlerin girilebileceği 20 hücreli bloğa (FruitsHeading hücresinin altında) atıfta bulunur. Bu OFFSET işlevi temel olarak şunları söyler: FruitsHeading hücresinden başlayın, 1 satır aşağı ve 0 sütun üstüne gidin, sonra 20 satır uzunluğunda ve 1 sütun genişliğinde bir alan seçin. Bu da bize Meyve seçimlerinin girildiği 20 sıralı bloğu veriyor.
Formülün bir sonraki parçası ISBLANK işlevidir:
= OFSET (Meyve Baş, 1, 0, IFERROR (MAÇ (DOĞRU, DİZİN ( ISBLANK (yukarıdaki)), 0, 0), 0) -1, 20), 1)
Burada, OFSET işlevi (yukarıda açıklanmıştır) “yukarıdaki” ile değiştirilmiştir (okunmasını kolaylaştırmak için). Ancak ISBLANK işlevi, OFFSET işlevinin tanımladığı 20 sıra hücre aralığında çalışıyor.
ISBLANK daha sonra, OFFSET işlevi tarafından başvurulan 20 satırlık aralıktaki hücrelerin her birinin boş (boş) olup olmadığını belirten 20 TRUE ve FALSE değeri kümesi oluşturur. Bu örnekte, ilk 8 hücre boş olmadığından ve son 12 değer TRUE olacağından, kümedeki ilk 8 değer FALSE olacaktır.
Formülün bir sonraki parçası, INDEX işlevidir:
= OFSET (Meyve Başı, 1, 0, IFERROR (MAÇ (DOĞRU, İNDEKS (yukarıdaki, 0, 0), 0) -1, 20), 1)
Yine, “yukarıdaki” yukarıda açıklanan ISBLANK ve OFFSET işlevlerini ifade eder. INDEX işlevi, ISBLANK işlevi tarafından oluşturulan 20 TRUE / FALSE değerini içeren bir dizi döndürür.
INDEX normalde, belirli bir satır ve sütunu (o blok içinde) belirterek, bir veri bloğundan belirli bir değeri (veya değer aralığını) seçmek için kullanılır. Ancak satır ve sütun girişlerini sıfıra ayarlamak (burada yapıldığı gibi), INDEX'in tüm veri bloğunu içeren bir dizi döndürmesini sağlar.
Formülün bir sonraki parçası MATCH işlevidir:
= OFSET (Meyve Başı, 1, 0, IFERROR ( MAÇ (DOĞRU, yukarıdaki, 0) -1, 20), 1)
MATCH işlevi, INDEX işlevi tarafından döndürülen dizi içindeki ilk TRUE değerinin konumunu döndürür. Listedeki ilk 8 giriş boş olmadığı için dizideki ilk 8 değer YANLIŞ, dokuzuncu değer ise DOĞRU (aralıktaki 9. satır boş olduğundan).
Böylece, MATCH işlevi 9 değerini döndürür. Bununla birlikte, bu durumda, listede kaç giriş olduğunu gerçekten bilmek istiyoruz; bu nedenle formül, MATCH değerinden 1'i çıkarır (son girişin konumunu verir). Sonuçta, MATCH (TRUE, yukarıdaki, 0) -1, 8 değerini döndürür.
Formülün bir sonraki parçası IFERROR işlevidir:
= OFSET (Meyve Başı, 1, 0, IFERROR (yukarıdaki, 20), 1)
Belirtilen ilk değer hatayla sonuçlanırsa, IFERROR işlevi alternatif bir değer döndürür. Bu işlev, tüm hücre bloğunun (20 satırın tümü) girişlerle dolması durumunda, MATCH işlevi bir hata döndüreceğinden dahil edilir.
Bunun nedeni, MATCH işlevine ilk TRUE değerini (ISBLANK işlevinden gelen değerler dizisinde) aramasını söylememizdir, ancak hücrelerin hiçbiri boş değilse, tüm dizi FALSE değerleriyle doldurulur. MATCH, aradığı dizide hedef değeri (TRUE) bulamazsa, bir hata döndürür.
Bu nedenle, listenin tamamı doluysa (ve bu nedenle, MATCH bir hata verir), IFERROR işlevi bunun yerine 20 değerini döndürecektir (listede 20 giriş olması gerektiğini bilerek).
Sonunda, OFFSET (FruitsHeading, 1, 0, yukarıdaki, 1) gerçekte aradığımız aralığı döndürür: FruitsHeading hücresinde başlayın, 1 satır aşağı ve 0 sütunun üstüne gidin, daha sonra birçok satır uzunluğundaki bir alanı seçin Listede girişler var (ve 1 sütun genişliğinde). Böylece, formülün tamamı birlikte yalnızca gerçek girdileri içeren aralığı döndürür (ilk boş hücreye kadar).
Açılan menünün kaynağı olan aralığı tanımlamak için bu formülü kullanmak, listeyi serbestçe düzenleyebileceğiniz anlamına gelir (kalan girişler üst hücreden başlayıp bitişik olduğu sürece girişleri ekleme veya kaldırma) ve açılan menü her zaman geçerli olacaktır. Liste (bkz. Şekil 6).
Burada kullanılan örnek dosya (Dinamik Listeler) dahil edilmiştir ve bu web sitesinden indirilebilir. Ancak, makrolar çalışmaz, çünkü WordPress, içinde makro bulunan Excel kitaplarını sevmez.
Liste bloğundaki satır sayısını belirlemeye bir alternatif olarak, liste bloğuna kendi aralık adı atanabilir, bu daha sonra değiştirilmiş bir formülde kullanılabilir. Örnek dosyada, ikinci bir liste (İsimler) bu yöntemi kullanır. Burada, tüm liste bloğu (“NAMES” başlığının altında, örnek dosyadaki 40 satır) NameBlock aralığına atanmıştır . NamesList'in tanımlanması için alternatif formül:
= OFFSET (İsim Başlığı, 1, 0, IFERROR (MAÇ (DOĞRU, İNDEKS (ISBLANK ( NamesBlock ), 0, 0), 0) -1, ROWS (NamesBlock) ), 1)
burada NamesBlock, OFFSET (FruitsHeading, 1, 0, 20, 1) ve ROWS (NamesBlock), önceki formülde 20 (satır sayısı ) yerine geçer.
Bu nedenle, kolayca düzenlenebilen açılır listeler için (deneyimsiz diğer kullanıcılar da dahil), dinamik aralık adlarını kullanmayı deneyin! Ayrıca, bu makalenin açılır listelere odaklanmasına rağmen, dinamik aralık adlarının, boyut olarak değişebilen bir aralık veya listeye başvurmak için ihtiyacınız olan her yerde kullanılabileceğini unutmayın. Keyfini çıkarın!
