Excel Search Engine

:mrgreen: sungguh judul yang boombastis … ndak mutu blas … maksute piye kuwi :? … masak excel mau nyaingi google :roll:

begini maksutnya sOdara2 sekaliyan … kadang2 kita pengen mencari data ini dan itu yang ada dalam tabel … tabel ini ceritanya punya banyak baris dan kolom … biasanya sih saya pakE ctrl+F lalu form pencarian pun akan nongol dan memberikan bantuwannya untuk mencarikan data2 yang dicari … gampangkan :roll: naaaaaaaghhhh karena pencarian data dalam excel sangatlah mudah … bagaimana kalO kita persulit saja :smile: … yang ndak setuju silahkan brenti mbaca artikel ini sampE disini :smile: … yang setuju monggo lanjooot

sebagE contoh liyat penampakan tabel sederhana berikOt

tabel terdiri 4 kolom yaitu : Nomor Urut, Judul Lagu, Nama Penyanyi dan Tahun …PERHATIAN : data2 yang saya tampilkan dalam tabel ini boleh jadi amat sangat tidak akurat :smile:jadi jangan dijadikan bahan referensi

sekarang kita buWat sheet pencarian datanya … yang penampakannya kayak berikOt

bagEmana cara mbikin sheet pencarian data ini :?: … berikoot langkah2nya

1. bikin validasi Cell E5 …

pada cell ini dapat dipilih kolom mana yang akan dicari datanya … sebelOm bikin validasinya bikin dulu named range JudulKolom yang merujuk pada range =Tabel!$B$1:$D$1 … selanjootnya bikin validasi dengan type list yang merujuk pada range JudulKolom

lanjoot lagi … pada cell C9 kita masukkan formula yang akan mencari kolom keberapa yang pilih untuk dicari datanya dengan formula

=MATCH(E5;JudulKolom)

2. bikin ScrollBar

 caranya ada disini … atur CellLink ke C10 … cell C11 isi dengan formula =C10+1 lalu copykan sampe C19

3. bikin Formula untuk mencari jumlah data

pada Cell G5 masukkan formula berikOt:

=COUNT(Tabel!A:A)

4. bikin formula untuk mencari jumlah data yang cocok dengan keyword 

pada cell G7 saya kombinasikan beberapa fungsi sehingga membentuk formula berikot

{=COUNT(IF(ISERROR(SEARCH($E$7;OFFSET(Tabel!$A$1;1;$C$9;SearchEngine!$G$5;1);1));”A”;1))}

karena formula ini merupakan formula array untuk eksekusinya harus dengan menekan bersamaan tombol Ctrl + Shift + Enter … ingatEnter aja tidak tjukup sOdara2 !!!!

5. menampilkan data2 yang cocok dengan keyword

data2 tersebOt akan ditampilkan pada range D10:G19 … yang pertama pada kolom D akan menampilkan No Urut data yang tjotjok dengan keyword … pada cell D10 masukkan formula berikOt :

{=IF(C10>$G$7;””;SMALL(IF(ISERROR(SEARCH($E$7;OFFSET(Tabel!$A$1;1;$C$9;SearchEngine!$G$5;1);1));””;ROW(OFFSET(Tabel!$A$1;1;$C$9;SearchEngine!$G$5;1)));C10)-1)}

jangan lupa ini juga formula array jadi harus pakai Ctrl + Shift + Enter

bingung … bingung …. bingung dengan formulanya :? :roll:jalan ceritanya gimana sehingga didapat formula tersebut :idea:

pertama kita cari data yang cocok dengan keyword pada kolom yang dipilih … pake fungsi SEARCHyang dikombinasikan dengan OFFSET

=SEARCH($E$7;OFFSET(Tabel!$A$1;1;$C$9;SearchEngine!$G$5;1);1)

jika keyword cocok dengan suatu data maka cari ROW dari data tersebut … tambahkan fungsi IF, ISERROR dan ROW

=IF(ISERROR(SEARCH($E$7;OFFSET(Tabel!$A$1;1;$C$9;SearchEngine!$G$5;1);1));””;ROW(OFFSET(Tabel!$A$1;1;$C$9;SearchEngine!$G$5;1)))

urutkan dari baris yang terkecil dulu … pakai fungsi SMALL … karena ada judul kolom maka hasilnya harus kurangi 1 agar dapat menunjukkan posisi kolom nomor pada tabel

=SMALL(IF(ISERROR(SEARCH($E$7;OFFSET(Tabel!$A$1;1;$C$9;SearchEngine!$G$5;1);1));””;ROW(OFFSET(Tabel!$A$1;1;$C$9;SearchEngine!$G$5;1)));C10)-1

formula ini sudah bisa dipakai tapi akan menghasilkan error saat urutan ke-x (cell C10) lebih besar dari jumlah data yang cocok (cell G7) …. untuk menghilangkan error formula perlu dimodip sedikit lagi menjadi

=IF(C10>$G$7;””;SMALL(IF(ISERROR(SEARCH($E$7;OFFSET(Tabel!$A$1;1;$C$9;SearchEngine!$G$5;1);1));””;ROW(OFFSET(Tabel!$A$1;1;$C$9;SearchEngine!$G$5;1)));C10)-1)

jangan lupa FORMULA ARRAY …FORMULA ARRAY…FORMULA ARRAY :smile: … copykan sampEcell D19

karena kita sudah punya nomor urutnya pada kolom D … formula yang dipakE untuk menampilkan Judul Lagu, Nama Penyanyi dan Tahun bisa lebih pendek … berikOt formula2nya

• pada cell E10 menampilkan Judul Lagu dengan formula

 =IF(C10>$G$7;””;OFFSET(Tabel!$A$1;D10;1))

• pada cell F10 menampilkan Penyanyi dengan formula

=IF(C10>$G$7;””;OFFSET(Tabel!$A$1;D10;2))

• pada cell G10 menampilkan Tahun dengan formula

 =IF(C10>$G$7;””;OFFSET(Tabel!$A$1;D10;3))

… kali ini cukup di Enter saja formula bisa jalan :smile:

selanjootnya copy aja ke baris2 dibawahnya :wink: …. kelaaaaaaaaaaarrrrrrr deeeeeeeeehhhhh

file contohnya dengan judul SearchEngine.xls monggo di download kayak biYasanya :smile: … semoga manpaat dan …. MDLMDL