Excel: Gunakan Tabel Sebagai Drop-Down Daftar Validasi Data

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

Membuat tabel di MS Excel bisa sangat membuat frustrasi jika Anda mengulangi masukan yang sama berulang kali.

Pada saat yang sama, akan sangat mudah jika Anda hanya memilih konten input dari daftar drop-down tertentu.

Mari kita lihat bagaimana menggunakan aturan validasi, sebuah fungsi yang memungkinkannya.

Tabel di bawah ini adalah daftar pengeluaran individu. Tanggal, jenis pengeluaran, dan nilai dimasukkan setiap kali sejumlah uang dibelanjakan.

Dengan menggunakan tabel ini, mari kita atur kolom B sehingga jenis pengeluaran dapat dipilih daftar drop-down, dan hanya dari sana, untuk menghindari pengaturannya setiap saat, dan menghindari kesalahan dalam entri data.

Solusi 1: Buat daftar dengan input langsung

Mari kita mulai dengan memilih sel yang ingin kita atur daftar drop-downnya.

Pilih Validasi data dari tab Data di Excel, mungkin hanya ditampilkan sebagai ikon kecil.

Pada tab pengaturan, tampilkan opsi drop-down izinkan dan pilih opsi Daftar. Ini akan memungkinkan Anda untuk membuat atau memilih daftar nilai yang diizinkan untuk sel data yang dipilih.

Di bidang sumber, cukup masukkan nilai yang ingin Anda izinkan, dipisahkan dengan koma.

Cukup validasi dengan mengklik OK, dan daftar validasi drop-down Anda akan dibuat untuk sel yang dipilih.

Jika Anda memilih salah satu sel ini, sebuah ikon akan muncul di akhir sel, dan dengan mengkliknya, daftar validasi drop-down akan ditampilkan, membiarkan Anda memilih nilai yang akan secara otomatis dimasukkan ke dalam sel berdasarkan Anda daftar masukan langsung.

Solusi 2: Pilih rentang nilai yang ada

Demikian pula, daripada mengetikkan nilai secara manual, Anda bisa menentukan rentang nilai yang diperbolehkan dengan memilih daftar yang sudah ada di buku kerja.

Praktik terbaik saat membuat beberapa daftar validasi drop-down di Microsoft Excel adalah dengan memiliki satu atau beberapa lembar spesifik yang hanya berisi berbagai daftar validasi, untuk mempermudah menemukan dan memperbaruinya.

Setelah membuat daftar nilai yang diizinkan, dalam contoh kami di lembar kerja yang sama agar lebih mudah diakses secara visual, pilih sel tempat validasi data harus diberlakukan.

Kemudian, buka menu validasi data yang sama dari tab data, pilih jenis daftar di menu drop-down, dan untuk sumber, daripada memasukkan nilai Anda sendiri, klik tombol di sisi kanan bidang.

Anda akan diarahkan ke lembar kerja Anda, dan yang harus Anda lakukan adalah mengklik sel pertama tabel validasi data Anda, gerakkan mouse Anda ke sel terakhir yang berisi salah satu nilai yang valid, sambil menahan klik Anda, dan lepaskan klik hanya setelah Anda mencapai sel terakhir dari nilai validasi data.

Nilai yang telah Anda pilih dengan cara itu akan direferensikan dalam daftar validasi data Anda, dan Anda cukup menambahkan nilai ke validasi data Anda - atau menghapusnya - dengan memperbarui daftar itu.

Tetapkan entri validasi data pada lembar yang berbeda

Menjadi Excel Pro: Bergabunglah dengan kursus kami!

Tinggikan keterampilan Anda dari pemula menjadi pahlawan dengan kursus Excel 365 Basics kami, yang dirancang untuk membuat Anda mahir hanya dalam beberapa sesi.

Daftarkan di sini

Untuk menata buku kerja Anda dengan lebih baik, mungkin lebih mudah untuk membuat daftar validasi data Anda di buku kerja tertentu, sehingga Anda dapat dengan mudah menemukan dan mengaksesnya, dan tidak perlu bertanya-tanya di mana mereka diatur.

Letakkan data Anda di sana, pilih tabel, dan gunakan tabel opsi: tabel di tab sisipkan, untuk mengubah daftar nilai Anda menjadi tabel yang akan secara otomatis diubah ukurannya saat Anda menambahkan atau menghapus nilai - dan begitu juga dengan validasi data Anda.

Lalu, klik di mana saja di tabel bergaya Anda, dan masukkan nama tabel di tab desain tabel.

Ini akan memungkinkan Anda untuk mereferensikan tabel ini di mana saja, termasuk dalam daftar validasi data, hanya dengan memasukkan nama tabel, bukan rentangnya!

Cara memperbolehkan sel kosong dalam daftar validasi data

Jika Anda ingin mengizinkan nilai kosong di bidang yang validasi datanya diaktifkan, Anda harus memilih salah satu sel yang validasi datanya diaktifkan, dan buka tab data, untuk membuka menu validasi data (lihat di atas di mana tombolnya berada ).

Di sana, pastikan bahwa opsi abaikan kosong dicentang, dan klik terapkan perubahan ini ke semua sel lain dengan pengaturan yang sama sebelum mengklik OK.

Anda kemudian dapat mengosongkan sel dengan memilihnya dan menghapus konten, tanpa mendapatkan pesan kesalahan yang menyatakan bahwa konten sel tidak diperbolehkan.

Cara menonaktifkan nilai kosong dalam daftar validasi data

Jika Anda tidak ingin nilai kosong diizinkan dalam daftar drop-down validasi data, dan mendapatkan pesan kesalahan nilai dalam sel ini tidak valid atau hilang setiap kali Anda memilihnya, mulailah dengan memilih rentang sel yang datanya validasi berlaku, atau sel apa pun dalam daftar itu.

Kemudian, buka menu validasi data di tab data, dan hapus centang pada kolom abaikan kosong. Jika Anda memilih hanya satu sel, centang terapkan perubahan ini ke semua sel lain dengan pengaturan yang sama sebelum mengklik OK.

Kembali ke daftar drop-down validasi data Anda, kesalahan akan muncul - dan tidak mungkin membiarkan sel kosong lagi.

Cara menghapus daftar drop-down validasi data

Setiap kali Anda selesai dengan membatasi rentang nilai yang diizinkan dalam daftar, pilih sel mana pun dalam daftar itu.

Kemudian buka menu validasi data dari tab data - lihat di atas di bagian pertama bagaimana melakukannya - dan pastikan bahwa opsi terapkan perubahan ini ke semua sel lain dengan pengaturan yang sama dicentang, jika tidak, Anda hanya akan menghapus data validasi pada sel yang saat ini dipilih.

Kemudian, klik hapus semua dan validasi data yang telah Anda pilih akan dihapus dari buku kerja MS Excel!

VBA: Tambahkan daftar validasi

Menambahkan daftar validasi dengan VBA semudah menggunakan fungsi di bawah ini, di mana A1 adalah sel di mana data akan divalidasi terhadap daftar validasi data yang disimpan di sel B1 hingga B5.

Rentang ("a1"). Validasi _. Ubah xlValidateList, xlValidAlertStop, "= $ B $ 1: $ B $ 5"

Dengan menggunakan properti lanjutan dari fungsi daftar validasi data VBA, dimungkinkan untuk membuat beberapa daftar drop-down dependen di Excel VBA yang masing-masing memvalidasi berbagai bidang terhadap berbagai daftar validasi data, dan memiliki judul, judul kesalahan, pesan, dan pesan kesalahannya sendiri. .