↑免费领取Excel模板、视频教程学习↑带单位数字的计算问题很让人头疼，其根源在于数据源的不规范性今天的文章不是讨论表格的使用规范，也不是操作技巧的教程今天的案例将通过分析几种常见问题，由浅入深的对带单位数字的计算问题进行一次梳理
，让大家能够举一反三不仅知其然，更要知其所以然1数字后面的单位都是统一的如下图所示，每个人的销售额后面都有一个“元”字要对这样的一列数字求和，比较常用的是SUMPRODUCT和SUBSTITUTE组合，公式为：。
= SUMPRODUCT (1* B2:B14 (B2:B14,"元",""))&"元"
思考：这个公式的原理比较简单，首先是SUBSTITUTE(B2:B14,"元","")这一部分关于SUBSTITUTE的用法，之前有专门的教程，这个函数的功能是把单元格内指定的字符换成另一个内容，有点类似于查找替换功能。
思路解析：①平时使用函数的时候，第一参数是一个单元格在本例中第一参数使用的是数据区域，目的是把B2:B14这个范围内的每个单元格中的“元”字替换为空，也就是清除单位②由于SUBSTITUTE的结果是文本格式，不能直接求和，所以在前面用1*将结果转为数字。
1*也可以写成--，也就是两个减号，利用负负得正来实现文本转数字的效果③1*B2:B14 (B2:B14,"元","")得到的是一组数字，要对一组数字求和需要用到SUMPRODUCT函数如果用SUM函数的话，需要同时按Ctrl、shift和回车键才行。
④最后在求和结果后连接一个“元”字保持整体一致思路扩展：如果单位是两个字的话，方法也是一样的，例如数量单位都是“公斤”，则求和公式对应修改为=SUMPRODUCT(1*SUBSTITUTE(C2:C14,"公斤",""))&"公斤"。
即可。如下图：
2单位不一致，但是单位的长度一致这种情况比较少见，例如每个业务员需要领取不同包装方式和数量的赠品答谢客户，现在需要对赠品数量（可以理解为份数，不管是一盒还是一包都算作一份包装下的赠品）进行汇总，具体数据如图所示。
这种情况一般用公式=SUMPRODUCT(LEFT(D2:D14,LEN(D2:D14)-1)*1)解决，与第一种情况的区别在于将SUBSTITUTE这部分改为LEFT-LEN组合，LEFT函数的作用是从单元格数据的最左边开始提取指定字数的内容。
思路解析：①在本例中，难点是如何确定数字的位数，因此借助了LEN函数辅助LEN函数的功能是统计单元格内数据的字数，因为单位都是一个字，所以数字的位数就是整体内容的字数减1公式中的LEFT(D2:D14,LEN(D2:D14)-1)就是来提取数字的。
②同样，LEFT函数得到的也是文本，需要处理后才能求和。这和第一类问题的原理完全一样，不再赘述。3单位不统一，字数也不一致这种情况一般是针对同一行的数据进行计算，例如下面这个例子。
思考：金额=单价*数量，这个公式原本很简单，但是因为数量中存在着字数不等的单位，就需要先将数量中的数字提取出来后才能计算金额思路解析：①上面的图中用到的公式是=B2*-LOOKUP(1,-LEFT(C2,ROW($1:$9)))。
它看似与前两类状况是一样的“带单位数字的计算”的问题，但是有本质上的区别②前两类是数组计算，后面这类问题的本质却是找出对单元格内的数字的提取方法，进而再去计算就本例而言，还有一个更为常见的公式套路：=B2*LEFT(C2,LEN(C2)*2-LENB(C2))
关于如何从单元格提取数字，之前有一篇很详细的教程，本文就不再赘述小结：还是要再次强调数据源的规范性！不论何种情况，规范的数据源是高效工作的前提有些视觉效果可以用自定义格式去实现，例如统一添加单位“元”总之，一个单元格不要出现两种属性的内容，数字和单位分开存放才是最合理的。
扫一扫添加老师微信
在线咨询Excel课程推荐阅读关注我们，发现更多Excel优质教程大厂HR：有这种Excel思维的实习生，我真的想高薪留下来95后小姐姐因数据混乱被老板骂哭：我怒用这个神器一秒完成数据规范整理靠一只“猪”一秒拆分上千个工作表？！同事的骚操作看呆我......
比VBA好用100倍！拆分工作表，用数据透视表5秒就搞定！课程推荐抱怨身处黑暗，不如提灯前行
您的“点赞”、“在看”和 “分享”是我们持续产出的动力。
亲爱的读者们，感谢您花时间阅读本文。如果您对本文有任何疑问或建议，请随时联系我。我非常乐意与您交流。
发表评论:
◎欢迎参与讨论，请在这里发表您的看法、交流您的观点。