5
Data Validation dan
Makro
Excel 2010
5.1.1 Membuat Daftar Sebagai Validasi
1B
Dengan menggunakan daftar (list) sebagai validasi, Anda
dapat mengatur sendiri data yang boleh dimasukkan. Selain
data yang diatur tersebut, maka data tidak dapat
dimasukkan (ditolak).
Daftar merupakan kumpulan nilai yang bisa dimasukkan ke
dalam sel. Sel yang menggunakan daftar tidak harus diisi
dengan diketik tetapi dapat dipilih dari menu drop down.
Praktek
2B 5.5: Menggunakan daftar sebagai
validasi
Dalam praktek ini, Anda akan membuat kolom yang hanya
dapat diisi dengan jenis binatang tertentu saja.
1. Ketikkan data yang akan digunakan ke dalam daftar
validasi data. Satu sel digunakan untuk satu entri dalam
daftar. Pada contoh ini, data diketikkan pada sel G1:G6.
www.binsis.com 141
2. Lakukan seleksi pada sel atau range yang akan
diterapkan dengan validasi data (data validation). Pada
contoh ini, Penulis memilih range A2:A10.
1
2
3. Klik tab Data. Pada group Data Tools, klik tombol Data
Validation .
3
4. Pilih List pada Allow lalu klik tombol pada bagian
Source.
142 www.binsis.com
4
4
5. Lakukan seleksi pada area yang akan digunakan untuk
daftar validasi data. Pada contoh ini, dipilih daftar nama
binatang pada range G1:G6.
5
6. Klik tombol OK.
www.binsis.com 143
6
7. Maka pada area yang diterapkan data validation, Anda
hanya dapat mengisi tulisan sesuai dengan isi sel A1:A5
yang telah kita atur.
8. Bukan hanya itu, Anda juga dapat terbantu pada saat
memasukkan data. Excel akan menampilkan menu drop
down yang berisi pilihan nilai data secara otomatis.
8
7
5.2 Excel Makro
0B
Makro merupakan fasilitas otomatisasi pada Micorosft Office,
termasuk pada Excel. Makro disebut juga dengan Visual
Basic Application (VBA).
144 www.binsis.com
Dengan menggunakan makro, beberapa perintah yang
banyak dan komples dapat dijalankan sekaligus. Dengan
demikian, pengerjaan dan pengolahan data akan lebih
cepat, mudah, dan akurat.
Makro dapat dibuat dengan salah satu cara berikut:
• Pemrograman. Anda harus mengetikkan perintah-
perintah makro yan akan digunakan. Bahasa yang
digunakan mirip dengan bahasa Basic namun dengan
data dan perintah yang diambil dari Excel.
• Perekaman. Anda merekam perintah yang akan
digunakan. Setelah terekam, maka Anda dapat
memanggil perintah yang direkam tersebut.
• Kombinasi antara pemrograman dan peremakan. Anda
merekam kemudian mengedit kembali hasil rekaman
sehingga sesuai dengan pengolahan data yang perlu
dilakukan.
3B Praktek 5.6: Membuat makro dengan merekam
1. Agar dapat menggunakan makro dengan lebih leluas,
aktifkan dulu tab Developer pada Excel. Klik menu File
lalu pilih Options.
1
1
2. Pada bagian Customize Ribbon, aktifkan pilihan
Developer. Klik OK.
www.binsis.com 145
2
2
2
3. Maka akan muncul tab baru pada aplikasi Excel, yaitu
Developer. Pada tab Developer, klik tombol Record
Macro yang ada di dalam grup Code.
3
4. Tentukan nama mako pada Macro name. Misalnya:
Makro_1.
5. Sebagai pilihan, Anda dapat mengaktifkan shortcut key
sehingga nanti dapat menjalankan makro dengan
menekan kombinasi tombol keyboard.
Pada contoh ini, Penulis menekan tombol A keyboard
pada bagian Shortuct key. Dengan demikian, makro_1
dapat dijalankan dengan menekan kombinasi tombol
Ctrl + Shift + A.
146 www.binsis.com
6. Pilih Personal Macro Workbook pada Store macro in
sehingga makro disimpan pada file yang saat ini diedit.
Klik OK.
4
5 6
6
7. Mulai sekarang, setiap perintah yang Anda lakukan akan
direkam oleh makro.
Sebagai contoh, Ketikkan Rachmad Hakim S. pada sel
yang saat ini sedang aktif dan tekan Enter. Pada contoh
ini, Penulis menuliskannya pada sel A1.
7
8. Ganti warna teks dan background pada sel yang sedang
diedit. Gunakan warna hitam untuk isi sel dan warna
putih untuk tulisan (teks).
www.binsis.com 147
8
9. Hentikan perekaman makro dengan mengklik tombol
Stop Recording pada group Code.
9
Mulai sekarang, makro sudah dapat digunakan. Jadi jika
Anda ingin menulis Rachmad Hakim S. dengan teks warna
putih dan background hitam, cukup dilakukan dengan
menjalankan makro Makro_1.
4B Praktek 5.7: Menjalankan makro
1. Jalankan makro_1 untuk membuat tulisan. Pada tab
Developer, klik tombol Macros yang ada di dalam
grup Code.
1
2. Pilih Makro_1 lalu klik tombol Run untuk menjalankan
makro. Maka tulisan Rachmad Hakim S. akan dibuat
secara otomatis pada sel yang saat ini sedang terseleksi.
148 www.binsis.com
2
3. Simpan (save) dokemen Excel. Maka akan muncul kotak
dialog konfirmasi. Perhatikan bahwa file Excel .xlsx tidak
dapat menyimpan makro. Karena itu Anda dapat
menggunakan format .xlsm.
Klik No pada kotak dialog konfirmasi.
3
4. Pilih format Excel Maro-Enabled Workbook (*.xlsm)
pada Save as type lalu klik tombol Save.
www.binsis.com 149
4
4
5B Praktek 5.8: Mengedit makro
Pada praktek ini, makro_1 akan diedit sehigga tidak
menuliskan teks Rachmad Hakim S. Makro akan digunakan
untuk menuliskan teks yang diambil dari sel tertentu.
1. Pada tab Developer, klik tombol Macros di bagian
grup Code.
1
2. Klik pada makro yang akan diedit, yaitu makro_1, lalu
klik tombol Edit.
150 www.binsis.com
2
2
3. Maka akan muncul jendela baru berupa Microsoft
Visual Basic for Application.
4. Anda dapat melihat kode program untuk makro_1:
Sub Makro_1()
'
' Makro_1 Macro
' Menulis teks
'
' Keyboard Shortcut: Ctrl+Shift+A
www.binsis.com 151
'
ActiveCell.FormulaR1C1 = "Rachmad Hakim S."
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
End Sub
Kode di atas itulah yang berisi perintah untuk
mengerjakan langkah sebagai berikut:
• Membuat tulisan Rachmad Hakim S.
• Mengganti tampilan sel menjadi berwarna hitam
• Mengganti tampilan teks menjadi berwarna putih.
5. Pada contoh ini, perintah akan digunakan untuk
membuat menyalin tulisan di sebelah kiri sel yang
sedang terseleksi.
Untuk itu, ganti kode program dari:
ActiveCell.FormulaR1C1 = "Rachmad Hakim S."
Menjadi:
ActiveCell.FormulaR1C1 = "=RC[-1]"
Artinya, sel akan disalin dari baris (R: row) yang sama,
tetapi kolom (C: Column) melangkah ke kiri satu langkah.
6. Anda dapat menguji ulang dengan menjalankan
makro_1 dan pastikan mendapat hasil yang diinginkan.
Pada contoh ini, Penulis menjalankan makro_1 pada sel
B1.
152 www.binsis.com
Panduan Instan Excel 2010
Elex Media Komputindo
Rachmad Hakim S.
Pengenalan Excel
1.1 MENJALANKAN EXCEL...........................................................................2
1.2 BEKERJA DENGAN DOKUMEN EXCEL.................................................4
1.2.1 Menuliskan Data..............................................................................4
Praktek 1.1: Menuliskan Data pada Excel...............................................4
1.2.2 Mengatur Tampilan.........................................................................8
Praktek 1.2: Melakukan split secara vertikal............................................8
Praktek 1.3 : Melakukan split dengan tab View.....................................10
Praktek 1.4: Menggunakan Freeze Pane..............................................11
Praktek 1.5: Mengatur Garis Grid..........................................................12
Praktek 1.6: Mengatur warna skema (color scheme)............................13
1.2.3 Bekerja dengan Shortcut Key.......................................................14
Praktek 1.7: Tombol untuk mengakses tab dan ribbon.........................14
1.3 MENYIMPAN DAN MENUTUP FILE.......................................................16
Praktek 1.8: Menyimpan dan menutup file Excel...................................16
1.4 MENCETAK DOKUMEN..........................................................................18
Praktek 1.9: Mencetak dokumen...........................................................18
Praktek 1.10: Mengganti satuan panjang pada Excel...........................21
Penghitungan dan Formula
www.binsis.com 153
2.1 PENGENALAN FORMULA.....................................................................24
2.1.1 Membuat Formula Sederhana......................................................25
Praktek 2.1: Mengetikkan formula untuk menghitung...........................25
2.1.2 Formula dengan Sel dan Range...................................................26
Praktek 2.2: Menggunakan sel pada formula........................................26
Praktek 2.3: Menggunakan range pada formula....................................27
2.1.3 Kesalahan Formula (Error)............................................................29
2.2 MENGGUNAKAN TAB FORMULAS......................................................30
2.2.1 Formula Teks (Text).................................................................. 31
Praktek 2.4: Menggunakan formula Concatenate.................................31
Praktek 2.4: Mengatur list separtor Excel..............................................35
Formula Text yang sering digunakan.....................................................36
2.2.2 Formula Matematika.....................................................................38
Operator Matematika..............................................................................41
2.2.3 Formula Tanggal/Waktu................................................................41
Praktek 2.5: Penghitungan Usia atau Lama Waktu...............................42
2.2.4 Formula Logika..............................................................................43
Praktek 2.6: Formula Logika..................................................................44
2.2.5 Formula Finansial (Keuangan)......................................................45
Praktek 2.7: Membuka help untuk fungsi finansial................................45
Praktek 2.8: Perhitungan Finansial untuk Bisnis....................................47
2.3 PENGOLAHAN DATA DENGAN FORMULA.........................................48
2.3.1 Copy Paste Isi Formula.................................................................48
Praktek 2.9: Menyalin formula dengan Paste Values............................49
2.3.2 Referensi Relatif............................................................................50
Praktek 2.10: Menggunakan referensi relatif.........................................51
2.3.3 Referensi Mutlak...........................................................................52
Praktek 2.11: Menggunakan referensi mutlak untuk baris....................53
Praktek 2.12: Menggunakan referensi mutlak untuk kolom...................55
154 www.binsis.com
2.3.4 Mengaudit Formula.......................................................................57
Praktek 2.13: Melakukan Pengecekan Kesalahan Formula..................57
Praktek 2.14: Menampilkan isi formula sekaligus..................................59
Praktek 2.15: Menggunakan Watch Window.........................................59
Praktek 2.16: Evaluasi Formula.............................................................61
Praktek 2.17: Mengecek hubungan antar sel secara visual..................63
Format Dokumen
3.1 MENGATUR WORKSHEET....................................................................64
Praktek 3.1: Menggunakan worksheet pada file Excel..........................64
3.2 PROTEKSI DOKUMEN DAN PROTEKSI DATA....................................66
3.2.1 Proteksi Membuka dan Mengedit Dokumen.................................66
Praktek 3.2: Melakukan proteksi dokumen Excel..................................66
Praktek 3.3: Membuka file yang terproteksi password..........................69
3.2.2 Proteksi untuk Mengunci Isi Data.................................................71
Praktek 3.4: Mengunci data semua sel pada worksheet.......................71
Praktek 3.5: Unlock sebagian sel...........................................................73
3.3 FORMAT SEL DAN DATA......................................................................75
3.3.1 Format Sel.....................................................................................75
Praktek 3.6: Mengolah tampilan sel.......................................................75
Praktek 3.7: Menghapus format sel.......................................................79
3.3.2 Format Angka................................................................................80
Praktek 3.8: Menggunakan format angka dan persen...........................80
Praktek 3.9: Format Angka....................................................................83
3.3.3 Pengolahan Sel.............................................................................84
Praktek 3.10: Menggabungkan sel.........................................................84
Praktek 3.11: Menyembunyikan Kolom dan Baris.................................85
Praktek 3.12: Memasukkan dan menghapus baris/kolom.....................87
www.binsis.com 155
Tabel, Chart, Gambar, dan Desain Dokumen
4.1. TABEL.....................................................................................................90
Praktek 4.1: Sorting dan Filter pada Tabel............................................90
Praktek 4.2: Mengedit area dan tampilan tabel.....................................93
4.2. CHART....................................................................................................96
Praktek 4.3: Membuat grafik dari data...................................................97
Praktek 4.4: Mengedit property chart.....................................................99
Praktek 4.5: Mengedit data dalam grafik chart....................................102
Praktek 4.6: Mengganti Tipe Grafik.....................................................106
4.3. PIVOTTABLE DAN PIVOTCHART......................................................112
4.3.1 PivotTable....................................................................................112
Praktek 4.7: Menggunakan PivotTable................................................112
4.4. PIVOTCHART.......................................................................................120
Praktek 4.8: Membuat PivotChart........................................................120
4.5. MEMASUKKAN GAMBAR...................................................................126
Praktek 4.9: Memasukkan gambar bitmap (picture)............................126
Praktek 4.10: Memasukkan ClipArt ke dalam Excel............................131
Praktek 4.11: Memasukkan Shapes ke dalam dokumen Excel...........132
Praktek 4.12: Memasukkan SmartArt..................................................135
Praktek 4.13: Memasukkan gambar screenshot.................................137
4.6. MENDESAIN DOKUMEN.....................................................................137
4.6.1 Theme.........................................................................................138
Praktek 4.14: Mengubah theme dokumen Excel.................................138
4.6.2 Cell Style......................................................................................139
Praktek 4.15: Mengatur desain cell syle..............................................139
156 www.binsis.com
4.6.3 Background.................................................................................140
Praktek 4.16: Memasang background.................................................140
Pengolahan Data Tingkat Lanjut
5.1 CONDITIONAL FORMAT......................................................................142
Praktek 5.1: Menggunakan conditional format....................................142
5.1.1 Membuat Aturan Conditional Format..........................................144
Praktek 5.2: Membuat aturan baru untuk format kondisional..............144
Praktek 5.3: Menghapus dan mengedit format kondisional.................147
5.1.2 Data Bars, Color Scales, dan Icon Sets.....................................149
Praktek: Menggunakan tampilan balok, ikon, dan skala......................149
5.2 VALIDASI DATA....................................................................................150
Praktek 5.4: Menerapkan validatasi data.............................................151
5.2.1 Membuat Daftar Sebagai Validasi...............................................154
Praktek 5.5: Menggunakan daftar sebagai validasi.............................154
5.3 EXCEL MAKRO.....................................................................................157
Praktek 5.6: Membuat makro dengan merekam.................................158
Praktek 5.7: Menjalankan makro..........................................................161
Praktek 5.8: Mengedit makro...............................................................163
www.binsis.com 157