Artikel ini mengilustrasikan cara membuat kotak pencarian di excel untuk beberapa lembar. Asumsikan Anda memiliki 20 lembar di buku kerja Anda. Sekarang Anda perlu mencari data di semua sheet yang relevan dengan kata kunci. Akan sangat memakan waktu dan melelahkan untuk melakukan ini secara manual. Untungnya, Anda dapat membuat kotak pencarian untuk buku kerja Anda untuk mencari di semua lembar. Ikuti artikel untuk mempelajari cara melakukannya
Daftar isi
Download Buku Latihan
Anda dapat mengunduh buku latihan dari tombol unduh di bawah ini
Kotak Pencarian untuk Beberapa Lembar. xlsm
2 Cara Membuat Kotak Pencarian di Excel untuk Beberapa Lembar
Asumsikan Anda memiliki data sebagai berikut dalam beberapa lembar. Anda perlu mencari berdasarkan nama untuk menemukan dengan cepat semua hasil yang relevan di lembar tersebut
Ikuti cara-cara di bawah ini untuk bisa melakukannya
1. Cari Beberapa Lembar di Excel dengan Pemformatan Bersyarat
Ikuti langkah-langkah di bawah ini untuk menerapkan pemformatan bersyarat untuk mencari dengan cepat melalui beberapa lembar di excel
📌 Langkah
- Pertama, pilih rentang B3. F21 di Lembar2. Kemudian, pilih Pemformatan Bersyarat >> Aturan Baru dari tab Beranda
- Selanjutnya, pilih 'Gunakan rumus untuk menentukan sel mana yang akan diformat' sebagai jenis aturan. Setelah itu, masukkan rumus berikut di kotak teks di bawah untuk Deskripsi Aturan
=SEARCH(Sheet1!$B$4,$B3&$C3&$D3&$E3&$F3)_
- Selanjutnya, klik Format dan pilih warna Isi untuk menyorot sel. Lalu klik Oke
- Setelah itu, terapkan pemformatan yang sama ke rentang data di lembar lain dengan mengikuti prosedur yang sama
- Sekarang buka Sheet1 dan masukkan kata kunci di sel B4. Lebih baik menggunakan wildcard (*, ?, dll. ) di kotak telusur jika Anda tidak memerlukan pencocokan persis
- Lalu pergi ke Sheet2 untuk melihat hasil berikut. Anda dapat melihat hasil yang relevan disorot
- Selanjutnya, buka Sheet3 untuk melihat hasil yang serupa sebagai berikut
2. Buat Kotak Pencarian di Excel untuk Beberapa Lembar dengan VBA
Sekarang Anda mungkin berpikir untuk mendapatkan data yang disorot itu di lembar yang sama i. e. Sheet1 tempat Anda mencari kata kunci. Ikuti langkah-langkah di bawah ini untuk dapat melakukannya dengan VBA
📌 Langkah
- Pertama, buat salinan kumpulan data Anda, lalu tekan ALT+F11 untuk membuka jendela VBA. Kemudian pilih Sisipkan >> Modul
- Selanjutnya, salin kode berikut
- Setelah itu, tempel kode ke modul
- Sekarang, pindahkan data di setiap lembar ke sel A1 jika memungkinkan, lalu simpan file sebagai buku kerja yang mendukung makro. Ini karena VBA mungkin bertindak sedikit aneh jika ada kolom kosong di awal kumpulan data. Anda dapat melewati langkah ini dan melihat apakah itu berhasil
- Selanjutnya, kembali ke Sheet1 lalu sisipkan persegi panjang dengan memilih Sisipkan >> Ilustrasi >> Bentuk >> Persegi Panjang. Setelah itu ubah warna Fill dan ketik Search agar terlihat sesuai kebutuhan
- Sekarang, klik kanan pada kotak persegi panjang untuk memilih 'Tetapkan makro'. Kemudian, pilih nama makro dan klik OK
- Selanjutnya, ketikkan kata kunci pencarian di sel A4 lalu klik tombol Cari. Setelah itu, Anda akan melihat semua hasil relevan yang disorot dalam metode sebelumnya yang tercantum di bawah ini
Penjelasan Kode VBA
Opsi Eksplisit
Ini memaksa Anda untuk mendeklarasikan semua variabel
Sub SearchMultipleSheets()
Kami akan menulis kode di dalam prosedur subjek ini
Dim wsh Sebagai Lembar Kerja, abu Sebagai Lembar Kerja
Redup semua, dst As Range
Redup Apa Sebagai Varian
Mendeklarasikan variabel yang diperlukan
Tetapkan abu = Lembar Aktif
Tetapkan dst = abu. Rentang ("A6")
Wht = abu. Rentang ("A4"). Nilai
Mendefinisikan variabel
Aplikasi. Pembaruan Layar = Salah
Ini membuat VBA berjalan di latar belakang agar bekerja lebih cepat
dst. Wilayah Saat Ini. Jernih
Menghapus apa pun yang tersisa di wilayah keluaran
Untuk Setiap wsh Di Lembar Kerja
Ulangi setiap lembar
Jika wsh. Nama = abu. Beri Nama Kemudian Buka Lembar Berikutnya
Melewatkan lembar aktif
Atur semua = SearchAll(wsh. Kolom ("A"), Apa, Lihat. =xlSeluruh)
Panggil fungsi SearchAll yang ditentukan di bawah ini
Jika semua Tidak Ada Lalu Buka Lembar Berikutnya
Pergi ke lembar berikutnya jika tidak ada yang ditemukan di lembar saat ini
semua. Seluruh baris. Salin dst
Menyalin seluruh baris sel yang cocok
Tetapkan dst = abu. Rentang ("A" & Baris. Menghitung). Akhir(xlUp). Offset(1)
Menetapkan kolom A sebagai rentang keluaran
Lembar Berikutnya
Pernyataan GoTo mengarahkan VBA untuk dijalankan dari sini
Berikutnya
Pergi ke lembar berikutnya untuk mengulang tugas
Aplikasi. Pembaruan Layar = Benar
Menyetel pembaruan layar ke defaultnya
Akhir Sub
Prosedurnya berakhir di sini
Fungsi SearchAll(ByVal Where As Range, ByVal What, _
Opsional ByVal Setelah Sebagai Varian, _
Opsional ByVal LookIn As XlFindLookIn = xlValues, _
Opsional ByVal LookAt As XlLookAt = xlWhole, _
Opsional ByVal SearchOrder As XlSearchOrder = xlByRows, _
Opsional ByVal SearchDirection As XlSearchDirection = xlNext, _
MatchCase ByVal Opsional Sebagai Boolean = Salah, _
Opsional ByVal SearchFormat As Boolean = False) As Range
Fungsi ini disebut dengan subprosedur
Redupkan Alamat_Pertama Sebagai String
Dim R Sebagai Kisaran
Dim Stck Sebagai Koleksi Baru
Dim Tmp() Sebagai Rentang, Item
Redup m, n Selama
Mendeklarasikan variabel untuk fungsi
Jika Di Mana Tidak Ada Lalu Keluar dari Fungsi
Fungsi berakhir tanpa mengembalikan apapun jika kotak pencarian kosong
Baca Selengkapnya. Cara Membuat Kotak Pencarian di Excel Tanpa VBA (2 Cara Mudah)
Hal-hal untuk diingat
- Anda harus menerapkan pemformatan bersyarat ke data di semua sheet. Dan jangan lupa untuk mengetikkan kata kunci di sel yang tepat yang dirujuk dalam pemformatan bersyarat
- Jangan lupa untuk mencari dengan wildcard untuk mendapatkan semua kemungkinan hasil pencocokan jika Anda tidak memerlukan pencocokan persis
- Anda harus membuat salinan kumpulan data Anda sebelum menerapkan kode. Jika tidak, Anda mungkin kehilangan data Anda
- Anda dapat menyeret tombol Cari untuk memindahkannya sesuai kebutuhan
Kesimpulan
Sekarang Anda tahu cara membuat kotak pencarian untuk beberapa lembar di excel. Apakah Anda dapat menerapkan metode tersebut ke kumpulan data Anda? . Anda juga dapat mengunjungi blog ExcelDemy kami untuk mempelajari lebih lanjut tentang excel. Tetap bersama kami dan terus belajar