Menyaring Data Berdasarkan Beberapa Kriteria
Menyaring data pada tabel dengan satu atau lebih kriteria bisa dilakukan secara semi manual dengan menggunakan menu "Advanced Filter" (di Office 2010 terletak di tab menu Data). Kali ini saya akan sharing bagaimana menghimpun data pada tabel yang sudah ada berdasarkan satu/lebih kriteria pada tabel baru dengan menggunakan VBA. Supaya lebih mudah, langsung ke contoh saja, saya punya data tentang frekuensi gempa di pulau-pulau besar di Indonesia yang saya dapat dari BPS. Dengan sedikit modifikasi saya ubah susunan tabelnya supaya bisa diterapkan filter.
Dari data aslinya, sebenarnya ada satu kolom header lagi yaitu menurut kedalaman gempa, tapi saya hilangkan supaya lebih ramping. Tabel hasil reproduksi tersebut lalu saya ubah menjadi named table supaya lebih dinamis jika ada penambahan data baru. Caranya blok seluruh tabel dari sel A3 sampai kolom D di baris terakhir, pilih menu Insert > Table, beri tanda centang pada pilihan 'My table has headers'. Kalau tabel sudah dibuat dan ketika sel yang aktif berada di dalam tabel, maka akan muncul tab menu baru di posisi paling kanan berjudul "Design". Di input box "Table Name:" biasanya akan tertulis "Table1", atau "Table2" dst. Nama tabel tersebut bisa diubah semau kita, di file contoh saya beri nama tabelnya "Data".
Setelah tabel Data dibuat, selanjutnya tentukan kriteria yang akan dipakai sebagai parameter penyaringan, di sini saya memilih pulau, tahun dan kekuatan. Dari ketiga kriteria tersebut,kemudian saya buatkan daftarnya yang masing-masing berisi item-item yang unik saja, tidak ada yang terduplikasi, misal untuk kriteria tahun, hanya berisi tahun 2010-2013. Bisa langsung download file jadinya di google drive.
Dari setiap daftar kriteria itu, masing-masing saya buatkan named range-nya. Caranya blok data di setiap kriteria, misal untuk kriteria pulau, blok sel F7 sampai F14 dan ketikkan "pulau" (tanpa tanda petik) di Name Box. Namanya tidak harus "pulau", bebas bisa apa saja. Dua kriteria berikutnya, range-nya saya kasih nama "tahun" dan "kekuatan". Sedangkan untuk kata kunci di setiap kriteria berada di sel F4 sampai H4. Di range ini juga dibuatkan name-nya, tapi untuk yang ini bukan hanya kata kuncinya saja yang diblok, tapi header-nya juga. Jadi blok sel F3 sampai H4 dan ketikkan nama range-nya (di contoh saya beri nama "kriteria"). Untuk sementara beralih dulu ke sheet yang lain, buat sheet baru kalau belum ada (di contoh saya beri nama "Filter"). Sheet Filter ini nanti yang akan menampilkan data di tabel setelah melalui penyaringan.
Pertama buat dulu 3 sel yang masing-masing mengandung Data Validation, yaitu di sel A2, A3 dan A4. Aktifkan sel A2, di tab menu Data pilih menu Data Validation untuk memunculkan jendela Data Validation. Di tab settings bagian "Allow:" ganti "Any Value" dengan "List". Di bagian "Source:" ketikkan "=pulau" (tanpa tanda petik), dimana pulau ini merupakan nama range yang sebelumnya sudah dibuat. Ulangi langkah tersebut untuk sel A3 dan A4 dengan membedakan bagian Source-nya. Untuk melihat daftar nama range yang sudah dibuat, di tab menu Formula pilih menu File Manager. Selanjutnya buat header tabel di sel A7:D7, headernya saja yang dibuat, datanya tidak perlu karena nanti akan dimunculkan melalui macro.
Kembali ke sheet Tabel dimana terdapat tabel "Data" yang sebelumnya sudah dibuat. Di sel F4 masukkan formula "=IF(Filter!A2="";"*";Filter!A2)". Formula ini membaca data di sheet Filter di sel A2, jika isinya kosong, maka di sel F4 tersebut akan tertulis tanda bintang *. Jika ada isinya maka data tersebut disalin ke sel F4. Tanda * itu berarti bahwa tidak ada filter yang diterapkan untuk kriteria pulau, itu artinya semua pulau akan ditampilkan di tabel di sheet Filter. Untuk sel G4 masukkan formula "=IF(Filter!A3="";">0";Filter!A3)". Artinya sama dengan sel F4, hanya mengganti tanda "*" dengan ">0". Kenapa sel F4 pakai tanda "*" dan G4 pakai ">0", karena data di kolom pulau berbentuk string/huruf, sedangkan di kolom tahun formatnya angka. Untuk sel H4 masukkan formula "=IF(Filter!A4="";"*";Filter!A4)". Ganti tanda ";" pada formula di atas dengan tanda "," jika formula tidak mau di-enter.
Bagian worksheet sudah beres, selanjutnya beralih ke pembuatan macro. Buka jendela Visual Basic Editor dengan tombol Alt + F11. Supaya data yang disaring bisa langsung muncul seketika di sheet Filter sesaat setelah sel A2 sampai A4 berubah, maka macro disisipkan di worksheet event "Worksheet_Change". Klik 2x sheet Filter pada jendela Project Explorer di VBE dan salin kode berikut di jendela sebelah kanan yang masih kosong. Simpan file dengan format .xlsm (Macro Enabled Excel Workbook) dan silahkan diuji.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim jmldata As Long Dim rngfilter As Range Application.ScreenUpdating = False If Target.Address = Range("A2").Address Or Target.Address = Range("A3").Address Or _ Target.Address = Range("A4").Address Then ' bersihkan tabel filter jmldata = Cells(Rows.Count, "A").End(xlUp).Row If jmldata > 7 Then Rows("8:" & CStr(jmldata)).Delete Shift:=xlUp End If With Sheets("Tabel") .Select ' advanced filter .ListObjects("Data").Range.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=.Range("kriteria"), Unique:=False Set rngfilter = .Range("A4", .Cells(.Rows.Count, "A").End(xlUp)).Resize(, 4) On Error Resume Next jmldata = 0 jmldata = rngfilter.SpecialCells(xlCellTypeVisible).Rows.Count On Error GoTo 0 If jmldata = 0 Then Sheets("Filter").Select GoTo loncat End If rngfilter.Select Selection.Copy End With With Sheets("Filter") .Select .Range("A8").Select ActiveSheet.Paste Application.CutCopyMode = False End With loncat: With Sheets("Tabel").ListObjects("Data") .Range.AutoFilter .TableStyle = "TableStyleLight11" End With Application.ScreenUpdating = True End If End Sub |
File jadinya silahkan download Filter Data dgn Beberapa Kriteria.
Hallo gan, mau nanya kalau sheet yang di filter lebih dari satu sheet gmn ya?
ReplyDeletegimana kalau dibuatkan tombol dengan perintah yang sama..
ReplyDeletemisal data di sheet filter menampilkan Subtotal bagai mana ya??
ReplyDelete