Excel Search Engine : Cari Data Pada Tabel Excel

Cara Membuat Search Engine Diexcel Dengan Bantuan Beberapa Rumus Yang Di Gambungan Beberapa Rangkaian Seperti Berikut Ini :

Buatlah 2 Buah Sheet Yang Mana Sheet Tersebut Di Berinama Sheet1(Skuadl)Dan Sheet2(SearchPlayer)  Contoh Seperti Gambar Di Bawah Ini:

Sheet Skuad

Dari Tabel Tersebut Kita Buat 6 Kolom Yaitu : Nomor Nomor Urut, Name Player, Number Shirt, Position, National Dan Year. PERHATIAN : Data-Data Bisa Anda Edit Sendiri Kecuali Judul Kolom Sama Nomor Urut. Data Tersebut Diatas Cuma Di Jadikan Pedoman Untuk Search Engine Jadi jangan Dijadikan Bahan Referensi.

Sekarang Kita Buat Sheet Pencarian Datanya . Contoh Seperti Di Bawah Ini

SearchPlayer

Bagaimana Membuat Frekuensi Pencarian Data Ini Berikut Langkah Langkahnya

1. Bikin Validasi Cell D5
Pada Cell Ini Dapat Dipilih Kolom Mana Yang Akan Dicari Datanya. Sebelum Membuat Validasinya, Buat Terlebih Dahulu Named Range Judul Yang Merujuk Pada Range =Tabel!$B$1:$F$1. Selanjutnya Membuat Validasi Degan Type List Yang Merujuk Pada Range JudulKolom. Dan Pada Cell C11 Kita Input Formula Yang Akan Mencari Kolom Yang Keberapa Yang Di Cari Datanya Dengan Formula Seperti Berikut:
Membuat Judul Kolom

Define name

Pilih Kolom B1 Sampai F1 Yaitu Name Player, Number Shirt, Position, National, Year. Seperti Gambar Di Atas. Anda Bisa Langsung Membuat Define Name Dengan Mengklik Name Box Diatas Kolom A. Dan Tulis Name Range "Judul". Atau Dengan Terperinci Menbuat Name Range Seperti Di Bawah Ini

1. Di Menu Bar Pilih Insert → Name→Define. Seperti Gambar Berikut Ini

Define name

2. Setelah Memilih Define Akan Muncul Menu Bar Define Name, Di Kolom Refers To Pilih  Seperti Gambar Di Bawah Ini

Define name

Setelah Itu Akan Muncul Menu Bar Define name - Refers To:




Dan Pilih B1 Sampai F1 Dengan Formula : =Sheet1!$B$1:$F$1 Dan Klik

Di Kolom Names In Workbook Tulis "Judul" ( Tanpa Tanda Kutip) Lalu Ok.
Selesai Pembuatan Judul Formula Define Range ( Judul )


Dan Sekarang Ke Sheet SearchPlayer
Masukkan Formula Berikut Di C11

=MATCH(D5;Judul)

Seperti Gambar Berikut Ini 

Validasi Cell D5

2. Bikin ScrollBar


Scollbar


Sekarang Atur Scrollbar

Scollbar Button

Atur Ke C12 Input Angka 1 Dan Cell C13 Isi Dengan Formula =C12+1 Lalu Copykan sampai C22 , Lihat Gambar

Pengaturan Cell

3. Bikin Formula Untuk Mencari Jumlah Data
Cell I5 ( Jumlah Data ) Masukkan Formula Berikut:

=COUNT(Tabel!A:A)

4. Bikin Formula Untuk Mencari Jumlah Data Yang Cocok Dengan Keyword

Pada Cell I7 ( Hasil ) Saya Kombinasikan Beberapa Fungsi Sehingga Membentuk Formula Berikut

{=COUNT(IF(ISERROR(SEARCH($D$7;OFFSET(Skuad!$A$1;1;$C$11;Searchplayer!$I$5;1);1));”A”;1))}

Karena Formula Ini Merupakan formula array Untuk Eksekusinya Harus Dengan Menekan Bersamaan Tombol Ctrl + Shift + Enter Ingat Enter Saja Tidak Cukup !!!!

5. Menampilkan Data2 Yang Cocok Dengan Keyword
Data-Data Tersebut Akan Ditampilkan Pada Range D12:G22 Yang Pertama Pada Kolom D Akan Menampilkan No Urut Data Yang Cocok Dengan Keyword  Pada Cell D12 Masukkan Formula berikut :

{=IF(C12>$I$7;””;SMALL(IF(ISERROR(SEARCH($D$7;OFFSET(Skuad!$A$1;1;$C$11;SearchPlayer!$I$5;1);1));””;ROW(OFFSET(Skuad!$A$1;1;$C$11;SearchPlayer!$I$5;1)));C12)-1)}

Jangan Lupa, Ini Juga formula array Jadi Harus PakaiTombol Ctrl + Shift + Enter Mungkin Lagi Kebingungan Dengan Formulanya , Gimana Bisa Mendapatkan Formula Tersebut .

Dalam Formula Tersebut Mencari Data Yang Cocok Degan Keyword Pada Kolom Yang Dipilih, Dengan Memakai Fungsi SEARCH Yang Dikombinasikan Dengan OFFSET

=SEARCH($D$7;OFFSET(Skuad!$A$1;1;$C$11;SearchPlayer!$I$5;1);1)

Jika Keyword Cocok Dengan Suatu Data Maka Cari ROW Dari Data Tersebut Tambahkan Fungsi IF, ISERROR Dan ROW

=IF(ISERROR(SEARCH($D$7;OFFSET(Skuad!$A$1;1;$C$11;SearchPlayer!$I$5;1);1));””;ROW(OFFSET(Skuad!$A$1;1;$C$11;SearchPlayer!$I$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($D$7;OFFSET(Skuad!$A$1;1;$C$11;SearchPlayer!$I$5;1);1));””;ROW(OFFSET(Skuad!$A$1;1;$C$11;SearchPlayer!$I$5;1)));C12)-1

Formula Ini Sudah Bisa Dipakai Tapi Akan Menghasilkan Error Saat Urutan Ke-x (cell C12) Lebih Besar Dari Jumlah Data Yang Cocok (Cell I7) …. Untuk Menghilangkan Error Formula Perlu Dimodif Sedikit Lagi Menjadi

=IF(C12>$I$7;””;SMALL(IF(ISERROR(SEARCH($D$7;OFFSET(Skuad!$A$1;1;$C$11;SearchPlayer!$I$5;1);1));””;ROW(OFFSET(Skuad!$A$1;1;$C$11;SearchEngine!$I$5;1)));C12)-1)

Jangan Lupa Ini Juga formula array Jadi Harus PakaiTombol Ctrl + Shift + Enter Copy Sampai Cell D22


Karena Kita Sudah Punya Nomor Urutnya Pada Kolom D … Formula Yang Dipakai Untuk Menampilkan Player, Negara, Posisi, Tahun Kelahiran, Nomor Punggung Bisa Lebih Pendek Berikut Formula2nya




=IF(C12>$I$7;””;OFFSET(Skuad!$A$1;D12;1))
Isi Sesuai Urut Kolom Seperti Gambar Dengan Formula Yang Diganti Warna Biru Diatas Dimana Nama Player Kolom B No.1 , Nomor Punggung Pemain Di Kolom C No.2 Dan Seterusnya.. Kolom A Tidak Terpakai Karna Itu Range Rumus No. Urut.
•    pada cell E12 menampilkan Player dengan formula
=IF(C12>$I$7;””;OFFSET(Skuad!$A$1;D12;1))
•    pada cell F12 menampilkan Negara dengan formula
=IF(C12>$I$7;””;OFFSET(Skuad!$A$1;D12;4))
•    pada cell G12 menampilkan Posisi dengan formula
=IF(C12>$I$7;””;OFFSET(Skuad!$A$1;D12;3))
•    pada cell H12 menampilkan Tahun Kelahinran dengan formula
=IF(C12>$I$7;””;OFFSET(Skuad!$A$1;D12;5))
•    pada cell I12 menampilkan Nomor Punggung dengan formula
=IF(C12>$I$7;””;OFFSET(Skuad!$A$1;D12;2))

Untuk Formula Cukup di Enter Saja Bisa Jalan


Selanjutnya Copy Saja Ke Baris2 Dibawahnya. Selesai
File contohnya Dengan Judul hasaruddinmuhammad.blogspot.com.xls Silahkan Di Download


Semoga Manfaat. Terima Kasih

Sumber Dari Muhammad Syukron