Formula Advanced Worksheet Microsoft Excel merupakan aplikasi multi fungsi untuk pengolahan data secara otomatis, yang meliputi berbagai perhitungan dasar, penggunaan fungsi, formula, pembuatan grafik, serta manajemen data, sehingga dapat menampilkan data dengan lebih teratur dan mudah dalam pembacaan. Aplikasi Microsoft Excel telah lama digunakan dan mengalami berbagai penambahan fitur di dalamnya. Tabel berikut ini menunjukkan sejarah perkembangan aplikasi Microsoft Excel. Tahun
Versi
Sistem Operasi
Paket Office
1985
Excel 1.0
Apple Macintosh
Tidak ada
1987
Excel 2.0
Windows 2.0
Tidak ada
1988
Excel 1.5
Apple Macintosh
Tidak ada
1989
Excel 2.2
Apple Macintosh
Tidak ada
1989
Excel 2.2
IBM OS/2
Tidak ada
1990
Excel 3.0
Windows 3.0
Tidak ada
1990
Excel 3.0
Apple Macintosh
Tidak ada
1991
Excel 3.0
IBM OS/2
Tidak ada
1
Tahun
Versi
Sistem Operasi
Paket Office
1992
Excel 4.0
Windows 3.0 dan
Tidak ada
Windows 3.1 1992
Excel 4.0
Apple Macintosh
Tidak ada
1993
Excel 5.0
Windows 3.0,
Office 4.2. dan Office
Windows 3.1
4.3
1993
Excel 5.0
Apple Macintosh
Tidak ada
1995
Excel 7 untuk
Windows 95
Microsoft Office 95
Windows 95 dan
Microsoft Office 97
Windows 95 1997
Excel 97 (Excel 8)
Windows NT 1998
Excel 8
Apple Macintosh
Microsoft Office 98 untuk Macintosh
1999
2000
Excel 2000
Windows 98, Windows
(Excel 9)
ME, Windows 2000
Excel 9.0
Apple Macintosh
Microsoft Office 2000
Microsoft Office 2001 untuk Macintosh
2001
Excel 2002
Windows 98, Windows
(Excel 10)
ME, Windows 2000,
Microsoft Office XP
Windows XP 2001
Excel 10
Apple Macintosh OS X
Microsoft Office v.X
2003
Excel 2003
Windows 2000 (Service
Microsoft Office
(Excel 11)
Pack 3), Windows XP,
System 2003
windows Server 2003, Windows Vista, Windows Server 2008 2004
Excel 11
Apple Macintosh OS X
Microsoft Office 2004 untuk Macintosh
2
Tahun
Versi
Sistem Operasi
Paket Office
2007
Excel 2007
Windows XP SP2 atau
Microsoft Office
(Excel 12)
yang lebih tinggi,
System 2007
windows Server 2003 SP1, Windows Vista, Windows Server 2008 2010
Excel 2010
Windows 7, Windows
(Excel 14)
Vista, Windows XP,
Microsoft Office 2010
Windows Server 2008, Windows Server 2008 R2, Windows 8
1.1 Pendahuluan Ada beberapa istilah yang perlu Anda ketahui sebelum menggunakan Microsoft Excel, antara lain: •
Cell, merupakan bagian terkecil pada sebuah worksheet yang dapat diisi dengan nilai tertentu. Isi sebuah cell dapat berupa angka, teks, karakter, fungsi, formula. Untuk menunjukkan posisi cell digunakan kombinasi nama kolom dan baris, misalnya cell B4, cell F5.
Gambar 1.1 Alamat sebuah cell
3
•
Worksheet (lembar kerja), merupakan gabungan dari 256 kolom dan 65536 baris (Excel 2003); atau gabungan dari 16384 kolom dan 1048576 baris (Excel 2007) ; atau gabungan dari 1048576 kolom dan 1048576 baris (Excel 2010).
Gambar 1.2 Jumlah worksheet
•
Workbook (buku kerja), merupakan kumpulan dari 256 worksheet, secara default dengan label sheet 256.
•
Range, merupakan rentang sel yang dapat diolah atau terkena aksi/perintah tertentu. Pemberian alamat range sel tersebut dimulai dengan sel pada sudut kiri dan diakhiri dengan sel pada sudut kanan. Misal, A3:F5 maka range sel mulai dari sel A3 sampai dengan sel F5.
•
Alamat Relatif, merupakan alamat yang dituliskan dalam bentuk rumus atau fungsi maka akan berubah jika di‐copy ke sel lainnya. Misalnya, seperti pada gambar berikut ini, jika di‐copy maka alamat sel akan berubah menyesuaikan posisi sel dari data sumber/sifatnya tidak tetap pada satu data sumber.
4
Gambar 1.3 Contoh penggunaan alamat sel relatif
•
Alamat sel absolut atau mutlak, maksudnya adalah alamat sel yang berisi data yang bersifat absolut, jika dipindah/di‐copy ke sel lain maka nilainya akan tetap dan tidak berubah. Untuk membuat alamat sel absolut caranya dengan mengapit alamat sel menggunakan tanda dollar ($) atau dengan menekan tombol fungsi F4, misalnya: $B$4. Contoh penggunaannya seperti terlihat pada gambar berikut ini.
Gambar 1.4 Contoh penggunaan alamat sel absolut
5
•
Nama kotak, maksudnya Anda dapat memberikan nama pada daerah range sel tertentu, misalnya Anda membuat range sel D11:G16, kemudian pada kotak isian sebelah kiri bar formula, masukkan nama kotak range sel tersebut. Untuk mengakses range sel tersebut, Anda tinggal memanggil nama kotak yang telah dibuat.
Gambar 1.5 Membuat nama kotak range sel
Sedangkan untuk menampilkan fungsi yang ada pada Microsoft Excel, Anda bisa menggunakan kombinasi tombol Shift+F3, sehingga akan tampil kotak dialog Insert Function.
Gambar 1.6 Kotak dialog Insert Function
6
Hal ini akan mempercepat Anda dalam menggunakan fungsi‐fungsi yang ada. Anda tinggal memilih jenis kategori dan daftar fungsinya pada kotak di bawahnya. Excel adalah spreadsheet yang multifungsi. Salah satu fungsi yang memperoleh dukungan yang baik adalah fungsi di bidang matematika. Di bab ini, kita akan belajar bersama bagaimana menggunakan formula dan fungsi matematika di Excel. Excel menggunakan formula (ekspresi matematika yang Anda buat) dan fungsi (ekspresi matematika yang telah ada di Excel) untuk melakukan kalkulasi data secara dinamis di worksheet Anda.
1.1.1
Membuat Formula
Untuk membuat formula, berikut ini caranya: •
Untuk memasukkan formula di sel, klik sel tersebut. Kemudian ketikkan formula. Lalu akhiri dengan menekan Enter atau Tab untuk berpindah ke sel selanjutnya.
•
Semua formula dimulai dengan tanda sama dengan =.
•
Semua formula menggunakan operator matematika seperti berikut:
•
•
* untuk perkalian.
•
/ untuk pembagian.
•
+ untuk penambahan.
•
‐ untuk pengurangan.
Formula berisi angka, akan menghasilkan hasil perhitungannya. Dan ini tidak akan berubah. Misalnya: =3*5 akan menghasilkan angka 15. 7
•
Tapi formula yang mengandung referensi hasilnya bisa bervariasi tergantung pada data di sel yang direferensikan.
Gambar 1.7 Kegiatan ketika membuat formula di Excel
1.1.2
Urutan Operator Matematika
Semua formula menggunakan urutan operasi standar guna menghitung hasilnya. Berikut ini beberapa kaidah yang digunakan. •
Jika ada bagian formula yang diletakkan di dalam tanda kurung, maka yang ada di dalam tanda kurung ini akan diproses terlebih dahulu, sebelum yang lainnya. Contohnya seperti berikut: Formula (B2*C2)‐A3 akan menjumlahkan B2 dan C2 terlebih dahulu, baru kemudian mengurangkannya ke A3.
•
Setelah ekspresi yang ada di tanda kurung, Excel akan mengal‐ kulasi formula menggunakan operator matematika di urutan berikut ini:
8
•
Perkalian.
•
Pembagian.
•
Penambahan.
•
Pengurangan.
Dengan kata lain, Excel akan mulai memproses formula dengan perkalian dan pembagian. Setelah selesai, baru Excel akan menambahkan dan mengurangkan sisa dari formula.
1.1.3
Memasukkan Fungsi
Tiap fungsi Excel adalah fungsi yang predefined (telah didefinisikan). Dan umumnya menggunakan argumen. Walaupun beberapa fungsi tidak menggunakan argumen, tetapi ada juga yang memiliki argumen yang cukup kompleks sampai 3 atau 4 argumen. Excel juga menyediakan fungsi Paste Function untuk memudahkan menyalin formula. Anda tinggal klik pada sel yang mengandung fungsi, kemudian menyalin dengan klik Edit > Paste atau menekan CTRL+C dan beralih ke sel yang akan dituju lalu klik Edit > Paste atau menekan CTRL+V. Kemudian klik ikon Paste function lalu klik ikon panah ke bawah dan klik pada ikon Paste Formulas seperti berikut.
Gambar 1.8 Paste Formulas
9
Untuk memasukkan fungsi atau formula, caranya seperti berikut ini: 1. Letakkan pointer pada sel yang ada. 2. Klik pada tombol Insert Function.
Gambar 1.9 Tombol Insert Function untuk memasukkan fungsi
3. Muncul jendela fungsi Insert Function. Anda dapat mencari fungsi berdasarkan deskripsi di kotak Search for a function.
Gambar 1.10 Membuat fungsi
10
4. Excel akan menampilkan syntax window yang akan membantu Anda membangun fungsi. Dari window ini, Anda bisa klik tombol Collapse yang ada di bagian kanan dengan panah merah.
Gambar 1.11 Tampilan syntax window
5. Anda bisa men‐drag untuk memilih sel atau range sel yang akan dimasukkan sebagai argumen dari fungsi Anda. Kemudian tekan Enter untuk memasukkannya.
Gambar 1.12 Memasukkan fungsi Excel dengan drag untuk memilih
6. Kalau mau menambahkan parameter lagi, Anda bisa memilih di kotak selanjutnya. Klik OK kalau semuanya sudah selesai.
11
Gambar 1.13 Klik OK kalau semuanya sudah selesai
7. Maka hasil dari fungsi akan terlihat.
Gambar 1.14 Hasil dari fungsi akan terlihat
Tiap fungsi bisa memiliki jumlah argumen yang berbeda‐beda. Berikut ini contoh di antaranya. Fungsi tanpa argumen: •
Rand
•
Pi
Fungsi dengan 1 argumen:
12
•
Average
•
Max
•
Hour
Fungsi dengan 2 argumen: •
Round
•
Countif
Fungsi dengan 3 argumen: •
If
•
SumIf
Fungsi dengan argumen 1 atau lebih: •
And
•
Or
•
Sum
Anda juga bisa memasukkan referensi ke range sel. Berikut ini beberapa informasi mengenai range sel: •
Kalau di worksheet lain, caranya: worksheet!A1:D4.
•
Kalau
di
workbook
lain,
contohnya:
c:\dokumenku\[tes.xls]Sheet1!A2:A5. •
Kalau di lebih dari satu worksheet: sheet1:sheet5:A12.
1.2 Fungsi Kondisional dan Logis Excel memiliki beberapa fungsi logis yang memungkinkan Anda menge‐ set beberapa kondisi dan membuat data bisa merespons kondisi tersebut. Misalnya Anda ingin agar kalkulasi tertentu dilakukan atau teks tertentu ditampilkan jika kondisi tertentu terpenuhi. Fungsi yang digunakan untuk analisis ini bisa diakses di Insert > Function dan dengan judul Logical. 13
Gambar 1.15 Kategori fungsi dengan statement Logical
1.2.1
Statemen IF
Fungsi IF digunakan untuk menganalisis data, dan menentukan apakah data sesuai dengan kondisi tertentu atau tidak. Setelah itu melakukan action tertentu berdasarkan pada kondisi yang dipenuhi. Formula ini bisa dimasukkan dengan berbagai cara. Misalnya dengan mengetikkan langsung atau menggunakan fungsi library di ribbon formula pada bagian yang berkaitan dengan fungsi logik. Biasanya fungsi IF dibarengi dengan tiga argumen yang ditutup pada satu tanda kurung. Argumen yang dimasukkan adalah kondisi yang harus dipenuhi (tes_logik), lalu action yang harus dilakukan ketika kondisi True dipenuhi (nilai_true), serta action yang harus dilakukan ketika nilai False yang muncul (nilai_false). = IF (tes_logik, nilai_true, nilai_false)
14
Statement IF ini bisa ada di kategori Logical pada jendela Insert Function.
Gambar 1.16 Insert function di kategori Logical
Untuk melihat kejelasan tentang IF secara sederhana, berikut ini contoh penggunaannya. Di mana penulis akan menampilkan dua string yang berbeda kalau nilai yang dimasukkan berbeda juga: 1. Jalankan Excel. 2. Kemudian buat tampilan seperti berikut. Kita akan membuat formula di kotak yang terseleksi berdasarkan nilai yang ada di kotak yang mengandung shading (D4).
Gambar 1.17 Kondisi sebelum dimasukkan formula dengan statement IF
15
3. Kalau Anda buka jendela logical If, muncul jendela Function Arguments yang harus diisikan untuk memilih argumen‐ argumen yang ada.
Gambar 1.18 Pengisian argumen-argumen di jendela Function argumen
4. Klik pada kotak di sebelah kanan Logical test untuk memilih argumen pada logika. Kemudian tentukan tes logikanya dengan menggunakan statement boolean tertentu, misalnya apakah nilai dari sel yang diberi shade lebih dari delapan (8) atau tidak.
Gambar 1.19 Penentuan shade
5. Kemudian tentukan teks yang akan ditampilkan dengan mengisikan value_if_true di antara titik dua. 16
6. Dan tentukan juga teks yang akan diisikan jika tes logika menjadi false. Teks juga diapit dengan tanda titik dua.
Gambar 1.20 Pengisian nilai yang ditampilkan saat true dan saat false
7. Maka kalau ditekan Enter, fungsi akan terlihat.
Gambar 1.21 Fungsi sudah terlihat
8. Coba sekarang berikan angka 9 di nilai shading, maka tes logika akan menjadi True. Maka tulisannya “Nilai Anda Bagus”.
Gambar 1.22 Nilai Logika menjadi True
17
9. Kalau diberikan nilai dengan test value False, maka hasilnya menjadi Value_If_false.
Gambar 1.23 Pengisian value jika False
Ketika membuat statement kondisional di atas, Anda bisa melihat digunakannya operator. Simbol‐simbol yang digunakan sebagai operator logika adalah: >
Lebih besar dari
<
Lebih kecil dari
>=
Lebih besar atau sama dengan
<=
Kurang dari atau sama dengan
=
Sama dengan
<>
Tidak sama dengan
Contoh tes logika contohnya adalah B1 > B2, yang akan mengetes apakah sebuah nilai di sel B1 lebih besar dari B2 atau tidak? Tipe lain dari tes logik bisa berupa string teks, jika Anda ingin menggunakan string teks, Anda bisa menggunakan tanda petik dua, seperti C5=”Ya”. Anda bisa juga membuat contoh praktisnya, misalnya sebuah admin perusahaan ingin mencari tahu apakah salesman tertentu sesuai target 18
atau belum, di mana kalau melebihi target, akan mendapat bonus 20% atau 0,2. Seperti terlihat pada gambar berikut ini.
Gambar 1.24 Pembuatan Tes logika
Anda juga bisa mengubah nilai value_if_false dengan teks tertentu, misalnya “TANPA BONUS” seperti berikut ini.
Gambar 1.25 Tulisan “TANPA BONUS” jika value_if_false
19
Maka kalau diklik OK, terlihat kalau target lebih rendah dari penjualan, terlihat tulisan “TANPA BONUS” di situ.
Gambar 1.26 Tulisan “TANPA BONUS” ketika target lebih rendah dari penjualan
Tapi kalau target melebihi penjualan, terlihat ada Bonus sebanyak 20% dari yang ada. Ini terbukti ketika formula tersebut disalin/drag ke bawahnya.
Gambar 1.27 Di baris kedua, terlihat adanya bonus dengan nilai 20% dari target, yang dibuat dari statement IF
1.2.2
IF Bersarang
Ketika Anda memiliki lebih dari satu kondisi dan ada 2 hasil yang bisa dipilih, maka Anda bisa menggunakan IF Bersarang alias Nested IF. Contoh sintaksnya adalah: 20
=IF(tes_logika_pertama, nilai_true_pertama, IF(tes_logika_kedua, nilai_true_kedua, nilai_false) )
1.2.3
SumIf
Anda bisa menggunakan SumIf untuk menghitung total nilai di mana ada syaratnya. Misalnya kolom tertentu memiliki teks tertentu atau nilai yang memenuhi kriteria tertentu. Sintaksnya adalah: =SUMIF(range,kriteria,range_sum)
Beberapa pengertian adalah: •
Range= Range dari sel yang ingin dites.
•
Kriteria = kriteria di form berupa angka, ekspresi atau teks yang menentukan sel mana yang akan diproses SUM‐nya.
•
Sum Range: Sel‐sel yang akan di‐sum. Sel ini akan di‐sum jika sel di kolom kriteria bernilai True. Jika sum range dihapuskan, maka sel di range akan ditampilkan.
Misalnya ada sebuah tabel berikut, dan kita ingin menjumlahkan nilai total dari supplier bernama “Cipta kencana”. 1. Klik pada Insert Function.
Gambar 1.28 Tampilan tabel awal
21
2. Kemudian buat fungsi SUMIF(). Muncul argumen Range, criteria, dan sum_range.
Gambar 1.29 Pengisian argumen
3. Pilih dulu argumen untuk range, yaitu di kolom Supplier.
Gambar 1.30 Pemilihan argumen untuk range
4. Kemudian tentukan kriteria dari range, misalnya karena kita hanya ingin men‐sum yang tulisannya “Cipta kencana”, maka isikan di kriteria.
22
Gambar 1.31 Pengisian di kriteria
5. Isikan range untuk dijumlahkan pada kolom Total di argumen Sum_range.
Gambar 1.32 Pengisian range yang akan dijumlahkan
6. Tekan Enter, hasilnya ditampilkan.
23
1.2.4
CountIf
CountIF digunakan untuk menghitung jumlah sel pada range tertentu sesuai dengan kriteria tertentu. Sintaksnya adalah: =countif(range, kriteria)
Penjelasan argumen: •
Range: satu sel atau lebih yang akan dihitung, termasuk angka, nama, array atau referensi yang digunakan.
•
Kriteria: Kriteria dalam bentuk angka, ekspresi, teks, atau referensi sel yang berisi angka. Nilai teks yang kosong akan tidak dianggap.
Contoh penggunaan CountIf ini terlihat di tutorial seperti berikut ini: 1. Misalnya ada daftar nilai beberapa siswa. Kita ingin menghitung berapa jumlah siswa yang lulus. Di mana seorang siswa disebut lulus jika nilainya lebih dari 70.
Gambar 1.33 Daftar beberapa siswa
24
2. Kemudian pilih fungsi COUNTIF(). Muncul kotak untuk memilih parameter fungsi CountIf.
Gambar 1.34 Kotak pemilihan parameter fungsi
3. Tentukan area yang akan dihitung di Range. Yaitu di kolom Nilai.
Gambar 1.35 Penentuan area yang dijadikan range
4. Kemudian tentukan kriteria untuk menghitung jumlah lulus, yaitu yang kriteria lebih dari 70, yaitu “>70”.
25
Gambar 1.36 Pengisian range dan kriteria
5. Hasilnya, di tempat dibuatnya formula akan terlihat bahwa dari 10 orang yang ada, hanya 6 orang yang lulus.
Gambar 1.37 Jumlah orang yang lulus
1.2.5
AverageIf
Ini merupakan permintaan ke fungsi untuk menghitung rata‐rata (average) dari range tertentu menggunakan kondisi tertentu. Ini mirip dengan SumIf dan Countif. Average If memungkinkan user untuk membuat rata‐rata berkondisional dengan mudah menurut kriteria tertentu. 26
Rumus yang digunakan adalah: AverageIf (Range, Kriteria, [average_range])
Penjelasan dari beberapa argumen yang ada adalah: •
Range: Satu sel atau lebih yang akan dihitung rata‐ratanya. Bisa termasuk angka, nama, array atau referensi yang berisi angka.
•
Kriteria: Kriteria dalam bentuk angka, ekspresi, referensi sel atau teks yang menentukan sel yang akan dirata‐rata. Misalnya angka 20, ”>20”, “20” atau B4.
•
[Average_Range]: Sel sebenarnya yang dirata‐rata, jika diko‐ songkan, maka digunakan range.
Contohnya adalah: =AVERAGEIF(A2:A5,”>25000”, B2:B5)
1.2.6 And, Or, dan Not Kalau ingin membuat formula dengan IF yang sangat banyak, lebih baik Anda menggunakan operand AND, OR, dan NOT. Ini digunakan untuk mengetes fungsi lebih dari satu. Ketiga fungsi And, Or, dan Not ini bisa dikombinasikan yang nantinya akan menghasilkan nilai boolean True atau False. 1.2.6.1 AND Fungsi ini merupakan tes logika yang akan mengetes apakah semua kondisi True. Kalau semuanya True, maka akan dihasilkan nilai boolean True. Jika salah satu atau kedua argumen False, maka nilai akhirnya akan menjadi False juga. Argumen yang dimasukkan di And diletakkan di dalam tanda kurung. Anda bisa memasukkan argumen maksimal 30 buah untuk And. Berikut 27
ini contoh untuk mengecek apakah konten dari dua sel B1 dan B2 lebih dari 100. = AND (B1>100, B2 > 100)
Argumen‐argumen yang dimasukkan di statement AND dipisahkan oleh koma, dan maksimalnya 30 argumen untuk satu statemen AND. Jika salah satu dari nilai argumen di atas memiliki nilai kurang dari satu, maka statement AND ini akan menghasilkan nilai False. =IF(AND(B1>100; B2>100); B1+B2;“Angka tidak mencukupi”)
Berikut ini contohnya: 1. Misalnya ada dua nilai, di D4 dan di D5. Kita ingin men‐ jumlahkan kalau saja nilainya lebih dari 100, tapi kalau salah satu ada yang tidak lebih dari 100, maka akan ditampilkan tulisan “Angka tidak mencukupi”.
Gambar 1.38 Kondisi awal sebelum pembuatan fungsi
2. Isikan tes logika dengan AND, menggunakan dua argumen, yaitu apakah D4>100 dan D5>100. 3. Isikan nilai jika hasilnya True. 4. Isikan nilai jika hasilnya False. 28
Gambar 1.39 Pengisian tes logika, lalu value jika True dan jika False
5. Maka hasilnya adalah tulisan “Angka tidak mencukupi”, ini karena nilai AND menghasilkan false karena minimal salah satu dari kedua parameter bernilai False.
Gambar 1.40 Hasil pembuatan AND
Penggunaan lain dari AND adalah untuk mengecek apakah sebuah angka ada di antara limit tertentu. Misalnya untuk mengecek apakah angka berada antara 50 dan 100. =IF(AND(B1>50;B1<100);B1;”Angka diluar range”)
1.2.6.2 Or Fungsi ini merupakan tes logika untuk melihat apakah ada dari salah satu kondisi yang True. Kalau minimal ada satu yang true, maka hasil True akan dikembalikan. 29
Contohnya adalah: =IF(OR(B1>100;B2>100); “Minimal satu ada yang TRUE”;”Nilai tidak ada yang lebih dari 100 satupun”)
Pada formula di atas, agar bernilai True, maka nilai B1 atau B2 minimal harus ada satu yang punya nilai lebih dari 100. 1.2.6.3 Not Not akan mengecek apakah argumen False, sehingga menghasilkan nilai True. Jadi, ini kebalikannya. Misalnya, jika sel B1 berisi angka 13, maka akan ditampilkan nilai B1, kalau tidak, dituliskan “Tidak beruntung”. =IF(NOT(B1=13);B1;”Tidak beruntung”)
Fungsi NOT hanya bisa berisi satu argumen.
1.2.7
IsError
Fungsi IsError ini sangat berguna jika formula Anda menghasilkan nilai error. Sintaksnya adalah: Iserror(nilai)
Nilai ini bisa berupa semua nilai error, seperti:
30
•
#N/A
•
#VALUE!
•
#REF!
•
#DIV/0!
•
#NUM!
•
#NAME?
•
#NULL!
Contohnya seperti gambar berikut. Pertama ada 3 sales dengan nilai penjualan tiap bulan yang berbeda‐beda. Kemudian tentukan total‐nya di kolom Total. Karena yang pertama (joni) dan yang kedua (jodi) belum punya penjualan, maka hasilnya masih kosong.
Gambar 1.41 Penjualan yang masih kosong
Sekarang akan menghitung rata‐rata/average di kolom Rata‐rata. Karena untuk joni dan jodi tidak ada nilai, muncul error division by zero.
Gambar 1.42 Kolom rata-rata menghasilkan error division by zero
Untuk itu, dibuat statement IF tapi untuk tes logikanya menggunakan ISERROR(fungsi_average). Dengan nilai jika TRUE (berarti ada error‐nya), muncul tulisan “Ada Error Nih”. Kalau False (artinya tidak ada error‐nya), maka fungsi AVERAGE () akan dijalankan. 31
Gambar 1.43 Pemasukan argumen fungsi
Maka di baris pertama dan kedua, muncul tulisan “Ada Error Nih”.
Gambar 1.44 Tampilan ketika error, diganti dengan teks
1.2.8
IfError
IfError adalah permintaan yang lazim untuk menyederhanakan penge‐ cekan error. Sintaksnya adalah: = IFERROR(nilai, nilai_jika_error)
Contohnya ketika user ingin menangkap error di VlookUP, dan menggunakan teks error sendiri dan bukan dari pesan error di Excel, maka bisa menggunakan fungsi IF dan ISERROR: =IF(ISERROR(VLOOKUP(“Joni”; Sales; 3; FALSE)); “Nilai Tidak ditemukan”; VLOOKUP(“Joni”;Sales; 3; FALSE)
32
1.3 Fungsi LookUp Excel bisa memberikan hasil yang bervariasi di sel, tergantung pada kondisi yang ditentukan. Misalnya berdasarkan statement apakah angka ada di atas atau di bawah limit tertentu. Sehingga data yang berbeda akan menghasilkan tampilan yang berbeda. Anda sebelumnya telah memakai statemen IF, tapi IF ini punya kelemahan karena maksimal hanya bisa mengakomodasi tujuh statement IF. Karena itu, kalau mau membuat lebih dari tujuh, Anda perlu menggunakan fungsi LOOKUP. Fungsi ini memungkinkan Anda membuat formula untuk melihat banyak data dan mencari informasi yang sesuai dengan kondisi tertentu. Ini lebih mudah diatur daripada IF bersarang dan bisa menghasilkan hasil yang lebih bervariasi.
1.3.1
LookUp
Sebelum memulai menggunakan fungsi Lookup, Anda perlu belajar apa saja arti dan sintaks dari fungsi lookup. 1.3.1.1 Vector Lookup Sebuah vector adalah untaian data yang hanya berisi satu baris atau satu kolom. Lookup akan melihat baris atau kolom untuk mencari nilai tertentu. Ketika nilai ini ditemukan, hasil di baris atau kolom yang berdekatan akan ditampilkan. Misalnya kolom D di spreadsheet berisi angka, sementara kolom E‐nya berisi teks. Lookup akan mencari nilai yang diminta dari kolom D dan mengembalikan teks hasilnya dari kolom E. Sintaks untuk lookup adalah:
33
=LOOKUP(nilai_lookup; vektor_lookup; hasil_vektor)
Penjelasan dari berbagai argumen di atas adalah: •
Nilai_lookup: mengacu pada angka atau entri teks untuk dicari. LOOKUP_VECTOR adalah area untuk melakukan pencarian dari LOOKUP_VALUE. RESULT_VECTOR merupakan baris atau kolom yang berdekatan dari nilai atau teks bersangkutan yang telah ditemukan.
•
Data di lookup vector harus diletakkan dalam urutan, misalnya dari yang paling rendah ke paling tinggi, teks dari A ke Z. Jika ini tidak dilakukan, maka fungsi Lookup bisa menghasilkan nilai yang salah.
Contoh penggunaan Lookup ini seperti berikut: 1. Misalnya ada tabel yang menampilkan kolom Gaji dan Mobil seperti berikut.
Gambar 1.45 Tabel yang menampilkan kolom Gaji dan Mobil
34
2. Klik pada Insert Function, kemudian cari “Lookup” di kotak Search for a function.
Gambar 1.46 Search for a function
3. Tentukan jenis argumen, yaitu yang lookup_value, lookup, dan result_vector. Klik OK kemudian.
Gambar 1.47 Pemilihan argumen
4. Pilih lookup_value ke sel gaji. 5. Pilih lookup_vector ke daftar gaji. 6. Dan result_vector ke daftar mobil. Klik OK. 35
Gambar 1.48 Pengisian result_vector
7. Ketika sel gaji belum dimasukkan, muncul error #N/A yang artinya Not Available.
Gambar 1.49 Kode menghasilkan tampilan error karena belum diisikan gaji
8. Kalau misalnya gaji diisikan 6, otomatis fungsi akan me‐lookup mobil yang sesuai dengan gaji 6 juta, yaitu Innova.
36
Gambar 1.50 Tampilan teks oleh Lookup dengan gaji = 6
9. Kalau misalnya diganti dengan gaji = 2, maka mobil otomatis berganti ke Suzuki Carry.
Gambar 1.51 Tampilan mobil berganti karena fungsi Lookup berubah ke angka 2
10. LookUp juga akan mengestimasi nilai terdekat, jadi misalnya dimasukkan nilai gaji = 5, maka akan dicarikan mobil yang terdekat nilainya adalah ke = 4 . 37
Gambar 1.52 Ketika tidak ada nilai 5 di daftar gaji, maka akan dipindahkan ke yang lebih dekat
11. Formula bisa lebih disederhanakan dengan Define Name untuk memberi range dengan nama tertentu. Misalnya kolom Gaji diberi nama “DaftarGaji”, lalu sel gaji diisikan “Gaji” dan kolom Mobil diberi nama “DaftarMobil”. 12. Nanti bisa diganti kodenya dengan: LOOKUP(Gaji;DaftarGaji;DaftarMobil)
13. Ada kemungkinan lookup_value ada di bawah nilai terendah dari vektor, kalau demikian kasusnya, maka akan dimunculkan pesan #N/A.
38
Gambar 1.53 Tampilan ketika nilai di bawah vektor yang ada, maka ditampilkan error #N/A
1.3.1.2 Hlookup Lookup Horizontal bisa digunakan tidak hanya di sebuah vector (satu kolom atau baris data), tapi juga bisa di array (kolom dan baris yang ganda). Hlookup mencari nilai tertentu secara horizontal di baris paling atas. Ketika nilai ditemukan, Hlookup akan mencari ke bawah untuk melihat baris tertentu dan memasukkan nilai dari sel. Ini sangat berguna ketika data ditampilkan dalam format tabular besar, di mana susah bagi Anda untuk membaca semua kolom dan melihat ke bawahnya untuk sel yang ada. Hlookup akan melakukan ini secara otomatis. Sintaks untuk Hlookup ini adalah: = Hlookup (nilai_lookup; array_tabel, nomor_indeks_baris)
Beberapa penjelasannya seperti berikut: •
Nilai_lookup bisa berupa angka, teks, atau referensi ke sel, yang merupakan nilai yang bisa dicari di semua row teratas dari data. 39
•
Array_tabel adalah referensi sale atau nama range dari semua data tabel.
•
Nomor_indeks_baris merepresentasikan baris di mana hasil ini ditampilkan. Ini harus berupa angka, misalnya 4 yang akan menginstruksikan Hlookup untuk mengekstrak nilai baris 4 dari array_tabel.
Perlu diperhatikan bahwa data di array harus dalam urutan ascending, yaitu dari yang kecil ke yang besar. Dengan fungsi Lookup sederhana, hanya satu kolom atau data vektor yang dibutuhkan, tapi Hlookup menggunakan array yang berupa lebih dari satu kolom atau baris. Misalnya user memiliki spreadsheet yang menampilkan berbagai nilai suku bunga untuk berbagai periode seperti berikut.
Gambar 1.54 Tampilan awal tabel
1. Buat fungsi Hlookup, cari nilai lookup berupa nilai pinjaman, lalu table array berupa tabel yang akan diambil, serta nomor index berupa tahun ditambah 1.
40
Gambar 1.55 Pemasukan argumen Hlookup
2. Terlihat tampilan formula seperti berikut ini.
Gambar 1.56 Tampilan formula terlihat setelah dibuat
3. Maka kalau Anda memasukkan waktu, dan jumlah pinjaman, akan ditampilkan bunga yang diperoleh sesuai dengan tabel yang ada.
41
Gambar 1.57 Daftar pinjaman sesuai dengan bunga yang ada
1.3.2
Transpose
Fungsi Transpose akan digunakan untuk memindahkan data yang ada pada baris tertentu ke bentuk kolom, atau sebaliknya dari kolom menjadi baris tertentu. Hal ini berguna ketika Anda telah menyusun data dalam bentuk baris/kolom, kemudian perlu mengubahnya dalam bentuk kolom/baris, tentu akan kesulitan ketika harus mengetik satu per satu secara manual. Contoh penggunaan fungsi ini sebagai berikut. 1. Persiapkan tampilan data, misal seperti berikut ini.
Gambar 1.58 Data yang akan di-transpose
42
2. Kemudian blok range sel, di mana saja yang Anda inginkan untuk menampilkan hasil transpose. 3. Klik tab Formulas > Lookup & Reference > TRANSPOSE.
Gambar 1.59 Memilih fungsi TRANSPOSE
4. Pada kotak dialog Function Arguments, pilih sel yang akan di‐ transpose dengan melakukan pilihan pada bagian Array.
Gambar 1.60 Memilih sel yang akan di-transpose
43
5. Kemudian akhiri perintah dengan menekan tombol Ctrl + Shift + Enter secara bersamaan. 6. Sehingga akan tampil hasil transpose seperti berikut ini. Tampilan data yang sebelumnya secara horizontal akan berubah menjadi bentuk vertikal.
Gambar 1.61 Hasil transpose
7. Pada kotak formula di bagian atas akan terlihat bentuk rumusan yang ada, seperti berikut.
Gambar 1.62 Formula TRANSPOSE
8. Terlihat formula yang terbentuk di antara tanda { }, yang menunjukkan formula dalam bentuk array.
44
