Tag Archives: conditional formating

Menyembunyikan Error Perhitungan dengan Conditional Formating

Meskipun excel mampu melakukan perhitungan dengan tepat namun error dalam perhitungan matematis maupun dengan fungsi jika salah satu input bernilai salah maka akan ditampilkan error, error tersebut kadang tidak enak dipandang apabila hasil perhitungan merupakan sebuah laporan yang akan dipublikasikan, nah sebagai ilustrasi perhatikan contoh kasus dibawah ini :

Pada gambar diatas adalah contoh laporan penjualan yang akan dipublikasikan, dari gambar tersebut terdapat error pada cell D5 error terjadi karena dilakukan perhitungan pembagian namun angka pembagi karena bernilai 0 (nol) maka hasiilnya akan ditampilkan warning berupa pesan #DIV/0 (Division by zero), seperti kita ketahui bahwa bilangan berapapun tidak bisa dibagi dengan nol, nah dari kasus diatas bagaimana caranya agar pesan error tersebut tidak ditampilkan ? cara yang akan kita lakukan adalah menyembunyikannya dengan conditional formating.

Silahkan anda buat lembar kerja seperti tampak pada gambar diatas, lakukan perhitungan untuk kolom rata-rata pada cell D4 dengan formula :

=ROUNDDOWN(C4/B4,0)

Rumus diatas melakukan pembagian dan hasilnya dibulatkan kebawah 0 decimal dibelakang koma dengan fungsi ROUNDDOWN, kemudian lakukan peng-copy-an untuk baris dibawahnya.
Tahap berikutnya kita sembunyikan pesan error dengan conditional formating
1. blok cel d4 sampai d7 (kolom rata-rata)
2. Klik icon conditional formating, pilih manage rule (Pada Excel 2003 Pilih Menu FOrmat – Conditional Formating)

3. Pada kotak dialog Conditional Formating Rule Manager, klik New Rule

4. Pada kotak dialoh new formating rule pilih use formula to determine wich cell to format

5. kemudian pada kotak Format value where this formula is true isi dengan
=ISERROR(D4)
6. Kemudiak klik tombol format
7. Pada tab font pilih warna font yang senada / sama warnanya dengan warna bacground cell dalam hal ini hijau

8. Klik OK pada semua kotak dialog sampai hilang kotak dialog
9. Sebagai hasil akhir maka anda akan melihat bahwa error #DIV/0 kini telah hilang !

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, pada gambar tersebut tugas anda hanyalah mengisi bagian yang berwarna hijau.

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

Tahap-tahap pengisian

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

Untuk memberikan penamaan pada cell silahkan baca http://latihanexcel.co.cc/2010/04/memberi-nama-pada-cell-area/

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 dibawah ini :

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, silahkan baca http://latihanexcel.co.cc/prasyarat/ untuk mengubah regional seting 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)
Unuk 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;”>0″)*makan_per_hari)

5. 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 COUTNIF, maka formulanya:
=IF(COUNTIF(C8:H8;”>0″)=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.
Untuk memudahkan studi kasus ini silahkan download file latihan studi kasus ini.

Mengecek Nilai Ganjil atau Genap

Kadangkala kita menemukan suatu kasus yang mengharuskan kita memisahkan sejumlah angka menjadi dua kelompok yaitu ganjil dan genap, pekerjaan akan sangat mudah jika data yang anda hadapi hanya sedikit misal hanya terdiri dari sepuluh baris, namun pekerjaan tersebut akan menjadi pekerjaan sulit jika data yang anda miliki jumlahnya adalah ribuan. Sebagai ilustrasi perhatikan gambar berikut :

Nah dari data diatas anda memiliki dua tugas yaitu :

  1. Mencari data yang bernilai ganjil atau genap
  2. dan Memberikan tanda dengan huruf tebal (bold) untuk keterangan yang bernilai ganjil

Untuk mengerjakannya tugas pertama :

  1. Tempatkan pointer cell pada B2 dan ketik formula berikut :
  2. =IF(A2=ODD(A2);”Ganjil”;”Genap”)
    Formula diatas menggunakan fungsi ODD untuk menaikan satu angka bagi bilangan positif dan menurunkan satu angka bilangan negatif ke nilai ganjil terdekat, kemudian hasilnya dibandingkan dengan cell A2 jika sama nilainnya ganjil dan jika beda nilanya genap.

Sedangkan untuk mengerjakan tugas kedua membedakan nilai ganjil dan genap dengan memberi format bold pada nilai ganjil ikuti langkah berikut :

Block Kolom B2 sampai B9 (atau sesuai data milik anda), lihat gambar dibawah ini :

Pilih menu Format –> Conditional Formating Sehingga akan tampil berikut :

Pada Condition 1 Pilih Formula Is ketik formula =A2=ODD(A2) kemudian klik tombol format, maka akan keluar kotak dialog formating seperti berikut:

Di kotak formating tersebut pada bagian Font Style pilih Bold, jika telah selesai klik tombol OK  dan klik OK lagi untuk menutuf conditional Formating, sekarang copy paste formual yang telah anda buat.  Sekarang data anda telah diberi tanda menjadi data ganjil dan genap.