excel 2010: makro dan data validation

18
5 Data Validation dan Makro Excel 2010 5.1.1 1BMembuat Daftar Sebagai Validasi 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. 2BPraktek 5.5: Menggunakan daftar sebagai validasi Dalam praktek ini, Anda akan membuat kolom yang hanya dapat diisi dengan jenis binatang tertentu www.binsis.com 141

Upload: rachmad-hakim-s

Post on 20-Jun-2015

2.123 views

Category:

Documents


4 download

DESCRIPTION

Panduan Instan Photoshop 2010, www.binsis.com

TRANSCRIPT

Page 1: Excel 2010: Makro dan Data Validation

5

Data Validation dan Makro

Excel 2010

5.1.1 1BMembuat Daftar Sebagai ValidasiDengan 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.

2BPraktek 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

Page 2: Excel 2010: Makro dan Data Validation

2. Lakukan seleksi pada sel atau range yang akan diterapkan dengan validasi data (data validation). Pada contoh ini, Penulis memilih range A2:A10.

3. Klik tab Data. Pada group Data Tools, klik tombol Data Validation .

4. Pilih List pada Allow lalu klik tombol pada bagian Source.

142 www.binsis.com

3

21

Page 3: Excel 2010: Makro dan Data Validation

5. Lakukan seleksi pada area yang akan digunakan untuk daftar validasi data. Pada contoh ini, dipilih daftar nama binatang pada range G1:G6.

6. Klik tombol OK.

www.binsis.com 143

4

4

5

Page 4: Excel 2010: Makro dan Data Validation

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.

5.2 0BExcel Makro

Makro merupakan fasilitas otomatisasi pada Micorosft Office, termasuk pada Excel. Makro disebut juga dengan Visual Basic Application (VBA).

144 www.binsis.com

6

8

7

Page 5: Excel 2010: Makro dan Data Validation

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.

3BPraktek 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.

2. Pada bagian Customize Ribbon, aktifkan pilihan Developer. Klik OK.

www.binsis.com 145

1

1

Page 6: Excel 2010: Makro dan Data Validation

3. Maka akan muncul tab baru pada aplikasi Excel, yaitu Developer. Pada tab Developer, klik tombol Record Macro yang ada di dalam grup Code.

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

3

2

2

2

Page 7: Excel 2010: Makro dan Data Validation

6. Pilih Personal Macro Workbook pada Store macro in sehingga makro disimpan pada file yang saat ini diedit. Klik OK.

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.

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

6

4

5

6

7

Page 8: Excel 2010: Makro dan Data Validation

9. Hentikan perekaman makro dengan mengklik tombol Stop Recording pada group Code.

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.

4BPraktek 5.7: Menjalankan makro

1. Jalankan makro_1 untuk membuat tulisan. Pada tab Developer, klik tombol Macros yang ada di dalam grup Code.

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

9

8

1

Page 9: Excel 2010: Makro dan Data Validation

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.

4. Pilih format Excel Maro-Enabled Workbook (*.xlsm) pada Save as type lalu klik tombol Save.

www.binsis.com 149

2

3

Page 10: Excel 2010: Makro dan Data Validation

5BPraktek 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.

2. Klik pada makro yang akan diedit, yaitu makro_1, lalu klik tombol Edit.

150 www.binsis.com

4

1

4

Page 11: Excel 2010: Makro dan Data Validation

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

2

2

Page 12: Excel 2010: Makro dan Data Validation

' 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 WithEnd 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

Page 13: Excel 2010: Makro dan Data Validation

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

Page 14: Excel 2010: Makro dan Data Validation

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

Page 15: Excel 2010: Makro dan Data Validation

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

Page 16: Excel 2010: Makro dan Data Validation

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

Page 17: Excel 2010: Makro dan Data Validation

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