programming excel

(1)

Programming

in

Microsoft

Excel

2000

Penyusun : Achmad Affandi ( 0044-30-0902 )

(2)

2

2 Production Engineering

Production Engineering Department

Department

PT.

PT. Semarang

Semarang Autocomp

Autocomp

Manufacturing

Indonesia

Semarang

2005

Daftar Isi

Modul 1 :

Modul 1 : FormulaFormula dandan FunctionFunction...3...3

Formula Formula (rumus)...3(rumus)...3

Penyusunan Penyusunan FormulaFormuladi dalam Excel...3di dalam Excel...3

Operator Operator perhitungan...4perhitungan...4

Urutan proses perhitungan...4

Array Formula Array Formula...5...5

Cell/Range References Cell/Range References...6...6

Kenapa menggunakan pengalamatan yang tidak Kenapa menggunakan pengalamatan yang tidak relativerelative...7...7

Notasi R1C1...7

Pengalamatan Worksheet atau Workbook lain...8

Nama Sebagai Referensi...8

Manfaat Lain dari Sistem Penamaan Cell/Ra Manfaat Lain dari Sistem Penamaan Cell/Range...nge... ...9...9

Formula Errors Formula Errors...10...10

Function Function(fungsi)...12(fungsi)...12

Fungsi di dalam Fungsi...12

Lebih lanjut dengan fungsi IF...13

Menggunakan fungsi COUNTIF dan SUMIF...16

Menggunakan Menggunakan Array Formula Array Formula untuk COUNT dan SUM...17untuk COUNT dan SUM...17

Beberapa Fungsi Matematika dalam Excel...18

Beberapa Fungsi Statistik dalam Excel...22

Beberapa Fungsi Tanggal dan Waktu Beberapa Fungsi Tanggal dan Waktu dalam Excel...25dalam Excel...25

Beberapa Fungsi Logika dalam Excel...27

Beberapa Fungsi Teks dalam Excel...28

Beberapa Fungsi LookUp dan Alamat dalam Excel...32

Beberapa Fungsi Information dalam Excel...36

MegaFormula MegaFormula...41...41

Modul 2 : Pengenalan Modul 2 : Pengenalan VBAVBA...44...44

Sekilas Pemrograman VBA...44

Visual Basic Editor Visual Basic Editor ...46...46

Membuat kode Membuat kodeVBAVBA...47...47

Memasukkan kode dengan cara biasa...48

(3)

2

2 Production Engineering

Production Engineering Department

Department

PT.

PT. Semarang

Semarang Autocomp

Autocomp

Manufacturing

Indonesia

Semarang

2005

Daftar Isi

Modul 1 :

Modul 1 : FormulaFormula dandan FunctionFunction...3...3

Formula Formula (rumus)...3(rumus)...3

Penyusunan Penyusunan FormulaFormuladi dalam Excel...3di dalam Excel...3

Operator Operator perhitungan...4perhitungan...4

Urutan proses perhitungan...4

Array Formula Array Formula...5...5

Cell/Range References Cell/Range References...6...6

Kenapa menggunakan pengalamatan yang tidak Kenapa menggunakan pengalamatan yang tidak relativerelative...7...7

Notasi R1C1...7

Pengalamatan Worksheet atau Workbook lain...8

Nama Sebagai Referensi...8

Manfaat Lain dari Sistem Penamaan Cell/Ra Manfaat Lain dari Sistem Penamaan Cell/Range...nge... ...9...9

Formula Errors Formula Errors...10...10

Function Function(fungsi)...12(fungsi)...12

Fungsi di dalam Fungsi...12

Lebih lanjut dengan fungsi IF...13

Menggunakan fungsi COUNTIF dan SUMIF...16

Menggunakan Menggunakan Array Formula Array Formula untuk COUNT dan SUM...17untuk COUNT dan SUM...17

Beberapa Fungsi Matematika dalam Excel...18

Beberapa Fungsi Statistik dalam Excel...22

Beberapa Fungsi Tanggal dan Waktu Beberapa Fungsi Tanggal dan Waktu dalam Excel...25dalam Excel...25

Beberapa Fungsi Logika dalam Excel...27

Beberapa Fungsi Teks dalam Excel...28

Beberapa Fungsi LookUp dan Alamat dalam Excel...32

Beberapa Fungsi Information dalam Excel...36

MegaFormula MegaFormula...41...41

Modul 2 : Pengenalan Modul 2 : Pengenalan VBAVBA...44...44

Sekilas Pemrograman VBA...44

Visual Basic Editor Visual Basic Editor ...46...46

Membuat kode Membuat kodeVBAVBA...47...47

Memasukkan kode dengan cara biasa...48

(4)

3

Memasukkan kode dengan

Memasukkan kode dengan Macro Recorder Macro Recorder ...49...49

Relative Relativeatauatau Absolute Absolute...51...51

Membersihkan hasil perekaman Membersihkan hasil perekaman macromacro...53...53

Objek dan Koleksi...54

Mengetahui Objek yang Aktif...55

Bekerja dengan objek Bekerja dengan objekRangeRange...56...56

Properti PropertiRangeRange...57...57

Properti PropertiCellsCells...57...57

Properti PropertiOffsetOffset...58...58

Pemrograman Dasar VBA...59

Bahasa VBA : Sekilas Bahasa VBA : Sekilas Pandang...59Pandang...59

Komentar...60

Variabel, Jenis Data, dan Variabel, Jenis Data, dan Konstanta...61Konstanta...61

Menentukan Jenis Data...62

Mendeklarasikan Variabel...63

Menentukan Jenis Data...63

Memaksa Pendeklarasian Semua Memaksa Pendeklarasian Semua Variabel...64Variabel...64

Lingkup Suatu Variabel...64

Konstanta...66

Jenis Data Jenis DataStringString...66...66

Jenis Data Jenis DataDateDate...66...66

Operator Perhitungan, Logika, dan Perbandingan...67

Array Array ...67...67 Variabel Objek...69 Variabel Objek...69 Built-In Function Built-In Function...69...69

Struktur Dasar Pemrograman...71

With-End With With-End With...71...71 For-Next For-Next...71...71 Do-While Do-While...74...74 Do-Until Do-Until...74...74 If-Then If-Then...76...76 Select-Case Select-Case...77...77

Bekerja Dengan Prosedur...78

Mendeklarasikan Prosedur...78

Menjalankan Prosedur...79

Bekerja Dengan Fungsi...84

Mendeklarasikan Fungsi...85 Mendeklarasikan Fungsi...85 Menjalankan Fungsi...86 Menjalankan Fungsi...86 Argumen/Parameter...87 Argumen/Parameter...87 By Value By ValueatauatauBy ReferenceBy Reference...88...88

Contoh Prosedur dan Fungsi...88

Teknik Penanganan Kesalahan...91

‘Menangkap’ ‘Menangkap’ Error Error ...91...91

On Error Resume Next On Error Resume Next...91...91

On Error Goto On Error Goto error_labelerror_label...92...92

Manfaat ‘Menangkap’ Kesalahan...93

(5)

4

(6)

5 Modul 1 :

Formula

_dan

Function

Formula (rumus)

Sebagian pengguna Excel menganggap bahwa Formula dan Function adalah sama atau sejenis.

Padahal formula (rumus) adalah satu atau beberapa rumus yang dibuat oleh pengguna,

sedangkan function (fungsi) adalah suatu rumus tunggal yang sudah dibuat oleh Excel dan

memiliki fungsi dan maksud tertentu. Suatu formula yang kita buat dapat terdiri dari beberapa fungsi, tetapi sangat tidak mungkin di dalam fungsi yang sudah ada kita sisipkan formula yang kita buat (karena kita tidak bisa membuka isi dari fungsi tersebut.)

Sebuah formula dapat berupa perhitungan matematik, membandingkan nilai antar worksheet, atau menggabungkan teks. Formula juga dapat berisi referensi ke cell lain yang :

berada dalam worksheet yang sama;

berada dalam worksheet yang berbeda tetapi masih dalam satu workbook; atau dalam workbook yang berbeda.

Contoh berikut ini akan menambahkan nilai 25 dan nilai cell B4 kemudian membagi hasilnya

dengan jumlah (Sum) nilai D5, E5, dan F5.

Alamat cell Nilai konstan Fungsi (function) =(B4+25)/SUM(D5:F5) Alamat range operator bagi operator tambah

CATATAN …

Formula yang kita buat akan segera dihitung oleh Excel setelah kita selesai memasukkannya. Ini akan terjadi jika Calculation Mode (lihatTools Options) diset Automatic. Jika formula tersebut berhubungan dengan formula cell lain, maka formula lain tersebut dihitung terlebih dahulu oleh Excel.

Terkadang kita memiliki worksheet dengan ratusan atau ribuan formula yang kompleks dimana proses perhitungan akan memakan waktu yang lama. Jika ini terjadi, kita sebaiknya menset Calculation Mode menjadi Manual. Ketika dalam mode ini, Excel akan menampilkan teks Calculate distatus bar , jika dalam worksheet kita terdapat formula yang belum diproses oleh Excel. Kita dapat memerintah Excel untuk melakukan proses perhitungan dengan menekan :

F9, untuk menghitung formula di seluruh workbook yang terbuka.

Shift-F9, untuk menghitung formula di worksheet yang aktif saja.

Ctrl-Shift-F9, untuk memaksa Excel memproses semua perhitungan yang ada. Cara ini tidak disarankan, gunakan hanya jika Excel tidak melakukan proses perhitungan secara benar.

Ketika kita mengubahCalculation Mode, seluruh workbook yang dibuka akan terpengaruh.

Penyusunan Formula di dalam Excel

Formula di dalam Excel selalu dimulai dengan tanda = (sama dengan). Tanda = ini akan

memberi tahu bahwa karakter-karakter berikutnya adalah suatu rumus di mana terdiri dari

operand (elemen yang akan dihitung) yang dipisahkan oleh operator (jenis perhitungan). Excel akan melakukan perhitungan mulai dari kiri ke kanan, berdasarkan tingkatan masing-masing operator di dalam rumus. Kita bisa mengubah urutan perhitungan dengan menggunakan tanda

(7)

6

kurung. Pada contoh sebelumnya, tanda kurung akan memaksa Excel untuk menghitung B4+25 terlebih dahulu lalu kemudian membaginya dengan hasil penjumlahan nilai D5, E5, dan F5.

Operator perhitungan

Operator akan menandakan jenis perhitungan apa yang akan dilakukan terhadap operand. Excel

memiliki jenis 4 operator, yaitu : Arithmetic (operator untuk perhitungan biasa); Comparison

(operator untuk perbandingan);Text concatenation (operator untuk menggabungkan dua buah

teks); dan Reference(operator untuk mengkombinasikan alamat cell/range).

Arithmetic operators digunakan untuk melakukan perhitungan matematik seperti penjumlahan, pengurangan, perkalian, atau untuk menghasilkan hasil berupa nilai.

Operator Maksud Contoh + Penjumlahan 3+3 – Pengurangan Tanda Negatif 3–1 –1 * Perkalian 3*3 / Pembagian 3/3 % Persentase 20%

^ Pangkat 3^2 (sama dengan 3*3)

Comparison operators digunakan untuk memperbandingkan dua buah nilai yang hasil perbandingannya akan berupa nilai TRUE (Benar) atau FALSE (Salah).

Operator Maksud Contoh = sama dengan A1=B1

> lebih besar dari A1>B1

< lebih kecil dari A1<B1

>= lebih besar atau sama dengan dari A1>=B1

<= lebih kecil atau sama dengan dari A1<=B1

<> tidak sama dengan A1<>B1

Text concatenation operators digunakan untuk menggabungkan beberapa teks menjadi satu.

Operator Maksud Contoh

& Menggabungkan dua teks menjadi satu "North" & "wind" menghasilkan "Northwind"

Reference operators digunakan untuk menggabung alamat beberapa cell/range menjadi satu.

Operator Maksud Contoh

: Operator range, yang menandakan satu alamat untuk seluruh cell

yang terdapat di antara dua alamat cell, termasuk kedua cell tersebut

B5:B15

, Operator penggabungan banyak range menjadi satu range SUM(B5:B15,D5:D15)

Urutan proses perhitungan

Jika kita menggabungkan beberapa operator dalam satu formula, Excel akan melakukan perhitungan sesuai dengan urutan yang ada pada tabel di bawah. Jika dalam suatu formula terdapat beberapa operator yang memiliki tingkatan yang sama – sebagai contoh misalkan formula mengandung operator perkalian dan pembagian – Excel akan menghitung mulai dari kiri

(8)

7

sampai ke kanan. Untuk mengubah urutan perhitungan bagian tertentu, tutuplah bagian yang ingin dihitung terlebih dahulu dengan m enggunakan tanda kurung.

Operator Maksud Level

: (spasi) , operator reference (alamat cell/range)

↑ +           ↓ -– tanda negatif % persentase ^ pangkat

* dan / perkalian dan pembagian + dan – penjumlahan dan pengurangan & penggabungan teks

= < > <= >= <> perbandingan

Array Formula

Array Formula adalah fasilitas dari Excel yang memungkinkan kita untuk mengisi satu rumus langsung ke dalam satu atau lebih range di mana Excel akan menjaga rumus tersebut untuk

selalu sama. Array Formula memungkinkan kita untuk melakukan beberapa perhitungan dan

mengembalikan satu atau beberapa hasil. Kita bisa membuat array formula dengan mengisikan

formula biasa, tetapi kita harus menekan Ctrl+Shift+Enter ketika selesai memasukkan formula.

Menghitung formula untuk menghasilkan satu nilai. Terkadang Excel harus melakukan beberapa perhitungan untuk menghasilkan satu nilai. Sebagai contoh, worksheet di bawah ini menunjukkan satu perusahaan yang memiliki kantor penjualan di Eropa dan Amerika yang masing-masing kantor memiliki 3 divisi produk. Untuk mencari rata-rata pendapatan kantor Eropa pada tahun 1992, kita membutuhkan sebuah array formula.

B C D E 3 1992 1997 4 Elektronik 5 Eropa 100.600 161.000 6 Amerika 133.100 198.200 7 Total Elektronik 233.700 359.200 8 Akustik 9 Eropa 129.200 160.700 10 Amerika 150.500 190.100 11 Total Akustik 279.700 350.800 12 Perlengkapan Studio 13 Eropa 89.900 153.900 14 Amerika 112.300 190.700

15 Total Perlengkapan Studio 202.200 344.600

16 =AVERAGE(IF(C5:C14=“Eropa”,D5:D14))

Cell C16 berisi array formula =AVERAGE(IF(C5:C14=“Eropa”,D5:D14)), yang akan mencari

seluruh cell pada range C5:C14 yang berisi teks “Eropa” dan menghitung rata-rata pada D5:D14.

Menghitung formula untuk menghasilkan beberapa nilai. Untuk menghasilkan beberapa nilai dengan menggunakan array formula, kita harus memilih range di mana nilai-nilai akan ditaruh. Contoh berikut, kita akan menghitung 10 % total penjualan per bulan secara langsung.

(9)

8

3 Juni Juli Agustus

4 7234 1200 525

5 10 % =C4:E4*0.1

6

Beberapa keuntungan dari array formula adalah sebagai berikut : menggunakan memori yang lebih sedikit;

pekerjaan kita menjadi lebih efisien; dan

dapat menghilangkan kebutuhan akan formula perantara (intermediate formulas).

Sedangkan kerugian dari array formula adalah sebagai berikut : dapat memperlambat kinerja kalkulasi worksheet kita;

worksheet kita sulit dimengerti oleh orang lain; dan

harus mengingat untuk menekan Ctrl+Shift+Enter setelah selesai mengisi formula.

CATATAN …

Yang perlu diingat adalah sebelum kita menuliskan formula di atas, pastikan kita sudah memilih range C5:E5 terlebih dahulu. Dan setelah memasukkan formulanya, pastikan untuk menekan tombol Ctrl+Shift+Enter untuk mengakhirinya. Excel secara otomatis akan mengubah formulanya menjadi{=C4:E4*0.1} sebagai tanda bahwa formula yang bersangkutan adalaharray formula. Jika kita mencoba untuk mengubah formula pada salah satu cell, maka Excel akan menampilkan pesan kesalahan bahwa kita tidak bisa mengubah isi salah satu array (You cannot change part of an array ). Untuk mengubah formula tersebut, kita harus terlebih dahulu memilih range array formula yang bersangkutan, yaitu C5:E5, kemudian mengubahnya. Dan ingat, setelah mengubah formulanya, tekan Ctrl+Shift+Enter untuk mengakhirinya. Disinilah kelebihan suatu array formula, bahwa perubahannya tidak bisa per bagian, melainkan harus semua bagian. Jika kita menekan Enter ketika selesai mengetik formula pada cell/range, maka formulanya hanya akan diisi pada cell yang aktif saja.

Dan satu hal yang penting lagi adalah Excel secara otomatis akan menambahkan tanda kurung kurawal ({}) sebagai penanda bahwa formula yang ada adalah array formula.

Dan kita jangan sekali-kali menambahkan tanda kurung kurawal secara manual.

LATIHAN

Buatlah dua buah array 1 x 5 dan isikan sembarang nilai, lalu kalikan kedua buah array tersebut dengan menggunakan array formula !

Buatlah daftar sepuluh hewan di kolom A, lalu tampilkan jumlah huruf masing-masing hewan di kolom B dengan menggunakan array formula ! Setelah itu tampilkan jumlah huruf keseluruhan hewan dengan satu array formula tanpa perlu bantuan panjang huruf masing-masing hewan !

Cell/Range References

Sebagian besar formula yang kita buat terdiri dari alamat cell/range (cell/range references).

Pengalamatan ini memiliki 4 jenis, yaitu :

Relative. Pengalamatan ini sepenuhnya relatif. Ketika suatu formula dicopy , alamat cell di dalam formula akan berubah sesuai dengan alamat barunya. Contoh : A1.

Absolute. Pengalamatan ini sepenuhnya absolut. Ketika suatu formula dicopy, alamat cell di dalam formula tidak akan berubah. Contoh : $A$1.

Row Absolute. Pengalamatan ini absolut sebagian. Ketika suatu formula dicopy, alamat cell di dalam formula akan berubah bagian kolomnya saja. Contoh : A$1.

(10)

9

Column Absolute. Pengalamatan ini absolut sebagian. Ketika suatu formula dicopy, alamat cell di dalam formula akan berubah bagian barisnya saja. Contoh : $A1.

Secara default , alamat cell/range adalahrelative. Untuk mengubah menjadi yang lain, kita harus

secara manual menambahkan tanda dolar ($) ke dalam alamatnya.

Kenapa menggunakan pengalamatan yang tidak relative

Kalau kita pikir-pikir, satu-satunya alasan kita untuk mengubah jenis pengalamatan adalah

ketika kita berencana untuk mengcopy formula tersebut ke cell/range lain. Perhatikan contoh di

bawah ini dimana cell C4 berisi f ormula=C$3*$B4.

A B C D E F G 1 L e b a r 2 Panjang 3 12 14 16 18 4 3 36 42 48 54 5 4 48 56 64 72 6 5 60 70 80 90 7 6 72 84 96 108 8 7 84 98 112 126 9

Formula di atas digunakan untuk menghitung luas untuk lebar (terdapat pada kolom B) dan

panjang (terdapat pada baris 3). Setelah kita mengisi formula di C4, kita bise mengcopy hingga

C8 dan F8. Karena formula tersebut menggunakan alamat absolut untuk baris 3 dan kolom B dan

alamat relatif untuk kolom dan baris lain, maka setiap formula yang dicopy akan menghasilkan

alamat yang benar. Jika formula di C4 hanya menggunakan alamat relatif, maka ketika dicopy

alamat formula cell tujuannya akan berubah sesuai dengan alamat barunya – dan akan menghasilkan nilai yang salah.

Notasi R1C1

Selain pengalamatan normal dengan menggunakan notasi A1 (terdiri dari huruf kolom dan angka baris), kita juga bisa menggunakan notasi R1C1. Pada sistem ini, cell A1 sama dengan cell R1C1, dan A2 sama dengan R1C2, dan begitu seterusnya. Untuk mengubah setting menjadi notasi

R1C1, akses opsi Tools Options… General R1C1 reference style. Tabel di bawah ini

memberikan contoh formula dengan notasi standar dan notasi R1C1 dimana formula yang dimasukkan berada di cell B2 (atau cell R1C2).

Formula Sederhana Dengan Menggunakan Dua Notasi

Standar R1C1 =A1+1 =RC[-1]+1 =$A$1+1 =R1C1+1 =$A1+1 =RC1+1 =A$1+1 =R1C[-1]+1 =SUM(A1:A10) =SUM(RC[-1]:R[9]C[-1]) =SUM($A$1:$A$10) =SUM(R1C1:R10C1)

Angka di dalam kurung siku merupakan alamat relatif terhadap cell yang aktif. Misalnya, R[-5]C[-3] menandakan alamat cell 5 baris ke atas dan tiga kolom ke kiri. Sedangkan, R[5]C[3] menandakan alamat cell 5 baris ke bawah dan tiga kolom ke kanan.

(11)

10 CATATAN …

Jika kita merasa bahwa kita kesulitan menggunakan notasi R1C1, maka kita tidak sendirian. Notasi R1C1 tidak terlalu memusingkan untuk pengalamatan absolut, tetapi lain halnya jika kita berbicara mengenai pengalamatan relatif dimana kita harus menggunakan tanda kurung siku.

Biasanya notasi R1C1 digunakan untuk memeriksa hasil suatu formula ketika kita meng-copy -nya. Coba dan latihlah !

Pengalamatan Worksheet atau Workbook lain

Untuk mereferensikan alamat cell/range di worksheet lain, kita bisa menggunakan tanda seru (!)

sebagai pemisah antara worksheet dan cell/range, misalnya :

=Sheet1!A1+1

Kita juga bisa mereferensikan alamat cell/range yang ada di workbook lain dengan menggunakan

tanda kurung siku ([]) sebagai pemisah antara workbook dan worksheet, misalnya :

=[Budget.xls]Sheet1!A1+1

Jika terdapat spasi pada referensi nama workbooknya, maka kita harus menggunakan tanda

kutip tunggal (‘ ’) untuk mengapit nama workbook dan worksheetnya, misalnya :

=‘[Budget for 2001]Sheet1’!A1+A1

Tetapi jika workbook tersebut tidak dalam keadaan terbuka, maka kita harus menambahkan

pathlengkapnya, misalnya :

=‘C:\MSOffice\Excel\[Budget for 2001]Sheet1’!A1+A1

CATATAN …

Jika kita memiliki workbook yang rusak (corrupted ) dan tidak bisa membukanya, kita bisa membuat formula di workbook baru kita yang berisi referensi alamat ke workbook yang rusak tersebut dan mendapatkan semua nilai yang ada (tetapi bukan formulanya). Kita dapat melakukan ini karena file yang rusak tersebut tidak perlu dibuka. Kita hanya perlu, misalnya, membuat formula di cell A1 worksheet baru seperti=[Badfile.xls]Sheet1!A1lalu mengcopy nya ke cell lain sebanyak yang kita ingin. Jika data yang ada di workbook yang rusak sampai cell AA123, makacopy lah formula tersebut hingga cell AA123.

Nama Sebagai Referensi

Salah satu fasilitas Excel yang dinilai sangat berguna adalah kemampuan untuk mengganti

alamat cell/range (bisa juga komponen lain seperti chart ) dengan nama tertentu yang memiliki

arti bagi kita. Kita bisa memberi nama cell/range tertentu dengan mengakses menu Insert

Name Define (atau dengan menekan Ctrl+F3) lalu memasukkan namanya dan alamat cell/range yang ditunjuk oleh nama tersebut. Cara yang lebih cepat untuk memberi nama

cell/range adalah dengan mengubah di kotak isian Name box yang

terletak di samping kiri formula bar).

Kita juga bisa mengakses menu Insert Name Create untuk menciptakan nama secara

otomatis jika kita memiliki range yang memiliki judul baris atau kolom. Contohnya bisa dilihat pada worksheet dan gambar 1.1 di bawah ini.

A B C D E F

1 Qtr1 Qtr2 Qtr3 Qtr4

2 North 311 213 112 446 3 South 193 486 398 509

(12)

11

4 West 594 698 677 231

5 East 753 811 911 710

Gambar 1.1

Kotak dialog Create Names

Setelah itu kita akan mendapatkan bahwa range B2:B5 akan memiliki nama Qtr1, B2:E2 akan

memiliki namaNorth, dan seterusnya. Dan jika kita memasukkan formula ke dalam sebuah cell,

=Qtr2 South

Excel secara otomatis akan menghasilkan nilai 486. Fasilitas ini disediakan oleh Excel dengan

menggunakan operator spasi, yang diberi nama intersection operator . Kita juga bisa

mendapatkan total untuk West dengan menggunakan formula

=SUM(West)

CATATAN …

Sistem penamaan ini sangat berguna terutama saat nanti kita mempergunakan kode program VBA yang menggunakan alamat cell atau range. Kenapa ? Karena kode program VBA tidak secara otomatis memperbaharui alamat cell/range jika terjadi pergeseran atau pemindahan cell/range yang bersangkutan. Misalnya, jika kode VBA kita menggunakan alamat Range(“C4”), maka kode kita akan salah jika kita menyisipkan baris atau kolom baru di atas C4.

Dengan menggunakan nama alamat seperti Range(“InterestRate”), hal tersebut akan bisa kita hindari.

Excel 2000 sebenarnya sudah menambahkan fasilitas baru yang bisa mengubah formula secara otomatis jika kita menyisipkan baris atau kolom baru. Sebagai contoh, misalkan kita mempunyai formula di cell A5 sebagai berikut :

=SUM(A1:A4)

Lalu kita sisipkan baris baru di atas baris 5, maka formulanya akan secara otomatis menjadi :

=SUM(A1:A5)

Manfaat Lain dari Sistem Penamaan Cell/Range

Selain manfaat-manfaat yang dimiliki di atas, sistem penamaan yang disediakan oleh Excel juga

bisa digunakan sebagai fasilitas variabel dankonstanta.

Nama digunakan sebagai konstanta. Kita bisa membuat satu nama untuk menampilkan nilai tertentu. Bisa dilihat pada gambar 1.2, nama LST memiliki referensi ke nilai =“Laporan Stock

Barang Tahun 2001”. Dan jika pada salah satu cell, kita memasukkan formula =LST, maka cell

tersebut akan menampilkan judul secara penuh.

Nama digunakan sebagai variabel. Kita bisa membuat satu nama untuk menampilkan nilai berdasarkan perhitungan terhadap cell-cell tertentu. Bisa dilihat pada gambar 1.3, nama

(13)

12

PANGKAT memiliki perhitungan =A1^B1 (nilai cell A1 dipangkatkan dengan nilai cell B1). Dan

jika pada salah satu cell, kita memasukkan formula =PANGKAT, maka cell tersebut akan

menampilkan hasil formula pangkat tersebut.

Gambar 1.2

Nama digunakan sebagai konstanta. Menggunakan nilai secara eksplisit.

Gambar 1.3

Nama digunakan sebagai variabel. Menggunakan nilai dari cell/range tertentu atau variabel yang lain.

Formula Errors

Terkadang kita mendapatkan nilai kesalahan (error value) jika formula yang kita buat memiliki

kesalahan dalam perhitungan, parameter/argumen fungsi, atau referensi cell/range. Jika formula

yang kita buat memiliki referensi ke cell/range yang memiliki error value, maka formula tersebut

juga akan memilikierror value. Hal ini disebut gejala riak (ripple effect ) – di mana satu cell yang

memiliki error valuedapat menyebabkan banyak cell yang menggunakan alamat cell sebelumnya

mengalami error value juga. Di bawah ini akan dijelaskan beberapa error value yang mungkin

(14)

13 Error Value

dalam Excel

Error Value Penjelasan

##### Error ini muncul jika cell yang bersangkutan berisi nilai yang lebarnya

melebihi lebar cell atau berisi formula tanggal/waktu yang menghasilkan bilangan negatif. Untuk mengatasi ini, kita bisa :

menambah lebar kolom;

mengubahnumber format menjadi yang lain; atau

mengubah formula tanggal/waktu agar benar.

#DIV/0! Terdapat pembagian dengan nol (suatu operasi yang tidak diperbolehkan

di planet ini) dalam formula yang bersangkutan. Misalnya =A1/0. Error ini

juga muncul jika formula mencoba membagi dengan cell yang kosong. Untuk mengatasi masalah ini kita bisa :

jika nilai 0 digunakan secara eksplisit sebagai bilangan pembagi, ubahlah

menjadi nilai selain 0, misalnya dari formula =A1/0 menjadi=A1/2; atau

jika formula yang ada mencoba membagi dengan cell yang kosong atau berisi nilai 0 (misalnya B5), ubahlah formula yang bersangkutan misalnya menjadi seperti=IF(B5=0,"",A5/B5).

#N/A Formula yang bersangkutan memiliki referensi (secara langsung maupun

tidak langsung) ke cell yang memiliki nilai N/A. N/A ini menandakan bahwa

data tidak tersedia (not available). Ini biasanya terjadi ketika kita

menggunakan fungsi LOOKUP dan terdapat kondisi : cell berisi nilai yang tidak terdapat di tabel LOOKUP; atau tabel LOOKUP yang ada tidak dalam keadaan terurut.

#NAME? Formula menggunakan nama atau pengenal (identifier ) yang tidak dikenal

oleh Excel. Ini dapat terjadi jika kita :

menghapus sebuah nama yang masih digunakan dalam formula; salah mengetik nama atau fungsi yang ada;

lupa menggunakan tanda titik dua (:) pada alamat range; atau teks yang ada di dalam f ormula tidak diapit oleh kutip.

#NULL! Formula yang bersangkutan menggunakan intersection operator (operator

spasi – baca “Nama sebagai referensi” untuk keterangan lebih lanjut) terhadap dua range tapi tidak saling bersinggungan satu sama lain. Ini juga mungkin terjadi jika kita lupa menambahkan tanda koma (,) sebagai

pemisah range. Untuk mengatasi masalah ini kita bisa :

memeriksa ulang dua range yang menggunakan intersection operator ,

misalnya terdapat formula =A1:A5 B1:E1 dimana tidak terdapat cell yang

merupakan persinggungan. Kita bisa mengubahnya menjadi =A1:A5

A1:E1; atau

memastikan bahwa tanda koma digunakan sebagai pemisah dua range, misalnya kita ingin mentotal nilai yang ada di range A1:A5 dan range

C1:C5 maka formula yang benar harus berbentuk =SUM(A1:A5,C1:C5) dan

bukannya =SUM(A1:A5 C1:C5).

#NUM! Error ini muncul jika terjadi kesalahan menggunakan bilangan dalam

formula atau fungsi yang ada. Ini biasanya muncul jika :

mengirimkan parameter/argumen yang jenisnya bukan bilangan untuk parameter/argumen yang membutuhkan bilangan; atau

membuat formula yang menghasilkan bilangan yang terlalu besar atau terlalu kecil yang Excel bisa tampilkan. Untuk mengatasi masalah ini

pastikan bahwa hasil dari formula yang ada harus berkisar antara –1*10307

sampai 1*10307_.

#REF! Formula yang ada menggunakan referensi alamat cell/range yang tidak

benar. Ini biasanya terjadi jika kita menghapus cell/range yang masih digunakan oleh formula lain.

#VALUE! Error ini akan muncul jika kita mengirimkan parameter atauoperand yang

jenisnya berbeda dengan yang dibutuhkan. Ini biasanya terjadi jika : kita memasukkan data teks sedangkan yang dibutuhkan adalah data bilangan atau data logika. Misalnya : cell A5 berisi 15 dan cell A6 berisi “Contoh”, kemudian kita mencoba membuat formula di cell A7 menjadi

=A5+A6; atau

kita mengirimkan alamat range sedangkan yang dibutuhkan adalah data tunggal atau satu alamat cell.

CATATAN …

(15)

14 Function (fungsi)

Function (fungsi) adalah sederetan atau sekumpulan formula yang sudah disediakan oleh Excel

untuk melakukan operasi tertentu dengan menggunakan nilai yang disebut argument . Sebagai

contoh, fungsi SUM akan menjumlahkan nilai seluruh cell pada range. Argument (argumen)

dapat berupa bilangan, teks, nilai logika (TRUE atau FALSE), array, atau alamat cell/range. Argumen yang kita berikan jenisnya harus sesuai dengan ketentuan yang terdapat pada fungsi

yang bersangkutan. Argumen dapat berupa nilai konstan, formula, atau fungsi lain.

Tanda sama dengan Nama fungsi

Argumen

=SUM(A10,B5:B10,50,37)

Tanda koma yang memisahkan masing-masing argumen Tanda kurung

Fungsi di dalam Fungsi

Terkadang kita dihadapkan pada kondisi di mana kita harus memasukkan satu fungsi sebagai argumen fungsi yang lain. Dan ini menyebabkan kita harus menulis fungsi di dalam fungsi – yang

biasanya disebut nested function (fungsi bersarang/bertingkat). Sebagai contoh, formula di

bawah menggunakan fungsi AVERAGE dan membandingkan hasilnya dengan 50.

nested function

=IF(AVERAGE(F2:F5)>50,SUM(G2:G5),0)

Sebuah nested function harus menghasilkan jenis nilai yang sama dengan yang dibutuhkan oleh

argumen tersebut. Misalnya, suatu argumen membutuhkan nilai logika (TRUE atau FALSE), maka

nested function tersebut harus menghasilkan nilai logika. Jika tidak, maka Excel akan menghasilkan kesalahan #VALUE! error.

CATATAN …

Sebuah formula dapat terdiri hinggatujuh tingkat nested function. Contohnya bisa dilihat di atas, fungsi AVERAGE dan SUM keduanya adalah fungsi level kedua karena mereka merupakan argumen bagi fungsi IF.

Jika terdapat fungsi lain di dalam fungsi AVERAGE, maka fungsi tersebut merupakan fungsi level ketiga.

Kita juga bisa menggunakan Formula Palette (fasilitas yang disediakan Excel untuk menyunting

formula. Fasilitas ini berada di bawah formula bar ketika kita klik tombol Edit Formula atau

klik tombol Paste Function ) dalam membuat nested function ini. Sebagai contoh bisa

(16)

15

Gambar 1.4

Contoh penggunaan Formula Palette

Untuk berpindah antar fungsi di dalam Formula Palette, klik nama fungsi dalam formula bar .

Sebagai contoh, untuk mengubah range argumen fungsi AVERAGE di gambar 1.4, klik AVERAGE

di formula bar.

Lebih lanjut dengan fungsi IF

Seperti kita ketahui bahwa fungsi IF digunakan untuk menseleksi kondisi dan menghasilkan nilai

tertentu berdasarkan nilai TRUE (benar) atau FALSE (salah). Syntax (bentuk umum) dari fungsi

IF adalah sebagai berikut :

=IF(kondisi ,nilai_jika_benar ,nilai_jika_salah)

Kondisi adalah satu atau lebih persamaan yang menghasilkan nilai TRUE atau FALSE. Contohnya, A10 = 100 adalah kondisi; jika cell A10 berisi nilai 100 maka kondisi akan menghasilkan nilai TRUE; sebaliknya menghasilkan nilai FALSE. Kondisi disini bisa menggunakan

operator perbandingan (comparison operators) yang sudah dibicarakan pada bagian “Operator

Perhitungan” sebelumnya.

Nilai_jika_benar adalah nilai yang akan dihasilkan jika kondisinya menghasilkan nilai TRUE. Contohnya, jika parameter/argumen ini diisi dengan teks “Within Budget” dan kondisi menghasilkan nilai TRUE, maka fungsi IF ini akan menghasilkan teks “Within Budget”.

Nilai_jika_benar juga bisa diisi dengan fungsi lain.

Nilai_jika_salah adalah nilai yang akan dihasilkan jika kondisinya menghasilkan nilai FALSE. Contohnya, jika parameter/argumen ini diisi dengan teks “Over Budget” dan kondisi menghasilkan nilai FALSE, maka fungsi IF ini akan menghasilkan teks “Over Budget”.

(17)

16 CATATAN …

Sebenarnya Excel menyediakan fungsi tambahan yang bisa digunakan untuk menghitung data berdasarkan kondisi tertentu. Misalnya, untuk menghitung total nilai berdasarkan teks tertentu, kita bisa menggunakan fungsi SUMIF. Fungsi SUMIF (dan juga COUNTIF) ini akan dibahas pada bagian selanjutnya.

Kita dapat menyusun fungsi IF lagi di dalam nilai_jika_benar atau nilai_jika_salah (nested function) hingga 7 level.

LATIHAN

Jika cell A10 berisi dosis obat dalam cc, buatlah fungsi IF di cell lain untuk menseleksi apakah dosis tersebut “Dosis Normal” (dosis <= 1000 cc) atau “Over Dosis” (dosis > 1000 cc) ! Buatlah fungsi IF untuk menseleksi nilai cell A1. Jika A1 berisi 100, maka tampilkan total nilai yang ada di B5:B10, sebaliknya jangan tampilkan apa-apa !

Misalkan kita mempunyai beberapa orang sales untuk menjual produk kita yang seminggu sekali melaporkan hasil penjualannya. Kita sebagai pemilik perusahaan telah menentukan pembagian bonus terhadap hasil penjualan mereka per minggu. Jika lebih besar dari $ 1,000, maka bonusnya adalah 10 % dari penjualan tersebut, sebaliknya bonusnya adalah 7.5 % dari penjualan tersebut. Untuk menghitung secara otomatis, kita harus membuat tabel hasil penjualan satu bulan untuk (andaikan saja) 3 orang sales dengan kolom-kolom : Name Sales, Penjualan, dan Bonus yang diterima (dalam $). Buatlah tabel tersebut dengan fungsi IF-nya ! Buatlah fungsi IF untuk menyatakan nilai huruf berdasarkan nilai bilangan tertentu dengan ketentuan sebagai berikut :

Jika Nilai Tampilkan Huruf

> 89 A

80 –89 B

70 –79 C

60 –69 D

< 60 E

Kita bisa menggunakan nested function IF untuk menyelesaikan masalah ini.

Pada fungsi IF sebelumnya kita hanya menggunakan kondisi tunggal sebagai seleksinya, misalkan A10=100. Padahal sering kali dalam kehidupan nyata, kita dihadapkan pada lebih dari satu kondisi, misalkan : “Saya boleh masuk bioskop jika umur saya lebih dari 17 tahun dan mempunyai karcis” atau “saya mau minum jika ada air jeruk atau ada air teh”.

Bisa dilihat pada paragraf sebelumnya, kalimat “jika ada air jeruk atau ada air teh” terdiri dari dua kondisi yaitu “ada air jeruk” dan “ada air teh” dengan penghubung kondisi “atau”. Pada

dasarnya untuk menghubungkan dua buah kondisi digunakan operator AND (Dan) dan OR

(Atau) dengansatu tambahan operator NOT (Bukan). Berikut adalah tabel logika operator

tersebut yang dikenal dengan nama logika boolean.

Kondisi A Kondisi B A AND B A OR B NOT A

FALSE FALSE FALSE FALSE TRUE

FALSE TRUE FALSE TRUE TRUE

TRUE FALSE FALSE TRUE FALSE

TRUE TRUE TRUE TRUE FALSE

Operator AND hanya akan menghasilkan nilai TRUE jika keseluruh logika adalah TRUE,

sedangkan operator OR hanya akan menghasilkan nilai FALSE jika keseluruh logika adalah

(18)

17

Excel sudah menyediakan fungsi tersendiri sebagai pengganti operator logika Boolean ini, yaitu :

Fungsi AND(kondisi1, kondisi2, …) yang bisa berisi hingga 30 kondisi. Parameter yang diberikan

harus menghasilkan nilai TRUE atau FALSE atau bisa juga berupa array atau alamat range yang berisi nilai TRUE atau FALSE.

Fungsi OR(kondisi1, kondisi2, …) yang bisa berisi hingga 30 kondisi. Parameter yang diberikan

harus menghasilkan nilai TRUE atau FALSE atau bisa juga berupa array atau alamat range yang berisi nilai TRUE atau FALSE.

FungsiNOT(kondisi). Parameter yang diberikan harus menghasilkan TRUE atau FALSE.

Untuk lebih jelasnya, perhatikan contoh berikut :

AND(TRUE,TRUE) menghasilkanTRUE AND(2+2=4,2+3=6) menghasilkanFALSE

Jika A1:A3 berisi nilai TRUE,FALSE, dan TRUE, maka OR(A1:A3) menghasilkanTRUE

NOT(1+1=2) menghasilkanFALSE

Excel sebenarnya juga menyediakan operator logika Boolean (walaupun tidak terdokumentasi di

dalamExcel Reference Guide) seperti berikut :

+ menandakan operator OR. Kita bisa memisahkan kondisi dengan tanda + ini.

*menandakan operator AND. Kita bisa memisahkan kondisi dengan tanda * ini.

Untuk lebih jelasnya, bandingkanlah kedua contoh yang memiliki tujuan yang sama, berikut ini : =IF(AND(A1<10,B1<10), “OK”, “NG”)

=IF((A1<10)*(B1<10), “OK”, “NG”)

CATATAN …

Jika menggunakan operator logika Boolean * dan +, pastikan untuk menutup masing-masing logika dengan tanda kurung.

LATIHAN

Jika cell A1 bisa diisikan sembarang nilai, buatlah fungsi di B1 untuk memeriksa apakah isi cell A1 berada “dalam batas” (100 <= nilai <= 1000) atau “luar batas” !

Jika kita memiliki tabel sebagai berikut :

A B C D

1 Nama Barang Harga Jenis Discount

2 Celana CutBrai 75000 A

3 Lem Tikus 20000 C

Kolom Jenis bisa berisi A, B, C, D, dan E. Isilah kolom Discount dengan ketentuan

sebagai berikut :

Jika Jenis A atau B dan harga < 50000, maka Discount = 5% * Harga Jika Jenis A atau B dan harga >= 50000, maka Discount = 10% * Harga Jika Jenis C, D, atau E dan harga < 50000, maka Discount = 10% * Harga Jika Jenis C, D, atau E dan harga >= 50000, maka Discount = 15% * Harga

(19)

18

Formula di dalam Excel, biasanya, akan menghitung seluruh nilai yang t erdapat pada range yang diberikan. Tetapi, terkadang kita menginginkan Excel untuk menghitung nilai yang memenuhi kondisi tertentu saja.

Sebagai contoh, misalkan kita memiliki dua rumah kontrakan, yang setiap bulannya memiliki pengeluaran yang berbeda-beda. Sebagai pemilik rumah kontrakan yang baik kita harus menghitung total pengeluaran listrik dan air per tahun untuk masing-masing rumah agar kita bisa menentukan tarif kontrak rumah untuk bulan depan. Seandainya kita menyusun dalam bentuk tabel, mungkin bentuknya akan sebagai berikut :

A B

1 Rumah Kontrakan Pengeluaran

2 Jl. H. Eman 150000 3 Gg. Pemuda 195000 4 Jl. H. Eman 125000 5 Jl. H. Eman 75000 6 Gg. Pemuda 100000 7 Gg. Pemuda 175000

Kita bisa membuat total pengeluaran untuk rumah “Jl. H. Eman” dengan menggunakan fungsi

SUMIF (yang memilikisyntax =SUMIF(range,criteria,sum_range) ) sebagai berikut :

=SUMIF(A2:A7,“Jl. H. Eman”,B2:B7)

Atau menghitung berapa kali pengeluaran untuk rumah “Gg. Pemuda” dengan menggunakan

fungsi COUNTIF (yang memiliki syntax =COUNTIF(range,criteria)) sebagai berikut :

=COUNTIF(A2:A7,“Gg. Pemuda”)

Sehingga kita bisa menyusunnya menjadi seperti bentuk tabel sebagai berikut :

A B C

11 Rumah Kontrakan Total Pengeluaran Berapa Kali Pengeluaran

12 Jl. H . Eman =SUMIF(A2:A7,A12,B2:B7) =COUNTIF(A2:A7,A12)

13 Gg. Pemuda =SUMIF(A2:A7,A13,B2:B7) =COUNTIF(A2:A7,A13)

CATATAN …

Criteria bisa diisi dengan sembarang teks atau bilangan, misalnya 32, “32”, “>32”, atau “apel”.

Berikut adalah contoh lain dari penggunaan fungsi COUNTIF dan SUMIF beserta penjelasannya. Yang perlu diingat adalah formula di bawah mengasumsikan bahwa kita sudah memiliki range

dengan nama datadan range yang akan dijumlah dengan nama jumlah.

Contoh Penggunaan COUNTIF dan SUMIF

Formula Nilai yang dikembalikan

=COUNTIF(data,12) Jumlah cell yang berisi nilai 12

=SUMIF(data,“<>0”,jumlah) Total nilai yang tidak sama dengan nol.

=COUNTIF(data,“yes”) Jumlah cell yang berisi teks yes(tidakcase-sensitive)

=SUMIF(data,“*”,jumlah) Total nilai yang berisi teks apa saja

=COUNTIF(data,“???”) Jumlah cell yang berisi teks berjumlah 3 huruf

LATIHAN

(20)

19

Buatlah fungsi untuk menghitung total nilai yang isi cellnya lebih besar dari 0 ! Buatlah fungsi untuk menghitung jumlah cell yang berisi nilai antara 1 sampai 10 ! Buatlah fungsi untuk menghitung total nilai yang isi cellnya terdapat satu huruf “s” !

Menggunakan Array Formula untuk COUNT dan SUM

Jika metode perhitungan standard tidak bisa diterapkan pada suatu kasus tertentu, kita bisa menggunakan metode Array Formula. Dan jangan lupa untuk menekan Ctrl+Shift+Enter setelah selesai memasukkan formulanya. Berikut akan diberikan contoh-contoh penggunaan Array Formula pada kasus-kasus yang unik.

Untuk menghitung jumlah cell yang berisi bilangan (tidak akan menghitung teks dan cell kosong), gunakan formula berikut :

=SUM(IF(ISNUMBER(A2:A10),1,0))

Untuk menghitung jumlah cell yang memiliki error value, gunakan formula berikut :

=SUM(IF(ISERR(A2:A10),1,0))

Contoh lain jika kita memiliki tabel sebagai berikut :

A B C

1 Bulan Daerah Penjualan

2 Jan Utara 100 3 Jan Selatan 200 4 Jan Barat 300 5 Feb Utara 150 6 Feb Selatan 250 7 Feb Barat 350 8 Mar Utara 200 9 Mar Selatan 300 10 Mar Barat 400 11

maka kita bisa membuat formula-formula berikut :

Array Formula menggunakan fungsi SUM

Formula Nilai yang dikembalikan

=SUM((A2:A10=“Jan”)*(B2:B10=“Utara”)*C2:C10) Total penjualan untuk bulan “Jan” di

daerah “Utara” =SUM((A2:A10=“Jan”)*(B2:B10<>“Utara”)*C2:C10

)

Total penjualan untuk bulan “Jan” di daerah selain “Utara”

=SUM((A2:A10=“Jan”)*(B2:B10=“Utara”)) Jumlah penjualan untuk bulan “Jan”

di daerah “Utara”

=SUM((A2:A10=“Jan”)*(C2:C10>=200)*(C2:C10)) Total penjualan bulan “Jan” untuk

yang penjualannya >= 200

=SUM((C2:C10>=300)*(C2:C10<=400)*(C2:C10)) Total penjualan untuk yang

penjualannya antara 300 dan 400

=SUM((C2:C10>=300)*(C2:C10<=400)) Jumlah penjualan untuk yang

penjualannya antara 300 dan 400

Beberapa Fungsi Matematika dalam Excel

(21)

20

ABS

Penjelasan : Nilai absolut dari suatu bilangan.

Syntax :ABS(bilangan)

Contoh : =ABS(2)menghasilkan 2 =ABS(-2) menghasilkan 2

Jika A1 berisi nilai –16, maka =SQRT(ABS(A1)) menghasilkan 4

CEILING

Penjelasan : Pembulatan ke atas suatu nilai berdasarkan besaran tertentu, menjauhi 0.

Syntax :CEILING(bilangan,besaran_pembulatan) Contoh : =CEILING(2.5,1) menghasilkan 3

=CEILING(-2.5,-2) menghasilkan –4

=CEILING(-2.5,2) menghasilkan #NUM! =CEILING(0.234,0.01) menghasilkan 0.24

EVEN

Penjelasan : Pembulatan ke atas suatu nilai menjadi bilangan genap terdekat, menjauhi 0.

Syntax :EVEN(bilangan)

Contoh : =EVEN(1.5) menghasilkan 2 =EVEN(-1)menghasilkan –2 =EVEN(3) menghasilkan 4 =EVEN(2) menghasilkan 2

FLOOR

Penjelasan : Pembulatan ke bawah suatu nilai berdasarkan besaran tertentu, mendekati 0.

Syntax :FLOOR(bilangan,besaran_pembulatan) Contoh : =FLOOR(2.5,1) menghasilkan 2

=FLOOR(-2.5,-2) menghasilkan –2 =FLOOR(-2.5,2) menghasilkan #NUM! =FLOOR(0.234,0.01) menghasilkan 0.23

INT

Penjelasan : Pembulatan ke bawah suatu nilai menjadi bilangan bulat terdekat.

Syntax :INT(bilangan)

Contoh : =INT(8.9)menghasilkan 8 =INT(-8.9) menghasilkan –9

Formula ini bisa digunakan untuk mendapatkan nilai desimal suatu bilangan positif yang terdaat di cell A1 :

=A1-INT(A1)

MOD

Penjelasan : Hasil sisa bagi suatu pembagian. Fungsi MOD ini juga bisa diekspresikan dengan

menggunakan fungsi INT : MOD(n,d) = n – d*INT(n/d).

Syntax :MOD(bilangan,pembagi) Contoh : =MOD(11,3) menghasilkan 2

(22)

21

=MOD(3,-2) menghasilkan –1 =MOD(-6,-4) menghasilkan –2

ODD

Penjelasan : Pembulatan ke atas suatu nilai menjadi bilangan ganjil terdekat, menjauhi 0.

Syntax :ODD(bilangan)

Contoh : =ODD(1.5)menghasilkan 3 =ODD(3)menghasilkan 3 =ODD(-1) menghasilkan –1 =ODD(-2) menghasilkan –3

POWER

Penjelasan : Pemangkatan suatu bilangan. Operator “^” sebenarnya bisa digunakan untuk menggantikan fungsi ini, seperti 5^2.

Syntax :POWER(bilangan,pangkat) Contoh : =POWER(5,2) menghasilkan 25

=POWER(98.6,3.2) menghasilkan 2401077 =POWER(4,5/4) menghasilkan 5.656854

PRODUCT

Penjelasan : Mengalikan semua bilangan yang diberikan.

Syntax :PRODUCT(bilangan1,bilangan2,…)

Contoh : Jika cell A2:C2 berisi nilai 5, 15, dan 30, maka :

=PRODUCT(A2:C2) menghasilkan 2250

=PRODUCT(A2:C2,2) menghasilkan 4500

ROUND

Penjelasan : Membulatkan suatu bilangan pada posisi digit yang kita tentukan.

Syntax :ROUND(bilangan,posisi_digit)

Jika posisi_digit > 0, bilangan akan dibulatkan pada bagian desimalnya. Jika posisi_digit = 0, bilangan akan dibulatkan pada bilangan bulat terdekat. Jika posisi_digit < 0, bilangan akan dibulatkan pada bagian kiri titik desimal.

Contoh : =ROUND(2.15,1) menghasilkan 2.2

=ROUND(2.149,1) menghasilkan 2.1 =ROUND(-1.475,2) menghasilkan –1.48 =ROUND(21.5,-1) menghasilkan 20 =ROUND(-5.93,0) menghasilkan –6 =ROUND(1575,-2) menghasilkan 1600 ROUNDDOWN

Penjelasan : Membulatkan suatu bilangan ke bawah pada posisi digit yang kita tentukan.

Syntax :ROUNDDOWN(bilangan,posisi_digit)

Contoh : =ROUNDDOWN(2.15,1) menghasilkan 2.1 =ROUNDDOWN(2.149,1) menghasilkan 2.1

(23)

22

=ROUNDDOWN(-1.475,2) menghasilkan –1.47 =ROUNDDOWN(21.5,-1) menghasilkan 20 =ROUNDDOWN(-5.93,0) menghasilkan –5 =ROUNDDOWN(1575,-2) menghasilkan 1500 ROUNDUP

Penjelasan : Membulatkan suatu bilangan ke atas pada posisi digit yang kita tentukan.

Syntax :ROUNDUP(bilangan,posisi_digit)

Contoh : =ROUNDUP(2.15,1) menghasilkan 2.2 =ROUNDUP(2.149,1) menghasilkan 2.2 =ROUNDUP(-1.475,2) menghasilkan –1.48 =ROUNDUP(21.5,-1) menghasilkan 30 =ROUNDUP(-5.93,0) menghasilkan –6 =ROUNDUP(1575,-2) menghasilkan 1600 SQRT

Penjelasan : Mendapatkan akar pangkat dari nilai tertentu.

Syntax :SQRT(bilangan)

Contoh : =SQRT(16)menghasilkan 4

=SQRT(-16) menghasilkan #NUM! =SQRT(ABS(-16)) menghasilkan 4

SUBTOTAL

Penjelasan : Menghitung nilai subtotal. Sebenarnya untuk menghitung SUBTOTAL, lebih mudah

melalui menuData Subtotals…. Perbedaan fungsi SUBTOTAL dari fungsi yang

lain (seperti SUM) adalah fungsi ini tidak akan menghitung cell yang berisi fungsi SUBTOTAL lain, sehingga tidak akan terjadi perhitungan dua kali.

Syntax :SUBTOTAL(no_fungsi,ref1,ref2,…)

ref1, ref2, adalah alamat range (bisa sampai 29 range) di mana SUBTOTAL akan dihitung.

no_fungsi adalah bilangan antara 1 sampai 11 yang masing-masing memiliki jenis perhitungan sebagai berikut :

no_fungsi jenis perhitungan

1 AVERAGE 2 COUNT 3 COUNTA 4 MAX 5 MIN 6 PRODUCT 7 STDDEV 8 STDDEVP 9 SUM 10 VAR 11 VARP

(24)

23

dengan menggunakan jenis perhitungan SUM

SUM

Penjelasan : Menjumlahkan seluruh bilangan yang ada di parameter/argumen.

Syntax :SUM(bilangan1, bilangan2,…) Contoh : =SUM(3,2) menghasilkan 5

=SUM(“3”,2,TRUE) menghasilkan 6, karena teks akan diterjemahkan terlebih

dahulu menjadi bilangan dan TRUE diterjemahkan menjadi angka 1

Jika A1:B1 berisi “3”, TRUE, maka =SUM(A1,B1,2) menghasilkan 2, karena nilai

yang terdapat di dalam cell t idak akan diterjemahkan menjadi bilangan Jika cell A2:E2 berisi 5, 15, 30, 40, dan 50, maka :

=SUM(A2:C2) menghasilkan 50 =SUM(B2:E2, 15) menghasilkan 150

SUMIF

Baca bagian “Menggunakan fungsi COUNTIF dan SUMIF” untuk keterangan lebih lanjut.

TRUNC

Penjelasan : Menghilangkan bagian desimal suatu bilangan. TRUNC dan INT kurang lebih sama, jika TRUNC menghilangkan bagian desimal bilangan, maka INT membulatkan beilangan ke bawah. TRUNC dan INT berbeda ketika menggunakan bilangan

negatif: =TRUNC(-4.3) akan menghasilkan –4, tetapi =INT(-4.3) akan

menghasilkan bilangan –5.

Syntax :TRUNC(bilangan,jumlah_desimal) Contoh : =TRUNC(8.963824) menghasilkan 8

=TRUNC(8.963824,3) menghasilkan 8.963 =TRUNC(-8.9,2) menghasilkan –8.9

Beberapa Fungsi Statistik dalam Excel

Berikut adalah fungsi statistik dalam Excel yang sering digunakan dalam pekerjaan :

AVERAGE

Penjelasan : Mendapatkan nilai rata-rata untuk seluruh nilai yang diberikan.

Syntax :AVERAGE(bilangan1, bilangan2,…)

Contoh : Jika range A1:A5 diberi namaNilai dan berisi nilai 10, 7, 9, 27, dan 2, maka :

=AVERAGE(A1:A5) menghasilkan 11 =AVERAGE(Nilai) menghasilkan 11 =AVERAGE(A1:A5, 5) menghasilkan 10

=AVERAGE(A1:A5) sebenarnya sama dengan formula =SUM(A1:A5)/COUNT(A1:A5)

COUNT

Penjelasan : Menghitung jumlah cell atau parameter/argumen yang berisi bilangan. Parameter/ argumen yang berupa bilangan, tanggal, maupun teks yang merupakan bilangan akan dihitung; selain itu tidak akan dihitung.

(25)

24

Syntax :COUNT(nilai1, nilai2,…)

Contoh : Jika A1:A7 berisi “Sales”, 12/8/90, , 19, 22.24, TRUE, dan #DIV/0! maka :

=COUNT(A1:A7) menghasilkan 3 =COUNT(A4:A7) menghasilkan 2 =COUNT(A1:A7, 2) menghasilkan 4

COUNTA

Penjelasan : Menghitung jumlah cell atau parameter/argumen yang tidak kosong.

Syntax :COUNTA(nilai1, nilai2,…)

=COUNTA(A1:A7) menghasilkan 6 =COUNTA(A4:A7) menghasilkan 4

=COUNTA(A1:A7,“Data”) menghasilkan 7

COUNTBLANK

Penjelasan : Menghitung jumlah cell atau parameter/argumen yang kosong.

Syntax :COUNTBLANK(range)

=COUNTA(A1:A7) menghasilkan 1

COUNTIF

Baca bagian “Menggunakan fungsi COUNTIF dan SUMIF” untuk keterangan lebih lanjut.

FORECAST

Penjelasan : Menghitung, atau memperkirakan, nilai yang belum diketahui berdasarkan

nilai-nilai yang sudah diketahui. Nilai yang sudah diketahui terdiri dari nilai-x dan

nilai-y (sama seperti chart ) sedangkan fungsi akan menghasilkan nilai-y yang

belum diketahui untuknilai-x yang dimasukkan.

Syntax :FORECAST(x_dicari, y_diketahui, x_diketahui)

Contoh : Misalnya kita sebagai orang yang bertanggung jawab dalam menyusun jadwal

produksi suatu perusahaan tempe MENDOWANTM_{. Perusahaan kita memiliki}

mesin tempe otomatis SETENGAHMATENG®

_{sebanyak 5 buah. Kita}

kemudian mengumpulkan data jumlah tempe yang dihasilkan

per jam (dalam ribuan) untuk jumlah mesin yang berbeda

sebagai berikut :

A B C

1 Jumlah Mesin Jumlah Tempe per Hari (x 1000)

2 1 15 3 2 27 4 3 41 5 4 60 6 5 95 7

Karena negara sedang mengadakan TEMPEISASI, maka perusahaan menerima pesanan sebanyak 200 ribu tempe / hari. Untuk memenuhi pesanan tersebut, perusahaan akan menambahkan jumlah mesin yang ada menjadi 10 buah. Tetapi kita tidak yakin apa 10 mesin dapat menghasilkan 200 ribu tempe, karena itu kita dapat mencoba untuk memperkirakannya dengan memasukkan formula di dalam Excel pada cell C4 sebagai berikut :

(26)

25

=FORECAST(10,B2:B6,A2:A6)

Apakah hasilnya benar ? Berapakah jumlah mesin minimal untuk menghasilkan minimal 200 ribu tempe / hari ?

FREQUENCY

Penjelasan : Menghitung kemunculan data dengan batasan tertentu pada daftar data yang kita berikan dan mengembalikan kumpulan nilai berupa array vertikal. Karena fungsi FREQUENCY ini menghasilkan nilai berupa array, maka dalam memasukkan formula ini harus menekan Ctrl+Shift+Enter.

Syntax :FREQUENCY(array_sumber_data,array_batasan_data)

Contoh : Misalnya kita sebagai dosen mempunyai data nilai mahasiswa yang kita

masukkan di range A1:A10 (array_sumber_data) sebagai berikut 79, 54, 87,

49, 66, 91, 72, 83, 95, dan 65. Kemudian kita ingin mengelompokan nilai-nilai tersebut berdasarkan batasan 0-65, 66-75, 76-85, dan 86-100. Maka kita

masukkan di range B1:B3 (array_batasan_data) sebagai berikut 65, 75, dan 85

(yang terakhir tidak perlu dimasukkan). Lalu kita harus membuat array formula

di range C1:C4 dan memasukkan formula =FREQUENCY(A1:A10,B1:B3)

.

Hasilnya kurang lebih akan seperti berikut ini :

A B C 1 79 65 3 2 54 75 2 3 87 85 2 4 49 3 5 66 6 91 7 72 8 83 9 95 10 65 11

Walaupun penggunaan fungsi FREQUENCY ini lumayan sulit, fungsinya akan sangat terasa membantu sekali saat kita berhubungan dengan banyak data.

GROWTH

Penjelasan : Fungsi ini sangat menarik untuk digunakan. Ia dapat menghitung “nilai pertumbuhan” dengan menggunakan data yang sudah diketahui. Fungsi ini akan menghasilkan deretan nilai-y untuk deretan nilai-x yang kita masukkan dengan bantuan nilai-x dan nilai-y yang sudah diketahui. Karena fungsi GROWTH ini menghasilkan nilai berupa array, maka dalam memasukkan formula ini harus menekan Ctrl+Shift+Enter.

Syntax :GROWTH(y_diketahui, x_diketahui, x_dicari)

Contoh : Kita bicara mengenai perusahaan tempe MENDOWANTM

kembali. Setelah

sukses dalam tahun-tahun terakhir, terkadang perusahaan

masih tetap kewalahan dalam menangani pesanan yang terus

meningkat per bulannya. Untuk itulah, kita—yang sekarang

sudah diangkat menjadi Marketing Director—dituntut untuk bisa

membaca peluang pasar. Kita kemudian menyusun data

penjualan tempe 6 bulan ke belakang (bulan 11 – 16) sebagai

(27)

26 berikut :

A B C

1 Bulan Jumlah Tempe Terjual (x 1000)

2 11 331 3 12 473 4 13 690 5 14 1020 6 15 1500 7 16 2200 8 17 9 18

Lalu untuk mengetahui peluang pasar 2 bulan berikutnya (bulan 17 – 18), kita bisa memasukkan array formula di B8:B9 seperti berikut :

=GROWTH(B2:B7,A2:A7,A8:A9)

Dan kita mendapatkan bahwa peluang penjualan di bulan ke-17 adalah

3201.967 (ribu) tempe dan di bulan ke-18 adalah 4685.361 (ribu) tempe.

MAX

Penjelasan : Mencari bilangan terbesar di dalam sekumpulan data yang diberikan.

Syntax :MAX(bilangan1,bilangan2,…)

Contoh : Jika A1:A5 berisi 10, 7, 9, 27, dan 2 maka :

=MAX(A1:A5) menghasilkan 27 =MAX(A1:A5, 30) menghasilkan 30

MEDIAN

Penjelasan : Mencarimedian(bilangan tengah) di dalam sekumpulan data yang diberikan.

Syntax :MEDIAN(bilangan1,bilangan2,…) Contoh : =MEDIAN(2,4,1,3,5) menghasilkan 3

=MEDIAN(2,4,1,3,5,6) menghasilkan 3.5 →rata-rata dari 3 dan 4

MIN

Penjelasan : Mencari bilangan terkecil di dalam sekumpulan data yang diberikan.

Syntax :MIN(bilangan1,bilangan2,…)

Contoh : Jika A1:A5 berisi 10, 7, 9, 27, dan 2 maka :

=MIN(A1:A5) menghasilkan 2

MODE

Penjelasan : Mencari modus (bilangan yang paling sering muncul) di dalam sekumpulan data yang diberikan.

Syntax :MODE(bilangan1,bilangan2,…)

Contoh : =MODE({5.6,4,4,3,2,4}) menghasilkan 4

PERMUT

Penjelasan : Menghitung banyaknya permutasi (banyaknya kombinasi bilangan).

Syntax :PERMUT(jumlah_bilangan,jumlah_dipilih)

Contoh : Dengan rumus ini kita bisa mengetahui banyaknya kendaraan bermotor yang memiliki nomor polisi B xxxx QQ. Kita mengetahui bahwa nomor normal kendaraan bermotor adalah 4 angka dengan kombinasi angka 0 sampai 9

(sebanyak 10 angka). Maka kita bisa me masukkan formula =PERMUT(10,4) dan

(28)

27

QUARTILE

Penjelasan : Mencari kuartir (nilai paruh) tertentu dari sekumpulan data yang diberikan. Kuartir diperlukan misalnya untuk menentukan 25 % pemasukkan tertinggi.

Syntax :QUARTILE(data,no_kuartir)

no_kuartir Nilai yang dihasilkan

0 nilai paling kecil (sama dengan fungsi MIN)

1 kuartir pertama - 25 %

2 kuartir kedua - 50 % (sama dengan fungsi MEDIAN)

3 kuartir ketiga - 75 %

4 nilai paling besar (sama dengan fungsi MAX)

Contoh : =QUARTILE({1,2,4,7,8,9,10,12}, 1) menghasilkan 3.5

Beberapa Fungsi Tanggal dan Waktu dalam Excel

Berikut adalah fungsi tanggal dan waktu dalam Excel yang sering digunakan dalam pekerjaan :

DATE

Penjelasan : Menghasilkan tanggal berdasarkan data yang kita berikan.

Syntax :DATE(tahun,bulan,angka_hari)

Contoh : =DATE(2001,10,1) menghasilkan “10/1/2001”

DAY

Penjelasan : Mendapatkan angka hari dari tanggal yang diberikan. Nilai yang dihasilkan akan berkisar antara 1 sampai 31.

Syntax :DAY(tanggal)

Contoh : =DAY(“4-Jan”) menghasilkan 4 =DAY(“8/11/1998”) menghasilkan 11

HOUR

Penjelasan : Mendapatkan jam dari waktu yang diberikan. Nilai yang dikembalikan berkisar antara 0 (12:00 AM) sampai 23 (11:00 PM).

Syntax :HOUR(waktu)

waktu disini bisa berisi teks yang menyatakan waktu; bilangan desimal (misalnya, 12 PM dinyatakan sebagai 0.5, karena dianggap setengah hari); atau hasil dari fungsi lain.

Contoh : =HOUR(0.7) menghasilkan 16

=HOUR(“3:30:30 PM”) menghasilkan 15

MINUTE

Penjelasan : Mendapatkan menit dari waktu yang diberikan. Nilai yang dikembalikan berkisar antara 0 sampai 59.

Syntax :MINUTE(waktu)

Contoh : =MINUTE(“4:48:00 PM”) menghasilkan 48 =MINUTE(0.01) menghasilkan 14

MONTH

(29)

28

berkisar antara 1 (January) sampai 12 (December).

Syntax :MONTH(tanggal)

Contoh : =MONTH(“6-May”) menghasilkan 5 =MONTH(“2004/04/01”) menghasilkan 4

NOW

Penjelasan : Menghasilkan tanggal dan waktu sekarang.

Syntax :NOW()

SECOND

Penjelasan : Mendapatkan detik dari waktu yang diberikan. Nilai yang dikembalikan berkisar antara 0 sampai 59.

Syntax :SECOND(waktu)

Contoh : =SECOND(“4:48:18 PM”) menghasilkan 18 =SECOND(0.01) menghasilkan 24

TIME

Penjelasan : Menghasilkan waktu berdasarkan data yang kita berikan.

Syntax :DATE(jam,menit,detik)

Contoh : =TIME(16,48,10) menghasilkan “4:48:10 PM”

TODAY

Penjelasan : Menghasilkan tanggal sekarang.

Syntax :TODAY()

WEEKDAY

Penjelasan : Menghasilkan urutan hari dalam satu minggu dari tanggal yang kita berikan.

Syntax :WEEKDAY(tanggal,jenis)

jenis Nilai yang dihasilkan

1 (default ) angka 1 (Minggu) sampai 7 (Sabtu)

2 angka 1 (Senin) sampai 7 (Minggu)

3 angka 0 (Senin) sampai 6 (Minggu)

Contoh : =WEEKDAY(“2/14/1998”) menghasilkan 7 (Sabtu)

YEAR

Penjelasan : Mendapatkan angka tahun dari tanggal yang diberikan. Nilai yang dihasilkan akan berkisar antara 1900 sampai 9999.

Syntax :YEAR(tanggal)

Contoh : =YEAR(“7/5/1998”) menghasilkan 1998

Beberapa Fungsi Logika dalam Excel

Berikut adalah fungsi logika dalam Excel yang sering digunakan dalam pekerjaan :

AND

Penjelasan : Menghasilkan TRUE jika semua parameter/argumen bernilai TRUE. Untuk lebih jelasnya lihat bagian “Lebih lanjut dengan fungsi IF”.

(30)

29

Syntax :AND(kondisi1,kondisi2,…)

Contoh : lihat bagian “Lebih lanjut dengan fungsi IF”

FALSE

Penjelasan : Menghasilkan nilai logika FALSE.

Syntax :FALSE()

IF

Penjelasan : Menghasilkan suatu nilai jika kondisinya TRUE dan nilai lainnya jika kondisinya FALSE. Untuk lebih jelasnya lihat bagian “Lebih lanjut dengan fungsi IF”.

Syntax :=IF(kondisi,nilai_jika_benar,nilai_jika_salah) Contoh : lihat bagian “Lebih lanjut dengan fungsi IF”

NOT

Penjelasan : Membalik logika. Untuk lebih jelasnya lihat bagian “Lebih lanjut dengan fungsi IF”.

Syntax :NOT(kondisi)

OR

Penjelasan : Menghasilkan FALSE jika semua parameter/argumen bernilai FALSE. Untuk lebih jelasnya lihat bagian “Lebih lanjut dengan fungsi IF”.

Syntax :OR(kondisi1,kondisi2,…)

TRUE

Penjelasan : Menghasilkan nilai logika TRUE.

Syntax :TRUE()

Beberapa Fungsi Teks dalam Excel

Berikut adalah fungsi teks dalam Excel yang sering digunakan dalam pekerjaan :

CONCATENATE

Penjelasan : Menggabungkan beberapa teks menjadi satu. Sebenarnya fungsi ini bisa digantikan oleh operator “&”.

Syntax :CONCATENATE(teks1,teks2,…)

Contoh : Jika range A11:C11 berisi “spesies”, “ikan bandeng”, dan 32, maka :

=CONCATENATE(“Populasi untuk ”,A11,“ ”,B11,“ adalah ”,C11,“/mil”)

akan menghasilkan “Populasi untuk spesies ikan bandeng adalah 32/mil.

DOLLAR

Penjelasan : Mengubah bilangan menjadi teks dengan menggunakan format currency , dengan pembulatan bilangan desimal sebanyak yang kita tentukan. Format yang digunakan sebenarnya adalah $#,##0.00_);($#,##0.00).

Syntax :DOLLAR(bilangan,desimal)

(31)

30

=DOLLAR(1234.567, -2) menghasilkan “$1,200” =DOLLAR(-1234.567, -2) menghasilkan “($1,200)”

=DOLLAR(-0.123, 4) menghasilkan “($0.1230)”

EXACT

Penjelasan : Membandingkan dua teks dan menghasilkan nilai TRUE jika kedua teks

benar-benar sama, sebaliknya FALSE. EXACT adalah case-sensitive tetapi

menghiraukan pemformat an yang ada.

Syntax :EXACT(teks1,teks2)

Contoh : =EXACT(“excel”,“excel”) menghasilkan TRUE =EXACT(“Excel”,“excel”) menghasilkan FALSE =EXACT(“e xcel”, “excel”) menghasilkan FALSE

Jika kita membuat worksheet untuk orang lain dan kita ingin memastikan agar orang tersebut memasukkan data dengan benar, kita bisa buat array formula berikut untuk memeriksanya :

{=AND(EXACT(nilai_panduan,range_yang_dibandingkan)

Misalnya saja kita memiliki range tempat orang memasukkan datanya pada B2:D4, dan pada cell A1 terdapat kata kunci di mana orang harus memasukkan data sesuai dengan isi cell tersebut,

maka kita bisa membuat array formula di A3 sebagai berikut :=AND(EXACT(A1,B2:D4))

Untuk lebih jelasnya perhatikan worksheet di bawah ini.

A B C D

1 kg

2 kg kg kg

3 =AND(EXACT(A1,B2:D4)) kg kg kg

4 kg kg kg

Jika kita mengisi salah satu cell dengan isi selain ‘kg’, maka cell A3 akan bernilai FALSE. Sebaliknya jika range B2:D4 berisi ‘kg’ semua, maka A3 akan bernilai TRUE.

FIND

Penjelasan : Mencari satu teks di dalam teks yang lainnya dan menghasilkan posisi karakter pertamanya. Kita juga bisa menggunakan fungsi SEARCH untuk mencari teks

tertentu, tetapi tidak seperti SEARCH, fungsi FIND adalah case-sensitive dan

tidak mengizinkan adanya karakter wildcard . Jika teks yang dicari tidak terdapat

dalam teks yang lainnya, maka Excel akan memberikan nilai kesalahan #VALUE!.

Syntax :FIND(teks_yang_dicari,teks_tempat_mencari,posisi_mulai) Contoh : =FIND(“c”,“Microsoft Excel”) menghasilkan 3

=FIND(“c”,“Microsoft Excel”,5) menghasilkan 13 =FIND(“Ex”,“Microsoft Excel”) menghasilkan 11

Misalkan kita mempunyai daftar komponen dan nomor serinya, dan kita ingin memisahkan nama komponen tersebut dari nomor serinya pada setiap cell. Andaikan saja range A2:A4 berisi “Ceramic Insulators #124-TD45-87”, “Copper Coils #12-671-6772”, dan “Variable Resistors

#116010”. Maka kita bisa membuat formula =MID(A2,1,FIND(" #",A2)-1) di cell B2 misalnya

lalu meng-copy -nya ke cell B3 dan B4. Kita juga bisa membuat array formula di range B2:B4

seperti{=MID(A6:A8,1,FIND(" #",A6:A8)-1)}.

FIXED

Penjelasan : Membulatkan suatu bilangan pada posisi digit yang kita tentukan dan mengembalikan nilainya sebagai teks.

(32)

31

Contoh : =FIXED(1234.567,1) menghasilkan “1234.6” =FIXED(-1234.567,-1) menghasilkan “-1230”

=FIXED(44.332) menghasilkan “44.33”

LEFT

Penjelasan : Mengambil beberapa karakter pertama (dari kiri) dalam suatu teks.

Syntax :LEFT(teks,jumlah_karakter)

Contoh : =LEFT(“Sale Price”,4) menghasilkan “Sale”

Beberapa program atau aplikasi terkadang menghasilkan bilangan negatif dengan tanda negatif

(-) di sebelah kanan nilai. Ini akan menjadi masalah ketika kita melakukan import file dari

aplikasi tersebut ke dalam Excel. Berikut adalah program untuk mengubah nilai negatif, misalnya “156-”, yang terdapat di cell A2 agar menjadi nilai –156 :

=LEFT(A2,LEN(A2)-1)*-1

LEN

Penjelasan : Menghitung jumlah karakter yang terdapat di dalam (atau panjangnya) teks.

Syntax :LEN(teks)

Contoh : =LEN(“Phoenix Pinion”) menghasilkan 14 =LEN(“”) menghasilkan 0

LOWER

Penjelasan : Mengganti semua huruf yang terdapat di dalam teks menjadi huruf kecil.

Syntax :LOWER(teks)

Contoh : =LOWER(“PT. Gajah Tunggal”) menghasilkan “pt. gajah tunggal”

MID

Penjelasan : Mengambil beberapa karakter dari sebuah teks.

Syntax :MID(teks,posisi_mulai,banyaknya_karakter)

Contoh : =MID(“PT. Gajah Tunggal”,5,5) menghasilkan “Gajah” =MID(“ABC”,2,5) menghasilkan “BC”

=MID(“ABC”,4,2) menghasilkan “” (teks kosong) =MID(“ABC”,0,2) menghasilkan #VALUE!

PROPER

Penjelasan : Meng-hurufbesar-kan huruf pertama pada masing-masing kata, dan meng-hurufkecil-kan sisanya.

Syntax :PROPER(teks)

Contoh : =PROPER(“ini adaLah JUDUL”) menghasilkan “Ini Adalah Judul” =PROPER(“2-orang anak”) menghasilkan “2-Orang Anak”

=PROPER(“76BudGet”) menghasilkan “76Budget”

REPLACE

Penjelasan : Akan mengganti bagian pada posisi tertentu dalam suatu teks dengan bagian lain.

Syntax :REPLACE(teks,posisi_diganti,jumlah_kar_diganti,teks_pengganti) Contoh : =REPLACE(“abcdefghijk”,6,5,“*”) menghasilkan “abcde*k”

=REPLACE(“abcdefghijk”,6,1,”12345”) menghasilkan “abcde12345ghijk” =REPLACE(“abcdefghijk”,6,3,””) menghasilkan “abcdeijk”

(33)

32

REPT

Penjelasan : Mengulang teks sebanyak yang kita tentukan.

Syntax :REPT(teks,banyaknya_pengulangan) Contoh : =REPT(“+-”,3) menghasilkan “+-+-+-”

=REPT(“Sales”,2.9) menghasilkan “SalesSales”

RIGHT

Penjelasan : Mengambil beberapa karakter terakhir (dari kanan) dalam suatu teks.

Syntax :RIGHT(teks,jumlah_karakter)

Contoh : =RIGHT(“Sale Price”,3) menghasilkan “ice”

SEARCH

Penjelasan : Mencari satu teks di dalam teks yang lainnya dan menghasilkan posisi karakter

pertamanya. Fungsi ini mengizinkan adanya karakter wildcard yaitu ? untuk

mewakilkan satu karakter bebas dan * untuk mewakilkan beberapa karakter

bebas. Fungsi ini tidak case-sensitive jadi tidak akan membedakan antara huruf

besar dan huruf kecil. Jika teks yang dicari tidak terdapat dalam teks yang lainnya, maka Excel akan memberikan nilai kesalahan #VALUE!.

Syntax :SEARCH(teks_yang_dicari,teks_tempat_mencari,posisi_mulai) Contoh : =SEARCH(“C”,“Microsoft Excel”) menghasilkan 3

=SEARCH(“??c”,“Microsoft Excel”,5) menghasilkan 11 =SEARCH(“*c”,“Microsoft Excel”,5) menghasilkan 5

SUBSTITUTE

Penjelasan : Akan mengganti bagian tertentu dalam suatu teks dengan bagian lain. Gunakan SUBSTITUTE jika kita ingin mengganti suatu teks dalam teks tertentu. Dan gunakan REPLACE jika kita ingin mengganti suatu teks pada posisi tertentu dalam teks tertentu.

Syntax :SUBSTITUTE(teks,teks_lama,teks_baru,urutan_yang_diganti)

Contoh : =SUBSTITUTE(“Sales Data”,“Sales”,“Cost”) menghasilkan “Cost Data” =SUBSTITUTE(“1 Januari 1991”,“1”,“2”) menghasilkan “2 Januari 2992” =SUBSTITUTE(“1 Januari 1991”,“1”,“2”,1) menghasilkan “2 Januari 1991”

TEXT

Penjelasan : Mengubah nilai menjadi teks yang memiliki format tertentu.

Syntax :TEXT(nilai,format_teks)

Contoh : =TEXT(2.715,“$0.00”) menghasilkan “$2.72”

=TEXT(“4/15/91”,“mmmm dd, yyyy”) menghasilkan “April 15, 1991”

TRIM

Penjelasan : Menghilangkan spasi lebih yang terdapat di antara kata.

Syntax :TRIM(teks)

Contoh : =TRIM(“ Saya Suka Buah ”) menghasilkan “Saya Suka Buah”

UPPER

Penjelasan : Mengganti semua huruf yang terdapat di dalam teks menjadi huruf besar.

Syntax :UPPER(teks)