Modul - 3 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 MS-Excel XP. Sedangkan pada versi 2003 tidak terlalu banyak berubah seperti pada versi XP.
Tampilan MS-Excel MS-Excel mempunyai tampilan standar seperti ditunjukkan pada Gambar 3.1, dengan elemen-elemennya antara lain:
Column Cell
Name Box
Formula Bar
Row Sheet
Gambar 3.1 Tampilan standar MS-Excel XP 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
Komputer Dasar
17
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 3.2.
Gambar 3.2 Kotak dialog penyimpanan
18
2.
Tentukan tempat file disimpan, pilih Save in (Alt + i) dan tentukan di mana file 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.
Modul 3: Spreadsheet
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.3 disajikan tiga bentuk cursor yang berbeda.
Gambar 3.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, sedangkan bentuk ketiga muncul jika mouse diarahkan ke pojok kanan bawah cell aktif. Bentuk kedua dan ketiga nanti akan sering digunakan pada operasi manipulasi cell.
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 Penomoran Otomatis MS-Excel bekerja pada tabel, oleh karena itu untuk menandai baris pada sisi kanan biasanya dibuat penomoran baris. Cara membuat penomoran secara otomatis dapat dilakukan dengan dua cara. Cara yang pertama, menggunakan langkah sebagai berikut: 1.
Ketikan nilai awal nomor misalnya 1 (satu) pada cell penomoran.
2.
Pilih menu Edit > Fill > Series, sehingga muncul tampilan pada Gambar 3.4
3.
Pada radio button Series in pilih Columns dan text area Stop Value dengan nilai akhir penomoran misalnya 10 (sepuluh).
Komputer Dasar
19
4.
Klik OK.
Gambar 3.4 Kotak dialog Fill Series Cara yang kedua, menggunakan langkah sebagai berikut: 1.
Ketikan nomer awal di cell penomoran
2.
Ketikan nomer berikutnya di bawahnya
3.
Blok keduanya dan letakkan cursor dipojok kanan bawah sehingga cursor berubah seperti bentuk ketiga.
4.
Klik kiri, jangan dilepas (drag) dan tarik ke bawah dan berhenti sampai nomor yang dikehendaki dan lepas klik.
Operasi pada Cell Penjumlahan Operasi penjumlahan dilakukan dengan operan satu atau dua cell dan operator jumlah (+). Contoh: Isi cell C1 berisi penjumlahan antara cell A1 dan B1, sehingga pada cell C1 diisi dengan cara = A1 + B1 Isi cell C1 berisi penjumlahan antara cell A1 dan bilangan 10, sehingga pada cell C1 diisi dengan cara = A1 + 10 Pengurangan Operasi pengurangan dilakukan dengan operan satu atau dua cell dan operator kurang (-). Contoh: Isi cell C1 berisi pengurangan cell A1 oleh B1, sehingga pada cell C1 diisi dengan cara = A1 - B1
20
Modul 3: Spreadsheet
Isi cell C1 berisi pengurangan cell A1 oleh bilangan 5, sehingga pada cell C1 diisi dengan cara = A1 – 5
Perkalian Operasi perkalian dilakukan dengan operan satu atau dua cell dan operator kali (*). Contoh: Isi cell C1 berisi perkalian antara cell A1 dan B1, sehingga pada cell C1 diisi dengan cara = A1 * B1 Isi cell C1 berisi perkalian antara cell A1 dan bilangan 1%, sehingga pada cell C1 diisi dengan cara = A1 * 1% Pembagian Operasi pembagian dilakukan dengan operan satu atau dua cell dan operator bagi (/). Contoh: Isi cell C1 berisi pembagian cell A1 oleh B1, sehingga pada cell C1 diisi dengan cara = A1 / B1 Isi cell C1 berisi pembagian cell A1 oleh bilangan 3, sehingga pada cell C1 diisi dengan cara = A1 / 3 Pangkat Operasi penjumlahan dilakukan dengan operan satu atau dua cell dan operator pangkat (^). Contoh: Isi cell C1 berisi pangkat cell A1 oleh B1, sehingga pada cell C1 diisi dengan cara = A1 ^ B1 Isi cell C1 berisi pangkat cell A1 oleh bilangan 10, sehingga pada cell C1 diisi dengan cara = A1 ^ 10
Latihan 1 Buat tabel kerja seperti dibawah dan isilah kolom-kolom kosong sesuai dengan instruksi: 1.
Kolom No dengan penomoran secara fill series
2.
Kolom A+B dengan penjumlahan kolom A dan kolom B
Komputer Dasar
21
3.
Kolom A-B dengan pengurangan kolom A oleh kolom B
4.
Kolom A*B dengan perkalian kolom A dan kolom B
5.
Kolom A/B dengan pembagian kolom A oleh kolom B
6.
Kolom A^B dengan perpangkatan kolom A oleh kolom B
7.
dan seterusnya sesuai nama kolomnya.
No 1 2 3 4 5 6 7 8 9 10
A 10 15 12 23 4 5 12 23 34 56
B
A+B
A-B
A*B
A/B
A^B A*5% B/10
C^3
B-5
6 4 7 8 5 4 1 3 12 3
Latihan Mandiri 1 Buat Tabel kerja untuk penggajian karyawan di PT Mudah Saja. Jumlah karyawan ada 10 orang dengan gaji pokok sama yaitu 1 juta rupiah, keterangan setiap karyawan sebagai berikut: 1.
Nama Kurdi, jumlah keluarga 5
2.
Nama Nia, jumlah keluarga 2
3.
Nama Rudi, jumlah keluarga 4
4.
Nama Fulan Akhmad, jumlah keluarga 3
5.
Nama Akihito Nohara, jumlah keluarga 1
6.
Nama Juniarto Wibowo, jumlah keluarga 2
7.
Nama Akhmad Solehan, jumlah keluarga 5
8.
Nama Sinta Juliarti, jumlah keluarga 3
9.
Nama Anton Nugroho, jumlah keluarga 2
10. Nama Isa Ridwan, jumlah keluarga 1 Hitunglah parameter berikut untuk setiap karyawan:
22
1.
Tunjangan beras adalah 20 ribu untuk tiap orang
2.
Tunjangan anak adalah 5% dari gaji pokok per anak
3.
Tunjangan istri adalah 10% dari gaji pokok
4.
Tunjangan kesehatan adalah 3% dari gaji pokok per orang
5.
Gaji Kotor adalah jumlahan dari Gaji Pokok dan tunjangan-tunjangan
6.
Pajak Penghasilan adalah 15% dari Gaji Kotor
7.
Gaji Bersih adalah Gaji Kotor dikurangi Pajak Penghasilan
Modul 3: Spreadsheet
Fungsi pada MS-Excel Fungsi pada MS-Excel digunakan untuk mempermudah pekerjaan pada tabel. Dari banyak fungsi dapat diklasifikasikan menurut banyaknya operan (parameter) yang masuk dalam fungsi. Bisa juga diklasifikasikan berdasarkan disiplin ilmu yang memakainya. Pada pembahasan ini hanya akan dibahas sedikit fungsi-fungsi berdasarkan pemakaiannya.
Matematika Absolute Fungsi yang menghasilkan nilai mutlak dari nilai yang diberikan Bentuk umum: ABS(number) Contoh: ABS(-1) menghasilkan nilai 1. ABS(A1) menghasilkan nilai mutlak dari nilai pada cell A1. Ceiling Fungsi yang menghasilkan nilai pembulatan integer teratas paling dekat Bentuk Umum: CEILING(number; significance) Contoh: CEILING(1,2;1) menghasilkan nilai 2 CEILING(A1;1) menghasilkan nilai pembulatan atas dengan nilai signifikan 1. Combination Fungsi yang menghasilkan banyaknya kombinasi yang mungkin dilakukan dari bilangan yang diberikan (C(m,n)) Bentuk Umum: COMBIN(number;number_choosen) Contoh: COMBIN(3;2) menghasilkan 3 COMBIN(5;2) menghasilkan 10 COMBIN(A1;4) menghasilkan nilai kombiasi dari nilai pada cell A1 dengan banyak 4 pilihan. Cosine Fungsi yang menghasilkan nilai cosinus dari nilai yang diberikan berupa sudut dalam radian. Bentuk Umum: COS(number) Contoh: COS(0) menghasilkan 1 COS(1,570796) menghasilkan nilai 0
Komputer Dasar
23
Exponential Fungsi yang menghasilkan nilai eksponensial berpangkat bilangan yang diberikan Bentuk Umum: EXP(number) Contoh: EXP(2) menghasilkan nilai e1 Factorial Fungsi yang menghasilkan nilai faktorial dari nilai yang diberikan Bentuk Umum: FACT(number) Contoh: FACT(3) menghasilkan nilai 3*2*1*0!=6 Floor Fungsi yang menghasilkan nilai pembulatan ke bawah dengan nilai signifikan yang diberikan Bentuk Umum: FLOOR(number;significance) Contoh: FLOOR(5,1;2) menghasilkan nilai 4 Logarithm Natural Fungsi yang menghasilkan nilai logaritma dengan basis bilangan e. Bentuk Umum: LN(number) Contoh: LN(10) menghasilkan nilai 2,302585 Logarithm Fungsi yang menghasilkan nilai logaritma dengan basis diberikan. Bentuk Umum: LOG(number;base) Contoh: LOG(2;2) menghasilkan nilai 1 Squareroot Fungsi yang menghasilkan nilai akar kuadrat dari nilai yang diberikan Bentuk Umum: SQRT(number) Contoh: SQRT(9) menghasilkan nilai 3 Summation Fungsi yang menghasilkan jumlah dari array yang diberikan. Bentuk Umum: SUM(number1;number2; …) or SUM(Array) Contoh:
24
Modul 3: Spreadsheet
SUM(A1:H1) menjumlah baris 1 dari kolom A..H SUM(A1:A11) menjumlah kolom A dari baris 1..11 Sumproduct Fungsi yang menghasilkan jumlah perkalian dari dua array Bentuk Umum: SUMPRODUCT(Array1;Array2) Contoh: SUMPRODUCT(A1:A11;B1:B11) menjumlahkan perkalian perkalian pada kolom A dan kolom B dari baris ke-1..11.
elemen-elemen
Statistical Average Fungsi yang menghasilakan nilai rata-rata dari array yang diberikan Bentuk Umum: AVERAGE(number1;number2;…) atau AVERAGE(Array) Contoh: AVERAGE(A1:A11) menghasilkan rata-rata nilai pada kolom A baris 1 sampai dengan 11. Correlation Fungsi yang menghasilkan nilai korelasi dari dua array yang diberikan Bentuk Umum: CORREL(Array1;Array2) Contoh: CORREL(A1:A11;B1:B11) menghasilkan nilai korelasi antara kolom A dan kolom B dari baris 1..11 Minimum Fungsi yang menghasilkan nilai minimum dari array yang diberikan Bentuk Umum: MIN(Array) Contoh: MIN(A1:A11) menghasilkan nilai minimal dari array pada kolom A dari baris 1..11 Maximum Fungsi yang menghasilkan nilai maximal dari array yang diberikan Bentuk Umum: MAX(Array) Contoh: MAX(A1:A11) menghasilkan nilai maximal dari array pada kolom A dari baris 1..11 Mode Fungsi yang menghasilkan nilai modus dari array yang diberikan
Komputer Dasar
25
Bentuk Umum: MOD(Array) Contoh: MOD(A1:A11) menghasilkan nilai modus dari array pada kolom A dari baris 1..11 Permutation Fungsi yang menghasilkan nilai permutasi dari bilangan yang diberikan Bentuk Umum: PERMUT(number;number_chosen) Contoh: PERMUT(A1:A11) menghasilkan nilai modus dari array pada kolom A dari baris 1..11 Standarize Fungsi yang menghasilkan nilai standarisasi dari suatu bilangan dengan mean dan standard deviasi yang diberikan Bentuk Umum: STANDARIZE(number;mean;standard_dev) Contoh: STANDARIZE(3;0;3) Standard Deviation Fungsi yang menghasilkan nilai estimasi dari standar deviasi dari suatu array yang diberikan Bentuk Umum: STDEV(Array) Contoh: STDEV(A1:A11) Variance Fungsi yang menghasilkan nilai estimasi berdasarkan sampel yang diberikan Bentuk Umum: VAR(Array) Contoh: VAR(A1:A11)
Teks Left Fungsi yang mengembalikan beberapa karakter pada text dihitung dari kiri. Bentuk Umum: LEFT(text;number) Contoh: LEFT(“SAYA”;3) menghasilkan SAY LEFT(A1;2) mengambil 2 karakter paling kiri pada text di sel A1
26
Modul 3: Spreadsheet
Right Fungsi yang mengembalikan karakter pada text dihitung dari kanan. Bentuk Umum: RIGHT(text; number) Contoh: RIGHT(“AKU”;2) menghasilkan KU RIGHT(B3;2) mengambil 2 karakter paling kanan pada text di sel B3 Mid Fungsi yang mengembalikan sejumlah karakter pada text dimulai dari indeks karakter tertentu. Bentuk Umum: MID(text;start_index;number) Contoh: MID(“PON-ONI”;2;5) menghasilkan ON-ON MID(B4;4;3) megambil 3 karakter dimulai dari indeks ke-4 pada text di sel B4 Len Fungsi yang menghasilkan panjang text Bentuk Umum: LEN(text) Contoh: LEN(“AKU”) menghasilkan nilai 3 LEN(D5) menghasilkan panjang dari text yang terletak di sel D5
Logika Not Fungsi yang menghasilkan nilai negasi dari operannya Bentuk Umum: NOT(logical_value) Contoh: NOT(TRUE) meghasilkan nilai FALSE NOT(B1>5) menghasilkan TRUE jika nilai sel B1 < 5, FALSE jika nilai sel B2 >5 And Fungsi yang mengembalika nilai TRUE jika semua nilai pernyataannya TRUE Bentuk Umum: AND(logical_value1;logical_value2;logical_value3;...) Contoh: AND(TRUE;FALSE) menghasilkan FALSE AND(A1>5;B1<4) menghasilkan TRUE jika nilai sel A1>5 dan B1<4 Or Fungsi yang mengembalikan nilai TRUE jika salah satu nilai pernyataannya TRUE
Komputer Dasar
27
Bentuk Umum: OR(logical_value1;logical_value2;....) Contoh: OR(A1>5;B2<4) menghasilkan FALSE jika nilai A1<=5 dan B2>4.
IF Fungsi yang mengembalikan statemen tertentu jika benar, dan statement yang lain jika salah. Bentuk Umum: IF(logical_value; statement_true; statement_false) Contoh: IF(TRUE;1;0) menghasilkan nilai 1 IF(A5>2; 6; 7) meghasilkan 6 jika A5>2 dan 7 jika sebaliknya.
Look-Up & Reference Akan dijelaskan dengan contoh.
Latihan 2 Dari Latihan Mandiri 1, buat subtotal dari pajak yang diberikan, gaji kotor, dan gaji bersih yang dibayarkan oleh Bendahar Gaji.
Latihan Mandiri 2 Suatu perusahaan keuangan Bank IDAMAN menerapkan penggajian berdasarkan ketentuan masa kerja dan jabatan. Jabatan terdiri dari empat jenis yaitu Bendahara(BE), Teller(TL), Customer Service(CS), Satpam(SP). Besarnya gaji pokok untuk Bendahara Rp 3.000.000,-, Teller Rp 2.000.000,-, Customer Service Rp 2.500.000,-, dan Satpam Rp 1.500.000,-. Gaji tersebut bertambah sebanyak 1% jika masa kerjanya bertambah 2 tahun. Selain mendapat gaji juga mendapat tunjangan-tunjangan antara lain:
28
1.
Tunjangan Istri/ Suami sebesar 5% dari gaji pokok.
2.
Tunjangan anak sebesar 4% dari gaji pokok untuk anak pertama dan kedua, 2% untuk anak ketiga, dan 0% untuk anak keempat dan selanjuntya.
3.
Tunjangan kesehatan sebesar 5% dari gaji pokok untuk karayawan/istri/anak pertama/anak kedua dan 2.5% untuk anak ketiga dan selanjutnya.
4.
Tunjangan perumahan sebesar Rp 2.000.000,- pertahun masa kerja sampai dengan 10 tahun, dan Rp 2.500.000,- untuk di atas 10 tahun
5.
Tunjangan kendaraan sebesar Rp 1.000.000,- pertahun masa kerja sampai dengan 10 tahun, dan Rp 1.500.000,- untuk di atas 10 tahun.
Modul 3: Spreadsheet
6.
Tunjangan Asuransi Pensiun (AsPen) sebesar 5% dari Gaji Pokok,tidak dibayarkan kepada pegawai namun disetorkan pada AsPen.
Pajak yang harus dibayarkan adalah 11% dari GaPok atau 8.5% jika membayar Zakat Mal untuk pegawai beragama Islam. Buat Laporan Keuangan untuk penggajian pegawai di Bank IDAMAN dengan banyak kolom disesuaikan dengan keperluan. Nama Pegawai
NIK
Ira Kusmiyati Sumirah
BE8706
Hakan Yakin
BE8807
Yakin Sukur
CS8806
Nurul Imamah
CS9104
Iksan
CS0002
Arum Lestari
TL9704
Rini Astuti
TL9702
Harun Aliraja
TL9903
Paino Kusumobroto
SP9004
Ikbal
SP9105
NIK mempunyai maksud dua karakter dimuka menunjukan jabatan, dua karakter selanjutnya tahun masuk, dua karakter berikutnya adalah jumlah keluarga (pegawai + istri/suami + anak). Contoh BE8706, artinya jabatan Bendahara, bekerja sejak tahun 1987 dan mempunyai 4 anak.
Komputer Dasar
29
