正在阅读:
excel wps表格 使用公式进行工时计算,公式运用
有位朋友有个工时计算需求,在excel表格中,有人员、工序、耗时,班组等信息,当前是由人工句填写计算每个人每个工时的耗时,主要是扣除中间的休息时间,人工计算,经常出错。
如下表:
希望有个好的方法,能自动计算。
excel其实我平时基本不怎么用,就一些简单的数据处理,也不是特别熟悉,就知道一些常用的公式,不过,根据对方提供的信息,以及系统开发的思维,我要求对方提供一份班次的基本信息。
随后,为了规范数据和格式,对以上表格进行了修改,保证时间数据的利用和后续的计算,同时该表作为不同班次计算工时,扣除休息时间的基本信息表。
完成后回到主表,主表中,希望增加两列,用于输入某项工作的开始和结束时间,并最终在【实际产值】中,显示该项工作的实际耗时。
根据提供的班次信息,要解决以下几个问题:
一种班次跨两个休息时间的问题;
一种班次跨天的问题;
excel(WPS表格)中时间的计算;
在主表中获取班次信息表的信息;
首先是excel的时间计算,网上查了下,基本上使用【11:15】【17:15】这种格式,可以直接进行加减计算,不过计算得出的结果默认单位是天,因此,要计算出两个时间的小时差,还需要乘于24
如17:15-11:15= 0.25天 0.25*24=6小时
但是如果遇到跨天怎么办呢,比如其中一个班次是16:00到次日2:30, 正常用 2:30去减,就得到负数,不过这种情况,只要相减后再加24,就可以计算出实际时间;
补充:还有一种是时间计算方式,如:
(2:30+TEXT(24,"24:00:00"))*24 相当于让跨天的时间增加了24小时后再减去开始时间,进行时间计算。
第二是如果获取班次表中的相关信息,虽然不怎么用excel,不过思路和开发系统差不多,就是【ID】KEY相连, 这里的key,其实就是班次名称, 班次1 班次2 班次3 。。。
然后用到了 vlookup这个函数,如 VLOOKUP(O2,班次!$A$1:$L$8,7,1)
补充:VLOOGUP的基本使用方法,里面几个参数的意思为:
第一个参数O2,表示用于匹配的数据,或者叫关键字,KEY;
第二个参数表示用于匹配的数据范围;
第三个参数表示用于匹配数据范围内的第几列是你需要匹配了之后,返回的数据;
第四个参数是指匹配方式,如完全匹配,或者其他匹配方式。
转换成SQL语句,类似
select 休息时间 from 班次表
where 班次名次='班次1'
结合图中可以看到,vlookup函数,通过O2的班次名次,和【班次】工作簿的班次名次进行筛选,获取该班次对应的相关数据,如开始结束时间,休息时间等。
补充:由于公式可以进行自动填充,因此,在表格范围【班次!$A$1:$L$8】需要设置绝对路径。
最后一个问题,就是关于当前的工作时间,是否需要扣除当前班次对应的休息时间,这个呢,就是根据需求描述,这里简单的用开始时间,和结束时间,和休息的开始时间 和结束时间进行对比,如果包含在内,则扣除,
如果不包含,则不扣除。
最后 多个公式 组合后的结果:
实际产值单元格公式:
=IF(J2<I2,(J2+TEXT(24,"24:00:00"))*24-I2*24,J2*24-I2*24)-IF(AND(I2<=VLOOKUP(O2,班次!$A$1:$L$8,5,1),J2>=VLOOKUP(O2,班次!$A$1:$L$8,6,1)),VLOOKUP(O2,班次!$A$1:$L$8,7,1),0)-IF(AND(I2<=VLOOKUP(O2,班次!$A$1:$L$8,9,1),IF(J2<I2,(J2+TEXT(24,"24:00:00"))*24,J2*24)>=VLOOKUP(O2,班次!$A$1:$L$8,10,1)*24),VLOOKUP(O2,班次!$A$1:$L$8,11,1),0)
操作人员只要在 开始时间 结束时间输入 时间信息,公式自动计算出 实际产值的值,即实际工时。
不过总体好像有点复杂,对于熟悉excel的可能有更好的方法,不过基本满足了需求吧。
转载请注明文本地址:https://www.bemhome.com/post/185.html