Cara Menghubungkan Daftar Dropdown di Excel

Apakah Anda ingin mempelajari caranya tautkan daftar dropdown di excel? Daftar drop-down Excel adalah fitur yang berguna saat Anda membuat formulir entri data atau dasbor Excel.

Anda menampilkan daftar item sebagai menu drop-down di sel, dan pengguna dapat membuat pilihan dari menu drop-down. Ini bisa berguna ketika Anda memiliki daftar nama, produk, atau wilayah yang sering kali perlu Anda masukkan ke dalam kumpulan sel.

Contoh daftar drop-down di Excel

Berikut adalah contoh daftar drop-down Excel:

Di sini Anda dapat membaca tentang: Cara Menyalin Lembar Excel ke Buku Kerja Lain – Tutorial

Pada contoh yang ditunjukkan pada gambar, elemen di A2: A6 telah digunakan untuk membuat menu drop-down di C3. Namun terkadang, Anda mungkin ingin menggunakan lebih dari satu daftar drop-down di Excel sehingga item yang tersedia di daftar drop-down kedua bergantung pada pilihan yang dibuat di daftar drop-down pertama.

Ini disebut daftar drop-down dependen di Excel.

Di bawah ini adalah contoh dari apa yang ingin kami jelaskan dengan daftar drop-down dependen di Excel:

Anda dapat melihat bahwa opsi di menu dropdown 2 bergantung pada pilihan yang dibuat di menu dropdown 1.

Jika Anda memilih 'Buah' Di menu drop down 1, nama buah ditampilkan, tetapi jika Anda memilih Sayuran di menu drop-down 1, lalu nama sayuran ditampilkan di menu drop-down 2. Ini disebut daftar drop-down bersyarat atau dependen di Excel.

Buat daftar dropdown dependen di Excel

Berikut langkah-langkah membuat daftar dropdown dependen di Excel:

• langkah 1: Pilih sel tempat Anda menginginkan daftar drop-down (utama) pertama.
• langkah 2: pergi ke Data -> Validasi Data. Ini akan membuka kotak dialog validasi data.

• langkah 3: Di kotak dialog validasi data, di dalam tab konfigurasi, pilih opsi daftar.

• langkah 4: Di pedesaan Sumber, menentukan rentang berisi item yang akan ditampilkan di daftar drop-down pertama.

• langkah 5: Klik menerima. Ini akan membuat menu tarik-turun 1.

• langkah 6: Pilih seluruh kumpulan data (A1:B6 dalam contoh ini).

• langkah 7: Pergi ke Rumus -> Nama yang ditentukan -> Buat dari pilihan (atau Anda dapat menggunakan pintasan keyboard Kontrol + Shift + F3).

• langkah 8: Di kotak dialog 'Buat nama dari pilihan', centang opsinya Baris atas dan hapus centang yang lainnya. Melakukan hal ini menciptakan 2 rentang nama ('Buah-buahan' dan 'Sayuran'). Rangkaian Nama Buah mengacu pada semua buah-buahan dalam daftar dan rangkaian Nama Sayuran mengacu pada semua sayuran dalam daftar.

• langkah 9: Klik menerima.
• langkah 10: Pilih sel di mana Anda ingin daftar drop-down Dependent/Conditional (E3 dalam contoh ini).
• langkah 11: Pergi ke Data -> Validasi Data.

• langkah 12: Di kotak dialog Validasi data, di dalam tab Konfigurasi, Pastikan bahwa daftar dipilih.

• langkah 13: Di Bidang sumber, masukkan rumusnya = TIDAK LANGSUNG (D3). Di sini, D3 adalah sel yang berisi menu dropdown utama.

• langkah 14: Klik menerima.

Sekarang, ketika Anda membuat pilihan di dropdown 1, opsi yang tercantum di dropdown 2 akan diperbarui secara otomatis.

Bagaimana cara kerjanya?

Bagaimana cara kerjanya? – Daftar drop-down di Excel bersyarat (di sel E3) mengacu pada =INDIRECT(D3). Artinya ketika Anda memilih 'Buah-buahan' di sel D3, daftar drop-down di E3 mengacu pada rentang yang disebutkan 'Buah' (melalui Fungsi TIDAK LANGSUNG) dan karenanya mencantumkan semua elemen dalam kategori itu.

• Catatan penting:jika kategori induk lebih dari satu kata (mis. 'Buah musiman' lebih tepatnya 'Buah-buahan'), maka Anda harus menggunakan rumus = TIDAK LANGSUNG (PENGGANTI (D3,”“,”_”)), alih-alih fungsi INDIRECT sederhana yang ditunjukkan di atas.

Alasannya adalah Excel tidak mengizinkan spasi dalam rentang bernama. Jadi saat Anda membuat rentang bernama menggunakan lebih dari satu kata, Excel secara otomatis menyisipkan garis bawah di antara kata-kata tersebut.

misalnya: saat Anda membuat rentang bernama dengan 'Buah musiman', Akan disebut Musim_Buah-buahan di backend. Penggunaan fungsi GANTI dalam Fungsi TIDAK LANGSUNG memastikan bahwa spasi menjadi garis bawah.

Setel ulang/hapus konten daftar dropdown yang bergantung secara otomatis

Ketika Anda telah membuat pilihan dan kemudian mengubah dropdown induk, dropdown dependen tidak akan berubah dan oleh karena itu akan menjadi entri yang salah.

• misalnya: Jika Anda memilih 'Buah' suka kategori lalu pilih Apple sebagai item, lalu kembali dan ubah kategori menjadi 'Sayuran', dropdown dependen akan terus ditampilkan Apple sebagai elemennya.

Anda dapat menggunakan VBA untuk memastikan bahwa konten daftar dropdown dependen disetel ulang setiap kali daftar dropdown induk diubah. Berikut adalah kode VBA untuk menghapus isi daftar dropdown dependen:

Sub Worksheet_Change Pribadi (ByVal Target Sebagai Rentang)

Jika terjadi kesalahan, lanjutkan berikutnya

Jika Target.Kolom = 4 Maka

Jika Target.Validation.Type = 3 Lalu

Application.EnableEvents = Salah

Target.Offset(0, 1).ClearContents

Ini akan berakhir jika

Ini akan berakhir jika

exitHandler:

Application.EnableEvents = Benar

Keluar Sub

End Sub

Bagaimana cara kerjanya?

Berikut cara membuat kode ini berfungsi:

• langkah 1: Salin kode VBA.
• langkah 2: Di buku kerja Excel tempat Anda memiliki daftar drop-down dependen, masuk ke Tab pengembang, dan di dalam grup 'Kode', Klik Visual Basic (Anda juga dapat menggunakan pintasan keyboard – ALT+F11).

• langkah 3: Di jendela editor VB, di sebelah kiri penjelajah proyek, Anda akan melihat semua nama lembar kerja. Klik dua kali yang memiliki daftar drop-down.

• langkah 4: Tempelkan kode ke dalam jendela kode di sebelah kanan.

• langkah 5: Tutup editor VB.

Sekarang, setiap kali Anda mengubah daftar dropdown induk, kode VBA akan terpicu dan konten daftar dropdown dependen akan dihapus (seperti yang ditunjukkan di bawah).

Jika Anda bukan ahli VBA, Anda juga bisa menggunakan trik pemformatan bersyarat sederhana yang akan menyorot sel setiap kali ada ketidakcocokan. Ini dapat membantu Anda melihat dan memperbaiki ketidakcocokan secara visual (seperti yang ditunjukkan di bawah).

Berikut langkah-langkah untuk menyorot perbedaan dalam daftar dropdown dependen:

• langkah 1: Pilih sel yang memiliki daftar drop-down dependen.
• langkah 2: Pergi ke Beranda -> Pemformatan bersyarat -> Aturan baru.

• langkah 3: Di kotak dialog Aturan baru format, Memilih 'Gunakan rumus untuk menentukan sel mana untuk format'.

• langkah 4: Pada kolom rumus, masukkan rumus berikut:=ESERROR(VLOOKUP(E3,INDEX($A$2:$B$6,,MATCH(D3,$A$1:$B$1)), 1,0))

• langkah 5: Mengatur formatnya.
• langkah 6: Klik Oke.

Anda mungkin juga tertarik mempelajari tentang: Cara Mengelompokkan PivotTable berdasarkan Bulan di Excel

Rumusnya menggunakan fungsi VLOOKUP untuk memeriksa apakah item daftar dropdown dependen adalah item dari kategori induk atau tidak. Jika tidak, rumus akan mengembalikan kesalahan. Ini digunakan oleh fungsi ESERROR untuk kembali NYATA yang memberitahukan pemformatan bersyarat untuk menyorot sel.

Seperti yang Anda lihat, ini adalah cara yang benar Tautkan daftar dropdown di Excel. Kapan pun Anda bisa, ambillah tutorial latihan kecil ini sehingga Anda dapat menerapkan fitur berguna ini. Kami harap kami telah membantu Anda.