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