Membuat daftar drop-down di Excel menyederhanakan entri data dan meminimalkan kesalahan. Namun dengan kumpulan data yang lebih besar, menelusuri daftar yang panjang menjadi rumit. Bukankah lebih mudah untuk mengetik dan menemukan item Anda dengan cepat? A "daftar drop-down yang dapat dicari" menawarkan kemudahan ini. Panduan ini akan memandu Anda melalui empat metode untuk menyiapkan daftar seperti itu di Excel.

• Daftar drop-down yang dapat dicari di Excel 365
• Buat daftar drop-down yang dapat dicari (untuk Excel 2019 dan lebih baru)
• Buat daftar drop-down yang dapat dicari dengan mudah (untuk semua versi Excel)
• Buat daftar drop-down yang dapat dicari dengan kotak Kombo dan VBA (lebih kompleks)

Video

Daftar drop-down yang dapat dicari di Excel 365

Excel 365 telah memperkenalkan fitur yang sangat dinantikan pada daftar drop-down validasi datanya: kemampuan untuk mencari dalam daftar. Dengan fungsi pencarian, pengguna dapat dengan cepat menemukan dan memilih item dengan cara yang lebih efisien. Setelah memasukkan daftar drop-down seperti biasa, cukup klik sel dengan daftar drop-down dan mulailah mengetik. Daftar akan langsung memfilter agar sesuai dengan teks yang diketik.

Dalam hal ini, saya mengetik San di sel dan daftar drop-down menyaring kota-kota yang dimulai dengan istilah pencarian San, Seperti San Fransisco dan San Diego. Kemudian Anda dapat memilih hasilnya dengan mouse Anda atau menggunakan tombol panah dan tekan Enter.

Buat daftar drop-down yang dapat dicari (untuk Excel 2019 dan lebih baru)

Jika Anda menggunakan Excel 2019 atau versi yang lebih baru, metode di bagian ini juga dapat digunakan untuk membuat daftar drop-down dapat dicari di Excel.

Dengan asumsi Anda telah membuat daftar drop-down di sel A2 pada Sheet2 (gambar di sebelah kanan) menggunakan data dalam rentang A2:A8 dari Sheet1 (gambar di sebelah kiri), ikuti langkah-langkah berikut untuk membuat daftar dapat dicari.

Langkah 1. Buat kolom pembantu yang mencantumkan item pencarian

Di sini kita memerlukan kolom pembantu untuk mencantumkan item yang cocok dengan data sumber Anda. Dalam hal ini, saya akan membuat kolom pembantu kolom D of Sheet1.

1. Pilih sel pertama D1 di kolom D dan masukkan header kolom, seperti "Hasil Pencarian" pada kasus ini.
2. Masukkan rumus berikut ke dalam sel D2 dan tekan Enter.

   =FILTER(A2:A8,ISNUMBER(SEARCH(Sheet2!A2,A2:A8)),"Not Found")

   

Langkah 2: Konfigurasikan ulang daftar drop-down

1. Pilih sel daftar drop-down (dalam hal ini, saya memilih sel A2 dari Sheet2), lalu pilih Data > Validasi Data > Validasi Data.
   
2. Dalam majalah Validasi Data kotak dialog, Anda perlu mengkonfigurasi sebagai berikut.
   1. Di bawah Settings tab, klik tombol di sumber kotak.
      
   2. Grafik Validasi Data kotak dialog akan dialihkan ke Sheet1, pilih sel (misalnya D2) dengan rumus dari Langkah 1, tambahkan a # simbol, dan klik Penyelesaian .
      
   3. Pergi ke Peringatan Kesalahan tab, hapus centang pada Tampilkan peringatan kesalahan setelah data yang tidak valid dimasukkan kotak centang, dan terakhir klik OK Tombol untuk menyimpan perubahan.
      

Hasil

Daftar drop-down di sel A2 pada Sheet2 sekarang dapat dicari. Ketik teks di dalam sel, klik panah drop-down untuk memperluas daftar drop-down, dan Anda akan melihat daftar langsung difilter agar sesuai dengan teks yang diketik.

Buat daftar drop-down yang dapat dicari dengan mudah (untuk semua versi Excel)

Mengingat berbagai keterbatasan metode di atas, berikut adalah alat yang sangat efektif untuk Anda - Kutools untuk Excel's Jadikan Daftar Drop-down Dapat Dicari, Popup Otomatisfitur. Fitur ini tersedia di semua versi Excel dan memungkinkan Anda mencari item yang diinginkan dengan mudah di daftar drop-down dengan pengaturan sederhana.

Setelah mengunduh dan menginstal Kutools untuk Excel, pilih Kutools > Daftar Drop-down > Jadikan Daftar Drop-down Dapat Dicari, Popup Otomatis untuk mengaktifkan fitur ini. Dalam Jadikan Daftar Drop-down Dapat Dicari kotak dialog, Anda perlu:

1. Pilih rentang yang berisi daftar drop-down yang perlu ditetapkan sebagai daftar drop-down yang dapat dicari.
2. Klik OK untuk menyelesaikan pengaturan.
   

Hasil

Saat Anda mengklik sel daftar drop-down dalam rentang yang ditentukan, kotak daftar akan muncul di sebelah kanan. Ketik teks untuk memfilter daftar secara instan, lalu pilih item atau gunakan tombol panah dan tekan Enter untuk menambahkannya ke sel.

Buat daftar drop-down yang dapat dicari dengan kotak Kombo dan VBA (lebih kompleks)

Jika Anda hanya ingin membuat daftar drop-down yang dapat dicari tanpa menentukan jenis daftar drop-down tertentu. Bagian ini memberikan pendekatan alternatif: menggunakan kotak Kombo dengan kode VBA untuk menyelesaikan tugas.

Misalkan Anda memiliki daftar nama negara di kolom A seperti yang ditunjukkan pada gambar di bawah, dan sekarang Anda ingin menggunakannya sebagai sumber data dari daftar drop-down searchale, Anda dapat melakukan hal berikut untuk menyelesaikannya.

Anda perlu menyisipkan kotak kombo alih-alih daftar drop-down validasi data di lembar kerja Anda.

1. Jika Pengembang tab tidak ditampilkan di pita, Anda dapat mengaktifkan Pengembang tab sebagai berikut.
   1. Di Excel 2010 atau versi yang lebih baru, klik File > Opsi. Dan di Opsi Excel kotak dialog, klik Sesuaikan Pita di panel kiri. Pergi ke kotak daftar Customize the Ribbon, centang Pengembang kotak, dan kemudian klik OK tombol. Lihat tangkapan layar:
      
   2. Di Excel 2007, klik Office tombol> Opsi Excel. di Opsi Excel kotak dialog, klik Populer di panel kiri, periksa Perlihatkan tab Pengembang di Pita kotak, dan terakhir klik OK .
      
2. Setelah menunjukkan Pengembang tab, klik Pengembang > Menyisipkan > Kotak kombo.
   
3. Gambarlah kotak Kombo di lembar kerja, klik kanan lalu pilih Properties dari menu klik kanan.
   
4. Dalam majalah Properties kotak dialog, Anda perlu:
   1. Pilih Salah dalam Pilih Kata Otomatis bidang;
   2. Tentukan sel di Sel Tertaut bidang. Dalam hal ini, kami memasukkan A12;
   3. Pilih 2-fmMatchEntryTidak Ada dalam Entri Pertandingan bidang;
   4. Tipe DropDownList ke dalam DaftarIsiRentang bidang;
   5. Tutup Properties kotak dialog. Lihat tangkapan layar:
      
5. Sekarang matikan mode desain dengan mengklik Pengembang > Mode Desain.
6. Pilih sel kosong seperti C2, masukkan rumus di bawah ini dan tekan Enter. Mereka menyeret Pegangan IsiOtomatisnya ke sel C9 untuk mengisi sel secara otomatis dengan rumus yang sama. Lihat tangkapan layar:

   =--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),""))

   
   Catatan:

   1. $ A $ 12 adalah sel yang telah Anda tentukan sebagai Sel Tertaut di langkah 4;
   2. Setelah menyelesaikan langkah-langkah di atas, kini Anda dapat menguji: memasukkan huruf C ke dalam kotak kombo, lalu Anda dapat melihat bahwa sel rumus yang mereferensikan sel berisi karakter C diisi dengan angka 1.
7. Pilih sel D2, masukkan rumus di bawah ini dan tekan Enter. Kemudian seret Pegangan IsiOtomatisnya ke bawah ke sel D9.

   =IF(C2=1,COUNTIF($C$2:C2,1),"")

   
8. Pilih sel E2, masukkan rumus di bawah ini dan tekan Enter. Kemudian seret Pegangan IsiOtomatisnya ke E9 untuk menerapkan rumus yang sama.

   =IFERROR(INDEX($A$2:$A$9,MATCH(ROWS($D$2:D2),$D$2:$D$9,0)),"")

   
9. Sekarang Anda perlu membuat rentang nama. Silakan klik Rumus > Tentukan Nama.
   
10. Dalam majalah Nama baru kotak dialog, ketik DropDownList dalam Nama kotak, masukkan rumus di bawah ini di Mengacu pada kotak, dan kemudian klik OK .

    =$E$2:INDEX($E$2:$E$9,MAX($D$2:$D$9),1)
    

    
11. Sekarang, aktifkan mode desain dengan mengklik Pengembang > Mode Desain. Kemudian klik dua kali kotak Combo untuk membuka Microsoft Visual Basic untuk Aplikasi jendela.
12. Salin dan tempel kode VBA di bawah ini ke editor Kode.
    
    Kode VBA: buat daftar drop-down dapat dicari

    Private Sub ComboBox1_GotFocus()
    	ComboBox1.ListFillRange = "DropDownList"
    	Me.ComboBox1.DropDown
    End Sub

13. Tekan lain + Q kunci untuk menutup Microsoft Visual Basic untuk Aplikasi jendela.

Mulai sekarang, ketika karakter dimasukkan dalam kotak kombo, ia akan melakukan pencarian fuzzy dan kemudian mencantumkan nilai yang relevan dalam daftar.

Artikel terkait: