Excerpt for Optimalisasi Fungsi Excel by , available in its entirety at Smashwords

Mudah & Tepat – Optimalisasi Fungsi Excel


Kata Pengantar

Alhamdullillah, segala puji bagi Tuhan Yang Maha Raja Penguasa Semesta, buku yang merupakan “sisa-sisa” penguasaan macro Excel sejak lima tahun lampau ini akhirnya dapat terselesaikan.

Terima kasih kepada mantan rekan Scomptec Surabaya era 2001-2002 yang telah sedikit banyak memberikan inspirasi secara tidak langsung saat buku ini masih berupa modul workshop kecil, “gus” Dur, Beni “empus”, Erwien DF, Adi Rahman, Novirnawaty dan mbak Ambar. Begitu juga untuk para mahasiswa bimbingan TA di STIKOM yang sudah “menghidupkan” lagi semangat dan banyak ide baru untuk menulis ulang buku ini, Suzanna Dewi, Deny, Ratna, David dan juga untuk bu Sulistiowati yang secara tidak langsung pula menawarkan banyak solusi di dalam penyelesaian dengan menggunakan Excel, terutama saat pengerjaan proposal hibah PHK 2007.

Masih banyak “lubang” di banyak bagian buku ini yang masih dirasa kurang. Terutama di saat menyusun contoh latihan studi kasus, yang sedikit banyak diambil dari beberapa kasus nyata yang difiktifkan. Tapi, tetap diharapkan agar buku ini bermanfaat bagi para praktisi IT, terutama yang intens menggunakan Microsoft Excel. Karena meski banyak orang yang menganggap Excel hanya sekedar aplikasi spreadsheet biasa, tetapi didalamnya mengandung potensi besar yang dapat dimanfaatkan untuk pekerjaan yang seringkali dianggap kompleks.

Selamat berkarya !!!


Candi, Sidoarjo

Rabiuts Tsani 1428 H / Mei 2007

Daftar isi

PENDAHULUAN


Frame1



Microsoft Excel saat ini merupakan aplikasi spreadsheet terpopuler di Indonesia. Hal ini disebabkan oleh banyak faktor diantaranya yaitu : kemudahan serta fleksibilitas Excel dalam mengolah berbagai bentuk laporan spreadsheet, dan tentu saja kelengkapan fungsi built in yang belum tertandingi oleh aplikasi spreadsheet yang lain.

Banyak orang masih beranggapan bahwa Excel hanyalah sebuah aplikasi spreadsheet biasa yang hanya bisa ditempeli dengan rumus-rumus yang sudah tersedia. Sehingga seringkali para penggunanya kerepotan untuk mengatasi berbagai macam kasus yang tidak bisa diselesaikan oleh rumus yang telah disediakan oleh Excel. Anggapan yang salah tersebut ternyata masih berkembang di banyak pihak, hanya karena kebanyakan dari para praktisi komputer yang tidak mengenal cara pembuatan user defined function atau fungsi buatan sendiri dengan menggunakan bahasa Visual Basic for Application yang telah tersedia dalam Excel.

Visual Basic for Application ? Mungkin sebagian besar orang hanya mengenal bahasa pemrograman Visual Basic yang lebih banyak digunakan sebagai pemrograman untuk database. Tapi, Visual Basic for Application ? Bahasa yang satu ini memang satu keluarga dengan Visual Basic, tetapi khusus hanya ada di dalam aplikasi, terutama Office seperti : Word, Access, Power Point dan tak ketinggalan pula Excel. Selain ada di aplikasi Office Visual Basic for Application (VBA) juga terdapat di beberapa aplikasi lain seperti Microsoft Project, Visio dan Corel Draw.

Buku ini tidak hanya memfokuskan pada Visual Basic for Application untuk kepentingan Excel, tetapi juga membahas banyak tentang pemanfaatan form dalam sheet dengan memanfaatkan fungsi – fungsi yang telah ada dalam Microsoft Excel. Sehingga diasumsikan bahwa para pembaca buku ini sebelumnya telah menguasai aplikasi Excel secara interaktif dengan baik. Pada buku ini lebih banyak menggunakan metode learning by doing yang akan mengajarkan sintaks dan obyek melalui contoh dan latihan yang disertakan, bukan dengan menerangkan kegunaannya secara teoritis. Dan bukan tidak mungkin juga bisa dijadikan dasar untuk belajar VBA untuk aplikasi lain seperti Word dan Access.

Aggregasi Berkondisi


Frame2

Fungsi aggregasi dalam Microsoft Excel yang sering dikenal oleh kebanyakan pengguna umumnya adalah fungsi – fungsi dasar seperti SUM, COUNT, AVERAGE atau fungsi maksimum dan minimum (MAX dan MIN). Tetapi bagaimana jika sebuah range cell yang akan dijumlahkan memiliki kondisi tertentu yang harus ditampilkan tanpa harus menggunakan berbagai teknik rumit seperti penggunaan VBA (atau seringkali disebut sebagai Macro Excel).

Dalam kasus tersebut, yang dibutuhkan adalah fungsi aggregasi berkondisi seperti halnya SUMIF, COUNTIF atau juga penggabungan beberapa fungsi aggregasi dasar dengan menggunakan array formula.

SumIF


Format dari fungsi SUMIF adalah SUMIF(range yang akan dijumlah (termasuk dengan kolom yang akan menjadi kondisi), kriteria penjumlahan (bisa berupa kondisi langsung atau referensi cell), range yang berisi angka yang akan dijumlahkan.

Latihan

Buat penyelesaian dari sheet berikut untuk melakukan perhitungan total berat dan total nilai pengiriman berdasarkan kota tujuan dari sebuah tabel yang ada.



Jawaban

  1. Di dalam cell C11, ketikkan fungsi berikut :

=SUMIF($B$4:$C$8,B11,$C$4:$C$8)

  1. Copykan fungsi tersebut ke dalam cel C12 dan C13

  2. Untuk menghitung total nilai pengiriman, ketikkan fungsi berikut di dalam cell D11 :

=SUMIF($B$4:$D$8,B11,$D$4:$D$8)

  1. Copykan juga ke cell D12 dan D13


Array Formula


Penekanan kombinasi tombol Ctrl+Shift+Enter akan mengakibatkan fungsi dieksekusi sebagai sebuah array formula. Jika saat fungsi dieksekusi dengan penekanan Enter biasa, maka hasil dari fungsi tersebut akan mengabaikan segala kondisi yang telah ditentukan dalam fungsi IF. Saat array formula dieksekusi, maka fungsi secara otomatis akan diapit dengan tanda { }, Array formula dapat diterapkan di berbagai fungsi yang memiliki kondisi di saat pemrosesan fungsi tersebut.

Dalam penyelesaian ini, penggunaan array formula berbeda dengan penggunaan fungsi SUMIF. Pemilihan penggunaan array formula dengan fungsi aggregasi IF (SUMIF atau COUNTIF) didasarkan pada kompleksitas kondisi yang diterapkan dalam pencarian sebuah nilai. Dalam kasus ini penggunaan array formula lebih didasarkan pada dua jenis kondisi yang harus dipenuhi, yaitu tanggal serta jenis kue (dalam Microsoft Excel 2007, penggunaan dua kriteria telah dapat dipenuhi dengan fungsi SUMIF yang baru tanpa harus menggunakan array formula.



Latihan

Selesaikan sheet berikut untuk menghitung total penjualan baik dari segi jumlah maupun dari segi nilai. Dari sheet tersebut, total didapat dari dua buah acuan yaitu dari tiap tanggal dan jenis kue itu sendiri.

Jawaban

  1. Ketikkan fungsi berikut di dalam cell B16, dan untuk mengakhiri pengetikan fungsi gunakan kombinasi tombol Ctrl+Shift+Enter (ingat, bukan tombol Enter biasa), karena fungsi berikut akan menghasilkan sebuah array formula :

=SUM(IF($B$4:$B$11=$B$14,

IF($A$4:$A$11=A16,$D$4:$D$11,0),0))

  1. Copykan ke cel B17 hingga B20


  1. Kini untuk menghitung total nilai penjualan, ketikkan fungsi berikut di cell C16 dan tetap diakhir dengan kombinasi Ctrl+Shift+Enter :

=SUM(IF($B$4:$B$11=$B$14,

IF($A$4:$A$11=A16,$E$4:$E$11,0),0))

  1. Copykan ke cell C17 hingga cell C20.

  2. Untuk selanjutnya, ketikkan fungsi berikut (dan copykan ke cell yang berkaitan dibawahnya) :


    1. Cell D16 (jumlah penjualan Lemper) :

=SUM(IF($B$4:$B$11=$D$14,

IF($A$4:$A$11=A16,$D$4:$D$11,0),0))

    1. Cell E16 (nilai penjualan Lemper) :

=SUM(IF($B$4:$B$11=$D$14,

IF($A$4:$A$11=A16,$E$4:$E$11,0),0))

    1. Cell F16 (jumlah penjualan Sosis solo) :

=SUM(IF($B$4:$B$11=$F$14,

IF($A$4:$A$11=A16,$D$4:$D$11,0),0))

    1. Cell G16 (nilai penjualan Sosis solo) :

=SUM(IF($B$4:$B$11=$F$14,

IF($A$4:$A$11=A16,$E$4:$E$11,0),0))


Indeks Cell Relatif


Fungsi OFFSET digunakan untuk mengarahkan cell ke posisi tertentu dengan langkah baris (rows) dan kolom (column) tertentu. Sedangkan fungsi ROWS digunakan untuk menghitung jumlah baris dalam sebuah range cell. Fungsi MATCH digunakan untuk menampilkan posisi relatif dari sebuah array cell yang diblok oleh pengguna.


Latihan



Jawaban

  1. Untuk menyelesaikan soal tersebut, dengan menggunakan kondisi majemuk yaitu bulan dan jenis kue yang disebutkan, maka diperlukan sebuah deret angka bantu yang diketikkan antara cell A21 hingga cell F21.

  2. Kini ketikkan fungsi berikut di dalam cell B16 dan diakhiri dengan penekanan kombinasi tombol Ctrl+Shift+Enter :

=SUM(IF(MONTH($A$4:$A$12)=A$21,

OFFSET($A$3,1,MATCH($A16,$B$3:$E$3,0),

ROWS($A$4:$A$12),1),0))

  1. Selanjutnya copykan fungsi tersebut ke cell C16 hingga F16, lalu ke seluruh area yang ada di tabel total penjualan kue.





Latihan

Jawaban

  1. Yang harus dilakukan pertama kali adalah melakukan data validation dengan memilih menu Data, Validation di dalam cell B3, kemudian di dalam kotak dialog set seperti pada gambar berikut ini :

  1. Selanjutnya, di dalam cell B4, set juga data validation seperti pada kotak dialog berikut :



  1. Kemudian di dalam cell D3, ketikkan fungsi berikut untuk menampilkan jumlah penjualan kue dalam bulan tertentu dengan kategori kue pada cell B4 :

=INDEX(A6:E15,MATCH(B3,A6:A15,0),MATCH(B4,A6:E6,0))

  1. Terakhir, di dalam cell D4, ketikkan fungsi berikut untuk menghitung nilai penjualan kue tersebut :

=VLOOKUP(B4,A19:B22,2,FALSE)*D3


SumProduct


Fungsi SUMPRODUCT digunakan untuk melakukan perkalian antar array, dengan mengasumsikan bahwa dua atau lebih array (atau seringkali diasumsikan sebagai sebuah range cell) memiliki dimensi yang sama.

Latihan

Jawaban

  1. Pada perhitungan soal tersebut, umumnya akan menggunakan perkalian dari tiap harga yang didapat dari fungsi VLOOKUP, tetapi dengan penyelesaian tersebut, akan membutuhkan lima buah fungsi VLOOKUP yang dikalikan dengan tiap jumlah bahan baku.

  2. Karenanya digunakan fungsi array formula menggunakan SUMPRODUCT serta fungsi TRANSPOSE.

  3. Ketikkan fungsi berikut di cell G6, dan akhiri dengan penekanan tombol Ctrl+Shift+Enter, lalu copykan ke cell G7 hingga G10 :

=SUMPRODUCT(B6:F6,TRANSPOSE($B$14:$B$18))





Perkalian Kolom Majemuk


Untuk melakukan penjumlahan tertentu dengan kondisi majemuk, dapat juga dilakukan dengan menggunakan fungsi DSUM. Tetapi, agar saat entri kondisi dapat terkontrol, maka dapat diberikan proses Data Validation di dalam cell yang akan dijadikan sebagai kondisi.


Latihan

Jawaban

  1. Klik di dalam cell D21, lalu pilih menu Data, Validation

  1. Selanjutnya, di dalam kotak dialog, berikan setting seperti pada gambar berikut :

  1. Kini klik di dalam cell E21, pilih lagi menu Data, Validation, lalu set property dalam kotak dialog seperti gambar berikut :

  1. Setting terakhir Data Validation di dalam cell F21, dengan setting sebagai berikut :

  1. Jika sudah selesai, maka cobalah untuk mengklik di dalam cell D21, E21 dan F21, dan pilih salah satu item yang ada dalam dropdownlist.

  1. Selanjutnya, ketikkan fungsi berikut, di dalam cell E23 :

=IF(DSUM(A3:E16,D3,D20:F21)>100,

DSUM(A3:E16,E3,D20:F21)*0.1,0)

Lookup Range Majemuk


Fungsi LOOKUP dapat dikombinasikan dengan array formula, sehingga konversi dari nilai huruf ke nilai angka dapat langsung dicari dengan menggunakan pencarian nilai terdekat untuk menghindari penggunaan IF.

Latihan

Jawaban

  1. Perhitungan IP semester atau IPS berdasarkan pada perhitungan konversi nilai akhir ke dalam nilai huruf. Selanjutnya nilai huruf ini akan diberikan bobot seperti pada tabel bantu. Kemudian, tiap bobot akan dikalikan dengan jumlah SKS dari tiap mata kuliah yang ada. Hasil dari penjumlahan tadi akan dibagi dengan total SKS yang diambil oleh seorang mahasiswa.


IPS =

Jumlah (Bobot x SKS)

Jumlah SKS


  1. Dengan fungsi VLOOKUP, pengerjaan kolom IPS akan membutuhkan setidaknya empat kali fungsi yang dikalikan dengan tabel konversi, ditambah dengan fungsi IF untuk melakukan konversi dari nilai angka ke nilai huruf.

  2. Kini, di dalam cell G5, ketikkan fungsi berikut dengan diakhiri penekanan Ctrl+Shift+Enter :

=SUM((MATCH(LOOKUP(C5:F5,$C$17:$C$21,

$D$17:$D$21),

TRANSPOSE($D$17:$D$21),0)-1)*

TRANSPOSE($D$24:$D$27))/

SUM($D$24:$D$27)


FORM DALAM SHEET


Frame3

Pengenalan Form


Dalam VBA terdapat dua macam form, yang pertama dan akan dibahas dalam bab ini adalah form dalam sheet yang langsung embedded atau ditempelkan dalam sebuah sheet sebagai suatu obyek tertentu. Sedangkan yang kedua adalah userform yang dibuat melalui interface VBA dan akan menjadi sebuah interface form utuh yang menempel dalam sebuah workbook.

Form dalam sheet merupakan sebuah obyek dalam worksheet, seperti layaknya obyek yang lain misal WordArt atau Drawing, maka form dalam sheet dapat diresize ataupun dipindahkan ke lokasi manapun dan mempunyai kemampuan untuk floating atau mengambang di atas sel yang lain.

Untuk menampilkan toolbar yang berisikan obyek-obyek form maka klik menu View, kemudian pilih Toolbars, selanjutnya beri tanda cek pada option Forms. Maka selanjutnya akan muncul toolbar Forms pada worksheet seperti terlihat pada gambar.


Menu untuk membuat toolbar Forms terlihat

Toolbar Forms

Untuk menggambarkan sebuah obyek form dalam sheet, klik salah satu obyek, kemudian gambarkan pada worksheet dengan cara klik kiri kemudian tahan serta drag mouse sebesar ukuran yang diinginkan.

Spinner


Obyek spinner

Obyek spinner pada dasarnya berguna untuk menambah dan mengurangi suatu nilai dari suatu sel referensi dengan penambahan tertentu yang konstan. Secara umum, penggunaan obyek spinner seringkali digabungkan dengan built in function VLOOKUP dan IF. Berikut ini adalah contoh penggunaan obyek spinner dalam form.

Latihan

Buat contoh data seperti pada gambar berikut :

  1. Kemudian arahkan pointer pada obyek spinner, lalu klik kanan dan pilih menu Format Control

Menu Format Control

  1. Setelah itu set property control spinner :

Kotak dialog Format Control

  1. Property Current Value menunjukkan bahwa saat itu spinner dalam posisi di awal nilai, sedangkan Minimum Value menunjukkan nilai minimum yang bisa dicapai oleh spinner, untuk Maximum Value menunjukkan bahwa nilai tersebut merupakan nilai maksimum yang bisa dicapai oleh spinner. Pada soal ini nilai minimum diset menjadi 1 dan nilai maksimum menjadi 5 sesuai dengan tujuan bahwa jika spinner diklik, maka pada isi sel di bawah Barang akan berisi nama barang sesuai dengan yang ada di tabel bantu.

  1. Pada sel A3, ketikkan fungsi berikut :

=VLOOKUP(F13,E7:F11,2)

  1. Lalu coba klik spinner ke atas atau ke bawah, jika nama barang berganti sesuai pergerakan spinner maka lanjutkan ke langkah berikutnya.


  1. Pada sel D3 ketikkan rumus berikut :

=SUMIF(B7:C13,A3,C7:C13)

  1. Pada sheet akan tampak hasil sebagai berikut :

Hasil akhir implementasi obyek spinner

Latihan

Buat sheet seperti pada gambar dengan menggunakan spinner dan memanfaatkan fungsi VLOOKUP dan SUMIF untuk menghitung total penjualan per kue. (Petunjuk : fungsi diisikan pada sel A4 dan E3. Untuk kolom sub total diambil dengan menggunakan fungsi VLOOKUP dari tabel bantu)



Jawaban

  1. Klik kanan pada spinner dan pilih menu Format Control, kemudian di dalam kotak dialog, set seperti pada gambar berikut :

  1. Di dalam cell A4, ketikkan fungsi berikut untuk menampilkan nama kue :

=VLOOKUP(D27,$A$27:$C$30,2,FALSE)

  1. Kemudian di cell D4, ketikkan fungsi berikut untuk menghitung total penjualan tiap kue :

=SUMIF(B6:D23,A4,D6:D23)

  1. Berikutnya format cell D4 dengan format Accounting agar dapat menampilkan satuan mata uang yang sesuai.

Hasil akhir latihan spinner kedua




Combobox


Obyek combobox

Penggunaan obyek combobox hampir sama dengan spinner, tetapi pada combobox (atau seringkali disebut sebagai drop down), bisa dilihat nilai yang dimaksudkan secara langsung dalam obyek tersebut. Sekarang ikuti langkah – langkah untuk mengimplementasikan combobox ke dalam sheet .

Buat penyelesaian untuk sheet berikut

Jawaban

  1. Pada kolom total ketikkan fungsi berikut :

=VLOOKUP(B7,$A$14:$B$16,2,FALSE)*C7

  1. Klik kanan pada combobox, pilih menu Format Control lalu ganti setting pada kotak dialog seperti pada gambar berikut :


  1. Selanjutnya, di dalam cell D4, ketikkan fungsi berikut :

=SUMIF(B7:D11,INDEX(A14:A16,D16),D7:D11)


Hasil akhir


Latihan

  1. Buat sheet seperti pada gambar, untuk menghitung jumlah total pesanan kue, berikut dengan total tagihan untuk tiap tanggal.

Perhitungan tanggal menggunakan tabel temporer tanggal, sedangkan harga dihitung dengan menggunakan fungsi VLOOKUP dan kemudian dihitung dengan menggunakan kolom Jumlah untuk tiap kue di tiap tanggal.

2. Kemudian tempatkan combobox di cell B3 dan set property control pada combobox sebagai berikut

Property dari combobox

  1. Isi kolom harga dengan menggunakan fungsi VLOOKUP di kolom C7, lalu copy ke seluruh kolom harga :

=VLOOKUP(B7,$B$17:$C$19,2,FALSE)

  1. Berikutnya hitung kolom total dengan fungsi berikut :

=D7*C7

  1. Setelah itu masukkan rumus berikut ke sel E3 :

=SUMIF(A7:D14,INDEX(C23:C27,E19),D7:D14)

  1. Untuk Total Tagihan, ketikkan fungsi berikut di sel E4 :

=SUMIF(A7:D14,INDEX(C23:C27,E19),E7:E14)

  1. Jika selesai, maka sheet akan terlihat seperti pada gambar berikut :



Option Button dan Group Box


Obyek group box

Obyek option button

Group box merupakan tempat dari kumpulan option button dalam kaitannya dengan form dalam sheet. Option button sendiri merupakan obyek yang pada konsepnya ditetapkan secara majemuk dan user diharuskan memilih salah satu dari option button tersebut dengan melakukan klik didalamnya. Berikut ini adalah contoh implementasi Group Box yang memiliki option button didalamnya.

  1. Buat sheet dengan layout seperti pada gambar

  1. Kemudian set property pada masing-masing option button sebagai berikut

  1. Setelah itu ketikkan fungsi berikut pada sel D4 kemudian copykan ke baris selanjutnya di kolom yang sama :

=(VLOOKUP(B4;$G$4:$H$7;2;FALSE)+

IF(G16=1;-(VLOOKUP(B4;$G$4:$H$7;2;FALSE)*0,1);500))*C4

4. Jika selesai maka hasil sheet akan tampak seperti pada gambar berikut :

Hasil akhir contoh option button

Latihan

Buat penyelesaian sheet berikut :

Jawaban

  1. Isikan kolom Harga dengan fungsi VLOOKUP berikut di cell C14 :

=VLOOKUP(B14,$B$24:$C$26,2,FALSE)

  1. Sedangkan di cell E14, tempatkan rumus perkalian berikut untuk menghitung total tiap penjualan :

=D14*C14

  1. Set property tiap option button seperti pada kotak dialog berikut :

  1. Berikutnya di cell E3, ketikkan fungsi SUMIF berikut untuk menghitung total kue tiap tanggal :

=SUMIF(A14:D21,INDEX(D23:D27,E26),D14:D21)

  1. Sedangkan di cell E4, ketikkan fungsi berikut untuk menghitung total tagihan tiap kue :

=SUMIF(A14:D21,INDEX(D23:D27,E26),E14:E21)


Latihan

Buat penyelesaian untuk sheet berikut :

Jawaban

  1. Set property pada combobox seperti pada gambar kotak dialog berikut :

  1. Untuk spinner, set property sebagai berikut :

  1. Selanjutnya, di dalam cell D3, ketikkan fungsi VLOOKUP berikut untuk menampilkan nama mata kuliah yang bersangkutan :

=VLOOKUP(F4,A22:B25,2,FALSE)

  1. Kini, di dalam cell C5, ketikkan fungsi VLOOKUP berikut untuk menampilkan nilai akhir dari tiap mahasiswa per mata kuliah :

=VLOOKUP(F5,$A$9:$F$17,F4+2,FALSE)


Latihan


Jawaban

  1. Format property spinner sebagai berikut :

  1. Format property combobox sebagai berikut :

  1. Di dalam cell D11, ketikkan fungsi berikut untuk menghitung total harga pengiriman berdasarkan berat dan kota tujuan yang dimaksud :

=(VLOOKUP(VLOOKUP(INDEX(A12:A20,D18),A12:B20,2,FALSE),$A$6:$B$9,2,FALSE)+1)*D7*B3


Latihan

Jawaban

  1. Set property optionbutton seperti pada kotak dialog berikut :

  1. Set property pada combobox sebagai berikut :

  1. Di dalam cell B6, ketikkan fungsi berikut untuk menampilkan nilai tiap mahasiswa berdasarkan jenis nilai yang dipilih di dalam optionbutton

=VLOOKUP(INDEX(A14:A22,E25),A14:E22,E24+1)

  1. Di dalam cell D6, ketikkan fungsi berikut untuk menghitung nilai akhir tiap mahasiswa berdasarkan persentase tiap jenis nilai yang ada :


=(VLOOKUP(INDEX(A14:A22,E25),A14:B22,2)*B13)+(VLOOKUP(INDEX(A14:A22,E25),A14:C22,3)*C13)+(VLOOKUP(INDEX(A14:A22,E25),A14:D22,4)*D13)+(VLOOKUP(INDEX(A14:A22,E25),A14:E22,2)*E13)




Checkbox


Obyek checkbox

Obyek checkbox merupakan obyek yang hanya mempunyai dua macam nilai yaitu true atau false. Penggunaan checkbox seringkali digunakan dua pilihan yang saling berlawanan (ya atau tidak). Contoh implementasi checkbox :

  1. Buat sheet dengan layout seperti pada gambar

  1. Set property checkbox seperti pada kotak dialog berikut :

  1. Kemudian ketikkan fungsi berikut pada sel C5 , lalu copykan ke baris pada kolom berikutnya :

=VLOOKUP(B5;$B$15:$C$19;2;FALSE)+

IF($E$12=TRUE;15000;0)

3. Jika selesai maka sheet akan tampak seperti pada gambar :

Latihan

Jawaban

  1. Set property checkbox sebagai berikut :

  1. Berikutnya, set property dari optionbutton menjadi seperti pada kotak dialog berikut :

  1. Kini ketikkan fungsi berikut untuk menampilkan total perhitungan harga bahan kue.

=(VLOOKUP($B$3,$A$11:$B$15,2,FALSE)*B4)+

(VLOOKUP($C$3,$A$11:$B$15,2,FALSE)*C4)+

(VLOOKUP($D$3,$A$11:$B$15,2,FALSE)*D4)+

(VLOOKUP($E$3,$A$11:$B$15,2,FALSE)*E4)

  1. Terakhir, ketikkan fungsi berikut untuk menampilkan harga jual sesuai dengan pilihan laba dan diskon dari checkbox dan option button.

=F4/100+IF($G$11=TRUE,-(F4/100*0.1),0)+IF($G$12=1,F4/100*0.05,F4/100*0.1)




Listbox


Obyek listbox

Obyek listbox sebenarnya sama dengan obyek combobox yang bertujuan untuk memilih sebuah nilai dari serial nilai yang telah ada didalam obyek tersebut. Perbedaan yang utama adalah dari segi bentuk listbox yang cenderung membesar dan menampilkan hampir semua serial nilai secara eksplisit. Berikut ini adalah contoh langkah untuk implementasi listbox :

Jawaban

  1. Set property listbox sebagai berikut :

  1. Kemudian ketikkan fungsi berikut di dalam cell C3 untuk menghitung total penjualan tiap kue :

=SUM(OFFSET(A8,1,E20,ROWS(A9:A17),1))*

VLOOKUP(INDEX(B8:E8,E20),A20:B23,2,FALSE)

Latihan

Jawaban

  1. Set property pada listbox seperti pada gambar berikut :

  1. Kemudian, set property pada combobox sebagai berikut :

  1. Selanjutnya, untuk menghitung jumlah total penjualan kue per bulan berdasarkan kue tertentu ketikkan fungsi berikut di dalam cell D5 , dan untuk mengeksekusi tekan kombinasi tombol Ctrl+Shift+Enter (ingat, bukan dengan temobol Enter).

=SUM(IF(MONTH(A10:A18)=E21,OFFSET(A9,1,E22,

ROWS(A9:A17),1)),0)

  1. Lalu untuk menghitung nilai penjualan ketikkan fungsi berikut di cell D6 :

=D5*VLOOKUP(INDEX(A21:A24,E22),A21:B24,2)

Latihan

Jawaban

  1. Set property listbox sebagai berikut :

  1. Selanjutnya, klik kanan pada spinner lalu set property seperti pada kotak dialog berikut :

  1. Di dalam cell C4,ketikkan fungsi berikut untuk menampilkan nama kue :

=INDEX(A22:A27,E27)

  1. Kini di dalam cell D6, ketikkan fungsi berikut untuk menghitung jumlah penjualan kue per kue di tiap cabang dengan menggunakan array formula (menggunakan kombinasi tombol Ctrl+Shift+Enter saat mengakhiri pengetikan fungsi) :

=SUM(IF(B11:B19=INDEX(E22:E24,E26),

OFFSET(A10,1,E27+1,ROWS(A11:A19),1),0))

  1. Terakhir, di dalam cell D7, ketikkan fungsi berikut untuk menampilkan nilai penjualan :

=D6*VLOOKUP(INDEX(A22:A25,E27),

A22:B25,2,FALSE)

USER DEFINED FUNCTION


Frame4

Dalam Excel terdapat dua macam fungsi yang bisa dipergunakan di dalam sebuah sheet. Fungsi yang pertama yaitu built in function yaitu fungsi yang telah disediakan oleh Excel. Sedangkan yang kedua yaitu user defined function yang merupakan rumus atau fungsi buatan sendiri sebagai pengguna Excel. Yang termasuk didalamnya antara lain adalah fungsi matematis umum seperti penjumlahan, pengurangan antar sel, ataupun gabungan dari beberapa built in function dalam satu sel sekaligus.

Pada VBA atau lazim disebut macro dapat membuat tiga macam program yaitu melalui module, user form, dan class module. Module merupakan kumpulan fungsi dan prosedur yang bisa langsung dijalankan dalam sebuah workbook. Sedangkan class module adalah module yang bersifat reusable atau dapat digunakan kembali. Sedangkan yang terakhir adalah User form yang akan menempatkan sebuah form tersendiri yang juga bisa ditempelkan ke dalam workbook yang ada.

Pada bab ini, dibahas mengenai cara pembuatan user defined function yang dilakukan dengan menggunakan bahasa pemrograman Visual Basic for Application (atau selanjutnya disebut dengan VBA).

Pengenalan Fungsi


Fungsi merupakan dasar dari sesi pembelajaran mengenai VBA. Hal ini dikarenakan fungsi merupakan sesi yang paling mudah dari semua sesi pembelajaran VBA serta mencakup semua aspek logika yang ada dalam bahasa pemrograman.

Salah satu ciri dari sebuah fungsi adalah adanya nilai balik yang dihasilkan oleh fungsi tersebut. Dalam VBA Excel, nilai balik yang dihasilkan adalah nilai yang akan berada dalam sel yang kita ketikkan fungsi tersebut. Penamaan fungsi pada Excel maksimal 255 karakter tanpa simbol dan tanda baca, serta harus diawali dengan alfabet dan bersifat incase sensitive (tidak memperdulikan huruf kecil dan besar).

Variabel dan Parameter


Variabel merupakan tempat penyimpan sementara dalam memori untuk sebuah nilai yang tidak diketahui. Dalam VBA, deklarasi sebuah variabel bisa dilakukan secara explicit ataupun secara implicit. Secara explicit, deklarasi variabel menggunakan perintah DIM. Sedangkan secara implicit, nama variabel langsung dideklarasikan dalam sebuah fungsi atau prosedur dengan memberikan variabel tersebut suatu nilai tertentu. Pemberian nama variabel maksimal adalah 255 karakter, harus diawali dengan alfabet dan tidak mengandung tanda baca serta tidak boleh sama dalam satu jangkauan (scope).

Sebuah variabel dapat mempunyai berbagai macam jangkauan (scope). Dalam VBA Excel, jangkauan yang dapat digunakan adalah :

  1. Global

Dapat dikenali di semua file workbook yang sudah terbuka dalam suatu sesi.

  1. Public

Dapat dikenali di semua worksheet yang ada dalam sebuah workbook.



  1. Private

Hanya dapat dikenali dalam jangkauan (scope) yang berlaku saat itu.

Parameter merupakan variabel yang digunakan sebagai umpan balik dalam sebuah fungsi. Dalam sebuah fungsi bisa mempunyai satu atau lebih parameter. Isian parameter dapat berupa sebuah nilai atau referensi alamat sel tertentu.

Berikut ini adalah contoh fungsi yang pertama :

  1. Buat sebuah workbook baru

  2. Kemudian tekan Alt + F11, atau ke menu Tools, Macro, Visual Basic Editor

Menu Visual Basic Editor

  1. Buat module baru, melalui menu Insert, Module.

Menu Insert Module

  1. Pada module ketikkan fungsi berikut :

Public Function TestJumlah(Nilai1, Nilai2)

TestJumlah = Nilai1 + Nilai2

End Function

  1. Kemudian kembali ke worksheet dan buat contoh data berikut :

Contoh Data

  1. Selanjutnya pada sel C1 ketikkan fungsi yang baru tersebut :

=TestJumlah(A1;B1)

  1. Perhatikan hasilnya di sel C1

Hasil dari fungsi pertama


Percabangan Tunggal


Percabangan tunggal pada VBA mempunyai sintaks yang sama dengan percabangan tunggal pada Visual Basic 6.0. Untuk percabangan tunggal dapat diimplementasikan dengan fungsi IF..THEN... ELSE...END IF. Berikut ini adalah contoh fungsi untuk percabangan tunggal.

Latihan

Tujuan dari fungsi berikut adalah mengalikan sebuah nilai numerik dengan angka 100. Fungsi ini dilengkapi pengecekan bahwa nilai adalah bertipe numerik dengan menggunakan sintaks IsNumeric, jika nilai yang dijadikan parameter bukan bertipe numerik maka akan dimunculkan pesan ( messagebox ).

Public Function CobaIF(Nilai1)

If IsNumeric(Nilai1) Then

CobaIF = Nilai1 * 100

Else

MsgBox "Nilai harus berisi angka !", vbCritical, "Salah"

End If

End Function

Jika diaplikasikan ke dalam worksheet yang ada, maka isikan terlebih dulu sebuah contoh data misalkan :

Contoh data untuk fungsi CobaIF

Kemudian ketikkan fungsi berikut pada sel A2 :

=CobaIF(A1)

Apabila fungsi yang diketikkan benar maka sel A2 akan berisi 2400, tetapi andai sel A1 diisi dengan nilai “TEST”, maka akan muncul pesan berikut :

Error Message dari fungsi CobaIF

Latihan

Fungsi berikut ini hampir sama dengan fungsi sebelumnya, tetapi memiliki dua buah parameter dan dua parameter tersebut dijumlahkan satu sama lain.








Public Function CobaIF2(Nilai1, Nilai2)

If IsNumeric(Nilai1) Then

If IsNumeric(Nilai2) Then

CobaIF2 = Nilai1 * Nilai2

Else

MsgBox "Nilai 2 harus berisi " & _

"angka !", , "Salah"

End If

Else

MsgBox "Nilai 1 harus berisi " & _

"angka !", vbCritical, "Salah"

End If

End Function


Untuk melakukan pengecekan kebenaran fungsi, bisa dicoba dengan melakukan tes pada worksheet dengan contoh data berikut :

Kemudian ketikkan fungsi berikut di dalam cell C1 :

=cobaif2(A1,B1)

Kini, coba lagi dengan contoh data yang berbeda :

Dan ketikkan fungsi yang sama di cell C2 :

=cobaif2(A2,B2)

Latihan

Fungsi berikut untuk mengecek nilai yang sudah diinputkan dalam suatu sel, jika lebih besar dari 50 maka akan menampilkan “LULUS”, jika tidak maka akan menampilkan “TIDAK LULUS”

Public Function CobaIF3(Nilai)

If IsNumeric(Nilai) Then

If Nilai >= 50 Then

CobaIF3 = "LULUS"

Else

CobaIF3 = "TIDAK LULUS"

End If

Else

MsgBox "Nilai harus diberi angka !", _

vbCritical, "Salah"

End If

End Function


Untuk melakukan pengecekan fungsi dapat dicoba dengan contoh worksheet berikut :

Selanjutnya ketikkan fungsi berikut di dalam cell B2 (lalu copy ke cell B3 hingga cell B6) :

=cobaif3(A2)


Latihan

Menampilkan nama hari dalam bahasa Indonesia dari suatu isi sel, contoh isi sel adalah 04 April 2007, maka hasil dari fungsi adalah : Rabu.

Jawaban

  1. Ketikkan fungsi berikut di VBA Editor :





Public Function namaHari(tanggal)

Dim xhari() As String

xhari = Split("Minggu,Senin," & _

"Selasa,Rabu,Kamis," & _

"Jumat,Sabtu", ",")

If IsDate(tanggal) Then

namaHari = xhari(Weekday _

(tanggal) - 1)

Else

MsgBox "Tanggal error !"

End If

End Function

  1. Untuk mengecek fungsi gunakan contoh data berikut :

  1. Lalu ketikkan fungsi baru tersebut di dalam cell B1 dan copykan ke seluruh cell yang berkaitan :

=namahari(A1)

Percabangan Majemuk


Percabangan majemuk dapat menggunakan perintah Select Case…End Select. Berikut ini adalah contoh aplikasi percabangan majemuk yang berguna untuk mengkonversi nilai angka ke nilai huruf.

Public Function TestSelectCase(nilai)

Dim xhuruf As String

If IsNumeric(nilai) Then

Select Case nilai

Case Is > 80

xhuruf = "A"

Case Is > 60

xhuruf = "B"

Case Is > 40

xhuruf = "C"

Case Is > 20

xhuruf = "D"

Case Else

xhuruf = "E"

End Select

Else

MsgBox "Nilai harus diisi angka !", _

vbCritical, "Salah"

End If

TestSelectCase = xhuruf

End Function

Untuk melakukan pengecekan fungsi berikan contoh data berikut :

Kemudian ketikkan fungsi berikut di dalam cell dan copykan ke cell yang berkaitan dibawahnya dalam kolom yang sama :

=testselectcase(A2)

Looping


Looping merupakan perulangan yang dilakukan dalam suatu blok perintah selama suatu kondisi yang dimaksud masih dalam keadaan benar. Terdapat dua macam looping yaitu :

    1. Looping terbatas

Merupakan looping yang dapat diprediksi berapa jumlah perulangan yang akan dijalani. Looping ini menggunakan perintah For…Next

    1. Looping tak terbatas

Merupakan looping yang tak dapat diprediksi berapa jumlah perulangannya. Dapat menggunakan perintah Do While…Loop, atau perintah While…Wend.


Latihan

Fungsi untuk menghitung jumlah huruf dari suatu kata atau kalimat ( dengan For..Next )

Public Function TestLooping(kalimat) As Integer

Dim xpanjang, x As Integer

x = 1

xpanjang = 0

Do While x <= Len(kalimat)

If Mid(kalimat, x, 1) <> " " Then

xpanjang = xpanjang + 1

End If

x = x + 1

Loop

TestLooping = xpanjang

End Function

Untuk melakukan testing dari fungsi tersebut, gunakan contoh worksheet seperti pada gambar berikut :

Lalu, ketikkan fungsi berikut di dalam cell B2, dan copykan ke cell B3 :

=testlooping(A2)

Maka, hasil yang didapat adalah sebagai berikut :

Latihan

Fungsi untuk menghitung jumlah huruf dari suatu kata atau kalimat ( dengan Do While…Loop )

Public Function TestLooping2(kalimat) As String

Dim xpanjang As Integer

xpanjang = 0

For i = 1 To Len(kalimat)

If Mid(kalimat, i, 1) <> " " Then

xpanjang = xpanjang + 1

End If

Next i

TestLooping2 = "Jumlah huruf : " & xpanjang

End Function


Array dan Range


Array merupakan sebuah seri variabel yang mempunyai tipe data yang sama. Seri tersebut umumnya ditandai dengan adanya penomoran dalam setiap seri yang disebut sebagai indeks. Dalam hubungannya dengan worksheet, maka sebuah range juga bisa dikatakan sebagai sebuah array yang terdiri dari variabel-variabel yang berupa nilai dari suatu isi sel. Misal :

Berikut ini adalah contoh listing function yang memanfaatkan range sebagai suatu parameter dan memasukkan range tersebut ke dalam suatu array untuk kemudian dihitung.

Fungsi yang dibutuhkan untuk menghitung hasil akhir berdasarkan persentase yang telah ditentukan adalah sebagai berikut :

Public Function nilaiAkhir _

(nilai, persentase) As Double

Dim xnilai(3), xporsi(3), xtemp

xtemp = 0

For Each a In nilai.Cells

xnilai(xtemp) = a

xtemp = xtemp + 1

Next

xtemp = 0

For Each a In persentase.Cells

xporsi(xtemp) = a

xtemp = xtemp + 1

Next

For i = 0 To 3

xhasil = xhasil + (xnilai(i) * xporsi(i))

Next

nilaiAkhir = xhasil

End Function

Testing dilakukan dengan mengetikkan fungsi berikut di dalam cell F5, lalu copykan ke kolom yang ada dibawahnya :

=nilaiakhir(B5:E5,$B$4:$E$4)



Procedure


Procedure berbeda dengan function, sebab tidak membutuhkan parameter ataupun umpan balik dari hasil yang akan dijalankan. Procedure bisa dijalankan dengan menggunakan shorcut keyboard atau dari menu Tools, Run Macro. ( Alt + F8 ) atau dari sebuah komponen form yang ditempatkan dalam sebuah worksheet (umumnya dengan menggunakan Button). Agar procedure tersebut dapat kita akses melalui shorcut keyboard atau dari sebuah icon di toolbar maka kita perlu mengedit property dari sebuah macro tersebut melalui menu Tools, Macro, Macro.

Menu Macro


Kemudian set property dari macro yang kita maksudkan dengan menekan tombol Options :

Kotak dialog macro

Setelah itu beri shorcut kepada macro tersebut :

Kotak Dialog Macro Options

Latihan

Buat sebuah procedure untuk mengganti warna dari sebuah cell dengan warna merah dan latar belakang kuning.

Jawaban

  1. Ketikkan listing berikut pada VBA Editor

Public Sub MemberiWarna()

Selection.Interior.Color = vbYellow

Selection.Font.Color = vbRed

End Sub

  1. Kemudian buat worksheet untuk melakukan pengecekan procedure

  1. Klik kanan pada button, lalu pilih menu Assign Macro dan arahkan ke macro MemberiWarna

Untuk melakukan pengecekan kebenaran macro, letakkan cursor di cell A1 dan klik button, lakukan hal yang sama di cell A2 (atau cell lain yang akan diganti warnanya). Selain cell individual, macro juga dapat diterapkan di sebuah range, dengan melakukan blok terhadap rangkaian cell.

Studi Kasus : Entri Survei


Frame5






Persiapan Awal


Studi kasus yang akan dibahas merupakan sebuah workbook yang akan digunakan untuk melakukan entri data survei dengan mengisikan skor tertentu. Dalam studi kasus ini nantinya akan melibatkan berbagai fitur dari bab – bab sebelumnya yang telah dibahas.

Dalam studi kasus ini akan terdiri dari dua buah worksheet yaitu worksheet pertama yang digunakan untuk melakukan entri survei serta worksheet kedua yang digunakan untuk menyimpan data hasil survei. Di dalam worksheet pertama maupun kedua akan digunakan fungsi aggregasi berkondisi, lookup majemuk sekaligus juga penggunaan macro dari user defined function.

Langkah awal yang harus dilakukan adalah membuat sebuah workbook baru, dan menempatkan dua buah worksheet didalamnya (jika terdapat lebih dari dua worksheet, maka hapus sisa worksheet lainnya). Selanjutnya, beri nama worksheet pertama sebagai Isi Survei dan worksheet yang kedua menjadi nama Data.

Data Pertanyaan


Worksheet Data digunakan untuk menyimpan data pertanyaan awal beserta keterangan dari tiap skor yang akan diberikan dalam proses pengisian survei. Worksheet ini nantinya juga akan disembunyikan saat proses pengisian survei dilakukan, tetapi tetap dapat ditampilkan kembali.

Daftar skor dari pertanyaan (dalam kasus ini diasumsikan dengan menggunakan jawaban dengan skala satu sampai dengan lima), dimasukkan ke dalam sebuah range yang memiliki nama skordata. Sedangkan cell yang akan menampung berapa kali total survei yang telah diinputkan dimasukkan ke dalam sebuah range yang diberi nama Total_Survei. Penamaan tersebut akan digunakan sebagai acuan dari prosedur yang akan dibuat dengan menggunakan VBA.

Selain itu, juga dibuat tabel bantuan sebagai keterangan dari tiap skor yang ada. Tabel bantuan ini akan menjadi acuan dari fungsi lookup untuk analisa jawaban selanjutnya.

Contoh isi pertanyaan dari worksheet data adalah sebagai berikut :

Langkah selanjutnya adalah melakukan pemberian nama range pada skor data. Blok area antara cell B4 hingga F10, lalu pilih menu Insert dan pilih sub menu Range, Define. Kemudian isikan nama skordata dan klik tombol Add untuk menambahkan nama range baru.

Berikutnya adalah melakukan penamaan pada cell yang akan digunakan sebagai counter dari total survei, yaitu di dalam cell B2. Lakukan langkah sebelumnya dan beri nama sebagai range Total_Survei.

Langkah selanjutnya di dalam worksheet ini adalah membuat prosedur untuk menyembunyikan worksheet sementara yang akan diarahkan ke dalam button Hide Data. Prosedur dibuat di dalam Visual Basic Editor dengan nama prosedur hideData.

Untuk membuat prosedur tersebut, buat terlebih dulu sebuah module (lihat lagi bab User Defined Function sub bab procedure), lalu ketikkan listing berikut :

Sub hideData()

Sheets("Data").Visible = False

End Sub

Kini, arahkan prosedur tersebut ke dalam button yang ada. Klik kanan pada button dan pilih menu Assign Macro lalu arahkan ke prosedur yang sudah dibuat.

Langkah terakhir adalah membuat fungsi untuk keterangan simpulan dari tiap jawaban pertanyaan, ketikkan fungsi berikut di dalam cell G4 dan copykan hingga ke cell G10.

=LOOKUP(INDEX($B$3:$F$3,

MATCH(MAX(B4:F4),B4:F4,0)),$F$13:$G$17)

& " sebanyak : " &

(INDEX(B4:F4,MATCH(MAX(B4:F4),B4:F4,0))/

Total_Survei)*100 & "%"

Isian Data


Untuk worksheet yang akan digunakan di dalam isian data survei, buat seperti pada gambar. Yang perlu diperhatikan adalah pengisian pada cell A6 hingga cell A12 yang berasal dari worksheet Data. Pada cell A6, ketikkan fungsi berikut ini, lalu copykan hingga ke cell A12 :

=Data!A4

Selanjutnya di dalam cell B6, berikan nilai nol, lalu tentukan validasi data dengan menggunakan menu Data, Validation dan set seperti pada gambar kotak dialog berikut, lalu copykan hingga ke cell B12 :


Langkah berikutnya adalah memberi nama range untuk entrian skor survei. Blok cell B6 hingga B12, lalu beri nama range tersebut sebagai skodata.

Selanjutnya, klik kanan pada combobox, dan pada kotak dialog Format Control, set seperti pada gambar berikut :

Kini, di dalam cell A20, ketikkan fungsi berikut untuk menampilkan total survei yang telah dilakukan :

="Total survei : " & Total_Survei

Sedangkan untuk mendapatkan keterangan skor pada cell A23, ketikkan fungsi berikut :

="Jawaban terbanyak : " &

INDEX(Data!G4:G10,$G$26)

Sedangkan di dalam cell B26, untuk mendapatkan persentase skor, ketikkan fungsi berikut dan copykan hingga ke cell F26 :

=B25/Total_Survei

Selanjutnya adalah mengetikkan prosedur yang akan digunakan untuk ketiga button yang tersedia. Masuklah ke dalam Visual Basic Editor, lalu ketikkan listing berikut :

Sub submitSurvei()

For i = 1 To Range("skor").Rows.Count

xtemp = Range("Data!skor").Item(i, _

Range("skordata").Item(i)) + 1

Range("Data!skor").Item(i, _

Range("skordata").Item(i)) = xtemp

Next

xtemp2 = Range("Data!total_survei"). _

Item(1) + 1

Range("Data!total_survei").Item(1) = xtemp2

End Sub

Sub resetSurvei()

Range("skordata").ClearContents

Range("skordata").Activate

End Sub

Sub showData()

Sheets("Data").Visible = True

Sheets("Data").Activate

End Sub


Langkah terakhir dalam worksheet ini adalah mengarahkan tiap button ke dalam prosedur yang bersesuaian. Dalam tiga gambar berikut diarahkan masing –masing button yaitu Submit, Reset dan Show.





Download this book for your ebook reader.
(Pages 1-40 show above.)