Kami baru-baru ini menyelidiki asas-asas pengesahan data Excel, meneroka bagaimana untuk menyediakan senarai drop-down yang mudah menggunakan senarai yang dipisahkan koma, julat sel, atau bernama.

Dalam sesi hari ini, kami akan menyelidiki lebih mendalam ke dalam fungsi ini, dengan memberi tumpuan kepada membuat senarai drop-down cascading yang menyesuaikan pilihan mereka berdasarkan pemilihan yang dibuat dalam drop-down awal. Pada asasnya, kami akan membina senarai pengesahan data Excel yang bergantung kepada nilai yang dipilih dalam senarai lain.

Mewujudkan senarai drop-down bergantung pelbagai peringkat dalam Excel adalah mudah. Anda memerlukan beberapa julat bernama dan formula tidak langsung. Teknik ini serasi dengan semua versi Excel dari 365 kembali ke 2010.

1. Masukkan penyertaan untuk senarai drop-down

Mulailah dengan memasukkan item yang anda inginkan dalam senarai drop-down anda, dengan setiap senarai dalam lajur berasingan. Sebagai contoh, untuk membuat dropdown cascading untuk pengeksport buah -buahan, lajur A Lembaran Sumber saya ( Buah ) boleh menyenaraikan item untuk dropdown pertama, manakala tiga lajur lain menyenaraikan item untuk dropdowns bergantung.

2. Menubuhkan julat bernama

Seterusnya, anda perlu menamakan senarai utama anda dan setiap senarai bergantung. Anda boleh melakukan ini dengan sama ada menambah nama baru dalam tetingkap Pengurus Nama (tab Formula > Pengurus Nama> Baru) atau secara langsung menaip nama dalam kotak Nama .

Nota. Ketahui bahawa jika baris pertama anda berfungsi sebagai tajuk lajur, seperti yang ditunjukkan dalam tangkapan skrin di atas, anda harus mengecualikannya dari julat yang dinamakan.

Untuk arahan terperinci, lihat bagaimana untuk menentukan nama dalam Excel.

Perkara penting yang perlu diperhatikan:

1. Senarai drop-down pertama harus mengandungi entri satu perkataan, seperti aprikot , mangga , oren . Untuk item berbilang kata, rujuk bagaimana untuk membuat dropdown cascading dengan entri berbilang kata.
2. Senarai bergantung mesti dinamakan tepat sebagai entri yang sama dalam senarai utama. Sebagai contoh, senarai yang ditunjukkan apabila " mangga " dipilih harus dinamakan mangga .

Selepas disediakan, anda boleh menggunakan Ctrl F3 untuk membuka tetingkap Pengurus Nama dan mengesahkan nama dan rujukan semua senarai anda.

3. Buat senarai drop-down pertama (utama)

1. Pilih sel (s) di mana anda mahu senarai drop-down utama anda muncul, sama ada pada lembaran yang sama atau lain.

2. Navigasi ke tab Data , pilih Pengesahan Data , dan konfigurasikan senarai juntai bawah menggunakan julat bernama dengan memilih senarai di bawah membenarkan dan memasukkan nama julat dalam kotak sumber .

   Untuk panduan terperinci, lihat membuat senarai drop down berdasarkan julat yang dinamakan.

   Anda kemudian akan melihat menu drop-down di lembaran kerja anda seperti ini:

   

4. Sediakan senarai drop-down bergantung

Pilih sel (s) untuk menu drop-down bergantung dan gunakan pengesahan data Excel seperti pada langkah sebelumnya. Kali ini, masukkan formula berikut dalam bidang sumber :

=INDIRECT(A2)

Di mana A2 adalah sel dengan senarai drop-down pertama (primer) anda.

Jika A2 kosong, anda mungkin melihat mesej ralat yang menyatakan " sumber yang kini menilai kesilapan. Adakah anda mahu meneruskan? "

Anda boleh mengklik dengan selamat ya , dan sebaik sahaja anda memilih item dari drop-down pertama, senarai kedua, bergantung akan memaparkan entri yang sepadan.

5. Tambah senarai drop-down bergantung ketiga (pilihan)

Sekiranya perlu, anda boleh menambah senarai drop-down cascading ketiga yang bergantung kepada sama ada pemilihan dalam drop-down kedua atau pilihan dalam dua dropdown pertama.

Menyiapkan jatuh turun ketiga bergantung pada senarai kedua

Buat jenis drop-down jenis ini sama seperti menu drop-down bergantung kedua, dengan mengingati dua perkara penting yang disebut tadi.

Sebagai contoh, untuk menunjukkan senarai kawasan dalam lajur C berdasarkan negara yang dipilih dalam lajur B, buat senarai wilayah untuk setiap negara dan namakannya sama seperti negara muncul dalam lungsur kedua. Sebagai contoh, senarai untuk wilayah India harus dinamakan "India", dan untuk wilayah China, "China".

Kemudian, pilih sel untuk dropdown ketiga (C2 dalam contoh kami) dan gunakan pengesahan data Excel dengan formula ini (B2 mengandungi menu drop-down kedua dengan negara):

=INDIRECT(B2)

Memilih India dalam lajur B kemudian akan memaparkan kawasan yang sepadan di drop-down ketiga:

Nota. Senarai kawasan adalah unik untuk setiap negara tetapi bebas daripada pemilihan senarai drop-down pertama.

Membuat dropdown ketiga bergantung pada senarai pertama dan kedua

Untuk menubuhkan menu drop-down cascading yang bergantung kepada pilihan dalam drop-down pertama dan kedua, ikuti langkah-langkah ini:

1. Buat julat yang dinamakan tambahan, menamakannya berdasarkan kombinasi dari dua dropdown pertama anda. Sebagai contoh, jika anda mempunyai mangga, oren, dan lain -lain. Dalam senarai pertama dan India, Brazil, dan lain -lain. Di tempat kedua, nama seperti Mangoindia , Mangogo , Orangesindia , Orangesbrazil , dan lain -lain. Nama -nama ini tidak boleh termasuk underscores atau watak khas lain.

2. Gunakan pengesahan data Excel dengan formula pengganti tidak langsung untuk menggabungkan nama -nama dari dua lajur pertama, mengeluarkan ruang. Untuk sel C2, formula adalah: =INDIRECT(SUBSTITUTE(A2&B2," ",""))

   Di mana A2 dan B2 mengandungi dropdown pertama dan kedua.

   Ini akan menyebabkan senarai drop-down ketiga anda memaparkan kawasan berdasarkan buah dan negara yang dipilih dari dua senarai drop-down pertama.

   

Kaedah ini adalah yang paling mudah untuk mencipta drop-down cascading di Excel, walaupun ia mempunyai batasan tertentu.

Batasan pendekatan ini:

1. Senarai drop-down utama mesti mengandungi entri satu perkataan. Untuk penyertaan berbilang kata, lihat cara membuat senarai drop-down cascading dengan entri berbilang kata.
2. Kaedah ini gagal jika senarai drop-down utama termasuk aksara yang tidak dibenarkan dalam nama julat, seperti tanda hubung (-) atau ampersands (&). Dropdown cascading dinamik dapat menyelesaikan masalah ini.
3. Menu drop-down dicipta dengan cara ini tidak mengemas kini secara automatik; Anda mesti menyesuaikan rujukan Ranges secara manual apabila menambah atau mengeluarkan item dari senarai sumber. Untuk mengatasinya, pertimbangkan untuk membuat senarai drop-down yang dinamik.

Membuat senarai drop-down cascading dengan entri pelbagai perkataan

Formula tidak langsung yang kami gunakan lebih awal hanya dengan item satu perkataan. Sebagai contoh, =INDIRECT(A2) secara tidak langsung merujuk sel A2 dan memaparkan julat bernama dengan nama yang sama seperti dalam sel yang dirujuk. Walau bagaimanapun, nama Excel tidak boleh mengandungi ruang, jadi formula ini tidak akan berfungsi untuk nama berbilang kata.

Penyelesaian ini melibatkan penggunaan fungsi tidak langsung dengan pengganti, seperti yang kita lakukan untuk dropdown ketiga.

Katakan anda mempunyai tembikai air di antara produk anda. Dalam kes ini, namakan senarai pengeksport tembikai air sebagai satu perkataan tanpa ruang - tembikai .

Untuk dropdown kedua, gunakan pengesahan data Excel dengan formula ini untuk mengeluarkan ruang dari nama dalam sel A2:

=INDIRECT(SUBSTITUTE(A2," ",""))

Mencegah perubahan dalam senarai drop-down utama

Pertimbangkan senario di mana pengguna memilih pilihan dari semua senarai drop-down, kemudian mengubah fikiran mereka dan memilih item yang berbeza dalam senarai pertama. Ini boleh menyebabkan ketidakpadanan antara pilihan pertama dan kedua. Untuk mengelakkan ini, anda mungkin mahu mengelakkan perubahan dalam senarai drop-down pertama sebaik sahaja pilihan dibuat pada kedua.

Untuk mencapai matlamat ini, gunakan formula khas apabila menubuhkan dropdown pertama yang memeriksa jika entri dipilih dalam dropdown kedua:

=IF(B2="", Fruit, INDIRECT("FakeList"))

Di mana B2 mengandungi dropdown kedua, " Buah " adalah nama senarai dalam menu drop-down pertama, dan " Fakelist " adalah nama yang tidak wujud.

Sebaik sahaja item dipilih dalam senarai drop-down kedua, tiada pilihan akan tersedia apabila pengguna cuba mengklik anak panah senarai pertama.

Membuat senarai drop-down yang dinamik di Excel

Manfaat utama senarai drop-down bergantung kepada Excel dinamik ialah ia mengemas kini secara automatik apabila anda mengedit senarai sumber. Walaupun menubuhkan dropdowns dinamik memerlukan lebih banyak masa dan formula yang kompleks, usaha ini berbaloi kerana menu drop-down ini mudah digunakan setelah dikonfigurasikan.

Terdapat pelbagai cara untuk mencapai ini dalam Excel. Anda boleh menggunakan gabungan fungsi offset, tidak langsung, dan counta atau formula perlawanan indeks yang lebih mantap. Saya lebih suka yang terakhir kerana ia menawarkan beberapa kelebihan, seperti:

1. Anda hanya perlu membuat tiga julat bernama, tanpa mengira bilangan item dalam senarai utama dan bergantung.
2. Senarai anda boleh termasuk item berbilang kata dan watak khas.
3. Bilangan penyertaan boleh berbeza -beza dalam setiap lajur.
4. Perintah penyertaan tidak penting.
5. Sangat mudah untuk mengekalkan dan mengubah suai senarai sumber.

Sekarang, mari kita bergerak ke langkah praktikal.

1. Susun data sumber anda dalam jadual

Mulailah dengan memasukkan semua pilihan untuk senarai drop-down anda ke dalam lembaran kerja. Kali ini, anda akan menyimpan data sumber dalam jadual Excel. Selepas memasukkan data, pilih, tekan Ctrl T, atau pergi ke tab Masukkan > Jadual . Kemudian, namakan jadual anda dalam kotak nama jadual .

Cara terbaik untuk mengatur ialah menggunakan tajuk meja untuk item drop-down pertama dan data jadual untuk item dropdown yang bergantung. Tangkapan skrin di bawah menunjukkan jadual saya bernama Exporters_TBL - Nama buah sebagai tajuk dan negara pengeksport yang disenaraikan di bawah setiap nama buah.

2. Buat nama Excel

Dengan data sumber anda siap, anda akan menyediakan rujukan yang dinamakan secara dinamik mengambil senarai yang betul dari jadual anda.

2.1. Namakan baris tajuk meja (dropdown utama)

Untuk membuat nama baru untuk tajuk jadual, pilih dan sama ada pergi ke Formula > Pengurus Nama > Baru atau tekan Ctrl F3.

Excel akan menggunakan sistem rujukan jadual untuk membuat nama dalam format Table_Name [#Headers] .

Beri nama yang jelas, tidak dapat dilupakan, seperti buah_list , dan klik OK .

2.2. Namakan sel yang mengandungi senarai drop-down pertama

Walaupun anda belum membuat dropdown, anda perlu memilih sel untuk dropdown pertama anda dan namakannya sekarang kerana anda memerlukan nama ini dalam rujukan nama ketiga.

Sebagai contoh, drop-down pertama saya akan berada di sel B1 pada Lembaran 2, jadi saya akan namakannya seperti Buah :

Petua. Gunakan rujukan sel yang sesuai untuk menyalin senarai drop-down di seluruh lembaran kerja.

Petua ini dari Karen sangat berguna, jadi sila baca yang berikut dengan teliti:

Jika anda bercadang untuk menyalin senarai drop-down anda ke sel lain, gunakan rujukan sel campuran apabila menamakan sel (s) dengan senarai drop-down pertama anda.

Untuk menyalin ke lajur lain (ke kanan), gunakan lajur relatif (tanpa tanda $) dan rujukan mutlak (dengan $) seperti = Sheet2! B $ 1.

Akibatnya, senarai drop-down bergantung untuk B1 akan muncul dalam sel B2; Untuk C1, ia akan berada di C2, dan sebagainya.

Untuk menyalin ke baris lain (ke bawah lajur), gunakan lajur mutlak (dengan $) dan baris relatif (tanpa $) rujukan seperti = Sheet2! $ B1.

Untuk menyalin sel drop-down ke mana-mana arah, gunakan rujukan relatif (tanpa tanda $) seperti = Sheet2! B1.

2.3. Namakan formula untuk mendapatkan penyertaan menu bergantung

Daripada membuat nama yang unik untuk setiap senarai yang bergantung, kami akan membuat satu formula bernama yang tidak terikat dengan mana -mana sel atau julat tertentu. Formula ini akan mengambil senarai penyertaan yang betul untuk dropdown kedua berdasarkan pemilihan dalam senarai drop-down pertama. Kelebihannya ialah anda tidak perlu membuat nama baru apabila anda menambah entri baru ke senarai drop -down pertama - satu formula merangkumi semua.

Buat nama Excel baru dengan formula ini:

=INDEX(exporters_tbl,,MATCH(fruit,fruit_list,0))

Di mana:

• exporters_tbl - nama jadual (dari langkah 1);
• fruit - Nama sel yang mengandungi senarai drop -down pertama (dari langkah 2.2);
• fruit_list - Nama merujuk kepada baris tajuk meja (dari langkah 2.1).

Saya menamakannya Exporters_List , seperti yang ditunjukkan dalam tangkapan skrin di bawah.

Anda telah menyelesaikan sebahagian besar kerja! Sebelum berpindah ke langkah terakhir, bijak untuk membuka Pengurus Nama (Ctrl F3) dan periksa nama dan rujukan:

3. Sediakan Pengesahan Data Excel

Ini adalah bahagian yang paling mudah. Dengan formula yang dinamakan di tempat, sediakan pengesahan data seperti biasa (tab data > pengesahan data ).

• Untuk senarai drop-down pertama, enter = fruit_list (nama dari langkah 2.1) dalam kotak sumber.
• Untuk senarai drop-down bergantung, enter = exporters_list (nama dari langkah 2.3).

Selesai! Menu drop-down dinamik anda selesai dan akan dikemas kini secara automatik untuk mencerminkan perubahan dalam jadual sumber.

Dropdown Excel dinamik ini hampir sempurna, tetapi ia mempunyai satu kecacatan - jika lajur dalam jadual sumber anda mengandungi bilangan item yang berlainan, baris kosong akan muncul di menu anda seperti ini:

Tidak termasuk baris kosong dari dropdown cascading dinamik

Untuk mengeluarkan sebarang baris kosong dari kotak drop-down anda, anda perlu meningkatkan formula indeks / perlawanan yang digunakan untuk senarai drop-down dinamik yang bergantung.

Pendekatan ini melibatkan penggunaan dua fungsi indeks, di mana yang pertama mendapat sel kiri atas dan yang kedua mengembalikan sel kanan bawah julat, atau menggunakan fungsi mengimbangi dengan indeks bersarang dan counta. Inilah cara melakukannya:

1. Buat dua nama tambahan

Untuk mengelakkan membuat formula terlalu kompleks, mula -mula membuat dua nama penolong dengan formula mudah ini:

• Nama yang dipanggil col_num untuk merujuk nombor lajur yang dipilih: =MATCH(fruit,fruit_list,0)
• Nama yang dipanggil Whole_col untuk merujuk lajur yang dipilih (keseluruhan lajur, bukan hanya nombor): =INDEX(exporters_tbl,,col_num)

Dalam formula ini, exporters_tbl adalah nama meja sumber anda, fruit adalah nama sel yang mengandungi dropdown pertama, dan fruit_list adalah nama yang merujuk kepada baris pengepala meja.

2. Buat rujukan yang dinamakan untuk dropdown bergantung

Seterusnya, gunakan salah satu daripada formula berikut untuk membuat nama baru (mari kita panggilnya Exporters_LIST2 ) untuk senarai drop-down bergantung:

=INDEX(exporters_tbl,1,col_num) : INDEX(exporters_tbl, COUNTA(entire_col), col_num)

=OFFSET(INDEX(exporters_tbl,1,col_num),0,0,COUNTA(entire_col))

3. Sapukan pengesahan data

Akhirnya, pilih sel yang mengandungi dropdown bergantung dan gunakan pengesahan data dengan memasukkan = exporters_list2 (nama dari langkah sebelumnya) dalam kotak sumber .

Tangkapan skrin di bawah menunjukkan menu drop-down dinamik yang dihasilkan di Excel tanpa garisan kosong!

Nota. Apabila menggunakan senarai drop-down yang dinamik yang dibuat dengan formula ini, pengguna masih boleh mengubah nilai dalam lungsur pertama selepas memilih dari yang kedua, yang berpotensi menyebabkan ketidakcocokan. Untuk mengelakkan ini, anda boleh menggunakan formula VBA atau kompleks yang dicadangkan dalam tutorial ini.

Inilah cara anda membuat senarai pengesahan data Excel berdasarkan nilai senarai lain. Jangan ragu untuk memuat turun buku kerja sampel kami untuk melihat senarai drop-down dalam tindakan. Terima kasih kerana membaca!

Buku Kerja Amalan untuk Muat turun

Cascading Dropdown Contoh 1 - Versi Mudah Cascading Dropdown Contoh 2 - Versi Lanjutan Tanpa Kekosan

Atas ialah kandungan terperinci Cara Membuat Senarai Drop-Down (Cascading) di Excel. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!