EXCEL SEARCH ENGINE : CARI DATA PADA TABEL EXCEL – Belajar Microsoft Excel : Tips, Tricks & Tutorial

EXCEL SEARCH ENGINE : CARI DATA PADA TABEL EXCEL

:mrgreen: sungguh judul yang boombastis … ndak mutu blas … maksute piye kuwi ๐Ÿ˜• … masak excel mau nyaingi google ๐Ÿ™„

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 ๐Ÿ™„ naaaaaaaghhhh karena pencarian data dalam excel sangatlah mudah … bagaimana kalO kita persulit saja ๐Ÿ™‚ … yang ndak setuju silahkan brenti mbaca artikel ini sampE disini ๐Ÿ™‚ … yang setuju monggo lanjooot

sebagE contoh liyat penampakan tabel sederhana berikOt

SE1

tabel terdiri 4 kolom yaitu : Nomor Urut, Judul Lagu, Nama Penyanyi dan TahunPERHATIAN : data2 yang saya tampilkan dalam tabel ini boleh jadi amat sangat tidak akurat ๐Ÿ™‚ jadi jangan dijadikan bahan referensi

sekarang kita buWat sheet pencarian datanya … yang penampakannya kayak berikOt

SE2

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 ๐Ÿ’ก

pertama kita cari data yang cocok dengan keyword pada kolom yang dipilih … pake fungsi SEARCH yang 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 ๐Ÿ™‚ … copykan sampE cell 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 ๐Ÿ™‚

selanjootnya copy aja ke baris2 dibawahnya ๐Ÿ˜‰ …. kelaaaaaaaaaaarrrrrrr deeeeeeeeehhhhh

file contohnya dengan judul SearchEngine.xls monggo di download kayak biYasanya ๐Ÿ™‚ … semoga manpaat dan …. MDLMDL

tips dan trik lainnya tentang search engine excel klik disini

bingung cara downloadnya ??

Baca aja halaman download dengan klik icon di samping

feed , email and my social media

206 Comments

Jika sOdara menemukan sesuatu yang bermanpaat di marih .... silahkan kasih komennya