Untuk melakukan Filter data pada Excel dapat dilakukan dengan berbagai macam cara, salah satu cara yang paling umum digunakan adalah dengan menggunakan fitur Filter atau Advanced Filter yang dapat diakses melalui ribbon Data ==> tab Sort & Filter ==> klik Filter atau Advanced Filter, seperti gambar di bawah ini:
Tetapi bukan cara tersebut yang akan dibahas pada posting kali ini, posting kali ini akan membahas mengenai cara melakukan filter data berdasarkan suatu kriteria tertentu, dalam pembahasan kali ini hanya akan menggunakan 1 buah kriteria tetapi nanti juga akan dijelaskan bagaimana melakukan filter dengan kriteria lebih dari 1 (multiple kriteria).
Data yang akan difilter terdiri dari 3 kolom yaitu: Nomer, Kategori, dan Keterangan seperti gambar di bawah ini:
Tujuan yang ingin dicapai adalah membuat daftar berdasarkan kategori pada kolom B.
Untuk menyelesaikan kasus ini, setidaknya ada 6 cara yang saya ketahui selain dengan memanfaatkan fitur Advanced Filter bawaan dari Excel.
Misal kriteria ada di cell F1 lalu akan dibuat daftar/list pada cell F4 ke bawah berdasarkan kriteria berupa Vowel atau Consonant, maka untuk solusinya harus dibuat sebuah kolom bantu di cell E4 sampai E27 untuk mendapatkan letak baris data yang sesuai dengan kriteria yang telah ditentukan.
Kolom bantu dibuat dengan cara:
Hasil akhir dari formula ini adalah sebuah Array Data yang tersusun dari atas ke bawah.
Perhatian, tanda pemisah yang saya gunakan adalah titik koma karena regional setting di komputer saya adalah Indonesia, jika anda menggunakan regional setting English maka ubah setiap titik koma di atas menjadi koma.
Alur logika dari formula di atas adalah:
Dari formula yang paling dalam:
If(kriteria , data_jika_kriteria_bernilai_TRUE, data_jika_kriteria_bernilai_FALSE)
jika formula If di atas dimasukkan ke dalam blok warna maka akan menjadi seperti berikut:
If(B2:B27=F1;A2:A27)
perhatikan bahwa nomor data hanya diambil jika kriteria bernilai TRUE, sedangkan jika bernilai FALSE akan diabaikan.
Jika ingin membuat filter data berdasarkan banyak kriteria, maka lakukan modifikasi pada kriteria, conrohnya adalah sebagai berikut:
Tanda * adalah pengganti fungsi AND yang setara dengan perkalian pada Excel.
Sedangkan formula yang paling luar berfungsi untuk menyusun hasil secara Ascending, jika ingin disusun hasil secara Descending maka ubah fungsi Small dengan fungsi Large.
syntax dari Small adalah:
Small(Array , jumlah_data_terkecil)
Mungkin para pembaca ada yang belum paham dengan Array Formula yang digunakan di atas, Array formula adalah formula yang menghasilkan nilai lebih dari 1, untuk membentuk suatu formula menjadi Array harus dengan cara menekan 3 tombol yaitu CTRL Shift Enter sehingga Array Formula juga sering disebut sebagai CSE Formula. Lalu kenapa harus menggunakan Array Formula? Jawabannya karena dalam kasus ini ingin dibuat sebuah daftar dari suatu kriteria tertentu, dan proses membandingkan kriteria di dalam fungsi IF adalah melakukan perbandingan satu data terhadap banyak data, oleh sebab itu dibutuhkan Array Formula.
Setelah diperoleh nomor urut data yang sesuai berdasarkan kriteria, maka untuk mengambil nilai datanya dapat digunakan cara-cara berikut ini:
Untuk Error trapnya pada Excel versi 2007 ke atas bisa menggunakan IfError sedangkan pada versi 2003 ke bawah bisa mengginakan IsErr.
Cara ke-5 adalah dengan memanfaatkan fitur Pivot Table
Cara ke-6 adalah menggunakan VBA (Visual Basic for Application)
Script VBA ditulis dalam sebuah modul VBE adalah dan dipanggil dengan cara menekan sebuah shape (tombol) :
Option Explicit
' -------------------------- '
' Filter Data dengan VBA '
' Code by : Dwint Ruswanto '
' 25 Desemeber 2012 '
' -------------------------- '
Sub FilterData()
Dim HdKrit As Range, Rng As Range, cRg As Range
Dim Krit As String
Dim IdxRow As Long
Krit = Range("f1").Value
Range(Cells(4, 12), Cells(Rows.Count, 12)).ClearContents
Set HdKrit = Range("b1")
Set Rng = Range(HdKrit.Offset(1, 0), HdKrit.End(xlDown))
For Each cRg In Rng
If cRg = Krit Then
IdxRow = Cells(Rows.Count, 12).End(xlUp).Row + 1
Cells(IdxRow, 12).Value = cRg.Offset(0, 1).Value
End If
Next cRg
End Sub
Pembahasan lebih detail mengenai masing-masing fungsi di atas, Pivot Table, dan VBA insya ALLAH akan saya tuliskan dalam suatu postingan yang lain.
File sampel dapat diunduh pada link berikut ini https://www.box.com/s/xa57ld0e7t0w6c2bn51x
Tujuan yang ingin dicapai adalah membuat daftar berdasarkan kategori pada kolom B.
Untuk menyelesaikan kasus ini, setidaknya ada 6 cara yang saya ketahui selain dengan memanfaatkan fitur Advanced Filter bawaan dari Excel.
Misal kriteria ada di cell F1 lalu akan dibuat daftar/list pada cell F4 ke bawah berdasarkan kriteria berupa Vowel atau Consonant, maka untuk solusinya harus dibuat sebuah kolom bantu di cell E4 sampai E27 untuk mendapatkan letak baris data yang sesuai dengan kriteria yang telah ditentukan.
Kolom bantu dibuat dengan cara:
- Melakukan blok mulai dari cell E4 sampai dengan cell E27
- Pada formula bar diketik formula berikut ini: =Small(If(B2:B27=F1;A2:A27);A2:A27)
- Tekan 3 tombol berikut ini: CTRL Shift Enter sehingga akan muncul tanda kurung kurawal di awal dan akhir formula
Hasil akhir dari formula ini adalah sebuah Array Data yang tersusun dari atas ke bawah.
Perhatian, tanda pemisah yang saya gunakan adalah titik koma karena regional setting di komputer saya adalah Indonesia, jika anda menggunakan regional setting English maka ubah setiap titik koma di atas menjadi koma.
Alur logika dari formula di atas adalah:
Dari formula yang paling dalam:
If(kriteria , data_jika_kriteria_bernilai_TRUE, data_jika_kriteria_bernilai_FALSE)
jika formula If di atas dimasukkan ke dalam blok warna maka akan menjadi seperti berikut:
If(B2:B27=F1;A2:A27)
perhatikan bahwa nomor data hanya diambil jika kriteria bernilai TRUE, sedangkan jika bernilai FALSE akan diabaikan.
Jika ingin membuat filter data berdasarkan banyak kriteria, maka lakukan modifikasi pada kriteria, conrohnya adalah sebagai berikut:
If((kriteria_1) * (kriteria_2) * (kriteria_3) * (kriteria_4) *(kriteria_n) , data_jika_kriteria_bernilai_TRUE , data_jika_kriteria_bernilai_FALSE)
Tanda * adalah pengganti fungsi AND yang setara dengan perkalian pada Excel.
Sedangkan formula yang paling luar berfungsi untuk menyusun hasil secara Ascending, jika ingin disusun hasil secara Descending maka ubah fungsi Small dengan fungsi Large.
syntax dari Small adalah:
Small(Array , jumlah_data_terkecil)
Mungkin para pembaca ada yang belum paham dengan Array Formula yang digunakan di atas, Array formula adalah formula yang menghasilkan nilai lebih dari 1, untuk membentuk suatu formula menjadi Array harus dengan cara menekan 3 tombol yaitu CTRL Shift Enter sehingga Array Formula juga sering disebut sebagai CSE Formula. Lalu kenapa harus menggunakan Array Formula? Jawabannya karena dalam kasus ini ingin dibuat sebuah daftar dari suatu kriteria tertentu, dan proses membandingkan kriteria di dalam fungsi IF adalah melakukan perbandingan satu data terhadap banyak data, oleh sebab itu dibutuhkan Array Formula.
Setelah diperoleh nomor urut data yang sesuai berdasarkan kriteria, maka untuk mengambil nilai datanya dapat digunakan cara-cara berikut ini:
- LookUp, formulanya adalah : LookUp(E4;$A$2:$A$27;$C$2:$C$27)
- VLookUp, formulanya adalah VLookUp(E4;$A$2:$C$27;3;0)
- Index, formulanya adalah Index($C$2:$C$27;E4)
- OffSet, formulanya adalah OffSet($C$1;E4;0)
Untuk Error trapnya pada Excel versi 2007 ke atas bisa menggunakan IfError sedangkan pada versi 2003 ke bawah bisa mengginakan IsErr.
Cara ke-5 adalah dengan memanfaatkan fitur Pivot Table
Cara ke-6 adalah menggunakan VBA (Visual Basic for Application)
Script VBA ditulis dalam sebuah modul VBE adalah dan dipanggil dengan cara menekan sebuah shape (tombol) :
Option Explicit
' -------------------------- '
' Filter Data dengan VBA '
' Code by : Dwint Ruswanto '
' 25 Desemeber 2012 '
' -------------------------- '
Sub FilterData()
Dim HdKrit As Range, Rng As Range, cRg As Range
Dim Krit As String
Dim IdxRow As Long
Krit = Range("f1").Value
Range(Cells(4, 12), Cells(Rows.Count, 12)).ClearContents
Set HdKrit = Range("b1")
Set Rng = Range(HdKrit.Offset(1, 0), HdKrit.End(xlDown))
For Each cRg In Rng
If cRg = Krit Then
IdxRow = Cells(Rows.Count, 12).End(xlUp).Row + 1
Cells(IdxRow, 12).Value = cRg.Offset(0, 1).Value
End If
Next cRg
End Sub
Pembahasan lebih detail mengenai masing-masing fungsi di atas, Pivot Table, dan VBA insya ALLAH akan saya tuliskan dalam suatu postingan yang lain.
File sampel dapat diunduh pada link berikut ini https://www.box.com/s/xa57ld0e7t0w6c2bn51x
Post a Comment