Simple Dashboard

Saya punya data produk yang dibuat berdasarkan kolom, tiap kolom memuat semua data dan identitas produk yang bersangkutan. Kalau saya ingin mempresentasikan produk demi produk, maka sebaiknya hanya satu kolom yang harus tampak dilayar, sementara kolom yang lainnya tersembunyi. Menggunakan fasilitas “hide” dan “unhide” rasanya terlalu repot dan tidak profesional, apalagi dipresentasikan didepan orang banyak atau bahkan client saya.

Oke, mari kita lihat sebagian – lima kolom data saja sebagai contoh.

Data saya buat pada range B4:G11 pada sheet “DATA” seperti pada gambar. Untuk menampilkan setiap kolom secara individu di layar presentasi, maka saya harus membuat yang namanya “dashboard“. Saya membuat sheet baru dengan nama “DASHBOARD” . Langkah-langkah pembuatan dashboard adalah sebagi berikut ;

• Copy kolom B4:B11 dari sheet DATA ke sheet DASHOBOARD
• Pada sheet DASHBOARD, pada cell C4 buat formula “=Offset(DATA!B4,0,1)”
• Ganti angka 1 yang terletak paling kanan pada formula tersebut dengan alamat absolut sebuah cell, saya memakai cell $C$1.
• Comedian copy formula yang di Cell C1 ke bawah sampan C11

• Ganti nilai Cell C1 dengan angka 4, maka range C4:C11 akan berubah menjadi data Produk-4. Ini artinya formula offset sudah bekerja degan baik.
• Langkah berikutnya adalah menambahkan Spinner Button untuk mengubah nilai Cell C1 agar range C4:C11 berubah sesuai dengan perubahan nilai C1
• Setting property (Windows) at Format Control (Mac) seperti berikut
• Property at Format Control ini memiliki ‘Link Cell”, dan isikan link nya ke cell C1
• Kemudian isikan nilai minimum dan maksimum (mulai dari 1 sampai dengan jumlah kolom produk. Disini saya membatasi hanya samai 5. Bisa dibuat sampai sebanyak-banyaknya)
• Bagi yang belum mengetahui cara membuat spinner button, bisa dilihat pada postingan saya terdahulu. klik disini : Spinner Button
• Untuk menjalankannya, tinggal click panda panah atas atau bawah, maka produk yang ditampilkan sesuai dengan nomor yang keluar pada cell C1.

Selamat mencoba dan semoga bermanfaat

Ketut Wiryadinata

Dynamic Chart

Sudah beberapa minggu saya mencoba menemukan agar dynamic chart bisa bekerja baik di Excel 2011 Mac maupun di Excel for Windows. Kali ini, di hari pertama di tahun 2015 saya menuliskannya untuk Anda. Saya membuatnya dengan Excel 2011 Mac, namun sudah saya test dan bekerja baik pada Excel Windows.

Buat table, mislnya data penjualan tahun 2010 sampai dengan 2013 di range Q2:U14 seperti pada gambar, dan berdasarkan tabel tersebut buat Line Chart di sheet yang sama dan buat Bar Chart di sheet yang lain. Dalam hal ini Line Chart saya buat di sheet “Line” dan Bar Chart saya buat di sheet “Column”.

Saya tidak membahas bagaimana cara membuat Line Chart dan Bar Chart, karena pembuatan chart adalah sangat sederhana dan saya yakin Anda sudah bisa membuatnya. Yang saya bahas adalah  bagaimana agar chart yang Anda buat itu bisa menjadi presentasi yang dinamis. Anda bisa memilih secara interaktif hasil penjualan  tahun berapa saja yang ingin ditampilkan dalam satu chart / graph. Check tahun yang ditampilkan, Uncheck tahun yang tidak ingin ditampilkan.

Line Chart yang dihasilkan adalah sebagai berikut :

Line Chart dengan semua tahun ditampilkan (2010-2013)

Line Chart hanya menampilkan 2 tahun (2011 dan 2013, tahun lainnya di hidden)

 

 

 

 

 

 

 

 

 

Untuk membuat Chart Anda interaktif, drag 4 buah CheckBox dari Toolbox dan letakkan dibawah Chart Anda. Akan muncul checkbox dengan nama CheckBox6, Checkbox7.. dst.-nya sampai Checkbox9. Di komputer Anda mungkin namanya mulai dari Checkbox1 dstnya, tetapi tidak jadi masalah. Edit Text nama checkbox dengan 2010, 2011, 2012, dan 2013, kemudian assign macro masing-masing checkbox tersebut. 2010 assign ke Sub CheckBox6_Click(), 2011 assign ke Sub CheckBox7_Click() dan seterusnya.  Macro-macro tersebut adalah sebagai berikut :

Sub CheckBox6_Click()
Application.ScreenUpdating = False
If Sheets(“Line”).Range(“M1”).Value = True Then
    Sheets(“Line”).Columns(“R:R”).Hidden = False
Else
    Sheets(“Line”).Columns(“R:R”).Hidden = True
End If
ActiveSheet.Range(“A1”).Select
Application.ScreenUpdating = True
End Sub

Sub CheckBox7_Click()
Application.ScreenUpdating = False
If Sheets(“Line”).Range(“N1”).Value = True Then
    Sheets(“Line”).Columns(“S:S”).Hidden = False
Else
    Sheets(“Line”).Columns(“S:S”).Hidden = True
End If
ActiveSheet.Range(“A1”).Select
Application.ScreenUpdating = True
End Sub

Sub CheckBox8_Click()
Application.ScreenUpdating = False
If Sheets(“Line”).Range(“O1”).Value = True Then
    Sheets(“Line”).Columns(“T:T”).Hidden = False
Else
    Sheets(“Line”).Columns(“T:T”).Hidden = True
End If
ActiveSheet.Range(“A1”).Select
Application.ScreenUpdating = True
End Sub

Sub CheckBox9_Click()
Application.ScreenUpdating = False
If Sheets(“Line”).Range(“P1”).Value = True Then
    Sheets(“Line”).Columns(“U:U”).Hidden = False
Else
    Sheets(“Line”).Columns(“U:U”).Hidden = True
End If
ActiveSheet.Range(“A1”).Select
Application.ScreenUpdating = True
End Sub

Sebelum bisa dijalankan, masing-masing CheckBox harus di link ke cell tertentu, cell mana saja Anda bebas meilihnya. Dalam hal ini saya memilih link 2010 di M1, 2011 di N1, 2012 di O1 dan 2013 di P1 sesuai dengan yang say tuliskan pada macro masing-masing checkbox.

Dengan cara yang sama, BarChart yang Anda buat di sheet lain bisa di berikan CheckBox dibawahnya dengan meng copy-paste dari yang sudah Anda buat sebelumnya  Line Chart. Hasilnya akan tampak sepert pada gambar berikut:

Selamat Mencoba, Semoga Bermanfaat.

Happy New Year 2015. Wish you a joyful, bright, healthy, prosperous and happiest new year ahead.

Ketut Wiryadinata