Rabu, 26 November 2014

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

1. Select the "Year" column di list range: $B$1:$B$3281;
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&"*")

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:


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.
Sebelum itu, definisikan nama untuk list yang diizinkan pada sheet “Data” . Caranya blok kolom yang akan diberi nama > Formula > define name
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:
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

Selasa, 25 November 2014

Indicate sales performance graphically on a dashboard



ketikkan kode berikut ini pada cell C23 :


=ROUND(C10,0) & " " &  IF(C9<C10,"▲", IF(C9=C10,"●","▼"))

The ROUND function is used to reduce a given value to a specific number of decimal places.
The function will round the last number (the rounding digit) up or down depending on the value of the number to the right of the rounding digit.
If the value of the number to the right of the rounding digit is less than five, the rounding digit is left unchanged. Ex: 3.567 akan dibulatkan menjadi 3.6
Jika cell C9<C10 maka beri simbol ▲, IF(C9=C10 diberi simbol ●, selain itu beri simbol ▼. Simbol ini akan ditempatkan setelah nilai hasil pembulatan dengan menggunakan round dan spasi.

Semoga bermanfaat.

sumber: http://chandoo.org/

Extreme In cell Graphs and Gantt Chart

Ketik formula berikut pada cell bidang grafik di kolom D25, lakukan hal yang sama terhadap baris berikutnya dengan klik dan drag. Bilangan pembagi 5 bisa diganti sesuai kebutuhan:

=REPT("|",C25/5)





Membuat Gantt charts

=IF(AND(F$8>=$D9, F$8<=$E9),"1","")

jika cell F8 lebih besar/sama dengan cell D9 dan cell F8 kurang dari/sama denggan cell E9 maka cell F9 bernilai 1 namun jika salah satu dari kedua keadaan diatas tidak terjadi maka cell F9 bernilai 0.
Lakukan hal yang sama pada kolom dan baris yang akan dibuat chart.
lalu, home > klik conditional formating > new rule > select rule type: format only that cell contains > cell value, equal to ="1" dan ganti warna sesuai keinginan misalnya biru.





Semoga bermanfaat.

Sumber: http://chandoo.org


Protect Worksheet in Ms. Excel

Cara untuk memprotect worksheet excel sehingga sheet hanya bisa read only
Buka Ms Excel > rename sheet1=contoh
klik tab developer>visual basic>klik 2x pada worksheet contoh
ketikkan kode berikut di worksheet contoh

Private Sub Worksheet_Activate()
Dim contoh As Worksheet
Dim myPassword As String
myPassword = "password"
For Each Sh In ActiveWorkbook.Worksheets
contoh.Protect Password:=myPassword
Next contoh
End Sub

Agar sheet di excel bisa di edit harus ketikkan kode berikut di lembar module pada visual basic editor:

Sub UnprotectAll()
Dim contoh As Worksheet
Dim myPassword As String
myPassword = "password"
For Each contoh In ActiveWorkbook.Worksheets
contoh.Unprotect Password:=myPassword
Next Sh
End Sub

untuk membuka worksheet yang telah dikunci, setelah menulis coding diatas kita tinggal memanggil fungsi diatas akan mengedit dengan mengetikan coding sbg berikut:

call UnprotectAll


Semoga bermanfaat.

Dashboards in Microsoft Excel

What is a dashboard ?

A dashboard is a visual display of the most important inormation [....] which fits entirely on a single computer screen [...]

(information dashboard design, 2006)


Caranya:
Siapkan data mentah yang akan digunakan dalam spread sheet. Misalnya data mentah dengan nama "data"


Buka sheet baru dengan nama "dashboard" dan buat tabel 10 baris dalam sheet dashboard untuk menampilkan tabel dalam sheet data.


Buat sheet baru dengan nama "calculate"

Insert a scroll bar control dalam cell: developer > insert > form > scroll bar
klik kanan pada scroll bar > tab control
current value : 0
minimum value : 1
maximum value : 91
incremental change : 1
page change : 10
cell link : calculation!$D$6 (nilai actual start position pada sheet calculation)

Setelah selesai buka sheet dashboard, ketikkan formula =OFFSET(data!E7,calculation!$D$6,0,1,1) untuk data product kemudian klik dan drag untuk baris dibawahnya. lakukan hal yang sama untuk Nomor, KPI1, KPI2, dan KPI3. dengan merubah reference "data!E7" dengan data yang kita inginkan.

Download contohnya di sini :Dashboard

sumber: http://chandoo.org/wp/2008/08/27/excel-kpi-dashboard-sort-2/

Senin, 24 November 2014

Mewarnai Baris dan Kolom pada Cell Aktif


    Dim RngRow          As Range
    Dim RngCol          As Range
    Dim RngFinal        As Range
    Dim Row             As Long
    Dim Col             As Long
   
    Cells.Interior.ColorIndex = xlNone
   
    Row = Target.Row
    Col = Target.Column
   
    Set RngRow = Range("A" & Row, Target)
    Set RngCol = Range(Cells(1, Col), Target)
    Set RngFinal = Union(RngRow, RngCol)
   
    RngFinal.Interior.ColorIndex = 6







Senin, 17 November 2014

Trying VBA in MS. Excel

Trying Macro Visual Basic in Excel
Codingnya segera menyusul. Ini sangat memudahkan pekerjaan tidak perlu berulang kali mengetik, hanya tinggal klik. he



download: here