Create a Dynamic Chart in Excel

Excel Mania, masih ingatkah anda dengan article “Trik Membuat Grafik Menggunakan VBA Excel”?, i.e sebuah artikel sederhana yang mengupas (tentang) tata cara membuat grafik di MS Excel menggunakan fasilitas Visual Basic Editor. Adalah fakta bahwa untuk menciptakan grafik yang interaktif itu tidak harus menguasai syntax-syntax yang rumit. Ada metode/cara lain yang lebih sederhana dengan output akhir yang tidak kalah menarik (dibandingkan VBA, red). “Banyak jalan menuju Roma”, begitu kata pepatah. Untuk itu mari kita tinggalkan sejenak dunia per-VBA-an dan beralih ke dunia per-function-an. Kita gali kembali beberapa formulasi yang terdapat di Microsoft Excel kemudian kita optimalkan untuk membuat dynamic charts yang interaktif. Yuk kita mulai…..

Ada beberapa fungsi yang akan kita gunakan (terkait pembuatan dynamic chart ini, red), yakni; Offset function, Row function, Column function, Address function, Index function dan Indirect function. Sebelum masuk ke inti pembahasan, mari kita bahas secara singkat fungsi-fungsi diatas satu-persatu.

Pertama; Offset function. Fungsi ini pernah saya bahas di artikel sebelumnya. Silahkan merujuk ke sini.

Kedua; Row function dan Column function. Syntaxnya adalah sebagai berikut: ROW(reference) dan COLUMN(reference). Menurut “Excel Help”, reference pada syntax diatas adalah the cell or range of cells for which you want the row or column number, atau dengan bahasa lain reference merupakan cell atau range yang ingin kita ketahui numerical valuenya dari record suatu baris atau kolom. Contoh: =Row(B3), jika kita enter akan menghasilkan numerical value 3. Adapun =Column(B3) akan menghasilkan numerical value 2 (penjelasan detailnya bisa dilihat di Excel Help).

Ketiga; Address function. Syntaxnya adalah sebagai berikut: ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text]). Inputan Row_num dan column_num berupa numeric value (lihat pembahasan Row function dan Column function pada poin kedua, red), [abs_num] juga berupa numeric value (i.e angka antara 1-4 dimana 1 artinya [Absolute] yang ditandai dengan $ pada sisi baris dan $ pada sisi kolomnya e.g $B$1, 2 yang artinya [Absolute row; relative column] yang ditandai dengan $ pada sisi barisnya saja e.g B$1, 3 yang artinya [Relative row; absolute column] yang ditandai dengan $ pada sisi kolomnya saja e.g $B1 dan 4 yang artinya [Relative], yakni tidak ditandai $ pada sisi baris maupun kolomnya e.g B1. Sedangkan inputan [a1] berupa logical value i.e True atau False. Jika [a1] kita isi dengan “True” maka format yang dihasilkan adalah A1 e.g B1, namun jika kita isi dengan “False” maka format yang dihasilkan adalah R1C1 e.g B1 di format A1 sama dengan R1C2 di format R1C1. Adapun [sheet_text] diisi dengan nama Sheet, dan sifatnya optional, tidak mandatory. Anda bisa googling atau berselancar di Excel Help jika ingin mengetahui lebih detail.

Keempat; Index function. Detail syntaxnya kurang lebih sebagai berikut: INDEX(reference,row_num,column_num,area_num). Inputan dari Reference adalah range e.g A1:A20, B1:B5 dll yang berisi record, sedangkan row_num dan column_num berupa numeric value dimana intersection (pertemuan) dari keduanya (pada range yang kita select) akan menghasilkan sebuah record yang kita cari. Inputan area_num adalah numeric value, jika reference yang kita isikan itu hanya satu range (single), maka kita cukup mengisikan numeric value 1 pada area_num ini, namun jika reference yang kita isikan itu lebih dari satu range, maka numeric value yang dimasukkan harus disesuaikan dengan jumlah rangenya dan posisi recordnya. Misal ada 3 buah range i.e range1, range2 dan range3, jika record yang kita cari itu terdapat di range3, maka numeric value yang kita inputkan (pada area_num) adalah 3 dst (silahkan merujuk ke Excel Help untuk penjelasan lebih lanjut).

Kelima; Indirect function. Syntaxnya adalah sebagai berikut: INDIRECT(ref_text,a1). Fungsi INDIRECT menghasilkan beragam tipe data, bisa berupa text, numeric value dll tergantung tipe data apa yang terdapat pada cell yang dituju e.g A1 berisi record: “900”, berarti tipe data dari cell A1 adalah numeric value, jika record A1 berisi: “Old Nakula” misalnya, berarti tipe data dari cell A1 adalah text. ref_text pada dasarnya menunjukkan cell e.g A1, A2, B1 dll meskipun bisa jadi (cell-cell tersebut) dibentuk dari gabungan character dan value e.g “A1” bisa dibentuk dari character A digabung dengan value 1 menggunakan formula =A&1 atau CONCATENATE(A,1). Adapun inputan a1 berupa logical value (lihat pembahasannya pada Address function di poin ketiga, red). Untuk lebih detailnya, rekan-rekan bisa melihat contoh penggunaannya pada Excel Help.

Beberapa waktu yang lalu saya mendownload file dynamic chart di URL berikut: http://peltiertech.com/Excel/Charts/ChartByControl.html, nama filenya: ChartAgainstStandard.zip. Sebenarnya banyak sample chart yang bisa kita pelajari dan eksplor lebih jauh disana, namun kali ini saya hanya akan mengambil satu contoh chart saja i.e chart against standard dengan beberapa modifikasi dan tambahan formulasi versi saya sendiri (silahkan mendownload file aslinya terlebih dahulu pada URL diatas untuk mempermudah pemahaman). Di file dengan nama “Chart Against Standard” tersebut terdapat 4 buah Sheets/Worksheet i.e Test, Hydrogen, Helium, Lithium, dan Beryllium yang berisi data numeric (masing-masing data dimulai dari cell A1 sampai cell B21). Tampilan filenya adalah sebagai berikut:

Pembahasan pertama kita awali dari Combo Box atau List Box. Pada dasarnya kedua form control tersebut sama (khususunya dalam hal pengambilan source data), karenanya saya cukupkan pembahasan ini hanya pada salah satu (dari kedua form control) tersebut saja (saya pilih List Box yang berada dibawah Combo Box. Lihat gambar diatas, red). Klik menu Developer (jika menu tersebut belum muncul di excel, silahkan klik symbol MS Office di pojok kiri atas, klik Word options di pojok kiri bawah, centang Show Developer tab in the Ribbon, kemudian klik OK, maka akan muncul “Developer” di menu Excel, red), lalu klik Insert Control> List Box [Form control] dan klik kanan pada Sheets(“Test”). Maka List Box yang kita butuhkan akan muncul (di file aslinya sendiri sudah ada List Box dan Combo Box, jadi poin ini hanya sebagai pembelajaran saja, red). Ini adalah langkah yang pertama. Langkah yang kedua adalah: Perhatikan kolom N pada gambar diatas [i.e Sheets(“Test”)], ada Cell link dan Input range. Klik menu Formulas>Name Manager>New. Masukkan formulasi ini =OFFSET(Test!$N$3,0,1,COUNTA(Test!$O:$O)-1,1) ke “Refers to”, [penjelasan formulasi seperti ini bisa anda lihat di sini, red) dan ketik RgMaterial pada “Name”. Apa sih kegunaannya?. Inilah fungsi yang akan mengupdate data List Box secara otomatis (a dynamic range function, red). Katakanlah anda ingin menambahkan zat “Kalium” pada Input Range [di kolom O dibawah Beryllium misalnya, red) maka List Box akan memasukkan dan menampilkan seluruh data secara otomatis ke dalam database-nya termasuk zat Kalium yang paling terakhir terupdate. Bagaimana cara mengkoneksikan/memasukkan data-data tersebut ke dalam List Box?, ikuti langkah ketiga berikut. Klik kanan form List Box> Format Control> Control. Pada Input range, ketik RgMaterial, dan ketik $O$1 pada Cell link, pilih Single pada Selection type, kemudian klik OK. Proses ini cukup dilakukan sekali saja. Cell link berfungsi sebagai pengurut record, jika kita select Helium misalnya pada List Box maka Cell link akan menunjukkan angka 2.

Coba perhatikan sample grafik “Chart Against Standard” yang kita download tersebut, setiap kali kita menselect pilihan zat/material yang ada di List Box, content data pada kolom P & Q akan ikut berubah secara otomatis mengikuti pilihan di List Box. Kuncinya dimana sih?, mari kita ambil salah satu formula di cell Q1 sebagai sample yakni:

=INDIRECT("'"&INDEX(RgMaterial,$O$1)&"'!"&ADDRESS(ROW(B1),COLUMN(B1)))

Formula =ADDRESS(ROW(B1),COLUMN(B1)) akan menghasilkan sebuah cell $B$1, yakni dari ADDRESS(1,2,,,,). Adapun =INDEX(RgMaterial,$O$1) menghasilkan salah satu pilihan zat yang terselect di List Box. Jika kita select Helium misalnya, maka hasil yang akan dimunculkan oleh formulasi tersebut adalah Helium. Jika kita gabungkan ke-2 formulasi tersebut menjadi: ="'"&INDEX(RgMaterial,$O$1)&"'!"&ADDRESS(ROW(B1),COLUMN(B1)) maka value yang dihasilkan adalah 'Helium'!$B$1. Lantas apa fungsi INDIRECT pada INDIRECT('Helium'!$B$1)?. INDIRECT ini akan mengambil value dari Sheets(“Helium”) pada cell B1 i.e text “Helium” itu sendiri (silahkan lihat content data pada Sheet Helium, red). Dengan kata lain, jika kita tarik formulasi tersebut hingga ke bawah (i.e dari Q1 sampai Q21 pada Sheets(“Test”)), maka data yang akan tercopy di range tersebut adalah data B1:B21 pada Sheet Helium. Dan itu akan berubah-ubah secara dinamis mengikuti pilihan pada List Box, fahimtum?.. J. Catatan: kita abaikan saja data yang terdapat pada range A1:B21 di Sheet(“Test”) karena tidak terlalu signifikan perannya pada pembahasan kali ini.

Ok lanjut, coba kita klik kanan charts pada Sheets(“Test”) tersebut. Maka akan muncul informasi sebagai berikut:

Awalnya (sebelum saya modifikasi), Series X values =ChartAgainstStandard.xls!$P$3:$P$21, Series Y values =ChartAgainstStandard.xls!$Q$3:$Q$21 (bisa dicheck di file asli yang temen-temen download, red). Namun kemudian saya ubah formatnya menjadi seperti yang terlihat pada gambar diatas (i.e $P$3:$P$21 saya ganti dengan SumbuX dan $O$3:$O$21 saya ganti dengan sumbuY). Apa tujuannya? Tidak lain dan tidak bukan: Automatic data selection, yakni setiap kali ada penambahan record data e.g dari $P$3:$P$21 menjadi $P$3:$P$35 misalnya, maka grafik akan mengupdate sumbu x sesuai dengan penambahan data terbaru i.e menselect record sampai $P$35, begitu juga dengan sumbu y. Tapi sebelum mengganti $P$3:$P$21 dengan SumbuX dan $O$3:$O$21 dengan sumbuY, kita harus mendefinisikan terlebih dahulu SumbuX dan SumbuY tersebut pada Name Manager. Klik menu Formulas>Name Manager>New. Masukkan formulasi ini ke “Refers to”, =OFFSET(Test!$N$3,0,2,COUNTA(Test!$P:$P)-1,1) dan ketik SumbuX pada “Name”. Ulangi langkah yang sama dengan memasukkan =OFFSET(Test!$N$3,0,3,COUNTA(Test!$Q:$Q)-1,1) pada “Refers to” dan SumbuY pada “Name”. Baru setelah itu anda bisa mengganti range $P$3:$P$ dan $O$3:$O$21 dengan SumbuX dan SumbuY. OK, itu saja corat-coret tak berbobot dari saya hari ini, mohon maaf jika ada kekurangan, may it’s beneficial and...have a nice try!..