Gunakan nama pelbagai dinamik dalam Excel untuk dropdowns fleksibel

Spreadsheet Excel sering merangkumi dropdown sel untuk memudahkan dan/atau menyeragamkan entri data.  Dropdown ini dibuat menggunakan ciri pengesahan data untuk menentukan senarai penyertaan yang dibenarkan.

Untuk menyediakan senarai dropdown yang mudah, pilih sel di mana data akan dimasukkan, kemudian klik Pengesahan data (pada Data tab), pilih pengesahan data, pilih Senarai (di bawah membenarkan :), dan kemudian masukkan item senarai (dipisahkan oleh koma) di Sumber: medan (lihat Rajah 1).

Kaedah kedua ini memudahkan untuk mengedit pilihan dalam senarai, tetapi menambah atau mengeluarkan item boleh menjadi masalah.  Oleh kerana julat yang dinamakan (fruitchoices, dalam contoh kami) merujuk kepada pelbagai sel tetap ($ H $ 3: $ H $ 10 seperti yang ditunjukkan), jika lebih banyak pilihan ditambah ke sel H11 atau di bawah, mereka tidak akan muncul dalam lungsur turun (Oleh kerana sel -sel tersebut bukan sebahagian daripada julat fruitchoices).

Begitu juga jika, sebagai contoh, penyertaan pear dan strawberi dipadamkan, mereka tidak lagi akan muncul dalam lungsur turun, tetapi sebaliknya dropdown akan termasuk dua pilihan "kosong" sejak jatuh turunnya masih merujuk seluruh pelbagai fruitchoices, termasuk sel kosong H9 dan H10.

Atas sebab -sebab ini, apabila menggunakan julat bernama biasa sebagai sumber senarai untuk jatuh turun, julat yang dinamakan itu sendiri mesti diedit untuk memasukkan lebih banyak atau lebih sedikit sel jika penyertaan ditambah atau dipadam dari senarai.

Penyelesaian masalah ini adalah menggunakan a dinamik nama julat sebagai sumber untuk pilihan dropdown.  Nama julat dinamik adalah salah satu yang secara automatik mengembang (atau kontrak) dengan tepat sepadan dengan saiz blok data sebagai penyertaan ditambah atau dikeluarkan.  Untuk melakukan ini, anda menggunakan a Formula, bukannya pelbagai alamat sel tetap, untuk menentukan julat yang dinamakan.

Cara Menyiapkan Julat Dinamik di Excel

Nama julat biasa (statik) merujuk kepada julat sel yang ditentukan ($ H $ 3: $ H $ 10 dalam contoh kami, lihat di bawah):

Tetapi julat dinamik ditakrifkan menggunakan formula (lihat di bawah, diambil dari spreadsheet berasingan yang menggunakan nama pelbagai dinamik):

Sebelum kita memulakan, pastikan anda memuat turun fail contoh excel kami (sort makro telah dilumpuhkan).

Mari kita periksa formula ini secara terperinci.  Pilihan untuk buah -buahan berada di blok sel secara langsung di bawah tajuk (Buah -buahan).  Tajuk itu juga diberikan nama: Buah -buahan:

Seluruh formula yang digunakan untuk menentukan julat dinamik untuk pilihan buah -buahan adalah:

= Offset (FruitSheading, 1,0, IfError (Match (True, Index (IsBlank (Offset (FruitSheading, 1,0,20,1)), 0,0), 0) -1,20), 1)

Buah -buahan merujuk kepada tajuk yang satu baris di atas entri pertama dalam senarai.  Nombor 20 (digunakan dua kali dalam formula) adalah saiz maksimum (bilangan baris) untuk senarai (ini boleh diselaraskan seperti yang dikehendaki).

Perhatikan bahawa dalam contoh ini, terdapat hanya 8 penyertaan dalam senarai, tetapi terdapat juga sel -sel kosong di bawah ini di mana penyertaan tambahan dapat ditambah.  Nombor 20 merujuk kepada keseluruhan blok di mana penyertaan boleh dibuat, bukan kepada bilangan penyertaan sebenar.

Sekarang mari kita pecahkan formula menjadi kepingan (pengekodan warna setiap bahagian), untuk memahami bagaimana ia berfungsi:

= Offset (Fruitsheading, 1,0, Iferror (Match (True, Index (Isblank (Offset (Fruitsheading, 1,0,20,1)), 0,0), 0) -1,20), 1)

Sekeping "paling dalam" adalah Offset (Fruitsheading, 1,0,20,1).  Ini merujuk blok 20 sel (di bawah sel buah) di mana pilihan boleh dimasukkan.  Fungsi offset ini pada dasarnya mengatakan: bermula di Buah -buahan sel, turun 1 baris dan lebih 0 lajur, kemudian pilih kawasan yang panjangnya 20 baris dan 1 lajur lebar.  Jadi itu memberi kita blok 20 baris di mana pilihan buah-buahan dimasukkan.

Sekeping formula seterusnya adalah Isblank fungsi:

= Mengimbangi (buah -buahan, 1,0, ifeRror (perlawanan (benar, indeks (Isblank (perkara di atas),0,0), 0) -1,20), 1)

Di sini, fungsi offset (dijelaskan di atas) telah digantikan dengan "di atas" (untuk membuat lebih mudah dibaca).  Tetapi fungsi Isblank beroperasi pada julat sel 20-baris yang fungsi offset mentakrifkan.

Isblank kemudian mencipta satu set 20 nilai yang benar dan palsu, menunjukkan sama ada setiap sel individu dalam julat 20 baris yang dirujuk oleh fungsi offset kosong (kosong) atau tidak.  Dalam contoh ini, 8 nilai pertama dalam set akan palsu kerana 8 sel pertama tidak kosong dan 12 nilai terakhir akan benar.

Sekeping formula seterusnya ialah fungsi indeks:

= Offset (Fruitsheading, 1,0, ifError (perlawanan (Benar,Indeks (di atas, 0,0),0) -1,20), 1)

Sekali lagi, "perkara di atas" merujuk kepada fungsi Isblank dan Offset yang diterangkan di atas.  Fungsi indeks mengembalikan array yang mengandungi 20 nilai sebenar / palsu yang dibuat oleh fungsi Isblank.

Indeks biasanya digunakan untuk memilih nilai tertentu (atau julat nilai) dari blok data, dengan menentukan baris dan lajur tertentu (dalam blok tersebut).  Tetapi menetapkan input baris dan lajur menjadi sifar (seperti yang dilakukan di sini) menyebabkan indeks mengembalikan array yang mengandungi keseluruhan blok data.

Sekeping formula seterusnya adalah fungsi perlawanan:

= Mengimbangi (buah -buahan, 1,0, ifeRror (Padanan (Benar, di atas, 0) -1,20), 1)

The Perlawanan fungsi mengembalikan kedudukan nilai sebenar pertama, dalam array yang dikembalikan oleh fungsi indeks.  Oleh kerana 8 penyertaan pertama dalam senarai tidak kosong, 8 nilai pertama dalam array akan palsu, dan nilai kesembilan akan benar (sejak 9^th baris dalam julat kosong).

Jadi fungsi perlawanan akan mengembalikan nilai 9.  Walau bagaimanapun, dalam kes ini, kami benar -benar ingin mengetahui berapa banyak penyertaan dalam senarai, jadi formula menolak 1 dari nilai perlawanan (yang memberikan kedudukan entri terakhir).  Jadi pada akhirnya, sepadan (benar, di atas, 0) -1 mengembalikan nilai 8.

Sekeping formula seterusnya adalah fungsi Iferror:

= Mengimbangi (buah -buahan, 1,0,Iferror (di atas, 20),1)

Fungsi iferror mengembalikan nilai alternatif, jika nilai pertama yang ditentukan hasil dalam ralat.  Fungsi ini dimasukkan sejak, jika seluruh blok sel (semua 20 baris) dipenuhi dengan penyertaan, fungsi perlawanan akan mengembalikan ralat.

Ini kerana kita memberitahu fungsi perlawanan untuk mencari nilai sebenar pertama (dalam pelbagai nilai dari fungsi Isblank), tetapi jika tiada sel -sel kosong, maka keseluruhan array akan diisi dengan nilai palsu.  Sekiranya perlawanan tidak dapat mencari nilai sasaran (benar) dalam array yang dicari, ia mengembalikan ralat.

Oleh itu, jika keseluruhan senarai penuh (dan oleh itu, perlawanan akan mengembalikan ralat), fungsi IFError sebaliknya akan mengembalikan nilai 20 (mengetahui bahawa mesti ada 20 penyertaan dalam senarai).

Akhirnya, Mengimbangi (buah -buahan, 1,0, di atas, 1) Mengembalikan julat yang sebenarnya kita cari: Mulakan di sel buah -buahan, turunkan 1 baris dan lebih dari 0 lajur, kemudian pilih kawasan yang banyak baris lama kerana terdapat penyertaan dalam senarai (dan 1 lajur lebar).  Jadi keseluruhan formula bersama -sama akan mengembalikan julat yang hanya mengandungi entri sebenar (hingga sel kosong pertama).

Menggunakan formula ini untuk menentukan julat yang merupakan sumber untuk jatuh turun bermakna anda boleh mengedit senarai secara bebas (menambah atau mengeluarkan entri, selagi penyertaan yang tinggal bermula di sel atas dan bersebelahan) dan jatuh turun akan sentiasa mencerminkan semasa Senarai (lihat Rajah 6).

Fail contoh (senarai dinamik) yang telah digunakan di sini dimasukkan dan boleh dimuat turun dari laman web ini. Namun, makro tidak berfungsi, kerana WordPress tidak menyukai buku Excel dengan makro di dalamnya.

Sebagai alternatif untuk menentukan bilangan baris dalam blok senarai, blok senarai boleh diberikan nama julatnya sendiri, yang kemudiannya boleh digunakan dalam formula yang diubah suai.  Dalam fail contoh, senarai kedua (nama) menggunakan kaedah ini.  Di sini, seluruh blok senarai (di bawah tajuk "nama", 40 baris dalam fail contoh) diberikan nama julat Nameblock.  Formula alternatif untuk menentukan senarai nama adalah:

= Offset (namesheading, 1,0, ifError (padanan (benar, indeks (Isblank (NamesBlock), 0,0), 0) -1,Baris (NamesBlock)), 1)

di mana NamesBlock menggantikan offset (buah -buahan, 1,0,20,1) dan Baris (NamesBlock) menggantikan 20 (bilangan baris) dalam formula terdahulu.

Oleh itu, untuk senarai dropdown yang boleh diedit dengan mudah (termasuk oleh pengguna lain yang mungkin tidak berpengalaman), cuba gunakan nama pelbagai dinamik!  Dan ambil perhatian bahawa, walaupun artikel ini telah difokuskan pada senarai dropdown, nama pelbagai dinamik boleh digunakan di mana sahaja anda perlu merujuk julat atau senarai yang boleh berbeza -beza. Nikmati!