materi kkpi excel
TRANSCRIPT
-
5/20/2018 Materi Kkpi Excel
1/72
Ketrampilan Komputer dan Pengelolaan Informasi
Diktat ini disusun sebagai pegangan siswa untuk belajar Ketrampilan Komputer dan
Pengelolaan Informasi (KKPI) dengan Kompetensi Dasar Mengoperasikan Perangkat Lunak
Pengolah Angka (Spreadsheet Software)
8/12/2014 - SMK Neger i 2 Depok Sl ema n
Ketrampilan Komputer dan Pengelolaan Informasi - KKPI
-
5/20/2018 Materi Kkpi Excel
2/72
HALAMAN SAMPUL
-
5/20/2018 Materi Kkpi Excel
3/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel ii
DAFTAR ISI
HALAMAN SAMPUL ............................................................................................................................ i
DAFTAR ISI......................................................................................................................................... ii
1. PENGANTAR............................................................................................................................... 1
a. Definisi Formula ................................................................................................................... 1
b. Definisi Fungsi ...................................................................................................................... 1
2. MENGENAL FORMULA ............................................................................................................... 2
a. Menggunakan Function Library ............................................................................................ 3
b. Menuliskan Formula Secara Manual ..................................................................................... 5
3. MENGENAL TATA CARA PENULISAN ........................................................................................... 6
a. Tanda Sama Dengan ............................................................................................................. 6
b. Tanda Kurung ....................................................................................................................... 6
c. Tanda Pemisah Argumen / Variabel...................................................................................... 6
d. Tanda Kutip / Petik ............................................................................................................... 7
e. Gunakan Alamat Sel / Range ................................................................................................ 8
4. MENGENAL NAMA SEL ............................................................................................................... 9
a. Menggunakan Name Box ..................................................................................................... 9
b. Menggunakan Name Manager ........................................................................................... 11
c. Operator Hitung Dasar ....................................................................................................... 121) Cara Penulisan ............................................................................................................. 12
2) Cara Penggunaan ......................................................................................................... 12
d. Operator Pembanding ........................................................................................................ 13
e. Auto Fill ............................................................................................................................. 14
f. Sel Absolut ......................................................................................................................... 16
5. Fungsi SUM .............................................................................................................................. 20
6. Fungsi AVERAGE...................................................................................................................... 21
7. Fungsi COUNT ......................................................................................................................... 22
8. Fungsi MAX dan MIN................................................................................................................ 23
9. Fungsi COUNTIF ...................................................................................................................... 24
10. Fungsi SUMIF .......................................................................................................................... 26
11. Penggabungan Fungsi .............................................................................................................. 27
a. Contoh 1: Menggabungkan Fungsi SUM dengan Perkalian ................................ ................. 28
b. Contoh 2: Menggunakan 2 Fungsi SUMIF dalam 1 Formula ................................ ................ 29
12. Fungsi IF................................................................................................................................... 30
13. Fungsi IF Bercabang ................................................................................................................. 32
-
5/20/2018 Materi Kkpi Excel
4/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel iii
14. Fungsi OR ................................................................................................................................. 34
15. Fungsi AND .............................................................................................................................. 36
16. Fungsi Tanggal ......................................................................................................................... 38
17. Fungsi Waktu ........................................................................................................................... 40
18. Fungsi Pembulatan .................................................................................................................. 42
19. Fungsi Teks .............................................................................................................................. 43
20. Fungsi VLOOKUP ...................................................................................................................... 45
21. Fungsi HLOOKUP ...................................................................................................................... 51
22. Pesan Kesalahan ...................................................................................................................... 52
a. ##### ................................................................................................................................. 53
b. #REF! ................................................................................................................................. 53
c. #VALUE! ............................................................................................................................. 53d. #DIV/0! .............................................................................................................................. 53
e. #NULL! ............................................................................................................................... 54
f. #NUM! ............................................................................................................................... 54
g. #NAME? ............................................................................................................................. 54
h. #N/A! ................................................................................................................................. 54
23. Troubleshooting....................................................................................................................... 55
a. Peringatan Kesalahan ......................................................................................................... 55
b. Dokumentasi Bantuan ........................................................................................................ 55
c. Melihat Seluruh Formula .................................................................................................... 56
d. Melihat Hasil Fungsi pada Suatu Formula ................................ ................................ ........... 57
e. Trace Precedents dan Trace Dependents ............................................................................ 58
f. Evaluasi Formula ................................................................................................................ 59
g. Error Checking ................................................................................................................... 60
24. Tips .......................................................................................................................................... 61
25. Contoh Penerapan ................................................................................................................... 62
a. Contoh 1: Laporan Persentase Komisi Sales ................................ ........................................ 62
b. Contoh 2: Menghitung Total Upah Karyawan ................................ ................................ ..... 63
c. Contoh 3: Mengurai Struktur NIM (Nomor Induk Mahasiswa) ................................ ............ 64
d. Contoh 4: Discount Penjualan Rokok .................................................................................. 65
e. Contoh 5: Biaya Paket Wisata Harian.................................................................................. 66
26. Penutup ................................................................................................................................... 68
-
5/20/2018 Materi Kkpi Excel
5/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 1
1.
PENGANTAR
Salah satu hal yang membuat aplikasi Excel terkenal adalah kelengkapan fitur Formulas dan
Functions yang di Indonesia mungkin lebih dikenal dengan istilah rumus. Formula dan fungsi ini
digunakan untukmembantu Anda dalam mengerjakan beragam proses perhitungan data secara
cepat dan semi otomatis. Formula ini bisa Anda gunakan dalam perhitungan yang sederhanahingga yang kompleks, baik untuk data berupa angka, data teks, data tanggal, data waktu, atau
kombinasi dari data-data tersebut.
Contoh penggunaannya misalnya semua karyawan 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, maka 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 hingga bisa membuat Anda lebih produktif dan lebih efisien lagi.
a. Definisi Formula
Secara garis besar Formula pada aplikasi Excel adalah suatu persamaan matematika untuk
menghitung nilai-nilai tertentu dengan tujuan untuk mendapatkan hasil yang diharapkan.
Penulisan formula selalu diawali tanda sama dengan dan umumnya melibatkan operator
dasar matematika. Sebagai contoh, misalkan Anda hendak menghitung 10 + 15 maka
formulanya adalah: =10+15
Simbol tanda kurung ataupun tanda-tanda 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 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
b. 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, misalkan
saja Anda hendak menghitung nilai rata-rata penjualan dari 8 unit produk, maka formulayang digunakan adalah: =(prod1+prod2+prod3+prod4+prod5+prod6+prod7+prod8)/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 data menjadi lebih singkat
dan tentunya relatif lebih mudah untuk dikerjakan.
Fungsi-fungsi dalam Excel memiliki nama-nama yang unik. Jika Anda pernah mendengar kataSUM, itu adalah nama salah satu fungsi yang paling umum diterapkan pada aplikasi Excel
-
5/20/2018 Materi Kkpi Excel
6/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 2
yang kegunaannya untuk mengitung data pada range tertentu. 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, dengan demikian formula yang digunakan adalah:
=AVERAGE(range_produk)
Disini sudah jelas terlihat bagaimana sebuah fungsi dapat menyederhanakan sekaligus
menyingkat sebuah formula yang panjang menjadi lebih sederhana. Dalam penerapannya,
fungsi juga umumnya mengacu pada alamat sel. Misalkan saja data untuk 8 unit produk
tersebut terdapat pada sel B5 hingga B12 maka formulanya akan menjadi:
=AVERAGE(B5:B12)
Dari penjelasan singkat diatas, dapat dilihat bahwa struktur formula pada aplikasi Excel
tersusun dari banyak komponen, seperti misalnya data berupa nilai yang akan dihitung,
alamat sel atau range, operator dasar matematika, operator pembanding, fungsi, serta
simbol-simbol atau tanda pelengkap lainnya yang membantu proses perhitungan dalam
formula tersebut. Contoh sebuah formula dengan struktur yang cukup lengkap dapat dilihatberikut ini :
=VLOOKUP(C4;$A$12:$C$16;IF(B4
-
5/20/2018 Materi Kkpi Excel
7/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 3
Formula yang Anda tuliskan bisa dilihat sekaligus dimuat dalam sebuah bar yang dinamakan
dengan Formula Bar. Anda tentu mengenal lokasi formula bar ini yang merupakan suatu bar
kosong dengan tulisan fx disampingnya serta terletak di bagian atas bidang kerja utama
aplikasi Excel.
Jika suatu sel Anda isi dengan data biasa maka isi sel ini akan sama dengan isi pada formula
bar, namun jika suatu sel Anda isi dengan formula maka sel akan menampilkan hasil
perhitungan dan formula bar akan memuat formulanya. Sebagai contoh, pada gambar
berikut ini dapat dilihat bahwa sel D3 menampilkan data 25000 yang merupakan hasil
perhitungan, sementara formula bar menampilkan =B3*C3 yang merupakan formula yang
digunakan. Jika Anda hendak menyunting formula tersebut maka Anda tinggal meng-klik
mouse pada formula bar atau menekan tombol F2pada keyboard.
Untuk menuliskan formula ini, ada 2 cara yang bisa dilakukan yaitu dengan menggunakan
Function Libraryserta cara manual. Berikut ini penjelasan singkat untuk kedua cara tersebut.
a. Menggunakan Function Library
Pada aplikasi Excel 2010, Function Librarybisa Anda temukan pada ribbon didalam tab
Formulas. Isinya berupa seperangkat tombol-tombol perintah penggunaan fungsi yang
terbagi kedalam beberapa kategori spesifik seperti misalnya fungsi yang berhubungan
dengan akunting dan keuangan dimuat dalam kategori Financial, fungsi yang
berhubungan dengan operasi matematika dimuat dalam kategori Math & Trig, dan
demikian seterusnya.
Untuk menggunakannya, klik pada sel dimana Anda ingin menuliskan hasil perhitungan
kemudian klik salah satu tombol yang sesuai pada group Function Library tersebut.
Berikutnya pada daftar pilihan fungsi yang ditampilkan, klik pada fungsi yang ingin Andagunakan.
-
5/20/2018 Materi Kkpi Excel
8/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 4
Kotak dialog Function Argumentskemudian akan ditampilkan dan Anda tinggal mengisikan
argumen pada kolom-kolom isian yang telah disediakan didalam kotak dialog tersebut sesuai
dengan aturan penulisan formulanya. Argumen-argumen ini akan dibahas di bagian lain
dalam rangkaian artikel ini.
Selain tombol-tombol perintah yang spesifik untuk kategori fungsi tertentu, dalam group
Function Library ini terdapat beberapa tombol perintah lain yang bisa Anda gunakan untuk
mempermudah penulisan formula, yaitu:
Insert Functions:Jika Anda klik maka akan ditampilkan kotak dialog Insert Functionyang
berisikan seluruh fungsi yang ada berikut kategorinya. Anda tinggal memilih fungsi yang
sesuai untuk langsung menggunakannya. Fasilitas pencarian juga disediakan dalam kotak
dialog ini untuk membantu Anda mencari fungsi tertentu secara cepat.
AutoSum: Tombol ini bisa Anda gunakan untuk menuliskan fungsi-fungsi dasar yang
umum digunakan.
Recently Used:Berisikan daftar fungsi-fungsi terakhir yang pernah Anda kerjakan.
-
5/20/2018 Materi Kkpi Excel
9/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 5
b. Menuliskan Formula Secara Manual
Selain dengan bantuan Function Library, Anda juga dapat menuliskan formula berikut
argumen-argumen yang menyertainya secara manual. Cara ini mungkin terlihat lebih sulittapi pada penerapannya kadang malah lebih mudah dengan cara manual ini dibandingkan
dengan menggunakan Function Library. Banyak para praktisi Excel yang malah selalu
menuliskan formula dengan cara manual ini, alasannya terutama karena penulisan
argumen formula bisa dikustomisasi / divariasikan dengan lebih bebas. Keuntungan
lainnya adalah Anda akan lebih mengenal lagi argumen untuk suatu formula tertentu
hingga Anda bisa menuliskannya pada sembarang versi Excel tanpa harus mencari tahu
terlebih dahulu dimana letak dan bagaimana cara penggunaan tombol-tombol perintah
Function Library pada tiap-tiap versi Excel.
Mengingat banyaknya pengguna Excel yang lebih memilih penulisan formula secara
manual maka pada beberapa versi Excel yang terakhir (2007 / 2010 / 2013), Microsoftpun memberikan fitur-fitur bantuan untuk mempermudah penulisan formula secara
manual ini. Fitur-fitur bantuan ini diantaranya adalah:
Intellisense: Fitur ini merupakan sebuah fitur AutoComplete yang akan menjaga
penulisan nama fungsi dari kesalahan ketik sekaligus mempercepat Anda dalam
menuliskan nama fungsi yang akan digunakan. Sebagai contoh misalkan Anda hendak
menggunakan fungsi SUM, maka didalam sel Anda tinggal mengetikan tanda =
diikuti huruf S. Perhatikan bahwa Excel akan menampilkan daftar semua nama
fungsi yang berawalan huruf S. Berikutnya dari daftar tersebut Anda tinggal klik-
ganda pada nama fungsi SUMuntuk memuatnya pada formula bar.
Keterangan Penulisan Argumen: Setelah Anda memilih nama fungsi, berikutnya
Excel akan memberikan bantuan informasi tentang argumen-argumen untuk fungsi
tersebut. Sebagai contoh pada gambar berikut ini, dapat dilihat argumen-argumen
yang digunakan pada fungsi VLOOKUP, yaitu lookup value, table_array, dst.
-
5/20/2018 Materi Kkpi Excel
10/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 6
3.
MENGENAL TATA CARA PENULISAN
Ada beberapa aturan penulisan formula yang harus diketahui dan diperhatikan, terutama
jika Anda hendak menuliskan formula dengan cara manual. Berikut ini adalah aturan-aturan
penulisan tersebut.
a. Tanda Sama Dengan
Tanda sama dengan atau = merupakan tanda awal dari penulisan formula yang mutlak
harus dituliskan jika Anda hendak menggunakan formula apapun. Tanda ini juga bisa
digunakan sebagai rujukan ke data pada lokasi lain. Sebagai contoh misalkan sel A1
berisikan data 10. Pada sel A5, jika Anda ketikan tanda = (tanpa tanda kutip) yang
diikuti dengan penekanan tombol Entermaka sel A5juga akan berisi data 10.
b. Tanda Kurung
Tanda kurung atau ( ) digunakan untuk memisah bagian-bagian yang dianggap perlu
dalam suatu formula. Penggunaan tanda ini secara garis besar sama dengan
penggunaan dalam operasi matematika biasa, tentunya agar perhitungan menjadi lebih
mudah untuk dibaca dan dianalisa.
Dalam kaitannya dengan penulisan fungsi, maka tanda kurung umum digunakan setelah
menuliskan nama fungsi-nya, selain itu juga umum digunakan untuk memisahkan satu
fungsi dengan fungsi lainnya jika Anda menggunakan multiple fungsi dalam suatu
perhitungan.
Berikut ini contoh beberapa penggunaan tanda kurung dalam penulisan formula pada
aplikasi Excel:
=(A1+A2)*(B1+B2)
=SUM(A1:A7)
=A1*(SUM(B1:B10))
dst
c. Tanda Pemisah Argumen / Variabel
Dalam penerapan formula lanjutan, Anda akan banyak menggunakan banyak argumen
ataupun variabel dalam suatu formula. Untuk memisahkan variabel-variabel ini ada dua
tanda yang digunakan yaitu tanda koma , atau titik-koma ;. Tanda mana yang harus
Anda gunakan? Ini disesuaikan dengan seting regional yang Anda gunakan pada
Windows dan Excel:
Untuk seting regional Indonesia, maka tanda pemisah variabel yang digunakan
adalah titik-komaatau ;
Untuk seting regional selain Indonesia, maka tanda pemisah variabel yang
digunakan adalah komaatau ,
-
5/20/2018 Materi Kkpi Excel
11/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 7
Cara memeriksa seting regional ini pada Windows 7 adalah sebagai berikut:
Pastikan aplikasi Excel dalam kondisi tertutup.
Start Menu > Control Panel > Region & Language.
Pada jendela Region and Languageyang ditampilkan, pilih seting regional yang ingin
Anda gunakan dengan memilihnya di bagian Format dan klik tombol OK untukmenyetujuinya.
d.
Tanda Kutip / Petik
Tanda kutip atau digunakan jika Anda hendak menggunakan isi atau data pada
sebuah sel untuk digunakan pada formula. Sebagai contoh pada tabel dibawah ini
menunjukan formula untuk menghitung jumlah data Jeruk yaitu dengan menggunakan
fungsi COUNTIF(penggunaan fungsi ini lebih lanjut dijelaskan pada tulisan yang lain).
Jika formula dituliskan tanpa tanda kutip yaitu: =COUNTIF(B2:B9;Jeruk)
-
5/20/2018 Materi Kkpi Excel
12/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 8
Maka hasilnya adalah 0karena Excel jelas tidak mengenali data Jeruk tersebut. Namun
jika data Jeruk pada formula tersebut kita tambahkan tanda kutip seperti berikut ini:
=COUNTIF(B2:B9;Jeruk)
Hasilnya akan keluar yaitu 3karena dengan ditambahkannya tanda kutip tersebut makaaplikasi Excel akan mengenali data Jeruk tersebut.
Penggunaan tanda kutip ini akan banyak Anda temukan dalam artikel yang lain pada
rangkaian artikel ini yang membahas tentang contoh-contoh penggunaan formula
secara spesifik.
e. Gunakan Alamat Sel / Range
Penulisan suatu formula hendaknya selalu merujuk pada alamat sel / range dan jangan
merujuk langsung pada isi sel atau datanya, karena hal ini akan membuat formula
menjadi statis dan terkunci hanya untuk data tersebut saja. Agar lebih jelas, perhatikancontoh berikut ini.
Pada sel D2akan diisikan formula untuk menjumlahkan Data 1dan Data 2. Jika formula
langsung merujuk pada isi sel-nya, maka formulanya adalah:
=10+7
Hasilnya tentu saja benar yaitu 17, namun jika Anda salin formula tersebut untuk
menjumlahkan baris berikutnya, maka hasilnya tetap 17. Hal ini disebabkan formula
menjadi statis atau terkunci hanya untuk menghitung baris yang pertama saja.
Namun jika Anda merujuk pada alamat selnya, yaitu B2dan C2, maka formulanya akan
menjadi:
=B2+C2
-
5/20/2018 Materi Kkpi Excel
13/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 9
Hasilnya tetap 17namun keuntungannya formula akan bersifat dinamis hingga jika Anda
salin formula ke baris berikutnya maka alamat sel akan mengikuti dan menghasilkan
nilai yang benar yaitu 11yang didapat dari formula baru yaitu:
=B3+C3
Pahami baik-baik beragam aturan penulisan formula ini karena salah sedikit saja dalam
menempatkan simbol atau mungkin tanda tertentu pada formula akan menyebabkan
formula tidak akan berfungsi sebagaimana mestinya.
4.
MENGENAL NAMA SEL
Formula pada aplikasi Excel tentunya akan sangat tergantung pada alamat sel termasuk range
yang merupakan gabungan dari beberapa sel sekaligus. Dalam sebuah formula yang kompleks,
sering terdapat kesalahan dalam penulisan alamat sel atau range ini, hal ini rasanya wajar
mengingat alamat sel atau range tersebut yang merupakan kombinasi huruf dan angka dalam
jumlah banyak.
Untuk mempermudah penulisan, maka Anda bisa memberi Namapada alamat sel atau range
tersebut, nama ini tentunya bebas dan disesuaikan dengan keinginan Anda sendiri, namuntentunya disarankan agar Anda menggunakan penamaan yang mudah untuk diingat.
a. Menggunakan Name Box
Cara menggunakan penamaan untuk alamat sel atau range ini sangat mudah. Perhatikan
contoh formula sederhana berikut ini.
-
5/20/2018 Materi Kkpi Excel
14/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 10
Formula untuk menghitung Totalpada sel C10adalah :=SUM(C4:C8)
Untuk mempermudah, maka range C4:C8akan diberi nama, misalnya saja DATA1. Caranya,
sorot atau blok sel C4sampai dengan C8. Lalu perhatikan disudut kiri atas, terdapat kolom
kosong yang memuat keterangan alamat sel, kolom kosong ini disebut dengan Name Box.
Dalam kondisi sel yang masih tersorot, ketikan nama yang Anda inginkan pada Name Box
tersebut, dalam contoh ini yaitu DATA1dan akhiri dengan menekan Enterpada keyboard.
Sampai tahapan ini, range C4:C8 telah diberi nama yaitu DATA1. Hingga jika kita hendak
menggunakan formula pada range tersebut, jika formula yang sebelumnya adalah
=SUM(C4:C8)
Dan setelah disederhanakan alamat range-nya menjadi nama DATA1, maka formulanya kini
menjadi =SUM(DATA1)
-
5/20/2018 Materi Kkpi Excel
15/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 11
b. Menggunakan Name Manager
Dalam sebuah workbook yang kompleks yang berisikan banyak nama, maka dipastikan
Anda akan kesulitan untuk mengingat nama apa saja yang telah Anda buat dan nama
tersebut Anda definisikan untuk alamat sel atau range apa. Untuk membantu pengaturan
nama-nama ini, maka Anda dapat menggunakan fitur Name Manager. Fitur ini terdapat
pada tab Formulas, didalam group Defined Names. Cobalah untuk melakukan klik mouse
pada tombol Name Managertersebut untuk menampilkan kotak dialog Name Manager.
Pada kotak dialog Name Manager tersebut, dengan mudah Anda dapat melihat daftar
nama-nama yang telah Anda buat berikut alamat sel atau range-nya. Anda juga dapat
menghapusnya, merubahnya, ataupun membuat nama yang baru pada kotak dialog ini.
-
5/20/2018 Materi Kkpi Excel
16/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 12
c. Operator Hitung Dasar
Kegunaan utama dari formula pada aplikasi Excel tentunya untuk membantu proses
penghitungan agar lebih cepat dan mudah. Urusan menghitung ini jelas tidak lepas dari
peran Operator Hitung Dasar Matematikayang tentunya telah Anda kenal dengan baik,
yaitu penambahan, pengurangan, perkalian, dan pembagian. Sebelum masuk ke
penggunaan formula yang sesungguhnya, penggunaan operator hitung dasar ini harus
dikuasai dengan baik.
1) Cara Penulisan
Pada dasarnya cara penulisan operator hitung dasar dalam aplikasi Excel sama
dengan operator hitung dasar matematika, hanya saja mungkin ada sedikit
perbedaan untuk beberapa operator seperti misalnya perkalian dan pembagian.
Agar lebih jelas, tabel berikut ini menjelaskan daftar operator hitung dasar dalam
aplikasi Excel.
2) Cara Penggunaan
Cara menggunakan operator hitung dasar ini pada prinsipnya sama dengan
perhitungan matematika, hanya saja sesuai dengan aturan penulisan formula yangtelah dibahas sebelumnya, usahakan agar data yang akan dihitung selalu merujuk
-
5/20/2018 Materi Kkpi Excel
17/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 13
pada alamat sel. Sebagai contoh, pada tabel berikut ini akan dihitung nilai Total
untuk data Januarihingga Maretpada tiap-tiap tahun.
Maka secara matematika, untuk tahun 2010Anda akan menambahkan nilai 53 + 26
+ 32. Dan pada Excel, Anda tinggal mengganti nilai-nilai tersebut dengan alamat sel-
nya, yaitu C3, D3, dan E3. Langkah pengerjaan lengkapnya adalah sebagai berikut:
Klik sel F3, lalu ketikan tanda =
Klik sel C3, lalu ketikan tanda +
Klik sel D3, lalu ketikan tanda +
Klik sel E3, tekan tombol Enter
Hasilnya akan langsung terlihat pada sel F3.
Dengan cara yang sama, cobalah untuk menghitung data untuk tahun 2011 dan 2012.
Sampai tahapan ini, diharapkan Anda sudah bisa memahami bagaimana cara kerja dasar
dari penggunaan formula pada aplikasi Excel. Dalam penggunaan lanjutan, Anda juga dapat
menggunakan bantuan tanda kurung untuk mempermudah Anda dalam menghitungseperti halnya proses penghitungan pada operasi matematika biasa.
d. Operator Pembanding
Dalam penerapan formula lanjutan, terutama pada formula yang menyertakan fungsi-
fungsi logika, Anda mungkin harus menggunakan Operator Pembanding. Sama halnya
dengan operator hitung dasar, operator pembanding juga pada dasarnya sama dengan
operator pembanding matematika biasa yang telah Anda kenal. Ada 6 operator
pembanding dalam aplikasi Excel yang harus Anda ketahui, terutama dari cara penulisan
serta istilah operator tersebut dalam bahasa Inggris. Berikut ini selengkapnya.
Jika ada 2 buah nilai yang dibandingkan dengan operator-operator pembanding tersebut,
maka hasilnya adalah sebuah nilai logikaTRUE(nilai yang dianggap benar) atau FALSE(nilaiyang dianggap salah).
-
5/20/2018 Materi Kkpi Excel
18/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 14
Contoh penggunaan operator pembanding ini dalam kaitannya dengan fungsi-fungsi logika
akan dibahas kemudian pada artikel selanjutnya yang khusus membahas tentang dasar
penggunaan fungsi logika.
e. Auto Fill
Auto Fill adalah fitur pada aplikasi Excel untuk mengisi data pada sel berdasarkan pola
tertentu dari data pada sel lain. Fitur ini umumnya sering digunakan oleh para pengguna
Excel, misalnya saja untuk membuat penomoran yang berurutan. Cara penggunaannya
juga relatif sangat mudah, Anda bisa menggunakan tombol Auto Filldalam group Editing
pada tab Homeatau menggunakan drag dengan mouse. Namun tampaknya para pengguna
Excel lebih menyukai Auto Fill dengan drag mouse karena merupakan cara yang paling
mudah untuk dilakukan. Agar lebih jelas berikut ini contoh penggunaan Auto Fill.
Pada tabel berikut ini dapat dilihat bahwa sel B2 hingga B4 telah berisikan data berupa
nomor 1hingga 3. Jika Anda hendak melanjutkan penomoran tersebut ke sel berikutnya,
maka Anda tinggal menyorot sel B2hingga B4. Kemudian dalam kondisi sel masih tersorot,
dekatkan mouse ke simbol kotak kecil yang terletak di sudut kanan bawah hingga pointer
mouse berubah menjadi simbol tanda plus berwarna hitam.
Berikutnya klik dan tahan lalu drag kearah bawah hingga melewati beberapa sel danlepaskan tombol mouse.
Hasilnya dapat dilihat pada gambar berikut ini dimana sel-sel berikutnya secara otomatis
telah terisi dengan nomor yang berurutan.
-
5/20/2018 Materi Kkpi Excel
19/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 15
Dalam kaitannya dengan penggunaan formula, Auto Fill ini bisa Anda gunakan untuk
menyalin formula dari satu sel ke sel-sel lainnya secara cepat. Prinsipnya sama dengan
contoh Auto Fill diatas, yaitu selalu mengikuti pola yang sudah terbentuk pada sel yang
menjadi acuan. Agar lebih jelas, perhatikan contoh berikut ini.
Pada tabel dibawah ini, tentunya dengan mudah Anda bisa menghitung hasil penjumlahan
di sel D3dengan menggunakan Operator Hitung Dasar. Formulanya adalah =B3+C3
Berikutnya, bagaimana cara untuk menghitung hasil penjumlahan pada sel-sel berikutnya,
yaitu sel D4dan D5? Dengan cara manual jelas bisa, namun bayangkan seandainya data
yang harus Anda hitung jumlahnya sangat banyak, maka cara manual alias dihitung satu-
persatu jelas bukan cara yang efisien.
Agar lebih cepat sekaligus mempermudah pekerjaan Anda maka Anda bisa menggunakan
Auto Fill. Caranya sama seperti contoh Auto Fill sebelumnya. Anda tinggal memilih sel D3
yang telah berisikan formula perhitungan, lalu mendekatkan pointer mouse ke sudut kanan
bawah dan kemudian klik, tahan, lalu drag ke arah bawah.
Setelah sampai di sel D5, lepaskan tombol mouse lalu perhatikan hasilnya. Secara otomatis
sel D4dan D5telah berisikan formula dengan mengacu pada formula didalam sel D3.
-
5/20/2018 Materi Kkpi Excel
20/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 16
Jika dianalisa, dari contoh tersebut dapat Anda lihat bahwa proses Auto Fill pada sel yang
berisikan formula ini akan selalu menambahkan alamat sel dengan sel berikutnya,
tergantung kearah mana Anda lakukan proses Auto Fill tersebut:
Jika arahnya kebawah maka nomor baris yang akan selalu bertambah, contohnya 1,
2, 3, 4, 5, dst...
Jika arahnya kekanan maka nama kolom yang akan selalu bertambah, contohnya A,
B, C, D, E, dst...
Pada contoh diatas, arah proses Auto Fill adalah kebawah, hingga nomor baris yang akan
selalu bertambah sementara nama kolomnya tetap. Dan proses ini tentunya menghasilkan
formula yang benar pada sel D4dan D5
=B3+C3
=B4+C4
=B5+C5
Dari analisa tersebut maka proses Auto Fill ini bisa Anda jalankan pada sebuah tabel yangbentuknya statis seperti tabel pada contoh diatas. Namun untuk tabel yang bentuknya
dinamis atau kompleks, misalnya saja data yang akan dihitung berada pada alamat sel yang
tidak tentu, maka Auto Fill ini tidak dapat Anda gunakan kecuali dengan bantuan Sel
Absolut yang dibahas pada artikel yang lain dalam rangkaian artikel ini.
Pelajari baik-baik konsep penggunaan Auto Fill ini serta arah maupun cara pergerakannya,
karena dalam aplikasi Excel, proses Auto Fill ini merupakan salah satu bantuan utama yang
dapat mempercepat pekerjaan Anda saat harus menggunakan formula pada sebuah tabel
dengan data yang jumlahnya banyak.
f. Sel Absolut
Sel Absolutadalah istilah untuk sel yang alamatnya berada dalam kondisi terkunci. Tujuan
dari penguncian ini adalah agar alamat sel tidak mengalami perubahan saat dilakukan
proses Auto Fill yang selalu menambahkan alamat sel ke sel berikutnya. Dengan adanya Sel
Absolut ini maka Auto Fill tetap bisa digunakan pada sebuah tabel data yang semi konsisten
atau pada formula lanjutan yang menggunakan tabel referensi.
Cara menggunakan Sel Absolut sangat mudah, yaitu dengan menambahkan tanda $
didepan nama kolom atau nomor barisnya. Untuk menambahkan tanda $ ini Anda dapat
mengetikannya secara manual atau menggunakan tombol F4pada keyboard Anda. Aturan
yang berlaku untuk Sel Absolut ini adalah sebagai berikut.
-
5/20/2018 Materi Kkpi Excel
21/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 17
Jika tanda $ terletak didepan nama kolom, maka saat digunakan Auto Fill, nama
kolom tersebut akan terkunci namun nomor barisnya terus bertambah. Contohnya
$A1.
Jika tanda $ terletak didepan nomor baris, maka saat digunakan Auto Fill, nomor
baris tersebut akan terkunci namun nama kolomnya terus bertambah. Contohnya
A$1. Jika tanda $ terletak didepan nama kolom dan nomor baris, maka saat digunakan
Auto Fill, alamat sel tidak akan berubah alias selalu tetap. Contohnya $A$1.
Agar lebih jelas dalam memahami Sel Absolut ini, perhatikan tabel berikut ini. Misalkan
saja Anda akan menghitung persentase dari jumlah Data 1dan Data 2. Maka formula yang
digunakan pada D3adalah D3=(B3+C3)*B8
Namun jika digunakan Auto Fill untuk mengisi sel selanjutnya yaitu sel D4 dan D5, maka
hasilnya akan salah, karena formulanya akan menjadi:
D4=(B4+C4)*B9
D5=(B5+C5)*B10
Sel B4, C4, B5, dan C5 sudah benar, yaitu merupakan penambahan alamat dari sel
sebelumnya, namun sel B9 dan B10 jelas salah karena seharusnya sel tersebut tidak
berubah yaitu alamatnya tetap B8yang berisikan nilai persentase. Pada sel B8inilah Anda
bisa menggunakan Sel Absolut untuk mengunci alamat sel tersebut.
Karena arah Auto Fill bergerak kebawah yang mengakibatkan penambahan nomor baris,
maka Anda cukup menambahkan tanda $didepan nomor barisnya saja. Dengan demikian,
formula pada D3yang seharusnya adalah: D3=(B3+C3)*B$8
Jika digunakan Auto Fill, maka formula pada sel D4dan D5akan menjadi:
D4=(B4+C4)*B$8
D5=(B5+C5)*B$8
Dan formula ini tentunya akan menghasilkan nilai yang benar.
-
5/20/2018 Materi Kkpi Excel
22/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 18
Sebagai latihan sekaligus agar Anda lebih memahami cara kerja dan penggunaan Sel
Absolut ini, Anda bisa melihat sebuah tabel perkalian yang umum dimiliki oleh anak
sekolah dasar seperti berikut ini.
Pada tabel perkalian tersebut, perhitungannya secara garis besar adalah data pada kolom B
dikalikan dengan data pada baris 2. Sebagai contoh, pada sel isian pertama yaitu sel C3,
formulanya adalah: C3=B3*C2
-
5/20/2018 Materi Kkpi Excel
23/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 19
Karena perhitungan selalu menggunakan nama kolom yang sama yaitu kolom B serta
nomor baris yang sama yaitu baris 2, maka Anda dapat menambahkan tanda $ didepan
kolom B dan nomor baris 2 untuk membuat sel tersebut menjadi Absolut. Hingga
formulanya menjadi seperti berikut ini: C3=$B3*C$2
Berikutnya Anda tinggal memilih sel C3 tersebut lalu melakukan proses Auto Fill kearah
kanan.
Dalam kondisi sel C3:L3masih tersorot, lakukan Auto Fill sekali lagi kearah bawah.
-
5/20/2018 Materi Kkpi Excel
24/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 20
Hasilnya Anda sudah mendapatkan sebuah tabel perkalian yang lengkap yang dikerjakan
hanya dalam waktu singkat saja, tentunya dengan menggunakan bantuan Sel Absolut dan
Auto Fill.
5.
Fungsi SUM
Pada beberapa artikel terdahulu telah dibahas konsep-konsep dasar yang harus Anda
ketahui tentang formula pada aplikasi Excel. Mulai artikel ini, akan dibahas beberapa fungsi
dasar yang umum digunakan dalam formula Excel untuk membantu proses penghitungan
data pada aktifitas pekerjaan sehari-hari.
Fungsi yang pertama dan bisa dibilang merupakan salah satu fungsi yang paling banyak
diketahui dan digunakan setiap pengguna aplikasi Excel adalah fungsi SUM. Fungsi SUM
digunakan untuk menjumlahkan data dari beberapa sel. Aturan penulisannya sebagai
berikut: =SUM(sel awal:sel akhir)
Alamat sel awal hingga sel akhir merupakan sebuah range, dengan demikian aturan
penulisan fungsi SUM juga bisa dituliskan sebagai berikut: =SUM(range)
Sebagai contoh, pada tabel berikut ini akan dihitung nilai Total Penjualanpada sel C7untuk
data bulan Januari hingga bulan April, yaitu data pada sel C3hingga C6.
Sesuai dengan aturan penulisan formula yang telah dibahas pada artikel sebelumnya, maka
cara penulisan fungsi SUM untuk menghitung nilai Total Penjualan tersebut adalah sebagai
berikut:
Pilih sel C7
Ketikan tanda = Ketikan SUMatau gunakan fitur Intellisense
Ketikan tanda (
Klik sel C3lalu drag ke sel C6
Ketikan tanda )
-
5/20/2018 Materi Kkpi Excel
25/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 21
Tekan Enteruntuk melihat hasilnya
Dan hasil penjumlahan akan ditampilkan pada sel C7seperti tampak pada gambar berikut
ini.
Dengan demikian, formula yang telah Anda gunakan untuk menghitung nilai Total tersebut
adalah: C7=SUM(C3:C6)
Untuk memeriksa hasil dari penulisan formula ini, cobalah untuk merubah data pada sel C3
hingga C6 dan lihatlah apakah hasil perhitungan pada sel C7 telah berubah. Jika berubah,
maka penulisan formula sudah sesuai.
Penulisan range pada formula ini memang lebih mudah dilakukan dengan menggunakan klik
dan drag mouse, namun untuk range yang terlalu jauh, tentunya Anda pun dapat
mengetikan alamat range tersebut secara manual. Atau untuk kemudahan perhitungandikemudian hari.
6.
Fungsi AVERAGE
Fungsi AVERAGE digunakan untuk menghitung nilai rata-rata dari sejumlah data pada
beberapa sel. Aturan penulisannya sebagai berikut: =AVERAGE(sel awal:sel akhir)
Alamat sel awal hingga sel akhir merupakan sebuah range, dengan demikian aturan
penulisan fungsi AVERAGE juga bisa dituliskan sebagai berikut: =AVERAGE(range)
Sebagai contoh, pada tabel berikut ini akan dihitung nilai rata-rata Penjualan per Tahunpada sel C7untuk data tahun 2009 hingga 2012, yaitu data pada sel C3hingga C6.
-
5/20/2018 Materi Kkpi Excel
26/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 22
Maka formula yang digunakan adalah: C7=AVERAGE(C3:C6)
Dan hasilnya akan ditampilkan pada sel C7seperti tampak pada gambar berikut ini.
7.
Fungsi COUNT
Banyak pengguna aplikasi Excel yang menganggap fungsi COUNT ini sama dengan fungsi
SUM, padahal kegunaannya jelas berbeda. Jika fungsi SUM digunakan untuk menghitung
jumlah datamaka fungsi COUNTdigunakan untuk menghitung banyaknya pemilik data. Olehkarenanya berhati-hatilah saat Anda harus memutuskan fungsi mana yang akan Anda
gunakan, apakah SUMatau COUNT.
Aturan penulisan fungsi COUNT sebagai berikut: =COUNT(sel awal:sel akhir)
Alamat sel awal hingga sel akhir merupakan sebuah range, dengan demikian aturan
penulisan fungsi COUNTjuga bisa dituliskan sebagai berikut: =COUNT(range)
Agar lebih jelas dalam memahami fungsi COUNTini juga agar tidak tertukar penggunaannya
dengan fungsi SUM, perhatikan contoh berikut ini. Pada tabel yang ditanyakan adalah
jumlah materi training yang ada dan bukan jumlah nilainya.
-
5/20/2018 Materi Kkpi Excel
27/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 23
Maka formula yang digunakan adalah: C7=COUNT(C3:C6)
Dan hasilnya akan ditampilkan pada sel C7seperti tampak pada gambar berikut ini.
Perhatikan bahwa walaupun yang ditanyakan adalah jumlah pemilik data, dalam hal ini
Materi Training, namun alamat range yang dimasukan dalam formula tetap range untuk
datanya yaitu C3:C6dan bukan B3:B6.
8.
Fungsi MAX dan MIN
Fungsi MAX digunakan untuk mengetahui nilai maksimum dari sejumlah data dan
fungsi MIN untuk mengetahui nilai minimum juga dari sejumlah data. Aturan penulisan
fungsi MAX dan MIN sama saja, yaitu sebagai berikut:
=MAX(sel awal:sel akhir) atau =MIN(sel awal:sel akhir)
Alamat sel awal hingga sel akhir merupakan sebuah range, dengan demikian aturan
penulisan fungsi MAX / MIN juga bisa dituliskan sebagai berikut:
=MAX(range) atau =MIN(range)
Sebagai contoh, pada tabel berikut ini ditanyakan berapa nilai maksimum dan minimum
untuk semua nilai yang ada, yaitu data nilai pada sel C3hingga C6.
-
5/20/2018 Materi Kkpi Excel
28/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 24
Maka formula yang digunakan untuk melihat nilai maksimum adalah: C7=MAX(C3:C6)
Dan formula yang digunakan untuk melihat nilai minimum adalah: C8=MIN(C3:C6)
Hasilnya akan ditampilkan pada sel C7dan C8seperti tampak pada gambar berikut ini.
9.
Fungsi COUNTIF
Fungsi dalam aplikasi Excel dikelompokan berdasarkan beberapa kategori tertentu, salah
satunya yaitu kategori fungsi Logika (Logic Functions).
Fungsi yang terdapat dalam kategori ini digunakan untuk menghitung atau menganalisa
data yang bersandar pada perbandingan suatu kondisi atau kriteria tertentu hingga fungsi-
fungsi yang termasuk dalam kategori ini lazim disebut juga dengan Condit ional Functions.
Ada banyak fungsi yang terdapat pada kategori ini dimana hampir sebagian besar nama
fungsinya memiliki akhiran IF yang jika diartikan secara harfiah adalah "jika".
Fungsi COUNTIF termasuk kedalam kategori Logic Functions tersebut. Fungsi ini pada
dasarnya memiliki kegunaan yang sama dengan fungsi COUNT yaitu untuk menghitung
banyaknya data. Namun pada fungsi COUNTIF, banyaknya data ini hanya akan dihitung jika
sesuai dengan kriteria atau kondisi tertentu. Aturan penulisannya sebagai berikut:
=COUNTIF(Range Data;Kondisi)
-
5/20/2018 Materi Kkpi Excel
29/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 25
Dari aturan penulisan tersebut dapat Anda lihat bahwa fungsi COUNTIF ini terdiri dari 2
argumen dimana tiap-tiap argumen dipisahkan dengan tanda pemisah tertentu. Hal ini
berbeda dengan beberapa fungsi yang telah dibahas sebelumnya yang masih menggunakan
argumen tunggal yang sangat sederhana.
Agar lebih jelas dalam memahami penggunaan fungsi COUNTIF ini, perhatikan contoh tabel
berikut ini. Tiap-tiap siswa memiliki status kelulusan yang berbeda dimana ada yang LULUS
dan ada yang GAGAL. Sementara yang ditanyakan adalah berapakah jumlah siswa yang
LULUS?
Berdasarkan aturan penulisan COUNTIF, status LULUSmerupakan kondisi yang dikehendaki,
dan range yang memuat status LULUS tersebut merupakan range data yang akan dihitung
oleh Excel yaitu sel C3hingga C6. Dengan demikian formula untuk menghitung banyaknya
siswa yang lulus tersebut adalah sebagai berikut:
C7=COUNTIF(C3:C6;LULUS)
Namun berdasarkan aturan penulisan formula, LULUS adalah variabel yang tidak dikenali
oleh Excel sehingga formula diatas jelas akan menghasilkan pesan kesalahan jika dijalankan.
Agar kata LULUS tersebut dikenali, maka kita berikan tanda kutip diantaranya, sehingga
formulanya menjadi:
C7=COUNTIF(C3:C6;"LULUS")
Dan sekarang formula ini akan menghasilkan nilai yang benar seperti tampak pada gambar
berikut ini.
-
5/20/2018 Materi Kkpi Excel
30/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 26
Pelajari baik-baik penggunaan formula dengan multi argumen ini karena dalam penerapan
Excel lebih lanjut Anda akan banyak menjumpainya.
10.Fungsi SUMIF
Selain COUNTIF, fungsi logika lainnya yang umum digunakan adalah fungsi SUMIF. Sesuai
dengan fungsi dasarnya yaitu SUM, fungsi SUMIF juga digunakan untuk menjumlahkan data
pada range tertentu, hanya saja data yang akan dijumlahkan mengacu pada kondisi
tertentu.
Aturan penulisan fungsi SUMIF ini sebagai berikut:
=SUMIF(range kondisi;kondisi;range data)
Agar lebih jelas perhatikan contoh pada tabel berikut ini. Yang ditanyakan pada tabel
tersebut bukanlah total laba keseluruhan melainkan total laba hanya untuk barang yang
bagus saja serta total laba hanya untuk barang yang rusak saja.
Pada kasus ini, kata BAGUSdan RUSAKmerupakan sebuah kondisi yang berada pada range
C3 hingga C6. Sementara data yang akan dijumlahkan berada pada range D3 hingga D6.
Dengan demikian, sesuai dengan aturan penulisan SUMIF, maka formula untuk menghitung
Total Laba Barang Bagusdi sel D9adalah sebagai berikut:
D9=SUMIF(C3:C6;BAGUS;D3:D6)
Sesuai dengan aturan penulisan formula, kata BAGUS bukan merupakan variabel yang
dikenali Excel, maka kita perlu menambahkan tanda kutip untuk mengapit kata tersebut,
hingga formulanya akan menjadi:
-
5/20/2018 Materi Kkpi Excel
31/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 27
D9=SUMIF(C3:C6;"BAGUS";D3:D6)
Lalu untuk menghitung Total Laba Barang Rusak di sel D10, tentunya Anda tinggal
mengganti kata BAGUS menjadi RUSAK, hingga formulanya menjadi:
D10=SUMIF(C3:C6;"RUSAK";D3:D6)
Dan contoh hasilnya dapat dilihat pada gambar berikut ini.
11.
Penggabungan Fungsi
Pada artikel sebelumnya telah dibahas contoh beberapa fungsi yang umum digunakan.
Banyak para pengguna aplikasi Excel yang mengira bahwa dalam satu sel dan satu formula
hanya boleh digunakan satu fungsi saja.
Opini ini tentunya salah besar, karena dalam penggunaannya, Anda dapat menggabungkan
beberapa fungsi sekaligus untuk menghitung nilai yang dicari. Dan justru karena adanya
penggabungan beberapa fungsi ini maka aplikasi Excel menjadi semakin powerfull namuntetap efisien. Salah satu hal yang mutlak harus diperhatikan dalam penggabungan beberapa
fungsi sekaligus dalam satu formula adalah penggunaan tanda kurung serta tanda pemisah
argumen.
Tanda kurung ini berfungsi untuk memisahkan antara fungsi yang satu dengan fungsi yang
lainnya. Kesalahan penempatan tanda kurung ini tentunya akan mengakibatkan formula
menjadi error hingga Excel akan menampilkan pesan kesalahan. Jika Anda mengalami
kesulitan dalam menempatkan tanda kurung ini, misalnya saja karena terlalu banyaknya
fungsi yang akan digabungkan, akan lebih baik jika Anda menuliskannya dulu di kertas dalam
bentuk coretan kasar. Gunakan saja prinsip matematika dasar dimana tiap-tiap perhitungan
dalam suatu perhitungan yang kompleks selalu dipisahkan dengan tanda kurung.
-
5/20/2018 Materi Kkpi Excel
32/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 28
Mulai Excel 2007, Microsoft juga telah menambahkan fitur berupa warna yang berbeda
untuk tiap-tiap tanda kurung yang saling berhubungan. Fitur ini tentunya bisa Anda gunakan
juga sebagai bantuan.
Berikut ini beberapa contoh untuk penggabungan fungsi ini.
a.
Contoh 1: Menggabungkan Fungsi SUM dengan Perkalian
Pada tabel berikut ini Anda diminta untuk menghitung Total Labapada sel C10.
Secara matematika, Total Laba tentunya diperoleh dengan menambahkan semua unit
yang terjual kemudian dikalikan dengan Laba per Unit. Namun pada tabel tersebut tidak
terdapat sel khusus untuk menghitung jumlah keseluruhan Unit Terjual. Dengan
demikian solusinya adalah Anda harus menggabungkan fungsi SUM untuk menghitung
jumlah Unit Terjual yang kemudian dikalikan dengan Laba per Unit. Formulanyasebagai berikut ini C10=SUM(C3:C6)*C8
Atau agar sesuai dengan konsep dasar matematika mengingat ada 2 perhitungan yang
digunakan maka Anda juga dapat menuliskan formulanya sebagai berikut:
C10=(SUM(C3:C6))*C8
Contoh hasil akhirnya dapat dilihat pada gambar berikut ini.
-
5/20/2018 Materi Kkpi Excel
33/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 29
b. Contoh 2: Menggunakan 2 Fungsi SUMIF dalam 1 Formula
Pada tabel berikut ini Anda diminta untuk menghitung stok Barang Baguspada sel D9
serta stok Barang Rusak pada sel D10. Data stok barang tersebut diambil dari tabel
Laporan Stok Gudang.
Jika data hanya diambil dari satu gudang saja, misal Gudang 1 maka dengan mudah
Anda dapat menghitung stok yang diminta yaitu dengan menggunakan formula SUMIF
yang telah Anda pelajari sebelumnya. Namun dalam kasus ini gudangnya ada 2, maka
solusinya Anda tinggal gabungkan saja formula SUMIF untuk-tiap gudang dalam 1
formula.
Formula untuk menghitung stok barang bagus dari Gudang 1adalah:
=SUMIF(C4:C6;"BAGUS";D4:D6)
Dan formula untuk menghitung stok barang bagus dari Gudang 2adalah:
=SUMIF(C4:C6;"BAGUS";E4:E6)
Dengan demikian formula akhirnya pada sel D9adalah sebagai berikut:
D9=SUMIF(C4:C6;"BAGUS";D4:D6)+SUMIF(C4:C6;"BAGUS";E4:E6)
Untuk menghitung stok barang rusak tentunya Anda bisa menggunakan fitur Auto Fill.
Dan contoh hasil akhirnya bisa Anda lihat pada gambar berikut ini.
-
5/20/2018 Materi Kkpi Excel
34/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 30
12.
Fungsi IF
Dari semua fungsi logika yang terdapat dalam aplikasi Excel, fungsi IFadalah fungsi logika
yang paling banyak diaplikasikan, terutama dalam penerapan formula lanjutan yang
melibatkan banyak data dan keputusan bercabang. Kegunaan dari fungsi IF ini sebetulnya
sangat sederhana, fungsi ini akan mengambil suatu kondisi tertentu kemudian menentukannilai TRUE atau FALSE. Nilai TRUE adalah nilai dimana kondisi tersebut terpenuhi dan nilai
FALSE adalah nilai untuk kondisi yang tidak terpenuhi.
Aturan penulisan fungsi IF ini adalah sebagai berikut, =IF(kondisi;nilai_TRUE;nilai_FALSE)
kondisimerupakan kriteria acuan yang dijadikan pembanding
nilai_TRUEmerupakan nilai untuk kondisi yang terpenuhi
nilai_FALSEmerupakan nilai untuk kondisi yang tidak terpenuhi
Agar lebih jelas dalam memahami penggunaan fungsi IF ini, perhatikan contoh pada tabel
berikut ini.
Pada tabel tersebut kolom Statusakan diisi dengan kata LULUSatau GAGAL. Namun untuk
mengisinya ada kondisi yang harus dipenuhi, misalkan saja siswa akan lulus jika nilainya
diatas 60. Dari pernyataan ini maka didapat beberapa point, yaitu:
Kondisi: Nilai diatas 60. Nilai ini berada pada sel C3 hingga C6. Dan karena ada nilai
acuan yaitu 60 sebagai pembanding maka Anda harus menggunakan Operator
Pembandinguntuk melengkapi argumen pada kondisi ini. Operator pembanding yang
digunakan tentunya tanda lebih besar ">".
Kondisi 1: Jika nilai diatas 60, maka siswa Lulus. Kondisi ini merupakan nilai untuk
kondisi TRUEyaitu kondisi yang terpenuhi Kondisi 2: Jika nilai dibawah 60, artinya tidak diatas 60, maka siswa Gagal. Kondisi ini
adalah nilai untuk kondisi FALSEyaitu kondisi yang tidak terpenuhi.
Berikutnya Anda tinggal menerapkan point-point tersebut pada fungsi IF disesuaikan dengan
aturan penulisannya. Dan karena data tiap-tiap siswa bervariasi, maka tabel akan diisi untuk
siswa yang pertama dulu yaitu Siti Nurhaliza. Formulanya sebagai berikut:
D3=IF(C3>60;"LULUS";"GAGAL")
Arti dari formula tersebut jika diterjemahkan dalam kalimat biasa adalah "Jika nilai dalam sel
C3 lebih besar dari 60, maka tuliskan kata LULUS pada sel D3. Namun jika nilainya ternyatatidak lebih besar dari 60 maka tuliskan kata GAGAL pada sel D3".
-
5/20/2018 Materi Kkpi Excel
35/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 31
Contoh hasilnya dapat Anda lihat pada gambar berikut ini:
Untuk memeriksa apakah formula yang dituliskan sudah benar, cobalah untuk mengganti
nilai pada sel C3, misalnya menjadi 40, lalu perhatikan hasilnya. Jika tertulis GAGAL maka
artinya formula yang digunakan sudah benar.
Lalu bagaimana mengisikan baris-baris selanjutnya? Menuliskan formula secara manual satu
persatu jelas bisa, namun hal ini akan sangat menyita waktu. Cara cepatnya, gunakan saja
fitur Auto Fillyang telah dibahas pada artikel sebelumnya.
Dan hasilnya semua kolom Status kini telah terisi dengan nilai yang benar.
-
5/20/2018 Materi Kkpi Excel
36/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 32
13.
Fungsi IF Bercabang
Fungsi logika IFyang telah dibahas pada artikel sebelumnya adalah salah satu fungsi yang
relatif banyak digunakan, terutama di lingkungan bisnis / perusahaan. Namun fungsi IF ini
memiliki keterbatasan karena kondisi yang menjadi acuan hanya satu saja. Bagaimana jika
data Anda menggunakan beberapa kondisi sekaligus?
Seperti yang telah dibahas pada artikel Penggabungan Fungsi, penerapan fungsi pada
aplikasi Excel bisa digabungkan untuk memenuhi satu tujuan atau hasil tertentu. Dan dalam
kasus ini, dimana data memiliki beberapa kondisi yang harus dibandingkan, maka fungsi
yang digunakan tetap fungsi IF, namun bukan hanya satu IF (IF tunggal) melainkan beberapa
IF sekaligus. Dalam aplikasi Excel, penggunaan beberapa IF (Multiple IF) dalam satu formula
ini lazim disebut dengan fungsi IF Bercabangatau Nested IF.
Pada dasarnya aturan penulisan fungsi IF bercabang ini sama saja dengan IF tunggal, yaitu:
=IF(kondisi;nilai_TRUE;nilai_FALSE)
kondisimerupakan kriteria acuan yang dijadikan pembanding
nilai_TRUEmerupakan nilai untuk kondisi yang terpenuhi
nilai_FALSEmerupakan nilai untuk kondisi yang tidak terpenuhi
Karena kondisi-nya lebih dari satu, maka untuk nilai_FALSE digunakan kembali fungsi IF
berikutnya. Misalnya jika kondisi-nya ada 2, maka aturan penulisannya menjadi:
=IF(kondisi_1;nilai_TRUE1;(IF(kondisi_2;nilai_TRUE2;nilai_FALSE)))
Jika dijabarkan, maka formula tersebut berarti:
Lihat kondisi_1, jika terpenuhi maka gunakan nilai_TRUE1.
Jika kondisi_1tidak terpenuhi, maka lihat kondisi_2.
Jika kondisi_2terpenuhi, maka gunakan nilai_TRUE2.
Jika kondisi_2tidak terpenuhi, maka gunakan nilai_FALSE.
Fungsi IF bercabang ini tentunya tidak hanya untuk 2 kondisi saja, misalnya jika ada 3 kondisi
maka aturan penulisannya menjadi:
=IF(kondisi_1;nilai_TRUE1;(IF(kondisi_2;nilai_TRUE2;(IF(kondisi_3;nilai_TRUE3;nilai_FALSE
)))))
Atau jika kondisinya ada 4 maka aturan penulisannya menjadi:
=IF(kondisi_1;nilai_TRUE1;(IF(kondisi_2;nilai_TRUE2;(IF(kondisi_3;nilai_TRUE3;(IF(kondisi_
4;nilai_TRUE4;nilai_FALSE)))))))
Dan demikian seterusnya.
-
5/20/2018 Materi Kkpi Excel
37/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 33
Agar penggunaan fungsi IF bercabang ini lebih jelas, perhatikan contoh tabel berikut ini.
Misalkan saja pada tabel daftar barang tersebut Anda diminta untuk mengisi nilai diskon
pada sel C3 hingga C6, dengan kondisi bahwa produk Mouse dan produk Keyboard akan
mendapatkan diskon sebesar 5%. Diluar produk tersebut tidak diberikan diskon.
Analisa dari kasus tersebut adalah ada 2 kondisi yang menjadi acuan, yaitu Mouse diskon 5%
serta Keyboard diskon 5%. Kedua kondisi ini tentunya harus dimasukan kedalam formula IF
satu persatu, boleh diawali dengan kondisi untuk Mouse ataupun untuk Keyboard. Sebagai
contoh diambil kondisi untuk Mouse terlebih dahulu. Untuk memperjelas pembahasan,
berikut ini dituliskan kembali aturan penulisan fungsi IF.
C3=IF(kondisi;nilai_TRUE;nilai_FALSE)
Kondisi pertama adalah Mouse dengan diskon 5%. Mouse serta produk-produk lainnya
berada pada kolom 3 dan diawali dengan alamat sel B3. Maka formulanya akan menjadi:
C3=IF(B3="Mouse";nilai_TRUE;nilai_FALSE)
Nilai jika B3adalah Mousemerupakan nilai_TRUEyaitu 5%, maka formulanya menjadi:
C3=IF(B3="Mouse";5%;nilai_FALSE)
Nilai_FALSEdigunakan jika ternyata isi sel bukan Mouse, artinya produk lainnya. Dan karena
ada satu kondisi lagi yaitu Keyboard maka kita masukan kondisi tersebut sebagai kondisi
pada fungsi IF berikutnya:
C3=IF(B3="Mouse";5%;(IF(B3="Keyboard";nilai_TRUE2;nilai_FALSE)))
Jika B3 adalah Keyboard maka nilai_TRUE2 adalah 5% karena kondisinya terpenuhi.
Sehingga formulanya akan menjadi:
C3=IF(B3="Mouse";5%;(IF(B3="Keyboard";5%;nilai_FALSE)))
Jika ternyata B3tidak berisikan Mouseataupun Keyboard, maka tidak diberikan diskon sama
sekali atau diskon sama dengan 0. Nilai ini tentunya merupakan nilai_FALSEuntuk semua
kondisi yang telah dibandingkan. Dengan demikian formula akhirnya adalah sebagai berikut:
C3=IF(B3="Mouse";5%;(IF(B3="Keyboard";5%;0)))
-
5/20/2018 Materi Kkpi Excel
38/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 34
Dan formula tersebut jika dijalankan pada sel C3 tentunya akan menghasilkan nilai yang
benar seperti tampak pada gambar berikut ini.
Dan untuk mengisi sel-sel berikutnya seperti biasa Anda dapat menggunakan Auto Fill.
Pelajari baik-baik penggunaan formula IF bercabang ini karena dalam penerapan aplikasi
Excel lanjutan Anda akan banyak menggunakan formula ini. Hati-hati dengan penggunaan
tanda kurung karena semakin banyak IF yang Anda gunakan maka Anda akan menggunakan
tanda kurung yang banyak juga.
14.
Fungsi OR
Fungsi ORjuga termasuk kedalam fungsi logika. Kegunaannya adalah untuk membandingkan
2 atau lebih kondisi dan menghasilkan nilai TRUE atau FALSE. Nilai TRUEakan digunakan jika
salah satu kondisi atau semua kondisi terpenuhi. Sedangkan nilai FALSEakan digunakan jika
semua kondisi tidak terpenuhi.
Agar lebih jelas, tabel berikut ini memperlihatkan contoh nilai OR yang dihasilkan untuk 2
buah kondisi:
-
5/20/2018 Materi Kkpi Excel
39/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 35
Dan tabel berikut ini memperlihatkan contoh nilai OR yang dihasilkan untuk 3 buah kondisi:
Aturan penulisan fungsi OR pada dasarnya sangat sederhana yaitu hanya memuat
kondisinya saja sebagai berikut:
=OR(kondisi_1;kondisi_2;kondisi_3;......;kondisi_n)
Jika formula tersebut dijalankan, maka nilai yang akan dihasilkan adalah TRUE atau FALSE.
Agar TRUE atau FALSE tersebut ada nilainya, maka fungsi OR umum digabungkan
penggunaannya dengan IFhingga fungsi ini juga kemudian lazim disebut dengan fungsi IF-
OR. Aturan penulisannya adalah sebagai berikut:
=IF(OR(kondisi_1;kondisi_2);nilai_TRUE;nilai_FALSE)
Dalam penerapannya, fungsi OR dapat Anda gunakan untuk menyederhanakan fungsi IF
Bercabang. Sebagai contoh pada artikel IF Bercabang yang telah dibahas sebelumnya,
terdapat tabel diskon sebagai berikut, dimana ada kondisi untuk produk Mousedan produkKeyboardyang akan mendapatkan diskon sebesar 5%.
Kondisi yang diinginkan tersebut jika dianalogikan dalam kalimat akan menjadi "Jika produk
Mouse ATAU Keyboard, maka berikan diskon sebesar 5%, namun jika bukan maka tidak ada
diskon". Dari kalimat ini didapatkan beberapa point penting, yaitu:
kondisi_1: B3=Mouse
kondisi_2: B3=Keyboard
nilai_TRUE: 5%
nilai_FALSE: 0
-
5/20/2018 Materi Kkpi Excel
40/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 36
Dan point-point ini tentunya tinggal Anda masukan kedalam fungsi IF-OR. Diawali dengan
aturan penulisannya terlebih dahulu:
C3=IF(OR(kondisi_1;kondisi_2);nilai_TRUE;nilai_FALSE)
Kemudian tinggal Anda ganti tiap-tiap argumen dengan nilai yang sesuai. Dengan demikianformula akhirnya adalah sebagai berikut:
C3=IF(OR(B3="Mouse";B3="Keyboard");5%;0)
Dan formula ini jika Anda jalankan tentunya akan menghasilkan nilai yang benar seperti
tampak pada gambar berikut ini.
Kemudian seperti biasanya untuk mengisi sel-sel berikutnya Anda tinggal mengunakan Auto
Fill.
Dari contoh tersebut, Anda dapat melihat bahwa fungsi IF-OR bisa Anda gunakan untuk
menyederhanakan penggunaan formula IF Bercabang.
15. Fungsi AND
Fungsi AND adalah kebalikan dari fungsi OR. Kegunaannya sama yaitu untuk
membandingkan 2 atau lebih kondisi dan menghasilkan nilai TRUE atau FALSE. Perbedaan
AND dengan OR adalah nilai untuk kondisi TRUE dan FALSE-nya. Pada fungsi AND, nilai TRUE
akan digunakan jika semua kondisi terpenuhi, dan nilai FALSEakan digunakan jika salah satuatau semua kondisi tidak terpenuhi.
-
5/20/2018 Materi Kkpi Excel
41/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 37
Agar lebih jelas, tabel berikut ini memperlihatkan contoh nilai AND yang dihasilkan untuk 2
buah kondisi:
Dan tabel berikut ini memperlihatkan contoh nilai AND yang dihasilkan untuk 3 buah
kondisi:
Aturan penulisan fungsi AND juga sama dengan OR yaitu hanya memuat kondisinya saja, dan
agar kondisi TRUE dan FALSE ada nilainya maka fungsi AND juga umum digabungkan
penggunaannya dengan IF hingga kemudian lazim disebut dengan fungsi IF-AND dengan
aturan penulisan sebagai berikut:
=IF(AND(kondisi_1;kondisi_2);nilai_TRUE;nilai_FALSE)
Contoh penggunaan fungsi AND ini dapat dilihat pada tabel berikut.
Misalkan pada kolom Statusakan diisi dengan kata LULUSatau GAGAL. Status LULUS akan
didapatkan Siswa jika kedua nilai yaitu nilai Bahasa dan nilai Matematika diatas 60. Jika
salah satu nilai saja dibawah 60 maka siswa dinyatakan gagal.
Kondisi yang diinginkan tersebut jika dianalogikan dalam kalimat akan menjadi "Jika nilai
Bahasa DAN Matematika diatas 60 maka siswa akan Lulus. Namun jika tidak maka siswa
akan Gagal". Dari kalimat ini didapatkan beberapa point penting, yaitu:
-
5/20/2018 Materi Kkpi Excel
42/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 38
kondisi_1: C3>60
kondisi_2: D3>60
nilai_TRUE: LULUS
nilai_FALSE: GAGAL
Dan point-point ini tentunya tinggal Anda masukan kedalam fungsi IF-AND. Diawali denganaturan penulisannya terlebih dahulu:
E3=IF(AND(kondisi_1;kondisi_2);nilai_TRUE;nilai_FALSE)
Berikutnya Anda tinggal mengganti argumen-argumen tersebut dengan nilai yang sesuai.
Dengan demikian formula akhirnya adalah sebagai berikut:
E3=IF(AND(C3>60;D3>60);"LULUS";"GAGAL")
Formula ini jika Anda jalankan tentunya akan menghasilkan nilai yang benar seperti tampak
pada gambar berikut ini.
Kemudian seperti biasanya untuk mengisi sel-sel berikutnya Anda tinggal mengunakan Auto
Fill.
16.
Fungsi Tanggal
Perhitungan dalam aplikasi Excel adakalanya melibatkan sistem penanggalan, misalnya saja
untuk menghitung lama waktu cicilan atau tanggal jatuh tempo. Untuk mempermudah
perhitungan tersebut maka Anda dapat menggunakan beberapa Fungsi Tanggal yang
disediakan oleh Excel.
-
5/20/2018 Materi Kkpi Excel
43/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 39
Beberapa fungsi tanggal yang umum digunakan berikut aturan penulisannya dapat dilihat
pada tabel berikut ini.
Contoh penerapan fungsi penanggalan ini misalnya saja untuk menghitung tanggal jatuh
tempo seperti tabel berikut ini.
Karena yang ditanyakan adalah tanggal Jatuh Tempo, maka fungsi yang digunakan adalah
fungsi DATE dimana fungsi DATE ini bisa digunakan untuk memasukan format tanggal
tertentu. Dari tabel diatas dapat dilihat bahwa aturan penulisan fungsi DATE ini adalah
sebagai berikut D3=DATE(YEAR;MONTH;DAY)
Tahun, Bulan, dan Tanggal diambil dari Tanggal Kredit yaitu sel B3. Hingga formulanya
menjadi D3=DATE(YEAR(B3);MONTH(B3);DAY(B3))
Karena lama cicilan adalah 10 bulan yang terletak di sel C3, maka Anda tinggal
menambahkan MONTH(B3) dengan C3. Dan formula akhirnya adalah sebagai berikut
D3=DATE(YEAR(B3);MONTH(B3)+C3;DAY(B3))
Contoh penerapan lain untuk fungsi tanggal ini misalnya untuk menghitung durasi
berdasarkan tanggal awal dan akhir seperti tabel berikut ini.
-
5/20/2018 Materi Kkpi Excel
44/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 40
Yang ditanyakan dari tabel tersebut adalah Lama Project dalam satuan Bulan. Untuk
menghitungnya maka Anda tinggal mengurangi data pada sel C3 dengan sel B3. Atau jika
dituliskan formula dasarnya sebagai berikut D3=C3-B3
Karena yang ditanyakan Bulan, maka data yang diambil jelas hanya data Tahundan Bulan
saja. Kemudian Excel juga memiliki fungsi YEARdan MONTHuntuk mengambil data Tahun
dan Bulan dari sel tertentu (lihat tabel aturan penulisan diatas), maka formulanya menjadi:
D3=(YEAR(C3)-YEAR(B3))+(MONTH(C3)-MONTH(B3))
Kemudian karena dalam 1 tahun ada 12 bulan, maka tentunya hasil pengurangan data tahun
harus dikalikan dengan 12. Hingga formula akhirnya adalah sebagai berikut:
D3=(YEAR(C3)-YEAR(B3))*12+(MONTH(C3)-MONTH(B3))
Saat menggunakan fungsi-fungsi penanggalan ini, perhatikan selalu format sel yang
digunakan. Hasil yang diharapkan akan meleset jika format sel yang Anda gunakan tidak
sesuai.
17.
Fungsi Waktu
Selain Fungsi Tanggal, Excel juga menyediakan beragam fungsi untuk menghitung waktu
atau jam. Beberapa Fungsi Waktuyang umum digunakan berikut aturan penulisannya dapat
dilihat pada tabel berikut ini.
-
5/20/2018 Materi Kkpi Excel
45/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 41
Cara menggunakan fungsi waktu tersebut sama saja dengan fungsi tanggal yang telah
dibahas sebelumnya, hanya saja yang berbeda adalah satuannya. Jika fungsi tanggal
menggunakan 3 satuan utama YEAR; MONTH; dan DAY, maka fungsi waktu menggunakan 3
satuan utama HOUR; MINUTE; dan SECOND.
Kemudian jika Anda akan menghitung total waktu, baik dalam bentuk jam, menit, ataupundetik maka total waktu yang didapat dari hasil perhitungan harus dikalikan dengan bilangan
yang sesuai dengan satuannya. Aturannya bilangan pengali ini adalah sebagai berikut:
Jam: 24
Menit: 1440
Detik: 86400
Agar lebih jelas, perhatikan contoh tabel berikut ini.
Yang ditanyakan dari tabel tersebut adalah Total Jam, Total Menit, serta Total Detik. Untuk
menghitungnya sangatlah mudah. Anda tinggal mengurangi data Jam Keluar pada sel C3dengan data Jam Masuk pada sel B3 dan kemudian mengalikannya denan bilangan yang
sesuai.
Misalnya untuk menghitung Total Jam, maka formulanya sebagai berikut: C5=(C3-B3)*24
Kemudian untuk menghitung Total Menit Anda tinggal mengganti bilangan pengalinya
dengan 1440 dan untuk menghitung Total Detik maka bilangan pengali yang digunakan
adalah 86400. Berikut ini formula akhirnya.
C6=(C3-B3)*1440
C7=(C3-B3)*86400
-
5/20/2018 Materi Kkpi Excel
46/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 42
Sama halnya dengan fungsi penanggalan ini, perhatikan selalu format sel yang digunakan.
Hasil yang diharapkan akan meleset jika format sel yang Anda gunakan tidak sesuai.
18.
Fungsi Pembulatan
Untuk menyederhanakan hasil perhitungan, Excel juga menyediakan Fungsi Pembulatanyang bisa Anda gunakan untuk membulatkan angka tertentu berdasarkan kriteria
pembulatan yang bisa Anda pilih sesuai kebutuhan.
Ada 4 varian fungsi pembulatan yang umum digunakan, yaitu INT, ROUND, ROUNDUP, dan
ROUNDDOWN. Perbedaan fungsi-fungsi tersebut berikut aturan penulisannya bisa dilihat
pada tabel berikut ini.
Pada aturan penulisan tersebut, angkamerupakan angka yang akan Anda bulatkan dan digit
mengatur jumlah bilangan dibelakang koma. Agar lebih jelas dalam memahami fungsi
pembulatan ini, misalkan saja Anda hendak membulatkan angka 8,762.
Jika Anda menggunakan fungsi INTmaka formulanya adalah =INT(8,762)
Hasil dari formula tersebut adalah 8karena INT akan membulatkan suatu angka kebawah
sekaligus menghilangkan angka dibelakang koma.
Jika Anda menggunakan fungsi ROUND dan menginginkan ada 2 angka dibelakang koma,
maka formulanya adalah =ROUND(8,762;2)
Hasilnya adalah 8,76 karena fungsi ROUND ini akan membulatkan angka sesuai kaidah
matematika.
Jika Anda menggunakan fungsi ROUNDUPdan menginginkan ada 2angka dibelakang koma,
maka formulanya adalah =ROUNDUP(8,762;2)
Hasilnya adalah 8,77karena fungsi ROUNDUPini akan membulatkan angka keatas.
Jika Anda menggunakan fungsi ROUNDDOWN dan menginginkan ada 2 angka dibelakang
koma, maka formulanya adalah =ROUNDDOWN(8,762;2)
Hasilnya adalah 8,76karena fungsi ROUNDDOWNini akan membulatkan angka kebawah.
Dalam penerapannya, tentu saja angka yang hendak dibulatkan tidak selalu berbentuk angka
melainkan bisa merujuk pada data angka di sel tertentu ataupun hasil dari suatu
perhitungan. Sebagai contoh pada tabel berikut ini, fungsi ROUNDDOWNdigunakan untuk
membulatkan hasil penjumlahan yang didapat dari formula SUM.
-
5/20/2018 Materi Kkpi Excel
47/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 43
19.
Fungsi Teks
Formula pada aplikasi Excel tidak hanya diperuntukan bagi data berupa angka, karena Excel
menyediakan juga beberapa fungsi untuk mengolah data berupa teks, diantaranya yang
sering digunakan adalah fungsi LEFT; MID; dan RIGHT. Ketiga fungsi tersebut memiliki
kegunaan yang sama yaitu untuk mengambil beberapa karakter dari suatu teks. Sementara
perbedaannya terletak pada posisi atau arah pengambilan karakternya. Penjelasan serta
aturan penulisan untuk ketiga fungsi tersebut adalah sebagai berikut.
Fungsi LEFT akan mengambil sejumlah karakter dari arah kiri. Jumlah karakter yang akan
diambil ditentukan pada argumen jumlah_karakter. Aturan penulisan selengkapnya adalah
sebagai berikut:
=LEFT(teks;jumlah_karakter)
Sebagai contoh jika pada sel B2Anda ketikan formula berikut ini:
=LEFT("KOMPUTER";3)
Maka hasilnya sel B2tersebut akan menampilkan kata KOM, yaitu 3 karakter pertama dari
arah kiri pada kata KOMPUTER seperti tampak pada gambar berikut ini.
Fungsi MID akan mengambil sejumlah karakter dari arah kiri. Namun awal pengambilan
karakter tersebut bisa Anda tentukan sendiri pada argumen awal. Dan jumlah karakter yang
akan diambil tetap ditentukan pada argumen jumlah_karakter. Aturan penulisan
selengkapnya adalah sebagai berikut:
=MID(teks;awal;jumlah_karakter)
-
5/20/2018 Materi Kkpi Excel
48/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 44
Sebagai contoh jika pada sel B2Anda ketikan formula berikut ini:
=MID("KOMPUTER";4;2)
Maka hasilnya sel B2 tersebut akan menampilkan kata PU, yaitu 2 karakter yang dihitung
mulai karakter ke-4 pada kata KOMPUTER seperti tampak pada gambar berikut ini.
Fungsi RIGHTakan mengambil sejumlah karakter dari arah kanan. Jumlah karakter yang akan
diambil ditentukan pada argumen jumlah_karakter. Aturan penulisan selengkapnya adalah
sebagai berikut:
=RIGHT(teks;jumlah_karakter)
Sebagai contoh jika pada sel B2Anda ketikan formula berikut ini:
=RIGHT("KOMPUTER";3)
Maka hasilnya sel B2 tersebut akan menampilkan kata TER, yaitu 3 karakter pertama dari
arah kanan pada kata KOMPUTER seperti tampak pada gambar berikut ini.
Seperti halnya formula lainnya, penggunaan fungsi teks ini tentu saja dapat merujuk ke
alamat sel tertentu. Sebagai contoh perhatikan tabel berikut ini. Pada sel C2 digunakan
formula MIDdengan teks yang merujuk pada sel B2.
-
5/20/2018 Materi Kkpi Excel
49/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 45
20.
Fungsi VLOOKUP
VLOOKUPtermasuk kedalam salah satu fungsi yang paling banyak digunakan dalam aplikasi
Excel. Sesuai dengan nama dasarnya yaitu lookup yang bisa diartikan melihat atau mencari,
maka fungsi ini akan menghasilkan suatu formula untuk mengisi data pada tabel
berdasarkan data pada tabel lainnya atau tabel referensi tertentu dengan menggunakansuatu nilai kunci yang spesifik. Awalan huruf V didepan kata lookup merupakan singkatan
dari kata Vertical. Istilah vertical ini merujuk pada bentuk tabel referensi yang digunakan,
dimana judul kolomnya terletak dibagian atas dan data-datanya tersusun kebawah secara
vertikal. Contoh sebuah tabel vertical ini bisa dilihat pada gambar berikut ini.
Aturan penulisan VLOOKUP mungkin bisa dibilang agak rumit bagi yang baru saja
mempelajari penggunaan formula pada Excel. Namun jika sudah terbiasa, aturan ini
sebetulnya bisa dibilang sederhana, yaitu:
=VLOOKUP(nilai_kunci;range_tabel_referensi;no_index_kolom;tipe_data)
Penjelasan aturan penulisan tersebut sebagai berikut:
nilai_kunci: Adalah nilai yang dijadikan acuan untuk membaca tabel referensi. Nilai
ini harus ada baik pada tabel yang akan diisi maupun pada tabel referensi.
range_tabel_referensi : Adalah range dari tabel yang berisikan data referensi untuk
mengisi hasil yang diharapkan. Pastikan bahwa range yang Anda pilih tidak
menyertakan judul kolomnya.
no_index-kolom: Adalah nomor urut data dalam tabel referensi yang akan dituliskan
hasilnya. Dimulai dari kolom paling kiri pada tabel referensi tersebut dengan nomor
index 1, dan seterusnya. tipe_data: Ada 2 jenis tipe data yaitu TRUEdan FALSE. Nilai TRUE Anda gunakan jika
nilai datanya tidak pasti atau berada pada range tertentu dan nilai FALSE Anda
gunakan jika nilai data berharga pasti.
Agar lebih jelas bagaimana cara menggunakan fungsi VLOOKUP ini, perhatikan contoh
berikut ini.
-
5/20/2018 Materi Kkpi Excel
50/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 46
Pada contoh tersebut terdapat 2 buah tabel. Data pada tabel Laporan Penjualanjelas akan
selalu bertambah tiap harinya. Setiap kali ada pembeli maka Anda tinggal mengisikan data
tanggal, nama pembeli serta kode voucher-nya. Sementara data Voucherserta Hargaakan
terisi secara otomatis tiap kali Anda mengisikan data pada kolom Kode. Pengisian data
secara otomatis ini bisa dicapai dengan melihat tabel referensi yaitu tabel Stok Gudangdan
tentunya dengan menggunakan formula VLOOKUP.
Langkah pertama akan diisi dahulu data pada tabel Voucheryaitu data pada sel E12. Untukmempermudah pembahasan, aturan penulisan VLOOKUP kembali dituliskan sebagai berikut:
E12=VLOOKUP(nilai_kunci;range_tabel_referensi;no_index_kolom;tipe_data)
nilai_kunciyang digunakan adalah data pada kolom Kodedalam tabel Laporan Penjualan,
yaitu sel D12. Alasannya karena Voucherakan bisa terisi dengan bersandar pada data dalam
kolom Kodetersebut.
-
5/20/2018 Materi Kkpi Excel
51/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 47
Dengan demikian formulanya menjadi:
E12=VLOOKUP(D12;range_tabel_referensi;no_index_kolom;tipe_data)
Kemudian range_tabel_referensi jelas adalah range data pada tabel Stok Gudang yaitu
B4:D7.
Dengan demikian formulanya menjadi:
E12=VLOOKUP(D12;B4:D7;no_index_kolom;tipe_data)
no_index_kolomAnda tentukan dengan melihat didalam range_tabel_referensi . Dalam hal
ini Anda akan mengisi data pada kolom Voucher maka Anda lihat data untuk Vouchertersebut didalam range_tabel_referensi berada pada kolom keberapa?
Dari gambar diatas dapat dilihat bahwa data yang akan diambil berada pada kolom ke-2,
maka no_index_kolomyang digunakan adalah 2. Dengan demikian formulanya menjadi:
E12=VLOOKUP(D12;B4:D7;2;tipe_data)
-
5/20/2018 Materi Kkpi Excel
52/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 48
Yang terakhir adalah tipe_data. Disini yang akan digunakan adalah FALSE. Penjelasannya
adalah karena data tersebut bersifat pasti, misalnya kode S sudah pasti untuk Simpati, kode
E sudah pasti untuk Esia, dan demikian seterusnya. Dengan demikian formula lengkap untuk
mengisi data pada kolom Voucher tersebut adalah:
E12=VLOOKUP(D12;B4:D7;2;FALSE)
Untuk mengisi data pada sel selanjutnya yaitu sel E13dan E14seperti biasanya Anda dapat
menggunakan Auto Fill. Namun perhatikan bahwa tabel referensi yang menjadi acuan
memiliki range alamat sel yang statis alias tetap. Oleh karenanya, sebelum Anda
menjalankan proses Auto Fill maka Anda harus mengunci terlebih dahulu range tabel
referensi tersebut agar menjadi sel yang absolut. Hingga formulanya akan menjadi:
E12=VLOOKUP(D12;$B$4:$D$7;2;FALSE)
Dan proses Auto Fill kini dapat dijalankan untuk mengisi data pada sel-sel berikutnya.
-
5/20/2018 Materi Kkpi Excel
53/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 49
Berikutnya, bagaimana mengisi data pada kolom Hargayaitu sel F12hingga F14? Formula
yang digunakan tentunya tetap sama hanya saja no_index_kolom yang berbeda yaitu 3
karena data yang akan diisikan diambil dari kolom ke-3 pada tabel referensi.
Dan berikut ini formula yang digunakan setelah menggunakan sel absolut untuk range tabel
referensinya.
F12=VLOOKUP(D12;$B$4:$D$7;3;FALSE)
Pada contoh diatas Anda telah melihat penggunaan tipe data FALSE yaitu tipe data yang
pasti. Untuk tipe data TRUEaturan penulisan formula-nya tetap sama, hanya saja data yang
akan diisikan adalah data yang tidak pasti atau berada pada range tertentu. Contoh untuk
penggunaan tipe data TRUE ini bisa dilihat pada tabel berikut ini.
-
5/20/2018 Materi Kkpi Excel
54/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 50
Anda harus mengisikan formula VLOOKUPpada kolom Nilaidalam tabel Daftar Nilai Siswa,
yaitu sel E13hingga sel E15. Nilai yang akan diisikan tersebut mengacu pada range tertentu
dalam tabel referensi, yaitu:
Nilai E: Score 0 s/d 39
Nilai D: Score 40 s/d 59
Nilai C: Score 60 s/d 79
Nilai B: Score 80 s/d 89
Nilai A: Score 90 s/d 59
Kondisi ini jelas menggambarkan sebuah data yang tidak pasti oleh karenanya tipe data yangdigunakan adalah TRUE. Dan formula akhir yang digunakan adalah sebagai berikut,
=VLOOKUP(D13;$B$4:$C$8;2;TRUE)
Yang harus Anda perhatikan untuk tipe data TRUE ini adalah urutan data pada tabel
referensi harus menaik atau tersusun dari data terkecil hingga data terbesar.
-
5/20/2018 Materi Kkpi Excel
55/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 51
21.
Fungsi HLOOKUP
HLOOKUP adalah varian lain dari fungsi VLOOKUP yang telah dibahas pada artikel
sebelumnya. Kegunaannya juga sama yaitu untuk mengisi data pada tabel berdasarkan data
pada tabel lainnya atau tabel referensi tertentu dengan menggunakan suatu nilai kunci yang
spesifik. Perbedaannya dengan VLOOKUP ditunjukan oleh awalan huruf H yang berartiHorizontal, artinya tabel referensi yang digunakan berbentuk horisontal, dimana judul
kolomnya terletak dibagian kiri dan data-datanya tersusun kekanan dalam arah horisontal.
Contoh sebuah tabel horisontal bisa dilihat pada gambar berikut ini.
Aturan penulisan HLOOKUP juga sama dengan VLOOKUP, namun ada sedikit perbedaan
yaitu pada no index-nya. Jika pada VLOOKUP no index mengacu kepada kolom
(no_index_kolom) maka pada HLOOKUP mengacu pada baris (no_index_baris), hal ini
dikarenakan datanya yang memang tersusun dalam suatu baris.
Berikut ini aturan penulisannya:
=HLOOKUP(nilai_kunci;range_tabel_referensi;no_index_baris;tipe_data)
Penjelasan aturan penulisan tersebut sebagai berikut:
nilai_kunci: Adalah nilai yang dijadikan acuan untuk membaca tabel referensi. Nilai
ini harus ada baik pada tabel yang akan diisi maupun pada tabel referensi.
range_tabel_referensi : Adalah range dari tabel yang berisikan data referensi untuk
mengisi hasil yang diharapkan. Pastikan bahwa range yang Anda pilih tidak
menyertakan judul barisnya.
-
5/20/2018 Materi Kkpi Excel
56/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 52
no_index_baris: Adalah nomor urut data dalam tabel referensi yang akan dituliskan
hasilnya. Dimulai dari baris paling atas pada tabel referensi tersebut dengan nomor
index 1, dan seterusnya.
tipe_data: Ada 2 jenis tipe data yaitu TRUE dan FALSE. Nilai TRUE Anda gunakan jika
nilai datanya tidak pasti atau berada pada range tertentu dan nilai FALSE Anda
gunakan jika nilai data berharga pasti.
Sebagai contoh untuk penggunaan HLOOKUP ini, perhatikan tabel berikut.
Pada sel C8dalam tabel Cek Stok, digunakan formula HLOOKUPagar jika Anda ketikan nama
Produkpada sel B8maka otomatis ditampilkan jumlah Stokyang tersedia dengan mengacu
pada tabel referensi Stok Gudang. Dengan demikian maka formula yang digunakan adalah
sebagai berikut.
C8=HLOOKUP(B8;C3:E4;2;FALSE)
Penjelasan dari formula tersebut adalah:
B8 adalah nilai_kunci yang digunakan karena jumlah Stok akan ditampilkan jika
nama Produkdalam sel B8tersebut diisi.
C3:E4adalah range_tabel_referensiyang digunakan sebagai acuan pengisian jumlah
Stokpada tabel Cek Stok.
no_index_baris adalah 2 karena yang akan dituliskan pada tabel Cek Stokdiambil
dari baris kedua pada range_tabel_referensi .
Tipe data yang digunakan adalah FALSEkarena datanya bersifat pasti.
22. Pesan Kesalahan
Saat bekerja dengan formula pada aplikasi Excel, mungkin saja ada kesalahan yang Anda
lakukan. Kesalahan ini akan ditampilkan oleh aplikasi Excel dalam bentuk kode atau pesan
kesalahan tertentu. Dengan mengetahui arti dari pesan-pesan kesalahan tersebut
diharapkan Anda bisa segera mencari solusi atau memperbaikinya. Berikut ini beberapa
pesan kesalahan yang akan ditampilkan oleh Excel tersebut:
-
5/20/2018 Materi Kkpi Excel
57/72
SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 53
a. #####
Pesan kesalahan ini umum disebut dengan istilah "Railroad tracks. Beberapa penyebab
munculnya pesan kesalahan ini diantaranya adalah:
Anda menuliskan data yang panjang namun lebar kolomnya terlalu sempit hingga seltidak dapat menampilkan data tersebut.
Anda mengisi angka atau bilangan negatif pada suatu sel dimana sel tersebut
memiliki format sel Date (tanggal) atau Time (waktu).
b. #REF!
REF bisa diartikan dengan REFERENCE atau referensi, hingga pesan kesalahan ini
umumnya berhubungan dengan kesalahan pada formula yang melibatkan penggunaan
tabel referensi sebagai acuan. Beberapa penyebab munculnya pesan kesalahan ini
diantaranya adalah:
Anda membuat suatu formula yang terhubung dengan suatu tabel referensi namun
kemudian tabel referensi tersebut terhapus.
Pada fungsi lookup atau referensi seperti misalnya VLOOKUP dan HLOOKUP, Anda
menggunakan nomor index kolom atau baris yang lebih besar dari jumlah kolom
atau baris pada tabel referensinya.
Data rujukan yang digunakan dalam formula dipindahkan ke tempat lain.
c. #VALUE!
VALUE bisa diartikan dengan nilai, hingga pesan kesalahan ini umumnya berhubungan
dengan kesalahan nilai yang digunakan pada formula. Beberapa penyebab munculnyapesan kesalahan ini diantaranya adalah:
Anda menggunakan parameter teks pada argumen suatu fungsi yang seharusnya
menggunakan parameter berupa angka atau bilangan. Misalnya seperti
=HLOOKUP(B8;C3:E4;"dua";FALSE).