Kotak Pencarian di Excel

Membuat Kotak Pencarian di Excel

Ide membuat kotak pencarian di excel, sehingga kami terus menulis data yang diperlukan dan karenanya akan menyaring data dan hanya menampilkan data sebanyak itu. Pada artikel ini, kami akan menunjukkan cara membuat kotak pencarian dan memfilter data di excel.

15 Langkah Mudah Membuat Kotak Pencarian Dinamis di Excel

Anda dapat mendownload Template Excel Kotak Pencarian ini di sini - Template Kotak Pencarian Excel

Untuk membuat kotak pencarian dinamis di excel. kami akan menggunakan data di bawah ini. Anda dapat mengunduh workbook dan mengikuti kami untuk membuatnya sendiri.

Ikuti langkah-langkah di bawah ini untuk membuat kotak pencarian dinamis di excel.

  • Langkah 1: Pertama buat daftar unik nama " Kota " dengan menghapus duplikat di lembar kerja baru.

  • Langkah 2: Untuk daftar unik dari kota memberi nama sebagai “ CityList ”

  • Langkah 3: Buka tab Pengembang di excel dan dari sisipan, kotak menyisipkan " Kotak Kombo ".

  • Langkah 4: Gambarkan kotak " Combo " ini pada lembar kerja Anda tempat data berada.

  • Langkah 5: Klik kanan pada "Combo Box" ini dan pilih opsi " Properties ".

  • Langkah 6: Ini akan membuka opsi properti seperti di bawah ini.

  • Langkah 7: Kami memiliki beberapa properti di sini, untuk properti " Sel Tertaut " memberikan link ke sel D2 .

  • Langkah 8: Untuk properti " List Fill Range ", berikan nama yang diberikan ke daftar unik "Cities".

  • Langkah 9: Untuk properti " Match Entry ", pilih 2-fmMatchEntryNone karena saat Anda mengetik nama di kotak kombo, kalimat tersebut tidak akan selesai secara otomatis.

  • Langkah 10: Kami selesai dengan bagian properti dari "Combo Box". Pergi ke tab " Pengembang " dan batal pilih opsi mode " Desain " dari "Kotak Kombo".

  • Langkah 11: Sekarang dari combo box kita bisa melihat nama kota di daftar drop-down di excel.

Faktanya, kita dapat mengetikkan nama di dalam kotak kombo dan hal yang sama akan mencerminkan sel D2 yang sebaris juga.

  • Langkah 12: Sekarang kita perlu menulis rumus untuk memfilter data saat kita mengetik nama kota di kotak kombo. Untuk ini, kita perlu memiliki tiga kolom pembantu, untuk kolom pembantu pertama kita perlu mencari nomor baris dengan menggunakan fungsi ROWS.

  • Langkah 13: Di kolom pembantu kedua kita perlu menemukan pencarian terkait nama kota dan jika cocok kita memerlukan nomor baris dari kota-kota itu untuk memasukkan rumus di bawah ini.

Rumus ini akan mencari nama kota di tabel utama jika cocok maka akan mengembalikan nomor baris dari kolom "Helper 1" atau akan mengembalikan sel kosong.

Misalnya, sekarang saya akan mengetik " Los Angeles " dan di mana pun nama kota itu ada di tabel utama untuk kota-kota itu, kita akan mendapatkan nomor barisnya.

  • Langkah 14: Setelah nomor baris dari nama kota yang dimasukkan atau dipilih tersedia, kita perlu menyatukan nomor baris ini satu di bawah yang lain, jadi di kolom pembantu ketiga kita perlu menumpuk semua nomor baris dari nama kota yang dimasukkan ini.

Untuk mendapatkan nomor baris ini bersama-sama kita akan menggunakan rumus kombinasi " IFERROR di Excel " dan fungsi " SMALL " di Excel.

Rumus ini akan mencari nilai terkecil dalam daftar kota yang cocok berdasarkan nomor baris sebenarnya dan akan menumpuk yang terkecil pertama, terkecil kedua, terkecil ketiga dan seterusnya. Setelah semua nilai kecil ditumpuk bersama-sama, fungsi SMALL melempar nilai kesalahan, jadi untuk menghindari hal ini kita telah menggunakan fungsi IFERROR dan jika nilai kesalahan datang maka akan mengembalikan sel kosong sebagai hasilnya.

  • Langkah 15: Sekarang buat format tabel yang identik seperti di bawah ini.

Pada tabel baru ini, kita perlu memfilter data berdasarkan nama kota yang kita ketik di kotak pencarian excel. Ini dapat dilakukan dengan menggunakan kombinasi fungsi IFERROR, INDEX, dan COLUMNS di excel. Di bawah ini adalah rumus yang perlu Anda terapkan.

Salin rumus dan tempelkan ke semua sel lain di tabel baru.

Ok, kita sudah selesai mendesain bagian, mari pelajari cara menggunakannya.

Ketik nama kota di kotak kombo dan tabel baru kami akan memfilter data kota yang dimasukkan saja.

Seperti yang Anda lihat, saya baru saja mengetik "LO" dan semua hasil pencarian terkait disaring dalam format tabel baru.

Hal-hal yang Perlu Diingat di sini

  • Anda perlu memasukkan kotak kombo di excel dari "Kontrol Formulir ActiveX" di bawah tab "Pengembang".
  • Kotak kombo cocok dengan semua huruf terkait mengembalikan hasilnya.