Excel设置工作日历的方法图文教程(如何用Excel设置工作日历呢)

生产计划中排程是基于日程来定的，什么时候开工，什么时候不开工都需要提前规划好，这也是计算产能负荷的重要依据之一，工作日历在信息化软件是有专门的设定的，如“公假、假期日历、工厂日历”，设置好这些，在进行MRP运算的时候，可以设定基于主日历运算的逻辑。

古老师在实际使用信息化软件中，进行系统配置的工作日历较少，原因是大多数工厂的信息化软件都不用这个，默认全年无休。加上配置好的工作日历赶不上“变化”，调整太大，反而不如在Excel中设置方便，所以平时在计算人力负荷、设备负荷的时候，工作日历统一用Excel设定好，再上传到信息化软件中（如Excel服务器、帆软BI、MES等），进行导入。

今天分享一下如何用Excel设置工作日历。新建一个工作表，命名“XX工厂工作日历”，输入标题：日期、年、月、周、星期、计划出勤天数、剩下出勤天数、计划出勤工时、剩下出勤工时以及公众假日和日期。分别有颜色标记对应的公式项和填写项，输入完成效果如下图：

日期：录入公式：A2=SEQUENCE(365,,L2),创建一个开始日期为2023年1月1日的连续365天的日期，录入完后，立即按Ctrl+Shift+3，切换成标准的日期格式“YYYY-MM-DD”,自动填充把当年的日期全部录入完；

年、月、周：录入公式B2=YEAR(A2)、C2=MONTH(A2)、D2=WEEKNUM(A2,2),分别对应显示对应的年、月、周，此时返回的结果都是数值，可以通过自定义格式“#”后面加中文显示对应的中文显示结果，如显示数值1为1周，自定义格式“#周”；

这里需要注意的周，因为2023年1月1日刚好是星期天，所以函数参数用2的话就，1月2就是第2周了，今年就有53周了，如果需要连续7天代表1周的话，就把参数更改为1，周数的定义如果是出口型工厂，建议和客户的周数一致即可；

星期公式=WEEKDAY(A2,2),下拉填充是一个1到7范围的数字，不是我想要的中文显示，当然也可以不用公式，直接等于A1，并把格式设置为“AAA”一样可以显示星期，但是本质上还是日期

所以这个公式需要更改一下，换成标准的中文显示，把公式变成=VLOOKUP(WEEKDAY(A2,2),{1,\”星期一\”;2,\”星期二\”;3,\”星期三\”;4,\”星期四\”;5,\”星期五\”;6,\”星期六\”;7,\”星期日\”},2,0)，就得到一个标准的中文周数显示；

计划出勤天数-填写项,这一列设定为人工判断，因为工厂出勤多少天，需要计划考虑，充分考虑公众假期和星期日来制定，一般用0代表不出勤，1代表出勤，在填写前可以提前把公众假期录入好，再通过条件格式的颜色来提醒这些假期。

选中F2单元格，条件格式→使用公式确定单元格格式→录入公式=IFERROR(VLOOKUP($A2,$L:$L,1,0),0)=$A2→确定；并把F2的格式通过格式刷或者粘贴格式的方法复制到所有工作日历，这样当是公众假日的时候就自动提醒颜色了；

接下来就手动填写计划出勤天数，一般工厂每月就休息2天，所以一般情况设定两个周日就0就可以了，如有公众假日，就少设置一个周日或者不设置。

剩下出勤天数，就是用公式判断，只要日期比当天小就返回0，录入公式=IF(B2<TODAY(),0,F2)

同理计划出勤工时也是手工填写，条件根据实际情况填写，一般情况是，如果是单班，只要有出勤，除了星期六、星期日不加班（8小时 ）的话，其他日期都是加班（11小时）。剩下出勤工时：录入公式：=IF(B2<TODAY(),0,H2)

这些录入完后，基本就完成工作日历的设置了，最后来一个汇总版本的。

公式1：=UNIQUE(C2:C366)

公式2：=SUMIFS(H:H,C:C,O2)

公式3：=SUMIFS(I:I,C:C,O2)

这样全年的数据一目了然了，做工作日历的最大的目的就是以此为基准，在任何有日期的地方都可以通过VLOOKUP函数匹配对应的数据进行对应负荷分析；

如标工计算出本月订单需要的工时20000小时，如本月出勤还有200小时，这样就可以反算出人力负荷需要200人。设备负荷同样的原理。

源文件：88 排程中的工作日历如何制定？.XLSX