BAB VIII
FIKTUR EXCEL TERAPAN
A.Menghitung Selisih Jam.
Perhatikan tabel
selisih jam berikut :
= C2-B2
Rumus diatas
akan berhasil tanpa eror apabola waktu selisuh lebih besar dari pada waktu
mulai, namun apa yang terjadi jika waktu selesai lebih kecil dari waktu mulai,
rumus diatas akan menghasilkan eror. Untuk menhilangkan eror tersebut maka
formulanya harus diubah menjadi seperti berikut:
=C2-B2+(B2>C2;1)
Selanjutnya copy
formula tersebut pada baris berikutnya, maka eror yang sebelumnya timbulny akan
hilanmg. Sedikit penjelasan dari formula diatas, setelah melakukan pengurangan
antara kolom C dengan B selanjutnya dilakukan pengecekan apakah kolom b
nilainya lebih besar dari kolom C, jika benar nilainya akan ditambah satu.
B.Menghitung Angsuran.
Simulasi berikut
ini adalah simulasi perhitungan angsuran pinjaman.besar pinjaman :
Rp.50.000.000 bunga/tahun:8% jangka waktu pijaman :36 bulan (3tahun) dari
informasi diatas akan dicari informasi berapa angsuran yang harus dibayar baik
pokok maupun bunga,besaerta jadwal angsuran sampai dengan pinjaman tersebut
selesaiberikut screenshoot hasil perhiungan jabwal angsuran pinjaman :
Untuk menghitung
angsuran perbulan (cell F7) gunakan formula berikut :
=ABS(PMT(F5/12;F6;F4))
Untunk mengisi
sisa pinjaman angsuran pertama digunakan formula:
=F10-D11
Formula di atas
mengurangi nilai sisa pinjaman sebelumnya dengan sisa pinjaman angsuran.
C. Membuat Rangking Otomatis
Perhatikan
gambar berikut!
Pada gambar di
atas yang harus diisi adalah kolom Rank.
Untuk mengisi
kolom Rank :
1. Nilai
Bahasa Inggris : 3. Nilai Jumlah Nilai :
=RANK(D3;inggris;0) =RANK(F3;jml_nilai;0)
2. Nilai Tea Potensi Akademik : 4.
Nilai Rata-rata :
=RANK(E3;tpa;0) =RANK(G3;rata_rata;0)
Catatan :
Pada rumus di
atas range diberi nama, masing-masing Inggris, tpa, jml_nilai dan rata_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 contih), gunakan 0, namun jika data sudah
diurutkan gunakan 1.
D. Referensi Cell dengan VLOOKUP
Selama ini untuk
mengisi dengan fungsi logika digunakan fungsi if, fungsi if tersebut cukup
berguna jika pilihan yang tersedia relatif sedikit, namun fungsi if akan
merepotkan jika pilihan yang ada sangat banyak, akibat formula Anda menggunakan
if akan sangat panjang, kelemahan tersebut bisa diantisipasi dengan fungsi
referensi cell yaitu VLOOKUP. Untuk memahami fungsi VLOOKUP ada baiknya Anda
perhatikan lembar kerja di bawah ini :
Untuk mengerjakan
lembar kerja di atas buatlah tiga buah tabel referensi seperti tampak pada
gambar berikut :
Berikan nama
untuk ketiga tabel tersebut dengan insert name define, tabel pertama diberi
nama merk, harga, dan tipe.
Tugas Anda harus
mengisi kolom merek, tipe, tahun keluar, harga dasar, target harga jual,
under/upper estimate, dan grafik.
1. Mengisi
Merek Mobil
Gunakan formula
berikut :
=VLOOKUP(LEFT(C6;1)MEREK;2;FALSE)
Formula di atas
mengambil satu karakte sebelah dari kolom kode mobil dengan fungsi LEFT yang
merupakan bagian dari fungsi text, dan membandingkannya dengan tabel merek.
Pada tabel merek
kolom merek berada pada urutan ke-2, karena kodenyatidak disortir atau
diurutkan maka pada penutup vlookup up diberikan kondisi FALSE
2. Mengisi
Tipe
Gunakan formula
berikut untuk mengisi tipe mobil :
=VLOOKUP
(MID(C6;3;3);tipe;2;FALSE)
Formula di atas
membandingkan tiga karakter di tengah-tengah dengan fungsi MID, dan
membandingkannya dengan tabel tipe, di mana kolom tipe berada pada kolom ke-2,
dan diberikan nilai FALSE karena tabel tipe kode tidak disortir atau diurutkan.
3. Mengisi
Tahun Keluar
Untuk mengisi
harga pokok kendaran gunakan formula :
;IF(MID(C6;7;2)=”093 ;”20093;”20103)))
4. Mengisi
Harga Dasar
Untuk mengisi
harga poko kendaraan gunakan formula :
=VLOOKUP(CONCATENATE(LEFT(C6;1);MID(C6;3;3));harga;2;FALSE)
Fungsi diatas
untuk mencocokan kan kode-kode hargadengan tabel harga,dimana kode harga
menggabungkan karakter ke1 dari kode mobile dan karakter ke3 dan 3karakter
selanjutnya dengan MID menggunakan fungsi text CONCATENATE,dan membandingkan
dengan tabel hargaa dan mengambil nilai kolom harga dikolom kedua,karena kode
harga tidak disortir maka gunakan nilai FALSE.
5. Mengisi
Target Harga Jual.
=G6+(G6*VLOOKUP(LEFT(C6;1);merek:3:FALSE))
Target
harga jual merupakan laba yang diinginkan dikali dengan harga dasar/pokok,
besarnya laba yang diinginkan ada pada tabel merek,untuk diambil setiap laba
masing-masing merek kemudian dikali dengan harga dasar dan hasilnya ditambahkan
dengan harga dasar. Misal untuk honda maka formulanya :
250.000.000+(250.000.000*10%), dari rumusan diatas maka jadilah formulanya
nampak pada formula menghitung target harga jual.
6. Mengisi
Under/Upper Estimate.
Gunakan formula
berikut:
=((IG-H6)/IG)*100
Under/upper
estimate merupakan selisih kurang atau selisih lebih antara harga 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(G12<0;REPT(“n”:-ROUND(J12*10;0));REPT(“n”;ROUND(J12*10;0)))
Formula diatas
mengecek jika nilainya minus (<0) maka membuat huruf “n” diulang-ulang
dengan fungsi REPT sebanyak kolom J (dibulatkan dengan ROUND) dikali 10, namun
sebelum fungsi REPT diberi tanda – (minus), dan jika positif (<0) maka sama
dengan penjelasan diatas namun fungsi REPT-nya tanpa tanda minus di depannya
8. Mengubah
Menjadi Grafik
Untuk mengubah
warna karakter di kolom K dengan warna merah jika kolom J nilainya negatif dan
warnanya kuning jika nilainya positif maka langkah- langkahnya :
a. Ubah
jenis huruf menjadi wingding dan warna menjadi kuning
b. Kita
ubah yang nilainya negatif menjadi berwarna merah, tempatkan pada Cell K6 klik
Conditional Formating pada ribbon Home, kemudian klik Manage Rule.
c. Selanjutnya
akan tampil akan tampil gambar di bawahm untuk membuat rule baru klik New Rule.
d. Setelah
keluar pilihan rule, Anda pilih rule Use Foe,ula to Determine wich Cell to
Format (lihat gambar). Selanjutnya pada lotal formula isi =J6<0 (artinya
jika kolom J6 bernilai minus), maka klik tombol Format.
e. Pada
lotak dialog Format Cell ubah warnanya menjadi merah, klik OK dan tutup semua
jendela.
f. Copy
formula di K^ ke baris berikutnya
.
E. Menghitung Gaji Karyawan
Studi kasus kali
ini akan menghitung gaji setiap karyawan pada suatu perusahaan, dari daftar
gaji tersebut akan menghitung jumlah jam kerja masing-masing karyawan dan
menghitung lembur dari masing karyawan serta menentukan besarnya penghasilan
yang diterima, baik dari gaji pokok, insentif, dan pengurangan atas potongan
atas potongan, untuk jelasnya perhatikan gambar di bawah ini!
Sedangkan untuk
pengisian kolom diebrikan bebrapa ketentuan seperti terlihat pada gambar di
bawah ini
Tahap-tahap
pengisian adalah sebagai berikut!
Sebagai langkah
awal Anda beri nama pada area berikut:
1. Pengisian
Kolom Jumlah Jam Kerja.
Pada cell 18buat
formula berikut:
=SUM(C8:H8)
Formula diatas
digunakan untuk menjumlahkan jam kerja dari kolom hari senin sampai hari
sabtu,selanjutnya copy paste pada baris berikutnya.
2. Pengisian
Kolom Jumlah Jam Lembur.
Pada cell J8 buat
formula berikut:
=IF(18>(jam_normal*6);18-(jam_normal*6);0)
Formula diatas
untuk melakukan pengecekan dengan fungsi if,jika jumlah jam kerja (18) lebih
dari 48 (jam kerja normal *6hari).
3. Pengisian
Upah Pokok.
Untuk mengisi
upah pokok yaitu perkalian antara kolom jumlah jam kerja dengan upah per jam,
maka formulanya adalah sebagai berikut:
=18*upah
_per_jam
Untuk
menambahkan simbol rupiah didepan angka, klik menu format cell, kemudian pada
tab general pilih accounting pada bagian bagian category, jika tidak ada
lambang rupiah lakukan perubahan pada seting regional komputer anda.
4. Pengisian
Upah Lembur.
Formulanya:
=IF(J8>0;J8*lembur_per_jam;0)
Untuk
menimbulkan lambang rupiah, silakan lakukan format cell seperti pada langkah ke
2.
5. Pengisian
Uang Maka.
Formulanya :
=(COUNTIF(C8:H8;”>03)*makan_pe_hari).
6. Pengisian
Uang Insentif.
Maka formulanya:
=IF(COUNTIF(C8:H8;”>03)=6:insensitif_mingguan:0).
Untuk
menampilkan format rupiah caranya seperti pada langkah ke-2.
7. Pengisian
Gaji Kotor.
Gaji kotor
merupakan penjumlahan dari upah pokok, upah lembur, uang makan, dan insentif,
maka formulanya dalah :
=SUM(K8:N8)
Untuk
menampilkan formula rupiah caranya seperti pada langkah ke2.
8. Pengisian
Pajak.
Pajak ditentukan
dengan tarif 10% dari gaji kotor maka formulanya:
=pajak*08
Untuk
menampilkan format rupiah caranya seperti pada langkah ke2.
9. Pengisian
Gaji Bersih.
Gaji kotor
diperoleh dari pengurangan antara gaji kotor dengan pajak, maka formulanya
=08-P8
Untuk
menampilkan format rupiah caranya seperti pada langkah ke2.
10. Menghilangkan
“0 jam” Pada Kolom Jumlah jam Lembur.
Pada saat anda
mengisi kolom jumlah jam lembur, terdapat karyawan yang memiliki 0 jam,
sedangkan pada gambar diatas tidak tampak 0 jam, untuk melakukannya kita akan meberikan
CONDITIONAL FORMATING, untuk itu blok kolom jumlah lembur, pilih menu
format+> conditional formating, maka akan tampak gambar dibawah ini.
.
11.Menghilangkan
“RP ” pada kolom upah lembur.
Untuk
menghilangkan Rp 0 pada kolom upah lembur, blok kolom upah lembur dan lakakukan
conditional formating 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,
dan max.
soal :
1.Fungsi
yang digunakan menghasilkan TRUE bila argumentasinya BENAR dan FALSE...
A.
=AND(logical 1, logical
2)
B. =OR
(logical 1, logical 2)
C.
=UPPER (text).
D.=PROPPER(text).
E.=LEN(text).
2.rumus yang digunakan untuk
menghitung pokok pinjaman
(principal
paymend) adalah..
A.
Prince.
B.
Product.
C.
ABS
D.
PMT.
E.
PPMT.
3.Untuk
mengambil karakter pada bagian sebelah kiri dari suatu teks, digunakan fungsi
teks...
A.
Left.
B.
Mid.
C.
Right.
D.
Len.
E.
UPPER.
4. jika anda memasukan teks dimana
angka atau logika yang diharapkan, atau anda memasukan range dimana diperlukan
sel tunggal , maka kode kesalahan yang muncul adalah...
- #DIV/0!
- #N/A
- #NAME?
- #NULL!#n/a
- #name?
5. untuk menghitung angsuran per
bulan digunakan rumus,...
- ABS.
- PMT.
- PPMT.
- Prince.
- Product.
6.anda
memasukan rumus yang berisi teks yang tdk dikenal Ms.Excel. kode kesalahan yang
muncul adalah...
- #N/A
- #NULL!
- #NAME?
- #NUM!
- REF!
7.kode
kesalahan jika anda mungkin menghapus sel yang diacu oleh sel lain dalam
worksheet adalah ...
- #REFI
- #N/A
- #NAME?
- #NULL!
- #VALUE!
8.untuk
menjumlahkan data pada suatu baris atau kolom tertentu secara otomatis dapat
digunakan ikon...
- Autosum.
- Save.
- Font
size.
- Print.
- Font.
8.kita
dapat menghitung penjumlahan data dalam satu range dengan menggunakan fungsi...
- Max
- Min.
- Count
- Round.
- Sum.
9.fungsi untuk menghitung angsuran
bunga(interest paymennt) yaitu,..
- ABS
- IPMT
- PMT
- Prince
- Product
10.fungsi yang digunakan untuk
mengambil sebagian karakter bagian tengah dari satu teks yaitu...
- Left
- Mid
- Len
- Upper
- Right
11.tombaol (*) memilik fungsi...
- Penjumlahan
- Pembagian
- Perkalian
- Pengurangan
- Perpangkatan
12.untuk menjumlahkan data pada
suatu baris atau kolom tertentu secara otomatis dapat menggunakan ikon...
- Autosum
- Save
- Font
- Font
size
- Print
13. Tanda untuk mengabsolutkan cell
yaitu..
- &
- *
- $
- @
- #
14.fungsi yang digunakan untuk
mengambil sejumlah karakter dari sebelah kanan teks yaitu...
- Min
- Count
- Round
- Left
- Right
15. Fungsi yang digunakan untuk
mencari rangking suatu bilangan dari sekumpulan data yaitu...
- Rank d.
int
- Round e.
count
Tidak ada komentar:
Posting Komentar