Microsoft Excel Lanjutan
Tim Pengajar TIK - FISIP
Materi Excel Lanjutan • Format Data Cell & Validasi Data • Penggunaan text dan number pada formula • Fungsi tanggal dan waktu
• Skenario Data • What If Analysis
• LOOKUP • Vlookup • Hlookup
• Kombinasi formula LOOKUP dan IF
FORMAT DATA CELL & VALIDASI DATA • FUNGSI TANGGAL DAN WAKTU Fungsi
: =NOW()
Kegunaan
: Untuk memasukkan tanggal dan jam sekarang
Fungsi
: =SECOND(data_waktu/sel_data)
Kegunaan
: Untuk mengambil detik dari waktu
Fungsi
: =TODAY()
Kegunaan
: Untuk memasukkan tanggal sekarang (hari ini)
Fungsi
: =TIME(hour, minute, second)
Kegunaan
: Untuk mengatur waktu dengan AM dan PM (12 digit)
FORMAT DATA CELL & VALIDASI DATA • FUNGSI TANGGAL DAN WAKTU Fungsi
:
=DATE(year, month, day)
Kegunaan
:
Untuk menuliskan suatu data tanggal pada sel tertentu
• Fungsi DATE dapat digantikan dengan merubah tipe data pada format cell dengan date.
FORMAT DATA CELL & VALIDASI DATA • FUNGSI TEKS / STRING Fungsi
: =LEFT (teks, jumlah karakter yang diambil)
Kegunaan
:
Fungsi
: =RIGHT (teks, jumlah karakter yang diambil)
Kegunaan
:
Fungsi
: =MID (teks, start_number, number_char)
Kegunaan
:
Untuk mengambil teks sejumlah karakter tertentu dari sebelah kiri
Untuk mengambil teks sejumlah karakter tertentu dari sebelah kanan
Untuk mengambil teks dari kedudukan tertentu dengan sejumlah karakter tertentu
FORMAT DATA CELL & VALIDASI DATA • FUNGSI TEKS / STRING Fungsi
:
=LOWER (teks)
Kegunaan
:
Mengubah teks atau string dari huruf kapital ke huruf kecil
Fungsi
:
=UPPER (teks)
Kegunaan
:
Mengubah teks atau string dari huruf kecil ke huruf kapital
Fungsi
:
=LEN(teks)
Kegunaan
:
Menghitung jumlah karakter dalam suatu teks/string
SKENARIO DATA PADA WHAT IF ANALYSIS Ada 3 perintah yang dimuat dalam What-If-Analysis ini, yaitu: • Scenario Manager - Untuk melihat beberapa hasil atau output dari beberapa kemungkinan input yang berlainan. Perintah ini umum digunakan dalam analisa bisnis atau analisa suatu project untuk melihat beragam kemungkinan perhitungan yang mungkin terjadi. • Goal Seek - Untuk mencari berapa input atau nilai awal agar Anda mendapatkan output atau nilai akhir yang diharapkan. • Data Table - Untuk mencari nilai hasil secara cepat dari beberapa nilai input yang beragam.
SKENARIO DATA • Menu DATA Pilih WHAT IF ANALYSIS Pilih SCENARIO MANAGER
Contoh Skenario Data (Kasus 1) • Misalnya saja Anda hendak menjual 3 unit barang yang harganya dalam mata uang USD. Keuntungan penjualan sekitar 10% dari total penjualan barang tersebut. Dan karena harga dalam USD, maka Anda harus merubahnya dulu kedalam nilai rupiah dengan acuan kurs sekitar 10.000.
• Sumber: http://social.technet.microsoft.com/wiki/contents/articles/29142.mengenalperintah-what-if-analysis-pada-excel-part-02-scenario-manager.aspx
Contoh Skenario Data (Kasus 1) • Namun bagaimana jika ternyata kurs menunjukan peningkatan sementara persentase laba Anda malah mengalami penurunan? atau kurs meningkat sekaligus laba meningkat juga? dan berbagai kemungkinan lainnya yang mungkin saja terjadi. • Disinilah kegunaan utama dari perintah Scenario Manager ini dimana Anda dapat membuat beberapa scenario tertentu dan kemudian melihat hasilnya dalam Sheet yang terpisah.
Contoh Skenario Data (Kasus 1) Opsi 1: • Kurs Naik menjadi 12.000 • Persentase Laba Naik menjadi 12% Opsi 2: • Kurs Naik menjadi 11.000 • Persentase Laba Naik menjadi 12% Opsi 3: • Kurs Turun menjadi 9.000 • Persentase Laba Naik menjadi 11%
Contoh Skenario Data (Kasus 1)
Contoh Skenario Data (Kasus 1) Untuk melihat komparasi output dari ketiga scenario tersebut, klik tombol Summary. Kemudian pada jendela Scenario Summary yang ditampilkan, ketikan alamat sel B8 pada bagian Result cells. Result cells ini adalah alamat sel yang menyimpan nilai output yang akan dikomparasi, dalam contoh ini yaitu sel nilai untuk Jumlah Laba (Rupiah).
VLOOKUP & HLOOKUP
• Fungsi : membaca suatu tabel dan mengambil nilai yang diinginkan pada tabel tersebut berdasarkan kunci yang diinginkan. • VLOOKUP() pembacaan tabel secara vertikal (dari atas ke bawah). • HLOOKUP pembacaan tabel secara horisontal (dari kiri ke kanan).
VLOOKUP & HLOOKUP • VLOOKUP() pembacaan tabel secara vertikal karena tabel tersusun secara vertikal seperti contoh berikut:
• HLOOKUP() pembacaan tabel secara horisontal karena tabel tersusun secara vertikal seperti contoh berikut:
VLOOKUP & HLOOKUP • Contoh penggunaan VLOOKUP() : • Klik Menu Formulas Insert Function Ketikkan VLOOKUP OK • Muncul :
CONTOH VLOOKUP
KOMBINASI FORMULA (Kasus 2) Sebuah perusahaan virtual bernama “eneng-eneng wae” menggaji karyawannya berdasar gender. Karyawan Pria mendapat gaji pokok sebesar Rp.1.000.000,- sedangkan karyawan wanita sebesar Rp. 750.000,-. Apabila karyawan pria menikah maka akan mendapatkan tunjangan sebesar Rp. 600.000,- sedangkan karyawan wanita sebesar Rp. 350.000,-. Untuk karyawan yang menduduki posisi “Supervisor” mendapatkan insentif sebesar Rp. 1.000.000,, posisi “Manajer” mendapatkan insentif sebesar Rp. 2.000.000,- dan posisi Direktur mendapat insentif Rp. 5.000.000,-. Bagi karyawan yang sudah menikah dan mempunyai anak mendapatkan tunjangan sebesar Rp. 350.000,- untuk anak pertama dan Rp 150.000,- untuk anak kedua dan selanjutnya. Sedangkan bagi karyawan yang berstatus “Duda” atau “Janda” dan mempunyai anak akan mendapatkan tunjangan sebesar Rp. 100.000,- untuk masing-masing anak. Dengan menggunakan fungsi IF dan VLOOK-UP atau HLOOK-UP dalam MS excel, buatkan tabel yang sesuai dengan cerita diatas.
Tabel Berdasarkan Kasus 2
Tabel Berdasarkan Kasus 2
Tugas Mahasiswa Buatlah tabel pada Ms. Excel dengan formula IF, VLOOKUP, HLOOKUP dan operasi matematika dasar untuk menentukan: 1. T. khusus 2. T. nikah 3. T. jabatan 4. T. anak 5. Total Gaji
