Note: The other languages of the website are Google-translated. Back to English
English English

Bagaimana cara membuat kotak pencarian Anda sendiri di Excel?

Selain menggunakan fungsi Find di Excel, sebenarnya Anda dapat membuat kotak pencarian sendiri untuk mencari nilai yang dibutuhkan dengan mudah. Artikel ini akan menunjukkan kepada Anda dua metode untuk membuat kotak pencarian Anda sendiri di Excel secara detail.

Buat kotak pencarian Anda sendiri dengan Conditional Formatting untuk menyorot semua hasil pencarian
Buat kotak pencarian Anda sendiri dengan rumus untuk mendaftar semua hasil pencarian


Buat kotak pencarian Anda sendiri dengan Conditional Formatting untuk menyorot semua hasil pencarian

Anda dapat melakukan hal berikut untuk membuat kotak pencarian Anda sendiri dengan menggunakan fungsi Pemformatan Bersyarat di Excel.

1. Pilih kisaran dengan data yang Anda butuhkan untuk mencari dengan kotak pencarian, lalu klik Format Bersyarat > Aturan baru bawah Beranda tab. Lihat tangkapan layar:

2. Dalam Aturan Pemformatan Baru kotak dialog, Anda perlu:

2.1) Pilih Gunakan rumus untuk menentukan sel mana yang akan diformat pilihan dalam Pilih Jenis Aturan kotak;

2.2) Masukkan rumus = ISNUMBER (CARI ($ B $ 2, A5)) ke dalam Memformat nilai yang rumus ini benar kotak;

2.3) Klik dibentuk tombol untuk menentukan warna yang disorot untuk nilai yang dicari;

2.4) Klik OK .

Catatan:

1. Dalam rumusnya, $ B $ 2 adalah sel kosong yang Anda perlukan untuk menggunakannya sebagai kotak pencarian, dan A5 adalah sel pertama dari rentang yang Anda pilih yang Anda perlukan untuk mencari nilai di dalamnya. Harap ubah sesuai kebutuhan Anda.

2. Rumusnya tidak peka huruf besar / kecil.

Sekarang kotak pencarian dibuat, saat mengetik kriteria pencarian ke dalam kotak pencarian B2 dan tekan tombol Enter, semua nilai yang cocok dalam kisaran yang ditentukan dicari dan disorot segera seperti gambar di bawah ini.


Buat kotak pencarian Anda sendiri dengan rumus untuk mendaftar semua hasil pencarian

Misalkan Anda memiliki daftar data yang terletak di kisaran E4: E23 yang perlu Anda cari, jika Anda ingin mencantumkan semua nilai yang cocok di kolom lain setelah mencari dengan kotak pencarian Anda sendiri, Anda dapat mencoba metode di bawah ini.

1. Pilih sel kosong yang berdekatan dengan sel E4, disini saya pilih sel D4, lalu masukkan rumus = IFERROR (SEARCH ($ B $ 2, E4) + ROW () / 100000, "") ke dalam bilah rumus, lalu tekan Enter kunci. Lihat tangkapan layar:

Note: Dalam rumusnya, $ B $ 2 adalah sel yang Anda perlukan untuk menggunakannya sebagai kotak pencarian, E4 adalah sel pertama dari daftar data yang perlu Anda cari. Anda dapat mengubahnya sesuai kebutuhan.

2. Tetap pilih sel E4, lalu seret Fill Handle ke bawah ke sel D23. Lihat tangkapan layar:

3. Sekarang pilih sel C4, masukkan rumus = IFERROR (PERINGKAT (D4, $ D $ 4: $ D $ 23,1), "") ke Formula Bar, dan tekan Enter kunci. Pilih sel C4, lalu seret Fill Handle ke C23. Lihat tangkapan layar:

4. Sekarang Anda perlu mengisi rentang A4: A23 dengan nomor seri yang meningkat 1 dari 1 menjadi 20 seperti gambar di bawah ini:

5. Pilih sel kosong yang Anda perlukan untuk menampilkan hasil pencarian, masukkan rumus = IFERROR (VLOOKUP (A4, $ C $ 4: $ E $ 23,3, FALSE), "") ke dalam Formula Bar dan tekan Enter kunci. Tetap pilih sel B4, seret Fill Handle ke B23 seperti gambar di bawah ini.

Mulai sekarang, saat memasukkan data di kotak pencarian B2, semua nilai yang cocok akan terdaftar dalam kisaran B4: B23 seperti gambar di bawah ini.

Note: metode ini tidak peka huruf besar / kecil.


Alat Produktivitas Kantor Terbaik

Kutools for Excel Memecahkan Sebagian Besar Masalah Anda, dan Meningkatkan Produktivitas Anda hingga 80%

  • Reuse: Masukkan dengan cepat rumus, bagan yang kompleks dan apa pun yang pernah Anda gunakan sebelumnya; Enkripsi Sel dengan kata sandi; Buat Milis dan mengirim email ...
  • Bilah Formula Super (dengan mudah mengedit beberapa baris teks dan rumus); Membaca Tata Letak (membaca dan mengedit sel dalam jumlah besar dengan mudah); Tempel ke Rentang yang Difilter...
  • Gabungkan Sel / Baris / Kolom tanpa kehilangan Data; Pisahkan Konten Sel; Gabungkan Baris / Kolom Duplikat... Mencegah Sel Duplikat; Bandingkan Rentang...
  • Pilih Duplikat atau Unik Baris; Pilih Baris Kosong (semua sel kosong); Temukan Super dan Temukan Fuzzy di Banyak Buku Kerja; Pilih Acak ...
  • Salinan Tepat Beberapa Sel tanpa mengubah referensi rumus; Buat Referensi Otomatis ke Beberapa Lembar; Sisipkan Poin, Kotak Centang, dan lainnya ...
  • Ekstrak Teks, Tambahkan Teks, Hapus berdasarkan Posisi, Hapus Space; Membuat dan Mencetak Subtotal Paging; Konversi Konten Antar Sel dan Komentar...
  • Filter Super (simpan dan terapkan skema filter ke sheet lain); Penyortiran Lanjutan menurut bulan / minggu / hari, frekuensi dan lainnya; Filter Khusus dengan huruf tebal, miring ...
  • Gabungkan Workbooks dan WorkSheets; Gabungkan Tabel berdasarkan kolom kunci; Pisahkan Data menjadi Beberapa Lembar; Konversi Batch xls, xlsx dan PDF...
  • Lebih dari 300 fitur canggih. Mendukung Office / Excel 2007-2019 dan 365. Mendukung semua bahasa. Penerapan yang mudah di perusahaan atau organisasi Anda. Fitur lengkap uji coba gratis 30 hari. Jaminan uang kembali 60 hari.
tab kte 201905

Tab Office Membawa antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Jauh Lebih Mudah

  • Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
  • Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
  • Tingkatkan produktivitas Anda hingga 50%, dan kurangi ratusan klik mouse untuk Anda setiap hari!
officetab bawah
Urutkan komentar berdasarkan
komentar (28)
Belum ada peringkat. Jadilah yang pertama memberi peringkat!
Komentar ini diminimalkan oleh moderator di situs
Terima kasih banyak atas bantuan Anda. Ini sangat mudah dan sederhana untuk dibuat.
Komentar ini diminimalkan oleh moderator di situs
data saya digunakan oleh anggota kantor. jadi saya ingin melindungi kehilangan data dengan kata sandi. jika saya mencoba mengaktifkan bilah pencarian kata sandi juga tidak berfungsi. apa yang dapat saya lakukan untuk mengamankan data dan membuatnya dapat dicari
Komentar ini diminimalkan oleh moderator di situs
Selamat siang,
Harap format sel yang perlu Anda tentukan sebagai kotak pencarian sebagai Tidak Terkunci sebelum melindungi lembar kerja dengan kata sandi. Kemudian data Anda dilindungi tetapi dapat dicari.
Komentar ini diminimalkan oleh moderator di situs
cara menambahkan lembar kerja lain sebagai area untuk pencarian data
Komentar ini diminimalkan oleh moderator di situs
Selamat siang,
Untuk mencari data di lembar kerja lain (seperti Sheet5), harap ubah rumus dalam metode pemformatan bersyarat ke =(SEARCH(Sheet5!$B$2,A5)).
Maaf tidak dapat menyelesaikan masalah dengan metode kedua di atas.
Komentar ini diminimalkan oleh moderator di situs
Jika Anda memiliki data dari beberapa lembar, Anda mungkin ingin mengompilasi semua data itu ke dalam satu lembar agar tidak terlalu rumit. Misalnya, Sheet1, Sheet2 dan Sheet3 semuanya memiliki data yang ingin Anda "Telusuri", pada "Sheet4" Anda dapat di Kolom A pada Sheet4 mengatakan "=Jika(Sheet1!A1="","",Sheet1!A1) dan seret rumus itu turun berapa baris yang ingin Anda isi (misalkan 40 Baris ke bawah) Masih di Kolom A di Sheet4 Anda akan masuk di Sel A41 "=if(Sheet2!A1="","",Sheet2!A1) , dll dan seret ke bawah, ulangi sampai semuanya selesai. Kemudian gunakan rumus di atas dan mencari semua lembar.
Komentar ini diminimalkan oleh moderator di situs
bagaimana saya bisa menambahkan lembar kerja sebagai area pencarian lain?
Komentar ini diminimalkan oleh moderator di situs
Bagaimana jika data akan terus bertambah, data baru masuk setiap hari? Jadi saya ingin pencarian tidak hanya mencari informasi di kolom tertentu saat ini tetapi semua baris baru yang akan ditambahkan ke kolom tersebut nanti.
Komentar ini diminimalkan oleh moderator di situs
Hai alicia.
Maaf tidak bisa membantu dengan itu. Anda perlu memperbarui referensi rumus secara manual setelah baris baru ditambahkan ke kolom.
Terima kasih atas komentar anda
Komentar ini diminimalkan oleh moderator di situs
saya juga... Apakah ada bantuan yang bisa Anda tawarkan?
Komentar ini diminimalkan oleh moderator di situs
Anda dapat memilih rentang sampai ke bawah sehingga ketika Anda memasukkan data baru - ini akan mempertimbangkan data baru saat melakukan pencarian.

Misalnya: =$A$3:$U$1048576 (walaupun data yang saya masukkan sebenarnya sampai baris 74)
Komentar ini diminimalkan oleh moderator di situs
^ya cara lain adalah membuat tabel. 
Anda dapat memeriksa dari "Kelola aturan" > "Berlaku untuk" Aturan pemformatan bersyarat akan diperpanjang secara otomatis saat data baru ditambahkan ke tabel..
Komentar ini diminimalkan oleh moderator di situs
Setelah memasukkan rumus =ISNUMBER(SEARCH($B$1,A4)) untuk pemformatan bersyarat, jika sel yang saya gunakan untuk fungsi pencarian kosong, semua sel yang dicari (A4:C368) akan disorot. Tetapi begitu string dimasukkan untuk kriteria pencarian, sel-sel yang berisi kriteria pencarian disorot dengan benar. Apakah ada cara untuk mengubah rumus agar tidak menyorot sampai kriteria pencarian dimasukkan? Atau apakah saya melakukan sesuatu yang salah?

Juga, menggunakan rumus di langkah 5 di lembar lain dalam buku kerja tidak berfungsi. Apa yang saya coba lakukan adalah melakukan pencarian dan menunjukkan hasil pencarian itu pada satu lembar bernama Pencarian & Hasil sementara informasi yang akan dicari pada lembar bernama Indeks. Rumus yang saya gunakan adalah =IFERROR(VLOOKUP(A3,Index!$A$4:$C$368,5,FALSE),"") di mana A3 adalah awal dari sel bernomor saya di lembar Pencarian & Hasil dan pencarian berlangsung di seluruh sel A4:C368 pada lembar Indeks.
Komentar ini diminimalkan oleh moderator di situs
Saya memiliki masalah yang sama. Apakah Anda mendapatkan jawaban?
Komentar ini diminimalkan oleh moderator di situs
saya juga. saya memiliki masalah yang sama
Komentar ini diminimalkan oleh moderator di situs
Hai Colby. Rumus vlookup Anda=IFERROR(VLOOKUP(A3,Index!$A$4:$C$368,5,FALSE),"") mengacu pada rentang dari A4 hingga C368 dalam indeks nama Lembar yang hanya memiliki 3 kolom tetapi Anda memasukkan 5 dengan benar setelah $C$368 yang memberi tahu rumus vlookup untuk menampilkan nilai kolom 5 yang tidak ada dalam rentang yang Anda pilih. Perbaiki ini dan saya yakin masalah Anda akan terpecahkan.
Komentar ini diminimalkan oleh moderator di situs
sama telah mengalami masalah yang sama dengan rumus, coba ini di aturan pemformatan bersyarat Anda "=AND($I$1<>"",ISNUMBER(SEARCH($I$1,$B4)))", ini berfungsi untuk saya
I1 adalah kotak pencarian saya, B4 adalah sel pertama dari rentang yang dipilih
Komentar ini diminimalkan oleh moderator di situs
saya mengikuti metode pencarian yang dihasilkan dan itu bekerja dengan sempurna namun hasilnya hyperlink dan itu menunjukkan kepada saya hasil tanpa hyperlink apakah ada cara saya bisa membuatnya menunjukkan hasil dengan koneksi tautan?
Komentar ini diminimalkan oleh moderator di situs
Halo, saya menggunakan untuk mencari antara vendor saya (satu kolom) dan merek (kolom lain). Dengan pengaturan ini, ada merek (seperti Microsoft) yang dapat memiliki lebih banyak vendor (vendor A, vendor B, vendor C,...). Saya ingin mencari nama merek (microsoft) dan ingin melihat semua kemungkinan vendor (A, B & C juga). Tapi sekarang hasilnya hanya vendor pertama dan itu saja. Bagaimana saya bisa mengubah/memperbaikinya?

Banyak terima kasih!
Komentar ini diminimalkan oleh moderator di situs
Hai Tomas,
Mungkin Anda dapat mengatur ulang data Anda dan membuat daftar tarik-turun dinamis untuk menyelesaikan masalah. Anda dapat menelusuri artikel di bawah ini untuk lebih jelasnya.
https://www.extendoffice.com/documents/excel/1350-excel-create-dynamic-drop-down-list.html
Komentar ini diminimalkan oleh moderator di situs
Hai! Apakah ini berlaku untuk Google Spreadsheet? Saya mencoba membuat kotak pencarian dengan rumus untuk mencantumkan semua hasil pencarian. Saya mengikuti setiap langkah yang tercantum di atas tetapi hasilnya tidak menunjukkan apa-apa. Ada kesalahan, katanya "ubah perhitungan Iteratif". Tapi apa pun yang saya lakukan, itu tidak menunjukkan apa-apa - hanya kosong. Bisakah Anda membantu saya dengan ini, tolong... Terima kasih!
Komentar ini diminimalkan oleh moderator di situs
Hai! Apakah ini berlaku untuk Google Spreadsheet? Saya mencoba membuat kotak pencarian dengan rumus untuk mencantumkan semua hasil pencarian. Saya mengikuti setiap langkah yang tercantum di atas tetapi hasilnya tidak menunjukkan apa-apa. Ada kesalahan, katanya "ubah perhitungan Iteratif". Tapi apa pun yang saya lakukan, itu tidak menunjukkan apa-apa - hanya kosong. Bisakah Anda membantu saya dengan ini, tolong... Terima kasih!
Komentar ini diminimalkan oleh moderator di situs
Hari baik,
Ini hanya berlaku untuk aplikasi Microsoft Excel. Maaf untuk ketidaknyamanannya.
Komentar ini diminimalkan oleh moderator di situs
hai! Saya menggunakan bilah pencarian sorotan dasar, tetapi saya mengalami beberapa masalah. itu memprediksi pencarian saya dan menemukannya tanpa masalah ... namun, itu selalu menyoroti sel beberapa di bawah atau di atas yang dicari. Apakah Anda dapat membantu saya dengan ini?
Komentar ini diminimalkan oleh moderator di situs
Bisakah Anda membuat formula yang menangkap informasi senilai dua sel dalam retrospeksi saya menggunakan pengaturan yang menangkap nama pengguna dan data lencana jadi saya membutuhkannya ketika memfilter yang membawa kedua sel informasi bukan hanya satu
Komentar ini diminimalkan oleh moderator di situs
saya telah mencoba menggunakan ini tetapi dosisnya tidak biasa untuk menyorot kotak yang saya cari mengapa ini
Komentar ini diminimalkan oleh moderator di situs
Hai, untuk bagian kedua: "Buat Kotak Pencarian Anda Sendiri Dengan Rumus Untuk Mencantumkan Semua Hasil Pencarian", tidak disebutkan apa yang harus dilakukan dengan kotak pencarian, kotak pencarian saya tidak memiliki rumus di dalamnya. Saya mencoba melakukannya dengan banyak kolom juga, apakah itu berfungsi juga?
Komentar ini diminimalkan oleh moderator di situs
me pueden apoyar en cual es la formula para solo buscar
porfa
Belum ada komentar yang diposting di sini
Tinggalkan komentar anda
Posting sebagai Tamu
×
Beri peringkat pos ini:
0   Karakter
Lokasi yang Disarankan