microsoft office excel 2016 untuk profesional memindah atau menyalin sheet ke file ..... 35 bab 7...
TRANSCRIPT
Microsoft Office Excel 2016 untuk Profesional
Microsoft Office Excel 2016 untuk Profesional
Johar Arifin
PENERBIT PT ELEX MEDIA KOMPUTINDO
Microsoft Office Excel 2016 untuk Profesional Johar Arifin 2016, PT Elex Media Komputindo, Jakarta Hak cipta dilindungi undang-undang Diterbitkan pertama kali oleh Penerbit PT Elex Media Komputindo Kelompok Gramedia, Anggota IKAPI, Jakarta 2016 [email protected]
716051812 ISBN: 978-602-02-9531-2
Dilarang keras menerjemahkan, memfotokopi, atau memperbanyak sebagian atau seluruh isi buku ini tanpa izin tertulis dari penerbit. Dicetak oleh Percetakan PT Gramedia, Jakarta Isi di luar tanggung jawab percetakan
vii
Daftar Isi
Kata Pengantar ............................................................................................................... v
Daftar Isi ........................................................................................................................ vii
Bab 1 Microsoft Excel untuk Profesional ......................................................... 1
1.1 Pengantar .......................................................................................... 1
1.2 Sistematika Pembahasan ............................................................... 1
1.3 Mempelajari Materi Buku Ini ......................................................... 2
1.4 File Materi Buku Ini .......................................................................... 3
1.5 Mengunduh File ............................................................................... 4
Bab 2 Tes Kemampuan Dasar ............................................................................ 5
2.1 Pengantar .......................................................................................... 5
2.2 Tes Kemampuan Dasar .................................................................. 5
Bab 3 Dasar Pengoperasian Excel 2016 ......................................................... 9
3.1 Pengantar .......................................................................................... 9
3.2 Mengaktifkan File ........................................................................... 10
3.3 Mengenal Jendela Excel ............................................................... 11
Bab 4 Mengenal Lembar Kerja ....................................................................... 13
4.1 Mengenal Lembar Kerja ............................................................... 13
4.2 Mengganti Nama Sheet ................................................................ 13
4.3 Memberi Warna Nama Sheet ..................................................... 14
4.4 Menambah dan Mengurangi Sheet .......................................... 14
4.3 Menetapkan Jumlah Sheet .......................................................... 14
viii
Bab 5 Mengisi Data ke Lembar Kerja .............................................................. 17
5.1 Mengisi Data ke Lembar Kerja .................................................... 17
5.2 Studi Kasus Terapan ....................................................................... 17
5.2.1 Mengatur Penjajaran Data ......................................... 18
5.2.2 Mencatat Bilangan Bulat ............................................. 18
5.2.3 Mencatat Angka Desimal ............................................. 20
5.2.4 Mencatat Angka Pecahan ............................................ 21
5.2.5 Mencatat Angka dengan Tambahan Teks .............. 21
5.2.6 Mencatat Angka dengan Fungsi Text ....................... 22
5.2.7 Huruf E untuk Penulisan Angka Cepat .................... 23
5.2.8 Konversi Angka .............................................................. 23
5.2.9 Mengisi Data Tipe Tanggal ........................................ 24
5.2.10 Mengisi Tanggal (Sistem Komputer 1) .................... 25
5.2.11 Mengisi Tanggal (Sistem Komputer 2) .................... 25
5.2.12 Mengisi Data Tipe Waktu ............................................ 26
Bab 6 Mengenal Lembar Kerja dan Tabel .................................................... 27
6.1 Mengenal Lembar Kerja ............................................................... 27
6.2 Menjelajah Isi Lembar Kerja ........................................................ 27
6.3 Membuat Tabel Data .................................................................... 29
6.4 Menyalin Sheet dalam Satu File ................................................. 33
6.5 Mengenal Menu Sheet ................................................................. 33
6.6 Membuat Kelompok Sheet .......................................................... 34
6.7 Memindah atau Menyalin Sheet ke File ................................... 35
Bab 7 Sel dan Rumus ........................................................................................ 37
7.1 Pengantar ....................................................................................... 37
7.2 Posisi Alamat Sel ............................................................................ 38
7.3 Jenis Alamat Sel .............................................................................. 40
7.4 Menyusun Rumus .......................................................................... 42
ix
7.5 Formula Array ................................................................................. 46
Bab 8 Deret Data ............................................................................................... 49
8.1 Pengantar ....................................................................................... 49
8.2 Studi Kasus Terapan ...................................................................... 50
8.3 Membuat/Menambah Daftar Urutan ........................................ 54
8.4 Menghapus Daftar Urutan ........................................................... 56
Bab 9 Tombol Data Masukan .......................................................................... 57
9.1 Pengantar ........................................................................................ 57
9.2 Pita Developer ................................................................................ 58
9.3 Studi Kasus Terapan ...................................................................... 59
9.3.1 Data Validation .............................................................. 59
9.3.2 Option Button ................................................................ 63
9.3.3 Combo Box .................................................................... 66
9.3.4 List Box ............................................................................. 67
9.3.5 Check Box ....................................................................... 68
9.3.6 Scroll Bar .......................................................................... 69
Bab 10 Sekilas Fungsi Excel ................................................................................ 77
10.1 Sekilas Tentang Fungsi ............................................................... 77
10.2 Mengenal Fungsi ......................................................................... 78
Bab 11 Fungsi Logika .......................................................................................... 81
11.1 Pendekatan Logika ...................................................................... 81
11.2 Dasar Pendekatan Logika .......................................................... 81
11.3 Fungsi IF – Teks (1) ...................................................................... 83
11.4 Fungsi IF – Teks (2) ...................................................................... 83
11.5 Fungsi IF – Teks (3) ...................................................................... 84
11.6 Fungsi IF – Angka dan Teks....................................................... 85
11.7 Fungsi IF Bertingkat ..................................................................... 86
11.8 Fungsi IFS ....................................................................................... 86
x
11.9 Fungsi IFERROR ............................................................................ 87
11.10 Fungsi OR ...................................................................................... 88
11.11 Fungsi AND ................................................................................... 89
11.12 Fungsi NOT ................................................................................... 90
Bab 12 Fungsi Lookup ........................................................................................ 91
12.1 Fungsi Vlookup dan Hlookup ................................................... 91
12.2 Mengenal Tabel Data .................................................................. 92
12.2.1 Mengenal Susunan Tabel ......................................... 92
12.2.2 Membuat Susunan Tabel ........................................... 93
12.2.3 Menyusun Fungsi ........................................................ 95
12.3 Fungsi LOOKUP ............................................................................ 97
Bab 13 Laporan Penjualan ............................................................................... 101
13.1 Pengantar ..................................................................................... 101
13.2 Fungsi dan Studi Kasus Terapan ............................................. 101
13.2.1 Fungsi LEFT ................................................................ 102
13.2.2 Fungsi RIGHT .............................................................. 102
13.2.3 Fungsi MID .................................................................. 103
13.2.4 Fungsi VALUE ............................................................. 104
13.2.5 Fungsi AVERAGE, AVERAGEIF, & AVERAGEIFS .. 105
13.2.6 Fungsi COUNT, COUNTA, COUNTIF, & COUNTIFS ....................................................................................... 105
13.2.7 Fungsi MIN dan MAX ............................................... 106
13.2.8 Fungsi SUM, SUMIF, dan SUMIFS .......................... 106
13.3 Studi Kasus Terapan ................................................................... 107
Bab 14 Hari dan Tanggal .................................................................................. 115
14.1 Pengantar ..................................................................................... 115
14.2 Studi Kasus Terapan ................................................................... 116
14.2.1 Nomor Urut atau Seri Hari ...................................... 116
14.2.2 Menghitung Waktu Maju ......................................... 117
xi
14.2.3 Menghitung Waktu Mundur ................................... 117
14.2.4 Menghitung Waktu Maju atau Mundur ............... 118
14.2.5 Hari Kerja Efektif ......................................................... 119
14.2.6 Menghitung Usia ....................................................... 120
14.2.7 Tanggal Pensiun ........................................................ 121
14.2.8 Menandai Ulang Tahun ............................................ 122
14.2.9 Menandai Surat Keputusan ..................................... 122
14.2.10 Membuat Urutan Data Bulan .................................. 123
14.2.11 Membuat Tabel Waktu Pembayaran .................... 125
14.2.12 Analisis Umur Piutang (1) ........................................ 126
14.2.13 Analisis Umur Piutang (2) ........................................ 128
Bab 15 Manajemen Database .......................................................................... 129
15.1 Database ....................................................................................... 129
15.2 Studi Kasus Terapan ................................................................... 130
15.2.1 Menampilkan Tombol Filter .................................... 130
15.2.2 Menyaring Data ......................................................... 131
15.2.3 Menyaring Data dengan Advance Filter .............. 132
15.2.4 Fasilitas Subtotal ......................................................... 133
Bab 16 Format Kondisional ............................................................................... 135
16.1 Format Kondisional .................................................................... 135
16.2 Studi Kasus Terapan ................................................................... 136
16.2.1 Menandai Batasan Unit Penjualan ......................... 136
16.2.2 Menandai Penjualan Teratas .................................. 137
16.2.3 Format dengan Data Bars ....................................... 137
16.2.4 Format dengan Icon Sets ......................................... 139
16.2.5 Format Sel Berisi Data .............................................. 139
16.3 Melihat Format Kondisional ..................................................... 140
xii
Bab 17 What-If Analysis ..................................................................................... 141
17.1 What-If Analysis .......................................................................... 141
17.2 Studi Kasus Terapan ................................................................... 142
17.2.1 Perhitungan Impas .................................................... 142
17.2.2 Menentukan Laba ...................................................... 144
17.2.3 Perhitungan Pajak Metode Gross-Up ................... 145
17.2.4 Dokumentasi Tawaran ............................................ 146
17.2.5 Tabel Data (kolom) ................................................... 150
17.2.6 Tabel Data (baris)...................................................... 152
17.2.7 Variasi Unit dan Harga Jual .................................... 152
Bab 18 Grafik ....................................................................................................... 155
18.1 Pengantar .................................................................................... 155
18.2 Studi Kasus Terapan ................................................................... 157
18.2.1 Membuat Grafik dalam 1 Detik! ............................ 157
18.2.2 Menyunting Grafik Baku .......................................... 158
18.2.3 Survei Kepuasan Responden .................................. 159
18.2.4 Menyunting Label Data ........................................... 161
18.2.5 Penggunaan Dua Tipe Grafik ................................ 162
18.2.6 Mengubah Seri Data ............................................... 163
18.2.7 Efek Latar Belakang Grafik ..................................... 164
18.2.8 Mengubah Seri Data dengan Gambar ............... 166
18.2.9 Seri Data Tumpang Tindih .................................... 167
18.2.10 Grafik Tipe Waterfall ............................................... 168
Bab 19 PivotTable .............................................................................................. 169
19.1 PivotTable ..................................................................................... 169
19.2 Studi Kasus Terapan ................................................................... 169
19.2.1 Rincian Barang Terjual .............................................. 170
19.2.2 Laporan Penjualan dengan Pilihan Pembeli ....... 171
xiii
19.2.3 Laporan Pembeli Barang ......................................... 174
19.2.4 Desain dan Format Laporan .................................. 174
19.2.5 Fasilitas Slicer ............................................................. 175
19.2.6 Pengaturan Nilai ....................................................... 176
19.2.7 Pengaturan Peringkat ............................................... 177
19.3 PivotChart ..................................................................................... 178
Bab 20 Macro ...................................................................................................... 179
20.1 Pengantar ..................................................................................... 179
20.2 Menggunakan Macro ................................................................ 180
20.2.1 Merekam Macro ......................................................... 180
20.2.2 Menjalankan Macro .................................................. 182
20.2.3 Macro Security ............................................................ 183
20.3 Visual Basic for Application (VBA) .......................................... 183
Bab 21 Proteksi Data .......................................................................................... 189
21.1 Proteksi Data ................................................................................ 189
21.2 Studi Kasus Terapan ................................................................... 190
21.2.1 Proteksi Range ............................................................ 190
21.2.2 Proteksi Sheet ............................................................. 193
21.2.3 Proteksi Struktur dan Jendela File .......................... 193
21.2.4 Enkripsi File ................................................................ 194
21.2.5 Membuka Proteksi dan Enkripsi ............................ 194
Bab 22 Studi Kelayakan Bisnis .......................................................................... 195
22.1 Studi Kelayakan Bisnis ................................................................ 195
22.2 Fungsi NPV, IRR, MIRR, XNPV, dan XIRR .............................. 196
22.3 Fungsi PV ...................................................................................... 197
22.4 Studi Kasus Terapan ................................................................... 197
22.4.1 Metode NPV, IRR, dan MIRR ................................... 198
22.4.2 Metode XNPV dan XIRR ........................................... 199
xiv
22.4.3 Tabel Present Value Interest Factor Annuity ....... 199
22.4.4 Metode NPV dengan Fungsi PV dan NPV ......... 201
22.4.5 Metode Profitability Index ...................................... 202
22.4.6 Metode Payback Period ......................................... 202
22.4.7 Kesimpulan Kelayakan Investasi ............................ 204
Bab 23 Pinjaman Bank ....................................................................................... 207
23.1 Pinjaman Bank ............................................................................. 207
23.2 Angsuran Pinjaman .................................................................... 207
23.3 Fungsi Finansial ........................................................................... 209
23.4 Studi Kasus Terapan ................................................................... 210
23.4.1 Angsuran Pinjaman Bunga Tetap .......................... 210
23.4.2 Angsuran Pinjaman Bunga Menurun ................... 212
23.4.3 Angsuran Pinjaman Bunga Efektif (1) ................... 213
23.4.4 Angsuran Pinjaman Bunga Efektif (2) .................. 214
23.4.5 Angsuran Pinjaman Bunga Efektif (3) ................... 214
23.4.6 Angsuran Pinjaman Pilihan Sistem Bunga ........... 216
Bab 24 Studi Kasus Bisnis .................................................................................. 219
24.1 Pengantar ..................................................................................... 219
24.2 Studi Kasus Terapan ................................................................... 220
24.2.1 Mengelompokkan Usia Karyawan (1) ................... 220
24.2.2 Mengelompokkan Usia Karyawan (2) ................... 221
24.2.3 Menandai Duplikasi Data ......................................... 222
24.2.4 Menandai Data Teratas/Terbawah ....................... 223
24.2.5 Menampilkan Data Terpilih (1) .............................. 224
24.2.6 Menampilkan Data Terpilih (2) ............................... 225
24.2.7 Menampilkan Data Terpilih (3) ............................... 226
24.2.8 Menampilkan Data Triwulanan ............................. 227
24.2.9 Perbandingan Kinerja Tahunan (1) ...................... 228
xv
24.2.10 Perbandingan Kinerja Tahunan (2) ........................ 228
24.2.11 Transaksi Wilayah Tertentu .................................... 229
24.2.12 Nilai Transaksi Pelanggan (1) ................................. 231
24.2.13 Nilai Transaksi Pelanggan (2) .................................. 232
24.2.14 Nilai Transaksi 3 Pelanggan ................................... 233
24.2.15 Wiraniaga dan Transaksi Penjualan ..................... 233
24.2.16 Menampilkan Data dengan Kriteria (1) ............... 234
24.2.17 Menampilkan Data dengan Kriteria (2) ................ 235
24.2.18 Penempatan Karyawan ........................................... 235
24.2.19 Membuat Alamat Email .......................................... 236
24.2.20 Membuat Kata Sandi ............................................... 238
Tentang Penulis ....................................................................................................... 239
1
Bab 1
Microsoft Excel untuk Profesional
Program aplikasi Microsoft Excel sangat mudah dioperasikan. Hanya saja, sering kali pengguna tidak memiliki banyak waktu untuk mengoperasikannya dengan benar.
1.1 PENGANTAR
Program aplikasi Microsoft Excel hampir pasti terpasang pada sebagian besar populasi komputer di dunia. Barangkali, tidak berlebihan jika dikatakan bahwa komputer belum lengkap tanpa kehadiran program aplikasi ini. Penggunaan program yang relatif mudah, dikembangkan terus menerus, dan mampu diterapkan di semua bidang yang berbasis angka, menjadikan program aplikasi ini begitu populer. Tentu saja, program aplikasi ini menjadi tumpuan bagi kalangan profesional; mereka yang berkerja memerlukan kepandaian khusus untuk menjalankannya. Hampir semua profesi yang dalam aktivitas kesehariannya mengolah angka, pasti memerlukan program aplikasi ini.
Kalangan profesional yang menguasai program aplikasi Microsoft Excel dengan benar pasti dapat bekerja lebih efektif dan efisien. Persoalannya, untuk dapat menggunakan program aplikasi Excel secara benar dan efisien, memerlukan waktu yang cukup lama. Buku ini hadir menjawab kebutuhan kalangan yang tidak banyak memiliki waktu luang dan selalu menghargai waktu.
1.2 SISTEMATIKA PEMBAHASAN
Mempelajari program aplikasi Excel secara tuntas pada dasarnya dapat dilakukan dengan mempelajari penjelasan setiap fasilitas yang disediakan
2
oleh pihak pembuat. Excel akan memberikan penjelasan secara rinci prosedur dalam mengoperasikan fasilitas yang disediakan dengan disertai contoh studi kasus. Bahkan, setiap pengguna dapat meminta bantuan materi yang sedang dipelajari dengan menekan tombol fungsi F1. Hanya saja, cara semacam ini tidak cocok bagi mereka yang tidak banyak memiliki waktu luang. Yang lebih praktis tentu saja belajar melalui buku yang membahas program aplikasi ini. Buku tentang Microsoft Excel sudah banyak ditulis adalah sebuah fakta yang tidak terbantahkan. Meskipun demikian, kehadiran buku ini berbeda dengan buku sejenis dalam penyajian dan bobot materi yang dibahas.
Materi buku ini ditulis melalui tahapan persiapan yang cukup lama, tidak sekadar mengupas fasilitas program aplikasi Excel, tetapi juga disertai studi kasus terapan. Sistematika bahasan dimulai dari penjelasan fasilitas, posisi dalam jendela, dan cara pengoperasian. Setiap pembahasan disertai studi kasus terapan yang sebagian di antaranya terinspirasi dari kasus riil yang biasa dihadapi kalangan profesional dalam bekerja. Pembahasan studi kasus yang disajikan tentu saja memiliki bobot tersendiri karena hampir pasti jarang terungkap di buku mana pun, tetapi bisa jadi merupakan kasus yang dihadapi oleh pembaca.
1.3 MEMPELAJARI MATERI BUKU INI
Bagaimana cara mempelajari isi suatu buku secara efektif dan efisien? Pertanyaan yang tidak mudah dijawab secara umum, tetapi barangkali sebagian di antara pembaca telah mengetahui jawabannya. Menulis buku Excel untuk memenuhi kebutuhan semua pembaca pasti tidak mungkin. Kebutuhan setiap pembaca pasti berbeda-beda dan program aplikasi Excel terdiri dari begitu banyak fasilitas yang dapat dikembangkan sesuai kemampuan penulisnya. Namun paling tidak, sebagian dari isi buku ini bermanfaat bagi pengembangan karier pembaca.
Bagaimana cara mempelajari buku ini? Materi buku ini terdiri dari 24 bab, pembahasan bab 3 sampai bab 12, merupakan dasar pengoperasian Excel. Pembaca buku ini hampir pasti pernah atau bahkan sering menggunakan program aplikasi Excel. Bagi pembaca yang merasa masih perlu belajar Excel dengan efektif dan efisien, silakan pelajari bab tersebut. Posisi urutan bab selanjutnya dirancang sesuai dengan kebutuhan pengguna sehingga barangkali membingungkan pembaca. Misal, mengapa pembahasan melompat setelah mengupas What-If Analysis ke bab tentang grafik, baru kemudian proteksi, dan seterusnya. Memang urutan bab dalam buku ini tidak
3
begitu dipentingkan dan terkesan acak karena penulis tidak akan mendikte pembaca mempelajari bab demi bab secara berurutan. Pembaca bebas memilih bab yang harus dipelajari sesuai dengan kebutuhan, karena materi antarbab ada yang tidak berhubungan.
Bagaimana memulainya? Pembahasan bab 2 penulis rancang untuk mengukur kemampuan pembaca dalam mengoperasikan program aplikasi Excel. Tidak ada salahnya, baik yang merasa belum begitu menguasai, cukup menguasai, atau mahir sekalipun, mencoba menjawab secara jujur setiap pertanyaan. Semoga saja setelah mempelajari materi dalam bab 2, pembaca dapat memetakan kemampuan terutama kekurangan dalam mempelajari program aplikasi Excel. Selanjutnya, silakan pilih bab yang akan dipelajari sesuai dengan kebutuhan, praktis bukan?
1.4 FILE MATERI BUKU INI
Buku yang baik dalam pandangan penulis adalah buku yang mudah dipelajari dan dilengkapi file untuk keperluan belajar. Implementasi hal tersebut dengan mengupas setiap bahasan dengan jelas, mudah dan singkat menggunakan bahasa yang sederhana sehingga dapat dipahami oleh semua kalangan pembaca. Selain itu, untuk mencapai tujuan tersebut, tentu harus disiapkan file materi pembahasan dan media untuk berlatih, sehingga pembaca fokus pada masalah tanpa harus bersusah payah merancang tabel data.
File yang penulis sertakan dalam buku ini (diunduh pembaca) selain untuk memudahkan dalam mempraktikkan materi juga sebagai bentuk pertanggungjawaban penulis kepada pembaca. Bagi penulis, bentuk berbagi pengetahuan secara tulus adalah dengan memberikan file tersebut, karena sebagus apa pun buku tentang Excel tanpa disertai file bisa jadi menunjukkan ketidaktulusan dalam berbagi. Penulis menempatkan pembaca sebagai sahabat, dari pembacalah penulis sering mendapatkan masukan untuk perbaikan buku. Inspirasi dari pembaca tidak dapat dikesampingkan, selanjutnya dikembangkan dan dikembalikan kepada pembaca, dengan harapan dapat memotivasi belajar, menginspirasi, dan meningkatkan kemampuan diri.
4
1.5 MENGUNDUH FILE
File pendamping buku ini dapat pembaca unduh di website www.elexmedia.id dengan prosedur:
1. Buka www.elexmedia.id/download.
2. Cari judul buku ini, yaitu Microsoft Office Excel 2016 untuk Profesional. Selanjutnya, klik Download.
3. Ekstrak file tersebut ke komputer atau harddisk Anda.
239
Tentang Penulis:
Johar Arifin, kelahiran Bumiayu-Brebes, alumni Magister Akuntansi Universitas Diponegoro Semarang dan pernah mengikuti pelatihan di Small Business Training Institute (SBTI), Ansan City, Korea Selatan. Saat buku ini ditulis, tercatat sebagai pengajar pada Program Vokasi Universitas Indonesia dan STIKES Banten. Penulis buku bisnis terapan dengan Microsoft Excel terkemuka di Indonesia yang sangat produktif, buku ini merupakan karya yang ke-83.
Komunikasi dengan penulis dapat dilakukan melalui Email: [email protected] atau WhatsApp: 08159960211
Catatan:
Untuk melakukan pemesanan buku, hubungi
Layanan Langsung PT Elex Media Komputindo:
Gramedia Direct Jl. Palmerah Barat No. 29-37, Jakarta 10270
Telemarketing/CS: 021-53650110/111
ext: 3901/3902/3292