Hallo … sobat setia belajar office. Pada kesempatan ini admin akan mencoba berbagi sebuah aplikasi menggunakan excel, sebagai bahan latihan untuk mendalami rumus-rumus excel yaitu membuat format rental CD/DVD menggunakan rumus-rumus excel. Mudah-mudahan bisa bermanfaat untuk sobat-sobat yang mempunyai usaha dibidang rentalan.
Format ini dibuat berdasarkan logika admin sendiri, dengan format sederhana namun terbilang cukup lumayan kompleks. Langsung saja langkah-langkah membuatnya sangat mudah yaitu sebagai berikut :
Pada pembuatan format rental CD/DVD ini admin bagi menjadi 4 sheet yang terhubung dengan rumus dengan yang lainnya, tujuannya dipisah supaya mudah untuk edit dan entry data-datanya.
Buat sheet 1 dengan nama “Data CD DVD”
Contoh formatnya seperti gambar dibawah ini
Sheet ini digunakan untuk menginput data-data CD/DVD yang tersedia dengan asumsi bahwa setiap CD/DVD sudah diberi label dengan kode yang berbeda-beda. Data-datanya meliputi : Kode, Judul Film, Genre, Jenisnya CD/DVD, tahun rilis, tarif rentalnya (per 3 hari), dsb bisa ditambahkan lagi sesuai kebutuhan.
Buat sheet 2 dengan nama “Data Pelanggan”
Contoh formatnya seperti gambar dibawah ini
Sheet ini digunakan untuk pendaftaran pelanggan yang akan menyewa CD/DVD datanya meliputi : No ID, Nama, No HP, Alamat dsb bisa ditambahkan sendiri jika ada data lain yang diperlukan.
Buat sheet 3 dengan nama “Peminjaman”
Contoh formatnya
Sheet ini digunakan untuk input pelanggan yang akan meminjam CD/DVD, pada sheet ini terdapat rumus-rumus excel didalamnya.
Data-datanya meliputi : Tanggal Sekarang, No ID, Nama, Kode CD/DVD, Judul CD/DVD, Tanggal Pinjam, Tanggal Harus Kembali, Tanggal Pengembalian, Biaya Rental, Denda Perhari, Denda Terlambat, KET.
Berikut Penjelasan
- Tanggal Sekarang : untuk mengetahui tanggal sekarang (tgl akan menyesuaikan setiap file excel ini dibuka) Rumusnya =TODAY()
- No ID : jika ada pelanggan yang akan meminjam CD/DVD inputkan No ID jika belum maka daftar dahulu di sheet 2. Supaya lebih praktis kita gunakan Data Validation dari sheet 2 (pelanggan) jadi tinggal pilih saja listnya
- Nama : Untuk nama kita buat otomatis sehingga ketika No ID diinput maka nama otomatis akan muncul.
Rumus yang digunakan =IFERROR(VLOOKUP(B5;’Data Pelanggan’!$B$4:$E$13;2;FALSE);””) baca juga untuk lebih mendalami fungsi vlookup dan fungsi if error - Kode CD : Untuk input CD/DVD yang akan dipinjam. Untuk mempermudah bisa gunakan juga Data Validation dari sheet data CD/DVD
- Judul Film : Dibuat otomatis muncul
Rumus yang digunakan =IFERROR(VLOOKUP(D5;’Data CD DVD ‘!$A$4:$G$14;2;FALSE);””)
- Tanggal Pinjam : Untuk mengisi tanggal CD/DVD dipinjam.
- Tanggal Harus Kembali : Dibuat dengan batas peminjaman untuk 3 hari.
Rumusnya dibuat otomatis =IF(ISBLANK(F5);””;F5+3), kenapa ditambahkan =IF(ISBLANK) tujuannya supaya rapih jika kolom kosong tidak tampil tanggalnya. - Tanggal pengembalian : digunakan untuk input tanggal CD/DVD dikembalikan.
- Biaya rental : otomatis muncul dari sheet (data CD/DVD) =IFERROR(VLOOKUP(D5;’Data CD DVD ‘!$A$4:$G$14;6;FALSE);””)
- Biaya denda contoh Rp 1000/hari
- Biaya Denda Terlambat =IF(H5>G5;(H5-G5)*$K$2;””)
- Ket : untuk menampilkan keterangan sudah dikembalikan atau belum dikembalikan rumusnya
=IF(ISBLANK(B5);” “;IF(ISBLANK(H5);”Belum Kembali”;”Sudah Kembali”))
Buaf sheet 4 dengan nama Data Stok CD DVD
Digunakan untuk mengetahui berapa jumlah CD/DVD film yang ada, sedang dipinjam dan tersedia.
Contoh formatnya seperti gambar dibawah ini
- Jml CD/DVD
=COUNTIF(‘Data CD DVD ‘!$B$4:$B$14;’Data Stok CD DVD’!B4)
baca juga untuk mendalami rumus Countif Excel (satu kriteria)
- Dipinjam
=COUNTIFS(Peminjaman!$K$5:$K$14;”Belum Kembali”;Peminjaman!$E$5:$E$14;’Data Stok CD DVD’!B4)
baca juga untuk mendalami rumus Countifs Excel (dua atau lebih keriteria)
- Tersedia
=F4-G4
Jika sobat menginginkan file yang sudah jadi silahkan sobat download dibawah ini
NB :
Untuk kolom atau cell yang ada rumus tidak boleh dihapus/delete
Kekurangan Rumus Excel harus menyesuaikan Range untuk barisnya dari datanya misalnya ‘Data CD DVD ‘!$A$4:$G$14
Terimakasih semoga bermanfaat.
mas edi,,, kalau caranya cd/dvd biar tidak bisa di copy gimana ya mas,,, seperti cd/dvd original music mas
mitra detik @ saya kurang paham untuk proteksi cd/dvd biar tidak bisa dicopy
Wah kalo masalah rumus excel saya kurang paham mas, padahal dari excel aja ternyata bisa buat aplikasi ya mas, saya sering liat soalnya kayak form aplikasi misalnya
memang bisa mas marnes dari excel dibuat semacam aplikasi menggunakan excel vba macro, masih sedang dipelajari.
Mantab ne,,kalau untuk aplikasi perjaanan dinas bisa gak mas edi
Mantab ne,,Kalau aplikasi untuk perjalanan dinas bisa gak mas edi
Bisa aja sobdibuat rumusnya, tapi saat ini belum punya gambaran untuk contoh formatnya.
hai mas edi.
kalau saya lihat kok beberapa contohnya ada kata2 banjarnegara? wong ngapak ya mas? hehehe
sebelumnya terimakasih banyak mas edi udah share ilmunya disini, sangat2 membantu sekali dan saya bantu rekomendasikan ke temen2 buat belajar dari web nya mas edi.
begini mas, saya lagi coba belajar tentang peminjaman ini, mau nanya dong mas, kalau ketersediaan barang yg ada di data stok cd/dvd itu di pindah/di buat copy nya di sheet “peminjaman”, enaknya pakai formula apa ya mas?
dalam arti begini, misal mau pinjem film sapiderman, stok nya ada 2, nah itu film udah keluar semua, kalau saya coba tetap masukin itu film ketersediaan barang kan jadi “-1”. kalau dibuat warning atau info bahwa stok nya udah “0”, itu bagaimana formula nya ya mas?
atau memang sengaja dibuat kita harus lihat sheet stok barang dulu baru input data di sheet peminjaman?
maturnuwun mas edi,
iya sob saya dari Banjarnegara. Terimakasih ya udah membantu rekomendasikan keteman2 sobat. Iya sob untuk file ini dibuat kita liat stok dulu baru input data. misalnya orang nanya ada stok film ini…. maka adminnya bisa liat stok dulu dan catatannya bahwa setiap film harus diberi label yang berbeda2. tapi bagus juga idenya klo dibuat ada warning klo stok udah 0.
makasih ya.