如下图:是员工的工时记录表,记录了每个员工每天的工作小时数,有一些单元格中记录的是汉字+工时的记录方式,现在想要合计所有的工时,怎么合计呢?
这是答疑群里的一个问题,很少遇到,特意给大家写一下,思路很重要!
合计公式
1、在H2单元格输入公式:=SUM(IFERROR(--MID(B2:G2,LENB(B2:G2)-LEN(B2:G2)+1,10),0))。
2、然后按CTRL+SHIFT+回车(数组输入快捷键),结果计算出来了!
向下复制公式,所有的计算结果就都出来了。
思考过程
1、在答疑群中看到这个问题后,开始感觉太复杂了,基本不能实现;后来想到用数组公式应该能解决问题,我用一个员工的数据来展示思考的过程。
2、如下图,首先判断一个单元格汉字的数量,使用公式:=LENB(B2)-LEN(B2)。
LENB( ):一个汉字占两个宽度位置;
LEN( ):一个汉字占一个宽度位置;两个相减,正好是汉字的数量。
3、提取数字,使用公式:=MID(B2,LENB(B2)-LEN(B2)+1,10)。
说明:MID是从中间截取数字;LENB(B2)-LEN(B2)+1是从汉字的下一位开始截取;数字10,是为了简化公式,设置一个固定长度,超过最大数字长度就行。
这是一个典型汉字中提取数字的公式。
4、上面的公式截取出来的是文本格式,不能进行合计,所以要转换为数字,在公式前加上两个减号,负负得正,就把文本转换为了数字;但是空单元格时会出错,如下图。
5、于是在公式外边再加上一个IFERROR,来处理错误值。
6、最后用SUM合计所有情况下的数字,只有最后的结果是正确的。
说明:这是我临时想到的一个公式,你有什么好的方法吗?欢迎留言!
留言与评论(共有 0 条评论) |