Menghitung Masa Kerja #1

Pada contoh kasus kali ini akan menampilkan permsalahan klasik tentang
bagaimana mengitung masa kerja  yang pada intinya bagaimana mencari selisih diantara dua tanggal yang berbeda. Selanjutnya kasus ini akan berkembang dengan kasus yang sama misal menghitung usia seseorang, menghitung lama studi seorang mahasiswa dan sebagainya.

Ada banyak cara untuk menghitung waktu berdasarkan selisih antara dua tanggal, pada conoh kali ini akan diampilkan contoh sederhana, dimana
hanya menggunakan dua fungsi yaitu fungsi INT() unuk membulatkan kebawah, dan fungsi NOW() untuk memmanggil tanggal sekarang dari komputer serta perhitungan matematis biasa.

Asumsi yang digunakan adalah dalam satu tahun terdapat 365 hari dan dalam
satu bulan terdapat 30 hari.

Untuk jelasnya perhatikan gambar dibawah ini ! tugas anda hanya mengisi bagian yang berwarna kuningan saja

Langkah-langkah pengerjaan

  • Jenis kelamin diisi dengan mengambil dari nomor induk yaitu digit keempat sebanyak satu digit, jika digit keempat bernilai 1 maka jenis kelaminnya laki-laki, jika bernilai 2 maka jenis kelaminnya perempuan, untuk mengambil digit keempat yaitu angka 1 atau 2 maka digunakan fungsi MID(), dan hasilnya dilakukan pemilihan dengan fungsi IF(), maka pada kolom D5 diisi dengan rumus :
    =IF(MID(B6;4;1)=”1″;”Laki-laki”;”Perempuan”)
    Untuk baris selanjutnya silahkan dicopy rumusnya
  • Tahun diisi dengan mengurangi tanggal sekarang dengan tanggal masuk, untuk mengetahui tanggal sekarang digunakan fungsi NOW() dan hasilnya akan berupa hari, supaya menjadi tahun maka jumlah hari tersebut kita bagi dengan angka 365 hari, asumsinya 1 tahun 365 hari, dan agar hasilnya bilangan bulat tanpa desimal dibelakangnya kita bulatkan kebawah dengan fungsi INT(), dari ketentuan tersebut maka rumus untuk mengisi tahun adalah :
    =INT((NOW()-E6)/365)
    Untuk baris berikutnya copy rumus tersebut !
  • Untuk pengisian bulan, urutannya adalah mencari selisih dalam hari anatar tanggal sekarang dengan tanggal masuk (sebut saja hasilnya A), kemudian mencari jumlah hari dari kolom tahun dengan mengkali 365 (sebut saja hasilnya B), kemudian selisihkan antara A dengan B dan hasilnya dibagi 365, hasilnya dikali 12 dan bulatkan kebawah hasil akhirnya, dari logika diatas maka rumus secara sederhana adalah :
    int(((A-B)/365)*12)
    sedangkan jika diimplementasikan kedalam formula excel untuk menghitung bulan adalah
    =INT((((NOW()-E6)-(F6*365))/365)*12)
    copy untuk mngisi baris dibawahnya
  • Untuk pengisian kolom hari, logikanya adalah mencari jml hari dari tanggalmasuk sampai dengan tanggal ini (sebut saja A), kemudian mengkonversi kolom tahun menjadi hari dengan mengkalikan dengan nilai 365 (sebut saja B), mengkonversi kolom bulan menjadi hari dengan mengkalikan dengan angka 30 (sebut saja C), selisihkan ketiga variabel tersebut dan hasilnya dibulatkan kebawah sehingga rumusnya :
    int(A-B-C)
    Jika diimplementasikan dalam formula nya adalah :
    =INT((NOW()-E6)-(F6*365)-(G6*30))
    untuk baris selanjutnya copy rumus tersebut

Untuk latihannya silahkan download

Fungsi AND

Fungsi ini akan menghasilkan nilai TRUE  (benar), jika semua argumen benar, dan akan menghasilkan nilai FALSE (salah) jika salah satu atau lebih argumen bernilai FALSE.

Syntax
AND(logika1,logikal,…)

Logika1, logika2, …  dengan range logika 1 sampai 30 kondisi yang bisa anda tesuntuk menghasilkan nilei  TRUE atau FALSE.

Catatan

Argumen harus bisa dievaluasi ke nilai value seperti TRUE atau FALSE,  atau argumen merupakan sekumpulan array, atau menunjuk ke refernsi kumpulan cell yang berisi nilai-nilai logical.

Jika  terdadpa array atau  referensi kumpulan cell bersisi text aau bernilai kosong maka nilainya akan diabaikan.

Jika range tertentu mengandung nilai yang bukan logical values, fungsi AND akan menghasilkan nilai error berupa #VALUE!

Contoh 1 :

Buat lembar kerja kosong dan ketik contoh dibawah ini :

Penjelasan :

  • Pada baris A2 akan menghasilkan nilai TRUE karena semua argumen bernilai TRUE
  • Pada baris A3 akan menghasilkan nilai FALSE karena salah satu argumen bernilai FALSE
  • Pada baris A4 akan menghasilkan nilai TRUE karena semua argumen bernilai TRUE

Contoh 2 :

Buat lembar kerja kosong dan keik contoh dibawah ini :

Penjelasan :

  • Pernyataan pertama akan bernilai TRUE karena cell A2 yang bernilai 50 berada pada kisaran angka 1 dan 100
  • Pernyataan kedua akan menghasilkan nilai Nilai diluar batas range karena cel A3 yang bernilai 104 berada di luar kisaran angka 1 dan 100
  • Pernyataan ketiga akan bernilai sama dengan cell A2 jika nilai A2 berada pada kisaran angka 1 dan 100, dan akan menghasilkan Nilai diluar batas range jika diluar kisaran angka 1 dan 100.

Menghitung Selisih Hari dan Jam

Pada contoh kasus ini akan dibahas bagaimana mehiug selisih hari dan jam pada sebuah usaha rental, Perhatikan gmbar dibawah ini, tugas anda hanya mengisi bagian yang berwarna kuning, yaitu lama dalam jumlah hari dan jam, total tagihan, discount, dan jumlah neto yang harus dibayar oleh penyewa, seta pada bagian akhir mentotalkan sera mencari waktu pengembalian terlama dan tercepat. Langkah-langkah pengisian

  • Sebelumnya tentukan terlebih dahulu area_cell pada kolom pembayaran bruto, Discount, Pembayaran neto, untuk membuat penamaan area cell silahkan baca artikel Memberi Nama Pada Cell Area
  • Pengisian lama peminjaman dalam hari (Kolom H8)
    =INT((F8-D8)+(G8-E8))
    Rumus diatas mengurangi kolom tanggal pengembalian dengan tanggal peminjaman dan hasilnya ditambah dengan hasil pengurangan kolom jam pengembalian dengan jam peminjaman, hasilnya akan berupa desimal dan dibulatkan ke bawah dengan fungsi INT().
  • Pengisian lama peminjaman dalam jam (Kolom I8)
    =INT(((F8-D8)+(G8-E8)-H8)*24)
    Rumus diatas mengurangi tanggal pengembalian dan peminjaman untuk mengetahui apakah peminjaman dilakukan pada tanggal yang sama atau tidak, kemudian hasilnya ditambah dengan pengurangan jam pengembalian dan jam peminjaman. Jika terjadi tanggal peminjaman dan pengembalian berbeda maka hasilnya dikurangi kolom H8, sehingga yang ditampilkan adalah selisih jamnya saja, hasil rumus tersebut dibulatkan ke bawah menggunakan fungsi INT().
  • Pengisian pembayaran bruto (Kolom J8)
    Kolom J8 adalah jumlah uang yang h arus dibayar oleh penyewa dengan ketentuan sewa per hari adalah Rp. 5.000 dan Rp 100 per jam, maka formulanya adalah :
    =(H8*5000)+(I8*100)
  • Pengisian discount  (Kolom K8)
    Kolom K8 adalah perhitungan diskon untuk penyewa yang mengembalikan kurang dari dua hari dengan besar discount sebesar 10% dari harga sewa yang harus dibayar, dan jika lewat dari dua hari maka tidak mendapat discount, maka formulanya adalah :
    =IF(H8<2,10%*J8,0)
  • Pengisian pembayaran neto (Kolom L8)
    Pembayaran neto merupakan hasil pengurangan dari pembayaran bruto-discount, maka formulanya :
    =J8-K8
  • Pengisian Peminjaman tercepat(Kolom H15)
    Peminjaman tercepat adalah lama hari pinjaman dengan nilai terkecil, untuk mencarinya gunakan fungsi MIN(area_cell) dan untuk menambahkan kata ‘hari’ dibelakangnya gunakan fungsi CONCATENATE(), maka formulanya :
    =CONCATENATE(MIN(hari_pinjam),” “,”hari”)
    dari formula diatas pertama mencari nilai terkecil dari jumlah hari kemudian nilainya dibagungkan dengan spasi (” “) dan kata “hari” dengan fungsi CONCATENATE
  • Pengisian Peminjaman terlama(Kolom H16), pada dasarnya sama dengan formula peminjaman tercepat, namun menggunakan fungsi MAX(area_cell)
  • Menghitung Kolom Total (Kolom J14,K14,L14)
    Untuk menghitungnya menggunakan formula =SUM(area_cell), dari contoh diatas maka rumusnya :
    Kolom Pembayaran Bruto : =SUM(bayar)
    Kolom Discount : =SUM(discount)
    Kolom Pembayaran Neto : =SUM(neto)

Untuk mempelajari kasus diatas, silahkan download file latihannya.

Memberi Nama Pada Cell Area

Penggunaan formula di excel sering melibatkan aktifitas mem-blok cell atau kita sebut cell area, agar pengaturan atau formula dapat berfungsi dengan baik.

Ada dua cara untuk memberi nama pada cell area yang telah di blok, yaitu :

Menggunakan Menu

1. Untuk memberi nama menggunakan menu, anda blok cell area yang akan diberi nama lihat gambar :

Kemudian pilih menu

2. Beri nama pada area yang di blok, jika telah selelsai Klik Add

3. Maka seluruh nama area yang telah disimpan akan ditampilkan.

4. Klik Ok untuk menutupnya

Menggunakan Cara Cepat
1. Blok Area yang akan diberi nama.
2. Klik kotak drop down diatas cell a1

3. Ketik nama area (tidak boleh pakai spasi), jika telah selesai tekan tombol enter
4. Untuk melihat semua nama area yang telah dibuat klik kotak dropdown tersebut, maka akan ditampilkan semua nama area, jika anda klik salah satu nama area maka, akan terblok area tertentu sesuai namanya

Penggunaan nama area ini untuk memudahkan dalam proses kalkulasi dan penggunaan fungsi dalam skala besar.

Hasil Studi Mahasiswa

Pada studi kasus kali ini akan membahas bagaimana membuat kartu hasil studi (KHS) untuk mahasiswa sekolah tinggi dimana setiap mata kuliah diberi nilai dengan huruf antara A sampai dengan E dengan bobot 4 untuk nilai A sampai 0 untuk nilai E. Setiap mata kuliah memiliki kode sendiri yang sifatnya unik terdiri dari 6 digit, digit terakhir merupakan jumlah SKS yang akan menjadi faktor pengali dengan bobot nilai.

Untuk  jelasnya perhatikan gambar dibawah ini

Mari kita bahas satu persatu kolom diatas, kolom diatas yang akan dibuat formulanya adalah kolom Mutu, SKS, dan kolom Mutu x SKS. Ketiga kolom tersebut akan terisi secara otomatis saat anda mengisikan nilai pada kolom nilai.

Untuk membuat formula pada  kolom nilai baris ke-13 kita gunakan fungsi if dengan formula sebagai berikut :

=IF(F13=”A”;4;IF(F13=”B”;3;IF(F13=”C”;2;IF(F13=”D”;1;O))))

formula diatas artinya jika cell F13 (kolom nilai) berisi  A maka kolom mutu secara otomatis terisi dengan angka 4, jika B terisi angka 3, jika C terisi angka 2 jika nilainya D terisi angka 1
dan jika tidak cocok semuanya adalah 0, kurung tutup akhir formula jumlahnya sebanyak jumlah if di dalam formula, if didalam formula tersebut terdapat empat if jadi kurung tutupnya sejumlah empat buah.

Selanjutnya kita mengisi kolom SKS dengan ketentuan jumlah SKS adalah digit terakhir dari kode mata kuliah, misal jika kode mata kulian ESP203 maka SKS nya adalah 3, untuk itu kita gunakan fungsi text yaitu
fungsi right, namun hasil dari fungsi text adalah character meskipun secara visual berupa angka, karena tipe datanya character maka tidak bisa dikalkukasikan, agar hasilnya berubah menjadi numeric sehingga mudah untuk dikalkulasikan hasil dari fungsi right kita konversi ke numeric dengan fungsi value, sehingga kolom SKS akan kita isi dengan rumus sebagai berikut :

=VALUE(RIGHT(D13;1))

Setelah kolom SKS terisi dengan formula diatas saatnya mengisi kolom
Mutu x SKS dengan dengan menggunakan formula perkalian matematika bisa yaitu :

=G13*H13

Sedangkan untuk mengisi kolom total dari kolom SKS dan Mutu x Bobot menggunakan fungsi database yaitu fungsi sum masing masing dengan rumus :

Total kolom SKS

=SUM(H13:H19)

Total mutu x bobot

=SUM(I13:I19)

Dan index prestasi diisi dengan formula pembagian antara total mutu x bobot dengan total sks dan membulatkan sampai dengan dua angka dibelakang koma menggunakan fungsi round dengan rumus :

=ROUND(I20/H20;2)

Terakhir adalah menetukan beban SKS yang boleh diambil oleh mahasiswa
yang bersangkutan dengan ketentuan jika indeks prestasinya kurang dari sama dengan 3 boleh mengambil maksimal 24 SKS, jika indeks prestasi kirang dari 2,75 boleh mengambil 22 SKS dan jika indeks prestasinya kurang dari 2 maka maksimal boleh mengambil 18 SKS, dengan menggunakan fungsi if maka ketentuan tersebut dibuat rumusan sebagai berikut :

=IF(I22>=3;24;IF(I22>2,75;22;IF(I22>2;20;18)))

Nah sampai disini anda telah mempelajari fungsi if, right, sum, value, dan round. Silahkan download file latihan excel nya untuk anda pelajari lebih lanjut.

Memecah Nilai Rupiah

Contoh kasus berikut diambil dari kasus yang dihadapi bendahara sekolah SMK NEGERI 2 KUNINGAN, dimana permasalahan yang dihadapi adalaha
harus menyediakan sejumlah uang untuk mebayar gaji dan honor kepada
para guru dan tata usaha.

Permasalahannya adalah harus berapa lembar uang yang disediakan nominal Rp. 100.000, Rp. 50.000 sampai dengan pecahan terkecil yaitu Rp. 200 apabila bendahara memegang sejumlah uang yang akan dibagi, misal sejumlah Rp. 46.875.450,-

Permasalahan diatas bisa diatasi dengan menggunakan formula di excel tanpa harus membua program aplikasi yang rumit hanya menggunakan fungsi if, rounddown, sum serta operasi matematika biasa anda bisa menghitung berapa lembar / keping uang yang disediakan untuk
memecah sejumlah uang yang anda miliki.

Perhatihan screenshoot dibawah ini :

Pada gambar dibawah untuk memecah sejumlah uang tertentu, anda hanya harus mengisi berapa nilai nominal yang akan dipecah, misal Rp. 1.745.300 maka secara otomatis formula yang dibuat dikolom D akan menghitung jumlah  uang dalam satuan lembar/keping untuk setiap satuan pecahan rupiah.

Untuk lebih jelasnya silahkan anda download dan pelajari fungsi-fungsi yang dugunakan dalam studi kasus kali ini.