jump to navigation

EXCEL SEARCH ENGINE : CARI DATA PADA TABEL EXCEL August 16, 2011

Posted by norkuys in : Excel , trackback

: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

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

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

 =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 Downloadnya ❓
Baca aja halaman download klik disini
e-mail :gmail
like facebook page :FBExcellerates
follow twitter :twittersyukronfatah

Comments»

1. paijo - November 13, 2014

sippp booossss,,, suuwwoonnn
lanjutkan artikelmuu

2. sita - November 19, 2014

makasih inpo nya

3. nayla nyla - December 23, 2014

masih bingung sama penempatan rumusnya apalagi formula array.nya

4. motorbreath - January 13, 2015

memahami formula array memang perlu perhatian lebih 🙂

5. inron - January 7, 2015

download nya ribet gan… bikin biar tinggal klik dong ato di tempat yang gampang – gampang

6. motorbreath - January 13, 2015

bagian mana dari instruksinya yang kurang jelas gan ❓

7. duco furniture - January 19, 2015

keren sob.

8. kaldera - January 21, 2015

keren bgt gan, sangat membantu pekerjaan saya. yang saya ingin tanyakan, saya mencoba mengutak atik agar menu search di kolom tidak cm 3, namun msh belum bisa menambahkan. mohon bantuannya gan, bs mnt kontak hp.ny gan?

9. Robin - January 25, 2015

Gan, kenapa jika sheet “tabel” nama kolom (ex: tahun [pada cell D1] jika diganti dengan huruf awalan “A” (Misal ganti jadi : Album), maka cell pada C9 menghasilkan #N/A,
utk [B1]Judul Lagu hanya dinamakan “Lagu” [C9] menghasilkan value 3, bukannya 1.
dan beberapa huruf yang berbeda di [B1] dan [D1] kondisi formula mengasilkan value yang tidak seharusnya.
Solusi untuk kasus ini apa ya, Gan?
*Soalnya saya mau buat SearchEngine dengan Data tabel nasabah mendapat kendala seperti itu.

Terima Kasih banyak atas jawabannya.

10. motorbreath - January 25, 2015

ubah dikit formula =MATCH(E5;JudulKolom) menjadi =MATCH(E5;JudulKolom;0)

11. Robin - January 30, 2015

Terima Kasih, Gan..
Very Helpfully..

12. vadqul - March 21, 2015

Kok hsiilnya jadi value ya pada d10.E10.F10.G10 saat pencarian nama.. tapi pada D11.E11.F11.G11 tampil hasil pencarian. Padahal ada dua hasil karena value yg D10 jadi cuma terlihat 1 hasil.. mohon bantuannya

13. majiid - March 28, 2015

downloadnya dimana gan?

14. linggaeviyantoko@gmail.com - April 28, 2015

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

maksud dari bikin validasi dengan type list itu caranya gimana mas???saya baru pemula.

terimakasih

15. aam - May 14, 2015

gan link downloadnya dimanaaaa

16. ayu you (@you_ayoe) - May 29, 2015

nggak ngerti ada yang bisa buatin nggak????hope banget….. aku disuruh buat pencarian data begitu,, aku coba nggak bisa2… kalo ada yang mau bantu saya,,, contac @you_ayoe at twitter… thanks

17. Lukman Hutabarat - June 10, 2015

Mantap, Buat Teman-teman yg masih Bingun VBA, Berikut ini Tutorial Lengkap VBA saya : Pemograman VBA

18. ina - July 3, 2015

Assalamualaikum,,
Sugeng sonten mas,,matur suwun sanget di pun bantu ,,,
sangat bermanfaat sekali ,,,
saya modif sedikit ulasan kang mas di atas utk menbantu pekerjaan saya,,dan ada yg kutambahin kolom ,,judul aku ganti item,,penyanyi aku ganti mesin,tahun aku ganti berat,,
hanya yg saya msih bingung cara nambahin sum total berat gimana ya,,sama scroll kalo kutarik kebawah sering muncul intruksi dr formulanya,,
gimana cara nya nambahin tombol next agar tidak narik-narik scrol nya,,,soalnya data sampe ribuan,,

ngoten mawon mas e,,,nyuwun pencerahane,,
syukron jazakumulloh khoiron katsiron,,,

19. ahmad - September 2, 2015

Keren Gan, Lebih bagus lagi jika di ji jelaskan dengan Video

20. Haris Afiatno - September 5, 2015

terima kasih sedianya telah membagi ilmu, semoga agan selalu sempat untuk menuliskan lagi tentang excel. hanya mampu ucapkan terima kasih yang banyak untuk ilmu nya.

21. winarsono gilang - September 25, 2015

mas mau tanya nih? itu tanda kurung kurawal buat apa ya? klo ane hapus rumusnya malah g berfungsi, dan klo ane F2 secara otomatis kurung kurawalnya({}) ilang sendiri ketika ane kasih kurung kurawal secara manual malah si formula kebaca text.mohon pencerahan

22. aibarash - October 29, 2015

izin Ndan, kamo misal multiple bagaimana? misal judul X tapi yang tahun 2010 aja

23. Apik IMcyber - December 30, 2015

Upload file yg udah jadinya om

24. m.arifin - January 3, 2016

Mohon ijin download file dan menggunakannya
Suwun 2x

25. promotion paris sportif - February 22, 2016

Right here is the perfect site for anybody who wishes to find out
about this topic. You understand a whole lot its almost tough to argue with you (not that I
actually would want to…HaHa). You certainly put a brand new
spin on a subject that has been written about for decades.
Wonderful stuff, just excellent!

26. Schiedsrichter dong könnte Rachadamnern Boxstadium nicht verlassen - March 12, 2016

I enjoy, result in I found exactly what I was having a look for.
You have ended my four day long hunt! God Bless you man.
Have a great day. Bye

27. abu muhammad - April 21, 2016

syukron ilmunya mas.
kalo pencariannya misal khusus di kolom A saja pada sheet yg berbeda bgmn caranya mas? jadi skali klik pencariannya meliputi kolom A pada sheet2 pada satu workbook atau pada banyak workbook

28. yuda - April 25, 2016

apakah ini bsa digunakan juga untuk mencari data di semua sheet??
contoh: mencari data atau mensearch dari keseluruhan sheet bukan data d satu sheet sja, melainkan bisa menampilkan data yang cari dari kseluruhan sheet… apakah bisa??
thanks agan, oh iya mungkin bsa d upload contoh excel yang ini gan… bsa bisa liat langsung dmna pnempatan rumusnya.. makasih..

29. irsyan - June 22, 2016

salam mas
mas sya mau minta video tutorialnya bisa gak mas soalnya agak kebingungan saya mas dalam proses pembuatan kolomnya

30. ghama - September 1, 2016

mas kok file nya gak bisa di download ya?

31. excellerates - September 1, 2016

barusan saya coba bisa kok

32. ghama - September 5, 2016

mas, saya sudah coba buat rumus ini dari awal sampai akhir, akan tetapi saya terkendala pada saat memasukkan keyword data Judul Lagu dll tidak muncul, malah muncul nya #NAME? kira2 kenapa itu mas?

33. excellerates - September 5, 2016

coba modif ada file yang didownload … untuk menambah kolom bisa insert kolom pada tabel yang sudah ada … misal antara kolom B-C …yang di sheet “SearchEngine” juga diinsert kolom agar hasil pencariannya sama dengan tabel

34. ghama - September 6, 2016

Alhamdulillah udah bisa mas..
Oiya tanya satu lagi mas, caranya supaya scroll bar bisa resize gimana ya mas? karena data yang saya tampilkan pada beberapa cell ada yang di wrap text sehingga jika saya mencari dengan keyword yang berbeda akan berbeda pula ukuran row height nya.. terima kasih mas

35. excellerates - September 6, 2016

sepertinya untuk cell yang berisi formula tidak bisa otomatis

36. wahit Y - November 29, 2016

gan punten mau tanya, saya sudah buat databse dan saya modif dari awal dengan interterface sendiri, hasilnya lumayan pas pas pencarian tahun kug data tidak ada yag muncul yah. terima kasih atas bantuannya.

37. excellerates - November 29, 2016

susah jawabnya pak … karena formula bisa jalan dengan syarat dan kondisi tertentu … jadi dengan informasi yang ada saya tidak bisa menjawabnya

38. wahit y - November 30, 2016

Kira2 data apa yg dibutuhkan pak, semua condisi OK kecuali pas tahun.. terus tombol go juga g bisa.. terima kasih banyak moga ilmunya bermanfaat dan menambah ladang amal

39. excellerates - December 1, 2016

Mohon maaf pak, saya benar2 tidak tahu jawabannya berdasarkan informasi yang diberikan

40. sudiman - March 31, 2017

bagaimana jika data yang kita cari berdasarkan 2 criteria berbeda?

41. sudarmon0 - June 12, 2017

ada artikel kelanjutannya Pak. silahkan kunjungi link berikut https://excellerates.com/2011/11/26/excel-search-engine-cari-data-pada-tabel-excel-episode-2-kriteria/

42. Haidir - April 17, 2017

mohon solusi ketika mengetik kata kunci sering tidak muncul hasilnya, tq, salam kenal dr kalbar, aay