Membuat kotak pencarian di Excel meningkatkan fungsionalitas spreadsheet Anda dengan mempermudah memfilter dan mengakses data tertentu dengan cepat. Panduan ini mencakup beberapa metode untuk mengimplementasikan kotak pencarian, yang melayani berbagai versi Excel. Baik Anda seorang pemula atau pengguna tingkat lanjut, langkah-langkah ini akan membantu Anda menyiapkan kotak pencarian dinamis menggunakan fitur seperti fungsi FILTER, Pemformatan Bersyarat, dan berbagai rumus.

• Buat kotak pencarian dengan mudah menggunakan fungsi FILTER
  (tersedia di Excel 2019 dan yang lebih baru, Excel untuk Microsoft 365)
• Buat kotak pencarian menggunakan Format Bersyarat
  (tersedia di semua versi Excel)
• Buat kotak pencarian dengan kombinasi rumus
  (tersedia di semua versi Excel)

Buat kotak pencarian dengan mudah menggunakan fungsi FILTER

• Fungsi ini secara otomatis memperbarui output seiring perubahan data Anda.
• Fungsi FILTER dapat mengembalikan sejumlah hasil, dari satu baris hingga ribuan, bergantung pada berapa banyak entri dalam kumpulan data Anda yang cocok dengan kriteria yang Anda tetapkan.

Di sini saya akan menunjukkan cara menggunakan fungsi FILTER untuk membuat kotak pencarian di Excel.

Langkah 1: Sisipkan kotak teks dan konfigurasikan properti

1. Pergi ke Pengembang tab, klik Menyisipkan > TKotak ext (Kontrol ActiveX).
   jenis: Jika Pengembang tab tidak ditampilkan pada pita, Anda dapat mengaktifkannya dengan mengikuti instruksi pada tutorial ini: Bagaimana cara menampilkan / menampilkan tab pengembang di Pita Excel?
   
2. Kursor akan berubah menjadi tanda silang, lalu Anda perlu menyeret kursor untuk menggambar kotak teks di lokasi di lembar kerja tempat Anda ingin meletakkan kotak teks tersebut. Setelah menggambar kotak teks, lepaskan mouse.
   
3. Klik kanan kotak teks dan pilih Properties dari menu konteks
   
4. Dalam majalah Properties panel, tautkan kotak teks ke sel dengan memasukkan referensi sel di Sel Tertaut bidang. Misalnya mengetik "J2" memastikan bahwa setiap data yang dimasukkan dalam kotak teks diperbarui secara otomatis di sel J2, dan sebaliknya.
   
5. klik Mode Desain bawah Pengembang tab untuk keluar dari Mode Desain.
   

Kotak teks sekarang memungkinkan Anda memasukkan teks.

Langkah 2: Terapkan fungsi FILTER

1. Sebelum menggunakan fungsi FILTER, salin baris header asli ke area baru. Di sini saya menempatkan baris header di bawah kotak pencarian.
   jenis: Pendekatan ini memungkinkan pengguna melihat dengan jelas hasil pada judul kolom yang sama dengan data asli.
   
2. Pilih sel di bawah header pertama (mis I5 dalam contoh ini), masukkan rumus berikut ke dalamnya dan tekan Enter kunci untuk mendapatkan hasil.

   =FILTER(Sheet2!$A$5:$G$281,Sheet2!$B$5:$B$281=J2,"No data found")

   
   Seperti yang ditunjukkan pada gambar di atas, karena kotak teks sekarang tidak memiliki masukan, rumusnya menampilkan hasilnya "Tidak ada data ditemukan"Dalam I5.

Hasil: Uji kotak pencarian

Sekarang mari kita uji kotak pencarian. Dalam contoh ini, ketika saya memasukkan nama pelanggan di kotak pencarian, hasil terkait akan disaring dan segera ditampilkan.

Buat kotak pencarian menggunakan Conditional Formatting

Pemformatan Bersyarat dapat digunakan untuk menyorot data yang cocok dengan istilah pencarian, secara tidak langsung menciptakan efek kotak pencarian. Metode ini tidak menyaring data namun secara visual memandu Anda ke sel yang relevan. Bagian ini akan menunjukkan cara membuat kotak pencarian menggunakan Conditional Formatting di Excel.

Langkah 1: Sisipkan kotak teks dan konfigurasikan properti

1. Pergi ke Pengembang tab, klik Menyisipkan > TKotak ext (Kontrol ActiveX).
   jenis: Jika Pengembang tab tidak ditampilkan pada pita, Anda dapat mengaktifkannya dengan mengikuti instruksi pada tutorial ini: Bagaimana cara menampilkan / menampilkan tab pengembang di Pita Excel?
   
2. Kursor akan berubah menjadi tanda silang, lalu Anda perlu menyeret kursor untuk menggambar kotak teks di lokasi di lembar kerja tempat Anda ingin meletakkan kotak teks tersebut. Setelah menggambar kotak teks, lepaskan mouse.
   
3. Klik kanan kotak teks dan pilih Properties dari menu konteks
   
4. Dalam majalah Properties panel, tautkan kotak teks ke sel dengan memasukkan referensi sel di Sel Tertaut bidang. Misalnya mengetik "J3" memastikan bahwa setiap data yang dimasukkan dalam kotak teks diperbarui secara otomatis di sel J3, dan sebaliknya.
   
5. klik Mode Desain bawah Pengembang tab untuk keluar dari Mode Desain.
   

Kotak teks sekarang memungkinkan Anda memasukkan teks.

Langkah 2: Terapkan Pemformatan Bersyarat untuk mencari data

1. Pilih seluruh rentang data yang akan dicari. Di sini saya memilih rentang A3:G279.
2. Di bawah Beranda tab, klik Format Bersyarat > Aturan baru.
   
3. Dalam majalah Aturan Pemformatan Baru kotak dialog:
   1. Pilih Gunakan rumus untuk menentukan sel mana yang akan diformat dalam Pilih Jenis Aturan Pilihan.
   2. Masukkan rumus berikut ke dalam Memformat nilai yang rumus ini benar kotak.

      =$B3=$J$3

      Di sini, $ B3 mewakili sel pertama di kolom yang ingin Anda cocokkan dengan kriteria pencarian dalam rentang yang dipilih, dan $J$3 adalah sel yang ditautkan ke kotak pencarian.
   3. klik dibentuk tombol untuk menentukan warna isian untuk hasil pencarian.
   4. klik OK tombol. Lihat tangkapan layar:
      

Hasil

Sekarang mari kita uji kotak pencarian. Dalam contoh ini, ketika saya memasukkan nama pelanggan ke dalam kotak pencarian, baris terkait yang berisi pelanggan ini di kolom B akan langsung disorot dengan warna isian yang ditentukan.

Buat kotak pencarian dengan kombinasi rumus

Jika Anda tidak menggunakan Excel versi terbaru dan memilih untuk tidak hanya menyorot baris, metode yang dijelaskan di bagian ini mungkin berguna. Anda bisa menggunakan kombinasi rumus Excel untuk membuat kotak pencarian fungsional di versi Excel mana pun. Silakan ikuti langkah-langkah di bawah ini.

Langkah 1: Buat daftar nilai unik dari kolom pencarian

1. Dalam hal ini, saya memilih dan menyalin rentang B4: B281 ke lembar kerja baru.
2. Setelah menempelkan rentang di lembar kerja baru, tetap pilih data yang ditempel, buka Data Tab dan pilih Hapus Duplikat.
   
3. Di pembukaan Hapus Duplikat kotak dialog, klik OK .
   
4. A Microsoft Excel kotak prompt kemudian muncul untuk menunjukkan berapa banyak duplikat yang dihapus. Klik OK.
   
5. Setelah menghapus duplikat, pilih semua nilai unik dalam daftar, kecuali header, dan tetapkan nama untuk rentang ini dengan memasukkannya ke dalam Nama kotak. Di sini saya menamai rentang tersebut sebagai Pelanggan.
   

Langkah 2: Masukkan kotak kombo dan konfigurasikan properti

1. Kembali ke lembar kerja yang berisi kumpulan data yang ingin Anda cari. Pergi ke Pengembang tab, klik Menyisipkan > Kotak Kombo (Kontrol ActiveX).
   jenis: Jika Pengembang tab tidak ditampilkan pada pita, Anda dapat mengaktifkannya dengan mengikuti instruksi pada tutorial ini: Bagaimana cara menampilkan / menampilkan tab pengembang di Pita Excel?
   
2. Kursor akan berubah menjadi tanda silang, lalu Anda perlu menyeret kursor untuk menggambar kotak kombo di lokasi di lembar kerja tempat Anda ingin meletakkan kotak pencarian. Setelah menggambar kotak kombo, lepaskan mouse.
   
3. Klik kanan kotak kombo dan pilih Properties dari menu konteks
   
4. Dalam majalah Properties panel:
   1. Tautkan kotak kombo ke sel dengan memasukkan referensi sel ke dalam Sel Tertaut bidang. Dia aku mengetik "M2".
      Tip: Tentukan bidang ini untuk memastikan bahwa data apa pun yang dimasukkan dalam kotak kombo akan diperbarui secara otomatis di sel M2, dan sebaliknya.
   2. Dalam majalah DaftarIsiRentang bidang, masukkan nama rentang Anda tentukan untuk daftar unik di Langkah 1.
   3. Mengubah Entri Pertandingan lapangan ke 2 – fmMatchEntryNone.
   4. Tutup Properties pane
      
5. klik Mode Desain bawah Pengembang tab untuk keluar dari Mode Desain.
   

Anda sekarang dapat memilih item apa pun dari kotak kombo atau mengetikkan teks yang akan dicari.

Langkah 3: Terapkan rumus

1. Buat tiga kolom pembantu yang berdekatan dengan rentang data asli. Lihat tangkapan layar:
   
2. Di dalam sel (H5) di bawah judul kolom pembantu pertama, masukkan rumus berikut dan tekan Enter.

   =ROWS($B$5:B5)

   Sini B5 adalah sel yang berisi nama pelanggan pertama pada kolom yang akan dicari.
   
3. Klik dua kali pojok kanan bawah sel rumus, otomatis sel berikutnya akan terisi rumus yang sama.
   
4. Di dalam sel (I5) di bawah tajuk kolom pembantu kedua, masukkan rumus berikut dan tekan Enter. Lalu klik dua kali pojok kanan bawah sel rumus untuk otomatis mengisi sel di bawahnya dengan rumus yang sama.

   =IF(ISNUMBER(SEARCH($M$2,B5)),H5,"")

   Sini M2 adalah sel yang ditautkan ke kotak kombo.
   
5. Di dalam sel (J5) di bawah tajuk kolom pembantu ketiga, masukkan rumus berikut dan tekan Enter. Lalu klik dua kali pojok kanan bawah sel rumus untuk otomatis mengisi sel di bawahnya dengan rumus yang sama.

   =IFERROR(SMALL($I$5:$I$281,H5),"") 

   
6. Salin baris header asli ke area baru. Di sini saya menempatkan baris header di bawah kotak pencarian.
   
7. Pilih sel di bawah header pertama (mis L5 dalam contoh ini), masukkan rumus berikut ke dalamnya dan tekan tombol Enter.

   =IFERROR(INDEX($A$5:$G$281,$J5,COLUMNS($L$4:L4)),"")

   Sini A5: G281 adalah seluruh rentang data yang ingin Anda tampilkan di sel hasil.
   
8. Pilih sel rumus ini, seret Isi Handle ke kanan lalu ke bawah untuk menerapkan rumus ke kolom dan baris terkait.
   
   Catatan:

   • Karena tidak ada input di kotak pencarian, maka hasil rumus akan menampilkan data mentah.
   • Metode ini tidak peka huruf besar-kecil, artinya metode ini akan mencocokkan teks terlepas dari apakah Anda mengetikkan huruf besar atau kecil.

Hasil

Sekarang mari kita uji kotak pencarian. Dalam contoh ini, ketika saya memasukkan atau memilih nama pelanggan dari kotak kombo, baris terkait yang berisi nama pelanggan tersebut di kolom B akan disaring dan segera ditampilkan dalam rentang hasil.

Membuat kotak pencarian di Excel dapat meningkatkan cara Anda berinteraksi dengan data secara signifikan, menjadikan spreadsheet Anda lebih dinamis dan ramah pengguna. Baik Anda memilih kesederhanaan fungsi FILTER, bantuan visual Pemformatan Bersyarat, atau keserbagunaan kombinasi rumus, setiap metode menyediakan alat berharga untuk meningkatkan kemampuan manipulasi data Anda. Bereksperimenlah dengan teknik ini untuk menemukan teknik mana yang paling sesuai dengan kebutuhan spesifik dan skenario data Anda. Bagi mereka yang ingin mempelajari lebih dalam kemampuan Excel, situs web kami menawarkan banyak tutorial. Temukan tips dan trik Excel lainnya di sini.

Artikel terkait