老板让她统计一份报表数据带着单位用求和公式总是报错
带单位数据汇总求和
先来看第一种:通过单元格格式设置单位
这种在输入表格的时候,正常输入数字即可,单位是后面或是提起设置好了的。选中单元格。
点击鼠标右键,点击“设置单元格格式”。
弹
同事一早就愁眉苦脸,一问才知,老板让她统计一份报表,里面的数据带着单位,她使用求和公式总是报错,下面我们就来分析如何对带有单位的数据求和。这里分两种情况,一是单位是通过单元格格式设置上去的,求和求积求差求商都不受影响,二是单位一个个手工输入的,更苦恼的是单位还不一致的情况。
带单位数据汇总求和
先来看第一种:通过单元格格式设置单位
这种在输入表格的时候,正常输入数字即可,单位是后面或是提起设置好了的。选中单元格。
点击鼠标右键,点击“设置单元格格式”。
弹出窗口,点击“自定义”,在类型输入框里输入“0 单位”即可,比如这里的“0元”。
确定后,选中的单元格批量添加上了元的单位,这样不管加减乘除,丝毫不受影响,你可以试下在单价下方使用求和公式,照样计算出来。
瞧,在金额列输入”数量*单价=金额“的公式,顺利就计算出了金额。
计算出金额后,在底部按下”ALT =“,快速引用出求和公式,计算出总金额。
二、手动输入单位的数字求和
在单价列手动输入单位,如果用正常的计算公式,就会报错,那是因为手动输入单位,单元格格式变成了文本,文本和数字是不可以相乘的。
如果单位都相同,方法一,打开替换窗口,将单位元替换掉。
替换掉单位后,再用平常的方法求金额,汇总金额就行了。
如果老板不让去掉单位,直接计算金额呢?那就用下面这个公式了。
公式:=G2*MID(H2,1,LEN(H2)-1)
MID函数就是将带单位的单价去除单位,转换成数字后再乘以数量。公式后面的”-1“指的是单位的长度,如果单位是”美元“就”-2“。同样的这个公式适合的是单位字符一样的情况。
双击单元格,整列复制公式批量计算出每行的金额。
问题又来了,如果出来单位有的一个字符,有的两个字符的又咋办呢?如下图,两个字符的,使用”-1“就报错了。
别急,这里介绍另外一种方法,也就是昨天《从身份证号码里提取出生日期、计算年龄、判断性别》文中介绍的”CTRL E“快速填充的另一种用法。
在第一个单元格内输入”&13*5“,这里的”&“也可以是其他符号,没有实际的作用。
选中第一个单元格和下方所有空白单元格,在键盘上按下”CTRL E“,批量提取出每行的数量和单价。
打开替换窗口,将符号”&“替换成”=“即可。
替换完成后,单元格内自动转换成公式,计算出结果。
最后再送上手动输入单位的金额如何汇总?这时就可以使用组合公式完成。
公式:=SUMPRODUCT(1*SUBSTITUTE(H2:H10,"元",""))&"元"
公式太长,个人建议复制,修改参数即可,注意的是,这个公式也要求单位一致。
看到这的都是爱学习的,就给个大拇指鼓励下吧,或分享给更多的人。
关注我,持续分享更多办公软件技巧。
excel 表格中公式如何带上单位,比如求和,为什么带上单位就不行了
你用的求和方式只能对数字求和,要带上单位的话,简单的方法就是将数据放在一个单元格,单位放在后面那个单元格,然后将数据的单元格求和就行了。Excel表格里的数据一旦带上单位,就不能运用求和公式怎么样才可以求和呢
数据带单位放在一个单元格里不是好习惯。如果单位不是很复杂,可以将单位放在单元格格式里,如果比较复杂,应该将单位另放一列。
你这个问题的求和公式是
=SUMPRODUCT(--LEFT(E5:H5,LEN(E5:H5)-1))
也可以
=SUM(--LEFT(E5:H5,LEN(E5:H5)-1))
但这个是数组公式了。
文章评论