BIKIN DASHBOARD EXCEL SEDERHANA PAKE SCROLLBAR, GRAFIK, VALIDASI DAN FORMULA ARRAY

lagi pengen belajar bikin dahboard excel nih 🙂 , bikin dashboard memang harus disesuEkan dengan informasi yang dibutuhkan. bagiYan utama dari dashboard biYasanya adalah Grafik -chart-. Chart yang saya gunakan kalE ini adalah tipe Stacked Column yang saya kombinasikan dengan Line seperti penampakan skrinsyut berikut

Informasi yang ditampilkan dalam dashboard ini merupakan olahan data dari tabel yang berada pada sheet DATA, tabel ini berisi data penjualan 4 macam barang (A, B, C dan D) yang terdiri dari 3 kolom (Tanggal, Jenis Barang dan Jumlah dalam Unit). beriKot ini skrinsyutnyaah

Grafik akan menampilkan jumlah penjualan harian tiap jenis barang yang digambarkan dalam grafik Stacked Column, sedangkan jumlah total penjualan pada tanggal tersebut digambarkan dengan gafik line. Data yang akan ditampilkan dapat dipilih untuk semua jenis barang atau hanya satu jenis barang saja.  Grafik hanya dapat menampilkan 10 hari yang berurutan saja, untuk memilih tanggal yang akan ditampilkan dapat dilakukan dengan menggeser scroolbar ke posisi tanggal yang diinginkan

dalam gambar pada beberapa bagiYan saya kasi nomor2, beriKot keterangan dari nomor2 tersebOt :

1. Filter Data

filter data ini berguna untuk memilih hanya 1 jenis barang saja yang akan ditampilkan dalam grafik atau semua barang. bagiYan utama dari filter ini adalah Cell M3 yang divalidasi dengan aturan List yang merujuk dalam cell $C$23:$C$27 seperti penampakan berikOt

2. ScrollBar

ScrollBar digunakan untuk memilih tanggal yang akan ditampilkan data2nya pada grafik. ScrollBar ini terhubung ke Cell K23 , untuk mempercantik tampilan isi cell ini saya sembunyikan dengan memberi warna font yang sama dengan warna background cell – lihattulisan ini. Cell ini juga tertutup ScrollBarnya. Nilai dari cell K3 digunakan dalam formula untuk menentukan tanggal pada cell D17.

3. Tabel Data

Tabel data ada dua yaitu tabel untuk series A, B, C dan D serta tabel data untuk Series TOTAL, sebelumnya harus kita tentukan dulu tanggal yang akan ditampilkan berdasarkan Scrollbar. ScrollBar kita pakai untuk memilih tanggal yang data2nya akan tampil dalam grafik. nilai dari ScroolBar yang ada pada cell K3 digunakan dalam formula untuk menentukan tanggal pada cell D17 yang formulanya sebagai berikut

=DATE(2011;1;K23+1)

kemudiYan pada cell sebelah kanannya yaitu E17 kita kasi formula

=D17+1

copykan formula E17 ke cell2 disamping kanannya sampai M17

kita telah mempunyai 10 tanggal yang berurutan pada range D17:M17, Category (X) axis labels dalam grafik akan berisi tanggal2 tersebut, karena format tanggal yang lengkap saya pikir terlalu panjang maka tanggal2 tersebut saya ubah formatnya menjadi “d mmm” sehingga tahun tidak ditampilkan, caranya :

cell D16 isi dengan formula berikOt

=TEXT(D17;”d mmm”)

lalu copykan sampai M16

• Tabel Data untuk series A, B, C dan D,

kita mulai dari cel D24 ,saya gunakan Array Formula kombinasi fungsi2 :IF,OR,SUM. pertama kita cari jumlah penjualan barang A -cell $C24- pada tanggal yang ada pada cell D$17 , isikan formula berikot pada cell D24 :

=SUM((DATA!$B$2:$B$300=$C24)*(DATA!$A$2:$A$300=D$17)*(DATA!$C$2:$C$300))

kemudiyan kita atur filternya, agar data hanya akan ditampilkan pada saat cell filter (M3) berisi “(SEMUA)” -Cell C23- atau “A” -cell C24-, saya pakE fungsi IF dan OR sehingga formulanya menjadi

{=IF(OR($M$3=$C24;$M$3=$C$23);SUM((DATA!$B$2:$B$300=$C24)*(DATA!$A$2:$A$300=D$17)*(DATA!$C$2:$C$300));0)}

jangan lupa pakai Ctrl+Shift+Enter agar array formula dapat berjalan kemudiYan kopikan kesamping dan ke bawah sampai Range DASHBOARD!$D$24:$M$27 terisi semua

• Tabel Data untuk series TOTAL

data untuk series TOTAL saya letakkan pada range D18:M18, formula lebih sederhana seperti formula pada D18 berikot :

{=SUM((DATA!$A$2:$A$300=D17)*(DATA!$C$2:$C$300))}

kopikan sampai ke M18

penampakan akhir dari data tabel akan seperti gambar berikOt ini

4. Grafik

seperti yang sudah saya sebutkan diatas penjualan harian tiap jenis barang digambarkan dalam grafik Stacked Column, sedangkan jumlah total penjualan pada tanggal tersebut digambarkan dengan gafik line. yang perlu diperhatikan adalah lebar masing2 kolom yang ditampilkan harus mengikuti lebar kolom pada tabel data.

grafik terdiri atas 5 series yaitu :

• Series A yang merujuk pada range =DASHBOARD!$D$24:$M$24
• Series B yang merujuk pada range =DASHBOARD!$D$25:$M$25
• Series C yang merujuk pada range =DASHBOARD!$D$26:$M$26
• Series D yang merujuk pada range =DASHBOARD!$D$27:$M$27
• Series TOTAL yang merujuk pada range =DASHBOARD!$D$18:$M$18

Category (X) axis labels =DASHBOARD!$D$16:$M$16

coba anda geser2 ScrollBar dan ganti filternya lalu amati apa yang terjadi pada grafiknya 😉

Bonus : Briptu Norman suka lagu india, tapi kalO sodara suka Excel bisa ceKlik link website orang india ini 🙂

silahkan download filenya SimpleDashboard.xls kayak biasanya 🙂  ….  sekiYan semoga bermanpaat dan MDLMDL………….. 🙂

artikel lain tentang dashboard bisa klik disini

bingung cara downloadnya ?? Baca aja halaman download dengan klik icon di samping

feed , email and my social media