Tutorial ini menunjukkan langkah-langkah mudah untuk membuat senarai drop-down dalam Excel: Buat dari julat sel, bernama julat, jadual Excel, lembaran kerja lain. Anda juga akan belajar bagaimana membuat menu drop-down Excel dinamik, boleh diedit, dan boleh dicari.

Microsoft Excel adalah baik untuk menganjurkan dan menganalisis data yang kompleks. Salah satu ciri yang paling berguna ialah keupayaan untuk membuat menu drop-down yang membolehkan pengguna memilih item dari senarai yang telah ditetapkan. Menu drop-down membolehkan kemasukan data yang lebih cepat, lebih tepat dan lebih konsisten. Artikel ini akan menunjukkan kepada anda beberapa cara yang berbeza untuk membuat menu drop-down di Excel.

Senarai drop-down Excel ---------------------- Senarai drop-down Excel , juga dikenali sebagai *kotak drop-down *atau *drop-down menu *, digunakan untuk memasukkan data dalam spreadsheet dari senarai item yang telah ditetapkan. Apabila anda memilih sel yang mengandungi senarai, anak panah kecil muncul di sebelah sel, yang boleh anda klik untuk memilih.

Tujuan utama menggunakan senarai drop-down dalam Excel adalah untuk mengehadkan bilangan pilihan yang tersedia kepada pengguna. Di samping itu, senarai drop-down menghalang kesilapan dan membuat kemasukan data lebih cepat dan lebih konsisten.

Cara membuat senarai drop-down di Excel

Untuk membuat senarai drop-down dalam Excel, gunakan ciri pengesahan data. Berikut adalah langkah -langkah:

1. Pilih satu atau lebih sel yang anda mahu senarai drop-down muncul. Ini boleh menjadi sel tunggal, pelbagai sel, atau lajur keseluruhan. Untuk memilih pelbagai sel yang tidak berterusan, tahan kekunci CTRL.
2. Pada tab Data , dalam kumpulan Alat Data , klik Pengesahan Data .
3. Pada tab Tetapan kotak dialog Pengesahan Data , lakukan perkara berikut:
   • Dalam kotak membenarkan , pilih Senarai .
   • Di dalam kotak sumber , masukkan item yang dipisahkan koma, sama ada dengan atau tanpa ruang. Atau pilih julat sel pada lembaran kerja yang mengandungi item.
   • Pastikan kotak drop-down di dalam sel dipilih (lalai), jika tidak, tidak akan ada anak panah drop-down di sebelah sel.
   • Pilih atau kosongkan pilihan kosong kosong bergantung kepada cara anda mahu mengendalikan sel kosong.
   • Apabila selesai, klik OK.

Tahniah! Anda telah berjaya membuat senarai drop-down yang mudah di Excel. Pengguna anda kini boleh mengklik anak panah di sebelah sel dan pilih entri yang mereka mahu.

Senarai drop-down yang dibuat dengan nilai yang dipisahkan koma sesuai untuk senarai pengesahan data kecil yang tidak mungkin berubah. Untuk senarai yang sering dikemas kini, lebih baik menggunakan julat atau jadual sebagai sumber. Berikut adalah arahan langkah demi langkah terperinci untuk setiap kaedah.

petunjuk. Untuk mempercepatkan kemasukan data dalam jadual Excel, anda juga boleh menggunakan jadual kemasukan data.

Buat menu drop-down dari julat sel

Untuk memasukkan senarai drop-down berdasarkan nilai dalam julat sel, lakukan langkah-langkah berikut:

1. Mula-mula buat senarai item yang ingin anda sertakan dalam senarai drop-down. Untuk melakukan ini, masukkan setiap item dalam sel yang berasingan. Ini boleh dilakukan dalam lembaran kerja yang sama seperti senarai drop-down atau dalam lembaran kerja yang berbeza.
2. Pilih sel yang akan mengandungi senarai.
3. Pada reben, klik tab Data > Pengesahan Data .
4. Dalam tetingkap Dialog Pengesahan Data , pilih Senarai dari menu drop -down. Letakkan kursor dalam kotak sumber dan pilih julat sel yang mengandungi item, atau klik ikon dialog keruntuhan dan pilih julat. Apabila selesai, klik OK.

Kelebihan : Anda boleh mengubah suai senarai dropdown anda dengan membuat perubahan dalam skop rujukan tanpa mengedit senarai pengesahan data itu sendiri.

Kekurangan : Untuk menambah atau memadam item, anda perlu mengemas kini rujukan skop sumber .

Masukkan senarai drop-down dari julat bernama

Pada mulanya, kaedah ini membuat senarai pengesahan data Excel memerlukan sedikit masa, tetapi ia boleh menjimatkan lebih banyak masa dalam jangka masa panjang.

1. Buat senarai item di lembaran kerja. Nilai harus dimasukkan ke dalam satu lajur atau satu baris, tanpa sel kosong. petunjuk. Adalah lebih baik untuk menyusun item dalam susunan abjad atau dalam susunan tersuai yang anda mahu mereka muncul dalam menu lungsur.

2. Buat julat yang dinamakan. Cara terpantas ialah memilih sel dan masukkan nama yang dikehendaki secara langsung dalam kotak nama . Apabila selesai, klik Enter untuk menyimpan julat bernama yang baru dibuat. Untuk maklumat lanjut, lihat bagaimana untuk menentukan nama dalam Excel. Sebagai contoh, mari buat skop yang dipanggil bahan -bahan :

   

3. Pilih sel yang digunakan untuk memilih senarai - pada lembaran kerja yang sama seperti julat bernama atau pada lembaran kerja yang berbeza.

4. Buka tetingkap Dialog Pengesahan Data dan konfigurasikan peraturan:

   • Dalam kotak membenarkan , pilih Senarai .
   • Dalam kotak sumber , masukkan tanda yang sama diikuti dengan nama julat. Dalam kes kita, ia adalah = bahan -bahan .
   • Klik OK .

   

Notis. Sekiranya julat bernama anda mempunyai sekurang -kurangnya satu sel kosong , pastikan kotak kosong dipilih untuk membolehkan sebarang nilai dimasukkan ke dalam sel pengesahan.

Kelebihan : Jika anda memasukkan beberapa senarai drop-down dalam lembaran kerja yang berbeza, julat bernama akan menjadikannya lebih mudah untuk mengenal pasti dan mengurus.

Kekurangan : Menubuhkan mengambil sedikit masa.

Buat senarai drop-down dari Jadual Excel

Daripada menggunakan julat yang dinamakan, anda boleh meletakkan data sumber ke dalam jadual Excel yang berfungsi sepenuhnya. Mengapa anda mahu menggunakan borang? Pertama sekali, kerana ia membolehkan anda membuat senarai drop-down dinamik yang boleh dikemas kini secara automatik apabila anda menambah atau memadam item ke meja.

Untuk membuat senarai drop-down dinamik dari jadual Excel, ikuti langkah-langkah ini:

1. Masukkan item senarai dalam jadual, atau gunakan kekunci pintasan Ctrl T untuk menukar julat yang ada ke meja.
2. Pilih sel yang anda mahu masukkan senarai drop-down.
3. Buka tetingkap Dialog Pengesahan Data .
4. Pilih senarai dari kotak drop-down yang dibenarkan .
5. Dalam kotak sumber , masukkan formula yang merujuk lajur tertentu dalam jadual, tidak termasuk sel tajuk. Untuk melakukan ini, gunakan fungsi tidak langsung dengan rujukan berstruktur seperti berikut: =INDIRECT("Table_name[Column_name]")
6. Apabila selesai, klik OK .

Untuk contoh ini, kami membuat menu drop-down dari lajur bernama Bahan dalam Jadual1 :

=INDIRECT("Table1[Ingredients]")

Kelebihan : Cara yang mudah dan cepat untuk memasukkan menu drop-down dinamik yang boleh dilanjutkan di Excel.

Kekurangan : tidak dijumpai :)

Cara Membuat Senarai Drop-Down Dinamik di Excel

Jika anda menukar item dalam senarai pemilihan dengan kerap, cara terbaik adalah untuk membuat senarai drop-down dinamik . Dalam kes ini, apabila anda menambah atau memadam item ke senarai sumber, senarai akan dikemas kini secara automatik dalam semua sel yang disertakan.

Cara terpantas untuk membuat senarai drop-down dinamik dalam Excel adalah untuk mencipta dari jadual, seperti yang ditunjukkan di atas. Ini adalah tingkah laku lalai jadual Excel; Tiada tetapan atau tindakan tambahan diperlukan.

Cara lain ialah menggunakan julat yang dinamakan biasa dan merujuknya menggunakan formula offset seperti yang diterangkan di bawah.

1. Dalam lajur kosong yang tidak mengandungi data lain, masukkan item dari menu lungsur dalam sel yang berasingan.

2. Buat formula penamaan. Untuk melakukan ini, tekan Ctrl F3 untuk membuka kotak dialog nama baru . Masukkan nama yang anda mahu dalam kotak nama , dan kemudian masukkan formula berikut dalam rujukan kepada kotak. =OFFSET(Sheet3!$A$2, 0, 0, COUNTA(Sheet3!$A:$A)-1, 1)

   dalam:

   • Sheet3 - Nama lembaran kerja
   • A - lajur di mana item drop -down terletak
   • $ A $ 2 - sel yang mengandungi item pertama

   

3. Selepas menentukan nama formula, buat senarai drop-down berdasarkan julat penamaan dengan cara yang biasa.

Notis. Dalam contoh ini, Cell A1 mengandungi tajuk dan tidak boleh dimasukkan dalam julat dinamik. Oleh itu, kami menambah pembetulan -1 dalam fungsi Counta untuk mengelakkan penambahan kosong pada akhir senarai juntai bawah. Jika tidak ada data lain di atas sel yang dirujuk, gunakan fungsi Counta tanpa pembetulan ini, sebagai contoh:

=OFFSET(Sheet3!$A$1, 0, 0, COUNTA(Sheet3!$A:$A), 1)

Bagaimana formula ini berfungsi

Formula ini mengandungi dua fungsi - Offset dan Counta. Fungsi CountA mengira bilangan semua sel bukan kosong dalam lajur yang ditentukan. Offset menggunakan kiraan ini sebagai parameter ketinggian , jadi ia mengembalikan julat rujukan yang hanya termasuk sel-sel yang tidak kosong bermula dari sel yang anda sediakan untuk parameter rujukan yang mengandungi item pertama.

Notis. Untuk membuat formula berfungsi dengan betul, tidak ada sel kosong di antara entri data dalam lajur yang dirujuk. Sekiranya terdapat data lain di atas sel yang dirujuk atas, seperti yang ditunjukkan dalam contoh di atas, anda harus menolak bilangan sel yang sepadan dari hasil CountA.

Kelebihan : Kelebihan utama senarai drop-down dinamik ialah anda tidak perlu mengubah rujukan kepada julat yang dinamakan setiap kali anda mengembangkan atau mengecilkan senarai sumber. Anda hanya perlu memadam atau memasukkan entri baru dalam senarai sumber dan menu drop-down anda akan dikemas kini secara automatik!

Kelemahan : Proses persediaan agak rumit.

Buat senarai drop-down dinamik dalam Excel 365/2021

Arahan dinamik Excel mempunyai banyak ciri inovatif yang tidak terdapat dalam versi lama. Salah satu ciri baru yang dipanggil Unik boleh membantu anda membuat senarai drop-down dinamik menggunakan formula mudah.

Katakan anda mempunyai dataset dengan banyak item pendua, lajur A dalam angka di bawah. Matlamat anda adalah untuk menambah senarai drop-down di mana setiap item muncul sekali sahaja.

Untuk mengekstrak item yang unik, gunakan formula berikut:

=UNIQUE(A2:A21)

Secara pilihan, anda boleh menyusun nilai yang diekstrak mengikut abjad dengan membungkusnya dalam fungsi jenis:

=SORT(UNIQUE(A2:A21))

Formula array dinamik ini hanya memasuki satu sel (E2) dan ia secara automatik akan berkembang ke bilangan sel yang dikehendaki untuk memaparkan semua item yang unik.

Seterusnya, anda menetapkan senarai drop-down menggunakan rujukan pelbagai limpahan, iaitu alamat sel diikuti oleh watak hash. Dalam contoh kami, ia adalah = $ e $ 2# atau = sheet1! $ E $ 2# Jika senarai drop-down berada dalam lembaran kerja lain:

Hasilnya adalah senarai drop-down dinamik yang boleh dilanjutkan -Fungsi unik secara automatik akan mengekstrak item yang baru ditambah ke jadual sumber, dan rujukan pelbagai limpahan akan memaksa Excel untuk mengemas kini senarai drop-down dengan sewajarnya.

petunjuk. Anda boleh menggunakan kaedah yang sama untuk membuat senarai drop-down cascading dalam Excel 365.

Cara membuat senarai drop-down dari lembaran kerja lain

Untuk memasukkan menu drop-down yang mengekstrak data dari lembaran kerja yang berbeza, anda boleh menggunakan skop umum, skop bernama, atau Jadual Excel:

• Apabila membuat menu lungsur dari julat bernama, pastikan julat nama itu adalah buku kerja , dan kemudian sediakan senarai pengesahan data dengan cara yang biasa.
• Tidak ada langkah tambahan apabila membuat dropdown dari meja, kerana nama/rujukan jadual sah di seluruh buku kerja.
• Jika anda memasukkan senarai drop-down dari skop biasa, sertakan nama lembaran kerja dalam rujukan sumber. Dalam tetingkap Dialog Pengesahan Data , letakkan kursor di dalam kotak sumber , beralih ke lembaran kerja lain dan pilih skop yang mengandungi projek. Excel secara automatik akan menambah nama lembaran kerja kepada rujukan.

Cara membuat senarai drop-down dari buku kerja lain

Untuk membuat menu drop -down di Excel menggunakan senarai dari buku kerja lain sebagai sumber, anda perlu menentukan dua julat bernama - satu dalam buku kerja sumber dan yang lain dalam buku kerja di mana anda ingin memasukkan senarai pengesahan data. Langkah -langkahnya adalah seperti berikut:

1. Dalam Buku Kerja Sumber, buat julat bernama untuk senarai sumber, seperti source_list .

2. Dalam buku kerja utama, tentukan nama yang merujuk senarai sumber anda. Untuk contoh ini, kami membuat nama yang dipanggil item yang merujuk: = sourceFile.xlsx! SOURCE_LIST

   Jika nama buku kerja mengandungi ruang atau aksara bukan alfabetikal, ia mesti dilampirkan dalam petikan tunggal, seperti berikut:

   = 'File file.xlsx'! Source_list

   Untuk maklumat lanjut, lihat cara membuat rujukan luaran dalam Excel.

   

3. Dalam buku kerja utama, pilih sel yang digunakan untuk memilih senarai dan klik tab Data> Pengesahan Data . Dalam kotak sumber , rujuk nama yang anda buat dalam langkah 2. Dalam kes kami, ia adalah = item.

NOTIS:

• Agar senarai drop-down dari buku kerja lain untuk berfungsi, buku kerja sumber mesti dibuka.
• Senarai drop -down yang dibuat dengan cara ini tidak dikemas kini secara automatik apabila item ditambah atau dipadam ke senarai sumber - anda perlu mengubah suai rujukan senarai sumber secara manual.

Cara membuat senarai drop-down yang dinamik dari buku kerja lain

Untuk membuat senarai drop-down yang dinamik dari buku kerja lain, tentukan nama formula dalam buku kerja sumber menggunakan formula offset, seperti yang dijelaskan dalam membuat senarai drop-down dinamik dalam Excel. Dalam kes ini, sebaik sahaja sebarang perubahan dibuat ke senarai sumber, menu drop-down dalam buku kerja lain akan dikemas kini dengan segera.

Buat senarai drop-down yang boleh dicari di Excel 365

Dalam Excel 365, senarai pengesahan data mempunyai ciri autocomplete yang hebat. Untuk mempercepatkan kemasukan data dalam senarai besar, hanya mula menaip perkataan sasaran dalam sel menu drop-down-algoritma autocomplete akan sepadan dengan substring input dengan item dalam senarai drop-down dan menunjukkan kepada anda perlawanan yang dijumpai. Apabila anda memasukkan lebih banyak watak, senarai yang dipaparkan menyusut, dan sebaliknya, apabila anda memadam aksara, lebih banyak perlawanan dipaparkan.

Masukkan senarai drop-down dengan mesej

Untuk memaparkan mesej maklumat apabila seseorang mengklik sel senarai drop-down, ikuti perkara berikut:

• Dalam kotak dialog Pengesahan Data , beralih ke tab Mesej Input .
• Pastikan pilihan mesej input paparan dipilih apabila sel dipilih .
• Masukkan tajuk dan mesej dalam medan yang sepadan (sehingga 225 aksara).
• Klik OK untuk menyimpan mesej dan menutup kotak dialog.

Senarai drop-down yang dihasilkan dengan mesej akan kelihatan seperti ini:

Buat senarai drop-down yang boleh diedit di Excel

Secara lalai, senarai drop-down Excel tidak dapat diedit, iaitu terhad kepada nilai dalam senarai itu sendiri. Jika anda memasukkan sebarang nilai lain, amaran ralat akan dipaparkan. Walau bagaimanapun, anda boleh membenarkan pengguna memasukkan nilai mereka sendiri. Kaedahnya adalah seperti berikut:

1. Buka tetingkap Dialog Pengesahan Data .
2. Pada tab Alerts Ralat , nyahtandakan kotak peringatan ralat persembahan selepas memasukkan data tidak sah .

Secara teknikal, ini menjadikan senarai drop-down ke dalam kotak combo . Istilah "kotak kombo" merujuk kepada senarai drop-down yang boleh diedit yang membolehkan pengguna memilih nilai dari senarai yang telah ditetapkan atau masukkan nilai tersuai terus ke dalam kotak.

Secara pilihan, anda boleh memaparkan mesej amaran apabila seseorang cuba memasukkan nilai yang tidak ada dalam senarai:

1. Pada tab Alerts Ralat , pilih pilihan Alerts Ralat Pameran selepas memasukkan data tidak sah .
2. Pilih mesej atau amaran dari kotak gaya dan masukkan tajuk dan teks mesej.
   • Mesej maklumat lebih baik digunakan apabila tidak ada masalah dengan pengguna yang memasukkan nilai tersuai.
   • Mesej amaran akan mendorong pengguna untuk memilih item dari kotak drop-down dan bukannya memasukkan data sendiri, walaupun ia tidak melarang ini.

Berikut adalah senarai drop-down Excel yang boleh diedit dengan mesej amaran sebenarnya:

petunjuk. Jika anda tidak pasti apa tajuk atau teks mesej untuk dimasukkan, anda boleh meninggalkan medan kosong. Dalam kes ini, Excel akan memaparkan amaran lalai " Nilai ini tidak memenuhi had pengesahan data yang ditakrifkan untuk sel ini ".

Ini adalah cara membuat senarai dropdown mudah dalam Excel. Dalam artikel seterusnya, kami akan meneroka topik ini lebih lanjut dan belajar bagaimana untuk memasukkan senarai drop-down cascading (ketergantungan) dengan pengesahan data bersyarat. Sila terus perhatikan, terima kasih kerana membaca!

Muat turun buku kerja latihan

Senarai Drop -Down Excel - Contoh (fail .xlsx)

Atas ialah kandungan terperinci Cara Membuat Senarai Drop Down Dalam Excel: Dinamik, Diedit, Boleh Dicari. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!