EXCEL SEARCH ENGINE : CARI DATA PADA TABEL EXCEL EPISODE 2 KRITERIA – Belajar Microsoft Excel : Tips, Tricks & Tutorial
November 26, 2011
HomeExcelEXCEL SEARCH ENGINE : CARI DATA PADA TABEL EXCEL EPISODE 2 KRITERIA

EXCEL SEARCH ENGINE : CARI DATA PADA TABEL EXCEL EPISODE 2 KRITERIA

By  Excel  75 Comments

setelah saya launching artikel dengan judul yang sangat bombastis ini ( 💡  EXCEL SEARCH ENGINE : CARI DATA PADA TABEL EXCEL :idea:) banyak respon yang masuk … salah satu respon yang masuk agar hasil pencarian otomatis terUpdate hasilnya pada saat keyword diketik … ndak perlu lagi nunggu kita pencet ENTER … akhErnya brojol artikel yang ini  ( 💡  EXCEL SEARCH ENGINE : CARI DATA PADA TABEL EXCEL – 2  💡 )

tidak hanya sampe disitu … ada jugaK beberapa orang yang mintak agar kita bisa menambah kriterianya yang hanya semata wayang itu … saya juga sudah kadung janji akan membahasnya jugak suWatu saat nanti 😎 … setelah bolak balik menggosok lampu ajaib …. lalu wuuuuuuuuuuuus muncullah jin Aladin Excel … saya sudah was2 jangan2 jinnya akan ngomong “wani piro ????”  😯 … untunglah  jinnya baEk hati dan suka menulung 😎 … saya dikasi trik bikin Search Engine dengan 2 kriteria 💡

baeklaagh … bagEmana penampakan Excel Search Engine dengan 2 kriteria ini … monggo diliyat skrinsyut berikot :

SE2KOLOM

bandingkan dengan yang cuman satu kolom

lalu apa saja yang perlu dimodip :

1. beberapa cell yang saya gesar-geser penempatannya

selaEn saya pindah2 … beberapa cell yang dulu saya sembunyikan dengan cara warna font =  warna interior cell , ditutupi ScrollBar dll … maksudnya agar terlihat lebih rapi 😎  … sekarang saya ubah menjadi kelihatan agar lebih jelas

2.  tambah List Kolom ( cell E13 warna abu2 ) dan Keyword (cell G13 warna pink) untuk kriteria kedua

memang ada sedikit perubahan peletakan dari yang tadinya List Kolom di atas Keyword sekarang saya ubah menjadi berdampingan

nomor kolom jugak saya rubah dari yang tadinya berada pada cell C9 saya rubah ke cell H11 untuk nomor kolom kriteria #1 dan cell H13 untuk kriterian #2

fromulanya tetap menggunakan fungsi MATCH yang berubah hanya referensinya karena range List Kolom bergeser ( lihat cells warna orange )

3. perubahan formula untuk mencari jumlah data yang cocok dengan 2 kriteria pencarian

pada Search Engine yang lama ada pada cell G7 dengan Formula Array

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

sekarang bergeser ke cell H7 dengan Formula Array

=SUM(IF(ISERROR(SEARCH($G$11;OFFSET(Tabel!$A$1;1;$H$11;$H$5;1);1)=SEARCH($G$13;OFFSET(Tabel!$A$1;1;$H$13;$H$5;1)));0;1))

4. perubahan formula untuk menampilkan data2 yang cocok dengan keyword

pada Search Engine lawas ada pada range D10:D19 … dengan formula Array pada cell D10 :

=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)

pada Search Engine 2 Krteria ini saya geser ke range D16:D25 … dengan formula Array

=IF(C16>$H$7;””;SMALL(IF(ISERROR(SEARCH($G$11;OFFSET(Tabel!$A$1;1;$H$11;$H$5;1);1)=SEARCH($G$13;OFFSET(Tabel!$A$1;1;$H$13;$H$5;1)));””;ROW(OFFSET(Tabel!$A$1;1;$H$13;$H$5;1)));C16)-1)

cell2 dibawahnya tinggal dicopy aza

silahkan download file SearchEngine2Kolom.xls … 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

75 Comments
    « Older Comments
  1. anca

    tolong bahasa manusianya 🙂
    =SUM(IF(ISERROR(SEARCH($G$11;OFFSET(Tabel!$A$1;1;$H$11;$H$5;1);1)=SEARCH($G$13;OFFSET(Tabel!$A$1;1;$H$13;$H$5;1)));0;1))

    Muhammad Syukron :
    itulah kelemahan saya … tidak bisa memberikan penjelasan yang mudah dimengerti

    Reply
  2. nandang

    asllmlkm..
    mau tanya mas kalaunya filter data pake cara search engine bisa ga??? kira2 code ny gmana?? trmksh
    wlkm slm..

    Muhammad Syukron : apakah seperti ini

    Reply
  3. rien rine

    mas mohon bantuannya,
    sy pny data
    register barang
    1 pena
    1 pensil
    1 spidol
    2 piring
    3 mobil
    3 motor

    sy ingin–> jika 1 maka otomatis ditampilkan pena, pensil, spidol
    jika 3 maka tampil mobil, motor
    mhn bantuannya krn datanya banyak sekali
    terima kasih sekali

    Muhammad Syukron :
    Bisa pakai contoh file pada artikel ini … cuman contoh filenya dimodifikasi dikit

    pada Sheet “Tabel” kolom lagu isi dengan data 1, 1, 2, 3, 3 dst … kemudian kolom Penyanyi isi dengan pensil, spidol, piring, mobil, motor dst

    pada sheet “SearchEngine” formula pada D10 ganti dengan formula ARRAY (eksekusi dengan Ctrl+SHift+Enter) berikut:
    =IF(C10>$G$7,"",SMALL(IF(OFFSET(Tabel!$A$1,1,$C$9,SearchEngine!$G$5,1)=$E$7,ROW(OFFSET(Tabel!$A$1,1,$C$9,SearchEngine!$G$5,1)),""),C10)-1)
    copykan ke baris dibawahnya

    dan pada G7 ganti formulanya dengan formula ARRAY berikut :
    =COUNT(IF(OFFSET(Tabel!$A$1,1,$C$9,SearchEngine!$G$5,1)=$E$7,ROW(OFFSET(Tabel!$A$1,1,$C$9,SearchEngine!$G$5,1)),""))

    untuk contoh file ada disini

    Reply
  4. bramaswara

    mas..kok ga bisa di donlot ya?

    Muhammad Syukron : nggak bisanya kenapa ???? flash player aktip ndak ????

    Reply
  5. awang

    Asalamualaikum,
    Mungkin ini agak melenceng dari tema pembahasan… bisa ga ya mas search data banyak sekaligus..contoh: saya ada 10000 data dan saya mau cek 2000 data secara bersamaan karena takut ada kesalahan input. Kalo cek satu2 banyak makan waktu..

    wassalam trims..

    Reply
  6. agungtri@muslim

    SAYA KO GAB BISA DOWNLOAD YA GAN??

    Reply
  7. ryswan

    terima kasih atas bantuannya Gan, sangat membantu sekali untuk saya. semoga Allah selalu memberikan rejeki dan perlindunganNya selalu untuk Agan. Aamiin ya robbal alamin. Terima kasih sekali lagi,

    Reply
  8. Ryan

    salam kenal mas, mau tanya kalo bisa gak buat search engine untuk 1 file excel yang terdiri dari 50-100 sheet,mohon bantuannya..trims

    Reply
  9. DARWIN RACHMANTO

    Assalamu’alaikum wr wb

    Mas, mohon pencerahannya, Misal ada 3 sheet yang berisi no surat dan perihal surat (di asumsikan sheet 1 = Surat Intern, sheet 2 = Surat Ekstern, sheet 3 = Undangan)

    pada sheet 4 adalah sebuah report, dimana jika saya mengetikkan nomor surat maka akan dilakukan pencarian di 3 sheet dan kemudian ditampilkan data berdasarkan no surat yg di inputkan.

    atas bantuannya terima kasih

    Reply
  10. aa

    numpang sedot y

    Reply
  11. rizky

    link downloadnya dimana ya

    Reply
  12. Adlan Syiari

    Alhamdulillah menemukan trit yang sangat bagus sekali ..
    Semoga ilmunya bermanfaat bagi semuanya .. Aamiin ..

    mas mau tanya dong, disini kan search bedasarkan 2 kriteria, kalau misalkan ditambah menjadi 3 kriteria atau 4 kriteria, rumus yang perlu dirubah pada rumus ini
    =IF(C16>$H$7;””;SMALL(IF(ISERROR(SEARCH($G$11;OFFSET(Tabel!$A$1;1;$H$11;$H$5;1);1)=SEARCH($G$13;OFFSET(Tabel!$A$1;1;$H$13;$H$5;1)));””;ROW(OFFSET(Tabel!$A$1;1;$H$13;$H$5;1)));C16)-1)

    apa ya ? mohon bantuannya Ya Mas .. 🙂

    Reply
  13. chresandro

    Reblogged this on chresandro and commented:
    mantap

    Reply
  14. Akyak Udin Amadjid

    Ass Wr Wb, Mas Syukron…
    Makasih share ilmunya, smg menambah keberkahan dalam kehidupan Mas Syukron. kalau rumusan tersebut saya coba di drivegoogle kok ga bisa ya? khusunya rumusan formula array.

    Reply
  15. chariez

    sangat membantu saya mas syukron..many thanks…ada refrensi lain mas yg lebih menarik…

    Reply
  16. nathan

    Ass Wr Wb, Mas Syukro

    terima kasih buat ilmunya yang sangat membantu.
    mau tanya… apakah dimungkinkan jika kita mencari dengan kriteria data dalam interval.
    misalnya : mencari -> rihanna -> hanya dari tahun 1998-2008 saja
    thank you sebelumnya

    Reply
  17. dikhen

    bos link downloadnya mn yaaa pusing nihhhh cri linknya dari kemaren

    Reply
  18. arya

    Maaf mas … saya coba buat yang 2 kolom .. jika data terlalu banyak kok berat sekali yah … ??? kalau datanya sedikit gak begitu berat , berapa banyak row yang bisa diakses ..???mohon solusinya … data sampai row 244582.. sekali lagi mohon solusinya

    Reply
  19. Adi siswanto

    Assalamualaikum mas…
    saya ingin nanya kalau kita mau buat search engine yang memunculkan semua item yang berbeda dengan satu code gimana ya mas…

    Reply
  20. irsyan

    salam mas apa ada video tutorial dari awal pembuatan gak mas ya soalnya saya agak bingung ni mas

    Reply
  21. azmi6292

    Mas saya kan nambah kolom judul di sheet tabel, harapan saya biar data yg muncul lebih banyak.. pas di kotak search engine saya mau nambahin kolomnya, 1 kolom berhasil tapi kolom lainnya gagal, padahal saya udah sesuaian rumus yang ini : =IF(C16>$H$7;””;OFFSET(Tabel!$A$1;D16;2)) sama tabel yang sheet sebelahnya. kira2 kenapa ya mas? XD

    Reply
  22. nurkholis

    formula untuk mencari data antara gimana ya mas, misal saya punya data 2000 an, formula untuk mencari data yang loncat antara no 500 – 750 formulanya gimana terima kasih

    Reply
    • excellerates

      Bikin dulu list nomor yang benar/lengkap … nanti berdasarkan nomor tersebut dengan Fungsi COUNTIF bisa dilihat nomor2 yang tidak ada … nomor2 yang yang hasil COUNTIF nya 0 adalah nomor yang diloncati … untuk menampilkan semua nomor yang diloncati tersebut bisa pakai Auto Filter

      Reply
    « Older Comments

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