Menyisipkan filename and path dengan fungsi

Kasus kali ini adalah bagaimana menyisipkan nama file serta lokasi file yang sedang digunakan atau istilahnya path and filename untuk file yang sedang dibuka.

Sebenarnya excel sudah menyediakan menu ini secara langsung yang dapat kita akses dengan membuka menu file–> page setup, kemudian pada tab Header/Footer pilih tombol Custom Header atau Custom Footer maka akan tampil kotak dialog seperti dibawah ini :
Continue reading

Kesalahan Perhitungan di Excel 2007

September 2007 Tim Excel diperingatkan oleh microsft tentang masalah di Excel 2007 yaitu tentang bug perhitungan angka 65.535. berikut adalah penjelasan dari Tim Excel tentang masalah tersebut .

Latar belakang
Tepatnya 9 September 2007 microsft memperingatkan masalah di Excel 2007 tentang perhitungan angka 65.535 Contoh pertama yang diberikan coba pada sembarang cell ketik formula berikut :
= 77,1 * 850 hasilnya adalah 100.000 bukan 65.535 (padahal di excel 2003 hasilnya 65.535) Sebagian besar laporan tambahan difokuskan pada proses perkalian (misal = 5,1 * 12.850; = 10,2 * 6.425;. = 20.4 * 3.212,5), pengujian lebih lanjut menunjukkan fenomena yang sama dengan 65.536 juga. Masalah ini hanya ada di Excel 2007, bukan versi sebelumnya.
Continue reading

Proteksi Formula Excel

Artikel ini sebagai jawaban atas permintaan Saudara Velli, Md. via shout box, banyak tujuan yang ingin dicapai jika kita melakukan proteksi terhadap sheet termasuk proteksi formula.

Sebagai contoh sederhana kantor pusat mengirim formulir yang harus diisi oleh kantor cabang, kantor pusat menginginkan formula tidak bisa dilihat apalagi diubah oleh kantor cabang, kantor cabang hanya memiliki hak untuk mengisi data pada area cell yang telah ditentukan, bagaimana melakukan proteksi sheet dan formula di excel ??

Nah pada studi kasus ini akan dibahas tentang Proteksi sheet dan formula di excel. Untuk memudahkan studi kasus kita ambil kasus yang telah kita bahas sebelumnya yaitu kasus Pembukuan Sederhana Untuk Tokodengan excel.
Perhatikan gambar dibawah ini :

Ilustrasi : Kita asumsikan bahwa kantor pusat mengirim format pembukuan sederhana untuk diisi oleh kantor cabang selama bulan tertentu. Kantor pusat hanya mengizinkan kantor cabang mengisi kolom tanggal, keterangan, debet dan kredit, sedangkan kolom saldo akan diisi secara otomatis dan tidak boleh dirubah atau dihapus bahkan kantor cabang tidak diizinkan melihat formulanya !

Untuk melakukannya, ikuti langkah-langkah berikut :

  1. Download file excel untuk contoh pembukuan DISINI
  2. Buka file hasil download, dari file tersebut kita menginginkan agar user hanya boleh merubah data pada kolom Tanggal, Keterangan, Debet dan kredit (Cell A8 sampai Cell F31) sisanya tidak boleh diubah, dihapus bahkan dilihat formulanya untuk kolom saldo dan jumlah tiap-tiap kolom.
  3. Blok Cell A8 sampai F31 (Kolom Tanggal, Keterangan, Debet dan kredit)
  4. Pilih Menu Format–>Cell, maka akan tampak kota dialog format cell, pilih tab Protectin Ceklist pilihan Locked dan Hidden kemudian klik OK
  5. Kemudian Klik Menu tool –> Protection –> Allow User To Edit Ranges …
  6. Pada Gambar diatas klik tombol New …, secara otomatis kolom Refers to Cells sudah terisi jika anda melakukan blok cell pada langkah ke-3, pada bagian Title, isi terserah anda misal Range1, pada bagian password kosongkan, kemudian klik tombol permitions…

  7. Setelah anda mengklik tombol protections, maka akan timbul kotak dialog untuk mendata user yang akan diberi akses untuk mengedit / merubah kolom tanggal, keterangan, debet dan kredit. User disini adalah user yang terdaftar pada komputer yang membuka file excel anda, jika anda tidak tahu user apa gunakan saja user Administrator, karena administrator adalah user standar di windows XP, untuk menambahkannya klik tombol Add
  8. Setelah Itu masukan user yang akan diberi akses untuk mengedit file, misal administrator, kemudian klik tombol OK
  9. user yang telah didata akan tampil pada kotak dialog dibawah ini, tentukan aksesnya jika diizinkan klik allow jika tidak diizinkan merubah atau mengedit klik Deny, jika hendak menambah user lain klik Add, dan jika akan menghapus user kik remove tentunya setelah memilih salah satu user yang akan dihapus. .
  10. Setelah itu klik tombol protect sheet.
  11. Maka akan keluar kotak dialoh protect sheet, biarkan pilihan yang lain standar tugas anda hanya mengisi password, klik OK
  12. Masukan kembali password anda untuk konfirmasi, kemudian klik OK
  13. Sekarang saat nya untuk menguji, simpan perubaan dan tutup file , buka kembali dan coba anda ubah atau tambah data pada kolom tanggal, keterangan debet dan saldo, dan lakukan penghapusan salah satu data pada kolom saldo jika terdapat error seperti pada gambar dibawah, serta lihat pada formula bar apakah formulanya tampak ? jika formula tidak tampak berarti file yang anda proteksi siap didistribusikan dan dikirim ke cabang untuk diisi data dan anda tanpa khawatir cabang tidak akan merubah data yang lain selain keempat kolom yang telah anda tentukan !

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.