办公小技巧:Excel协助 生产流程不冲突

CFan 电脑爱好者 2021-05-07 10:01应用 标签:技巧 办公

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

201921sclc1

图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)。

201921sclc2

图2 提取任务号

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

201921sclc3

图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)。

201921sclc4

图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)。

201921sclc5

图5 字符连接

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

201921sclc6

图6 查看冲突任务

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

201921sclc7

图7 条件格式设置

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

201921sclc8

图8 显示冲突任务

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

QQ图片20210420165237