jump to navigation

Tricks Excel: Membuat Grafik Dengan Warna Otomatis (Chart with Automatic Colour) August 8, 2010

Posted by excelux in Tutorials, Tips & Tricks.
Tags: , , , , , , , , ,
trackback

“Assalmualaikum…. mau tanya.
saya sudah membuat suatu program menggunakan grafik. anggaplah data tersebut targetnya 5 dan nilai a=4, b=5, c=6, bagaimana merubah warna grafik secara otomatis, apabila kurang dari target “a”= warna merah, sama dengan target “b”= warna kuning, dan melebihi target “c”= warna hijau. seandanya a=6, b=4, c=5 bagaimana supaya warna grafik tersebut berubah secara otomatis.”

“Assalmualaikum…. I wanna ask.
I made a program (file) using a graph. Let say target of the data is 5, and the value of  a=4, b=5, c=6, how to change the color of the graph automatically, if the value is less than the target (in) “a”= red, equal the target “b”= yellow, more than the target “c”= green. If a=6, b=4, c=5 how to change the color of the graph automatically.”

Beberapa hari lalu ada yang mempost pertanyaan di atas di halaman tanya-jawab, dan saya sudah janjikan akan mencoba menjadikannya bahan post saya terbaru. Jadi ini dia penjelasannya, disertai file contoh untuk lebih memperjelas lagi.

Few days ago, someone posted the above question in QA page, and I’ve promised to make it as new post in this blog. So here comes the explanation with example file to make it clearer.

Kali ini saya membahas trik excel untuk membuat grafik batang biasa tapi warnanya yang tidak biasa. Untuk warna dibuat otomatis, jadi jika pada saat tertentu hasilnya kurang dari target warna di grafiknya menjadi merah, jika sama dengan target warnanya menjadi kuning dan bila lebih dari target warnanya menjadi hijau. Kurang lebih seperti gambar di bawah ini.

This time I will discuss an excel trick to make ordinary bar chart with extraordinary color. When a certain value is less than the target, than the bar color will change into red. If it matches the target than the color turn into yellow, and when it override the target, the color become green. The picture below will show you the illustration of the above case.

Untuk membuatnya ada dua cara yaitu dengan macro dan tanpa macro. Tapi kali ini saya bahas yang mudah saja yaitu tanpa macro. Triknya adalah dengan membuat data dummy seperti gambar di atas di sebelah data yang sesungguhnya. Jadi dibuat 3 data dummy yaitu masing-masing untuk yang lebih dari target, sama dengan target, dan kurang dari taget (tergantung ada berapa kemungkinan warna yang diinginkan).

There are two ways to make that kind of graph, using macro or without using macro. This time I will discuss the trick without using macro. The point of the trick is preparing the dummy data, as you can see from the picture above, next to the real data. There are 3 dummy data that represent less than the target data, equal, and more than the target (you can make more/less than 3 depend on the number of categorization of the value).

Rumus untuk data dummy tersebut adalah =if(realisasi>target,realisasi,0) untuk yang dummy lebih dari target, demikian juga untuk yang lain tinggal mengganti > dengan = dan <.

Formula for dummy data is =if(value>target,value,0) for the dummy of more than target, and so does for equal and less than target, just replace > with = and <.

Setelah data dummy siap, langkah terakhir adalah membuat grafiknya. Sebagai catatan, untuk sumber data yang diambil adalah data dummynya bukan data aslinya. Dengan demikian ada 3 series (tiga warna juga). Nah tinggal diwarnai saja series pertama (lebih dari target) dengan warna hijau, berikutnya kuning, dan terakhir merah). Jadi deh grafik dengan warna otomatis. Karena jika realisasi melebihi target maka yang terisi hanya di dummy lebih dari target lainnya 0 sehingga warna pun hijau. Begitu juga kalau kurang dari target, maka di kolom lebih dari dan sama dengan target nilainya 0 dan yang ada isinya hanya di kolom kurang dari target. Dengan dimikian grafik yang dihasilkan pun otomatis berwarna merah.

After dummy data is ready, the final step is inserting the graph it self. It is just an ordinary graph, but instead of inputting the real data as data source, get the dummy data. So there will be 3 series available in the graph. Just give each series the required color for given condition.

Ini contoh filenya, seperti biasanya saya sediakan 2 link, link yang ga repot (mediafire.com) dan link yang sedikit repot (ziddu.com) jika teman-teman berkenan supaya saya bisa dapet sedikit $$$, he… he… Tapi bukan paksaan lho teman-teman bisa pilih link yang mana aja.

Here is the example of the above trick.

http://www.mediafire.com/file/f4we269adkbd4gt/Template_Grafik_Warna_Otomatis.xls

http://www.ziddu.com/download/11132233/Template_Grafik_Warna_Otomatis.xls.html

Selamat mencoba. Semoga bermanfaat.

Don’t forget, practices make perfect. Hopefully useful.

Comments»

1. Ena Henrayana - March 14, 2011

sebelumnya salam kenal,Mohon maaf saya memberanikan diri untuk bertanya ada yang saya kurang mengerti mengenai rumus itu pak kalau yang di butuhkan target dari 0 s/d 69 warna merah, 70 s/d 79 warna kuning dan 80 s/d 100 warna hijau rumusnya bagaimana?

excelux - March 20, 2011

Ok kalau begitu tabel bantunya ada tiga kolom satu untuk warna merah satu untuk warna kuning dan satu untuk hijau.

Anggap saja datanya di A1 dst ke bawah. maka rumus untuk kolom merah “=if(and(A1>=0,A1=70,A1=80,A1<=100),A1,0)"
Lalu copy ke bawah deh sampai semua data tercover

Setelah itu langkahnya sama buat grafik batang dari tabel bantu tadi, pilih jenis yang stacked. Tinggal beri warna saja sesuai keperluan.

2. day priatna - April 23, 2011

Assalamualaikum.
Mau tanya juga. Saya mengumpulkan data tiap jam dalam satu hari. Lalu data tersebut akan saya tampilkan dalam bentuk grafik single line. Apakah bisa pada nilai maksimum dan minimum yang kita tentukan warna garis grafik akan berubah warna menjadi merah, dan bila nilainya berada diantara max. dan min akan berubah warna menjadi biru secara otomatis. terima kasih

excelux - April 27, 2011

Kalau pakai line bisa tapi agak ribet, paling mudah pakai batang. Coba dikirim contoh datanya saja pak.

3. hery - November 13, 2011

mo tanya nih, klo saya ingin membuat grafik yang mengandung nilai nol, tetapi tidak ingin memunculkannya pada grafik, padahal grafik tersebut perlu “view value” caranya gimana?

4. eboy - January 11, 2012

salam kenal , saya mw tanya apakah data digudang bisa saya buat seperti diatas, karena item barang digudang cukup banyak?

saya berharap anda bisa hubungin saya via email
trimakasih banyak


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 184 other followers

%d bloggers like this: