Excell

1

BAB I LINGKUNGAN KERJA EXCEL

Work Sheet

Work Sheet Work Sheet

Row Heading Cell Name Box

Tab Sheet

Task Pane

Range Colum Heading Formula Bar

Work Sheet adalah suatu bidang yang merupakan lembar kerja dari Excel. Bagian bagian dari work sheet diantaranya: kolom, baris, dan cell (sel) Cell (selanjutnya ditulis sel) adalah pertemuan antara baris dan kolom. Contoh: A10 (fokus berada pada baris A dan kolom 10) Range adalah kumpulan dari beberapa sel. Colom Heading adalah induk dari sebuah kolom, dimana pengaturan atau perubahan pada induk ini akan berakibat kepada seluruh sel yang terdapat pada kolom tersebut. Row Heading adalah induk dari sebuah baris, pengaturan pada row heading ini, juga akan berakibat pada seluruh sel yang terdapat pada baris tersebut. Formula Bar, adalah batang yang mengidentifikasikan seluruh kegiatan pada sebuah sel. Formula bar menampilkan seluruh karak ter yang dituliskan pada sebuah cell. Formula bar juga digunakan untuk menuliskan rumus-rumus (formula) kepunyaan Excel. Name Box adalah kotak yang menampilkan informasi tentang sel atau range yang sedang aktif (terpilih). Tab Sheet adalah suatu lajur yang menampilkan sheet-sheet yang terdapat pada work book. Task Pane adalah sebuah jendela yang berisi perintah-perintah yang dapat digunakan sebagai jalan pintas untuk mengontrol aplikasi. Task Pane merupakan fitur baru dari Ms. Office yang mulai diterapkan pada Ms. Office XP (2002).

Hand Out Ms. Office Excell 2003 - INOCHI Computer – Agung Novian, S.Kom

2

BAB II PENGATURAN Sebelum memulai bekerja dengan Excel, saya anjurkan Anda untuk melakukan sedikit pengaturan-pengaturan, yang mungkin dapat membantu Anda nantinya. Option Klik menu ToolsOptions… untuk menampilkan dialog Options. Dialog ini terdiri dari 13 tab. Hanya saja, saya hanya akan menjelaskan beberapa bab saja yang saya anggap cukup penting. Tab pertama adalah tab General, pengaturan pada komponen-komponen dari tab ini adalah untuk mengubah tampilan dasar dari worksheet Excel. Salah satu yang dapat Anda ubah adalah “Default file location:”. Ubahlah kotak teks dengan alamat sebuah folder yang akan dijadikan sebagai lokasi default. Contoh, teks pada kotak teks tersebut dengan: “C:\Excel”. Artinya, Excel akan otomatis menuju ke lokasi tersebut saat Anda membuka atau menyimpan file Excel (*.xls). Syaratnya, Anda telah membuat folder dengan nama “Excel” tersebut di drive C. Anda juga bisa mengganti “Standard Font” dan “Size”, jika diperlukan untuk mengubah jenis dan ukuran huruf standard.

Tab kedua adalah tab Save, pada tab ini gantilah nilai default 10 minutes dengan nilai 1 (satu). Ini adalah fasilitas untuk menyimpan file recover (pemulihan), yang akan tersimpan secara otomatis tiap satuan menit yang Anda tentukan. Fungsi file ini adalah sebagai file sementara yang akan sangat membantu bila Anda mengalami gangguan pada komputer Anda. Misalnya, jika satu saat Anda mengalami aliran listrik padam, sementara Anda belum menyimpan hasil kerja Anda. Nah, file recover ini adalah file duplikat dari hasil kerja Anda, yang dapat Anda gunakan setelah Anda menyalakan kembali komputer Anda. Besarnya nilai menit yang Anda masukkan adalah interval waktu untuk penyimpanan file recovery.

Tab berikutnya adalah tab Custom Lists. Komponen pada tab ini digunakan untuk membuat suatu daftar khusus seperti daftar nama-nama hari atau nama-nama bulan. Sebagai contoh, kita akan memasukan daftar nama-nama bulan. Klik “NEW LIST” pada “Custom List”, kemudian ketikkan nama bulan pertama: “Januari” pada kotak “List Entries:” , tekan Enter pada keyboard, ketikkan nama bulan kedua, “Pebruari”, tekan Enter lagi. Lakukan langkah tersebut sampai bulan ke duabelas. Setelah itu tekan tombol Add di sebelah kotak “List Entries:”. Untuk mencobanya, klik tombol OK untuk menutup dialog Options. Ketikkan: “Januari” pada sebuah sel. Pindahkan pointer mouse pada kotak kecil berwarna hitam di sisi kanan bawah sel yang telah Anda tulisi, hingga pointer mouse berubah menjadi tanda plus. Klik tombol kiri mouse, lakukan dragging (menyeret mouse) ke arah bawah. Maka, daftar yang telah Anda buat akan otomatis tertulis pada sel-sel di bawah sel yang telah Anda tulisi tadi.

Hand Out Ms. Office Excell 2003 - INOCHI Computer – Agung Novian, S.Kom

3

Customize Klik menu ToolsCustomize… untuk menampilkan dialog Customize. Atau cara singkat adalah dengan mengklik kanan pada menubar atau toolbar. Bisa juga dengan mengklik ganda bidang kosong di daerah toolbar. Dialog ini terdiri dari 3 (tiga) tab. Tab pertama adalah Toolbars. Tab ini digunakan untuk menambahkan atau mengurangi toolbar-toolbar pada aplikasi Excel. Untuk menambahkan, cukup tandai kotak periksa pada sebuah daftar toolbar. Maka akan ditampilkan toolbar baru dalam keadaan mengapung (floating). Anda dapat menempelkan toolbar ini di sisi atas, bawah, kiri atau kanan jendela Excel, dengan cara menyeret bagian titlebar dari toolbar tersebut ke arah penempelan sesuai keinginan Anda.

Tab kedua adalah Command. Salah satu kegunaan komponen dari tab ini adalah untuk menambahkan tomboltombol yang dirasa diperlukan. Sebagai contoh, klik list “Format” pada kotak “Categories:”, kemudian pada kotak “Commands:” klik tombol “Cell…”, tahan tombol kiri mouse, lakukan dragging ke arah toolbar dari jendela Excel. Maka pada toolbar akan bertambah sebuah tombol baru.

Anda juga dapat mengurangi/menghilangkan tombol-tombol pada toolbar atau menu-menu dari menubar yang dirasa tidak diperlukan, dengan cara klik tombol atau menu yang akan dihilangkan, lakukan draging menuju ke dialog Customize ini. Masih untuk tab kedua, fasilitas berikutnya adalah “Modify Selection”, fasilitas ini digunakan untuk mengubah tampilan dari menu atau toolbar. Anda dapat mengubah teks yang ditampilkan sebuah menu atau mengubah gambar dari menu tersebut, atau gambar dari sebuah tombol pada toolbar. Untuk contoh, klik menu File pada menubar dari jendela Excel, kemudian klik tombol Modify Selection, akan ditampilkan sebuah pop up menu, gantilah teks yang bertuliskan “&File” dengan “&Berkas”, setelah itu tekan tombol Enter pada keyboard. Sekarang Anda lihat tampilan menu File pada menubar dari jendela Excel sudah berganti dengan teks: “Berkas”. Hand Out Ms. Office Excell 2003 - INOCHI Computer – Agung Novian, S.Kom

4

Percobaan berikutnya, klik menu FileNew…, klik tombol Modify Selection, pada pop up menu yang ditampilkan, klik menu Change Button Image, klik sebuah gambar, maka sekarang gambar untuk menu FileNew… sudah berganti. Selain dapat mengganti gambar dengan gambar yang telah disediakan, Anda dapat pula mengganti gambar menu tersebut dengan gambar yang Anda gambar sendiri. Caranya setelah mengklik tombol Modify Selection, klik menu Edit Button Image… Pada dialog yang ditampilkan, klik kotak “Erase”, gerakan pointer mouse ke kotak Picture, lakukan dragging, hingga seluruh gambar terhapus. Klik sebuah warna pada kotak “Colors” gerakan pointer mouse ke kotak Picture, menggambarlah dengan cara mendragging. Setelah selesai, klik OK.

Tapi, nah bagaimana jika Anda menyesali pengeditan gambar, karena gambarnya buruk, mungkin? Gampang saja, klik menu FileNew… tadi, klik tombol Modify Selection, pada pop up menu yang ditampilkan, klik Reset. Beres! Dengan kemampuan di atas, bisa saja Anda mengalih bahasakan semua menu ke dalam bahasa Indonesia. Tab berikutnya adalah tab Options, pada tab ini tandai kotak periksa “Show Standard and Formatting toolbars on two rows”, agar toolbar Standard dan toolbar Formatting ditampilkan dalam dua baris (defaultnya adalah satu baris). Tandai pula kotak periksa “Always show full menus”, agar (misal, saat anda mengklik menu File) sub-sub menu ditampilan seluruhnya

Mengset Regional Mengset Regional tidak Anda lakukan aplikasi Excel, Anda mengsetnya pada Control Panel, caranya: klik tombol Start pada taskbar, klik Control Panel. Kemudian pada jendela yang ditampilkan klik ganda icon Regional and Language Options. Pada dialog icon Regional and Language Options, pilih list Indonesian, pada kotak kombo. Seperti ilustrasi gambar di bawah ini.

Hand Out Ms. Office Excell 2003 - INOCHI Computer – Agung Novian, S.Kom

5

Mengset Regional ini akan sangat berpengaruh saat Anda penggunaan formula dan memformat sel pada Excel. Pengaruh pada penggunaan formula yang sangat kelihatan adalah saat penggunaan simbol pemisah parameter. Perhatikan contoh formula seperti berikut ini: =IF(A2 <> 0, 5/100 * A3, 0) Contoh formula di atas, simbol pemisah parameter yang digunakan adalah simbol coma (,), maka jika Anda mengset Regional pada Indonesian, untuk simbol pemisah parameter Anda harus menggunakan simbol semicolon (;). Dan formula di atas, harus Anda tulis sebagai berikut: =IF(A2 <> 0; 5/100 * A3; 0) Pada buku ini simbol pemisah parameter yang digunakan adalah simbol semicolon (;).

Hand Out Ms. Office Excell 2003 - INOCHI Computer – Agung Novian, S.Kom

6

BAB III Mengolah WorkSheet Bekerja dengan sel Bekerja dengan Excel, berarti Anda bekerja dengan sel. Semua aktifasi dilakukan dalam sel. Untuk mengetikkan teks pada sebuah sel klik sel tersebut, dan mulailah mengetik. Awas! Jika Anda salah mengetikkan teks dan Anda ingin memperbaikinya, Anda tidak akan dapat menggunakan tombol-tombol anak panah pada keyboard. Misalnya, Anda sebtulnya ingin mengetikkan: “Agung”, tetapi ternya Anda salah mengetik dengan: “Aung”, berarti Anda harus menyisipkan sebuah huruf yaitu: “g”. Jika Anda pernah menggunakan Ms. Word, Anda tinggal menekan tombol anak panah ke kiri pada keyboard, hingga cursor berada di depan teks: “A”, kemudian Anda tinggal menuliskan huruf “g”. Berbeda pada Excel, begitu Anda menekan tombol arah panah ke kiri, yang berpindah bukanlah cursor, melainkan sel yang terfokus. Untuk mengedit teks (melakukan perubahan terhadap isi) dari sebuah sel, klik ganda pada sel tersebut atau tekan tombol F2 pada keyboard, sekarang Anda bisa menggunakan tombol anak panah pada keyboard. Memformat teks Memformat teks, maksudnya mengubah bentuk tampilan teks pada suatu sel, seperti tebal, garis bawah, italic (cetak miring), bentuk dan ukuran font, dan sebagainya. Untuk memformat teks pada suatu sel, klik terlebih dahulu sel yang akan diformat. Untuk menebalkan teks, tekan Ctrl + B atau klik tombol . Untuk memiringkan tekan Ctrl + I atau klik tombol . Untuk menggarisbawahi tekan Ctrl + U atau atau klik tombol . Untuk mengubah bentuk font, klik kombo ukuran font, klik kombo

, kemudian klik nama sebuah font yang dikehendaki. Untuk mengubah , dan pilih nilai yang ukuran yang dikehendaki.

Memformat Sel

Memformat sel berarti mengubah sel (bentuk, data, dan lain-lain) dari nilai standard. Untuk memformat sel, klik kanan pada sel yang akan diformat, pilih Format Cell… Ada beberapa jenis pemformatan, seperti: Number, untuk mengubah data yang dihasilkan dan ditampilkan pada sel. Alignment, untuk mengatur posisi teks terhadap sel. Font, seperti memformat teks. Border, untuk memberi bingkai pada sel. Pattern, pewarnaan latar belakang sel. Protection, mengatur kelayakan (akses) untuk mengedit sel. Sebagai contoh memformat sel, ikuti langkah berikut ini. Ketikkan data seperti pada ilustrasi gambar berikut: 1 2 3 4

A Nama Ado Ajat Udeng

B Gaji Pokok 700000 700000 700000

C Lembur 250000 100000 100000

D Total 950000 800000 800000

Klik cell B2, tahan tombol mouse, lakukan dragging sampai sel D4 (langkah ini disebut memblok). Klik kanan diantara range (sel yang terblok), pilih Format Cell... Akan ditampilkan dialog Format Cells. Klik tab “Number”, pilih/klik list “Currency”, Tandai kotak periksa “Use 1000 Separator (,)”, Ubah “Decimal places:” menjadi 0 (nol). Ubah “Symbol” bila perlu (dengan “Rp Indonesian”). Klik OK. Apa yang terjadi? Angka pada sel yang terblok akan disisipi separator ribuan (contoh: 700.000). Sebetulnya, langkah ini bisa Anda lakukan dengan mudah, dengan cara: setelah sel terblok, klik tombol Coma Style . Masih dalam posisi sel terblok, klik tombol Decrease Decimal , sebanyak dua kali, untuk menghilangkan dua angka desimal di belakang koma. Atau untuk cara lebih mudah lagi, dan sekaligus untuk menampilkan simbol Rp, Anda tinggal mengklik tombol Currency Style, . Tampilan akhir pemformatan seperti ilustrasi gambar berikut ini: Hand Out Ms. Office Excell 2003 - INOCHI Computer – Agung Novian, S.Kom

7

1 2 3 4

A Nama Ado Ajat Udeng

B Gaji Pokok 700.000 700.000 700.000

C Lembur 250.000 100.000 100.000

D Total 950.000 800.000 800.000

Jika Anda telah menentukan atau menginginkan baris-baris pada satu atau beberapa kolom diformat dengan suatu pilihan format tertentu, Anda dapat melakukan dengan mengklik column heading dari kolom tersebut atau memblok beberapa column heading, kemudian lakukan pemformatan. Contoh kasus 1: Baris-baris pada kolom B, Anda gunakan untuk mengisi data Gaji Pokok, untuk mempercantik tampilan, Anda ingin memformat baris-baris pada kolom B dengan Currency. Maka, klik kananlah pada column heading B, dan pilih menu Format Cell... pilih Format Cell..., lakukan langkah memformat sel seperti cara yang telah dijelaskan di atas. A 1 2 3 4

B Gaji Pokok 700.000 700.000 700.000

C

Contoh kasus 2: Baris-baris pada kolom B, Anda gunakan untuk mengisi data Gaji Pokok, baris-baris pada kolom C, Anda gunakan untuk mengisi data Lembur. Di sini berarti ada dua buah kolom yang akan diformat dengan tipe yang sama (Currency), yaitu: kolom B dan kolom C. Daripada Anda harus mengklik satu persatu column heading kemudian dilakukan pemformatan, Anda dapat secara cepat melakukannya dengan mengklik column heading B, kemudian lakukan dragging ke kolom C. Klik kanan pada kolom B atau kolom C, dan pilih menu Format Cell..., lakukan langkah memformat sel seperti cara yang telah dijelaskan di atas. A 1 2 3 4

B Gaji Pokok 700.000 700.000 700.000

C Lembur 250.000 100.000 100.000

Contoh kasus 3: Baris-baris pada kolom B, Anda gunakan untuk mengisi data Gaji Pokok, baris-baris pada kolom C, Anda gunakan untuk mengisi data Tanggal, dan baris-baris pada kolom D, Anda gunakan untuk mengisi data Lembur. Di sini berarti ada dua buah kolom yang akan diformat dengan tipe yang sama (Currency), yaitu: kolom B dan kolom D. Jika Anda melakukan cara pada contoh kasus 2, maka kolom C pun akan terformat secara Currency. Untuk memformat beberapa kolom dengan posisi kolom yang tidak berurutan, Anda dapat melakukannya dengan mengkombinasikan penggunaan tombol Ctrl pada keyboard. Untuk kasus ini, klik column heading B, tekan dan tahan (jangan dilepas) tombol Ctrl pada keyboard. Kemudian klik column heading D. Lepaskan tombol Ctrl, klik kanan pada kolom B atau kolom D, dan pilih menu Format Cell..., lakukan langkah memformat sel seperti cara yang telah dijelaskan di atas. A 1 2 3 4

B Gaji Pokok 700.000 700.000 700.000

C Tanggal 24-Sep-82 13-Okt-84 15Okt-81

D Lembur 250.000 100.000 100.000

Membingkai Sel Agar tampilan bisa lebih menarik dan membentuk sebuah tabel, range atau sel tentunya harus dibingkai (diberi garis-garis tepi). Meskipun worksheet pada Excel sepertinya telah bergaris tepi/berbingkai tetapi sebenarnya tidak. Kalau Anda mau bukti, silakan klik menu FilePrint Preview. Anda tidak akan melihat garis bingkai disana. Cara termudah untuk membingkai sel atau range adalah dengan mengklik sel atau membuat suatu range (memblok sel), kemudian, klik tombol dropdown (gambar segitiga kecil) pada tombol Border.

Hand Out Ms. Office Excell 2003 - INOCHI Computer – Agung Novian, S.Kom

8

Cara lain adalah dengan klik kanan pada suatu sel atau range, kemudian klik Format Cells… Pada dialog yang ditampilkan, klik tab Border, klik tombol Outline, dan atau klik juga tombol Inside, klik OK. Ini adalah cara untuk membingkai range dengan bentuk dan ketebalan garis yang sama. Bagaimana jika ingin membingkai seperti pada ilustrasi gambar di bawah? A 1 2 3 4 5

B Nama Ado Ajat Udeng

C

D

Gaji Pokok 700000 700000 700000

Lembur 250000 100000 100000

E Total 950000 800000 800000

Mudah saja, sebelumnya, Anda perlu membuat dua buah range. Klik sel B2, blok sampai E2. Tekan dan tahan (jangan dilepas) tombol Ctrl, kemudian blok sel B3 sampai E5. Lepas tombol Ctrl, klik kanan di daerah terblok, klik Format Cells… Klik tab Border, pada kotak “Style”, klik gambar garis dengan dua baris , bila perlu klik kombo “Color”, pilih warna sesuai keinginan, klik tombol Outline. Klik gambar garis sebaris

, bila perlu

klik juga kombo “Color”, pilih warna sesuai keinginan, klik tombol Inside, terakhir klik OK, klik bebas pada worksheet (pada sel lain yang tidak terblok), bagaimana hasilnya? Mengubah ukuran kolom Untuk mengubah ukuran kolom seperti memperlebar atau mempersempit kolom dapat dilakukan dengan cara mengklik sebuah sel pada kolom tersebut. Contoh, jika Anda ingin mengubah ukuran kolom D, Anda klik saja sel D1, atau D2, atau Dn. Kemudian klik menu FormatColumnWidth… Masukkan nilai baru pada kotak Column width:, klik OK. Maka, column D sudah berubah ukuran lebarnya. Cara lain mengubah ukuran kolom adalah, gerakan pointer mouse ke arah column headings. Posisikan pointer mouse ke garis pemisah antara column heading D dan E sampai pointer berubah menjadi: , lakukan dragging ke arah kanan untuk memperlebar dan ke arah kiri untuk mempersempit kolom. Untuk mengubah beberapa kolom dengan ukuran lebar yang sama, Anda dapat melakukannya dengan memblok (atau memilih dengan bantuan tombol Ctrl) column heading-column heading yang akan diubah, kemudian posisikan pointer mouse ke sebuah garis pemisah dari column heading yang terblok. Lakukan dragging. A

B

C

D

E

1 2 3 4 Menambahkan dan Mengurangi Kolom Anda dapat menambahkan kolom-kolom dalam Excel dengan 2 (tiga) cara, yaitu: 1. 2.

Klik sebuah sel. Klik menu InsertColumns Klik column heading, klik kanan pada daerah kolom yang terblok dan pilih Insert atau tekan tombol I pada keyboard.

Kolom akan ditambahkan pada sisi kiri dari posisi kolom aktif. Untuk menambahkan beberapa kolom sekaligus, dengan memblok beberapa column heading, kemudian lakukan salah satu cara di atas. Untuk mengurangi kolom juga disediakan 2 (dua) cara, yaitu: 1. 2.

Klik sebuah sebuah sel. Klik menu EditDelete, pada dialog yang ditampilkan, klik option Entire column, klik OK. Klik column heading, klik kanan pada daerah kolom yang terblok dan pilih menu Delete. Hand Out Ms. Office Excell 2003 - INOCHI Computer – Agung Novian, S.Kom

9 Anda juga dapat mengurangi beberapa kolom sekaligus dengan memblok beberapa column heading, kemudian lakukan salah satu cara di atas. Mengubah ukuran baris Untuk mengubah ukuran ketinggian baris dapat dilakukan dengan cara mengklik sebuah sel pada baris tersebut. Contoh, jika Anda ingin mengubah ukuran baris 10, Anda klik saja sel A10, atau B10, atau ?10. Kemudian klik menu FormatRowHeight… Masukkan nilai baru pada kotak Row height:, klik OK. Maka, baris 10 sudah berubah ukuran tingginya. Cara lain mengubah ukuran tinggi baris adalah, gerakan pointer mouse ke arah row headings. Posisikan pointer mouse ke garis pemisah row heading 10 dan 11 sampai pointer berubah menjadi: , lakukan dragging ke arah bawah untuk mempertinggi dan ke arah kiri untuk memperpendek baris. Untuk mengubah beberapa baris dengan ukuran tinggi yang sama, Anda dapat melakukannya dengan memblok (atau memilih dengan bantuan tombol Ctrl) row heading-row heading yang akan diubah ukurannya, kemudian posisikan pointer mouse ke sebuah garis pemisah dari row heading yang terblok. Lakukan dragging. A

B

C

1 2 3 4 5 Menambahkan dan Mengurangi Baris Anda dapat menambahkan baris dalam Excel dengan 2 (tiga) cara, yaitu: 1. 2.

Klik sebuah sebuah sel. Klik menu EditDelete, pada dialog yang ditampilkan, klik option Entire row, klik OK. Klik row heading, klik kanan pada daerah kolom yang terblok dan pilih Insert atau tekan tombol I pada keyboard.

Baris akan ditambahkan pada sisi atas dari nomor baris aktif. Untuk menambahkan beberapa baris sekaligus, dengan memblok beberapa row heading, kemudian lakukan salah satu cara di atas. Untuk mengurangi baris juga disediakan 2 (dua) cara, yaitu: 1. 2.

Klik menu EditDelete Klik column heading, klik kanan pada daerah kolom yang terblok dan pilih menu Delete.

Anda juga dapat mengurangi beberapa baris sekaligus dengan memblok beberapa row heading, kemudian lakukan salah satu cara di atas. Menuliskan Formula Untuk menuliskan suatu formula, Anda dapat langsung menuliskannya pada sebuah sel, penulisan formula diawali dengan simbol assign (=), kemudian diikuti oleh nama formula. Contoh: =SUM(D2:D6) Sebuah formula, selalu dilengkapi oleh parameter-parameter. Parameter adalah ketentuan-ketentuan yang dijadikan rujukan untuk menghasilkan nilai kembali dari formula tersebut. Parameter-parameter ini biasanya ditampilkan pada sebuah kotak berwarna kuning yang terlihat tepat di bawah sel yang sedang ditulisi formula. Contoh: =IF(logical_true;[value_if_true];[value_if_false]) Contoh di atas adalah sebuah formula dengan nama IF, setelah Anda menuliskan =IF kemudian dilanjutkan dengan menuliskan simbol kurung buka, maka baris kode (syntax) akan ditampilkan. Perhatikan bahwasanya parameter logical_true akan tercetak tebal. Artinya sekarang tugas Anda adalah menyertakan nilai untuk parameter ini. Setelah Anda mengisikan nilai pada parameter logical_true kemudian menuliskan simbol semicolon/titik koma Hand Out Ms. Office Excell 2003 - INOCHI Computer – Agung Novian, S.Kom

10 (;), kali ini giliran parameter [value_if_true] akan dicetak tebal. Parameter value_if_true ditempatkan pada kurung siku, maksud dari kurung siku adalah parameter di antara tanda ini bersifat optional, artinya Anda boleh mengisikan nilai, boleh pula untuk tidak mengisikan nilai pada parameter ini. Akhiri penulisan formula dengan menekan tombol Enter pada keyboard.

Hand Out Ms. Office Excell 2003 - INOCHI Computer – Agung Novian, S.Kom

11

BAB IV Fungsi-Fungsi Excel Latihan 1 Marilah kita menginjak pada penggunaan formula. Untuk latihan awal, kita akan mencoba beberapa operasi dasar seperti penjumlahan (SUM), rata-rata (AVERAGE), menentukan nilai terbesar (MAX), menentukan nilai terkecil (MIN). Fungsi-Fungsi yang digunakan: SUM



Fungsi ini digunakan untuk menjumlahkan sekelompok data (dalam hal ini adalah kumpulan sel-sel yang membentuk range). Dalam Excel, range digambarkan dengan bentuk: , contoh: SelAwal:SelAkhir B4:C4 Rutin penggunaan: =SUM(SelAwal:SelAkhir) AVERAGE



Fungsi ini digunakan untuk menghitung rata-rata dari sekelompok data. Rutin penggunaan: =AVERAGE(SelAwal:SelAkhir) MAX



Fungsi ini digunakan untuk mencari nilai terbesar dari sekelompok data. Rutin penggunaan: =MAX(SelAwal:SelAkhir) MIN



Fungsi ini digunakan untuk mencari nilai terkecil dari sekelompok data. Rutin penggunaan: =MIN(SelAwal:SelAkhir) Langkah Awal Buatlah data-data seperti gambar berikut: 1 2 3 4 5 6 7 8 9 10 11 12 13

A B Daftar Gaji Karyawan Nama Ado Ajat Udeng Bana Aep

Gaji Pokok 700000 700000 700000 500000 500000

C Lembur 250000 100000 100000 150000 0

D Total

Jumlah Rata-Rata Terbesar Terkecil

Hand Out Ms. Office Excell 2003 - INOCHI Computer – Agung Novian, S.Kom

12 Langkah Penyelesaian 1.

Klik sel D4, ketikkan formula untuk penjumlahan, yaitu:

=SUM(

. Atau Anda dapat melakukannya secara

cepat dengan menekan tombol AutoSum. 2.

Klik sel B4, blok sampai sel C4. Hingga fromula sekarang berubah menjadi:

3.

Klik sel D4, pindahkan pointer mouse ke kotak kecil di sisi kanan bawah sel ini, sampai pointer berubah bentuk menjadi tanda plus (+) tipis berwarna hitam. Klik tombol kiri mouse, lakukan dragging sampai sel D8, atau Anda dapat mengganti cara ini dengan melakukan klik ganda. Maka sel D5 sampai sel D8 akan terisi nilai penjumlahan dengan sendirinya.

4.

Klik sel D4, lihatlah formulabar. Di sana tertulis: tertulis:

=SUM(B5:C5)

=SUM(B4:C4)

=SUM(B4:C4)

.

, sekarang klik sel D5, pada formulabar

. Begitupun ketika Anda mengklik sel D6 dan seterusnya, sel yang tertulis dalam

formula =SUM berbeda-beda mengikuti nomor baris. Ini sudah merupakan ketentuan dari Excel yang otomatis mengganti nomor baris dari sel yang berisi formula. 5.

Klik sel B10, ketikkan formula:

6.

Klik sel B4, blok sampai sel B8.

7.

Klik sel B11, ketikkan formula: kiri pada tombol AutoSum. Klik sel B12, blok sampai sel B8.

9.

Klik sel B11, ketikkan formula:

=AVERAGE(

. Atau dengan menekan tombol dropdown (segitiga) di sebelah

=MAX(

. Atau dengan menekan tombol dropdown (segitiga) di sebelah kiri

, kemudian Anda pilih Max.

10. Klik sel B4, blok sampai sel B8. 11. Klik sel B12, ketikkan formula: pada tombol AutoSum.

.

, kemudian Anda pilih Average.

8.

pada tombol AutoSum.

=SUM(

=MIN(

. Atau dengan menekan tombol dropdown (segitiga) di sebelah kiri

, kemudian Anda pilih Min.

12. Klik sel B4, blok sampai sel B8. Tekan tombol Enter pada keyboard. 13. Blok sel A10 sampai A13. Pindahkan pointer mouse ke sudut kanan bawah sel A13, sampai pointer berubah bentuk menjadi tanda plus (+) tipis berwarna hitam. Klik tombol kiri mouse, lakukan dragging sampai sel D13. 14. Blok sel A1 sampai D1. klik tombol menjadi satu sel.

, untuk me-merge sel. Merge digunakan untuk menggabungkan range

15. Klik row heading (kepala baris) ke 3, klik tombol untuk meratatengahkan teks. 16. Blok column heading B, C, dan D. Klik tombol

untuk menebalkan teks, kemudian klik tombol

untuk memformat tampilan angka menjadi Currency.

17. Blok sel A3 sampai D3, tekan dan tahan tombol Ctrl pada keyboard, Klik sel A4, blok sampai D8, Klik sel A10, blok sampai D13. 18. Lepas tombol Ctrl, klik kanan pada sel yang terblok, pilih Format Cells.... Klik tab Border, pada kotak “Style”, klik gambar garis dengan dua baris , bila perlu klik kombo “Color”, pilih warna sesuai keinginan, klik tombol Outline. Klik gambar garis sebaris

, bila perlu klik juga kombo “Color”, pilih warna

sesuai keinginan, klik tombol Inside, klik OK. Klik di sembarang sel yang tidak terblok. 19. Blok sel A3 sampai D3, klik kanan pada sel yang terblok, pilih Format Cells.... Klik tab Patterns. Klik sebuah kotak warna. Klik OK. Klik di sembarang sel yang tidak terblok. Hand Out Ms. Office Excell 2003 - INOCHI Computer – Agung Novian, S.Kom

13 A 1 2 3 4 5 6 7 8 9 10 11 12 13

B

C Daftar Gaji Karyawan

Nama Ado Ajat

Gaji Pokok Rp 700.000 Rp 700.000

Rp Rp

Udeng Bana Aep

Rp Rp Rp

700.000 500.000 500.000

Rp Rp Rp

Jumlah

Rp

2.600.000

Rata-Rata Terbesar Terkecil

Rp Rp Rp

620.000 700.000 500.000

D

Lembur 250.000 100.000

Rp Rp

Total 950.000 800.000

100.000 150.000 -

Rp Rp Rp

800.000 650.000 500.000

Rp

600.000

Rp

3.200.000

Rp Rp Rp

120.000 250.000 -

Rp Rp Rp

740.000 950.000 500.000

Latihan 2 Kita akan mencoba fungsi manipulasi data bertipe string, seperti: LEFT, MID, RIGHT. Fungsi-fungsi ini digunakan untuk mengambil sebagian teks dari suatu teks dengan kriteria tertentu. LEFT



Fungsi ini digunakan untuk mengambil sebagian huruf dari sisi kiri berdasar pada banyaknya huruf yang ditentukan. Rutin pemanggilan: =LEFT(SelSumber;BanyaknyaHuruf) MID



Fungsi ini digunakan untuk mengambil sebagian huruf mulai dari posisi awal yang ditentukan, berdasar pada banyaknya huruf yang ditentukan. Rutin pemanggilan: =MID(SelSumber;PosisiAwal;BanyaknyaHuruf) RIGHT



Fungsi ini digunakan untuk mengambil sebagian huruf mulai dari sisi kanan, berdasar pada banyaknya huruf yang ditentukan. Rutin pemanggilan: =RIGHT(SelSumber;BanyaknyaHuruf) Langkah Awal Buatlah data-data seperti gambar berikut: 1 2

A Andi Offset Agung Novian

1.

Klik sel B1, ketikkan:

2.

Klik sel B2, ketikkan:

3.

Klik sel C1, ketikkan:

4.

Klik sel C2, ketikkan:

5.

Klik sel D1, ketikkan:

B Andi Agu

C Off Novia

=LEFT(A1;4) =LEFT(A2;3)

D set vian

. .

=MID(A1;6;3) =MID(A2;7;4) =RIGHT(A2;3)

. . .

Hand Out Ms. Office Excell 2003 - INOCHI Computer – Agung Novian, S.Kom

14 6.

Klik sel D2, ketikkan:

=RIGHT(A2;4)

.

Hand Out Ms. Office Excell 2003 - INOCHI Computer – Agung Novian, S.Kom

15 Latihan 3 Kali ini kita akan mencoba menyelesaikan sebuah kasus untuk menghitung tarif yang harus dibayarkan seorang penginap di sebuah losmen dengan ketentuan tarif per malam adalah Rp. 25.000. Langkah Awal Buatlah data-data seperti gambar berikut:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

A B LOSMEN INOCHI Jl. P. Drajat Cirebon

C

D

Tanggal Datang 17/01/2006 11/01/2006 12/01/2006 15/01/2006 03/01/2006 02/01/2006 12/01/2006 02/01/2006

Pergi 19/01/2006 12/01/2006 16/01/2006 16/01/2006 07/01/2006 08/01/2006 13/01/2006 04/01/2006

E

F

Tarif\malam : 25000 No 1 2 3 4 5 6 7 8

Nama Tamu Aidit Untung Ratu Adil Westerling Daud Baureuh Kahar Muzakar Mario Caras Andi Azis

Lama Inap

Total Bayar

Langkah Penyelesaian 1.

Klik sel E9, ketikkan formula:

2.

Pindahkan pointer mouse ke kotak kecil di sisi kanan bawah sel E9, sampai pointer berubah bentuk menjadi tanda plus (+) tipis berwarna hitam. Klik tombol kiri mouse, lakukan dragging sampai sel E16, atau Anda dapat mengganti cara ini dengan melakukan klik ganda. Untuk diingat, pada latihan selanjutnya, istilah ini disebut dengan “menggandakan”.

3.

Klik kanan column heading F, pilih Format Cell... Klik tab Number, klik list Custom pada kotak “Category:”. Pada kotak “Type:”, ketikkan: . Klik OK untuk menutup dialog. # "hari"

4.

Klik sel F9, ketikkan formula

5.

Gandakan sampai sel F16.

6.

Pada sel F11, F13, F14, F15, dan F16, tertulis:

=D9-C9

=B5*E9

.

.

#VALUE!

tertulis pada sel-sel ini. Sebagai contoh pada sel F11, tertulis:

. Kenapa hal ini terjadi. Lihatlah formula yang =B7*E11

. Periksalah nilai sel B7, adalah

teks: “Nama Tamu”, dan periksa sel E11, adalah nilai 4. Mungkinkah teks dapat dikalikan dengan suatu nilai (angka)? Seperti telah dijelaskan pada pembahasan sebelumnya, Excel otomatis mengganti nomor baris sel, saat melakukan penggandaan formula. Bagaimana mengatasinya? Salah satu caranya adalah mengganti setiap formula yang mengandung nama kolom B (seperti: B6, B7, B8, dan seterusnya) dengan B5. Stop! Jangan melakukan cara di atas. Untuk contoh latihan ini bisa saja anda mengganti setiap formula, karena data yang terkandung hanya sedikit, hanya 8 (delapan) data saja. Bagaimana jika data yang ada jumlahnya ratusan atau ribuan? Bersedia Anda mengantinya? Cara yang praktis dan benar adalah dengan mengabsolutkan (menetapkan) sel B5. Mengabsolutkan sebuah sel akan menghentikan otomatisasi penggantian nomor baris pada Excel. 7.

Blok sel F9 sampai F16. tekan tombol Delete pada keyboard untuk menghapus isi dari sel. Klik sel F9. Ketikkan: , klik sel B5. Tekan tombol F4 pada keyboard sehingga sekarang pada formulabar tertulis: = =$B$5

8.

. Lengkapi penulisan, hingga formula menjadi:

=$B$5*E9

.

Gandakan sampai sel F16.

Hand Out Ms. Office Excell 2003 - INOCHI Computer – Agung Novian, S.Kom

16 9.

Klik column heading F, klik tombol

.

10. Blok row heading 7 dan 8, klik tombol , pada daerah yang terblok klik kanan dan pilih Format Cells... Klik tab Alignment, pilih Center pada kombo “Horizontal”, pilih Center pula pada kombo “Vertical”, tandai () kotak periksa Wrap text. Klik OK. 11. Blok sel A1 sampai F1, tekan dan tahan tombol Ctrl, blok sel B2 sampai F2, A7 sampai A8, B7 sampai B8, C7 sampai D7, E7 sampai E8, dan F7 sampai F8. Lepas tombol Ctrl, klik tombol

.

12. Blok sel A7 sampai F7, tekan dan tahan tombol Ctrl, blok sel A9 sampai F9. Lepas tombol Ctrl, lakukan pembingkaian seperti pada langkah nomor 18 pada Latihan 1. 13. Blok column heading C dan D, tampilkan dialog Format Cells, pilih tab Number, klik list Custom pada kotak “Category:”. Pada kotak “Type:”, ketikkan: . Klik OK untuk menutup dialog. dd-mmm-yy A 1 2 3 4 5 6 7 8 9 1 0 11 1 2 1 3 1 4 1 5 1 6

B

C D LOSMEN INOCHI Jl. P. Drajat Cirebon

E

F

Tanggal

Lama Inap

Total Bayar

Tarif\malam : 25000 No

Nama Tamu

Datang

Pergi

1

Aidit

17-Jan-06

19-Jan-06

2 hari

50.000

2 3

Untung Ratu Adil

11-Jan-06 12-Jan-06

12-Jan-06 16-Jan-06

1 hari 4 hari

25.000 100.000

4

Westerling

15-Jan-06

16-Jan-06

1 hari

25.000

5

Daud Baureuh

03-Jan-06

07-Jan-06

4 hari

100.000

6

Kahar Muzakar

02-Jan-06

08-Jan-06

6 hari

150.000

7

Mario Caras

12-Jan-06

13-Jan-06

1 hari

25.000

8

Andi Azis

02-Jan-06

04-Jan-06

2 hari

50.000

Latihan 4 Kita akan mencoba fungsi manipulasi data bertipe Date dan Time, seperti: NOW, HOUR, MINUTE, SECOND, DAY, MONTH, YEAR, WEEKDAY. Fungsi-fungsi ini digunakan untuk mengambil nilai data tanggal (Date) atau waktu (Time) tertentu dari suatu data. Langkah Awal Buatlah data-data seperti gambar berikut: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

A B PT INOCHI Jl. P. Drajat Ciebon

C

D

E

F

G

Waktu Jam

Menit

H

Data Absensi Karyawan Tanggal

Hari ke-

Bulan Tahun No

Nama

Jam Masuk

Jam Pulang

1 2

Ado Kopral Udeng Ajat Jack Abe Bana Oso

6:55:54 6:59:02 6:57:43 6:59:00 6:55:59 6:54:34 7:55:12 7:00:00

15:00:09 15:03:55 15:02:45 15:03:02 15:10:55 15:15:15 15:00:01 15:01:02

Lama

Detik

Langkah Penyelesaian 1.

Blok sel A12 sampai A13, gandakan sampai sel A19.

Hand Out Ms. Office Excell 2003 - INOCHI Computer – Agung Novian, S.Kom

17 2.

Klik sel E12, ketikkan formula:

=D12-C12)

.

Pengertian: Menghitung selisih sel D12 dengan sel C12 3.

Gandakan sampai sel E19.

4.

Klik sel F12, ketikkan formula:

=HOUR(E12)

.

Pengertian: Menentukan yang merupakan bagian jam dari sel E12 5.

Gandakan sampai sel F19.

6.

Klik sel G12, ketikkan formula:

.

=MINUTE(E13)

Pengertian: Menentukan yang merupakan bagian menit dari sel E12 7.

Gandakan sampai sel G19.

8.

Klik sel H12, ketikkan formula:

. Gandakan sampai sel H19.

=SECOND(E13)

Pengertian: Menentukan yang merupakan bagian detik dari sel E12 9.

Klik sel C6, ketikkan formula:

=DAY(NOW())

.

Pengertian: Menentukan tanggal sekarang dari sistem komputer. 10. Klik sel C7, ketikkan formula:

.

=MONTH(NOW())

Pengertian: Menentukan bulan sekarang dari sistem komputer. 11. Klik sel C8, ketikkan formula:

=YEAR(NOW())

.

Pengertian: Menentukan tahun sekarang dari sistem komputer. 12. Klik sel E8, ketikkan formula:

.

=WEEKDAY(NOW())

Pengertian: Menentukan hari ke... dari minggu sekarang dari sistem komputer. 13. Blok sel A1 sampai H1, A2 sampai H2, A4 sampai H4, A10 sampai A11, B10 sampai B11, C10 sampai C11, D10 sampai D11, E10 sampai E11, F10 sampai H10, merge-kan range-range tersebut. 14. Blok row heading 1, 4, 10 dan 11, tebalkan dan rata tengahkan. 15. Blok column heading C sampai H, rata tengahkan. 16. Blok sel A10 sampai H10, dan sel A12 sampai H12, berikan bingkai (border). 17. Blok sel C6 sampai C8, dan sel A6, rata kirikan. A 1 2 3 4 5

B

C

D E PT INOCHI Jl. P. Drajat Ciebon

F

G

H

Data Absensi Karyawan

Hand Out Ms. Office Excell 2003 - INOCHI Computer – Agung Novian, S.Kom

18 6 7 8 9 10 11 12 13 14 15 16 17 18 19

Tanggal Bulan Tahun No 1 2 3 4 5 6 7 8

Nama Ado Kopral Udeng Ajat Jack Abe Bana Oso

28 4 2006

Hari ke-

Jam Masuk 6:55:54 6:59:02 6:57:43 6:59:00 6:55:59 6:54:34 7:55:12 7:00:00

6

Jam Pulang 15:00:09 15:03:55 15:02:45 15:03:02 15:10:55 15:15:15 15:00:01 15:01:02

Lama 8:04:15 8:04:53 8:05:02 8:04:02 8:14:56 8:20:41 7:04:49 8:01:02

Jam 8 8 8 8 8 8 7 8

Waktu Menit 4 4 5 4 14 20 4 1

Detik 15 53 2 2 56 41 49 2

Fungsi IF Fungsi ini digunakan untuk menguji suatu kondisi atau logika, yang akan menentukan nilai kembali ke-1 jika kondisi tersebut bernilai benar dan menentukan nilai kembali ke-2 jika kondisi tersebut salah. Rutin pemanggilan: =IF(Kondisi;[Nilai_Jika_Benar];[Nilai_Jika_Salah] Keterangan: Kondisi Nilai_Jika_Benar Nilai_Jika_Salah

Suatu ungkapan logika yang berupa perbandingan. Nilai yang dihasilkan jika pengujian kondisi bernilai benar. Nilai yang dihasilkan jika pengujian kondisi bernilai salah.

Contoh 1: Pada sel B1 tertulis formula: =IF(A1="On";"Menyala";"Padam") If akan menguji isi sel dari A1, jika isi sel pada A1 adalah: “On”, maka pada sel B1 akan terisi teks: “Menyala”. Tetapi jika tidak (isi sel pada A1 bukan teks “On”, misalnya: “Off”, “Mati”, atau tidak berisi teks), maka pada sel B1 akan terisi teks: “Padam”. Kita dapat menggambarkan logika di atas dengan flowchart sebagai berikut: Salah A1 = “On”

B1 = “Padam” Benar

B1 = “Menyala” Contoh 2: Ketikkan formula berikut ini pada sel B1: =IF(A1="On";"Menyala";IF(A1="Off";"Padam";"")) If akan menguji isi sel dari A1, jika isi sel pada A1 adalah “On”, maka pada sel B1 akan terisi teks “Menyala”. Tetapi jika tidak (isi sel pada A1 bukan teks “On”), maka If akan melakukan pengujian ke dua dari isi sel A1. Jika isi sel pada B1 adalah “Off”, maka pada sel B1 akan terisi teks “Padam”. Jika tidak (isi sel pada A1 bukan teks “On”, bukan pula teks “Off”, maka kosongkan ("") isi dari sel B1). Dengan flowchart:

Hand Out Ms. Office Excell 2003 - INOCHI Computer – Agung Novian, S.Kom

19 Salah

Salah

A1 = “On”

A1 = “Of” Benar

B1 = kosong Benar

B1 = “Menyala”

B1 = “Padam”

Contoh 3: Pada sel C1 tertulis formula: =IF(A1="On";IF(B1="Hijau";"Jalan";IF(B1="Merah";"Berhenti";"Kebut"));"Rusak") If akan menguji isi sel dari A1, jika isi sel pada A1 adalah “On”, maka periksa isi sel B1. Jika isi sel B1 adalah “Hijau”, maka isi sel C1 dengan teks “Jalan”. Jika tidak (jika isi sel B1 bukan “Hijau”), maka periksa kembali isi sel B1. Jika isi sel B1 adalah “Merah”, maka isi sel C1 dengan teks “Berhenti”. Jika tidak (jika isi sel B1 bukan “Merah”) ”, maka isi sel C1 dengan teks “Kebut”. Pengujian awal di atas adalah pengujian untuk jika jika isi sel pada A1 adalah “On”, tentu saja Anda harus menentukan pengujian jika kondisi (isi dari sel A1 bukan “On”). Pada contoh di atas, apapun isi dari sel A1 (selain “On”) maka isi sel C1 dengan teks “Rusak”. Dengan flowchart: Salah A1 =

C1 = “Rusak” Bena Bena

B1 = “Hija

C1 = “Jalan”

Salah Bena B1 = “Mer

C1 =

Salah C1 = “Kebut” Contoh 4:

Salah

Salah

C1 = “Dijarah A1 = A1 = Oknum” “On” “Off =IF(A1="On";IF(B1="Hijau";"Jalan";IF(B1="Merah";"Berhenti";"Kebut")); IF(A1="Off";"Rusak";"Dijarah BenarOknum")) Benar

Pada sel C1 tertulis formula:

Pengujian awal di atas adalah pengujian untuk jika jika isi pada A1 adalah “On”, sama seperti pada contoh 3. C1sel = “Rusak” Pengujian terakhir adal pengujian untuk jika jika isi sel pada A1 bukan “On”. Jika isi dari sel A1 adalah “Off”, maka isi sel C1 dengan teks “Rusak”. Jika tidak (jika isi sel A1 bukan “Off”, misalnya: “Hilang”, atau kosong), maka isi sel C1 dengan teks “Dijarah Oknum”. Benar B1 = C1 = “Jalan” Dengan flowchart: “Hijau Salah Benar B1 = “Merah

C1 = “Berhenti”

Hand Out Salah Ms. Office Excell 2003 - INOCHI Computer – Agung Novian, S.Kom C1 = “Kebut”

20

Latihan 3 Latihan berikut ini mencoba menggunakan logika IF untuk menentukan Jenis Barang, Nama Barang, dan Harga Barang, berdasarkan Kode Barang yang telah ditentukan. Di sini kita juga menggunakan beberapa fungsi String. Langkah Awal Buatlah data sebagai berikut: A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

B

C

D

Jenis Barang

Nama Barang

Harga Barang

TOKO INOCHI Jl. P. Drajat Cirebon Kode Barang ABL2 AGL1 SSC2 SKG8 KMN1 KSU2 Keterangan Kode: A = Air Mineral

BL = Air Mineral Botol

1 = 500

S = Susu

GL = Air Mineral Gelas

2 = 1000

K = Kopi

SC = Susu Sachet

8 = 4000

SK = Susu Kaleng MN = Kopi Murni SU = Kopi Susu

Langkah Penyelesaian 1.

Isi sel B5 dengan formula: =IF(LEFT(A5;1)="A";"Air Mineral";"")

Pengertian: Jika 1 huruf pertama dari sel A5 adalah “A”, maka isi sel B5, dengan teks “Air Mineral”, jika tidak maka kosongkan sel B5. 2.

Gandakan sampai sel B10. Apa yang Anda lihat? Sel B7 sampai B10 berisi nilai FALSE. Kenapa? Ya, karena Anda baru melakukan satu pengujian untuk huruf “A”, saja.

3.

Klik kembali sel B5, lanjutkan penulisan formula dengan diawali menekan tombol F2 pada keyboard, hapus sebagian formula (sisi kanan), sampai batas simbil semicolon (;) lengkapi hingga formula menjadi: =IF(LEFT(A5;1)="A";"Air Mineral"; IF(LEFT(A5;1)="S";"Susu"

Hand Out Ms. Office Excell 2003 - INOCHI Computer – Agung Novian, S.Kom

21 Saat Anda menekan tombol Enter untuk mengakhiri pengetikan formula, Anda akan mendapatkan pesan seperti gambar ilustrasi di bawah ini:

Tekan saja tombol Enter sekali lagi, atau klik tombol Yes. Kotak pesan tersebut adalah pesan yang memberitahu kepada Anda bahwa Anda melakukan kesalahan penulisan formula. Tetapi perlu diingat, kesalahan yang Anda lakukan bukanlah kesalahan struktural dari penggunaan fungsi If, ini hanyalah kesalahan karena Anda lupa atau malas untuk mengetikkan tanda kurung tutup (“)”) untuk mengakhiri penggunaan fungsi IF. Lain halnya jika Anda mendapatkan kotak pesan berikut ini:

Ini adalah kesalahan yang cukup fatal, kemungkinan karena Anda lupa mengisikan parameter yang diperlukan atau kesalahan menuliskan simbol. 4.

Gandakan kembali sampai sel B5. Sekarang tinggal B9 dan B10 saja yang berisi nilai FALSE.

5.

Terakhir lengkapi formula hingga:

=IF(LEFT(A5;1)="A";"Air Mineral";IF(LEFT(A5;1)="S";"Susu";"Kopi")) Atau: =IF(LEFT(A5;1)="A";"Air Mineral";IF(LEFT(A5;1)="S";"Susu";IF(LEFT(A5;1)="K"; "Kopi";"Tidak terdata"))) 6.

Gandakan kembali sampai sel B10. Ya, itulah cara efektif untuk melakukan pengujian. Jangan langsung menguji secara lengkap, bertahaplah.

7.

Klik sel C5, ketikkan formula:

=IF(MID(A5;2;2)="BL";CONCATENATE(B5;" ";"Botol");"") Pengertian: Jika mulai huruf ke dua dari sel A5 sebanyak dua huruf adalah “BL”, maka isi sel C5, dengan menggabungkan (Concenate) isi dari sel B5, spasi (“ “) dan “Botol”. Jika tidak, maka kosongkan sel C5. 8.

Gandakan sampai sel C10.

9.

Lakukanlah cara pengujian seperti contoh langkah pengujian untuk sel B5 di atas, hingga pada formula C5 berisi formula:

=IF(MID(A5;2;2)="BL";CONCATENATE(B5;" ";"Botol");IF(MID(A5;2;2)="GL";CONCATENATE(B5;" ";"Gelas"); IF(MID(A5;2;2)="SC";CONCATENATE(B5;" ";"Sachet");IF(MID(A5;2;2)="KL"; CONCATENATE(B5;" ";"Kaleng");IF(MID(A5;2;2)="MN";CONCATENATE(B5;" ";"Murni");IF(MID(A5;2;2)="SU";CONCATENATE(B5;" ";"Susu");"Tidak dikenal")))))) 10. Gandakan sampai sel C10. 11. Klik sel D5, ketikkan formula berikut: Hand Out Ms. Office Excell 2003 - INOCHI Computer – Agung Novian, S.Kom

22 =IF(RIGHT(A5;1)="1";500;0) Pengertian: Jika 1 huruf dari sisi kanan A5 adalah teks 1 (satu), maka isi sel D5, dengan nilai angka 1500. Jika tidak, maka isi sel D5 dengan nilai angka 0 (nol). 12. Gandakan hingga sel D10. 13. Lakukan cara pengujian bertahap, hingga akhir formula untuk sel D5 adalah, sebagi berikut: =IF(RIGHT(B6;1)="1";500;IF(RIGHT(B6;1)="2";1000;IF(RIGHT(B6;1)="8";4000;0))) 14. Gandakan hingga sel D10. 15. Bingkailah hingga tampak menarik. A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

B

C

D

TOKO INOCHI Jl. P. Drajat Cirebon Kode Barang ABL2 AGL1 SSC2 SKL8 KMN1 KSU2

Jenis Barang

Nama Barang

Air Mineral Air Mineral Susu Susu Kopi Kopi

Harga Barang

Air Mineral Botol Air Mineral Gelas Susu Sachet Susu Kaleng Kopi Murni Kopi Susu

1.000 500 1.000 4.000 500 1.000

Keterangan Kode: A = Air Mineral S = Susu K = Kopi

BL = Air Mineral Botol GL = Air Mineral Gelas SC = Susu Sachet SK = Susu Kaleng MN = Kopi Murni SU = Kopi Susu

1 = 500 2 = 1000 8 = 4000

Fungsi [V|H]LookUp [V|H]LookUp adalah fungsi untuk mencari suatu nilai di dalam baris suatu tabel atau suatu nilai larik, dan kemudian mengembalikan ke dalam sel yang telah Anda tentukan. HLookUp digunakan manakala penyusunan data (dalam tabel) diatur secara horizontal/mendatar. Sedangkan VLookUp digunakan jika penyusunan data (dalam tabel) diatur secara vertikal/menurun. Tabel yang merupakan sumber data untuk [V|H]LookUp, adalah berupa range (kumpulan sel-sel) yang boleh Anda definisikan maupun tidak. Pada pembahasan kali ini saya hanya akan menjelaskan penggunaan range yang didefiniskan. A 1 2 No.

4 5 6 7 8 9 10 11 12 13

1 2 3 4 5 6 7 8

15 16 17 18 19

C

D

E

F

G

Daftar Gaji Karyawan PT. INOCHI

3

14

B

Kode Bagian

Nama Agung Novian Astrid Indriyati David Ricardo Dina Oktavia Iftar Ruchman Moh. Sanaji Gustaf L. J. Slamet P.

Nama Bagian

Gaji Pokok

A B C C D E D E Tabel Bagian: Kode Bagian A B C D E

Nama Bagian Personalia Administrasi Keuangan Penjualan Satpam

Hand Out Ms. Office Excell 2003 - INOCHI Computer – Agung Novian, S.Kom

23 20 21 22 23

Tabel Gaji: Kode Bagian Gaji Pokok

A 350000

B 300000

C 300000

D 250000

E 275000

Langkah Penyelesaian 1.

Blok sel C15 sampai D19, pada Name Box, , ketikkan: BAGIAN. Atau Anda dapat mengganti cara ini dengan klik menu: InsertNameDefine... Ketikkan: BAGIAN pada kotak “Names in workbook:”. Klik OK.

2.

Blok sel C22 sampai G23, lakukan cara seperti langkah nomor 1, kali ini namai dengan GAJI.

3.

Klik sel D4, ketikkan formula berikut: =VLOOKUP(C4;BAGIAN;2) Pengertian: Mencari data dengan kriteria dari isi sel C4, mencari pada tabel dengan nama BAGIAN, dan kolom yang dijadikan sumber (untuk mengembalikan nilai ke sel D4) adalah kolom ke 2 (dari tabel BAGIAN).

4.

Gandakan sampai sel D11.

5.

Klik sel E4, ketikkan formula berikut: =HLOOKUP(C4;GAJI;2) Pengertian: Mencari data dengan kriteria dari isi sel C4, mencari pada tabel dengan nama GAJI, dan baris yang dijadikan sumber (untuk mengembalikan nilai ke sel E4) adalah baris ke 2 (dari tabel BAGIAN).

6.

Gandakan sampai sel E11.

Hand Out Ms. Office Excell 2003 - INOCHI Computer – Agung Novian, S.Kom