Gunakan nama rentang dinamis di Excel untuk dropdown yang fleksibel

Spreadsheet Excel sering menyertakan dropdown sel untuk menyederhanakan dan/atau menstandarisasi entri data.  Dropdown ini dibuat menggunakan fitur validasi data untuk menentukan daftar entri yang diijinkan.

Untuk mengatur daftar dropdown sederhana, pilih sel di mana data akan dimasukkan, lalu klik Validasi data (di Data tab), pilih validasi data, pilih Daftar (di bawah izin :), lalu masukkan item daftar (dipisahkan oleh koma) di Sumber: Lapangan (lihat Gambar 1).

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

Demikian juga jika, misalnya, entri pir dan stroberi dihapus, mereka tidak akan lagi muncul di dropdown, tetapi sebaliknya dropdown akan mencakup dua pilihan "kosong" karena dropdown masih merujuk seluruh rentang fruitchoices, termasuk sel -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 memasukkan lebih atau lebih sedikit sel jika entri ditambahkan atau dihapus dari daftar.

Solusi untuk masalah ini adalah menggunakan a dinamis nama rentang sebagai sumber untuk pilihan dropdown.  Nama rentang dinamis adalah nama yang secara otomatis mengembang (atau kontrak) agar sesuai dengan ukuran blok data saat entri ditambahkan atau dihapus.  Untuk melakukan ini, Anda menggunakan a rumus, Daripada rentang alamat sel tetap, untuk menentukan rentang bernama.

Cara mengatur rentang dinamis di excel

Nama rentang normal (statis) mengacu pada kisaran sel yang ditentukan ($ H $ 3: $ H $ 10 dalam contoh kami, lihat di bawah):

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

Sebelum kita mulai, pastikan Anda mengunduh file contoh excel kami (sortir makro telah dinonaktifkan).

Mari kita periksa formula ini secara rinci.  Pilihan untuk buah -buahan berada di blok sel langsung di bawah heading (Buah).  Judul itu juga diberi nama: Fruitsheading:

Seluruh rumus yang digunakan untuk menentukan rentang dinamis untuk pilihan buah 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 judul yang merupakan 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 seperti yang diinginkan).

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

Sekarang mari kita uraikan formula menjadi potongan-potongan (color-coding masing-masing 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 "terdalam" 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 Fruitsheading sel, turun 1 baris dan lebih dari 0 kolom, lalu pilih area yang panjangnya 20 baris dan 1 kolom lebar.  Jadi itu memberi kita blok 20-baris di mana pilihan buah-buahan dimasukkan.

Bagian berikutnya dari formula adalah KOSONG fungsi:

= Offset (Fruitsheading, 1,0, IfError (Match (true, index (Isblank (di atas),0,0), 0) -1,20), 1)

Di sini, fungsi offset (dijelaskan di atas) telah diganti dengan "di atas" (untuk membuat segalanya lebih mudah dibaca).  Tetapi fungsi ISBLANK beroperasi pada rentang sel 20-baris yang didefinisikan oleh fungsi offset.

Isblank kemudian menciptakan satu set 20 nilai benar dan salah, menunjukkan apakah masing-masing sel individu dalam kisaran 20-row yang dirujuk oleh fungsi offset kosong (kosong) atau tidak.  Dalam contoh ini, 8 nilai pertama dalam set akan salah karena 8 sel pertama tidak kosong dan 12 nilai terakhir akan benar.

Bagian berikutnya dari rumus adalah fungsi indeks:

= Offset (Fruitsheading, 1,0, iferror (cocok (true,Indeks (di atas, 0,0),0) -1,20), 1)

Sekali lagi, "di atas" mengacu pada fungsi isblank dan offset yang dijelaskan di atas.  Fungsi indeks mengembalikan array yang berisi 20 nilai true / false yang dibuat oleh fungsi isBlank.

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

Bagian berikutnya dari formula adalah fungsi kecocokan:

= Offset (Fruitsheading, 1,0, Iferror (Cocok (benar, di atas, 0) -1,20), 1)

Itu COCOK Fungsi Mengembalikan Posisi Nilai Benar Pertama, dalam Array yang Dikembalikan oleh Fungsi Indeks.  Karena 8 entri pertama dalam daftar tidak kosong, 8 nilai pertama dalam array akan salah, dan nilai kesembilan akan benar (karena 9^th baris dalam kisaran kosong).

Jadi fungsi kecocokan akan mengembalikan nilai 9.  Namun, dalam hal ini, kami benar -benar ingin tahu berapa banyak entri dalam daftar, sehingga rumus mengurangi 1 dari nilai kecocokan (yang memberikan posisi entri terakhir).  Jadi pada akhirnya, cocok (benar, di atas, 0) -1 mengembalikan nilai 8.

Bagian berikutnya dari formula adalah fungsi IfError:

= Offset (Fruitsheading, 1,0,Iferror (di atas, 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 kecocokan akan mengembalikan kesalahan.

Ini karena kami memberi tahu fungsi kecocokan untuk mencari nilai sejati pertama (dalam array nilai dari fungsi isblank), tetapi jika tidak ada sel yang kosong, maka seluruh array akan diisi dengan nilai palsu.  Jika kecocokan tidak dapat menemukan nilai target (true) di array itu sedang mencari, ia mengembalikan kesalahan.

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

Akhirnya, Offset (Fruitsheading, 1,0, di atas, 1) Mengembalikan kisaran yang sebenarnya kami cari: mulai dari sel buah -buahan, turun 1 baris dan lebih dari 0 kolom, lalu pilih area yang namun banyak baris selama ada entri dalam daftar (dan 1 kolom lebar).  Jadi seluruh formula bersama -sama akan mengembalikan kisaran yang hanya berisi entri yang sebenarnya (turun ke sel kosong pertama).

Menggunakan rumus ini untuk menentukan kisaran yang merupakan sumber dropdown berarti Anda dapat dengan bebas mengedit daftar (menambah atau menghapus entri, selama entri yang tersisa mulai dari sel atas dan berdekatan) dan dropdown akan selalu mencerminkan arus arus Daftar (lihat Gambar 6).

File contoh (daftar dinamis) yang telah digunakan di sini disertakan dan dapat diunduh dari situs web ini. Makro tidak berfungsi, karena WordPress tidak suka buku Excel dengan makro di dalamnya.

Sebagai alternatif untuk menentukan jumlah baris di blok daftar, blok daftar dapat ditetapkan nama rentangnya sendiri, yang kemudian dapat digunakan dalam formula yang dimodifikasi.  Dalam file contoh, daftar kedua (nama) menggunakan metode ini.  Di sini, seluruh blok daftar (di bawah judul "Nama", 40 baris dalam file contoh) ditetapkan nama jangkauan NameBlock.  Formula alternatif untuk mendefinisikan daftar nama adalah:

= Offset (namaNeading, 1,0, iferror (Match (true, index (isBlank (NameBlock), 0,0), 0) -1,Baris (namesblock)), 1)

Di mana NameBlock menggantikan offset (fruitsheading, 1,0,20,1) dan Baris (namesblock) menggantikan 20 (jumlah baris) dalam formula 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 saja Anda perlu merujuk rentang atau daftar yang dapat bervariasi dalam ukuran. Menikmati!