PUSDIKLAT KEUANGAN UMUM Badan Pendidikan Dan Pelatihan Keuangan Kementerian Keuangan R.I

PUSDIKLAT KEUANGAN UMUM Badan Pendidikan Dan Pelatihan Keuangan Kementerian Keuangan R.I

DTU MICROSOFT EXCEL, WORD DAN POWERPOINT 2010 TINGKAT LANJUTAN

Microsoft Excel

DIKLAT TEKNIS UMUM MICROSOFT EXCEL, WORD, DAN POWERPOINT 2010 TINGKAT LANJUTAN

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


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

Modul: Microsoft Excel -----------------------------------------------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

Modul: Microsoft Excel -----------------------------------------------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

Modul: Microsoft Excel -----------------------------------------------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

Modul: Microsoft Excel -----------------------------------------------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 2 3 4 5 6 7 8 9 10 11 12 13 14 15

Ctrl+C Ctrl+X Ctrl+V Ctrl+Z Ctrl+Y Ctrl+A Ctrl+S Ctrl+O Ctrl+W or Ctrl+F4 Ctrl+N Ctrl+P Ctrl+B Ctrl+U Ctrl+I Ctrl+F

Copy Cut Paste Undo Redo Select all Save Open Close workbook New workbook Print Bold Underline Italic Find

Shortcut Keys for Selecting Data and Cells NO

SHORTCUTS

1

Ctrl+spacebar

2

Shift+spacebar

3

Shift+backspace

4 5

Ctrl+Shift+* Ctrl+/ Ctrl+Shift+O (letter O)

6 7

Ctrl+\

8

Ctrl+Shift+|

9 10

Ctrl+[ (opening square bracket) Ctrl+Shift+{ (opening brace)

FUNGSI 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 Select entire row If inside a table, toggle between selecting the table row and the entire row With multiple cells selected, revert selection to only the active cell Select the current region Select the array containing the active cell Select all cells that contain comments In a selected row, select the cells that do not match the value in the active cell In a selected column, select the cells that do not match the value in the active cell Select all cells directly referenced by formulas in the selection Select all cells directly or indirectly referenced by formulas in the selection


11 12 13

Ctrl+] (closing square bracket) Ctrl+Shift+} (closing brace) Alt+; (semicolon)

Select cells that contain formulas that directly refer-ence the active cell Select cells that contain formulas that directly or indirectly reference the active cell Select the visible cells in the current selection

Shortcut Keys for Extending Selections NO

SHORTCUTS

1

F8

2

Shift+F8

3

5 6

Shift+arrow key Ctrl+Shift+arrow key Shift+Home Ctrl+Shift+Home

7

Ctrl+Shift+End

8 9

Shift+Page Down Shift+Page Up End+Shift+arrow key

4

10 11

End+Shift+Home

12

End+Shift+Enter Scroll Lock+Shift+Home Scroll Lock+Shift+End

13 14

FUNGSI Turn Extend mode on or off In Extend mode, EXT appears in the status line and the arrow keys extend the selection 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 Extend the selection by one cell Extend the selection to the last nonblank cell in the same column or row as the active cell Extend the selection to the beginning of the row Extend the selection to the beginning of the worksheet Extend the selection to the last used cell on the worksheet in the lower-right corner Extend the selection down one screen Extend the selection up one screen Extend the selection to the last nonblank cell in the same column or row as the active cell Extend the selection to the last used cell on the worksheet in the lower-right corner Extend the selection to the last cell in the current row Extend the selection to the cell in the upper-left corner of the window Extend the selection to the cell in the lower-right corner of the window

Shortcut Keys for Navigation NO

SHORTCUTS

FUNGSI

1 2 3 4 5 6 7 8 9 10

Ctrl+Home Ctrl+End Ctrl+Page Up Ctrl+Page Down Shift+F11 Alt+Tab Alt+Shift+Tab Ctrl+Esc Ctrl+F5 F6

11

Ctrl+F6

Move to Cell A1. Move to last cell in the used range of the worksheet. Move to previous worksheet. Move to next worksheet. New Worksheet. Switch to next program . Switch to previous program. Display Windows Start menu. Restore window size of current workbook. Switch to next pane in a window that has been split. When more than one workbook is open, switch to the next open workbook window.

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

SHORTCUTS

FUNGSI

12 13 14 15 16 17 18 19 20 21

Ctrl+Shift+F6 Ctrl+F9 Ctrl+F10 Ctrl+arrow key Home Ctrl+Backspace F5 Shift+F5 Shift+F4 Ctrl+. (period)

22

Ctrl+Up Arrow

23

Ctrl+Down Arrow

Switch to the previous workbook window. Minimize the window . Maximize the window. Move to edge of current region. Move to beginning of row. Scroll to display the active cell . Display the GoTo dialog . Display the Find dialog . Find next. Move to next corner of selected range. Move to the first row in the data if your data has no blank cells 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 2 3 4 5

Enter Alt+Enter Ctrl+Enter Shift+Enter Tab

6

Shift+Tab

7 8 9 10 11 12 13 14 15 16

Esc Arrow keys Home F4 or Ctrl+Y Ctrl+Shift+F3 Ctrl+D Ctrl+R Ctrl+F3 Ctrl+K Ctrl+; (semicolon) Ctrl+Shift+: (colon)

Complete a cell entry and select the next cell below. Start a new line in the same cell. Fill the selected cell range with the current entry. Complete a cell entry and select the next cell above. Complete a cell entry and select the next cell to the right. Complete a cell entry and select the previous cell to the left. Cancel a cell entry. Move one character up, down, left, or right. Move to the beginning of the line. Repeat the last action. Create names from row and column labels. Fill down. Fill to the right. Define a name. Insert a hyperlink. Enter the date.

17 18

Alt+down arrow

19 20 21 22 23 24 25

Ctrl+Z = (equal sign) Backspace Enter Ctrl+Shift+Enter Esc Shift+F3

26

Ctrl+A

Enter the time. Display a drop-down list of the values in the current column of a range. Undo the last action. Start a formula. In the formula bar, delete one character to the left. Complete a cell entry from the cell or formula bar. Enter a formula as an array formula. Cancel an entry in the cell or formula bar. In a formula, display the Insert Function dialog box. When the insertion point is to the right of a function name in a formula, display the Function Arguments dialog


SHORTCUTS

FUNGSI

27

Ctrl+Shift+A

28 29

F3 Alt+= (equal sign) Ct l+Shift+ (quotation mark)

box. When the insertion point is to the right of a function name in a formula, insert the argument names and parentheses. Paste a defined name into a formula. Insert an AutoSum formula with the SUM function. Copy the value from the cell above the active cell into the cell or the formula bar.

NO

SHORTCUTS

FUNGSI

31

Ct l+ (apostrophe)

32

Ctrl+` (backtick)

33

F9

34

Shift+F9

35

Ctrl+Alt+F9

36

Ctrl+Alt+Shift+F9

37

F2

38

Alt+Enter

39

Backspace

40

Delete

41 42 43 44 45

Ctrl+Delete F7 Shift+F2 Enter Ctrl+Z

46

Ctrl+Shift+Z

47 48

Delete Ctrl+- (hyphen) Ctrl+Shift++ (plus sign) Alt+

Copy a formula from the cell above the active cell into the cell or the formula bar. Alternate between displaying cell values and displaying formu-las. 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. Calculate the active worksheet. Calculate all worksheets in all open workbooks, regardless of whether they have changed since the last calculation. Recheck dependent formulas and then calculate all cells in all open workbooks, including cells not marked as needing to be calculated. 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. Start a new line in the same cell. Edit the active cell and then clear it or delete the preceding character in the active cell as you edit cell contents. Delete the character to the right of the insertion point or delete the selection. Delete text to the end of the line. Display the Spelling dialog box. Edit a cell comment. Complete a cell entry and select the next cell below. Undo the last action. When the AutoCorrect smart tag is displayed, undo or redo the last automatic correction. Clear the contents of the selected cells. Delete the selected cells.

30

49 50

Insert blank cells. Display the Style dialog box.


SHORTCUTS

51 52

(apostrophe) Ctrl+1 Ctrl+Shift+~

53

Ctrl+Shift+$

54

Ctrl+Shift+%

55

Ctrl+Shift+^

56

Ctrl+Shift+#

57

Ctrl+Shift+@

NO

SHORTCUTS

58

Ctrl+Shift+!

59 60 61 62 63

Ctrl+B Ctrl+I Ctrl+U Ctrl+5 Ctrl+9 Ctrl+Shift+( (opening parenthesis) Ctrl+0 (zero) Ctrl+Shift+) (closing parenthesis) Ctrl+Shift+& Ctrl+Shift+_ (under-score)

64 65 66 67 68

FUNGSI Display the Format Cells dialog box. Apply the General number format. Apply the Currency format with two decimal places (negative numbers in parentheses). Apply the Percentage format with no decimal places. Apply the Exponential number format with two decimal places. Apply the Date format with the day, month, and year. Apply the Time format with the hour and minute, and AM or PM.

FUNGSI Apply the Number format with two decimal places, thousands separator, and minus sign (-) for negative val. Apply or remove bold formatting. Apply or remove italic formatting. Apply or remove underline. Apply or remove strikethrough. Hide the selected rows. Unhide any hidden rows within the selection. Hide the selected columns. Unhide any hidden columns within the selection. Apply the outline border to the selected cells. 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

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 -------------------------------------------------------------------------------------------------------------

1

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.

PENDAHULUAN -------------------------------------------------------------------------------------------------------------

2

B. KEGIATAN BELAJAR

1. Kegiatan Belajar 1 : PENGETAHUAN DASAR TENTANG EXCEL INDIKATOR Setelah mengikuti pembelajaran ini peserta diklat diharapkan dapat mengusai pengetahuan dasar tentang:

a.



Workbook dan Worksheet, Cell dan Range;



Alamat Cell Relatif dan Absolut;



Mengatur Format Cell; dan



Navigasi dalam Excel

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.

KEGIATAN BELAJAR 1 : PENGETAHUAN DASAR TENTANG EXCEL -------------------------------------------------------------------------------------------------------------

3

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.


4

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.


5

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


6

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


7

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


8

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.


9

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

. ls ]Sheet !$B$

Excel

dengan

notasi

[filekerja

.


10



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


11

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)


12

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


13

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


14

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/

.


15

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


16

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


17

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.


18

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.


19

2. Kegiatan Belajar 2 : MENGHITUNG DENGAN EXCEL INDIKATOR

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

a. Uraian dan Contoh 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.

KEGIATAN BELAJAR 2 : MENGHITUNG DENGAN EXCEL -------------------------------------------------------------------------------------------------------------

20

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


21

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 KEGIATAN BELAJAR 2 : MENGHITUNG DENGAN EXCEL -------------------------------------------------------------------------------------------------------------

22

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


23

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.


24

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


25

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.


26

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


27

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 .


28

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.


29

Fungsi alternatif yg ditawarkan Excel berdasarkan masukan pada formula bar/Cell

Keterangan atas fungsi yg disorot

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


30

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.


31

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.


32

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 / ula , hu uf d

ela

/

. Hu uf

a gka a gka ha i, da hu uf

ela

a gka a gka 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:


33

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

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

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

Menampilkan nama bulan secara lengkap

Keterangan

Berdasarkan informasi yang disajikan dalam Tabel 2. 2 di atas, kita akan mencoba mengatur format tanggal menggunakan Custom Format.


34

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 17August-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.


35

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

6

h:m:s

20:5:7

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

20:05:07 Angka jam, menit, dan detik masingmasing dua digit Angka jam, menit, dan detik disajikan apa adanya

05:07:00 Menampilkan data per seratus detik

Contoh penggunaan format waktu adalah:  =te t

:

:

,[ ] dengan hasil 1205.


36

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  =DAY

/ / / /

: :

aka aka

e ghasilka e ghasilka

ilai ilai


37

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 2 3 4

1/12/2012 6/7/2012 45555 18/9/2012

Diinput dengan Format Bahasa Indonesia Diinput dengan Format Bahasa Inggris Dii put de ga Fo at Ge e al 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.


38

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 1 2 3 4 5

Nilai 17/8/2012 21/12/2012 1/10/2012 30/9/2012 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 1 2 3 4 5

Nilai 0.352048536 0.393226872 0.593730651 0.824227857 0.826215213


39

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




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 1 2 3 4 5

Nilai 44774 44274 45247 46412 43532


40

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)




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 1 2 3 4 5

Nilai 44774 44274 45247 46412 43532


41

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 1 2 3 4 5

Tanggal Lahir 25973 29490 31925 27055 30975


42

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 1 2 3 4 5

Nilai 44774 44274 45247 46412 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


43

Latihan: Tulislah kata/frasa berikut ini sepenuhnya dalam format kapital: No 1 2 3 4 5

Kata/Frasa Latihan Excel Tingkat Menengah Pusdiklat Keuangan Umum 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 1 2 3 4 5

Kata/Frasa Latihan Excel Tingkat Menengah Pusdiklat Keuangan Umum Badan Pendidikan dan Pelatihan Keuangan


44

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 1 2 3 4 5

Kata/Frasa LATIHAN EXCEL tingkat menengah PUSDIKLAT KEUANGAN UMUM 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


45

Latihan: Gabungkanlah frasa/kata dan angka berikut ini menggunakan fungsi CONCATENATE atau AMPERSAND (jangan lupa sisipkan spasi kosong pada tempatnya): No 1 2 3 4 5

Kata/Frasa 1 Sepak Piala Latihan Pusdiklat Nomor

Kata/Frasa 2 Bola Dunia Excel Keuangan Umum 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 1 2 3 4 5



46

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 1 2 3 4 5

Kata/Frasa latihan Excel tingkat menengah pusdiklat keuangan umum badan pendidikan dan pelatihan keuangan

Keterangan 3 karakter 4 karakter 5 karakter 6 karakter 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: 






47

Contoh: 

=RIGHT LATIHAN EXCEL ,

aka

e

e ika

kelua an berupa

EXCEL Latihan: Ubahlah sebagian dari kata/frasa berikut menjadi huruf besar (kapital) menggunakan fungsi RIGHT, LEFT, UPPER, LEN, dan CONCATENATE (AMPERSAND): No 1 2 3 4 5

Kata/Frasa latihan Excel tingkat menengah Pusdiklat keuangan umum badan pendidikan dan pelatihan keuangan

Keterangan 3 karakter 4 karakter 5 karakter 6 karakter 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


48

Latihan: Ubahlah sebagian dari kata/frasa berikut menjadi huruf besar (kapital) menggunakan fungsi MID, CONCATENATE (AMPERSAND), LEFT, RIGHT, UPPER, dan TRIM: No

Kata/Frasa

1 2 3 4 5

latihan Excel tingkat menengah pusdiklat keuangan umum badan pendidikan dan pelatihan keuangan

Karakter Awal 5 6 2 4 10

Jumlah 3 karakter 4 karakter 5 karakter 6 karakter 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



=TRIM A A

aka

aka

e ghasilka

e ghasilka

LATIHAN EXCEL

AA


49

Latihan: Hilangkanlah spasi yang tidak perlu pada kata/frasa berikut menggunakan fungsi TRIM: No 1 2 3 4 5

Kata/Frasa [spasi]LATIHAN[spasi]EXCEL[spasi] tingkat[spasi][spasi]menengah[spasi] [spasi]PUSDIKLAT[spasi] KEUANGAN[spasi][spasi][spasi]UMUM 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


50

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 1 30 59 ; 88 X 116 t 2 31 60 < 89 Y 117 u 3 32 61 = 90 Z 118 v 4 33 ! 62 > 91 [ 119 w 5 34 " 63 ? 92 \ 120 x 6 35 # 64 @ 93 ] 121 y 7 36 $ 65 A 94 ^ 122 z 8 37 % 66 B 95 _ 123 { 9 38 & 67 C 96 ` 124 | 10 39 ' 68 D 97 a 125 } 11 40 ( 69 E 98 b 126 ~ 12 41 ) 70 F 99 c 127 • 13 42 * 71 G 100 d 128 € 14 43 + 72 H 101 e 129 • 15 44 , 73 I 102 f 130 ‚ 16 45 74 J 103 g 131 ƒ 17 46 . 75 K 104 h 132 „ 18 47 / 76 L 105 i 133 … 19 48 0 77 M 106 j 134 20 49 1 78 N 107 k 135 21 50 2 79 O 108 l 136 ˆ 22 51 3 80 P 109 m 137 ‰ 23 52 4 81 Q 110 n 138 Š 24 53 5 82 R 111 o 139 25 54 6 83 S 112 p 140 Œ 26 55 7 84 T 113 q 141 • 27 56 8 85 U 114 r 142 Ž 28 57 9 86 V 115 s 143 • 29 58 : 87 W

C# Chr C# Chr C# Chr C# Chr 144 • 172 ¬ 200 È 228 ä 145 173 - 201 É 229 å 146 174 ® 202 Ê 230 æ 147 175 ¯ 203 Ë 231 ç 148 176 ° 204 Ì 232 è 149 • 177 ± 205 Í 233 é 150 – 178 ² 206 Î 234 ê 151 — 179 ³ 207 Ï 235 ë 152 ˜ 180 ´ 208 Ð 236 ì 153 ™ 181 µ 209 Ñ 237 í 154 š 182 ¶ 210 Ò 238 î 155 183 · 211 Ó 239 ï 156 œ 184 ¸ 212 Ô 240 ð 157 • 185 ¹ 213 Õ 241 ñ 158 ž 186 º 214 Ö 242 ò 159 Ÿ 187 » 215 × 243 ó 160 188 ¼ 216 Ø 244 ô 161 ¡ 189 ½ 217 Ù 245 õ 162 ¢ 190 ¾ 218 Ú 246 ö 163 £ 191 ¿ 219 Û 247 ÷ 164 ¤ 192 À 220 Ü 248 ø 165 ¥ 193 Á 221 Ý 249 ù 166 ¦ 194 Â 222 Þ 250 ú 167 § 195 Ã 223 ß 251 û 168 ¨ 196 Ä 224 à 252 ü 169 © 197 Å 225 á 253 ý 170 ª 198 Æ 226 â 254 þ 171 « 199 Ç 227 ã 255 ÿ

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 2 3 4 5

Sepak Piala Latihan Pusdiklat Nomor

Bola Dunia Excel Keuangan Umum 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. KEGIATAN BELAJAR 2 : MENGHITUNG DENGAN EXCEL -------------------------------------------------------------------------------------------------------------

51

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 1 2 3 4 5


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.


52

Contoh: 

=FIND A , MAHARDIKA

aka

e ghasilka

kelua a

e upa

angka 2 

=FIND a , MAHARDIKA ,

aka

e ghasilka keluaran berupa

aka

e ghasilka kelua a

#VALUE (error) 

=FIND A , MAHARDIKA ,

e upa

angka 4 Latihan: Temukan huruf yang dicari pada tabel berikut dan ubahlah seluruh huruf sesudahnya menjadi huruf kapital menggunakan fungsi FIND: No 1 2 3 4 5

Kata/Frasa lAtihan Excel Tingkat menengah Pusdiklat keuangaN umum badan pendidikan dan pElatihan keuangan

Huruf yang Dicari a t d N 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.


53

Contoh 

=SEARCH A , MAHARDIKA

aka

e ghasilka kelua a

e upa

angka 2 

=SEARCH a ,

MAHARDIKA ,

aka

e ghasilka

kelua a

MAHARDIKA ,

aka

e ghasilka

kelua a

berupa angka 2 

=SEARCH A , berupa angka 4

Latihan: Temukan huruf yang dicari pada tabel berikut dan ubahlah seluruh huruf sesudahnya menjadi huruf kapital menggunakan fungsi SEARCH: No 1 2 3 4 5

Kata/Frasa lAtihan Excel Tingkat menengah Pusdiklat keuangaN umum badan pendidikan dan pElatihan keuangan

Huruf yang Dicari a t d N 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.


54

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 1 2 3 4 5

Alpha-Numeric 1000 2000 3000 4000 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



=REPT a



=REPT - ,



=REPT HA ,

,

aka aka

e ghasilka kelua a e ghasilka kelua a e ghasilka kelua a aka

AAAAA a a a ----------

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


55

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
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


56

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
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.


57

Latihan: Tentukan nilai keluaran dari tiap pernyataan berikut: No. 1 2 3 4 5 6

Nilai A1 180 172 131 185 197 131

Nilai A2 116 173 180 128 104 188

Nilai A3 150 130 146 107 106 166

Logical Test A1 < A2 OR A3 > A2 A1 < A2 OR A1 > A2 A1 < A2 OR A3 > A2 A1 < A2 OR A3 <> A2 A1 > A2 OR A3 > A2 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.


58

Latihan: Tentukan nilai keluaran dari tiap pernyataan (logical test) berikut: No. 1 2 3 4 5 6

Nilai A1 180 172 131 185 197 131

Nilai A2 116 173 180 128 104 188

Nilai A3 150 130 146 107 106 166

Logical Test A1 < A2 OR A3 > A2 A1 < A2 OR A3 = A2 A1 > A2 OR A3 > A2 A1 > A2 OR A3 <> A2 A1 < A2 OR A3 > A2 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.


59

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 pernyataan tersebut e uliska kelua a

e upa kata SEPULUH , jika

bernilai FALSE, maka Excel

akan

e upa kata SERATUS

Latihan: Tentukan keluaran dari fungsi IF berikut ini: No.

Fungsi IF

1 2 3 4 5 6

=IF A = A , SAMA , TIDAK SAMA =IF A > A , , =IF(A1 <> A2,100,200) =IF(A2 > A1,A2,A1) =IF *A =A / , BENAR , SALAH =IF(A2/A1=A1/5,A1*A2,A1/A2)

Keluaran jika TRUE

Keluaran jika FALSE


60

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


61

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)


62

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:


63



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


64

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:


65



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-Valuekan  Tekan Tombol CTRL + C untuk meng-copy isi Cell/Range  Klik kanan pada mouse atau keyboard dan akan muncul tampilan berikut


66

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


67

 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


68

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


69

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


70

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)


71

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:


72

 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 data a ua , aitu hu uf

e e uka pasa ga , de ga

a pada baris ketiga

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.


73



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



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

jumlah

kolomnya,

maka

u p

fungsi

LOOKUP

akan

membandingkan lookup_value dengan data pada kolom pertama. 

Berikut ilustrasi dari Tabel Array dimaksud:



a b c Lookup_ alue Bu p aka

1 2 3 e e uka pasa ga

kedua data a ua , aitu hu uf

, de ga

a pada baris

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



=LOOKUP(A2, {0, 60, 70, 80, 90}, {"F", "D", "C", "B", "A"}) dengan ilai kelua a sa a de ga



F A

=LOOKUP(A2, {0, 60, 70, 80, 90}, {"F", "D", "C", "B", "A"}) dengan ilai kelua a sa a de ga

C


74

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.


75

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  Table_array adalah range A1:C4  Row_index_num adalah 3 (data keluaran ada di baris ke-3)  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)


76

 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  Table_array adalah range A1:C4  Row_index_num adalah 3 (data keluaran ada di baris ke-3)  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  Table_array adalah range A1:C4  Row_index_num adalah 4 (data keluaran ada di baris ke-4)  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


77

 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 NO

PENGHASILAN

KELOMPOK

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


78

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.


79



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



Table_array adalah range A2:C10



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


80

 = VLOOKUP(1,A2:C10,3,TRUE) dengan keluaran 100 







Col_index_num adalah 3 (kolom Temperature)






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-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












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


81

 =VLOOKUP(2,A2:C10,2,TRUE) 













VLOOKUP menemukan pasangan yang paling mendekati angka 2 (atau angka terbesar yang nilainya kurang dari nilai lookup_value atau kurang dari 2) adalah angka 1,29 dengan nilai keluaran pada kolom ke-2 adalah 1.71.

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 VLOOKUP NO

PENGHASILAN

KELOMPOK

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


82

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


83

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


84

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


85

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


86

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


87



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


88



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


89

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


90

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


91



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.


92

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.


93

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



atau

.

Criteria

bersifat

e 

data ase. Co toh

case

ite ia adalah

, >

, B , Ka to ,

Excel

insensitiveartinya

tidak

akan

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 E el

e a i kata a g e akhi a

a

da

aka

e

uat

e ju lah tepat

huruf. 

Penggunaan asterisk (*) membuat Exceltidak membatasi jumlah huruf dari kata a g kita a i,

isal a *a , aka

e ghitu g selu uh kata a g e akhi a

e

uat E el

a .



Jumlah kriteria yang dapat digunakan hanya satu



Pastikan database tidak mengandung spasi yang tidak diperlukan, se a

E el

aka

ka to [spasi], da

e ka to ,

edaka eskipu

data

[spasi] ka to ,

ata kita tidak dapat

membedakannya.


94

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)  A2:A5 adalah range pada formula ini  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)  A2:A5 adalah range pada formula ini  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


95



=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)  B2:B5 adalah range pada formula ini 

<>

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")  B2:B5 adalah range pada formula ini  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


96



=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")  A2:A7 adalah range/database pada formula ini  "?????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,"*")  A2:A7 adalah range/database pada formula ini 

*

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,"<>"&"*")  A2:A7 adalah range/database pada formula ini 

<> & * 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.


97

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 bersamasama berhasil melampaui kuotanya pada periode Juni s.d. Agustus?

g) Fungsi COUNTIFS Syntax: =COUNTIFS(range1, criteria1, a ge , Fungsi

ite ia ,…

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.


98

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)


99



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.


100

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


101

 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

atau 

, >

, B , Ka to ,

.

Criteria bersifat case insensitive artinya Excel tidak akan e



ite ia adalah

edaka a ta a Ka to , KANTOR , atau ka to .

Criteria juga dapat mengandung karakter asterisk (*) atau tanda tanya (?)


102



Penggunaan tanda tanya (?) akan membatasi jumlah huruf dalam satu kata a g di a i oleh E el. Misal a, ???a Ex el

e a i kata a g e akhi a

a

da

aka

e

uat

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

menghitung seluruh kata yang e akhi a

e

uat E el

a .



Jumlah kriteria yang dapat digunakan hanya satu (tunggal)



Pastikan database tidak mengandung spasi yang tidak diperlukan, se a

E el

aka

ka to [spasi], da

e

edaka

ka to ,

data

eskipu

[spasi] ka to ,

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 

>


u tuk

e

atasi

proses penjumlahan


103

 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 =




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


104

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


105

 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

a g aka

diaplikasika

ke

criteria_range pertama 

>= %

adalah

ite ia kedua

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


106

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 mempraktikkannya 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.


107

c. Tes Formatif Kegiatan Belajar 2 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 "

7.

B S

Formula

", , ,

adalah

=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.


108

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.


109

3. Kegiatan Belajar 3 : MENYUSUN LAPORAN INDIKATOR INDIKATOR Setelah mengikuti pembelajaran ini peserta diklat diharapkan dapat secara efektif menggunakan fasilitas Excel berikut dalalm penyusunan laporan:

a.



Conditional Formatting;



Remove Duplicates dan Sort;



Filter;



Pivot Table; dan



Goal Seek

Uraian dan Contoh

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.

KEGIATAN BELAJAR 3 : MENYUSUN LAPORAN -------------------------------------------------------------------------------------------------------------

110

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:


111

Presets Menu

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

-

Mengandung teks tertentu (Text that Co tai s…

-

Data Tanggal Tertentu (a Date O u i g…

-

Data Ke

E ual To…

a Duplli ate Value…


112

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


113



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:

Ubah format Highlight

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.


114

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.


115

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


116

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.


117

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.


118

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.


119

1) Format only cells that contain Ketika pilihan ini dipilih, maka akan tampak kotak dialog sebagaimana tampak pada Gambar 3. 13 di bawah.

1

2 Pengaturan Format Cell

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)


120



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)


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


121

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)


Blanks Pilihan ini digunakan untuk mengatur format cell atau kumpulan cell (menandai) yang tidak berisi data sama sekali (blank) Format dari cell yang memenuhi syarat diatur melalui menu format (Gambar 3. 13).



No Blanks Pilihan ini digunakan untuk mengatur format cell atau kumpulan cell (menandai) yang tidak blank Format dari cell yang memenuhi syarat diatur melalui menu format (Gambar 3. 13).



Errors Pilihan ini digunakan untuk mengatur format cell atau kumpulan cell (menandai) yang mengandung error (misalnya #N/A!, #VALUE, dan #DIV/0!) Format dari cell yang memenuhi syarat diatur melalui menu format (Gambar 3. 13).


122



No Errors Pilihan ini digunakan untuk mengatur format cell atau kumpulan cell (menandai) yang tidak mengandung error Format dari cell yang memenuhi syarat diatur melalui menu format (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:

Formula diinput di sini

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.


123

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)


124

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.


125

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


126



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


127

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:

SORT

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.


128



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.

Ascending atau Descending

basis (seed) untuk sort

posisi pointer

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. KEGIATAN BELAJAR 3 : MENYUSUN LAPORAN -------------------------------------------------------------------------------------------------------------

129

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:

SORT

Gambar 3. 25 – Menu SORT



Untuk menggunakan fasilitas ini, langkah pertama yang kita lakukan adalah menyorot range yang akan kita sort, yaitu A1:L9


130



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

te te tu ukup dilakuka de ga itu, kita juga dapat

e eka to

e ghapus le el atau k ite ia ol Delete Level . Di sa pi g

e a faatka fasilitas Copy Level u tuk

e pe

udah

kita dalam melakukan penetapan kriteria.


131

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.


132



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.


133

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


134

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:

Duplikasi telah hilang

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.


135

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.


136

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

LOCATION

di

e ilih ACTION

e upa

a a hasil p oses filte isasi aka

COPY TO ANOTHER disajika

pada lokasi

tertentu dalam worksheet yang sama dengan data asli.


137

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.


138

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.


139

DATA UNIK

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.


140

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


141

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.

PIVOT

Gambar 3. 42 – Menu Pivot Table


142

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.


143

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


144

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.


145

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.


146

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.

FILTER

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


147

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:


148

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 besarbesaran 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:


149

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:


150

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


151

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 sehariharinya 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.


152

c.

Tes Formatif Kegiatan Belajar 3 Perhatikan data berikut ini:

Gunakan fasilitas: -

Conditional Formatting untuk mewarnai seluruh baris data (Kolom A s.d. D) jika ell dala

d.

kolo

a a

e isi data AA

Urutkan data me ggu aka ke

tgl seto

Umpan Balik dan Tindak Lanjut 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.


153

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 langkahlangkah yang ditempuh dalam menyelesaikan pekerjaan sehari-hari di tempat kerja, khususnya yang berkaitan dengan penggunaan Excel.

Microsoft Excel -------------------------------------------------------------------------------------------------------------

154

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 -------------------------------------------------------------------------------------------------------------

155

KUNCI JAWABAN Test Formatif Kegiatan Belajar 1

Test Formatif Kegiatan Belajar 2

Microsoft Excel -------------------------------------------------------------------------------------------------------------

156

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 -------------------------------------------------------------------------------------------------------------

157

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 -------------------------------------------------------------------------------------------------------------

158

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 -------------------------------------------------------------------------------------------------------------

159

DAFTAR PUSTAKA Jelen, Bill. 2010. Microsoft Excel 2010 In Depth. Indiana, USA: Que Publishing. Microsoft Excel Help

DAFTAR ISTILAH -------------------------------------------------------------------------------------------------------------

160

PUSDIKLAT KEUANGAN UMUM Badan Pendidikan Dan Pelatihan Keuangan Kementerian Keuangan R.I

Recommend Documents