ADVANCED FILTER : EPISODE FILTER DATA TEKS
Assalaamu’alaikum Warahmatullahi Wabarakatuh
Filter tabel …. pekerjaan ini sepertinya sering kita temui saat kita ingin membuat laporan2 berdasarkan tabel … untungnya untuk keperluan ini Microsoft Excel yang baEk hati sudah menyediakan beberapa tool untuk membantu kita dalam memfilter tabel berdasarkan kriteria2 yang kita inginkan … untuk keperluan filter data ini saya biasa pakE Advanced Filter dengan Macro … kenapa saya pakE cara ini 😕 😕 😕 … ya karena bisanya cuman ini … sebelumnya saya sudah pernah bikin artikel ini dan ini … beberapa pertanyaan dalam komentar yang masuk menanyakan tentang aplikasi teknik melakukan Advanced Filter dengan macro pada beberapa kasus yang ditemui … dalam artikel kali ini saya mencoba untuk masuk lebih dalam 😎 tentang penerapan Advanced Filter pada data2 yang berupa TEKS
sebelumnya melanjutkan ada baEknya sOdara download dulu file contoh AdvancedFilterTeks.xlsm yang ada di folder 2014/04-APRIL 😉 … selesE download langsung buka aja filenya jadi sambil melanjutkan membaca bisa sekalian praktek
Untuk melakukan Advanced Filter sOdara harus menentukan variabel2 yang diperlukan … variabel2 tersebut diperlukan untuk menentukan tabel sumber datanya, kriteria yang disyaratkan, lokasi tempat hasil filter ditampilkan, dan penanganan duplikasi data2 hasil filter … variabel2 tersebut sebagai berikut berikut :
1. Tabel Data… merupakan Range dalam Worksheet Excel tempat tabel data sumber yang akan difilter. Jika ukuran tabel tetap (jumlah baris dan kolomnya tidak berubah) maka Range Tabel bisa ditentukan langsung. Dalam file contoh AdvancedFilterTeks.xlsm Tabel data sumber berada pada sheet “Data” range “A1:E594”, jika ukuran tabel ini tetap sOdara bisa langsung menetapkan Range Tabel dengan kode berikut :
Thisworkbook.Sheets("Data").Range("A1:E594")
Namun ada kalanya ukuran tabel dinamis … bisa berubah menjadi bertambah atau berkurang … terutama pada baris tabel jika terjadi penambahan/pengurangan record … jika terjadi kondisi tersebut kode perlu dimodipikasi agar bisa mengikuti perubahan ukuran tabel menjadi sebagai berikut :
Thisworkbook.Sheets("Data").Range("A1").CurrentRegion
Dengan penambahan CurrentRegion pada kode diatas ukuran tabel yang digunakan akan selalu mengikuti perubahan pada Tabel baik itu penambahan ataupun pengurangan jumlah baris/kolom
2. CriteriaRange … merupakan Range tempat kriteria yang akan digunakan sebagai kondisi/syarat yang harus dipenuhi … untuk membuat Range Kriteria kita harus tentukan dulu Judul Kolom yang akan difilter … Tabel Data yang ada dalam file AdvancedFilterTeks.xlsm sheet “Data” terdiri atas 5 kolom yaitu : NO, KODE, NAMA, PEMBAYARAN, dan TANGGAL … Kolom yang berisi data2 teks ada pada kolom KODE dan NAMA … untuk contoh yang akan saya sajikan dibawah saya pilih kolom NAMA … langkah berikutnya judul kolom yang dipilih dimasukan ke sembarang cell … suka2 sOdara aja yang penting sOdara mudah mencarinya … satu hal yang harus dihindari dalam menentukan lokasi Judul Kolom untuk keperluan membuat kriteria adalah menempatkan Judul Kolom pada baris terakhir dalam Worksheet Excel (row ke-1.048.576) … setelah menempatkanJudul Kolom dalam Cell yang dipilih selanjutnya kita tempatkan Kriteria yang diinginkan pada cell dibawahnya … dalam file contoh Range Kriteria saya tempatkan pada sheet “Kriteria” Range “D1:D2” (silahkan dilihat pada file contoh) … kodenya sebagai berikut :
ThisWorkbook.Sheets("Kriteria").Range("D1:D2")
3. CopyToRange … merupakan Range tempat hasil filter akan ditempatkan … variabel ini bisa saja hanya terdiri atas 1 Cell namun sebaiknya variabel ini disesuaikan dengan jumlah kolom pada Tabel Data … karena jumlah kolomnya ada 5 maka dalam contoh file hasil filter saya tempatkan pada sheet “Laporan” range “A1:E1”
ThisWorkbook.Sheets("Laporan").Range("A1:E1")
4. Unique … betipe Boolean (TRUE atau FALSE) … pilih TRUE jika ingin hasil filter hanya menampilkan data2 yang unik (data yang kembar hanya akan ditampilkan 1 kali saja) … sebaliknya jika ingin hasil filter menampilkan data2 yang apa adanya meskipun ada duplikasi silahkan pilih FALSE
untuk data tipe Teks kondisi yang dapat dipakE untuk menyusun kriteria dapat berupa : sama dengan [teks], tidak sama dengan [teks], diawali [teks], diakhiri [teks], mengandung [teks] dan tidak mengandung [teks]
➡ Sama Dengan [Teks]
Sama dengan lambangnya “=” … jadi untuk membuat kriterianya gabungkan aja lambang “=” dengan teks yang kita inginkan … misal kita akan memfilter nama yang sama dengan HERI … pada cell D2 cukup tuliskan =HERI … loh kok munculnya #NAME? … tenang jangan kuatir kasi aja tanda apostrof ( ” ‘ “) di depan tanda sama dengan … coba sOdara klik tombol filternya … hasilnya ada 5 data cocok dengan kriteria nama sama dengan HERI
➡ Tidak Sama Dengan [Teks]
Kebalikan dengan yang diatas … tinggal ganti tanda “=” dengan “<>” … masukkan aja kriterianya <>HERI … coba test lagi dengan klik tombol filter yang disediakan … hasilnya banyak banget tapi tidak ada satupun nama HERI yang muncul
➡ Dimulai Dengan [Teks]
Dimulai lambangnya apa yah 😕 😕 ❓ … ya udah dari pada bingung ilangin aja lambangnya … jadi pada cell D2 cukup masukkan HERI …klik tombol filter … hasilnya 6 data cocok dengan kriteria … HERI ada 5 dan HERIA ada 1
➡ Diakhiri Dengan [Teks]
saya cari lambangnya juga susah 🙄 … sampai mumet akhirnya saya dapet jugak triknya dari hasil googling … kali ini coba keywordnya disingkat … ambil 3 digit hurup terakhirnya aja menjadi =*ERI … klik lagi tombol Filternya untuk ngetest … hasilnya 6 data cocok dengan kriteria … nama HERI muncul 5 kali dan FERI muncul 1 kali
➡ Mengandung [Teks]
Untuk menulis kriteria mengandung hurup ERI hampir sama dengan kriteria Diakhiri Dengan ERI … tinggal ilangin tanda “=” … jadi pada cell D2 sOdara masukkan *ERI … klik tombolnya … dan hasilnya ada beberapa nama yang cocok dengan kriteri mengandung hurup ERI yaitu : HERI muncul 5 kali kemudian ada lagi : HERIA, MERIY, ERI Y, FERI masing2 muncul 1 kali
➡ Tidak Mengandung [Teks]
Untuk menulis kriteria nama yang tidak mengandung hurup ERI masukkan saja pada cell D2 <>*ERI* … klik tombolnya … dan hasilnya banyak nama tapi tak satupun nama mengandung hurup ERI
selain kriteria2 tersebut diatas ada juga beberapa kriteria lainnya yang bisa diterapkan pada data teks … namun karena jarang digunakan jadi tidak saya bahas dalam artikel ini … mungkin laEn waktu 😉
…::: KODE VBA :::…
Untuk melakukan filter seperti diatas sangat simpel cukup bikin kriterianya lalu klik tombol filter dan hasilnya akan muncul … bikin tombol tersebul juga cukup mudah … sOdara tinggal insert Shape sesuai keinginan sOdara … klik kanan Shape tersebut lalu pilih Assign Macro … tinggal pilih macro AdvFilterTEKS yang ada dalam list … lalu klik OK
Sekarang kita cermati kode yang harus dibikin step by step … semoga ndak bingung … Pola kode VBA untuk menjalankan Advanced Filter adalah sebagai berikut :
[Range Tabel Data].AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=[Range Kriteria], CopyToRange:=[Range Copy Ke], Unique:=[True/False]
Yang ada dalam tanda kurung kotak [….] dan dicetak tebal adalah variable yang musti kita tentukan … kode tersebut meskipun ditampilkan dalam dua baris penulisan pada VB Editor harus dalam satu baris … Baeklagh mari kita mulai bikin kodenya
➡ Bikin nama prosedur
Sub AdvFilterTEKS()
➡ Deklarasikan Variabel yang digunakan
Dim TabelData As Range, Kriteria As Range, CopyKe As Range, Unik As Boolean
➡ Tentukan Nilai masing2 Variabel
Set TabelData = ThisWorkbook.Sheets("Data").Range("A1")
Set Kriteria = ThisWorkbook.Sheets("Kriteria").Range("D1:D2")
Set CopyKe = ThisWorkbook.Sheets("Laporan").Range("A1:E1")
Unik = False
➡ Bikin kode advanced filter sesuai pola diatas, karena saya pengen tabel data yang dinamis saya pakE CurrentRegion
TabelData.CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Kriteria, CopyToRange:=CopyKe, Unique:=False
➡ Aktifkan sheet “Laporan” untuk melihat hasilnya, langkah ini hanya opsional saja … tanpa kode berikut Advanced Filter sudah bisa jalan kok
ThisWorkbook.Sheets("Laporan").Activate
➡ Akhiri prosedur
End Sub
Sehingga kodenya menjadi
Sub AdvFilterTEKS()
Dim TabelData As Range, Kriteria As Range, CopyKe As Range, Unik As Boolean
Set TabelData = ThisWorkbook.Sheets("Data").Range("A1")
Set Kriteria = ThisWorkbook.Sheets("Kriteria").Range("D1:D2")
Set CopyKe = ThisWorkbook.Sheets("Laporan").Range("A1:E1")
Unik = False
TabelData.CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Kriteria, CopyToRange:=CopyKe, Unique:=Unik
ThisWorkbook.Sheets("Laporan").Activate
End Sub
Penggunaan variabel seperti diatas sebenarnya tidak wajib … penggunaan variabel diatas saya maksuTkan agar lebih mudah dipahami … kalaO mau diilangin agar kodenya lebih pendek silahkan saja … kode tanpa deklarasi variabel sebagai berikOt
Sub AdvFilterTeksTanpaVariabel()
ThisWorkbook.Sheets("Data").Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=ThisWorkbook.Sheets("Kriteria").Range("D1:D2"), CopyToRange:=ThisWorkbook.Sheets("Laporan").Range("A1:E1"), Unique:=False
ThisWorkbook.Sheets("Laporan").Activate
End Sub
Coba sodara bikin tombol baru dan hubungkan dengan kode yang tanpa variabel dan bandingkan apaka hasilnya sama 😕 ❓
Semoga berguna dan MDLMDL 🙂
Wassalamualaikum Warohmatullohi Wabarokatuh
tips dan trik lainnya tentang filter data klik disini
bingung cara downloadnya ??Baca aja halaman download dengan klik icon di samping |
feed , email and my social media![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
Reblogged this on Iyan Supriyadi.
Saya mau minta tolong agar Mas syukron menyempurnakan program akuntansi yang saya buat sendiri….apakah mas syukron punya nomor HP yang bisa saya hubungi…?
siap
Permisi, saya mau minta tolong. Saya ingin menggunakan worksheets change event di beberapa sheet (multiple Sheets), kode yang sudah saya pakai utk 2 sheet adl sbb :
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range(“G2”) Then
ThisWorkbook.Sheets(“BPN.1”).Range(“A2”).CurrentRegion.AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range(“G1:G2”)
End If
If Target = Range(“G2”) Then
ThisWorkbook.Sheets(“BPN.2”).Range(“A2”).CurrentRegion.AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range(“G1:G2”)
End If
End Sub
Bagaimana cara mengubah kode biar lebih ringkas ? Saya ingin menggunakan code diatas utk sheet 1 (BPN.1), sheet 2 (BPN.2), ……. sampai sheet 31 (BPN.31)
Terima kasih atas bantuannya…
Mas tolong dibantu , saya ada tugas d tmpat kerja , di RS, untuk membuat laporan, menghitung jumlah pasien per alamat nya ( Kecamatan dan Desa) . jadi untuk mengetahui pasien dari desa mana yang paling banyak, dan seterusnya.
Sekarang metode yang saya gunakan adalah dengan menggunakan fungsi [ =countif ] dan itu membutuhkan waktu lama karna harus membuat fungsi sat per satu dengan jumlah total 32 kecamatan dan setiap kecamatan minimal ada 7 desa,
Kira- kira ada caara yang lebih efektif kah?? untuk menghitungnya???????
untuk bentuk laporannya
=======================================
kecamatan XXXXX (jumlah)
– desa xxxxx (jumlah)
– desa xxxxx (jumlah)
– desa xxxxx (jumlah)
Bang, saya mau nanya… kalau copy teks / hasil filteran nya kita cuma mau nampilin kolom kode dan pembayaran saja bagaimana? jadi kolom yang ada dalam range asal tidak semua masuk dalam hasil filteran.
misal yang akan ditampilkan adalah NAMA dan TANGGAL
Sub AdvFilterTeksTanpaVariabel()
ThisWorkbook.Sheets("Laporan").Range("A1").value="NAMA"
ThisWorkbook.Sheets("Laporan").Range("B1").value="TANGGAL"
ThisWorkbook.Sheets("Data").Range("A1").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, CriteriaRange:=ThisWorkbook.Sheets("Kriteria").Range("D1:D2"), _
CopyToRange:=ThisWorkbook.Sheets("Laporan").Range("A1:B1"), Unique:=False
ThisWorkbook.Sheets("Laporan").Activate
End Sub
Perhatikan kode :
1. ThisWorkbook.Sheets(“Laporan”).Range(“A1″).value=”NAMA”
2. ThisWorkbook.Sheets(“Laporan”).Range(“B1″).value=”TANGGAL”
3. CopyToRange:=ThisWorkbook.Sheets(“Laporan”).Range(“A1:B1”),
Maaf mas saya mau tanya kalo sumber data nya lebih dari 1 sheet gimana y ? maksudnya data nya ada 30 sheet misalnya…
assalammualaikum
saya butuh bantuan memfilter data pak.
saya ada data pada sheet 1, data tersebut acak dgn data kolom ( nama barang, tanggal, dll)
saya ingin memfilter data (nama barang) ke sheet 2, tiap filter nama barang terpisah 1 row, sehingga dalam sheet 2 secara otomatis sudah memfilter data sheet 1 dengan terpisah 1 row tiap filter nama barang
Jaza-Kallah Khairan Katsiiraa
coba kodenya diubah seperti ini
Sub AdvFilterTEKS()
Dim TabelData As Range, Kriteria As Range, CopyKe As Range, Unik As Boolean
Dim jData As Integer, i As Integer
Set TabelData = ThisWorkbook.Sheets("Data").Range("A1")
Set Kriteria = ThisWorkbook.Sheets("Kriteria").Range("D1:D2")
Set CopyKe = ThisWorkbook.Sheets("Laporan").Range("A1:E1")
Unik = False
TabelData.CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Kriteria, CopyToRange:=CopyKe, Unique:=Unik
jData = CopyKe.CurrentRegion.Rows.Count
If jData > 3 Then
With ThisWorkbook.Sheets("Laporan")
i = 3
Do
If .Cells(i, 1).Value = Empty Then Exit Do
If .Cells(i, 3).Value .Cells(i - 1, 3).Value Then
.Cells(i, 1).EntireRow.Insert
i = i + 1
End If
i = i + 1
Loop
End With
End If
ThisWorkbook.Sheets("Laporan").Activate
End Sub
syntax error pada :
If .Cells(i, 3).Value .Cells(i – 1, 3).Value Then
.Cells(i, 1).EntireRow.Insert
harusnya antara
If .Cells(i, 3).Value
dengan
.Cells(i – 1, 3).Value
ada tanda “lebih kecil” dan “lebih besar”
namun ndak tahu kenapa tanda tersebut otomatis dihilangkan oleh wordpress … mungkin karena tanda tersebut digunakan dalam HTML
coba ubah kodenya menjadi
If .Cells(i, 3).Value “lebih kecil” “lebih besar” .Cells(i – 1, 3).Value
nulis “lebih kecil” “lebih besar” pakai simbol yah 😉
sintax error pada If .Cells(i, 3).Value .Cells(i – 1, 3).Value Then
.Cells(i, 1).EntireRow.Insert
halo mas. sebelumnya terima kasih atas ilmunya sangat berguna sekali buat saya. mas saya mau tanya. saya menggunakan kode ini untuk data saya, dan saya sudah berhasil memfilter sesuai kriteria keinginan saya. nah setelah saya mendapatkan data yang telah terfilter (katakanlah ada 100 rows) dan terdiri dari beberapa kolom (katakanlah ada kolom nama dan gaji), saya ingin menambahkan angka sebesar (misal) 100000 pada kolom gaji untuk 100 row yang sudah terfilter tersebut. kira2 caranya bagaimana ya mas? terima kasih banyak sebelumnya:)
kalau kasusnya seperti itu lebih baik Advanced Filternya langsung ditabel saja … langkahnya :
gan saya mau tanya bagaimana cara mengambil salah satu isi sel yang difilter untuk judul diatas tabel yang di filter