同事一早就愁眉苦脸,一问才知,老板让她统计一份报表,里面的数据带着单位,她使用求和公式总是报错,下面我们就来分析如何对带有单位的数据求和。这里分两种情况,一是单位是通过单元格格式设置上去的,求和求积求差求商都不受影响,二是单位一个个手工输入的,更苦恼的是单位还不一致的情况。 先来看第一种:通过单元格格式设置单位 这种在输入表格的时候,正常输入数字即可,单位是后面或是提起设置好了的。选中单元格。 点击鼠标右键,点击“设置单元格格式”。 弹出窗口,点击“自定义”,在类型输入框里输入“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操作技巧》