Edward SII Teknik Sipil: Excel penghitung anggaran (Excel budget calculator)

Ms. excel icon

Apakah anda sering pake Ms Excel? Dan siapa sih yang nggak kenal anggota keluarga besar Ms Office yang satu itu. Apalagi sebagai manusia yang sehari-hari beradu dengan angka, angka, dan angka, tentu Ms Excel termasuk salah satu aplikasi favorit. Dimana Ms excel juga sering dipake oleh para consultan teknik loe...,,, dimana Excel sering dipak-e untuk menghitung jumlah suatu daerah, menghitung RAPBD, dan masih banyak lagi.

Gag man, bukan gue mau curhat atau nulis artikel?

Hehe.. baiklah. Kembali ke topik. Kita tentu udah kenal dengan fungsi-fungsi yang ada di MS Excel. Mulai dari fungsi-fungsi yangsering dipake sampe fungsi-fungsi yang nggak pernah dipake sama sekali. Fungsi-fungsi di Excel itu buaanyak sekali.... ada jutaan trilyun!!

Sorry sob, becanda! sebenarnya cuma ada 239 fungsi default pada MS Excel Professional Edition 2003, gan!

Wah... kurang kerjaan juga ya sampe mau menghitung jumlah fungsi tersebut. Hehehe

. Oke, fungsi apa yang paling sering kita gunakan? Ada MAX, MIN, LOOKUP, VLOOKUP, SORT, IF, AVERAGE dan masih banyak lagi. Tiap-tiap fungsi mempunyai tugas yang berbeda-beda. Kalo mau lihat daftar semua fungsi Excel, buka aja menu Insert->Function.

Nggak jarang juga kita menggunakan dua atau lebih fungsi Excel untuk menyelesaikan satu problem tertentu. Contoh kecil, kita mau mencari berapa nilai cosinus dari 30 derajat?

Gampang, gan... tinggal ketik "=COS(30)".. enter!

Heh? Trus hasilnya berapa?

0.154251.. gan

Trus kalo pake kalkulator?

0.866...?? Lha...! Kok?

Iya.. karena fungsi COS() pada Excel itu adalah menghitung nilai kosinus dari bilangan radian. Kalo mau menghitung kosinus bilangan dalam derajat, harus dikonversi dulu dengan fungsi RADIANS(). Jadi, fungsi lengkapnya adalah "=COS(RADIANS(30))", hasilnya... insya Allah... 0.866025.

Maksimum Absolute

Nah, di dalam proses analisis dan desain struktur, nggak jarang kita berhadapan dengan permasalahan yang berjudul.... Maximum-Absolute... Yaitu mengambil nilai yang terbesar tanpa memperhatikan tanda positif atau negatif.

Contoh kasus, sewaktu mendesain profil baja, kita harus mencari momen lentur terbesar. Dari analisis struktur, diperoleh kumpulan data momen lentur dari batang tipikal dengan berbagai kombinasi pembebanan, misalnya datanya sbb:
250, 350, 225, -354 ,-245, 349, 298, -189, 198, -79

Kalau kita gunakan fungsi MAX() maka hasilnya adalah 350
Kalau kita gunakan fungsi MIN() hasilnya -354, yang nilai absolutnya lebih besar daripada 300.

Solusinya, kadang kita membuat fungsi kompleks
=IF(MAX(range)>ABS(MIN(range)),MAX(range),MIN(range))
Yang artinya, jika nilai maksimum lebih besar daripada nilai minimum yang diabsolutkan, maka ambil nilai maksimum, jika tidak, ambil minimum.

Bagaimana kalo kita bikin fungsi sendiri saja? Biar lebih gampang. Apalagi kalo fungsi itu sering digunakan. Bagaimana caranya? Simak..

Membuat Fungsi ABSMAX

Fungsi ini kita namakan ABSMAX artinya Absolute Maximum. Perlu diingat, penamaan fungsi sifatnya bebas yang penting belum ada di dalam fungsi standar MS Excel. Mau pake nama fungsi MASBEJOKEREN juga bisa. Tapi, ada beberapa aturan penamaan, misalnya:

tidak boleh pake spasi
harus diawali oleh huruf
boleh pake angka
tidak boleh karakter lain, kecuali underscore (_)
tidak boleh pake kata kunci Visual Basic, misalnya dim, single, then, dll
Itu aja sih..

Next, ikuti saja step-step berikut:

Buka MS Excel. Buka VB Editor, dengan cara :

- Tombol Alt + F11, atau

- Menu Tools -> Macro -> Visual Basic Editor
Di sebelah kiri harusnya ada jendela Project Explorer. Kalau tidak ada coba tekan Ctrl+R atau klik menu View -> Project Explorer
Highlight (sorot) VBAProject (namafile.xls). Di tutorial ini saya pakai namafile Fungsi Khusus.xls. Klik kanan -> Insert -> Module
Module adalah tempat dimana kita menuliskan fungsi. Harus di module? Ya! Tidak ada tawar menawar. Klik ganda Module1 yang baru saja muncul di Project Explorer. Anda akan dibawa ke sebuah layar utama yang masih kosong. Untuk memastikan anda sedang aktif di Module1, perhatikan salah satu dari 4 gejala berikut ini.
Mari kita isi layar putih tersebut dengan kode berikut.
Berikut penjelasannya:
1. ```
Function ABSMAX (R as range, param as Integer)
```
  ABSMAX adalah nama fungsi,
  
  R adalah nama variabel yang tipenya Range. Range adalah obyek pada MS Excel yang berisi satu atau lebih sel. Variabel R boleh diganti dengan yang lain misalnya Jengkol as Range.
  
  param adalah variabel integer. Ini untuk mengatur seandainya nilai yang terpilih adalah nilai negatif, maka apakah negatifnya yang diambil atau nilai absolutnya (positif). Jika 0, ambil nilai aslinya (negatif), jika 1 ambil nilai absolut (positif).
2. Deklarasi variabel. Beberapa programmer kadang mengabaikan bagian ini. Untuk aplikasi atau fungsi yang ringan, deklarasi variabel kadang tidak dilakukan.
  Tapi untuk code yang sangat banyak, deklarasi variabel sangat efektif untuk alokasi memori sehingga aplikasi berjalan tidak terlalu berat.
3. Vmaks untuk menyimpan nilai maksimum,
  
  Vmin untuk menyimpan nilai minimum.
  
  temp1 untuk menyimpan nilai maksimum sementara,
  
  temp2 untuk menyimpan nilai minimum sementara
  
  i dan j adalah indeks untuk looping.
  
  N adalah jumlah sel yang ada pada range R
4. temp1 dan temp2 diisi dengan nilai baris pertama kolom pertama dari range R.
5. Looping untuk mencari nilai maksimum dan minimum, masing-masing di masukkan ke variabel Vmaks dan Vmin.
6. Setelah itu kita bandingkan antara Vmaks dengan Vmin, mana yang paling besar nilainya itulah pemenangnya..
7. Jangan lupa cek variabel param. Fungsinya simpel, kalo memang pemenangnya adalah Vmin, apakah tanda negatifnya mau diikutkan ato nggak.. disitulah fungsi param
Udah... beres.. Jangan lupa simpan. Trus tutup jendela Visual basic, dan kembali ke Excel.

Mencoba Fungsi ABSMAX

Coba masukkan angka-angka yang akan dicari maksimum absolutnya. Lihat contoh di bawah.
Trus, coba ketikkan perintah atau fungsi "=ABSMAX(B1:B10,0)". Lihat hasilnya
Atau, sebagai pembanding, silahkan simak beberapa fungsi di bawah ini

Nah... mudah-mudahan sudah jelas dengan cara pakai fungsi ABSMAX.

Menggunakan Fungsi ABSMAX di File Lain

Nah.. ini ada satu masalah lagi. Fungsi ABSMAX yang barusan kita buat, hanya bisa dipake di file dimana module itu berada.

Jadi, kalo mau dipake di worksheet lain, harus ngetik code lagi, gan? Tjapee deech

Tenang... semua masalah ada solusinya. Salah satu solusi dari masalah ini adalah... iya.. betul... ngetik code lagi. (gubrakkk)

Hehe.. begini nih solusinya.

Buka lagi jendela VB Editor. Seperti biasa... Alt+F11. Cari module yang berisi kode fungsi. Klik kanan, pilih Export File
Ketik namanya, misalnya "fungsiku". Ekstensinya adalah .bas. Jangan lupa lokasi direktori penyimpanannya diperhatikan
Trus, kalo mau dipake di file lain, tinggal diimpor. Buka file lain atau buat file baru. Buka VB Editor. Klik kanan pada nama file di Project Explorer, pilih Import File.
Cari file *.bas yang berisi fungsi tersebut. Selesai. Gunakan seperti biasa