Item daftar sudah tidak asing lagi bagi kami oleh formulir di situs. Lebih mudah memilih nilai yang sudah jadi. Misalnya, tidak ada yang masuk sebulan secara manual, itu diambil dari daftar seperti itu. Anda bisa mengisi daftar drop-down di Excel menggunakan berbagai alat. Dalam artikel kami akan mempertimbangkan masing-masing.
Cara membuat daftar tarik turun di Excel
Bagaimana cara membuat daftar drop-down di Excel 2010 atau 2016 dengan satu perintah pada toolbar? Pada tab "Data" di bagian "Bekerja dengan Data", cari tombol "Validasi Data". Klik dan pilih item pertama.
Sebuah jendela akan terbuka. Di tab "Opsi" di bagian tarik-turun "Tipe Data", pilih "Daftar".
Sebuah baris akan muncul di bawah ini untuk menunjukkan sumbernya.
Anda dapat menentukan informasi dengan berbagai cara..
- Input manual
Masukkan daftar yang dipisahkan oleh tanda titik koma.
- Pilih rentang nilai dari lembar kerja Excel
Untuk melakukan ini, mulailah memilih sel dengan mouse..
Cara melepaskannya - jendela akan menjadi normal kembali, dan alamatnya akan muncul di baris.
- Membuat daftar drop-down di Excel dengan substitusi data
Pertama, tentukan nama. Untuk melakukan ini, buat tabel di lembar mana saja.
Pilih dan klik tombol kanan mouse. Klik perintah Tetapkan Nama.
Masukkan nama pada baris di atas.
Panggil jendela "Validasi Data" dan di bidang "Sumber", tentukan nama dengan awalan dengan "=".
Dalam salah satu dari tiga kasus ini, Anda akan melihat item yang diinginkan. Memilih nilai dari daftar drop-down Excel dilakukan dengan menggunakan mouse. Klik padanya dan daftar data yang ditentukan akan muncul..
Anda telah belajar cara membuat daftar turun bawah di sel Excel. Tetapi lebih banyak yang bisa dilakukan..
Substitusi Data Dinamis Excel
Jika Anda menambahkan beberapa nilai ke rentang data yang disubstitusikan ke dalam daftar, maka itu tidak akan berubah sampai alamat baru ditentukan secara manual. Untuk menautkan rentang dan elemen aktif, Anda perlu mengatur yang pertama sebagai tabel. Buat array seperti ini.
Pilih dan pada tab "Beranda", pilih gaya tabel apa saja.
Pastikan untuk mencentang kotak di bawah ini..
Anda akan mendapatkan desain ini.
Buat elemen aktif seperti dijelaskan di atas. Masukkan formula sebagai sumber
= TIDAK LANGSUNG ("Table1 [Kota]") 
Untuk menemukan nama tabel, buka tab "Desain" dan lihatlah. Anda dapat mengubah nama menjadi yang lain.
Fungsi INDIRECT membuat tautan ke sel atau rentang. Sekarang item Anda di sel terikat ke array data.
Mari kita coba menambah jumlah kota.
Prosedur sebaliknya - penggantian data dari daftar drop-down ke tabel Excel, bekerja sangat sederhana. Di sel tempat Anda ingin menempelkan nilai yang dipilih dari tabel, masukkan rumus:
= Cell_Address
Misalnya, jika daftar data ada di sel D1, maka di sel tempat hasil yang dipilih akan ditampilkan masukkan rumus
= D1
Cara menghapus (menghapus) daftar drop-down di Excel
Buka jendela pengaturan daftar turun bawah dan pilih "Nilai apa saja" di bagian "Tipe Data".
Item yang tidak perlu akan hilang.
Elemen Ketergantungan
Terkadang di Excel ada kebutuhan untuk membuat beberapa daftar ketika satu tergantung yang lain. Misalnya, setiap kota memiliki beberapa alamat. Saat memilih yang pertama, kita hanya akan mendapatkan alamat penyelesaian yang dipilih.
Dalam hal ini, berikan nama untuk setiap kolom. Pilih tanpa sel pertama (nama) dan tekan tombol mouse kanan. Pilih Tetapkan Nama.
Ini akan menjadi nama kota..
Anda akan mendapatkan kesalahan saat memberi nama St. Petersburg dan Nizhny Novgorod, karena nama tersebut tidak boleh mengandung spasi, garis bawah, karakter khusus, dll..
Karena itu, ganti nama kota-kota ini dengan menempatkan garis bawah.
Elemen pertama dalam sel A9 dibuat dengan cara biasa..
Dan yang kedua kita menulis rumus:
= TIDAK LANGSUNG (A9)
Anda akan melihat pesan kesalahan terlebih dahulu. Setuju.
Masalahnya adalah tidak adanya nilai yang dipilih. Begitu kota dipilih dalam daftar pertama, yang kedua akan berfungsi. 
Cara mengkonfigurasi dropdown dependen di Excel dengan pencarian
Anda dapat menggunakan rentang data dinamis untuk item kedua. Ini lebih nyaman jika jumlah alamat bertambah..
Buat daftar kota drop-down. Kisaran bernama disorot dalam oranye..
Untuk daftar kedua Anda harus memasukkan rumus:
= OFFSET ($ A $ 1; CARI ($ E $ 6; $ A: $ A; 0) -1; 1; COUNTIF ($ A: $ A; $ E $ 6); 1)
Fungsi OFFSET mengembalikan referensi ke rentang yang diimbangi relatif terhadap sel pertama dengan sejumlah baris dan kolom: = OFFSET (mulai; turun; kanan; row_size; kolom_size)
SEARCH mengembalikan nomor sel dengan kota yang dipilih dalam daftar pertama (E6) di area yang ditentukan SA: $ A.
COUNTIF menghitung jumlah kecocokan dalam kisaran dengan nilai dalam sel yang ditentukan (E6).
Kami mendapat daftar tarik-turun terkait di Excel dengan kondisi untuk mencocokkan dan menemukan rentang untuk itu.
Multi-pilih
Seringkali kita perlu mendapatkan beberapa nilai dari dataset. Anda dapat menampilkannya di sel yang berbeda, atau menggabungkannya menjadi satu. Bagaimanapun, makro diperlukan.
Klik pada label sheet di tombol kanan bawah mouse dan pilih perintah "View Code".
Jendela pengembang akan terbuka. Masukkan algoritma berikut ke dalamnya.
Private Sub Worksheet_Change (ByVal Target As Range) Pada Kesalahan, Resume Selanjutnya Jika Tidak Bersinggungan (Target, Range ("C2: F2")) Bukan Apa-Apa Dan Target.Cells.Count = 1 Kemudian Aplikasi.EnableEvents = Salah Jika Len (Target.Offset (1, 0)) = 0 Kemudian Target.Offset (1, 0) = Target Else Target.End (xlDown). Offset (1, 0) = Target End Jika Target.ClearContents Application.EnableEvents = True End Jika End Sub jika 
Perhatikan bahwa di baris
If Not Intersect (Target, Range ("E7")) Bukan Apa-Apa Dan Target.Cells.Count = 1 Lalu Tuliskan alamat sel dengan daftar. Kami akan memilikinya E7.
Kembali ke lembar kerja Excel dan buat daftar di sel E7.
Ketika Anda memilih nilai akan muncul di bawahnya.
Kode berikut akan mengakumulasi nilai dalam sel.
Private Sub Worksheet_Change (Rentang Target ByVal) Pada Kesalahan, Resume Berikutnya Jika Tidak Persimpangan (Target, Rentang ("E7")) Bukan Apa-Apa Dan Target.Cells.Count = 1 Kemudian Aplikasi. Target Jika Len (oldval) 0 Dan oldval newVal Lalu Target = Target & "," & newVal Else Target = newVal End If Jika Len (newVal) = 0 Lalu Target.ClearContents Application.EnableEvents = True End Jika End Sub End Segera setelah Anda memindahkan pointer ke sel lain, Anda akan melihat daftar kota yang dipilih. Untuk membuat sel yang digabungkan di Excel, baca artikel ini..
Kami berbicara tentang cara menambah dan mengubah daftar turun bawah di sel Excel. Kami harap informasi ini membantu Anda..
Semoga harimu menyenangkan!
