Rumus Membuat Menu Pencarian dengan Banyak kolom

  1. 2 minggu lalu

    Selamat malam semua

    mohon dibantu donk teman-teman
    saya ingin membuat menu pencarian data dengan banyak kolom
    untuk rumus excelnya seperti apa ya dalam format excel berikut yg saya lampirkan.
    mohon pencerahannya...
    terima kasih

  2. Caton

    Okt 3 Terverifikasi Indonesia + 12.008 Poin

    @Apriuz ... saya ingin membuat menu pencarian data dengan banyak kolom ...

    Silahkan diperiksa solusi pada file terlampir, mungkin saja sesuai dengan yang mas @Apriuz harapkan. Formula yang saya gunakan mengandalkan nama range dinamis, jadi telusuri masing-masing nama range untuk memahami alur kalkulasinya.

    Demikian.

  3. Terimakasih mas @Caton ilmunya..
    God Bless

  4. Selamat malam mas @Caton
    saya coba pelajari ikuti caranya tapi rumus terkadang berubah otomatis
    nama rumus contoh "sheet1" berubah otomatis di range name menjadi nama worksheet
    sehingga rumus error/nggak berfungsi..

    adakah pemahaman yang lebih mudah untuk di ikuti mas @Caton ?
    atau bisa dijelaskan lebih detail skema rumus yg dibuat...

    maaf banyak bertanya
    terima kasih

  5. Caton

    Okt 4 Terverifikasi Indonesia + 12.008 Poin

    @Apriuz ... atau bisa dijelaskan lebih detail skema rumus yg dibuat ...

    Sebagaimana yang sudah saya sampaikan:

    Formula yang saya gunakan mengandalkan nama range dinamis, jadi telusuri masing-masing nama range untuk memahami alur kalkulasinya.

    Perlu dipahami, nama-nama range dinamis yang saya gunakan lingkupnya adalah lokal, sehingga Excel otomatis menambahkan prefiks berupa nama Worksheet dimana lingkup nama-nama range tersebut ditentukan. Coba ubah nama Worksheet Sheet1 menjadi nama berbeda ... :)

    Jika ingin menerapkan formula yang saya berikan pada Worksheet berbeda dan layout berbeda, maka ada beberapa hal yang perlu dipersiapkan:

    db.Main : Nama range ini merupakan nama untuk tabel data induk. Dibuat dinamis agar data yang akan diambil sesuai dengan jumlah data aktual pada tabel data induknya. Jika data induk aktualnya berbeda Worksheet (misalkan data induk pada sheet MASTER, sedangkan proses pencarian akan dilakukan pada sheet TRANSAKSI), maka lingkup nama range db.Main harus dibuat global (Scope = Workbook). Formula nama range ini:

    =OFFSET($A$15; 1; 0; COUNT($A$16:$A$100); 7)

    Sesuaikan notasi sel A15 terhadap range tabel data induk yang mas @Apriuz buat. Demikian pula dengan notasi A16:A100, sesuaikan ke range aktual tabel data induk.

    db.List : Nama range ini merupakan array yang diambil dari db.Main berdasarkan nilai pada sel R2. Jika sel R2 = TRUE, maka array diambil dari kolom ke-2 (kolom KODE) db.Main, Jika sel R2 = FALSE, maka array diambil dari kolom ke-3 (kolom NAMA) db.Main. Scope nama range bisa global (Workbook), juga bisa lokal (ke Worksheet dimana nama range ini akan digunakan). Formula yang digunakan untuk nama range ini adalah:

    =INDEX(db.Main; 0; (TES!$R$2=FALSE)+2)

    db.Count : Tujuan nama range ini adalah untuk menghitung jumlah data yang sesuai (cocok) dengan kriteria yang dimasukkan pada TextBox. Jika CheckBox di centang (nilai pada sel R3), maka db.Count dihitung berdasarkan pencarian secara eksak (tepat). Jika tidak dicentang, maka db.Count dihitung berdasarkan pencarian secara relatif. Formulanya:

    =IF(LEN(db.Key); IF($R$3; SUMPRODUCT(N(EXACT(db.Key; db.List))); COUNTIF(db.List; "*"&db.Key&"*")); 0)

    db.Result : Nama range ini merupakan sebuah Array Formula yang akan menghasilkan nilai berupa indeks baris tabel data induk yang sesuai dengan string yang dicari. Sebenarnya, proses pencarian antara eksak dan relatif tidak berbeda. Kedua mode pencarian memanfaatkan fungsi yang sama. Hanya saja, jika pencarian dilakukan secara eksak, maka db.Count akan mencari data yang tepat sesuai dengan teks yang dicari melalui formula:

    ... SUMPRODUCT(N(EXACT(db.Key; db.List))) ...

    Apabila formula di atas menghasilkan nilai > 0, maka db.Count akan dianggap TRUE, sehingga formula pada db.Result akan dieksekusi:

    =IF(db.Count; SMALL(IF(ISNUMBER(SEARCH($R$4; db.List)); ROW(db.List); ""); ROW($A10))-15;"")

    Array formula dalam formula di atas adalah:

    ... SMALL(IF(ISNUMBER(SEARCH($R$4; db.List)); ROW(db.List); ""); ROW($A1)) ...

    Pada formula di atas, perhatikan notasi pada bagian terakhir:

    ... ROW($A1) ...

    Notasi inilah yang membuat nama range db.Result menjadi dinamis. Jika kursor diletakkan pada sel A8, sel A9 dan sel A10, kemudian buka jendela Name Manager pada masing-masing sel, maka terlihat formula dari nama range db.Result ikut berubah, tepatnya pada notasi terakhir tersebut:

    Untitled.png

    Ini adalah hal penting yang harus diperhatikan. Jika mas @Apriuz membuat tabel hasil pencarian pada range yang berbeda dengan range pada file contoh (A7:G12), maka yang harus dilakukan untuk menyesuaikan nama range db.Result ini adalah:

    — Letakkan kursor pada sel di baris awal tabel hasil pencarian, misalkan saja, pada sel A5.
    — Dengan posisi kursor berada pada sel A5, kemudian buka Name Manager (tekan tombol CONTROL F3), lalu sorot nama range db.Result. Perhatikan pada kolom Refers To. Jika notasi terakhir tersebut tidak merujuk ke sel $A1, maka ubah notasi tersebut menjadi $A1. Perhatikan, pada notasi tersebut, notasi absolut (tanda $) harus berada pada sisi kolom ($A ...). Sehingga formula tetap akan mengacu ke kolom A, sedangkan indeks baris akan bersifat relatif.

    Jika penyesuaian formulanya tepat, maka nama range db.Result pada formula:

    =IF(db.Count; IFERROR(INDEX(db.Main; db.Result; COLUMN(A$1)); ""); "")

    akan menghasilkan nilai indeks baris yang tepat. Ini saja yang dapat saya jelaskan. Jika masih ada masalah, silahkan lampirkan file yang bermasalah tersebut ...

    Demikian, semoga dapat dipahami.

  6. Dear Mas @Caton
    terima kasih mas atas penjelasannya

    saya sudah ikuti penjelasan mas @Caton tapi belum berhasil juga
    mohon koreksinya letak kesalahan dimana ya mas,
    dan mohon dibantu penjelasan kesalahannya ya mas
    berikut saya lampirkan filenya
    terima kasih

  7. Caton

    Okt 5 Terverifikasi Indonesia + 12.008 Poin

    @Apriuz ... mohon koreksinya letak kesalahan dimana ya mas, dan mohon dibantu penjelasan kesalahannya ...

    [1]. Linked Cell untuk OptionButton Control dan CheckBox Control

    — Untuk OptionButton OptionButton1, nilai properti Linked Cell-nya adalah $Q$3. Sedangkan untuk OptionButton OptionButton2, dikosongkan saja, karena yang dibutuhkan adalah perubahan nilai dari OptionButton1 saja. Kontrol OptionButton2 dibutuhkan agar nilai dari OptionButton1 dapat berubah saat OptionButton2 dipilih.

    — Linked Cell dari kontrol CheckBox1 diisi dengan nilai $Q$4. Jadi untuk sel Q4 sebenarnya merupakan trigger apakah pencarian dilakukan secara eksak atau secara relatif.

    [2]. COUNTIF terhadap nilai numerik pada db.Count

    Formula pada nama range db.Count:

    =IF(LEN(db.key);IF(Sheet1!$Q$4;SUMPRODUCT(N(EXACT(db.key;db.list)));COUNTIF(db.list;"*"&db.key&"*"));0)

    tidak akan berhasil oleh karena jika pilihan pencarian pada kolom NOMOR, maka db.List akan berisi array nilai bertipe numerik yang berasal dari kolom NOMOR. Berbeda dengan contoh awal dimana tipe nilai pada kolom NOMOR adalah teks (alphanumerik). Ini masalahnya jika contoh data yang diberikan berbeda dengan data aktualnya ... :D

    Untuk mengatasi masalah tersebut, ubah formulanya menjadi:

    =IF(LEN(db.key);IF(Sheet1!$Q$4;SUMPRODUCT(N(EXACT(db.key;db.list)));SUMPRODUCT(ISNUMBER(SEARCH(db.key;db.list&""))*1));0)

    [3]. Indeks baris tidak tepat pada nama range db.Result

    Nama range db.Result menggunakan formula:

    =IF(db.count;SMALL(IF(ISNUMBER(SEARCH(Sheet1!$Q$5;db.list));ROW(db.list);"");ROW(Sheet1!$A1048571))-16;"")

    Kesalahan utamanya adalah pada notasi ROW(Sheet1!$A1048571). Seperti yang sudah saya jelaskan:

    — Letakkan kursor pada sel di baris awal tabel hasil pencarian, dalam hal ini sel A10.
    — Dengan posisi kursor berada pada sel A10, kemudian buka Name Manager (tekan tombol CONTROL F3), lalu sorot nama range db.Result. Perhatikan pada kolom Refers To. Jika notasi terakhir tersebut tidak merujuk ke sel $A1, maka ubah notasi tersebut menjadi $A1. Perhatikan, pada notasi tersebut, notasi absolut (tanda $) harus berada pada sisi kolom ($A ...). Sehingga formula tetap akan mengacu ke kolom A, sedangkan indeks baris akan bersifat relatif.

    jadi, seharusnya (dengan posisi kursor ada pada sel A10), formula db.Result adalah:

    =IF(db.count;SMALL(IF(ISNUMBER(SEARCH(Sheet1!$Q$5;db.list));ROW(db.list);"");ROW(Sheet1!$A1))-16;"")

    Demikian, contoh revisi terlampir.

 

atau Mendaftar untuk ikut berdiskusi!