SolVeR

Dynamic Data dengan VLOOKUP

Sapto Hartoko

Salah satu contoh untuk membuat suatu rangkuman, misalkan saja datanya sudah banyak, untuk lebih menarik dan kelihatan profesional, alangkah baiknya memakai rangkuman yang dynamic sehingga user bisa mengubah dengan keinginannya.

Contoh di bawah adalah salah satu contoh sangat sederhana dimana user bisa mengubah pada dropdown tahun, jika tahun diubah maka data yang akan dimunculkan sesuai dengan tahun yang dipilih.

Cara pembuatan :

Kumpuklan data dan letakkan di sheet lain, bisa juga data tersebut diberi nama (Define Name) MyData (Gambar 02)
Buat kolom tahun dan diberi nama (Define Name) dengan Tahun (Gambar 02)
Selanjutnya pada sheet lain,pada salah satu cell untuk pilihan tahun buat Data Validation berupa list dengan referensi tahun (Gambar 03)
Buat rumus dengan VLOOKUP dengan Lookup_value tahun, Table_array MyData dan Col_index_num sesuaikan dengan kolom bulan (Gambar 03)

Jika sudah selesai, maka tiap kali user mengubah pada cell tahun maka data dan grafik akan berubah sesuai dengan tahun yang dipilih.

Untuk lebih jelasnya silakan download file :

Download Contoh Dynamic Data dengan VLOOKUP

Shortcut di Excel yang sering digunakan

Sapto Hartoko

Shortcut pada excel, pengoperasian menu, toolbar atau ribbon (excel 2007) akan lebih cepat jika kita sudah hapal shortcut nya. Adapun shortcut yang sering dipakai yaitu :

Action	Shotcut
Pindah cell ke kanan satu per satu	Tab
Pindah cell ke bawah satu per satu	Enter
Menghapus data cell terpilih	Backspace
Kembali ke row awal	Home
Insert tanggal	Ctr + ; (semicolon)
Insert Jam	Ctr + Shift + ; (semicolon)
Memulai menulis rumus	=
Spelling check	F7
Menanpilkan Style dialog	Alt + ' (apostrophe)
Menampilkan Format Cells dialog	Ctr + 1
Mengubah format ke general number	Ctr + Shift + ~
Mengubah angka ke format dollar	Ctr + Shift + $
Mengubah angka ke format persen	Ctr + Shift + %
Membuat border	Ctr + Shift + &

Referensi :
http://www.microsoft.com/athome/organization/compshortcuts.aspx

Print Out Comment di Excel

Sapto Hartoko

Sudah susah susah membuat comment tiap cell di excel, tetapi pada saat print out comment tadi tidak keluar, apa yang salah, setting printer atau excelnya yang rusak? Tidak tampak karena secara default excel tidak print out comment tersebut. Sudah setting di Page Setup, tetapi pada page setup Comment disable, tidak bisa setting (Gambar 02). Untuk bisa nampa, perlu setting pada Page Setup.

Pertama, kenapa pada page setup Comment menjadi disable? Hal tersebut disebabkan karena Setting Page Setup pada saat Print Preview. Oleh sebab itu comment tidak bisa setting. (Gambar 02).

Oleh sebab itu langkah supaya comment bisa print out,

Dari Menu File, pilih Page Setup
Pada jendela Page Setup, pilih Tab Sheet, Comment sudah tidak disable lagi (Gambar 03)
Pada Comment pilih pada combo box sesuai selera (Gambar 03)

Excel Comment, cara pembuatan dan cara mengubah format

Sapto Hartoko

Langkah pertama cara pembuatan excel comment

Klik kanan paca cell yang akan ditambah cemment
Pilih Insert Comment (Gambar 01)
Lalu isi pada bagian kotak komentar yang Anda kehendaki

Untuk mengubah bentuk comment (Comment Shape)

Klik untuk memilih bagian pada comment
Dalam keadaan terpilih, dari menu Draw (pojok bawah excel), pilih Change AutoShape, pilih bentuk yang dikehendaki (Gambar 02)

Bagaimana dengan warna dan jenis Fontnya? Secara default format comment pada excel tergantung dari setting windows tooltip, jika ingin ganti format semua comment di excell sbb

Pada desktop, klik kanan dan pilih properties
Pilih Tab Appearance dan pilih Advance akan terlihat seperti Gambar 03
Pada Itempilih ToolTip, pada bagian kanan dari dropdown pilih format kesukaan, begitu juga dengan font dan jenisnya.
Setelah semua konfirmasi Ok atau Apply, maka semua comment pada Excel akan berubah.

Untuk mengubah format comment 1 comment saja contohnya

Sorot pada comment yang akan diubah laku klik kanan (Gambar 04)
Pilih Format Comment, ubah format sesuai dengan keinginan (Gambar 05)
Setelah semua selesai klik Ok, maka format comment akan berubah (Gambar 06)

Membuat fungsi kustom (Excel User Defined Functions)

Sapto Hartoko

Yang dimaksud dengan fungsi kostom atau User Defined Functions di excel yaitu fungsi yang kita buat sendiri yang berbeda dengan fungsi yang sudah ada di excel misal SUM, SUMIF, VLOOKUP dan lainnya. Kelebihannya adalah sbb :

Dapat membuat dengan rumit fungsi matematika kustom
Meringkas jika rumus sudah terlalu panjang
Bisa diagnona format cell
Kustom memanipulasi text, dll

Adapum urutan untuk membuat fungsi kustom adalah sbb (Untuk penjelasan lengkap silakan lihat posting sebelumnya Excel VBA Basic bag 1 (Hello Word!) :

Buka workbook baru
Buka Visual basic editor (ALT+F11)
Insert Modul baru (Insert->Module)
Buat Fungsi (Atau Copy Paste Contoh di bawah)
Keluar dari Visual Basic Editor (ALT+Q)
Pakai Fungsi yang telah dibuat tadi

Contoh 1, Membuat fungsi luas bujur sangkar

1
2
3

Public Function LuasBujurSangkar(sisi As Integer) As Integer
 LuasBujurSangkar sisi sisi
End Function

Excel VBA Basic bag 3 (Modifikasi Redord Macro)

Sapto Hartoko

Cara pembacaan code Visual Basic dari hasil record macro artikel sebelumnya

Baris 1 Sub Macro1
Nama dari Sub tesebut adalah Macro1 dan harus diakhiri dengan End Sub (Baris ke 11)
Macro1 boleh diganti dengan selera dan sebaiknya yang berhubungan dengan hasil dari Sub tersebut, contoh Sub doWarnai
Baris 1 s/d 5 adalah sekedar comment, catatan harus dimulai dengan tanda accent program tidak menjalankan perintah ini
Baris ke 6, Range("C4:D5").Select
Memerintahkan untuk memilih area C4:D5
Bisa dihapus dan kita memilih cellnya secara manual, cell yang terpilih akan diwarnai
Baris ke 7 sampai 10, bisa diganti dengan
1
2

Selection.Interior.ColorIndex 41
Selection.Interior.Pattern xlSolid
Karena Selection.Interior berulang ulang ditulis dengan With Selection.Interior dan diakhiri dengan End With
Selection.Interior maksudnya cell yang sudah diselect (disorot) akan diberi dengan Color Index 41 (Light Blue) dan dengan Pattern xlSolid

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/21/2009 by Sapto Hartoko
'
 Range("C4:D5").Select
 With Selection.Interior
 .ColorIndex 41
 .Pattern xlSolid
 End With
End Sub

Jika line

Excel VBA Basic bag 2 (Record Macro)

Sapto Hartoko

Programer tidak harus pinter ngetik, cara praktis belajar & membuat program VBA di excel. Gunakan fasilitas yang ada pada excel yaitu merekam pekerjaan kita yang berulang ulang dengan Record Macro, yang nantinya dipakai untuk mengulang pekerjaan kita hanya dengan menjalankan macro tersebut.

Langkah langkah dalam merecord macro

Buka excel Anda, sebaiknya workbook tersebut langsung diberi nana dan disimpan (Contoh dibawah kami beri nana VBA-Record-Macro)
Pada menu pilih Tool->Macro->Record New Macro... (Gambar 01)
Sehingga akan tampil jendela seperti Gambar 02

Untuk kali ini kami sarankan tempat penyimpanan macro adalah di This Woekbook (File Excel yang baru dibuat tadi)

Setelan tombol Ok ditekan, makan akan muncul Toolbar "Stop Recording" (Gambar 03)
Pada step ini operasi di excel sudah mulai direcord
Pilih cell dengan range C4:D5, lalu beri warna "Light Blue"

Setelah selesai memberi warna, tekan tombol Stop Recording pada Toolbar "Stop Recording" (Gambar 04)

Pekerjaan selesai, macro sudah tersimpan, untuk melihat hasil dalam VBA
Dari menu pilih Tools->Macro->Visual Basic Editor
Hasil seperti Gambar 05
Sebelum testing macro, cell yang kita warnai tadi ganti dengan warna lain atau hilangkan warna
Untuk testing hasil recording, kembali ke excel workbook dari menu pilih Tool->Macro->Macro...
Jalankan macro yang yang Anda record tadi, hasilnya cell C4:D5 akan diwarnai kembali ke warna Light Blue

Excel VBA Basic bag 1 (Hello Word!)

Sapto Hartoko

Tak kenal maka tak sayang, untuk kenalalan dengan bahasa pemograman, yang paling susah adalah bagaimana cara memulai. Berikut adalah cara membuat program dengan visual basic di Excel yang paling sangat sederhana yaitu "Hello Word!", jika Anda bisa membuat program Hello Word! seperti di bawah tanpa menyontek, berarti 50% bahasa VBA (Visual Basic for Application) sudah dikuasai. Kenana, karena selanjutnya Anda jika kesulitan dalam pembuatan program tinggal lihat "Bantuan" (Help).

Langkah pembuatan VBA "Hello Word!"

Buat file excel dan beri nama VBA-Permulaan, atau terserah Anda
Pada menu Tools->Macro->Visual Basic Editor (Atau tekan ALT dan F11 secara bersamaan), Gambar 01
Langkah di atas menghasilkan jendela baru dengan nama Microsoft Visual Basic Edito (Gambar 02)

Sorot Project Explorer Pilih VBAProject (VBA-Permulaan.xls), sesuai nama file yang dibuat step sebelumnya (Gambar 02)

Klik kanan Pilih Insert->Module, sehingga muncul pada modul dengan nama madule1

Gambar 03, pastikan Module1 tadi tersorot, dan kemudian tulis pada bagian kanan sbb

1
2
3

Sub showPesan()
  MsgBox "Hello World!"
End Sub

Tutup Jendela Microsoft

Cara copy paste (Absolute Reference) di Excel

Sapto Hartoko

Download Contoh Excel Cara copy paste (Absolute Reference) di Excel

Cara pembuatan rumus excel supaya bisa copy paste, baik ke samping maupun kebawah dengan hasil yang benar. Strategi yang harus dipakai yaitu pemakaian tanda (dollar) pada rumus excel tersebut. Berikut adalah simulasi copy dan paste rumus excel, Gambar 01, dimana rumus tersebut tidak memakai tanda dollar rumus Cell B6 adalah mengambil referensi dari Cell B3, sehingga rumusnya menjadi =B3. Coba rumus tersebut copy dan paste ke bawah yaitu ke cell B7, maka rumus yang dihasilkan akan menjadi =B4.Untuk keterangan detailnya adalah sbb :

Rumus asal pada cell B6 adalah =B3 (Gambar 01)

Copy Paste cell B6 ke cell B7, hasil rumus menjadi =B4
Copy Paste cell B6 ke cell B5, hasil rumus menjadi =B2
Copy Paste cell B6 ke cell A6, hasil rumus menjadi =A3
Copy Paste cell B6 ke cell C6, hasil rumus menjadi =C3

Tanpa dollar sama sekali, tiap kali rumus copy dan paste ke tempat lain rumus akan berubah ubah

Rumus asal pada cell B6 adalah =B$3 (Gambar 02)

Copy Paste cell B6 ke cell B7, hasil rumus menjadi =B$3
Copy Paste cell B6 ke cell B5, hasil rumus menjadi =B$3
Copy Paste cell B6 ke cell A6, hasil rumus menjadi =A$3
Copy Paste cell B6 ke cell C6, hasil rumus menjadi

Mencegah terjadinya double input pada Excel

Sapto Hartoko

Download Contoh Excel Mencegah terjadinya double input

Kode unik adalah kode yang dimana hanya boleh muncul 1 kali, contoh yang paling awam adalah jika kita mendata siswa, kode unik yang paling bagus adalah No Induk, karana satu No Induk berarti satu siswa. Untuk mencegah terjadinya double input No Induk pada saat diinput bisa menggunakan fasilitas Conditional formating yang sudah ada pada excel.

Langkah langkah cara pembuatan dari contoh di atas sbb :

Sorot dari cell A20 sampai A2
dari menu Format, pilih Conditional Formatin, seperti contoh gambar 02
Pada Dropdown Conditiona1 pilih Formua Is
Pada Textbox sampingkanannya tulis formula "=COUNTIF($A$2:$A$20,A20) > 1", tanpa tanda petik
Tekan tombol format, pilih format sesuai kebutuhan dan selera (Gambar 03)
Tekan Ok