Jurus Cepat Kuasai Formula Excel 2013

 

Jurus Cepat Kuasai Formula Excel 2013

Alfa Hartoko

PENERBIT PT ELEX MEDIA KOMPUTINDO

Jurus Cepat Kuasai Formula Excel 2013

Alfa Hartoko ©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

okti@elexmedia.id

717051968

ISBN: 978-602-04-5018-6

Dilarang keras menerjemahkan, memfotokopi, atau memperbanyak sebagian atau seluruh isi buku 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 JURUS MENGENAL FORMULA EXCEL 2013.......................1 1.1 Mengenal Function Library......................................................1 1.2 Mengenal Defined Names ......................................................4 1.3 Mengenal Formula Auditing....................................................5 1.4 Mengenal Calculation ............................................................6

Bab 2 JURUS MENGGUNAKAN FORMULA INSERT FUNCTION ....7 2.1 Menggunakan Insert Function Financial ....................................7 2.2 Menggunakan Insert Function Date & Time ............................ 10 2.3 Menggunakan Math & Trig.................................................. 12 2.4 Menggunakan Insert Function Statistical ................................ 14 2.5 Menggunakan Insert Function Lookup & Reference.................. 15 2.6 Menggunakan Insert Function Text ........................................ 18 2.7 Menggunakan Insert Function Logical.................................... 19 2.8 Menggunakan Insert Function Information.............................. 21

Bab 3 JURUS MENGOPTIMALKAN PENGGUNAAN FORMULA EXCEL ........................................................... 23 3.1 Mengoptimalkan Penggunaan Formula Financial.................... 23

3.1.1 Mengoptimalkan Penggunaan Formula PDURATION..... 23 3.1.2 Mengoptimalkan Penggunaan Formula XNPV............... 25

3.2 Mengoptimalkan Penggunaan Formula Logical ...................... 27 3.2.1 Mengoptimalkan Penggunaan Formula IFERROR........... 27 3.2.2 Mengoptimalkan Penggunaan Formula XOR................. 28

3.3 Mengoptimalkan Penggunaan Formula TEXT.......................... 30 3.3.1 Mengoptimalkan Penggunaan Formula FIXED............... 30 3.3.2 Mengoptimalkan Penggunaan Formula EXACT ............. 31

3.4 Mengoptimalkan Penggunaan Formula MAT & TRIG............... 33 3.4.1 Mengoptimalkan Penggunaan Formula SUMIFS............ 33

viii

3.4.2 Mengoptimalkan Penggunaan Formula LCM ................ 34 3.5 Mengoptimalkan Penggunaan Formula Statistical ................... 35

3.5.1 Mengoptimalkan Penggunaan Formula RANK.EQ......... 35 3.5.2 Mengoptimalkan Penggunaan Formula TREND............. 37

3.6 Mengoptimalkan Penggunaan Formula LookUp & Reference MATCH ............................................................................ 39

3.7 Mengoptimalkan Penggunaan Formula Date & Time ............... 41 3.7.1 Mengoptimalkan Penggunaan Formula DAYS............... 42 3.7.2 Mengoptimalkan Penggunaan Formula EDATE ............. 43

Bab 4 JURUS CEPAT KUASAI PENGGUNAAN FORMULA LOGICAL ....................................................... 45 4.1 Formula IF Bertingkat........................................................... 45 4.2 Formula IF Kombinasi AND.................................................. 47 4.3 Formula IF Kombinasi OR .................................................... 49 4.4 Formula IF Kombinasi Average............................................. 50 4.5 Formula IF Kombinasi SUMIF................................................ 52 4.6 Formula OR Kombinasi NOT................................................ 54 4.7 Formula IF Kombinasi RANK ................................................ 56 4.8 Formula IF Kombinasi OR+RANK ......................................... 58

Bab 5 JURUS CEPAT KUASAI FORMULA LOOKUP & REFERENCE .................................................. 61 5.1 Formula VLOOKUP LEFT ...................................................... 61 5.2 Formula VLOOKUP MID ...................................................... 64 5.3 Formula VLOOKUP RIGHT ................................................... 68 5.4 Formula HLOOKUP LEFT ...................................................... 70 5.5 Formula CHOOSE LEFT ....................................................... 72 5.6 Formula CHOOSE MID ....................................................... 74 5.7 Formula CHOOSE RIGHT .................................................... 76 5.8 Formula INDEX LEFT............................................................ 79 5.9 Formula INDEX MID............................................................ 80 5.10 Formula INDEX RIGHT....................................................... 82

Bab 6 JURUS PENERAPAN FORMULA EXCEL UNTUK PERPAJAKAN.................................................... 85 6.1 Pajak Kendaraan Bermotor .................................................. 85

6.1.1 Pajak Kendaraan Jenis Sepeda Motor ......................... 85 6.1.2 Pajak Kendaraan Jenis Mobil dan Truck....................... 91 6.1.3 Denda Pajak Kendaraan Sepeda Motor....................... 95

6.2 Pajak Jual Beli Tanah .......................................................... 97 6.3 Pajak Penghasilan Pribadi ................................................. 100 6.4 Pajak Hadiah dan Undian ................................................. 103 6.5 Pajak Royalti .................................................................... 104

ix

Bab 7 JURUS PENERAPAN FORMULA EXCEL UNTUK KEUANGAN USAHA........................................ 107 7.1 Laporan Keuangan Sederhana ........................................... 107 7.2 Membuat Laporan Penjualan.............................................. 111

7.2.1 Membuat Laporan Penjualan Harian ......................... 111 7.2.2 Membuat Laporan Penjualan Mingguan..................... 121

7.3 Membuat Buku Besar Laporan Keuangan Usaha .................. 126 7.4 Membuat Laporan Laba Rugi ............................................. 136

7.4.1 Laporan Laba Rugi Periode Tunggal .......................... 136 7.4.2 Laporan Laba Rugi Multi Periode............................... 139

Bab 8 PENERAPAN FORMULA EXCEL UNTUK ADMINISTRASI SEKOLAH................................. 147 8.1 Membuat Sistem Penerimaan Siswa Baru ............................ 147

8.1.1 Membuat Sistem Penambahan Nilai .......................... 148 8.1.2 Menentukan Hasil Seleksi Murid Baru........................ 150

8.2 Menghitung Besaran SPP dan Sumbangan Siswa Baru.......... 154 8.3 Menghitung Biaya Seragam Siswa Baru.............................. 157 8.4 Menentukan Kelengkapan Syarat Mengikuti Ujian................ 159 8.5 Menghitung Nilai Ujian Akhir Siswa ................................... 161 8.6 Menentukan Peringkat Nilai Ujian Akhir Siswa .................... 163 8.7 Membuat Daftar Nilai Siswa.............................................. 164 8.8 Membuat Daftar Penerima Beasiswa................................... 168

Bab 9 PENERAPAN FORMULA EXCEL UNTUK ADMINISTRASI PERKANTORAN ....................... 171 9.1 Pengarsipan Data Karyawan ............................................. 171 9.2 Sistem Absensi Karyawan.................................................. 173 9.3 Sistem Penggajian Karyawan............................................. 175 9.4 Sistem Kenaikan Gaji Karyawan ........................................ 178 9.5 Bonus Akhir Tahun............................................................ 181 9.6 Menentukan Masa Pensiun Karyawan................................. 184 9.7 Menghitung Besaran Pesangon Karyawan........................... 187 9.8 Menghitung Besaran Pensiun Karyawan.............................. 189

PROFIL PENULIS..................................................................... 193

1

JURUS MENGENAL

FORMULA EXCEL 2013

Microsoft Excel 2013 merupakan program aplikasi komputer yang cukup

bisa diandalkan untuk pengisian dan pengolahan data yang sebagian besar

berupa angka. Keunggulan dari Excel, yaitu duplikasi rumus yang bisa

diterapkan secara cepat dengan tetap menjaga kestabilan perhitungan.

Ketelitian dan kecermatan pengisian data berupa angka tentunya menjadi

suatu keharusan bagi setiap orang yang mengoperasikannya. Namun,

dengan penggunaan Formula dan memanfaatkan berbagai fasilitas pada

aplikasi Excel, waktu pengoperasian bisa dipersingkat tanpa mengurangi

ketelitian data yang dimasukkan.

Nah, untuk lebih mengenal apa sebenarnya Formula dan beragam

kelengkapannya bagi efektivitas penghitungan dan beragam perbaikan

data, Anda bisa mengikuti ulasan pada bab ini.

1.1 Mengenal Function Library

Ketika Anda membuka aplikasi Excel, akan muncul lembar kerja

dilengkapi tab Excel. Salah satu di antara tab tersebut, bila Anda

perhatikan adalah tab Formulas. Fasilitas Formula sebagaimana telah

1

2

dikemukakan sebelumnya bisa Anda pergunakan untuk membantu

pendataan, penghitungan, serta perbaikan data berupa angka dan teks.

Gambar 1.1 Kelengkapan fasilitas di dalam tab Formula

Beragam opsi yang ada di dalam tab Formula ini dikelompokkan ke

dalam empat klasifikasi, yaitu Function Library, Defined Names, Formula

Auditing, dan Calculation. Klasifikasi ini dilakukan untuk mempermudah

pengguna Excel ketika menggunakan opsi-opsi yang ada di dalam tab

Formula > Function Library.

Gambar 1.2 Kelengkapan fasilitas di dalam Function Library

Function Library merupakan kumpulan ikon shortcut dari fungsi-fungsi

rumus utama dalam Excel 2013. Pengguna Excel 2013 bisa memasukkan

beragam rumus data untuk mempermudah dan mempercepat perbaikan

dan pengolahan data baik berupa angka atau teks.

Tersedia 10 opsi yang bisa Anda gunakan di dalam Function Library.

Kesepuluh opsi ini beserta fungsinya bisa dilihat melalui tabel di bawah ini.

3

Nama Opsi  Fungsi 

Insert Function   

Untuk  memasukkan  rumus  formula  secara  langsung 

tanpa melalui  ikon  jenis‐jenis kategori  formula dalam 

Function Library. 

Autosum   Berisi  lima  formula  dasar,  yaitu  penjumlahan,  rata‐

rata, jumlah data, nilai maksimal, dan nilai minimal. 

Recently Used   Merupakan fasilitas untuk menampilkan formula yang 

telah digunakan sebelumnya. 

Financial    

Merupakan  kelompok  formula  yang  berhubungan 

dengan  perhitungan  keuangan  dan  bisnis.  Misalnya 

untuk menentukan  suku  bunga,  nilai  angsuran,  nilai 

investasi, penyusutan aset, dan lain sebagainya. 

Logical   

Merupakan  kelompok  formula  logika  dengan 

menempatkan parameter benar atau salah suatu data 

sebagai ciri khas dari kelompok formula logical. 

Text   

Merupakan  kelompok  formula  yang  berhubungan 

dengan  isi sel berupa teks. Misalnya untuk merapikan 

teks,  huruf  besar,  huruf  kecil,  menggabungkan  dua 

teks atau lebih, dan lain sebagainya. 

Date & Time   

Merupakan  kelompok  formula  yang  berhubungan 

dengan hari, tanggal, dan waktu. Misalnya menuliskan 

tanggal,  waktu,  penambahan  hari,  bulan,  tahun, 

Penambahan  detik,  menit,  dan  jam,  Menghitung 

jumlah hari kerja, dan lain sebagainya. 

Lookup &  

Reference   

Merupakan kelompok formula yang bisa digunakan untuk 

melakukan pencarian data  atau  sel  yang berbasis  kolom 

maupun  baris  dalam  suatu  range.  Misalnya  formula 

Vlookup, Hlookup, Index, Match, dan lain sebagainya.  

Math & Trig   

Merupakan  kelompok  formula  yang  bisa  digunakan 

untuk  menyelesaikan  formula  perhitungan 

matematika  dan  trigonometri.  Sebagai  contoh 

perpangkatan,  pembulatan  bilangan,  sinus,  cosinus, 

tangen, dan lain sebagainya. 

4

More Functions   

Merupakan fasilitas dalam function  library yang berisi 

empat kelompok besar formula Excel, yaitu Statistical, 

Engineering, Cube, dan Information. 

Gambar 1.3 Tabel fasilitas dalam Function Library

1.2 Mengenal Defined Names

Kategori opsi-opsi yang bisa digunakan untuk membuat, memperbaiki,

dan menampilkan elemen data ataupun nama adalah Defined Names.

Kategori ini berisikan empat fasilitas penting berkaitan dengan

pengkoordinasian elemen dalam suatu data.

Gambar 1.4 Kelengkapan fasilitas di dalam Defined Names

Keempat opsi memiliki fungsi-fungsi yang bisa Anda amati pada tabel di

bawah ini.

Nama Opsi  Fungsi 

Name Manager   

Merupakan  dashboard  yang  bisa  digunakan  untuk 

membuat, menambahkan, dan mengatur nama pengganti 

range sel tertentu dalam fasilitas Define Names. 

Define Name   Digunakan  untuk  membuat  nama  pengganti  suatu 

area sel yang ditentukan. 

Use in Formula   Menggunakan  nama  pengganti  area  atau  range  sel 

untuk dibuat rumus formula yang dinginkan. 

Create from Selection 

 

Membuat  nama  berdasarkan  seleksi  area  dengan 

pilihan  baris  paling  atas,  kolom  paling  kiri,  baris 

paling bawah, serta kolom paling kanan. 

5

1.3 Mengenal Formula Auditing

Sebagaimana namanya, Formula Editing adalah kategori opsi-opsi yang

bisa digunakan untuk memperbaiki formula yang telah dimasukkan ke

dalam lembar kerja Excel. Tersedia 7 opsi yang bisa Anda gunakan di

dalam ketegori ini.

Gambar 1.5 Kelengkapan fasilitas di dalam Formula Auditing

Bila Anda ingin mengetahui lebih lanjut kegunaan dari 7 opsi yang ada di

dalam Formula Auditing, lihatlah tabel di bawah ini.

Nama Opsi  Fungsi 

Trace  

Precedents   

Digunakan untuk menampilkan  alur dari  sel‐sel  input 

sebagai  titik awal anak panah menuju sel output dari 

formula dalam tabel. 

Trace Dependents 

 

Digunakan untuk mengetahui seberapa  jauh suatu sel 

input digunakan untuk menghitung output yang sesuai 

dengan formula yang ditentukan. 

Remove  

Arrows   

Digunakan  untuk menghapus  alur  anak  panah  Trace 

Precedents dan Trace Dependents. 

Show  

Formulas   

Digunakan  untuk menampilkan  penulisan  rumus  secara 

lengkap pada sel yang berisi formula perhitungan.  

Error  

Checking   

Digunakan untuk memeriksa apakah ada formula yang tidak 

sesuai dengan formula yang baku dalam program Excel. 

Evaluate 

Formula   

Digunakan  untuk  mengevaluasi  alur  perhitungan  dengan 

jumlah evaluasi sesuai dengan tingkatan rumus yang dibuat. 

Evaluasi  langkah  demi  langkah  sampai  pada  perhitungan 

akhir suatu rumus formula yang ada dalam tabel. 

193

PROFIL PENULIS

Alfa Hartoko lulus dari Teknik Industri UII, Yogyakarta pada

tahun 2001. Minat akan dunia penulisan, grafis, dan fotografi

membawanya menjadi penulis serta freelance desain grafis untuk

media online. Buku-buku yang pernah ditulisnya untuk Elex

Media Computindo adalah Kupas Tuntas Online dengan Ponsel,

Desain Merchandise Pilihan, Berlomba Jadi Populer di Youtube,

Kupas Tuntas Paypal, Kupas Tuntas Microsoft Office 2010,

50 Kasus Teknik Seleksi Pilihan, dan lain sebagainya.

Konsultasi melalui email : hartokoalfa@gmail.com

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/3427