Excel: Gunakan Jadual Sebagai Lungsur Daftar Pengesahan Data

Pengesahan data: Cara membuat sel untuk memilih nilai dari drop-down

Membuat jadual di MS Excel sangat mengecewakan jika anda mengulangi input yang sama berulang kali.

Pada masa yang sama, sangat senang jika anda hanya memilih isi input dari senarai drop-down tertentu.

Mari lihat bagaimana menggunakan peraturan pengesahan, fungsi yang memungkinkannya.

Jadual di bawah adalah senarai perbelanjaan individu. Tarikh, jenis perbelanjaan, dan nilai dimasukkan setiap kali sejumlah wang dibelanjakan.

Dengan menggunakan jadual ini, mari kita tetapkan lajur B supaya jenis perbelanjaan dapat dipilih senarai drop-down, dan hanya dari sana, untuk mengelakkan penyediaannya setiap kali, dan mengelakkan kesilapan dalam memasukkan data.

Penyelesaian 1: Buat senarai dengan input langsung

Mari kita mulakan dengan Memilih sel yang ingin kita tetapkan senarai drop-down.

Pilih Pengesahan data dari tab Data di Excel, mungkin ditampilkan sebagai ikon kecil sahaja.

Pada tab tetapan, tampilkan pilihan tarik turun dan pilih pilihan Daftar. Ini akan membolehkan anda membuat atau memilih senarai nilai yang dibenarkan untuk sel data yang dipilih.

Di medan sumber, masukkan nilai yang anda mahu izinkan, dipisahkan dengan koma.

Cukup sahkan dengan mengklik OK, dan senarai pengesahan drop-down anda akan dibuat untuk sel yang dipilih.

Sekiranya anda memilih salah satu sel ini, ikon akan muncul di hujung sel, dan dengan mengkliknya, senarai pengesahan drop-down akan ditampilkan, membiarkan anda memilih nilai yang akan dimasukkan secara automatik di dalam sel berdasarkan senarai input langsung.

Penyelesaian 2: Pilih julat nilai yang ada

Begitu juga, daripada menaip nilai secara manual, anda boleh menentukan julat nilai yang dibenarkan dengan memilih senarai yang sudah ada di buku kerja.

Amalan terbaik semasa membuat beberapa senarai pengesahan drop-down di Microsoft Excel adalah dengan mempunyai satu atau lebih helaian khusus yang hanya mengandungi pelbagai senarai pengesahan, agar lebih mudah untuk mencarinya dan memperbaruinya.

Setelah membuat senarai nilai yang dibenarkan anda, dalam contoh kami pada lembaran kerja yang sama untuk menjadikannya lebih mudah dilihat secara visual, pilih sel di mana pengesahan data harus ditegakkan.

Kemudian, buka juga menu pengesahan data dari tab data, pilih jenis senarai di menu drop-down, dan untuk sumbernya, bukannya memasukkan nilai anda sendiri, klik pada butang di sebelah kanan lapangan.

Anda akan diarahkan ke lembaran kerja anda, dan yang harus anda lakukan ialah mengklik sel pertama dari jadual pengesahan data anda, menggerakkan tetikus anda ke sel terakhir yang mengandungi salah satu nilai yang sah, sambil menahan klik anda, dan melepaskan klik hanya setelah anda mencapai sel terakhir nilai pengesahan data.

Nilai yang anda pilih dengan cara itu akan dirujuk dalam daftar pengesahan data anda, dan anda boleh menambahkan nilai pada pengesahan data anda - atau menghapusnya - dengan mengemas kini senarai tersebut.

Tetapkan entri pengesahan data pada helaian lain

Menjadi Excel Pro: Sertai kursus kami!

Meningkatkan kemahiran anda dari pemula kepada Hero dengan kursus asas Excel 365 kami, yang direka untuk menjadikan anda mahir dalam hanya beberapa sesi.

Daftar di sini

Untuk menyusun buku kerja anda dengan lebih baik, mungkin lebih mudah untuk membuat senarai pengesahan data anda dalam buku kerja tertentu, jadi anda dapat dengan mudah menemukan dan mengaksesnya, dan tidak perlu bertanya-tanya di mana mereka ditetapkan.

Letakkan data anda di sana, pilih jadual, dan gunakan jadual pilihan: jadual di tab sisipan, untuk mengubah senarai nilai anda menjadi jadual yang secara automatik akan diperbaiki semasa anda menambah atau membuang nilai - dan demikian juga pengesahan data anda.

Kemudian, klik di mana sahaja di meja bergaya anda, dan masukkan nama jadual di tab reka bentuk jadual.

Ini akan membolehkan anda merujuk jadual ini di mana sahaja, termasuk dalam senarai pengesahan data, dengan hanya memasukkan nama jadual dan bukannya julat!

Cara membenarkan sel kosong dalam senarai pengesahan data

Sekiranya anda ingin membiarkan nilai kosong dalam bidang yang mengaktifkan pengesahan data, anda mesti memilih salah satu sel yang validasi data diaktifkan, dan pergi ke tab data, untuk membuka menu pengesahan data (lihat di atas di mana butang berada ).

Di sana, pastikan bahawa pilihan kosongkan dicentang, dan klik terapkan perubahan ini ke semua sel lain dengan tetapan yang sama sebelum mengklik OK.

Anda kemudian akan dapat mengosongkan sel dengan memilihnya dan membuang kandungan, tanpa mendapat mesej ralat yang menyatakan bahawa kandungan sel tidak dibenarkan.

Cara mematikan nilai kosong dalam senarai pengesahan data

Sekiranya anda tidak mahu nilai kosong diizinkan dalam senarai drop-down pengesahan data, dan dapatkan mesej ralat nilai dalam sel ini tidak sah atau hilang setiap kali anda memilihnya, mulakan dengan memilih julat sel di mana data pengesahan berlaku, atau mana-mana sel dalam senarai itu.

Kemudian, buka menu pengesahan data di tab data, dan hapus centang bidang abaikan kosong. Sekiranya anda hanya memilih satu sel, periksa terapkan perubahan ini ke semua sel lain dengan tetapan yang sama sebelum mengklik OK.

Kembali ke senarai juntai bawah pengesahan data anda, ralat akan muncul - dan sel tidak lagi kosong.

Cara membuang senarai drop-down pengesahan data

Apabila anda selesai dengan mengehadkan julat nilai yang dibenarkan dalam senarai, pilih mana-mana sel dalam senarai itu.

Kemudian buka menu pengesahan data dari tab data - lihat di atas di bahagian pertama bagaimana melakukannya - dan pastikan bahawa pilihan terapkan perubahan ini ke semua sel lain dengan tetapan yang sama dicentang, jika tidak, anda hanya akan membuang data pengesahan pada sel yang sedang dipilih.

Kemudian, klik hapus semua dan pengesahan data yang telah anda pilih akan dikeluarkan dari buku kerja MS Excel!

VBA: Tambahkan senarai pengesahan

Menambah senarai pengesahan dengan VBA semudah menggunakan fungsi di bawah, di mana A1 adalah sel di mana data akan disahkan terhadap daftar pengesahan data yang disimpan di sel B1 hingga B5.

Julat ("a1"). Pengesahan _. Ubah suai xlValidateList, xlValidAlertStop, "= $ B $ 1: $ B $ 5"

Dengan menggunakan sifat lanjutan fungsi senarai pengesahan data VBA, adalah mungkin untuk membuat beberapa senarai juntai bawah yang bergantung pada Excel VBA yang masing-masing mengesahkan pelbagai bidang terhadap pelbagai senarai pengesahan data, dan mempunyai tajuk, tajuk ralat, mesej dan mesej kesalahan mereka sendiri .