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

Memilih Non-Contiguous Range

Memilih Range Non-Contiguous

Memilih range yang non-contiguous (cell-nya tidak bersebelahan)  tdak semudah memilih range yang contiguous. Perhatikan range pada gambar berikut :

Untuk memilih range A3:A12 yang contiguous, maka Anda bisa memilih cell A3 terlebih dulu kemudian tekan Ctrl+Shift+Down_Arrow (Windows) atau Cmd+Down_Arrow (Mac), maka Anda akan mendapatkan range A3:A12.  Namun kalau cara itu Anda terapkan di Cell B3, maka Anda hanya akan mendapatkan range B3:B6 karena setelah cell B6 cell-cell nya kosong.

Didalam VBA, memilih range yang contiguous seperti pada A3:A12 dapat dilakukan dengan menuliskan code VBA berikut:

Sub pilih_range_contig()
Range(“A3”, Range(“A3”).End(xlDown)).Select
End Sub

Sementara untuk memilih range B3:B12 yang non_contiguous, code di atas perlu di modifikasi sedikit degan menambahkan columns() saja. Columns(1) artinya yang dipilih adalah kolom A, columns(2) artinya yang dipilih adalah kolom B. Dengan demikian codenya menjadi sebagai berikut:

Sub pilih_range_non_contig()
Range(“A3”, Range(“A3”).End(xlDown)).Columns(2).Select
End Sub

Catatan : Columns(i) disini pengertiannya adalah relatif. Columns(1) berarti kolom itu sendiri, columns(2) berarti 1 kolom diseblah kanannya. Bila Anda memiliki range yang horizontal, maka logika yang sama berlaku. Gantikan columns(i) dengan rows(i), Anda akan dapatkan range yang Anda cari. Selamat Mencoba dan Semoga Bermanfaat.

Hasil pemilihan range contiguous

Hasil pemilihan range non-contiguous

 

Hasil pemilihan range non_contiguous kalau dilakukan dengan cara seperti memilih range contiguous

Remove Duplicates

Menghapus Duplikat

Ada orang yang masih bekerja dengan pemikiran yang sangat sederhana untuk menghilangkan cell-cell yang sama dalam satu kolom, yaitu dengan melakukan sortir (ascending maupun descending), kemudian dari cell-cell yang nilainya sisakan satu dan lainnya dihapus. Tidak salah, tetapi lama dan menjemukan.

Kalau Anda cukup sering mengerjakan data yang memerlukan penghapusan cell duplikasi, sebaiknya Anda menggunakan function khusus untuk itu, namanya “Remove Duplicates”. Atau lebih cepat lagi Anda bisa menggunakan beberapa baris code VBA.

Sub remove_duplicate()
Dim rng As Range
On Error GoTo Batal
Set rng = Application.InputBox(Prompt:=”Please Select Range”, Title:=”Pilih Range”, Type:=8)
rng.RemoveDuplicates
Range(“A1”).Select
Batal:
End Sub

Data sebelum di Remove Duplicates, bisa masih acak maupun sudah di-sort

Data sesudah di Remove Duplicates

Program meminta User until memilih range, dan yang dipilih adalah range data yang belum di sortir

Range Data yang dipilih adalah yang sudah di sortir. Hasilnya sama.

Bila yang terduplikasi adalah rows, jangan khawatir, program ini masih tetap berfungsi baik dalam menghapus baris-baris yang sama. Jangan lupa range nya dipilih mencakup semua kolomnya.
Selamat Mencoba Semoga Bermanfaat.

Membuat 12 Sheet Otomatis Dengan Nama Jan,Feb..,Dec

Membuat Sheet Sekaligus Dengan Nama Bulan

Bagi Anda yang bekerja dengan data yang harus dikelola pada 12 sheet sesuai bulan dalam setahun, persingkat pekerjaan Anda dengan membuat 12 sheet secara otomatis dengan nama bulan : Jan,Feb,Mar…. dstnya sampai Dec.

Anda tinggal membuat 3 baris code VBA sbagai berikut.

Sub monsheet()
‘ membuat 12 sheet dengan nama Jan,Feb… sampai Dec.
‘ works fine, has been tested by Ike
For Each mysheet In Array(“Jan”, “Feb”, “Mar”, “Apr”, “May”, “Jun”, “Jul”, “Aug”, “Sep”, “Oct”, “Nov”, “Dec”)
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = mysheet
Next mysheet
End Sub

Jalankan program ini maka ke 12 sheet tersebut langsung tersedia di sebelah kanan sheet Anda yang ada.  Selamat mencoba, Semoga Bermanfaat.

SumSpecial : User Defined Functions

Membuat Sendiri Function Excel

Bila function-function yang tersedia di dalam MS Excel masih belum cukup spesifik bagi Anda untuk melakukan proses data yang sesuai kebutuhan Anda, maka tidaklah sulit untuk membuat sendiri function untuk Excel. Penggunaannya pun sama, tinggal Anda tuliskan tanda “=” diikuti nama function yang Anda buat, kemudian yang dalam kurung () biasanya diisi alamat cell atau range yang akan diproses, tergantung variabel yang diperlukan oleh function tersebut. Bila function tersebut bekerja dengan beberapa variable, maka variable-variable tersebut dituliskan di dalam kurung tersebut, dipisahkan dengan tanda baca koma.

Saya membuat sebuah function sederhana untuk mendapatkan jumlah total dari Uang Muka seperti gambar berikut:

Untuk mendapatkan jumlah Uang Muka pada cell E2, kita memerlukan sebuah variabel kunci yaitu cell-cell yang diawali dengan angka 116 pada kolom B. Perintah yang perlu diberikan kepada function yang akan kita buat adalah: “Jumlahkan semua nilai yang ada pada 2 kolom di sebelah kanan  kolom B – yaitu kolom D, untuk setiap cell pada kolom B yang  3 digit di depannya =116. Saya berikan nama function ini “sumspecial“.

Mari tuliskan kode berikut:

Public Function sumspecial(m As String, n As Integer)
k = Len(m)
Set Rng = ActiveSheet.Range(“B2:B9”)
jumlah = 0
For Each shel In Rng
    If Left(shel.Value, k) = m Then
    jumlah = jumlah + shel.Offset(0, n).Value
End If
Next shel
sumspecial = jumlah
End Function

Variable m dan n yang saya sertakan dalam function ini, m untuk 3 digit awalan yaitu 116, dan n untuk jumlah kolom diseblah kanan kolom B. Dengan demikian cara menggunakan function ini pada kolom E2 adalah :

=sumspecial(116,2).  

Maka hasil yang diperoleh adalah 4,500,000. Demikian juga Anda bisa menghitung jumlah Persediaan Barang pada Cell E6 dengan menuliskan =sumspecial(117,2). Lebih jauh Anda bisa menuliskan di cell E10 =sumspecial(11,2) dan hasilnya adalah jumlah total semua kolom D karena semua cell di kolom B berawalan 11 yatu 7,500,000.

Anda bisa memodifikasi function ini untuk range yang lebih luas. Selamat Mencoba dan Semoga Bermanfaat.

Tentu ada yang bertanya, kenapa susah-susah begitu? Untuk mendapatkan jumlah total di E2 bisa dibuat formula sederhana =sum(D3:D5), selesai. Betul sekali. Namun untuk keperluan jumlah yang range nya lebih luas tentu akan pegel juga memilih range yang sesuai. Cara yang saya lakukan ini hanya salah satu cara saja, banyak sekali cara lainnya. Saya mengitung dengan VBA- Visual Basic for Application for Excel  agar sesuai dengan thema Blog saya, yaitu lebih banyak membahas penulisan code VBA.

Untuk para pemula dalam VBA-Excel, klik disini untuk pengertian Procedure, Function dan Module

Link Data ke List Box – UserForm

ListBox RowSource in Excel for Mac

Excel 2011 for Mac tidak mendukung fitur Rowsource untuk ListBox maupun ComboBox. Tidak seperti Excel for Windows, kalau kita membuat sebuah List Box atau ComboBox dalam userform maka pada property ListBox terlihat ada Rowsource dimana kita bisa mengisi alamat range pada sheet Excel agar tampil otomatis didalam ListBox pada saat program userform di eksekusi.  Bagi pemakai Excel for Mac, tidak usah khawatir karena masih ada cara lain untuk menampilkan data ke dalam ListBox.   Dan cara ini tidak hanya khusus untuk Mac, berlaku juga untuk Windows, hanya perlu menulis beberapa baris code saja.

Misal Anda ingin mengisi Listbox dengan data yang ada di Range(B11:B22) pada sheet 1, maka buat code untuk userform Anda sebagai berikut.

Private Sub UserForm_Initialize()
With ListBox1 For i = 11 To 22
 .AddItem Sheets(“sheet1”).Range(“B” & i).Value
Next i
End With
End Sub

Bila program dijalankan, ListBox anda terihat seperti pada gambar dan untuk melihat sampai ke bulan December Anda bisa scroll ke bawah.

Selamat Mencoba, Semoga Bermanfaat

Membuat Navigator Presentasi Excel

Navigator Presentasi Excel

Menyambung posting saya sebelumnya, kali ini pindah-pindah cell tidak menggunakan shortcut, tetapi menggunakan navigator buatan sendiri yang berupa dropdown menu.

• Buat sebuah userform (userform2, atau Anda bisa memberi nama lain), pasang label dengan tulisan NAVIGATOR.
• Masukkan ke area userform sebuah ComboBox (ComboBox1) dan sebuah CpmmandButton (CommandButton1) dengan cara drag and drop dari ToolBox .
• Berikan nama CommandButton1 dengan “GO”
• Klik kanan area userform1 dan pilih View Code
• Masukkan code berikut untuk mengisi pilihan menu yang sesuai dengan pilihan Anda.  Dalam hal ini saya memberikan 3 pilihan.

Private Sub UserForm_Initialize()
ComboBox1.AddItem “CALCULATION”
ComboBox1.AddItem “DATA”
ComboBox1.AddItem “SUMMARY”
End Sub

• Klik kanan CommandButton1 dan pilih View Code, serta tuliskan code berikut:

• Private Sub CommandButton1_Click()
  pilih = ComboBox1.Value
  Select Case pilih Case Is = “CALCULATION” Application.Goto Sheets(“Calculation”).Range(“A2”), Scroll:=True
  Case Is = “DATA” Application.Goto Sheets(“BR”).Range(“A3”), Scroll:=True
  Case Is = “SUMMARY” Application.Goto Sheets(“Original”).Range(“A2”), Scroll:=True
  End Select
  End Sub

• Perhatikan code diatas, yang memberikan perintah kemana harus pindah sesuai dengan apa yang Anda pilih di CombobBox1.  Bisa pindah dalam sheet yang sama atau bisa pindah ke cell di sheet yang berbeda.
• Untuk menjalankan navigator ini, Anda perlu membuat sebuah makro untuk memunculkan userform1 dengan code sebagai berikut:

• Sub RunUserform()
  userform1.show
  End Sub

• Jalankan makro ini dan hasilnya adalah sebagai berikut

• Misal Anda memilih CALCULATION, kemudian Tekan GO, maka otomatis Anda berpindah ke sheet Calculation di cell A2.
• Bila Anda mau langsung tanpa tekan GO langsung pindah, maka tuliskan codenya di Combobox1 langsung sebagai berikut
• • Private Sub Combobox1_Click()
    pilih = ComboBox1.Value
    Select Case pilih
    Case Is = “CALCULATION” Application.Goto Sheets(“Calculation”).Range(“A2”), Scroll:=True
    Case Is = “DATA” Application.Goto Sheets(“BR”).Range(“A3”), Scroll:=True
    Case Is = “SUMMARY” Application.Goto Sheets(“Original”).Range(“A2”), Scroll:=True
    End Select
    End Sub
• Dengan demikian CommandButton1 sudah tidak diperlukan lagi sekarang.
• Selamt Mencoba dan Semoga Bermanfaat

Presentasi Cantik Dengan MS Excel

Presentasi Cantik dengan EXCEL

Software yang paling umum dipakai untuk presentasi adalah MS Power Point, dan saya yakin Anda pun sudah terbiasa menggunakannya. Ketika Anda akan mempresentasikan laporan dalam bentuk MS Excel, maka langkah yang paling umum dilakukan adalah meng-copy sheet Excel ke dalam halaman Power Point.

Masalah yang muncul adalah hurufnya menjadi sangat kecil agar sheet yang begitu lebar dipaksakan menjadi satu halaman Power Point. Ada yang mencari solusi dengan membagi sheet yang lebar itu menjadi beberapa halaman Power Point, namun presentasi menjadi tidak integrated. Terkadang harus bolak balik halaman untuk menjelasakan keterkaitan antar halaman yang sudah dipecah-pecah itu.

Dalam hal ini saya pribadi memilih presentasi tetap menggunakan Excel, namun dicari cara yang pas dan cantik untuk berpindah dari range satu ke range lainnya dalam sebuah worksheet ataupun ke worksheet yang berbeda sekalipun, tanpa terlihat gerakan menggeser-geser mouse.

Fasilitas MS Excel untuk berpindah dari cell ke cell lainnya adalah melalui menu Goto. Masalahnya fasilitas ini hanya membawa cursor pindah ke cell yang dituju, namun cell tersebut tidak berada dalam posisi di paling kiri atas layar, sehingga untuk menampilkan bagian presentasi harus digeser-geser. Inilah yang membuat orang menghindari presentasi dengan Excel.

Coba lihat gambar dibawah ini. Range presentasi adalah K25:U54 sehingga Anda harus menggeser cursor ke cell K24. Dengan perintah Goto K24, maka hasilnya adalah seperti pada gambar dibawah. Jelas Anda harus mengatur lagi agar K24 berada di posisi paling kiri atas layar agar range presentasi Anda terlihat dengan baik dan jelas. Note : Kalau anda mencoba mungkin hasilnya berbeda karena tergantung dari posisi cursor semula sebelum pindah ke K24.

Solusi saya untuk mengatasi hal ini adalah dengan sebaris macro yang membuat perpindahan ke cell K24 dan langsung berada di posisi paling kiri atas layar seperti pada gambar berikut.

Makro yang saya buat adalah sebagai berikut
Sub Presentasi_1()
Application.Goto Range(“K24”), Scroll:=True
End Sub

Agar makro bisa dijalankan tanpa terlihat dilayar, buatkan shortcut untuk makro ini sehingga langsung bisa di eksekusi dengan shortcut misalnya dengan cmd+opt+k (Mac) atau Ctrl+k (Windows) atau kombinasi yang lainnya, terserah kombinasi apa yang Anda pilih.

Bila makro tersebut Anda jalankan, hasilnya adalah seperti gambar berikut. Cell K24 langsung berada di posisi paling kiri atas layar dan range presentasi Anda langsung terlihat. Untuk pindah ke range lainnya, tinggal mambuta makro beikutnya dengan copy paste. Ganti alamat cell nya dan ganti shortcut nya, maka dalam sekejap Anda pindah ke range lain dengan mulus dan cantik.

Selamat Mencoba dan Semoga Bermanfaat.