无论你身居企业还是行政事业单位,值班那是必须的!特别是如果你是一办公室的负责人,排列值班表的任务就自然落到了你的身上了。通常情况下,值班的领导为1-2人,值班的工作人员为2-4人,排列组合的结果就有很多了!当然,如果你的单位只有2-5人,就算是手动排列也花不了多少时间,要是有100人呢?排列的规则不同,自然排出的值班表就各不相同,本文旨在研究方法,并非一一枚举有多种排列,或许可以对你的工作有所启发!
数据表格设计 本文采用EXCeL来做,标题设为【泊梁山甲午年五虎上将、天罡地煞轮流值班表】,表头在第三,分别为【日期、带班领导、值班人员、标志、备注】!
值班规则约定 五虎上将:共5人,每1人负责值班一周,依次轮流;天罡地煞:共20人,每2人负责值班一周,依次轮流。遇周末(周六、周日)所有人员休息,值班的人不休息,反过来理解也行,即当班人的人员不去打战而休整……遇节假日值班人也不休息,即没有机会外出考察观光了……又遇周末并且没时间出去考察的就大碗吃肉喝酒吧!详细说明见如程序实现思路!
Sub test()
Dim sht As Worksheet, Lead(5), Under(10), Holidays(30), Job(10), Weeks, R%, C%, DaiBan$, ZhiBan$
Set sht = ThisWorkbook.Sheets(1)
Lead(1) = "林 冲"
Lead(2) = "卢俊义"
Lead(3) = "吴 用"
Lead(4) = "关 胜"
Lead(5) = "公孙胜"
Under(1) = "秦 明 呼延灼"
Under(2) = "花 荣 柴 进"
Under(3) = "李 应 朱 仝"
Under(4) = "鲁智深 武 松"
Under(5) = "董 平 扬 志"
Under(6) = "戴 宗 刘 唐"
Under(7) = "李 逵 扈三娘"
Under(8) = "雷 横 顾大嫂"
Under(9) = "时 迁 孙二娘"
Under(10) = "阮小二 潘金莲"
Holidays(1) = #4/5/2014#
Holidays(2) = #4/6/2014#
Holidays(3) = #4/7/2014#
Holidays(4) = #5/1/2014#
Holidays(5) = #5/2/2014#
Holidays(6) = #5/3/2014#
Holidays(7) = #5/31/2014#
Holidays(8) = #6/1/2014#
Holidays(9) = #6/2/2014#
Holidays(10) = #9/6/2014#
Holidays(11) = #9/7/2014#
Holidays(12) = #9/8/2014#
Holidays(13) = #10/1/2014#
Holidays(14) = #10/2/2014#
Holidays(15) = #10/3/2014#
Holidays(16) = #10/4/2014#
Holidays(17) = #10/5/2014#
Holidays(18) = #10/6/2014#
Holidays(19) = #10/7/2014#
Job(1) = #5/4/2014#
Job(2) = #9/28/2014#
Job(3) = #10/11/2014#
sht.Range(sht.Cells(4, 1), sht.Cells(65535, 4)).ClearContents
For Weeks = #3/31/2014# To #12/31/2014#
R = sht.[A65536].End(xlUp).Row + 1
sht.Cells(R, 1) = Weeks
If DateDiff("d", #3/31/2014#, Weeks) Mod 7 = 0 Then
DaiBan = Lead(DateDiff("d", #3/31/2014#, Weeks) Mod 5 + 1)
ZhiBan = Under(DateDiff("d", #3/31/2014#, Weeks) Mod 10 + 1)
sht.Cells(R, 2) = DaiBan
sht.Cells(R, 3) = ZhiBan
Else
sht.Cells(R, 2) = DaiBan
sht.Cells(R, 3) = ZhiBan
End If
If Weekday(Weeks) = 7 Or Weekday(Weeks) = 1 Then sht.Cells(R, 4) = "▲"
For C = 1 To UBound(Holidays)
If Holidays(C) = Weeks Then sht.Cells(R, 4) = "●"
If (Weekday(Weeks) = 7 Or Weekday(Weeks) = 1) And Holidays(C) = Weeks Then sht.Cells(R, 4) = "■"
Next
If Weeks = Job(1) Or Weeks = Job(2) Or Weeks = Job(3) Then sht.Cells(R, 4) = "○"
Next
End Sub