动态轮换工作时间表Excel [英] Dynamic rotating work schedule excel
问题描述
我在这里的订单很高，
我正在尝试为3个团队在今年剩余时间里制定轮换的卫生设施清单.
I'm trying to make a rotating sanitation list for the remainder of the year for 3 teams.
我希望工作表根据工作的一周中的几天自动分配团队成员的工作区域(房间； B，C，D列).
I want the sheet to auto assign team members job areas (Rooms;columns B,C,D) based on the days of the week the work.
第1小组的成员只能分配到周一至周五的任何会议室 第4小组的成员只能分配任何房间星期六，并且是星期日的首选小组 只能为第5小组成员分配任何周三的房间，并且是星期六的首选小组.
Members of Team 1 can only be assigned any Room Mon-Fri Members of Team 4 can only be assigned any Room Sat-Tues and are the preferred team for Sundays Members of Team 5 can only be assigned any Room Wed-Sat and are the preferred team for Saturdays
A列中的每一天在每个区域(房间)将有3个(B-D列)不同的员工ID.
Each day in column A will have 3 (columns B-D) different employee ID's in each area(Room)
我希望工作表能使每个人都均匀分布，并让每个人都能工作所有3套房间.
I want the sheet to keep everyone evenly spread out in terms of occurrences and for everyone to work all 3 sets of rooms.
作为一个附加的挑战，我希望能够在每个团队之间添加或删除员工ID，并可以自动调整工作表.
As an added challenge, i would like to be able to add or subtract employee ID's to and from each team and have the sheet auto adjust.
B列中有一个公式，该公式可以显示我要执行的操作，但这是来自其他人的工作，因此我无法使其适应我的需求.基本上列B，C和& D需要公式.
I have a formula in column B that shows what I am trying to do, but it was from another person's work and I having trouble adapting it to my needs. Basically columns B,C,& D need the formulas.
这是单元格B3中的公式，我只是将其复制下来
=IF(OR(WEEKDAY($A2)=1,WEEKDAY($A2)=7),IFERROR(INDEX(Team1,MOD(MATCH(OFFSET(B2,IF(WEEKDAY($A2)=5,-5,-5),0),Team1,0),$U$2)+1),$G$2),IFERROR(INDEX(Team1,MOD(MATCH(OFFSET(B2,IF(WEEKDAY($A2)=2,-3,-1),0),Team1,0),$U$2)+1),$G$1))
This is the formula in Cell B3 and I just copied it down
=IF(OR(WEEKDAY($A2)=1,WEEKDAY($A2)=7),IFERROR(INDEX(Team1,MOD(MATCH(OFFSET(B2,IF(WEEKDAY($A2)=5,-5,-5),0),Team1,0),$U$2)+1),$G$2),IFERROR(INDEX(Team1,MOD(MATCH(OFFSET(B2,IF(WEEKDAY($A2)=2,-3,-1),0),Team1,0),$U$2)+1),$G$1))
使用命名范围将团队分为3个单独的列.我不确定如何键入上述公式以包括所有三个团队，以及如何在仅周六至周二使用团队4，仅在周三至周六使用团队5和仅在周一至周五使用团队1.
The teams are set up in 3 separate columns using named ranges. I am not sure how to type the above formula to include all three teams and also get the formula to use Team 4 on only Saturday-Tuesday, Team 5 on only Wednesday-Saturday and Team 1 only on Monday-Friday.
我真的希望我可以上传此表，以供大家看看.
I really wish I could upload this sheet for everyone to take a look at.
感谢我能提供的任何帮助！
Thanks for any help I can get on this!
推荐答案
从另一个论坛获得答案，并且工作完美..不需要VBA
Answered from another forum and works perfect..no need for VBA
此提案对团队的安排进行了一些更改 桌子.该提案还添加了一个可以移动的帮助列(E) 和/或出于美学目的而隐藏，并使用以下内容填充:
=IF(WEEKDAY(A2,16)=1,IF(COUNTIFS(E$1:E1,4)< COUNTIFS(E$1:E1,5),4,5),IF(WEEKDAY(A2,16)=2,IF(COUNTIFS(E$1:E1,5)< COUNTIFS(E$1:E1,4),5,4),IF(WEEKDAY(A2,16)<=4,IF(COUNTIFS(E$1:E1,4)< COUNTIFS(E$1:E1,1),4,1),IF(COUNTIFS(E$1:E1,5)< COUNTIFS(E$1:E1,1),5,1))))
This proposal makes a few changes to the arrangement of the Team's tables. This proposal also adds a helper column (E) which may be moved and/or hidden for aesthetic purposes and is populated using:
=IF(WEEKDAY(A2,16)=1,IF(COUNTIFS(E$1:E1,4)< COUNTIFS(E$1:E1,5),4,5),IF(WEEKDAY(A2,16)=2,IF(COUNTIFS(E$1:E1,5)< COUNTIFS(E$1:E1,4),5,4),IF(WEEKDAY(A2,16)<=4,IF(COUNTIFS(E$1:E1,4)< COUNTIFS(E$1:E1,1),4,1),IF(COUNTIFS(E$1:E1,5)< COUNTIFS(E$1:E1,1),5,1))))
使用以下命令填充房间4"列:
The room 4 column is populated using:
=INDEX(H$5:L$22,IF(MOD(COUNTIFS(E$2:E2,E2)+2*(COUNTIFS(E$2:E2,E2)-1),COUNTIFS(I$5:M$22,E2))=0,COUNTIFS(I$5:M$22,E2),MOD(COUNTIFS(E$2:E2,E2)+2*(COUNTIFS(E$2:E2,E2)-1),COUNTIFS(I$5:M$22,E2))),IF(E2=1,1,IF(E2=4,3,5)))
房间5& 6列的填充方式是:
The room 5&6 column is populated using:
=INDEX(H$5:L$22,IF(MOD(COUNTIFS(E$2:E2,E2)+2*(COUNTIFS(E$2:E2,E2)-1),COUNTIFS(I$5:M$22,E2))=0,0,MOD(COUNTIFS(E$2:E2,E2)+2*(COUNTIFS(E$2:E2,E2)-1),COUNTIFS(I$5:M$22,E2)))+1,IF(E2=1,1,IF(E2=4,3,5)))
使用以下命令填充房间7& 8"列:
The room 7&8 column is populated using:
=INDEX(H$5:L$22,IF(MOD(COUNTIFS(E$2:E2,E2)+2*(COUNTIFS(E$2:E2,E2)-1),COUNTIFS(I$5:M$22,E2))=COUNTIFS(I$5:M$22,E2)-1,1,IF(MOD(COUNTIFS(E$2:E2,E2)+2*(COUNTIFS(E$2:E2,E2)-1),COUNTIFS(I$5:M$22,E2))=0,0,MOD(COUNTIFS(E$2:E2,E2)+2*(COUNTIFS(E$2:E2,E2)-1),COUNTIFS(I$5:M$22,E2)))+2),IF(E2=1,1,IF(E2=4,3,5)))
这篇关于动态轮换工作时间表Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
