Cara membuat daftar dropdown di Excel

Daftar drop-down adalah alat yang sangat berguna yang dapat membantu membuat bekerja dengan informasi lebih nyaman. Itu memungkinkan untuk memuat beberapa nilai dalam sel sekaligus, yang dengannya Anda dapat bekerja, seperti yang lainnya. Untuk memilih yang Anda butuhkan, cukup klik pada ikon panah, setelah itu daftar nilai uXNUMXbuXNUMXbis ditampilkan. Setelah memilih yang spesifik, sel secara otomatis diisi dengannya, dan rumus dihitung ulang berdasarkan itu.

Excel menyediakan banyak metode berbeda untuk menghasilkan menu drop-down, dan sebagai tambahan, ini memungkinkan Anda untuk menyesuaikannya secara fleksibel. Mari kita menganalisis metode ini secara lebih rinci.

Proses pembuatan daftar

Untuk menghasilkan menu pop-up, klik item menu di sepanjang jalur “Data” – “Validasi Data”. Sebuah kotak dialog akan terbuka di mana Anda perlu menemukan tab "Parameters" dan klik di atasnya jika belum dibuka sebelumnya. Ini memiliki banyak pengaturan, tetapi item "Tipe Data" penting bagi kami. Dari semua arti, "Daftar" adalah yang benar.

Jumlah metode yang digunakan untuk memasukkan informasi ke dalam daftar pop-up cukup besar.

1. Indikasi independen dari elemen daftar yang dipisahkan oleh titik koma di bidang "Sumber" yang terletak di tab yang sama dari kotak dialog yang sama.
   

   

2. Indikasi awal nilai. Bidang Sumber berisi rentang di mana informasi yang diperlukan tersedia.
   

   

3. Menentukan rentang bernama. Metode yang mengulangi yang sebelumnya, tetapi hanya perlu memberi nama rentang terlebih dahulu.
   

   

Salah satu dari metode ini akan menghasilkan hasil yang diinginkan. Mari kita lihat metode untuk menghasilkan daftar drop-down dalam situasi kehidupan nyata.

Berdasarkan data dari daftar

Katakanlah kita memiliki tabel yang menjelaskan jenis buah yang berbeda.

Untuk membuat daftar di menu tarik-turun berdasarkan kumpulan informasi ini, Anda perlu melakukan hal berikut:

1. Pilih sel yang dicadangkan untuk daftar mendatang.
2. Temukan tab Data di pita. Di sana kita klik "Verifikasi data".
   

   

3. Temukan item "Tipe Data" dan alihkan nilainya ke "Daftar".
   

   

4. Di bidang yang menunjukkan opsi "Sumber", masukkan rentang yang diinginkan. Harap dicatat bahwa referensi mutlak harus ditentukan sehingga saat menyalin daftar, informasi tidak bergeser.
   

Selain itu, ada fungsi untuk menghasilkan daftar sekaligus di lebih dari satu sel. Untuk mencapai ini, Anda harus memilih semuanya, dan melakukan langkah yang sama seperti yang dijelaskan sebelumnya. Sekali lagi, Anda perlu memastikan bahwa referensi absolut ditulis. Jika alamat tidak memiliki tanda dolar di sebelah nama kolom dan baris, maka Anda perlu menambahkannya dengan menekan tombol F4 hingga tanda $ berada di sebelah nama kolom dan baris.

Dengan perekaman data manual

Dalam situasi di atas, daftar ditulis dengan menyorot rentang yang diperlukan. Ini adalah metode yang mudah, tetapi terkadang perlu untuk merekam data secara manual. Ini akan memungkinkan untuk menghindari duplikasi informasi dalam buku kerja.

Misalkan kita dihadapkan pada tugas membuat daftar yang berisi dua kemungkinan pilihan: ya dan tidak. Untuk menyelesaikan tugas, perlu:

1. Klik pada sel untuk daftar.
2. Buka "Data" dan temukan bagian "Pemeriksaan Data" yang tidak asing lagi bagi kita.
   

   

3. Sekali lagi, pilih jenis "Daftar".
   

   

4. Di sini Anda harus memasukkan “Ya; Tidak” sebagai sumbernya. Kami melihat bahwa informasi dimasukkan secara manual menggunakan titik koma untuk enumerasi.

Setelah mengklik OK, kami memiliki hasil sebagai berikut.

Selanjutnya, program akan secara otomatis membuat menu drop-down di sel yang sesuai. Semua informasi yang telah ditentukan pengguna sebagai item dalam daftar pop-up. Aturan untuk membuat daftar di beberapa sel mirip dengan yang sebelumnya, dengan satu-satunya pengecualian bahwa Anda harus menentukan informasi secara manual menggunakan titik koma.

Membuat daftar drop-down menggunakan fungsi OFFSET

Selain metode klasik, dimungkinkan untuk menggunakan fungsi PEMBUANGANuntuk menghasilkan menu dropdown.

Mari kita buka lembarannya.

Untuk menggunakan fungsi untuk daftar dropdown, Anda perlu melakukan hal berikut:

1. Pilih sel yang diinginkan di mana Anda ingin menempatkan daftar masa depan.
2. Buka tab "Data" dan jendela "Validasi Data" secara berurutan.
   

   

3. Setel "Daftar". Ini dilakukan dengan cara yang sama seperti contoh sebelumnya. Akhirnya, rumus berikut digunakan: =OFFSET(A$2$;0;0;5). Kami memasukkannya di mana sel yang akan digunakan sebagai argumen ditentukan.

Kemudian program akan membuat menu dengan daftar buah-buahan.

Sintaks untuk ini adalah:

=OFFSET(referensi,line_offset,column_offset,[tinggi],[lebar])

Kita melihat bahwa fungsi ini memiliki 5 argumen. Pertama, alamat sel pertama yang akan di-offset diberikan. Dua argumen berikutnya menentukan berapa banyak baris dan kolom yang akan diimbangi. Berbicara tentang kami, argumen Tinggi adalah 5 karena mewakili ketinggian daftar. 

Daftar dropdown di Excel dengan substitusi data (+ menggunakan fungsi OFFSET)

Dalam kasus yang diberikan PEMBUANGAN diizinkan untuk membuat menu pop-up yang terletak di kisaran tetap. Kerugian dari metode ini adalah setelah menambahkan item, Anda harus mengedit sendiri rumusnya.

Untuk membuat daftar dinamis dengan dukungan untuk memasukkan informasi baru, Anda harus:

1. Pilih sel yang diinginkan.
2. Perluas tab "Data" dan klik "Validasi Data".
3. Di jendela yang terbuka, pilih item "Daftar" lagi dan tentukan rumus berikut sebagai sumber data: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Klik OK.

Ini berisi fungsi COUNTIF, untuk segera menentukan berapa banyak sel yang terisi (walaupun memiliki jumlah kegunaan yang jauh lebih banyak, kami hanya menuliskannya di sini untuk tujuan tertentu).

Agar rumus berfungsi secara normal, perlu untuk melacak apakah ada sel kosong di jalur rumus. Seharusnya tidak.

Daftar dropdown dengan data dari lembar lain atau file Excel

Metode klasik tidak berfungsi jika Anda perlu mendapatkan informasi dari dokumen lain atau bahkan lembar yang terdapat dalam file yang sama. Untuk ini, fungsinya digunakan TIDAK LANGSUNG, yang memungkinkan Anda memasukkan dalam format yang benar tautan ke sel yang terletak di lembar lain atau secara umum – file. Anda perlu melakukan hal berikut:

1. Aktifkan sel tempat kita menempatkan daftar.
2. Kami membuka jendela yang sudah kami ketahui. Di tempat yang sama di mana kami sebelumnya menunjukkan sumber untuk rentang lain, formula ditunjukkan dalam format =LANGSUNG(“[List1.xlsx]Sheet1!$A$1:$A$9”). Secara alami, alih-alih List1 dan Sheet1, Anda dapat memasukkan nama buku dan lembar Anda masing-masing. 

Perhatian! Nama file diberikan dalam tanda kurung siku. Dalam hal ini, Excel tidak akan dapat menggunakan file yang saat ini ditutup sebagai sumber informasi.

Perlu juga dicatat bahwa nama file itu sendiri masuk akal hanya jika dokumen yang diperlukan terletak di folder yang sama dengan folder tempat daftar akan dimasukkan. Jika tidak, maka Anda harus menentukan alamat dokumen ini secara lengkap.

Membuat Dropdown Dependen

Daftar dependen adalah daftar yang isinya dipengaruhi oleh pilihan pengguna di daftar lain. Misalkan kita memiliki meja terbuka di depan kita yang berisi tiga rentang, yang masing-masing telah diberi nama.

Anda harus mengikuti langkah-langkah ini untuk membuat daftar yang hasilnya dipengaruhi oleh opsi yang dipilih di daftar lain.

1. Buat daftar pertama dengan nama rentang.
   

   

2. Di titik masuk sumber, indikator yang diperlukan disorot satu per satu.
   

   

3. Buat daftar kedua tergantung pada jenis tanaman yang dipilih orang tersebut. Atau, jika Anda menentukan pohon di daftar pertama, maka informasi di daftar kedua adalah "ek, hornbeam, kastanye" dan seterusnya. Hal ini diperlukan untuk menuliskan rumus di tempat input dari sumber data =TIDAK LANGSUNG(E3). E3 – sel yang berisi nama rentang 1.=INDIRECT(E3). E3 – sel dengan nama daftar 1.

Sekarang semuanya sudah siap.

Bagaimana cara memilih beberapa nilai dari daftar drop-down?

Terkadang tidak mungkin untuk memberikan preferensi hanya pada satu nilai, jadi lebih dari satu harus dipilih. Maka Anda perlu menambahkan makro ke kode halaman. Menggunakan kombinasi tombol Alt + F11 membuka Editor Visual Basic. Dan kode dimasukkan di sana.

Sub Worksheet_Change Pribadi (ByVal Target Sebagai Rentang)

    On Error Resume Next

    If Not Intersect(Target, Range(«Е2:Е9»)) Tidak Ada Dan Target.Cells.Count = 1 Kemudian

        Application.EnableEvents = Salah

        Jika Len (Target.Offset (0, 1)) = 0 Maka

            Target.Offset (0, 1) = Target

        lain

            Target.End (xlToRight) .Offset (0, 1) = Target

        End If

        Target.Hapus Konten

        Application.EnableEvents = Benar

    End If

End Sub 

Agar isi sel ditampilkan di bawah, kami memasukkan kode berikut ke dalam editor.

Sub Worksheet_Change Pribadi (ByVal Target Sebagai Rentang)

    On Error Resume Next

    If Not Intersect(Target, Range(«Н2:К2»)) Tidak Ada Dan Target.Cells.Count = 1 Kemudian

        Application.EnableEvents = Salah

        Jika Len (Target.Offset (1, 0)) = 0 Maka

            Target.Offset (1, 0) = Target

        lain

            Target.End (xlDown) .Offset (1, 0) = Target

        End If

        Target.Hapus Konten

        Application.EnableEvents = Benar

    End If

End Sub

Dan terakhir, kode ini digunakan untuk menulis dalam satu sel.

Sub Worksheet_Change Pribadi (ByVal Target Sebagai Rentang)

    On Error Resume Next

    If Not Intersect(Target, Range(«C2:C5»)) Tidak Ada Dan Target.Cells.Count = 1 Kemudian

        Application.EnableEvents = Salah

        NilaiBaru = Target

        Aplikasi.Urungkan

        oldval = Sasaran

        Jika Len (oldval) <> 0 Dan oldval <> newVal Lalu

            Target = Target & «,» & newVal

        lain

            Target = nilai baru

        End If

        Jika Len (newVal) = 0 Maka Target.ClearContents

        Application.EnableEvents = Benar

    End If

End Sub

Rentang dapat diedit.

Bagaimana cara membuat daftar dropdown dengan pencarian?

Dalam hal ini, Anda harus terlebih dahulu menggunakan jenis daftar yang berbeda. Tab "Pengembang" terbuka, setelah itu Anda perlu mengklik atau mengetuk (jika layar disentuh) pada elemen "Sisipkan" – "ActiveX". Ini memiliki kotak kombo. Anda akan diminta untuk menggambar daftar ini, setelah itu akan ditambahkan ke dokumen.

Selanjutnya, dikonfigurasi melalui properti, di mana rentang ditentukan dalam opsi ListFillRange. Sel tempat nilai yang ditentukan pengguna ditampilkan dikonfigurasi menggunakan opsi LinkedCell. Selanjutnya, Anda hanya perlu menuliskan karakter pertama, karena program akan secara otomatis menyarankan nilai yang memungkinkan.

Daftar dropdown dengan substitusi data otomatis

Ada juga fungsi bahwa data diganti secara otomatis setelah ditambahkan ke rentang. Sangat mudah untuk melakukan ini:

1. Buat satu set sel untuk daftar mendatang. Dalam kasus kami, ini adalah satu set warna. Kami memilihnya.
   

   

2. Selanjutnya, perlu diformat sebagai tabel. Anda perlu mengklik tombol dengan nama yang sama dan memilih gaya tabel.
   

   

   

Selanjutnya, Anda perlu mengonfirmasi rentang ini dengan menekan tombol "OK".

Kami memilih tabel yang dihasilkan dan memberinya nama melalui bidang input yang terletak di atas kolom A.

Itu saja, ada tabel, dan itu dapat digunakan sebagai dasar untuk daftar drop-down, yang Anda butuhkan:

1. Pilih sel tempat daftar berada.
2. Buka dialog Validasi Data.
   

   

3. Kami mengatur tipe data ke "Daftar", dan sebagai nilai kami memberikan nama tabel melalui tanda =.
   

   

   

Semuanya, sel sudah siap, dan nama-nama warna ditampilkan di dalamnya, seperti yang kita butuhkan. Sekarang Anda dapat menambahkan posisi baru hanya dengan menuliskannya di sel yang terletak sedikit lebih rendah segera setelah yang terakhir.

Ini adalah keuntungan dari tabel, bahwa rentang secara otomatis meningkat ketika data baru ditambahkan. Dengan demikian, ini adalah cara paling mudah untuk menambahkan daftar.

Bagaimana cara menyalin daftar drop-down?

Untuk menyalin, cukup menggunakan kombinasi tombol Ctrl + C dan Ctrl + V. Jadi daftar drop-down akan disalin bersama dengan pemformatan. Untuk menghapus pemformatan, Anda perlu menggunakan tempel khusus (di menu konteks, opsi ini muncul setelah menyalin daftar), di mana opsi "kondisi pada nilai" diatur.

Pilih semua sel yang berisi daftar drop-down

Untuk menyelesaikan tugas ini, Anda harus menggunakan fungsi "Pilih grup sel" di grup "Temukan dan Pilih".

Setelah itu, kotak dialog akan terbuka, di mana Anda harus memilih item "Semua" dan "Ini sama" di menu "Validasi Data". Item pertama memilih semua daftar, dan item kedua hanya memilih yang mirip dengan daftar tertentu.