EXCEL中用VBA实现自动排班,通过选择年月就能自动显示每月值班信息。

发布网友发布时间：2022-05-01 17:40

共1个回答

热心网友时间：2022-04-14 20:27

无论你身居企业还是行政事业单位，值班那是必须的！特别是如果你是一办公室的负责人，排列值班表的任务就自然落到了你的身上了。通常情况下，值班的领导为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) = "■"

If Weeks = Job(1) Or Weeks = Job(2) Or Weeks = Job(3) Then sht.Cells(R, 4) = "○"

End Sub