Excel elektronik tabloları, veri girişini basitleştirmek ve / veya standart hale getirmek için genellikle hücre açılır pencereleri içerir. Bu açılır menüler izin verilen girişlerin listesini belirtmek için veri doğrulama özelliği kullanılarak oluşturulur.
Basit bir açılır liste oluşturmak için verilerin girileceği hücreyi seçin, ardından Veri Doğrulama(Verisekmesinde), Veri Doğrulama'yı seçin, Liste'yi (İzin Ver altında) seçin ve ardından Kaynak: alan (bkz. Şekil 1).
Bu temel açılır menüde izin verilen girişlerin listesi belirtilir veri doğrulama içinde; 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 için veya tercihler listesinin uzun olduğu durumlarda zor olabilir.
Başka bir seçenek de listeyi bir elektronik tablodaki adlandırılmış aralık içine koymak ve ardından belirtmek veri doğrulama alanının Kaynak: alanında (eşit bir işaret ile önceden gösterilen) bu aralık adı (Şekil 2'de gösterildiği gibi).
Bu ikinci yöntem listedeki seçimlerin düzenlenmesini kolaylaştırır, ancak öğe eklemek veya çıkarmak sorunlu 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 veya altındaki hücrelere daha fazla seçenek eklenirse, açılır pencerede görünmezler. (bu hücreler FruitChoices aralığının bir parçası olmadığından).
Aynı şekilde, örneğin, Armut ve Çilek girişleri silinirse, açılır menüde artık görünmeyecek, ancak bunun yerine açılan menüde iki Açılan menüden “boş” seçimler, H9 ve H10 boş hücreleri de dahil olmak üzere tüm FruitChoices ürün yelpazesini gösteriyor.
Bu nedenlerden dolayı, bir açılan menü listesi için normal olarak adlandırılmış bir aralık kullanılırken, adlandırılmış aralık Girişler listeye eklenir veya listeden silinirse daha fazla veya daha az hücre içerecek şekilde düzenlenmesi gerekir.
Bu soruna bir çözüm dinamikaçılır seçimler için kaynak olarak aralık adı. Dinamik aralık adı, girdiler eklendikçe veya kaldırıldıklarında 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 formulakullanın.
Dinamik Ayarlama Excel'deki Aralık
Normal (statik) bir aralık adı, belirtilen hücre aralığına karşılık gelir (örneğimizde $ H $ 3: $ H $ 10, aşağıya bakın):
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 dosyası 'ımızı indirdiğinizden emin olun (sıralama makroları devre dışı bırakıldı).
Bu formülü ayrıntılı olarak inceleyelim. Meyvelerin seçenekleri, doğrudan bir başlığın altında bulunan bir hücre bloğundadır (MEYVE). Bu başlığa ayrıca bir ad verilir: FruitsHeading
Dinamik aralığı tanımlamak için kullanılan formülün tamamı Meyve seçenekleri şöyledir:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
Meyvelerin Avı, listedeki ilk girişin üstünde bir satır bulunan başlığı ifade eder. 20 sayısı (formülde iki kez kullanılır) listenin maksimum boyutu (satır sayısı) (bu istediğiniz şekilde ayarlanabilir).
Bu örnekte, yalnızca 8 giriş olduğunu unutmayın. Listede, ancak bunların altında ek girdilerin eklenebileceği boş hücreler de var. 20 rakamı, girişlerin yapılabileceği tüm bloğa atıfta bulunur, fiili giriş sayısına değil.
Şimdi nasıl çalıştığını anlamak için formülü parçalara ayıralım (her parçanın renk kodlaması). :
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
“En içteki” parça OFSET (FruitsHeading, 1,0,20,1)'dir. 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: FruitsHeadinghücresinden başlayın, 1 satır aşağı ve 0 sütuntan aşağı 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 satırlık bloğu veriyor.
Formülün bir sonraki parçası ISBLANKişlevidir:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),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 satırlık hücre aralığı üzerinde çalışıyor.
ISBLANK daha sonra her 20 hücrede bulunan hücrelerin her birinin 20'de olup olmadığını belirten 20 TRUE ve FALSE değeri oluşturuyor. OFFSET işlevi tarafından başvurulan satır aralığı boş (boş) veya değil. Bu örnekte, ilk 8 hücre boş olmadığından ve son 12 değerin DOĞRU olacağı için kümedeki ilk 8 değer FALSE olacaktır.
Formülün bir sonraki parçası INDEX işlevidir:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,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 DOĞRU / YANLIŞ değerleri içeren bir dizi döndürür.
INDEXnormalde belirli bir değeri (veya değer aralığını) çıkarmak için kullanılır. belirli bir satır ve sütunu belirterek bir veri bloğu (bu bloğun içinde). 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ürmesine neden olur.
Formülün bir sonraki parçası MATCH işlevidir:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)
MATCHiş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 thsatırının boş olması nedeniyle) olacaktır.
Öyleyse MATCH işlevi 9değ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 (son girişin konumunu veren) 1 değerini çıkarır. 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:
=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)
Belirtilen ilk değer bir 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 doluysa, MATCH işlevi bir hata döndüreceğinden dahil edilir.
Bunun nedeni, MATCH işlevine bakmamızı söylediğimizdir. ilk TRUE değeri (ISBLANK işlevinden gelen değerler dizisinde), ancak hücrelerin YOK boşsa, dizinin tamamı FALSE değerleriyle doldurulur. Eğer MATCH aradığı dizide hedef değeri (DOĞRU) bulamazsa, bir hata döndürür.
Yani tüm liste doluysa (ve dolayısıyla MATCH bir hata döndürür), IFERROR işlevi bunun yerine, 20 değerini döndürün (listede 20 giriş olması gerektiğini bilerek).
Sonunda, OFFSET (FruitsHeading, 1,0, yukarıdaki, 1), aradığımız aralık: FruitsHeading hücresinde başlayın, 1 satır aşağı ve 0 sütun üzerine gidin, ardından listede girdiler olduğu sürece (ve 1 sütun genişliğinde) birçok satırdan oluşan bir alan seçin. Böylece formülün tamamı birlikte yalnızca asıl girişleri içeren aralığı döndürür (ilk boş hücreye kadar).
Açılan menü için kaynak olan aralığı tanımlamak için bu formülü kullanmak serbestçe düzenleyebileceğiniz anlamına gelir Liste (kalan girişler üst hücrede başladığı ve bitişik olduğu sürece girişleri ekleme veya kaldırma) ve açılır menü her zaman geçerli listeyi yansıtır (bkz. Şekil 6).
Burada kullanılan örnek dosya (Dinamik Listeler) bu web sitesine dahil edilmiştir ve 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 atanabilir. değiştirilmiş bir formülde kullanılabilen kendi aralık adı. Örnek dosyada, ikinci bir liste (İsimler) bu yöntemi kullanır. Burada, tüm liste bloğuna (“NAMES” başlığı altında, örnek dosyadaki 40 satır), AdBlockaralığının adı atanır. Adlar Listesini tanımlamanın alternatif formülü şudur: NamesBlock, OFFSET (FruitsHeading, 1,0,20,1) ve >ROWS (NamesBlock)önceki formülün 20'sini (satır sayısı) değiştirir. Böylece, 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. Tadını çıkarın!=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)
İlgili Mesajlar: