Tip dan Trik Membuat Laporan Menggunakan Excel

Sanksi Pelanggaran Pasal 113 Undang-Undang Nomor 28 Tahun 2014 tentang Hak Cipta 1. Setiap Orang yang dengan tanpa hak melakukan pelanggaran hak ekonomi

sebagaimana dimaksud dalam Pasal 9 ayat (1) huruf i untuk Penggunaan Secara Komersial dipidana dengan pidana penjara paling lama 1 (satu) tahun dan/atau pidana denda paling banyak Rp100.000.000 (seratus juta rupiah).

2. Setiap Orang yang dengan tanpa hak dan/atau tanpa izin Pencipta atau pemegang Hak Cipta melakukan pelanggaran hak ekonomi Pencipta sebagaimana dimaksud dalam Pasal 9 ayat (1) huruf c, huruf d, huruf f, dan/atau huruf h untuk Penggunaan Secara Komersial dipidana dengan pidana penjara paling lama 3 (tiga) tahun dan/atau pidana denda paling banyak Rp500.000.000,00 (lima ratus juta rupiah).

3. Setiap Orang yang dengan tanpa hak dan/atau tanpa izin Pencipta atau pemegang Hak Cipta melakukan pelanggaran hak ekonomi Pencipta sebagaimana dimaksud dalam Pasal 9 ayat (1) huruf a, huruf b, huruf e, dan/atau huruf g untuk Penggunaan Secara Komersial dipidana dengan pidana penjara paling lama 4 (empat) tahun dan/atau pidana denda paling banyak Rp1.000.000.000,00 (satu miliar rupiah).

4. Setiap Orang yang memenuhi unsur sebagaimana dimaksud pada ayat (3) yang dilakukan dalam bentuk pembajakan, dipidana dengan pidana penjara paling lama 10 (sepuluh) tahun dan/atau pidana denda paling banyak Rp4.000.000.000,00 (empat miliar rupiah).

Tip dan Trik Membuat Laporan Menggunakan Excel

Yudhy Wicaksono & Solusi Kantor

PENERBIT PT ELEX MEDIA KOMPUTINDO

Tip dan Trik Membuat Laporan Menggunakan Excel

Yudhy Wicaksono & Solusi Kantor © 2017, PT Elex Media Komputindo, Jakarta Hak cipta dilindungi undang-undang Diterbitkan pertama kali oleh Penerbit PT Elex Media Komputindo Kelompok Gramedia, Anggota IKAPI, Jakarta 2017

717051514 ISBN: 978-602-04-4564-9

[eEp]

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

v

KATA PENGANTAR

Pembuatan laporan merupakan salah satu pekerjaan yang paling sering dijumpai dalam dunia bisnis dan perkantoran. Namun demikian, masih banyak pengguna komputer yang kesulitan dalam membuat laporan sehingga laporan yang dihasilkan tidak bisa terbaca atau tidak mudah dipahami. Buku Tip dan Trik Membuat Laporan Menggunakan Excel membahas berbagai tip dan trik pembuatan laporan menggunakan Excel dengan cepat dan mudah. Anda akan dipandu langkah demi langkah menggunakan berbagai fitur populer Excel yang digunakan untuk mempermudah pembuatan laporan.

Penulis menyadari bahwa tidak ada sebuah karya yang sempurna. Oleh karena itu, penulis mengharapkan adanya masukan, saran ataupun kritikan dari pembaca yang bersifat membangun. Apabila Anda berniat memberi masukan, saran ataupun kritikan silahkan menghubungi saya melalui Facebook atau e-mail: yudhy_wicaksono2000@yahoo.com

Kepada PT Elex Media Komputindo saya mengucapkan terima kasih karena bersedia menerbitkan buku karya saya yang ke-68. Terima kasih juga saya ucapkan untuk Bapak AS Pratisto (Eep) dan Tim Elex Media Komputindo yang telah banyak membantu hingga buku ini bisa terbit.

Salatiga, Juli 2017

Yudhy Wicaksono yudhy_wicaksono2000@yahoo.com

www.solusi-kantor.com

vii

DAFTAR ISI

KATA PENGANTAR................................................................................ V DAFTAR ISI ........................................................................................VII BAB 1 FITUR PENTING DALAM PEMBUATAN LAPORAN............................. 1

1.1 Pengaturan Setting Regional Komputer.....................................1 1.2 Sel dan Range...........................................................................4

1.2.1 Menyembunyikan Baris dan Kolom ......................................5 1.2.2 Mengubah Lebar Kolom.......................................................7 1.2.3 Mengubah Tinggi Baris ........................................................9

1.3 Format Sel ..............................................................................10 1.3.1 Format Data ......................................................................10 1.3.2 Format Alignment ..............................................................13 1.3.3 Menggunakan Fitur Wrap Text ...........................................14 1.3.4 Menggabungkan Sel ..........................................................16 1.3.5 Format Font ......................................................................17 1.3.6 Mengubah Jenis dan Ukuran Font......................................18 1.3.7 Format Border ...................................................................19 1.3.8 Mengubah Warna Sel dan Warna Font ...............................21 1.3.9 Menyalin Format Sel ..........................................................23

1.4 Mengatur dan Mencetak Worksheet.........................................24 1.4.1 Menentukan Area Cetak Worksheet ....................................25 1.4.2 Menentukan Baris dan Kolom Sebagai Judul Cetak ...........25 1.4.3 Mengatur Urutan Pencetakan .............................................27 1.4.4 Menentukan Orientasi Cetak ..............................................28 1.4.5 Mengatur Margin ................................................................29 1.4.6 Menambahkan Header dan Footer .....................................30 1.4.7 Memilih Ukuran Kertas .......................................................33

viii

1.4.8 Mengatur Skala Hasil Pencetakan.......................................34 1.4.9 Tampilan Page Layout........................................................35 1.4.10 Tampilan Page Break Preview ............................................36 1.4.11 Menentukan Page Break Secara Manual.............................37 1.4.12 Mencetak Worksheet ..........................................................38

BAB 2 MEMBUAT LAPORAN DENGAN SORT DAN FILTER........................ 41 2.1 Mengurutkan Data dengan Fitur Sort ......................................41

2.1.1 Mengurutkan Data Teks .....................................................41 2.1.2 Mengurutkan Data Tanggal................................................44 2.1.3 Mengurutkan Data Angka...................................................45 2.1.4 Mengurutkan Data Menggunakan Kotak Dialog Sort ..........47 2.1.5 Mengurutkan Data Secara Horizontal .................................50

2.2 Menyaring Data .......................................................................52 2.3 Menyaring Data Teks...............................................................55

2.3.1 Menyaring Data Teks Equals ..............................................55 2.3.2 Menyaring Data Teks Does Not Equal ................................56 2.3.3 Menyaring Data Teks Begins With ......................................58 2.3.4 Menyaring Data Teks Does Not Begin With ........................59 2.3.5 Menyaring Data Teks Ends With.........................................60 2.3.6 Menyaring Data Teks Does Not End With...........................61 2.3.7 Menyaring Data Teks Contains ...........................................63 2.3.8 Menyaring Data Teks Does Not Contain.............................64

2.4 Menyaring Data Tanggal .........................................................65 2.4.1 Menyaring Data Tanggal Equals .........................................65 2.4.2 Menyaring Data Tanggal Does Not Equal ...........................67 2.4.3 Menyaring Data Tanggal Is Before......................................68 2.4.4 Menyaring Data Tanggal Is Before Or Equal To..................69 2.4.5 Menyaring Data Tanggal Is After.........................................70 2.4.6 Menyaring Data Tanggal Is After Or Equal To.....................72 2.4.7 Menyaring Data Tanggal Between ......................................73 2.4.8 Menyaring Data Tanggal Not Between ...............................74

2.5 Menyaring Data Angka ............................................................75 2.5.1 Menyaring Data Angka Equals............................................76 2.5.2 Menyaring Data Angka Does Not Equal..............................77 2.5.3 Menyaring Data Angka Is Greater Than ..............................78 2.5.4 Menyaring Data Angka Is Greater Than Or Equal To ..........79 2.5.5 Menyaring Data Angka Is Less Than...................................81 2.5.6 Menyaring Data Angka Is Less Than Or Equal To...............82 2.5.7 Menyaring Data Angka Between.........................................83 2.5.8 Menyaring Data Angka Not Between ..................................85 2.5.9 Menyaring Data Angka Top 10 ...........................................86

ix

2.5.10 Menyaring Data Angka Above Average ...............................87 2.5.11 Menyaring Data Angka Below Average ...............................88

2.6 Menyaring Data Menggunakan Advanced Filter .......................89 2.6.1 Hasil Penyaringan pada Range yang Sama.........................91 2.6.2 Hasil Penyaringan pada Range Berbeda .............................93 2.6.3 Dua Kriteria Penyaringan pada Satu Baris yang Sama........95 2.6.4 Kriteria Penyaringan untuk Kolom yang Sama....................96 2.6.5 Dua Kriteria pada Baris yang Berbeda................................97 2.6.6 Menghindari Baris Kriteria dalam Keadaan Kosong ............98 2.6.7 Penggunaan Operator AND dan OR 1................................99 2.6.8 Penggunaan Operator AND dan OR 2..............................100

BAB 3 MEMBUAT LAPORAN DENGAN SUBTOTAL ................................103 3.1 Menggunakan Fitur Subtotal .................................................103 3.2 Menambah Level Subtotal 1 ..................................................107 3.3 Menambah Level Subtotal 2 ..................................................110 3.4 Menghapus Subtotal .............................................................115

BAB 4 MEMBUAT LAPORAN DENGAN TABLE ......................................117 4.1 Membuat Table .....................................................................118 4.2 Mengubah Style Table ...........................................................121 4.3 Mengkonversi Table Menjadi Range Database .......................124

BAB 5 LAPORAN DENGAN CONDITIONAL FORMATTING .......................127 5.1 Highlight Cells Rules .............................................................127 5.2 Top/Bottom Rules .................................................................131 5.3 Data Bars ..............................................................................133 5.4 Color Scales ..........................................................................135 5.5 Icon Sets ...............................................................................137 5.6 Conditional Formatting Menggunakan Formula ....................139 5.7 Manajemen Conditional Formatting ......................................141 5.8 Conditional Formatting Bertingkat ........................................142 5.9 Menghapus Conditional Formatting ......................................144 5.10 Trik Conditional Formatting ..................................................145

5.10.1 Kisaran Data ....................................................................145 5.10.2 Di Luar Kisaran Data........................................................147 5.10.3 Data dengan 2 Kriteria .....................................................148 5.10.4 Penjualan Terendah Tiap Bulan........................................149 5.10.5 Penjualan Tertinggi Tiap Bulan.........................................151 5.10.6 Penjualan Terendah Tiap Salesman .................................152 5.10.7 Penjualan Tertinggi Tiap Salesman ..................................153 5.10.8 Group Data ......................................................................155 5.10.9 Border Tabel Berisi Data ..................................................157

x

BAB 6 MEMBUAT LAPORAN DENGAN PIVOTTABLE .............................161 6.1 Membuat PivotTable..............................................................161 6.2 Mengelola PivotTable ............................................................167

6.2.1 Mengubah Judul Kolom dan Judul Baris..........................167 6.2.2 Memindah atau Menghapus Field.....................................168 6.2.3 Update Perubahan Sumber Data PivotTable.....................170 6.2.4 Mengubah Lokasi Penempatan PivotTable .......................173

6.3 Perhitungan PivotTable..........................................................175 6.3.1 Mengubah Perhitungan Ringkasan ...................................175 6.3.2 Subtotals..........................................................................177 6.3.3 Grand Totals ....................................................................179 6.3.4 Menampilkan Persentase Penjualan..................................181 6.3.5 Menampilkan Peringkat Penjualan....................................183 6.3.6 Membuat Calculated Field................................................186 6.3.7 Membuat Calculated Item ................................................190

6.4 Format dan Tampilan PivotTable ..........................................192 6.4.1 PivotTable Style Options ..................................................193 6.4.2 PivotTable Style................................................................194 6.4.3 Mengisi Sel Kosong..........................................................196 6.4.4 Report Layout ..................................................................198

6.5 Group pada PivotTable..........................................................200 6.5.1 Group Secara Manual.......................................................201 6.5.2 Group Berdasarkan Tanggal ............................................203 6.5.3 Group Berdasarkan Angka ...............................................204 6.5.4 Menghapus Group............................................................206

TENTANG PENULIS ........................................................................... 207

1

FITUR PENTING DALAM

PEMBUATAN LAPORAN

Dalam pembuatan laporan, ada beberapa fitur penting yang perlu Anda kuasai, agar dapat menghasilkan laporan yang menarik dan profesional. Beberapa fitur tersebut di antaranya pengaturan setting regional komputer, penanganan sel atau range serta pengaturan dan pencetakan worksheet.

1.1 Pengaturan Setting Regional Komputer

Pengaturan setting regional komputer berpengaruh pada penggunaan tanda koma (,) dan titik koma (;) sebagai simbol desimal dan pemisah ribuan. Untuk memudahkan dalam memahami buku ini, sebaiknya Anda menerapkan setting regional komputer Indonesian (Indonesia), sama dengan setting regional komputer yang digunakan penulis.

1. Untuk pengguna Windows XP, klik tombol Start. Pilih menu Control Panel. Muncul jendela Control Panel.

2

Gambar 1.1 Jendela Control Panel Windows XP.

2. Klik ikon Date, Time, Language, and Regional Options pada window Control Panel. Muncul jendela Date, Time, Language, and Regional Options.

Gambar 1.2 Jendela Date, Time, Language, and Regional Options

3. Klik ikon Regional and Language Options. Muncul kotak dialog Regional and Language Options.

3

Gambar 1.3 Kotak dialog Regional and Language Options.

4. Pilih tab Regional Options. Pada pilihan Standards and formats, pilih Indonesian. Pilih Indonesia pada pilihan Location. Klik tombol OK.

5. Untuk pengguna Windows 7, klik tombol Start kemudian pilih Control Panel. Muncul jendela Control Panel.

Gambar 1.4 Jendela Control Panel Windows 7.

6. Klik Clock, Language, and Region. Muncul jendela Clock, Language, and Region.

4

Gambar 1.5 Jendela Clock, Language, and Region.

7. Klik Region and Language. Muncul kotak dialog Region and Language. Pada kotak isian format pilih Indonesian (Indonesia). Klik tombol OK.

Gambar 1.6 Kotak dialog Region and Language.

1.2 Sel dan Range

Worksheet pada dasarnya merupakan sebuah tabel yang terdiri atas kolom dan baris. Worksheet dalam Excel 2007 atau versi setelahnya mempunyai 16.384 kolom dan 1.048.576 baris. Setiap kolom diberi nama dengan 1 karakter huruf dari A sampai Z kemudian dilanjutkan 2 karakter huruf dari AA sampai ZZ dan dilanjutkan dengan 3 karakter huruf dari AAA sampai XFD. Setiap baris ditandai dengan angka dari 1 sampai 1048576.

5

Perpotongan kolom dan baris dalam worksheet disebut sel. Secara default, sel diberi nama kolom dan baris perpotongan. Misalnya, perpotongan sel pada kolom D baris 10 diberi nama sel D10. Setiap sel dibatasi Gridlines, yaitu garis vertikal dan horizontal yang terdapat dalam sebuah worksheet. Sel yang aktif (diseleksi) ditandai dengan adanya penunjuk sel (cell pointer).

1.2.1 Menyembunyikan Baris dan Kolom

Apabila Anda tidak ingin menampilkan atau mencetak baris tertentu dalam worksheet, maka Anda dapat menyembunyikan baris tersebut. Selain menyembunyikan baris, Anda juga dapat menyembunyikan kolom dalam worksheet.

1. Tekan kombinasi tombol Ctrl+N untuk membuat workbook baru. Input data ke dalam range A1:J10 seperti terlihat pada Gambar 1.7.

Gambar 1.7 Menyembunyikan baris.

2. Seleksi baris yang akan disembunyikan, misalnya baris 6 dan 7. Tekan kombinasi tombol Ctrl+9 atau klik tombol Format dalam tab Home group Cells kemudian pilih menu Hide & Unhide > Hide Rows.