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

EXCEL SEARCH ENGINE : CARI DATA PADA TABEL EXCEL

By  Excel  214 Comments

: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 Tahun … PERHATIAN : 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
Tags:, , , , , , , , , , , , ,

Related Posts

214 Comments
    « Older Comments
  1. ghama

    mas kok file nya gak bisa di download ya?

    Reply
  2. excellerates

    barusan saya coba bisa kok

    Reply
  3. ghama

    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?

    Reply
    • excellerates

      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

      Reply
      • ghama

        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

      • excellerates

        sepertinya untuk cell yang berisi formula tidak bisa otomatis

      • Ruby Rusdiono

        Assalamuallaikum Warohmatullahi Wabarakatu Mas,

        Saya mau tanyakan kembali soal pertanyaan mas ghama, karena saya mengalami hal yang sama ketika dimasukan kata pada keyword datanya menjadi #NAME? juga. Melihat dari penjelasan mas maksudnya gimana ya?? apakah jumlah Kolom dan Row baik di “Tabel” dengan “SearchEngine” harus sama ?? dan juga saya juga mempunya masalah dengan nomor urutnya yang tidak sama dengan yang ada di “Tabel” pada saat ditampilkan pada sheet “SearchEngine”.

        Mohon pencarahannya mas 🙂

        Nb : Data yang saya paka sudah disesuaikan dengan kebutuhan saya sehingga tidak sama lagi.

      • excellerates

        Assalamuallaikum Warohmatullahi Wabarakatuh

        Jumlah kolom pada search engine boleh beda dengan tabel datanya … bisa ditampilkan kolom tertentu saja tidak semuanya … coba lakukan step by step … ubah dulu sheet tabel datanya dengan cara insert column … setelah itu periksa apakah sheet search engine masih bisa berfungsi seperti yang diinginkan … kalau sukset lanjut dengan menyesuaikan kolom2 yang ada pada search engine .

      • Ruby Rusdiono

        Baik akan saya coba. Jazakallah Khairan Katsiran atas bantuannya akhi:) Kalo ada kendala akan kami hubungi kembali heheheh

  4. wahit Y

    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.

    Reply
    • excellerates

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

      Reply
      • wahit y

        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

      • excellerates

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

  5. sudiman

    bagaimana jika data yang kita cari berdasarkan 2 criteria berbeda?

    Reply
  6. Haidir

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

    Reply
  7. Dinul

    Mohon bantuannya pak, saya ngikutin ini dari kemarin hingga sore ini, berhasil pak. hanya saja dan anehnya saya menggunakan manual di langkah pertama, tidak bisa membuat rumus match(cell;DefineName) anggeplah saya cari judul lagu, begitu dirumuskan macth malah #N/A pak, kira2 kenapa ya ?

    dan lagi jika berhasil saya malah, kadang tidak sesuai kolomnya, judul lagu itu misal di saya kolom ke 2 tapi begitu berhasil di rumus MATCH malah hasilnya kolom 23.

    mhn petunjuknya

    Reply
    • excellerates

      apakah bisa dipastikan data yang akan di-MATCH ada dalam range define names

      Reply
      • Dinul

        sudah pak, hehehe akhirnya ketemu masalahnya tp blom solve, yaitu jika judul kita kasih beberapa spasi misal di kolom ke 5 saya tulis “Jenis Lagu” pasti match akan bingung hitungnya hehehe tp begitu pakai rumus match(cell;DefineName;0) pasti akan sama pak.

        dan anehnya lagi meski kolom yang tertampil sudah benar selalu salah begitu di rumus offset,hehehe akhirnya inisiatif saya kurang 1 saja dan berhasil pak.

        btw ilmunya manfaat bgt pak.terima kasih

      • excellerates

        Selamat deh kalau begitu 😉

  8. jelly

    pak, kalau hasilnya mau dijadikan grafik gimana ya caranya?

    Reply
  9. yosep

    cara membuat rangenya bagaimana
    soalnya cara no 1 bagi saya masih ambigu

    Reply
  10. bp1329

    muantaaaap…
    terima kasih…

    Reply
  11. Wahana Griya Multi

    MIN WAKTU SAYA KETIK KATA DI KEYWORD KOK MUNCUL NAME SEMUA YA?

    Reply
  12. Tress Indarto

    Muhammad Syukron, terimakasih file sudah bisa saya donlot, dan jalan dengan baik.
    semoga menjadi amal baik Pak Syukron.

    Salam
    Tress Indarto

    Reply
    13. « Older Comments

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