Tag Archives: isblank

Membuat Potongan Uang Makan Karyawan Berdasarkan Keterlambatan Waktu

Posting kali ini merupakan sebuah bantuan kepada salah satu pembaca blog ini yaitu saudara ahmad eko, melalui email beliau menulis pesan seperti berikut

Bos bisa bantu saya menemukan rumus potongan buat karyawan yang kdatangan nya terlambat.

Dan beliau mengirimkan file kerja seperti berikut :

Dari gambar diatas beliau memberikan ketentuan sebagai berikut :

1. Jika karyawan telat kurang dari 10 menit maka dipotong Rp. 2.000

2. Jika karyawan telat masuk lebih dari 10 menit maka dipotong Rp. 5.000

3. Berapapun jam masuk karyawan dihitung 1 dan jika tidak hadir dihitung 0

Berdasarkan ketentuan diatas maka file kerja pada gambar pertama diubah menjadi seperti berikut :

Pada gambar diatas, yang harus diinput adalah jam masuk setiap harinya pada area warna putih, sedangkan pada area warna kuning akan otomatis terisi.

1. Untuk mengisi kolom keterlambatan (kolom D7) menggunakan formula sebagai berikut :

=IF(ISBLANK(C7),0,VALUE(C7-$L$3))

Kemudian kolom keterlambatan (kolom D7)  diformat menggunakan format time .

2. Untuk mengisi kolom potongan (E7) menggunakan formula :

=IF(D7>=TIME(0,10,0),5000,IF(AND(D7>TIME(0,0,0),D7<TIME(0,10,0)),2000,0))

Fungsi Time digunakan untuk membuat waktu jadi kalau telat 10 menit maka ditulis time(0,10,0) jika tepat waktu maka time(0,0,0)

3. Untuk mengisi berapa hari karyawan hadir (bekerja) gunakan formula :

=IF(ISBLANK(C7),0,1)+IF(ISBLANK(F7),0,1)+IF(ISBLANK(I7),0,1)+IF(ISBLANK(L7),0,1)+IF(ISBLANK(O7),0,1)+IF(ISBLANK(R7),0,1)+IF(ISBLANK(U7),0,1)

artinya jika kolom C7,E7,I7,L7,O7,R7, DAN U7  tidak ada isinya (blank) maka dianggap nol (tidak hadir) tetapi jika ada isinya maka diangap hadir (1)

Silahkan download file latihan untuk mempelajarinya :

DOWNLOAD

Membuat Nota Penjualan Dengan Excel

Nota penjualan merupakan dokumen yang mutlak dibuat sebagai bukti fisik penjualan barang apabila toko tidak memiliki sistem yang komprehensif untuk mencatat transaksi penjualan. Untuk toko-toko kecil membuat nota bisa menggunakan bantuan excel, tidak perlu menggunakan aplikasi Point Of Sale (POS) kalau memang tidak memiliki dana untuk membelinya atau volume transaksi nya masih kecil.

Berikut adalah contoh membuat nota dengan excel menggunakan Fungsi Match dan  Fungsi Index yang telah dibahas pada artikel sebelumnya. Untuk jelasnya silahkan perhatikan screenshoot nota penjualan dibawah ini :

Perhatikan pada gambar diatas, tugas dari operator komputer untuk mengisi nota penjualan diatas hanya mengisi kolom kode barang dan jumlah sedangkan kolom yang lainnya (berwarna abu-abu) akan diisi secara otomatis mengambil dari daftar barang yang disimpan dalam tabel referensi seperti tampak pada gambar dibawah ini :

Untuk membuat nota seperti diatas berikut adalah langkah-langkahnya :

1. Buat tampilan nota seperti tampak pada gambar pertama !

2. Buat daftar barang yang akan jadi referensi seperti tampak pada gambar kedua, pada contoh diatas terdapat 4 buah barang kenyataannya toko atau warung pasti memiliki puluhan bahkan ratusan barang, jadi pada prakteknya data seluruh barang di toko atau warung.

3. Pada tabel referensi kolom nama barang (Cell K5:K8) diberi nama barang, gunakan fasilitas insert name define.

4.  Berikutnya masih pada tabel referensi kolom harga barang (Cell L5:L8) beri nama harga, gunakan faslitas insert name define.

5. Terakhir di tabel referensi beri nama kode pada kolom kode barang (Cell M5:M8) dengan fasilitas insert name define.

6. Pada formulir nota penjualan beri nama harga_satuan pada kolom harga (cell F9:F14) dengan fasilitas insert name define.

7. Pada formulir nota penjualan beri nama jml_terjual pada kolom harga (cell G9:G14) dengan fasilitas insert name define.

8. Pada formulir nota penjualan beri nama harga_x_jml pada kolom harga (cell H9:H14) dengan fasilitas insert name define.

10. Pada Cell G11, buat tanggal secara dinamis untuk membuat tanggal kwitansi dengan fungsi date menggunakan formula :

=now()

11. Agar kolom nama barang otomatis terisi ketika mengisi kode pada kolom kode barang, maka pada kolom nama barang (cell E9) ketik formula diawah ini dan copy pada baris dibawah nya :

=IF(NOT(ISBLANK(D9)),INDEX(data_barang,MATCH(D9,kode,0),1),””) 

Maksud dari formula diatas adalah apabila cell D9 / kolom kode barang  tidak kosong (Fungsi ISBLANK), lakukan pencarian data menggunakan Fungsi Index dan Fungsi Match pada tabel referensi, namun jika D9 kosong maka kolom nama barang pun akan kosong.

12. Berikutnya pada kolom harga baris pertama di formulir nota  ketik formula berikut :

=IF(NOT(ISBLANK(D9)),INDEX(data_barang,MATCH(D9,kode,0),2),0)

Maksud dari formula diatas adalah apabila cell D9 / kolom kode tidak kosong (Fungsi ISBLANK), lakukan pencarian data menggunakan Fungsi Index dan Fungsi Match pada tabel referensi, namun jika D9 kosong maka kolom nama barang pun akan kosong.

13. Pada kolom total (cell H9) ketik formula berikut untuk mengkalikan harga satuan dengan banyaknya barang yang dibeli :

=F9*G9

14. Pada baris Total DIbayar (Cell H15) ketik formula berikut :

=SUMPRODUCT(harga_satuan,jml_terjual)

Formula diatas dimaksudkan menjumlahkan hasil perkalian dari tabel harga dengan tabel Jml pada nota penjualan, atau bisa juga menggunakan formula :

=SUM(harga_x_jml)

Simpan dokumen anda, sebagai latihan silahkan download file latihan diatas.

DOWNLOAD

Mencari Nilai dengan Fungsi Index #Bag. 2

Artikel kali ini merupakan lanjutan dari artikel sebelumnya yaitu “Mencari Nilai dengan Fungsi Index #Bag. 1“, pada artikel sebelumnya anda harus melakukan secara manual ketika akan mencari nilai pada suatu cel dengan menggunakan fungsi index. Perhatikan kembali screenshoot dibawah ini :

Sebagai contoh untuk mencari jumlah pendaftar prodi kehutanan – S1 pada tahun 2008 maka anda harus melakukan penentuan index kolom dan index baris secara manual.

Pembuatan formula index apabila penentuan  index kolom dan baris dilakukan secara manual sungguh merepotkan apabila data tersebar dalam spreedsheet yang begitu banyak, nah pada artikel kali ini akan dibuat pengisian cell untuk mencari jumlah pendaftar menggunakan menu, sehingga pengguna tinggal memilih nama jurusan dan tahun pendaftaran maka otomatis akan ditampilkan hasil pencarian data dalam cell. Perhatikan kedua gambar dibawah ini !

Dari kedua gambar diatas untuk mencari jumlah pendaftar anda tinggal memilih program studi – jenjang serta tahun pada drop down yang disediakan. Nah pada aritikel kali ini kita akan memodifikasi artikel sebelumnya menjadi seperti tampak pada kedua gamabr diatas.

Berikut langkah-langkah pengerjaan :

1. Blok cell C7 sampai H11 (C7:H11) dan beri nama data dengan menggunakan fasilitas insert name define.

2. Buat tampilan seperti pada gambar ke-3 (ke-4)

3. Blok cell B7 sampai B11 (B7:B11) dan beri nama prodi dengan menggunakan fasilitas insert name define.

4. Blok cell C6 sampai H6 (C6:H6) dan beri nama tahun dengan menggunakan fasilitas insert name define.

5. Pada tampilan gambar ke-3 (ke-4), simpan kursor di cell K7 untuk membuat drop down jurusan, gunakan fasilitas data validation untuk membuatnya, berikut ketentuan pembuatannya. Pada bagian Allow pilih List selanjutnya pada bagian Source ketik =prodi, sesuaikan namanya sesuai pada langkah 3, jika telah selesai klik OK.

6.  Pada tampilan gambar ke-3 (ke-4), simpan kursor di cell K8 untuk membuat drop down tahun, gunakan fasilitas data validation untuk membuatnya, berikut ketentuan pembuatannya. Pada bagian Allow pilih List selanjutnya pada bagian Source ketik =tahun, sesuaikan namanya sesuai pada langkah 3, jika telah selesai klik OK.

7. Simpan kursor di K9 untuk mencari jumlah pendaftar, dan ketik formula berikut :

=IF(OR(ISBLANK(K7),ISBLANK(K8)),””,INDEX(data,IF(ISERROR(MATCH(K7,prodi,0)),””,MATCH(K7,prodi,0)),IF(ISERROR(MATCH(K8,tahun,0)),””,MATCH(K8,tahun,0))))

Untuk bahan latihan silahkan download filenya :

DOWNLOAD