Excel elektronik tabloları genellikle veri girişini basitleştirmek ve/veya standart hale getirmek için hücre açılır listelerini içerir. Bu açılır menüler, izin verilen girişlerin bir 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'ya^{(Data Validation)} tıklayın ( Veri^(Data) sekmesinde), Veri Doğrulama'yı^{(Data Validation)} seçin, Liste'yi^(List) seçin ( İzin ver^(Allow) :)'in altında ve ardından liste öğelerini girin (virgülle ayırarak) ) Kaynak^(Source) : alanında (bkz. Şekil 1).

Bu tür temel açılır listede, izin verilen girişlerin listesi veri doğrulamanı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. Ancak bu, deneyimsiz kullanıcılar için veya seçenekler listesinin uzun olduğu durumlarda zor olabilir.

Başka bir seçenek, listeyi elektronik tablo içinde adlandırılmış bir aralığa yerleştirmek ve ardından veri doğrulamasının ^{(named range within the spreadsheet)}Kaynak^(Source) : alanında ( Şekil 2'de^{(Figure 2)} gösterildiği gibi ) bu aralık adını (önce eşittir işaretiyle) belirtmektir .

Bu ikinci yöntem, listedeki seçenekleri düzenlemeyi kolaylaştırır, ancak öğelerin eklenmesi veya çıkarılması 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), ^{(FruitChoices)}H11 veya altındaki hücrelere daha fazla seçenek eklenirse , bunlar açılır menüde görünmez (çünkü bu hücreler FruitChoices aralığının parçası değildir).

Benzer şekilde, örneğin, Armut^(Pears) ve Çilek^{(Strawberries)} girişleri silinirse, artık açılır menüde görünmezler, bunun yerine açılır menü, boş hücreler H9 ve H9 ve H10 .

Bu nedenlerle, bir açılır liste için liste kaynağı olarak normal adlandırılmış bir aralık kullanılırken, listeden girişler eklenir veya silinirse, adlandırılmış aralığın kendisi daha fazla veya daha az hücre içerecek şekilde düzenlenmelidir.

Bu soruna bir çözüm , açılır seçenekler için kaynak olarak bir dinamik aralık adı kullanmaktır. ^(dynamic)Dinamik aralık adı, girdiler eklendikçe veya kaldırılırken bir veri bloğunun boyutuyla tam olarak eşleşmesi için 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.^(formula)

Excel'de Dinamik Aralık ^{(Dynamic Range)}Nasıl^(Excel) Kurulur

Normal (statik) bir aralık adı, belirli bir hücre aralığına atıfta bulunur (örneğimizde $H$3:$H$10, aşağıya bakın):

Ancak dinamik aralık bir formül kullanılarak tanımlanır (aşağıya bakın, dinamik aralık adlarını kullanan ayrı bir elektronik tablodan alınmıştır):

Başlamadan önce, Excel örnek dosyamızı indirdiğinizden emin olun  (sıralama makroları devre dışı bırakıldı).

Bu formülü detaylı olarak inceleyelim. Meyveler için seçenekler, bir başlığın hemen altındaki bir hücre bloğundadır ( MEYVELER^(FRUITS) ). Bu başlığa ayrıca bir ad atanır: FruitsHeading :

Meyve^(Fruits) seçenekleri için dinamik aralığı tanımlamak için kullanılan formülün tamamı şudur:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)

FruitsHeading , listedeki ilk girişin bir satır yukarısındaki başlığa atıfta bulunur. 20 sayısı (formülde iki kez kullanılır) liste için maksimum boyuttur (satır sayısı) (bu istenildiği gibi ayarlanabilir).

Bu örnekte, listede yalnızca 8 giriş olduğunu, ancak bunların altında ek girişlerin eklenebileceği boş hücreler olduğunu unutmayın. 20 sayısı, gerçek giriş sayısını değil, girişlerin yapılabileceği tüm bloğu ifade eder.

Şimdi, nasıl çalıştığını anlamak için formülü parçalara ayıralım (her parçayı renkle kodlayarak):

=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 OFFSET(FruitsHeading,1,0,20,1) . Bu, seçeneklerin girilebileceği 20 hücre bloğuna ( MeyvesHeading hücresinin altında) başvurur. ^{(FruitsHeading)}Bu OFFSET işlevi temelde şunu söyler: FruitsHeading hücresinden başlayın, 1 satır aşağı ve 0 sütunun üzerine gidin, ardından 20 satır uzunluğunda ve 1 sütun genişliğinde bir alan seçin. Bu bize Fruits seçeneklerinin girildiği 20 satırlık bloğu verir.

Formülün sonraki parçası ISBLANK işlevidir:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)

Burada, OFFSET işlevi (yukarıda açıklanmıştır) "yukarıdakiler" ile değiştirilmiştir (okumayı kolaylaştırmak için). Ancak ISBLANK işlevi, ^(ISBLANK)OFFSET işlevinin tanımladığı 20 satırlık hücre aralığında çalışıyor.

ISBLANK daha sonra, ^(ISBLANK)OFFSET işlevi tarafından başvurulan 20 satırlık aralıktaki tek tek hücrelerin her birinin boş (boş) olup olmadığını gösteren bir 20 DOĞRU^(TRUE) ve YANLIŞ^(FALSE) değer kümesi oluşturur . Bu örnekte, ilk 8 hücre boş olmadığından kümedeki ilk 8 değer YANLIŞ , son 12 değer ise ^(FALSE)DOĞRU^(TRUE) olacaktır .

Formülün 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^(ISBLANK) ve OFFSET işlevlerine atıfta bulunur . 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ütun (bu 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 girdilerini sıfıra ayarlamak (burada yapıldığı gibi), INDEX'in^(INDEX) tüm veri bloğunu içeren bir dizi döndürmesine neden olur.

Formülün sonraki parçası KAÇINCI^(MATCH) işlevidir:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)

KAÇINCI işlevi, ^(MATCH)INDEX işlevi tarafından döndürülen dizi içindeki ilk DOĞRU^(TRUE) değerin konumunu döndürür . Listedeki ilk 8 giriş boş olmadığından dizideki ilk 8 değer YANLIŞ^(FALSE) , dokuzuncu değer DOĞRU^(TRUE) olacaktır ( aralığın 9. ^{satırı boş olduğu için).}

Böylece KAÇINCI işlevi ^(MATCH)9 değerini döndürür . Ancak bu durumda, listede kaç giriş olduğunu gerçekten bilmek istiyoruz, bu nedenle formül KAÇINCI^(MATCH) değerinden 1 çıkarır (bu, son girişin konumunu verir). Sonuç olarak, MATCH ( TRUE ,the Above,0)-1, ^(TRUE)8 değerini döndürür .

Formülün sonraki parçası EĞERHATA^(IFERROR) işlevidir:

=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)

EĞERHATA işlevi^(IFERROR) , belirtilen ilk değer bir hatayla sonuçlanırsa alternatif bir değer döndürür. Bu işlev dahil edilmiştir, çünkü tüm hücre bloğu (20 satırın tümü) girişlerle doldurulursa, KAÇINCI^(MATCH) işlevi bir hata döndürür.

Bunun nedeni, KAÇINCI^(MATCH) işlevine ilk DOĞRU değeri ( ^(TRUE)ISBLANK işlevindeki değerler dizisinde) aramasını söylüyor olmamızdır , ancak hücrelerin HİÇBİRİ boş değilse, tüm dizi ^(NONE)YANLIŞ^(FALSE) değerlerle doldurulacaktır . MATCH , aradığı dizide hedef değeri ( TRUE ) bulamazsa, bir hata döndürür.

Bu nedenle, tüm liste doluysa (ve dolayısıyla KAÇINCI^(MATCH) bir hata döndürürse), EĞERHATA^(IFERROR) işlevi bunun yerine 20 değerini döndürür (listede 20 giriş olması gerektiğini bilerek).

Son olarak, OFFSET(FruitsHeading,1,0,the Yukarıdaki,1)^{(OFFSET(FruitsHeading,1,0,the above,1))} aslında aradığımız aralığı döndürür: FruitsHeading hücresinden başlayın, 1 satır aşağı ve 0 sütunun üzerine gidin, ardından ne kadar uzun satır olursa olsun 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 girişleri içeren aralığı döndürür (ilk boş hücreye kadar).

Açılır listenin kaynağı olan aralığı tanımlamak için bu formülü kullanmak, listeyi özgürce düzenleyebileceğiniz anlamına gelir (kalan girişler üst hücrede başladığı ve bitişik olduğu sürece girişleri ekleme veya çıkarma) ve açılır liste her zaman geçerli olanı yansıtır. listesi (bkz . Şekil 6^{(Figure 6)} ).

Burada kullanılan örnek dosya (Dinamik Listeler) dahil edilmiştir ve bu web sitesinden indirilebilir. Bununla birlikte, makrolar çalışmaz, çünkü WordPress , içinde makro bulunan ^(WordPress)Excel kitaplarını sevmez .

Liste bloğundaki satır sayısını belirlemeye alternatif olarak, liste bloğuna daha sonra değiştirilmiş bir formülde kullanılabilen kendi aralık adı atanabilir. Örnek dosyada, ikinci bir liste ( Names ) bu yöntemi kullanır. Burada, tüm liste bloğuna ("NAMES" başlığının altında, örnek dosyada 40 satır) NameBlock^(NameBlock) aralık adı atanır . NamesList'i^(NamesList) tanımlamak için alternatif formül şu şekildedir :

=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)

burada NamesBlock , OFFSET ( FruitsHeading ,1,0,20,1^{(FruitsHeading,1,0,20,1)} ) yerine geçer ve ROWS(NamesBlock) , önceki formülde 20'nin (satır sayısı) yerini alır.

Bu nedenle, kolayca düzenlenebilen açılır listeler için (deneyimsiz olabilecek diğer kullanıcılar da dahil), dinamik aralık adlarını kullanmayı deneyin! Ayrıca, bu makale açılır listelere odaklanmış olsa da, dinamik aralık adlarının, boyut olarak değişebilen bir aralığa veya listeye başvurmanız gereken her yerde kullanılabileceğini unutmayın. Zevk almak!

Use Dynamic Range Names in Excel for Flexible Dropdowns

Excel spreadsheets often include cell dropdоwns to simplify and/or standardize data entry.  These dropdowns are creаted using the data validation feature to speсifу a list of allоwable entries.

To set up a simple dropdown list, select the cell where data will be entered, then click Data Validation (on the Data tab), select Data Validation, choose List (under Allow:), and then enter the list items (separated by commas) in the Source: field (see Figure 1).

In this type of basic dropdown, the list of allowable entries is specified within the data validation itself; therefore, to make changes to the list, the user must open and edit the data validation.  This may be difficult, however, for inexperienced users, or in cases where the list of choices is lengthy.

Another option is to place the list in a named range within the spreadsheet, and then specify that range name (prefaced with an equal sign) in the Source: field of the data validation (as shown in Figure 2).

This second method makes it easier to edit the choices in the list, but adding or removing items can be problematic.  Since the named range (FruitChoices, in our example) refers to a fixed range of cells ($H$3:$H$10 as shown), if more choices are added to the cells H11 or below, they will not show up in the dropdown (since those cells are not part of the FruitChoices range).

Likewise if, for example, the Pears and Strawberries entries are erased, they will no longer appear in the dropdown, but instead the dropdown will include two “empty” choices since the dropdown still references the entire FruitChoices range, including the empty cells H9 and H10.

For these reasons, when using a normal named range as the list source for a dropdown, the named range itself must be edited to include more or fewer cells if entries are added or deleted from the list.

A solution to this problem is to use a dynamic range name as the source for the dropdown choices.  A dynamic range name is one that automatically expands (or contracts) to exactly match the size of a block of data as entries are added or removed.  To do this, you use a formula, rather than a fixed range of cell addresses, to define the named range.

How to Setup a Dynamic Range in Excel

A normal (static) range name refers to a specified range of cells ($H$3:$H$10 in our example, see below):

But a dynamic range is defined using a formula (see below, taken from a separate spreadsheet which uses dynamic range names):

Before we get started, make sure you download our Excel example file (sort macros have been disabled).

Let’s examine this formula in detail.  The choices for Fruits are in a block of cells directly below a heading (FRUITS).  That heading is also assigned a name: FruitsHeading:

The entire formula used to define the dynamic range for the Fruits choices is:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)

FruitsHeading refers to the heading that is one row above the first entry in the list.  The number 20 (used two times in the formula) is the maximum size (number of rows) for the list (this can be adjusted as desired).

Note that in this example, there are only 8 entries in the list, but there are also empty cells below these where additional entries could be added.  The number 20 refers to the entire block where entries can be made, not to the actual number of entries.

Now let’s break down the formula into pieces (color-coding each piece), to understand how it works:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)

The “innermost” piece is OFFSET(FruitsHeading,1,0,20,1).  This references the block of 20 cells (underneath the FruitsHeading cell) where choices may be entered.  This OFFSET function basically says: Start at the FruitsHeading cell, go down 1 row and over 0 columns, then select an area that is 20 rows long and 1 column wide.  So that gives us the 20-row block where the Fruits choices are entered.

The next piece of the formula is the ISBLANK function:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)

Here, the OFFSET function (explained above) has been replaced with “the above” (to make things easier to read).  But the ISBLANK function is operating on the 20-row range of cells that the OFFSET function defines.

ISBLANK then creates a set of 20 TRUE and FALSE values, indicating whether each of the individual cells in the 20-row range referenced by the OFFSET function is blank (empty) or not.  In this example, the first 8 values in the set will be FALSE since the first 8 cells are not empty and the last 12 values will be TRUE.

The next piece of the formula is the INDEX function:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)

Again, “the above” refers to the ISBLANK and OFFSET functions described above.  The INDEX function returns an array containing the 20 TRUE / FALSE values created by the ISBLANK function.

INDEX is normally used to pick a certain value (or range of values) out of a block of data, by specifying a certain row and column (within that block).  But setting the row and column inputs to zero (as is done here) causes INDEX to return an array containing the entire block of data.

The next piece of the formula is the MATCH function:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)

The MATCH function returns the position of the first TRUE value, within the array that’s returned by the INDEX function.  Since the first 8 entries in the list are not blank, the first 8 values in the array will be FALSE, and the ninth value will be TRUE (since the 9^th row in the range is empty).

So the MATCH function will return the value of 9.  In this case, however, we really want to know how many entries are in the list, so the formula subtracts 1 from the MATCH value (which gives the position of the last entry).  So ultimately, MATCH(TRUE,the above,0)-1 returns the value of 8.

The next piece of the formula is the IFERROR function:

=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)

The IFERROR function returns an alternate value, if the first value specified results in an error.  This function is included since, if the entire block of cells (all 20 rows) are filled with entries, the MATCH function will return an error.

This is because we’re telling the MATCH function to look for the first TRUE value (in the array of values from the ISBLANK function), but if NONE of the cells are empty, then the entire array will be filled with FALSE values.  If MATCH cannot find the target value (TRUE) in array it is searching, it returns an error.

So, if the entire list is full (and therefore, MATCH returns an error), the IFERROR function will instead return the value of 20 (knowing that there must be 20 entries in the list).

Finally, OFFSET(FruitsHeading,1,0,the above,1) returns the range we are actually looking for:  Start at the FruitsHeading cell, go down 1 row and over 0 columns, then select an area that is however many rows long as there are entries in the list (and 1 column wide).  So the entire formula together will return the range that contains only the actual entries (down to the first empty cell).

Using this formula to define the range that is the source for the dropdown means you can freely edit the list (adding or removing entries, as long as the remaining entries start at the top cell and are contiguous) and the dropdown will always reflect the current list (see Figure 6).

The example file (Dynamic Lists) that’s been used here is included and is downloadable from this website. The macros don’t work, however, because WordPress doesn’t like Excel books with macros in them.

As an alternative to specifying the number of rows in the list block, the list block can be assigned its own range name, which can then be used in a modified formula.  In the example file, a second list (Names) uses this method.  Here, the entire list block (underneath the “NAMES” heading, 40 rows in the example file) is assigned the range name of NameBlock.  The alternate formula for defining the NamesList is then:

=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)

where NamesBlock replaces OFFSET(FruitsHeading,1,0,20,1) and ROWS(NamesBlock) replaces the 20 (number of rows) in the earlier formula.

So, for dropdown lists which can be easily edited (including by other users who may be inexperienced), try using dynamic range names!  And note that, although this article has been focused on dropdown lists, dynamic range names can be used anywhere you need to reference a range or list that can vary in size. Enjoy!

Related posts

• Excel'de Boş Satırları Silme

• Excel'in Hücreleri Seslendir Özelliği Nasıl Kullanılır?

• Word Belgesine Excel Çalışma Sayfası Nasıl Eklenir

• Excel'in Durum Analizi Nasıl Kullanılır?

• Excel'de Bir Satır Nasıl Onarılır

• Kolay İşbirliği İçin Bir Excel Dosyası Nasıl Paylaşılır

• Excel'de Hücreler, Sütunlar ve Satırlar Nasıl Birleştirilir

• Excel'i Google E-Tablolara Dönüştürmenin 4 Yolu

• Word ve Excel Güvenli Modda Nasıl Açılır

• Excel'de Sütunlar Nasıl Taşınır

• Birden Çok Excel Dosyasındaki Veriler Nasıl Birleştirilir

• Excel'de DÜŞEYARA Yerine Dizin Eşleştirme Ne Zaman Kullanılır?

• DÜŞEYARA gibi Excel Formüllerinde #Yok Hataları Nasıl Düzeltilir

• Excel'de Z-Skoru Nasıl Hesaplanır

• MS Excel İçin Gelişmiş Bir VBA Kılavuzu

• Excel'i MySQL'e Bağlama

• Çalışma Sayfası Verilerinizi Yazdırma için Excel'de Ortalayın

• Excel'de Onay İşareti Kullanmanın 4 Yolu

• Excel'in Durum Analizi Hedef Arama Aracını Kullanma

• Google E-Tablolar ve Microsoft Excel – Farklar Nelerdir?