Untuk istriku Vinsi, serta dua putriku Vilya dan Arwen.
Prakata Ebook ini membahas tentang salah satu fitur yang paling banyak digunakan oleh para pengguna aplikasi Excel yaitu Formula dan Fungsi. Jelas tidak mungkin untuk membahas semua hal tentang Formula dan Fungsi ini karena materinya yang sangat luas dan relatif kompleks. Oleh karena itu maka saya tambahkan awalan “Pengantar” pada judul ebook ini, karena yang dibahas dalam ebook ini lebih kearah konsep dan pemahaman dasar fitur Formula dan Fungsi pada aplikasi Excel yang umum digunakan dan diterapkan terutama dalam ruang lingkup pekerjaan sehari-hari. Ebook ini ditujukan untuk semua kalangan yang ingin mempelajari fitur Formula dan Fungsi pada Excel. Pokok bahasannya sengaja disusun secara bertahap, mulai dari definisi dasar, pengenalan formula dan fungsi dasar, fungsi lanjutan yang umum digunakan, hingga beberapa contoh penerapannya. Untuk mengikuti ebook ini diharapkan pembaca juga telah mengenal dasar penggunaan aplikasi Excel atau telah terbiasa bekerja dengan Excel walaupun hanya sebatas entri data saja. Ebook ini juga merupakan update dari rangkaian tulisan saya yang pernah dimuat di Microsoft TechNet Wiki, juga merupakan update dari ebook sebelumnya yang pernah saya rilis untuk komunitas. Materi dalam ebook ini disusun berdasarkan penggunaan dan penerapan Formula dan Fungsi pada aplikasi Excel 2013. Untuk versi Excel lainnya seperti misalnya Excel 2007, 2010, atau 2016 secara garis besar tetap sama, hanya saja mungkin perlu beberapa penyesuaian lagi. Misalnya saja untuk kotak dialog yang ditampilkan hingga letak tombol maupun menu perintah yang mungkin ada perbedaan. Akhir kata, mudah-mudahan ebook ini bisa berguna dan bermanfaat bagi siapapun yang membacanya. Dan segala saran, kritik, koreksi, hingga celaan untuk ebook ini tentunya dengan senang hati akan saya terima sebagai bekal masukan yang sangat berharga untuk ebook-ebook lainnya yang mungkin akan saya tulis kemudian.
Aris Lesmana | http://arhiez.net Medio Bandung, Februari – 2017
http://arhiez.net
Halaman | 1
Untuk mempermudah Anda dalam mengikuti pokok bahasan materi dalam ebook ini, Anda dapat mendownload file-file Excel yang digunakan sebagai contoh dalam ebook ini pada tautan berikut ini: http://arhiez.net/files/ebook-2017-02.zip
http://arhiez.net
Halaman | 2
Daftar Isi Prakata ....................................................................................................................... 1 Daftar Isi .................................................................................................................... 3 1. Berkenalan dengan Formula dan Fungsi ...................................................... 6 Definisi Formula .....................................................................................................................................6 Definisi Fungsi ........................................................................................................................................7
2. Seting Regional ................................................................................................. 9 3. Konsep Dasar..................................................................................................... 11 Mengenal Penggunaan Fungsi ..................................................................................................... 13 Tanda Sama-Dengan ........................................................................................................................ 17 Tanda Kurung ...................................................................................................................................... 19 Tanda Pemisah Argumen ................................................................................................................ 19 Tanda Kutip .......................................................................................................................................... 20 Operator Hitung Dasar .................................................................................................................... 20 Operator Pembanding ..................................................................................................................... 22 Menggunakan Alamat Sel dan Range........................................................................................ 23 Memberi Nama Sel dan Range ..................................................................................................... 24 Menggabungkan Fungsi ................................................................................................................. 27
4. Auto Fill ............................................................................................................. 29 5. Sel Absolut ....................................................................................................... 33 6. Fungsi Dasar .................................................................................................... 38 Fungsi SUM .......................................................................................................................................... 38 Fungsi PRODUCT................................................................................................................................ 40 Fungsi AVERAGE ................................................................................................................................. 41 Fungsi COUNT dan COUNTA ........................................................................................................ 42 Fungsi MAX dan MIN ....................................................................................................................... 44
http://arhiez.net
Halaman | 3
7. Fungsi Pembulatan ......................................................................................... 46 Fungsi INT ............................................................................................................................................. 46 Fungsi ROUND, ROUNDUP, dan ROUNDDOWN................................................................... 46
8. Fungsi Tanggal dan Waktu .......................................................................... 49 Fungsi DATE, DAY, MONTH, dan YEAR ..................................................................................... 49 Fungsi TIME, HOUR, MINUTE, dan SECOND ........................................................................... 51
9. Fungsi Teks ...................................................................................................... 54 Fungsi UPPER, LOWER, dan PROPER .......................................................................................... 54 Fungsi LEFT, MID, dan RIGHT ........................................................................................................ 56
10. Fungsi Logika ................................................................................................... 58 Fungsi COUNTIF ................................................................................................................................. 58 Fungsi SUMIF ....................................................................................................................................... 60 Fungsi AVERAGEIF ............................................................................................................................. 61 Fungsi IF Tunggal ............................................................................................................................... 62 Fungsi IF Bercabang (Nested IF) ................................................................................................... 64 Fungsi OR (IF-OR) .............................................................................................................................. 68 Fungsi AND (IF-AND) ....................................................................................................................... 70
11. Fungsi Referensi .............................................................................................. 73 Fungsi VLOOKUP................................................................................................................................ 73 Fungsi HLOOKUP ............................................................................................................................... 80
12. Fungsi Pelengkap ............................................................................................ 83 Fungsi TODAY dan NOW ................................................................................................................ 83 Fungsi LEN ............................................................................................................................................ 84 Fungsi ROMAN ................................................................................................................................... 84 Fungsi CONCATENATE..................................................................................................................... 85 Fungsi TEXT .......................................................................................................................................... 86
13. Troubleshooting.............................................................................................. 89 Kode Kesalahan .................................................................................................................................. 89 Pesan Kesalahan ................................................................................................................................. 92 Dokumentasi Bantuan ...................................................................................................................... 93 Melihat Hasil Sementara pada Bagian Formula Tertentu ................................................... 93 Melihat Seluruh Formula ................................................................................................................. 94 Trace Precedents dan Trace Dependents ................................................................................. 95
http://arhiez.net
Halaman | 4
Evaluasi Formula................................................................................................................................. 97 Error Checking ..................................................................................................................................... 97
14. Tips .................................................................................................................... 99 15. Contoh Penerapan ........................................................................................ 101 Contoh 1: Menghitung Jumlah Barang dari Beberapa Gudang .....................................101 Contoh 2: Membuat Laporan Persentase Komisi Sales .....................................................103 Contoh 3: Menghitung Total Upah Karyawan ......................................................................105 Contoh 4: Mengurai Struktur NIM (Nomor Induk Mahasiswa) ......................................107 Contoh 5: Menghitung Discount Penjualan Rokok.............................................................109 Contoh 6: Membuat Laporan Biaya Paket Wisata Harian.................................................111
Penutup .................................................................................................................. 114 Tentang Penulis .................................................................................................... 115
http://arhiez.net
Halaman | 5
1. Berkenalan dengan Formula dan Fungsi Salah satu hal yang membuat aplikasi Microsoft Excel banyak digunakan di berbagai kalangan, terutama di kalangan bisnis, organisasi, hingga kalangan pendidikan adalah adanya fitur Formulas dan Functions yang di Indonesia mungkin lebih dikenal dengan istilah rumus Excel, formula Excel, atau fungsi Excel. Formula dan fungsi ini digunakan untuk membantu Anda dalam mengerjakan beragam proses perhitungan dan olah data secara cepat dan semi otomatis. Formula dan fungsi ini bisa Anda gunakan dalam perhitungan data yang sederhana hingga yang kompleks. Baik untuk data berupa angka, data teks, data tanggal, data waktu, atau kombinasi dari data-data tersebut. Contoh sederhana untuk penerapan formula ini, misalnya saja semua karyawan dalam perusahaan akan mendapat bonus sebesar 5% dari pendapatan mereka perbulan. Bagaimana cara menghitungnya? Dengan proses perhitungan biasa maka Anda tentunya harus menghitung data karyawan satu demi satu. Namun dengan menggunakan formula pada Excel, Anda cukup mengerjakan perhitungan untuk satu data karyawan saja, dan data-data karyawan yang lainnya akan mengikuti. Dari contoh sederhana ini bisa dilihat bahwa dengan adanya formula maka pekerjaan Anda bisa diselesaikan dengan lebih cepat dan lebih efisien lagi.
Definisi Formula Secara garis besar Formula pada aplikasi Excel adalah suatu persamaan untuk menghitung atau mengolah nilai dan data tertentu dengan tujuan untuk mendapatkan hasil yang diharapkan. Penulisan formula selalu diawali tanda sama dengan dan umumnya melibatkan operator dasar matematika. Sebagai contoh, misalnya Anda hendak menghitung 10 + 15 maka formulanya adalah:
=10+15
http://arhiez.net
Halaman | 6
Simbol tanda kurung ataupun tanda bantuan lainnya yang umum digunakan dalam operasi matematika juga bisa Anda gunakan pada formula Excel, misalnya:
=(25+5)*8 Nilai dalam aplikasi Excel dimuat didalam sel atau range yang memiliki alamat tertentu, oleh karena itu maka penulisan formula pun umumnya merujuk pada alamat sel-nya dan bukan pada nilainya, misalnya:
=(A5*A6)+(B5/C5) Atau dalam beberapa kondisi, bisa juga Anda kombinasikan antara alamat sel dengan suatu nilai tertentu, misalnya:
=(B4+C4)*150
Definisi Fungsi Jika Anda bekerja dengan data-data yang kompleks, maka formula yang digunakan juga umumnya semakin kompleks dan tentunya akan semakin panjang. Sebagai contoh, misalnya saja Anda hendak menghitung nilai rata-rata penjualan dari 8 unit produk yang datanya berada pada sel B1 hingga B8, maka formula yang digunakan adalah:
=(B1+B2+B3+B4+B5+B6+B7+B8)/8 Formula sepanjang itu hanya untuk 8 unit produk saja, bagaimana jika produknya ada 100 unit? atau 10000 unit? Sebagai solusinya maka Excel menyediakan fitur yang dinamakan dengan Function atau fungsi. Secara garis besar fungsi ini bisa dibilang sebuah preset dari formula yang bertujuan untuk menyederhanakan formula hingga membuat proses perhitungan atau pengolahan data menjadi lebih singkat dan tentunya relatif lebih mudah untuk dikerjakan. Fungsi-fungsi dalam Excel memiliki nama-nama yang unik. Jika Anda pernah mendengar kata SUM, itu adalah nama salah satu fungsi yang paling umum diterapkan pada aplikasi Excel yang kegunaannya untuk menghitung data pada range tertentu.
http://arhiez.net
Halaman | 7
Lalu bagaimana sebuah fungsi bisa menyederhanakan perhitungan? dari contoh sebelumnya dimana Anda hendak menghitung nilai rata-rata dari 8 unit produk, maka Anda cukup menggunakan fungsi untuk menghitung nilai rata-rata, yaitu AVERAGE. Fungsi AVERAGE ini kemudian diterapkan dalam formula dengan aturan sebagai berikut:
=AVERAGE(range) Formula tersebut sudah dapat memberikan gambaran bagi Anda bahwa sebuah fungsi dapat menyederhanakan sekaligus menyingkat sebuah formula yang panjang menjadi lebih sederhana. Dalam penerapannya, fungsi juga umumnya mengacu pada alamat sel. Hingga untuk contoh sederhana diatas, dimana data penjualan berada pada sel B1 hingga B8 maka formulanya akan menjadi:
=AVERAGE(B1:B8) Dengan adanya fungsi maka struktur formula pada aplikasi Excel akan tersusun dari banyak komponen, yaitu fungsi-fungsi yang digunakan, data yang akan dihitung dan diolah lebih lanjut, alamat sel atau range, operator dasar matematika, operator pembanding, hingga simbol-simbol atau tanda pelengkap lainnya. Semua komponen tersebut akan disusun dalam argumen-argumen tertentu yang membantu proses perhitungan dalam formula tersebut. Contoh struktur sebuah formula dengan komponen atau argumen-argumen penyusunnya dapat dilihat berikut ini.
=VLOOKUP(C4, $A$12:$C$16, IF(B4<=3, 2, 3), TRUE)+G3 Fungsi AVERAGE serta VLOOKUP dalam contoh diatas, serta konsep dasar tentang argumen maupun komponen-komponen penyusun lainnya dalam suatu formula dan fungsi akan dibahas pada babbab selanjutnya dalam ebook ini.
http://arhiez.net
Halaman | 8
2. Seting Regional Aplikasi Excel umumnya berhubungan erat dengan data dalam bentuk angka atau bilangan, nilai mata uang, tanggal, hingga satuan waktu. Dan seperti Anda ketahui, aturan penulisan data-data tersebut untuk tiap negara berbeda. Sebagai contoh, tanda pemisah ribuan di Indonesia menggunakan tanda titik, sementara di USA menggunakan tanda koma. Contoh lainnya misalnya penanggalan, tanggal 2/1/2017 di Indonesia akan dibaca tanggal 2 Januari 2017, sementara di USA dibaca tanggal 1 Februari 2017. Adanya perbedaan dalam format penulisan dan pembacaan ini tentunya dapat mengakibatkan kesalahan hasil akhir jika Anda menggunakan formula atau bisa juga mengakibatkan formula error atau tidak dapat dijalankan. Oleh karena itu, sebelum Anda mulai bekerja dengan aplikasi Excel, Anda harus menentukan dulu aturan negara atau region mana yang akan Anda gunakan. Pada sistem operasi Windows, pengaturan ini dapat Anda pilih lewat Control Panel. Pada Control Panel tersebut Anda tinggal mencari pilihan Region, Regional Settings, atau Regional and Language Options. Sebagai contoh, pada sistem operasi Windows 10 caranya sebagai berikut:
Pastikan aplikasi Excel dalam kondisi tertutup. Klik-kanan tombol Start Windows, lalu klik Control Panel.
http://arhiez.net
Halaman | 9
Klik pada icon Region.
Pada jendela Region yang ditampilkan, pilih seting regional yang ingin Anda gunakan dengan memilihnya di bagian Format dan klik tombol OK untuk menyetujuinya.
Untuk keseragaman, semua pokok bahasan dalam ebook ini akan menggunakan seting regional English (United States) yang merupakan seting region default pada Windows.
http://arhiez.net
Halaman | 10
