microsoft excel lanjutan - · pdf file•format data cell & validasi data...

T i m P e n g a j a r T I K - F I S I P

Microsoft 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

Materi Excel Lanjutan

• FUNGSI TANGGAL DAN WAKTU

FORMAT DATA CELL & VALIDASI DATA

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)

• FUNGSI TANGGAL DAN WAKTU

• Fungsi DATE dapat digantikan dengan merubah tipe data padaformat cell dengan date.

FORMAT DATA CELL & VALIDASI DATA

Fungsi : =DATE(year, month, day)

Kegunaan : Untuk menuliskan suatu data tanggal pada sel tertentu

• FUNGSI TEKS / STRING

FORMAT DATA CELL & VALIDASI DATA

Fungsi : =LEFT (teks, jumlah karakter yang diambil)

Kegunaan : Untuk mengambil teks sejumlah karakter tertentu dari sebelah kiri

Fungsi : =RIGHT (teks, jumlah karakter yang diambil)

Kegunaan : Untuk mengambil teks sejumlah karakter tertentu dari sebelah kanan

Fungsi : =MID (teks, start_number, number_char)

Kegunaan : Untuk mengambil teks dari kedudukan tertentu dengan sejumlahkarakter tertentu

• FUNGSI TEKS / STRING

FORMAT DATA CELL & VALIDASI DATA

Fungsi : =LOWER (teks)

Kegunaan : Mengubah teks atau string dari huruf kapital ke huruf kecil

Fungsi : =LEN(teks)

Kegunaan : Menghitung jumlah karakter dalam suatu teks/string

Fungsi : =UPPER (teks)

Kegunaan : Mengubah teks atau string dari huruf kecil ke huruf kapital

Ada 3 perintah yang dimuat dalam What-If-Analysis ini, yaitu:

• Scenario Manager - Untuk melihat beberapa hasil atau output dari beberapakemungkinan input yang berlainan. Perintah ini umum digunakan dalamanalisa bisnis atau analisa suatu project untuk melihat beragam kemungkinanperhitungan yang mungkin terjadi.

• Goal Seek - Untuk mencari berapa input atau nilai awal agar Andamendapatkan output atau nilai akhir yang diharapkan.

• Data Table - Untuk mencari nilai hasil secara cepat dari beberapa nilai input yang beragam.

SKENARIO DATA PADA WHAT IF ANALYSIS

• Menu DATA Pilih WHAT IF ANALYSIS PilihSCENARIO MANAGER

SKENARIO DATA

• 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 hargadalam USD, maka Anda harus merubahnya dulu kedalam nilai rupiah dengan acuan kurssekitar 10.000.

• Sumber: http://social.technet.microsoft.com/wiki/contents/articles/29142.mengenal-perintah-what-if-analysis-pada-excel-part-02-scenario-manager.aspx

Contoh Skenario Data (Kasus 1)

• Namun bagaimana jika ternyata kurs menunjukanpeningkatan sementara persentase laba Anda malahmengalami penurunan? atau kurs meningkat sekaligus labameningkat juga? dan berbagai kemungkinan lainnya yang mungkin saja terjadi.

• Disinilah kegunaan utama dari perintah Scenario Manager inidimana Anda dapat membuat beberapa scenario tertentudan 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 bagianResult cells. Result cells ini adalah alamat sel yang menyimpannilai output yang akan dikomparasi, dalam contoh ini yaitu selnilai untuk Jumlah Laba (Rupiah).

Contoh Skenario Data (Kasus 1)

VLOOKUP & HLOOKUP

• Fungsi : membaca suatu tabel dan mengambil nilaiyang diinginkan pada tabel tersebut berdasarkankunci yang diinginkan.

• VLOOKUP() pembacaan tabel secara vertikal (dariatas ke bawah).

• HLOOKUP pembacaan tabel secara horisontal(dari kiri ke kanan).

• VLOOKUP() pembacaan tabel secara vertikalkarena tabel tersusun secara vertikal seperti contohberikut:

• HLOOKUP() pembacaan tabel secara horisontalkarena tabel tersusun secara vertikal seperti contohberikut:

VLOOKUP & HLOOKUP

• Contoh penggunaan VLOOKUP() :

• Klik Menu Formulas Insert Function KetikkanVLOOKUP OK

• Muncul :

VLOOKUP & HLOOKUP

CONTOH VLOOKUP

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.

KOMBINASI FORMULA (Kasus 2)

Tabel Berdasarkan Kasus 2

Tabel Berdasarkan Kasus 2

Tugas MahasiswaBuatlah tabel pada Ms. Excel dengan formula IF, VLOOKUP, HLOOKUP danoperasi matematika dasar untuk menentukan:1. T. khusus2. T. nikah3. T. jabatan4. T. anak5. Total Gaji