jump to navigation

Tutorial Excel: Mengambil Data Dari Matriks Dengan Double Lookup May 25, 2013

Posted by excelux in Tutorials, Tips & Tricks.
Tags: , , ,
add a comment

Nah teman-teman, ternyata sudah lama sekali saya tidak memposting suatu artikel di blog ini. Berikut ini ada beberapa pertanyaan yang menanyakan hal yang serupa, jadi saya pasang saja di blog, mungkin teman-teman lain juga memerlukannya.

Excelux-Double Lookup

Nah dari gambar di atas, permasalahan yang terjadi adalah, ada data tabel berbentuk matriks, sebagaimana terlihat pada Tabel Tarif di atas. Lalu bagaimana caranya supaya data tersebut dapat diambil dengan memasukkan input pada data antaran sebagaimana terlihat pada tabel di bawahnya.

Untuk melakukannya, saya menyebutnya menggunakan “Double Lookup”, jadi melakukan lookup 2 kali. Yang pertama untuk melihat ada di baris ke berapa dari tabel matriks, dan yang kedua untuk melihat ada di kolom ke berapa data tersebut. Bisa dilihat pada contoh pasangan yang berwarna kuning dan hijau.

Untuk melakukannya ada dua cara. Yang pertama dengan menggungakan perintah index() dikombinasikan dengan match(), yang kedua bisa menggunakan perintah offset() dikombinasikan dengan match() juga (meskipun sebenarnya offset() bisa lebih sakti karena dia bisa untuk mengambil bukan hanya satu titik tapi satu blok array).

Rumus yang digunakan adalah sebagai berikut (bisa dilihat juga di file yang bisa didownload):

=INDEX($B$3:$D$6,MATCH(B10,$A$3:$A$6,0),MATCH(C10,$B$2:$D$2,0))  –> rumus ini saya gunakan di D10

atau

=OFFSET(A2,MATCH(B11,$A$3:$A$6,0),MATCH(C11,$B$2:$D$2,0),1,1)  –> rumus ini untuk yang di D11

Dua-duanya sama saja bisa diaplikasikan, tinggal di copy paste ke bawah saja. Sedikit penjelasan, perintah index() dan offset() digunakan untuk nanti mengambil suatu cell yang posisinya (baris ke dan kolom ke) bisa di atur. Perintah match() untuk menentukan posisinya, baris dan kolom ke berapa. Makanya ada 2, yang pertama untuk mencari posisi baris ke, sementara yang kedua untuk mencari posisi kolom ke.

Demikian sedikit penjelasan singkat tentang Double Lookup, di bawah ini contoh file yang bisa didownload. Semoga Bermanfaat.

http://www.mediafire.com/download/rjw6d8ue3skwws6/Double_Lookup.xlsx

Advertisements

Kamus Gambar Excel 2007 (Command Reference Guide of Excel 2007) January 20, 2011

Posted by excelux in Aplikasi.
Tags: , , , ,
5 comments

Sebagai pengguna setia Excel 2003, saya sangat malas pindah ke Excel 2007. Saya akui Excel 2007 lebih bagus, tapi repotnya adalah mencari dimana letak menu yang kita butuhkan yang biasanya dengan mudah bisa kita dapat di Excel 2003. Mungkin temen-temen mengalami kesulitan yang sama. Lebih lama nyari tempatnya dari pada mengoperasikannya.

Alhamdulillah microsoft ternyata sudah menyediakan bantuan berupa aplikasi untuk melihat letak menu yang biasa kita gunakan di Excel 2003 pada Excel 2007. Contohnya seperti gambar di bawah ini ketika saya ingin menggunakan “data table”

Jadi tinggal buka file tersebut lalu pilih menu seperti ketika kita memilih di Excel 2003 dan dapatlah keterangan dimana menu itu bisa kita jumpai di Excel 2007. Ini linknya.

http://www.mediafire.com/?955gs28millg1j2

Selamat mendownload, semoga bermanfaat.

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

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

“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.

Tricks Excel: Template Grafik Speedometer (Speedometer Chart Template) August 3, 2010

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

Temans, ini ada trik kreasi bentuk grafik di excel seperti speedometer. Yah buat variasi aja untuk menyajikan laporan biar agak unik dikit. Tanpa macro (VBA) loh…

Dear friends, here is a chart creation trick in excel which looks like speedometer. It is just a variation in providing a unique report. It’s made without macro (VBA), guys. Check it out.

Sebenernya ini adalah piechart biasa, ditambah sebuah gambar speedometer di backgroundnya, ditambah sedikit trik dalam menghitung proporsinya. Plus terakhir sebuah trik melink gambar dari sebuah angka atau tulisan di cell tertentu untuk membuat min, max dan judul grafik. Selengkapnya bisa didownload di file berikut, bila ada pertanyaan silahkan dituliskan di commentnya.

Basically it is a usual piechart, plus a speedometer picture as a background, and a little excel trick in calculating proportion of value in order to shape the chart. Plus the last thing linking a picture from a cell to make min, max and chart title. You can download the file from the link below, feel free to ask question in comment section:

Jadi 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.

I provide 2 links, simpler to download link (mediafire.com) and less simple to download link (ziddu.com, If you let me earn some $$$, he… he…). Don’t worry you can choose any link you want freely.

http://www.mediafire.com/file/rdd4fky754h1432/Grafik_Speedometer.xls

http://www.ziddu.com/download/11054459/Grafik_Speedometer.xls.html

Selamat mencoba. Semoga bermanfaat.

Have a nice try. Hopefully useful.

Excel Tips: Membandingkan Dua Sheets Sekaligus (Comparing Two Sheets at Once) July 28, 2010

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

Beberapa hari yang lalu, jurus yang sudah lama saya simpan ini akhirnya terpakai juga. Kadang kita perlu untuk membandingkan dua sheets di excel sekaligus, baik sheets tersebut terletak di dua file yang berbeda atau malah di satu file yang sama. Kalau letaknya di dua file yang berbeda kita masih bisa mengakalinya dengan mengatur lebar window excel secara manual lalu meletakkannya satu di kiri, dan satu di kanan. Tapi bila yang akan dibandingkan dua sheets dalam satu file lebih repot lagi (bagi yang belum tahu tipsnya) karena by default windownya cuma satu.

Few days ago, the old tricks that I had kept for a long time finally used. Sometimes we need to compare two sheets in excel at once. The sheets could be located in two different files or in the same file. When we found it in the different files we still can handle it by resize the excel window manually and place it in the left and right of our screen. But when they are located in the same file it will raise difficulty (for the one who don’t know this tips) because by default there is only one window.

Misalnya pada gambar di atas kita ingin membandingkan data di Sheets Jakarta dan Surabaya. Bila tampilannya seperti ini maka kita harus klik Sheets Jakarta, cek datanya, lalu klik Sheets Surabaya, cek data lagi, balik lagi ke Jakarta, dan seterusnya. Tentu akan sangat merepotkan. Jadi kita akan gunakan tips ini untuk membuat tampilannya lebih mudah dilihat. Klik Window – New Window. Maka jadilah yang tadinya hanya ada satu window excel sekarang ada dua. Tinggal teman-teman atur yang satu buka Sheets Jakarta dan yang satu buka Sheets Surabaya. Langkah terakhir klik Window – Compare Side by Side With… Nah jadi deh tampilannya enak dilihat seperti gambar di bawah ini.

For example, in the figure above, we want to compare the data in sheets Jakarta and Surabaya. If the view is just like that figure, it will be very troublesome. We need to click sheets Jakarta, check the data, click sheets Surabaya, and check the data again, click sheets Jakarta again, and again, and again. So we need this tips to make the view more comfortable. Click Window – New Window and you will find that now there are two excel window. In the first window open sheets Jakarta and the second window sheets Surabaya. The last step click Window – Compare Side by Side With… And the window become easier to look at just like the figure below.

Lebih gampang bukan ngelihatnya. Selamat mencoba. Semoga bermanfaat.

The view is nicer, isn’t it. Have a nice try. Hopefully useful.