Excel协助 生产流程不冲突

因为Excel自带日程表和函数，所以我们经常在Excel中制作一些和日期有关的流程表。不过只是简单的写上日期，这样并不能很好地规避流程中的日期冲突，其实借助Excel的函数和条件格式等工具，我们可以制作出更为科学的流程图表。比如某公司有三个加工中心，分别可以加工不同部门的零件，生产部制定生产流程表后，需要检查每个流程在选择不同加工中心后，加工任务会不会冲突，如果发生冲突，则需要提示冲突原因，并且重新选择加工中心或者生产时间以避免冲突（图1）。

图1 可以检查冲突流程表

从上面的案例可以看到，不同产品在选择每个加工中心后都有一个生产时间，如果同一个加工中心在加工不同产品的时候发生时间重叠，这样就会导致冲突。对于冲突的检查，我们可以借助比较前后任务的结束时间和开始时间进行检查。在上述案例中，任务1和任务2都使用“加工中心1”，任务1结束时间是2019/11/7，但是任务2的开始时间是2019/11/6，因此两个任务会出现冲突。要实现上述效果只要解决下面三个问题即可：

1.每个加工中心对应的任务号

2.每个和加工中心对应任务的开始和结束时间

3.根据是否冲突添加文字提示和颜色提醒

首先是提取任务号，任务号提取可以借助LOOKUP函数来完成，定位到H2单元格中，输入函数“=IF(F2="",NA(),LOOKUP(D2,IF($F$1:F1=F2,$D$1:D1,NA()),$A$1:A1))”，这是一个IF嵌套数组函数，它使用LOOKUP函数进行任务号的提取，任务号是通过F列选择的加工中心号码提取。完成函数的输入后按下Crrl+Shift+回车键完成数组函数的输入，这样下拉就可以提取到符合要求（时间冲突）的任务号了（图2）。

图2 提取任务号

接着根据任务号提取任务的开始和结束时间，定位到J2单元格输入函数“=VLOOKUP(H2,$A$2:$E$6,4,0)”，这一函数的意思是在A2:E6区间查找开始时间值，值在查找区域的第4列（D列），查找到的开始任务的时间填充到H列对应的单元格中。这样下拉公式后如果出现冲突的任务号，这个任务的开始时间会自动提取（图3）。

图3 提取开始时间

继续在K2单元格中输入公式“=VLOOKUP(H2,$A$2:$E$6,5,0)”用来提取对应任务的结束时间。定位到I2单元格输入公式“=IF(K2>=D2,TRUE,"")，这里使用IF函数对时间的比较值进行判断，如果K2单元格（即上一个任务的结束时间）大于D2单元格（即比较任务的开始时间），那么就显示为TRUE，表示有时间冲突。比如任务1的结束时间是2019/11/7就大于任务2的开始时间2019/11/6，所以该任务就有冲突。这里注意比较的是同一个加工中心的任务，冲突的I3单元格显示为TRUE（图4）。

图4 比较时间

对于显示为TRUE的任务，可以使用“&”（连字符）来自定义显示的冲突提醒字符。在实例中提醒字符为“冲突！有加工任务-任务1:一车间,涡轮”，可以看到提醒文本的格式是：“ 冲突！有加工任务-”+“H列的提取的任务号”+“任务号对应的B、C列对应的内容”，因此提醒字符可以使用固定字符+H+B+C列的格式组合。定位到L2单元格输入公式“="冲突！有加工任务-"&H2&":"&VLOOKUP(H2,$A$2:$E$6,2,0)&","&VLOOKUP(H2,$A$2:$E$6,3,0)”，这里B、C列的内容使用VLOOKUP函数进行提取，同时使用“&”字符进行连接（图5）。

图5 字符连接

定位到G2单元格输入公式“=IF(I2=TRUE,L2,"")”，表示当I2单元格显示为TRUE时（表示任务时间有冲突），那么G2单元格就显示L2单元格内容（即上述设置的提醒文本），这样对于冲突的任务在G列就可以正常显示出上述的提示文本，方便我们快速查看是哪项任务和当前任务冲突，并且冲突任务实际加工产品也可以一目了然地看出来（图6）。

图6 查看冲突任务

当然如果冲突的任务很多，我们还可以借助条件格式进行醒目提醒，选择“A2:D6”区域，点击“开始→条件格式→文本包含”，选择单元格包含文本“冲突！”，单元格颜色就填充为“黄色”（图7）。

图7 条件格式设置

这样当我们在流程表中检查出了冲突的任务，提醒单元格就会出现上述提醒文本，并且自动激活条件格式，这些任务的单元格会自动填充为黄色，此时可以通过选择不同的加工中心（可以在F列添加一个加工中心下拉列表进行选择）或者调整起始时间进行调整，为了方便查看还可以将其他无关的复制列全部隐藏（图8）。

图8 显示冲突任务

当然这里我们只是以生产流程为例，大家可以举一反三将它运用在多个会议室安排，一个主持人在多场会议的主持任务等场合中。