4 Langkah Cara Membuat Mesin Pencari di Excel, Sederhana tapi Powerfull

Membuat Mesin Pencari di Excel – Dalam dunia kerja adakalanya kita dapat tugas untuk mencari sebuah data spesifik dari banyaknya data yang ada. Jika kita mencari secara manual tentu itu akan seperti mencari jarum dalam tumpukan jerami. Sementara Bos biasanya ingin yang serba cepat. kalau mencari secara manual, alih-alih datanya ketemu kita bisa dapat semprot duluan.

Untuk itu pada artikel kali ini kita akan coba membuat sebuah mesin pencari sederhana menggunakan rumus Excel yang bisa dengan cepat menemukan data yang kita cari hanya dengan mengetikkan beberapa huruf depan dari data yang kita cari. Dan karena kita akan membuat sebuah mesin pencari yang diusahakan menggunakan rumus Excel yang tidak terlalu panjang maka disini disamping tabel inti, kita juga akan menggunakan kolom bantu yang akan mempermudah rumus Excel berikutnya.

Kolom bantu ini akan kita buat menggunakan array formula yang ditulis sekaligus di banyak kolom kombinasi dari fungsi SMALL, LOWER dan ROW. Kemudian untuk menampilkan data yang kita cari akan digunakan kombinasi dari fungsi ISNUMBER dan OFFSET.

Langkah 1. Menyiapkan Data

Sebelum melangkah lebih jauh, tentu hal pertama yang harus kita siapkan adalah tabel datanya. Di sini kita akan membuat contoh berupa dua tabel yaitu tabel pertama untuk data yang akan kita cari sementara tabel kedua adalah untuk menampilkan hasil dari data yang kita cari.

Dan untuk mempermudah pemahaman penggunaan rumus Excel, kita akan buat tabel sederhana yang nantinya bisa teman-teman kembangkan lagi sesuai kebutuhan. Dan berikut adalah contoh tabel awalnya:

Langkah 2. Membuat Kolom Bantu

Langkah berikutnya adalah mengisi rumus di kolom bantu. Nanti setelah selesai dibuat, untuk kolom bantu ini bisa teman-teman hide kolomnya agar tak kelihatan atau bisa juga dengan menyamarkan warna font-nya sesuai warna latar (misalnya putih).

Dan seperti yang saya katakan di awal bahwa untuk kolom bantu ini kita akan membuatnya menggunakan formula array yang ditulis sekaligus di banyak kolom. Jadi langkah penulisan rumusnya adalah: blok dulu range G5:G34 baru kemudian di formula bar ketik rumus Excel berikut:

=SMALL(IF(LOWER(LEFT(C5:C34;LEN(J2)))=LOWER(J2);ROW(1:30));ROW(1:30))

Jika sudah, karena kita akan membuatnya menjadi array formula maka akhiri penulisan rumusnya dengan menekan Ctrl + Shift dan Enter (jangan hanya Enter). kalau langkahnya benar maka hasilnya adalah munculnya simbol kurung kurawal yang mengapit rumus secara otomatis. Berikut adalah gambarnya:

Penjelasan Rumus

Formula array Excel ini adalah rumus untuk mencari nilai terkecil dari sebuah kumpulan data berdasarkan kriteria tertentu. Mari kita jelaskan setiap bagian dari rumus ini secara detail:

1. LOWER(LEFT(C5:C34, LEN(J2))): Ini adalah fungsi untuk mengubah teks menjadi huruf kecil dan kemudian mengambil sejumlah karakter dari sebelah kiri dari sel-sel C5 hingga C34 sepanjang panjang teks yang ada di sel J2. Misalnya, jika J2 berisi kata “kucing”, maka rumus ini akan mengubah teks di sel C5 hingga C34 menjadi huruf kecil dan mengambil sebagian awal dari teks yang sama panjang dengan “kucing”.
2. LOWER(J2): Ini adalah bagian yang sama dengan di atas, tetapi hanya untuk sel J2. Tujuannya adalah untuk membandingkan teks yang sama dengan kriteria pencarian.
3. IF(LOWER(LEFT(C5:C34, LEN(J2)))=LOWER(J2), ROW(1:30)): Ini adalah bagian utama dari rumus yang menggunakan fungsi IF. Ini memeriksa apakah teks yang diperoleh dari sel C5 hingga C34 sama dengan teks yang ada di sel J2 (dalam huruf kecil). Jika sama, maka fungsi ROW(1:30) akan memberikan baris dari 1 hingga 30, sesuai dengan baris data yang dicek.
4. SMALL(IF(…), ROW(1:30)): Ini adalah fungsi SMALL yang digunakan untuk mencari nilai terkecil dari baris-baris yang memenuhi kriteria yang diberikan dalam fungsi IF di atas. Bagian ROW(1:30) di sini memberikan urutan ke-n dari nilai terkecil yang ingin dicari. Dalam konteks ini, rumus mencari nilai terkecil pertama, kedua, ketiga, dan seterusnya yang memenuhi kriteria pencarian.
5. {=…}: Tanda kurung kurawal di awal dan akhir rumus menunjukkan bahwa ini adalah rumus array, yang berarti rumus ini akan beroperasi pada seluruh rentang data yang diberikan (dalam hal ini, C5 hingga C34).

Jadi, secara keseluruhan, rumus ini akan memberikan nilai terkecil dari baris-baris di sel C5 hingga C34 yang memiliki teks awal yang sama dengan teks yang ada di sel J2 (dalam huruf kecil). Misalnya, jika J2 berisi “kucing” dan ada beberapa teks di sel C5 hingga C34 yang diawali dengan “kucing” (seperti “kucing hitam”, “kucing putih”, dst.), maka rumus mesin pencari Excel ini akan memberikan nomor baris dari data-data tersebut yang memiliki nilai terkecil.

Langkah 3. Membuat Border dengan Conditional Formatting

Agar data yang kita cari tertampil dengan rapi ada baiknya kita menggunakan Conditional Formatting untuk memunculkan garis atau border tabel secara otomatis mengikuti jumlah data yang ada. Caranya sangat mudah. Yang perlu teman-teman lakukan hanyalah blok range I5:L34 kemudian di tab Home pilih Conditional Formatting. Pilih New Rule… kemudian pilih Use a Formula to determine which cells to format dan isi kolom Format values where this formula is true: dengan rumus berikut:

=LEN(I5)>0

Kalau sudah, pilih Format… dan pilih Border. Set bordernya seperti pada gambar di bawah ini:

Teman-teman juga bisa menambahkan warna jika mau. Pilih Fill, kemudian pilih warna yang diinginkan dan OK.

Kalau setelah klik OK tidak terjadi apa-apa biarkan saja. Itu karena di range I5:L34 belum ada data yang tampil. Nanti begitu ada datanya maka akan otomatis tampil juga border dan warnanya. Seperti pada contoh di bawah ini:

Langkah 4. Menampilkan Hasil Pencarian

Langkah terakhir adalah menampilkan hasil pencarian sesuai huruf yang diketikkan di sel J2. Misalkan jika di sel J2 kita mengetik “MO” maka akan muncul data atas nama Mohamad Akbar Maulana dan Mohamad Ikhsan. Di sini fungsi yang akan kita gunakan di sel I5 untuk membuat mesin pencari di Excel adalah kombinasi dari fungsi IF, ISNUMBER dan OFFSET. Berikut adalah rumus Excel untuk sel I5:

=IF(ISNUMBER($G5);OFFSET(B$4;$G5;0);"")

Seret rumus di atas ke sel-sel yang ada di samping dan juga di bawahnya (range I5:L34). Jika benar maka hasilnya akan sebagai berikut:

Penjelasan Rumus

Rumus Excel ini adalah rumus yang cukup sederhana tetapi memiliki beberapa fungsi penting. Mari kita bahas setiap bagian dari rumus tersebut terkait dengan membuat mesin pencari di Excel:

1. ISNUMBER($G5): Ini adalah fungsi ISNUMBER yang memeriksa apakah nilai di sel G5 adalah angka atau bukan. Jika nilai di sel G5 adalah angka, fungsi ini akan mengembalikan TRUE; jika tidak, akan mengembalikan FALSE.
2. OFFSET(B$4, $G5, 0): Ini adalah fungsi OFFSET yang digunakan untuk mengambil nilai dari sel yang berada di lokasi relatif terhadap sel B$4 (yaitu, sel di baris ke-$G5 dan kolom ke-0 dari B$4). Jika nilai di sel G5 adalah angka, maka fungsi OFFSET akan mengambil nilai dari sel yang ditunjukkan oleh offset ini; jika tidak, akan mengembalikan nilai kosong (“”).
3. IF(ISNUMBER($G5), OFFSET(B$4, $G5, 0), “”): Ini adalah fungsi IF yang digunakan untuk menguji kondisi. Jika nilai di sel G5 adalah angka (yaitu, ISNUMBER($G5) mengembalikan TRUE), maka rumus akan mengeksekusi fungsi OFFSET untuk mengambil nilai dari sel yang sesuai dengan offset yang dijelaskan di atas. Jika nilai di sel G5 bukan angka (ISNUMBER($G5)) mengembalikan FALSE), maka rumus akan mengembalikan nilai kosong (“”).

Jadi, secara keseluruhan, rumus ini berfungsi sebagai berikut:

• Jika nilai di sel G5 adalah angka, maka rumus akan mengambil nilai dari sel yang sesuai dengan offset yang dijelaskan di OFFSET(B$4, $G5, 0).
• Jika nilai di sel G5 bukan angka (misalnya, teks atau sel kosong), maka rumus akan mengembalikan nilai kosong (“”).

Rumus ini sering digunakan untuk mengambil nilai dari sel tertentu berdasarkan nilai atau kondisi yang ada di sel lain.

Dan jika kita coba ketikkan huruf tertentu di kolom pencarian (sel J2) maka akan muncul data yang terkait dengan huruf tersebut. Contohnya adalah sebagai berikut:

Kesimpulan Membuat Mesin Pencari di Excel

Rumus-rumus di Microsoft Excel jika dioptimalkan maka bukan saja akan membuat kita lebih cepat dalam bekerja tapi juga akurat dan efektif. Dan begitu pula ketika diigunakan untuk membuat mesin pencari di Excel yang bisa digunakan sebagai alat untuk mencari data spesifik seperti pada contoh di atas. Kalau pada contoh data yang dimiliki hanya sedikit maka bisa jadi di dunia kerja data yang tersedia begitu banyaknya hingga ketika kita mengandalkan kerja manual akan membuat frustrasi. Untuk itu, dengan menggunakan formula Excel yang tepat, pekerjaan kita bisa lebih cepat selesai.

Pranala Luar

File Excel Cara Membuat Mesin Pencari di Excel ini bisa Anda download jika memang memerlukannya dengan cara klik tautan di bawah ini:

Atau juga jika ingin mempelajari langkah demi langkahnya secara visual, bisa kunjungi Youtube Channel Depot Excel dengan judul: Rahasia Membuat Mesin Pencari dengan Excel untuk Pencarian Data Lebih Cepat. Di sana Anda akan melihat bagaimana rumusnya bekerja. Semoga bermanfaat!