Excel – Search box kombinasi fungsi Search, Rank dan vlookup

Cari kata di excel sebenarnya sangat mudah cukup dengan menekan kombinasi tombol ctrl+F isi kata yang ingin dicari pada form yang disediakan dan tekan Find All, semua sell yang mengandung kata tersebut akan ditampilkan. Selesai.

Contoh diatas adalah cara gampangnya dan yang akan dibahas pada tulisan ini adalah pencarian yang memanfaatkan fungsi search , rank dan vlookup . Sebelum lebih jauh lagi baiknya ketiga fungsi ini harus dimengerti cara penggunaannya,

Fungsi search()

Apa yang dilakukan oleh fungsi search? Fungsi search pada excel bukan menapilkan karakter dari sel yang dicari ke sel yang dituju layaknya fungsi copy, melainkan fungsi search akan mengembalikan nomor posisi karakter yang di cari,

sintaks

=SEARCH(find_text,within_text,[start_num])

dimana

find_text adalah teks yang ingin anda temukan posisinya

within_text adalah text yang akan di cari posisinya oleh find_text

start_num adalah argumen opsional yang berfungsi menentukan posisi awal pencarian.

contoh

=SEARCH(“ada”,“kepada”)

pada contoh tersebut akan mengembalikan nilai 4 , karena kata “ada” berada posisi ke 4 kata “kepada”.

Lanjut ke fungsi Rank

Fungsi Rank()

Rank! Yah, ranking jadi tidak usah dijelaskan lebih panjang lagi, lanjut ke sintaxnya

=RANK(number,ref,[order])

dimana

number adalah nomor atau angka yang akan dirankingkan

ref adalah referensi berupa larik bilangan atau daftar angka termasuk null(kosong), bila larik tersebut adalah bukan nomor sel tersebut akan mengembalikan nilai kesalahan

order adalah argumen yang digunakan untuk mengurutkan hasil ranking

contoh

terdapat baris data

A1 berisi nilai 7
A2 berisi nilai 6
A3 berisi nilai 8

Baris data tersebut di-RANK-kan, seperti pada contoh berikut

=RANK(6,A1:A3,1)

dan hasil yang diperoleh adalah 1 , karena angka 6 adalah nilai terkecil dari larik bilangan 7,6,8

bila rumus yang dipakai adalah

=RANK(6,A1:A3,0)

maka hasil yang diperoleh adalah 3 , bila diurutkansecara descending maka angka 6 berada pada urutan ke 3

Bila argumen nilai order 1 maka urutan rank nya dimulai dari terkecil hingga yang paling besar dan bila nilai argumen order adalah 0 maka urutannya akan sebaliknya.

fungsi vlookup

lookup digunakan untuk menemukan dan menampilkan data dari sel berdasarkan rentan larik yang dipilih

sintaks

=VLOOKUP(lookup_value,table_array, col_index_num,[range_lookup])

dimana

lookup_value berisi nilai yang akan dicari dalam rentang larik, pencarian dilakukan berdasrkan kolom paling kiri.

table_array adalah data larik dimana kolom pertama adalah kolom yang digunakan untuk nilai lookup_value nya

col_index_num adalah nomor kolom dalam larik dimulai dari angka 1 pada kolom paling kiri, data pada col_index_num akan ditampilkan pada sel yang disematkan rumus vlookup

range_lookup adalah opsional yang berisi nilai TRUE jika mengharapkan atau sel akan mengembalikan nilai yang diperkirakan cocok atau FALSE jika menginginkan nilai yang sama persis.

Fungsi pentingnya sudah selesai dipaparkan.

kombinasikan Fungsi Search, Rank dan vlookup untuk membuat search box

Skenarionya adalah jika sel cari di isi maka data yang relevan dengan data yang ada pada baris data akan ditampilkan pada sel dibawahnya. Bagaimana caranya? Ikuti langkah berikut.

gunakan rumus search

Buat tabel pada sheet sebagai contoh buatlah data bulan seperti pada gambar 1

Gambar 1. Tampilan yang dibuat sebelum dimasukkan rumus

Arahkan kursor ke sel D3 dan gunakan rumus search

Gambar 2. Menggunakan rumus SEARCH
=SEARCH($G$2,C3)

guanakan rumus search pada semua kolom seperti pada gambar 3

Gambar 3. Rumus SEARCH

Gunakan Rumus Rank

Rumus Rank digunakan pada kolom awal yaitu sel B3 hingga B14, kenapa harus kolom awal? Karena komom ini akan digunakan untuk meng-index vlookup sel.

Lihat penggunaan rumusnya pada gambar 4

Gambar 4. Rumus RANK

Rumus Vlookup untuk menampilkan data

Gunakan rumus vlookup pada sel G3 hingga G14, lihat Gambar 5

=VLOOKUP(F3,$B$3:$D$14,2,FALSE)
Gambar 5. Rumus VLOOKUP

dengan menggunakan ketiga rumus tersebut seharusnya telah menghasilkan search box namun masih terdapat kesalahan, tahap berikutnya penanganan kesalahan.

Penanganan Kesalahan

Hilangkan #N/A

Setelah melihat hasilnya ternya pada kolom untuk menampilkan hasil pencarian masih terdapat kesalahan data pada sel cari kosong namun kolom hasil pencarian sudah terisi dan terdapat sel berisi nilai #N/A

pada gambar 6 sel G4 hingga G14 menghasilkan #N/A, bagaimana cara menghilangkannya? Gunakan rumus IFNA .

Singkatnya yang dikerjakan IFNA adalah mengembalikan nilai bila TRUE dan jika menghasilkan #N/A akan mengembalikan nilai yang ditentukan.

rumus

=IFNA(VLOOKUP(F3,$B$3:$D$14,2,FALSE),"")

gunakan rumus ini pada kolom F3 hingga F14 , lihat gambar 7

Gambar 7. Rumus IFNA

Data tidak tampil

Gambar 8. Data tidak tampil

Pada gambar 8 pada sel search telah diisi teks namun hasilnya tidak ada, kesalahan tersebut disebabkan oleh vlookup tidak menemukan lookup_value pada index_pertama/kolom pertama yaitu RANK. RANK sendiri akan mengembalikan nilai kesalahan jika referensinya bukan angka atau null(kosong), berarti kesalah terjadi pada larik angka yang digunakan sebagai referensi oleh RANK yaitu sel D3 hingga D14 , lihat gambar 9

Gambar 9. value search error

pemecahan masalahnya adalah dengan menggunakan rumus IFERROR , sama seperti IFNA , IFERROR juga akan mengembalikan nilai yang telah ditentukan bila hasil rumus yang digunakan adalah ERROR,

lihat gambar 10, bagaimana menggunakan rumus IFERROR

=IFERROR(SEARCH($G$2,C3),"")
Gambar 10. Sisipkan rumus IFERROR pada kolom search

dan hasilnya pada kolom RANK telah terisi angka dan search telah tampil, lihat gambar 11

Gambar 11. Kolom Cari telah terisi

Menampilkan semua pencarian yang ditemukan

Masalah berikutnya adalah harapannya jika menulis sepenggal kalimat/kata atau bahkan 1 huruf pada hasil pencarian akan memuat semua data yang memuat sepenggal kata tersebut, sebagai contoh bila mengisi huruf "J" pada kolom cari maka hasil yang ditampilkan adalah JANUARI, JUNI dan JULI namum yang tampil cuma 1 sel saja, lihat gambar 12

Gambar 12. data tidak sesuau harapan

masalah ini terjadi diakibatkan oleh kolom rank yang adalah kolom yang dicari oleh VLOOKUP memiliki nilai yang sama, yaitu 1, lihat gambar 13

Gambar 13. nilai rank sama

Gunakan rumus bantuan untuk mengurutkan perangkingan, modifikasi sel search dengan menambahkan rumus ROW() , lihat rumus dan gambar 14.

=IFERROR(SEARCH($G$2,C3)+ROW()/10,"")

Cara kerja rumus ROW() adalah mengembalikana nomor baris sel yang terpilih sebagai contoh jika menerapkan rumus ROW pada sel A1 maka akan mengembalikan nilai 1, dimana nilai 1 tersebut adalah baris 1.

Skenario rumus ROW yang digunakan adalah menambah nilai SEARCH yang diperoleh dengan ROW()/10 . Sehingga menghasilkan nilai yang berbeda di tiap barisnya.

dan hasil yang diperoleh adalah seperi pada gambar 15, Apabila sel cari diisi huruf “J” maka pencerian dalam larik yang memiliki huruf “J” akan ditampilkan.

Gambar 15. Hasil penjumlahan nilai search dan row() / 10

Sebenarnya sampai tahap ini search box sudah jadi, tapi pada kolom baris masih terdapat nilai yang error, untuk memperbaikinya cukup tambahkan rumus IFERROR, lihat rumus dan gambar 16

=IFERROR(RANK(D3,$D$3:$D$14,1),"")
Gambar 16. Rumus IFERROR Pada rank

atau bisa juga hide saja kolom RANK dan SEARCH seperti pada gamar 17

Gambar 17. kolom RANK dan SEARCH di hide

Terimakasih telah membaca dan selamat berkreasi

Tinggalkan komentar