MODUL V MS EXCEL 2007 (ADVANCED)
A. TUJUAN Mahasiswa dapat mengoperasikan secara lebih lanjut penggunaan berbagai fungsi (formula) dalam Microsoft Excel 2007.
B. ALAT DAN BAHAN 1. Personal Computer 2. OS Windows XP 3. Microsoft Office 2007
C. LANDASAN TEORI Fungsi dalam hal ini adalah rumus yang sudah disediakan oleh Microsoft Excel, yang akan membantu dalam proses perhitungan. Umumnya penulisan fungsi harus dilengkapi dengan argument, baik berupa angka, label, rumus, alamat sel, atau range. Argument ini harus ditulis dengan diapit tanda kurung (). Beberapa fungsi yang sering digunakan adalah : 1. Fungsi Average(…) Fungsi ini digunakan untuk mencari nilai rata-rata dari sekumpulan data (range).
Bentuk
umum
=AVERAGE(number1,number2,…)
penulisannya dimana
number1,
adalah number2,
dan
seterusnya adalah range data yang akan dicari nilai rata-ratanya. 2. Fungsi Max(…) Fungsi ini digunakan untuk mencari nilai tertinggi dari sekumpulan data (range). Bentuk umum penulisannya adalah =MAX(number1,number2,...) dimana number1, number2, dan seterusnya adalah range data (numeric) yang akan dicari nilai tertingginya. 3. Fungsi Min(…) Sama halnya dengan fungsi Max, bedanya fungsi min digunakan untuk mencari nilai terendah dari sekumpulan data numerik.
4. Fungsi Sum(…) Fungsi Sum digunakan untuk menjumlahkan sekumpulan data pada suatu range.
Bentuk
umum
penulisan
fungsi
ini
adalah
=SUM(number1,number2,…) dimana number1, number2, dan seterusnya adalah range data yang akan dijumlahkan. 5. Fungsi Left(…) Fungsi left digunakan untuk mengambil karakter pada bagian sebelah kiri dari suatu teks. Bentuk umum penulisannya adalah =LEFT(text,num_chars). Dimana text adalah data yang akan diambil sebagian karakternya dari sebelah kiri, num_chars adalah jumlah karakter yang akan diambil. 6. Fungsi Mid(…) Fungsi Mid digunakan untuk mengambil sebagian karakter bagian tengah dari suatu
teks.
Bentuk
umum
pemakaian
fungsi
ini
adalah
=MID(text,star_num,num_chars) artinya mengambil sejumlah karakter mulai dari start_num, sebanyak num_char. 7. Fungsi Right(…) Fungsi right merupakan kebalikan dari fungsi left, kalau fungsi left mengambil sejumlah karakter dari sebelah kiri, maka fungsi mengambil sejumlah karakter dari sebelah kanan teks. Bentuk umu penulisannya adalah =RIGHT(text,num_chars) dimana text adalah data yang akan diambil sebagian karakternya dari sebelah kanan, num_chars adalah jumlah karakter yang akan diambil. 8. Fungsi Logika Fungsi IF digunakan untuk memberikan nilai berdasarkan ekspresi logika. Apakah suatu pernyataan bernilai BENAR (True) atau bernilai SALAH (False). Cara penulisannya adalah : =IF(Ekspresi_logika,Jawaban_Benar,Jawaban_salah) Contoh dalam soal :
a. Jika kode L maka Kelamin “Laki-laki”, kode P maka “Perempuan” cel C2 ditulis : =IF(B2=”L”,”Laki-laki”,”Perempuan”) b. Jika jam kerja lebih dari 80 maka Keterangan “Lembur”, jika jam kerja kurang dari 80 maka Keterangan “Kurang”, dan jika jam kerja sama dengan 80 maka Keterangan “Cukup” cel E2 ditulis : =IF(D2>80,”Lembur”, if(D2=80,”Cukup”,”Kurang”)) Maka hasil akhirnya adalah
9. Fungsi Logika And dan Or Bentuk umumnya adalah sebagai berikut : =IF(AND(kondisi1,kondisi2), statemen1,statemen2) •
artinya statemen1 hanya akan dikerjakan apabila kondisi1 dan kondisi2 terpenuhi.
=IF(OR(kondisi1,kondisi2),statemen1,statemen2) •
artinya statemen1 akan dikerjakan jika salah satu atau semua kondisi terpenuhi.
10. Fungsi HLOOKUP dan VLOOKUP HLOOKUP, digunakan untuk membaca suatu data di dalam table yang tersusun secara horizontal. Cara penulisannya adalah : =HLOOKUP(Data_Kunci,Posisi_Tabel,Nomor_Baris,0) Baris 1 Baris 2
VLOOKUP, digunakan untuk membaca suatu data di dalam table yang tersusun secara vertical. Cara penulisannya adalah : =VLOOKUP(Data_Kunci,Posisi_Tabel,Nomor_Kolom,0)
Kolom 1
Kolom 2
Sebagai contohnya table berikut :
Kolom 3
• Sel D4 dengan fungsi =VLOOKUP(RIGHT(C5,3),$A$12:$C$18,3). Dengan =VLOOKUP karena tabel kunci tersusun vertical RIGHT(C5,3) data kunci yang diambil pada sel C4, 3 huruf dari kanan $A$12:$G$18 posisi tabel kunci, yaitu dari A14 sampai G16 3
Nomor Kolom yaitu Kolom kedua
• Hasil perhitungan pada tabel setelah dimasukkan fungsi di atas
BEKERJA DENGAN GRAFIK Dalam excel dikenal dengan nama chart, merupakan sebuah informasi/data yang ditampilkan dalam bentuk gambar. Sehingga dengan gambar kita akan lebih memahami dan membaca data yang disajikan tersebut. Terlihat menu Insert pada Ms Excel 2007 seperti gambar berikut : Jika ingin membuat garfik, pertama-tama harus mengetikan tabel data pada lembar kerja. Kemudian memilih range tabel data yang akan dibuat grafiknya.
Pada menu insert, kita dapat memilih chart yang sesuai dengan pilihan kita
Misalkan Chart jenis Column dipilih, dan pengaturan chart tersebut terdapat dalam chart tools. Chart tools akan muncul apabila kita mengaktifkan sebuah grafik. Bentuk grafik dari tabel diatas adalah sebagai berikut :
D. LANGKAH-LANGKAH PRAKTIKUM 1. Buka Program Ms Excel 2007 2. Simpanlah Lembar Kerja anda dengan nama file “NIM_modul ke-“. Contoh : L100100100_modul5.xls 3. Ganti nama work sheet sesuai dengan perintah. Berikut adalah contoh sheet yang telah dirubah sesuai kebutuhan.
4. Pengerjaan tugas disesuaikan dengan nama sheet anda.
E. TUGAS PRAKTIKUM
TUGAS 1 Kerjakan percobaan berikut pada sheet Percobaan1, dan simpan.
Keterangan : a. Data Nama Instruktur, Nama Paket, dan Target Jam diambil dari Tabel Kunci 1 dan Tabel Kunci 2 menggunakan fungsi VLOOKUP. b. Sisa Jam = Target Jam – Realisasi. c. Keterangan dengan ketentuan, jika Sisa Jam = 0 maka “Selesai”, selain itu “Tidak Selesai”.
d. Untuk penulisan jam, gunakan fasilitas Format Cells tepatnya pada tab Number.
TUGAS 2 Buatlah seperti table berikut pada sheet selanjutnya, beri nama sheet tersebut dengan nama “Percobaan2”.
Kemudian buatlah dua buah tabel kunci seperti berikut ini pada sheet berbeda, beri nama sheet tersebut dengan nama Tabel Kunci. Tabel kunci 1. NIK
JABATAN
GAJI POKOK
80M
MANAGER
2500000
80P
ADMINISTRASI PERSONALIA
1500000
85A
KEPALA ADMINISTRASI
1600000
85P
KEPALA PERSONALIA
1600000
85K
KEPALA KEUANGAN
2000000
88S
SALES SUPERVISOR
900000
90C
SALES PROMOTION
700000
Tabel kunci 2 NIK
M80
S85
M85
M90
S88
S90
tahun 1980
tahun 1985
tahun 1985
tahun 1990
tahun 1988
tahun 1990
TAHUN MASUK
1. Ketentuan proses a. Kolom Status ditentukan dari karakter
pertama NIK, jika M maka
”MENIKAH”, jika S maka ”LAJANG”. b. Kolom Jabatan dan kolom Gaji Pokok diambil dari Tabel Kunci 1 (penggunaan fungsi VLOOKUP). c. Kolom Tunjangan Anak Ditentukan dari banyaknya anak. Jika Jumlah Anak lebih dari 2 maka Tunjangan Anak = 2*15%*gaji pokok, selain itu Tunjangan Anak = Jumlah Anak*15%*gaji pokok. d. Kolom Tunjangan Istri ditentukan Dari kolom Status. Jika status ”MENIKAH” maka Tunjangan Istri = 20%*gaji pokok, selain itu tidak mendapat tunjangan istri (0). e. Kolom Transport didapat dari 15%*gaji pokok f. Kolom Total Gaji didapat dari penjumlahan Gaji Pokok, Tunjangan Anak, Tunjangan Istri, dan Transport. g. Kolom Tahun Masuk diambil dari Tabel Kunci 2 (penggunaan fungsi HLOOKUP. h. Kolom Total Gaji karyawan merupakan total dari masing-masing kolom, seperti Gaji Pokok, Tunjangan Anak, Tunjangan Istri, dan Total Gaji.
2. Membuat grafik dengan ketentuan: a. BAR seperti pada soal. b. PIE berdasar kolom total gaji. c. Buat nama sheet ini dengan nama GRAFIK.
3. Jangan lupa sesuaikan FORMAT cell atau kolomnya hingga tabel terlihat rapi.
