资源描述：
《用excel编写倒班的方法》由会员上传分享，免费在线阅读，更多相关内容在应用文档-天天文库。
1、用Excel编写倒班的方法作者：Chfj在本文中将利用Excel表格公式实现倒班查询的任务。本方法不需要VBA等宏编程工具，完全用Excel提供的函数来实现。以下分两种情况。第一种为根据你设定的时期，来查询此日的上班情况。第二种为直接显示当日上班情况。本方法中设定为四班三运转倒班方式，甲乙丙丁班次有可能与实际不符，需要调正公式。一、根据日期查询某日上班情况将下表粘贴到Excel中，在黄色单元格（C2）内输入要查询的日期。即可在下面显示出甲乙丙丁应上什么班和0点班、8点班、4点班是哪个班在上班。在此需要注意C2是查询后面标注为黄色的单元格，粘贴此表须对应单元格粘贴，否则不能正常显
2、示。或也可根据你设置的日期输入单元格替换C2。查询2014年2月7日甲班=CHOOSE(MOD(ROUND(C2,0)+3,8)+1,"第一个8点班","第二个8点班","第一个4点班","第二个4点班","小休","第一个0点班","第二个0点班","大休")乙班=CHOOSE(MOD(ROUND(C2,0)+5,8)+1,"第一个8点班","第二个8点班","第一个4点班","第二个4点班","小休","第一个0点班","第二个0点班","大休")丙班=CHOOSE(MOD(ROUND(C2,0)+7,8)+1,"第一个8点班","第二个8点班","第一个4点班","第二个
3、4点班","小休","第一个0点班","第二个0点班","大休")丁班=CHOOSE(MOD(ROUND(C2,0)+1,8)+1,"第一个8点班","第二个8点班","第一个4点班","第二个4点班","小休","第一个0点班","第二个0点班","大休")0点班=CHOOSE(MOD(ROUNDUP((ROUND(C2,0)-36525)/2,0),4)+1,"丁","丙","乙","甲")8点班=CHOOSE(MOD(ROUNDUP((ROUND(C2,0)-36525-1)/2,0)+3,4)+1,"丁","丙","乙","甲")4点班=CHOOSE(MOD(ROUND
4、UP((ROUND(C2,0)-36525-1)/2,0)+2,4)+1,"丁","丙","乙","甲")4一、查询当日上班情况将下表粘贴到Excel中，本表不需要输入日期即可在打开Excel文件时，自动在下面显示出甲乙丙丁应上什么班和0点班、8点班、4点班是哪个班在上班，现在是几点班。今日=TODAY()甲班=CHOOSE(MOD(ROUND(TODAY(),0)+3,8)+1,"第一个8点班","第二个8点班","第一个4点班","第二个4点班","小休","第一个0点班","第二个0点班","大休")乙班=CHOOSE(MOD(ROUND(TODAY(),0)+5,8)+
5、1,"第一个8点班","第二个8点班","第一个4点班","第二个4点班","小休","第一个0点班","第二个0点班","大休")丙班=CHOOSE(MOD(ROUND(TODAY(),0)+7,8)+1,"第一个8点班","第二个8点班","第一个4点班","第二个4点班","小休","第一个0点班","第二个0点班","大休")丁班=CHOOSE(MOD(ROUND(TODAY(),0)+1,8)+1,"第一个8点班","第二个8点班","第一个4点班","第二个4点班","小休","第一个0点班","第二个0点班","大休")0点班=CHOOSE(MOD(ROUNDUP
6、((ROUND(TODAY(),0)-36525)/2,0),4)+1,"丁","丙","乙","甲")8点班=CHOOSE(MOD(ROUNDUP((ROUND(TODAY(),0)-36525-1)/2,0),4)+1,"甲","丁","丙","乙")4点班=CHOOSE(MOD(ROUNDUP((ROUND(TODAY(),0)-36525-1)/2,0),4)+1,"乙","甲","丁","丙")=NOW()=CHOOSE(ROUNDDOWN((NOW()-ROUNDDOWN(TODAY(),0))*10000/3333,0)+1,"现在是0点班","现在是8点班","
7、现在是4点班")二、锁定单元格为了防止恶意修改，可以对Excel工作表设置保护。参照图一选中需要输入的单元格C2，再单击审阅，然后再单击，弹出图二对话框。在图二对话框中选弹出图三对话框。在图三对话框中输入密码，其它保持默认。确定后在在图四对话框中再次输入确认密码。到此完成。4图一图二4图三图四一、运行效果截图图五4
