TIK Group: BAB 7 FUNGSI EXCEL TERAPAN DAN BAB 8 FITUR EXCEL TERAPAN

BAB 7 FUNGSI EXCEL TERAPAN

A. Fungsi Terapan

1. Fungsi AND

Fungsi AND digunakan untuk menguji seluruh argumen yang dimasukkan ke dalam fungsi. Jika seluruh argumen bernilai benar maka fungsi AND akan menghasilkan nilai logika benar (TRUE), sedangkan apabila ada salah satu argumen atau lebih yang bernilai salah maka fungsi AND akan menghasilkan nilai logika salah (FALSE). Bentuk penulisan fungsi AND:

=AND(logical1;[logical2];…)

2. Fungsi AVERAGE

Fungsi AVERAGE digunakan untuk menghitung rata-rata satu data angka atau lebih yang diketikkan langsung dalam fungsi atau data angka yang terdapat dalam range. Bentuk penulisan fungsi AVERAGE:

=AVERAGE(number1;[number2];…)

3. Fungsi COUNTA

Fungsi COUNTA digunakan untuk menghitung jumlah sel berisi data. Bentuk penulisan fungsi COUNTA:

=COUNTA(value1;[value2];…)

4. Fungsi COUNTIF

Fungsi COUNTIF digunakan untuk menghitung jumlah sel alam range yang sesuai dengan kriteria yang ditentukan. Bentuk penulisan fungsi COUNTIF:

=COUNTIF(range;criteria)

5. Fungsi EXACT

Fungsi EXACT digunakan untuk membandingkan dua teks string. Jika kedua teks yang dibandingkan sama, maka akan menghailkan nilai TRUE, sedangkan apabila kedua teks yang dibandingkan tidak sama, maka akan menghasilkan nilai FALSE. Bentuk penulisan fungsi EXACT:

=EXACT(text1;text2)

6. Fungsi ISTEXT

Fungsi ISTEXT digunakan untuk mendeteksi apakah suatu sel berisi nilai teks. Bentu penulisan fungsi ISTEXT:

=ISTEXT(value)

7. Fungsi LOWER

Fungsi LOWER digunakan untuk mengonversi seluruh teks menjadi huruf kecil (lower case). Bentuk penulisan fungsi LOWER:

=LOWER(text)

8. Fungsi NA

Fungsi NA merupakan fungsi yang menghasilkan nilai #NA, yang berarti tidak ada nilai yang sesuai dengan nilai yang dicari. Bentuk penulisan fungsi NA:

=NA( )

9. Fungsi OFFSET

Fungsi OFFSET digunakan untuk menampilkan data yang terdapat dalam suatu range sekian baris atau sekian kolom yang disebutkan jaraknya dari range asalnya. Bentuk penulisan fungsi OFFSET:

=OFFSET(reference;rows;cols;[height];[width])

10. Fungsi OR

Fungsi OR digunakan untuk menguji seluruh argumen yang dimasukkan ke dalam fungsi. Jika seluruh argumen bernilai benar maka fungsi OR akan menghasilkan nilai logika benar (TRUE), sedangkan apabila ada salah satu argumen atau lebih yang bernilai salah maka fungsi OR akan menghasilkan nilai logika salah (FALSE). Bentuk penulisan fungsi AND:

=OR(logical1;[logical2];…)

11. Fungsi PROPER

Fungsi PROPER digunakan untuk mengubah sekumpulan kata menjadi huruf kapital pada awal setiap kata dan mengubah karakter selanjutnya menjadi huruf kecil (title case). Bentuk penulisan fungsi PROPER:

=PROPER(text)

BAB 8 FITUR EXCEL TERAPAN

A. Menghitung Selisih Jam

Perhatikan tabel selisih jam berikut

=C2-B2

Rumus diatas akan berhasil tanpa error apabila waktu selesai lebih besar daripada waktu mulai.

Bila nilai waktu selesai lebih kecil dari waktu mulai akan mnghasilkan error. Untuk menghilangkan error tersebut maka formulanya harus diubah menjadi:

=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 berapa angsuran yang harus dibayar baik pokok maupun bunga, beserta jadwal angsuran sampai dengan pinjaman tersebut selesai. Berikut screenshoot hasil perhitungan jadwal angsuran pinjaman :

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

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

Rumus diatas membagi bunga (F4) menjadi perbulan dan nilainya diabsolutkan (mengabaikan nilai negatif).

Untuk mengisi kolom pokok pinjaman diisi dengan formula:

=ABS(PPMT($F$5/12;A11;$F46;$F$4))

Fungsi PPMT digunakan untuk menghitung pokok pinjaman (principal payment), tanda $ dalam alamat cell digunakan untuk mengabsolutkan cell, agar memudahkan untuk dicopy.

Untuk mengisi kolom angsuran diisi dengan:

=ABS(D12+E12)

Untuk mengisi sisa pinjaman angsuran pertama, dengan cara mengurangi nilai sisa pinjaman sebelumnya dengan sisa pinjaman angsuran, digunakan formula:

=F10-D11

C. Membuat Rangking Otomatis

Perhatikan gambar dibawah ini :

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 :
=RANK(F3;jml_nilai;0)

4. Nilai Rata-rata :
=RANK(G3;rata_rata;0)

Catatan:
Pada rumus diatas 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, 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, akibatnya formula anda mengguna if akan sangat panjang, kelemahan tersebut bisa diantisipasi dengan fungsi referensi cell yaitu VLOOKUP.

Untuk memahami fungsi VLOOKUP ada baiknya anda perhatikan lembar kerja dibawah ini :

Untuk mengerjakan lembar kerja diatas buatlah tiga buah tabel referensi seperti tampak pada gambar berikut :

1. Tabel Harga

2. Tabel Merek

3. Tabel Tipe Mobil

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 diatas mengambil satu karakter 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 kode nya tidak di sortir/diurut 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 diatas membandingkan 3 karakter ditengah-tengah dengan fungsi MID, dan membandingkannya dengan tabel tipe, dimana kolom tipe berada pada kolom ke=2, dan diberikan nilai FALSE karena tabel tipe kode tidak di sortir / diurut.

3. Mengisi Tahun Keluar

Untuk mengisi tahun keluar gunakan formula :

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

Formula diatas, menggabungkan fungsi IF dan fungsi MID dimana fungsi MID hanya untuk mengambil dua karakter dari kode mobil.

4. Mengisi Harga Dasar

Untuk mengisi harga pokok kendaraan gunakan formula :

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

Fungsi diatas untuk mencocokan kode kode harga dengan tabel harga, dimana kode harga menggabungkan karakter ke-1 dari kode mobil dan karakter ke 3 dan 3 karekter selanjutnya dengan MID menggunakan fungsi text CONCATENATE, dan membandingkan dengan tabel harga yang dan mengambil nilai kolom harga di kolom kedua, karena kode harga tidak disortir maka gunakan nilai FALSE.

5. Mengisi Target Harga Jual

Gunakan Formula berikut :

=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 itu 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 seperti tampak pada formula menghitung target harga jual.

6. Mengisi Under/Upper Estimate

Gunakan Formula berikut :

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

Under/Upper Estimate 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));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 merubah warna karakter di kolom K dengan warna merah jika kolom J nilainya negatif dan warnanya kuningan jika nilainya Positif, maka langkah-langkahnya :

1. Ubah jenis huruf menjadi wingding dan warna menjadi kuning.

2. Kita ubah yang nilainya negatif menjadi berwarna merah, tempatkan pada Cell K6 klik Conditional Formating pada ribbon Home, kemudian klik manage Rule.

3. Selanjutnya akan tampil gambar dibawah, untuk membuat rule baru klik New Rule.

4. Setelah keluar pilihan rule anda pilih rule Use Formula to Determine wich Cell to Format

(lihat Gambar), slenjutnya pada kotak formula isi =J6<0 (artinya jika kolom J6 bernilai minus), maka klik tombol Format.

5. Pada kotak dialog Format Cell ubah warnanya menjadi merah, klik OK dan tutup semua jendela.

6. Copy Formula di K6 ke baris berikutnya, maka hasilnya akan tampak :

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, untuk jelasnya perhatikan gambar dibawah ini!

http://i864.photobucket.com/albums/ab201/latihanexcel/hitung_gaji2.png

Sedangkan untuk pengisian kolom diberikan beberapa ketentuan seperti terlihat pada gambar dibawah ini :

Tahap-tahap pengisian adalah sebagai berikut!

Sebagai langkah awal anda beri nama pada area cell berikut :

Nama Area	Cell
lembur_per_jam	I24
upah_per_jam	I25
makan_per_hari	I26
insentif_mingguan	I27
jam_normal	I28
pajak	I29

1. Pengisian Kolom Jumlah Jam Kerja
Pada cell I8 buat formula berikut :
=SUM(C8:H8)
Formula diatas 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(I8>(jam_normal*6);I8-(jam_normal*6);0)
Formula diatas melakukan pengecekan dengan fungsi if, jika jumlah jam kerja (I8) lebih dari 48 (jam kerja normal x 6 hari) berarti terdapat lembur untuk mencarinya maka jumlah jam kerja dikurangi hasil perkalian antara jam kerja normal dengan 6 hari kerja, jika jumlah jam kerja lebih kecil dari 48 maka tidak ada lembur atau nol.
Setelah terisi lakukan format cell agar dibelakang angka hasil perhitungan terdapat satuan jam, caranya sebagai berikut :
Klik menu Format –> cell, pilih tab General dan pada bagian Caption pilih custom dan pada bagian tipe ketik ## “jam”, lihat gambar diatas!

3. Pengisian Upah Pokok
Untuk mengisi upah pokok yaitu perkalian antara kolom jumlah jam kerja dengan upah per jam, maka formulanya adalah sebagai berikut :
=I8*upah_per_jam
Untuk menambahkan simbol rupiah di depan angka, klik menu format cell, kemudian pada tab general pilih accounting pada bagian Catgeory, jika tidak ada lambang rupiah lakukan perubahan pada seting regional komputer anda.

4. Pengisian Upah Lembur
Untuk mengisi lembur terlebih dahulu mengecek apakah karyawan yang bersangkutan memiliki jam lembur atau tidak ? jika ya lakukan perkalian jumlah jam lembur dengan upah lembur per jam nya jika tidak maka isi dengan nol, dari ketentuan ditersebut maka formulanya adalah :
=IF(J8>0;J8*lembur_per_jam;0)
Untuk menimbulkan lambang rupiah, silahkan lakukan format cell seperti pada langkah ke-2.

5. Pengisian Uang Makan
ketentuan untuk mengisi uang makan adalah uang makan akan dibayar hanya jika karyawan yang bersangkutan hadir atau jam kerja lebih dari nol, jika jam kerja pada hari yang bersangkutan adalah nol artinya tidak hadir berkerja dan tidak memperoleh uang makan harian, untuk itu dalam satu minggu dihitung berapa kali karyawan yang bersangkutan hadir dengan menggunakan fungsi COUNTIF dengan kriteria jam kerja lebih dari nol, maka formulanya adalah :
=(COUNTIF(C8:H8;”>03″)*makan_per_hari)

6. Pengisian Uang Insentif
Uang insentif hanya diberikan kepada karyawan yang tidak pernah absen dalam satu minggu, jika dalam satu minggu (6 hari) full terus bekerja (jam kerja harian tidak nol) maka dia berhak memperoleh uang insentif, untuk itu terlebih dahulu harus dicek dengan menghitung banyaknya hari dengan kriteria jam kerja lebih dari 0 dengan fungsi COUNTIF, maka formulanya:
=IF(COUNTIF(C8:H8;”>03″)=6;insentif_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 formulaynya adalah :
=SUM(K8:N8)
Untuk menampilkan format rupiah caranya seperti pada langkah ke-2.

8. Pengisian Pajak
Pajak ditentukan dengan tarif 10% dari gaji kotor maka formulanya adalah :
=pajak*O8
Untuk menampilkan format rupiah caranya seperti pada langkah ke-2.

9. Pengisian Gaji Bersih
Gaji kotor diperoleh dari pengurangan antara gaji kotor dengan pajak, maka formulanya
=O8-P8
Untuk menampilkan format rupiah caranya seperti pada langkah ke-2.

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 memberikan CONDITIONAL FORMATING, untuk itu blok kolom jumlah jam lembur, pilih menu Format -> Conditional Formating, maka akan tampak gambar dibawah ini :

Pada gambar diatas pada kotak drop down pertama pilih Cell Value Is, kemudian pada drop down kedua pilih less then, dan pada kotak isian ketiga isi dengan angka 1, kemudian klik tombol format maka akan tampak gambar dibawah ini :

Pada gambar diatas klik color pilih wana yang sesuai dengan warna latar belakang kolom Jumlah jam lembur dalam hal ini warna hijau. kemudian klik tombol ok dua kali sampai kedua kotak dialog hilang, dan perhatikan hasilnya nilai 0 jam dihilangkan !
Maksud langkah-langkah diatas adalah jika nilai kolom jumlah jam lembur kurang dari 1 maka isi cell nya diberi warna yang sama dengan warna latar belakang cell, sehingga seolah-olah nilai 0 jam hilang.

11. Menghilangkan “Rp 0” Pada Kolom Upah Lembur
Untuk menghilangkan Rp 0 pada kolom upah lembur, blok kolom upah lembur dan lakukan 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 EVALUASI BAB 7 & BAB 8

1. Untuk mendeteksi apakah suatu sel berisi nilai teks merupakan Fungsi…

a. COUNTIF d. EXACT

b. AVERAGE e. ISTEXT

c. AND

2. Untuk menampilkan data yang terdapat dalam suatu range sekian baris atau sekian kolom yang disebutkan jaraknya dari range asalnya merupakan Fungsi…

a. COUNTIF d. EXACT

b. AVERAGE e. ISTEXT

c. AND

3. Reference adalah…

a. Jumlah baris ke bawah atau ke atas

b. Jumlah kolom ke kanan atau ke kiri

c. Alamat sel dimana Anda akan mengutip data

d. Tinggi range yang ditanyakan dengan jumlah baris yang di perlukan untuk menampung data

e. Lebar range yang ditanyakan dengan jumlah kolom yang di perlukan untuk menampung data offset

4. Bentuk penulisan fungsi OR adalah…

a. =OR(text)

b. =OR(number1;[number2];…)

c. =OR(logical1;[logical2];…)

d. =OR(Value1;[value2];…)

e. =OR(value)

5. Bentuk penulisan fungsi OFFSET adalah…

a.=OFFSET(logical;rows;cols;[height];[width])

b. =OFFSET(value;rows;cols;[height];[width])

c.=OFFSET(reference;rows;cols;[height];[width])

d. =OFFSET(text;rows;cols;[height];[width])

e.=OFFSET(range;rows;cols;[height];[width])

6. Jika terjadi error pada saat meghitung selisih jam, maka fomulanya harus diubah menjadi…

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

b. =C2-B2+OR(B2>C2;1)

c. =C2-B2+AND(B2>C2;1)

d. =C2-B2+RANK(B2>C2;1)

e. =C2-B2+MID(B2>C2;1)

7. Bentuk umum Formula Rank adalah…

a. =RANK(D3;inggris;0)

b. =RANK(range;nomor_cell;order)

c. =RANK(F3;jml_nilai;0

d. =RANK(G3;rata_rata;0)

e. =RANK(nomor;range_cell;order)

8. Kelemahan “if” jika pilihan untuk mengisi dengan fungsi logika sangat banyak maka dapat di atasi dengan fungsi referensi cell, yaitu…

a. SUM

b. AVERAGE

c. VLOOKUP

d. RANK

e. ABS

9. Untuk mengambil satu karakter sebelah dari kolom kode mobil (C6) dengan fungsi LEFT yang merupakan bagian dari fungsi text, dan membandingkannya dengan tabel merek, menggunakan formula…

a. =VLOOKUP(MID(C6;3;3);tipe;2;FALSE

b. =VLOOKUP(LEFT(C6;1);merek;2;FALSE

c. =VLOOKUP(RIGHT(C6;1);merek;2;FALSE

d. =VLOOKUP(MID(C6;1);merek;2;FALSE

e. =VLOOKUP(LEFT(C6;3;3);tipe;2;FALSE

10. Untuk 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 ipe kode tidak disortir atau diurutkan, menggunakan formula…

a. =VLOOKUP(MID(C6;3;3);tipe;2;FALSE

b. =VLOOKUP(LEFT(C6;1);merek;2;FALSE

c. =VLOOKUP(RIGHT(C6;1);merek;2;FALSE

d. =VLOOKUP(MID(C6;1);merek;2;FALSE

e. =VLOOKUP(LEFT(C6;3;3);tipe;2;FALSE

11. Untuk mengubah warna karakter di kolom K dengan warna merah jika kolom J nilainya negatif dan warna kuning jika nilainya positif, maka urutan langkah-langkahnya adalah…

a. Kita ubah yang nilainya negatif menjadi warna merah, tempatkan pada Cell K6 klik Conditional Formating pada ribbon Home, kemudian Klik Manage Rule à Pilih Rule Use Formula to Determine with Cell to Format dan pada kotak formula isi J6<0, maka Klik Format à Ubah jenis huruf menjadi wingding dan warna menjadi kuning à Pada Format Cells ubah warna menjadi merah, klik OK à Copy formula di K6 ke baris berikutnya à Klik New Rule

b. Ubah jenis huruf menjadi wingding dan warna menjadi kuning à Kita ubah yang nilainya negatif menjadi warna merah, tempatkan pada Cell K6 klik Conditional Formating pada ribbon

Home, kemudian Klik Manage Rule à Klik New Rule à Pilih Rule Use Formula to Determine with Cell to Format dan pada kotak formula isi J6<0, maka Klik Format à Pada Format Cells ubah warna menjadi merah, klik OK à Copy formula di K6 ke baris berikutnya

c. Pada Format Cells ubah warna menjadi merah, klik OK à Copy formula di K6 ke baris berikutnya à Klik New Rule

d. Pilih Rule Use Formula to Determine with Cell to Format dan pada kotak formula isi J6<0, maka Klik Format à Ubah jenis huruf menjadi wingding dan warna menjadi kuning

e. Kita ubah yang nilainya negatif menjadi warna merah, tempatkan pada Cell K6 klik Conditional Formating pada ribbon