BAB 8 RINGKASAN DAN SOAL 15 "DINA ANDIKA"

12.56 |

Bab 8.FITUR EXCEL TERAPAN

A. Menghitung Selisih Jam

=C2-B2

Rumus di atas akan berhasil tanpa error apabila waktu selesai lebih besar dari pada waktu mulai,namun apa yang terjadi jika waktu selesai lebih kecil dari waktu mulai,rumus di atas akan menghasilkan error. Untuk menghilangkan error tersebut maka formulanya harus diubah menjadi seperti berikut:

=C2-B2+IF(B2>C2;1)

Selanjutnya copy formula tersebut pada baris berikutnya,maka error yang sebelumnya timbul akan hilang.

B. Menghitung Angsuran

Simulasi berikut ini adalah simulasi perhitungan angsuran pinjaman.

Besar pinjaman : Rp.50.000.000 Bunga/tahun : 8%

Jangka waktu pinjaman : 36 bulan (3 tahun). Dari informasi diatas akan dicari informasi beberapa angsuran yang harus dibayar baik pokok mauoun bunga, beserta jadwal angsuran sampai dengan pinjaman tersebut selesai.

Untuk menghitung angsuran per bulan (Cell F7) gunakan formula berikut:

=ABS(PMT(F5/12;F6;F4))

Sedangkan untuk mengisi kolom pokok pinjaman diisi dengan formula:

=ABS(PPMT($F$512;A11;$F$6;$F$4)

Sedangkan untuk mengisi kolom angsuran diisi dengan:

=ABS(IPMT($F$5/12;A11;$F$6;$F$4))

Sedangkan untuk mengisi kolom besarnya angsuran digunakan formula:

=ABS(D12+E12)

Untuk mengisi sisa pinjaman angsuran pertama digunakan formula:

=F10-D11

C. Membuat Rangking Otomatis

Perhatikan gambar berikut.

Pada gambar diatas yang harus diisi adalah kolom rank.

Untuk mengisi kolom rank:

1.Nilai Bahasa Inggris :

=RANK(D3;inggris;0)

2. Nilai Tes Potensi Akademik :

=RANK(E3;tpa;0)

3.Nilai Jumlah Nilai :

=RANK9F3;jml_nilai;0)

4. nilai rata-rata:

=RANK(G3;rata_rata;0)

Catatan:

Pada rumus di atas rabge diberi nama, masing-masimg inggris, tpa,jml_nilai dan rat_rata. Untuk membuat nama rang gunakan define range. Bentuk umum formula rank adalah:

=RANK (nomor;range_cell;order).

Untuk bagian order :

Jika data tidak diurutkan (apa adanya seperti pada contoh), gunakan 0, namun jika data sudah diurutkan gunakan 1.

D. Referensi Cell dengan VLOOKUP

Selama ini untuk mengisi dengan fungsi logika digunakan fungsi if, fungsai if tersebut cukup berguna jika pilihan yang tersedia relaatif sedikit, namaun fungsi if akan merepotkan jika pilihan yang ada sangat banyak, akibatnya formula anda menggunakan if akan sangat panjang, kelemahan tersebut basa diantisipasi dengan fungsi referensi cell yaitu VLOOKUP. Untuk memahami fungsi VLOOKUP ada baiknya anda perhatikan lembar kerja dibawah ini :

1. Table harga 2. Tabel merek

3.Tabel tipe mobil

1. Mengisi merek mobil

Gunakan fungsi formula berikut :

=VLOOKUP(LEFTT(C6;1);merek;2;FALSE)

Formula diatas mengambil satu karakter sebelah dari kolom kode dengan fungsi LEFTyang merupakan bagi dari fungsi text, dan membandingkannya dengan table merek.

2. Mengisi tipe

Gunakan formula berikut :

=VLOOKUP(MID9C6;3;3);tipe;2;FALSE)

Formula diatas membandingkan tiga karakter di tengah-tangah dengan formula MID,dan membandingkannya dengan table tipe, dimana kolom tipe berada pada kolom ke-2,dan diberikan nilai FALSE karna table tipe kode tidak disortir atau diurutkan.

3. Mengisi tahun keluar

Gunakan formua berikut ;

=IF(MID(C6;7;2)=”073;20073”;IF(MID(C6;7;2)=”083;”20083;IF(MID(C6;7;2)=’093;”20093;”20103)))

4. Mengisi harga dasar

Gunakan formula berikut :

=VLOOKUP(COUNTENATE(LEFT(C6;1);MID(C6;3;3);harga;2;FALSE)

5. Mengisi target harga jual

Gunakan formula berikut:

=G6+(G6*VLOOKUP(LEFT(C6;1);merek;3;FALSE)

Missal untuk Honda mak formulanya :250.000.000+(250.000.000*10%), dari rumusan diatas maka jadilah formulanya seperti tampak pada formula menghitung target harga jual.

6. Mengisi under / upper estimate

Gunakan formula berikut:

=((I6-H6)/I6)*100

UNDER / UPPER merupakan selisih kurang atau selisih lebih antara harga jual yang sesungguhnya dengan target harga jual, jika nilainya minus maka dibawah target dan jika plus sebaliknya.

7. Mengisi grafik

Gunakan formula berikut:

=IF(J12<0;REPT(“n”;ROUND(J12*10;0));REPT9”n”;ROUND(J12*10;0)))

Mengubah menjadi grafik

Langak-langkah berikut ;

1. Ubah jenis huruf menjadi wingding dan warna menjadi kuning

2. Kita ubah nilai negative menjadi warna merah, tempatkan pada Cell K6 klik Counditional Formating pada ribbon home, kemudian klik manage rule.

3. Selanjutnya akan tampil gambar dibawah ini , untuk membuat rule baru klik NEW rule.

4. Setelah keluar pilihan rule, anda pilih rule USE FORMULA TO DETERMINE WICH CELL TO FORMAT , selanjutnya pada kotak formula isi =J6<(artinya jika kolom bernilai minus), maka klik tombol format.

5. Pada kotak dialog format cell ubah warna manjadi merah, klik OK dan tutup semua jendela.

6. Copy formula ke K6 ke baris berikutnya.

E. Menghitung gaji karyawan

Tahap-tahap pengisian sebagai berikut ;

1. Pengisian kolom jumlah jam kerja

=SUM(C8:H8)

2. Pengisian kolom jumlah jan lembur

=IF(I8>(jam_normal*6);0)

3. Pengisian upah pokok

=I8*upah_per_jam

4. Pengisian upah lembur

=IF(J8>0;J8*lembur_per_jam;0)

5. Pengisian uang makan

=(COUNTIF(C8:H8;”>03)*makan-per_hari)

6. Pangisian uang insentif

=IF(COUNTIF(C8:H8;”>03)=6;insentif_mingguan;0)

7. Pengisian gaji kotor

=SUM(K8:N80

8. Pengisian pajak

=pajak*O8

9. Pengisian gaji bersih

=O8-P8

10. Menghitungkan “0 jam”pada kolom jumlah jam lembur

Pada saat anda mengisi kolom jumlah jam lembur, terdapat karyawan yang memiliki yang memiliki 0 jam, sedangkan pada gambar diatas tidak tampak 0 jam, untuk melakukannya kita akan memberikan COUNDITIONAL FORMATING, untuk itu blok kolom jumlah jam lembur, pilih menu format

Counditiona formatting, maka akan tampak gambar dibawah ini.

11. Menghilangkan “RP 0” pada kolom upah gaji

Untuk menghilangkan Rp 0 pada kolom upah lembur, blok kolom upah lenbur dan lakukan counditional formatting seperti pada langkah 10.

12. Mengisi baris total, rata-rata, terbesar dan terkecil

Untuk mengisi baris total, rata-rata, terkecil dan terbesar gunakan fungsi SUM,AVERAGE,MIN MAX.

PERTANYAAN :

1.Tanda yang digunakan untuk mengabsolutkan cell yaitu….

a. & b.* c.$ d.# e. @

2.Untuk menghitung angsuran per bulan digunakan rumus….

a.ABS b. PMT c.PPMT d.Prince e.Product

3.Rumus yang digunakan untuk menghitung pokok pinjaman (principal payment) adalah….

a.Prince b.Product c.ABS d.PMT e.PPMT

4.Fungsi yang digunakan untuk mengambil sebagian karakter bagian tengah dari suatu teks yaitu….

a.Left b.Mid c.Right d.Len e.Upper

5.Tombol (/) memiliki fungsi….

a.Penjumlahan b.Pengurangan c.Perkalian d.Pembagian e.Perpangkatan

6.Kita dapat menghitung penjumlahan data dalam satu range dengan menggunakan fungsi….

a.Max b.Min c. Count d.Round e.Left

7.Fungsi yang digunakan untuk mengambil sejumlah karakter dari sebelah kanan teks adalah…..

a.Right b.Min c.Count d.Round e.Left

8.Tombol asterisk (*) memiliki fungsi….

a.Penjumlahan b.Pengurangan c.Perkalian d.Pembagian e.Perpangkatan

9.Kode kesalahan jika anda mungkin menghapus sel yang diacu oleh sel lain dalam worksheet adalah….

a.REF! b.#N/A c.#NAME? d.#NULL! E.#VALUE!

10.Untuk menjumlahkan data pada suatu baris atau kolom tertentu secara otomatis dapat menggunakan icon….

a.Autosum b.Save c.Font Size d.Print e.Font

11.Fungsi yang digunakan untuk mencari suatu nilai dari suatu tabelpencarian pada baris yang telah ditentukan dengan metode pencarian mendatar (horizontal),yaitu….

a.AND b.VLOOKUP c.HLOOKUP d.OR UPPER

12.Fungsi yang digunakan untuk mencari suatu nilai dari suatu tabel pencarian pada baris yang telah ditentukan dengan metode pencarian menurun (vertical) yaitu….

a.AND b.VLOOKUP c.HLOOKUP d.OR e.UPPER

13.Untuk menghitung banyak nya data yang terdapat pada range tertentu,berdasarkan criteria tertentu atau kondisi tertentu digunakan rumus….

a.=CONTIF b. =DMAX c.=DAVG d.=DMIN e.=DAVERAGE

14.Fungsi yang digunakan untuk menentukan standar deviasi dari suatu data (range) yaitu….

a.Var(…) b.STDEV(…) c.Sum(…) d.Left(…) e.MID

15.Fungsi yang digunakan untuk mencari raking suatu bilangan diantara sekimpulan data yaitu….

a.Count b.Rank c.Sum d.Round e.Int