Modul Ms Excel 97
Posted by labkom on 21 Januari 2009
Modul Ms Excel 9Ms Excel merupakan program spreadsheet yang dapat digunakan untuk membuat hitungan-hitungan, daftar, diagram, laporan keuangan, dan sebagainya.
Materi modul ini
Bagian 1
1. Memulai dan mengenal Excel2. Memulai bekerja dengan worksheet
3. Menyimpan worksheet
4. Menutup worksheet
5. Membuka worksheet
6. Menentukan format angka
7. Membuat rumus
8. Toolbar standard dan format
9. Toolbar lain
10. Mencetak worksheet
Bagian 2
1. Formula dasar (SUM, AVERAGE, MIN, MAX, COUNT)2. Alamat sel
3. Formula lanjut (IF, VLOOKUP, HLOOKUP)
Bagian 3
1. GrafikMemulai dan Mengenal Excel
1. Jalankan Ms Excel melalui menu StartàProgramàMicrosoft Excel2. Perhatikan window Ms Excel yang muncul seperti berikut ini
Ada beberapa hal yang harus anda ketahui pada window Excel di atas yaitu:
1. Menu Utama: merupakan menu untuk mengakses seluruh perintah yang ada pada Ms Excel.
2. Toolbar Standard: berisi kumpulan tombol-tombol perintah standard seperti pembukaan, pembuatan, pencetakan, pengeditan, dan sebagainya.
3. Toolbar Format: berisi kumpulan tombol-tombol perintah pemformatan standard seperti jenis, ukuran, dan warna huruf, format angka, warna sel, dan sebagainya.
4. Formula bar: merupakan kolom isian untuk membuat formula.
5. Kolom: identifikasi kolom.
6. Baris: identifikasi baris.
7. Pointer: penunjuk sel yang sedang aktif.
8. Scrollbar horizontal: digunakan untuk menggeser-geserkan worksheet ke kiri dan ke kanan.
9. Scrollbar vertikal: digunakan untuk menggeser-geserkan worksheet ke atas dan ke bawah.
10. Pilihan Worksheet: digunakan untuk memilih worksheet yang anda miliki.
11. Status bar: untuk memperlihatkan status pekerjaan anda.
Dalam bekerja dengan Excel, anda harus terlebih dahulu mengerti istilah-istilah berikut:
1. Worksheet: adalah dokumen tempat anda bekerja. Dalam satu file Ms Excel (yang berekstensi XLS) mungkin terdapat beberapa buah worksheet, yang masing-masing memiliki nama tersendiri. Workseet terdiri dari baris-baris dan kolom-kolom. Misalnya anda memiliki file Excel dengan nama LAPORAN.XLS, di dalamnya mungkin terdapat worksheet Januari, Februari, Maret, April, dan sebagainya.
2. Baris: adalah baris-baris yang ada dalam worksheet. Dalam Excel baris ini dinamai dengan 1,2,3, … sampai dengan 16192.
3. Kolom: adalah kolom-kolom yang ada dalam worksheet. Dalam Excel baris ini dinamai dengan A, B, C, D, …, AA, AB, AC, … sampai dengan 256 kolom.
4. Sel: adalah perpotongan antara baris dengan kolom. Misalnya perpotongan baris 2 dengan kolom C, dinamai dengan sel C2.
5. Pointer: adalah kotak segiempat yang ada pada worksheet yang berupakan penunjuk sel yang sedang aktif atau sedang dipilih. Anda hanya dapat bekerja pada sel yang aktif ini. Gunakan pointer mouse atau tombol panah pada keyboard untuk menuju ke suatu lokasi sel tertentu.
6. Range: adalah area yang terdiri dari beberapa buah sel. Untuk menyatakan area dari sel A1 sampai dengan sel C4 digunakan range A1:C4.
7. Grid: garis kotak-kotak yang memisahkan sel.
Memulai bekerja dengan worksheet
Ketikkan angka-angka pada worksheet anda sehinga terlihat seperti gambar di samping ini.Dasar-dasar worksheet
Ada beberapa hal yang dapat kita pelajari dari contoh di atas, yaitu:1. AutoFill, digunakan untuk secara otomatis mengisi sel-sel yang berdampingan dengan data-data yang terurut, seperti 1,2,3,…, January, February, March,April, … dan sebagainya. Untuk membuat AutoFill, isikan minimal 2 sel dengan urutan data yang dikehendaki (misalnya 1 dan 2), kemudian blok sel tersebut, lalu tarik (drag) Fill Handle (yaitu kotak kecil yang ada pada sudut kanan bawah pointer) sampai dengan lokasi yang anda kehendaki. Secara otomatis data-data pada sel yang anda lewati akan terisi dengan data yang terurut berdasarkan data awal yang anda berikan sebelumnya.
2. Membuat rumus sederhana untuk menjumlahkan harga 1 dan harga 2 pada kolom total harga. Arahkan pointer pada kolom E baris 3. Kemudian ketikkan =C3 + D3 lalu tekan ENTER. Arti dari rumus ini adalah jumlahkan sel C3 dengan sel D3. Total harga untuk baris ini akan terlihat pada sel E3. Penulisan formula atau rumus pada worksheet Excel selalui dimulai dengan tanda sama dengan (=) baru diikuti dengan rumusnya.
3. Anda tidak harus menuliskan rumus untuk setiap baris lainnya (baris 4,5,6, dan tujuh). Anda dapat menggunakan AutoFill untuk mengisi sel-sel berikutnya dengan mengambil referensi pada salah satu sel yang telah ada isi dan rumusnya. Pada contoh di atas, tarik (drag) lah fill handle pada pointer sel E3 menuju sel E7, sehingga tampilan worksheet anda menjadi seperti di samping.
4. Perhatikan pada sel E7, dan formula bar (yang menampilkan formula atau rumus pada sel yang aktif). Di situ tertulis =C7+D7. Artinya secara otomatis (melalui AutoFill) Excel mengisi sel-sel yang kita tentukan dengan formula yang benar.
Memformat sel
Pemformatan dilakukan untuk memperindah tampilan laporan anda. Beberapa yang yang dapat dilakukan adalah membuat garis pinggir tabel, memformat huruf (ukuran, jenis, dan warna), perataan paragraph, warna sel, kolom, dan baris, dan lainnya.Membuat shading/warna sel
1. Sekarang arahkan pointer pada baris nomor 2 lalu blok sel-sel pada kolom B, C, D, dan E.2. Pilih menu FormatàCells sehingga muncul window Format Cell, lalu pilih Pattern
3. Tentukan warna yang anda kehendaki.
Membuat border
1. Blok seluruh range yang hendak anda buat garis pinggirnya, pada contoh ini adalah range D2:E72. Pilih menu FormatàCells sehingga muncul window Format Cell, lalu pilih Border.
3. Pilih jenis garis pinggir yang anda kehendaki yaitu None untuk tanpa garis, Outline untuk garis di luar range yang anda pilih, dan Inside untuk garis di dalam range yang anda pilih (grid). Pilihan ini dapat anda temukan pada kolom Presets.
4. Untuk membuat garis selain seperti di atas, anda dapat langsung menentukannya pada kolom Border. Klik pada lokasi garis untuk menambah atau menghilangkan garis.
5. Anda dapat pula menentukan jenis garis untuk masing-masing garis yang ditentukan pada langkah 3 yaitu dengan menentukan Style-nya. Warna garis dapat anda tentukan melalui pilihan Color.
Setelah di format, worksheet anda akan terlihat seperti gambar di samping ini.Latihan 1
Buatlah worksheet, pada file yang sama dengan di atas, seperti gambar di samping ini.Hal-hal yang perlu dilakukan adalah:
1. Mengetik data-data pada worksheet
2. Mengetik data nomor barang pada dua baris pertama yaitu A12001 dan A12002, lalu melakukan AutoFill untuk baris selanjutnya.
3. Membuat shading pada kepala tabel
4. Memformat kepala tabel sehingga text yang ada menjadi tidak terpotong. Ini dilakukan dengan mem-blok kepala tabel, lalu memilih menu FormatàCells, pilih Alignment, dan pilih Text Alignment HorizontalàCenter
5. Memformat judul tabel sehingga berada di tengah-tengah tabel dengan memblok judul tersebut, kemudian memilih menu FormatàCells, pilih Alignment, dan pilih Text Alignment HorizontalàCenter Across Selection.
6. Arahkan pada sel Harga, tentukan formulanya adalah HARGA SATUAN * JUMLAH. Isikan dengan AutoFill semua sel Harga untuk baris-baris selanjutnya.
Menyimpan Worksheet
Untuk menyimpan file Excel dapat anda lakukan sama dengan aplikasi-aplikasi Windows (khususnya keluarga MS Office) yaitu:1. dengan menu FileàSave
2. dengan tombol Save () pada toolbar standard
3. dengan tombol CTRL-S pada keyboard
Kemudian tentukan nama file dan lokasi folder penyimpanan pada komputer anda.
Menutup Worksheet
Untuk menutup file Excel dapat anda lakukan sama dengan aplikasi-aplikasi Windows (khususnya keluarga MS Office) yaitu:1. dengan menu FileàClose
2. dengan tombol CTRL-F4 pada keyboard
Membuka worksheet
Untuk membuka file Excel dapat anda lakukan sama dengan aplikasi-aplikasi Windows (khususnya keluarga MS Office) yaitu:1. dengan menu FileàOpen
2. dengan tombol Open () pada toolbar standard
3. dengan tombol CTRL-O pada keyboard
Kemudian tentukan lokasi folder dan nama file yang hendak anda buka.
Membuat worksheet baru dalam satu file
Satu file Ms Excel mampu menampung lebih dari satu worksheet. Untuk membuat worksheet baru pilih menu InsertàWorksheet.Menghapus worksheet
Gunakan menu EditàDelete Sheet untuk menghapus worksheet yang sedang aktif.Memilih worksheet tertentu
Gunakan tab pilihan worksheet untuk memilih suatu worksheet tertentu pada file anda.Mengganti nama worksheet
Double klik pada tab pilihan worksheet yang akan diganti namanya, lalu isikan nama yang anda kehendaki, dan tekan ENTER.Latihan 2
1. simpan file latihan di atas dengan nama C:\My Documents\latihan1.xls2. tutup file itu
3. buka kembali
Format angka
Seringkali kita perlu memformat angka-angka pada worksheet, khususnya untuk laporan-laporan sehingga enak dipandang dan mengurangi kesalahlihatan terhadap angka-angka tersebut. Hal-hal yang biasa dilakukan dalam pemformatan angka adalah lokasi simbol ribuan dan jutaan, lokasi simbol bilangan desimal, persentasi, format mata uang, format akunting, format scientific, format tanggal, dan lain-lain.Untuk memformat angka:
1. blok sel-sel yang hendak di format angkanya
2. pilih menu FormatàCells
3. pilih Number, dan tentukan jenis pemformatan angka
Latihan 3
Formatlah worksheet sebelumnya sehingga tampilannya seperti di samping.Hal-hal yang dilakukan
1. blok range D4:D10
2. pilih menu FormatàCells, pilih Number
3. pilih jenis format Number, Decimal Place = 2,
4. pilih Use 1000 Separator.
5. ulangi untuk range F4:F10.
Toolbar Standard
Toolbar standard berisi perintah-perintah standard yang sering digunakan. Dengan menggunakan toolbar, pekerjaan dapat menjadi lebih mudah dan cepat, karena hanya perlu menekan satu tombol, dibandingkan dengan menggunakan menu.Berikut ini gambar toolbar standard dan fungsi-fungsinya.
Toolbar Format
Berikut ini gambar toolbar format yang merupakan kompulan tombol-tombol pemformatan worksheet beserta fungsi-fungsinya.
Toolbar lainnya
Anda dapat menggunakan toolbar-toolbar lainnya yang dapat mengefisienkan pekerjaan anda. Untuk melihat dan menggunakan semua jenis toolbar yang tersedia, gunakan menu ViewàToolbar dan pilih toolbar yang ingin anda gunakan, sebagai berikut:1. Autotext, toolbar untuk membuat dan menggunakan autotext
2. Control Toolbox, untuk membuat aplikasi dalam Ms Excel
3. Database, untuk mengakses dan menggunakan database pada worksheet anda
4. Drawing, untuk membuat gambar-gambar yang dapat digunakan bagi laporan dan worksheet anda
5. Forms, untuk membuat formulir-formulir isian pada worksheet
6. Picture, untuk mengedit gambar
7. Reviewing, untuk membuat review pada worksheet
8. Tables and border, untuk membuat tabel dan garis pinggir
9. Visual Basic, untuk membuat aplikasi Ms Excel dengan Visual Basic
10. Web, untuk mengakses internet atau intranet
11. WordArt, untuk membuat word art
12. Custom, untuk membuat toolbar yang dapat anda sesuaikan dengan keperluan anda
Mencetak worksheet
Pencetakan worksheet dapat anda lakukan seperti layaknya pentecakan pada aplikasi Windows lainnya, yaitu dengan menu FileàPrint, tombol Print pada toolbar standard, atau tombol CTRL-P pada keyboard.Mencetak seluruh atau beberapa worksheet
1. Gunakan menu FileàPrint atau CTRL-P, lalu akan muncul window print seperti di samping2. Tentukan printer yang akan digunakan
3. Tentukan halaman yang akan dicetak, apakah semuanya (All) atau halaman tertentu yang dinyatakan pada Page(s), From … To ….
4. Tentukan bagian mana yang akan dicetak, apakah seluruh worksheet yang ada pada file (Entire workbook), atau worksheet yang sedang terpilih / aktif saja (Active sheet(s)), atau sel-sel worksheet yang sedang dipilih saja (Selection).
5. Tentukan jumlah pencetakan untuk setiap halamannya.
6. Jika anda ingin mencetak pada file dan bukan pada printer, klik pilihan Print to File. Ini digunakan jika pada saat anda mencetak anda tidak memiliki printer, dan akan mencetaknya pada komputer lain yang memiliki printer namun tidak memiliki program Ms Excel.
7. Klik OK jika semua telah selesai.
Setting pencetakan
Terkadang
anda perlu melakukan setting sebelum mencetak laporan sehingga hasil
cetakan sesuai dengan yang anda harapkan. Setting ini meliputi margin,
ukuran dan orientasi kertas, header dan footer, skala, kualitas
pencetakan, dan sebagainya.Pilih menu FileàPrint, lalu klik tombol Preview, kemudian klik tombol Setup. Akan muncul window Page Setup seperti pada gambar di sebelah.
1. Tentukan orientasi kertas apakah memanjang (Landscape) atau meninggi (Potrait).
2. Tentukan skala pencetakan dalam persentasi dari ukuran normal ( Adjust to … %), atau dikecilkan sebesar panjang dan lebar kertas (Fit to … pages wide by … tall).
3. Tentukan ukuran kertas (Paper size)
4. Tentukan Kualitas pencetakan (Print quality)
Untuk men-setting margin, pilih tab Margin pada window Page Setup.
1. Tentukan margin atas (Top)
2. Tentukan margin kiri (Left)
3. Tentukan margin bawah (Bottom)
4. Tentukan margin kanan (Right)
5. Tentukan margin header (Header)
6. Tentukan margin footer (Footer)
7. Tentukan posisi worksheet pada kertas apakah berada di tengah kertas secara horizontal (Horizontally), atau secara vertikal (Vertically).
Untuk men-setting header dan footer, pilih tab Header/Footer pada window Page Setup.
1. Tentukan text header yang akan ditampilkan pada header halaman, atau klik tombol Custom Header untuk header yang anda kehendaki
2. Tentukan text footer yang akan ditampilkan pada header halaman, atau klik tombol Custom Footer untuk footer yang anda kehendaki
Untuk menentukan hal-hal lain pada pencetakan, pilih tab Sheet pada window Page Setup.
1. Tentukan setting pencetakan apakah grid disertakan pada pencetakan (Gridlines), apakah pencetakannya hitam-putih (Black and White), apakah kualitas pencetakan hanya berupa draft (Draft quality), dan apakah anda akan menyertakan nama-nama baris dan kolom pada pencetakan (Row and columns headings).
2. Tentukan arah pencetakan jika worksheet anda melebihi ukuran kertas, apakah kebawah lalu ke samping (Down, then over) atau kesamping lalu ke bawah (Over, then down).
Formula
Formula atau rumus digunakan untuk melakukan perhitungan pada worksheet. Ada banyak formula yang terlah tersedia pada Ms Excel yagn meliputi rumus-rumus aritmetik, keuangan, scienctific, logika, dan sebagainya. Berikut ini kita akan membahas beberapa formula yang umum digunakan dalam worksheet.Fungsi SUM()
Digunakan untuk menjumlahkan angka-angka pada range tertentu. Sebagai contoh, buka lagi file latihan sebelumnya (C:\My Documents\latihan1.xls).Ketikkan baris-baris tambahan seperti gambar si samping, lalu ikuti langkah-langkah berikut:
1. Pada kolom setelah kolom TOTAL (B10), ketik kan =SUM(E4:E8), yang artinya jumlahkan isi sel-sel dari sel E4 hingga sel E8.
2. Tekan ENTER.
Anda dapat juga melakukan hal di atas tanpa pengetikan untuk mempermudah pekerjaan yaitu dengan:
1. mengetikkan tanda = pada kolom B10
2. mem-blok sel-sel pada range E4 hingga E8
3. tekan ENTER.
Fungsi AVERAGE().
Digunakan utuk membuat harga rata-rata dari suatu range.1. Pada sel E13, ketikkan =AVERAGE(E4:E8) untuk membuat harga rata-rata dari sel E4 hingga E8.
2. Tekan ENTER.
Fungsi MAX().
Digunakan untuk mengeluarkan nilai maksimal dari suatu range.1. Pada sel E11, ketikkan =MAX(E4:E8), untuk mengambil harga maksimal dari sel E4 hingga E8.
2. Tekan ENTER
Fungsi MIN().
Digunakan untuk mengeluarkan nilai maksimal dari suatu range.1. Pada sel E12, ketikkan =MIN (E4:E8), untuk mengambil harga maksimal dari sel E4 hingga E8.
2. Tekan ENTER
Fungsi COUNT()
Digunakan untuk mengeluarkan nilai maksimal dari suatu range.1. Pada sel E14, ketikkan =COUNT (E4:E8), untuk mengambil jumlah data pada sel E4 hingga E8.
2. Tekan ENTER
Latihan 4
Ketikkan dan format laporan tabel berikut ini , dan isikan semua sel yang mengandung formula.Formula: STOCK AKHIR = STOCK AWAL + JUMLAH PEMBELIAN – JUMLAH PENJUALAN
1. Simpan dengan nama C:\My Documents\latihan4.xls
2. Keluar dari Ms Excel
Alamat Sel
Pada worksheet Excel setiap sel memiliki alamat (misalnya A20) untuk digunakan sebagai referensi pada formula. Ada beberapa macam alamat sel, yaitu:1. Alamat Relatif: alamat sel yang selalu berubah, misalnya C4.
2. Alamat Absolut: alamat sel yang tidak akan berubah, misalnya $C$4.
3. Alamat Semi Absolut Baris, alamat sel yang barisnya tetap, misalnya C$4.
4. Alamat Semi Absolut Kolom, alamat sel yang kolomnya tetap, misalnya $C4.
Alamat-alamat sel yang absolut digunakan saat membuat formula yang mengambil referensi dari sel-sel tertentu sebagai data masukan. Perhatikan contoh berikut:
Contoh ini adalah contoh penghitungan harga barang dalam satuan dollar dan rupiah, dengan mengambil data kurs dollar terhadap rupiah. Untuk mencari harga dalam rupiah, kita dapat menggunakan formula HARGA * DOLLAR, yaitu =B4*B1. Namun jika kita tarik (AutoFill) untuk harga-harga pada baris selanjutnya, kita akan mendapatkan pesan error pada baris-baris selanjutnya. Ini terjadi karena kita menggunakan alamat relatif untuk data kurs satu dollar, yaitu alamat sel B1. Akibatnya untuk baris kedua, formulanya menjadi =B5*B2, dimana sel B2 bukanlah data untuk kurs dollar.
Agar tidak terjadi error, kita harus membuat sel kurs dollar menjadi absolut sehingga untuk baris-baris selanjutnya tetap menggunakan alamat sel B1. Untuk itu, kita harus membuat formula pada sel data kurs dollar menjadi absolut baris, yaitu B$1. Kita gunakan semi absolut baris karena kita ingin barisnya yang tetap. Jadi formula yang harus kita masukkan adalah =B$1 * B4.
Akibatnya worksheet kita akan terlihat seperti gambar di samping, dimana isi sel C6 adalah =B$1*B6.
Perhatikan juga isi sel-sel lain pada baris-baris lainnya.
TIPS:
1. Untuk membuat alamat sel absolut dapat digunakan tombol F4 sebanyak 1 kali.
2. Untuk membuat alamat sel semi absolut baris dapat digunakan tombol F4 sebanyak 2 kali.
3. Untuk membuat alamat sel semi absolut kolom dapat digunakan tombol F4 sebanyak 3 kali.
Latihan 5
Buat tabel berikut pada sebuah worksheet baru.
Formula:
1. Bunga harian = persentasi bunga harian * jumlah tabungan
2. Bunga bulanan = persentasi bunga bulanan * jumlah tabungan
3. Jumlah bunga = bunga harian + bunga bulanan
Catatan:
Anda harus membuat data bunga harian dan bunga bulanan menjadi alamat sel semi absolut baris pada formula bunga harian dan bulanan.
Fungsi IF()
Fungsi IF adalah formula untuk keperluan operasi logika yang akan mengevaluasi apakah suatu pernyataan matematis benar atau salah. Jika pernyataan benar, maka formula IF akan memberikan suatu nilai, dan jika salah akan memberikan nilai lain.Format fungsi ini adalah
=IF(test_logika, nilai_benar, nilai_salah)
Perhatikan contoh pada gambar di samping.
Pada contoh ini, sel B2 akan berisi 10% jika sel A2 berisi angka yang lebih besar daripada 200000, dan akan berisi 0 jika kurang dari 200000.
Latihan 6
Buatlah daftar nilai sebagai berikut:Formula:
Nilai akhir = (Nilai UTS + Nilai UAS + Nilai Praktek)/3 (Tips: Gunakan fungsi AVERAGE().)
Status = jika nilai akhir > maka status = ”LULUS” dan jika tidak maka status = “GAGAL”
Fungsi VLOOKUP() dan HLOOKUP()
Fungsi ini digunakan untuk melakukan pembacaan data dari suatu tabel ke tabel lain. Hal ini dilakukan agar tidak melakukan pengetikan data yang sama secara berulang-ulang. Fungsi VLOOKUP() digunakan untuk membaca data tabel dalam arah vertikal (data yang dibaca berada dalam bentuk baris-baris), sedangkan fungsi HLOOKUP() dalam arah horisontal (data yang dibaca berada dalam bentuk kolom-kolom).Format dari fungsi ini adalah:
=VLOOKUP(kolom_yang_dibandingkan, tabel_data, nomor_kolom_tabel_data)
· kolom_yang_dibandingkan adalah kolom pada tabel utama yang akan dibandingkan dengan tabel data pembanding.
· tabel_data adalah tabel pembanding yang berisi data-data pembanding, biasanya dalam bentuk range yang semi absolut baris.
· nomor_kolom_tabel_data adalah nomor kolom pada tabel pemanding yang isinya akan ditampilkan pada tabel utama.
dan
=HLOOKUP(baris_yang_dibandingkan, tabel_data, nomor_baris _tabel_data)
· baris_yang_dibandingkan adalah baris pada tabel utama yang akan dibandingkan dengan tabel data pembanding.
· tabel_data adalah tabel pembanding yang berisi data-data pembanding, biasanya dalam bentuk range yang semi absolut kolom.
· nomor_kolom_tabel_data adalah nomor baris pada tabel pemanding yang isinya akan ditampilkan pada tabel utama.
Latihan 7
Untuk lebih jelasnya perhatikan dan praktekkan contoh berikut: Anda diminta untuk membuat laporan gaji karyawan sebuah perusahaan. Setiap karyawan akan mendapatkan gaji pokok yang besarnya teragnutng dari golongannya. Bonus akan diberikan sesuai dengan golongan dan jam kerjanya setiap bulan. Di samping itu terdapat tunjangan istri dan anak untuk karyawan yang telah menikah yang besarnya juga tergantung dari golongan karyawan tersebut. Lalu tentukan gaji bersih yang dapat diterima karyawan, yaitu total gaji pokok ditambah bonus dan tunjagan-tunjangan yang ada. Berikut ini gambar tabel untk laporan yang dimaksudkan di atas.Pada contoh di atas pertama-tama kita harus mengisikan gaji pokok pada daftar gaji karyawan berdasarkan data tabel pembanding. Isi dari gaji pokok ini tergantung dari golongan karyawan yang bersangkutan. Jika golongan 2 gaji pokoknya adalah 300000, jika golongan 5 gaji pokoknya adalah 600000, demikian seterusnya.
Karena kita melihat tabel pembanding dalam arah vertikal (data pembanding tersusun dalam bentuk baris-baris), maka kita harus menggunakan fungsi VLOOKUP(). Kolom yang akan kita bandingkan pada abel daftar gaji adalah kolom GOL (C11:C17) , sementara tabel pembanding yang kita gunakan adalah tabel pembanding yang berisi data golongan dan gaji pokok, yaitu A$3:B$7. (tanda semi absolut baris kita gunakan karena kita ingin range tabel yang semi absolut baris). Kolom yang akan kita ambil dari tabel pembanding ini adalah kolom gaji pokok, yaitu kolom kedua dari tabel pembanding. Karena itu, isikan pada sel G11 =VLOOKUP(C11:C17, A$3:B$7, 2). Kopikan untuk baris-baris selanjutnya.
Untuk kolom BONUS, kita harus menggunakan gabungan dua buah fungsi yaitu VLOOKUP() dan IF(). Pertama kita harus membandingkan jika JAM KERJA lebih dari 50 jam , maka karyawan mendapatkan bonus yang tergantung dari golongannya, sehingga kita harus membandingkan kolom GOL (C11:C17) dengan kolom ketiga dari tabel pembanding. Namun jika tidak lebih dari 50, karyawan tidak akan mendapatkan bonus. Jadi formula untuk sel H11 harus berisi =IF(jam kerja > 50, cari nilai bonus berdasarkan golongan, 0). Dalam formula Excel dapat dituliskan sebagai berikut:
=IF(D11>50,VLOOKUP(C11:C17, A$3:C$7, 3), 0).
Kopikan formula ini untuk baris-baris selanjutnya.
Kolom selanjutnya adalah kolom TUNJANGAN ISTRI. Di sini kita juga harus menggunakan gabungan dua buah fungsi yaitu VLOOKUP() dan IF(). Pertama kita harus mengecek apakah karyawan yang bersangkutan telah MENIKAH atau BELUM. Jika telah menikah, maka ada tambahan gaji berupa TUNJANGAN ISTRI yang jumlahnya tergantung golongannya. Jika belum menikah, maka dia tidak mendapatkan tunjangan istri. Formula untuk keadaan di atas adalah =IF(status=”Menikah”, cari tunjangan istri berdasarkan golongan, 0).
Dalam formula Excel, hal di atas dapat kita tuliskan (pada sel I11) sebagai berikut:
=IF(E11=”Menikah”, VLOOKUP(C11:C17, A$3:D$3, 4), 0)
Untuk kolom TUNJANGAN ANAK, kasusnya hampir mirip, namun kita gunakan tabel pembanding dengan range A$3:E$3 dan mengambil kolom ke lima (TUNJANGAN ANAK). Jadi ketikkan pada sel G11:
=IF(E11=”Menikah”, VLOOKUP(C11:C17, A$3:E$3, 5), 0)
Kolom terakhir, yaitu GAJI BERSIH, adalah penjumlahan gaji pokok, bonus, tunjangan istri, dan tunjangan anak. Setelah diisikan formulanya dan dilakukan pemformatan angka, maka tabel di atas akan terlihat seperti berikut ini:
Latihan 8
Gunakan fungsi HLOOKUP() untuk membuat laporan hasil ujian sebagai berikut:Ketentuan:
1. Gaji harian diperoleh dari tabel pembanding berdasarkan golongan. (Tips: gunakan fungsi HLOOKUP() ).
2. Gaji bulanan adalah gaji harian dikali jumlah hadir (dalam sebulan)
Grafik
Grafik merupakan sarana penyampaian data yang paling gampang dibaca dan dianalisa. Ms Excel menyediakan fasilitas untuk membuat grafik dari data-data yang tersedia. Grafik yang dapat dihasilkan sangat bermacam ragam, mulai dari grafik garis, bar, pie slice, 3D, dan sebagainya. Untuk membuat grafik dalam Excel juga tidak sulit, karena ada Chart Wizard yang akan menolong anda dalam membuat grafik yang sulit sekalipun.Ketikkan data-data berikut ini pada sebuah worksheet baru:
Kemudian kita akna membuat grafik untuk data di atas dengan langkah-langkah sebagai berikut:
1. Blok tabel data di atas
2. Klik Chart Wizard pada toolbar standard.
3. Tentukan jenis grafik yang anda kehendaki (Step 1 of 4), misalnya pilih jenis Column 3D, lalu tekan Next.
4. Tentukan cara penampilan grafik anda apakah berdasarkan kolom atau baris. Tentukan hal ini pada pilihan Series in: Rows atau Colums (Step 2 of 4). Pilih Rows lalu tekan Next.
5. Tentukan judul grafik (Chart Title), judul axis X, Y, dan Z jika ada (Step 3 of 4) lalu tekan Next.
6. Tentukan tujuan penggambaran grafik, apakah pada worksheet baru (As New sheet) atau sebagai object baru pada worksheet yang sedang aktif sekarang (As object in…) (Step 4 of 4). Pilih lalu tekan Next.
7. Selesai, hasilnya adalah sebagai berikut:
Memformat elemen grafik
Sebuah grafik Excel terdiri dari banyak elemen, seperti yang terlihat pada gambar di bawah ini:Untuk memformat setiap elemen grafik yang terlihat, double-klik elemen itu, sehingga muncul window format. Di sini kita dapat melakukan setting seperti:
1. warna dan tampilannya (Pattern)
2. garis pinggirnya (Border)
3. label data
4. skala (Scale)
5. jenis huruf (Font)
6. format angka (Number)
7. tampilan text (Alignment)
Latihan 8
Ketikkan data-data berikut pada worksheet baru.Kemudian buat grafik seperti berikut berdasarkan data-data di atas.
Hal-hal yang perlu dilakukan agar tampilan grafik seperti di atas:
1. Blok data lalu klik tombol Chart Wizard
2. Pilih jenis chart Line
3. Format font Category Axis, Value Axis, dan Legend menjadi Arial 8 point
4. Format font Title menjadi Arial 12 point
5. Format font Category Axis Title dan Value Axis Title menjadi Arial 10 point
6. Format Series sehingga memiliki Data Label (Data LabelàShow Value)
7. Format Font Data Label menjadi Arial 8 point
8. Format warna Plot Area menjadi gradasi hitam dan putih.
source: http://labkom.blogdetik.com/2009/01/21/modul-ms-excel-97/
