dtu microsoft excel, word dan … · sejumlah fasilitas standar yang ditawarkan excel dalam proses...

PUSDIKLAT KEUANGAN UMUM

Badan Pendidikan Dan Pelatihan Keuangan

Kementerian Keuangan R.I

DTU MICROSOFT EXCEL, WORD DAN POWERPOINT 2010 TINGKAT LANJUTAN

Microsoft Excel

Modul: Microsoft Excel ------------------------------------------------

MODUL

Microsoft Excel

Oleh:

Iqbal Soenardi

Agus Hekso Pramudijono

KEMENTERIAN KEUANGAN REPUBLIK INDONESIA

BADAN PENDIDIKAN DAN PELATIHAN KEUANGAN

PUSDIKLAT KEUANGAN UMUM

2012

DIKLAT TEKNIS UMUM

MICROSOFT EXCEL, WORD, DAN POWERPOINT

2010 TINGKAT LANJUTAN


Identitas Modul

Judul Modul:

Microsoft Excel

Penulis:

Iqbal Soenardi

Agus Hekso Pramudijono

Digunakan untuk diklat:

Diklat Teknis Umum Microsoft Excel, Word, dan Powerpoint 2010

Tingkat Lanjutan

Pusdiklat Keuangan Umum

Badan Pendidikan dan Pelatihan

Kementerian Keuangan Republik Indonesia

Cetakan Pertama

2013


Kata Pengantar

Puji syukur kami panjatkan kepada Tuhan Yang Maha Esa atas rahmat-Nya

sehingga penyusunan Modul Microsoft Excel untuk Diklat Teknis Umum (DTU) Ms.

Excel, Word, dan PowerPoint 2010 (Tingkat Lanjutan) dapat diselesaikan dengan baik.

Modul ini digunakan sebagai acuan atau pedoman bagi pengajar, peserta, dan pihak

penyelenggara diklat agar tujuan pembelajaran dapat tercapai secara sistematis dan

efektif, baik dengan metode classical maupun e-learning.

Modul Microsoft Excel Tingkat Lanjutan disusun oleh Saudara Iqbal Soenardi dan

Saudara Agus Hekso Pramudijono berdasarkan Keputusan Kuasa Pengguna Anggaran

Pusat Pendidikan dan Pelatihan Keuangan Umum Nomor KEP-111/KPA PUS.KU/2012

tanggal 14 Mei 2012 tentang Pembentukan Tim Penyusunan Modul di Lingkungan Pusat

Pendidikan dan Pelatihan Keuangan Umum Tahun Anggaran 2012. Modul ini membantu

peserta untuk meningkatkan kompetensi dalam pengaplikasian teknis penyusunan

formula Excel dengan memanfaatkan fungsi Excel yang standar serta memanfaatkan

sejumlah fasilitas standar yang ditawarkan Excel dalam proses penyusunan laporan di

tempat kerja. Ruang lingkup Modul terdiri dari Pengetahuan Dasar Tentang Excel,

Menghitung Dengan Excel, dan Menyusun laporan.

Penyusunan modul ini mendapatkan dukungan dari berbagai pihak, antara lain

unit pengguna, narasumber, widyaiswara, dan pejabat struktural di lingkungan Pusdiklat

Keuangan Umum. Oleh karena itu, pada kesempatan ini kami mengucapkan terima kasih

kepada semua pihak yang telah membantu proses penyusunan modul ini. Kami

menyadari sepenuhnya bahwa dalam penyusunan modul masih banyak kekurangan,

untuk itu kritik dan saran yang membangun dari pembaca sangat diharapkan demi

kesempurnaan modul ini. Semoga modul ini bermanfaat bagi peserta, pengajar, panitia

diklat, dan pembaca lainnya.

Jakarta, Desember 2012 Kepala Pusdiklat Keuangan Umum

Tony Rooswiyanto NIP 195604041982031001


Daftar Isi

Identitas Modul .................................................................................................................. iii

Kata Pengantar ....................................................................................................................iv

Daftar Isi .............................................................................................................................. v

Daftar Tabel ....................................................................................................................... vii

Daftar Gambar .................................................................................................................. viii

Daftar Keyboard Shortcut .................................................................................................. xii

Petunjuk Penggunaan ...................................................................................................... xvii

Peta Konsep ...................................................................................................................... xix

A. PENDAHULUAN ........................................................................................................... 1

1. Deskripsi .................................................................................................................. 1

2. Prasyarat Kompetensi ............................................................................................. 1

3. Standar Kompetensi ................................................................................................ 1

4. Kompetensi Dasar ................................................................................................... 1

5. Relevansi Modul ...................................................................................................... 2

B. KEGIATAN BELAJAR ..................................................................................................... 3

1. Kegiatan Belajar 1 : PENGETAHUAN DASAR TENTANG EXCEL ................................ 3

a. Uraian dan Contoh .............................................................................................. 3

1) Workbook dan Worksheet, Cell dan Range .................................................... 3

2) Alamat Cell Relatif dan Absolut .................................................................... 12

3) Mengatur Format Cells ................................................................................. 12

4) Navigasi dalam Excel ..................................................................................... 17

b. Rangkuman ....................................................................................................... 18

c. Tes Formatif Kegiatan Belajar 1 ........................................................................ 18

d. Umpan Balik dan Tindak Lanjut ........................................................................ 19

2. Kegiatan Belajar 2 : MENGHITUNG DENGAN EXCEL ............................................. 20

a. Uraian dan Contoh ............................................................................................ 20

1) Memahami Formula...................................................................................... 20


2) Bekerja dengan Fungsi Excel ......................................................................... 28

b. Rangkuman ..................................................................................................... 107

c. Tes Formatif Kegiatan Belajar 2 ...................................................................... 108

d. Umpan Balik dan Tindak Lanjut ...................................................................... 109

3. Kegiatan Belajar 3 : MENYUSUN LAPORAN ........................................................ 110

a. Uraian dan Contoh .......................................................................................... 110

1) Menggunakan Conditional Formatting ....................................................... 110

2) Mengurutkan Data ...................................................................................... 128

3) Menghilangkan Duplikasi Data dan Filtering .............................................. 133

4) Menggunakan Pivot Table .......................................................................... 142

5) Menggunakan Goal Seek ............................................................................ 149

b. Rangkuman ..................................................................................................... 152

c. Tes Formatif Kegiatan Belajar 3 ...................................................................... 153

d. Umpan Balik dan Tindak Lanjut ...................................................................... 153

PENUTUP ......................................................................................................................... 154

TES SUMATIF ................................................................................................................... 155

KUNCI JAWABAN ............................................................................................................. 156

DAFTAR ISTILAH .............................................................................................................. 157

DAFTAR PUSTAKA ........................................................................................................... 160


Daftar Tabel

Tabel 1. 1 – Daftar Keyboard Shortcuts untuk Format Cells ............................................. 16

Tabel 1. 2 – Keyboard Shortcuts untuk Navigasi .............................................................. 17

Tabel 2. 1 – Operator Matematika ................................................................................... 21

Tabel 2. 2 – Format Tanggal .............................................................................................. 34

Tabel 2. 3 – Format Waktu................................................................................................ 36

Tabel 2. 4 – Daftar Comparsion Operator ......................................................................... 56

Tabel 2. 5 – Contoh Logical Test ....................................................................................... 56

Tabel 2. 6 – Contoh Logical Test AND ............................................................................... 57

Tabel 2. 7 – Contoh Logical Test OR .................................................................................. 58

Tabel 2. 8 – Hasil Pembulatan dengan num_digits = 2 ..................................................... 69

Tabel 2. 9 – Hasil Pembulatan dengan num_digits = -2 .................................................... 70

Tabel 2. 10 – Tabel Fungsi IS ............................................................................................. 83

Tabel 2. 11 - Contoh Penggunaan Operator Matematika untuk Teks dan Angka ............ 89


Daftar Gambar

Gambar 1. 1 – Excel Option Dialog: General Options ......................................................... 4

Gambar 1. 2 – Menu New Workbook ................................................................................. 5

Gambar 1. 3 – Tab Worksheet ............................................................................................ 6

Gambar 1. 4 – Mengubah Nama Worksheet ...................................................................... 6

Gambar 1. 5 – Klik Icon Insert Worksheet .......................................................................... 6

Gambar 1. 6 – Menu Insert Sheet ....................................................................................... 7

Gambar 1. 7 – Menghapus Worksheet ............................................................................... 7

Gambar 1. 8 – Menu Delete Sheet ..................................................................................... 7

Gambar 1. 9 – Klik Kanan pada Radio Bar ........................................................................... 8

Gambar 1. 10 – Kotak Dialog Move or Copy ....................................................................... 9

Gambar 1. 11 – Bekerja dengan Dua Worksheet dalam Satu Workbook ......................... 10

Gambar 1. 12 – Contoh Bekerja dengan Dua Workbook .................................................. 11

Gambar 1. 13 – Range B3:C9 ............................................................................................ 12

Gambar 1. 14 – Dialog Format Cell (CTRL + 1) .................................................................. 12

Gambar 1. 15 – Format Cells............................................................................................. 13

Gambar 1. 16 – Data dengan Format TEXT ....................................................................... 14

Gambar 1. 17 – Custom Format 1 ..................................................................................... 15

Gambar 1. 18 – Custom Format 2 ..................................................................................... 16

Gambar 2. 1 – Data Static ................................................................................................. 21

Gambar 2. 2 – Data Formula ............................................................................................. 21

Gambar 2. 3 – Sifat Relatif dari Formula ........................................................................... 23

Gambar 2. 4 – Sifat Relatif dari Formula (Relative Referencing) ...................................... 23

Gambar 2. 5 – Sifat Relatif Tak Selamanya Berhasil ......................................................... 23

Gambar 2. 6 – Sorot Target Kelompok Cell ....................................................................... 25

Gambar 2. 7 – Ketikkan Nilai atau Formula yang Diinginkan ............................................ 25

Gambar 2. 8 – Tekan Tombol Ctrl + Enter Bersamaan...................................................... 26

Gambar 2. 9 – Fill Handle .................................................................................................. 26


Gambar 2. 10 – Simulasi Teknik Double Click Fill Handle ................................................. 27

Gambar 2. 11 – Formulas Tab in Excel .............................................................................. 30

Gambar 2. 12 – Fungsi yang Ditawarkan Saat Mengetikkan Formula .............................. 31

Gambar 2. 13 – Mencari Fungsi menggunakan Tombol Panah ........................................ 32

Gambar 2. 14 – Fungsi dan Tooltips-nya .......................................................................... 32

Gambar 2. 15 – Fasilitas Autosum .................................................................................... 33

Gambar 2. 16 – Autosum .................................................................................................. 34

Gambar 2. 17 – Custom Format untuk Tanggal dd-mmmm-yyy ...................................... 35

Gambar 2. 18 – Contoh Fungsi Tanggal dan Waktu .......................................................... 38

Gambar 2. 19 – Daftar Character ...................................................................................... 52

Gambar 2. 20 – Hasil Perhitungan .................................................................................... 65

Gambar 2. 21 – Hasil Perhitungan Menggunakan IFERROR ............................................. 65

Gambar 2. 22 – Menu Setelah Menekan Klik Kanan pada Mouse/Keyboard .................. 67

Gambar 2. 23 – Tampilan Menu Setelah Memilih Paste Special ...................................... 67

Gambar 2. 24 – Layar Dialog Paste Special ....................................................................... 68

Gambar 2. 25 – Hasil Akhir Proses Paste Value ................................................................ 68

Gambar 2. 26 – Lookup dengan result_vector ................................................................. 72

Gambar 2. 27 – LOOKUP tanpa result_vector .................................................................. 72

Gambar 2. 28 – Contoh ISERROR ...................................................................................... 83

Gambar 2. 29 – Contoh Perhitungan Menggunakan Fungsi COUNT ................................ 87

Gambar 2. 30 – Contoh Perhitungan Menggunakan Fungsi COUNTA .............................. 88

Gambar 2. 31 – Contoh Penggunaan Fungsi DCOUNT...................................................... 88

Gambar 2. 32 – Contoh Penggunaan Fungsi DCOUNTA ................................................... 92

Gambar 2. 33 – Contoh Penggunaan COUNTBLANK ........................................................ 94

Gambar 2. 34 – Contoh Penggunaan COUNTIF 1 ............................................................. 96

Gambar 2. 35 – Contoh Penggunaan COUNTIF 2 ............................................................. 98

Gambar 2. 36 – Latihan COUNTIF dengan Dua/Lebih Kriteria .......................................... 99

Gambar 2. 37 – Contoh SUMPRODUCT 1 ....................................................................... 102

Gambar 2. 38 – Contoh SUMPRODUCT 2 ....................................................................... 103

Gambar 2. 39 – Contoh Penggunaan SUMIF .................................................................. 105

Gambar 2. 40 – Contoh Penggunaan SUMIFS ................................................................. 107


Gambar 3. 1 - Akses terhadap Menu Conditional Formatting ........................................ 111

Gambar 3. 2 - Menu Conditional Formatting.................................................................. 112

Gambar 3. 3 – Highlight Cells Rules ................................................................................ 113

Gambar 3. 4 – Contoh Data ............................................................................................ 113

Gambar 3. 5 – Contoh Penggunaan Menu Greater Than 1 ............................................ 114

Gambar 3. 6 – Contoh Penggunaan Menu Greater Than 2 ............................................ 114

Gambar 3. 7 – Menu Top/Bottom Rules ......................................................................... 115

Gambar 3. 8 – Menu Data Bars ....................................................................................... 116

Gambar 3. 9 – Contoh Penggunaan Menu Data Bars ..................................................... 116

Gambar 3. 10 – Contoh Penggunaan Menu Color Scales ............................................... 117

Gambar 3. 11 – Contoh Penggunaan Menu Icon Sets .................................................... 118

Gambar 3. 12 – Contoh Penggunaan Menu New Rule ................................................... 119

Gambar 3. 13 – Contoh Penggunaan Menu Format only cells that contain .................. 120

Gambar 3. 14 – Contoh Penggunaan Menu Spesific Text............................................... 121

Gambar 3. 15 – Contoh Penggunaan Menu Dates Occuring .......................................... 121

Gambar 3. 16 – Co toh Pe ggu aa Me u Use a fo ula to dete i e… ................... 123

Gambar 3. 17 – Contoh Penggunaan Formula untuk Conditional Formatting 1 ............ 125

Gambar 3. 18 – Contoh Hasil Penggunaan Formula untuk Conditional Formatting 1 ... 125

Gambar 3. 19 – Contoh Penggunaan Formula untuk Conditional Formatting 2 ............ 126

Gambar 3. 20 – Contoh Hasil Penggunaan Formula untuk Conditional Formatting 2 ... 127

Gambar 3. 21 – Menu Data Sort ..................................................................................... 128

Gambar 3. 22 – Contoh Data untuk Menu SORT ............................................................ 128

Gambar 3. 23 – Contoh Hasil Sort dengan Cara Cepat ................................................... 129

Gambar 3. 24 – Contoh Kelemahan Sort dengan Cara Cepat ......................................... 129

Gambar 3. 25 – Menu SORT ........................................................................................... 130

Gambar 3. 26 – Fasilitas pada Menu Sort ....................................................................... 131

Gambar 3. 27 – Sort Options .......................................................................................... 132

Gambar 3. 28 – SORT Menggunakan Multiple Criteria ................................................... 132

Gambar 3. 29 – Fasilitas Remove Duplicates .................................................................. 133

Gambar 3. 30 – Pilihan dalam Fasilitas Remove Duplicates ........................................... 134

Gambar 3. 31 – Kotak Dialog Pasca Remove Duplicates ................................................ 134

Gambar 3. 32 – Data Hasil Remove Duplicates .............................................................. 135

Gambar 3. 33 – Contoh Data .......................................................................................... 135

Gambar 3. 34 – Contoh Hasil Remove Duplicates .......................................................... 136

Gambar 3. 35 – Menu Advanced Filter ........................................................................... 137

Gambar 3. 36 – Contoh Penggunaan Advanced Filter .................................................... 138

Gambar 3. 37 – Contoh Penggunaan Filter ..................................................................... 139

Gambar 3. 38 – Hasil Proses Filter .................................................................................. 139

Gambar 3. 39 – Pilihan pada Menu Filter ....................................................................... 140

Gambar 3. 40 – Contoh Number Filters pada Kolom D .................................................. 141

Gambar 3. 41 – Hasil Akhir Proses Filtering .................................................................... 141

Gambar 3. 42 – Menu Pivot Table .................................................................................. 142

Gambar 3. 43 – Database untuk Pivot ............................................................................ 143

Gambar 3. 44 – Layar Dialog Pivot Tabel ........................................................................ 144


Gambar 3. 45 – Layar Dialog Pivot Tabel 2 ..................................................................... 144

Gambar 3. 46 – Fasilitas pada Menu Pivot Table ............................................................ 145

Gambar 3. 47 – Proses Pembuatan Laporan Menggunakan Pivot Table........................ 146

Gambar 3. 48 – Contoh Penggunaan Report Filter pada Pivot Table ............................. 147

Gambar 3. 49 – Contoh Multiple Filter dan Hasilnya...................................................... 148

Gambar 3. 50 – Menu Refresh pada Pivot Table ............................................................ 149

Gambar 3. 51 – Contoh Penggunaan Goal Seek ............................................................. 150

Gambar 3. 52 – Menu Goal Seek .................................................................................... 151

Gambar 3. 53 – Variabel Goal Seek ................................................................................ 151


Daftar Keyboard Shortcut

Windows Shortcut Keys

NO SHORTCUTS FUNGSI

1 Ctrl+C Copy 2 Ctrl+X Cut 3 Ctrl+V Paste 4 Ctrl+Z Undo 5 Ctrl+Y Redo 6 Ctrl+A Select all 7 Ctrl+S Save 8 Ctrl+O Open 9 Ctrl+W or Ctrl+F4 Close workbook

10 Ctrl+N New workbook 11 Ctrl+P Print 12 Ctrl+B Bold 13 Ctrl+U Underline 14 Ctrl+I Italic 15 Ctrl+F Find

Shortcut Keys for Selecting Data and Cells

NO SHORTCUTS FUNGSI

1 Ctrl+spacebar If used outside a table, select entire column If used inside a table, toggle between selecting the data, data and headers, and the entire column

2 Shift+spacebar Select entire row If inside a table, toggle between selecting the table row and the entire row

3 Shift+backspace With multiple cells selected, revert selection to only the active cell

4 Ctrl+Shift+* Select the current region 5 Ctrl+/ Select the array containing the active cell

6 Ctrl+Shift+O (letter O)

Select all cells that contain comments

7 Ctrl+\ In a selected row, select the cells that do not match the value in the active cell

8 Ctrl+Shift+| In a selected column, select the cells that do not match the value in the active cell

9 Ctrl+[ (opening square bracket)

Select all cells directly referenced by formulas in the selection

10 Ctrl+Shift+{ (opening brace)

Select all cells directly or indirectly referenced by for-mulas in the selection


11 Ctrl+] (closing square bracket)

Select cells that contain formulas that directly refer-ence the active cell

12 Ctrl+Shift+} (closing brace)

Select cells that contain formulas that directly or indi-rectly reference the active cell

13 Alt+; (semicolon) Select the visible cells in the current selection

Shortcut Keys for Extending Selections

NO SHORTCUTS FUNGSI

1 F8 Turn Extend mode on or off In Extend mode, EXT appears in the status line and the arrow keys extend the selection

2 Shift+F8

Add another range of cells to the selection or use the arrow keys to move to the start of the range you want to add Then press F8 and the arrow keys to select the next range

3 Shift+arrow key Extend the selection by one cell

4 Ctrl+Shift+arrow key

Extend the selection to the last nonblank cell in the same column or row as the active cell

5 Shift+Home Extend the selection to the beginning of the row 6 Ctrl+Shift+Home Extend the selection to the beginning of the worksheet

7 Ctrl+Shift+End Extend the selection to the last used cell on the worksheet in the lower-right corner

8 Shift+Page Down Extend the selection down one screen 9 Shift+Page Up Extend the selection up one screen

10 End+Shift+arrow key

Extend the selection to the last nonblank cell in the same column or row as the active cell

11 End+Shift+Home Extend the selection to the last used cell on the worksheet in the lower-right corner

12 End+Shift+Enter Extend the selection to the last cell in the current row

13 Scroll Lock+Shift+Home

Extend the selection to the cell in the upper-left corner of the window

14 Scroll Lock+Shift+End

Extend the selection to the cell in the lower-right corner of the window

Shortcut Keys for Navigation

NO SHORTCUTS FUNGSI

1 Ctrl+Home Move to Cell A1. 2 Ctrl+End Move to last cell in the used range of the worksheet. 3 Ctrl+Page Up Move to previous worksheet. 4 Ctrl+Page Down Move to next worksheet. 5 Shift+F11 New Worksheet. 6 Alt+Tab Switch to next program . 7 Alt+Shift+Tab Switch to previous program. 8 Ctrl+Esc Display Windows Start menu. 9 Ctrl+F5 Restore window size of current workbook.

10 F6 Switch to next pane in a window that has been split.

11 Ctrl+F6 When more than one workbook is open, switch to the next open workbook window.


NO SHORTCUTS FUNGSI

12 Ctrl+Shift+F6 Switch to the previous workbook window. 13 Ctrl+F9 Minimize the window . 14 Ctrl+F10 Maximize the window. 15 Ctrl+arrow key Move to edge of current region. 16 Home Move to beginning of row. 17 Ctrl+Backspace Scroll to display the active cell . 18 F5 Display the GoTo dialog . 19 Shift+F5 Display the Find dialog .

20 Shift+F4 Find next. 21 Ctrl+. (period) Move to next corner of selected range.

22 Ctrl+Up Arrow Move to the first row in the data if your data has no blank cells

23 Ctrl+Down Arrow Move t o the last row in the data if your data has no blank cells

Shortcut Keys for Data Entry, Formatting, and Calculating Data

NO SHORTCUTS FUNGSI

1 Enter Complete a cell entry and select the next cell below. 2 Alt+Enter Start a new line in the same cell. 3 Ctrl+Enter Fill the selected cell range with the current entry. 4 Shift+Enter Complete a cell entry and select the next cell above. 5 Tab Complete a cell entry and select the next cell to the right.

6 Shift+Tab Complete a cell entry and select the previous cell to the left.

7 Esc Cancel a cell entry. 8 Arrow keys Move one character up, down, left, or right. 9 Home Move to the beginning of the line.

10 F4 or Ctrl+Y Repeat the last action. 11 Ctrl+Shift+F3 Create names from row and column labels. 12 Ctrl+D Fill down. 13 Ctrl+R Fill to the right. 14 Ctrl+F3 Define a name. 15 Ctrl+K Insert a hyperlink. 16 Ctrl+; (semicolon) Enter the date.

17 Ctrl+Shift+: (colon)

Enter the time.

18 Alt+down arrow Display a drop-down list of the values in the current column of a range.

19 Ctrl+Z Undo the last action. 20 = (equal sign) Start a formula. 21 Backspace In the formula bar, delete one character to the left. 22 Enter Complete a cell entry from the cell or formula bar. 23 Ctrl+Shift+Enter Enter a formula as an array formula. 24 Esc Cancel an entry in the cell or formula bar. 25 Shift+F3 In a formula, display the Insert Function dialog box.

26 Ctrl+A When the insertion point is to the right of a function name in a formula, display the Function Arguments dialog


NO SHORTCUTS FUNGSI

box.

27 Ctrl+Shift+A When the insertion point is to the right of a function name in a formula, insert the argument names and parentheses.

28 F3 Paste a defined name into a formula. 29 Alt+= (equal sign) Insert an AutoSum formula with the SUM function.

30 Ct l+Shift+ (quotation mark)

Copy the value from the cell above the active cell into the cell or the formula bar.

NO SHORTCUTS FUNGSI

31 Ct l+ (apostrophe)

Copy a formula from the cell above the active cell into the cell or the formula bar.

32 Ctrl+` (backtick) Alternate between displaying cell values and displaying formu-las.

33 F9

Calculate all worksheets in all open workbooks. When a portion of a formula is selected, calculate the selected portion and then press Enter or Ctrl+Shift+Enter (for array formulas) to replace the selected portion with the calculated value.

34 Shift+F9 Calculate the active worksheet.

35 Ctrl+Alt+F9 Calculate all worksheets in all open workbooks, regardless of whether they have changed since the last calculation.

36 Ctrl+Alt+Shift+F9 Recheck dependent formulas and then calculate all cells in all open workbooks, including cells not marked as needing to be calculated.

37 F2 Edit the active cell and position the insertion point at the end of the cell contents. If in-cell editing is turned off, moves the inser-tion point to the formula bar.

38 Alt+Enter Start a new line in the same cell.

39 Backspace Edit the active cell and then clear it or delete the preceding character in the active cell as you edit cell contents.

40 Delete Delete the character to the right of the insertion point or delete the selection.

41 Ctrl+Delete Delete text to the end of the line. 42 F7 Display the Spelling dialog box. 43 Shift+F2 Edit a cell comment. 44 Enter Complete a cell entry and select the next cell below. 45 Ctrl+Z Undo the last action.

46 Ctrl+Shift+Z When the AutoCorrect smart tag is displayed, undo or redo the last automatic correction.

47 Delete Clear the contents of the selected cells. 48 Ctrl+- (hyphen) Delete the selected cells.

49 Ctrl+Shift++ (plus sign)

Insert blank cells.

50 Alt+ Display the Style dialog box.


NO SHORTCUTS FUNGSI

(apostrophe) 51 Ctrl+1 Display the Format Cells dialog box. 52 Ctrl+Shift+~ Apply the General number format.

53 Ctrl+Shift+$ Apply the Currency format with two decimal places (negative numbers in parentheses).

54 Ctrl+Shift+% Apply the Percentage format with no decimal places.

55 Ctrl+Shift+^ Apply the Exponential number format with two decimal places.

56 Ctrl+Shift+# Apply the Date format with the day, month, and year.

57 Ctrl+Shift+@ Apply the Time format with the hour and minute, and AM or PM.

NO SHORTCUTS FUNGSI

58 Ctrl+Shift+! Apply the Number format with two decimal places, thousands separator, and minus sign (-) for negative val.

59 Ctrl+B Apply or remove bold formatting. 60 Ctrl+I Apply or remove italic formatting. 61 Ctrl+U Apply or remove underline. 62 Ctrl+5 Apply or remove strikethrough. 63 Ctrl+9 Hide the selected rows.

64 Ctrl+Shift+( (opening parenthesis)

Unhide any hidden rows within the selection.

65 Ctrl+0 (zero) Hide the selected columns.

66 Ctrl+Shift+) (closing parenthesis)

Unhide any hidden columns within the selection.

67 Ctrl+Shift+& Apply the outline border to the selected cells.

68 Ctrl+Shift+_ (under-score)

Remove the outline border from the selected cells.


Petunjuk Penggunaan

Modul Microsoft Excel merupakan satu dari empat modul yang saling melengkapi

pada Diklat Teknis Umum (DTU) Micorsoft Excel, Word, dan PowerPoint 2010 Tingkat

Lanjutan dengan alur penggunaan modul sebagai berikut:

Modul Excel Tingkat Lanjutan ini terdiri atas tiga kegiatan belajar (KB), yaitu Lebih

Pengetahuan Dasar tentang Excel, Menghitung dengan Excel, dan Menyusun Laporan.

Modul ini dikembangkan denga pe dekata la gkah-de i la gkah gu a

memudahkan pembaca dalam memahami dan mempraktikkan teori dan teknik-teknik

yang diperkenalkan.

Pada bagian awal modul diperkenalkan sejumlah pengetahuan dasar tentang

Excel yang dilanjutkan dengan pengenalan sejumlah fungsi standar yang dimiliki Excel

dalam membantu penyelesaian pekerjaan sehari-hari di lingkungan kerja dan yang

terakhir, modul ini akan membantu pembaca dalam memahami sejumlah fasilitas

standar Excel yang sangat bermanfaat dalam penyusunan laporan kerja. Pada akhir

setiap kegiatan belajar diberikan rangkuman yang berisi intisari dari materi yang sudah

dibahas sebelumnya.


Selanjutnya untuk mengevaluasi pemahaman pembaca, disetiap akhir kegiatan

belajar juga disajikan tes formatif. Meskipun sudah disediakan kunci jawaban atas

pertanyaan-pertanyaan dalam tes formatif, peserta disarankan untuk tidak melihat dulu

kunci jawaban, namun sebaiknya peserta mengerjakan terlebih dahulu tes formatif

sesuai dengan alokasi waktu yang diberikan baru kemudian melakukan penilaian secara

mandiri dan mengecek nilainya dengan kriteria umpan balik, apakah sudah tercapai

dengan baik. Jika nilai baik belum tercapai, maka peserta disarankan membaca kembali

materi dan mengulangi mengerjakan soal tes sampai memperoleh hasil yang

diharapkan.


Peta Konsep

PENGETAHUAN DASAR TENTANG EXCEL

MENGHITUNG DENGAN EXCEL

MEMAHAMI FORMULA

BEKERJA DENGAN FUNGSI STANDAR EXCEL

MENYUSUN LAPORAN

MENGGUNAKAN CONDITIONAL FORMATTING

MENGGUNAKAN FASILITAS SORTING

MENGGUNAKAN FASILITAS REMOVING DUPLICATES DAN FILTERING

MENGGUNAKAN PIVOT TABLE DAN GOAL SEEK

PENDAHULUAN

------------------------------------------------------------------------------------------------------------- 1

A. PENDAHULUAN

1. Deskripsi

Microsoft Excel merupakan salah satu fitur software Microsoft Office yang

populer dipergunakan dalam kegiatan perkantoran sehari-hari, khususnya untuk

kegiatan pengolahan data sederhana yang tidak efisien dilakukan menggunakan

aplikasi khusus (tailor-made application).

2. Prasyarat Kompetensi

Sebelum mempelajari modul ini, pembaca diharapkan sudah menggunakan

program Microsoft Excel 2010 dan memahami fungsi-fungsi dasar dari program

tersebut.

3. Standar Kompetensi

Setelah mengikuti mata pelajaran ini, peserta diharapkan mampu

mengaplikasikan teknis penyusunan formula Excel dengan memanfaatkan fungsi

Excel yang standar serta memanfaatkan sejumlah fasilitas standar yang ditawarkan

Excel dalam proses penyusunan laporan di tempat kerja.

4. Kompetensi Dasar

Setelah selesai mengikuti pembelajaran ini, peserta diklat diharapkan

mampu:

a. Mengaplikasikan pemahaman dasar tentang Excel sehingga dapat bekerja lebih

efisien.

b. Memahami dan mengaplikasikan fungsi-fungsi standar Excel dalam memproses

data dan informasi.

c. Memanfaatkan fasilitas-fasilitas standar yang ditawarkan Excel dalam

mengefisienkan proses penyusunan laporan di tempat kerja.

PENDAHULUAN

------------------------------------------------------------------------------------------------------------- 2

5. Relevansi Modul

Teknik yang diperkenalkan dalam modul ini ditujukan untuk memberikan

inspirasi sebesar-besarnya kepada para peserta diklat sehingga setelah mempelajari

modul ini, para peserta diklat diharapkan dapat mengembangkan teknik-tekniknya

sendiri untuk membantu mengefisienkan waktu yang dibutuhkan untuk

menyelesaikan pekerjaannya sehari-hari di tempat kerja.

KEGIATAN BELAJAR 1 : PENGETAHUAN DASAR TENTANG EXCEL

------------------------------------------------------------------------------------------------------------- 3

B. KEGIATAN BELAJAR

1. Kegiatan Belajar 1 : PENGETAHUAN DASAR TENTANG EXCEL

a. Uraian dan Contoh

1) Workbook dan Worksheet, Cell dan Range

a) Workbook

Workbook adalah sebutan untuk dokumen Excel yang berisikan minimal satu

Worksheet.

1. Pengaturan Workbook

Dalam pengaturan standarnya (default), satu Workbook berisikan tiga

Worksheets yang jumlahnya dapat ditambah atau dikurangi sesuai

keinginan dan kebutuhan kita.

Setelah mengikuti pembelajaran ini peserta diklat diharapkan dapat mengusai

pengetahuan dasar tentang:

Workbook dan Worksheet, Cell dan Range;

Alamat Cell Relatif dan Absolut;

Mengatur Format Cell; dan

Navigasi dalam Excel

INDIKATOR


------------------------------------------------------------------------------------------------------------- 4

Gambar 1. 1 - Excel Option Dialog: General Options

Excel memberikan fasilitas bagi kita untuk mengubah jumlah

worksheet standar dalam satu workbook melalui menu File, Options, yang

akan membawa kita ke kotak dialog sebagai tampak pada Gambar 1. 1.

Pengubahan jumlah worksheet standar pada setiap workbook yang

baru, dapat dilakukan dengan menambah atau mengurangi angka pada baris

Include this many sheets .

Co toh jika isia pada a is Include this many sheets kita u ah e jadi

angka 2, maka setiap kali kita membuka workbook yang baru, pada

workbook dimaksud akan tersedia dua worksheets.

2. Membuat Workbook Baru

Workbook baru dapat dibuat dengan salah satu dari dua cara sebagai

berikut:

a. Tekan tombol CTRL + N, secara otomatis Excel akan menampilkan satu

workbook baru dengan worksheet dalam keadaan kosong. Jumlah

worksheet dalam workbook tergantung pengaturan yang kita lakukan.

b. Gunakan Menu File New dan Excel akan menampilkan pilihan

sebagaimana yang tampak pada Gambar 1. 2. Kita dapat memilih salah

satu dari lima pilihan template, yaitu Blank Workbook, Recent Template,

Sample Template, My Template, dan New From Existing.


------------------------------------------------------------------------------------------------------------- 5

Gambar 1. 2 – Menu New Workbook

3. Bekerja dengan Lebih dari Satu Workbook

Di beberapa kesempatan, kita harus bekerja dengan lebih dari satu

workbook dan jika hal ini terjadi, sangat disarankan agar kita memastikan

bahwa seluruh workbook yang kita gunakan berada dalam folder yang

sama, baik di internal harddisk maupun media penyimpanan eksternal

(flashdisk, harddisk eksternal). Tujuannya adalah ketika kita harus bekerja di

komputer yang berbeda, seluruh file yang terkait dengan file utama kita ikut

terbawa untuk menghindarkan terjadinya broken link pada workbook aktif

kita.

Teknis bekerja dengan lebih dari satu Workbook dapat kita cermati

pada topik Bekerja dengan Lebih dari Satu Worksheet dala KB i i.

b) Worksheet

Worksheet adalah sebutan untuk dokumen Excel yang terdiri atas 16.384

kolom dikali 1.048.576 baris atau dalam tiap worksheet tersedia 17.179.869.184

(Tujuh Belas Milyar Seratus Tujuh Puluh Sembilan Juta Delapan Ratus Enam

Puluh Sembilan Ribu Seratus Delapan Puluh Empat) cell.

1. Mengubah Nama Worksheet

Dalam bentuk standarnya, setiap worksheet diberi nama Sheet1, Sheet2,

dan seterusnya sebagaimana yang dapat dicermati pada Gambar 1. 3.


------------------------------------------------------------------------------------------------------------- 6

Tab worksheet

Gambar 1. 3 – Tab Worksheet

Nama-nama dari tiap worksheet ini dapat kita ubah sesuai selera dan

kebutuhan, caranya dengan men-double click pada tab worksheet yang kita

inginkan, lalu kita ketikkan nama sheet yang sesuai dan diakhiri dengan

menekan tombol ENTER (Gambar 1. 4.).

Double click

Gambar 1. 4 – Mengubah Nama Worksheet

Penamaan sheet yang baik adalah jika nama tersebut cukup singkat, namun

informatif, dan yang terpenting adalah sedapat mungkin hindari penamaan

sheet sesuai dengan nama variabel yang digunakan oleh Excel guna

menghindari konflik antar variabel yang digunakan.

2. Menambah dan Mengurangi Worksheet

Worksheet dapat ditambah dengan salah satu dari tiga cara berikut, yaitu:

a. menekan icon yang terdapat diujung sebelah kanan kumpulan tab

worksheet (Gambar 1. 5)

New Sheet

Gambar 1. 5 – Klik Icon Insert Worksheet


------------------------------------------------------------------------------------------------------------- 7

b. menggunakan shortcut Shift + F11

c. menggunakan menu Insert Sheet pada Ribbon Home, lihat Gambar 1. 6.

Gambar 1. 6 – Menu Insert Sheet

Worksheet dapat dikurangi (dihapus) dengan salah satu dari dua cara

berikut, yaitu:

1) klik kanan pada tab worksheet yang ingin dihapus dan pilih opsi Delete

Gambar 1. 7 – Menghapus Worksheet

2) gunakan menu Delete Sheet pada Ribbon Home

Gambar 1. 8 – Menu Delete Sheet

Hal penting yang perlu diingat jika kita ingin menghapus sebuah atau

beberapa worksheet adalah bahwa proses ini tidak bisa dibatalkan


------------------------------------------------------------------------------------------------------------- 8

menggunakan perintah UNDO (CTRL + Z), oleh karena itu, jika dipandang

perlu, sebelum menghapus sebuah/beberapa worksheet, sebaiknya

workbook-nya di simpan terlebih dahulu menggunakan perintah Save atau

Save As.

3. Pindah dari Worksheet Satu ke Worksheet Lain

Untuk memindahkan worksheet aktif dari yang satu ke worksheet yang lain,

dapat dilakukan dengan cara:

a. meng-klik worksheet yang dituju; atau

b. meng-klik kanan di radio bar (Gambar 1. 9.)

Gambar 1. 9 – Klik Kanan pada Radio Bar

c. dengan menekan tombol CTRL + PGDN untuk pindah ke worksheet

setelah worksheet aktif atau menekan tombol CTRL + PGUP untuk

pindah ke worksheet sebelum worksheet aktif.

4. Meng-copy atau Memindahkan (Move) Worksheet

Satu atau beberapa worksheet dapat di-copy dan di-paste pada workbook

yang sama dengan cara:

Klik kanan pada tab worksheet yang ingin di-copy atau dipindahkan dan pilih

Move or Copy yang akan memunculkan kotak dialog sebagaimana pada

gambar berikut:

Target Workbook

Pilih Posisi Baru

Copy atau Pindah?

Gambar 1. 10 – Kotak Dialog Move or Copy


------------------------------------------------------------------------------------------------------------- 9

Kita dapat menentukan secara spesifik posisi worksheet ketika di-paste atau

posisi worksheet yang baru setelah dipindahkan (Pilih Posisi Baru).

Selanjutnya, checked Create a Copy jika kita ingin membuat copy dari

worksheet atau unchecked Create a Copy jika kita hanya ingin

memindahkan posisinya di workbook kita.

5. Mengcopy atau Memindahkan Worksheet ke Workbook Lain

Satu atau beberapa worksheet dapat di-copy dan/atau dipindahkan

ke workbook lain, baik workbook yang sudah ada sebelumnya di media

penyimpanan kita atau ke workbook yang sama sekali baru. Caranya adalah

dengan mengubah isian To Book (Gambar 1. 10.). Secara default, isian To

Book ini adalah Workbook aktif kita. Kita bisa mengubah isian ini dengan

meng-klik tanda V yang ada di ujung field isian untuk To Book.

Jika kita ingin meng-copy satu worksheet atau lebih ke workbook lain,

aka se elu e eka to ol OK , pastika piliha C eate a Cop

sudah kita centang atau worksheet yang kita pilih akan pindah ke workbook

yang baru dan hilang dari workbook lama.

6. Bekerja dengan Lebih dari Satu Worksheet

Bekerja dengan lebih dari satu worksheet adalah hal yang umum

terjadi terutama jika bekerja dengan jenis data yang beragam yang akan

jauh lebih memudahkan bagi kita jika disimpan di lebih dari satu worksheet,

misalnya, hendaknya kita memisahkan antara sheet yang berisi data dan

sheet yang kita gunakan untuk pelaporan. Di banyak kesempatan, kita

menggunakan sejumlah worksheet dalam satu workbook, namun tidak

jarang pula, pekerjaan kita melibatkan worksheet dari lebih dari satu

workbook.

Hal yang perlu diingat jika kita bekerja dengan lebih dari satu worksheet:

a. Formula akan memuat nama worksheet yang kita jadikan acuan, oleh

karenanya pemberian nama worksheet hendaknya sejelas dan selogis

mungkin, guna memudahkan kita pada tahap review jika terjadi

kesalahan.


------------------------------------------------------------------------------------------------------------- 10

Contoh:

Gambar 1. 11 – Bekerja dengan Dua Worksheet dalam Satu Workbook

Jika kita cermati contoh di atas, dapat kita simpulkan bahwa:

alamat A18 dan A19 berada dalam worksheet yang sama, yaitu

Sheet1 (worksheet aktif)

alamat C18 berada pada Sheet2, dengan notasi Sheet2!C18

(worksheet pasif).

penyajian alamat cell pada worksheet aktif tidak harus

menggunakan nama sheet,

sementara itu, penyajian alamat cell yang berada pada worksheet

pasif, wajib menggunakan nama sheet, hal ini dilakukan secara

otomatis oleh Excel.

b. Jika kita bekerja dengan dua workbook atau lebih, maka:

Sebaiknya seluruh workbook telah diberi nama dan telah di-save.

Seluruh workbook yang dilibatkan berada pada folder yang sama.

Contoh:

Gambar 1. 12 - Contoh Bekerja dengan Dua Workbook

Dari Gambar 1. 12, tampak bahwa:

Alamat A24 dan A25 berada pada worksheet dan workbook yang

sama, yaitu Sheet1.

Alamat B43 disajikan Excel dengan notasi [filekerja

. ls ]Sheet !$B$ .


------------------------------------------------------------------------------------------------------------- 11

Notasi [filekerja . ls ]Sheet menunjukkan nama file dan nama

worksheet yang kita tuju, sementara $B$43 merupakan alamat cell

yang dimaksud.

Perlu kita ingat, bahwa jika bekerja dengan workbook yang berbeda,

maka Excel akan secara otomatis mengabsolutkan alamat yang kita

tunjuk dengan memberikan tanda $ pada alamat dimaksud. Oleh

karenanya, jika kita ingin meng-copy cell yang berisikan alamat ini,

pastikan apakah alamat absolut ini perlu kita ubah menjadi alamat

relatif atau tidak.

c) Cell dan Range

1. Sebagaimana yang telah kita ketahui, bahwa Excel memuat lebih dari 17

Milyar cell di dalamnya dan tiap cell diberi nama dengan ketentuan bahwa

NOTASI HURUF melambangkan KOLOM, sementara NOTASI ANGKA

melambangkan BARIS yang diwakilinya.

Contoh: Cell A1 berada pada Kolom A atau kolom pertama dan baris

pertama.

2. Range merupakan gabungan dari sejumlah cell yang berdekatan posisinya.

Contoh berikut menggambarkan Range dengan alamat B3:C9. B3

menggambarkan posisi cell paling atas sebelah kiri, sementara C9

menggambarkan posisi cell paling bawah sebelah kanan.

Gambar 1. 13 - Range B3:C9


------------------------------------------------------------------------------------------------------------- 12

2) Alamat Cell Relatif dan Absolut

Penggunaan alamat Cell dan Range dalam sebuah formula dapat dilakukan

dengan salah satu dari tiga cara berikut:

Alamat Cell dan Range Relatif

Contoh: A3 dan B3:C9

Alamat Cell dan Range Absolut

Contoh: $A$3 dan $B$3:$C$9

Alamat Cell dan Range Kombinasi Relatif dan Absolut

Contoh: Contoh: $A3 dan $B$3:C9

Penentuan apakah sebuah alamat akan digunakan secara absolut atau relatif

amat sangat tergantung pada kegunaan data yang ada pada Cell atau Range

dimaksud dalam formula yang sedang kita bangun. Jika sebuah Cell/Range tertentu

merupakan acuan tunggal, maka sangat besar kemungkinan alamat Cell/Range

dimaksud harus kita absolutkan.

3) Mengatur Format Cells

Cara termudah untuk mengatur Format Cells adalah menggunakan shortcut:

CTRL + 1. Hasilnya adalah kotak dialog sebagai berikut:

Gambar 1. 14 - Dialog Format Cell (CTRL + 1)


------------------------------------------------------------------------------------------------------------- 13

Sebagaimana yang dapat kita lihat, ada enam hal terkait Format Cell yang

dapat kita atur, yaitu:

Number;

Alignment;

Font;

Border;

Fill; dan

Protection.

Lima di antara keenam hal terkait pengaturan Format Cell, kecuali Number

merupakan Fungsi Aksesoris Excel, dimana empat di antaranya, Allignment, Font,

Border, Fill, lebih menitikberatkan fungsinya untuk mengatur tampilan dari sebuah

Cell atau Range, oleh karenanya pembahasan dalam modul ini lebih dititikberatkan

pada pembahasan tentang Menu Format Cells Number.

Menu Format Cells Number digunakan untuk mengatur definisi dari sebuah cell atau

kelompok cell (Gambar 1. 14 Gambar 1. 14).

Secara default, format sebuah cell adalah General dan secara umum Excel hanya

mengenal tiga jenis data, yaitu:

1. Data Angka, cirinya kalau diinput akan rata kanan

2. Data Text (huruf), cirinya kalau diinput akan rata kiri

3. Data Logika, berisikan TRUE atau FALSE

Contoh:

Gambar 1. 15 - Format Cells


------------------------------------------------------------------------------------------------------------- 14

Berikut adalah penjelasan lebih lanjut tentang Format Cell Numbers.

1. Data Angka

Meskipun pada Gambar 1. 14 kita dapat cermati bahwa Excel juga

mengenal Data Tanggal, Waktu, bahkan Nilai Uang, namun perlu kita sadari

ah a se ua itu ha alah ta pila lua saja, setidaknya untuk konsumsi

manusia selaku pengguna Excel. Excel sendiri memperlakukan seluruh data

dimaksud sebagai angka.

Sebagai contoh, data tanggal.

Excel memperlakukan data tanggal ini sebagai data yang berurutan mulai dari

yang terkecil, yaitu tanggal 1 Januari 1900 yang diwakili oleh angka 1 agar dapat

digunakan untuk proses perhitungan. Jadi data tanggal ini bisa digunakan dalam

operasi pertambahan dan pengurangan.

Jadi yang kita lihat, misalnya sebagai tanggal 17 Agustus 2012 dibaca Excel

sebagai angka 41138, sedangkan tanggal 18 Agustus 2012 dibaca Excel sebagai

angka 41139, dengan selisih sebesar 1.

2. Data Teks

Sebuah cell atau kelompok cell dapat kita definisikan memiliki format

TEXT sehi gga seluruh data yang diinputkan dalam cell atau kelompok cell

dimaksud akan diperlakukan sebagai text, meskipun yang kita ketikkan adalah

angka.

Contoh:

Gambar 1. 16 - Data dengan Format TEXT


------------------------------------------------------------------------------------------------------------- 15

3. Custom Format

Custom Format digunakan untuk mengatur tampilan angka maupun huruf

yang jenis pengaturannya belum difasilitasi oleh Excel.

Sebagai contoh, jika kita ingin menampilkan nomor telfon 215555555 (format

NUMBER) ke dalam format (021) 555-5555, maka kita membutuhkan fasilitas

custom format ini untuk mewujudkannya.

Ca a a adalah de ga e asukka otasi # ### - #### pada isia

untuk Type sebagaimana yang dapat dicermati pada Gambar 1. 17. Section

Sample pada Gambar 1. 17 di bawah ini menunjukkan format hasil dari

pengaturan ini yaitu: (021) 555-5555 sesuai dengan rencana semula. Namun

perlu kita ingat bahwa terlepas tampilannya bagi kita, Excel akan

memperlakukannya sebagai angka 215555555.

Gambar 1. 17 – Custom Format 1

Contoh selanjutnya, kita ingin membuat Nomor Pokok Mahasiswa (NPM)

untuk mahasiswa baru dengan nomor dimulai dari misalnya 1234/BPPK/2012.

Penomoran ini juga dapat dipermudah menggunakan Custom Format.

Untuk melakukannya cukup dengan memasukkan nomor urut awal hingga ke

nomor urut akhir tanpa embel-embel /BPPK/ . Kete a ga /BPPK/

akan kita tambahkan melalui fasilitas Custom Format dengan tambahan string:

# /BPPK/ .


------------------------------------------------------------------------------------------------------------- 16

OUTPUT

CUSTOM FORMAT

Gambar 1. 18 – Custom Format 2

Banyak sekali pengaturan manual (custom) yang bisa kita lakukan yang

tidak mungkin kita bahas di modul ini satu per satu. Kuncinya adalah seberapa

banyak waktu yang bersedia kita investasikan untuk mengembangkan berbagai

custom format yang sesuai dengan kebutuhan kerja kita di lapangan.

4. Keyboard Shortcuts

Excel juga menyediakan seperangkat fasilitas Keyboard Shortcut untuk

memudahkan kita mengatur format dari cell atau sekelompok Cell/Range.

Berikut adalah daftar shortcut dimaksud:

Tabel 1. 1 - Daftar Keyboard Shortcuts untuk Format Cells

No Keyboard Shortcut Fungsi

1 CTRL + SHIFT + ~ Kembali ke Format General

2 CTRL + SHIFT + 1 Format Angka (Number) dengan 2 digit decimal

3 CTRL + SHIFT + @ Format Waktu (Jam)

4 CTRL + SHIFT + # Format Tanggal

5 CTRL + SHIFT + $ Format Mata Uang $ (Currency)

6 CTRL + SHIFT + % Format Persen (tanpa desimal)

7 CTRL + SHIFT + ^ Format Scientific


------------------------------------------------------------------------------------------------------------- 17

4) Navigasi dalam Excel

Navigasi adalah proses berpindahnya kita dari satu cell ke cell lainnya, dari

satu worksheet ke worksheet lainnya, atau dari satu workbook ke workbook lainnya.

Dengan bantuan mouse, perpindahan ini tidak menjadi masalah jika kita bekerja

dengan data yang jumlahnya sedikit. Namun akan jadi masalah besar jika kita

bekerja dengan data yang jumlahnya sangat banyak, mengingat perpindahan dari

satu cell ke cell lainnya, bisa jadi membutuhkan waktu yang relatif sangat banyak

dan yang pasti mengesalkan.

Untuk mengatasi permasalahan ini, Excel telah menyiapkan sejumlah alat bantu,

berupa Keyboard Shortcuts yang dapat kita cermati pada tabel berikut:

Tabel 1. 2 - Keyboard Shortcuts untuk Navigasi

No Keyboard Shortcut Fungsi

1 CTRL + Home Pindah ke Cell A1

2 CTRL + End Pindah ke Cell Paling Kanan Bawah dari Worksheet

3 CTRL + Page Up Pindah ke Worksheet Sebelumnya

4 CTRL +Page Down Pindah ke Worksheet Setelahnya

5 SHIFT +F11 New Worksheet

6 Alt+Tab Pindah ke Program/Aplikasi/Workbook Lain

7 Alt+Shift+Tab Pindah ke Program/Aplikasi/Workbook Lain

8 Ctrl+Esc Menampilkan Windows Start Menu.

9 Home Kembali ke awal baris (kolom A)

10 F5 Membuka Go To Dialog

11 CTRL + F atau SHIFT + F5

Membuka Find Dialog

12 CTRL + H Membuka Search and Replace Dialog

13 CTRL + . (titik) Pindah ke sudut-sudut dari sebuah range

14 End + Tanda Panah Pindah ke baris/kolom terjauh sebelum cell kosong berikutnya


------------------------------------------------------------------------------------------------------------- 18

Mengingat banyaknya shortcuts yang disediakan Excel untuk membantu

mempercepat navigasi kita ketika bekerja dengan Excel, pada bagian lampiran kami

sediakan daftar Keyboard Shortcut lengkap yang dapat dijadikan acuan dalam

bekerja.

b. Rangkuman

Kita dapat mengaitkan (linked) beberapa worksheet dalam satu workbook atau

mengaitkan satu workbook dengan beberapa workbook dalam menyelesaikan

suatu pekerjaan.

Pemahaman tentang alamat Cell yang relatif dan absolut sangat mempengaruhi

kecepatan dan efektivitas kita dalam bekerja dalam formula, khususnya jika

formula yang telah kita susun untuk suatu Cell tertentu harus disalin ke Cell

lainnya.

Pada dasarnya format data yang kita inputkan ke dalam suatu cell atau range

terdiri atas tiga jenis yaitu data karakter (string), data angka, dan data logika.

Penggunaan mouse dalam bekerja dengan Excel memang disarankan, namun di

banyak kesempatan penggunaan Keyboard Shortcut akan sangat

mempersingkat waktu kerja khususnya jika kita berurusan dengan data yang

jumlahnya besar.

c. Tes Formatif Kegiatan Belajar 1

Pilihlah B jika menurut Anda pernyataan di bawah ini benar dan S jika salah.

1. B S Jenis data yang dikenal Excel terdiri atas dua, yaitu data teks (string)

dan data angka (number)

2. B S Jumlah worksheet maksimal dalam satu workbook adalah 30

3. B S Alamat Cell $A18 menunjukkan bahwa baris pada cell tersebut berada

dalam posisi absolute

4. B S Alamat Cell Data1!B3000 menunjukan bahwa cell B3000 berada dalam

o ksheet a g e a a Data .

5. B S Dengan menekan tombol CTRL + PgDown kita dapat berpindah dari

satu worksheet ke worksheet lainnya yang berada di sisi sebelah kiri

worksheet yang aktif.


------------------------------------------------------------------------------------------------------------- 19

6. B S Untuk mengubah data angka kembali ke format General dapat

dilakukan dengan menekan tombol CTRL + ~

7. B S Sebuah worksheet dapat terdiri atas beberapa workbook.

8. B S Notasi huruf dari sebuah Cell mewakili kolom dan notasi angka

mewakili baris dari sebuah worksheet

9. B S Custom Format digunakan untuk mengubah format data pada sebuah

Cell atau range dengan format yang tidak tersedia dalam format

standar yang ditawarkan Excel

10. B S Dengan memasukkan format ###-###-### maka angka 123456789

akan disajikan sebagai 123-456-7890

d. Umpan Balik dan Tindak Lanjut

Periksalah jawaban anda dengan kunci jawaban tes formatif yang ada di

bagian belakang modul ini. Hitunglah jumlah jawaban anda yang sesuai dengan

kunci jawaban, kemudian gunakan rumus di bawah ini untuk mengetahui tingkat

penguasaan anda terhadap materi.

Penjelasan tingkat penguasaan:

91 % - 100 % : Amat Baik

81 % - 90.99 % : Baik

71 % - 80.99 % : Cukup

61 % - 70.99 % : Kurang

0 % - 60.99 % : Amat Kurang

Jika anda telah mencapai tingkat penguasaan 80% keatas, Saudara dapat

meneruskan dengan materi selanjutnya. Tetapi jika nilai anda kurang dari 80%,

maka anda harus mengulangi materi ini terutama yang belum anda kuasai.

KEGIATAN BELAJAR 2 : MENGHITUNG DENGAN EXCEL

------------------------------------------------------------------------------------------------------------- 20

2. Kegiatan Belajar 2 : MENGHITUNG DENGAN EXCEL


1) Memahami Formula

a) Pemahaman Dasar tentang Formula

Jika kita melihat data yang disajikan dalam sebuah worksheet, sulit bagi

kita untuk membedakan mana yang berupa data statik dan mana yang berupa

formula. Cara termudah untuk melakukannya adalah dengan memilih cell

tertentu untuk memastikan apakah isi cell-nya berupa data statik atau bukan.

Formula Bar

Gambar 2. 1 – Data Static

Formula Bar

Gambar 2. 2 – Data Formula

Contoh:

Berdasarkan informasi yang tampak pada formula bar, maka Gambar 2. 1

berisikan data statik (4) dan Gambar 2. 2 berisikan data formula (D1 + 4),

meskipun keduanya menyajikan informasi yang sama yaitu angka 4, namun

kedua cell ini pada dasarnya tidaklah sama.

Setelah mengikuti pembelajaran ini peserta diklat diharapkan dapat :

mengusai pengetahuan dasar fungsi-fungsi yang ada dalam Excel ; dan

menganalisis masalah dan menentukan langkah-langkah penyelesaian

masalah yang harus ditempuh dan menentukan fungsi apa saja yang dapat

digunakan untuk menyelesaikan masalah tersebut dalam pengelolaan

pekerjaan sehari-hari di tempat kerja

INDIKATOR


------------------------------------------------------------------------------------------------------------- 21

Formula pada dasarnya berisikan kombinasi alamat cell, operator

matematika, fungsi Excel, dan fungsi yang kita kembangkan (User Defined

Functions) sendiri yang dimaksudkan untuk membantu kita dalam

menyelesaikan suatu permasalahan, khususnya menyangkut penginputan,

pengolahan, dan pelaporan suatu data/informasi. Dan karena sifatnya ini, kita

tidak dapat memastikan berapa banyak jumlah formula yang dapat

dikembangkan, yang jelas milyaran jumlahnya, jika kita tidak ingin menyebutnya

unlimited.

Selanjutnya, jika kita perhatikan secara seksama anatomi sebuah formula,

maka tampak bahwa sebuah formula selalu dimulai dengan tanda sama dengan

= dan diikuti oleh sejumlah argumen yang melengkapi formula dimaksud.

Umumnya argumen dimaksud melibatkan operasi matematika mulai yang

paling sederhana hingga yang paling rumit. Pada tabel berikut disajikan sejumlah

operator matematika yang sering digunakan dalam Excel:

Tabel 2. 1 – Operator Matematika

No Simbol Fungsi

1 + Tambah

2 - Kurang

3 * Kali

4 / Bagi

5 ^ Eksponen

6 () Membatalkan Aturan Perhitungan

7 & Joining Text

8 > Lebih dari

9 < Kurang Dari

10 >= Lebih dari atau Sama dengan

11 <= Kurang dari atau Sama dengan

12 <> Tidak Sama dengan

13 = Sama dengan

14 - (Unary Minus) Tanda Negatif


------------------------------------------------------------------------------------------------------------- 22

Dalam operasi Matematika dikenal juga istilah Aturan (hirarki)

Perhitungan, yang uraiannya sebagai berikut:

1) Tanda Minus harus diperhitungkan terlebih dahulu

2) Tanda Exponen harus diperhitungkan setelah Tanda Minus

3) Tanda Perkalian dan Pembagian harus dikerjakan menyusul perhitungan

Tanda Eksponen dengan urutan dari kiri ke kanan.

4) Tanda Penambahan dan Pengurangan harus dikerjakan setelah perhitungan

perkalian atau pembagian, dengan urutan dari kiri ke kanan.

Contoh: 3 x 2 + 5 hasilnya harus 11 [(3 x 2) + 5] dan bukan 21 [3 x (2 + 5)]

Berikut adalah sejumlah fakta tentang sebuah formula dalam satu Cell Excel:

1) Bentuk Formula pali g sede ha a adalah ala at Cell, isal =A110

2) Jumlah karakter maksimal dalam satu formula adalah 8.192 karakter

3) Sebuah formula maksimal memuat 64 pencabangan (nesting)

4) Jumlah argumen maksimal yang diperkenankan dalam satu fungsi adalah

255

5) Formula dapat di-copy dari satu cell awal ke satu atau beberapa cell lainnya.

b) Sifat Relatif dari Formula

Gambar 2. 3 - Sifat Relatif dari Formula

Formula yang ada pada Cell C3 di atas pada prinsipnya mengatakan:

Kalika isi Cell ya g erjarak dua ell di se elah kiri A3 POSISIKU C3

dengan isi Cell yang berjarak satu cell di sebelah kiri posisiku B3 .

Jika kita ingin Cell C4 hingga Cell C6 bahkan Cell C10.000 memuat aturan

(formula) yang sama, maka kita tidak harus mengetikkan formula dimaksud


------------------------------------------------------------------------------------------------------------- 23

berulang kali, cukup dengan meng-copy formula yang ada di Cell C3 ke Cell C4

s.d. C6, secara otomatis akan melaksanakan ketentuan Kalika isi Cell ya g

berjarak dua cell di sebelah kiri POSISIKU dengan isi Cell yang berjarak satu

ell di se elah kiri posisiku ke seluruh cell yang menjadi target peng-copy-an.

Hasil dari peng-copy-an tersebut adalah sebagai berikut:

Gambar 2. 4 – Sifat Relatif dari Formula (Relative Referencing)

Dapat kita lihat bahwa formula pada Cell C4 berubah dari sebelumnya =A3*B3 di

cell C3 menjadi =A4*B4 di cell C4. Kemampuan Excel untuk secara otomatis

mengubah A3 menjadi A4 atau B3 menjadi B4 dikenal dengan Kemampuan

Relative Referencing.

c) Menghilangkan Sifat Relatif dari Formula

Kemampuan Relative Referencing ini tentunya sangat powerful dan sangat

memudahkan kita dalam bekerja, namun ada kalanya, kita tidak menghendaki

sifat ini muncul, misalnya dalam kasus di mana kita menggunakan satu

Cell/Range tertentu sebagai acuan tunggal atau cell yang berada dalam kolom

atau baris tertentu sebagai acuan.

Contoh:

Gambar 2. 5 - Sifat Relatif Tak Selamanya Berhasil


------------------------------------------------------------------------------------------------------------- 24

Mencermati Gambar 2. 5 di atas, kita melihat bahwa Cell Tarif PPN

merupakan acuan tunggal dalam menghitung besaran PPN yang dikenakan

terhadap barang yang ada pada baris 2 s.d. 6. Untuk itu, adalah tepat kiranya

jika pada perhitungan PPN pada Cell D3 kita menggunakan formula =C3*C1 di

mana Cell C1 berisikan data besaran Tarif PPN.

Formula ini sukses digunakan untuk menghitung PPN pada Cell D3, namun

menemui kegagalan pada Cell D4 dengan hasil #VALUE (alias Error).

Setelah kita teliti, ternyata penyebab kegagalan tersebut adalah karena

cell yang digunakan sebagai acuan tarif PPN bergeser dari Cell D1 menjadi Cell

D2 akibat kemampuan Relative Reference yang dimiliki Excel.

Untuk mengatasi kendala ini, Excel menyediakan fasilitas yang dikenal

dengan Kemampuan Absolute Referencing yang sifatnya bertolak belakang

dengan kemampuan Relative Referencing, caranya dengan menambahkan tanda

$ pada agia -bagian dari alamat Cell/Range. Pemberian tanda $ ini dapat

dilakukan secara manual, yaitu diketikkan langsung atau menggunakan TOMBOL

F4, baik saat pertama kali menginput maupun saat mengedit formula.

Jenis Absolute Referencing ini ada 2, yaitu

1) Absolute Reference, baik kolom dan baris dibuat absolut, contoh $D$4.

2) Mixed Reference, hanya kolom atau baris (salah satu) yang dibuat absolut

(D$4 atau $D4).

Kembali ke contoh kita yang ada pada Gambar 2. 5, permasalahan kita dapat

diselesaikan dengan mengganti formula pada Cell D3 dari yang semula =C3*D1

menjadi =C3*$D$1, dengan demikian ketika formula ini di-copy ke Cell lain,

acuan ke Cell D1 tidak akan berubah sehingga perhitungan PPN akan menjadi

benar sebagaimana seharusnya.

Sebuah pertanyaan besar sebenarnya, Kapan kita menggunakan acuan

absolut, mixed, maupun relatif terhadap sebuah Cell atau Range? Diskusikan

di kelas dengan instruktur maupun rekan satu kelas.


------------------------------------------------------------------------------------------------------------- 25

d) Meng-Copy Formula

Formula yang kita ketikkan pada cell tertentu dalam Excel dapat di-copy ke

satu/lebih cell lainnya dengan salah satu cara berikut ini:

1) Menggunakan tombol CTRL + ENTER

Cara ini biasanya efektif untuk melakukan input secara bersamaan, baik

angka maupun formula, terhadap sekelompok cell.

Adapun langkah kerjanya adalah sebagai berikut:

a. Sorot kelompok cell yang menjadi target input.

Gambar 2. 6 – Sorot Target Kelompok Cell

b. Ketikkan nilai atau formula yang diinginkan

Ketika mengetikkan nilai atau formula dimaksud, tampak pengaruhnya

hanya pada cell paling atas, yaitu dalam hal ini adalah Cell D3.

Gambar 2. 7 – Ketikkan Nilai atau Formula yang Diinginkan


------------------------------------------------------------------------------------------------------------- 26

c. Tekan tombol CTRL + ENTER secara bersamaan.

Gambar 2. 8 – Tekan Tombol Ctrl + Enter Bersamaan

2) Menggunakan Teknik Dragging The Fill Handle

Apa yang dimaksud dengan Fill Handle? Untuk memudahkan kita

dalam memahami apa yang dimaksud dengan Fill Handle di sini, ada baiknya

jika kita menyimak ilustrasi di bawah ini:

Fill Handle

Gambar 2. 9 – Fill Handle

Yang disebut Fill Handle adalah kotak kecil yang berada pada pojok kanan

bawah dari cell, lihat Gambar 2. 9, atau kelompok cell, lihat Gambar 2. 8.

Teknik Dragging the Fill Handle ini meliputi dua cara:

Drag the Fill Handle

Double Click the Fill Handle

a) Teknik Drag the Fill Handle

Teknik ini sangat cocok jika jumlah record tidak melebihi tampilan

satu halaman layar komputer, namun jika jumlah record sangat banyak,

teknik ini sangat merepotkan.


------------------------------------------------------------------------------------------------------------- 27

Teknik ini dilakukan dengan menarik kotak kecil yang ada pada

pojok kanan bawah cell dengan bantuan mouse hingga ke baris/kolom

yang menjadi target penyalinan.

b) Teknik Double Click the Fill Handle

Teknik ini sangat cocok jika melibatkan jumlah record yang sangat

banyak. Teknik ini dieksekusi dengan mendouble-click Fill Handle yang

ada pada pojok kanan bawah cell dengan bantuan mouse dan secara

otomatis akan mengcopy nilai/formula dari cell tersebut ke cell

terbawah dari database.

Dari simulasi yang dilakukan, Gambar 2. 10, ternyata teknik

double click ini tidak sepenuhnya efektif, jika saat digunakan ternyata

terdapat satu record yang kosong sebelum akhir database, dan kalau hal

ini terjadi, proses peng-copy-an akan berhenti satu row sebelum row

yang kosong tersebut.

Gambar 2. 10 – Simulasi Teknik Double Click Fill Handle


------------------------------------------------------------------------------------------------------------- 28

Untuk mencegah kesalahan yang tidak perlu, ketika menggunakan

teknik ini pastikan bahwa kita telah berhasil meng-copy dari awal hingga

ke akhir data, dengan menguji isi kolom D mulai dari Cell D3 dan

seterusnya menggunakan kombinasi tombol End diikuti Tombol Anak

Panah Bawah (tidak bersamaan).

2) Bekerja dengan Fungsi Excel

Secara garis besar, fungsi yang ada pada Excel ada dua jenis:

Fungsi Standar

Fungsi yang disediakan di dalam Excel

User Defined Function

User Defined Function adalah fungsi yang kita kembangkan sendiri

menggunakan VBA (Visual Basic Application) yang merupakan salah satu fasilitas

Powerful dari Microsoft Office.

Fungsi Standar ini terdiri atas beberapa kategori, yaitu kategori Financial, Logical,

Text, Date& Time, Lookup & Reference, Math & Trigonometry, Statistical,

Engineering, Cube, Information, dan Compatibility. Hal-hal yang terkait dengan

penggunaan fungsi-fungsi dimaksud dapat diakses melalui Tab Formula

sebagaimana diilustrasikan pada gambar berikut:

Gambar 2. 11 - Formulas Tab in Excel

a) Anatomi dari Sebuah Fungsi

Sebuah fungsi harus dituliskan mengikuti aturan atau tata penulisan

tertentu agar dapat berfungsi secara efektif. Cara penulisan sebuah fungsi ini

dise ut se agai Syntax .


------------------------------------------------------------------------------------------------------------- 29

Syntax yang berlaku umum adalah sebagai berikut:

=NAMAFUNGSI(Argumen1,Argumen2,Argumen3) atau

=NAMAFUNGSI(Argumen1, Argumen2, Argumen3)*

* Pada Syntax kedua, antara argumen1, argumen2, dst menggunakan spasi,

sementara pada syntax pertama tidak menggunakan spasi kosong.

Tanda kurung buka dan kurung tutup pada Syntax tersebut merupakan sebuah

keharusan bagi sebuah fungsi, bahkan untuk sebuah fungsi yang tidak

membutuhkan argumen sekalipun, contoh: Row(), Column(), Today().

b) Menemukan Fungsi yang Tepat

Dengan tersedianya banyak sekali fungsi yang siap pakai bahkan Excel

telah mengelompokkan fungsi-fungsi dimaksud dalam kategorisasi yang

memudahkan kita untuk mencarinya, namun berdasarkan pengalaman, akan

lebih baik jika di waktu senggang yang kita punya, kita melakukan eksplorasi

atas beragam fungsi yang tersedia, sehingga pada saat dibutuhkan kita tahu

fungsi mana yang kira-kira memenuhi kebutuhan kita.

Kenapa kira-kira? Karena eksplorasi yang kita lakukan tidak bertujuan

untuk menghafal fungsi tertentu, tujuan eksplorasi adalah untuk memberikan

pengalaman mental pada diri kita bahwa fungsi tertentu tersedia di Excel.

Kenapa kita tidak perlu menghafalkan fungsi yang kita temukan? Karena Excel

menyediakan fasilitas sebagai berikut:

1) Fasilitas Auto Complete

Ketika kita mengetikkan huruf awal dari fungsi, melalui tooltips-nya,

Excel langsung menawarkan sejumlah fungsi yang namanya dimulai dengan

huruf yang kita ketikkan.


------------------------------------------------------------------------------------------------------------- 30

Keterangan atas

fungsi yg disorot

Fungsi alternatif yg

ditawarkan Excel

berdasarkan

masukan pada

formula bar/Cell

Gambar 2. 12 – Fungsi yang Ditawarkan Saat Mengetikkan Formula

Selanjutnya kita tinggal memilih fungsi mana yang kita butuhkan

menggunakan tombol panah atas atau bawah, dan ketika kita berhasil

menemukan yang kita cari, cukup tekan tombol TAB untuk memilihnya,

misalnya fungsi SUMIF.

Tekan TAB untuk memilih

Jika terasa mengganggu, keterangan

atas fungsi ini dapat dipindah ke bawah,

gunakan mouse untuk menggesernya

Gambar 2. 13 – Mencari Fungsi menggunakan Tombol Panah


------------------------------------------------------------------------------------------------------------- 31

2) Fasilitas Tooltips yang Powerful

Setelah sebuah Fungsi dipilih, dalam hal ini adalah fungsi SUMIF,

menggunakan tombol TAB, selanjutnya Excel akan menyajikan tampilan

berikut:

tooltips

Gambar 2. 14 – Fungsi dan Tooltips-nya

Jika kita mencermati tooltips yang disajikan tampak bahwa Excel

memberikan informasi sederhana kepada kita tentang bagaimana fungsi ini

seharusnya digunakan. Dari informasi pada tooltips tampak bahwa untuk

menggunakan fungsi SUMIF, pertama yang harus disediakan adalah range

database yang menjadi acuan, kemudian dibutuhkan kriteria yang akan diuji

terhadap database, dan terakhir kita membutuhkan satu kolom data yang

berisikan angka-angka yang akan dijumlah berdasarkan kriteria yang

ditetapkan.

Posisi tooltips sebagaimana tampak pada Gambar 2. 14 dapat digeser

atau dipindah menggunakan mouse ke posisi lainnya, sekiranya dirasa

mengganggu dan menyulitkan kita dalam bekerja (lihat juga Gambar 31).

c) Menggunakan Autosum

Autosum merupakan salah satu fungsi yang paling sering digunakan oleh

para pengguna Excel, mulai dari tingkatan pemula hingga yang profesional.

Menu Autosum dapat ditemui di Formula Tab.


------------------------------------------------------------------------------------------------------------- 32

Jika tulisan AutoSum kita di-klik, Excelakan menawarkan pilihan sebagaimana

tampak pada gambar berikut:

klik

Gambar 2. 15 – Fasilitas Autosum

Kita selanjutnya dapat memilih apakah kita akan menjumlah data yang

ada (Sum), menghitung rata-rata (Average), menghitung jumlah data yang

bersifat angka (Number), menentukan nilai Maksimum data (Max) atau

menentukan nilai minimum data (Min).

Namun jika kita langsung meng-klik tanda Sigma (Σ), Excel akan

menampilkan fungsi sum dengan tampilan sebagai berikut:

Gambar 2. 16 - Autosum

Keyboard Shortcuts untuk fasilitas autosum (Σ) adalah tombol ALT + = yang

ditekan secara bersamaan.


------------------------------------------------------------------------------------------------------------- 33

d) Fungsi Sehari-hari

1. Mengenal Format Tanggal (Date) dan Waktu (Time) yang Digunakan Excel

Sebelum membahas berbagai fungsi yang ada, ada baiknya jika

meluangkan waktu untuk memahami format tanggal dan waktu yang

digunakan Excel. Perlu kita ketahui bersama bahwa penanggalan dalam

Excel dimulai dari tanggal 1 Januari 1900 dan berakhir 31 Desember 9999.

Data tanggal yang kita inputkan ke Excel, misalnya 8/17/2012 (17 Agustus

2012), akan dibaca Excel sebagai angka 41138 yang pada dasarnya

merupakan jumlah hari antara tanggal 1 Januari 1900 sampai dengan 17

Agustus 2012.

Secara default, Excel akan menampilkan data tanggal tersebut dalam

format tanggal (Date Format), yang bentuk defaultnya berupa m/d/yyy,

sehi gga aka ta pak se agai / / . Hu uf ela a gka a gka

ula , hu uf d ela a gka a gka ha i, da hu uf ela a gka

angka tahun dalam empat digit.

Terkadang, dalam operasi matematika yang melibatkan data tanggal,

hasil operasi ditunjukkan Excel dalam format angka (Number) dan bukan

dalam format tanggal sebagaimana yang kita harapkan, misalnya,

8/17/2012 + 1, menghasilkan angka 41139 dan bukan 8/18/2012. Jika hal ini

terjadi, jangan panik, cukup pastikan bahwa format cell/kolomnya telah

dalam format tanggal (date), gunakan CTRL + 1.

Untuk mengatur lebih lanjut tampilan data tanggal tersebut kita

dapat memanfaatkan Fasilitas Custom Format yang diakses dengan

menekan tombol CTRL + 1, yang pengaturan format tanggalnya dapat

dicermati melalui tabel berikut:


------------------------------------------------------------------------------------------------------------- 34

Tabel 2. 2 – Format Tanggal

No Format Keterangan

1 mm Menampilkan angka bulan dalam dua digit, angka bulan yang kurang dari 10 akan disajikan dengan angka nol mendahuluinya, misalnya September akan disajikan sbg. 09.

2 m Menampilkan angka bulan apa adanya, jika satu digit ditampilkan sebagai satu digit pula.

3 mmm Menampilkan singkatan nama bulan, yaitu tiga huruf pertama dari tiap nama bulan

4 mmmm Menampilkan nama bulan secara lengkap

5 dd Menampilkan angka hari dalam dua digit, angka hari yang kurang dari 10 akan disajikan dengan angka nol mendahuluinya, misal tanggal 9 akan disajikan sebagai 09

6 d Menampilkan angka bulan apa adanya, jika satu digit ditampilkan sebagai satu digit pula

No Format Keterangan

7 ddd Menampilkan singkatan nama hari, yaitu tiga huruf pertama dari tiap nama hari

8 dddd Menampilkan nama hari dalam satu minggu secara lengkap

9 yy Menampilkan dua digit angka tahun

10 yyy Menampilkan angka tahun secara lengkap

Berdasarkan informasi yang disajikan dalam Tabel 2. 2 di atas, kita akan

mencoba mengatur format tanggal menggunakan Custom Format.


------------------------------------------------------------------------------------------------------------- 35

Format tanggal yang baru

Gambar 2. 17 – Custom Format untuk Tanggal dd-mmmm-yyy

Pada Gambar 2. 17 di atas, kita mencoba mengubah tampilan data

tanggal denganformat dd-mmmm-yyy untuk menghasilkan tampilan 17-

August-2012.

Contoh lain penggunaan format Tanggal di atas adalah:

=te t , dd-mm- dengan hasil adalah 17-08-2012.

Satu pertanyaan, bagaimana menyajikan tanggal dimaksud dalam format

Bahasa Indonesia yang Baku?

Serupa dengan data tanggal, data waktu dalam Excel dicerminkan

dalam bentuk angka desimal. Logikanya, ada 24 jam dalam satu hari, oleh

karenanya pukul 12 Siang dilambangkan dengan angka 0,5, sementara

pukul 6 Pagi dilambangkan dengan angka 0,25.

Umumnya data waktu ini disajikan bersama data tanggal, misalnya Tanggal

17 Agustus 2012, Pukul 9 Pagi, disajikan oleh Excel sebagai 41138.375.


------------------------------------------------------------------------------------------------------------- 36

Berikut adalah daftar format waktu, dengan sampel pukul 20:05:07

Tabel 2. 3 – Format Waktu

No Format Output Keterangan

1 H 20 Angka jam ditampilkan apa adanya 1 atau 2 digit

2 Hh 20 Angka jam ditampilkan dalam dua digit: 01,02,..,24

3 h:mm 20:05 Angka jam ditampilkan apa adanya 1 atau 2 digit

4 hh:mm 20:05 Angka jam ditampilkan dalam dua digit: 01,02,..,24

5 hh:mm:ss 20:05:07 Angka jam, menit, dan detik masing-masing dua digit

6 h:m:s 20:5:7 Angka jam, menit, dan detik disajikan apa adanya

7 S 7 Angka detik disajikan apa adanya

8 Ss 07 Angka detik disajikan dalam dua digit

9 h:mm AM/PM

8:05 PM Angka jam disajikan apa adanya plus AM atau PM

10 [h]:mm 20:55 Jumlah hari ikut diperhitungkan sebagai jam

11 [m] 1205 Jumlah hari penuh dan jam ikut dihitung sebagai menit

12 [s] 72307 Jumlah hari, jam, dan menit ikut dihitung sbg. Detik

13 mm:ss:00 05:07:00 Menampilkan data per seratus detik

Contoh penggunaan format waktu adalah:

=te t : : ,[ ] dengan hasil 1205.


------------------------------------------------------------------------------------------------------------- 37

2. Fungsi NOW dan TODAY

Syntax: =NOW() dan =TODAY()

Fungsi NOW() akan menghasilkan informasi tanggal dan waktu,

sedangkan fungsi TODAY() hanya akan menghasilkan informasi tanggal.

Tanggal maupun waktu dimaksud merupakan tanggal dan waktu Operating

System (OS) yang kita gunakan.

Agar tidak menimbulkan masalah yang tidak perlu, khususnya jika

hasil dari kedua fungsi ini digunakan untuk perhitungan dan pelaporan, ada

baiknya sebelum fungsi ini digunakan, kita memastikan terlebih dahulu

bahwa tanggal dan waktu OS kita telah benar.

Contoh:

=NOW() akan menghasilkan informasi berikut: 8/1/2012 22:07

=TODAY() akan menghasilkan informasi berikut: 8/1/2012

3. Fungsi YEAR, MONTH, DAY, HOUR, MINUTE, DAN SECOND

Syntax:

=YEAR(date) menghasilkan angka tahun dari tanggal dalam empat

digit

=MONTH(date) menghasilkan angka bulan dari tanggal, 1 s.d. 12

=DAY(date) menghasilkan angka hari dari tanggal, 1 s.d. 31

=HOUR(date) menghasilkan angka jam dari tanggal, 1 s.d. 24

=MINUTE(date) menghasilkan angka menit dari tanggal, 1 s.d. 60

=SECOND(date) menghasilkan angka detik dari tanggal, 1 s.d. 60

Contoh:

=HOUR / / : aka e ghasilka ilai

=DAY / / : aka e ghasilka ilai


------------------------------------------------------------------------------------------------------------- 38

atau selengkapnya:

Gambar 2. 18 – Contoh Fungsi Tanggal dan Waktu

Latihan:

Tentukan Angka Tahun, Bulan, dan Tanggal dari data berikut ini

menggunakan fungsi YEAR, MONTH, DAN DAY:

No. Data Tanggal Keterangan

1 1/12/2012 Diinput dengan Format Bahasa Indonesia 2 6/7/2012 Diinput dengan Format Bahasa Inggris 3 45555 Dii put de ga Fo at Ge e al 4 18/9/2012 Diinput dengan Format Bahasa Indonesia

4. Fungsi DATE

Syntax:

=DATE(year,month, day)

Contoh:

=DATE(2012,8,17) akan menghasilkan nilai 8/17/2012 atau 41138

=DATE(YEAR(TODAY()),8,17) juga akan menghasilkan nilai 8/17/2012

atau 41138

Catatan: pada contoh kedua, kita menggunakan formula=YEAR(TODAY())

untuk mewakili angka tahun dan bukan angka sebagaimana contoh

pertama. Formula =YEAR(TODAY()) akan menghasilkan nilai 2012, sehingga

kedua formula menunjukkan hasil yang identik.


------------------------------------------------------------------------------------------------------------- 39

Latihan:

Berikut adalah data tanggal yang diinput dalam format Bahasa Indonesia.

Tugas anda adalah mengubah data tanggal tersebut ke dalam format yang

dimengerti oleh Excel menggunakan fungsi DATE:

No Nilai

1 17/8/2012 2 21/12/2012 3 1/10/2012 4 30/9/2012 5 1/6/2012

5. Fungsi TIME

Syntax:

=TIME(hour,minute,second)

Contoh:

=TIME(20,5,7) akan menghasilkan nilai 20:05:07

=TIME(HOUR(NOW()),5,7) juga akan menghasilkan nilai 20:05:07

Catatan: pada contoh kedua, kita menggunakan formula=HOUR(NOW())

untuk mewakili angka jam dengan catatan jam sistem pada saat formula

tersebut digunakan menunjukkan pukul 20 (pukul 8 malam).

Latihan:

Konversikan Nilai-nilai berikut ke dalam format waktu menggunakan fungsi

TIME.

No Nilai

1 0.352048536 2 0.393226872 3 0.593730651 4 0.824227857 5 0.826215213


------------------------------------------------------------------------------------------------------------- 40

6. Fungsi WEEKDAY

Syntax:

=WEEKDAY(serial_number,return_type)

Fungsi ini akan memberikan keluaran berupa angka yang menunjukkan

index hari dalam satu minggu, misalnya angka 1 untuk Minggu hingga angka

7 untuk Sabtu.

Keterangan:

serial_number adalah data tanggal

return_type adalah angka yang menentukan data keluaran

jika return_type bernilai 1 atau blank, maka hari Minggu bernilai 1

(arabic system)

jika return_type bernilai 2, maka hari Senin bernilai 1, Minggu

bernilai 7


bernilai 6

Contoh:

=WEEKDAY(41138,1)atau =WEEKDAY(41138)akan menghasilkan nilai 6

atau tanggal 17 Agustus 2012 jatuh pada hari Jumat.

Latihan:

Tentukan tanggal berikut jatuh pada hari apa, dihitung menggunakan fungsi

WEEKDAY:

No Nilai

1 44774 2 44274 3 45247 4 46412 5 43532


------------------------------------------------------------------------------------------------------------- 41

7. Fungsi WEEKNUM

Syntax:

=WEEKNUM(serial_number,return_type)

Fungsi ini memberikan keluaran berupa angka yang menunjukkan minggu

keberapa sebuah tanggal dalam satu tahun.

Keterangan:

serial_number adalah data tanggal

return_type adalah angka yang menentukan data keluaran

jika return_type bernilai 1 atau blank, maka hari Minggu bernilai 1

(arabic system)


bernilai 7

Contoh:

=WEEKNUM(41138,1) atau =WEEKNUM(41138) akan menghasilkan nilai

33 atau tanggal 17 Agustus 2012 jatuh pada minggu ke-33 dalam tahun

2012.

Latihan:

Tentukan tanggal berikut jatuh pada minggu ke berapa dalam tahun yang

bersangkutan, dihitung menggunakan fungsi WEEKNUM:

No Nilai

1 44774 2 44274 3 45247 4 46412 5 43532


------------------------------------------------------------------------------------------------------------- 42

8. Fungsi YEARFRAC

Syntax:

=YEARFRAC(start_date,end_date,basis)

Fungsi ini memberikan keluaran berupa angka yang menunjukkan hasil bagi

dari selisih antara dua tanggal dibagi dengan jumlah hari dalam setahun.

Keterangan:

Start_date and End_date adalah data tanggal, dimana start_date >

end_date

Basis menunjukkan nilai penyebut yang digunakan

Jika basis bernilai 0 atau blank, maka Excel akan menggunakan pola

US 30/360

Jika basis bernilai 1, maka Excel akan menggunakan pola

actual/actual

Jika basis bernilai 2, maka Excel akan menggunakan pola actual/360

Jika basis bernilai 3, maka Excel akan menggunakan pola actual/365

Jika basis bernilai 4, maka Excel akan menggunakan pola European

30/360

Contoh:

=YEARFRAC(41145,41138,1) akan menghasilkan nilai 0.019125683,

dimana basis yang digunakan adalah actual/actual (basis 1)

Latihan:

Tentukan usia pegawai yang ada pada daftar berikut, dihitung

menggunakan fungsi YEARFRAC (tanpa pembulatan):

No Tanggal Lahir

1 25973 2 29490 3 31925 4 27055 5 30975


------------------------------------------------------------------------------------------------------------- 43

9. Fungsi TEXT

a) Fungsi TEXT

Syntax:

=TEXT(value, format_text)

Fungsi ini digunakan untuk mengubah data angka menjadi data teks

(string) dengan mengikuti format yang ditentukan.

Keterangan:

Value adalah angka yang akan dikonversi menjadi text

Format_text adalah format penyaian angka dimaksud

Contoh:

=te t : : ,[ ] dengan hasil 1205 (text).

Latihan:

Tentukan tanggal, bulan, dan tahun dari data berikut ini menggunakan

fungsi TEXT:

No Nilai

1 44774 2 44274 3 45247 4 46412 5 43532

b) Fungsi UPPER

Syntax:

=UPPER(text)

Fungsi ini digunakan untuk mengubah huruf, kata, atau frasa

seluruhnya menjadi huruf besar (kapital)

Keterangan:

Text dapat berupa huruf, kata, atau frasa/kalimat

Contoh:

=UPPER latiha E el aka e e ika kelua a e upa

LATIHAN EXCEL


------------------------------------------------------------------------------------------------------------- 44

Latihan:

Tulislah kata/frasa berikut ini sepenuhnya dalam format kapital:

No Kata/Frasa

1 Latihan Excel 2 Tingkat Menengah 3 Pusdiklat 4 Keuangan Umum 5 Badan Pendidikan dan Pelatihan Keuangan

c) Fungsi LOWER

Syntax:

=LOWER(text)

Fungsi ini digunakan untuk mengubah huruf, kata, atau frasa

seluruhnya menjadi huruf kecil

Keterangan:


Contoh:

=LOWER LATIHAN EXCEL aka e e ika kelua a e upa

latihan Excel

Latihan:

Tulislah kata/frasa berikut ini sepenuhnya dalam format huruf kecil:

No Kata/Frasa

1 Latihan Excel 2 Tingkat Menengah 3 Pusdiklat 4 Keuangan Umum 5 Badan Pendidikan dan Pelatihan Keuangan


------------------------------------------------------------------------------------------------------------- 45

d) Fungsi PROPER

Syntax:

=PROPER(text)

Fungsi ini digunakan untuk mengubah huruf awal dari kata, frasa, atau

kalimat menjadi huruf besar (kapital)

Keterangan:


Contoh:

=PROPER LATIHAN EXCEL aka e e ika kelua a e upa

Latihan Excel

Latihan:

Tulislah kata/frasa berikut ini sepenuhnya dalam format proper:

No Kata/Frasa

1 LATIHAN EXCEL 2 tingkat menengah 3 PUSDIKLAT 4 KEUANGAN UMUM 5 badan pendidikan dan pelatihan keuangan

e) Fungsi CONCATENATE dan AMPERSAND

Syntax:

=CONCATENATE(text1,text2,te t ,…

Fungsi ini digunakan untuk menggabungkan dua text atau lebih

Alte atif da i fu gsi CONCATENATE i i adalah pe ggu aa si ol &

atau a pe sa d de ga s ta : =te t &te t &te t &…

Contoh:

=CONCATENATE LATIHAN , EXCEL e ghasilka kelua a

LATIHAN EXCEL

=CONCATENATE LATIHAN , , EXCEL e ghasilka kelua a

LATIHAN EXCEL

= LATIHAN & EXCEL ATAU = LATIHAN & & EXCEL

menghasilkan keluaran yang sama yaitu LATIHAN EXCEL


------------------------------------------------------------------------------------------------------------- 46

Latihan:

Gabungkanlah frasa/kata dan angka berikut ini menggunakan fungsi

CONCATENATE atau AMPERSAND (jangan lupa sisipkan spasi kosong

pada tempatnya):

No Kata/Frasa 1 Kata/Frasa 2

1 Sepak Bola 2 Piala Dunia 3 Latihan Excel 4 Pusdiklat Keuangan Umum 5 Nomor 18 (Numeric)

f) Fungsi LEN

Syntax:

=LEN(text)

Fungsi ini digunakan untuk menampilkan panjang karakter dalam

sebuah cell.

Keterangan:


Contoh:

=LEN LATIHAN EXCEL akan memberikan keluaran berupa angka

13

Latihan:

Hitunglah panjang karakter dari kata/frasa berikut menggunakan fungsi

LEN:

No Kata/Frasa



------------------------------------------------------------------------------------------------------------- 47

g) Fungsi LEFT

Syntax:

=LEFT(text,num_char)

Fungsi ini digunakan untuk menampilkan sejumlah karakter dari sebuah

text dihitung dari sisi paling kiri.

Keterangan:


Num_char adalah jumlah karakter yang ingin ditampilkan

Contoh:

=LEFT LATIHAN EXCEL , aka e e ika kelua a e upa

LATIH

Latihan:

Ubahlah sebagian dari kata/frasa berikut menjadi huruf besar (kapital)

menggunakan fungsi LEFT, RIGHT, UPPER, LEN, dan CONCATENATE

(AMPERSAND):

No Kata/Frasa Keterangan

1 latihan Excel 3 karakter 2 tingkat menengah 4 karakter 3 pusdiklat 5 karakter 4 keuangan umum 6 karakter 5 badan pendidikan dan pelatihan keuangan 7 karakter

h) Fungsi RIGHT

Syntax:

=RIGHT(text,num_char)

Fungsi ini digunakan untuk menampilkan sejumlah karakter dari sebuah

text dihitung dari sisi paling kanan.

Keterangan:




------------------------------------------------------------------------------------------------------------- 48

Contoh:

=RIGHT LATIHAN EXCEL , aka e e ika kelua an berupa

EXCEL

Latihan:


menggunakan fungsi RIGHT, LEFT, UPPER, LEN, dan CONCATENATE

(AMPERSAND):

No Kata/Frasa Keterangan

1 latihan Excel 3 karakter 2 tingkat menengah 4 karakter 3 Pusdiklat 5 karakter 4 keuangan umum 6 karakter 5 badan pendidikan dan pelatihan keuangan 7 karakter

i) FUNGSI MID

Syntax:

=MID(text, start_num, num_char)

Fungsi ini digunakan untuk menampilkan sejumlah karakteryang

dihitung dari posisi tertentu di dalam text.

Keterangan:


Start_num menunjukkan posisi karakter yang pertama kali dihitung

untuk ditampilkan


Contoh:

=MID LATIHAN EXCEL , , aka e e ika kelua a e upa

HAN EX


------------------------------------------------------------------------------------------------------------- 49

Latihan:


menggunakan fungsi MID, CONCATENATE (AMPERSAND), LEFT, RIGHT,

UPPER, dan TRIM:

No Kata/Frasa Karakter

Awal Jumlah

1 latihan Excel 5 3 karakter 2 tingkat menengah 6 4 karakter 3 pusdiklat 2 5 karakter 4 keuangan umum 4 6 karakter 5 badan pendidikan dan pelatihan keuangan 10 7 karakter

j) Fungsi TRIM

Syntax:

=TRIM(text)

Fungsi ini digunakan untuk menghilangkan spasi yang tidak berguna

(berlebih) dari sebuah text, baik yang ada di depan, tengah, maupun

belakang.

Misalnya:

[spasi]Latihan[spasi]Excel akan diubah menjadi Latihan[spasi]Excel

Latihan[spasi][spasi]Excel[spasi] akan diubah menjadi

Latihan[spasi]Excel

Keterangan:


Contoh:

=TRIM LATIHAN EXCEL aka e ghasilka LATIHAN EXCEL

=TRIM A A aka e ghasilka A A


------------------------------------------------------------------------------------------------------------- 50

Latihan:

Hilangkanlah spasi yang tidak perlu pada kata/frasa berikut

menggunakan fungsi TRIM:

No Kata/Frasa

1 [spasi]LATIHAN[spasi]EXCEL[spasi] 2 tingkat[spasi][spasi]menengah[spasi] 3 [spasi]PUSDIKLAT[spasi] 4 KEUANGAN[spasi][spasi][spasi]UMUM 5 badan pendidikan dan pelatihan keuangan

k) Fungsi CHAR

Syntax:

=CHAR(number)

Fungsi ini akan menghasilkan keluaran berupa karakter yang diwakili

angka (number) yang menjadi masukannya

Keterangan:

Number adalah angka mulai dari 1 sampai dengan 255 (ASCII

Character)

Contoh

=CHAR aka e ghasilka atau spasi koso g


------------------------------------------------------------------------------------------------------------- 51

Berikut adalah tabel yang berisikan hasil konversi angka menjadi

karakter yang diproses menggunakan fungsi CHAR ini.

C# Chr C# Chr C# Chr C# Chr C# Chr C# Chr C# Chr C# Chr C# Chr

1 � 30 � 59 ; 88 X 116 t 144 � 172 ¬ 200 È 228 ä

2 � 31 � 60 < 89 Y 117 u 145 173 - 201 É 229 å

3 � 32 61 = 90 Z 118 v 146 174 ® 202 Ê 230 æ

4 � 33 ! 62 > 91 [ 119 w 147 175 ¯ 203 Ë 231 ç

5 � 34 " 63 ? 92 \ 120 x 148 176 ° 204 Ì 232 è

6 � 35 # 64 @ 93 ] 121 y 149 • 177 ± 205 Í 233 é

7 � 36 $ 65 A 94 ^ 122 z 150 – 178 ² 206 Î 234 ê

8 � 37 % 66 B 95 _ 123 { 151 — 179 ³ 207 Ï 235 ë

9 38 & 67 C 96 ` 124 | 152 ˜ 180 ´ 208 Ð 236 ì

10 39 ' 68 D 97 a 125 } 153 ™ 181 µ 209 Ñ 237 í

11 � 40 ( 69 E 98 b 126 ~ 154 š 182 ¶ 210 Ò 238 î

12 � 41 ) 70 F 99 c 127 • 155 183 · 211 Ó 239 ï

13 42 * 71 G 100 d 128 € 156 œ 184 ¸ 212 Ô 240 ð

14 � 43 + 72 H 101 e 129 � 157 � 185 ¹ 213 Õ 241 ñ

15 � 44 , 73 I 102 f 130 ‚ 158 ž 186 º 214 Ö 242 ò

16 � 45 - 74 J 103 g 131 ƒ 159 Ÿ 187 » 215 × 243 ó

17 � 46 . 75 K 104 h 132 „ 160 188 ¼ 216 Ø 244 ô

18 � 47 / 76 L 105 i 133 … 161 ¡ 189 ½ 217 Ù 245 õ

19 � 48 0 77 M 106 j 134 162 ¢ 190 ¾ 218 Ú 246 ö

20 � 49 1 78 N 107 k 135 163 £ 191 ¿ 219 Û 247 ÷

21 � 50 2 79 O 108 l 136 ˆ 164 ¤ 192 À 220 Ü 248 ø

22 � 51 3 80 P 109 m 137 ‰ 165 ¥ 193 Á 221 Ý 249 ù

23 � 52 4 81 Q 110 n 138 Š 166 ¦ 194 Â 222 Þ 250 ú

24 � 53 5 82 R 111 o 139 167 § 195 Ã 223 ß 251 û

25 � 54 6 83 S 112 p 140 Œ 168 ¨ 196 Ä 224 à 252 ü

26 � 55 7 84 T 113 q 141 � 169 © 197 Å 225 á 253 ý

27 � 56 8 85 U 114 r 142 Ž 170 ª 198 Æ 226 â 254 þ

28 � 57 9 86 V 115 s 143 � 171 « 199 Ç 227 ã 255 ÿ

29 � 58 : 87 W Gambar 2. 19 – Daftar Character

Sisipkan spasi kosong di antara dua kata/frasa berikut menggunakan

fungsi CHAR dan CONCATENATE (AMPERSAND):

No Kata/Frasa 1 Kata/Frasa 2

1 Sepak Bola 2 Piala Dunia 3 Latihan Excel 4 Pusdiklat Keuangan Umum 5 Nomor 18 (Numeric)

l) Fungsi CODE

Syntaxt:

=CODE(text)

Fungsi ini merupakan kebalikan dari fungsi CHAR di atas. Fungsi CODE

ini akan menghasilkan nilai angka dari sebuah text.


------------------------------------------------------------------------------------------------------------- 52

Keterangan:

Text merupakan satu huruf karakter atau jika text yang

diperhitungkan merupakan sebuah kata atau frasa, maka yang

dikonversi menjadi angka hanyalah karakter pertama dari sisi paling

kiri.

Contoh

=CODE L aka e ghasilkan angka 76 (lihat tabel di atas)

Latihan:

Tentukan nilai angka dari karakter ke-5 dihitung dari kiri menggunakan

fungsi CODE dan MID:

No Kata/Frasa


m) Fungsi FIND

Syntax:

=FIND(find_text,within_text,start_num)

Fungsi ini akan menghasilkan keluaran berupa angka yang menunjukkan

posisi ditemukannya karakter yang dicari dari suatu text tertentu

Keterangan:

Fungsi ini merupakan case sensitive function, jadi fungsi ini

membedakan antara huruf besar dan kecil

Find_text berupa satu atau beberapa huruf, kata, atau bagian

kalimat

Within_text merupakan text yang menjadi target

Start_num merupakan posisi awal posisi pencarian, jika tidak

disajikan, maka pencarian dimulai dari karakter pertama.


------------------------------------------------------------------------------------------------------------- 53

Contoh:

=FIND A , MAHARDIKA aka e ghasilka kelua a e upa

angka 2

=FIND a , MAHARDIKA , aka e ghasilka keluaran berupa

#VALUE (error)

=FIND A , MAHARDIKA , aka e ghasilka kelua a e upa

angka 4

Latihan:

Temukan huruf yang dicari pada tabel berikut dan ubahlah seluruh

huruf sesudahnya menjadi huruf kapital menggunakan fungsi FIND:

No Kata/Frasa Huruf yang Dicari

1 lAtihan Excel a 2 Tingkat menengah t 3 Pusdiklat d 4 keuangaN umum N 5 badan pendidikan dan pElatihan keuangan E

Catatan: Bandingkan hasil yang diperoleh dengan hasil yang diperoleh

menggunakan fungsi SEARCH.

n) Fungsi SEARCH

Syntax:

=SEARCH(find_text,within_text,start_num)

Keterangan:

Fungsi ini bukan case sensitive function, jadi fungsi ini tidak

membedakan antara huruf besar dan kecil, bahkan dapat

mengenali simbol asterisk (*) dan simbol tanda tanya (?)

Untuk mencari simbol tanda tanya dalam sebuah kata gunakan

simbol tidle (~)

Find_text berupa satu atau beberapa huruf, kata, atau bagian

kalimat

Within_text merupakan text yang menjadi target

Start_num merupakan posisi awal posisi pencarian, jika tidak

disajikan, maka pencarian dimulai dari karakter pertama.


------------------------------------------------------------------------------------------------------------- 54

Contoh

=SEARCH A , MAHARDIKA aka e ghasilka kelua a e upa

angka 2

=SEARCH a , MAHARDIKA , aka e ghasilka kelua a

berupa angka 2

=SEARCH A , MAHARDIKA , aka e ghasilka kelua a

berupa angka 4

Latihan:

Temukan huruf yang dicari pada tabel berikut dan ubahlah seluruh

huruf sesudahnya menjadi huruf kapital menggunakan fungsi SEARCH:

No Kata/Frasa Huruf yang Dicari

1 lAtihan Excel a 2 Tingkat menengah t 3 Pusdiklat d 4 keuangaN umum N 5 badan pendidikan dan pElatihan keuangan E

Catatan: Bandingkan hasil yang diperoleh dengan hasil yang diperoleh

menggunakan fungsi FIND.

o) Fungsi VALUE

Syntax:

=VALUE(text)

Fungsi ini digunakan untuk mengubah data teks (alpha-numeric)

menjadi data angka (numeric).

Keterangan:

Data alpha-numeric adalah data teks yang serupa dengan angka

atau angka yang ditulis dalam kolom atau cell yang diformat sebagai

teks.

Ciri-cirinya, antara lain, akan disajikan secara rata kiri (left justify)

dalam cell, sementara data angka (numerik) akan disajikan secara

rata kanan (right justify)

Pada pojok kiri atas dari cell akan terlihat segita berwarna hijau.


------------------------------------------------------------------------------------------------------------- 55

Contoh

=VALUE T aka e ghasilka ilai ERROR ka e a T adalah data

karakter biasa dan bukan data alpha-numeric

=VALUE aka e ghasilka ilai u e i ka e a

adalah data Alpha-Numeric.

Latihan:

Ubahlah data teks berikut ini menjadi angka:

No Alpha-Numeric

1 1000 2 2000 3 3000 4 4000 5 5000

p) Fungsi REPT

Syntax:

=REPT(text,number_of_times)

Fungsi ini akan mengulangi penulisan huruf, kata, atau frasa sebanyak

yang kita butuhkan.

Keterangan:

Text dapat berupa huruf, kata, atau frasa

Number_of_times adalah jumlah pengulangan yang kita butuhkan

Contoh:

=REPT A , aka e ghasilka kelua a AAAAA

=REPT a , aka e ghasilka kelua a a a a

=REPT - , aka e ghasilka kelua a ----------

=REPT HA , aka e ghasilka kelua a

HAHAHAHAHAHAHAHA

10. Fungsi Logika

a) LOGICAL TEST

Sebelum membahas lebih lanjut tentang Fungsi Logika, ada

baiknya jika membahas sedikit tentang konsep Logical Test yang


------------------------------------------------------------------------------------------------------------- 56

merupakan dasar paling penting dari fungsi Logika ini. Logical Test

adalah proses membandingkan antara satu nilai dengan nilai lainnya

dengan keluaran berupa TRUE atau FALSE

Proses pembandingan nilai dimaksud dilakukan menggunakan

salah satu dari operator pembandingan (Comparison Operator) berikut:

Tabel 2. 4 – Daftar Comparsion Operator

No. Operator Arti Contoh

1 = Sama Dengan A1 = B2

2 < Kurang Dari A1 < B2

3 > Lebih Dari A1 > B2

4 <= Kurang Dari atau Sama Dengan A1 <= B2

5 >= Lebih Dari atau Sama Dengan A1 >= B2

6 <> Tidak Sama Dengan A1 <> B2

Jika pernyataan perbandingan terpenuhi, maka keluarannya akan

bernilai TRUE, sebaliknya jika tidak terpenuhi, keluarannya akan bernilai

FALSE

Contoh:

Tabel 2. 5 – Contoh Logical Test

No. Nilai A1 Nilai A2 Logical Test Keluaran

1 10 20 A1 = A2 FALSE

2 10 20 A1 <A2 TRUE

3 10 20 A1 >A2 FALSE

4 10 20 A1 <= A2 TRUE

5 10 20 A1 >= A2 FALSE

6 10 20 A1 <>A2 TRUE


------------------------------------------------------------------------------------------------------------- 57

Dari Tabel 2. 5 dapat kita cermati bahwa Cell A1 bernilai 10

sementara Cell A2 bernilai 20. Jika Comparison Operator yang

digu aka adalah = , aka kelua a a aka e ghasilka ilai FALSE

karena 10 kurang dari 20. Selanjutnya, jika kita menggunakan operator

<> (tidak sama dengan), maka keluarannya akan menghasilkan TRUE,

karena memang 10 tidak sama dengan 20.

b) Fungsi AND()

Syntax:

=AND logi al_test , logi al_test ,…

Jika pada bahasan tentang logical test sebelumnya kita hanya

membandingkan satu variabel dengan satu variabel lainnya, maka

melalui fungsi AND dan OR kita ingin membanding LEBIH DARI SATU

VARIABEL dengan SATU ATAU LEBIH VARIABEL lainnya.

Fungsi AND akan menghasilkan keluaran bernilai TRUE, apabila

SELURUH logical test yang dilakukan bernilai TRUE.

Tabel 2. 6 – Contoh Logical Test AND

No. Nilai A1 Nilai A2 Nilai A3 Logical Test Keluaran

1 10 20 30 A1 = A2 AND A1 = A3 FALSE

2 10 20 30 A1 <A2 AND A1 < A3 TRUE

3 10 20 30 A1 >A2 AND A1 > A3 FALSE

4 10 20 30 A1 <= A2 AND A1 <= A3 TRUE

5 10 20 30 A1 >= A2 AND A1 >= A3 FALSE

6 10 20 30 A1 <>A2 AND A1 <> A3 TRUE

Dari Tabel 2. 6 dapat kita cermati bahwa Logical Test menggunakan

operator AND ini hanya menghasilkan nilai TRUE apabila kedua

pernyataan logis yang ada juga bernilai TRUE.


------------------------------------------------------------------------------------------------------------- 58

Latihan:

Tentukan nilai keluaran dari tiap pernyataan berikut:

No. Nilai A1 Nilai A2 Nilai A3 Logical Test

1 180 116 150 A1 < A2 OR A3 > A2 2 172 173 130 A1 < A2 OR A1 > A2 3 131 180 146 A1 < A2 OR A3 > A2 4 185 128 107 A1 < A2 OR A3 <> A2 5 197 104 106 A1 > A2 OR A3 > A2 6 131 188 166 A1 > A2 OR A3 < A2

c) Fungsi OR()

=OR logi al_test , logi al_test ,…

Berbeda dari fungsi AND, fungsi OR akan menghasilkan keluaran

bernilai TRUE, apabila SALAH SATU KELUARAN dari seluruh logical test

yang dilakukan bernilai TRUE.

Tabel 2. 7 – Contoh Logical Test OR

No. Nilai A1 Nilai A2 Nilai A3 Logical Test Keluaran

1 10 20 30 A1 = A2OR A2< A3 TRUE

2 10 20 30 A1 < A2OR A2 > A3 TRUE

3 10 20 30 A1 > A2OR A2 < A3 TRUE

4 10 20 30 A1 <= A2OR A2 >= A3 TRUE

5 10 20 30 A1 >= A2OR A2 <= A3 TRUE

6 10 20 30 A1 = A2OR A2<> A3 TRUE

Dari Tabel 2. 7 dapat kita cermati bahwa Logical Test menggunakan

operator OR ini akan menghasilkan nilai TRUE meskipun salah satu

pernyataan yang digunakan menghasilkan nilai FALSE. Contoh,

pernyataan A1 = A2 OR A2 < A3 (=OR(A1 = A2, A2 < A3)) menghasilkan

nilai TRUE meskipun pernyataan A1=A2 menghasilkan nilai FALSE.


------------------------------------------------------------------------------------------------------------- 59

Latihan:

Tentukan nilai keluaran dari tiap pernyataan (logical test) berikut:

No. Nilai A1 Nilai A2 Nilai A3 Logical Test

1 180 116 150 A1 < A2 OR A3 > A2 2 172 173 130 A1 < A2 OR A3 = A2 3 131 180 146 A1 > A2 OR A3 > A2 4 185 128 107 A1 > A2 OR A3 <> A2 5 197 104 106 A1 < A2 OR A3 > A2 6 131 188 166 A1 < A2 OR A3 < A2

d) Fungsi IF

Di antara seluh fungsi yang disediakan Excel, maka fungsi IF ini

adalah salah satu fungsi terpenting yang harus kita kuasai secara

maksimal

Meskipun sepintas terlihat sangat sederhana, namun dalam

penggunaan sehari-hari, fungsi IF ini dapat dituliskan dalam bentuk

yang sangat kompleks (lihat bagian NESTED IF) sesuai tuntutan

pekerjaan kita.

Dapat dikatakan bahwa fungsi IF ini adalah satu-satunya fungsi

dalam Excel yang memang diciptakan untuk membantu

menerjemahkan secara efisien dan efisien setiap langkah yang kita

rumuskan dalam mengatasi berbagai tantangan dalam proses

pengolahan data atau pelaporan, mulai dari yang paling sederhana,

hingga yang paling kompleks ke dalam formula yang dapat dipahami

oleh Excel.

Syntax:

=IF(logical_test, respond_if_true, respond_if_false)

Fungsi ini merupakan tindak lanjut dari logical test yang kita lakukan,

termasuk menggunakan fungsi AND dan OR.


------------------------------------------------------------------------------------------------------------- 60

Sebagaimana yang telah kita pelajari sebelumnya, Logical Test

akan menghasilkan keluaran berupa pernyataan TRUE atau FALSE.

Melalui fungsi IF ini, kita ingin menentukan tindak lanjut apa yang

harus dilakukan untuk setiap respon yang dihasilkan oleh Logical Test

(TRUE atau FALSE) yang kita lakukan.

Contoh:

=IF(A1 < A2, 10, 100)

Fungsi di atas dapat dibaca sebagai berikut:

Jika pernyataan A1 < A2 bernilai TRUE, maka Excel akan menuliskan

keluaran fungsi ini berupa angka 10, jika pernyataan tersebut

bernilai FALSE, maka Excel akan menuliskan keluaran berupa angka

100.

=IF A < A , SEPULUH , SERATUS

Fungsi di atas dapat dibaca sebagai berikut:

Jika pernyataan A1 < A2 bernilai TRUE, maka Excel akan

menuliskan keluaran fu gsi i i e upa kata SEPULUH , jika

pernyataan tersebut bernilai FALSE, maka Excel akan

e uliska kelua a e upa kata SERATUS

Latihan:

Tentukan keluaran dari fungsi IF berikut ini:

No. Fungsi IF Keluaran

jika TRUE

Keluaran

jika FALSE

1 =IF A = A , SAMA , TIDAK SAMA 2 =IF A > A , , 3 =IF(A1 <> A2,100,200) 4 =IF(A2 > A1,A2,A1) 5 =IF *A =A / , BENAR , SALAH 6 =IF(A2/A1=A1/5,A1*A2,A1/A2)


------------------------------------------------------------------------------------------------------------- 61

e) Nested IF

Syntax 1:

=IF(logical_test, IF(logical_test, respond_if_true, respond_if_false ),

respond_if_false)

Keterangan: pilihan berikutnya muncul pada respond if true

Atau

Syntax 2:

=IF(logical_test, respond_if_true, IF(logical_test, respond_if_true,

respond_if_false))

Keterangan: pilihan berikutnya muncul pada respond if true

Atau

Syntax 3:

=IF(logical_test, IF(logical_test, respond_if_true, respond_if_false),

IF(logical_test, respond_if_true, respond_if_false)))

Keterangan: pilihan berikutnya muncul pada kedua respon

Nested IF digunakan untuk mengakomodasi situasi dimana kita harus

menghadapi pilihan berikutnya setelah Logical Test pertama

menghasilan respon, baik respon berupa nilai TRUE, FALSE, atau

keduanya.

Contoh:

Jika A1 < A2 menghasilkan nilai TRUE, kita harus menguji apakah Cell

A10 >A100, jika pengujian kedua ini menghasilkan nilai TRUE, maka

kelua a a g dihasilka adalah SATU , jika e ilai FALSE, maka

kelua a e ilai DUA , se alik a jika A1 < A2 menghasilkan nilai

FALSE, kita harus menguji apakah cell A11 > A101, dan jika pengujian

kedua ini menghasilkan nilai TRUE, maka keluaran yang dihasilkan

adalah TIGA , a u jika e ilai FALSE, aka kelua a a g

dihasilka adalah EMPAT


------------------------------------------------------------------------------------------------------------- 62

Uraian di atas dapat kita ubah menjadi formula berikut ini:

=IF A < A ,IF A > A , SATU , DUA ,IF A >A , TIGA , EMPAT

Catatan:

Formula di atas sudah benar, namun jika kita meng-copy

formulatersebut langsung ke Excel, maka setelah dieksekusi Excel

akan memberika respon #NAME? atau terjadi ERROR, kenapa?

Latihan:

Buatlah formula untuk situasi sebagai berikut, menggunakan fungsi

IF:

No Penghasilan Kelompok

1 <= 120.000.000 Bawah

2 120.000.000 – 500.000.000 Menengah – Bawah

3 500.000.000 – 1.000.000.000 Menengah Atas

4 > 1.000.000.000 Atas

Jika PTKP = 25.000.000, Hitunglah Penghasilan Kena Pajak atas Data

Penghasilan di bawah ini:

4,701,903.23

7,942,428.96

26,894,891.37

1,796,976.36

38,614,057.72

74,953,158.99

f) Fungsi IFERROR

Syntax:

=IFERROR(value,value_if_error)


------------------------------------------------------------------------------------------------------------- 63

Fungsi ini mulai diperkenalkan pada Excel 2007 dan digunakan

untuk mengantisipasi timbulnya pesan kesalahan yang dihasilkan Excel

dari proses/formula yang digunakan untuk menghasilkan value

tertentu, agar kesalahan yang timbul tidak menyebabkan proses

penghitungan terhenti (error trapping).

Keterangan:

Value adalah nilai yang ingin dihasilkan, biasa merupakan sebuah

formula/fungsi tertentu

Value_if_error adalah value alternatif yang kita gunakan sekiranya

formula/fungsi yang kita gunakan menghasilkan nilai error.

Pesan kesalahan dapat muncul tatkala kita keliru dalam

menggunakan suatu fungsi tertentu, namun perlu diingat bahwa

fungsi ini digunakan bukan untuk menutupi kesalahan akibat

penggunaan formula atau fungsi yang tidak benar. Sebaliknya,

fungsi ini digunakan untuk mengantisipasi dampak yang timbul

akibat adanya syarat atau kondisi yang tidak terpenuhi pada satu

atau beberapa cell saja, sementara formula yang digunakan bekerja

dengan baik pada cell lainnya.

Contoh pesan kesalahan yang timbul, antara lain:

- #N/A!

- #NAME?

- #DIV/0!

- #VALUE

- #NUM!

- #REF!

Pada contoh di bawah ini berlaku aturan bahwa:

Nilai cell pada Kolom B merupakan hasil bagi dari cell pada kolom A

yang terletak satu baris dengan dirinya lalu dikalikan dua.

Hasil dari perhitungan di atas adalah sebagai berikut:


------------------------------------------------------------------------------------------------------------- 64

Berdasarkan Gambar 38 di bawah, tampak bahwa Formula

=A1/A2*2 berfungsi dengan baik di seluruh cell kecuali pada Cell B3

yang nilainya (#DIV/0!) mengindikasikan adanya kesalahan yang jika

kita cermati disebabkan oleh nilai Cell A4 yang sama dengan 0.

Isi Cell A4 ini merupakan PENGECUALIAN dari kondisi umum yang

ada pada seluruh cell dan kita dapat mengantisipasi kondisi ini

menggunakan fungsi IFERROR

Kondisi alternatif yang ingin kita terapkan adalah:

Jika ERROR maka nilai Cell B terkait akan menjadi 100

Dengan demikian formula yang harus kita gunakan untuk seluruh

cell mulai dari B1 adalah =IFERROR(A1/A2*2,100). Dapat dengan

mudah kita lihat bahwa formula asli, yaitu =A1/A2*2 tetap kita

gunakan tanpa modifikasi.

Hasil dari perhitungan ini dapat kita cermati pada Gambar 39.

ERROR

Gambar 2. 20 – Hasil Perhitungan

Gambar 2. 21 – Hasil Perhitungan Menggunakan IFERROR


------------------------------------------------------------------------------------------------------------- 65

11. Fungsi RANDOM

Fungsi RANDOM digunakan untuk menghasilkan satu set angka

secara acak yang pengaturannya dilakukan Excel melalui metode dan

algoritma tertentu. Secara umum fungsi random ini terdiri atas dua jenis,

yaitu fungsi RAND dan RANDBETWEEN.

a) Fungsi RAND

Syntax:

=RAND()

Fungsi ini digunakan untuk menghasilkan angka acak POSITIF yang

nilainya < 0.

b) Fungsi RANDBETWEEN

Syntax:

= RANDBETWEEN(bottom, top)

Fungsi ini digunakan untuk menghasilkan angka acak, positif maupun

NEGATIF, yang rentangnya telah kita tetapkan (batasi).

Keterangan:

Bottom

Merupakan angka awal/batas bawah dari angka acak yang akan

dihasilkan

Top

Merupakan angka akhir/batas atas dari angka acak yang akan

dihasilkan

Contoh:

=RANDBETWEEN(500,5000) akan menghasilkan nilai antara 500 s.d.

5000 secara acak.

Latihan:


------------------------------------------------------------------------------------------------------------- 66

Buatlah 10 baris data tanggal secara random, mulai dari tanggal

1/1/2012 sampai dengan tanggal 31/7/2012.

Buatlah 10 baris data secara random, mulai dari angka 0 sampai

dengan 100.

Fungsi Random ini sangat bermanfaat untuk kepentingan simulasi

data, misalnya data penghasilan atau data penerimaan pajak, karena

dengan mudah kita dapat menghasilkan satu set nilai, baik menggunakan

fungsi RAND maupun RANDBETWEEN.

Salah satu kelebihan dan juga kelemahan fungsi RANDOM ini adalah

nilai yang dihasilkannya selalu berubah setiap Excel melakukan proses

penghitungan ulang (Rekalkulasi). Proses Rekalkulasi ini dilakukan Excel

secara otomatis segera setelah kita menginputkan data ke dalam sebuah

cell.

Proses Rekalkulasi ini dapat kita picu secara manual menggunakan

tombol F9 diikuti oleh perubahan nilai dari seluruh cell yang memuat fungsi

RAND atau RANDBETWEEN dan kita dapat melakukannya berkali-kali

sampai kita menemukan satu set angka yang mungkin lebih mencerminkan

situasi di dunia nyata sebelumnya kita memprosesnya lebih lanjut.

Untuk menghentikan proses perubahan ini, satu-satunya cara adalah

mengubah data tiap cell dari formula menjadi value menggunakan fasilitas

Copy dan Paste-Value.

Caranya:

Sorot Cell atau Range yang berisikan formula yang akan di Paste-Value-

kan

Tekan Tombol CTRL + C untuk meng-copy isi Cell/Range

Klik kanan pada mouse atau keyboard dan akan muncul tampilan

berikut


------------------------------------------------------------------------------------------------------------- 67

Gambar 2. 22 – Menu Setelah Menekan Klik Kanan pada Mouse/Keyboard

Pilih opsi Paste Special yang akan menampilkan pilihan berikut:

Gambar 2. 23 – Tampilan Menu Setelah Memilih Paste Special


------------------------------------------------------------------------------------------------------------- 68

Pilih Paste Special kembali, kemudian akan tampak layar dialog berikut:

Gambar 2. 24 – Layar Dialog Paste Special

Klik Opsi Values denga ouse atau de ga e eka to ol V pada

keyboard dilanjutkan dengan meng-klik pilihan OK atau tekan tombol

ENTER

Secara otomatis seluruh formula yang ada pada range tersebut

dipermanenkan menjadi angka, sebagaimana berikut:

FORMULA berganti

menjadi ANGKA

Gambar 2. 25 – Hasil Akhir Proses Paste Value

12. Fungsi ROUND

Fungsi ini digunakan untuk melakukan pembulatan terhadap suatu

angka/bilangan, baik dengan metode pembulatan yang sesuai aturan

matematika yang baku, angka 5 lebih dibulatkan ke atas dan di bawah


------------------------------------------------------------------------------------------------------------- 69

angka 5 dibulatkan ke bawah (ROUND), dengan memaksa pembulatan ke

atas (ROUNDUP), atau dengan memaksa pembulatan ke bawah

(ROUNDDOWN).

Syntax:

=ROUND(number, num_digits)

=ROUNDUP(number, num_digits)

=ROUNDDOWN(number,num_digits)

Keterangan:

Berikut aturan terkait num_digits:

Jika num_digits bernilai positif, maka besaran angkanya menunjukkan

jumlah angka di belakang koma (jumlah digit desimal) setelah

pembulatan dilakukan

Jika num_digits bernilai nol, maka semua digit desimal dibulatkan ke

satuan penuh, ke atas atau ke bawah.

Jika num_digits bernilai negatif, maka pembulatan dilakukan ke satuan,

puluhan, ribuan penuh, dst, tanpa angka desimal (desimal = 0)

Contoh:

Berikut Pembulatan dengan Metode Pembulatan ROUND, ROUNDUP, dan

ROUNDDOWN, dengan num_digits = 2

Tabel 2. 8 – Hasil Pembulatan dengan num_digits = 2

ANGKA ROUND ROUNDUP ROUNDDOWN

57,316.9475 57,316.9500 57,316.9500 57,316.9400

77,286.5077 77,286.5100 77,286.5100 77,286.5000

53,942.7712 53,942.7700 53,942.7800 53,942.7700

5,848.9557 5,848.9600 5,848.9600 5,848.9500

16,352.5987 16,352.6000 16,352.6000 16,352.5900

44,051.4368 44,051.4400 44,051.4400 44,051.4300

57,316.9475 57,316.9500 57,316.9500 57,316.9400


------------------------------------------------------------------------------------------------------------- 70

Dapat kita cermati pada Tabel 2. 8 bahwa setelah pembulatan digit desimal

ke-3 dan ke-4 berubah menjadi 0.

Berikut Pembulatan dengan Metode Pembulatan ROUND, ROUNDUP, dan

ROUNDDOWN, dengan num_digits = -2

Tabel 2. 9 – Hasil Pembulatan dengan num_digits = -2

ANGKA ROUND ROUNDUP ROUNDDOWN

11,193.2517 11,200.0000 11,200.0000 11,100.0000

59,562.2407 59,600.0000 59,600.0000 59,500.0000

15,886.5824 15,900.0000 15,900.0000 15,800.0000

78,701.5823 78,700.0000 78,800.0000 78,700.0000

72,955.8378 73,000.0000 73,000.0000 72,900.0000

90,631.4032 90,600.0000 90,700.0000 90,600.0000

11,193.2517 11,200.0000 11,200.0000 11,100.0000

Dapat kita cermati pada Tabel 2. 9 bahwa setelah pembulatan, digit desimal

seluruhnya berubah menjadi 0.

Latihan:

Jika PTKP = 25.000.000, Hitunglah Penghasilan Kena Pajak atas Data

Penghasilan di bawah ini dengan ketentuan bahwa sebelum dihitung

seluruh Nilai Penghasilan dibulatkan ke ribuan penuh:

4,701,903.23

7,942,428.96

26,894,891.37

1,796,976.36

38,614,057.72

74,953,158.99


------------------------------------------------------------------------------------------------------------- 71

13. Fungsi LOOKUP

a) Fungsi LOOKUP

Syntax:

=LOOKUP(lookup_value, lookup_vector, result_vector)

=LOOKUP(lookup_value, array)

Fungsi ini akan menghasilkan sebuah nilai yang berasal dari kumpulan

data berbentuk kolom atau baris (data vector) atau dari sebuah array.

Keterangan untuk =LOOKUP(lookup_value, lookup_vector,

result_vector):

Vector adalah sebuah range yang terdiri atas satu kolom atau satu

baris.

Lookup_value adalah nilai yang ingin kita cari dan wajib diisikan

Lookup_vector merupakan sebuah database yang dapat berisikan

data teks, angka, logika dan harus disusun secara ascending (dari

yang terkecil hingga terbesar) agar fungsi LOOKUP dapat

menghasilkan nilai yang kita inginkan.

Lookup_vector harus diisikan dalam fungsi

Result_vector bersifat opsional (dapat digunakan atau tidak). Jika

kita menggunakan result_vector, maka ukurannya (dimensinya)

harus identik dengan dimensi lookup_vector. Misalnya, jika

lookup_vector adalah sebuah data berbentuk kolom dengan ukuran

1 x 5, maka result_vector harus memiliki dimensi 1 x 5 pula.

Result_Vector berisikan nilai akhir yang harus ditampilkan oleh

fungsi LOOKUP ini. Jika result_vector tidak disertakan dalam fungsi,

maka data keluaran akan berasal dari lookup_vector.

Contoh untuk =LOOKUP(lookup_value, lookup_vector, result_vector):

Syntax: =LOOKUP(lookup_value, lookup_vector, result_vector)


------------------------------------------------------------------------------------------------------------- 72

Gambar 2. 26 – Lookup dengan result_vector

Dari Gambar 2. 26 dapat kita cermati bahwa lookup_value kita

adalah data yang ada pada kolom lookup_value, sementara

lookup_vector kita adalah data yang ada pada kolom Frequency,

sementara result_vector kita adalah data yang ada pada colom

Color.

Jika data result-vector kita abaikan, maka fungsi LOOKUP ini akan

menghasilkan nilai sebagai berikut:

Gambar 2. 27 – LOOKUP tanpa result_vector

Bandingkan hasil yang diperoleh dalam Gambar 2. 26 dan Gambar

2. 27!

Keterangan untuk =LOOKUP(lookup_value, array)

Lookup_value adalah nilai yang ingin kita cari

Array adalah sebuah range yang dapat berisikan data teks, angka,

maupun logika.

Fungsi LOOKUP akan membandingkan lookup_value dengan data

yang ada pada baris pertama atau kolom pertama dari sebuah

array, tergantung dimensi array dengan ketentuan sebagai berikut:


------------------------------------------------------------------------------------------------------------- 73

Jika jumlah kolom array tersebut melebihi jumlah barisnya,

maka LOOKUP akan secara otomatis membandingkan

lookup_value dengan data pada baris pertama array dimaksud.

Jika array tersebut berbentuk persegi atau jumlah baris array

melebihi jumlah kolomnya, maka LOOKUP akan secara

otomatis membandingkan lookup_value dengan data pada

kolom pertama array dimaksud.

Nilai dari Array harus disusun secara ascending(dari yang

terkecil hingga yang terbesar).

Contoh untuk =LOOKUP(lookup_value, array)

=LOOKUP("C", {"a", "b", "c", "d";1, 2, 3, 4}) dengan nilai keluaran =

3

Lookup_ alue pada o toh di atas adalah C

Pada argumen yang berisikan data Array, dapat disimpulkan bahwa

data array ini terdiri atas dua kolom, di mana data pada kolom

pe ta a da kolo kedua dipisahka e ggu aka ta da ; .

Berikut adalah ilustrasi dari Tabel Array

a 1

b 2

c 3

d 4

Karena jumlah baris array melebihi jumlah kolomnya, maka fungsi

LOOKUP akan membandingkan lookup_value dengan data pada

kolom pertama array, yaitu "a", "b", "c", "d".

Lookup_ alue C aka e e uka pasa ga a pada baris ketiga

data a ua , aitu hu uf , de ga ilai a ua sa a de ga a g

merupakan data baris ketiga pada kolom kedua tabel array (lihat

bagian yang diarsir pada Tabel Array).

Contoh berikutnya adalah:

=LOOKUP("bump", {"a",1;"b",2;"c",3}) dengan nilai keluaran = 2

Jika kita perhatikan secara seksama, contoh di atas menggunakan

format yang sedikit berbeda dibanding contoh yang pertama.


------------------------------------------------------------------------------------------------------------- 74

Pada contoh pertama: =LOOKUP("C", {"a", "b", "c", "d";1, 2, 3, 4}),

ta da ; dite patka setelah selu uh data kolo selesai dituliska ,

sementara pada contoh kedua ini, data kolom dan baris dituliskan

e pasa ga de ga pe isah e ggu aka ta da ; sege a

setelah pasangan data dituliskan.

Lookup_ alue da i fu gsi i i adalah kata u p

Tabel array-nya adalah tabel 2 x 3, dan karena jumlah baris array

melebihi jumlah kolomnya, maka fungsi LOOKUP akan

membandingkan lookup_value dengan data pada kolom pertama.

Berikut ilustrasi dari Tabel Array dimaksud:

a 1 b 2 c 3

Lookup_ alue Bu p aka e e uka pasa ga a pada baris

kedua data a ua , aitu hu uf , de ga ilai a ua sama dengan

2 yang merupakan data baris kedua pada kolom kedua tabel array

(lihat bagian yang diarsir pada Tabel Array di atas).

Contoh selanjutnya adalah:

=LOOKUP(A2, {0, 60, 70, 80, 90}, {"F", "D", "C", "B", "A"}) dengan

nilai keluaran sa a de ga F


ilai kelua a sa a de ga A


ilai kelua a sa a de ga C


------------------------------------------------------------------------------------------------------------- 75

b) Fungsi HLOOKUP

Syntax:

=HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup]

Fungsi ini akan mencari keberadaan lookup_value pada baris pertama

table_array dan mencarikan keluaran yang sesuai pada baris data yang

ditunjukkan oleh nilai dari row_index_num dengan sistem pencarian

sesuai dengan range_lookup.

Keterangan:

Lookup_value adalah nilai yang akan dicari/dicocokkan dengan data

yang ada pada table_array

Table_array adalah satu set database yang mengandung nilai

keluaran yang ingin dicari melalui lookup_value.

Row_index_num adalah posisi baris tempat nilai keluaran berada,

baris pertama dari table_array adalah baris ke-1, sehingga

row_index_num ini bernilai lebih dari 1.

Range_lookup adalah sistem pencarian, yaitu TRUE untuk sistem

APPROXIMATE MATCH dan FALSE untuk sistem EXACT MATCH. Jika

opsi ini tidak dituliskan, maka HLOOKUP akan menggunakan opsi

TRUE.

Approximate Match digunakan apabila data kita bersifat continue

(range data), misalnya, angka 40 berada di antara 30 dan 50,

sementara Exact Match digunakan apabila data kita bersifat

discreet.

Apabila HLOOKUP tidak berhasil menemukan pasangan

lookup_value, nilai keluarannya adalah #N/A jika opsi yang

digunakan adalah EXACT MATCH.


------------------------------------------------------------------------------------------------------------- 76

Contoh:

Tabel di bawah ini berisikan data mengenai Axles, Bearings, dan

Bolts yang berfungsi sebagai table_array bagi aplikasi fungsi

HLOOKUP kita kali ini:

Sumber: Excel Help

=HLOOKUP("Axles", A1:C4, 2, TRUE) dengan keluaran = 4

Lookup_ alues adalah kata A les

Table_array adalah range A1:C4

Row_index_num adalah 2 (data keluaran ada di baris ke-2)

Sifat pencarian: APPROXIMATE MATCH

HLOOKUP e e uka pasa ga A les pada a is da i ta el

di atas, lalu menampilkan nilai kolom Axles yang ada pada baris

ke 2, yaitu 4.

Opsi TRUE tidak terlalu berpengaruh pada formula ini karena

data yang dibandingkan menemukan pasangannya yang

memiliki nilai yang identik (Exact Match)

=HLOOKUP("Bearings", A1:C4, 3, FALSE) dengan keluaran = 7

Lookup_ alues adalah kata Bea i gs



Sifat pencarian: EXACT MATCH

Opsi TRUE tidak terlalu berpengaruh pada formula ini karena

data yang dibandingkan menemukan pasangannya yang

memiliki nilai yang identik (Exact Match)


------------------------------------------------------------------------------------------------------------- 77

HLOOKUP e e uka pasa ga Bea i gs pada a is da i

tabel di atas, lalu menampilkan nilai kolom Bearings yang ada

pada baris ke 3, yaitu 7.

=HLOOKUP("B", A1:C4, 3, TRUE) dengan keluaran = 5.

Lookup_ alues adalah kata B



Sifat pencarian: APPROXIMATE MATCH

Ka e a B tidak menemukan pasangannya pada baris pertama

dari tabel di atas, maka HLOOKUP akan menampilkan angka

te esa e ikut a setelah B , aitu B ka e a e upaka

data teks, nilainya dianggap sama dengan 0.

opsi yang digunakan adalah FALSE, maka formula ini akan

menghasilkan keluaran #N/A atau error.

=HLOOKUP("Bolts", A1:C4, 4) dengan keluaran = 11

Lookup_ alues adalah kata Bolts



Karena opsi TRUE atau FALSE tidak digunakan, maka HLOOKUP

secara otomatis akan menggunakan opsi TRUE (APPROXIMATE

MATCH)

HLOOKUP e e uka pasa ga Bolts pada a is da i ta el

di atas, lalu menampilkan nilai kolom Bolts yang ada pada baris

ke 4, yaitu 11.

Contoh lainnya adalah sebagai berikut:

=HLOOKUP(3, {1,2,3;"a","b","c";"d","e","f"}, 2, TRUE) dengan nilai

kelua a sa a de ga

Berdasarkan formula di atas, dapat disimpulkan bahwa:

Lookup_value-nya adalah angka 3


------------------------------------------------------------------------------------------------------------- 78

Table_array terdiri atas 3 kolom dan 3 baris (3 x 3) dengan

ilustrasi sebagai berikut:

1 2 3

A b c

D e f

Nilai keluaran berada pada baris ke-2

Opsi yang digunakan adalah TRUE

Lookup_value (3) ditemukan pada kolom ke-3, baris ke-1, dan

nilai keluarannya adalah c yang terletak di baris ke-2

Latihan: Dengan ketentuan sebagai berikut:

NO PENGHASILAN KELOMPOK

1 <= 120.000.000 Bawah

2 120.000.000 – 500.000.000 Menengah – Bawah

3 500.000.000 – 1.000.000.000 Menengah Atas

4 > 1.000.000.000 Atas

Kelompokkan daftar penghasilan berikut sesuai dengan

kelompoknya menggunakan fungsi HLOOKUP


1 109,636,929

2 284,816,798

3 433,225,466

4 99,569,476

5 94,730,539

6 841,625,150

7 773,270,139

8 120,924,334


------------------------------------------------------------------------------------------------------------- 79

c) Fungsi VLOOKUP

Syntax:

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]

Fungsi ini akan mencari keberadaan lookup_value pada kolom

pertama dari table_array dan mencarikan keluaran yang sesuai pada

kolom data yang ditunjukkan oleh nilai dari col_index_num dengan

ketentuan pencarian ditetapkan sesuai dengan range_lookup.

Meskipun fungsi HLOOKUP dan VLOOKUP pada prinsipnya

merupakan fungsi yang serupa, bahkan jika digunakan dengan kondisi

yang sama akan menghasilkan keluaran yang identik, namun jika

dibandingkan, secara mental dan teknis, fungsi VLOOKUP ini jauh lebih

mudah digunakan, karena manusia secara insting lebih nyaman bekerja

dengan data berbentuk kolom, selain itu juga karena database disajikan

dalam bentuk kolom.

Keterangan:

Lookup_value adalah nilai yang akan dicari/dicocokkan dengan data

yang ada pada table_array

Table_array adalah satu set database yang mengandung nilai

keluaran yang ingin dicari melalui lookup_value

Col_index_num adalah posisi kolom tempat nilai keluaran berada,

kolom pertama dari table_array adalah kolom ke-1, sehingga

col_index_num ini bernilai lebih dari 1.

Range_lookup adalah sistem pencarian, yaitu TRUE untuk sistem

APPROXIMATE MATCH dan FALSE untuk sistem EXACT MATCH. Jika

opsi ini tidak dituliskan, maka VLOOKUP akan menggunakan opsi

TRUE.

Approximate Match digunakan apabila data kita bersifat continue

(range data), misalnya, angka 40 berada di antara 30 dan 50,

sementara Exact Match digunakan apabila data kita bersifat

discreet.


------------------------------------------------------------------------------------------------------------- 80

Apabila VLOOKUP tidak berhasil menemukan pasangan

lookup_value, nilai keluarannya adalah #N/A jika opsi yang

digunakan adalah EXACT MATCH.

Contoh:

Tabel di bawah ini berisikan data mengenai Density, Viscosity, dan

Temperature yang berfungsi sebagai table_array bagi aplikasi

fungsi VLOOKUP kita kali ini:

Sumber: Excel Help

=VLOOKUP(1,A2:C10,2) dengan keluaran = 2.17

Lookup_values adalah angka 1


Col_index_num adalah 2 (kolom Viscosity)

Sifat pencarian adalah APPROXIMATE MATCH (TRUE or

OMITTED)

VLOOKUP menemukan pasangan yang paling mendekati

angka 1 (atau angka terbesar yang nilainya kurang dari

nilai lookup_value atau kurang dari 1) adalah angka 0,946

dengan nilai keluaran pada kolom ke-2 adalah 2.17

Jika opsi yang digunakan adalah FALSE, maka formula ini

akan menghasilkan keluaran #N/A atau error


------------------------------------------------------------------------------------------------------------- 81

= VLOOKUP(1,A2:C10,3,TRUE) dengan keluaran 100



Col_index_num adalah 3 (kolom Temperature)


OMITTED)




dengan nilai keluaran pada kolom ke-3 adalah 100.

=VLOOKUP(0.7,A2:C10,3,FALSE)

Lookup_values adalah angka 0.7


Col_index_num adalah 3 (kolom Temperature)

Sifat pencarian adalah EXACT MATCH (FALSE)

VLOOKUP tidak menemukan pasangan dari 0.7 pada

table_array dan menghasilkan pesan berupa #N/A atau

error

=VLOOKUP(0.1,A2:C10,2,TRUE)

Lookup_values adalah angka 0.1




OMITTED)

VLOOKUP tidak menemukan pasangan dari 0.1 pada

table_array karena data terkecil pada table adalah angka

0.457 yang dalam hal ini lebih dari 0.1. untuk kasus ini

VLOOKUP menghasilkan pesan berupa #N/A atau error


------------------------------------------------------------------------------------------------------------- 82

=VLOOKUP(2,A2:C10,2,TRUE)





OMITTED)




dengan nilai keluaran pada kolom ke-2 adalah 1.71.

Latihan:

Dengan ketentuan sebagai berikut:


1 <= 120.000.000 Bawah

2 120.000.000 – 500.000.000 Menengah – Bawah

3 500.000.000 – 1.000.000.000 Menengah Atas

4 > 1.000.000.000 Atas

Kelompokkan daftar penghasilan berikut sesuai dengan

kelompoknya menggunakan fungsi VLOOKUP


1 109,636,929

2 284,816,798

3 433,225,466

4 99,569,476

5 94,730,539

6 841,625,150

7 773,270,139

8 120,924,334


------------------------------------------------------------------------------------------------------------- 83

14. Fungsi IS

Fungsi ini digunakan untuk melakukan pengujian terhadap kondisi suatu cell

tertentu. Jika cell dimaksud memenuhi pertanyaan fungsi.

Misal:

Gambar 2. 28 – Contoh ISERROR

Pada Gambar 2. 28 di atas, dapat kita cermati bahwa Cell B1 berisi formula

=ISERROR(A1) dengan keluaran bernilai TRUE karena Cell A1 memuat pesan

kesalahan #DIV/0!

Berikut adalah daftar fungsi yang tergabung dalam kelompok fungsi IS

beserta dengan kegunaannya:

Tabel 2. 10 – Tabel Fungsi IS

No FUNGSI – Syntax: BERNILAI TRUE JIKA

a. ISBLANK(value) Value mengacu pada blank cell

b. ISERR(value) Value mengacu pada cell yang berisi salah satu pesan kesalahan kecuali #N/A

c. ISERROR(value) Value mengacu pada cell yang berisi salah satu pesan kesalahan, yaitu #N/A, #VALUE, #REF!, #DIV/0!, #NUM!, #NAME?, atau #NULL!

d. ISLOGICAL(value) Value mengacu pada cell yang berisi nilai logika (TRUE atau FALSE

e. ISNA(value) Value mengacu pada cell yang berisi pesan kesalahan #N/A (value not available)

f. ISNONTEXT(value) Value mengacu pada cell yang berisi nilai non-text (angka, logika, blank)

g. ISNUMBER(value) Value mengacu pada cell yang berisi angka (numeric)

h. ISREF(value) Value mengacu pada cell yang berisi pesan kesalahan #REF! yang muncul apabila formula kehilangan cell referensinya

i. ISTEXT(value) Value mengacu pada cell yang berisi text


------------------------------------------------------------------------------------------------------------- 84

Contoh:

Berikut adalah cuplikan worksheet pada kolom A1 s.d. A6:

=ISBLANK(A2)

Value adalah ell A a g e isi kata Gold , jadi tidak koso g

Fungsi ISBLANK akan memberikan nilai keluaran FALSE

=ISERROR(A4)

Value adalah cell A4 yang berisi pesan kesalahan #REF!

Fungsi ISERROR akan memberikan nilai keluaran TRUE

=ISNA(A4)

Value adalah cell A4 yang berisi pesan kesalahan #REF!

Fungsi ISNA akan memberikan nilai keluaran FALSE, karena #REF!

bukan #N/A

=ISERR(A6)

Value adalah cell A6 yang berisi pesan kesalahan #N/A

Fungsi ISERR akan memberikan nilai keluaran FALSE, karena fungsi

ini tidak didisain untuk mengenali pesan kesalahan #N/A

=ISNUMBER(A5)

Value adalah cell A5 yang berisi angka 330.92

Fungsi ISNUMBER akan memberikan nilai keluaran TRUE

=ISTEXT(A3)

Value adalah cell A3 yang berisi kata Region 1

Fungsi ISTEXT akan memberikan nilai keluaran TRUE


------------------------------------------------------------------------------------------------------------- 85

Latihan:

Hitunglah nilai pada kolom B dengan ketentuan sebagai berikut:

Jika kolom A bernilai #N/A, maka nilai keluaran formulanya adalah 10,

sebaliknya

jika nilai cell tersebut bukan #N/A, maka isi cell tersebut dikalikan 10

data yang digunakan untuk latihan ini adalah:

15. Fungsi COUNT

a) Fungsi COUNT

Syntax:

=COUNT alue , [ alue ], …

Fungsi ini digunakan untuk menghitung jumlah cell yang mengandung

angka dari satu atau beberapa range data.

Keterangan:

Value1 harus ada dan dapat berupa satu cell atau satu range cell

Value ,…. e sifat opsio al, juga dapat e upa satu ell atau satu

range cell

Jumlah value maksimal adalah 255


------------------------------------------------------------------------------------------------------------- 86

Contoh

Hitunglah jumlah cell yang berisikan angka pada kolom SATU, DUA,

dan TIGA:

Gambar 2. 29 – Contoh Perhitungan Menggunakan Fungsi COUNT

Pada Gambar 2. 29 di atas, dapat kita cermati bahwa total cell yang

berisikan data angka/numerik berjumlah 10 cell

Perhitungan di atas menggunakan 3 range cell,

yang pertama, range A2:A7 yang oleh fungsi COUNT

diperlakukan sebagai value1

yang kedua, range D2:D7 yang oleh fungsi COUNT diperlakukan

sebagai value2

yang ketiga, range G2:G7 yang oleh fungsi COUNT diperlakukan

sebagai value3

b) Fungsi COUNTA

Syntax:

=COUNTA alue , [ alue ],…

Fungsi ini digunakan untuk menghitung jumlah cell yang tidak kosong

(blank)

Keterangan:

Value1 harus ada dan dapat berupa satu cell atau satu range cell

Value ,…. e sifat opsio al, juga dapat e upa satu ell atau satu

range cell

Jumlah value maksimal adalah 255


------------------------------------------------------------------------------------------------------------- 87

Contoh

Hitunglah jumlah cell yang berisikan angka pada kolom SATU, DUA,

dan TIGA:

Gambar 2. 30 – Contoh Perhitungan Menggunakan Fungsi COUNTA

Pada Gambar 2. 30 di atas, dapat kita cermati bahwa total cell yang

tidak kosong berjumlah 16 cell

Perhitungan di atas menggunakan 3 range cell,

yang pertama, range A2:A7 yang oleh fungsi COUNT

diperlakukan sebagai value1

yang kedua, range D2:D7 yang oleh fungsi COUNT diperlakukan

sebagai value2

yang ketiga, range G2:G7 yang oleh fungsi COUNT diperlakukan

sebagai value3

c) Fungsi DCOUNT

Syntax:

=DCOUNT(database, field, criteria)

Fungsi ini digunakan untuk menghitung cell yang mengandung angka

pada sebuah field (kolom) dari database

Keterangan:

Database bersifat wajib, merupakan satu range cell, dimana tiap

kolom dari range ini diperlakukan sebagai field database, dan baris

pertama range ini memuat nama dari tiap kolom yang diwakilinya


------------------------------------------------------------------------------------------------------------- 88

Field bersifat opsional, merupakan kolom/field tertentu yang isinya

akan kita hitung. Jika field ini tidak disertakan, maka fungsi ini akan

menggunakan seluruh cell sebagai subyek yang akan dihitung.

Criteria bersifat wajib, merupakan satu range cell yang berisikan

kriteria/dasar yang digunakan untuk penghitungan

Contoh:

Mari kita cermati data pada gambar berikut ini:

Gambar 2. 31 – Contoh Penggunaan Fungsi DCOUNT

Pada Gambar 2. 31 terdapat sebuah database dengan alamat

A4:E10, dengan field Tree, Height, Age, Yield, dan Profit .

Range A1:F3 merupakan criteria yang dijadikan acuan/dasar untuk

penghitungan

Pada kasus kali ini, kita ingin menghitung berapa banyak Pohon

a g kolo Age -nya berisi angka dengan kriteria adalah Pohon

(Tree) adalah Apple dengan tinggiantara 10 dan 16

=DCOUNT A :E , Age ,A :F , di a a:

A5:E11 adalah database

Age adalah lokasi te pat ell yang akan dihitung

A1:F2 adalah lokasi tempat kriteria (criteria range) yang akan

digunakan

Isi criteria range ini dapat dibaca sebagai berikut:

Sub Kriteria pertama: Tree harus sama dengan Apple

Sub Kriteria kedua: Height harus lebih dari 10


------------------------------------------------------------------------------------------------------------- 89

Sub Kriteria ketiga: Height harus kurang dari 16

Hubungan di antara ketiga sub kriteria ini adalah DAN

Formula =DCOUNT A :E , Age ,A :F dapat di a a se agai

berikut:

Hitu glah ju lah ell pada kolo Age a g e isi a gka

dengan ketentuan bahwa pohon tersebut adalah pohon Apple

dengan tinggi antara 10 dan 16.

Nilai keluaran formula ini adalah 1, karena pohon Apple yang

memenuhi kriteria hanya 1, yaitu Apple dengan tinggi 14(10 <

< de ga kolo /field Age sa a de ga aris ke-9)

Pengembangan Kriteria:

Kembali ke contoh pada Gambar 2. 31, ketentuan tentang

penggunaan ta da sa a de ga = atau operator ate atika

lainnya digunakan untuk mem-filter data, guna memastikan bahwa

Excel hanya memperhitungkan data pada field (kolom) tertentu yang

memenuhi kriteria yang ditetapkan.

Ketika filterisasi dilakukan untuk data berbentuk teks, misalnya

Apple , E el tidak e edaka apakah teks te se ut e e tuk

huruf kapital (uppercase) atau huruf kecil (lower case) atau Excel

menerapkan non case sensitive filter method.

Contoh penggunaan operator matematika untuk teks dan angka:

Tabel 2. 11 - Contoh Penggunaan Operator Matematika untuk Teks dan Angka

Yang Kita Ketikkan Yang Tampak pada Excel

= =Co toh =Contoh

= = =25000

= <>Co toh <>Contoh

=>25000 >25000


------------------------------------------------------------------------------------------------------------- 90

Di dalam praktiknya, sering kali kita ingin menggunakan lebih dari

satu kriteria, baik dalam artian bahwa kriteria yang kita gunakan

melebihi satu kolom (field) atau menggunakan dua sub kriteria dalam

satu kolom, atau kombinasi keduanya.

Misalnya, pada Gambar 2. 31 di atas, kita pada dasarnya

menggunakan dua kriteria, yaitu Tree dan Height, dimana untuk kolom

Tree kita hanya menggunakan satu sub kriteria, yaitu Apple, sementara

untuk kolom Height kita menggunakan dua sub kriteria, yaitu >10 dan

<16.

Excel menetapkan apabila dua kriteria (termasuk sub kriterianya)

terletak pada baris yang sama, maka berdasarkan aturan boolean

logic, hubungan antara kedua kriteria tersebut menggunakan kata AND.

Sementara apabila dua kriteria terletak pada baris yang berbeda,

maka hubungan di antara keduanya dihubungkan dengan kata OR.

Jadi, pada kasus kita se elu a, =DCOUNT A :E , Age ,A :F ,

hubungan antara kriteria yang ada pada formula tersebut adalah:

T ee = Apple AND Height > 10 AND Height < 16

Lebih jauh, kita criteria range kita perluas (perhatikan Gambar 49) dari

semula A1:F2 menjadi A1:F3, maka kriteria yang berlaku adalah:

T ee = Apple AND Height > 10 AND Height < 16 OR T ee = Pea

dengan keluaran = 3, di mana 1 untuk Apple dan 2 untuk Pear.

Latihan:

Susunlah Criteria Range dengan ketentuan bahwa kriteria yang

digunakan adalah Wanita berusia lebih dari 25 atau Pria berusia

lebih dari 30. Criteria Range menggunakan dua kolom, yaitu J.

Kelamin dan Usia

Susunlah Criteria Range dengan ketentuan bahwa kriteria yang

digunakan adalah Status = Aktif atau Usia < 56, dengan kolom

Status dan Usia


------------------------------------------------------------------------------------------------------------- 91

d) Fungsi-Fungsi DCOUNTA

Syntax:

=DCOUNTA(database, field, criteria)

Fungsi ini digunakan untuk menghitung cell yang tidak kosong (blank)

pada sebuah field (kolom) dari database

Keterangan:

Database bersifat wajib, merupakan satu range cell, dimana tiap

kolom dari range ini diperlakukan sebagai field database, dan baris

pertama range ini memuat nama dari tiap kolom yang diwakilinya

Field bersifat opsional, merupakan kolom/field tertentu yang isinya

akan kita hitung. Jika field ini tidak disertakan, maka fungsi ini akan

menggunakan seluruh cell sebagai subyek yang akan dihitung.

Criteria bersifat wajib, merupakan satu range cell yang berisikan

kriteria/dasar yang digunakan untuk penghitungan

Contoh:

Mari kita cermati data pada gambar berikut ini:

Gambar 2. 32 – Contoh Penggunaan Fungsi DCOUNTA

Pada Gambar 2. 32 terdapat sebuah database dengan alamat

A4:E10, dengan field Tree, Height, Age, Yield, dan Profit .

Range A1:F3 merupakan criteria yang dijadikan acuan/dasar untuk

penghitungan


------------------------------------------------------------------------------------------------------------- 92

Pada kasus kali ini, kita ingin menghitung berapa banyak Pohon

a g kolo Age -nya berisi angka dengan kriteria adalah Pohon

(Tree) adalah Apple dengan tinggi antara 10 dan 16

=DCOUNTA A :E , P ofit ,A :F , di a a:

A5:E11 adalah database

P ofit adalah lokasi te pat ell yang akan dihitung

A1:F2 adalah lokasi tempat kriteria (criteria range) yang akan

digunakan

Isi criteria range ini dapat dibaca sebagai berikut:

Sub Kriteria pertama: Tree harus sama dengan Apple

Sub Kriteria kedua: Height harus lebih dari 10

Sub Kriteria ketiga: Height harus kurang dari 16

Hubungan di antara ketiga sub kriteria ini adalah DAN

Formula =DCOUNTA A :E , P ofit ,A :F dapat di a a

sebagai berikut:

Hitu glah ju lah ell pada kolo P ofit a g e isi a gka

dengan ketentuan bahwa pohon tersebut adalah pohon Apple

dengan tinggi antara 10 dan 16.

Nilai keluaran formula ini adalah 1, karena pohon Apple yang

memenuhi kriteria hanya 1, yaitu Apple dengan tinggi 14 (10 <

< de ga kolo /field P ofit sa a de gan 75 (tidak

blank)

Penjelasan lebih lanjut untuk pengembangan kriteria beserta latihannya

dapat dicermati pada pembahasan tentang fungsi DCOUNT.

e) Fungsi COUNTBLANK

Syntax:

=COUNTBLANK(range)

Fungsi ini digunakan untuk menghitung cell yang kosong (blank) dalam

satu range cell.


------------------------------------------------------------------------------------------------------------- 93

Fungsi ini sering digunakan untuk mengaudit hasil data entry guna

memastikan bahwa seluruh data telah diinput ke dalam database. Salah

satu kelemahan fungsi ini adalah kita hanya dapat menghitung berapa

jumlah cell yang kosong pada tiap field atau kolom data, namun kita

tidak tahu secara pasti di mana lokasi cell dimaksud berada.

Keterangan:

Range adalah satu kelompok cell, baik berbentuk kolom, baris, atau

keduanya.

Nilai keluaran fungsi ini adalah angka

Contoh:

Berikut adalah contoh penggunaan fungsi COUNTBLANK:

Gambar 2. 33 – Contoh Penggunaan COUNTBLANK

Range yang kita gunakan pada contoh di atas adalah A2:A8

Dari hasil penghitungan kita, pada database di atas terdapat 3 cell

yang kosong.

f) Fungsi COUNTIF

Syntax:

=COUNTIF(range, criteria)

Fungsi ini digunakan untuk menghitung berapa banyak cell dalam

database yang memenuhi kriteria tunggal yang kita gunakan.


------------------------------------------------------------------------------------------------------------- 94

Keterangan:

Fungsi ini sangat efektif digunakan untuk penyusunan laporan

karena dapat dengan mudah diaplikasikan untuk melakukan

rekapitulasi data yang jumlahnya sangat besar dan dalam waktu

yang sangat singkat.

Range adalah database yang akan menjadi target penghitungan kita

Criteria merupakan pernyataan yang dapat berupa angka, teks, cell

referensi serta menjadi acuan dalam penghitungan jumlah cell

dala data ase. Co toh ite ia adalah , > , B , Ka to ,

atau .

Criteria bersifat case insensitiveartinya Excel tidak akan

e edaka a ta a Ka to , KANTOR , atau ka to .

Criteria juga dapat mengandung karakter asterisk (*) atau tanda

tanya (?)

Penggunaan tanda tanya (?) akan membatasi jumlah huruf dalam

satu kata a g di a i oleh E el. Misal a, ???a aka e uat

E el e a i kata a g e akhi a a da e ju lah tepat

huruf.

Penggunaan asterisk (*) membuat Exceltidak membatasi jumlah

huruf dari kata a g kita a i, isal a *a , aka e uat E el

e ghitu g selu uh kata a g e akhi a a .

Jumlah kriteria yang dapat digunakan hanya satu

Pastikan database tidak mengandung spasi yang tidak diperlukan,

se a E el aka e edaka data [spasi] ka to ,

ka to [spasi], da ka to , eskipu ata kita tidak dapat

membedakannya.


------------------------------------------------------------------------------------------------------------- 95

Contoh:

Perhatikan contoh berikut ini:

Gambar 2. 34 – Contoh Penggunaan COUNTIF 1

=COUNTIF(A2:A5,"apel")

A2:A5 adalah range pada formula ini

apel adalah k ite ia a g digu aka

Nilai keluaran dari formula ini adalah 2, karena terdapat dua cell

pada kolo A a g e isi kata apel , aitu pada a is ke-2 dan

ke-5.

=COUNTIF(A2:A5,A4)


Kriteria dari formula ini tersimpan pada cell A4, yaitu Mangga

Nilai keluaran dari formula ini adalah 2, karena terdapat dua cell

pada kolo A a g e isi kata a gga , aitu pada a is ke-4

dan ke-6.

=COUNTIF(A2:A5,A3)+COUNTIF(A2:A5,A2)


Formula ini merupakan operasi penjumlahan yang melibatkan

dua sub formula, yaitu =COUNTIF(A2:A5,A3) dan

=COUNTIF(A2:A5,A2)

Sub Formula 1 menggunakan kriteria yang tersimpan pada cell

A3, yaitu Jeruk, sementara Sub Formula 2 menggunakan kriteria

yang tersimpan pada cell A2, yaitu Apel

Nilai keluaran Sub Formula 1 adalah 1 dan nilai keluaran Sub

Formula 2 adalah 2, sehingga nilai keluaran Formula ini adalah 3


------------------------------------------------------------------------------------------------------------- 96

=COUNTIF(B2:B5,">55")

B2:B5 adalah range pada formula ini

> adalah k ite ia a g digu aka

Nilai keluarannya adalah 2, karena hanya ada dua cell yang

nilainya lebih dari 55, yaitu pada baris ke-4 (Mangga) dan ke-5

(Apel)

=COUNTIF(B2:B5,"<>"&B4)


<> adalah k ite ia a g digu aka , di a a a gka

merupakan isi dari cell B4

Nilai keluarannya adalah 4, yaitu semua cell yang nilainya tidak

sama dengan 75

=COUNTIF(B2:B5,">=32")-COUNTIF(B2:B5,">85")


Formula ini merupakan operasi pengurangan yang melibatkan

dua sub formula, yaitu =COUNTIF(B2:B5,">=32") dan

=COUNTIF(B2:B5,">85")

Sub Formula e ggu aka k ite ia >= , Su Fo ula

e ggu aka k ite ia >

Nilai keluaran Sub Formula 1 adalah 4 dan nilai keluaran Sub

Formula 2 adalah 1, sehingga nilai keluaran Formula ini adalah 3

Contoh:


Gambar 2. 35 – Contoh Penggunaan COUNTIF 2


------------------------------------------------------------------------------------------------------------- 97

=COUNTIF(A2:A7,"*es")

A2:A7 adalah range/database pada formula ini

*es adalah kriteria yang digunakan, dan formula ini akan

e ghitu g SELURUH kata a g e akhi a de ga es

Nilai keluaran formula ini adalah 4, karena kata yang berakhiran

es ada .

=COUNTIF(A2:A7,"?????es")


"?????es" adalah kriteria yang digunakan, dan formula ini akan

menghitung seluruh kata yang berjumlah 7 karakter dan

e akhi a es

Nilai keluaran formula ini adalah 2, karena hanya ada dua kata

yang berjumlah 7 karakter da e akhi a es

=COUNTIF(A2:A7,"*")


* adalah k ite ia a g digu aka , da fo ula ini akan

menghitung seluruh Cell yang berisikan karakter/teks

Nilai keluaran formula ini adalah 4

=COUNTIF(A2:A7,"<>"&"*")


<> & * adalah k ite ia a g digu aka , da fo ula ini akan

menghitung seluruh Cell yang tidak berisikan karakter/teks

Nilai keluaran formula ini adalah 0

Sebagaimana yang sudah kita cermati pada keterangan

sebelumnya bahwa fungsi COUNTIF ini hanya efektif digunakan untuk

formula dengan kriteria tunggal. Dan untuk tujuan yang sama, namun

menggunakan lebih dari satu kriteria, Excel menawarkan fungsi

COUNTIFS sejak Microsoft Office 2007.


------------------------------------------------------------------------------------------------------------- 98

Meski demikian, ada baiknya jika mencermati skenario berikut ini:

Jika ternyata kita harus menggunakan lebih dari satu kriteria,

sementara, karena satu dan lain hal, kita mau tidak mau harus

menggunakan Microsoft Office 2003 yang belum memiliki fungsi

multi-criteria, yaitu fungsi COUNTIFS.Lalu apa yang harus kita

lakukan?

Latihan:

Gambar 2. 36 – Latihan COUNTIF dengan Dua/Lebih Kriteria

Berdasarkan data di atas, tentukan:

Berapa kali Davolio melebihi kuota penjualannya pada periode

Juni s.d. Agustus?

Hitunglah berapa orang Sales Person yang berhasil melampaui

kuota penjualan bulan Juni dan Juli?

Hitunglah berapa kali Leverling dan Buchanan secara bersama-

sama berhasil melampaui kuotanya pada periode Juni s.d.

Agustus?

g) Fungsi COUNTIFS

Syntax:

=COUNTIFS(range1, criteria1, a ge , ite ia ,…

Fungsi ini mulai diperkenalkan pada Microsoft Office 2007 untuk

mengatasi kelemahan yang ada pada fungsi COUNTIF dan digunakan

untuk menghitung berapa banyak cell dalam database yang memenuhi

sejumlah kriteria yang kita gunakan.


------------------------------------------------------------------------------------------------------------- 99

Cara penggunaan fungsi ini sama dengan cara penggunaan fungsi

COUNTIF, hanya saja jika pada COUNTIF kita menggunakan range dan

kriteria tunggal, maka pada fungsi COUNTIFS kita menggunakan range

dan kriteria lebih dari 1.

Contoh:

Berdasarkan tabel pada Gambar 2. 36 di atas, tentukan:

Berapa kali Davolio melebihi kuota penjualannya pada periode Juni

s.d. Agustus?

Formula:

=COUNTIFS B :D , =Yes

B2:D2 adalah range yang digunakan, dalam hal ini merupakan

data de ga a a Sales Pe so = Da olio

=Yes adalah ite ia a g digu aka , dala hal i i fo ula

akan menghitung jumlah cell pada range B2:D2 yang bernilai

es

Nilai keluarannya adalah 1

Hitunglah berapa orang Sales Person yang berhasil melampaui

kuota penjualan bulan Juni dan Juli?

Formula:

=COUNTIFS(B2:B5, =Yes ,C2:C5, =Yes

Pada formula di atas terdapat dua range, yaitu B2:B5 dan C2:C5

B2:B5 adalah range untuk kuota penjualan bulan Juni,

sedangkan C2:C5 adalah range untuk kuota penjualan bulan Juli

=Yes adalah ite ia a g digu aka u tuk kedua a ge,

artinya, formula ini hanya akan menghitung cell yang dalam

kedua range- a te dapat ilai es .

Nilai keluarannya adalah 2 (Buchanan dan Suyama)


------------------------------------------------------------------------------------------------------------- 100

Hitunglah berapa kali Leverling dan Buchanan secara bersama-sama

berhasil melampaui kuotanya pada periode Juni s.d. Agustus?

=COUNTIFS B :D , =Yes ,B :D , =Yes

Pada formula di atas terdapat dua range, yaitu B2:D5 dan B3:D3

B5:D5 adalah range untuk Sales Person = Leverling, sedangkan

B3:D3 adalah range untuk Sales Person = Buchanan

=Yes adalah ite ia a g digu aka u tuk kedua a ge,

artinya, formula ini hanya akan menghitung cell yang dalam

kedua range- a te dapat ilai es

Nilai keluarannya adalah 1 (Bulan Juli, keduanya melampaui

kuota)

16. Fungsi SUM

a) Fungsi SUMPRODUCT

Syntax:

=SUMPRODUCT a a ,a a ,a a ,…

Fungsi ini akan menjumlahkan hasil kali dari tiap komponen yang setara

dari data array

Keterangan:

Array1, array2, array3, dst. berisikan data numerik

Jika satu atau beberapa cell dalam array tidak berisikan data

numerik, Excel akan menganggap nilai cell dimaksud sebagai 0.

Array yang digunakan haruslah memiliki dimensi yang sama,

misalnya sama-sama berdimensi 1 x 5, 2 x 5. Jika dimensi array tidak

sama, maka nilai keluarannya akan menjadi #VALUE!

Jumlah array yang digunakan, minimal 2 dan maksimal 255.


------------------------------------------------------------------------------------------------------------- 101

Contoh:


Gambar 2. 37 – Contoh SUMPRODUCT 1

Jika kita mengaplikasikan formula =SUMPRODUCT(A2:A4,B2:B4),

maka nilai keluaran dari formula tersebut adalah 47, dengan

penjelasan sebagai berikut:

A2:A4 adalah array1

B2:B4 adalah array2

Excel akan menghitung hasil kali dari masing-masing baris pada

kedua array tersebut, yaitu 2 x 4, 3 x 5, dan 4 x 6

Selanjutnya Excel akan menjumlahkan hasil dari perkalian

dimaksud, yaitu 8 + 15 + 24, sehingga nilai keluaran akhirnya

adalah 47

Perhatikan contoh selanjutnya:

Gambar 2. 38 – Contoh SUMPRODUCT 2

Jika kita mengaplikasikan formula =SUMPRODUCT(A2:B4,C2:D4),

maka nilai keluaran dari formula tersebut adalah 202, dengan

penjelasan sebagai berikut:

Array yang digunakan adalah array dengan dimensi 3 x 2

A2:B4 adalah array1

C2:D4 adalah array2


------------------------------------------------------------------------------------------------------------- 102

Excel akan menghitung hasil kali dari masing-masing Cell yang

setara pada kedua array tersebut, yaitu 2 (DATA1) x 6 (DATA3),

3 (DATA1) x 7 (DATA3), 4 (DATA1) x 8 (DATA3), 4 (DATA2) x 8

(DATA4), 5 (DATA2) x 9 (DATA4), dan 6 (DATA2) x 10 (DATA4)

Nilai keluaran formula ini adalah 202 yang merupakan hasil

penjumlahan dari data hasil perkalian di atas

Latihan:

Menggunakan fungsi SUMPRODUCT, hitunglah hasil perkalian

antara kolom DATA1, DATA2, DATA3, dan DATA4 berdasarkan

informasi pada Gambar 56 di atas.

b) Fungsi SUMIF

Syntax:

=SUMIF(range, criteria, sum_range)

Fungsi ini digunakan untuk menghitung total nilai dari suatu sejumlah

cell yang memenuhi kriteria yang ditetapkan.

Keterangan:

Fungsi ini sangat efektif digunakan untuk penyusunan laporan

karena dapat dengan mudah diaplikasikan untuk melakukan

rekapitulasi data yang jumlahnya sangat besar dan dalam waktu

yang sangat singkat.

Range adalah database yang akan menjadi target penghitungan kita

Criteria merupakan pernyataan yang dapat berupa angka, teks, cell

referensi serta menjadi acuan dalam penghitungan jumlah cell

dala data ase. Co toh ite ia adalah , > , B , Ka to ,

atau .

Criteria bersifat case insensitive artinya Excel tidak akan

e edaka a ta a Ka to , KANTOR , atau ka to .

Criteria juga dapat mengandung karakter asterisk (*) atau tanda

tanya (?)


------------------------------------------------------------------------------------------------------------- 103

Penggunaan tanda tanya (?) akan membatasi jumlah huruf dalam

satu kata a g di a i oleh E el. Misal a, ???a aka e uat

Ex el e a i kata a g e akhi a a da e ju lah tepat

huruf.

Penggunaan asterisk (*) membuat Excel tidak membatasi jumlah

hu uf da i kata a g kita a i, isal a *a , aka e uat E el

menghitung seluruh kata yang e akhi a a .

Jumlah kriteria yang dapat digunakan hanya satu (tunggal)

Pastikan database tidak mengandung spasi yang tidak diperlukan,

se a E el aka e edaka data [spasi] ka to ,

ka to [spasi], da ka to , eskipu ata kita tidak dapat

membedakannya.

sum_range adalah range baik berupa baris atau kolom yang

berisikan nilai yang ingin kita jumlahkan.

Contoh:

Mari kita cermati ilustrasi berikut ini:

Gambar 2. 39 – Contoh Penggunaan SUMIF

=SUMIF(A2:A5, > ,B2:B5)

Formula ini digunakan untuk menghitung total komisi untuk

properti yang bernilai di atas USD160.000

A2:A5 adalah range untuk formula ini dan berisikan daftar Nilai

Properti

B2:B5 adalah sum_range dari formula ini dan berisikan daftar

Komisi yang akan dijumlahkan

> adalah k ite ia a g digu aka u tuk e atasi

proses penjumlahan


------------------------------------------------------------------------------------------------------------- 104

Nilai keluaran formula ini adalah 63.000, yaitu merupakan total

komisi dari properti yang bernilai 200.000, 300.000, dan

400.000 (lebih dari 160.000)

=SUMIF(A2:A5, >

Formula ini digunakan untuk menghitung total nilai properti

yang masing-masingnya lebih dari USD 160.000

A2:A5 merupakan range pada formula ini

Karena pada formulaTIDAK TERDAPAT sum_range, maka range

secara otomatis juga berfungsi sebagai sum_range

Nilai keluaran formula ini adalah 900.000, yaitu total dari

200.000, 300.000, dan 400.000

=SUMIF(A2:A5, = ,B :B

Formula ini digunakan untuk menghitung nilai komisi untuk

properti yang bernilai sama dengan USD300.000

A2:A5 adalah range untuk formula ini dan berisikan daftar Nilai

Properti

B2:B5 adalah sum_range dari formula ini dan berisikan daftar

Komisi yang akan dijumlahkan

= adalah k ite ia a g digu aka u tuk e atasi

proses penjumlahan

Nilai keluaran formula ini adalah 21.000

Serupa dengan fungsi COUNTIF, fungsi SUMIF ini hanya efektif

digunakan untuk formula dengan kriteria tunggal. Dan untuk tujuan

yang sama, namun menggunakan lebih dari satu kriteria, Excel

menawarkan fungsi SUMIFS sejak Microsoft Office 2007.

Latihan:

Berdasarkan tabel pada Gambar 58, hitunglah total nilai seluruh

rekening bank yang suku bunganya lebih dari 3% untuk tahun 2000

serta lebih dari atau sama dengan 2% untuk tahun 2001


------------------------------------------------------------------------------------------------------------- 105

c) Fungsi SUMIFS

Syntax:

=SUMIFS su _ a ge, ite ia_ a ge , ite ia ,…

Fungsi ini digunakan untuk menghitung total nilai dari suatu sejumlah

cell yang memenuhi sejumlah kriteria yang ditetapkan.

Keterangan:

Jika dibandingkan dengan fungsi SUMIF, fungsi SUMIFS selain

mengakomodasi lebih dari satu kriteria, juga jika kita perhatikan,

posisi sum_range-nya harus berada paling awal.

ite ia_ a ge , ite ia_ a ge … i i al ada ite ia_ a ge da

maksimal 127

ite ia , ite ia …. i i al ada ite ia da aksi al

Contoh:

Mari kita cermati contoh berikut ini

Gambar 2. 40 – Contoh Penggunaan SUMIFS

Berdasarkan tabel pada Gambar 2. 40, hitunglah total nilai seluruh

rekening bank yang suku bunganya lebih dari 3% untuk tahun 2000

serta lebih dari atau sama dengan 2% untuk tahun 2001

Formula:=SUMIFS(B2:E2,B3:E3, > % ,B4:E4, >= %

B2:E2 adalah sum_range dari formula ini dan menggambarkan

saldo akhir dari tiap rekening bank, yaitu 100, 390, 8321, dan

500 untuk masing-masing Account1 s.d. Account 4

B3:E2 adalah criteria_range pertama dan berisikan daftar suku

bunga yang berlaku untuk masing-masing rekening pada tahun

2000


------------------------------------------------------------------------------------------------------------- 106

B4:E4 adalah criteria_range kedua dan berisikan daftar suku

bunga yang berlaku untuk masing-masing rekening pada tahun

2001

> % adalah ite ia pe ta a a g aka diaplikasika ke

criteria_range pertama

>= % adalah ite ia kedua a g aka diaplikasika ke

criteria_range kedua

Nilai keluaran dari formula ini adalah 500

Berdasarkan tabel pada Gambar 2. 40, hitunglah total nilai seluruh

rekening bank dengan persyaratan bahwa suku bunganya berada di

antara 1% dan 3% untuk tahun 2002 dan lebih dari 1% untuk tahun

2001

Formula:

=SUMIFS(B2:E2,B :E , >= % ,B :E , <= % ,B :E , > %

B2:E2 adalah sum_range dari formula ini dan menggambarkan

saldo akhir dari tiap rekening bank, yaitu 100, 390, 8321, dan

500 untuk masing-masing Account1 s.d. Account 4

B :E , >= % ,B :E , <= % adalah persyaratan pertama yang

mensyaratkan bahwa tingkat bunga berada di antara 1% dan

3% untuk tahun 2002.

B5:E5 adalah criteria_range pertama yang berisikan daftar

tingkat bunga yang berlaku pada tahun 2002

B :E , > % merupakan persyaratan kedua yang mensyaratkan

tingkat bunga lebih dari 1% pada tahun 2001

B4:E4 adalah criteria_range kedua yang berisikan daftar tingkat

bunga yang berlaku pada tahun 2001


------------------------------------------------------------------------------------------------------------- 107

Latihan:

Perhatikan gambar berikut:

Berdasarkan gambar di atas, hitunglah:

Total nilai curah hujan (rainfall) untuk hari dengan suhu udara

rata-rata paling tidak 40 derajat Fahrenheit dan kecepatan

angin rata-rata kurang dari 10 mil per jam

b. Rangkuman

Sebuah fungsi terdiri atas nama fungsi yang bersangkutan diikuti dengan

sejumlah argumen yang mengiringinya.

Setiap argumen dapat diganti dengan fungsi lain yang keluarannya sesuai

dengan yang diinginkan argumen tersebut

Menguasai teknik penggunaan sebuah fungsi tidak hanya sebatas memahami

teori dan praktik standarnya saja. Untuk mempercepat pemahaman, kita perlu

mempelajari teknik-teknik yang dikembangkan orang lain dan mempraktikkan-

nya dalam pekerjaan sehari-hari untuk meningkatkan pemahaman dan

keterampilan kita dalam memanfaatkan kekuatan dari fungsi-fungsi tersebut.

Tidak penting seberapa banyak fungsi yang kita ketahui kalau kita tidak tahu

bagaimana memanfaatkan fungsi-fungsi dimaksud secara optimal. Oleh

karenanya sangat disarankan agar kita senantiasa berlatih untuk meningkatkan

kemampuan kita dalam memanfaatkan fungsi-fungsi dimaksud.

Teknik-teknik yang diperkenalkan pada modul ini diharapkan akan memberikan

inspirasi bagi kita semua untuk mengeksplorasi dan mengeksploitasi

kemampuan yang dimiliki oleh Microsoft Excel 2010 hingga ke level tertentu

yang memungkinkan kita semua bekerja secara lebih cepat, lebih efisien, dan

lebih akurat.


------------------------------------------------------------------------------------------------------------- 108


Pilihlah B jika menurut Saudara pernyataan di bawah ini benar dan S jika salah.

1. B S Kelua a da i fo ula =MID , , adalah

2. B S Fungsi IFEROR digunakan untuk mengantisipasi timbulnya pesan

kesalahan yang dihasilkan Excel dari proses/formula yang digunakan

untuk menghasilkan value tertentu, agar kesalahan yang timbul tidak

menyebabkan proses penghitungan terhenti (error trapping).

3. B S Operasi matematika berikut memberikan nilai keluaran 1+2x2 sama

dengan 6

4. B S Fungsi COUNTBLANK digunakan untuk menghitung jumlah Cell dalam

sebuah worksheet yang bernilai nol

5. B S Fungsi HLOOKUP akan mencari keberadaan lookup_value pada baris

pertama table_array dan mencarikan keluaran yang sesuai pada baris

data yang ditunjukkan oleh nilai dari row_index_num dengan sistem

pencarian sesuai dengan range_lookup

6. B S Fo ula =RIGHT MID " ", , , adalah

7. B S Formula =RANDBETWEEN(1/1/2000,1/1/2012) akan secara

random/acak menghasilkan data tanggal antara 1/1/2000 s.d.

1/1/2012

8. B S Fungsi DCOUNT digunakan untuk menghitung cell yang mengandung

teks/string pada sebuah field (kolom) dari database

9. B S Fungsi Autocomplete memungkinkan kita untuk tidak mengetikkan

kurung penutup pada sebuah formula

10. B S Fasilitas Visual Basic for Application yang disediakan Excel

memungkinkan kita menciptakan fungsi yang benar-benar sesuai

dengan kebutuhan kita.


------------------------------------------------------------------------------------------------------------- 109


Periksalah jawaban anda dengan kunci jawaban tes formatif yang ada di

bagian belakang modul ini. Hitunglah jumlah jawaban anda yang sesuai dengan

kunci jawaban, kemudian gunakan rumus di bawah ini untuk mengetahui tingkat

penguasaan anda terhadap materi.

Penjelasan tingkat penguasaan:

91 % - 100 % : Amat Baik

81 % - 90.99 % : Baik

71 % - 80.99 % : Cukup

61 % - 70.99 % : Kurang

0 % - 60.99 % : Amat Kurang

Jika anda telah mencapai tingkat penguasaan 80% keatas, Saudara dapat

meneruskan dengan materi selanjutnya. Tetapi jika nilai anda kurang dari 80%, maka

anda harus mengulangi materi ini terutama yang belum anda kuasai.

KEGIATAN BELAJAR 3 : MENYUSUN LAPORAN

------------------------------------------------------------------------------------------------------------- 110

3. Kegiatan Belajar 3 : MENYUSUN LAPORAN


1) Menggunakan Conditional Formatting

Fasilitas Conditional Formatting merupakan fasilitas yang disediakan Excel

yang memungkinkan kita untuk mengubah format suatu cell atau range sesuai

dengan kondisi yang kita tetapkan. Misalnya, kita akan mewarnai suatu cell dengan

warna merah menyala, jika nilai dari cell tersebut lebih dari 100.

Selain bermanfaat untuk memperindah tampilan laporan yang kita susun,

Conditional Formatting juga efektif digunakan untuk tujuan audit, misalnya, kita

bermaksud menandai cell/record tertentu yang tidak memenuhi atau memenuhi

persyaratan tertentu yang kita tetapkan dan hal ini sangat bermanfaat terutama jika

kita bekerja dengan data yang besar.

Berikut adalah hal-hal yang harus kita ketahui tentang fasilitas ini:

a. Sesuai namanya, format cell atau range yang kita atur melalui fasilitas ini dapat

berubah-ubah (dinamis) sesuai isi atau nilai dari sebuah cell atau range,

misalnya warna dasar sebuah cell berubah menjadi merah jika nilai cell

dimaksud kurang dari 50 dan warna dasar cell dimaksud berubah menjadi hijau

jika nilainya lebih dari 95.

INDIKATOR

Setelah mengikuti pembelajaran ini peserta diklat diharapkan dapat secara efektif

menggunakan fasilitas Excel berikut dalalm penyusunan laporan:

Conditional Formatting;

Remove Duplicates dan Sort;

Filter;

Pivot Table; dan

Goal Seek

INDIKATOR


------------------------------------------------------------------------------------------------------------- 111

b. Tidak seperti pada versi sebelumnya, dimana Jumlah Conditional Formatting

yang dapat digunakan untuk satu Cell/Range dibatasi maksimal 3 buah, maka

pada versi 2010 ini jumlah conditional format hanya dibatasi oleh kemampuan

memory komputer.

c. Fasilitas ini dapat diakses melalui menu (tab) Home dan menu Conditional

Formatting sebagaimana tampak pada gambar berikut ini:

Gambar 3. 1 - Akses terhadap Menu Conditional Formatting

d. Terdapat 5 Presets Menu, yaitu Highlight Cells Rules, Top/Bottom Rules, Data

Bars, Color Scales, dan Icon Sets dan 3 Menu tambahan, yaitu New Rules, Clear

Rules, dan Manages Rules yang memungkinkan kita untuk melakukan

pengaturan secara manual.

Berikut adalah ilustrasi Presets Menu dan Manual Menu pada Conditional

Formatting:


------------------------------------------------------------------------------------------------------------- 112

Gambar 3. 2 - Menu Conditional Formatting

Berikut adalah sejumlah penjelasan dan contoh penggunaan dari masing-masing

menu Presets.

1) Menu Highlight Cells Rules

Menu ini digunakan untuk mengubah format cell atau range yang

memenuhi kondisi tertentu, misalnya:

- Le ih da i G eate Tha …

- Ku a g da i Less Tha …

- Le ih da i atau Ku a g da i Bet ee …

- Sa a de ga E ual To…

- Mengandung teks tertentu (Text that Co tai s…

- Data Tanggal Tertentu (a Date O u i g…

- Data Ke a Duplli ate Value…

Presets Menu


------------------------------------------------------------------------------------------------------------- 113

Gambar 3. 3 – Highlight Cells Rules

Co toh Pe ggu aa Me u G eate Tha …:

Pada gambar berikut terdapat satu kolom angka yang kita beri nama

DATA

Gambar 3. 4 – Contoh Data


------------------------------------------------------------------------------------------------------------- 114

Pada skenario yang kita gunakan, kita akan menandai (highlight) seluruh

data yang bernilai lebih dari 30

Cara yang kita tempuh untuk melakukannya adalah sebagai berikut:

Kita sorot kolom data, mulai dari Cell A2 sampai dengan Cell A12

(A2:A12)

Lalu klik Menu Home, Conditional Formatting, Highlight Cell Rules,

G eate Tha … a g aka e u ulka kotak dialog e ikut:

Ubah Nilainya menjadi 30

Gambar 3. 5 – Contoh Penggunaan Menu Greater Than 1

Kita ubah nilai input field dari 26.5 menjadi 30 sehingga hasilnya

akan tampak sebagai berikut:

Gambar 3. 6 – Contoh Penggunaan Menu Greater Than 2

Setelah nilai input field kita ganti menjadi 30 (lalu tekan ENTER atau

klik OK), maka bagian dari data yang di-highlight adalah A5 (41), A9

(34), A10 (38), dan A12 (37) yang nilainya lebih dari 30.

Format yang digunakan untuk menandai cell dapat diubah melalui

combo box yang disediakan (Gambar 3. 6), baik sesuai preset

formats maupun custom formats.

Ubah format Highlight


------------------------------------------------------------------------------------------------------------- 115

2) Menu Top/Bottom Rules

Serupa dengan menu Highlight Cells Rules sebelumnya, menu ini digunakan

untuk mengatur format cell atau range dengan aturan tertentu, misalnya, 10

besar, top 10%, 10 terendah, di atas rata-rata, atau di bawah rata-rata.

Gambar 3. 7 – Menu Top/Bottom Rules

3) Menu Data Bars

Menu Data Bars ini digunakan untuk menampilkan grafik diagram

batang yang disajikan secara horizontal pada masing-masing cell yang berisi

data dalam range yang dipilih. Ukuan dari masing-masing batang pada tiap

cell tergantung nilai relatifnya dibanding cell lainnya dalam range yang kita

pilih. Karena sifat penyajiannya, maka fasilitas ini sangat cocok digunakan

untuk jumlah data yang relatif sedikit/terbatas atau fasilitas ini tidak cocok

untuk data yang berjumlah besar.


------------------------------------------------------------------------------------------------------------- 116

Gambar 3. 8 – Menu Data Bars

Terdapat dua pilihan bagi kita ketika menggunakan fasilitas ini, yaitu

Gradient Fill atau Solid Fill. Ketika pilihan yang tersedia kita pilih, Excel

secara otomatis mengubah tampilan range data yang kita miliki.

Berikut adalah contoh penggunaan Data Bars dengan menggunakan data

yang disajikan pada Gambar 3. 8 di atas.

Gambar 3. 9 – Contoh Penggunaan Menu Data Bars


------------------------------------------------------------------------------------------------------------- 117

4) Menu Color Scales

Serupa dengan menu Data Bars, menu Color Scales ini secara

otomatis akan mewarnai setiap cell pada range data yang kita pilih

berdasarkan pilihan yang tersedia pada menu.

Berikut adalah contoh penggunaan Color Scales dengan menggunakan data

yang disajikan pada Gambar 3. 4 di atas.

Gambar 3. 10 – Contoh Penggunaan Menu Color Scales

5) Menu Icon Sets

Selain penggunaan warna, Excel juga menawarkan fasilitas yang

memungkinkan pengguna memanfaatkan icon-icon tertentu sebagai bagian

dari format sebuah cell atau range.

Berikut adalah contoh penggunaan Menu Icon Sets dengan menggunakan

data yang disajikan pada Gambar 3. 4 di atas.


------------------------------------------------------------------------------------------------------------- 118

Gambar 3. 11 – Contoh Penggunaan Menu Icon Sets

e. Conditional Formatting yang sudah kita tetapkan dapat dihapus menggunakan

menu Clear Rules, dengan pilihan sebagai berikut:

1) Menghapus seluruh Conditional Formatting dari cell/kumpulan cell yang kita

pilih

2) Menghapus seluruh Conditional Formatting yang ada pada active sheet.

3) Menghapus seluruh Conditional Formatting dari tabel tertentu

4) Menghapus seluruh Conditional Formatting dari pivot table tertentu

f. Di samping menu PRESETS yang penggunaannya sangat intuitif, Excel juga

menyediakan fasilias yang memungkinkan kita untuk mengubah-ubah format

sebuah Cell atau Range dengan aturan yang kita tetapkan sendiri.

Fasilitas ini dapat kita akses menggunakan menu New Rule yang akan

menyajikan kotak dialog pada gambar di bawah ini. Pada kotak dialog terdapat

enam pilihan yang dapat kita pilih sesuai kebutuhan kita.


------------------------------------------------------------------------------------------------------------- 119

Gambar 3. 12 – Contoh Penggunaan Menu New Rule

Adapun pilihan dimaksud adalah:

1) Memformat seluruh cell berdasarkan nilai relatifnya

2) Memformat kumpulan cell yang mengandung nilai tertentu

3) Memformat kumpulan cell yang berada pada ranking atas atau ranking

bawah

4) Memformat kumpulan cell yang berada di atas atau di bawah nilai rata-rata

5) Memformat kumpulan cell yang unik atau kembar

6) Memformat kumpulan cell berdasarkan formula tertentu

Perlu kita ketahui bahwa seluruh menu preset yang disajikan sebelumnya

dikembangkan menggunakan fasilitas ini. Dengan latihan yang rutin, kita dapat

mengimitasi fasilitas presets tersebut bahkan mengembangkannya lebih jauh

agar memberi manfaat yang maksimal bagi kita.

Dari keenam pilihan yang disajikan pada Gambar 3. 12 di atas, kita akan sedikit

mengeksplorasi pilihan ke-2 dan ke-6, yaitu Format only cells that contain dan

Use a formula to determine which cells to format.


------------------------------------------------------------------------------------------------------------- 120

1) Format only cells that contain

Ketika pilihan ini dipilih, maka akan tampak kotak dialog sebagaimana

tampak pada Gambar 3. 13 di bawah.

Pengaturan

Format Cell

1 2

Gambar 3. 13 – Contoh Penggunaan Menu Format only cells that contain

Kotak dialog dimaksud mengharuskan kita memilih salah satu dari beberapa

opsi pada pilihan 1 (lihat gambar) yang jika kita klik akan memunculkan

sejumlah pilihan, yaitu:

Cell Value

Pilihan ini digunakan untuk mengatur Format Cell atau kumpulan cell

(menandai) yang mengandung nilai tertentu, misalnya (Pilihan 2 – Lihat

gambar):

between (nilai cell berada di antara dua nilai yang kita tetapkan)

not between (nilai cell tidak berada di antara dua nilai yang kita

tetapkan)

equal to (nilai cell sama dengan nilai yang kita tetapkan)

not equal to (nilai cell tidak sama dengan nilai yang kita tetapkan)

greater than (nilai cell lebih dari nilai yang kita tetapkan)

less than (nilai cell kurang dari nilai yang kita tetapkan)

greater than or equal to (nilai cell lebih dari atau sama dengan nilai

yang kita tetapkan)


------------------------------------------------------------------------------------------------------------- 121

less than or equal to (nilai cell kurang dari atau sama dengan nilai

yang kita tetapkan)

Format dari cell yang memenuhi syarat diatur melalui menu format

(Gambar 3. 13).

Specific Text

Pilihan ini digunakan untuk mengatur Format cell atau kumpulan cell

yang mengandung teks tertentu. Jika pilihan ini dipilih, kita diharuskan

untuk memilih apakah cell yang akan diformat:

Gambar 3. 14 – Contoh Penggunaan Menu Spesific Text

containing (mengandung teks tertentu)

not containing (tidak mengandung teks tertentu)

beginning with (dimulai dengan teks tertentu)

ending with (diakhiri dengan teks tertentu)


(Gambar 3. 13).

Dates Occurring

Pilihan ini digunakan untuk mengatur format cell atau kumpulan cell

yang memuat data tanggal tertentu yang dihitung dari tanggal sistem

pada hari ini.

Gambar 3. 15 – Contoh Penggunaan Menu Dates Occuring


------------------------------------------------------------------------------------------------------------- 122

Pilihan yang tersedia adalah:

Yesterday (kemarin)

Today (hari ini)

Tomorrow (besok)

In the last 7 days (7 hari terakhir)

Last week (1 minggu yang lalu)

This week (minggu ini)

Next week (minggu depan)

Last month (1 bulan yang lalu)

This month (bulan ini)


(Gambar 3. 13).

Blanks


(menandai) yang tidak berisi data sama sekali (blank)


(Gambar 3. 13).

No Blanks


(menandai) yang tidak blank


(Gambar 3. 13).

Errors


(menandai) yang mengandung error (misalnya #N/A!, #VALUE, dan

#DIV/0!)


(Gambar 3. 13).


------------------------------------------------------------------------------------------------------------- 123

No Errors


(menandai) yang tidak mengandung error


(Gambar 3. 13).

2) Use a formula to determine which cells to format

Ketika pilihan ini dipilih, maka akan tampak kotak dialog sebagaimana

tampak pada gambar berikut:

Gambar 3. 16 – Contoh Penggunaan Menu Use a formula to deter i e…

Pilihan penggunaan formula memungkinkan kita untuk lebih leluasa

mengatur format sebuah cell atau kumpulan cell di luar pre-defined rules

yang ditawarkan Excel melalui sejumlah pilihan lainnya.

Di samping itu, penggunaan formula ini memungkinkan kita untuk

mengatur format sebuah Cell atau kumpulan Cell berdasarkan nilai sebuah

Cell tertentu. Hal ini berbeda dari fasilitas sebelumnya yang telah kita

pelajari, dimana kita hanya dapat mengatur format sebuah cell berdasarkan

nilai dari cell yang bersangkutan.

Formula diinput di sini


------------------------------------------------------------------------------------------------------------- 124

Contoh 1:

Kita bermaksud menandai sejumlah cell dalam baris yang sama dengan

cell yang ada pada kolom E, jika cell pada kolom F dan baris tertentu

bernilai sama dengan 10.

Formula yang digunakan pada prinsipnya sangat simpel, yaitu

=$F(nobaris)=10 se agai a a a g dapat kita e ati pada Ga a

75 berikut:

Tanda dollar ($) pada alamat yang digunakan dalam formula di atas

dibutuhkan agar acuan Excel dalam menentukan format cell atau

kumpulan cell tidak berubah, yaitu cell yang ada pada kolom F.

Format cell yang kita pilih adalah warna latar hitam (black) dan tulisan

putih tebal (white, bold)


------------------------------------------------------------------------------------------------------------- 125

Gambar 3. 17 – Contoh Penggunaan Formula untuk Conditional Formatting 1

Hasil eksekusi formula ini tampak pada gambar berikut:

Gambar 3. 18 – Contoh Hasil Penggunaan Formula untuk Conditional Formatting 1

Tampak bahwa seluruh cell yang berada pada baris yang sama dengan

cell pada kolom F yang mengandung nilai 10 diwarnai dengan warna

latar hitam dan tulisan putih tebal.


------------------------------------------------------------------------------------------------------------- 126

Contoh 2:

Kita bermaksud mengarsir lembar laporan kita dengan ketentuan bahwa

tiap baris genap akan berwarna abu-abu atau warna lainnya yang

menurut kita akan membuat presentasi laporan kita menjadi lebih

menarik.

Selama ini kita mewarnai baris genap tersebut secara manual dan cara

ini terasa merepotkan terutama jika ada perubahan dalam laporan yang

mengharuskan kita menggeser satu baris tertentu atau harus

menambah atau mengurangi satu baris tertentu.

Penggunaan formula dalam kasus ini akan membuat laporan kita

menjadi sangat fleksibel dari sisi presentasi, karena Excel lah yang akan

mengatur formatnya untuk kita secara otomatis.

Gambar 3. 19 – Contoh Penggunaan Formula untuk Conditional Formatting 2


------------------------------------------------------------------------------------------------------------- 127

Formula yang digunakan pada prinsipnya sangat sederhana, yaitu

=MOD(ROW(),2)=0

=MOD(number, divisor) adalah fungsi yang akan menghasilkan nilai

keluaran berupa nilai sisa dari hasil bagi antara number dan divisor,

misalnya number =3 dan divisor = 2, maka MOD atau modulus-nya

adalah 1. Pada kasus kita, format cell hanya berlaku jika modulus = 0.

=ROW() adalah fungsi yang akan menghasilkan nilai keluaran berupa

nomor baris dari sebuah cell, misalnya Cell A100 memiliki nomor baris

sa a de ga atau jika fu gsi i i digu aka , =ROW(A100) , aka

nilai keluarannya adalah 100. Jika fungsi ROW() tidak disertai argumen,

maka Excel akan menggunakan nomor baris tempat fungsi itu

diinputkan sebagai argumennya.

Mari kita cermati ilustrasi pada Gambar 3. 13 di atas:

Range yang kita targetkan untuk pengaturan format ini adalah A1:F5

Format yang kita gunakan adalah warna latar abu-abu dan tulisan

hitam normal

Formula yang kita gunakan adalah =MOD(ROW(),2)=0 yang artinya

SETIAP BARIS GENAP (2, 4, 6, dst) dalam range yang menjadi target

akan diubah warna latarnya menjadi abu-abu

Jika kita ingin mewarnai SETIAP BARIS GANJIL (1, 3, 5, dst) dan

bukan baris genap, maka sedikit penyesuaian harus dilakukan

terhadap formula yang kita gunakan dengan mengubahnya menjadi

=MOD(ROW(),2) ta pa =0 se agai pe utup.

Hasil eksekusi formula ini adalah sebagai berikut:

Gambar 3. 20 – Contoh Hasil Penggunaan Formula untuk Conditional Formatting 2


------------------------------------------------------------------------------------------------------------- 128

2) Mengurutkan Data

Fasilitas SORT untuk mengurutkan data telah tersedia sejak versi awal Excel bahkan

jauh sebelumnya. Fasilitas sortir ini digunakan untuk mengurutkan data menurut

aturan:

Ascending (dari yang terkecil ke terbesar); dan

Descending (dari yang terbesar ke terkecil)

baik untuk data angka (numeric) maupun teks (text).

Menu SORT dapat diakses melalui menu DATA sebagaimana tampak pada gambar

berikut:

Gambar 3. 21 – Menu Data Sort

Untuk memudahkan pembahasan kita mengenai penggunaan menu SORT ini akan

lebih mudah jika menggunakan ilustrasi sebagai berikut:

Data yang kita gunakan tampak pada gambar berikut:

Gambar 3. 22 – Contoh Data untuk Menu SORT

Untuk SECARA CEPAT menggunakan menu SORT ini, kita harus menempatkan

pointer pada cell tertentu dalam tabel yang kita gunakan (tidak harus cell pada

baris pertama), misalnya pointer kita tempatkan pada Cell D5, yaitu kolom bulan

April dengan nilai 96.

SORT


------------------------------------------------------------------------------------------------------------- 129

Lalu kita klik icon sebagai mana tampak pada gambar di bawah ini, untuk

menghasilkan hasil sortir otomatis yang dilaksanakan berdasarkan nilai cell yang

ada pada kolom D (APR), yang menjadi kolom kunci terhadap range data A2:L9.

posisi

pointer

basis (seed) untuk sort

Ascending atau

Descending

Gambar 3. 23 – Contoh Hasil Sort dengan Cara Cepat

Meskipun akses cepat ini memberikan kita kemudahan dalam melakukan

pengu uta data, a u se agai a a juga p oduk i sta lai a, fasilitas

cepat ini mengandung sejumlah kelemahan, antara lain dapat kita cermati pada

ilustrasi berikut:

data

kembar

Gambar 3. 24 – Contoh Kelemahan Sort dengan Cara Cepat

Sebagaimana yang tampak pada Gambar 3. 24 di atas, pada baris ke-6 dan ke-7

terdapat nilai cell yang sama yaitu 27. Masalah ini merupakan masalah penting

karena kolom D tempat kedua cell dimaksud merupakan kolom yang menjadi

KUNCI dalam melakukan sort. Masalah akan menjadi kian pelik jika kita

bermaksud melakukan penetapan peringkat (ranking), misalnya dalam rangka

menetapkan pembayar pajak terbesar atau ranking kelulusan peserta ujian.


------------------------------------------------------------------------------------------------------------- 130

Intinya, pada saat ini kita tidak memiliki alasan tertentu dan berdasar untuk

menetapkan bahwa isi Cell D6 harus berada di atas isi Cell D7 karena nilai

keduanya sama dan identik.

Untuk mengatasi permasalahan ini, kita tidak dapat mengandalkan fasilitas sort

secara cepat karena membutuhkan lebih dari satu kriteria (seeds) agar proses

pengurutan data menjadi lebih mulus dan dapat dipertanggungjawabkan.

Dalam konteks dengan pekerjaan, untuk melakuan penetapan kriteria ini ada

baiknya jika kita berkonsultasi dengan pimpinan kita atau mendasarkan kriteria

yang digunakan pada peraturan yang berlaku, baik berupa SK, SE, Nota Dinas,

atau praktik yang berlaku secara umum, agar hasil pengurutan data yang kita

lakukan dapat dipertanggungjawabkan secara formal.

Semakin banyak kriteria (seeding/level) yang digunakan akan semakin baik guna

menghilangkan kemungkinan timbulnya masalah sebagaimana yang

diilustrasikan sebelumnya.

Misalnya, disepakati bahwa seeding (kriteria) yang dibutuhkan untuk melakukan

sortir adalah data bulan April (Ascending – A), Juni (Descending – D), Agustus

(A), dan September (D)

Cara yang ditempuh adalah dengan memanfaatkan menu SORT sebagaimana

tampak pada ilustrasi berikut:

Gambar 3. 25 – Menu SORT

Untuk menggunakan fasilitas ini, langkah pertama yang kita lakukan adalah

menyorot range yang akan kita sort, yaitu A1:L9

SORT


------------------------------------------------------------------------------------------------------------- 131

Lalu icon SORT kita klik yang akan menghadirkan kotak dialog sebagai berikut:

header

seeds

Gambar 3. 26 – Fasilitas pada Menu Sort

Terdapat sejumlah pilihan yang dapat diambil pada kotak dialog di atas.

Pertama, jika data kita memiliki header, maka kita harus mencentang pilihan

M data has headers , yang artinya data pada baris pertama dalam range yang

kita pilih tidak akan diikutsertakan dalam proses pengurutan data yang

dilakukan.

Jika kita cermati data kita sebagaimana tampak pada Gambar 80 dan Gambar 82

di atas, data kita memiliki header.

Dan jika data yang akan di-sort tidak memiliki header, pastikan opsi ini tidak

dicentang, karena jika dicentang maka data pada baris pertama tidak akan

diikutkan dalam proses sort.

Pada ilustrasi di atas, kolom kunci untuk melakukan sortir adalah kolom dengan

label APR (kolom D), yang disort berdasarkan value-nya, dan menggunakan

aturan ascending (smallest to largest).

Untuk menambah Level atau kriteria yang digunakan cukup dilakukan dengan

e eka to ol Add Level atau jika kita i gi e ghapus le el atau k ite ia

te te tu ukup dilakuka de ga e eka to ol Delete Level . Di sa pi g

itu, kita juga dapat e a faatka fasilitas Copy Level u tuk e pe udah

kita dalam melakukan penetapan kriteria.


------------------------------------------------------------------------------------------------------------- 132

Gambar 3. 27 – Sort Options

Level yang berada pada urutan pertama merupakan kriteria pertama yang

digunakan Excel dalam melakukan SORT.

Secara default, sortir dilakukan secara vertikal, berdasarkan nilai, warna cell,

icon yang ada pada cell, atau warna font yang digunakan pada kolom tertentu.

Namun aturan ini dapat diubah melalui fasilitas OPTIONS, yang jika di-klik akan

memunculkan dialog sebagaimana yang tampak di sebelah kanan, di mana

melalui fasilitas ini kita dapat mengubah orientasi dari yang sebelumnya vertikal

menjadi horizontal (sort left to right)

Berikut adalah hasil pengaturan level atau kriteria yang akan kita gunakan untuk

melakukan pengurutan data:

Gambar 3. 28 – SORT Menggunakan Multiple Criteria

Jumlah maksimal kriteria (level) yang dapat kita digunakan dalam proses

pengurutan data ini adalah 64 buah, jauh meningkat dibandingkan jumlah

kriteria yang dapat digunakan pada Excel versi 2007 dan sebelumnya.

Perlu diingat bahwa sortir akan mengubah susunan/urutan data kita secara

PERMANEN, oleh karenanya sangat disarankan agar kita meng-copy data

yang akan kita sort ke lokasi yang berbeda atau meng-copy sheet yang berisi

data yang akan kita sort ke sheet yang baru agar data aslinya tidak hilang.


------------------------------------------------------------------------------------------------------------- 133

Salah satu kelemahan fasilitas SORT ini adalah karena sifatnya yang PERMANEN

atau STATIK, sehingga jika terjadi perubahan data asal, maka kita harus

mengulang proses sort ini dari awal dan seringkali hal ini terasa menyulitkan,

terutama jika perubahan terjadi pada saat-saat terakhir sebelum tenggat waktu

pelaporan.

Satu pertanyaan besar yang mungkin harus kita jawab, mungkinkah proses

pengurutan data ini dilakukan secara DINAMIS? Artinya hasil sortir akan secara

otomatis berubah jika datanya kita berubah sesuai situasi dan kondisi terakhir

sebelum pelaporan?

3) Menghilangkan Duplikasi Data dan Filtering

a. Menghilangkan Duplikasi Data

Data kembar merupakan kondisi yang sering kita temui dalam bekerja

dengan sejumlah data, dan semakin besar jumlah data yang kita kelola, maka

semakin besar peluang timbulnya data kembar dalam database kita.

Jika menggunakan Conditional Formatting, misalnya, kita dapat

MENGIDENTIFIKASI lokasi data kembar dalam database kita, namun pada

kesempatan kali ini kita bermaksud menghilangkan atau menghapus data

kembar dari database kita melalui setidaknya dua fasilitas Excel, yaitu Fasilitas

Remove Duplicates dan Advanced Filter.

1) Fasilitas Remove Duplicates

Fasilitas ini dapat diakses melalui menu DATA sebagaimana tampak pada

ilustrasi berikut:

Gambar 3. 29 – Fasilitas Remove Duplicates

Seperti juga menu SORT, efek dari penggunaan fasilitas Remove Duplicates

ini bersifat PERMANEN, oleh karenanya ada baiknya jika kita meng-copy

data yang akan diproses ke lokasi atau sheet yang berbeda agar data aslinya

tidak hilang.


------------------------------------------------------------------------------------------------------------- 134

Sebagai ilustrasi, kita akan menggunakan data yang ada pada Gambar 80

dengan kolom D sebagai kolom kunci.

Langkah pertama yang kita lakukan adalah menyorot range yang akan kita

proses, yaitu range A2:L9 diikuti dengan menekan icon Remove Duplicates.

Sebagai ilustrasi, kita akan menggunakan data yang ada pada Gambar 3. 24

dengan kolom D sebagai kolom kunci.

Gambar 3. 30 – Pilihan dalam Fasilitas Remove Duplicates

Karena kolom kunci kita hanya satu, yaitu kolom D, maka kita harus

memastikan bahwa yang dicentang hanya kolom APR saja. Langkah yang kita

tempuh adalah meng-klik to ol UNSELECT ALL da e g-klik kolom APR

dan dilanjutkan dengan menekan tombol OK.

Setelah menekan tombol OK, Excel akan menghapus baris ke-7, karena pada

Cell D7 berisi nilai 27 sama dengan pada Cell D6 dan memunculkan kotak

dialog sebagaimana tampak pada gambar berikut:

Gambar 3. 31 – Kotak Dialog Pasca Remove Duplicates


------------------------------------------------------------------------------------------------------------- 135

Pada kotak dialog diinformasikan bahwa telah ditemukan 1 data kembar dan

telah dihapuskan (1 baris data) dan menyisakan hanya 7 data yang bersifat

unik.

Adapun hasil proses Remove Duplicates ini sebagai berikut:

Gambar 3. 32 – Data Hasil Remove Duplicates

Manfaat lain dari fasiltas Remove Duplicates ini adalah untuk mengekstrak

data yang bersifat unik dari sebuah database, yang datanya mungkin

berjumlah ribuan sehingga proses ekstraksi secara manual tidak mungkin

dapat dilakukan secara efisien.

Gambar 3. 33 – Contoh Data

Tampak pada Gambar 3. 33 adalah contoh database yang berisikan

data unit yang ada di Kementerian Keuangan. Meski dalam ilustrasi ini kita

hanya menggunakan 8 data, namun dalam pekerjaan kita, bisa jadi kita

menghadapi ribuan data sejenis, tanpa kita ketahui secara pasti berapa unit

spesifik yang ada dalam kolom UNIT tersebut.

Duplikasi telah hilang


------------------------------------------------------------------------------------------------------------- 136

Untuk mengekstrak data UNIT agar kita mendapatkan sebuah daftar

yang berisikan nama unit yang bersifat unik, kita dapat memanfaatkan

fasilitas Remove Duplicates.

Langkah pertama yang harus kita lakukan, adalah meng-copy seluruh

data dalam kolom UNIT ke lokasi yang baru, baik dalam sheet yang sama,

maupun dalam sheet yang berbeda. Selanjutnya, kita blok data unit

seluruhnya, dan klik icon REMOVE DUPLICATES yang akan menampilkan

kotak dialog mirip dengan yang ada pada Gambar 3. 30, lalu klik OK, dan

Excel akan menampilkan layar dialog sebagai berikut:

Gambar 3. 34 – Contoh Hasil Remove Duplicates

Berdasarkan informasi pada Gambar 3. 34, proses Remove Duplicates

ini menghilangkan 4 baris data dan menyisakan 4 baris data berikutnya

karena bersifat unik. Jadi dari 8 baris data asli ternyata hanya terdapat 4

baris data yang bersifat unik, yaitu DJA, DJP, DJBC, dan BPPK.


------------------------------------------------------------------------------------------------------------- 137

2) Fasilitas Advance Filtering

Sebagai alternatif Fasiltas Remove Duplicates, kita juga dapat

memanfaatkan fasilitas Advance Filtering yang dapat diakses melalui menu

DATA dan Filtering sebagaimana digambarkan dalam ilustrasi berikut:

Gambar 3. 35 – Menu Advanced Filter

Langkah pertama yang kita lakukan adalah menyorot kolom data termasuk

LABEL-nya, lalu pilih menu DATA, dan Advanced.

Ketika piliha ADVANCED kita klik, E el aka embuka kotak dialog

sebagai tampak pada Gambar 3. 36.

Pe hatika ah a a is LIST RANGE harus diisikan range yang berisikan

data yang akan kita proses.

Untuk pilihan ACTION, kita dapat e ilih FILTER THE LIST, IN-PLACE di

mana setelah proses filterisasi data asli akan digantikan data hasil proses

filter. Penggantian ini bersifat permanen dan sebaiknya sebelum melakukan

langkah ini, data asli harus di-copy dulu ke sheet yang berbeda agar tidak

hilang.

Selai itu, kita dapat e ilih ACTION e upa COPY TO ANOTHER

LOCATION di a a hasil p oses filte isasi aka disajika pada lokasi

tertentu dalam worksheet yang sama dengan data asli.


------------------------------------------------------------------------------------------------------------- 138

Gambar 3. 36 – Contoh Penggunaan Advanced Filter

Langkah penting lainnya dalam proses ini adalah kita harus memastikan

piliha UNIQUE RECORDS ONLY sudah kita e ta g se elu e eka

tombol OK.

Proses ini akan menghasilkan daftar data yang identik dengan yang

dihasilka elalui p oses a g e ggu aka fasilitas Remove Duplicates .

Meskipun terdapat dua cara yang berbeda untuk menghilangkan duplikasi

data, yaitu melalui fasilitas Remove Duplicates dan Advanced Filter, tapi kita

mengetahui secara pasti bahwa kedua cara tersebut akan menghasilkan

keluaran yang sama dan identik. Oleh karenanya, dalam suatu waktu

tertentu, jika dibutuhkan, kita dapat menggunakan salah satu cara yang

menurut kita paling nyaman untuk kita gunakan, karena manapun cara yang

ditempuh hasilnya akan sama dan identik.

b. Data Filtering (Penyaringan Data)

Data filtering merupakan salah satu fasilitas canggih yang ditawarkan

Excel bagi kita semua, yang memungkinkan kita melakukan pengurutan data

menggunakan lebih dari satu kolom kunci dan dengan cara yang jauh lebih

fleksibel dan efisien dibandingkan menggunakan fungsi SORT.


------------------------------------------------------------------------------------------------------------- 139

Fasilitas ini dapat diakses melalui menu DATA dan FILTER sebagaimana

yang dapat dicermati pada gambar berikut ini.

Gambar 3. 37 – Contoh Penggunaan Filter

Langkah pertama kita adalah menyorot range (database) yang akan kita proses,

dalam hal ini adalah A1:L10 (termasuk label data), kemudian pilih menu DATA

dan FILTER dimana Excel akan segera menampilkan hasilnya sebagai berikut:

Gambar 3. 38 – Hasil Proses Filter

Jika kita cermati Gambar 3. 38, tampak pada label tiap kolom muncul sebuah

icon bergambar segitiga terbalik yang jika kita klik akan menampilkan dialog

sebagai diilustrasikan pada Gambar 3. 39 berikut ini.


------------------------------------------------------------------------------------------------------------- 140

Gambar 3. 39 – Pilihan pada Menu Filter

Berdasarkan pilihan yang ada dalam kotak dialog dimaksud, kita dapat

mengurutkan data pada tiap kolom menggunakan pilihan Ascending maupun

Descending. Namun proses sortir ini hanya berlaku untuk satu level saja. Artinya

aturan sortir yang kita telah tetapkan untuk satu kolom tertentu akan tidak

berfungsi (hilang) jika kita kemudian menetapkan aturan sortir pada kolom yang

berbeda.

Kemudian, melalui fasilitas SEARCH (Gambar 3. 39) kita dapat menyaring

data sesuai persyaratan yang kita tetapkan. Misalnya kita hanya ingin

menampilkan seluruh data yang nilainya pada kolom D = 41. Caranya adalah

mengetikkan angka 41 pada baris SEARCH pada layar dialog.

Selain itu, kita dapat men-checked pilihan filter yang disajikan Excel

berdasarkan data yang bersifat unik (lihat DATA UNIK) pada kolom tertentu yang

secara otomatis akan direspon Excel dengan menyajikan data yang memenuhi

kriteria tersebut.

DATA UNIK


------------------------------------------------------------------------------------------------------------- 141

Untuk data yang bersifat angka, Excel memberikan fasilitas tambahan

berupa pilihan Number Filters, yang memungkinkan kita menetapkan sejumlah

kriteria penyaringan yang bersifat lebih dari sekedar penyaringan biasa.

Berbeda dari aturan sortir sebagaimana telah kita bahas di atas, pada

proses penyaringan ini kita dapat menggunakan aturan penyaringan yang

berbeda untuk tiap kolom secara bersamaan atau simultan. Misalnya untuk

kolom D kita hanya akan menyajikan data yang nilainya di atas 20, sementara

untuk kolom F kita hanya akan menyajikan data yang nilainya di atas 40.

Untuk mewujudkannya, kita harus meng-klik segitiga terbalik yang ada

pada kolom D dan F, lalu memilih Number Filters, dengan pilihan Greater Than,

lalu kita ketikkan batasan yang kita inginkan sebagaimana yang dapat dicermati

pada gambar berikut:

Gambar 3. 40 – Contoh Number Filters pada Kolom D

Proses ini diulangi lagi untuk kolom F dengan nilai batasan adalah 40. Hasil akhir

dari kedua proses filtering ini tampak pada gambar di bawah ini:

Gambar 3. 41 – Hasil Akhir Proses Filtering

Jika Gambar 3. 41 kita cermati, icon yang ada pada label data pada Kolom

D dan Kolom F berbeda dibanding icon yang ada pada label lainnya. Icon yang

berbeda ini menandakan bahwa pada kedua kolom tersebut kita menggunakan


------------------------------------------------------------------------------------------------------------- 142

dua aturan penyaringan (filtering) yang berbeda dan kedua aturan dimaksud

bersifat aktif secara simultan.

Data yang disajikan pada Gambar 3. 41 memenuhi ketentuan bahwa pada

kolom D nilainya pasti lebih dari 20 dan pada kolom F nilainya pasti lebih dari 40.

Dan memang benar bahwa di kolom D tidak ada data yang nilainya kurang dari

atau sama dengan 20, sementara di kolom F tidak ada data yang nilainya kurang

dari atau sama dengan 40.

Hasil dari proses penyaringan data ini dapat digunakan secara langsung

(dicetak) atau di-copy ke lokasi yang berbeda untuk dipergunakan sebagai bahan

pelaporan ke pimpinan.

Meski terlihat DINAMIS, namun proses penyaringan ini pada dasarnya

masih bersifat MANUAL yang mengharuskan kita mengubah langsung ke

sumber data sebelum menyajikannya sebagai laporan.

Misalnya, jika salah satu data berubah, maka kita harus menginputkannya

ke dalam database dan untuk kepentingan ini kemungkinan besar kita harus

menonaktifkan sejumlah aturan penyaringan yang telah kita tetapkan

sebelumnya. Setelah data yang baru kita masukkan barulah kita kembali dapat

melakukan penetapan aturan penyaringan. Bentuk pengulangan ini tentunya

melelahkan dan membosankan bagi sebagian dari kita. Mungkinkah ada cara

lain yang lebih DINAMIS dan tidak melelahkan apalagi membosankan?

4) Menggunakan Pivot Table

Pivot Table merupakan fasilitas yang disediakan Excel yang memungkinkan kita

sebagai pengguna untuk mampu menyusun laporan secara cepat, cermat, dan

akurat.

Gambar 3. 42 – Menu Pivot Table

PIVOT


------------------------------------------------------------------------------------------------------------- 143

Fasilitas ini dapat diakses melalui menu INSERT dan PIVOT TABLE yang dapat

digunakan untuk menghasilkan Pivot Table dan Pivot Chart.

Pivot Table digunakan untuk melakukan rekapitulasi dalam rangka

penyusunan laporan berupa angka, sedangkan Pivot Chart digunakan untuk

menghasilkan laporan berupa grafik (chart).

Untuk mendapatkan gambaran lengkap tentang penggunaan Pivot Table kita

membutuhkan sebuah database yang relatif besar jumlah datanya. Adapun

ringkasan database tersebut dapat dicermati pada gambar berikut ini:

Gambar 3. 43 – Database untuk Pivot

Kolom ESELON berisikan data Jenjang Eselon Pegawai, kolom KANTOR berisi

nama unit pegawai, kolom PANGKAT berisikan data pangkat/golongan ruang

pegawai, kolom JENJANG berisikan data pendidikan formal terakhir, kolom SEX

berisikan data jenis kelamin, kolom HONOR berisikan data jumlah honor yang

diterima pada suatu waktu tertentu, kolom GOL berisikan data Golongan Pegawai,

mulai dari Gol 1 untuk pegawai golongan I/a s.d. I/d hingga Gol 4 untuk pegawai

golongan IV/a s.d. IV/e. Total jumlah data adalah 811.


------------------------------------------------------------------------------------------------------------- 144

Tujuan kita kali ini adalah menghitung jumlah pegawai yang ada di tiap unit

(KANTOR) berdasarkan jenis kelaminnya.

Langkah pertama yang harus kita tempuh untuk mewujudkan tujuan ini adalah

meng-klik icon Pivot Tabel pada menu INSERT yang segera akan menampilkan kotak

dialog sebagai berikut:

Gambar 3. 44 – Layar Dialog Pivot Tabel

Sela jut a, kita ha us e gisi a is SELECT A TABLE OR RANGE de ga

cara meng-klik bagian pojok kanan isian Table/Range. Karena data kita berada pada

range A1 sampai dengan G812, maka kita harus menginputkan range $A$1:$G$812

sebagai inputnya.

Kemudian, kita ingin menentukan apakah laporan kita (Pivot Table) akan kita

tempatkan pada sheet yang sama dengan data aslinya atau pada worksheet yang

berbeda. Jika akan kita tempatkan pada sheet yang sama, pastikan lokasi tabelnya

tidak tumpang tindih dengan lokasi data asli.

Hasil akhir dari proses ini dapat dicermati pada gambar berikut:

Gambar 3. 45 – Layar Dialog Pivot Tabel 2


------------------------------------------------------------------------------------------------------------- 145

Setelah tombol OK ditekan, Excel segera menampilkan kotak dialog berikut

ini yang merupakan alat kendali untuk menentukan format laporan yang akan kita

susun,juga Menu Pivot Table yang disediakan pada Ribbon, sebagaimana tampak

pada gambar berikut:

Gambar 3. 46 – Fasilitas pada Menu Pivot Table

Selanjutnya kita ingin mendisain bentuk laporan yang akan dihasilkan. Secara umum

bentuk laporan yang ingin kita hasilkan adalah sebagai berikut:

Nama Unit Pria Wanita Total

Untuk itu, langkah yang harus kita tempuh adalah men-drag field ESELON

pada kotak kendali ke Kotak ROW LABELS (Gambar 3. 47) yang secara otomatis akan

memunculkan satu kolom berisikan seluruh nama unit yang ada dalam kolom UNIT.

Untuk penamaan kolom, kita lakukan dengan men-drag field SEX pada kotak

kendali ke Kotak COLUMN LABELS (Gambar 3. 47) yang secara otomatis akan

memunculkan satu baris berisikan pilihan jenis kelamin, yaitu P dan W termasuk

grand total-nya yang merupakan hasil penjumlahan jenis kelamin per baris laporan.


------------------------------------------------------------------------------------------------------------- 146

Sampai di sini kita telah berhasil menyajikan label untuk kolom dan baris dari

laporan kita. Untuk menyajikan hasil rekapitulasi untuk tiap item dalam laporan, kita

harus men-drag field jenis kelamin ke kotak dengan Σ Values (Gambar 3. 47) yang

secara otomatis akan melakukan proses rekapitulasi data untuk kita.

Gambar 3. 47 – Proses Pembuatan Laporan Menggunakan Pivot Table

Meskipun angka-angka yang tersaji dalam laporan kita sudah akurat, namun

dari sisi presentasi belum memadai. Untuk itu kita harus melalukan sejumlah

penyesuaian agar presentasi laporan kita menjadi lebih profesional.


------------------------------------------------------------------------------------------------------------- 147

Dengan beberapa penyesuaian, misalnya, dengan mengubah lebar kolom

untuk kolom L, M, dan N, kemudian mengubah label pada kolom K, L, M, dan N, kita

mendapatkan satu laporan sebagaimana tampak pada gambar di atas ini.

Secara manual kita bisa mengganti urutan dari kolom label maupun row

labels agar dapat sepenuhnya mencerminkan kebutuhan kita, dan Pivot Table akan

melakukan penyesuaian atas data laporan kita secara otomatis. Hanya saja akibat

perubahan yang kita lakukan ini, format laporan yang tadinya sudah kita perbaiki

sesuai dengan selera kita akan dikembalikan Excel ke dalam format bakunya.

Pada kotak dialog Pivot Table sebagaimana dapat dicermati pada Gambar 3.

47, terdapat sebuah kotak untuk penetapan aturan penyaringan (filter) bagi laporan

yang hendak kita sajikan. Seperti biasa kita bisa menggunakan lebih dari satu filter

secara simultan. Sebagai contoh, kita akan menggunakan dua buah filter untuk

laporan kita, yaitu KANTOR dan PANGKAT. Caranya sama dengan sebelumnya, yaitu

dengan men-drag field KANTOR dan PANGKAT ke dalam kotak REPORT FILTER.

Gambar 3. 48 – Contoh Penggunaan Report Filter pada Pivot Table

FILTER


------------------------------------------------------------------------------------------------------------- 148

Secara default, Filter berada pada posisi ALL yang artinya filter berada pada

posisi nonaktif. Untuk mengaktifkan filter ini, cukup dengan meng-klik icon segitiga

terbalik yang ada pada pojok kanan field filter pada laporan. Untuk kepentingan

ilustrasi kali ini, field KANTOR kita filter hanya untuk unit SET dan PANGKAT untuk

IV/b dengan hasil sebagai mana tampak di bawah ini.

Filter untuk tiap field dapat ditetapkan untuk lebih dari satu item dengan cara

e e ta g piliha SELECT MULTIPLE ITEMS se agai a a a g dapat di e ati

pada gambar berikut ini:

Gambar 3. 49 – Contoh Multiple Filter dan Hasilnya

Perlu untuk kita ingat bahwa laporan yang tampak pada Pivot Table ini tidak

secara otomatis akan berubah ketika data asli kita ubah. Untuk memastikan bahwa

laporan kita telah mencerminkan kondisi terakhir, maka kita harus meng-klik icon

REFRESH pada menu PIVOT TABLE sebagaimana tampak pada gambar berikut:


------------------------------------------------------------------------------------------------------------- 149

Gambar 3. 50 – Menu Refresh pada Pivot Table

Penggunaan fasilitas REFRESH ini akan mengubah format laporan Pivot Table

kita kembali ke bentuk bakunya. Jadi sangat disarankan untuk berhati-hati

menggunakannya khususnya bagi kita yang bermaksud melakukan makeover besar-

besaran terhadap tampilan Pivot Table-nya. Pastikan data kita sudah tetap sebelum

melakukan perubahan format.

5) Menggunakan Goal Seek

Goal Seek merupakan fasilitas khusus yang disediakan Excel yang

memungkinkan kita menebak sebuah angka tertentu melalui sejumlah proses trial

and error (iterasi) yang dilakukan oleh Excel secara otomatis.

Untuk memudahkan pembahasan, marilah kita mencermati contoh berikut ini:


------------------------------------------------------------------------------------------------------------- 150

Gambar 3. 51 – Contoh Penggunaan Goal Seek

Pada contoh di atas kita bermaksud menentukan berapa nilai Artikel 6 dalam

RAB yang tengah kita susun. Nilai Artikel 9 harus dua kali nilai Artikel 6. Berapapun

nilai yang kita inputkan sebagai nilai dari Artikel 6 harus menghasilkan nilai TOTAL

ANGGARAN yang identik dengan nilai TOTAL ALOKASI ANGGARAN

(selisihnya/parity check harus 0,000).

Setidaknya ada dua cara untuk mengatasi masalah ini. Pertama, adalah

melakukan proses trial and error secara manual dengan mengubah-ubah nilai Artikel

6. Cara kedua adalah dengan menggunakan fasilitas Goal Seek yang dapat diakses

melalui menu DATA, What-if-analysis, dan Goal Seek sebagaimana yang dapat

dicermati pada gambar berikut:


------------------------------------------------------------------------------------------------------------- 151

Gambar 3. 52 – Menu Goal Seek

Ketika pilihan Goal Seek kita klik, Excel akan menampilkan kotak dialog berikut ini:

Gambar 3. 53 – Variabel Goal Seek

Terdapat tiga baris informasi yang harus kita inputkan pada kotak dialog Goal

Seek, yang pertama adalah target cell yang nilainya ingin kita tetapkan. Pada kasus

ini kita tetapkan bahwa cell dengan alamat E23, yaitu cell yang berisi selisih antara

TOTAL ANGGARAN dan TOTAL ALOKASI ANGGARAN.

Nilai yang kita tetapkan untuk alamat E23 ini (To Value) adalah nol (0),

dengan demikian kita menginginkan agar selisih antara kedua nilai tersebut di atas

harus lah sama dengan NOL.

Selanjutnya, nilai NOL ini akan dan harus kita capai dengan membuat Excel

mengubah nilai Cell B10, yakni cell yang berisikan nilai untuk Artikel 6.

Setelah menekan tombol OK, Excel akan menyajikan bahwa nilai Artikel 6 adalah

452,95455, sedangkan nilai Artikel 9 adalah 905,90909 atau dua kali nilai Artikel 9.

Karena nilai Artikel 6 haruslah berupa bilangan tanpa desimal, maka kita pastikan

bahwa Artikel 6 harus bernilai 453 (dibulatkan ke atas), sedangkan Artikel 9 otomatis

berjumlah 906. Dari proses ini kita dapatkan bahwa masih terdapat total selisih

sebesar 2.000.

Nilai 2.000 ini dapat kita alokasikan ke pos Belanja Bahan (Belanja Bahan dikurangi

2.000) untuk membuat total selisih menjadi NOL atau jika kita mau, kita dapat

mengulangi proses Goal Seek ini dari awal, dengan input untuk Set Cell (E23) dan To


------------------------------------------------------------------------------------------------------------- 152

Value (0) yang sama dengan proses Goal Seek sebelumnya. Input untuk BY

CHANGING CELL kita ubah menjadi E9 yaitu alamat dari pos Belanja Bahan sebelum

kemudian kita menekan tombol OK.

Fasilitas ini sangat mudah dan praktis digunakan terutama bagi peserta yang sehari-

harinya berususan dengan penganggaran di kantor masing-masing.

b. Rangkuman

Berbagai fasilitas standar Excel yang diperkenalkan pada modul ini sangat efektif

dalam penyusunan laporan yang bersifat instan. Kita bahkan tidak perlu

menguasai fungsi-fungsi yang rumit untuk mengoperasikan fasiitas-fasilitas

dimaksud.

Fasilitas conditional formatting selain dapat dipergunakan untuk mempercantik

laporan, juga dapat dipergunakan untuk tujuan lain seperti untuk kepentingan

penyusunan dashboard, untuk menunjang fasilitas early warning system yang

dikembangkan, bahkan untuk kepentingan audit.

Kemampuan untuk mengurutkan data dengan field kunci mencapai 64 buah

membuat proses pengurutan data menjadi lebih akurat dan proses ini dapat

dilakukan dengan beberapa langkah sederhana saja.

Kemampuan lain yang ditawarkan Excel adalah fasilitas untuk menghapus

duplikasi data yang dilaksanakan secara instan dan akurat. Fasilitas ini pada

dasarnya merupakan pengembangan dari fungsi Filter (penyaringan data).

Fungsi filter sendiri pada versi 2010 ini dikembangkan jauh lebih maju

dibandingkan pada versi sebelumnya

Pemanfaatan fasilitas Pivot Table juga sangat membantu khususnya untuk

proses pelaporan yang bersifat instan dan non rutin dan dapat dimanfaatkan

secara efektif oleh mereka yang awam terhadap Excel.


------------------------------------------------------------------------------------------------------------- 153


Perhatikan data berikut ini:

Gunakan fasilitas:

- Conditional Formatting untuk mewarnai seluruh baris data (Kolom A s.d. D) jika

ell dala kolo a a e isi data AA

- Urutkan data me ggu aka ke tgl seto


Target waktu Anda dalam menyelesaikan kasus pada tes formatif di atas adalah 5

menit.

Jika Anda tidak dapat menyelesaikan kasus di atas dalam waktu paling lambat

setengah jam, maka disarankan agar Anda mempelajari kembali modul ini dengan

cara yang lebih baik.

Microsoft Excel

------------------------------------------------------------------------------------------------------------- 154

PENUTUP

Microsoft Excel 2010 yang merupakan pengembangan lebih lanjut dari versi-versi

sebelumnya merupakan sebuah aplikasi pengolah data dan bukan aplikasi pengolah

angka sebagaimana yang selama ini dipersepsikan.

Secara umum fasilitas yang disediakan Excel dapat dibagi dua, yaitu fungsi standar yang

dapat digunakan untuk berbagai tujuan serta fasiltias pelaporan standar, seperti fasilitas

SORT, FILTER, dan PIVOT TABLE.

Berbeda dengan penggunaan fungsi yang membutuhkan latihan rutin dan kreativitas

penggunanya, fasilitas pelaporan standar dapat digunakan oleh pengguna awam dengan

sedikit pengenalan dan pelatihan karena fasilitas-fasilitas ini dikembangkan dan dikemas

agar secara intuitif dapat dipahami dan digunakan.

Fasilitas standar yang ditawarkan Excel sangat banyak manfaatnya bagi kita yang

menghendaki penyunan laporan secara instan, namun sebagaimana produk instan

lainnya, fasilitas ini juga memiliki sejumlah kelemahan, antara lain keterbatasan dalam

penggunaannya.

Fungsi-fungsi yang ditawarkan Excel pada prinsipnya dapat dimanfaatkan untuk

mengatasi kelemahan yang ada pada fasilitas pelaporan standar, hanya saja untuk

menguasai teknik-teknik yang dibutuhkan, kita harus secara tekun mempelajari dan

mempraktikkan teknik-teknik tertentu terkait dengan pemanfaatan fungsi-fungsi

dimaksud, mengingat penguasaan Excel pada tingkat ini sama sulitnya dengan

penguasaan salah satu bahasa pemrograman. Untuk itu, kami sangat menyarankan agar

para peserta senantiasa melatih diri dengan terus berusaha mengefisienkan langkah-

langkah yang ditempuh dalam menyelesaikan pekerjaan sehari-hari di tempat kerja,

khususnya yang berkaitan dengan penggunaan Excel.

Microsoft Excel

------------------------------------------------------------------------------------------------------------- 155

TES SUMATIF

Perhatikan data berikut:

Hitunglah:

1. Total setoran di bawah Rp20.000.000

2. Total setoran yang dilakukan oleh tiap penyetor

3. U utka data pe eto a e ggu aka ke a a

4. Sajikan laporan penyetoran pajak dengan format di bawah ini menggunakan

fasilitas Pivot Table

Microsoft Excel

------------------------------------------------------------------------------------------------------------- 156

KUNCI JAWABAN

Test Formatif Kegiatan Belajar 1

Test Formatif Kegiatan Belajar 2

DAFTAR ISTILAH

------------------------------------------------------------------------------------------------------------- 157

DAFTAR ISTILAH

= RANDBETWEEN(bottom, top), 60

=AND logical_test , logical_test ,… ,

=CHAR(number), 46

=CODE(text), 47

=CONCATENATE te t ,te t ,te t ,… ,

=COUNT(value1, [value ], … ,

=COUNTA value , [value ],… ,

=COUNTBLANK(range), 87

=COUNTIF(range, criteria), 88

=COUNTIFS(range1, criteria1, range2,

criteria ,… ,

=DATE(year,month, day), 35

=DAY(date), 34

=DCOUNT(database, field, criteria), 82

=DCOUNTA(database, field, criteria), 85

=FIND(find_text,within_text,start_num),

48

=HLOOKUP(lookup_value,table_array,ro

w_index_num,[range_lookup], 69

=HOUR(date), 34

=IF(logical_test, respond_if_true,

respond_if_false), 55

=IFERROR(value,value_if_error), 58

=LEFT(text,num_char), 43

=LEN(text), 42

=LOOKUP(lookup_value, array), 65, 67

=LOOKUP(lookup_value, lookup_vector,

result_vector), 65, 66

=LOWER(text), 40

=MID(text, start_num, num_char), 44

=MINUTE(date), 34

=MOD(number, divisor, 119

=MONTH(date), 34

=NOW(), 33, 34

=OR(logical_test , logical_test ,… ,

=PROPER(text), 41

=RAND(), 60

=REPT(text,number_of_times), 51

=RIGHT(text,num_char), 43

=ROUND(number, num_digits), 64

=ROUNDDOWN(number,num_digits),

64

=ROUNDUP(number, num_digits), 64

=ROW(), 119

=SEARCH(find_text,within_text,start_nu

m), 49

=SECOND(date), 34

=SUMIF(range, criteria, sum_range), 96

=SUMIFS(sum_range,criteria_range1,

criteria , criteria_range ,criteria ,… , 99

=SUMPRODUCT arra ,arra ,arra ,…), 94

=TEXT(value, format_text), 39

=TIME(hour,minute,second), 36

=TODAY(), 33, 34

=TRIM(text), 45

=UPPER(text), 40

=VALUE(text), 50

=VLOOKUP(lookup_value,table_array,co

l_index_num,[range_lookup], 73

=WEEKDAY(serial_number,return_type),

36

=WEEKNUM(serial_number,return_type

), 37

=YEAR(date), 34

=YEARFRAC(start_date,end_date,basis),

38

Absolute Referencing, 23

Add Level, 123

Advanced Filter, 125, 129

Alamat Cell Relatif dan Absolut, 2, 10

Alignment, 11

AMPERSAND, 41, 42, 43, 44, 45, 47

Anatomi dari Sebuah Fungsi, 27

argumen, 20, 21, 27, 68, 119

Array, 67, 68, 69, 95, 96

Ascending, 120, 122, 131

Aturan (hirarki) Perhitungan, 20

Autosum, 29

Border, 11, 12

broken link, 4

case insensitive, 88, 97

Cell, 2

COLUMN LABELS, 136

comparison operator, 52

Compatibility, 26

Conditional Formatting, 104, 105, 107,

111, 117, 118, 119, 120

Copy Level, 123

COPY TO ANOTHER LOCATION, 129

Create a Copy, 7, 8

CTRL + 1, 11, 31

CTRL + ENTER, 23, 24

CTRL + N, 3

CTRL + PGDN, 7

DAFTAR ISTILAH

------------------------------------------------------------------------------------------------------------- 158

CTRL + PGUP, 7

CTRL + Z, 6

Cube, 26

Custom Format, 13, 14, 31, 32

Data Angka, 12

Data Teks, 13

Date, 26, 30, 31, 106

Delete Level, 123

Delete Sheet, 6

Descending, 120, 122, 131

double click, 4, 25

Engineering, 26

FALSE, 12, 51, 52, 53, 54, 55, 56, 70, 71,

72, 74, 75, 78, 79

Fasilitas AutoComplete, 28

Fasilitas Remove, 125, 126

Fill, 11, 12, 24, 25, 26, 109

fill handle, 25

FILTER, 129, 130, 138

FILTER THE LIST, IN-PLACE, 129

Financial, 26

folder, 4, 9

Font, 11, 12

Format Cells, 11, 12

Format only cells that contain, 112, 113

Format Tanggal, 15, 30

formula, 10, 11, 19, 20, 21, 22, 23, 24,

25, 35, 36, 55, 57, 58, 59, 61, 63, 71,

72, 75, 77, 78, 84, 85, 87, 89, 90, 91,

92, 93, 94, 95, 96, 98, 99, 100, 101,

102, 112, 116, 117, 118, 120

Formula, 8, 19, 21, 22, 23, 24, 27, 29,

35, 57, 59, 83, 87, 90, 91, 93, 94, 98,

100, 117, 118, 119, 120

General, 12, 15, 35

Goal Seek, 140, 141, 142

Home, 16, 105, 107

Information, 26

Insert Sheet, 5

ISBLANK(value), 78

ISERR(value), 78

ISERROR(value), 78

ISLOGICAL(value), 78

ISNA(value), 78

ISNONTEXT(value), 78

ISNUMBER(value), 78

ISREF(value), 78

ISTEXT(value), 78

karakter, 21, 42, 43, 44, 45, 46, 47, 48,

49, 50, 89, 91, 92, 97

Keyboard Shortcuts, 15, 16, 30

Logical, 26, 51, 52, 53, 54

LOGICAL TEST, 51

Lookup & Reference, 26

Math & Trigonometry, 26

Mengatur Format Cells, 11

mengekstrak data, 127

Menghilangkan Duplikasi Data dan

Filtering, 125

Menu Color Scales, 110

Menu Data Bars, 109

Menu Highlight Cells Rules, 106

Menu Icon Sets, 111

Menu Top/Bottom Rules, 108

Move or Copy, 7

Nama Worksheet, 4

Navigasi, 2, 15

Nested IF, 56

NOTASI ANGKA, 10

NOTASI HURUF, 10

Number, 11, 12, 15, 30, 31, 46, 51, 131,

132

Opsi Values, 63

Paste Special, 62

Penggunaan asterisk (*), 89, 97

Penggunaan tanda tanya (?), 89, 97

pesan kesalahan, 58, 77, 78, 79, 102

Pivot Table, 133, 134, 136, 138, 139,

140

Protection, 11

radio bar, 6

Range, 2

Ribbon, 5, 6, 136

Ribbon Home, 5, 6

ROW LABELS, 136

Save, 6

Save As, 6

SELECT MULTIPLE ITEMS, 139

Shift + F11, 5

SORT, 120, 121, 122, 123, 124, 125, 130

Statistical, 26

tab worksheet, 4, 5, 7

Tanda Exponen, 20

Tanda Minus, 20

Tanda Penambahan dan Pengurangan,

21

Tanda Perkalian dan Pembagian, 21

Teknik Double Click, 25, 26

Teknik Dragging, 24, 25

Template, 3

Text, 12, 20, 26, 40, 41, 42, 43, 44, 45,

47, 51, 106, 114

Time, 26, 30

tooltips, 28, 29

DAFTAR ISTILAH

------------------------------------------------------------------------------------------------------------- 159

Tooltips, 29

TRUE, 12, 51, 52, 53, 54, 55, 56, 70, 71,

72, 74, 75, 76, 77, 78, 79

Unary Minus, 20

UNIQUE RECORDS ONLY, 129

Use a formula to determine which cells

to format, 112, 116

Workbook, 2, 3, 4, 8, 16

Worksheet, 2, 4, 5, 6, 7, 8, 16

worksheet aktif, 6, 7, 9

worksheet pasif, 9

DAFTAR ISTILAH

------------------------------------------------------------------------------------------------------------- 160

DAFTAR PUSTAKA

Jelen, Bill. 2010. Microsoft Excel 2010 In Depth. Indiana, USA: Que Publishing.

Microsoft Excel Help

dtu microsoft excel, word dan … · sejumlah fasilitas standar yang ditawarkan excel dalam proses...

Documents