Assalamualaikum wr.wb
Alhamdulillah kita masih diberi kesempatan dan kesehatan untuk belajar dan berbagi ilmu. Setelah beberapa minggu saya membuat artikel tentang Akuaponik dan Otomotif, kini saya ingin berbagi tutorial tentang Microsoft Excel. Ide pembuatan tutorial ini muncul karena pembuatan jadwal harus memenuhi beberapa kondisi, diantaranya:
1. Piket diisi maksimal 2 orang/ Pada hari Aktif tiap bulan.
2. Jumlah hari piket masing-masing SDM dalam 1 bulan harus sama.
3. Aplikasi bisa digunakan tiap bulan dengan hari aktif yang berbeda-beda.
4. Jumlah SDM yang dijadwalkan piket sementara 13 Orang, bisa jadi sewaktu-waktu bertambah atau berkurang jumlahnya.
Berawal dari kondisi tersebut, akhirnya saya mempunyai ide membuat aplikasi sederhana menggunakan Microsoft Excel. Microsoft Excel dipilih untuk membuat aplikasi karena platform tersebut sangat familiar bagi saya dan SDM disini, sehingga pembuatannya dapat dilakukan dengan cepat, serta SDM lainnya dapat segera beradaptasi. Aplikasi Jadwal Piket yang saya buat sifatnya semi Otomatis, agar dapat menampung aspirasi SDM lainnya ketika ada pesanan hari piket, atau pesanan pasangan piket. Microsoft Excel yang saya gunakan untuk membuat aplikasi jadwal ini menggunakan Microsoft Excel 2010. Microsoft Versi diatas 2010 juga bisa menggunakan tutorial ini. Paragraf berikutnya mulai masuk ke pembahasan utama terkait proses dan cara pembuatan aplikasi:
A. FORMULA/SCRIPT YANG DIBUTUHKAN
1. IF...ELSE
2. IFERROR
3. COUNTIF
4. COUNTA
5. ROUNDUP
6. SUM
Penjelasan detail tentang formula tersebut akan lebih mudah dipahami ketika dijelaskan bersamaan dengan contoh script/ formula utuh pada paragraf selanjutnya.
B. TOOL PELENGKAP
1. Menu Home ->> Conditional Formating
2. Menu Data ->> Data Validation ->> List
C. MULAI PEMBUATAN
Setiap penjelasan script selalu berhubungan dengan gambar utama. Jadi perhatikan gambar utama berikut (Lebih mudah jika anda menyimpan gambar utama di PC kemudian dibuka di PC/Laptop anda:
 |
| Gambar Utama Aplikasi Jadwal Piket |
1. Cell C5 sampai O5 merupakan cell Daftar nama SDM
2. Cell B11 sampai B41 Merupakan Cell yang menjelaskan status Hari Aktif atau Hari Libur/Tanggal Merah cell pada kolom ini memanfaatkan Menu Excel Data validation yaitu Blok Cell B11 sampai B41->> Pilih Data ->> Data Validation ->> List ->> Maukkan Pilihan Hari Aktif ; Hari Libur/Tanggal Merah
 |
| Data Validation Keterangan Hari Aktif |
3. Cell A11 sampai A41 Merpakan Cell tanggal diisi 1 sampai 31.
4. Cell C11 Sampai O41 Merupakan Cell yang menjelaskan status penunjukkan SDM dalam melaksanakan Piket Atau Tidak yang diwakili oleh angka 1=Piket, 0 atau Kosong=Tidak Piket, cell pada kolom ini memanfaatkan Menu Excel Data validation yaitu Blok Cell C11 Sampai O41->> Pilih Data ->> Data Validation ->> List ->> Maukkan Pilihan 0 ; 1
 |
| Data Validation Keterangan Piket |
5. Cell B42 Berisi Jumlah Hari Aktif dalam satu bulan, Rumus/ Formulanya yaitu:
=COUNTIF(A11:$B$41;"HARI AKTIF")
6. Cell C7 sampai O7 berisi jumlah hari yang seharusnya diterima oleh SDM dalam satu bulan, diperoleh dari Jumlah Hari Aktif dibagi Jumlah SDM Aktif yang dibulatkan ke Atas. Rumus/ Formulanya yaitu:
=ROUNDUP(($B$42/COUNTA($C$5:$O$5));0)
7. Cell C8 sampai O8 berisi jumlah hari SDM yang sudah diplot (diisi angka 1) oleh pembuat jadwal dalam satu bulan, diperoleh dari Perhitungan Cell yang berisi Angka 1 Dalam Kolom Masing-masing Pendamping. Rumus/ Formulanya yaitu:
=COUNTIF(C11:C41;1)
Rumus tersebut cukup kita buat di Cell C8 kemudian kita tarik ke samping kanan sampai cell O8 menggunakan fasilitas autofill (Sorot Pojok Bawah kanan Cell sampai berubah menjadi tanda + Tipis setelah itu klik kiri, tahan, geser).
8. Cell C6 sampai O6 berisi keterangan apakah jumlah hari SDM yang sudah diplot (diisi angka 1) oleh pembuat jadwal dalam satu bulan sudah sesuai pembagian jumlah seharusnya agar adil, diperoleh dari perbandingan nilai Cell C8 sampai O8 dengan C7 sampai O8 secara berurutan vertikal.
Output yang diinginkan:
a. Jika JUMLAH DIBAGI KORKOT/PEMBUAT JADWAL >JUMLAH SEHARUSNYA AGAR ADIL Maka akan muncul keterangan JUMLAH HARI KEBANYAKAN
b. Jika JUMLAH DIBAGI KORKOT/PEMBUAT JADWAL <JUMLAH SEHARUSNYA AGAR ADIL Maka akan muncul keterangan JUMLAH HARI KURANG
c. Jika JUMLAH DIBAGI KORKOT/PEMBUAT JADWAL =JUMLAH SEHARUSNYA AGAR ADIL Maka akan muncul keterangan JUMLAH HARI PAS
Rumus/ Formulanya yaitu:
=IF(COUNTIF(C$11:C$41;1)=ROUNDUP(($B$42/COUNTA($C$5:$O$5));0);"JUMLAH HARI PAS";IF(COUNTIF(C$11:C$41;1)>ROUNDUP($B$42/COUNTA($C$5:$O$5);0);"JUMLAH HARI KEBANYAKAN";"JUMLAH HARI KURANG"))
Rumus tersebut cukup kita buat di Cell C6 kemudian kita tarik ke samping kanan sampai cell O6 menggunakan fasilitas autofill (Sorot Pojok Bawah kanan Cell sampai berubah menjadi tanda + Tipis setelah itu klik kiri, tahan, geser).
9. Cell C42 sampai O42 berisi total jumlah hari piket masing-masing SDM.
Rumus/ Formulanya yaitu:
=SUM(C11:C41)
Rumus tersebut cukup kita buat di Cell C42 kemudian kita tarik ke samping kanan sampai cell O42 menggunakan fasilitas autofill (Sorot Pojok Bawah kanan Cell sampai berubah menjadi tanda + Tipis setelah itu klik kiri, tahan, geser).
10. Cell P11 Sampai P41 berisi total jumlah SDM yang melaksanakan piket dalam 1 Hari.
Rumus/ Formulanya yaitu:
=SUM(C11:O11)
Rumus tersebut cukup kita buat di Cell P11 kemudian kita tarik ke bawah kanan sampai cell P41 menggunakan fasilitas autofill (Sorot Pojok Bawah kanan Cell sampai berubah menjadi tanda + Tipis setelah itu klik kiri, tahan, geser ke bawah).
11. Cell P42 berisi total jumlah hari piket seluruh SDM dalam 1 bulan.
Rumus/ Formulanya yaitu:
=SUM(P11:P41)
12. Cell Q11 Samapi Q41 berisi keterangan apakah jumlah pasangan SDM yang sudah diplot (diisi angka 1) oleh pembuat jadwal dalam satu hari sudah sesuai.
Output yang diinginkan:
a. Jika HARI LIBUR/TANGGAL MERAH tetep di plot piket maka akan muncul keterangan PREIAN KOK ADA PIKET
b. Jika Hari Aktif, dan Jumlah Peserta Piket = 0, Maka muncul keterangan BELUM ADA YANG PIKET
c. Jika Hari Aktif, dan Jumlah Peserta Piket < 2, Maka muncul keterangan KASIHAN PIKET SENDIRIAN
d. Jika Hari Aktif, dan Jumlah Peserta Piket = 2, Maka muncul keterangan PIKET SEPASANG 2 ORANG
d. Jika Hari Aktif, dan Jumlah Peserta Piket > 2, Maka muncul keterangan ANGGOTA PIKET KEBANYAKAN
Rumus/ Formulanya yaitu:
=IF(B11="HARI LIBUR/TANGGAL MERAH";"PREIAN KOK ADA PIKET";IF(P11=0;"BELUM ADA YANG PIKET";IF(P11<2;"KASIHAN PIKET SENDIRIAN";IF(P11=2;"PIKET SEPASANG 2 ORANG";"ANGGOTA PIKET KEBANYAKAN"))))
Rumus tersebut cukup kita buat di Cell Q11 kemudian kita tarik ke bawah kanan sampai cell Q41 menggunakan fasilitas autofill (Sorot Pojok Bawah kanan Cell sampai berubah menjadi tanda + Tipis setelah itu klik kiri, tahan, geser ke bawah).
Cell Q11 Samapi Q41 dikombinasikan dengan fitur Conditional Formating untuk memperjelas keterangan agar mempermudah pembuat jadwal dengan menambahkan warna berbeda di setiap hasil output. Cara menggunakan conitional formating yaitu
Blok Cell Q11 Samapi Q41 ->> Home ->>Conditional Formating->>New Rule->>Format Only Cells that Contain ->> Ganti Between Menjadi equal To ->> Masukkan Keterangan Yang Diinginkan "ANGGOTA PIKET KEBANYAKAN"->>Tekan format ->>Pilih Font->>Bold->>Color->>Pilih Warna Kuning->>Pilih Fill->>Pilih warna Hijau->>OK->>OK
Ulangi langkah tersebut sebanyak keterangan yang anda buat.
 |
| Conditional Formating |
a. PREIAN KOK ADA PIKET
b. BELUM ADA YANG PIKET
c. KASIHAN PIKET SENDIRIAN
d. PIKET SEPASANG 2 ORANG
d.ANGGOTA PIKET KEBANYAKAN
Menu conditional formating juga dapat digunakan pada cell lainnya sesuai selera. Selain itu juga untuk mempercantik tampilan aplikasi. Untuk Conditional Formating pada Cell lain silahkan dicoba sendiri ya??
13 . Cell Q7 berisi keterangan perbandingan TOTAL JUMLAH DIBAGI KORKOT dengan TOTAL JUMLAH SEHARUSNYA AGAR ADIL, atau lebih mudanya perbandingan Kolom P8 dan Kolom P7.
Output yang diinginkan:
a. Jika TOTAL JUMLAH DIBAGI KORKOT < TOTAL JUMLAH SEHARUSNYA AGAR ADIL Maka akan muncul keterangan BEBERAPA SDM HARUS ADA YANG DITAMBAH HARI AGAR PEMBAGIAN SAMA
b. Jika TOTAL JUMLAH DIBAGI KORKOT = TOTAL JUMLAH SEHARUSNYA AGAR ADIL Maka akan muncul keterangan PEMBAGIAN PIKET ADIL RATA
c. Jika TOTAL JUMLAH DIBAGI KORKOT > TOTAL JUMLAH SEHARUSNYA AGAR ADIL Maka akan muncul keterangan ADA SDM YANG KEBANYAKAN JUMLAH PIKETNYA
Rumus/ Formulanya yaitu:
=IF(P8<P7;"BEBERAPA SDM HARUS ADA YANG DITAMBAH HARI AGAR PEMBAGIAN SAMA";IF(P8=P7;"PEMBAGIAN PIKET ADIL RATA";"ADA SDM YANG KEBANYAKAN JUMLAH PIKETNYA"))
Cell Area kerja pembuat jadwal hanya dimulai dari cell B11 sampai O41, selain itu semua cell mengandung formula penting, yang apabila tidak sengaja terubah makan seluruh aplikasi juga akan bermasalah, oleh karena itu sebaiknya kita melakukan Protect Sheet dengan Mengaktifkan Cell yang hanya digunakan oleh pembuat jadwal dan Mengunci Cell yang mengandung Formula.
D. MENGUNCI CELL BERFORMULA DAN MENGAKTIFKAN CELL DINAMIS
1. Blok cell B11 sampai O41 (Cell yang nantinya bersifat dinamis/ nilainya berubah sesuai pembuat jadwal)->>Klik Kanan->> Protection->> Hilangkan Centang Locked.
 |
| Format Cells |
2. Klik Kanan nama sheet aktif (Berada Dibawah) yang merupakan letak jadwal ->> Hilangkan Centang Select Locked Cells ->> Beri Centang Select Unlocked Cells
 |
| Centang Locked dan Unlocked Cells |
3. Alhamdulillah Selesai.
Demikian postingan saya mengenai Membuat Aplikasi Jadwal Piket Menggunakan Microsoft Excel (Bonus Aplikasi Jadi).
Aplikasi dapat di unduh disini.
Password : WWW.ONNYPUTRANTO.COM
Semoga bermanfaat!
Wassalamualaikum wr.wb.
Jangan lupa ikuti seluruh update sosial media saya berikut:
Youtube : Onny Putranto
Instagram: @onnyputranto212
Facebook: Onny Putranto
Google Business: Arzan Servis Laptop
0 comments:
Posting Komentar