Advanced Filter Excel Template
Quite often Excel is used for working with small databases and spreadsheets with up to several tens (or hundreds) of thousands of rows. Such tools as Pivot Tables, filters and Advanced Filters are used for easy and convenient data analysis.
Advanced Filter allows a data sampling function to be performed from Excel spreadsheets which is similar to SQL-query. Typically, there are two additional rows: the top row that contains a field name (a heading) and the bottom value based on which the data will be filtered. All values can be defined either as a target value or specified in the comparison form: "more" (>100), "less" (<200), or text values, that contain a part of the "*some text*".
For example, we are going to have a look at the spreadsheet with three thousands of rows (sheet "Data") and create the user-friendly form of data filtering (sheet "Filter").
Spreadsheet “Data” berisi kolom dengan data berikut dan saya beri nama “tabelku”:
Date
Year
Month
Country
Category
Product
Count
Price
Total
Data sampling akan diatur berdasarkan:
Year
Month
Country
Category
Product
<!–nextpage–> 1) The list of available fields
Kita gunakan advanced filter untuk menyaring data yang unik yang dilakukan dengan membuka :
Data->Sort &; Filter->Advanced
2. Switch a radio-button to "Copy to another location";
3. The "Criteria range" field should be empty;
4. Select a cell, where data will be copied to "Copy to" misalnya kita akan memfilter dan dikopi ke kolom P baris 9;
5. And select "Unique records only".
6. As a result, we get a list of all values in the field "Country" which we will be sorted alphabetically for more convenience.
Apply the same procedure to the fields "Year" and "Category. Hasil Filter Year, Month, Country, dan Category seperti gambar dibawah:
2) Parameter For Data Filtering
Buat criteria parameter untuk menyaring di sheet Data kolom K, L, M, N, dan O. Tuliskan di K1, L1, M1, N1, dan O1 masing-masing dengan nama Year, Month, Country, Category, dan Product. Lalu tuliskan rumus di K2, L2, M2, N2, dan O2 sebagai berikut:
1. We will use the value of ">0" for the criteria "Year" and "Month", if the concrete field value is not specified:
Kita buat table dengan nama “criteria” pada sheet data. Kolom “Year” dan “Month” dituliskan rumus :
=IF(Filter!D3=””,”>0”, Filter!D3)
2. For the criteria "Country" and "Category" use "*":
3. Pada kolom “Country” dan “Category” dituliskan rumus
=IF(Filter!D5=””,”*”,Filter!D5)
4. for the "Product" field, a target value will be set between symbols "*":
=IF(Filter!D7="","","*"&Filter!D7&"*")
Buat criteria parameter untuk menyaring di sheet Data kolom K, L, M, N, dan O. Tuliskan di K1, L1, M1, N1, dan O1 masing-masing dengan nama Year, Month, Country, Category, dan Product. Lalu tuliskan rumus di K2, L2, M2, N2, dan O2 sebagai berikut:
1. We will use the value of ">0" for the criteria "Year" and "Month", if the concrete field value is not specified:
Kita buat table dengan nama “criteria” pada sheet data. Kolom “Year” dan “Month” dituliskan rumus :
=IF(Filter!D3=””,”>0”, Filter!D3)
2. For the criteria "Country" and "Category" use "*":
3. Pada kolom “Country” dan “Category” dituliskan rumus
=IF(Filter!D5=””,”*”,Filter!D5)
4. for the "Product" field, a target value will be set between symbols "*":
=IF(Filter!D7="","","*"&Filter!D7&"*")
3) Form used for data sampling
Kita buat sheet baru dengan nama “Filter”. Buat seperti gambar dibawah.
Let's create a new "Filter" list with request parameters and prepared data sampling results as it is shown below:
Kita buat sheet baru dengan nama “Filter”. Buat seperti gambar dibawah.
Let's create a new "Filter" list with request parameters and prepared data sampling results as it is shown below:
For the first four parameters we will limit the list of entered parameters by means of the "Data Validation" function. Moreover, it will allow you to use a drop-down list for the entered data:
Untuk Empat parameter pertama kita akan membatasi parameter masukan dengan fungsi “Data validation”. Selain itu, akan mengizinkan anda untuk memakai drop-down list untuk memasukkan data.
Range K5:K7 – define a name: tahun
Range L5:K16 – define a name: bulan
Range M5:K47 – define a name: kontri
Range N5:K8 – define a name: kategori
4) Button for data filtering
Buat tombol button: Developer > insert > ActiveXcontrol(Command Button)
Tuliskan kode berikut ini:
Range L5:K16 – define a name: bulan
Range M5:K47 – define a name: kontri
Range N5:K8 – define a name: kategori
4) Button for data filtering
Buat tombol button: Developer > insert > ActiveXcontrol(Command Button)
Tuliskan kode berikut ini:
Option Explicit
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
' clear old data first
Dim n As Long
n = Cells(Rows.Count, "A").End(xlUp).Row
If n > 9 Then
Rows("10:" & CStr(n)).Delete Shift:=xlUp
End If
With Sheets("Data")
.Select
' apply filter
.Range("A:I").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=.Range("Criteria"), Unique:=False
' select filtered rows
Dim rngFilter As Range
Set rngFilter = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Resize(, 9)
' count number of filtered rows
On Error Resume Next
n = 0
n = rngFilter.SpecialCells(xlCellTypeVisible).Rows.Count
On Error GoTo 0
If n = 0 Then
Sheets("Filter").Select
' skip copying
GoTo skip_copying
End If
' copy selection
rngFilter.Select
Selection.Copy
End With
' paste new data
Sheets("Filter").Select
Sheets("Filter").Range("A10").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Filter").Range("A10").Select
skip_copying:
' remove filter
Sheets("Data").ShowAllData
' table style
Sheets("Data").ListObjects("Tabelku").TableStyle = "TableStyleMedium2"
Application.ScreenUpdating = True
End Sub
Download Contohnya di sini
Semoga bermanfaat.
Sumber : http://www.excely.com/template/advanced-filter.shtml
Semoga bermanfaat.
Sumber : http://www.excely.com/template/advanced-filter.shtml
diposting oleh fitri @ November 26, 2014
0 Komentar
