Spreadsheet Excel^(Excel) sering menyertakan dropdown sel untuk menyederhanakan dan/atau menstandardisasi entri data. Dropdown ini dibuat menggunakan fitur validasi data untuk menentukan daftar entri yang diizinkan.

Untuk menyiapkan daftar dropdown sederhana, pilih sel tempat data akan dimasukkan, lalu klik Validasi Data^{(Data Validation)} (pada tab Data ), pilih ^(Data)Validasi Data^{(Data Validation)} , pilih Daftar^(List) (di bawah Izinkan^(Allow) :), lalu masukkan item daftar (dipisahkan dengan koma ) di bidang Sumber^(Source) : (lihat Gambar 1).

Dalam jenis dropdown dasar ini, daftar entri yang diizinkan ditentukan dalam validasi data itu sendiri; oleh karena itu, untuk melakukan perubahan pada daftar, pengguna harus membuka dan mengedit validasi data. Namun, ini mungkin sulit bagi pengguna yang tidak berpengalaman, atau dalam kasus di mana daftar pilihannya panjang.

Pilihan lain adalah menempatkan daftar dalam rentang bernama dalam spreadsheet^{(named range within the spreadsheet)} , dan kemudian tentukan nama rentang tersebut (diawali dengan tanda sama dengan) di bidang Sumber^(Source) : dari validasi data (seperti yang ditunjukkan pada Gambar 2^{(Figure 2)} ).

Metode kedua ini memudahkan untuk mengedit pilihan dalam daftar, tetapi menambahkan atau menghapus item bisa menjadi masalah. Karena rentang bernama ( FruitChoices , dalam contoh kita) mengacu pada rentang sel tetap ($H$3:$H$10 seperti yang ditunjukkan), jika lebih banyak pilihan ditambahkan ke sel H11 atau di bawahnya, mereka tidak akan muncul di dropdown (karena sel-sel itu bukan bagian dari rentang FruitChoices ).

Demikian juga jika, misalnya, entri Pears dan Strawberry^{(Strawberries)} dihapus, mereka tidak akan lagi muncul di dropdown, tetapi dropdown akan menyertakan dua pilihan "kosong" karena dropdown masih merujuk seluruh rentang FruitChoices, termasuk sel kosong H9 dan H10 .

Untuk alasan ini, saat menggunakan rentang bernama normal sebagai sumber daftar untuk dropdown, rentang bernama itu sendiri harus diedit untuk menyertakan lebih banyak atau lebih sedikit sel jika entri ditambahkan atau dihapus dari daftar.

Solusi untuk masalah ini adalah dengan menggunakan nama rentang dinamis^(dynamic) sebagai sumber untuk pilihan dropdown. Nama rentang dinamis adalah nama yang secara otomatis diperluas (atau dikontrak) agar sama persis dengan ukuran blok data saat entri ditambahkan atau dihapus. Untuk melakukannya, Anda menggunakan rumus^(formula) , bukan rentang alamat sel tetap, untuk menentukan rentang bernama.

Cara Mengatur Rentang Dinamis^{(Dynamic Range)} di Excel

Nama rentang normal (statis) mengacu pada rentang sel tertentu ($H$3:$H$10 dalam contoh kita, lihat di bawah):

Tetapi rentang dinamis didefinisikan menggunakan rumus (lihat di bawah, diambil dari spreadsheet terpisah yang menggunakan nama rentang dinamis):

Sebelum memulai, pastikan Anda mengunduh file contoh Excel kami  (makro sortir telah dinonaktifkan).

Mari kita periksa rumus ini secara rinci. Pilihan untuk Fruits berada di blok sel langsung di bawah heading ( FRUITS ). Judul itu juga diberi nama: FruitsHeading :

Seluruh rumus yang digunakan untuk menentukan rentang dinamis untuk pilihan Buah^(Fruits) adalah:

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

FruitsHeading mengacu pada heading yang berada satu baris di atas entri pertama dalam daftar. Angka 20 (digunakan dua kali dalam rumus) adalah ukuran maksimum (jumlah baris) untuk daftar (ini dapat disesuaikan sesuai keinginan).

Perhatikan bahwa dalam contoh ini, hanya ada 8 entri dalam daftar, tetapi ada juga sel kosong di bawahnya di mana entri tambahan dapat ditambahkan. Angka 20 mengacu pada seluruh blok tempat entri dapat dibuat, bukan jumlah entri yang sebenarnya.

Sekarang mari kita urai rumus menjadi beberapa bagian (kode warna setiap bagian), untuk memahami cara kerjanya:

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

Bagian "paling dalam" adalah OFFSET(FruitsHeading,1,0,20,1) . Ini merujuk pada blok 20 sel (di bawah sel FruitsHeading ) di mana pilihan dapat dimasukkan. Fungsi OFFSET ini pada dasarnya mengatakan: Mulai dari sel FruitsHeading , turun 1 baris dan lebih dari 0 kolom, lalu pilih area yang panjangnya 20 baris dan lebar 1 kolom. Jadi itu memberi kita blok 20 baris tempat pilihan Buah^(Fruits) dimasukkan.

Bagian berikutnya dari rumus adalah fungsi ISBLANK :

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

Di sini, fungsi OFFSET (dijelaskan di atas) telah diganti dengan "di atas" (untuk mempermudah membaca). Tetapi fungsi ISBLANK beroperasi pada rentang 20 baris sel yang ditentukan oleh fungsi OFFSET .

ISBLANK kemudian membuat satu set 20 nilai TRUE dan FALSE , yang menunjukkan apakah masing-masing sel individu dalam rentang 20 baris yang dirujuk oleh fungsi OFFSET kosong (kosong) atau tidak. Dalam contoh ini, 8 nilai pertama dalam kumpulan akan menjadi FALSE karena 8 sel pertama tidak kosong dan 12 nilai terakhir adalah TRUE .

Bagian berikutnya dari rumus adalah fungsi INDEX :

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

Sekali lagi, "yang di atas" mengacu pada fungsi ISBLANK dan OFFSET yang dijelaskan di atas. Fungsi INDEX mengembalikan larik yang berisi 20 nilai TRUE / FALSE yang dibuat oleh fungsi ISBLANK .

INDEX biasanya digunakan untuk mengambil nilai tertentu (atau rentang nilai) dari blok data, dengan menentukan baris dan kolom tertentu (di dalam blok itu). Tetapi menyetel input baris dan kolom ke nol (seperti yang dilakukan di sini) menyebabkan INDEX mengembalikan array yang berisi seluruh blok data.

Bagian berikutnya dari rumus adalah fungsi MATCH :

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

Fungsi MATCH mengembalikan posisi nilai TRUE pertama , dalam larik yang dikembalikan oleh fungsi INDEX . Karena 8 entri pertama dalam daftar tidak kosong, 8 nilai pertama dalam larik adalah FALSE , dan nilai kesembilan adalah TRUE (karena baris ke-9 ^dalam rentang kosong).

Jadi fungsi MATCH akan mengembalikan nilai 9 . Namun, dalam kasus ini, kami benar-benar ingin mengetahui berapa banyak entri dalam daftar, jadi rumusnya mengurangi 1 dari nilai MATCH (yang memberikan posisi entri terakhir). Jadi pada akhirnya, MATCH ( TRUE ,yang di atas,0)-1 mengembalikan nilai 8 .

Bagian berikutnya dari rumus adalah fungsi IFERROR :

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

Fungsi IFERROR mengembalikan nilai alternatif, jika nilai pertama yang ditentukan menghasilkan kesalahan. Fungsi ini disertakan karena, jika seluruh blok sel (semua 20 baris) diisi dengan entri, fungsi MATCH akan mengembalikan kesalahan.

Ini karena kami memberi tahu fungsi MATCH untuk mencari nilai TRUE pertama (dalam array nilai dari fungsi ISBLANK ), tetapi jika NONE dari sel kosong, maka seluruh array akan diisi dengan nilai FALSE . Jika MATCH tidak dapat menemukan nilai target ( TRUE ) dalam larik yang dicari, ia mengembalikan kesalahan.

Jadi, jika seluruh daftar penuh (dan karena itu, MATCH mengembalikan kesalahan), fungsi IFERROR akan mengembalikan nilai 20 (mengetahui bahwa harus ada 20 entri dalam daftar).

Akhirnya, OFFSET(FruitsHeading,1,0,the above,1) mengembalikan rentang yang sebenarnya kita cari: Mulai dari sel FruitsHeading , turun 1 baris dan lebih dari 0 kolom, lalu pilih area yang panjangnya banyak baris ada entri dalam daftar (dan lebar 1 kolom). Jadi seluruh rumus bersama-sama akan mengembalikan rentang yang hanya berisi entri aktual (hingga sel kosong pertama).

Menggunakan rumus ini untuk menentukan rentang yang merupakan sumber untuk dropdown berarti Anda dapat dengan bebas mengedit daftar (menambahkan atau menghapus entri, selama entri yang tersisa mulai dari sel atas dan bersebelahan) dan dropdown akan selalu mencerminkan saat ini daftar (lihat Gambar 6^{(Figure 6)} ).

File contoh (Daftar Dinamis) yang telah digunakan di sini disertakan dan dapat diunduh dari situs web ini. Namun, makro tidak berfungsi, karena WordPress tidak menyukai buku Excel dengan makro di dalamnya.

Sebagai alternatif untuk menentukan jumlah baris dalam blok daftar, blok daftar dapat diberi nama rentangnya sendiri, yang kemudian dapat digunakan dalam formula yang dimodifikasi. Dalam file contoh, daftar kedua ( Names ) menggunakan metode ini. Di sini, seluruh blok daftar (di bawah judul "NAMES", 40 baris dalam file contoh) diberi nama rentang NameBlock . Rumus alternatif untuk mendefinisikan NamesList kemudian:

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

di mana NamesBlock menggantikan OFFSET ( FruitsHeading,1,0,20,1 ) dan ROWS(NamesBlock) menggantikan 20 (jumlah baris) dalam rumus sebelumnya.

Jadi, untuk daftar dropdown yang dapat dengan mudah diedit (termasuk oleh pengguna lain yang mungkin tidak berpengalaman), coba gunakan nama rentang dinamis! Dan perhatikan bahwa, meskipun artikel ini telah difokuskan pada daftar dropdown, nama rentang dinamis dapat digunakan di mana pun Anda perlu merujuk rentang atau daftar yang ukurannya dapat bervariasi. Menikmati!

Use Dynamic Range Names in Excel for Flexible Dropdowns

Excel spreadѕheets often include cell dropdowns to simplify and/or standardize datа entry.  These dropdowns are created uѕing the data validation feature to specify a list of allowable 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

• Cara Menghapus Baris Kosong di Excel

• Cara Menggunakan Fitur Sel Bicara Excel

• Cara Memasukkan Lembar Kerja Excel ke dalam Word Doc

• Cara Menggunakan Analisis Bagaimana-Jika Excel

• Cara Memperbaiki Baris Di Excel

• Mengapa Anda Harus Menggunakan Rentang Bernama di Excel

• 4 Cara Menggunakan Tanda Centang di Excel

• Cara Membuat Diagram Lingkaran di Excel

• Gunakan Spike untuk Memotong dan Menempel Beberapa Item Teks di Word

• Cara Menggunakan Pernyataan If dan Nested If di Excel

• Cara Membuat Makro atau Script VBA Di Excel

• Cara Menghapus Garis Kisi di Excel

• Cara Membuat Tabel Pivot Sederhana di Excel

• Cara Menggabungkan Data Dalam Beberapa File Excel

• Mengeja Angka dengan Cepat di Word dan Excel

• Cara Menulis Rumus/Pernyataan IF di Excel

• 2 Cara Menggunakan Fungsi Transpose Excel

• Cara Menghapus Baris Duplikat di Excel

• Cara Menghitung Z-Score di Excel

• Pertahankan Referensi Sel saat Menyalin Rumus di Excel