Spreadsheet Excel kerap termasuk dropdowns sel untuk mempermudah dan / atau menyeragamkan kemasukan data. Dropdown ini dibuat menggunakan ciri pengesahan data untuk menentukan senarai penyertaan yang dibenarkan.
Untuk menetapkan senarai jatuh turun mudah, pilih sel tempat data akan dimasukkan, kemudian klik Pengesahan Data (pada tab Data ), pilih Pengesahan Data, pilih Senarai (di bawah Izinkan :), dan kemudian masukkan item senarai (dipisahkan oleh koma ) dalam medan Source : (lihat Rajah 1).
Dalam jenis dropdown asas ini, senarai penyertaan yang dibenarkan ditentukan dalam pengesahan data itu sendiri; Oleh itu, untuk membuat perubahan kepada senarai, pengguna mesti membuka dan mengedit pengesahan data. Ini mungkin sukar, bagaimanapun, untuk pengguna yang tidak berpengalaman, atau dalam kes di mana senarai pilihan adalah panjang.
Pilihan lain ialah meletakkan senarai dalam julat yang dinamakan dalam hamparan, dan kemudian nyatakan nama julat tersebut (diisi dengan tanda yang sama) dalam bidang Sumber : pengesahan data (seperti yang ditunjukkan dalam Rajah 2).
Kaedah kedua ini memudahkan untuk mengedit pilihan dalam senarai, tetapi menambah atau mengeluarkan item boleh menjadi masalah. Oleh kerana julat bernama (FruitChoices, dalam contoh kita) merujuk kepada julat tetap sel ($ H $ 3: $ H $ 10 seperti yang ditunjukkan), jika lebih banyak pilihan ditambahkan ke sel H11 atau di bawah, mereka tidak akan muncul dalam dropdown (kerana sel-sel itu bukan sebahagian daripada pelbagai FruitChoices).
Begitu juga, contohnya, penyertaan Pear dan Strawberry dipadamkan, mereka tidak lagi akan muncul dalam dropdown, tetapi sebaliknya penurunan akan termasuk dua pilihan "kosong" sejak dropdown masih merujuk ke seluruh jangkauan FruitChoices, termasuk sel kosong H9 dan H10.
Atas sebab-sebab ini, apabila menggunakan julat bernama normal sebagai sumber senarai untuk lungsur turun, julat yang dinamakan itu mesti diedit untuk memasukkan lebih atau kurang sel jika entri ditambah atau dipadamkan dari senarai.
Penyelesaian masalah ini ialah menggunakan nama pelbagai dinamik sebagai sumber untuk pilihan turun lajur. Nama pelbagai dinamik adalah salah satu yang secara automatik memperluas (atau kontrak) untuk betul-betul sepadan dengan saiz blok data apabila entri ditambahkan atau dikeluarkan. Untuk melakukan ini, anda menggunakan formula, bukannya rangkaian sel tetap, untuk menentukan julat yang dinamakan.
Bagaimana Menyediakan Julat Dinamik dalam Excel
Nama julat biasa (statik) merujuk kepada julat sel tertentu ($ H $ 3: $ H $ 10 dalam contoh kami, lihat di bawah):
Tetapi rentang dinamik ditakrifkan menggunakan formula (lihat di bawah, diambil dari hamparan berasingan yang menggunakan nama pelbagai dinamik):
Sebelum memulakan, pastikan anda memuat turun fail contoh Excel kami (sort macros telah dilumpuhkan).
Mari kita periksa formula ini secara terperinci. Pilihan untuk Buah-buahan berada dalam satu blok sel langsung di bawah tajuk (buah). Tajuk itu juga diberi nama: Buah Buahan :
Keseluruhan formula yang digunakan untuk menentukan jarak dinamik untuk pilihan Buah adalah:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX (ISBLANK (OFFSET (Buah Buih, 1, 0, 20, 1)), 0, 0), 0) -1, 20), 1)
Buah Buih 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 entri dalam senarai, tetapi terdapat juga sel kosong di bawah ini di mana entri tambahan boleh ditambah. Nombor 20 merujuk kepada keseluruhan blok di mana penyertaan boleh dibuat, bukan kepada bilangan penyertaan sebenar.
Sekarang mari kita memecahkan formula menjadi kepingan (warna-coding setiap bahagian), untuk memahami bagaimana ia berfungsi:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX (ISBLANK ( OFFSET (Buah Buih, 1, 0, 20, 1) ), 0, 0), 0) -1, 20), 1)
Bahagian "paling dalam" adalah OFFSET (Buah Buih, 1, 0, 20, 1) . Rujukan ini menyekat 20 sel (di bawah sel Buah Buih) di mana pilihan boleh dimasukkan. Fungsi OFFSET ini pada asasnya berkata: Mula di sel Buah - buahan, turun 1 baris dan lebih daripada 0 lajur, kemudian pilih kawasan yang panjang 20 baris dan lebar 1 ruangan. Jadi itu memberi kita blok 20 baris di mana pilihan Buah dimasukkan.
Sekeping formula seterusnya adalah fungsi ISBLANK :
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX ( ISBLANK (di atas), 0, 0), 0) -1, 20), 1)
Di sini, fungsi OFFSET (diterangkan di atas) telah digantikan dengan "di atas" (untuk membuat perkara lebih mudah dibaca). Tetapi fungsi ISBLANK beroperasi pada sel-sel 20-baris sel yang fungsi OFFSET ditentukan.
ISBLANK kemudian membuat satu set 20 nilai TRUE dan FALSE, 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 menjadi SALAH kerana 8 sel pertama tidak kosong dan 12 nilai terakhir akan menjadi BENAR.
Sekeping formula seterusnya adalah fungsi INDEX:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX (di atas, 0, 0), 0) -1, 20), 1)
Sekali lagi, "di atas" merujuk kepada fungsi ISBLANK dan OFFSET yang dinyatakan di atas. Fungsi INDEX mengembalikan array yang mengandungi 20 nilai TRUE / FALSE yang dicipta oleh fungsi ISBLANK.
INDEX biasanya digunakan untuk memilih nilai tertentu (atau julat nilai) daripada blok data, dengan menentukan baris dan lajur tertentu (dalam blok itu). Tetapi menetapkan input baris dan lajur kepada sifar (seperti yang dilakukan di sini) menyebabkan INDEX untuk mengembalikan array yang mengandungi keseluruhan blok data.
Sekeping formula seterusnya ialah fungsi MATCH:
= OFFSET (FruitsHeading, 1, 0, IFERROR ( MATCH (TRUE, di atas, 0) -1, 20), 1)
Fungsi MATCH mengembalikan kedudukan nilai TRUE yang pertama, dalam array yang dikembalikan oleh fungsi INDEX. Oleh kerana 8 entri pertama dalam senarai tidak kosong, 8 nilai pertama dalam array akan FALSE, dan nilai kesembilan akan TRUE (sejak baris ke-9 dalam jarak kosong).
Jadi fungsi MATCH akan mengembalikan nilai 9 . Walau bagaimanapun, dalam kes ini, kita benar-benar ingin tahu berapa banyak penyertaan dalam senarai, jadi formula mengurangkan 1 dari nilai MATCH (yang memberikan kedudukan entri terakhir). Maka pada akhirnya, MATCH (TRUE, di atas, 0) -1 mengembalikan nilai 8 .
Sekeping formula seterusnya adalah fungsi IFERROR:
= OFFSET (FruitsHeading, 1, 0, IFERROR (di atas, 20), 1)
Fungsi IFERROR mengembalikan nilai ganti, jika nilai pertama yang dinyatakan menyebabkan ralat. Fungsi ini dimasukkan sejak, jika seluruh blok sel (semua 20 baris) diisi dengan penyertaan, fungsi MATCH akan mengembalikan ralat.
Ini kerana kami memberitahu fungsi MATCH untuk mencari nilai TRUE yang pertama (dalam pelbagai nilai dari fungsi ISBLANK), tetapi jika NONE dari sel kosong, maka keseluruhan array akan diisi dengan nilai FALSE. Jika MATCH tidak dapat mencari nilai sasaran (TRUE) dalam pelbagai ia mencari, ia mengembalikan ralat.
Oleh itu, jika keseluruhan senarai penuh (dan dengan itu, MATCH mengembalikan ralat), fungsi IFERROR sebaliknya akan mengembalikan nilai 20 (mengetahui bahawa mesti ada 20 entri dalam senarai).
Akhirnya, OFFSET (FruitsHeading, 1, 0, di atas, 1) mengembalikan julat yang sebenarnya kita cari: Mulakan di sel Buah-buahan, turun 1 baris dan lebih daripada 0 lajur, kemudian pilih kawasan yang banyak baris panjang terdapat penyertaan dalam senarai (dan 1 ruangan lebar). Maka keseluruhan formula bersama-sama akan mengembalikan julat yang hanya mengandungi entri sebenar (ke sel kosong pertama).
Dengan menggunakan formula ini untuk menentukan julat yang merupakan sumber untuk jatuh turun bermakna anda boleh mengedit senarai secara bebas (menambah atau membuang entri, selagi entri yang tersambung bermula di sel teratas dan bersebelahan) dan lungsur akan selalu mencerminkan arus senarai (lihat Rajah 6).
Fail contoh (Senarai Dinamik) yang telah digunakan di sini dimasukkan dan boleh dimuat turun dari laman web ini. Makro tidak berfungsi, bagaimanapun, kerana WordPress tidak suka buku Excel dengan makro di dalamnya.
Sebagai alternatif untuk menentukan bilangan baris dalam blok senarai, blok senarai boleh diberikan nama julat sendiri, yang kemudiannya boleh digunakan dalam formula yang diubahsuai. Dalam fail contoh, senarai kedua (Nama) menggunakan kaedah ini. Di sini, blok senarai keseluruhan (di bawah tajuk "NAMES", 40 baris dalam fail contoh) diberikan nama julat NameBlock . Formula alternatif untuk menentukan NameList ialah:
= OFFSET (NamesHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX (ISBLANK ( NamesBlock ), 0, 0), 0) -1, ROWS (NamesBlock )
di mana NamesBlock menggantikan OFFSET (Buah Buih, 1, 0, 20, 1) dan ROWS (NamesBlock) menggantikan 20 (bilangan baris) dalam formula yang lebih awal.
Jadi, untuk senarai lungsur yang boleh diedit dengan mudah (termasuk oleh pengguna lain yang mungkin tidak berpengalaman), cuba gunakan nama pelbagai dinamik! Dan maklum bahawa, walaupun artikel ini telah tertumpu pada senarai dropdown, nama-nama pelbagai dinamik boleh digunakan di mana sahaja anda perlu merujuk kepada julat atau senarai yang boleh berubah-ubah. Nikmati!
