Dear Mr. Kid, Terima kasih atas pencerahannya, semoga mendapatkan berkah dari Allah SWT, amiiin
Hormat saya, Samsudin [cid:[email protected]] From: [email protected] [mailto:[email protected]] Sent: Tuesday, February 17, 2015 4:18 PM To: BeExcel Subject: Re: [belajar-excel] Roster 3-1 Pak Samsudin, Formula : =LOOKUP(MOD(C$13-"2014-12-31"-1,58)+1,$B$22:$B$51/($A$22:$A$51=MOD($A14-1,3)+1),$C$22:$C$51) =LookUp( hari_siklus_di_data , hari_siklus_referensi_pada_pola_tertentu , status_siklus_referensi ) hari_siklus_di_data = MOD(C$13-"2014-12-31"-1,58)+1 C$13-"2014-12-31" -> menghasilkan selisih hari cell c13 terhadap tanggal 31 des 2014 = jumlah hari sejak hari pertama siklus pola -1 -> indeks hari siklus (base 0), yaitu hasil jumlah hari dikurangi 1 supaya hari ke-1 siklus dianggap hari ke-0 MOD(C$13-"2014-12-31"-1,58) -> indeks hari siklus tabel referensi, yaitu sisa bagi indeks hari siklus terhadap jumlah hari dalam 1 siklus (yaitu 58). Jadi, 0 dibagi 58 akan bersisa 0, 5 dibagi 58 akan bersisa 5, 58 dibagi 58 bersisa 0 alias seperti indeks hari ke-0 alias mulai siklus baru lagi +1 -> mengubah indeks hari siklus menjadi hari siklus kembali, karena tabel referensi berisi nilai-nilai hari siklus (base 1 alias dimulai hari ke-1) hari_siklus_referensi_pada_pola_tertentu = $B$22:$B$51/($A$22:$A$51=MOD($A14-1,3)+1) $B$22:$B$51 -> nilai-nilai hari siklus di tabel referensi $A$22:$A$51 -> nilai-nilai pola siklus setiap hari siklus di tabel referensi $A14 -> nomor record (bukan nomor baris Excel) -1 -> mengubah nomor record menjadi indeks record (base 0) MOD($A14-1,3) -> indeks nomor pola, yaitu sisa bagi indeks nomor record terhadap jumlah pola yang ada (yaitu 3). Jadi, 0 dibagi 3 sisa 0, 2 dibagi 3 sisa 2, 3 dibagi 3 sisa 0 alias sama dengan sisa 0 dibagi 3 alias kembali ke pola pertama lagi +1 -> mengubah indeks nomor pola menjadi nomor pola status_siklus_referensi = $C$22:$C$51 Bahasa manusia : "cel, cariken padanan yang sama atau lebih [lookup] untuk nilai hari siklus di data yang merupakan 1+ sisa bagi selisih hari yang telah dikurangi 1 dengan jumlah total hari per siklus [MOD(C$13-"2014-12-31"-1,58)] pada suatu daftar nilai hari-hari siklus ditabel referensi [$B$22:$B$51] khusus yang menghasilkan nilai numerik [/] berdasar kriteria bahwa nomor pola di tabel referensi [$A$22:$A$51] sama dengan nomor pola untuk record data tersebut, yaitu 1+ hasil sisa bagi nomor record yang sudah dikurangi 1 dengan jumlah pola yang ada dalam tabel referensi [MOD($A14-1,3)], kemudian menampilkan hasilnya berupa nilai-nilai status [$C$22:$C$51] yang sepadan." Wassalam, Kid. On Tue, Feb 17, 2015 at 5:07 PM, Samsudin [email protected]<mailto:[email protected]> [belajar-excel] <[email protected]<mailto:[email protected]>> wrote: Dear Mr. Kid, Terima kasih atas pencerahannya dan jika berkenan bisa memberikan penjelasan tentang formula tersebut karena saya masih belum mudeng formula tersebut. Hormat saya, Samsudin [cid:[email protected]] From: [email protected]<mailto:[email protected]> [mailto:[email protected]<mailto:[email protected]>] Sent: Tuesday, February 17, 2015 12:03 PM To: BeExcel Subject: Re: [belajar-excel] Roster 3-1 [1 Attachment] Pak Samsudin, File terlampir menggunakan fungsi lookup dan sebuah tabel referensi berisi pola-pola yang akan digunakan. Asumsinya, A. nomor pola yang digunakan oleh setiap nama mengikuti nomor urutnya dengan siklus 3 pola. Jadi nomor urut 4 akan kembali ke pola nomor 1. B. tanggal mulai di-set per tanggal 2015-01-01. C. setiap pola adalah siklus 58 hari sesuai ketentuan yang ada. Hari ke-59 akan kembali seperti hari ke-1 di pola tersebut yang tetap. Silakan dikembangkan dan disesuaikan dengan kebutuhan setempat. Wassalam, Kid. On Mon, Feb 16, 2015 at 2:31 PM, Samsudin [email protected]<mailto:[email protected]> [belajar-excel] <[email protected]<mailto:[email protected]>> wrote: Dear Suhu Excel, Mohon bantuan dan pencerahannya untuk membuat jadwal roster 3-1, dimana ketentuannya adalah : 1. Karyawan dibagi 2 shift ( 7 hari masuk pagi dan 7 hari masuk malam) 2. Karyawan diberikan libur 1 hari, setelah masuk 14 hari (shift I dan Shift II) 3. Karyawan diberikan cuti 7 hari, setelah masuk 14 hari + libur 1 hari + 7 hari masuk (shift I / shift II). Atas bantuannya diucapkan terima kasih. Hormat saya, Samsudin --------------------------------------------------------------------------------------------------- This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and hereby notified that any disclosure, copying, or distribution of this message (or any part thereof), or the taking of any action based on it, is strictly prohibited. No liability or responsibility is accepted if information or data is, for whatever reason corrupted or does not reach its intended recipient. No warranty is given that this email is free of viruses. The views expressed in this email are, unless otherwise stated, those of the author and not those of the Company or its management. The Company reserves the right to monitor, intercept and block emails addressed to its users or take any other action in accordance with its email use policy. --------------------------------------------------------------------------------------------------- This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and hereby notified that any disclosure, copying, or distribution of this message (or any part thereof), or the taking of any action based on it, is strictly prohibited. No liability or responsibility is accepted if information or data is, for whatever reason corrupted or does not reach its intended recipient. No warranty is given that this email is free of viruses. The views expressed in this email are, unless otherwise stated, those of the author and not those of the Company or its management. The Company reserves the right to monitor, intercept and block emails addressed to its users or take any other action in accordance with its email use policy. --------------------------------------------------------------------------------------------------- This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and hereby notified that any disclosure, copying, or distribution of this message (or any part thereof), or the taking of any action based on it, is strictly prohibited. No liability or responsibility is accepted if information or data is, for whatever reason corrupted or does not reach its intended recipient. No warranty is given that this email is free of viruses. The views expressed in this email are, unless otherwise stated, those of the author and not those of the Company or its management. The Company reserves the right to monitor, intercept and block emails addressed to its users or take any other action in accordance with its email use policy.
