【新朋友】点击标题下面蓝色字“王俊东“关注。
【老朋友】点击右上角,转发或分享本页面内容。
excel系列课程
excel特效系列课程开始了,今天第2节!
第1节:Excel有公式的单元格标记颜色,很简单!
第2节:Excel删除空行,全在这里!
第3节:Excel快速填充合计公式!
第4节:Excel图表制作技巧之-借位法!
第5节:Excel批量生成指定表名的技巧!
第6节:Excel多区域条件合计的方法!
第7节:Excel合同金额为零自动报警!
第8节:Excel查询多列数据并求和的方法!
第9节:Excel不显示0的几种方法!
第10节:Excel多表打印小方法!
第11节:Excel提取不重复值的几种方法!
第12节:Excel前后10天的平均价格!
第13节:Excel批量生成文件夹的技巧!
第14节:Excel隐藏列,不让别人打开!
第15节:Excel正数、负数分开的技巧!
第16节:Excel多表合计之-合并计算!
第17节:Excel财务必看,动态查询统计!
第18节:Excel自由跳转的表格-超链接!
第19节:Excel根据入库单自动生成明细账-VBA!
第20节:Excel快速删除所有插件!
第21节:Excel自动分配金额的账龄分析表!
第22节:Excel一劳永逸制作工资条!
第23节:Excel群发工资条-邮件合并!
第24节:Excel基础功能-分列!
今天的问题如下图:是员工的工时记录表,记录了每个员工每天的工作小时数,有一些单元格中记录的是汉字+工时的记录方式,现在想要合计所有的工时,怎么合计呢?
这是答疑群里的一个问题,很少遇到,特意给大家写一下,思路很重要!
合计公式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合计所有情况下的数字,只有最后的结果是正确的。
说明:这是我临时想到的一个公式,你有什么好的方法吗?欢迎留言!
来源:Excel天天练
看完点点下面,对我的最大支持!