1
Modul Training Microsoft Excel (Advance)
2
Modul Training Microsoft Excel (Advance) Spread Sheet: Microsoft Excel Pada bab ini akan diperkenalkan mengenai aplikasi spreadsheet Microsoft Excel. Pengolah kata ini biasa disebut MS-Excel dibuat oleh Microsoft Corp., dan untuk menggunakannya harus membeli lisensi kepada pihak Microsoft. Mulai dipakai sejak dikenalkannya Windows 3.1. sampai sekarang.
Pertama kali diperkenalkan, dalam satu paket Microsoft Office, menggunakan versi angka namun sejak munculnya Windows 95, versi MS-Excel berubah menggunakan angka tahun (97, 98, 2000, XP (2002), 2003). Kelengkapan (fitur) yang dimuat terjadi perubahan yang cukup signifikan terutama perpindahan dari MS-Excel 2000 ke MSExcel XP. Sedangkan pada versi 2003 tidak terlalu banyak berubah seperti ada versi XP.
Tampilan MS-Excel MS-Excel mempunyai tampilan standar seperti ditunjukkan pada Gambar 1, dengan elemen-elemennya antara lain:
Gambar 1 Tampilan standar MS-Excel XP
3
1. Column, merupakan kolom pada sebuah spreadsheet, dengan representasi huruf, dimulai dari A sampai dengan IV 2. Row, merupakan baris pada sebuah spreadsheet, dengan representasi angka dimulai dari 1 sampai dengan 65536 3. Cell, merupakan bagian terkecil, sebagai tempat penulisan baik teks, angka,maupun symbol. 4. Name Box, merupakan kotak yang menujukkan letak cell yang sedang aktif 5. Formula Bar, merupakan kotak yang menggambarkan isi cell aktif, bisa isi sebenarnya maupun berupa formula (operasi dan fungsi). 6. Sheet, bisa diartikan sama dengan page pada dokumen, namun secara default MSWord akan menampilkan 3 (tiga) sheet.
Membuat dan Menyimpan Work-book Cara melakukan penyimpanan file pada MS-Excel menggunakan cara yang sama pada MS-Word. Langkah-langkah untuk menyimpan dan memberi nama file:
1. Klik menu File, pilih Save As. Jika mouse tidak berfungsi gunakan Alt + F + A, atau jika dokumen belum pernah disimpan sebelumnya gunakan saja Ctrl + S. Sehingga muncul tampilan seperti Gambar 2.
Gambar 2 Kotak dialog penyimpanan
2. Tentukan tempat file disimpan, pilih Save in (Alt + i) dan tentukan di mana file
4
dokumen akan disimpan. 3. Tentukan nama file dengan memilih File name (Alt + n), dan isilah text field di sebelah kanannya dengan nama file. 4. Tentukan tipe file yang disimpan, dengan mengisi text field di sebelah kanan Save as type (Alt + t), untuk MS-Excel menggunakan type Microsoft Excel Workbook (*.xls). Untuk menemukan alternative tipe penyimpanan klik panah atau gunakan panah ke bawah pada keyboard. 5. Jika langkah 1-4 sudah selesai dilakukan, klik Save (Alt + S) atau tekan Enter pada keyboard. 6. Jika Workbook telah diisi, lakukan penyimpanan secara berkala klik File > Save (Ctrl + S atau Alt + F + S). Sedangkan untuk menyimpan dengan nama lain gunakan langkah-langkah seperti saat pemberian nama.
Menutup Work-book dan Aplikasi Workbook yang sudah selesai dikerjakan, dan akan ditutup bisa ditutup dengan dua cara: 1. Menggunakan menu File > Close (Alt + F + C). 2. Klik tanda silang pojok kanan atas bagian bawah. Sedangkan untuk menutup aplikasi MS-Word bisa digunakan beberapa cara antara lain dengan menu File > Exit (Alt + F + X) atau Klik tanda silang pojok kanan atas atau dengan Alt + F4.
Bentuk-bentuk Cursor Berbeda dengan MS-Word, MS-Excel mempunyai keunikan karena lembar kerjanya berupa tabel. Selain itu bentuk cursor merepresentasikan kerja yang berbeda-beda. Pada Gambar 3 disajikan tiga bentuk cursor yang berbeda.
5
Gambar 3 Bentuk-bentuk cursor
Bentuk pertama, muncul jika mouse diarahkan ke toolbar atau menu bar. Bentuk kedua,muncul jika mouse diarahkan pada sembarang cell di workbook, Bentuk ketiga muncul jika mouse diarahkan ke pojok kanan bawah cell aktif. Bentuk kedua dan ketiga nanti akan sering digunakan pada operasi manipulasi cell.
Memindahkan Penunjuk Sel (Cell Pointer) Ada beberapa cara untuk memindahkan cell pointer. Untuk lengkapnya silahkan lihat table dibawah.
Tabel 1 Beberapa Cara Memindahkan Cell Pointer
Memasukkan Data ke Lembar Kerja Berbagai jenis data dapat dimasukkan ke dalam lembar kerja seperti teks, nilai, tanggal, jam dan lain sebagainya. Untuk memasukkan data ke dalam suatu sel, dapat mengikuti langkah berikut ini : 1. Pilih atau klik sel tempat anda akan memasukkan data 2. Ketikkan data yang ingin dimasukkan
6
3. Tekan Enter atau tombol arah panah atau tombol PgUp dan PgDn
Memperbaiki Kesalahan Pengetikan Bila ada kesalahan pengetikan data, anda dapat memperbaikinya dengan mengikuti langkah-langkah berikut ini : 1. Pilih sel yang datanya ingin diperbaiki, lalu tekan F2. Atau klik tombol kiri maouse 2 kali pada sel yang datanya ingin diperbaiki. 2. Selanjutnya perbaiki data yang salah tersebut dan tekan tombol Enter bila sudah selesai.
Mengatur Lebar Kolom 1.
Mengatur Lebar Kolom Menggunakan Mouse a) Arahkan pointer mouse pada batas kanan kolom yang akan diubah hingga tanda plus warna putih berubah menjadi tanda panah dua arah. Catatan : Bila ingin mengubah lebar sederet kolom, terlebih dahulu bloklah kolom yang akan diubah lebarnya, kemudian tempatkan pointer mouse ke batas kanan salah satu kolom tersebut. b) Klik tombol kiri mouse, dan sambil terus menekan mouse geser(drag)-lah mouse hingga lebar kolom sesuai yang diinginkan.
2. Mengatur Lebar Kolom Sesuai Data Terpanjang a) Arahkan pointer mouse pada batas kanan huruf kolom yang akan diubah lebarnya. b) Klik dua kali pada batas kolom. Lebar kolom akan mengikuti data terpanjang yang ada pada kolom.
Mengatur Tinggi Baris 1. Arahkan pointer mouse pada batas bawah baris yang akan diubah hingga tanda plus warna putih berubah menjadi tanda panah dua arah. Catatan : Bila ingin mengubah tinggi sederet baris, terlebih dahulu bloklah baris yang akan diubah tingginya, kemudian tempatkan pointer mouse ke batas bawah salah satu baris tersebut.
7
2. Klik tombol kiri mouse, dan sambil terus menekan mouse, geser(drag)-lah mouse hingga tinggi baris sesuai yang diinginkan.
Menghapus Data Untuk menghapus data di suatu sel atau range tertentu, pindahkan sel ke, atau buatlah range tempat yang ingin dihapus, lalu tekan tombol Delete. Dan bila ingin menghapus format dit, Clear, Formats. tampilan data/sel dapat dilakukan dengan memilih dan mengklik menu E
Cell dan Array Cell ditunjukkan dengan kombinasi column (kolom) dan row (baris). Contoh: B11 (menunjukkan cell dengan kolom B dan baris ke-11) C100 (menunjukkan cell dengan kolom C dan baris ke-100) Array merupakan himpunan cell ditunjukkan dengan bentuk umum sebagai berikut: Cell_awal : Cell_akhir Contoh: A1:A10 (menunjukkan array yang dimulai dari A1 diakhiri A10) A1:B11 (menunjukkan array dimulai A1 sampai A11 dan B1 sampai B11) Membuat Range/Blok Sel Pada saat bekerja menggunakan Excel, kita tidak hanya bekerja dalam satu sel saja. Terkadang, anda akan bekerja dalam grup/kumpulan sel. Misalnya saja bila anda ingin merubah jenis huruf beberapa kolom dan (atau) baris, ingin merubah rumus beberapa kolom dan (atau baris), copy- paste atau mendelete data beberapa kolom dan(atau) baris, dll. Dalam kondisi2 seperti ini, anda dapat menggunakan range/blok sel ini untuk memudahkan kerja anda. Range dinamakan menurut alamat sel di ujung kiri atas sampai ujung kanan bawah. Sebagai contoh, range dari sel B2 sampai E7 dituliskan sebagai range B2:E7. (lihat Gambar 4).
8
Gambar 4 Tampilan Range B2:E7
Range/blok sel dapat dibuat dengan beberapa cara : 1. Membuat Range Menggunakan Tombol Shift a) Tempatkan penunjuk sel awal range/bagian awal sel yang ingin diblok, yaitu B2 (lihat Gambar 3). b) Sambil menekan Shift, tekan tombol anak panah sampai sel tujuan, yaitu E7 2. Membuat Range Menggunakan Mouse** a) Klik sel yang merupakan sel awal range, yaitu B2. Pointer/penunjuk mouse harus barada dalam keadaan tanda plus warna putih (tunjuk ke dalam sel, bukan tepi sel). (lihat Gambar 3). b) Sambil tetap menekan klik kiri mouse, gerakkan mouse (drag) ke sel tujuan, yaitu sel E7. 3. Membuat Kolom atau Baris Suatu kolom atau baris dapat disorot dengan mengklik huruf kolom atau nomor baris yang anda inginkan. Misalnya cukup klik huruf kolom B bilai ingin menyorot seluruh kolom B. 4. Menyorot Sederetan Kolom atau Baris Untuk menyorot sederetan kolom (misalnya B, C, D) atau sederetan baris (misalnya 3, 4, 5) dapat dilakukan dengan mengikuti langkah berikut ini : a) Klik di huruf kolom atau di nomor baris awal (di contoh ini adalah B atau 3) b) Sambil tetap menekan tombol mouse kiri, geserlah(drag) pointer(penunjuk) mouse hingga menyorot seluruh baris itu (di contoh ini adalah D atau 5).
9
Gambar 5. Menyorot Sederetan Kolom
Menggunakan Rumus Anda dapat memasukkan rumus yang berupa instruksi matematika ke dalam suatu sel pada lembar kerja. Operator hitung yang dapat digunakan diantaranya adalah + (penjumlahan), - (pengurangan), * (perkalian), dan ^ (perpangkatan). Untuk mengenali cara penggunaannya, terlebih dahulu marilah membuat table seperti gambar 6 dibawah ini:
Gambar 6. Tabel Aritmatika
Untuk mengisi rumus pada cell E3 yakni menjumlahkan nilai A dan nilai B, dapat ditempuh dengan beberapa cara:
1. Menulis Rumus dengan Menggunakan Angka Tetap a) Tempatkan penunjuk sel pada posisi yang diinginkan (dalam contoh ini E3) b) Ketik rumus “=20+54” pada kolom baris rumus dan tekan Enter.
10
Catatan : Penulisan rumus selalu diawali dengan lambng sama dengan (=). 2. Menulis Rumus dengan Referensi Sel a) Tempatkan penunjuk sel pada posisi yang diinginkan (dalam contoh ini E3) b) Ketik rumus “=C3+D4” pada kolom baris rumus dan tekan Enter. Catatan : Dengan menggunakan cara ini, bila data di sel E3 &(atau) E4 diubah, maka hasil di sel E5 pun akan ikut berubah.
3. Menulis Rumus dengan Cara Menunjuk Dengan menggunakan keyboard atau mouse : a) Tempatkan penunjuk sel pada posisi yang diinginkan (dalam contoh ini E3) b) Ketik “=” pada kolom baris rumus c) Pilih atau klik sel C3, lalu ketik “+” d) Pilih atau klik sel D4 lalu tekan Enter.
Fungsi String = LEFT(karakter yang diambil, Jumlah yang diambil) Mengambil karakter dari kiri sebanyak yang diinginkan =RIGHT(karakter yang diambil, Jumlah yang diambil) Mengambil karakter dari kanan sebanyak yang diinginkan =MID(karakter yang diambil, Posisi awal pengambilan, jumlah yang diambil) Mengambil karakter dari tengah sebanyak yang diinginkan dimulai pada posisi tertentu =LEN(karakter) Menghitung jumlah karakter termasuk spasi =LOWER(karakter) Mengubah huruf besar ke huruf kecil =UPPER(karakter) Mengubah huruf kecil ke huruf besar =PROPER(karakter) Mengubah karakter pertama pada tiap teks menjadi huruf besar.
11
Contoh, ketik table berikut ini sesuai pada gambar 7:
Gambar 7. Contoh Fungsi String
Fungsi Logika =OR(kondisi1, Kondisi2, …) Fungsi akan bernilai TRUE, bila salah satu atau semua dari kondisi yang ada bernilai benar (TRUE). Dan akan menghasilkan nilai FALSE jika semua kondisi yang ada bernilai salah. =AND(kondisi1, Kondisi2, …) Fungsi akan bernilai TRUE, bila semua dari kondisi yang ada bernilai benar (TRUE). Dan akan menghasilkan nilai FALSE jika salah satu atau semua kondisi yang ada bernilai salah.
=NOT(kondisi) Fungsi ini akan bernilai kebalikan dari kondisi yang ada. Jika kondisi bernilai TRUE maka hasilnya bernilai FALSE
Untuk contoh penggunaan fungsi logika ( OR, AND, NOT) ketik table berikut ini sesuai pada gambar 8 dibawah ini:
Gambar 8. Contoh Fungsi String
12
=IF(Kondisi, Nilai benar, nilai salah) Fungsi ini menyatakan bahwa jika kondisi terpenuhi maka hasil yang dieksekusi atau tercetak adalah nilai benar, jika kondisi tidak terpenuhi maka yang dieksekusi atau tercetak adalah nilai salah.
Contoh penggunaan fungsi IF:
Untuk status Jika diketahui GOL = I, maka status Tetap Jika diketahui GOL = II, maka status Honor
Untuk Gaji Jika diketahui STATUS = Tetap, maka gaji 1000000 Jika diketahui STATUS = Honor, maka gaji 500000
Perhatikan Gambar 9 dalam peggunaan Fungsi IF:
Gambar 9. Contoh Fungsi String
Pada Cell D6 ketik rumus: =IF(C6="I","Tetap","Honor") Pada Cell D6 ketik rumus: =IF(D6="Tetap",1000000,500000)
13
Fungsi Pembacaan Tabel
=VLOOKUP Fungsi Ini digunakan untuk pembacaan table yang tersusun secara vertical, dengan rumus sebagai berikut:
=VLOOKUP(cell kunci, nama table, indeks kolom)
=HLOOKUP Fungsi Ini digunakan untuk pembacaan table yang tersusun secara horizontal, dengan rumus sebagai berikut:
=HLOOKUP(cell kunci, nama table, indeks kolom)
Untuk contok penggunaan Fungsi Vlookup dan Hlookup, perhatikan gambar 10 berikut ini:
Gambar 10. Contoh Fungsi String
14
Rumus untuk mengisi: Cell D11: =VLOOKUP(C11,$G$5:$H$7,2) Cell E11: =HLOOKUP(C11,$C$4:$E$5,2)
Link Document / Mengambil Data Dari File Excel Lain
Bila kita bekerja dengan 2 file excel, file pertama dimaksudkan untuk tempat kita bekerja mengolah data saja, sedangkan file kedua dimaksudkan tempat kita menyimpan data awal atau data yang tidak boleh sembarangan diubah nilainya.. Sebagai contoh lakukan langkah – langkah berikut: 1.
Buat Tabel di bawah ini dan simpan dengan nama lat1.xls
Gambar 11. Gambar tabel peminjaman buku
2.
Buat tabel di bawah ini dan simpan dengan nama lat2.xls
Gambar 12. Gambar tabel data
15
Kemudian kita akan memanggil 2 tabel yang terdapat dalam file lat2.xls ke dalam file lat1.xls 3.
Untuk memanggil data dari file lain kita gunakan Rumus =[Workbook.xls]Worksheet!Cells
4.
Lalu pada lat1.xls pada cell H4 ketikan Rumus : =[lat2.xls]Sheet1!B2 Maksud dari rumus tersebut kita memanggil data dari cell B2 pada file lat2.xls dan pada sheet1, hasil dari rumus tersebut adalah NIM, karena NIM adalah isi cell B2 pada file lat2.xls
5.
Dengan cara yang sama panggil tabel yang satu lagi dalam file lat2.xls ke lat1.xls pada cell M4, jika berhasil maka tampilan file lat1.xls akan menjadi:
Gambar 13. Setelah data di panggil dari file lat2.xls
Coba perhatikan pada gambar diatas kursor terletak pada cell H5, pada formula bar terlihat rumus bukan isi dari data pada H5, hal ini yang menyebabkan data tersebut tidak bisa kita ubah, walaupun kita paksa ubah, akan segera ketahuan bila kita cek jika yang tampil di formula bar bukan rumus pemanggil data maka data yang bersangkutan palsu atau telah diubah
6.
Setelah ini kita bisa selesaikan tabel peminjaman buku dengan fungsi VLOOKUP Contoh pada cell B5 Rumus nya =VLOOKUP(A5,$H$5:$J$8,2)
16
HIDE AND UNHIDE COLUMN / Menembunyikan kolom
Setelah tadi kita berhasil memanggil data dari file lain, untuk lebih amannya kita sembunyikan data yang telah kita panggil dengan cara:
1.
Blok kolom H,I,J
2.
Kemudian klik kanan
3.
Pilih HIDE
Gambar 14. Menyembunyikan kolom
Untuk menampilkan kembali, dengan cara klik kanan pilih UNHIDE
PROTEKSI FILE
Setelah kita sembunyikan data yang telah dipanggil, ada baiknya kita proteksi file kedua yang menyimpan data tersebut. Proteksi ini berupa pemberian password untuk membuka file, jadi hanya orang tertentu saja yang dapat membuka file tersebut, untuk membuatnya Dengan langkah sebagai berikut:
1.
Buka file lat2.xls
2.
Lalu simpan ulang file tersebut dengan cara klik file save as
3.
Klik Tool yang berada pada kotak dialog save as
4.
Pilih general option
17
Gambar 15. Langkah proteksi File
5.
Ketik / masukan password untuk membuka file
6.
Ketik / masukan password untuk merubah file (Jika perlu, password ini berguna untuk setelah kita berhasil membuka file, untuk merubah data pada file nya kita membutuhkan password lagi.)
Gambar 16. Kotak dialog General Option
18
7.
Setelah selesai klik OK
8.
Perhatian: Jangan lupakan password yang saudara buat, jika lupa maka anda tidak dapat membuka bahkan merubah data dalam file tersebut
Sekarang data kita telah aman, data yang dipanggil sudah dismbunyikan dan file yang memuat data tersebut telah di kunci dengan password dan hanya orang yang mengetahui saja yang dapat membuka file tersebut
