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 8Baris 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
Arahkan kursor ke sel D3 dan gunakan rumus search
=SEARCH($G$2,C3)guanakan rumus search pada semua kolom seperti pada gambar 3
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
Rumus Vlookup untuk menampilkan data
Gunakan rumus vlookup pada sel G3 hingga G14, lihat Gambar 5
=VLOOKUP(F3,$B$3:$D$14,2,FALSE)
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
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
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),"")
dan hasilnya pada kolom RANK telah terisi angka dan search telah tampil, lihat gambar 11
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
masalah ini terjadi diakibatkan oleh kolom rank yang adalah kolom yang dicari oleh VLOOKUP memiliki nilai yang sama, yaitu 1, lihat gambar 13
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.
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),"")
atau bisa juga hide saja kolom RANK dan SEARCH seperti pada gamar 17
Terimakasih telah membaca dan selamat berkreasi