EXCEL SEARCH ENGINE : CARI DATA PADA TABEL EXCEL EPISODE 2 KRITERIA
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 :
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 |
FORMULA RUMUS EXCEL CARA MENCARI PENJUMLAHAN BERTINGKAT – MULTI LEVEL SUM DENGAN FUNGSI SUMPRODUCT
BERMAIN DENGAN CONTROL TEXTBOX (1) [MACRO VBA EXCEL TUTORIAL SERIES]
ADVANCED FILTER … CARA ASIK MEMFILTER TABEL
Jika sOdara menemukan sesuatu yang bermanpaat di marih .... silahkan kasih komennya
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Assalamu a’laikum..
Alhamdulillah.. salut mas.. brarti ngga nutup kemungkinan bisa di buat yang lebih dari 2 kriteria.. cukup mas, biar jadi PR buat kami..
Wassalam,
Ibnu Rojab
Alaikum salam …
Silahkan dicoba modip jadi lebih dari 2 kriteria 🙂
Wah saluut… Search Engine yang kemarin aja udah banyak manfaatnya bagi saya untuk mencari data siswa dari sekian ratus siswa, apalagi sekarang ditambah lagi kriterianya, jadi lebih mak nyos.. Mohon sedot n terima kasih buanyak Syukron Katsiron.
sip 🙂 sip 🙂
Assalamu’alaikum. Mas
Matur nuwun alias hatur tank you
sip 🙂
salam kenal bos…mau nanya untuk list di E5 umpama mau nambah kolom gimana?maklum lg blajar,minta pencerahannya. Thanks.
list menggunakan data validation … coba baca2 artikel tentang validasi data 😉
sip boss…..pe er buat saya….akan kucari sampai ke ujung dunia…tp di dunia maya….thanks.
disini jugaK ada … silahkan diubek2
makasih bnyk nih kang…udah mau share ilmunya…
saya mau belajar banyak sama akang nih,boleh kan?
itu ‘judulkolom’ ngacu nya kemana ya kang.
mau modif tp msh blm nemu.
trus utk yg form SSP3 boleh minta modulenya?
btw, saya kemarin kirim email jg.
terima kasih bnyk sebelumnya.
judul kolom menggunakan validasi LIst yang mengacu pada named range
SSP3.rar yang sOdara donlot sudah lengkap dengan file2 pendukungnya … silahkan diimport lewat VBA Editor excel
maksudnya yang ini kang syukron…
=MATCH(E11,JudulKolom)
JudulKolom disitu merujuk ke cell berapa ya ?
oops..udah ketemu….terima kasih lagi
siiiiiip
Dear Bpak M.Syukron ..minta tolong cara bikin search engine ..dri dasarnya..tahap demi dahap…karena saya sangat awam sekali..terimaksih sebelumnya.
sudah ada kok di sini … coba cari artikel dengan keyword “search engine”
salam hangat buat M. Sykukron,
ak lagi coba bikin search engine untuk stok barang, dan setiap barang memiliki harga, yang ak tanyakan bagaimana memunculkan total jumlah harga dari hasil data yg muncul
sedangkan dalam harga ada yg menggunakan Cell Rupiah (Rp) dan Cell Dolar ($)
misal harga dalam contoh diatas adalah kolom Tahun … tinggal di SUM aja pada kolom hasil pencariannya
tapi jumlah baris sebaiknya disesuaikan dengan jumlah maximal data yang mungkin muncul … kalau bikinnya 10 baris seperti diatas sedangkan jumlah data yang sesuai kriteria ada 11 maka yang akan dijumlahkan hanya 10 baris saja
perkiraan saya sih pakai formula array juga bisa … tapi saya belum sempat utak atiknya
pakai array formula berikot ini juga bisa
=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;OFFSET(Tabel!$A$1;1;3;$H$5;1)))jangan lupa pakai Ctrl+Shift+Enter
terimakasih 🙂
makasih banyak mas Sykukron atas petunjuk dan pencerahannya
tapi kalo boleh tau nulis formula y di range berapa y??? biar bisa tau jumlah total dari data yang muncul.
Terimakasih 🙂
di E5 bisa 🙂
Mas Syukron yang baik hati mohon bantuannya untuk beberapa pertanyan ini:
1. Untuk membuat formula ARRAY di Cell H7 bagaimana jika KEYWORDnya ada (4) yaitu di
Cell (G11, G13, G15, dan G17)???
2. Lalu untuk bikin formula ARRAY yang di Cell D16 bagaimana.???
selama ini saya udah coba tapi Hasilnya GAGAL…… #$%& = PUSING hehehehe…
Mohon maf Mas jadi ngerepotin sampean maklum Mas masih belajar jadi belum begitu paham cara mengkombinasikan formula jika kita menambah beberapa kolom.
Terimakasih Sebelumnya
sebenarnya mungkin bisa pakai formula array tapi formulanya akan menjadi panjang … saran saya pakai advance filter aja … coba baca ini
Mas Syukron saya mohon BGT… bantuannya cara untuk menampilkan data2 yang cocok dengan (4) KEYWORD, karna saya tidak mengerti macro jadi saya tetep menggunakan Formula Array seperti yang contoh mas Syukron buat tapi saya coba kembangkan dan hasilnya seperti ini:
=IF(C27>$G$7,””,SMALL(IF(ISERROR(SEARCH($H$13,OFFSET(Tabel!$A$1,1,$J$13,$G$5,1),1)=SEARCH($H$15,OFFSET(Tabel!$A$1,1,$J$15,$G$5,1),1)=SEARCH($H$17,OFFSET(Tabel!$A$1,1,$J$17,$G$5,1),1)=SEARCH($H19,OFFSET(Tabel!$A$1,1,$J$19,$G$5,1))),””,ROW(OFFSET(Tabel!$A$1,1,$J$13:$J$19,$G$5,1))),C27)-7)
Penejelasannya seperti ini:
Untuk menapilkan data yg cocok dengan Keyword saya tempatkan di Cell (C27), untuk menampilkan Total Jumlah data saya tempatkan di Cell (G5), Untuk Keyword saya tempatkan di Cell (G13, G15, G17, G19), Untuk menampilkan Hasil data yang Cocok dengan Keyword saya tempatkan di Cell (G7), sedangkan untuk List Kolom saya tempatkan di Cell (J13, J15, J17, J19)
Tapi ketika saya Copy ke cel berikutnya hasilnya #NUM! ADUH… ampun deh DJ udah cape2 hasilnya seperti itu.
Jujur aja mas udah 2hari 2mlm ngotak atik formula ini tapi hasilnya seperti itu beginilah kalo gk tau ilmunya cape sendiri hasilnya. Sy mohon bantuannya bgt mas Syukron
Dan kalo boleh saya minta No Hp yg biasa di hubungi
terimakasih banyak sebelumnya…
maaf mas formulanya gk keliatan jelas jadi saya krim ulang
=IF(C27>$G$7,””,SMALL(IF(ISERROR(SEARCH($H$13,OFFSET
(Tabel!$A$1,1,$J$13,$G$5,1),1)
=SEARCH($H$15,OFFSET(Tabel!$A$1,1,$J$15,$G$5,1),1)
=SEARCH($H$17,OFFSET(Tabel!$A$1,1,$J$17,$G$5,1),1)
=SEARCH($H19,OFFSET(Tabel!$A$1,1,$J$19,$G$5,1))),””,
ROW(OFFSET(Tabel!$A$1,1,$J$13:$J$19,$G$5,1))),C27)-7)
Asalamualikum Mas Syukron….. Mas mau nanya kalo kita mau Print Preview dari data hasil pencarian bagaimana caranya??? kalo kita copy paste special pada sheet yg baru sepertinya menambah pekerjaan baru biar praktis bagaimana caranya?? mohon Penjelasan detilnya step by step.. Terimakasih banyak sebelumnya.
kalau mau memindahkan hasil pencarian yang saya tahu harus pakai macro … coba REKAM (Record) satu macro untuk memindahkan hasil pencariannya … kalO sudah jalan sesuai keinginan syukur … kalau ada masalah bisa disharing ke saya … kalau mau pakai macro sekalian sebaiknya pakai metode ADVANCE FILTER saja … silahkan dicari2 tulisan tentang ADFANCE FILTER di blog ini
Assalamualaikum Mas Syukron..salam Kenal..Mas Mau Nanya untuk Search Engine ini bisa berjalan dengan tabel data yang sudah terisi ya atau sudah total datanya, saya coba membuat search engine dengan metode yang mas Syukron sampaikan namun data tabelnya masih kosong dan tabel isi itu akan terisi setelah ada input data..fungsi search engine ini saya gunakan sebagai tools untuk me track history data inputnya..namun ketikan search engine saya gunakan hasilnya (0) tidak bisa membaca data tabel yang dituju. Mohon pencerahannya Mas…Terima Kasih.
seharusnya sih bisa … coba tabel yang ada dalam contoh file sOdara hapus isinya (baris 2 dst) lalu isi lagi dengan data … apakah masih tetep bisa jalan formulanya ❓
atau coba pakE metode Advance Filter
sudah saya coba Mas namun tidak bisa..pengisian data tersebut saya menggunakan userform mas, jadi tabel tersebut adalah database yang saya hide atau tidak dapat dilihat oleh user, atau ada pengaruhnya mas karena dalam data tersebut sebelahnya saya kasih vlookup untuk menjadi beberapa katagori detailnya dan saya jadikan report lagi dengan dengan tabel yang saya dapatkan setelah saya pivot. klu diijinkan saya kirim by email bgmna mas?namun ini confidential mas, bagaimana mas?
atau format cellnya akan berpengaruh juga untuk dapat dibaca searchengine Mas?
Terima Kasih sebelumnya Mas Syukron.
dari search yang saya lakukan hanya denga katogori yang pada KOLOM D saja Mas yang bisa terbaca oleh search engine, apakah ada kesalahan pada formula saya Mas?Terima Kasih
memang susah kalO ndak memeriksa filenya langsung .. tapi kalO sOdara kirimkan filenya ke saya apakah tidak melanggar peraturan di tempat sOdara bekerja .. saya serahkan semua pada sOdara … email saya klik aja pada gambar GMAIL pada sidebar
Mas syukron, bagaimana jika kita ingin melakukan sum untuk hasil search misalnya sum kolom tahun, kalo hasil search tidak lebih dari jumlah baris yang disediakan bisa di sum di bawah kolom, tapi jika hasil search lebih banyak dari jumlah baris yang disediakan sum tadi hanya menghitung yang tampil saja, yang tidak tampil tidak dihitung, bagaimana caranya agar bisa menghitung sum seluruh hasil search?
coba formula pada G7 … yang COUNT ganti SUM
Luar biasaaaa !!!! working perfectly, Mas Syukron emang keren abis dah.. Terima Kasih atas Petunjuknya Mas
Assalamualaikum…
Salam sukses kang. Mau nanya nih kang.
1. Misalkan ada beberapa tabel pada sheet1, sheet2, dst.. & Keyword pertama merujuk pada sheet tersebut, keyword ke-2 tetap sama (merujuk pada tabel) bagaimana formulanya?
2. Apabila pada range I16:I25 diberi masukkan data & data tersebut akan merujuk ke tabel kolom E (sesuai dengan hasil pencarian). bagaimana pemecahannya?
Natur nuwun kang….
Alaikum salam pak
1. kalo yang dimaksud adalah tabel data lebih dari 1 dan berada pada beberapa sheet yang beda … dan bisa dipilih tabel mana yang akan dipakai … bisa diutak atik lagi formulanya dengan tambahan fungsi INDIRECT … untuk contoh penggunaan fungsi INDIRECT bisa baca disini
2. coba perhatikan formula dalam cell G16 dan H16 … ada argumen fungsi yang berubah … naaah kunci ada di argumen yang berubah itu 😉
gimana caranya agar hasil pencarian tersebut langsung bisa di klik kaya di google tu (langsung kelihatan datanya). tanks
klik disini 🙂
Trima kasih mas, atas Pencerahannya.
usul saya dih cuma satu, bagai mana kalau mas sukron buat bukunya.
tentang membuat dashboard pada macro excel. ane siap sumbang saran dan info.
thanks balik
link download dimana ya…
mas tolong dinabantu z punya data di sheet1 begini
A1 B1
a 5
a 4
c 3
d 5
b 2
c 3
d 4
a 5
saya mw tampilkan rekapannya di sheet2
a 14
b 2
c 3
d 9
dengan catatan setiap data baru yang bertambah di sheet1 akan terupdate dengan sendirinya di sheet2
makasih sebelumnya
ok maksih bantuannya mas.. 🙂
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))
asllmlkm..
mau tanya mas kalaunya filter data pake cara search engine bisa ga??? kira2 code ny gmana?? trmksh
wlkm slm..
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
mas..kok ga bisa di donlot ya?
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..
SAYA KO GAB BISA DOWNLOAD YA GAN??
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,
salam kenal mas, mau tanya kalo bisa gak buat search engine untuk 1 file excel yang terdiri dari 50-100 sheet,mohon bantuannya..trims
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
numpang sedot y
link downloadnya dimana ya
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 .. 🙂
Reblogged this on chresandro and commented:
mantap
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.
sangat membantu saya mas syukron..many thanks…ada refrensi lain mas yg lebih menarik…
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
Karena kita sama-sama baru mengenal excel..
dan kebetulan Pertanyaan dari Nathan Pernah saya Kerjakan.
Nathan Bisa menggunakan Record Macro.
https://norkuys.wordpress.com/2011/12/10/advance-filter-cara-asik-memfilter-tabel/
bos link downloadnya mn yaaa pusing nihhhh cri linknya dari kemaren
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
Assalamualaikum mas…
saya ingin nanya kalau kita mau buat search engine yang memunculkan semua item yang berbeda dengan satu code gimana ya mas…
salam mas apa ada video tutorial dari awal pembuatan gak mas ya soalnya saya agak bingung ni mas
InsyaAllah saya bikin videonya 😉
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
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
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
Selamat malam pak
saya ada persoalan yg saya cari solusinya di mbah google blm dapat juga
cara menampilkan database di exce dalam 1 file dan beberapa kondisi
bisa saya hubungi bapak di alamat email?
terimakasih sebelumnya
[email protected]ates.com
Alkhamdulillah sudah punya Domain sendiri…Sedikit inputan Pak, Tulisan artikelnya “Overlap”…Keluar margin jadi agak kepotong-potong bacanya, apalagi kalo mau copast formula,
Moga makin sukses.
Terimakasih banyak om 🙂
hi, salam kenal
saya ingin bertanya bagaimana jika membuat tabel pencarian seperti diatas tetapi menggunakan 3 kreteria (atau lebih).
terima kasih
untuk tiga kolom atau lebih sebenarnya masih bisa dilakukan dengan formula … tapi formulanya akan sangat panjang dan komplek … sebaiknya gunakan advanced filter dengan VBA
sangat tertarik, saya ingin membuat aplikasi penduduk desa, bisa menghitung jumlah penduduk dengan berbagai kriteria, misalnya RT. 01 Laki-laki berapa, Tamat SD Berapa, Petani Berapa.. dll, mohon kirim nkr WA 085233535317
saya sudah download . saya Belajar modifikasi untuk aplikasi penduduk sehingga ada penabahan judul kolom pada shet “tabel” yaitu Cell B2 (Nama) Cell C2 (RT) Cell D2 (Umur) Cell E2 (Pekerjaan) Cell F2 (Pendidikan)
Pertanyaan saya
1. Bagaimana caranya agar bisa di deteksi hingga jumlah data 6.500 Penduduk, sementara di file contoh kan hanya mendektsi jumlah data 146
Terima kasih gan infonya