FUNGSI BUATAN PADA MICROSOFT EXCEL

FUNGSI BUATAN PADA MICROSOFT EXCEL

MENGENAL FITUR PEMROGRAMAN VISUAL BASIC APLICATIONS (VBA) MEMBUAT PROGRAM DENGAN VBA MEMBUAT APLIKASI DENGAN MS EXCEL MENGATASI MASALAH KETIKA FUNGSI TIDAK AKTIF

MODUL APLIKASI EXCEL. Kafrawi

0

I. PENDAHULUAN Visual Basic for Application atau VBA adalah sebuah bahasa pemrograman yang dibuat oleh Microsoft dan dapat digunakan untuk meningkatkan kemampuan aplikasi Office, termasuk di dalamnya Excel. Macro adalah kumpulan command dan prosedur untuk melakukan tugas tertentu, disimpan dalam bentuk modul pada file Excel. Macro dapat dipanggil untuk menanggapi suatu kejadian (event) seperti suatu klik pada tombol. Program aplikasi Microsoft Excel dilengkapi dengan banyak fungsi siap pakai, pengguna tinggal mengisikan data dan dapat langsung melihat hasil. Namun adakalanya untuk memecahkan suatu kasus diperlukan fungsi yang harus dikombinasikan. Untuk dapat menggunakan fungsi atau beberapa fungsi sekaligus tergantung pada masalah yang akan dipecahkan. Selain fungsi siap pakai Excel juga menyediakan fasilitas untuk membuat fungsi sendiri atau membuat fungsi buatan. Anda dapat mendefinisikan perintah perintah untuk membuat fungsi sesuai dengan keinginan anda. Excel mempunai fitur pemrograman yang dinamakan Visual Basic for Aplications ( VBA ) yang merupakan kombinasi integrasi antara lingkungan pemrograman Visual Basic Editor dengan bahasa pemrograman Visual Basic. Integrasi tersebut memudahkan untuk mendesain dan membuat program Visual Basic. Pada bahasan ini VBA hanya dapat dibangun pada aplikasi Excel untuk pengendalian fungsi aplikasi melaui berbagai objek terprogram. Kelebihan VBA 1. 2. 3. 4.

Excel akan mengeksekusi semua perintah yang diperintah dalam urutan yang sama Excel akan melaksanakan semua perintah cepat, lebih cepat daripada secara manual Dengan pemrograman yang baik maka perintah2 tadi bisa dibuat tanpa error Bisa melakukan yang tidak bisa dilakukan dengan excel secara manual. Kekurangan VBA

1. Kita harus punya kemampuan untuk mem-program. 2. Jika ingin memakai program VBA yang kita buat harus memiliki software excel yang sesuai versinya 3. Tidak mudah untuk membuat program yang tanpa error 4. Excel selalu berkembang sehingga program yang kita buat harus menyesuaikan dengan perkembangan software excel


1

II. MENGAKTIFKAN TAB DEVELOPER Tab Developer berisi menu-menu menu yang akan kita gunakan untuk bekerja dengan VBA, berikut adalah langkah-langkah langkah untuk mengaktifkan tab tersebut : 1. Jalankan aplikasi Microsoft Excel 2007 2007. 2. Klik menu Office.

3. Klik tombol Excel Options. 4. Pada dialog yang muncul, pilih kategori Popular pada bagian panel kiri. 5. Aktifkan opsi Show Developer tab in the Ribbon. 6. Klik tombol OK. 7. Pastikan tab Developer sudah terlihat pada Ribbon.

A. VISUAL BASIC EDITOR (EDITOR) 1. Klik tab Developer pada Ribbon, dari group Code klik Visual Basic (atau tekan Alt+F11). 2. Akan muncul Visual Basic Editor Editor, yang merupakan

tempat

dimana

kita

memasukkan dan mencoba kode program kita.


2

3. Pada panel kiri atas – bagian project, klik kanan pada Sheet1 dari project dengan nama VBAProject (Book1), (Book1) kemudian pilih menu View Code. Ini akan membuka tampilan editor untuk code kita, yang akan

dikelompokkan

dalam

suatu

module. Ketikkan code berikut pada editor tersebut. PrivateSub HelloWorld() Worksheets(“Sheet1″).Range Range(“A1″).Value = “Hello World” Worksheets(“Sheet1″).Range Range(“C3″).Value = “Hello World” EndSub

Penjelasan : Code ini adalah perintah membuat satu procedure dengan nama HelloWorld, yang

didefinisikan

di

dalam Private Sub … End Sub. 4. Arahkan cursor Anda pada bagian dalam prosedur tersebut dan tekan tombol F5 untuk menjalankan program kita. 5. Untuk menyimpan file gunakan format file tipe xlsm. Perhatikan ekstensi yang disimpan bukan tipe xlsx. File .xlsm merupakan file Excel dimana makro diperbolehkan atau disebut dengan Excel Macro-Enabled Macro Workbook file. 6. Mari kembali ke worksheet kita, Anda akan menemukan bahwa pada Sheet1 sudah terisi kata “Hello World”” pada cell A1 dan C3.

B. MENAMBAHKAN VISUAL CONTROL Control adalah komponen visual / grafis seperti tombol, yang dapat ditempatkan pada worksheet kita sehingga aplikasi kita menjadi lebih interaktif. Berikut adalah contoh penggunaan control : 1. Masih pada Sheet1,, coba arahkan cursor ke alamat E1.


3

2. Pada tab Developer, group Controls, klik gambar panah bawah kecil pada menu Insert. Dari pilihan yang muncul klik Spin Button (Form Control), gambar bentuknya pada sheet kita. Klik kanan pada spinner tersebut, dan pilih Format Control. 3. Pada dialog yang muncul, klik tab Control dan masukkan nilai-nilai berikut, dan klik tombol OK setelah selesai. o

Current Value : 0.

o

Minimum Value : 0.

o

Maximum Value : 200.

o

Incremental Change : 25.

o

Cell link : A2.

4. Cobalah klik gambar panah pada spinner dan perhatikan perubahan nilai yang terjadi pada cell A2.

C. MENYIMPAN DAN MEMBUKA KEMBALI WORKBOOK VBA 1. Tutup dan kemudian buka kembali workbook kita. 2. Secara default, Excel 2007 akan menonaktifkan perintah VBA / Macro pada workbook yang kita buka. Terlihat pada security warning yang muncul pada bar bagian atas worksheet (gambar). 3. Klik tombol Options pada bar tersebut. 4. Pilih opsi Enable this content, klik tombol OK. 5. Anda sudah dapat menjalankan kembali code VBA Anda. 6. Selesai.


4

III.

MEMBUAT PROGRAM DENGAN VBA OTOMATISASI SURAT Membuat surat tidak harus dilakukan dengan mengunakan program Microsoft Office Word. Microsoft Excel dapat juga digunakan untuk membuat surat, seperti pada gambar dibawah ini.


5

LANGKAH LANGKAH MEMBUAT OTOMATISASI SURAT 1. Buatlah surat seperti pada gambar di atas pada sheet 1 dan ubahlah menjadi “Surat”. 2. Buatlah daftar nama yang berisi No Surat, Nama yang akan dituju dan Alamat pada sheet 2 dan ubahlah menjadi “ Data” 3. Buatlah table daftar Surat Keluar, yang berisi Tanggal, Nomor Surat, Nama, Perihal pada sheet 3 dan ubahlah menjadi “ Surat_Keluar”. 4. Pilih sheet Data dan sorot range nama, lalu beri name box: “Nama” 5. Pilih sheet Surat_Keluar, klik pada kolom Nomor Surat, klik sel D3 pada gambar dibawah

ini.

Masukkan

rumus

=IF(ISBLANK(C3);”

”;1+COUNT($C$2:C2)

&”/”&”SU”&”/”&TEXT(NOW( );”YYYY”)) tekan enter, copy ke bawah.

Penjelasan Rumus: Jika sel C3 bernilai kosong maka hasilnya adalah kosong, jika sel C3 tidak kosong maka buat nomor urut surat yang berasal dari nilai 1 ditambah jumlah data di kolom tanggal ditambah karakter / SU/ tahun saat ini.

6. Klik sel E3, pada sheet Surat_Keluar aktifkan tab Data pilih Data Validation > Data Validation

Akan muncul gambar seperti di bawah ini :

Pilih list

Ketik = Nama lalu OK !

Kembali ke sel E3, copy ke bawah MODUL APLIKASI EXCEL. Kafrawi

6

7. Pilih sheet Surat aktifkan Tab Developer, kik tombol insert pilih ScrollBar ( Form Control. 8. Buatlah tombol scrollbar seperti pada gambar dibawah ini 9. Klik sel C1 pada sheet Surat, beri name box Link1

10. Klik kanan tombol ScrollBar pilih format Control, akan tampil :

1. Ketik : 3 2. Ketik : 3 3. Isi dengan sel $C$1 4. Aktifkan pilihan ini 5. Klik OK

Keterangan Format Control : Pada kolom Current Value diisi dengan nilai 3 sesuai dengan data nomor surat pertama yang ada pada baris ke tiga tepatnya pada sel C3. Karena data nomor surat keluar pertama berada pada baris ke tiga maka maksimum value dapat anda isi sesuai dengan jumlah data maksimum yang digunakan

11. Klik sel G2, beri name box : StartNumber, klik sel G4 beri name box : EndNumber. Kedua kolom tersebut digunakan untuk menentukan nomor urut yang akan dicetak.

Sel G2 Sel G4

12. Aktifkan tab Developer, klik insert pilih Command Button ( ActiveX Control ), buatlah 2 tombol seperti pada gambar dibawah ini. MODUL APLIKASI EXCEL. Kafrawi

7

13. Klik kanan pada tombol pertama pilih properties, berilah pengaturan seperti gambar dibawah ini !

Preview

14. Klik kanan pada tombol kedua, pilih properties, berilah pengaturan Name () Cetak dan caption Cetak Surat 15. Selanjutnya aktifkan tombol Design Mode, klik kanan tombol Preview, pilih View Kode dan ketiklah baris perintah di bawah ini :

16. Tekan Alt+f11 untuk kembali ke lembar kerja Excel, klik kanan tombol Cetak Surat pilih view code. Ketiklah perintah di bawah ini :


8

Keterangan kode : Kode 1, digunakan untuk menampikan preview surat Kode 2, digunakan untuk mencetak surat sesuai dengan nomor urut surat yang dipilih pada range StartNumber sampai EndNumber.

17. Tekan Alt+Q untuk menutup jendela Visual Basic Application dan kembali ke lembar kerja Excel. Non aktifkan Design Mode agar tombol dapat berfungsi.

18. Ketiklah

rumus

sel

H12

pada

Surat

(

gambar

di

atas

)

=INDIRECT(“Surat_Keluar!C”&link1) . Penjelasan rumus : rumus tersebut akan mengambil data tanggal dari table nomor surat keluar sesuai dengan nomor yang ada di sel C1 ( link1) 19. Plih sel E13 ketik rums : =INDIRECT(“Surat_Keluar!D”&link1) . Penjelasan rumus : rumus tersebut akan mengambil data nomor surat dari table nomor surat keluar sesuai dengan nomor yang ada di sel C1 ( link1) 20. Pilih

sel

E14

masukkan

rumus

=INDIRECT(“Surat_keluar!E”&link1),

untuk

menampilkan perihal surat. 21. Pilih sel C18 masukkan rumus rumus =INDIRECT(“Surat_keluar!E”&link1), untuk menampilkan nama tujuan surat. 22. Aplikasi otomatisasi surat siap digunakan !


9

IV.

MEMBUAT APLIKASI DENGAN MS EXCEL APLIKASI PENJUALAN KREDIT Aplikasi yang akan dibahas pada modul ini adalah Aplikasi Penjualan Kredit dengan system pembayaran angsuran. Aplikasi ini meliputi (1) Menu Utama, (2) Pencatatan Transaksi, (3) Perhitungan Pokok Angsuran, (4) Pencatatan Pembayaran Angsuran, dan (5) Laporan Pembayaran Angsuran. Langkah pertama adalah buatlah table dan menu pada masing masing sheet seperti gambar ini :

2

1

3

4

5

A.

Membuat Menu Utama Langkah langkah membuat Menu Utama : 1. Pilih Sheet Menu Utama, tampil form Menu Utama dengan icon gambar seperti pada gambar. Gambar 2

Gambar 3

Gambar 1

Gambar 4

2. Klik kanan gambar 1, pilih Hyperlink MODUL APLIKASI EXCEL. Kafrawi

10

3. Pada bagian sebelah kiri pilih Place in This Document. Pada bagian Cell Reference pilih TRANSAKSI,, seperti pada gambar dibawah ini.

4. Klik tab Screen Tip, pada kolom Screen Tip Texk ketik : INPUT TRANSAKSI. Lalu OK ! 5. Arahkan krusor ke gambar 1, maka akan tampil teks INPUT TRANSAKSI TRANSAKSI, jika gambar di klik akan tampil lembar kerja Transaksi Penjualan Kredit 6. Klik kanan gambar 2, pilih Hyperlink. Hyperlink. Buat link gambar ini dengan sheet Perhitungan Angsuran dan buatlah Screen Tip : Perhitungan Angsuran. Angsuran 7. Klik kanan gambar 3, pilih Hyperlink, buatlah link dengan sheet Pembayaran, Pembayaran buatlah Screen Tip : Pembayaran. 8. Klik kanan pada gambar 4, pilih Hyperlink, Hyperlink, buatlah link dengan sheet Laporan buatlah Screen Tip LAPORAN 9. Klik kanan gambar Anak Panah

di atas table, pilih Hyperlink buatlah link

dengan Menu Utama, berilah Screen Tip MENU UTAMA. 10. Klik

sel

C6

pada

sheet

Tansakasi,

masukkan

rumus

:

=IF(ISBLANK(C6);””;PMT(I6/12;H6; =IF(ISBLANK(C6);””;PMT(I6/12;H6;-(F6-G6))) . Tekan enter, copy J6 sampai J20 11. Sorot ramge B6:J20, beri Name Box Transaksi. 12. Sorot range B6:B20, beri Name Box Kode_Kredit

B. Tabel Perhitungan Angsuran Tabel Perhitungan Angsuran berisi data transaksi penjalan kredit, besarnya angsuran pokok dan angsuran bunga yang harus dibayar setiap bulan oleh cutomer. Berikut langkah pembuatannya :


11

1. Pilih Sheet Perhitungan Angsuran, klik sel D4

aktifkan

tab

Data

pilih

Data

Validation. Buatlah list data kode kredit seperti gambar di samping. Klik sel D4 pilih kode kredit 1

2. Pilih sel D5 ketik rumus untuk menampilkan nama customer sesuai dengan kode kredit yang dipilih =VLOOKUP(D4;Transaksi;3;0) tekan enter 3. Pilih sel G4 ketik rumus =VLOOKUP(D4;Transaksi;7;0) tekan enter. 4. Pilih sel G5 keteik rumus =VLOOKUP(D4;Transaksi;8;0) tekan enter. 5. Pilih sel G6 ketik rumus =VLOOKUP(D4;Transaksi;5;0)-VLOOKUP(D4; Transaksi;6;0) tekan enter. Hasilnya seperti pada gambar dibawah ini.

6. Pilih sel C11 ketik rumus =IF(OR(C10+1>$G$4;C10=”0”);C10+1) Tekan enter, copy sampai ke sel C70. Penjelasan Rumus : Jika sel C10 ditambah 1 lebih besar dari nilai yang ada di sel G4 atau sel C10 bernilai nol maka hasilnya nol. Jika tidak, maka hasilnya nilai sel C10 ditambah1 7. Klik sel D10. Masukkan rumus berikut untuk menghitung pokok angsuran dari setiap pembayaran angsuran. =IF(OR(C10=0;C10>$G$4);””;PPMT($G$5/12;C10;$G$4;-$G$6) Klik enter copy sampai sel C70. Penjelasan Rumus : Jika sel C10 bernilai 0 atau sel C10 lebih besar dari sel G4 maka hasilnya adalah kosong. Jika tidak, maka rumus PPMT akanmenghitng angsuran pokok setiap periode pembayaran. 8. Klik sel E10 ketik rumus : =IF(OR(C10=0;C10>$G$4);””;IPMT($G$5/12;C10; $G$4;-$G$6)). Tekan enter copy sampai ke sel E70.


12

Penjelasan Rumus : Jika sel C10 bernilai nol atau lebih besar dari sel G4 maka hasilnya adalah kosong. Jika tidak, rumus IPMT akan menghitung besarnya angsuran bunga setiap periode pembayaran. 9. Klik sel F10 ketik rumus : =IF(OR(C10=0;C10>$G$4);” “;D10+E10). Tekan enter copy sampai ke sel F70. 10. Untuk melihat besarnya angsuran pokok dan angsuran bunga setiap transaksi kredit, gunakan tombol pilihan kode kredit yang ada di sel D4.

C. Tabel Pembayaran Angsuran. Tabel Pembayaran Angsuran akan menampung Transaksi Pembayaran Angsuran, berikut langkah langkahnya : 1. Klik

sel

D5,

ketik

rumus

:

=IF(ISBLANK(B5);”

“;VLOOKUP

(B5;Transaksi;3;0)). Tekan enter salin rumus ke sel D33. 2. Klik

sel

E5,

ketik

rumus

:

=IF(ISBLANK(C5);”

“

;VLOOKUP

(B5;Transaksi;9;0)). Tekan enter salin rumus ke sel E33. 3. Selanjutnya sorot range G5:G33, aktifkan tab Data pilih Data Validation > Data Validation. 4. Buat list data untuk pilihan cara pembayaran, yaitu : Tunai dan Transfer.

1. Pilih List

2. Ketik : Tunai; Transfer 3. Klik: OK

5. Sorot range B5:B33 lalu beri name box : No_Pembayaran

D. Tabel Laporan Pembayaran Laporan PEmbayaran berisi informasi frekuensi Pembayaran, sisa lama kredit dan ststus kredit customer. MODUL APLIKASI EXCEL. Kafrawi

13

Berikut langkah langkahnya : 1. Pilih sheet Laporan Pembayaran, klik sel B6 ketik rumus =Transaksi!B6, tekan enter, salin rumus sampai ke sel B20. 2. Sel C6 ketik rumus untuk mengambil data nama customer dari table transaksi, =VLOOKUP(B6;Transaksi;3;0) tekan enter salin rumus sampai ke sel C20 3. Sel D6 ketik rumus =VLOOKUP(B6;Transaksi;7;0) tekan enter salin rumus sampai ke sel D20. 4. Sel E6 ketik rumus =VLOOKUP(B6;Transaksi;9;0) tekan enter salin rumus sampai ke sel E20. 5. Sel F6 ketik rumus berikut =IF(ISTEXT(C6);COUNTIF(No_Pembayaran; B6;” ”) tekan enter salin rumus sampai ke sel F20 Penjelasan Rumus : Jika sel C6 berisi teks, maka jumlahkan karakter kode kredit yang ada di kolom no kredit pada table pembayaran angsuran, jika tidak maka hasilnya kosong. 6. Klik sel G6, masukkan rumus =IF(F6=” “;” “;D6-F6) tekan enter salin rumus sampai ke sel G20 7. Klik sel H6, masukkan rumus =IF(F6=” “;” “;IF(F6=0;” “;G6*E6)) tekan enter, salin rumus sampai ke sel H20. 8. Terakhir klik sel I6, ketek rumus =IF(F6=D6;”Lunas”;IF(F6
Penjelasan Rumus : Jika sel F6 sama dengan sel D6 maka beri keterangan Lunas, jika nilai sel F6 lebih kecil dari sel D6 beri keterangan Belum Lunas, selain itu hasilnya Kosong.


14

MEMBUAT KALKULATOR ANGSURAN 1. Buatlah aplikasi kalkulator seperti pada gambar di bawah ini. 2. Aktifkan tab Developer, klik tombol insert pili Option Button ( Form Control) 3. Krusor akan berubah menjadi tanda plus ( + ) buatlah tombol di sel E6, kemudian

copy ke sel E8

4. Klik kanan pada sel E6, pilih Edit Text, ubah nama menjadi Honda, lakukan juga pada E8, ubah nama menjadi Yamaha, seperti pada gambar di atas. 5. Klik kanan pada Tombol Pertama, pilih Object, pilih Tab Control, jendela format control akan ditampilkan.

1. Klik pilihan ini

2. Ketik : $H$1


15

6. Klik tombol insert pilih Combo Box ( Form Control ), buatlah tombol disel H6, seperti pada gambar dibawah ini.

7. Klik sel E10, pilih Tab Data, klik Data Validation>Data Validation. Pada jendela yang tampil, buatlah pengaturan seperti di bawah ini,

Penjelasan : Pengaturan di atas berarti, sel E10 hanya bisa diisi oleh angka dengan nilai lebih besar atau sama dengan 500.000. 8. Klik sel E12, klik Data Validation, buatlah list data untuk pilihan jangka waktu angsuran mulai 1 sampai dengan 6 tahun, seperti pada gambar dibawah ini.


16

9. Sel E14, ketiklah rumus : =IF(ISBLANK(E12;””;IF(E12<=3;”9,5%”;”10%”)) Penjelasan Rumus : JIka sel E12 kosong maka hasilnya adalah kosong, jika sel E12 bernilai lebih kecil atau sama dengan 3, maka hasilnya 9,5%, selain itu hasilnya 10% 10. Klik sel E16, ketik rumus : =IF(ISBLANK(E12);””;PMT(E14/12;E12*12;(H10-E10))), tekan enter. Penjelassan Rumus: Jika sel E12 kosong maka hasilnya adalah kosong, jika tidak maka fungsi PMT akan menghitung besarnya angsuran per bulan sesuai dengan tingkat suku bunga dan jangka waktu yang telah ditetapkan. 11. Sorot range M20 : Q29, beri name box : Data 12. Padas sel H10 ketik rumus : =IF(H1=0;””;IF(H1=1;VLOOKUP(I1;Data;3;0); VLOOKUP(I1;Data;5;0))) tekan enter. Penjelasan Rumus : Jika sel H1 bernilai nol maka hasilnya adalah nol, jika sel H1 bernilai 1 maka bandingkan nilai sel I1 lalu ambil data dari table data kendaraan kolom ke tiga, jika tidak bandingkan nilai sel I1 lalu ambil data dari kolom kelima. Gambar Form simulasi kredit sementara

13. Klik tombol Honda, kemudian klik sel L2, ketik rumus : =IF($H$1=1; ADDRESS(20;14;$H$1)&”:”&ADDRESS(29;14;$H$1);(ADDRESS(20;16;$ H$1)&”:”&ADDRESS(29;16;$H$1)) Tekan enter ! Penjelasan Rumus:


17

Jika sel H1 bernilai 1 maka tampilkan data pada baris ke 20 sampai baris ke 29 kolom ke 14 atau kolom N. Jika tidak, maka tamilkan data pada baris ke 20 sampai baris 29 kolom ke 16 atau kolom P. 14. Posisi krusor pada L2, pilih tab Formula, klik tombol Define Name, buatlah name box seperti pada gambar di bawah ini, kemudian OK Gambar membuat Name Box :

15. Selanjutnya pilih Combo Box pada sel H6, klik kanan, pilih format control, seperti pada gambar di bawah ini.

16. Akan tampil jendela Format Control, pada bagian input range ketik name box : Range, pada bagian Cell Link klik tanda panahnya, pilih sel I1, klik tanda panahnya. 17. Untuk Drop Down line, isi dengan angka 10, aktifkan pilihan 3D shading, klik OK, seperti pada gambar dibawah ini.


18

18. Klik tombol pilihan Type Kendaraan, seluruh daftar kendaraan dengan Merk Honda akan ditampilkan. Pilih salah satu merek maka secara otomatis harga kendaraan akan ditampilkan. 19. Berikutnya, isi kolom uang muka dengan nilai 1.000.000,- pilih jangka waktu 3 tahun, hasilnya akan terlihat seperti pada gambar di bawah ini.

20. Agar table daftar kendaraan tidak mengganggu, dapat menyembunyikannya dengan cara sorot range M19:Q20, aktifkan tab Home pilih Fill Color.

V.

MENGATASI MASALAH KETIKA FUNGSI TIDAK AKTIF Fungsi buatan bisa jadi tidak dapat dijalankan atau data yang muncul adalah komentar kesalahan #NAME?, Cara mengatasinya adalah: Klik Office Button, pilih Excel Options klik Trust Centre Setting, tampilan akan berubah klik tombol Mscro Setting yang berada pada posisi kiri pada bagian Macro Setting, klik tombol pilihan Enable all macros ( not recommended; potentially dangerous code can run ) klik OK, jendela akan berubah klik OK sekali lagi sehingga kembali ke lembar kerja.

SEMOGA BERMANFAAT MODUL APLIKASI EXCEL. Kafrawi

19

FUNGSI BUATAN PADA MICROSOFT EXCEL

Recommend Documents