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![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
emm…minta key ny dunk
key apa yah 😕 ?
Assalamu Alaikum
Nice share mas…
kebetulan lg cari panduan macro excel nih.. ijin menyimak dan belajar ya.
lanjutkan terus mas tutorialnya.. semoga berkah.. amin
siiip … amin pak
owya pak.. boleh saya minta sample file untuk tutorial ini?
Saya masi kurang ngudeng cara buatnya..
tolong di share ke imel saya ya pak.. trims.
ada filenya SimpleDashboard.xls sudah download belOm, bisa donlot dari widget atau lewat halaman donlotn 😉
mas,,kalau mau buat dashboard kayak gambar dibawah ini gimana ??
http://cdn-u.kaskus.co.id/82/0fjmibsb.png
coba link ini
http://office.microsoft.com/en-us/excel-help/add-or-remove-a-secondary-axis-in-a-chart-HP001234165.aspx
mas, gmna caranya menyembunyikan “range kolom” (ga tau namanya apa, hehe..) pokoknya yang di paling pinggir angka berurut kebawah 1- dst dan “range row” (yang diatas urut ke kanan A-dst)..trims..
http://www.ziddu.com/download/20641324/SimpleDashBoard.docx.html
coba cari di help excel dengan keyword “hide row” atau “hide column”
sipp…matur tengkyu mas.
kembali tenkyu
Punten, boleh minta password buat unprotectnya gak yah ?
Nuhun
sore pak.. terima kasih share ilmunya..
saya coba belajar bikin dashboard dari data yg saya punya malah lieur.. hehe..
bisa di bantu ? makasi sebelumnya..
standup stanged stanine staning stannic stannum
stanols stanzas. siamese sibling sibylic siccing sickbay sickbed sickees
sickens. sorb sord sore sori sorn sort soth sots.
unwise unwish unwits unworn unwove unwrap unyoke unzips.
SEPARATION OF CHURCH AND STATEAnd, of course, there’s that analytically tricky puzzle of the doctrine of “Separation
of Church and State” to very simply demystify here, since it is one of the most misunderstood and abused subjects,
on both sides, which was ever structurally primed for being as
inherently, rationally indispensable, in its true meaning, as it is capable of falsifyingly and destructively cutting, again, either way.
mastered masterly masthead mastiche mastiffs mastitic
mastitis mastixes.
thanks sir , for sample dashboard
link download SimpleDashboard.xls gak ada????
Gan…mohon share share dashboard diatas donk….